S.Cheraghi
کاربر تازه وارد
- تاریخ عضویت
- 28 فوریه 2017
- نوشته‌ها
- 48
- لایک‌ها
- 4
خیلی وقتها پیش میاد که میخوایم خروجی یک تابع رو با تغییر بعضی متغیرهاش تعیین کنیم. یعنی مثلا در یک تابع محاسبه اقساط، بگیم که تعداد اقساط چندتا باشه تا میزان قسط برسه به میزان دلخواهمون.
برای این کار اکسل ابزاری رو در اختیار ما قرار میده که میتونیم خروجی تابع رو به مقدار دلخواه برسونیم. ابزار Goal seek از گروه ابزارهای تحلیل داده What If Analysis هست. نحوه عملکرد این ابزار رو با شرح یک مثال توضیح میدم:
فرض کنید فرمولی نوشته شده برای میزان خالص دریافتی حقوق یک کارمند. میخوایم ببینیم برای رسیدن به دریافتی 10 میلیون تومان در ماه، پایه چه عددی باید باشه؟
نکته:
علت انتخاب این مثال، مرور Nested If و کدنویسی در فرمت سل و مشاهده نحوه محاسبه مالیات حقوق بوده. برای اینکه بتونید فرمول رو مشاهده کنید و منطقش رو درک کنید، فایل این آموزش در انتهای صفحه قرار داده شده است.
برای محاسبه مالیات حقوق از If تودرتو استفاده کردیم. (سقف معافیت مالیاتی (سلول G2) قابل تغییر هست)
بیمه هم که بصورت 7درصد حقوق پایه به عنوان سهم کارمند محاسبه شده.
شکل 1- ابزار goal seek – محاسبه میزان مالیات حقوق، بیمه و میزان دریافتی
حالا فرض کنید میخواهیم خالص دریافتی رو برسونیم به 5میلیون تومان. میخواهیم ببینیم حقوق پایه چقدر باید تغییر کنه تا میزان دریافتی به عدد دلخواه برسه.
برای این کار، از تب Data/ What If Analysis گزینه Goal Seek رو میزنیم.
پنجره ای مطابق شکل 2 باز میشه.
حتما بخوانید: تابع چند شرطی And و Or
شکل 2- ابزار goal seek – پنجره Goal Seek
فیلدهای موجو در این ابزار به شرح زیر هست:
Set Cell: سلولی که حاوی فرمولی است که میخواهیم نتیجه اونو به عدد دلخواه برسونیم. (از سلول گرفته میشه)
To Value: مقدار مورد نظر که میخواهیم فرمول به اون عدد برسه. (بصورت دستی تایپ میشه)
By Changing Cell: سلول متغیر که با تغییر اون، به عدد دلخواه میخواهیم برسیم. (از سلول گرفته میشه)
در این مثال، سلول ها رو مطابق شکل 3 انتخاب میکنیم. سلول حقوق دریافتی میشه Set Cell، سلول To Value میشه 5 میلیون تومان و سلول حقوق پایه میشه By Changing Cell.
شکل 3- ابزار goal seek – تغییر خروجی فرمول با Goal Seek
بعد از زدن Ok اکسل محاسبات رو انجام میده و مطابق شکل 4 میبینیم که با تغییر حقوق پایه به 5،795،181 میزان حقوق دریافتی به 5 میلیون تومان خواهد رسید.
توجه کنید که چون میزان مایلات و بیمه تابعی از حقوق پایه هستن، اثرات رو روی نتیجه نهایی میذارن.
شکل 4- ابزار goal seek – خروجی ابزار Goal Seek
نکته:
در صورتی که عدد مورد نظر منطقی نباشه، Goal Seek به جواب نمیرسه. مثلا یک عددی به فرمول بدیم، که هیچ وقت با تغییر متغیر خاص، دست یافتنی نباشه.
ابزارهای تحلیل داده یا همون What If Analysis ابزارهای خیلی مفید و کاربردی هستن که در این مثال به Goal Seek اشاره کردیم. دو ابزار Table و Scenario در آموزش های بعد ارائه خواهد شد.
منبع آموزش:
Goal Seek، ابزاری برای تحلیل داده
برای این کار اکسل ابزاری رو در اختیار ما قرار میده که میتونیم خروجی تابع رو به مقدار دلخواه برسونیم. ابزار Goal seek از گروه ابزارهای تحلیل داده What If Analysis هست. نحوه عملکرد این ابزار رو با شرح یک مثال توضیح میدم:
فرض کنید فرمولی نوشته شده برای میزان خالص دریافتی حقوق یک کارمند. میخوایم ببینیم برای رسیدن به دریافتی 10 میلیون تومان در ماه، پایه چه عددی باید باشه؟
نکته:
علت انتخاب این مثال، مرور Nested If و کدنویسی در فرمت سل و مشاهده نحوه محاسبه مالیات حقوق بوده. برای اینکه بتونید فرمول رو مشاهده کنید و منطقش رو درک کنید، فایل این آموزش در انتهای صفحه قرار داده شده است.
برای محاسبه مالیات حقوق از If تودرتو استفاده کردیم. (سقف معافیت مالیاتی (سلول G2) قابل تغییر هست)
بیمه هم که بصورت 7درصد حقوق پایه به عنوان سهم کارمند محاسبه شده.
شکل 1- ابزار goal seek – محاسبه میزان مالیات حقوق، بیمه و میزان دریافتی
حالا فرض کنید میخواهیم خالص دریافتی رو برسونیم به 5میلیون تومان. میخواهیم ببینیم حقوق پایه چقدر باید تغییر کنه تا میزان دریافتی به عدد دلخواه برسه.
برای این کار، از تب Data/ What If Analysis گزینه Goal Seek رو میزنیم.
پنجره ای مطابق شکل 2 باز میشه.
حتما بخوانید: تابع چند شرطی And و Or
شکل 2- ابزار goal seek – پنجره Goal Seek
فیلدهای موجو در این ابزار به شرح زیر هست:
Set Cell: سلولی که حاوی فرمولی است که میخواهیم نتیجه اونو به عدد دلخواه برسونیم. (از سلول گرفته میشه)
To Value: مقدار مورد نظر که میخواهیم فرمول به اون عدد برسه. (بصورت دستی تایپ میشه)
By Changing Cell: سلول متغیر که با تغییر اون، به عدد دلخواه میخواهیم برسیم. (از سلول گرفته میشه)
در این مثال، سلول ها رو مطابق شکل 3 انتخاب میکنیم. سلول حقوق دریافتی میشه Set Cell، سلول To Value میشه 5 میلیون تومان و سلول حقوق پایه میشه By Changing Cell.
شکل 3- ابزار goal seek – تغییر خروجی فرمول با Goal Seek
بعد از زدن Ok اکسل محاسبات رو انجام میده و مطابق شکل 4 میبینیم که با تغییر حقوق پایه به 5،795،181 میزان حقوق دریافتی به 5 میلیون تومان خواهد رسید.
توجه کنید که چون میزان مایلات و بیمه تابعی از حقوق پایه هستن، اثرات رو روی نتیجه نهایی میذارن.
شکل 4- ابزار goal seek – خروجی ابزار Goal Seek
نکته:
در صورتی که عدد مورد نظر منطقی نباشه، Goal Seek به جواب نمیرسه. مثلا یک عددی به فرمول بدیم، که هیچ وقت با تغییر متغیر خاص، دست یافتنی نباشه.
ابزارهای تحلیل داده یا همون What If Analysis ابزارهای خیلی مفید و کاربردی هستن که در این مثال به Goal Seek اشاره کردیم. دو ابزار Table و Scenario در آموزش های بعد ارائه خواهد شد.
منبع آموزش:
Goal Seek، ابزاری برای تحلیل داده
