فرمول نویسی در اکسل (تابع های پرکاربرد زبانه formulas)
زبانه formulas اکسل از پرکاربردترین زبانه های اکسل و نقطه قدرت این نرم افزار است. در این زبانه ، چند صد تابع و فرمول آماده در زمینه های مختلف نظیر توابع مالی، مهندسی، آماری، توابع جستوجو، توابع تاریخ و زمان و … وجود دارد. در تصویر زیر این زبانه و بخشها و ابزارهای درون آن را مشاهده میکنید. و قصد داریم به مرور کلی فرمول نویسی در اکسل بپردازیم.
نسخه مایکروسافت آفیس اکسل ۲۰۱۳
نسخه مایکروسافت آفیس اکسل ۲۰۱۶
بخش FUNCTION LIBRARY
در این بخش تعداد زیادی تابع و فرمول از پیش آماده برای استفاده کاربران گنجانده شده است. بدلیل تعداد زیاد این توابع و فرمولها، تنها به توضیح چند تابع پرکاربرد اشاره خواهیم کرد.
همچنین در این جا به قسمت هایی کمی اشاره خواهیم کرد ، برای آشنایی بیشتر و مثال های جامع فایل PDF را در پایان آموزش دانلود و مطالعه کنید .
فرمول نویسی در اکسل با توابع پرکاربرد
تابع کاربردی و پرکاربردی MAX در اکسل
این تابع در بخش توابع آماری اکسل وجود دارد. اما برای دسترسی سریعتر در بخش AUTOSUM نیز نمایش داده میشوند. این تابع بزرگترین مقدار موجود در یک محدوده که مشخص میکنید را نمایش میدهد. فرض کنید در کاربرگ اکسل همانند تصویر زیر میزان فروش ماه های مختلف یک سال وجود داشته باشد. برای تعیین و نمایش پرفروشترین ماه در سلول C1 از تابع MAX استفاده میکنیم.
برای این کار ابتدا سلول C1 را فعال میکنیم و سپس همانند شکل زیر از زبانه FORMULAS تابع MAX را فرامیخوانیم.
در فرمول باز شده این تابع، همانند تصویر زیر محدوده فروش ماه های سال (B1 تا B12) را با ماوس انتخاب میکنیم و در پایان روی گزینه OK کلیک میکنیم.
در تصویر زیر مشاهده میکنید بیشترین فروش ماهانه در این سال نمایش داده شده است.
تابع MIN عکس این تابع عمل میکند.
تابع IF در اکسل
این تابع در بخش توابع LOGICAL زبانه FORMULAS قرار دارد. این تابع یک تابع منطقی است که یک یا چند شرط را بررسی میکند، اگر همه شرطها برقرار بود، عبارتی را که مشخص شده و اگر حداقل یکی از شرط ها برقرار نبود عبارت مشخص شده دیگری را نمایش میدهد. برای دسترسی به این تابع همانند تصویر زیر عمل کنید.
مثال از تابع IF در اکسل
نمره یک دانشجو در محیط اکسل بصورت زیر در دسترس است.
فروش ماهیانه یک فروشگاه در یک سال بصورت زیر است. ابتدا در سلول D1 میانگین فروش ماهانه و سپس با استفاده از تابع IF در ستون C در مقابل فروش هر ماه فرمولی تهیه کنید که اگر فروش هر ماه کمتر از میانگین باشد، عبارت “ضعیف” و در غیر اینصورت عبارت “مناسب” را نمایش دهد.
برای محاسبه میانگین در سلول D1 از بخش AUTO SUM فرمول AVERAGE را انتخاب میکنیم.
زمانیکه فرمول میانگین بصورت زیر نمایان شده، با استفاده از ماوس محدوده مورد نظر (B1 تا B12) را با ماوس انتخاب کرده و در پایان کلید ENTER را بفشارید.
میانگین فروش ماهانه در این سال در سلول D1 نمایش داده شده است.
حالا در سلول C1 تابع IF را فرامیخوانیم. در کادر LOGICAL_TEST باید شرطی نوشته شود به این صورت که اگر عدد موجود در سلول B1 کوچکتر از عدد موجود در سلول D1 باشد که این شرط بصورت B1<D1 نوشته میشود. دقت کنید بجای نوشتن عدد میانگین، آدرس سلول آن را در تابع قرار میدهیم. اما نکته حائز اهمیت در این مثال این است که چون قرار است فرمول موجود در سلول C1 برای سایر ماهها نیز کپی شود، اکسل بصورت خودکار فرمولها را تطبیق میدهد و چون آدرس سلول مبلغ میانگین (D1) برای همه ماه ها باید ثابت باشد، بعد از نوشتن آدرس سلول D1 (یا کلیک بر آن)، یک بار کلید F4 را فشار میدهیم تا این سلول در تطبیق فرمول ها ثابت در نظر گرفته شود.
حالا در کادر VALUE_IF_TRUE مقداری را که در صورت صحت شرط باید نمایش داده شود را وارد کنید. در این مثال اگر شرط برقرار باشد، یعنی فروش هر ماه کوچکتر از D1 باشد، باید عبارت “ضعیف” نمایش داده شود. بنابراین در این کادر عبارت “ضعیف” را مینویسیم.
در کادر VALUE_IF_FALSE مقداری را که در صورت عدم صحت شرط باید نمایش داده شود را وارد کنید. در این مثال اگر شرط برقرار نباشد، یعنی فروش ماهانه بزرگتر یا مساوی با D1 باشد، باید عبارت “مناسب” نمایش داده شود. بنابراین در این کادر عبارت “مناسب” را مینویسیم.
بعد از کلیک روی گزینه OK حاصل بصورت زیر خواهد بود.
حالا با یک دبل کلیک روی مربع کوچک گوشه کادر سلول C1 فرمول این سلول برای سایر سلولها کپی میشود و شکل نهایی جدول بدست میآید.
- توصیه می شود : استفاده از فرمول SUMIFS در اکسل
تابع TRANSPOSE اکسل
این تابع داده های افقی را به عمودی و عمودی را به افقی تبدیل میکند. فرض کنید اطلاعات زیر در اکسل در دسترس است.
اگر بخواهیم این دادهها را بصورت ستونی داشه باشیم از تابع TRANSPOSE استفاده میکنیم. برای این کار ابتدا محدوده مورد نظر برای ایجاد جدول جدید را بصورت زیر انتخاب میکنیم.
سپس در زبانه FORMULAS از بخش توابع LOOKUP & REFERENCE تابع TRANSPOSE را فرا می خوانیم.
تابع VLOOKUP در اکسل
این تابع در ستون اول یک محدوده یا جدول به جستوجو میپردازد و پس از یافتن مورد جستوجو، داده موجود در همان سطر اما در ستونهای دیگر جدول را نمایش میدهد.
- توصیه می شود : استفاده از فرمول VLOOKUP در اکسل
تابع DATE
این تابع برای درج تاریخ در سلولها و سایر توابع کاربرد دارد. تابع DATE در بخش توابع DATE & TIME قرار دارد. برای درج یک تاریخ میلادی در یک سلول ابتدا آن سلول را فعال کرده و سپس همانند تصویر زیر تابع DATE را فرامیخوانیم.
تابع TIME
این تابع برای درج زمان در یک سلول و یا تابع دیگر کاربرد دارد. اگر بخواهیم در سلول A1 ساعت ۱۲:۲۳:۱۴ را درج کنیم، کافیست همانند تصویر زیر تابع TIME را فراخوانی کنیم.
در کادر باز شده این تابع ساعت مدنظر را وارد کرده و روی گزینه OK کلیک میکنیم.
تابع کاربردی NOW اکسل
این تابع برای درج تاریخ و زمان لحظهای کاربرد دارد. با فراخوانی این فرمول، کادر آن بصورت زیر باز میشود که هیچ ورودی ندارد و فقط باید گزینه OK را انتخاب کرد.
پس از کلیک روی گزینه OK در سلول مورد نظر تاریخ و ثاعت همان لحظه ثبت میشود. این تابع از تاریخ و ساعت کامپیوتر شما استفاده میکند.
تابع COMBIN
این تابع که در بخش توابع MATH & TRIG قرار دارد برای محاسبه حالات ترکیب ریاضی کاربرد دارد.
فرض کنید میخواهید از بین ۸ نفر گروههای ۵ نفره تشکیل دهید. به چند حالت میتوانید گروه ۵ نفره تشکیل دهید؟
برای محاسبه ترکیب، تابع COMBIN را فرامیخوانیم.
تابع FACT (تابع فاکتوریل اکسل)
این تابع برای محاسبه مقدار فاکتوریل یک عدد کاربرد دارد. برای محاسبه فاکتوریل عدد ۸ در سلول A1 کافیست در کادر NUMBER این تابع عدد ۸ را قرار داده و روی گزینه OK کلیک کید.
تابع کاربردی MDETERM اکسل
این تابع ریاضی برای محاسبه دترمینان یک ماتریس کاربرد دارد. شرط محاسبه دترمینان تساوی تعداد ستون و سطر ماتریس باشد. در واقع دترمینان تنها در ماتریسهای مربع قابل محاسبه است.
تابع MINVERSE
این تابع برای محاسبه و تشکیل ماتریس معکوس یک ماتریس بکار میرود.
تابع MMULT
این تابع برای ضرب دو تابع در هم بکار میرود. شرایط ضرب دو تابع به این صورت است که تعداد ستون ماتریس اول باید با تعداد سطر ماتریس دوم برابر باشد. تعداد سطر ماتریس حاصلضرب برابر با تعداد سطر ماتریس اول و تعداد ستون آن برابر با تعداد ستون ماتریس دوم خواهد بود.
تابع MUNIT
این تابع برای تشکیل ماتریس یکه در ابعاد مختلف کاربرد دارد.
تابع اکسل ROUND
این تابع برای رند کردن اعداد کاربرد دارد. برای مثال فرض کنید عدد ۵۵۳۴.۶۵۲ را داریم. میخواهیم این عدد را تا دو رقم اعشار رند کنیم. برای این کار تابع ROUND را فرامیخوانیم و در کادر NUMBER این عدد را مینویسیم. در کادر NUM_DIGITS ، اگر عدد مثبت وارد شود، تعداد ارقام اعشار را مشخص میکند و اگر عدد منفی نوشته شود اعداد قبل از اعشار را رند میکند (عدد ۱- آخرین رقم قبل از اعشار عدد را صفر میکند، عدد ۲- دو عدد آخر عدد را صفر میکند و …).
تابع DB
این تابع که در بخش فرمولهای مالی اکسل قرار دارد برای محاسبه استهلاک یک دارایی به روش نزولی بکار میرود.
تابع DDB
این تابع برای محاسبه استهلاک به روش نزولی مضاعف بکار میرود. ورودیهای این تابع تقریبا همانند تابع DB است، با این تفاوت که این تابع کادری به نام FACTOR برای تعیین ضریب مضاعف شدن دارد.
تابع SYD
این تابع برای محاسبه استهلاک به روش مجموع سنوات بکار میرود.
تابع اکسل FV
این تابع برای محاسبه ارزش آتی یک سری اقساط برابر و با فاصله زمانی یکسان کاربرد دارد. برای مثال فرض کنید در پایان هر ماه به مدت ۵ سال مبلغ ۱۱۱۰۱۱۱ ریال را در حسابی در بانک پسانداز میکنیم. بانگ به ازای هر سال ۲۴ درصد سود به حساب ما اضافه میکند. میخواهیم بدانیم در پایان سال پنجم چه مبلغی در حساب ما وجود خواهد داشت.
برای حل این مسئله تابع FV را فرا می خوانیم.
تابع PV
این تابع برای محاسبه ارزش حال یک سری پرداخت و دریافت در آینده که مبلغ همه آنها برابر و با فاصله زمانی یکسان رخ میدهد بکار میرود. فرض کنید برای خرید اتومبیل بصورت قسطی قرار شده است به مدت ۶ سال و در پایان هر ماه مبلغ ۳۰۱۱۱۰۱۱۱ ریال پرداخت کنید. میخواهیم بدانیم اگر نرخ بهره سالانه ۱۸ درصد باشد، ارزش حال پرداختهای اقساط اتومبیل چه مقدار خواهد بود.
برای این کار تابع PV را فرامیخوانیم.
تابع PMT
این تابع برای محاسبه مبلغ اقساط وامها کاربرد دارد. برای مثال فرض کنید وامی به مبلغ ۱۱۰۱۱۱۰۱۱۱ ریال به سررسید ۵ ساله که در پایان هر سال یک قسط پرداخت میشود را با نرخ بهره ۲۱ درصد سالانه دریافت کرده اید. برای محاسبه مبلغ هر قسط، از تابع PMT استفاده میکنیم.
توابع IPMT و PPMT
توابع PPMT و IPMT بترتیب برای محاسبه اصل و فرع هر قسط پرداختی یا دریافتی کاربرد دارند.
- بیشتر بخوانید : محاسبه مبلغ قسط وام در اکسل با تابع PMT
تابع NPV اکسل
این تابع مبلغ خالص ارزش فعلی یک پروژه را محاسبه میکند.
تابع XNPV
برخلاف تابع NPV که برای محاسبه خالص ارزش روز در مواقعی که فاطله دریافتها و پرداختها برابر است بکار میرود، تابع XNPV برای محاسبه خالص ارزش روز سرمایه گذاری ها در مواقعی که فواصل بین دریافت ها و پرداخت ها متغیر است کاربرد دارد. ورودیهای این تابع همانند تابع NPV هستند با این تفاوت که در این تابع باید تاریخ دریافت ها و پرداخت ها نیز وارد شود.
تابع اکسل IRR
این تابع نرخ بازدهای را نشان میدهد که در آن نرخ، مقدار خالص ارزش روز یک سرمایهگذاری صفر میشود (نرخ بازده داخلی).
تابع XIRR
این تابع نرخ بازده داخلی یک پروژه را در صورتی که فواصل زمانی بین تاریخهای دریافت و پرداخت برابر نباشد، بدست میآورد. ورودی های این تابع همانند تابع IRR است بعلاوه اینکه در این تابع باید تاریخ ها نیز وارد شوند.
تابع RATE
این تابع برای محاسبه نرخ بهره یک وام با تعداد اقساط و مبلغ قسط مشخص را محاسبه میکند. درصد بهره محاسبه شده توسط این تابع برای هر دوره قسط است. برای مثال اگر در سال ۱۲ قسط داریم و حاصل این تابع ۲ درصد شده باشد، نرخ بهره سالانه ۲۴ درصد (۱۲*۲) است.
تابع COUNT اکسل
با کمک این تابع میتوان تعداد سلولهای دارای عدد (عددی) در یک منطقه از سلولها را شمرد.
تابع COUNTIF
تفاوتی که این تابع با تابع COUNT دارد این است که این تابع میتواند عمل شمارش را با شرطی که شما به آن میدهید انجام دهد. این تابع دارای دو کادر ورودی اطلاعات به نام RANGE (آدرس منطقه مورد نظر برای شمارش) و CRITERIA (شرط شمارش) است. برای مثال اگر بخواهید در یک منطقه از سلول ها اعداد بزرگتر از ۵ را بشمارید، باید در کادر CRITERIA عبارت ” >5 ” را وارد کنید.
بخش DEFINED NAMES
این بخش دومین بخش از زبانه FORMULAS است. در این بخش ابزارهایی برای نامگذاری سلولها و محدوده ها وجود دارد. در تصویر زیر این بخش و ابزارهای درون آن را مشاهده میکنید.
ابزار DEFINE NAME
اگر بخواهیم برای یک منطقه از سلولها نامی را انتخاب و در فرمولها از آن نام استفاده کنیم، از ابزار DEFINE NAME بهره میگیریم. فرض کنید میخواهید منطقه سلولهای A1 تا D4 را به نام ” AAA ” نامگذاری کنید. برای این کار روی ابزار DEFINE NAME کلیک کرده و از لیست باز شده گزینه DEFINE NAME را انتخاب میکنیم.
در کادر باز شده این ابزار همانند تصویر زیر در قسمت NAME عبارت ” AAA ” را وارد میکنیم.
سپس در قسمت REFERS TO با ماوس منطقه مورد نظر را انتخاب میکنیم.
در نهایت گزینه OK را انتخاب میکنیم.
ابزار NAME MANAGER
این ابزار برای مدیریت نامگذاری سلولها بکار میرود. با کلیک بر ابزار NAME MANAGER پنجره این ابزار بصورت زیر باز میشود.
در این کادر تمام محدوده های نامگذاری شده مشاهده میشوند. همچنین گزینه هایی برای حذف و ویرایش آنها وجود دارد.
بخش FORMULA AUDITING
این بخش برای ابزارهایی را برای مشاهده فرمولها در اختیار ما قرار میدهد. فرض کنید فرمول محاسبه مبلغ قسط وام و اصل و فرع قسط اول را بصورت زیر در اکسل داشته باشید.
برای اینکه اکسل به ما نشان دهد در فرمول سلول E1 از چه سلولهای دیگری استفاده شده، گزینه TRACE PRECEDENTS را انتخاب میکنیم.
مشاهده میشود که برای محاسبه مبلغ هر قسط در سلول E1 از سلولهای B1 ، B2 و B3 استفاده شده است.
برای پاک کردن این خطوط از گزینه REMOVE ARROWS استفاده میکنیم.
حال برای اینکه ببینیم سلول E3 در کدام سلولها بکار رفته است، ابتدا روی آن کلیک میکنیم و سپس گزینه TRACE DEPENDENTS استفاده میکنیم.
ابزار SHOW FORMULAS
این ابزار برای نمایش فرمول درون سلولها کاربرد دارد. فرض کنید در مثال قبل میخواهیم فرمول درون سلولها نمایش داده شود. برای این کار روی گزینه SHOW FORMULAS کلیک میکنیم.
برای لغو این حالت کافیست دوباره روی همین گزینه کلیک کنیم.
فرمول نویسی در اکسل با استفاده از توابع بالا ساده تر از هروقت دیگری است و تنها نیاز به کمی کاوش و جستجو دارد.
دیدگاه ها