تحلیل داده ها با ابزار Goal Seek اکسل

goal seek اکسل

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

مثال ساده زیر رو در نظر بگیرید. فرض کنید که شرکتی 300 کارمند داره و به هر کدوم از اونها ماهیانه 5 میلیون ریال حقوق میده. هزینه مواد اولیه به ازای هر کالا برابر 60 هزار ریال هست و قیمت فروش محصول در بازار هم 80 هزار ریال هست. تعداد تولید  ماهانه محصول شرکت در حال حاضر 100 هزار واحد در هر ماه هست و تمام محصولات تولید شده در بازار فروخته خواهند شد. حالا فرض کنیم که هزینه تمام شده هر واحد کالا و سود شرکت از روابط ساده زیر بدست میاد:

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

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

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

goal seek اکسل

 

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

goal seek اکسل

حالا اگر تنها متغیر قابل تعیین توسط شرکت، افزایش تولید ماهانه باشه و بخواهیم ببینیم باید چه مقدار تولید ماهانه شرکت رو افزایش بدیم تا با وجود افزایش حقوق پرسنل سود شرکت مانند حالت قبل برابر 500 میلیون ریال باشه  میتونیم از ابزار Goal Seek اکسل استفاده کنیم. برای اینکار مطابق شکل زیر از تب دیتا گزینه What-If Analysis رو انتخاب میکنیم و در منوی باز شده گزینه Goal Seek رو کلیک میکنیم.

goal seek اکسل

 

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

goal seek اکسل

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

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

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

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

goal seek اکسل

 

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

امیدوارم این مطلب براتون مفید بوده باشه.

 دانلود فایل نمونه ابزار Goal Seek اکسل

26 Comments on “تحلیل داده ها با ابزار Goal Seek اکسل”

  1. مجيد قاسمي

    سلام
    واقعا بابت مطلب خوب و فوق العاده تون ممنون ، ويژگي توضيح ساده و مثال كاربرديش واقعا متمايزش كرده ، يك دنيا ممنون

  2. نوید رضا جهان وش

    سلام
    یه سوال:
    اگه توی یه فایل که نام تکراری داریم بخواهیم که دو نام تکراری را پیدا کرده و سلولهای بعدی نامهای تکراری را باهم جمع بکنه، چی کار باید بکنیم؟
    مثلا در ستون A دو نام مشابه داریم که در ستون B دو عدد مختلف دارند و میخواهیم نتیجه آخر یک نام بماند و جمع B

  3. pourya

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

  4. mahyar.sh

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

  5. pouria4papo

    سلام . مطلب مفیدی بود. یه سوال، حالا اگه دو یا چند پارامتر قرار باشه تغییر کنه تکلیف چیه؟ اگه ممکنه مثل این مطلب با یه مثال ساده توضیح بدین ممنون.

  6. baran jafari

    سلام
    چه طور میتوان کاری کرد که با تغییر مداوم عدد یک سلول مد نظر، goal seek اتوماتیک فعال شده و محاسبات انجام شود ؟

  7. فرهمند

    سلام، بنده در سرچ راه‌حل يه مسأله در اكسل به سايت خوب شما رسيدم، لطفاً در خصوص مطلب ذيل راهنمائي بفرمائيد اگر ممكن هست.
    تابع ذيل:( y=F(x,z,t در يك فرآيند فرمول‌دهي پيچيده تعريف شده بطوريكه در ازاي هر كدام از متغيرها در سلول مربوطه ، y همزمان بدست مياد. حال اگر بخواهيم در جدولي ديگر در رديفهاي مستقل براي x1,z1,t1 , x2,z2,t2 و … y را بدست بياريم، به چه شكلي عمل شود؟
    به عبارتي جدول A تابع است و جدول B وروديها و خروجيهاي تابع بدون اينكه در تك‌تك خروجيها تابع A درج شود./سپاسگزارم

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *