اکثر مردم با استفاده از مراجع نسبی در اکسل^{(in Excel)} آشنا هستند . این به این دلیل است که مراجع سلولی در صفحات گسترده اکسل^(Excel) به طور پیش فرض به روش مرجع نسبی است.

با این حال، مواقعی وجود دارد که مراجع نسبی آزاردهنده می شوند. هر زمان که سلول ها را کپی کنید یا ستون ها و ردیف ها را پر کنید، مراجع سلول را تغییر می دهد. اگر نمی‌خواهید مراجع تغییر کند، باید با ارجاعات مطلق یا ارجاعات مختلط (شامل مراجع نسبی و مطلق) پیش بروید.

در یک مرجع مطلق، هر دو مرجع ستون و ردیف "قفل" هستند، بنابراین هیچ یک از آنها با کپی یا پر کردن از آن سلول تغییر نمی کنند.

این مقاله به بررسی این موضوع می‌پردازد که چگونه می‌توانید از مراجع مطلق استفاده کنید تا اکسل^(Excel) با داده‌ها آنطور که می‌خواهید رفتار کند.

نحوه کار مراجع^(References) نسبی در اکسل^(Excel)

هنگامی که مقادیر را در صفحه گسترده اکسل^(Excel) وارد می کنید ، هر سلول یک حرف و شماره خاص به آن اختصاص داده می شود. این نشان دهنده ستون و ردیف آن سلول است.

به عنوان مثال، مقدار "1" در صفحه گسترده زیر در ستون A و ردیف 2 است. بنابراین "مرجع" به این سلول A2 است .

اگر بخواهید بر اساس این سلول در سلول بعدی محاسبه ای انجام دهید، با افزودن عدد 1 به آن، فرمول زیر را می نویسید:

=A2+1

این فرمول مقدار A2 را در فرمول وارد می کند، آن را محاسبه می کند و سپس نتیجه را به سلولی که این فرمول در آن قرار دارد، خروجی می دهد.

وقتی Enter را فشار دهید ، نتیجه را خواهید دید.

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

در سلول بعدی، مرجع A2 به A3 تبدیل می شود. در سلول زیر آن، A3 به A4 تبدیل می شود. به عبارت دیگر، اکسل^(Excel) می داند که می خواهید 1 را به سلول قبلی اضافه کنید، بنابراین اکسل^(Excel) با کشیدن به پایین، شماره (مرجع ردیف) را بر اساس آن به روز می کند.

اگر فرمول را در ستون‌ها بکشید، به همین ترتیب عمل می‌کند. اکسل^(Excel) به جای به روز رسانی شماره، قسمت آخر مرجع (ستون) را به روز می کند تا همیشه به سلول بالای آن اشاره کند.

ستون سمت راست حاوی B2، سمت راست آن حاوی C2 و غیره است.

این یک مثال ساده از نحوه عملکرد آدرس دهی نسبی برای ارجاعات ستون و سلول است.

نحوه کار مراجع^(References) مطلق در اکسل^(Excel)

^(Absolute)ارجاعات مطلق در اکسل^(Excel) به شما این امکان را می دهد که به همان سلول ارجاع دهید، به جای اینکه اجازه دهید اکسل به طور خودکار^{(Excel automatically update)} مراجع سطر یا ستون را برای شما به روز کند. ارجاع "مخلوط" در صورتی است که فقط سطر یا ستون را قفل کنید و "ارجاع مطلق" زمانی است که هر دو را قفل کنید.

بیایید به چند نمونه نگاه کنیم.

فرض کنید صفحه‌گسترده شما یک «10» در ردیف بالا دارد و می‌خواهید هر ردیف زیر آن عدد را در عدد سلول سمت چپ ضرب کند. 

برای انجام این کار، فرمولی به شکل زیر وارد کنید:

=B$2*A3

این مرجع «2» را قفل می‌کند، بنابراین اگر سلول را با این فرمول به سلول‌های زیر آن بکشید، مرجع ردیف را تغییر نمی‌دهد. از آنجایی که A3 "قفل" باقی می ماند، هم مرجع سطر و ستون به طور خودکار تغییر می کند و همیشه سلول را به سمت چپ ارجاع می دهد.

متوجه خواهید شد که این کار فقط به این دلیل کار می‌کند که در حال کشیدن به سلول‌های همان ستون هستید. بنابراین لازم نیست ستون (B) را با قرار دادن علامت دلار ($) در مقابل آن قفل کنید.

مشکل این است که اگر بخواهید از همان فرمول در سمت راست فرمول اصلی استفاده کنید، مرجع "B" تغییر می کند و فرمول دیگر آنطور که در نظر گرفته شده است به B2 اشاره نمی کند.

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

استفاده صحیح از مراجع ^(References)مطلق^(Absolute) در اکسل^(Excel)

برای استفاده از ارجاع صحیح در این فرمول، باید دقیقاً آنچه را که می‌خواهید انجام دهید در نظر بگیرید.

در این حالت ما رفتارهای زیر را هنگام پر کردن به سمت راست می خواهیم.

• همیشه مقدار سلول B2 را ارجاع دهید
• همیشه مقدار ستون A را ارجاع دهید
• مرجع سطر ستون A را به سطر فعلی فرمول تغییر دهید

با نگاهی به این رفتارها، اکنون می‌دانید که چه چیزی را باید «قفل» کنید و چه چیزی را نه. هر دو "B" و "2" باید قفل شوند (بدون تغییر). همچنین ستون A باید قفل شود.

بنابراین فرمول شما در B3 باید به شکل زیر باشد: =$B$2*$A3

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

استفاده صحیح از ارجاع مطلق ممکن است مشکل باشد، بنابراین ضروری است که زمان بگذارید و به دقت در نظر بگیرید که چگونه می خواهید اکسل فرمول را به هنگام پر کردن ستون ها یا ردیف ها^{(columns or rows)} در هر جهت به روز کند.

دوچرخه سواری از طریق انواع مرجع^{(Reference Types)} در اکسل^(Excel)

زمانی که در حال تایپ فرمول هایی^{(typing formulas)} با ارجاع مطلق هستید، می توانید با فشار دادن کلید F4 ، که مرجع سلول را مطلق می کند، روند را سرعت بخشید.

وقتی F4 را فشار می‌دهید، مکان‌نما می‌تواند در دو طرف مرجع سلول (یا حتی در وسط آن) باشد، و همچنان آن مرجع واحد را به مطلق تبدیل می‌کند.

اگر مطلق نمی‌خواهید (مثلاً به جای آن مخلوط شده)، به F4 ضربه بزنید تا مرجع به شکلی که می‌خواهید به نظر برسد.

اگر می‌خواهید هر نوع ارجاعی را به سلول‌های دیگر در فرمول اضافه کنید، فقط مکان‌نمای خود را در آنجا قرار دهید و دوباره از طریق F4 حرکت کنید.

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

How to Use Absolute References in Excel

Most people are familiаr wіth using relative references in Excel. This is because cell references in Excel spreadsheets default to the relative reference method.

However, there are times when relative references get annoying. It will change cell references any time you copy cells or fill columns and rows. If you don’t want references to change, you’ll need to go with absolute references or mixed references (including relative and absolute references).

In an absolute reference, both the column and row references are “locked,” so neither of those change when you copy or fill from that cell.

This article will look at how you can use absolute references to make Excel behave the way you want with data.

How Relative References Work in Excel

When you enter values into an Excel spreadsheet, each cell has a specific letter and number assigned to it. This represents the column and row of that cell.

For example, the value of “1” in the spreadsheet below is in column A and row 2. So the “reference” to this cell is A2.

If you want to perform a calculation in the next cell based on this cell, by adding 1 to it, you will write the following formula:

=A2+1

This formula will insert the value from A2 into the formula, calculate it, and then output the result into the cell where this formula is.

When you press Enter, you’ll see the result.

With relative referencing, you don’t have to type this formula into every other cell. All you have to do is drag the corner of the cell with the original formula down as far as you’d like. 

In the next cell, the reference to A2 will become A3. In the cell below that, A3 will become A4. In other words, Excel knows that you want to add 1 to the previous cell, so Excel updates the number (the row reference) accordingly as you drag down.

This works in the same way if you drag the formula across columns. Instead of updating the number, Excel will update the latter part of the reference (the column) to always reference the cell above it.

The column to the right contains B2, to the right of that contains C2, and so on.

This is a simple example of how relative addressing works for both column and cell references.

How Absolute References Work in Excel

Absolute references in Excel let you reference the same cell, rather than letting Excel automatically update the row or column references for you. “Mixed” referencing is if you lock only the row or the column, and “absolute referencing” is when you lock both.

Let’s look at some examples.

Let’s say your spreadsheet has a “10” in a top row, and you want every row beneath it to multiply that number by the number in the cell to the left. 

To do this, you’d enter a formula that looks like this:

=B$2*A3

This locks the “2” reference so it won’t change the row reference if you drag the cell with this formula down to the cells below it. Since A3 remains “unlocked” both the row and column reference will still change automatically and always reference the cell to the left.

You will notice that this only works because you’re dragging down into cells in the same column. So you don’t have to lock column (B) by placing a dollar sign ($) in front of it.

The problem with this is that if you want to use the same formula to the right of the original formula, the “B” reference will change, and the formula will no longer reference B2 as intended.

Let’s take a look at how to use absolute references instead of mixed references to make filling in both directions work properly.

Correctly Using Absolute References in Excel

To use the correct referencing in this formula, you must consider precisely what you’re trying to do.

In this case, we want the following behaviors when filling to the right.

• Always reference the value in cell B2
• Always reference the value in column A
• Shift the reference of the row for column A to the current row of the formula

Looking at these behaviors, you now know what you need to “lock” and what you don’t. Both “B” and “2” need to be locked (unchanged). Also, column A needs to be locked.

So your formula in B3 needs to look like this: =$B$2*$A3

Now when you drag this same cell either down or up, the formula works as intended.

Correctly using absolute referencing can get tricky, so it’s essential to take the time to carefully consider how you want Excel to update the formula as you fill columns or rows in either direction.

Cycling Through Reference Types in Excel

You can speed along the process when you’re typing formulas with absolute references by pressing the F4 key, which will make the cell reference absolute.

The cursor can be on either side of the cell reference (or even in the middle of it) when you press F4, and it’ll still convert that single reference to absolute.

If you don’t want absolute (for example, mixed instead), keep tapping F4 until the reference looks the way you want.

If you want to add any kind of referencing to other cells in the formula, just place your cursor there and start cycling through F4 again.

Once you have configured your formula, just press Enter and begin filling your spreadsheet in any direction you like. If you set up your references right, everything should work just as expected.

Related posts

• هنگام کپی کردن فرمول در اکسل، منابع سلول را حفظ کنید

• نحوه حذف خطوط خالی در اکسل

• نحوه استفاده از ویژگی Speak Cells اکسل

• نحوه درج کاربرگ اکسل در Word Doc

• نحوه استفاده از تجزیه و تحلیل What-If اکسل

• نحوه جابجایی ستون ها در اکسل

• 5 روش برای تبدیل متن به اعداد در اکسل

• نحوه ادغام داده ها در چندین فایل اکسل

• مرتب سازی داده های تک ستونی و چند ستونی در صفحات گسترده اکسل

• نحوه ایجاد یک ماکرو یا اسکریپت VBA در اکسل

• نحوه مرتب سازی بر اساس تاریخ در اکسل

• نحوه حذف ردیف های تکراری در اکسل

• چرا باید از محدوده های نامگذاری شده در اکسل استفاده کنید؟

• نحوه استفاده از دستورات If و Nested If در اکسل

• نحوه باز کردن Word و Excel در حالت ایمن

• نحوه درج CSV یا TSV در کاربرگ اکسل

• 2 روش برای استفاده از تابع Transpose اکسل

• نحوه حروف الفبا در اکسل

• 4 روش برای استفاده از علامت چک در اکسل

• 4 روش برای تبدیل Excel به Google Sheets