خوش آموز اولین و تنها سایت آموزشی %100 رایگان ایران با بیش از هزاران مقالۀ آموزشی

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016

کد مطلب : 91 PDF

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

اگر می خواهید مثالهای ارائه شده را دقیقا مانند آموزش اجرا کنید فایل اکسل زیر را که مربوط به مثالهای همین درس می باشد دانلود کنید.

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

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


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

=A1+B1

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

=A2+B2

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

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


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

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

=B4*C4


15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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


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

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

شما معمولا از روش $A$2 استفاده خواهید کرد، آن دو روش دیگری که در تصویر بالا می بینید خیلی کمتر کاربرد دارند.

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

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


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

$E$2

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

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

(B4*C4)*$E$2


15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز


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


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

Sheet1!A1

اگر نام برگه شما دارای فاصله (space) باشد باید آن را داخل علامت نقل قول (' ') محصور کنید. بعنوان مثال اگر می خواهید به سلول A1 که در برگه July Budget قرار دارد ارجاع بدهید باید آن را به شکل زیر بنویسید:

'July Budget'!A1


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


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

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

='Menu Order'!E14


15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

اگر در هنگام ارجاع دادن، نام برگه را اشتباه تایپ کرده باشید، خطای ارجاع در سلول #REF! ظاهر می شود. در مثال زیر ما نام برگه را بعمد اشتباه تایپ کرده ایم. اگر بر روی خطای ظاهر شده کلیک کنید اکسل سه گزینه برای ویرایش، صرفنظر و یا تحقیق کردن در مورد خطا را در اختیار شما می گذارد.

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز


تمرین عملی


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

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

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

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

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

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

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

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

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

15 . ارجاع بصورت نسبی و ارجاع بصورت مطلق در سلولهای اکسل 2016 . آموزشگاه رایگان خوش آموز

منبع : gcflearnfree

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

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



نویسنده : امیر انصاری

دیدگاه ها(0)

دیدگاه خود را ثبت کنید:


دوره رایگان آموزش جامع اکسل 2016 از مقدماتی تا پیشرفته

دوره رایگان آموزش جامع اکسل 2016 از مقدماتی تا پیشرفته