Goal Seek در اکسل

Goal Seek در اکسل

یکی از قابلی تهای نرم افزار اکسل تحلیل حساسیت و تصمیم گیری در شرایط احتمالی هست. این قابلیت اکسل باعث میشه ما بتونیم با تغییر متغیرهای موردنظرمون شرایط رو تحلیل کنیم و نتیجه تغییر یک متغیر رو روی سایر متغیرهای سیستم مشاهده کنیم. ابزار Goal Seek اکسل یکی از این قابلیت‌ها هست که در این مطلب میخوایم باهاش آشنا بشیم.

مثال Goal Seek در اکسل

فرض کنید که شرکتی 300 کارمند داره و به هر کدوم از اونها ماهیانه 5 میلیون ریال حقوق میده. هزینه مواد اولیه به ازای هر کالا برابر 60 هزار ریال هست و قیمت فروش محصول در بازار هم 80 هزار ریال هست.

تعداد تولید  ماهانه محصول شرکت در حال حاضر 100 هزار واحد در هر ماه هست و تمام محصولات تولید شده در بازار فروخته خواهند شد. حالا فرض کنیم که هزینه تمام شده هر واحد کالا و سود شرکت از روابط ساده زیر بدست میاد:

 قیمت تمام شده هر کالا = هزینه مواد اولیه به ازای هر کالا + ( ( تعداد پرسنل *  حقوق ماهانه) / تعداد تولید ماهانه)

سود ماهانه شرکت = تعداد تولید ماهانه * ( قیمت فروش هر کالا – قیمت تمام شده هر کالا)

اطلاعات بالا در جدول زیر نمایش داده شده

حالا اگر بنا به دلایلی حقوق پرداختی پرسنل افزایش پیدا کنه، طبیعتا سود ماهانه شرکت کاهش پیدا خواهد کرد. فرض کنید در سال جدید حقوق کارکنان از 5 میلیون ریال به 6 میلیون و 200 هزار ریال افزایش پیدا کرده باشه و بقیه متغیرها ثابت باشه. بنابراین سود ماهانه شرکت به شکل زیر در میاد:

Goal Seek در اکسل Goal Seek در اکسل Goal Seek در اکسل Goal Seek در اکسل

برای اینکار مطابق شکل زیر از تب دیتا گزینه What-If Analysis رو انتخاب میکنیم و در منوی باز شده گزینه Goal Seek رو کلیک میکنیم.

حالا پنجره Goal Seek باز میشه و ما میتونیم مساله خودمون رو در اون تعریف کنیم. گزینه های این پنجره شامل موارد زیر هستند:

Set cell:  در این بخش متغیر وابسته خودمون رو که به دنبال رسیدن به مقدار مشخصی برای اون هستیم  تعریف میکنیم. در مثال ما این بخش سلول B7 هست که سود ماهانه شرکت رو نشون میده.

To value: در این بخش مقدار مورد نظر رو برای متغیر وابسته تعریف میکنیم. از اونجایی که هدف ما رسیدن به سود ماهانه دوره قبل یعنی 500 میلیون ریال هست مقدار این بخش رو برابر 500 میلیون قرار میدیم.

By changing cell: در این بخش متغیری که میخواهیم با تغییر اون مقدار متغیر وابسته رو به عدد موردنظر برسونیم تعریف میکنیم. در این مثال چون ما میخوایم با تغییر میزان تولید ماهانه سود ماهانه رو افزایش بدیم مقدار این گزینه رو برابر سلول B6 قرار میدیم.

بعد از اینکه مساله خودمون رو مطابق توضیحات بالا تعریف کردیم، اوکی رو کلیک میکنیم و ابزار Goal Seek اکسل با انجام محاسباتی، مقدار تولید ماهانه ای که با احتساب افزایش حقوق پرسنل سود ماهانه شرکت رو 500 میلیون ریال میکنه بدست میاره.

همونطور که در تصویر بالا میبینید، اگر حقوق پرسنل افزایش پیدا کنه و مابقی متغیرها ثابت باشه، شرکت میتونه با افزایش تولید و فروش ماهانه از 100 هزار کالا به 118 هزار  کالا، سودی معادل دوره قبل یعنی 500 میلیون ریال  داشته باشه.

اگر قصد دارید مهارت خود را در اکسل به سطح حرفه‌ای برسانید، یادگیری توابع و ابزارهای پرکاربرد اهمیت زیادی دارد. برای جستجوی اطلاعات در جداول، تابع VLOOKUP اکسل یکی از پرکاربردترین گزینه‌ها است، هرچند در داده‌های پیچیده می‌توان از ترکیب دو تابع INDEX MATCH نیز استفاده کرد. برای انجام محاسبات شرطی، آشنایی با تابع SUMIF اکسل، تابع COUNTIF اکسل و تابع AVERAGEIF اکسل ضروری است و به شما کمک می‌کند تا مجموع، تعداد و میانگین داده‌ها را بر اساس شرایط دلخواه محاسبه کنید.

علاوه بر توابع، ابزارهای تحلیلی اکسل نیز نقش مهمی در تصمیم‌گیری و آماده‌سازی داده‌ها دارند. با استفاده از Pivot Table در اکسل می‌توانید حجم زیادی از داده‌ها را به‌سرعت خلاصه و تحلیل کنید و با Data Table در اکسل سناریوهای مختلف را بررسی نمایید. همچنین Goal Seek در اکسل برای یافتن مقدار هدف بسیار کاربردی است. اگر با داده‌های متنی سروکار دارید، آموزش توابع MID و FIND و همچنین روش حذف 2 کاراکتراول با شرط در اکسل می‌تواند سرعت و دقت شما را در پردازش اطلاعات متنی به شکل محسوسی افزایش دهد.