سبد خرید
0

سبد خرید شما خالی است.

Goal Seek، ابزاری برای تحلیل داده

ابزار Goal seek
۵/۵ - (۱۲ امتیاز)

خیلی وقتها پیش میاد که میخوایم خروجی یک تابع رو با تغییر بعضی متغیرهاش تعیین کنیم. یعنی مثلا در یک تابع محاسبه اقساط، بگیم که تعداد اقساط چندتا باشه تا میزان قسط برسه به میزان دلخواهمون.
برای این کار اکسل ابزاری رو در اختیار ما قرار میده که میتونیم خروجی تابع رو به مقدار دلخواه برسونیم. ابزار Goal seek از گروه ابزارهای تحلیل داده What If Analysis هست. نحوه عملکرد این ابزار رو با شرح یک مثال توضیح میدم:

فرض کنید فرمولی نوشته شده برای میزان خالص دریافتی حقوق یک کارمند. میخوایم ببینیم برای رسیدن به دریافتی ۱۰ میلیون تومان در ماه، پایه چه عددی باید باشه؟

نکته:
علت انتخاب این مثال، مرور Nested If و کدنویسی در فرمت سل و مشاهده نحوه محاسبه مالیات حقوق بوده. برای اینکه بتونید فرمول رو مشاهده کنید و منطقش رو درک کنید، فایل این آموزش در انتهای صفحه قرار داده شده است.

 

برای محاسبه مالیات حقوق از If تودرتو استفاده کردیم. (سقف معافیت مالیاتی (سلول G2) قابل تغییر هست)

بیمه هم که بصورت ۷درصد حقوق پایه به عنوان سهم کارمند محاسبه شده.

 محاسبه میزان مالیات حقوق، بیمه و میزان دریافتی

شکل ۱- ابزار goal seek – محاسبه میزان مالیات حقوق، بیمه و میزان دریافتی

  حالا فرض کنید میخواهیم خالص دریافتی رو برسونیم به ۵ میلیون تومان. میخواهیم ببینیم حقوق پایه چقدر باید تغییر کنه تا میزان دریافتی به عدد دلخواه برسه.

برای این کار، از تب Data/ What If Analysis گزینه Goal Seek رو میزنیم.

پنجره ای مطابق شکل ۲ باز میشه.

ابزار goal seek - پنجره Goal Seek

شکل ۲- ابزار goal seek – پنجره Goal Seek

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

Set Cell: سلولی که حاوی فرمولی است که میخواهیم نتیجه اونو به عدد دلخواه برسونیم. (از سلول گرفته میشه)

To Value: مقدار مورد نظر که میخواهیم فرمول به اون عدد برسه. (بصورت دستی تایپ میشه)

By Changing Cell:  سلول متغیر که با تغییر اون، به عدد دلخواه میخواهیم برسیم. (از سلول گرفته میشه)

در این مثال، سلول ها رو مطابق شکل ۳ انتخاب میکنیم. سلول حقوق دریافتی میشه Set Cell، سلول To Value میشه ۵ میلیون تومان و سلول حقوق پایه میشه By Changing Cell.

تغییر خروجی فرمول با Goal Seek

شکل ۳- ابزار goal seek – تغییر خروجی فرمول با Goal Seek

بعد از زدن Ok اکسل محاسبات رو انجام میده و مطابق شکل ۴ میبینیم که با تغییر حقوق پایه به ۵،۷۹۵،۱۸۱ میزان حقوق دریافتی به ۵ میلیون تومان خواهد رسید.

توجه کنید که چون میزان مایلات و بیمه تابعی از حقوق پایه هستن، اثرات رو روی نتیجه نهایی میذارن.

ابزار goal seek - خروجی ابزار Goal Seek

شکل ۴- ابزار goal seek – خروجی ابزار Goal Seek

نکته:
در صورتی که عدد مورد نظر منطقی نباشه، Goal Seek به جواب نمیرسه. مثلا یک عددی به فرمول بدیم، که هیچ وقت با تغییر متغیر خاص، دست یافتنی نباشه.

 

ابزارهای تحلیل داده یا همون What If Analysis ابزارهای خیلی مفید و کاربردی هستن که در این مثال به Goal Seek اشاره کردیم. دو ابزار Table و Scenario در آموزش های بعد ارائه خواهد شد.

دانلود فایل اکسل آموزش استفاده از ابزار goal seek

برای دانلود فایل اکسلی که تو این آموزش ایجاد شده روی لینک زیر کلیک کنید:

با عضویت در سایت به صورت مستقیم دانلود کنید

  • ارسال به ایمیل

Des

دریافت فایلبرای دانلود فایل کلیک کنید

کلیدواژه : ابزار Goal Seekمتوسط

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • ملي 11 اردیبهشت 1398 / 4:41 ب.ظ

    سلام جنس اعداد متنی نیست یکی از اعداد بصورت Prosent نوشته شده

    • حسنا خاکزاد 11 اردیبهشت 1398 / 6:23 ب.ظ

      مشکلی نباید باشه
      منم همین کار و میکنم و جواب میگیرم. شاید پهنای ستونتون کمه و بصورت # میبینید جواب رو

      دفت کنید بین عدد و علامت درصد هم فاصله ای نباشه!

  • ملی 10 اردیبهشت 1398 / 12:44 ب.ظ

    سلام امیدوارم جواب منو بدید من عددی را در اکل بصورت درصد دارم که باید عدد دیگری را در آن ضرب کنم ولی متاسفانه به من ارور میئه و ضرب را انجام نمیده با اینکه هر دو عدد را درست نوشتم

    • حسنا خاکزاد 11 اردیبهشت 1398 / 11:26 ق.ظ

      درود بر شما
      قطعا مشکل از دو داده ای هست که دارید وارد میکنیم. اگر درست و با جنس عدد وارد شده باشه، اصلا مشکلی وجود نخواهد داشت. چون عملیات ضرب پیچیدگی نداره.
      جنس اعداد رو چک کنید که متنی نباشه

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان