درExcel دو مدل آدرس دهی به نامهای : آدرس دهی مطلق و آدرس دهی نسبی داریم.
آدرس دهی نسبی
فرض كنید سلولهای Excel را به صورت زیر پر كرده ایم :
در سلول B1 فرمول A1+A2 را می نویسیم .اگر این فرمول را copy كرده و در سلول B2 .Paste كنیم و یا با استفاده از Auto fill محتوای سلولهای B2 تا B4 را پر كنیم .اعداد 5و7و4 به ترتیب برای سلولهای B2 تا B4 ظاهر میشوند. حال میخواهیم بدانیم این اعداد از كجا بدست آمده اند. وقتی ما در سلول B1 فرمول A1+ A2 را تایپ میكنیم، در حقیقت سلول سمت چپی و یك سلول پایین آن با هم جمع میشوند. پس برای سلول B2 ، سلول سمت چپی ( A2 ) و سلول پایینی آن ( A3) با هم جمع میشوند كه جواب 5 میشود. برای سلولهای بعدی هم به همین ترتیب محاسبات انجام میشود. به این نوع آدرس دهی ، آدرس دهی نسبی گفته میشود چون نسبت به مكان هر سلول ، فرمول سلول عوض میشود و در حقیقت فرمول درون سلول B2 ، A2+A3 میشود.
هنگام کار با اکسل یکی از مفاهیمی که بسیار کاربردی هست و همیشه با آنها سروکار داریم بحث آدرس دهی هست. آدرس دهی نسبی یا همون Relative Cell Reference شکل معمولی آدرس سلول ها مانند A1 - B2 و... می باشد. فرض کنید ما چند ستون داده داریم و قرار هست همه آنها را در همان ستون جمع بزنیم . معمولا فرمول جمع را برای ستون اول نوشته و همان فرمول را با استفاده از قابلیت Auto Fill برای سایر ستون ها کپی می کنیم. مانند شکل زیر
ما در عکس بالا برای بدست آوردن جمع ستون اول از فرمول زیر استفاده کرده ایم.
خوب حالا با استفاده از قابلیت Auto Fill این فرمول را به خانه های مجاورش کپی کردیم که پس از کپی آدرس سلول ها به تناسب نام ردیف تغییر کرده و جمع مربوط به ستون خودش رو محاسبه کرده است .
و نهایتا به شکل زیر جمع سه ستون را با یکبار نوشتن و استفاده از آدرس دهی نسبی و بدون مشکل خاصی محاسبه نموده ایم.
اما حالا می خواهیم اعداد یک ستون را با یک عدد مشخص که در یک سلول قرار دارد جمع کنیم . خوب طبق روش بالا ابتدا فرمول جمع رو برای سطر اول نوشته و با استفاده از قابلیت Auto Fill اون رو برای سایر سطرهای زیرینش اعمال می کنیم. اما اینجا یک مشکل بوجود میاد اونم اینکه پس از اعمال قابلیتAuto Fill از سطر دوم تا اخر همه اعداد رو صفر نشون می دهد.مشکل کار اینجاست که با هر بار کپی آدرس نسبی خانه ای که عددمشخص شده جهت جمع کردن در آن قرار داشت متناسب با مقدار جابجایی آدرس آن سلول نیز تغییر می کند.برای درک بهتر مطلب تصاویر زیر را دنبال کنید
پس از کپی فرمول به سایر خانه ها طبق تصویر زیر می بینید که خروجی عدد صفر را نشان می دهد و آدرس نسبی خانه A6 به A7 تغییر کرده است.
طبق تصویر زیر آدرس نسبی خانه A6 به A8 تغییر کرده است.
اما راه حل اینکار استفاده ازآدرس دهی مطلق یا همون Absolute Cell Reference هست که سلولی را که مطلق می نماید، آدرس آن خانه در هنگام کپی ثابت می ماند و تغییرنمی کند.اما برای مطلق کردن آدرس یک سلول از علامت
$ استفاده می شود. که اینکار به سه روش قابل اجرا بر روی آدرس آن سلول می باشدکه در تصویر زیر مشاهده می نماید.
که پس از مطلق کردن سلول A6 در مثال بالا خروجی به شکل زیر خواهد شد.
همچنین می توانید آدرس سلولی را که می خواهید مطلق شود را انتخاب کنید های لایت کنید و کلید F4 را بزنید خواهید دید که علامت مطلق
$ به آن اضافه می شود.
در این آدرس دهی با كپی كردن فرمول در هر سلولی ، فرمول عوض نمی شود.فرض كنید میخواهیم حقوق افراد یك اداره را حساب كنیم. حقوق با استفاده از فرمول زیر حساب میشود ( حقوق پایه * 7% – حقوق پایه = حقوق ) حقوق پایه را در ستون B مینویسیم. برای محاسبه حقوق میتوانیم در سلول C1 فرمول B1-B1*7%= را بنویسیم و برای تمام افراد Auto fill كنیم. در این حالت در حقیقت از آدرس دهی نسبی استفاده كرده ایم. ولی فرض كنید كه درصد مالیات عوض شده و 10% شود. حالا باید دوباره فرمول جدیدی در سلول C1 نوشته و مجدد Auto fill كنیم. برای اینكه نخواهیم در هر بار عوض شدن درصد مالیات فرمول را عوض كنیم میتوانیم درصد مالیات را در سلول جدا نوشته و از آدرس آن در فرمول استفاده كنیم. پس مثلاً در سلول A1 عدد 7% را مینویسیم و در سلول C1 فرمول B1-B1*A1 را مینویسیم حال Auto fill میكنیم. ولی میبینیم كه برای بقیه سلولها جواب همان حقوق پایه می شود. چرا؟چون با آدرس دهی نسبی میخواهد عمل كند و چون در سلول C1 فرمول B1-B1*A1 بوده ، برای C2 فرمول B2-B2*A2 میشود كه مقدار A2 صفر است. پس جواب همان مقدار حقوق پایه میشود. برای رفع این مشكل باید سلول A1 ثابت شود. یعنی در تمام فرمولها A1 در ستون حقوق پایه ضرب شود. در این حالت از آدرس دهی مطلق استفاده میكنیم. برای ثابت كردن سطر یا ستون در كنار حرف ستون یا عدد سطر علامت $ میگذاریم. اگر بخواهیم سلول را ثابت كنیم در كنار حرف ستون و عدد سطر هر دو علامت $ میگذاریم. یعنی در سلول C1 مینویسیم :B1-B1*$A$1