تجزیه و تحلیل What-if در اکسل
معرفی
اکسل شامل ابزارهای قدرتمندی برای انجام محاسبات پیچیده ریاضی، از جمله تجزیه و تحلیل what-if است. این ویژگی می‌تواند به شما کمک کند که داده‌هایتان را آزمایش کنید و به سؤالات پاسخ دهید، حتی زمانی که داده‌ها ناقص هستند. در این درس، نحوه استفاده از ابزار تجزیه و تحلیل what-if به نام Goal Seek را یاد خواهید گرفت.
اختیاری: کتاب کار تمرینی ما را دانلود کنید.
جستجوی هدف
هر زمان که یک فرمول یا تابع در اکسل ایجاد می کنید، قسمت های مختلفی را در کنار هم قرار می دهید تا نتیجه را محاسبه کنید. Goal Seek برعکس عمل می کند: به شما امکان می دهد با نتیجه دلخواه شروع کنید و مقدار ورودی را محاسبه می کند که نتیجه را به شما نشان می دهد. ما از چند مثال برای نشان دادن نحوه استفاده از Goal Seek استفاده می کنیم.
برای استفاده از Goal Seek (مثال 1):
فرض کنید در یک کلاس ثبت نام کرده اید. شما در حال حاضر نمره 65 دارید و برای قبولی در کلاس حداقل به نمره 70 نیاز دارید. خوشبختانه، شما یک تکلیف نهایی دارید که ممکن است بتواند میانگین شما را افزایش دهد. می توانید از Goal Seek استفاده کنید تا متوجه شوید که در تکلیف نهایی به چه نمره ای برای قبولی در کلاس نیاز دارید.
در تصویر زیر مشاهده می کنید که نمرات چهار تکلیف اول 58 ، 70 ، 72 و 60 است. حتی اگر نمی دانیم کلاس پنجم چقدر خواهد بود، می توانیم یک فرمول – یا تابع – بنویسیم که نمره نهایی را محاسبه می کند. در این مورد، هر تکلیف به طور مساوی وزن داده می شود، بنابراین تنها کاری که باید انجام دهیم این است که با تایپ =AVERAGE(B2:B6) میانگین هر پنج نمره را انجام دهیم. هنگامی که از Goal Seek استفاده می کنیم، سلول B6 حداقل نمره ای را که باید در آن تکلیف کسب کنیم را به ما نشان می دهد.
1. سلولی را با مقداری که می خواهید تغییر دهید انتخاب کنید. هر زمان که از Goal Seek استفاده می‌کنید، باید سلولی را انتخاب کنید که از قبل حاوی یک فرمول یا تابع باشد. در مثال ما سلول B7 را انتخاب می کنیم زیرا حاوی فرمول =AVERAGE(B2:B6) است.
2. از تب Data، روی فرمان What-If Analysis کلیک کنید، سپس Goal Seek را از منوی کشویی انتخاب کنید.
3. یک کادر محاوره ای با سه فیلد ظاهر می شود. فیلد اول Set cell: حاوی نتیجه دلخواه خواهد بود. در مثال ما، سلول B7 قبلاً انتخاب شده است.
فیلد دوم، To value: نتیجه دلخواه است. در مثال ما عدد 70 را وارد می کنیم زیرا برای گذراندن کلاس باید حداقل آن را کسب کنیم.
فیلد سوم، سلول تغییر B y: سلولی است که Goal Seek پاسخ خود را در آن قرار می دهد. در مثال خود، سلول B6 را انتخاب می کنیم زیرا می خواهیم نمره ای را که باید در تکلیف نهایی کسب کنیم، تعیین کنیم.
4. وقتی کارتان تمام شد، روی OK کلیک کنید.
5. کادر محاوره ای به شما می گوید که آیا Goal Seek توانسته راه حلی بیابد یا خیر. روی OK کلیک کنید.
6. نتیجه در سلول مشخص شده ظاهر می شود. در مثال ما، Goal Seek محاسبه کرد که برای کسب نمره قبولی باید حداقل 90 در تکلیف نهایی کسب کنیم.
برای استفاده از Goal Seek (مثال 2):
فرض کنید در حال برنامه‌ریزی یک رویداد هستید و می‌خواهید تا جایی که می‌توانید افراد بیشتری را بدون بودجه بیش از 500 دلار دعوت کنید. می‌توانیم از Goal Seek استفاده کنیم تا بفهمیم چند نفر را دعوت کنیم. در مثال زیر، سلول B5 حاوی فرمول =B2+B3*B4 برای محاسبه کل هزینه رزرو اتاق به اضافه هزینه هر نفر است.
- سلولی را با مقداری که می خواهید تغییر دهید انتخاب کنید. در مثال خود، سلول B5 را انتخاب می کنیم.
2. از تب Data، روی فرمان What-If Analysis کلیک کنید، سپس Goal Seek را از منوی کشویی انتخاب کنید.
3. یک کادر محاوره ای با سه فیلد ظاهر می شود. فیلد اول Set cell: حاوی نتیجه دلخواه خواهد بود. در مثال ما، سلول B5 قبلاً انتخاب شده است.
فیلد دوم، To value: نتیجه دلخواه است. در مثال ما 500 را وارد می کنیم زیرا فقط می خواهیم 500 دلار خرج کنیم.
فیلد سوم، با سلول تغییر (By changing cell): سلولی است که Goal Seek پاسخ خود را در آن قرار می دهد. در مثال خود، سلول B4 را انتخاب می‌کنیم زیرا می‌خواهیم بدانیم چند مهمان می‌توانیم بدون صرف هزینه بیش از 500 دلار دعوت کنیم.
4. وقتی کارتان تمام شد، روی OK کلیک کنید.
5. کادر محاوره ای به شما می گوید که آیا Goal Seek توانسته راه حلی بیابد یا خیر. روی OK کلیک کنید.
6. نتیجه در سلول مشخص شده ظاهر می شود. در مثال ما، Goal Seek پاسخ را تقریباً 18.62 محاسبه کرد. در این مورد، پاسخ نهایی ما باید یک عدد کامل باشد، بنابراین باید پاسخ را به بالا یا پایین گرد کنیم. از آنجا که جمع کردن به بالا باعث می شود ما از بودجه خود فراتر برویم، به 18 مهمان کاهش می دهیم.
همانطور که در مثال بالا می بینید، در برخی موقعیت ها نیاز است که پاسخ یک عدد کامل باشد. اگر Goal Seek به شما یک اعشار می دهد، بسته به موقعیت، باید به بالا یا پایین گرد کنید.
انواع دیگر تجزیه و تحلیل what-if
برای پروژه های پیشرفته تر، ممکن است بخواهید انواع دیگری از تجزیه و تحلیل what-if را در نظر بگیرید: سناریوها (scenarios) و جداول داده (data tables). این گزینه‌ها به‌جای شروع از نتیجه دلخواه و کار به عقب، مانند Goal Seek، به شما امکان می‌دهند چندین مقدار را آزمایش کنید و ببینید نتایج چگونه تغییر می‌کنند.
- سناریوها به شما این امکان را می دهند که مقادیری را برای چندین سلول (تا 32) به طور همزمان جایگزین کنید. می توانید هر تعداد سناریو که می خواهید ایجاد کنید و سپس بدون تغییر مقادیر به صورت دستی آنها را با هم مقایسه کنید. در مثال زیر، ما از سناریوها برای مقایسه مکان‌های مختلف برای یک رویداد آینده استفاده می‌کنیم.
برای اطلاعات بیشتر در مورد سناریوها، این مقاله از مایکروسافت را بخوانید.
- جداول داده به شما این امکان را می دهد که یک یا دو متغیر را در یک فرمول بگیرید و آنها را با مقادیر متفاوتی که می خواهید جایگزین کنید، سپس نتایج را در یک جدول مشاهده کنید. این گزینه به ویژه قدرتمند است زیرا برخلاف سناریوها یا جستجوی هدف، چندین نتیجه را به طور همزمان نشان می دهد. در مثال زیر می توانیم 24 نتیجه احتمالی برای وام خودرو را مشاهده کنیم.
برای اطلاعات بیشتر در مورد جداول داده، این مقاله را از مایکروسافت بخوانید.
چالش!
- کتاب کار تمرینی ما را باز کنید.
- روی تب Challenge در پایین سمت چپ کتاب کار کلیک کنید.
- در سلول B8، تابعی ایجاد کنید که میانگین فروش را در B2:B7 محاسبه می کند.
- کتاب کار مبالغ فروش ماهانه دیو (Dave) را برای نیمه اول سال نشان می دهد. اگر او به میانگین سالانه 200000 دلار برسد، 5 درصد پاداش دریافت می کند. از Goal Seek استفاده کنید تا بفهمید که او چقدر باید در ماه ژوئن بفروشد تا به میانگین 200000 دلار برسد.
- وقتی کارتان تمام شد، کتاب کار شما باید به شکل زیر باشد:
