ارجاع به سلول ها دو نوع کلی دارن. ارجاع نسبی و ارجاع مطلق. این دو نوع ارجاع زمان کپی کردن سلول ها رفتارهای متفاوتی از خودشون نمایش میدن. ارجاع های نسبی زمان کپی کردن سلول ها تغییر میکنن اما ارجاعهای مطلق تغییری نمیکنن و ثابت هستن.
ارجاع های نسبی (Relative references)
به صورت پیش فرض تمام سلولهای اکسل در حالت ارجاع نسبی هستن. بنابراین هر وقت که سلولهایی که فرمول دارن رو کپی میکنید با توجه به موقعیت سلول در ردیف و ستونی که قرار گرفته ارجاعهای داخل فرمول تغییر میکنن. برای مثال اگر شما فرمول
=A1+B1
رو از ردیف ۱ به ردیف ۲ کپی کنید. فرمول به شکل
=A2+B2
تغییر میکنه. ارجاعهای نسبی مخصوصا در مواقعی که نیاز دارین یک محاسبه رو عینا در ردیفهای مختلف تکرار کنید خیلی کار شما رو راحتتر میکنن.
ایجاد و کپی کردن یک فرمول با استفاده از ارجاع نسبی
در مثال زیر، ما میخواهیم یک فرمول بسازیم که در هر ردیف قیمت (price) رو در ستون مقدار (quantity) ضرب کنه. بجای اینکه برای هر ردیف یک فرمول جداگانه بسازیم، ما میتونیم فقط یک فرمول در سلول D2 بسازیم و اون رو به سایر ردیفها هم کپی کنیم. چون ما داریم از ارجاع نسبی استفاده میکنیم، فرمول محاسبات مربوط به هر ردیف رو درست انجام میده.
۱- سلولی رو که قصد ایجاد فرمول در اون دارید رو انتخاب کنید، در این مثال ما سلول D4 رو انتخاب میکنیم.
۲- فرمول رو در سلول مربوطه وارد کنید. در این مثال فرمول زیر رو وارد میکنیم.
=B4*C4
۳- دکمه اینتر رو فشار بدید. فرمول محاسبه میشه و نتایج در سلول مربوطه نمایش داده میشن.
۴- با استفاده از ویژگی fill handle سلول فرمول رو به ردیفهای مجاورش کپی میکنیم.
۵- روی fill handle کلیک کنید و ماوس رو پایین نگهداشته و بکشید. در این مثال ما با استفاده از ویژگی fill handle سلول فرمول رو به دامنه سلولی D5:D13 کپی میکنیم.
۶- حالا دکمه ماوس رو رها کنید. فرمول به سلولهای مورد نظر کپی میشن. با توجه به اینکه ارجاع به سلولها بصورت نسبی هستن، نتایج هر ردیف به درستی محاسبه شده و نمایش داده میشن.
- توجه: شما میتونید با دوبار کلیک کردن روی سلولهایی که فرمول دارن، فرمول مربوطه رو ببینید تا از صحت فرمول اطمینان حاصل کنید. در ارجاع نسبی فرمولها در هر ردیف باید متفاوت باشن.
ارجاع های مطلق (Absolute references)
ممکنه بعضی وقتها نخواهید وقتی که فرمولها رو کپی میکنید ارجاعها بصورت نسبی باشن. برخلاف ارجاعهای نسبی، ارجاعهای مطلق موقع کپی کردن تغییر نمیکنن وثابت باقی میمونن. شما هر زمان که لازم باشه میتونید از ارجاعهای مطلق استفاده کنید.
ارجاعهای مطلق با قرار دادن یک علامت دلار ($) در ارجاع مشخص میشن. این علامت میتونه در کنار اسم ردیف یا ستون و یا هر دو قرار بگیره.
- توجه: شما معمولا از روش $A$2 استفاده میکنید، دو روش دیگه که در تصویر بالا میبینید خیلی کمتر کاربرد دارن.
- توجه: وقتی که فرمولی رو مینویسید، با فشردن کلید F4 در صفحه کلید میتونید بین روش ارجاع نسبی و یا ارجاع مطلق تغییر وضعیت بدید. این کار روش سادهای برای نوشتن ارجاعهای مطلق هست.
ایجاد و کپی کردن یک فرمول با استفاده از روش ارجاع مطلق
در مثال زیر، ما میخواهیم از سلول E2 (که دارای نرخ مالیات به ارزش ۷.۵% هست) برای محاسبه مالیات فروش تمام آیتمهای موجود در ستون D استفاده کنیم. برای اینکه مطمئن بشیم ارجاع ما به سلول نرخ مالیات در تمام فرمولها (حتی فرمولهایی که با روش کپی کردن ایجاد شدن) ثابت میمونه. ما به سلول E2 بصورت ارجاع مطلق اشاره میکنیم و اون رو بصورت
$E$2
در فرمولها مینویسیم.
۱- سلول مربوط به فرمول رو انتخاب کنید. در این مثال ما سلول D4 رو انتخاب میکنیم.
۲- فرمول رو وارد کنید. در این مثال ما فرمول زیر رو وارد میکنیم :
(B4*C4)*$E$2
۳- اینتر کنید. فرمول محاسبه و نتایج نمایش داده میشن.
۴- ویژگی fill handle رو در سلول D4 فعال کنید.
۵- روی علامت fill handle کلیک کنید و ضمن پایین نگهداشتن دکمه سمت چپ ماوس دامنه سلولی D5:D13 رو انتخاب کنید.
۶- حالا دکمه ماوس رو رها کنید. فرمول به سلولهای انتخاب شده کپی میشه. با توجه به اینکه ارجاع به نرخ مالیات بصورت ارجاع مطلق بوده مقادیر درست محاسبه میشن.
- توجه: شما میتونید با دوبار کلیک کردن روی سلولهای کپی شده، فرمول موجود در اونها رو مشاهده کنید تا از صحیح بودن فرمول مطمئن بشید. ارجاعهایی که بصورت مطلق بودن در تمامی ردیفها به همان شکل ثابت هستن و بقیه ارجاعها که نسبی بودن از روی ردیف مربوطه گرفته میشن.
- توجه: مطمئن شوید که در هنگام ارجاع های مطلق حتما از علامت $ استفاده کرده باشید. در مثال زیر تعمدا در بعضی جاها این علامت را قرار نداده ایم. همانطور که در تصویر زیر مشاهده می کنید اکسل در اینگونه مواقع از ارجاع نسبی استفاده می کند و هنگام کپی شدن نتایج غلطی را تولید می کند.
استفاده از ارجاع سلول ها در برگه های دیگر (ارجاع بین برگه ها)
اکسل به شما اجازه میده تا به هر سلولی در هر برگهای ارجاع بدید. این قابلیت وقتی میخواهید به یک سلولی که در یک برگه دیگه اکسل قرار داره ارجاع بدید خیلی مفید هست. برای ارجاع بین برگهها شما باید در هنگام ارجاع اول نام برگه رو بیاورید و بعد یک علامت تعجب (!) بعد از اون قرار بدید، بعنوان مثال اگر میخواهید به سلول A1 که در برگهای با نام Sheet1 قرار داره ارجاع بدید باید اون رو به شکل زیر بنویسید:
Sheet1!A1
- توجه: اگه نام برگه شما دارای فاصله (space) باشه باید اون رو داخل علامت نقل قول (‘ ‘) بذارید. بعنوان مثال اگه میخواهید به سلول A1 که در برگه July Budget قرار داره ارجاع بدید باید اون رو به شکل زیر بنویسید:
‘July Budget’!A1
روش ارجاع به سلول های سایر برگه های اکسل
در مثال زیر، ما به یک سلول که خودش نتایج یکسری محاسبات هست و در برگه دیگهای قرار داره، ارجاع میدیم. این قابلیت به ما اجازه میده تا از یک مقدار مشابه در بین دو برگه استفاده کنیم و مجبور نباشیم تا فرمول رو از نو بنویسیم و یا اینکه دادهها رو کپی کنیم.
۱- سلولی که میخواهید به اون ارجاع بدید رو پیدا کنید، در این مثال ما میخواهیم به سلول E14 که در برگه Menu Order قرار داره ارجاع بدیم.
۲- برگه مورد نظر رو انتخاب کنید. در این مثال ما برگه Catering Invoice رو انتخاب میکنیم.
۳- سلولی که میخواهید نتایج در اون ظاهر بشه رو انتخاب کنید. در این مثال ما سلول D4 رو انتخاب میکنیم.
۴- اول علامت = و بعد اسم برگه و بعد علامت تعجب ! و بعد اسم سلول مورد ارجاع رو وارد کنید. در این مثال ما مقدار زیر رو تایپ میکنیم:
=’Menu Order’!E14
۵- دکمه اینتر رو فشار بدید. مقدار سلول ارجاع داده شده نمایان میشه. حالا هروقت که مقدار سلول E14 در برگه Menu Order تغییر کنه، مقدار اون در برگه Catering Invoice هم بصورت اتوماتیک تغییر میکنه.
- توجه: اگه شما بعدها نام برگه اکسل رو هم تغییر بدید، اکسل بصورت اتوماتیک خودش ارجاعها رو اصلاح میکنه و نام جدید رو جایگزین نام قبلی میکنه.
- توجه: اگه در هنگام ارجاع دادن، نام برگه رو اشتباه تایپ کرده باشین، خطای ارجاع در سلول #REF! ظاهر میشه. در مثال زیر ما نام برگه رو به عمد اشتباه تایپ کردیم. اگه روی خطای ظاهر شده کلیک کنید اکسل سه گزینه برای ویرایش، صرفنظر و یا تحقیق کردن در مورد خطا رو در اختیار شما میذاره.
