ارجاع به سلولها دو نوع کلی دارند. ارجاع نسبی و ارجاع مطلق. این دو نوع ارجاع در هنگام کپی کردن سلولها رفتارهای متفاوتی از خود نشان می دهند. ارجاع های نسبی در هنگام کپی کردن سلولها تغییر می کنند اما از سمت دیگر ارجاع های مطلق تغییری نمی کنند و ثابت می مانند.

ارجاع های نسبی (Relative references)

به صورت پیش فرض تمامی سلولهای اکسل در حالت ارجاع نسبی می باشند. بنابراین هر وقت که سلولهای دارای فرمول را کپی می کنید با توجه به موقعیت سلول در ردیف و ستونی که قرار گرفته است ارجاع های داخل فرمول تغییر می کنند. برای مثال اگر شما فرمول

=A1+B1

را از ردیف 1 به ردیف 2 کپی کنید. فرمول به شکل

=A2+B2

تغییر میکند. ارجاع های نسبی مخصوصا در مواقعی که نیاز داشته باشید تا یک محاسبه را عینا در ردیف های مختلف تکرار کنید بسیار کار شما را راحتتر می کنند.

روش ایجاد و کپی کردن یک فرمول با استفاده از ارجاع نسبی

در مثال زیر، ما می خواهیم یک فرمول بسازیم که در هر ردیف قیمت (price) را در ستون مقدار (quantity) ضرب کند. بجای اینکه برای هر ردیف یک فرمول جداگانه بسازیم، ما می توانیم فقط یک فرمول در سلول D2 بسازیم و آن را به سایر ردیف ها نیز کپی کنیم. از آنجا که ما داریم از ارجاع نسبی استفاده می کنیم فرمول محاسبات مربوط به هر ردیف را به درستی انجام خواهد داد.

➊ سلولی را که قصد ایجاد فرمول در آن را دارید انتخاب کنید، در این مثال ما سلول D4 را انتخاب می کنیم.



➋ فرمول را در سلول مربوطه وارد کنید. در این مثال فرمول زیر را وارد می کنیم.

=B4*C4

➌ دکمه اینتر را بفشارید. فرمول محاسبه شده و نتایج در سلول مربوطه نمایش داده می شوند.

➍ با استفاده از ویژگی fill handle سلول فرمول را به ردیفهای مجاورش کپی می کنیم.



➎ بر روی fill handle کلیک کنید و ماوس را پایین نگهداشته و بکشید. در این مثال ما با استفاده از ویژگی fill handle سلول فرمول را به دامنه سلولی D5:D13 کپی می کنیم.



➏ حالا دکمه ماوس را رها کنید. فرمول به سلولهای مورد نظر کپی می گردد. با توجه به اینکه ارجاع به سلولها بصورت نسبی می باشد، نتایج هر ردیف به درستی محاسبه شده و نمایش داده می شوند.

ارجاع های مطلق (Absolute references)

ممکن است بعضی وقتها نخواهید تا وقتی که فرمولها را کپی می کنید ارجاع ها بصورت نسبی باشند. برخلاف ارجاع های نسبی، ارجاع های مطلق موقع کپی کردن تغییر نمی کنند وثابت باقی می مانند. شما هر زمان که لازم باشد می توانید از ارجاع های مطلق استفاده کنید.

ارجاع های مطلق با قرار دادن یک علامت دلار ($) در ارجاع مشخص می گردند. این علامت می تواند در کنار نام ردیف یا نام ستون و یا هر دو قرار بگیرد.

ایجاد و کپی کردن یک فرمول با استفاده از روش ارجاع مطلق

در مثال زیر، ما می خواهیم از سلول E2 (که دارای نرخ مالیات به ارزش 7.5% می باشد) برای محاسبه مالیات فروش تمامی آیتمهای موجود در ستون D استفاده کنیم. برای اینکه مطمئن شویم ارجاع ما به سلول نرخ مالیات در تمامی فرمولها (حتی فرمولهایی که با روش کپی کردن ایجاد شده اند) ثابت می ماند. ما به سلول E2 بصورت ارجاع مطلق اشاره می کنیم و آن را بصورت

$E$2

در فرمولها می نویسیم.

➊ سلول مربوط به فرمول را انتخاب کنید. در این مثال ما سلول D4 را انتخاب می کنیم.



➋ فرمولتان را وارد کنید. در این مثال ما فرمول زیر را وارد می کنیم :

(B4*C4)*$E$2

➌ اینتر کنید. فرمول محاسبه شده و نتایج نمایش داده می شوند.

➍ ویژگی fill handle را در سلول D4 فعال کنید.



➍ بر روی علامت fill handle کلیک کنید و ضمن پایین نگهداشتن دکمه سمت چپ ماوس دامنه سلولی D5:D13 را انتخاب کنید.



➎ حالا دکمه ماوس را رها کنید. فرمول به سلولهای انتخاب شده کپی می گردد. با توجه به اینکه ارجاع به نرخ مالیات بصورت ارجاع مطلق بوده است مقادیر به درستی محاسبه خواهند شد.

استفاده از ارجاع سلولها در برگه های دیگر (ارجاع بین برگه ها)

اکسل به شما اجازه می دهد تا به هر سلولی در هر برگه ای ارجاع بدهید. این قابلیت در هنگامی می خواهید به یک سلولی که در یک برگه دیگر اکسل قرار دارید ارجاع کنید بسیار سودمند است. برای ارجاع بین برگه ها شما باید در هنگام ارجاع ابتدا نام برگه را بیاورید و سپس یک علامت تعجب (!) بعد از آن قرار دهید، بعنوان مثال اگر می خواهید به سلول A1 که در برگه ای با نام Sheet1 قرار دهد ارجاع بدهید باید آن را به شکل زیر بنویسید:

Sheet1!A1

روش ارجاع به سلولهای سایر برگه های اکسل

در مثال زیر، ما به یک سلول که خودش نتایج یکسری محاسبات است و در برگه دیگری قرار دارد، ارجاع می دهیم. این قابلیت به ما امکان می دهد تا از یک مقدار مشابه در بین دو برگه استفاده کنیم و مجبور نباشیم تا فرمول را از نو بنویسیم و یا اینکه داده ها را کپی کنیم.

➊ ابتدا سلولی را که می خواهید به آن ارجاع بدهید را پیدا کنید، در این مثال ما می خواهیم به سلول E14 که در برگه Menu Order قرار دارد ارجاع بدهیم.



➋ برگه مورد نظرتان را انتخاب کنید. در این مثال ما برگه Catering Invoice را انتخاب می کنیم.



➌ سلولی را که می خواهید نتایج در آن ظاهر شود را انتخاب کنید. در این مثال ما سلول D4 را انتخاب می کنیم.



➍ ابتدا علامت = و سپس نام برگه و سپس علامت تعجب ! و سپس نام سلول مورد ارجاع را وارد کنید. در این مثال ما مقدار زیر را تایپ می کنیم :

='Menu Order'!E14

➎ دکمه اینتر را بفشارید. مقدار سلول ارجاع داده شده نمایان می شود. حالا هر گاه که مقدار سلول E14 در برگه Menu Order تغییر کند، مقدار آن در برگه Catering Invoice هم بصورت اتوماتیک تغییر خواهد کرد.

تمرین عملی

➊ ابتدا فایل اکسل زیر را دانلود کنید.

فایل نمونه اکسل برای تمرین

➋ برگه Paper Goods را انتخاب کنید.

➌ در سلول D4 فرمولی بنویسید که بهای واحد B4 را در مقدار C4 ضرب کند، و نتیجه آن را در نرخ مالیات E2 ضرب کند. در مورد نرخ مالیات از ارجاع مطلق استفاده نمایید، زیرا نرخ مالیات برای همه ردیف ها یکسان می باشد.

➍ با استفاده از ویژگی fill handle فرمول را به دامنه سلولی D5:D12 کپی کنید.

➎ نرخ مالیات در سلول E2 را به 6.5% تغییر بدهید. دقت کنید که تمامی سلولهای شما بروز رسانی می شوند. وقتی کارتان تمام شد فایل اکسل شما چیزی شبیه تصویر زیر خواهد بود.



➏ برگه Catering Invoice را انتخاب کنید.

➐ مقدار داخل سلول C5 را حذف کنید و آن را با سلول total cost در برگه paper goods جایگزین نمایید. نکته : مقدار total cost در سلول E13 در برگه Paper Goods می باشد.

➑ وقتی کار شما تمام شد برگه اکسل شما باید چیزی شبیه تصویر زیر باشد.



منبع : gcflearnfree

---

آموزش قبلی : ایجاد فرمولهای پیچیده تر با اکسل 2016

---

آموزش بعدی : 16 . توابع (Functions) اکسل 2016