آموزش مراجع نسبی و مطلق سلولی در اکسل
معرفی
دو نوع مرجع سلولی وجود دارد: نسبی و مطلق. مراجع نسبی و مطلق وقتی کپی می شوند و در سلول های دیگر پر می شوند، رفتار متفاوتی دارند. هنگامی که یک فرمول در سلول دیگری کپی می شود، مراجع نسبی تغییر می کنند. از سوی دیگر، ارجاعات مطلق بدون توجه به جایی که کپی می شوند ثابت می مانند.
اختیاری: کتاب کار تمرینی ما را دانلود کنید.
مراجع نسبی
به طور پیش فرض، همه مراجع سلولی مراجع نسبی هستند. هنگامی که در چندین سلول کپی می شوند، بر اساس موقعیت نسبی سطرها و ستون ها تغییر می کنند. به عنوان مثال، اگر فرمول =A1+B1 را از ردیف 1 به ردیف 2 کپی کنید، فرمول به =A2+B2 تبدیل می شود . ارجاعات نسبی مخصوصاً در مواقعی که نیاز به تکرار همان محاسبه در چندین سطر یا ستون داشته باشید راحت هستند.
برای ایجاد و کپی فرمول با استفاده از مراجع نسبی:
در مثال زیر می خواهیم فرمولی ایجاد کنیم که قیمت هر کالا را در مقدار آن ضرب کند. به جای ایجاد یک فرمول جدید برای هر ردیف، می توانیم یک فرمول واحد را در سلول D4 ایجاد کنیم و سپس آن را در ردیف های دیگر کپی کنیم. ما از مراجع نسبی استفاده خواهیم کرد تا فرمول کل هر مورد را به درستی محاسبه کند.
- سلولی را که حاوی فرمول است انتخاب کنید. در مثال خود، سلول D4 را انتخاب می کنیم.
2. برای محاسبه مقدار مورد نظر فرمول را وارد کنید. در مثال ما =B4*C4 را تایپ می کنیم.
3. روی صفحه کلید Enter را فشار دهید. فرمول محاسبه می شود و نتیجه در سلول نمایش داده می شود.
4. دستگیره fill را در گوشه سمت راست پایین سلول مورد نظر پیدا کنید. در مثال ما، دسته fill را برای سلول D4 پیدا می کنیم.
5. دسته fill را روی سلول هایی که می خواهید پر کنید کلیک کنید و بکشید. در مثال خود، سلول‌های D5:D13 را انتخاب می‌کنیم.
6. ماوس را رها کنید. فرمول با ارجاع نسبی به سلول های انتخاب شده کپی می شود و نتیجه را در هر سلول نشان می دهد.
می‌توانید روی سلول‌های پر شده دوبار کلیک کنید تا فرمول‌های آنها را از نظر صحت بررسی کنید. مراجع نسبی سلول باید برای هر سلول، بسته به ردیف آنها متفاوت باشد.
ارجاعات مطلق
ممکن است زمانی پیش بیاید که نخواهید یک مرجع سلول هنگام کپی شدن در سلول های دیگر تغییر کند. بر خلاف مراجع نسبی، مراجع مطلق هنگام کپی یا پر شدن تغییر نمی کنند. می توانید از یک مرجع مطلق برای ثابت نگه داشتن یک سطر و/یا ستون استفاده کنید.
یک مرجع مطلق در یک فرمول با اضافه کردن یک علامت دلار ($) تعیین می شود.می تواند قبل از مرجع ستون، مرجع ردیف یا هر دو باشد.
معمولاً هنگام ایجاد فرمول هایی که حاوی ارجاعات مطلق هستند از قالب A$2$ استفاده می کنید. دو قالب دیگر بسیار کمتر مورد استفاده قرار می گیرند.
برای ایجاد و کپی فرمول با استفاده از مراجع مطلق:
در مثال زیر، از سلول E2 (که دارای نرخ مالیات 7.5٪ است) استفاده خواهیم کرد.برای محاسبه مالیات فروش برای هر کالا در ستون D می باشد. برای اطمینان از ثابت ماندن ارجاع به نرخ مالیات – حتی زمانی که فرمول کپی شده و در سلول‌های دیگر پر می‌شود – باید سلول $E$2 را به یک مرجع مطلق تبدیل کنیم.
- سلولی را که حاوی فرمول است انتخاب کنید. در مثال خود، سلول D4 را انتخاب می کنیم.
2. برای محاسبه مقدار مورد نظر فرمول را وارد کنید. در مثال ما، = (B4*C4)*$E$2 را تایپ می‌کنیم و $E$2 را به یک مرجع مطلق تبدیل می‌کنیم.
3. روی صفحه کلید Enter را فشار دهید. فرمول محاسبه می شود و نتیجه در سلول نمایش داده می شود.
4. دستگیره fill را در گوشه سمت راست پایین سلول مورد نظر پیدا کنید . در مثال ما، دسته fill را برای سلول D4 پیدا می کنیم.
5. دسته fill را روی سلول هایی که می خواهید پر کنید کلیک کنید و بکشید (در مثال ما سلول های D5:D13 می باشند).
6. ماوس را رها کنید. فرمول با یک مرجع مطلق در سلول های انتخاب شده کپی می شود و مقادیر در هر سلول محاسبه می شود.
می‌توانید روی سلول‌های پر شده دوبار کلیک کنید تا فرمول‌های آنها را از نظر صحت بررسی کنید. مرجع مطلق باید برای هر سلول یکسان باشد، در حالی که سایر مراجع نسبت به ردیف سلول هستند.
مطمئن شوید که علامت دلار ($) را هر زمان که یک مرجع مطلق در چندین سلول ایجاد می کنید، اضافه کنید. علائم دلار در مثال زیر حذف شده است. این باعث شد که اکسل آن را به عنوان یک مرجع نسبی تفسیر کند و هنگام کپی شدن در سلول های دیگر نتیجه نادرستی ایجاد کند.
استفاده از مراجع سلولی با چندین کاربرگ
اکسل به شما این امکان را می دهد که به هر سلولی در هر کاربرگ رجوع کنید، که اگر بخواهید مقدار خاصی را از یک کاربرگ به کاربرگ دیگر ارجاع دهید، می تواند مفید باشد. برای انجام این کار، شما به سادگی باید مرجع سلول را با نام کاربرگ و سپس علامت تعجب (!) شروع کنید. برای مثال، اگر می‌خواهید به سلول A1 در Sheet1 ارجاع دهید، مرجع سلول آن Sheet1!A1 خواهد بود .
توجه داشته باشید که اگر نام کاربرگ حاوی فاصله باشد، باید علامت نقل قول (‘ “) را در اطراف نام قرار دهید. برای مثال، اگر می‌خواهید به سلول A1 در برگه‌ای به نام July Budget ارجاع دهید، مرجع سلول آن ‘July Budget’!A1 خواهد بود.
برای ارجاع سلول ها در کاربرگ ها:
در مثال زیر، ما به سلولی با مقدار محاسبه شده بین دو کاربرگ اشاره خواهیم کرد. این به ما این امکان را می‌دهد تا بدون بازنویسی فرمول یا کپی داده‌ها، از همان مقدار در دو کاربرگ متفاوت استفاده کنیم.
- سلولی را که می خواهید به آن ارجاع دهید پیدا کنید و کاربرگ آن را یادداشت کنید. در مثال ما، می‌خواهیم به سلول E14 در کاربرگ Menu Order اشاره کنیم.
2. به کاربرگ مورد نظر بروید. در مثال ما، کاربرگ Catering Invoice را انتخاب می کنیم.
3. سلولی را که می خواهید مقدار در آن نمایش داده شود، پیدا کرده و انتخاب کنید. در مثال خود، سلول C4 را انتخاب می کنیم.
4. علامت تساوی (=)، نام برگه به دنبال علامت تعجب (!) و آدرس سلول را تایپ کنید. در مثال خود، =’Terter Menu’!E14 را تایپ می کنیم.
5. روی صفحه کلید Enter را فشار دهید. مقدار سلول ارجاع شده ظاهر می شود. حال اگر مقدار سلول E14 در کاربرگ Menu Order تغییر کند، این مقدار به طور خودکار در کاربرگ Catering Invoice به روز می شود.
اگر بعداً نام کاربرگ خود را تغییر دهید، مرجع سلول به طور خودکار به روز می شود تا نام کاربرگ جدید را منعکس کند.
اگر نام کاربرگ را اشتباه وارد کنید، #REF! خطا در سلول ظاهر می شود. در مثال زیر، نام کاربرگ را اشتباه تایپ کرده ایم. برای ویرایش، نادیده گرفتن، یا تحقیق در صورت بروز خطا، روی دکمه Error در کنار سلول کلیک کنید و یک گزینه را از منو انتخاب کنید.
چالش!
- کتاب کار تمرینی ما را باز کنید.
- روی برگه Paper Goods در پایین سمت چپ کتاب کار کلیک کنید.
- در سلول D4 فرمولی را وارد کنید که unit price را در B4، مقدار را در C4 و tax rate را در E2 ضرب می کند. مطمئن شوید که از یک مرجع سلول مطلق برای نرخ مالیات استفاده کنید زیرا در هر سلول یکسان خواهد بود.
- از دستگیره fill استفاده کنید تا فرمولی را که ایجاد کرده اید در سلول های D5:D12 کپی کنید.
- tax rate در سلول E2 را به 6.5٪ تغییر دهید. توجه داشته باشید که تمام سلول های شما به روز شده اند. وقتی کارتان تمام شد، کتاب کار شما باید به شکل زیر باشد:
6. روی تب Catering Invoice کلیک کنید.
7. مقدار موجود در سلول C5 را حذف کنید و آن را با اشاره به هزینه کل کالاهای کاغذی جایگزین کنید. نکته: هزینه کالاهای کاغذی در سلول E13 در کاربرگ Paper Goods است.
8. از همان مراحل بالا برای محاسبه مالیات فروش برای هر کالا در کاربرگ Menu Order استفاده کنید. کل هزینه در سلول E14 باید به روز شود. سپس، در سلول C4 از کاربرگ Catering Invoice، یک مرجع سلولی به مجموع کل محاسبه شده ایجاد کنید. توجه : اگر از کتاب تمرین ما برای پیگیری در طول درس استفاده کرده اید، ممکن است قبلاً این مرحله را تکمیل کرده باشید.
9. وقتی کارتان تمام شد، کاربرگ Catering Invoice باید چیزی شبیه به این باشد:
