کاربرد علامت دلار در اکسل

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

تبدیل آدرس های نسبی به مطلق در فرمول های اکسل

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

آدرس دهی نسبی

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

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

آدرس دهی مطلق

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

برای درک بهتر به مثال زیر توجه کنید:

فرض کنیم می خواهیم اعداد ستون های مثال بالا را با یک عدد مشخص که در سلول B1 نوشته شده، جمع کنیم. به منظور ثابت کردن سلول B1 در فرمول علامت دلار را به آدرس این سلول اضافه می کنیم. این سلول می‌ تواند به یکی از ۳ صورت زیر آدرس ‌دهی شود:

هم سطر و هم ستون ثابت بماند: ۱$B$

فقط سطر ثابت بماند: ۱$B

فقط ستون ثابت بماند: ۱ B$

پس برای ثابت نگه داشتن ستون قبل از B علامت $ را اضافه می کنیم و برای فیکس کردن سطر علامت $ را قبل از عدد ۱ می آوریم. به این منظور پس از نوشتن فرمول، آدرس سلولی که می خواهیم در فرمول ثابت بماند را انتخاب می کنیم سپس کلید میانبر F4 را می زنیم و یا با Shift + 4 علامت $ را اضافه می کنیم.

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

=SUM(A2:A4)+$B$1

نام گذاری محدوده ها

کاربرد دیگر علامت دلار ($) در اکسل در نام گذاری محدوده ها است. در مطلب نام گذاری محدوده ها آموختیم که برای نام گذاری یک سلول یا محدوده آن را انتخاب می کنیم و سپس از تب Formulas گزینه Define Name را انتخاب می کنیم. با کلیک بر روی این گزینه کادر نام گذاری باز می شود.

در تصویر بالا نحوه آدرس دهی در قسمت Refers to را مشاهده می کنید. ابتدا نام شیت و بعد از آن علامت تعجب و سپس آدرس محدوده مورد نظر به صورت مطلق یعنی همراه با علامت دلار نوشته شده است. این آدرس به صورت پیش فرض با علامت $ نمایش داده می شود اما این علامت های دلار را می توان حذف کرد که عملکرد دیگری را به اجرا خواهد گذاشت.

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

داده های زیر را در نظر بگیرید. این داده ها در ستون C بر اساس “گسترش داروی ایران” فیلتر شده است. همان طور که در شکل زیر مشاهده می کنید شماره ردیف ها با فیلتر کردن داده ها تغییر کرده و ترتیب آن ها به هم خورده است.

می‌خواهیم فرمولی در در ستون “ردیف” بنویسیم که هنگام فیلتر کردن داده ها، ترتیب شماره ردیف ‌ها به هم نخورد و به ترتیب از ۱ تا ردیف مربوطه ادامه یابد.

در سلول A2 فرمول زیر را وارد کرده و سپس آن را به سمت پایین تا انتهای جدول کپی می کنیم.

=Subtotal(3,$B$2:B2)

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

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

۱- آرگومان دوم تابع Subtotal بازه ای می باشد که قرار است شمارش شود؛ بنابراین اگر بخواهیم این بازه با کپی کردن فرمول بزرگ‌ تر شود باید شروع آن را با علامت $ ثابت کنیم.

۲- در این مسئله، تابع Subtotal برای محاسبه نشدن داده‌های فیلتر شده استفاده شده‌ است.

۳- عدد ۳ نوشته‌ شده در آرگومان اول تابع Subtotal، در حقیقت نقش تابع CountA را دارد و کار شمارش داده های ستون B را انجام می ‌دهد.

فرمت Currency در اکسل

وقتی که عددی را در برگه اکسل وارد می کنیم، به صورت پیش فرض به فرمت General نمایش داده می شود که معمولاً به همان صورتی است که آن را تایپ می کنیم. گاهی برای نمایش اعداد به صورت دلار نیاز داریم که کنار هر عدد از علامت $ استفاده کنیم. ولی بجای تایپ $۱۰۰ ، می توانیم ۱۰۰ را تایپ کنیم و بعد فرمت Currency را روی آن اعمال کنیم.

به این منظور سلول یا محدوده ای که می خواهید فرمت داده های عددی آن ها را تغییر دهید انتخاب کنید. سپس از تب Home، از قسمت Number بر روی باکس لانچر کلیک نمایید.

بعد از باز شدن پنجره Format Cells (این پنجره با کلید های ترکیبی Ctrl+1 هم باز می شود)، دستورالعمل نمایش داده در شکل زیر را اجرا کنید.

نکاتی که باید بدانیم:

پیش فرض آدرس دهی در یک فایل نسبی (یعنی بدون علامت $) است و در صورت نیاز علامت $ را اضافه می کنیم تا آدرس دهی مطلق شود.
آدرس دهی بین دو فایل مطلق است و در صورت نیاز علامت های $ را حذف می کنیم.
پیش فرض آدرس دهی در Conditional Formattin به صورت مطلق است که می توانیم با حذف علامت $ آن را به نسبی تبدیل کنیم.
در نام گذاری محدوده ها پیش فرض آدرس دهی به صورت مطلق انجام می گیرد و اگر بخواهیم می توانیم با حذف علامت $ آن را به صورت نسبی درآوریم.