آموزش اکسل ۲۰۱۶ – درس پانزدهم: ارجاع نسبی و مطلق به سلول ها
آموزش کامل اکسل ۲۰۱۶ : ارجاعات سلولی
مقدمه
در درس قبل از آموزش کامل اکسل ۲۰۱۶ به فرمول نویسی پیشرفته در اکسل پرداخته شد و در این درس با ارجاعات سلولی آشنا می شویم. دو نوع مختلف ارجاع به سلول ها وجود دارد: ارجاع نسبی و ارجاع مطلق. این دو نوع ارجاعات سلولی هنگام کپی کردن فرمول به سلول های دیگر رفتار متفاوتی دارند. ارجاعات نسبی وقتی یک فرمول به سلول دیگر کپی می شود، تغییر می کنند. از سوی دیگر، ارجاعات مطلق تغییری نکرده و ثابت باقی می مانند.
در صورت تمایل می توانید فایل اکسل زیر که مربوط به تمرین آموزش های همین درس می باشد را دانلود نموده و در ادامه آموزش کامل اکسل ۲۰۱۶ با ما باشید.
آموزش کامل اکسل ۲۰۱۶
ارجاع نسبی
بطور پیش فرض تمامی سلول ها در اکسل در حالت ارجاع نسبی قرار دارند. با استفاده از ارجاع نسبی، هنگامی که یک فرمول در سلول های مختلف کپی می شود، بر اساس سطرها و ستونهای قرار گرفته در آن، تغییر می کند.
به عنوان مثال، اگر فرمول A1+B1= را از سطر ۱ به سطر ۲ کپی کنید، فرمول به شکل A2+B2= خواهد شد. ارجاعات نسبی در مواقعی که نیاز به تکرار محاسبات در چند سطر یا ستون را دارید، بسیار مناسب می باشند.
ایجاد و کپی یک فرمول با استفاده از ارجاع نسبی
در مثال زیر، می خواهیم یک فرمول ایجاد کنیم که در هر سطر قیمت هر محصول (price) را در تعداد آن محصول (quantity) ضرب کند. بجای اینکه برای هر سطر یک فرمول جداگانه بسازیم، می توانیم فقط یک فرمول در سلول D2 ایجاد کرده و آن را در سایر سطرها کپی کنیم. با توجه به اینکه از ارجاع نسبی استفاده می کنیم، فرمول، محاسبات مربوط به هر سطر را به درستی انجام می دهد.
۱- سلولی که می خواهید فرمول را در آن ایجاد کنید، انتخاب نمایید. در این مثال ما سلول D4 را انتخاب می کنیم.
۲- فرمول را در سلول مربوطه وارد کنید. در این مثال ما فرمول B4*C4= را تایپ می کنیم.
۳- کلید اینتر روی کیبورد را فشار دهید. فرمول محاسبه شده و مقدار محاسبه شده در سلول مربوط به فرمول نشان داده می شود.
۴- قابلیت fill handle که همان علامت مربع توپر در قسمت پایین سمت راست سلول می باشد، را در سلول D4 فعال کنید.
۵- روی مربع کلیک کرده و با با پایین نگه داشتن ماوس، روی سلول هایی که قصد کپی کردن فرمول در آن را دارید، بکشید. در مثال، دامنه سلولی D5:D13 را انتخاب می کنیم.
۶- دکمه ماوس را رها کنید تا فرمول در سلول های مورد نظر کپی شود. با توجه به اینکه از ارجاع نسبی استفاده کرده ایم، نتایج هر سطر بصورت جداگانه محاسبه و نمایش داده می شوند.
شما می توانید با دبل کلیک روی سلول هایی که دارای فرمول هستند، فرمول مربوطه را مشاهده کرده و از صحت آن اطمینان حاصل کنید. در ارجاع نسبی فرمولهای هر سطر باید متفاوت باشند.
ارجاع مطلق
در ادامه آموزش کامل اکسل ۲۰۱۶ با ارجاع مطلق به سلول های اکسل آشنا می شویم. گاهی ممکن است نخواهید هنگام کپی کردن فرمول به سلول های دیگر، ارجاعات بصورت نسبی باشند. برخلاف ارجاعات نسبی، ارجاعات مطلق هنگامی که یک فرمول به سلول دیگر کپی می شود، تغییری نکرده و ثابت باقی می مانند. شما می توانید از ارجاع مطلق برای حفظ یک سطر و یا ستون ثابت استفاده کنید.
ارجاع مطلق در یک فرمول با اضافه کردن علامت دلار ($) مشخص می گردد. این علامت می تواند پیش از نام سطر یا ستون و یا هر دو قرار گیرد.
معمولا بیشتر از فرمت ۲$A$ در فرمولهای مربوط به ارجاع مطلق استفاده می شود. دو فرمت دیگر که در تصویر بالا مشاهده می کنید، کمتر استفاده می شوند.
نکته: هنگام نوشتن یک فرمول، می توانید کلید F4 را روی صفحه کلید خود فشار دهید تا بین روش ارجاع نسبی و ارجاع مطلق تغییر وضعیت دهید.
ایجاد و کپی یک فرمول با استفاده از ارجاع مطلق
در مثال زیر می خواهیم از سلول E2 (که درصد مالیات آن %۷.۵ است) برای محاسبه مالیات فروش همه اقلام موجود در ستون D استفاده کنیم. برای اطمینان از این که ارجاع به سلول درصد مالیات یعنی E2، در تمام فرمولها (حتی فرمولهایی که به روش کپی ایجاد شده اند) ثابت باقی می ماند، بصورت ۲$E$ در سلول فرمول می نویسیم.
۱- سلولی که می خواهید فرمول را در آن ایجاد کنید، انتخاب نمایید. در این مثال ما سلول D4 را انتخاب می کنیم.
۲- فرمول را در سلول مربوطه وارد کنید. در این مثال ما فرمول زیر را تایپ می کنیم:
=(B4*C4)*$E$2
۳- کلید اینتر روی کیبورد را فشار دهید. فرمول محاسبه شده و مقدار محاسبه شده در سلول مربوط به فرمول نشان داده می شود.
۴- قابلیت fill handle که همان علامت مربع توپر در قسمت پایین سمت راست سلول می باشد، را در سلول D4 فعال کنید.
۵- روی مربع کلیک کرده و با با پایین نگه داشتن ماوس، روی سلول هایی که قصد کپی کردن فرمول در آن را دارید، بکشید. در مثال، دامنه سلولی 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# در سلول ظاهر می شود. در مثال زیر ما نام برگه را اشتباه وارد کرده ایم. بر روی علامت خطای ظاهر شده که در تصویر زیر برجسته شده است کلیک کرده و یکی از گزینه های ویرایش (Edit) ، نادیده گرفتن خطا (ignore) و یا بررسی خطا (Checking) را انتخاب کنید.
تمرین عملی
۱- فایل اکسل زیر را جهت انجام تمرینات دانلود کنید.
۲- برگه Paper Goods را انتخاب کنید.
۳- در سلول D4 فرمولی تایپ کنید که بهای واحد B4 را در سلول تعداد محصول یعنی C4 ضرب کرده و نتیجه آن را در درصد مالیات E2 ضرب کند. در مورد درصد مالیات از ارجاع مطلق استفاده کنید، زیرا درصد مالیات در همه سطرها یکسان می باشد.
۴- از قابلیت fill handle استفاده کرده تا فرمول را به دامنه سلولی D5:D12 کپی کنید.
۵- درصد مالیات در سلول E2 را به %۶.۵ تغییر دهید. توجه داشته باشید که تمام سلول ها بروز رسانی می شوند. بعد از انجام این تمرینات فایل اکسل شما شبیه تصویر زیر می شود.
۶- برگه Catering Invoice را انتخاب کنید.
۷- مقدار داخل سلول C5 را حذف کنید و به وسیله ارجاع، آن را با سلول total cost در برگه paper goods جایگزین نمایید. نکته : مقدار total cost در سلول E13 در برگه Paper Goods می باشد.
۸- بعد از انجام این تمرینات برگه Catering Invoice شبیه تصویر زیر می شود.
آموزش کامل اکسل ۲۰۱۶
ترجمه شده در سایت خلایق
منبع:
gcflearnfree.org
