آموزش فرمول نویسی حرفه ای در نرم افزار اکسل – جلسه دوم

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

در ادامه ی این مطلب می خواهیم سری دوم از آموزش فرمول نویسی حرفه ای را در نرم افزار اکسل را همراه با نکات و مثال ارائه کنیم، پس با آموزشی کاربردی در اکسل با کدنویسی به زبان ساده همراه باشید.

آموزش فرمول نویسی حرفه ای اکسل

دلایل اهمیت آدرس دهی

یادگیری و آموزش فرمول نویسی حرفه ای اصول و قوانینی داره که یکی از مهم ترین موضوعات، بحث مطلق/نسبی (Absolute/Relative) بودن آدرس محدوده هاست. این موضوع وقتی مطرح میشه که بخوایم فرمولی که نوشتیم رو Drag کنیم.

اگر به این مسئله تسلط عالی نداشته باشیم، هیچ وقت نمیتونیم به فرمولی که می نویسیم و درگ می کنیم اعتماد کنیم و مجبوریم تک تک نتایج رو بررسی کنیم که این کار در مقیاس های بزرگ بسیار وقتگیر خواهد بود.

برای درک بهتر این موضوع (آدرس دهی) اول باید با نحوه ارجاع به یک سلول و یا فراخوانی یک سلول در اکسل آشنا بشیم که به دو روش صورت میگیره:

• مدل A1
• مدل R1C1 یا (Row1Column1~ ردیف۱ستون۱)

هر دوی این آدرس ها به سل A1 اشاره می کنند که پیش فرض اکسل، همون حالت اول یعنی A1 است. فقط یک نکته اینکه درصورتی که بخواهیم از حالت دوم استفاده کنیم، باید تیک گزینه R1C1 reference style در شکل ۱ را بزنیم تا سرستون های اکسل از A, B, C… به ۱,۲,۳… و در نتیجه نوع آدرس دهی از A1 به R1C1 تغییرکند.

شکل ۱- آموزش فرمول نویسی آدرس دهی – تغییر نوع آدرس دهی در اکسل

مثال : حالا با حل یک مثال، بحث نسبی و مطلق بودن آدرس در فرمول نویسی رو شرح میدم :

محدوده ای از اعداد داریم که میخواهیم همه رو در یک سل به خصوص ضرب کنیم. طبق شکل ۱، در سل C2 می نویسیم A2*C1= و درگ می کنیم. مسئله ای که پیش میاد این هست که همه سل ها در حین درگ کردن، با هم حرکت میکنند (مطابق شکل ۱).

شکل ۲- آموزش فرمول نویسی آدرس دهی – درگ کردن فرمول (نتیجه غلط)

در حالیکه ما میخواهیم سل C1 ثابت باشه و فقط سل های ستون A تغییر کنند. یعنی چیزی مطابق با شکل ۲

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

شکل ۳- آموزش فرمول نویسی آدرس دهی – فرمول مد نظر بعد از درگ کردن

درگ کردن در اکسل به دو صورت هست. در لحظه یا در ستون حرکت میکنیم (به سمت بالا و پایین) و یا در سطر(به سمت چپ و راست).

وقتی در ستون حرکت میکنیم (بالا یا پایین) فقط ردیف سل حرکت کننده تغییر می کند و وقتی در ردیف حرکت میکنیم (چپ یا راست) فقط ستون سل حرکت کننده تغییر میکند.

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

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

• اول باید ببینیم در کدام مسیر داریم حرکت میکنیم (سطر یا ستون) و چه چیزی در حال تغییر است (شماره ردیف یا نام ستون)؟
• بعد تصمیم بگیریم که آیا میخواهیم تغییر کند یا ثابت بماند؟

وقتی میخواهیم سطر یا ستون رو فیکس کنیم، باید یک علامت $ پشت شماره ردیف یا نام ستون بذاریم. با این تفاسیر، چهار حالت برای آدرس دهی داریم:

حالا برگردیم به همان سوال اول. میخواهیم $ را برای فرمول A2*C1= تنظیم کنیم که با درگ کردن، بدرستی عمل کند. چون در ستون داریم حرکت میکنیم، پس فقط شماره ردیف تغییرمیکنه. حالاما باید تصمیم بگیریم کدوم شماره ردیف تغییر کنده و کدوم ثابت بمونه.

چون میخواهیم سل C1 ثابت بمونه و در همه سل ها تکرار بشه (شکل۳)، پس مطابق شکل ۴، $ را پشت ۱ در C1 میگذاریم. اما میخواهیم A2 درسل های بعدی به A3 و A4 و… تغییر کند.پس $ نیازی ندارد.

شکل ۴- آموزش فرمول نویسی آدرس دهی – درگ کردن فرمول (نتیجه درست)

نکته : علامت $ را هم میتونیم مستقیما تایپ کنیم. هم اینکه از کلید F4 استفاد کنیم. وقتی روی آدرس مورد نظر قرار بگیریم، با هر بار F4 زدن، یکی از ۴ حالت آدرس دهی ظاهر میشه.

مثال : میخواهیم یک جدول ضرب ایجاد کنیم. فرمول خیلی ساده هست، A2*B1=  حالا باید طوری آدرس دهی کنیم که با انتقال آن به کل جدول، محاسبات به درستی انجام شود. به شکل ۵ دقت کنید. علامت $ پشت نام A و ردیف ۱ قرار گرفته. چرا؟

شکل ۵-آموزش فرمول نویسی آدرس دهی در جدول ضرب

A2*B1= رو در نظر بگیرید. وقتی در ستون حرکت میکنیم، همواره میخواهیم اعداد موجود در ردیف ۱ در بقیه اعداد که در ستون A هستن، ضرب بشن. پس ردیف ۱ را فیکس میکنیم. وقتی هم که در ردیف حرکت میکنیم، میخواهیم عدد موجود در ستون A در بقیه اعداد ردیف ۱ ضرب بشن.

پس $ ها رو به این صورت اعمال میکنیم A2*B$1ا$= بعبارت کلی، هر جای این جدول ضرب هستیم، میخواهیم عددی در ردیف ۱ ضرب در عددی در ستون A بشه. پس ردیف ۱ و ستون A در فرمول باید فیکس بشه.

مبحث آدرس دهی بسیار بسیار مهمه. کسی که میخواد فرمول نویس حرفه ای بشه، حتما باید به این موضوع تسلط کافی داشته باشه. پس علاوه بر تمرین و تکرار دو مثال تشریح شده، حتما مثال های مختلفی رو امتحان کنید تا کاملا ملکه ذهنتون بشه.

هر موقع بحث درگ کردن و انتقال فرمول پیش میاد، اول از همه برید سراغ $ و آدرس دهی رو تنظیم کنید بعد شروع کنید به انتقال فرمول.

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