آدرس‌دهی نسبی و مطلق: علامت دلار ($) در اکسل

هنگام نوشتن یک فرمول در اکسل، علامت دلار ($) در ارجاع به سلول، بسیاری از کاربران را گیج می‌کند. اما توضیح آن بسیار ساده است. علامت دلار در ارجاع سلول‌های اکسل تنها با یک هدف است – به اکسل می‌گوید که در صورت کپی کردن فرمول در سلول‌های دیگر، مرجع را تغییر دهد یا نه. این آموزش کوتاه توضیحات کاملی را در مورد این ویژگی عالی ارائه می‌دهد.

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

یادآوری: آدرس هر سلول از ترکیب یک حرف و یک عدد تشکیل شده است که حرف بیانگر ستون و عدد بیانگر سطر در اکسل است.

آدرس‌دهی نسبی

با توجه به شکل زیر فرمول سلول C1 برابر است با A1+B1. حال در صورتی که فرمول سلول C1 را یک سلول به سمت راست یعنی D1 کپی کنیم تمامی سلول‌های وابسته به C1 نیز که A1, B1 هستند، در همان جهت یعنی به اندازه‌ی یک سلول به سمت راست حرکت می‌کنند؛ بنابراین فرمول سلول D1 برابر می‌شود با B1+C1.

⇓

توجه داشته باشید اگر فرمول سلول C1 را به اندازه‌ی یک سلول به پایین بکشید نیز همین حالت پیش می‌آید؛ یعنی تمامی سلول‌های وابسته به اندازه‌ی یک سلول به پایین می‌آیند. مثلاً اگر سلول C1 به سلول C2 کپی شود سلول وابسته نیز به سلول‌های A2, B2 کپی می‌شوند.

⇓

به عبارتی چون آدرس نسبی بود با کپی کردن سلول حاوی فرمول، سلول‌های وابسته به آن سلول نیز در همان جهت کپی شدند.

آدرس‌دهی مطلق

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

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

توجه: جهت گذاشتن علامت دلار کافیست یا از Shift + 4 استفاده کنید یا از دکمه‌ی F4 روی صفحه‌کلید کامپیوتر.

فرض کنید می‌خواهیم عدد 5 در سلول A1 را با تک‌تک اعداد ردیف 2 جمع کنیم و در ردیف 3 بنویسیم. جهت این کار ابتدا فرمول A1+A2 را در سلول A3 وارد می‌کنیم (A1+A2=A3) همان‌طور که در آدرس‌دهی نسبی گفتیم اگر فرمول سلول A3 را یک سلول به سمت راست یعنی سلول B3 کپی کنیم سلول‌های وابسته به آن یعنی A1,A2 نیز به همان جهت حرکت می‌کنند به عبارت دیگر فرمول سلول B3 می‌شود B1+B2. در حالی که باید A1+A2 می‌شد. به عبارتی سلول A1 به اشتباه به سلول B1 انتقال یافته است یعنی ستون آن تغییر کرده است و از ستون A به ستون B رفته است در حالی که باید روی همان ستون A ثابت می‌ماند ($A1). بنابراین فرمول درست در سلول A3 برابر می‌شود با: $A1+A2.

فرض کنید می‌خواهیم عدد 5 در سلول A1 را با تک تک اعداد ستون B جمع کنیم جهت این کار ابتدا فرمول A1+B1 را در سلول C1 وارد کنیم حال فرمول سلول C1 را به اندازه‌ی یک سلول به سمت پایین کپی می‌کنیم. مشاهده می‌کنیم که فرمول سلول C2 برابر می‌شود با A2+B. در حالی که باید A1+B2 می‌شد به عبارتی می‌توان گفت سلول A1 به اشتباه به سمت پایین حرکت کرده است یعنی از ردیف 1 به ردیف 2 منتقل شده است پس باید سطر آن ثابت شود که به پایین نیاید. بنابراین فرمول درست سلول C1 برابر می‌شود با : A$1+B1.

خلاصه‌ی کاربرد علامت دلار ($) در اکسل

در صورتی که جهت کپی کردن یک سلول به سمت بالا و پایین باشد به این معنی است که شما روی سطرهای مختلف حرکت می‌کنید جهت جلوگیری از حرکت روی سطرهای مختلف کافیست قبل از عدد، علامت دلار را بگذارید مثل A$1.

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

مثال‌ها

مثال 1) ارزیابی عملکرد (مشخص کردن نفر اول و آخر)

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

دانلود فایل اکسل این مثال

پاسخ:

=IF(F2=MAX($F$2:$F$8),”اول”,IF(F2=MIN($F$2:$F$8),”آخر”,””))

ترجمه‌ی فارسی:

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