آشنایی با آدرس‌دهی مطلق و نسبی
یادگیری مفهوم آدرسی در اکسل بسیار مهم است. در جلسات قبلی اشارات کوتاهی به بحث آدرس‌ها داشتیم اما اینجا به طور مفصل به آن می‌پردازیم.
فرض کنید که یک لیست از فاکتورهای فروش داریم. که در ستون اول شماره فاکتور و در ستون دوم مبلغ فاکتور قرار گرفته‌اند. شماره فاکتور ردیف اول، به صورت «فاکتور 1» وارد شده است. اگر با درگ کردن قصد تکمیل این ستون را داشته باشیم، سایر سلول‌های این ستون به صورت پیش فرض به فرم Fill Series تکمیل می‌شوند. یعنی به ترتیب فاکتور 2، فاکتور 3 و ...
اگر صرفاً قصد کپی کردن محتوای سلول‌ها را دارید، باید از گزینه Copy Cells استفاده کنید. حال می‌خواهیم یکسری مقادیر را برای جدول بالا محاسبه کنیم.
محاسبه مالیات ارزش افزوده: B5*9%=
در اینجا سلول B5 حاوی مبلغ فروش فاکتور 1 است. با درگ کردن حاصل این فرمول در ستون مالیات، می‌توانید مالیات ارزش افزوده کلیه فاکتورها را محاسبه کنید.
اگر بخواهید درصد ارزش افزوده را به صورت مستقیم در فرمول به کار نبرید و آن را از سلول دیگری، فراخوانی کنید، متوجه می‌شوید که با درگ کردن حاصل فرمول در ستون مالیات یا با خطا مواجه می‌شوید یا مقادیر به درستی نمایش داده نمی‌شوند. اگر شما مقدار 9% را در سلول C3 قرار داده باشید و فرمول را بدین صورت بنویسید: B5*C3=
پس از درگ کردن، همانطور که آدرس سلول متناظر مقدار روش فاکتور یکی یکی به سمت پایین می‌رود، اکسل برای محاسبه فرمول در فاکتورهای 2 به بعد، از سلول C3 هم یکی یکی به سمت ایین حرکت می‌کند، که باعث بروز خطا یا نمایش مقادیر ناصحیح می‌شود. ما انتظار داریم که ارجاع سلول C3 محاسبه فاکتورهای بعدی تغییر نکند. جهت اجتناب از تغییر باید از آدرس دهی مطلق استفاده کنیم. B5*$C$3=
با استفاده از علامت $ در ارجاع سلولی آدرس دهی مطلق انجام می‌شود. اگر هم قبل از نشانگر سطر و هم نشانگر ستون، از علامت $ استفاده کنیم، خاصیت آدرس دهی مطلق هم در سطر و هم در ستون فعال شده است. اگر علامت $ فقط قبل از یکی از نشانگرها درج شود، در این‌صورت شناوری در نشانگر دیگر رعایت می‌شود. B5*C$3=
در اینجا خاصیت آدرس‌های مطلق فقط روی ستون فعال است.
نکته: علامت $ را هم می‌توانید به صورت دستی تایپ کنید هم از طریق فشردن دکمه F4
نکته: آدرس‌های مطلق به رنگ قرمز و آدرس‌های نسبی به رنگ آبی نمایش داده می‌شود.
با توجه به توضیحات بالا، آدرس‌دهی‌هایی زیر بدین ترتیب هستند.
|
A1 |
آدرس‌دهی نسبی |
|
$A1 |
آدرس‌دهی مختلط |
|
A$1 |
آدرس‌دهی مختلط |
|
$A$1 |
آدرس‌دهی مطلق |
فرض کنید که جدول متشکل از یک سری اعداد داریم. حال می‌خواهیم به سلول‌های این جدول، به شیوه‌های مختلف ارجاع دهیم.
A2 67
D2 53
134=$A2*2
اگر آدرس‌دهی را در این فرمول به صورت نسبی تغییر دهیم و فرمول را کپی کرده و در چهار سلول آن‌سوتر Paste کنیم، مشاهده می‌کنیم که حاصل 106 می‌شود. چون فرمول با فرم Fill Series تبدیل به فرمول زیر شده است. D2*2=
اگر فرمول را هم در سطر و هم در ستون جابجا نموده و Paste کنید، مجدداً مشاهده می‌کنید که فرمول کپی شده به صورت متناظر در سطر و ستون تغییر کرده است. در صورتی که از آدرس‌دهی مختلط استفاده کنید، فرمول کپی شده تنها در سطر یا ستونی که اصطلاحاً Freeze نشده(علامت $ در کنار آن وجود ندارد)، به صورت متناظر Paste می‌شود.
مثال اول:
A2 67
A4 49
C2 10
$A2*2=98
در اینجا فقط ستون Freeze شده و سطر شناور است. بنابراین اگر فرمول را کپی کنیم، تغییر فقط در سطر اتفاق می‌افتد و فرمول بدین صورت در می‌آید: 98=$A4*2
مثال دوم: A$2*2=134
در اینجا، سطر Freeze شده و ستون شناور است. بنابراین اگر فرمول را کپی کنیم، تغییر فقط در ستون اتفاق می‌افتد و فرمول بدین صورت در می‌آید:
20=C2*2
مثال سوم، جدول ضرب:
فرض کنید که می‌خواهیم یک جدول ضرب ده در ده، ایجاد کنیم. اعداد یک تا ده را به ترتیب در سلول‌های B1 تا K1 و سلول‌های A2 تا A11 وارد نموده‌ایم. اگر فرمول زیر را در سلول B2 وارد کنیم:
A2*B1=
و بخواهیم آن را برای سایر سلول‌های جدول ضرب هم کپی کنیم، متوجه می‌شویم که مقادیر نادرستی به عنوان نتیجه نمایش می‌یابند. نمایش صحیح این مقادیر از طریق استفاده درست از آدرس‌دهی مطلق و نسبی امکان‌پذیر است. ابتدا سعی می‌کنیم سطر اول جدول ضرب را تکمیل می‌کنیم. اگر فرمول را به شیوه زیر تغییر دهیم، با توجه به آدرس‌دهی مختلطی که انجام شده است و عدم تغییر ستون، در حین کپی فرمول در سطر فعلی، محاسبه فرمول به درستی صورت می‌گیرد. =$A2*B1
اما شما نمی‌توانید این سطر را کپی کرده و برای تکمیل سایر سطرهای جدول استفاده کنید، چون باز هم با مقادیر اشتباهی روبرو خواهید شد. فرمول را به شیوه زیر تغییر می‌دهیم: $A2*B$1=
در این صورت، در بخش دوم فرمول هم، سطر Freeze می‌شود و در حین کپی فرمول تغییر نمی‌کند. بدین ترتیب می‌توانید با کپی سطر اول، کل جدول ضرب را بسازید.
مثال چهارم: فرض کنید یک جدول از تعداد فروش یکسری کالا، به تفکیک ماه‌های سال داریم. حال می‌خواهیم مبلغ فروش هر کالا را در هر ماه، محاسبه کنیم و در ستون میزان فروش هر ماه درج کنیم.
B4*C4=
در اینجا سلول B4 حاوی قیمت واحد کالا و سلول C4 حاوی تعداد فروش در ماه اول است. اگر این فرمول را در همین ستون کپی کنیم، میزان فروش هر ماه تمام کالاها به درستی محاسبه خواهد شد.
اما اگر همین فرمول را برای سایر ماه‌ها، برای همین کالا، کپی کنیم مقادیر نادرست محاسبه می‌شود. چون باید در تنظیم فرمول دقت شود که شناوری از ستون سلب شود.
=$B4*C4
در این صورت، اگر فرمول برای سایر ماه‌ها هم کپی شود، چون ستون ثابت می‌ماند و فقط سطر تغییر می‌کند، مقادیر به درستی محاسبه خواهند شد. می‌توانید همین فرمول را برای کلیه ماه‌های سایر کالاها هم، کپی و استفاده کنید.