Goal Seek، ابزاری برای تحلیل داده
خیلی وقتها پیش میاد که میخوایم خروجی یک تابع رو با تغییر بعضی متغیرهاش تعیین کنیم. یعنی مثلا در یک تابع محاسبه اقساط، بگیم که تعداد اقساط چندتا باشه تا میزان قسط برسه به میزان دلخواهمون.
برای این کار اکسل ابزاری رو در اختیار ما قرار میده که میتونیم خروجی تابع رو به مقدار دلخواه برسونیم. ابزار Goal seek از گروه ابزارهای تحلیل داده What If Analysis هست. نحوه عملکرد این ابزار رو با شرح یک مثال توضیح میدم:
فرض کنید فرمولی نوشته شده برای میزان خالص دریافتی حقوق یک کارمند. میخوایم ببینیم برای رسیدن به دریافتی ۱۰ میلیون تومان در ماه، پایه چه عددی باید باشه؟
علت انتخاب این مثال، مرور Nested If و کدنویسی در فرمت سل و مشاهده نحوه محاسبه مالیات حقوق بوده. برای اینکه بتونید فرمول رو مشاهده کنید و منطقش رو درک کنید، فایل این آموزش در انتهای صفحه قرار داده شده است.
برای محاسبه مالیات حقوق از If تودرتو استفاده کردیم. (سقف معافیت مالیاتی (سلول G2) قابل تغییر هست)
بیمه هم که بصورت ۷درصد حقوق پایه به عنوان سهم کارمند محاسبه شده.
شکل ۱- ابزار goal seek – محاسبه میزان مالیات حقوق، بیمه و میزان دریافتی
حالا فرض کنید میخواهیم خالص دریافتی رو برسونیم به ۵ میلیون تومان. میخواهیم ببینیم حقوق پایه چقدر باید تغییر کنه تا میزان دریافتی به عدد دلخواه برسه.
برای این کار، از تب Data/ What If Analysis گزینه Goal Seek رو میزنیم.
پنجره ای مطابق شکل ۲ باز میشه.
شکل ۲- ابزار goal seek – پنجره Goal Seek
فیلدهای موجو در این ابزار به شرح زیر هست:
Set Cell: سلولی که حاوی فرمولی است که میخواهیم نتیجه اونو به عدد دلخواه برسونیم. (از سلول گرفته میشه)
To Value: مقدار مورد نظر که میخواهیم فرمول به اون عدد برسه. (بصورت دستی تایپ میشه)
By Changing Cell: سلول متغیر که با تغییر اون، به عدد دلخواه میخواهیم برسیم. (از سلول گرفته میشه)
در این مثال، سلول ها رو مطابق شکل ۳ انتخاب میکنیم. سلول حقوق دریافتی میشه Set Cell، سلول To Value میشه ۵ میلیون تومان و سلول حقوق پایه میشه By Changing Cell.
شکل ۳- ابزار goal seek – تغییر خروجی فرمول با Goal Seek
بعد از زدن Ok اکسل محاسبات رو انجام میده و مطابق شکل ۴ میبینیم که با تغییر حقوق پایه به ۵،۷۹۵،۱۸۱ میزان حقوق دریافتی به ۵ میلیون تومان خواهد رسید.
توجه کنید که چون میزان مایلات و بیمه تابعی از حقوق پایه هستن، اثرات رو روی نتیجه نهایی میذارن.
شکل ۴- ابزار goal seek – خروجی ابزار Goal Seek
در صورتی که عدد مورد نظر منطقی نباشه، Goal Seek به جواب نمیرسه. مثلا یک عددی به فرمول بدیم، که هیچ وقت با تغییر متغیر خاص، دست یافتنی نباشه.
ابزارهای تحلیل داده یا همون What If Analysis ابزارهای خیلی مفید و کاربردی هستن که در این مثال به Goal Seek اشاره کردیم. دو ابزار Table و Scenario در آموزش های بعد ارائه خواهد شد.
دانلود فایل اکسل آموزش استفاده از ابزار goal seek
برای دانلود فایل اکسلی که تو این آموزش ایجاد شده روی لینک زیر کلیک کنید:
سلام جنس اعداد متنی نیست یکی از اعداد بصورت Prosent نوشته شده
مشکلی نباید باشه
منم همین کار و میکنم و جواب میگیرم. شاید پهنای ستونتون کمه و بصورت # میبینید جواب رو
دفت کنید بین عدد و علامت درصد هم فاصله ای نباشه!
سلام امیدوارم جواب منو بدید من عددی را در اکل بصورت درصد دارم که باید عدد دیگری را در آن ضرب کنم ولی متاسفانه به من ارور میئه و ضرب را انجام نمیده با اینکه هر دو عدد را درست نوشتم
درود بر شما
قطعا مشکل از دو داده ای هست که دارید وارد میکنیم. اگر درست و با جنس عدد وارد شده باشه، اصلا مشکلی وجود نخواهد داشت. چون عملیات ضرب پیچیدگی نداره.
جنس اعداد رو چک کنید که متنی نباشه