ابزار تحلیل what-if اکسل

معرفی

اکسل تعداد زیادی ابزارهای قدرتمند مثل ویژگی “تحلیل what-if” برای انجام محاسبات ریاضی پیچیده و تجزیه و تحلیل اونها داره. این ویژگی بهتون کمک می‌کنه اطلاعات و داده‌هاتون رو آزمایش و تحلیل کنید، حتی زمانی که اطلاعات ناقص و کم هست. 

Goal Seek

زمانی که یک فرمول یا تابع در اکسل ایجاد می‌کنید، به این معنی هست که بخش‌های مختلف رو کنار هم می‌ذارید تا یک نتیجه خاصی رو محاسبه کنید. Goal Seek دقیقا مخالف این کار رو انجام میده، یعنی نتیجه دل‌خواه شما رو بررسی کرده و مقدار ورودی‌هایی که اون نتیجه رو بهتون ارائه دادن رو محاسبه می‌کنه. ما از چند مثال برای نشان دادن نحوه عملکرد اون استفاده می‌کنیم.

نحوه استفاده از Goal Seek (مثال ۱):

تصور کنید که در یک کلاس ثبت‌ نام کردید. در حال حاضر نمره شما ۶۵ هست و برای این که آزمون رو قبول بشید حداقل نمره ۷۰ رو لازم دارید. خوشبختانه یک تکلیف نهایی هم برای این آزمون در نظر گرفته شده که با کمک اون ممکنه بتونید میانگین نمرتون رو افزایش بدید. حالا می‌تونید از Goal Seek برای فهمیدن نمره لازم در تکلیف نهایی و قبولی در آزمون استفاده کنید.

در تصویر زیر می‌تونید ببینید که نمرات در چهار درس اول ۵۸ ، ۷۰ ، ۷۲ و ۶۰ هستند . با این که نمی‌دونیم نمره درس پنجم چقدر هست، اما می‌تونیم یک فرمول یا تابع که نمره نهایی رو محاسبه می‌کنه بنویسیم. در این حالت هر نمره وزن یکسانی با بقیه داره. بنابراین تنها کاری که باید انجام بدیم محاسبه میانگین کل پنج نمره از طریق تایپ AVERAGE(B2:B6) =  هست. وقتی که از Goal Seek استفاده می‌کنیم، سلول B6 حداقل نمره برای قبولی رو بهتون نشان میده.

۱- سلولی که می‌خواید مقدارش رو تغییر بدید انتخاب کنید. همیشه برای استفاده از Goal Seek باید سلول‌هایی رو که شامل فرمول یا تابع هستند رو انتخاب کنید. در این مثال هم سلول B7 رو انتخاب می‌کنیم، چون فرمول در این سلول قرار داره.

۲- از تب داده‌ها روی فرمان What-If Analysis کلیک کرده و بعد از منوی کشویی گزینه Goal Seek رو انتخاب کنید.

۳- یک کادر با سه فیلد ظاهر میشه. اولین فیلد Set cell نتیجه رو نشون میده که برای این مثال در حال حاضر سلول B7 انتخاب شده. 

فیلد دوم برای قرار دادن مقدار نتیجه دلخواهمون هست. در مثال  ما 70 رو وارد می‌کنیم چون حداقل نمره‌ای هست که برای قبولی بهش نیاز داریم.

فیلد سوم آدرس سلولی هست که Goal Seek باید مقدار اون رو برامون نمایش بده. در این مثال سلول B6 رو انتخاب می‌کنیم.

۴- وقتی که سه تا فیلد رو تکمیل کردید روی OK کلیک کنید.

۵- مرحله بعد مشخص میشه که آیا Goal Seek می‌تونه یک سری اطلاعاتی پیدا کنه یا خیر. برای این کار روی OK کلیک کنید.

۶- نتیجه در سلول مشخص شده ظاهر میشه. در این مثال Goal Seek محاسبه کرد که برای قبولی در آزمون نهایی باید حداقل نمره ۹۰ رو بدست بیاریم.

نحوه استفاده از Goal Seek (مثال ۲):

فرض کنید که در حال برنامه‌ریزی برای یک رویداد خاص هستید و می‌خواید تا جایی که می‌تونید تعداد زیادی مهمان رو دعوت کنید. حالا برای فهمیدن این که چند نفر باید دعوت بشن می‌تونید از Goal Seek استفاده کنید. در مثال زیر سلول B5 حاوی فرمول B2+B3*B4 = برای محاسبه هزینه رزرو و هزینه کلی هر فرد هست.

۱- سلول B5 رو انتخاب می‌کنیم.

۲- از تب داده‌ها روی فرمان «What-If Analysis» کلیک کرده و بعد از منوی کشویی Goal Seek رو انتخاب کنید.

۳- یک کادر با سه فیلد ظاهر میشه. اولین فیلد Set cell نتیجه رو نشون میده که برای این مثال در حال حاضر سلول B5 انتخاب شده. 

فیلد دوم برای مقدار دلخواه هست. در مثال ما 500 رو وارد می کنیم، چون می خوایم فقط ۵۰۰ دلار خرج کنیم. 

فیلد سوم سلولی هست که Goal Seek پاسخ رو در اون نمایش میده. در مثال ما سلول B4 رو انتخاب می‌کنیم، چون می‌خوایم بدانیم برای هزینه کردن حداکثر ۵۰۰ دلار چند نفر مهمان می‌تونیم دعوت کنیم.

۴- روی OK کلیک کنید.

۵- مرحله بعد مشخص میشه که آیا Goal Seek می‌تونه یک راه حل و اطلاعاتی پیدا کنه یا خیر. برای این کار روی OK کلیک کنید.

۶- نتیجه در سلول مشخص‌شده ظاهر میشه. نتیجه جستجو در این مثال تقریبا ً ۱۸.۶۲ هست. در این مثال پاسخ نهایی ما باید یک عدد کامل باشه، پس باید پاسخ رو به بالا یا پایین گرد کنیم. از اون جایی که گرد کردن رو به بالا باعث میشه که از بودجه فراتر بریم، عدد بدست آمده رو به پایین یعنی ۱۸ مهمان گرد میشه.

همانطور که در مثال بالا می‌بینید بعضی از نتایج باید به صورت یک عدد کامل باشن. اگه Goal Seek یک عدد غیر کامل بعنوان نتیجه بهتون داد، بسته به موقعیت مورد نیاز اون عدد باید رو به پایین یا بالا گرد بشه.

انواع دیگر تحلیل what-if

برای پروژه های پیشرفته تر می‌تونید از انواع دیگه تحلیل what-if مثل سناریوها و جداول داده استفاده کنید. این نوع تحلیل به جای شروع از نتیجه مطلوب و رسیدن به مقدار اولیه، بهتون این امکان رو میده تا مقادیر چندگانه رو آزمایش کنید و ببینید که نتایج چگونه تغییر می‌کنند.

• سناریوها بهتون اجازه میدن تا مقادیر چندین سلول (حداکثر ۳۲ تا) رو همزمان جایگزین کنید. یعنی می‌تونید سناریوهای موردنظرتون رو ایجاد کنید و بعد اونها رو بدون تغییر مقادیر به صورت دستی مقایسه کنید. در مثال زیر ما از سناریوها برای مقایسه مکان‌های مختلف مناسب برای برگزاری یک جشن استفاده می‌کنیم.

• جداول داده‌ها به شما این امکان رو میدن تا یک یا دو متغیر در یک فرمول رو با مقادیر مختلفی که می‌خواین جایگزین کرده و بعد نتایج رو در یک جدول مشاهده کنید. این گزینه بر خلاف سناریوها یا Goal Seek بسیار قدرتمند هست، چون چندین نتیجه رو بصورت همزمان بهتون ارائه میده. در مثال زیر می تونید ۲۴ نتیجه ممکن برای یک وام خودرو رو مشاهده کنید.