مقدمه
با عرض سلام خدمت کاربران محترم سایت پی وی لرن و دوستانی که در مجموعه آموزشی اکسل ۲۰۱۹ همراه ما هستند.همونطور که در جلسه قبل مشاهده فرمودید عملیات بیش تر با PivotTables در EXCEL مثل فیلترها در اکسل ، Slicers در اکسل و نمودار محوری در اکسل یا PivotCharts بیان شد. در جلسه آخر به منوی What-if Analysis در اکسل خواهیم پرداخت و Goal Seek در اکسل ، انواع دیگر what-if analysis در اکسل رو مورد مطالعه قرار خواهیم داد.
What-if Analysis در اکسل
معرفی
اکسل ابزاری قدرتمند برای انجام محاسبات ریاضی پیچیده، از جمله what-if analysis است.
این ویژگی می تواند به شما در آزمایش و پاسخ دادن به سوالات با اطلاعات خود، حتی زمانی که اطلاعات ناقص است،کمک کند.
در این درس، شما یاد خواهید گرفت که از ابزار what-if analysis که Goal Seek نامیده می شود ،استفاده کنید.
Goal Seek در اکسل
هر زمان که یک فرمول یا عملکرد را در اکسل ایجاد می کنید، برای محاسبه نتیجه ،قسمت های مختلفی را قرار می دهید.
Goal Seek به روش متفاوتی کار می کند: این به شما امکان می دهد که با نتیجه دلخواه شروع کنید و مقدار ورودی که نتیجه را به شما می دهد را محاسبه کنید.
ما از چند مثال برای نشان دادن نحوه استفاده از Goal Seek استفاده خواهیم کرد.
استفاده از Goal Seek (مثال ۱)
بگذارید بگوییم شما در یک کلاس ثبت نام کرده اید.
شما در حال حاضر میانگین نمره ی ۶۵ را دارید و برای گذراندن دوره نیاز به حداقل نمره ی ۷۰ در کلاس را دارید.
خوشبختانه، شما یک تکلیف نهایی دارید که ممکن است بتواند میانگین شما را افزایش دهد.
شما می توانید از Goal Seek برای پیدا کردن نمره ای که شما نیاز دارید در تکلیف نهایی برای گذراندن کلاس را بیابید.
در تصویر زیر می توانید ببینید که نمرات در چهار تکلیف اول ۵۸، ۷۰، ۷۲ و ۶۰ است.
هرچند ما نمی دانیم که نمره ی پنجم چه خواهد بود، ما می توانیم فرمول یا عملکردی را بنویسیم که نمره نهایی را محاسبه می کند.
در این مورد، هر تکلیف به طور مساوی وزن می شود، بنابراین ما باید میانگین تمام پنج نمره را با تایپ AVERAGE(B2:B6) = به دست آوریم.
هنگامی که ما از Goal Seek استفاده می کنیم، سلول B6 به ما حداقل نمره ای که باید در این تکلیف پنجم به دست آوریم را نشان می دهد.
Goal Seek در اکسل – استفاده از Goal Seek (مثال ۱)
۱٫سلول را با مقداری که می خواهید تغییر دهید، انتخاب کنید. هر گاه از Goal Seek استفاده می کنید، باید سلول هایی را که قبلا شامل فرمول یا عملکرد هستند انتخاب کنید. در این مثال ما، سلول B7 را انتخاب خواهیم کرد زیرا شامل فرمول AVERAGE(B2:B6) = است.
Goal Seek در اکسل – استفاده از Goal Seek (مثال ۱)
۲٫ از زبانه Data ، بر روی فرمان What-If Analysis کلیک کنید، سپس از منوی کشویی Goal Seek را انتخاب کنید.
Goal Seek در اکسل – استفاده از Goal Seek (مثال ۱)
۳٫ یک جعبه محاوره با سه فیلد ظاهر می شود. اولین فیلد :Set cell، نتیجه دلخواه خواهد بود. در مثال ما سلول B7 در حال حاضر انتخاب شده است.
جعبه محاوره Goal Seek
فیلد دوم، :To value ، نتیجه دلخواه است. در این مثال ، ما ۷۰ را وارد خواهیم کرد زیرا حداقل نمره ای ست که باید آن را در کلاس بگیریم.
فیلد سوم، : By changing cell ، سلولی است که Goal Seek به آن پاسخ می دهد. در این مثال ما، سلول B6 را انتخاب خواهیم کرد زیرا ما می خواهیم درجه ای را که باید در انتساب نهایی کسب کنیم تعیین کنیم.
۴٫ وقتی که انجام شد، روی OK کلیک کنید.
استفاده از Goal Seek (مثال ۱)
۵٫ کادر محاوره ای به شما می گوید که Goal Seek قادر به یافتن یک راه حل است. روی OK کلیک کنید
استفاده از Goal Seek (مثال ۱)
۶٫ نتیجه در سلول مشخص شده ظاهر می شود. در مثال ما، Goal Seek محاسبه کرد که ما باید حداقل ۹۰ امتیاز را برای گذراندن دوره باید بدست آوریم.
Goal Seek در اکسل – استفاده از Goal Seek (مثال ۱)
استفاده از Goal Seek (مثال ۲)
بگذارید بگوییم شما یک رویداد را برنامه ریزی کرده اید و می خواهید بدون اینکه زیاده روی شود با بودجه ۵۰۰ دلار، بسیاری از افراد را دعوت کنید.
ما می توانیم از Goal Seek استفاده کنیم تا تعداد افرادی که باید دعوت کنید را بفهمیم.
در مثال ما در زیر، سلول B5 حاوی فرمول B2+B3*B4 = برای محاسبه کل هزینه رزرو اتاق، به علاوه هزینه هر فرد است.
۱٫سلول را با مقداری که می خواهید تغییر دهید، انتخاب کنید. در مثال ما سلول B5 را انتخاب می کنیم.
استفاده از Goal Seek (مثال ۲)
۲٫ از زبانه Data ، بر روی فرمان What-If Analysis کلیک کنید، سپس از منوی کشویی Goal Seek را انتخاب کنید.
Goal Seek در اکسل – استفاده از Goal Seek (مثال ۲)
۳٫ یک جعبه محاوره با سه فیلد ظاهر می شود.
جعبه محاوره Goal Seek
اولین فیلد :Set cell ، نتیجه دلخواه خواهد بود. در مثال ما سلول B5 در حال حاضر انتخاب شده است.
فیلد دوم، :To value ، نتیجه دلخواه است. در مثال ما، ۵۰۰ را وارد می کنیم، زیرا فقط می خواهیم ۵۰۰ دلار صرف کنیم.
فیلد سوم، :By changing cell ، سلولی است که Goal Seek آن را پاسخ می دهد.
در مثال ما سلول B4 را انتخاب خواهیم کرد زیرا ما می خواهیم بدانیم چطور میتوانیم مهمانهای زیادی را دعوت کنیم و اینکه هزینه ها بیش از ۵۰۰ دلار نشوند.
۴٫ وقتی که انجام شد، روی OK کلیک کنید.
استفاده از Goal Seek (مثال ۲)
۵٫ کادر محاوره ای به شما می گوید که Goal Seek قادر به یافتن یک راه حل است. روی OK کلیک کنید.
۶٫ نتیجه در سلول مشخص شده ظاهر می شود. در مثال ما، Goal Seek جواب را محاسبه کرد که حدود ۱۸٫۶۲ می باشد.
در این حالت، پاسخ نهایی ما باید یک عدد کامل باشد، بنابراین ما باید پاسخ را به بالا یا پایین رند کنیم.
از آنجا که اضافه شدن باعث می شود که از بودجه تخصیص یافته فراتر برویم، به ۱۸ مهمان اکتفا می کنیم.
استفاده از Goal Seek (مثال ۲)
همانطور که در مثال بالا می بینید، در بعضی از شرایط نیاز به جواب دادن به صورت یک عدد رند و کامل است. اگر Goal Seek به شما عددی اعطاء می کند، بسته به موقعیت مورد نیاز، باید به پایین یا بالا رند شود.
انواع دیگر what-if analysis در اکسل
برای پروژه های پیشرفته تر، ممکن است بخواهید انواع دیگر what-if analysis در اکسل را در نظر بگیرید: سناریوها و جداول داده. به جای شروع از نتیجه مطلوب و کار به عقب، مانند Goal Seek، این گزینه ها به شما امکان می دهد تا مقادیر چندگانه را آزمایش کنید و ببینید که چگونه نتایج تغییر می کنند.
Scenarios به شما اجازه می دهند که مقادیر چندین سلول (تا ۳۲) را در همان زمان جایگزین کنید.
شما می توانید سناریوهای بسیاری را که می خواهید ایجاد کنید و سپس آنها را بدون تغییر مقادیر به صورت دستی مقایسه کنید.
در مثال زیر، ما از سناریو برای مقایسه مکانهای مختلف برای رویداد آینده استفاده می کنیم.
انواع دیگر what-if analysis در اکسل
Data tables به شما این امکان را می دهند که یک یا دو متغیر را در یک فرمول بگیرید و آن ها را با همان ارزش های مختلفی که می خواهید جایگزین کنید.
سپس نتایج را در یک جدول مشاهده کنید. این گزینه به ویژه قدرتمند است، زیرا نتایج چند گانه را در یک زمان نشان می دهد.
بر خلاف سناریوها یا Goal Seek . در مثال زیر می توانید ۲۴ نتیجه ممکن برای یک وام خودرو را مشاهده کنید.
انواع دیگر what-if analysis در اکسل
با انواع دیگر what-if analysis در اکسل مبحث EXCEL 2019 رو به پایان می رسونیم.
چالش!
کتاب کار تمرین عملی ما را باز کنید
روی برگه Challenge در پایین سمت چپ کتاب کار کلیک کنید.
در سلول B8 یک تابع ایجاد کنید که میانگین فروش را در B2: B7 محاسبه کند.
این کتاب کار نشان می دهد که مبلغ فروش ماهانه Dave برای نیمه اول سال است. اگر میانگین فروش او به ۲۰۰،۰۰۰ دلار در اواسط سال رسید ، پاداش ۵٪ دریافت می کند. با استفاده از Goal Seek می توان فهمید که چقدر او باید در ماه ژوئن به فروش برساند تا میانگین ۲۰۰،۰۰۰ دلار باشد.
هنگامی که کتابکار شما به پایان رسید، کتاب کار شما باید شبیه به این باشد:
چالش!
کلام پایانی
در جلسه آخر و در قسمت پایانی به منوی What-if Analysis در اکسل پرداختیم و Goal Seek در اکسل ، انواع دیگر what-if analysis در اکسل رو مورد مطالعه قرار دادیم. امیدواریم مجموعه آموزشی EXCEL 2019 مورد توجه شما عزیزان قرار گرفته باشه و استفاده لازم رو برده باشین.
موفق و کامیاب باشید.
فروشگاه
فیلم های آموزشی
کتاب های آموزشی
مقالات آموزشی
وردپرس
