Scenario Manager در اکسل برای تحلیل حساسیت (What-If Analysis)
این مطلب سومین مطلب از مجموعه‌ی سه قسمتی در زمینه‌ی تجزیه و تحلیل داده‌ها در اکسل است. در این بخش نحوه‌ی استفاده از Scenario Manager (مدیر سناریو) در اکسل را به شما نشان خواهیم داد.
| سایر مقالات این مجموعه: |
Scenario Manager در اکسل می‌تواند ابزار مورد نظر شما باشد هنگامی که چندین متغیر در اختیار دارید و می‌خواهید با تغییر این متغیرها، تأثیر آن را در نتیجه‌ی نهایی مشاهده کنید.
فرض کنید مجموعه داده‌ای مطابق شکل زیر دارید و می‌خواهید مقدار سود را محاسبه کنید:
ارزش سود به 3 متغیر وابسته است – مقدار فروش، قیمت هر واحد و هزینه‌ی متغیر برای واحد. در این‌جا فرمول ما برای محاسبه‌ی سود آمده است:
=B2*B3-B4-B5*B2
ایده این است ببینیم که وقتی این متغیرهای وابسته را تغییر می‌دهیم، نتیجه‌ی نهایی چگونه تغییر می‌کند.
همان‌طور که در 2 مقاله‌ی اول این سری نشان داده شده‌است، اگر فقط یک یا دو متغیر را تغییر دهید، می‌توانید یک جدول داده‌ی یک متغیره یا دو متغیر ایجاد کنید. اما اگر 3 یا بیشتر از 3 متغیر دارید که می‌تواند تغییر کند، Scenario Manager راهی است که باید پیش گرفته شود.
تنظیم Scenario Manager در اکسل
- بروید به تب Data > گروه Data Tools > سپس What-If Analysis > در نهایت Scenario Manager.
- در کادر گفتگوی Scenario Manager، بر روی Add کلیک کنید.
- در کادر گفتگوی Scenario Values، جزئیات زیر را پر کنید:
- Scenario name: نام سناریو را در این کادر بنویسید. برای مثال بالا 3 سناریو می‌خواهیم تعریف می‌کنیم. در این‌جا بدترین حالت را وارد می‌کنیم و نام آن را Worst Case می‌گذاریم.
- Changing cells: سلول‌هایی را که مقدار آن‌ها در هر سناریو تغییر می‌کند، انتخاب کنید در این‌جا $B$2,$B$3,$B$5 (می‌توانید با فشار دادن دکمه‌ی Ctrl و استفاده از کلیک چپ ماوس نیز آن‌ها را انتخاب کنید).
- Comment: هر توضیحی را که مایل هستید اضافه کنید. هم‌چنین می‌توانید این فیلد را خالی رها کنید.
- OK را کلیک کنید. این کار کادر محاوره‌ای Scenario Values را باز می‌کند.
- در کادر گفتگوی Scenario Values، مقادیر زیر را پر کنید (از آن‌جا که این بدترین سناریو است، مقادیر را مطابق آن وارد کنید).
- $B$2: 50
- $B$3: 30
- $B$4: 30
توجە: اگر برای سلول‌ها نام ایجاد کرده باشید (در مثال بالا برای سلول‌های B2، B3 و B5)، آن نام به جای آدرس سلول‌ها نمایش داده خواهدشد. برای اطلاعات بیشتر مطلب نامگذاری محدوده‌ها در اکسل را مطالعه کنید. - OK را کلیک کنید (اگر می‌خواهید سناریوی دیگری را اضافه کنید، روی Add کلیک کنید).
این کار سناریوی Worst Case را برای این مجموعه داده ایجاد می‌کند. به طور مشابه می‌توانید این مراحل را دنبال کنید و چندین سناریو ایجاد کنید (برای مثال، Worst Case (بدترین مورد)، Realistic (واقع‌بینانه)، Best Case (بهترین مورد)). در تصویر زیر تمام سناریوهای افزوده‌شده را در کادر Scenarios مشاهده می‌کنید.
پس از ایجاد تمام سناریوها، می‌توانید با دابل‌کلیک بر روی هر یک از سناریوها یا زدن دکمه‌ی Show، نتیجه‌ی هر یک از سناریوها را مشاهده کنید. به محض دابل‌کلیک، مقادیر بر اساس آن سناریو تغییر می‌کنند.
برای ویرایش یک سناریوی از قبل موجود روی دکمه‌ی Edit، و برای حذف آن بر روی دکمه‌ی Delete کلیک کنید. در صورتی که در سایر ورک‌شیت‌های سناریوهای دیگری کرده‌اید می‌توانید با استفاده از دکمه‌ی Merge تمام سناریوها را مشاهده کرده و در صورت لزوم ادغام کنید.
علاوه بر این، می‌توانید خلاصه‌ای از کلیه سناریوها را نیز ایجاد کنید.
ایجاد خلاصه‌ای از تمام سناریوها
- بر روی دکمه‌ی Summary در کادر گفتگوی Scenario Manager کلیک کنید.
- در کادر گفتگوی Scenario Summary، یکی از موارد Scenario Summary یا Scenario Pivot Table report را انتخاب کنید (این دو روش برای نمایش خلاصه هستند). هم‌چنین Result cells را تعیین کنید (سلولی که نتیجه‌ی خروجی این محاسبات را خواهید داشت؛ B6 در این مثال).
- OK را کلیک کنید فوراً یک تب جدید با خلاصه‌ی هر سه سناریو ایجاد می‌شود.
برای مشاهده دقیق‌تر نمونه‌هایی که در این آموزش مورد بحث قرار گرفته‌است، شما می‌توانید نمونه ورک‌بوک ما را دانلود کنید. از شما به خاطر خواندن این مطلب تشکر می‌کنیم و خوشحال خواهیم شد که دوباره شما را ببینیم!
| دانلود |
بسیار عالی بود
خیلی عالی توضیح دادین. حالا بایستی سناریو های مناسب دیتام رو بسازم و لذت ببرم. امیدوارم همه چی در فرمت مطالب ارایه شده شما پیش بره. متشکرم
تنک یو