آموزش what if analysis در اکسل ۲۰۱۶
what if analysis در اکسل ۲۰۱۶
در آخرین درس از مجموعه درسهای آموزش کار با excel به تشریح یکی از بهترین ابزارهای این برنامه به نام what if analysis در اکسل ۲۰۱۶ خواهیم پرداخت.
اکسل شامل ابزارهای قدرتمندی برای انجام محاسبات پیچیده ریاضی، از جمله ابزار what-if analysis است، که یک ابزار عالی برای تجزیه و تحلیل داده ها می باشد. این قابلیت به شما کمک می کند به بررسی اما و اگرها در یک برگه اکسل بپردازید و همچنین به سوالاتی که در یک برگه به ذهن شما می رسد پاسخ دهید، حتی اگر داده های آن برگه ناقص باشند. در این درس با یکی از ابزارهای what-if analysis به نام Goal Seek در اکسل آشنا می شویم.
در صورت تمایل می توانید فایل اکسل زیر که مربوط به تمرین آموزش های همین درس می باشد را دانلود نموده و در ادامه آموزش what if analysis در اکسل ۲۰۱۶ با ما باشید.
what if analysis در اکسل ۲۰۱۶ ، آموزش goal seek در اکسل ۲۰۱۶
آموزش goal seek در اکسل ۲۰۱۶
هر زمان که یک فرمول و یا یک تابع را در اکسل ایجاد می کنید، مقادیر مختلفی را درون آن فرمول یا تابع قرار می دهید تا نتیجه را محاسبه کنید. ابزار Goal Seek برعکس عمل می کند؛ به شما اجازه می دهد تا با نتیجه دلخواهتان شروع کنید و سپس Goal Seek مقادیر ورودی را محاسبه کرده و در نهایت همان جواب دلخواه شما را نتیجه می دهد. در ادامه با ارائه مثالهایی به آموزش goal seek در اکسل ۲۰۱۶ و نحوه استفاده از آن خواهیم پرداخت .
روش استفاده از ابزار Goal Seek (مثال اول)
فرض کنیم شما در یک کلاس ثبت نام کرده اید. میانگین نمره شما در حال حاضر ۶۵ می باشد، و شما حداقل به نمره ۷۰ نیاز دارید تا بتوانید آن کلاس را بگذرانید. خوشبختانه، شما یک امتحان نهایی دارید که ممکن است بتواند میانگین نمرات شما را افزایش دهد. شما می توانید با استفاده از ابزار Goal Seek در اکسل پیدا کنید که به چه نمره ای در امتحان نهایی نیاز دارید تا بتوانید آن کلاس را با موفقیت بگذرانید.
در تصویر زیر می توانید ببینید که نمرات در چهار آزمون اول ۵۸، ۷۰، ۷۲ و ۶۰ می باشند. حتی اگر ندانیم که چه نمره ای در آزمون پنجم حاصل شده است، با این وجود می توانیم یک فرمول یا تابع بنویسیم که نمره نهایی (Final Grade) را محاسبه کند. ضریب همه آزمونها برابر یک است، بنابراین تمام کاری که باید انجام دهیم این است که میانگین پنج آزمون را با نوشتن تابع (AVERAGE(B2:B6= محاسبه کنیم. هنگامی که از ابزار Goal Seek استفاده کنیم، سلول B6 حداقل نمره مورد نیاز را که باید در آن آزمون کسب کنیم، نمایش می دهد.
۱- سلولی که قصد تغییر مقدار آن را دارید انتخاب کنید. هر زمان که از Goal Seek استفاده می کنید، باید سلول هایی را که دارای فرمول یا تابع هستند انتخاب کنید. در این مثال ما سلول B7 را انتخاب می کنیم، به این خاطر که دارای تابع (AVERAGE(B2:B6= می باشد.
۲- در زبانه Data روی دستور What-If Analysis کلیک کرده و سپس از منوی کشویی Goal Seek را انتخاب کنید.
۳- پنجره Goal Seek با سه فیلد مختلف ظاهر می شود. در فیلد اول یعنی Set cell، سلولی که می خواهیم نتیجه دلخواهمان در آن ظاهر شود، باید انتخاب گردد. در این مثال سلول B7 را انتخاب می کنیم.
در فیلد دوم یعنی To value، نتیجه دلخواه وارد می شود. در این مثال مقدار ۷۰ را در آن وارد می کنیم، به این خاطر که باید میانگین نمراتمان حداقل ۷۰ باشد تا بتوانیم آن کلاس را پاس کنیم.
فیلد سوم یعنی By changing cell، سلولی است که ابزار Goal Seek، پاسخ را در آن نمایش خواهد داد. در این مثال سلول B6 را انتخاب می کنیم، زیرا ما می خواهیم نمره ای را که باید در آزمون نهایی (Final Exam) کسب کنیم، تعیین نماییم.
۴- پس از پرکردن فیلدها، روی OK کلیک کنید.
۵- پنجره ای ظاهر شده و به شما اطلاع می دهد که Goal Seek قادر است پاسخ نهایی را ارائه دهد. روی OK کلیک کنید.
۶- نتیجه در سلول مشخص شده ظاهر می شود. در این مثال Goal Seek محاسبه کرده است که باید در آزمون نهایی (Final Exam) حداقل ۹۰ بگیریم تا بتوانیم این کلاس را با موفقیت بگذرانیم.
آموزش goal seek در اکسل ۲۰۱۶ (مثال دوم)
فرض کنیم شما یک مراسم ترتیب داده اید و می خواهید تعدادی از دوستانتان را دعوت کنید. حداکثر بودجه شما برای ترتیب این مراسم ۵۰۰ دلار است. ما می توانیم از Goal Seek استفاده کنیم تا ببینیم چند نفر را می توانیم دعوت کنیم. در مثال زیر، سلول B5 دارای فرمول B2+B3*B4= برای محاسبه کل هزینه رزرو اتاق، به علاوه هزینه هر فرد می باشد.
۱- سلولی که قصد تغییر مقدار آن را دارید انتخاب کنید. در این مثال ما سلول B5 را انتخاب می کنیم.
۲- در زبانه Data روی دستور What-If Analysis کلیک کرده و سپس از منوی کشویی Goal Seek را انتخاب کنید.
۳- پنجره Goal Seek با سه فیلد مختلف ظاهر می شود. در فیلد اول یعنی Set cell، سلولی که می خواهیم نتیجه دلخواهمان در آن ظاهر شود، باید انتخاب گردد. در این مثال سلول B5 از قبل انتخاب شده است.
در فیلد دوم یعنی To value، نتیجه دلخواه وارد می شود. در این مثال مقدار ۵۰۰ را در آن وارد می کنیم، به این خاطر که ما فقط می خواهیم ۵۰۰ دلار خرج کنیم.
فیلد سوم یعنی By changing cell، سلولی است که ابزار Goal Seek پاسخ را در آن نمایش خواهد داد. در این مثال سلول B4 را انتخاب می کنیم، زیرا می خواهیم بدانیم که چه تعداد مهمان می توانیم دعوت کنیم بدون اینکه بیش از ۵۰۰ دلار خرج کرده باشیم.
۴- پس از پر کردن فیلدها، روی OK کلیک کنید.
۵- پنجره ای ظاهر شده و به شما اطلاع می دهد که Goal Seek قادر است پاسخ نهایی را ارائه دهد. روی OK کلیک کنید.
۶- نتیجه در سلول مشخص شده ظاهر می شود. در این مثال Goal Seek محاسبه کرده است که پاسخ تقریبا ۱۸.۶۲ نفر می باشد. در این حالت، پاسخ نهایی ما باید یک عدد صحیح باشد، بنابراین ما باید پاسخ را به بالا یا پایین گرد کنیم. از آنجا که گرد کردن به سمت بالا باعث می شود که ما از بودجه خود فراتر برویم، به سمت پایین یعنی ۱۸ مهمان گرد می کنیم.
نکته: همانطور که در مثال بالا می بینید، برخی نتایج در شرایط خاص الزاما باید تبدیل به یک عدد صحیح گردند. اگر ابزار Goal Seek به شما یک عدد اعشاری نمایش دهد، شما با توجه به وضعیت و شرایط، باید آن عدد را به سمت بالا و یا به سمت پایین گرد کنید.
انواع دیگر what if analysis در اکسل ۲۰۱۶
در ادامه آموزش کار با excel به طور خلاصه با دو نوع دیگر از ابزار what if analysis در اکسل ۲۰۱۶ آشنا می شویم. در پروژه های پیشرفته تر، شما ممکن است بخواهید از انواع دیگر what-if analysis شامل ابزارهای scenarios و data tables استفاده کنید. به جای اینکه از نتیجه نهایی شروع کرده و رو به عقب برویم، درست مثل همان کاری که ابزار Goal Seek انجام می دهد، این ابزارها به ما اجازه می دهند تا مقادیر مختلف را آزمایش کرده و تاثیرش را در نتیجه نهایی ملاحظه کنیم.
- Scenarios : این ابزار به شما اجازه می دهد تا مقادیر چندین سلول (حداکثر تا ۳۲ سلول) را همزمان جایگزین یکدیگر کنید. شما می توانید تعداد زیادی scenarios ساخته و آنها را بدون اینکه مقادیرشان را به صورت دستی تغییر دهید، با هم مقایسه کنید. در تصویر زیر ما از ابزار scenarios استفاده می کنیم تا سالن های پذیرایی مختلف را برای مراسمی که در پیش رو داریم، با هم مقایسه کنیم.
- Data tables : این ابزار به شما این امکان را می دهد تا یک یا دو متغیر در یک فرمول را با مقادیر متفاوت دیگری که می خواهید، جایگزین کرده و سپس نتایج را در یک جدول مشاهده کنید. این ابزار بسیار قدرتمند می باشد زیرا برخلاف دو ابزار scenarios و Goal Seek، می تواند نتایج چندگانه ای را همزمان نشان دهد. در تصویر زیر ما می توانیم ۲۴ نتیجه ممکن (از سود ۵.۵ تا ۸ درصد و بازپرداخت ۳۶ تا ۷۲ ماهه) را برای بازپرداخت یک وام خودرو مشاهده کنیم.
تمرین عملی
۱- فایل اکسل زیر را جهت انجام تمرینات دانلود و سپس باز کنید.
۲- برگه Challenge را انتخاب کنید.
۳- در سلول B8 یک تابع ایجاد کنید که میانگین فروش را در دامنه سلولی B2:B7 محاسبه کند.
۴- این برگه مبلغ فروش ماهانه آقای Dave را برای نیمه اول سال نشان می دهد. اگر او به میانگین فروش ۲۰۰,۰۰۰ دلار در اواسط سال برسد Mid-Year Average) 5%) جایزه دریافت می کند. با استفاده از Goal Seek پیدا کنید که چقدر باید در ماه June فروش داشته باشد تا به میانگین ۲۰۰,۰۰۰ دلار دست پیدا کند.
۵- زمانی که تمام تمرینات را انجام دادید، فایل اکسل شما شبیه تصویر زیر می شود:
what if analysis در اکسل ۲۰۱۶
ترجمه شده در سایت خلایق
منبع:
gcflearnfree.org
