آنالیز حساسیت در اکسل – آشنایی با انواع ابزارهای تحلیل در اکسل

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

همچنین در صورتی که علاقمند به افزایش سطح مهارت و دانش خود در زمینه کار با نرم افزار اکسل هستید میتوانید به مجموعه آموزش اکسل فرادرس مراجعه کنید؛ برای دریافت این مجموعه آموزشی پرطرفدار کافیست روی لینک زیر کلیک کنید:

• مجموعه آموزش اکسل — کلیک کنید

مایکروسافت اکسل؛ حلال مشکلات محاسباتی!

اکسل یک برنامه نرم‌افزاری طراحی شده توسط مایکروسافت می‌باشد که از صفحات گسترده جهت مدیریت و سازماندهی اعداد و داده‌های آماری با کمک فرمول‌ها و توابع بهره می‌برد. این نرم‌افزار یکی از پرطرفدارترین برنامه‌های تولیدی توسط مجموعه مایکروسافت آفیس بوده و در سراسر جهان مورد استفاده قرار می‌گیرد. در واقع یکی از کاربردهای اصلی نرم‌افزار اکسل، تجزیه و تحلیل داده‌های آماری و امور مالی است.

کاربردهای اصلی مایکروسافت اکسل

• ورود و ذخیره اطلاعات
• مدیریت داده‌های آماری
• حسابداری
• آنالیز حساسیت در اکسل
• تحلیل امور مالی
• تهیه چارت و گراف
• برنامه نویسی
• مدیریت زمان و مدیریت وظایف و امور قابل انجام
• مدل‌سازی مالی
• مدیریت ارتباط با مشتری
• و تقریباً هر چیزی که به سازماندهی و نظم نیاز داشته باشد!

کاربردهای حسابداری و مالی اکسل

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

اکسل دارای مزایای بسیار زیادی است که حتی نمی‌توان تک تک آن‌ها را برشمرد! از برخی مزایای مایکروسافت اکسل می‌توان به مواردی همچون سهولت یادگیری، سابقه زیاد در این حوزه و درک نیاز کاربران، محیط ساده و کاربر پسند، وجود راهنما یا wizard برای اجرای عملیات پیچیده، وجود جداول بزرگ با تعداد سلول‌های زیاد و همچنین وب‌سایت‌های راهنما و انجمن‌های کاربران فعال اشاره نمود. یادگیری مایکروسافت اکسل و همچنین تحلیل حساسیت در اکسل برای تمامی افرادی که به نوعی در زمینه کسب و کار، دانش‌آموزی، دانشجویی یا تدریس مشغول به فعالیت می‌باشند مفید و کاربردی بوده و در واقع فراگیری این دانش به طور قطع خالی از لطف نخواهد بود!

همچنین لازم به ذکر است که اکسل می‌تواند میزان درک مطالب را با کمک یک نمایش تصویری از اطلاعات افزایش دهد. نمایش بصری مجموعه داده‌ها این امکان را برای تحلیلگران فراهم می‌سازد تا بتوانند به راحتی اقدام به تجزیه و تحلیل کنند. در واقع اکسل دشواری رسم داده‌ها را عملاً کاهش داده و وسیله مناسبی برای تفسیر داده‌ها محسوب می‌شود.

• آموزش داشبوردهای مدیریتی اکسل Excel
• آموزش رسم نمودارهای پیشرفته در اکسل

ابزارهای مختلف آنالیز حساسیت در اکسل

منظور از تحلیل حساسیت در اکسل (What-If Analysis)، فرآیند تغییر مقادیر در سلول‌ها برای مشاهده تاثیر تغییرات مذکور بر نتیجه فرمول‌های ورک‌شیت می‌باشد. برای محسابه تحلیل حساسیت در اکسل سه ابزار مختلف از جمله سناریوها (Scenarios)، جستجوی هدف (Goal Seek) و جدول داده‌ها (Data Tables) تعریف شده است. سناریوها و جدول داده‌ها وظیفه دریافت مقادیر و ارائه نتایج احتمالی را بر عهده دارند. شایان ذکر است که جدول داده‌ها تنها با یک یا دو متغیر کار می‌کند، اما قابلیت پذیرش مقادیر مختلف برای متغیرهای مذکور را دارا می‌باشد. برخلاف جدول داده‌ها، سناریوها می‌توانند چندین متغیر تا سقف ۳۲ مقدار را پوشش دهند. و اما سومین ابزار، یعنی جستجوی هدف عملکردی متفاوت از سناریوها و جدول‌ داده‌ها را ارائه می‌نماید. Goal Seek موظف است مقادیر ورودی احتمالی که نتیجه را محاسبه می‌نماید را تعیین کند.

علاوه بر سه ابزار مذکور، نصب افزونه‌هایی همچون add-ins و Solver نیز سبب سهولت کار با آنالیز حساسیت در اکسل خواهد شد. لازم به ذکر است که افزونه Solver عملکردی مشابه با Goal Seek داشته اما قادر است متغیرهای بیشتری را در خود جای دهد. ناگفته نماند که امکان ایجاد پیش‌بینی‌ها با کمک fill handle و سایر دستوراتی که در نرم‌افزار اکسل تعبیه شده است نیز وجود دارد. در ادامه با ابزارهای What-If Analysis بیشتر آشنا خواهید شد.

معرفی ابزار Scenario Manager

Scenario Manager زمانی که قصد دارید نتیجه نهایی تغییر چند متغیر را محاسبه کنید، گزینه مناسبی می‌باشد. به عنوان مثال فرض کنید مجموعه داده‌های تصویر زیر را دارید و می‌خواهید میزان سود را محاسبه نمایید:

همانطور که مشاهده می‌کنید، ارزش سود به ۳ متغیر مقدار فروش، قیمت هر واحد و هزینه متغیر برای واحد وابسته است. در این جا برای محاسبه سود فرمول =B2*B3-B4-B5*B2 استفاده شد. همانطور که پیش‌تر ذکر کردیم، در صورت داشتن یک یا دو متغیر می‌توان از جدول داده‌ها (Data Tables) استفاده نمود. اما چنانچه تعداد متغیرها ۳ یا از ۳ بیشتر بود، در این حالت باید برای محاسبه نتیجه از Scenario Manager استفاده نمود.

نحوه تنظیم Scenario Manager در آنالیز حساسیت در اکسل به چه صورت است؟

برای تنظیم سناریوها در اکسل مسیر زیر را طی کنید:

تب Data > گروه Data Tools > گزینه What-If Analysis > Scenario Manager

سپس کادری برای شما ظاهر می‌شود که می‌بایست در آن گزینه Add را انتخاب نموده و بعد کادر گفت‌وگویی باز می‌شود که در آن می‌بایست Scenario Values را پر کنید.

• در قسمت Scenario name نام سناریو را مشخص خواهید کرد.
• در قسمت Changing cells سلول‌هایی که مقادیر آن‌ها در هر سناریو تغییر می‌کند را انتخاب خواهید کرد.
• و در نهایت در قسمت Comment به صورت اختیاری می‌توانید هر توضیحی که مایل هستید را بنویسید.

سپس بر روی OK کلیک کنید، پس از کلیک بر روی OK کادر جدیدی برای تعیین Scenario Values ظاهر خواهد شد که در آن باید مقادیر داده‌ها را وارد نمایید.

نکته: برای ایجاد یک سناریوی دیگر در آنالیز حساسیت در اکسل می‌توانید مجدداً بر روی گزینه Add کلیک نمایید.

ناگفته نماند که در این مثال Worst Case یا بدترین حالت در نظر گرفته شده است. می‌توان برای گزینه‌های دیگر همچون Realistic (واقع‌بینانه) و Best Case (بهترین حالت) نیز سناریو افزود. موارد ذکر شده در کادر Scenarios قابل مشاهده و انتخاب هستند.

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

• آموزش استفاده از توابع و فرمول نویسی در اکسل Excel — کلیک کنید

نحوه ادغام سناریوها و تهیه خلاصه از آن‌ها

چنانچه در سایر ورک‌شیت‌ها سناریوهای دیگری را تعریف کرده‌اید و قصد دارید آن‌ها را با یک‌دیگر ادغام کنید، می‌توانید از گزینه Merge برای انجام این کار استفاده کرده و تمامی سناریوهای تعریف شده را مشاهده و در صورت نیاز ادغام نمایید. علاوه بر ادغام امکان تهیه خلاصه از تمامی سناریوهای ایجاد شده نیز فراهم است. برای ایجاد خلاصه از تمامی سناریوهای تعریف شده تنها کافیست بر روی دکمه Summary که در کادر گفت‌وگوی Scenario Manager موجود می‌باشد، کلیک نمایید. در کادر گفت‌وگوی ظاهر شده Scenario Summary می‌توانید یکی از موارد Scenario Summary یا Scenario Pivot Table report را برگزینید. همچنین در این کادر می‌بایست سلولی که نتیجه خروجی محاسبات مذکور در آن پیاده می‌شود را در Result cells تعیین نمایید. در نهایت با کلیک بر روی OK صفحه جدیدی برای شما باز می‌شود که نمایشگر خلاصه سناریوهای تعریف شده توسط شما است.

زمانی که به آنالیز حساسیت در اکسل نیاز دارید، ابزار Scenario manager بهترین انتخاب برای شما محسوب می‌شود. با کمک ابزار مذکور به راحتی می‌توانید سناریوهای مختلف برای متغیرهای خود ایجاد کرده و در نهایت یک گزارش خلاصه از آن‌ها جهت مقایسه تهیه نمایید.

معرفی ابزار Data Table

یکی از ابزارهای نرم‌افزار مایکروسافت اکسل برای تحلیل حساسیت، جدول داده‌ها یا Data Table است. نرم‌افزار مذکور این امکان را فراهم می‌سازد تا کاربر بتواند مقادیر مختلف ورودی را برای فرمول‌ها امتحان کرده و تاثیر اعمال تغییرات در مقادیر مذکور را روی خروجی مشاهده کند.

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

نحوه ایجاد جدول داده های تک متغیره در آنالیز حساسیت در اکسل

جدول داده تک متغیره در اکسل نمایشگر یک سری مقادیر برای یک سلول ورودی واحد و چگونگی تغییرات و تاثیر آن‌ها در نتیجه یک فرمول می‌باشد. برای روشن‌تر شدن موضوع مثال زیر را دنبال کنید:

فردی در بانک پس‌انداز داشته که به صورت ماهانه ۵٪ سود به ازای سپرده به حساب بانکی او واریز می‌شود. برای بررسی گزینه‌های مختلف می‌توان مطابق تصویر زیر عمل نمود:

لازم به ذکر است که B8 از طریق فرمول FV جهت به دست آمدن مانده آخر دوره محاسبه شده است.

حال برای این که بدانیم پس‌انداز فرد در طول ۵ سال بر اساس سرمایه‌گذاری اولیه به چه صورت از $1,000 تا $6,000 متغیر خواهد بود، می‌بایست یک آنالیز ساده حساسیت انجام دهیم.

مراحل ایجاد جدول داده‌های تک متغیره ستون‌گرا به ترتیب زیر است:

۱. مقادیر متغیر مورد نظر را در یک ستون و یا در یک ردیف وارد کنید. در مثال فوق برای ایجاد جدول داده ستون‌گرا باید مقادیر متغیر را در یک ستون (D3:D8) تایپ کرده و حداقل یک ستون را در سمت راست برای نتایج خالی بگذاریم.

۲. فرمول مورد نظر خود را در سلول یک ردیف بالا و یک سلول در سمت راستِ مقادیر متغیر وارد نموده یا سلول را به فرمول موجود در مجموعه داده اصلی لینک کنید. (مطابق مثال E2)

۳. محدوده جدول داده‌ها که شامل فرمول، سلول‌های مقادیر متغیر و سلول‌های خالی برای نتایج می‌شود را انتخاب کنید (مطابق مثال D2:E8)

۴. سپس برای ایجاد جدول داده ها از طریق مسیر Data > Group Data Tools > What-If Analysis رفته و بر روی Data Table کلیک کنید.

۵. در نهایت کادر جدول داده‌ها ظاهر می‌شود که در آن می‌بایست روی جعبه Column Input cell کلیک کرده و سلول متغیر ارجاع شده در فرمول را برگزینید. (در مثال B3)

۶. بعد روی گزینه OK کلیک کنید، پس از کلیک بر روی گزینه OK آنالیز حساسیت در اکسل بلافاصله سلول‌های خالی را با نتایج مربوط به مقدار متغیر در همان ردیف محاسبه خواهد کرد.

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

۱. ممکن است کاربری ترجیح دهد که طرح جدول داده‌های او به صورت افقی باشد، از این رو برای ایجاد طرح افقی در وهله اول می‌بایست مقادیر متغیر مذکور را در یک ردیف وارد نمایید. لازم به ذکر است که وجود حداقل یک ستون خالی به سمت چپ برای فرمول و یک ردیف خالی در زیر برای نتایج لازم می‌باشد. برای مثال فوق مقادیر متغیر در سلول‌های F3:J3 وارد شده است.

۲. فرمول را به طوری که یک ستون در سمت چپ اولین مقدار متغیر و یک سلول زیر آن باشد، در سلول وارد نمایید. (مثال E4)

۳. سپس یک جدول داده ساخته و مقدار ورودی B3 را این بار در جعبه Row input cell وارد کنید. در نهایت بر روی گزینه OK کلیک کرده و نتیجه زیر را دریافت نمایید.

سخن پایانی در مورد آنالیز حساسیت در اکسل

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

• آموزش پیاده سازی هوش تجاری در اکسل Excel — کلیک کنید