خوش آموز اولین و تنها سایت آموزشی %100 رایگان ایران با 4133 آموزش متنی، تصویری و ویدئویی

29. تجزیه و تحلیل داده ها با What-if در اکسل 2016

29. تجزیه و تحلیل داده ها با What-if در اکسل 2016

کد مطلب : 209 PDF

اکسل برای انجام محاسبات پیچیده ریاضی ابزارهای قدرتمندی دارد، یکی از این ابزارها تجزیه و تحلیل what-if می باشد. این ابزار به شما کمک می کند، تا سوالها و پاسخهایی را با داده های خود آزمایش کنید، حتی در زمانیکه داده های شما تکمیل نشده باشند هم این ابزار کار می کند. در این درس، شما یاد خواهید گرفت که چگونه از ابزار تجریه و تحلیل what-if اکسل استفاده کنید، نام این ابزار Goal Seek (جستجوی هدف) می باشد.

جشنواره آموزش های رایگان خوش آموز در تمامی 365 روز سال

اگر می خواهید مثالهای ارائه شده را دقیقا مانند آموزش اجرا کنید فایل اکسل زیر را که مربوط به مثالهای همین درس می باشد دانلود کنید.

دانلود فایل اکسل مربوط به مثالهای این آموزش

ابزار Goal Seek در اکسل


هر وقت که یک فرمول و یا یک تابع را در اکسل ایجاد می کنید، شما بخشهای مختلفی را با هم یکی می کنید تا نتایج را محاسبه کنید. ابزار Goal Seek متضاد این مساله این عمل می کند: این ابزار به شما امکان می دهد تا با نتابج مطلوبتان شروع کنید، و مقادیری را محاسبه می کند که این نتیجه را به شما بدهد. در ادامه با مثالهایی تشریح خواهیم کرد که این ابزار دقیقا چگونه کار می کند.

روش استفاده از ابزار Goal Seek در اکسل (مثال 1) :


فرض کنیم شما در یک کلاس ثبت نام کرده اید. نمره شما در حال حاضر 65 می باشد، و شما حداقل به نمره 70 نیاز دارید تا بتوانید آن کلاس را بگذرانید. خوشبختانه، شما یک امتحان نهایی دارید که می توانید به واسطه آن میانگین نمراتتان را بالاتر ببرید. شما می توانید از ابزار Goal Seek اکسل استفاده کنید تا متوجه شوید، در امتحان نهایی شما به چه نمره ای نیاز دارید تا بتوانید آن کلاس را با موفقیت بگذرانید (یا اصطلاحا پاس کنید).

در تصویر زیر شما نمرات 4 آزمون اول را می بینید. این نمرات 58، 70، 72 و 60 می باشند. اگر چه ما هنوز نمی دانیم نمره آزمون پنجم شما چه شده است، با این وجود می توانیم فرمولی (یا تابعی) بنویسیم که نمره آزمون نهایی شما را محاسبه کند. در این وضعیت، هز آزمون به یک اندازه در فرمول ما وزن خواهد داشت، بنابراین آنچه ما نیاز داریم اینست که میانگین 5 آزمون اول را با نوشتن دستور زیر محاسبه کنیم.

=AVERAGE(B2:B6)


وقتی ما از ابزار Goal Seek استفاده کنیم، سلول B6 حداقل نمره لازم را که ما باید در آن آزمون کسب کنیم را به ما نمایش می دهد.

29. تجزیه و تحلیل داده ها با What-if در اکسل 2016 . آموزشگاه رایگان خوش آموز

سلولی را که قصد ویرایش مقدارش را دارید انتخاب کنید. هر وقت که شما از ابزار Goal Seek اکسل استفاده می کنید، باید سلولی را که دارای یک فرمول یا یک تابع می باشد را انتخاب کنید. در این مثال ما سلول B7 را انتخاب می کنیم چون فرمول زیر داخل آن نوشته شده است :

=AVERAGE(B2:B6)


29. تجزیه و تحلیل داده ها با What-if در اکسل 2016 . آموزشگاه رایگان خوش آموز

در تب Data بر روی دستور What-If Analysis کلیک کنید و سپس از کادر باز شده گزینه Goal Seek را انتخاب نمایید.

29. تجزیه و تحلیل داده ها با What-if در اکسل 2016 . آموزشگاه رایگان خوش آموز

یک پنجره یا سه فیلد ظاهر می شود. اولین فیلد که Set cell می باشد، شامل نتایج مطلوب (ایده آل) می باشد. در مثال ما سلول B7 برای این مورد انتخاب شده است.

فیلد دوم To value نتیجه مورد نظر می باشد. در مثال ما مقدار 70 را در آن وارد می کنیم، زیرا ما باید در نهایت 70 نمره کسب کنیم تا بتوانیم این کلاس را پاس کنیم.

فیلد سوم By changing cell، سلولی است که ابزار Goal Seek پاسخ را در آن نمایش خواهد داد. در این مثال ما سلول B6 را انتخاب می کنیم، زیرا می خواهیم نمره مورد احتیاج ما در آزمون نهایی در آنجا نمایش داده شود.

وقتی این کارها را انجام دادید ok کنید.

29. تجزیه و تحلیل داده ها با What-if در اکسل 2016 . آموزشگاه رایگان خوش آموز

اگر ابزار Goal Seek قادر باشد نتیجه را برای شما محاسبه کند، این موضوع را به شما اطلاع می دهد. ok را کلیک کنید.

29. تجزیه و تحلیل داده ها با What-if در اکسل 2016 . آموزشگاه رایگان خوش آموز

نتیجه در سلولی که برای نمایش نتیجه مشخص کرده اید، ظاهر می شود. در مثال ما، ابزار Goal Seek به ما نشان می دهد که برای پاس کردن کلاس باید در آزمون نهایی نمره 90 بگیریم.

29. تجزیه و تحلیل داده ها با What-if در اکسل 2016 . آموزشگاه رایگان خوش آموز

روش استفاده از ابزار Goal Seek در اکسل (مثال 2) :


فرض کنیم شما مشغول برنامه ریزی برای یک مراسم هستید، و می خواهید تا جای ممکن افراد بیشتری را دعوت کنید، البته بودجه شما برای این مراسم 500 دلار می باشد. ما می توانیم از ابزار Goal Seek اکسل استفاده کنیم تا ببینیم، چند نفر را می توانیم دعوت کنیم. در مثال زیر، سلول B5 شامل فرمول زیر می باشد که مجموع هزینه رزرو اتاق به اضافه هزینه هر شخص می باشد.

=B2+B3*B4


ابتدا سلولی را که می خواهید مقدارش را تغییر بدهید، انتخاب نمایید. در این مثال ما سلول B5 را انتخاب می کنیم.

29. تجزیه و تحلیل داده ها با What-if در اکسل 2016 . آموزشگاه رایگان خوش آموز

در تب Data ، بر روی دستور What-If Analysis کلیک کنید، سپس از کادر باز شده گزینه Goal Seek را انتخاب نمایید.

29. تجزیه و تحلیل داده ها با What-if در اکسل 2016 . آموزشگاه رایگان خوش آموز

یک پنجره با سه فیلد نمایان می شود. فیلد اول Set cell حاوی نتایج مطلوب است. در مثال ما سلول B5 انتخاب شده است.

فیلد دوم To value شامل نتیجه لازم است، ما مقدار 500 را در آن وارد می کنیم زیرا فقط می خواهیم 500 دلار خرج کنیم.

فیلد سوم By changing cell محلی است که ابزار Goal Seek نتایج محاسبه شده را در آن نمایش خواهد داد. در این مثال، ما سلول B4 را انتخاب می کنیم، زیرا می خواهیم نتیجه اینکه ما چند مهمان را می توانیم دعوت کنیم، بدون اینکه بیش از 500 دلار خرج کنیم، در آنجا نمایش داده شود.

وقتی کارتان تمام شد ok کنید.

29. تجزیه و تحلیل داده ها با What-if در اکسل 2016 . آموزشگاه رایگان خوش آموز

اگر ابزار Goal Seek قادر به محاسبه نتایج باشد، در این پنجره به شما خبر میدهد. ok کنید.

نتایج محاسبه شده در سلول مربوطه نمایان می شود. در مثال ما، ابزار Goal Seek محاسبه کرده است که پاسخ تقریبا 18.62 می باشد. از آنجا که در این مورد خاص پاسخ ما باید یک عدد صحیح باشد، پس ما نیاز داریم تا این عدد را به سمت بالا و یا به سمت پایین گرد (رند) کنیم. و طبیعتا چون بودجه ما 500 دلار است و نمی خواهیم بیش از آن هزینه کنیم این عدد را به سمت پایین گرد می کنیم تا نتیجه تبدیل به 18 گردد.

29. تجزیه و تحلیل داده ها با What-if در اکسل 2016 . آموزشگاه رایگان خوش آموز

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

سایر انواع what-if analysis در اکسل


در پروژه های خیلی پیشرفته تر، شما ممکن است از انواع دیگر دستور what-if analysis استفاده نمایید. این انواع شامل گزینه های scenarios و data tables می باشند. جای اینکه مشابه ابزار Goal Seek از نتایج مطلوب شروع کنیم و رو به عقب برگردیم، این موارد گزینه هایی به شما می دهند که مقادیر مختلف را آزمایش کنید و تاثیرش را در نتیجه نهایی ملاحظه کنید.

  • Scenarios : این گزینه به شما امکان می دهد تا مقادیر سلولهای متعددی را (تا سقف 32 سلول)، هم زمان جایگزین کنید. شما می توانید سناریوهای مختلفی را با این ابزار بسازید و آنها را با هم مقایسه کنید و در عین حال از تغییر دادن دستی مقادیر پرهیز کنید. در تصویر زیر، ما از سناریوهایی استفاده می کنیم تا سالن های مختلف را برای یک مراسمی که در پیش رو داریم، با هم مقایسه کنیم.

    29. تجزیه و تحلیل داده ها با What-if در اکسل 2016 . آموزشگاه رایگان خوش آموز

  • Data tables : این گزینه به شما این امکان را می دهد که یک یا دو متغیر در یک فرمول را در نظر بگیرید و آنها را با مقادیر متفاوت دیگری که می خواهید جایگزین کنید، و سپس نتایج را به شک یک جدول مشاهده کنید. این گزینه بسیار قدرتمند می باشد زیرا برخلاف دو ابزار قبلی، می تواند نتایج چندگانه ای را همزمان به شما نشان می دهد. در تصویر زیر ما می توانیم 24 نتیجه ممکن را برای یک وام خودرو مشاهده نماییم.

    29. تجزیه و تحلیل داده ها با What-if در اکسل 2016 . آموزشگاه رایگان خوش آموز


توضیحات مترجم :
در منبعی که بنده این مطلب را از روی آن ترجمه کردم در مورد این دو گزینه scenarios و همینطور data tables توضیحات بیشتری داده نشده بود و فقط با ارائه لینکی به سایت مایکروسافت ارجاع داده بود تا کسانی که علاقمند هستند بتوانند از طریق آن لینکها بیشتر در این زمینه مطالعه کنند.

سخن پایانی


کار ترجمه دوره آموزش اکسل ما در این لحظه به پایان رسید، ترجمه این دوره 29 جلسه ای برای بنده تقریبا یکماه زمان برد و شاید بد نباشد بدانید که خود بنده هم قبل از ترجمه این دوره اطلاعات جامعی در مورد اکسل نداشتم و با ترجمه این دوره به نوعی خودم اولین خواننده این دوره نیز بودم، واقعا بسیار لذت بردم و خیلی چیزها از این دوره فرا گرفتم. امیدوارم تجربه شیرین و لذت بخشی که برای بنده حاصل شد برای شما نیز عینا تکرار شود. انشالله بلافاصله مشغول ترجمه یک دوره آموزشی دیگر خواهم شد، به این امید که مفید واقع شود. نظرات شما قطعا باعث دلگرمی بنده در ادامه این کار خواهد بود.

از اینکه در طول این دوره آموزشی همراه ما بودید صمیمانه سپاسگزارم.

gcflearnfree

آموزش قبلی : 28. انجام کارهای بیشتر با جداول چرخشی (PivotTables) اکسل 2016



نویسنده : امیر انصاری

دیدگاه ها(7)

سمیه ۱۳۹۶/۱۰/۲۹

خیلی ممنون از زحمات شما خیلی عالی و کاربردی بود

نیما ۱۳۹۶/۱۲/۱۶

خیلی ممنون بابت این دوره عالی...خسته نباشید

امیر ۱۳۹۷/۰۳/۲۷

سلام
واقعا دستت درد نکنه
عالی و فوق العاده بود ، خیلی نیاز داشتم
قبلا کلاس مهارت های هفتگانه رو رفته بودم ولی همش از یادم رفته بود
دوباره یادم اومد خیلی بهتر و جامع تر
با آرزوی موفقیت برای شما دوست عزیز

ملیکا ۱۳۹۷/۰۴/۱۲

عالی بود ممنونم

محمد نوید ۱۳۹۷/۰۴/۱۹

تشکر بابت تمام زحمات شما...بسیار برای بنده که کلاس کامپیوتر نرفته بودم مفید بود..یک جهان سپاس

فرزاد ۱۳۹۷/۰۵/۰۱

عالی بود دوره

علی ۱۳۹۷/۰۵/۱۱

خیلی ممنون بابت زحمت ترجمه. بسیار دوره مفیدی بود

دیدگاه خود را ثبت کنید:


دوره رایگان آموزش جامع اکسل 2016 از مقدماتی تا پیشرفته

دوره رایگان آموزش جامع اکسل 2016 از مقدماتی تا پیشرفته