برخی از کاربران اکسل وقتی علامت دلار (Dollar) را در یک فرمول اکسل می بینند معمولاً کمی دچار سردرگمی می شوند. بسیاری از کاربران تازه کار از این مبحث رد می شوند و آن را نادیده می گیرند اما در واقع اگر می خواهید به عنوان یک تحلیلگر اکسل پیشرفت کنید یا از اکسل برای ساخت ابزارهای شگفت انگیز استفاده کنید باید بدانید که علامت های دلار برای چی و چگونه استفاده می شوند. شیوه استفاده از آن ها ساده است و همه آنچه که باید بدانید را در این مقاله به شما نشان خواهیم داد.
علامت دلار چیست؟
ممکنه شبیه به فرمول زیر را دیده باشید:
=B3 * $A$1
این یک نمونه استفاده از علامت دلار در فرمول اکسل است.
چرا از علامت دلار استفاده می کنیم؟
علامت دلار برای نشان دادن آدرس مطلق (Absolute References) استفاده می شود. این تعریف ممکنه خیلی خوب و واضح نباشد اما برای فهمیدن معنی آن باید روش های آدرس دهی مکان سلول را بشناسید. با دو روش می توانید به مکان یک سلول آدرس دهید: آدرس نسبی (بدون علامت دلار) یا آدرس مطلق (شامل علامت دلار).
آدرس نسبی
آدرس نسبی (Relative Referencing) گزینه پیش فرض در فرمول های اکسل است. در نوشتن هر نوع فرمول اکسل می توانید از این روش استفاده کنید. احتمالاً از آن استفاده کرده اید اما با آن آشنا نبودید، مثلاً در یک فرمول به سلول A1 آدرس داده اید و هیچ علامت دلاری هم وجود ندارد پس در واقع از یک آدرس نسبی استفاده کرده اید.
وقتی که فرمول را در سلول های دیگر کپی یا درگ کنید، آدرس نسبی بیان می کند که اکسل چگونه می تواند مفهوم اصلی فرمول را درک کند و آن را به طور خودکار در سلول های دیگر ترجمه کند.
مثال زیر را در نظر بگیرید، هدف محاسبه مقادیر محدوده Age Unit Value در ستون D است. برای محاسبه مقدار هدف باید مقدار Sales Value در ستون B بر مقدار Units Sold در ستون C تقسیم شود.
می توانید یک معادله ریاضی مستقیم شبیه ۱۰۰,۰۰۰/۱,۰۰= را در سلول D4 وارد کنید اما با این روش نمی توانید آن را به سلول های بعدی در زیر این سلول درگ کنید و باید برای هر سلول ستون D1 یک فرمول جداگانه بنویسید.
روش درست در اینجا استفاده از آدرس نسبی است که به اکسل می گوید سلول ۲ ستون به سمت چپ (B) را بر سلول ۱ ستون به سمت چپ (C) تقسیم کند در نتیجه فرمول در سلول D4 برابر با B4/C4= خواهد شد:
استفاده از سیستم آدرس نسبی به شما امکان می دهد تا فرمول را به سمت سلول های پایین درگ کنید (یا بکشید) و بدون وارد کردن فرمول جدید در هر کدام از این سلول ها، فرمول را به طور مشابه روی آن ها هم اعمال کنید.
همانطور که در تصویر زیر می بینید، وقتی فرمول نوشته شده در سلول D4 را تا سلول D10 درگ کنید، اکسل به طور خودکار مقدار صحیح را برای همه سلول ها محاسبه می کند. فرمول در سلول D10 به طور خودکار به مقدار B10/C10= تغییر پیدا کرده است. به یاد داشته باشید این فرمول هم اعلام می کند که اکسل باید سلول ۲ ستون به سمت چپ را به سلول ۱ ستون به سمت چپ تقسیم کند:
همانطور که گفتیم استفاده از آدرس های نسبی بسیار رایج است و حتی بیشتر افراد بدون شناخت، از آنها در نوشتن فرمول استفاده می کنند.
با این حال مواردی پیش میاد که باید مکان سلول را به نوعی ثابت نگه دارید و اینجاست که مبحث آدرس مطلق و علامت دلار مطرح می شود .
آدرس مطلق
کاربرد آدرس مطلق جایی است که از علامت دلار در فرمول اکسل استفاده می شود. علامت دلار به اکسل اعلام می کند که هنگام درگ یا کپی فرمول در سلول های دیگر، قسمت خاصی از مکان سلول را ثابت نگه دارد و مانع تغییرات روی آن شود. سه روش برای استفاده از علامت دلار در اکسل وجود دارد.
سه روش استفاده از علامت دلار در فرمول اکسل
می توانید در آدرس سلول یک نقطه ثابت بسازید در:
- موقعیت ستون
- موقعیت سطر
- هر دو موقعیت سطر و ستون که شبیه ثابت کردن یک سلول خاص است.
برای تنظیم نقطه ثابت، یک علامت دلار ($) را قبل از قسمت ثابت مورد نظر در آدرس سلول اضافه کنید؛ به عنوان مثال:
= $A1 ….
به دلیل اینکه علامت دلار قبل از حرف ستون A آمده باید ستون A در فرمول به عنوان قسمت ثابت در نظر گرفته شود. اگر این فرمول را در هر جای دیگر صفحه کپی یا درگ کنید، همیشه به ستون A اشاره می کند. بسته به جایی که فرمول کپی می شود، سطر هم تغییر خواهد کرد.
= A$1 ….
به دلیل اینکه علامت دلار قبل از عدد ۱ آمده پس باید سطر ۱ در فرمول به عنوان قسمت ثابت در نظر گرفته شود. اگر این فرمول را در هر جای دیگر صفحه کپی یا درگ کنید، همیشه به سطر ۱ اشاره می کند. بسته به جایی که فرمول کپی می شود، ستون هم تغییر خواهد کرد.
= $A$1 ….
به دلیل اینکه هم قبل از حرف ستون و هم شماره سطر یک علامت دلار قرار گرفته پس سلول A1 در فرمول ثابت می باشد. مهم نیست که این فرمول را در کجای صفحه کپی یا درگ کنید، همیشه به سلول A1 اشاره خواهد کرد.
مثالی برای استفاده از آدرس های مطلق
فرض کنید در مثال قبل می خواهیم یک محدوده با نام Commission Value اضافه کنیم و مقدار هر سلول در این محدوده برابر با ۱۰ درصد از مقدار سلول متناظر در محدوده Sales Value باشد. می توانید فرمولی استفاده کنید که مقدار Sales Value را در %۱۰ ضرب کند اما اگر این درصد اشتباه یا نیاز به تغییر داشته باشد چه می کنید؟ یعنی باید دوباره فرمول ها را در هر سلول تغییر دهید و این خیلی طول می کشه و مقرون به صرفه نیست.
به جای آن می توانید یک سلول به این مقدار متغیر اختصاص دهید و از آدرس مطلق برای اشاره به این سلول استفاده کنید. برای انجام این کار مثلا اگر می خواهید مقدار را به ۲۰ درصد تغییر دهید و ببینید که اعداد چگونه تحت تأثیر قرار می گیرند، فقط کافیه مقدار سلول (سلول C1) را از %۱۰ به روی %۲۰ تنظیم کنید. در هر زمانی می توانید آن را ویرایش کنید و مقدار سلول های محدوده Commission Value هم به طور خودکار محاسبه خواهند شد:
در تصویر بالا مقدار سلول C1 روی ٪۱۰ تنظیم شده است. در ستون Commission Value از جدول (ستون E) اولین فرمول B4*C1= در سلول E4 نوشته شده است.
معنی اصلی فرمول برای اکسل یعنی: سلول ۳ ستون به سمت چپ (B4) را در سلول ۲ ستون به سمت چپ و ۳ سطر به سمت بالا (C1) ضرب کند. اگر فرمول را به همین صورت تا سلول E10 بکشیم، مقادیر محاسبه شده در تصویر به دست می آیند:
همانطور که می بینید فرمول در سلول E4 درست است اما وقتی که فرمول را به سمت پایین بکشید، اشتباه پیش می رود. برای فهمیدن مشکل باید به سلول E10 نگاه کنید، فرمول B10 * C7= را نشان می دهد. این فرمول درست نیست، مقدار B10 درسته اما C7 مقدار ۱۰% مورد نظر نیست.
چه اشتباهی انجام دادیم؟
فراموش کردیم آدرس مطلق را تنظیم کنیم.
اکسل کار خود را به درستی انجام داده است چرا که اکسل فکر می کند که باید سلول ۳ ستون به سمت چپ را در سلول ۲ ستون به سمت چپ و ۳ سطر به سمت بالا ضرب کند … اگر روی جدول تمرکز کنید، متوجه خواهید شد که اکسل اشتباهی انجام نداده بلکه اشتباه از ما است.
کاری که باید انجام دهید اینه که فرمول را به صورتی بنویسید که سلول ۳ ستون به سمت چپ در سلول C1 ضرب شود و سلول C1 همان مقداری است که ممکنه تغییر کند. فرمول را به شکل زیر تغییر دهید:
در تصویر بالا می توانید فرمول در سلول E4 را ببینید:
= B4 * $C$1
گفتیم که علامت دلار قبل از ستون و قبل از مکان سلول به معنای ایجاد یک سلول خاص یک نقطه ثابت است. با نوشتن علامت دلار قبل از حرف ستون C و قبل از سطر شماره ۱، سلول C1 تبدیل به یک نقطه ثابت می شود و مفهوم فرمول نیز تغییر می کند: سلول ۳ ستون به سمت چپ در مقدار سلول C1 ضرب می شود. حالا آن را در ستون E در جدول به سمت پایین بکشید تا نتیجه را مشاهده کنید:
این فرمول مطابق با هدف موردنظر کار می کند. یک مرور دوباره در سلول E10 نشان می دهد که فرمول برابر است با ضرب B10 (سلول ۳ ستون به سمت چپ) در C1، این فوق العاده است!
نکاتی برای استفاده از علامت دلار در فرمول اکسل
برای استفاده از آدرس های مطلق در فرمول اکسل می توانید علامت دلار را در مکان سلول به صورت دستی وارد کنید و یا اینکه می توانید با استفاده از کلید میانبر F4 ، به راحتی سه گزینه مرتبط با آدرس مطلق را تنظیم و انتخاب کنید.
برای استفاده از کلید میانبر اطمینان حاصل کنید که مکان نما به درستی در سمت چپ آدرس سلول قرار دارد. یعنی اگر می خواهید در فرمول ۵۰ * A1= آدرس مطلق اضافه کنید پس مطمئن شوید که مکان نما در سمت چپ حرف A چشمک می زند سپس کلید F4 را بزنید. اگر یک بار کلید F4 را بزنید، آن را به یک سلول ثابت تبدیل می کند و اگر دوبار بزنید، فقط مکان سطر ثابت می شود و در نهایت اگر می خواهید فقط ستون ثابت شود، یک بار دیگر F4 را فشار دهید.
خلاصه
امیدوارم این مقاله به شما کمک کرده باشد تا بتوانید به راحتی از علامت دلار در فرمول اکسل استفاده کنید. بسیاری از افراد مبتدی در اکسل از این مبحث رد می شوند اما این یک تکنیک اساسی در فرآیند یادگیری و توسعه مهارت های اکسل می باشد.
دانلود فیلم های آموزش صفر تا صد اکسل +جزوه
