فرض کنید میخواهید ببینید با نرخهای بهره و دورههای بازپرداخت گوناگون، باید چه مبلغی بپردازید. احتمالاً اعداد ورودی را دستی تغییر میدهید، از برگهها نسخههای تکراری میسازید یا برای بررسی نتیجه، فرمولهایی بسیار طولانی مینویسید.
اما نیازی به این کارها نیست. منوی موردنیاز در زبانه «دادهها» و بخش «پیشبینی» یا «ابزارهای داده» قرار دارد: «تحلیل چهمیشوداگر». بهجای ساختن کتابهای کاری سنگین با دهها برگه مشابه، میتوانید از «جدولهای داده»، «مدیر سناریو» و «هدفیابی» در اکسل کمک بگیرید تا چند مقدار را تغییر دهید و نتیجه این تغییرها را ببینید.
جدولهای داده
ساخت شبکهای با هر اندازه در چند ثانیه
هنگامی که میخواهید نتیجه یک فرمول را برای دامنه گستردهای از مقادیر بسنجید، میتوانید با «جدول داده» همه نتایج ممکن را کنار هم، در یک شبکه واحد و روی یک کاربرگ نمایش دهید. بسته به تعداد ورودیهایی که میخواهید بیازمایید، میتوانید جدولی تکمتغیره یا دومتغیره بسازید.
جدول داده تکمتغیره، فهرستی از مقادیر یک متغیر ورودی را که در امتداد یک ستون یا ردیف چیده شدهاند میآزماید و امکان ارزیابی همزمان یک یا چند فرمول را فراهم میکند. در مقابل، جدول داده دومتغیره، ترکیبهای دو متغیر ورودی را همزمان میآزماید؛ یکی از متغیرها به ورودی ردیف و دیگری به ورودی ستون اختصاص مییابد تا نتیجه یک فرمول ارزیابی شود.
فرض کنید میخواهید با سنجش نرخهای بهره و دورههای بازپرداخت گوناگون، دریابید از عهده کدام طرح وام مسکن برمیآیید. این کار را میتوان با جدول داده تکمتغیره یا دومتغیره انجام داد. تصور کنید در کاربرگ خود، در سلولهای A1 تا B4، مقادیر زیر را دارید:
| ستون A | ستون B | |
|---|---|---|
| 1 | مبلغ وام | 500000 |
| 2 | نرخ بهره | ۵٫۰٪ |
| 3 | مدت وام به ماه | 360 |
| 4 | فرمول | فرمول =PMT(B2/۱۲, B3, -B1) |
فرض کنید سلول B2 که نرخ بهره را در خود دارد، سلول ورودی A باشد و سلول B3 که مدت وام برحسب ماه را نگه میدارد، سلول ورودی B باشد. برای جدول داده تکمتغیره فقط از یک سلول ورودی استفاده میکنید، زیرا تنها یک متغیر را میآزمایید؛ اما هنگام ساخت جدول دومتغیره، هر دو سلول ورودی به کار میروند.
برای ساخت جدول دومتغیره، ردیف بالا و ستون نخست شبکه موردنظر را با مقادیری پر میکنید که با مقادیر سلولهای ورودی تفاوت دارند. برای نمونه، جدولی مانند این خواهید داشت:
| ستون C | ستون D | ستون E | ستون F | |
|---|---|---|---|---|
| 1 | =B4 (فرمول) | 120 | 180 | 240 |
| 2 | ۴٫۰٪ | |||
| 3 | ۴٫۵٪ | |||
| 4 | ۶٫۰٪ |
اما اگر جدول تکمتغیره میسازید، تنها ردیف یا ستون را با مقادیر پر میکنید. برای نمونه، میتوانید مبلغ بازپرداخت را با نرخهای بهره یا دورههای بازپرداخت گوناگون بیازمایید. مزیت جدول تکمتغیره این است که میتوانید چند فرمول متفاوت را نیز همزمان آزمایش کنید:
| ستون C | ستون D | ستون E | |
|---|---|---|---|
| 1 | =B4 (فرمول) | =(B4*B3)-B1 | |
| 2 | ۴٫۰٪ | ||
| 3 | ۴٫۵٪ | ||
| 4 | ۶٫۰٪ |
پس از آنکه شبکه را با یک یا چند متغیر پر کردید، محدوده C1:F4 یا C1:E4 را برگزینید و به «دادهها > پیشبینی > تحلیل چهمیشوداگر > جدول داده» بروید. سلولهای ورودی بالا، یعنی B2 و B3، را به یاد دارید؟ اگر فقط از یک متغیر استفاده میکنید، مثلاً B2 برای نرخ بهره، بسته به اینکه دادهها را در یک ستون یا ردیف چیدهاید، «سلول ورودی ستون» یا «سلول ورودی ردیف» را روی B2 تنظیم کنید و دیگری را خالی بگذارید. برای جدول داده دومتغیره نیز باید هر دو سلول ورودی را متناسب با چیدمان شبکه تعیین کنید.
پس از کلیک روی «تأیید»، اکسل سلولهای خالی شبکه را با نتایج احتمالی پر میکند. شمار متغیرهایی که میآزمایید، ساختار جدول را تعیین میکند و شمار مقادیری که وارد کردهاید نیز مشخص میکند اکسل چه تعداد نتیجه میتواند بسازد. میتوانید تا جایی که فضای کاربرگ اجازه میدهد مقدار وارد کنید و تنها با یک فرمان، شبکهای ۶۰سلولی، ۱۰۰سلولی یا حتی بزرگتر بسازید.
فقط در نظر داشته باشید که جدولهای بزرگ ممکن است پردازش را کند کنند، زیرا «جدولهای داده» با هر تغییر در هر یک از سلولهای کاربرگ، خودکار دوباره محاسبه میشوند. برای رفع این مشکل، از مسیر «پرونده > گزینهها > فرمولها» به بخش «محاسبه کتاب کار» بروید و تنظیم محاسبه را روی «جزئی» بگذارید. بهاینترتیب، محاسبات جدول تنها زمانی بهروزرسانی میشوند که خودتان بخواهید.
مدیر سناریو
برای مهار مدلهای پیچیده چندمتغیره
وقتی تصمیمی به بیش از دو عامل متغیر وابسته باشد، دیگر نمیتوان از «جدولهای داده» استفاده کرد. اینجاست که «مدیر سناریو»ی اکسل به کار میآید. این ابزار مجموعههای جداگانهای از مقادیر متغیر سلولها را ذخیره میکند تا هر زمان خواستید، میان آنها جابهجا شوید.
هر سناریو میتواند تا ۳۲ سلول یا متغیرِ در حال تغییر را در خود جای دهد؛ بنابراین میتوانید هر تعداد سناریوی نامگذاریشده که نیاز دارید، مانند «بهینه»، «منفی» یا «عادی»، بسازید. برای نمونه، میتوانید ورودیهای پایه متفاوتی برای درآمد و هزینه ذخیره کنید، بیآنکه ساختار اصلی فرمولها تغییر کند. فرض کنید کاربرگ خود را به این شکل تنظیم کردهاید:
| ستون الف | ستون ب | |
|---|---|---|
| 1 | درآمد ناخالص | 100000 |
| 2 | بهای تمامشده کالای فروشرفته | 27500 |
| 3 | سود ناخالص | =B1-B2 |
برای ساخت سناریوهای گوناگون، به «دادهها > پیشبینی > تحلیل چهمیشوداگر > مدیر سناریو» بروید و روی «افزودن» کلیک کنید. در کادر نام، «بهینه» یا هر نام دلخواه دیگری را برای سناریو بنویسید. در کادر «سلولهای در حال تغییر»، B1:B2 را وارد کنید و روی «تأیید» بزنید. در پنجره «مقادیر»، برای B1 عدد ۱۵۰۰۰۰ و برای B2 عدد ۳۰۰۰۰ را وارد کنید و سپس «تأیید» را بزنید. با همین روند میتوانید هر تعداد سناریوی دیگری با مقادیر متفاوت بسازید؛ هر سناریو نیز میتواند تا ۳۲ متغیرِ در حال تغییر داشته باشد.
پس از ساخت سناریوها، «بهینه» را برگزینید و روی «نمایش» کلیک کنید. اکسل مقدار سلول B1 را به $۱۵۰,۰۰۰ و مقدار سلول B2 را به $۳۰,۰۰۰ تغییر میدهد و فرمول سود ناخالص در B3 نیز خودکار دوباره محاسبه میشود و به $۱۲۰,۰۰۰ میرسد.
اگر گزارشی برای مقایسه کنار هم میخواهید، در «مدیر سناریو» روی «خلاصه» کلیک و سلول نتیجه، یعنی B3، را انتخاب کنید. اکسل در کاربرگی تازه، «گزارش خلاصه سناریو» را بهصورت مستقل میسازد و همه مقادیر ورودی و سودهای حاصل را کنار یکدیگر نشان میدهد.
هدفیابی
از هدف به ورودی برسید
درحالیکه «جدولهای داده» و «مدیر سناریو» با آغاز از ورودیهای معلوم، رو به جلو حرکت میکنند تا نتایج احتمالی را به دست آورند، «هدفیابی» مسیر را برعکس میپیماید. اگر خروجی مشخصی را که باید از یک فرمول به دست آید بدانید، هدفیابی میتواند مقدار ورودی لازم برای رسیدن به آن هدف را در تحلیل مالی شما محاسبه کند.
هدفیابی به سه ورودی نیاز دارد: «تنظیم سلول» که فرمول شما را در خود دارد؛ «به مقدار» که نتیجه عددی هدف است؛ و «با تغییر سلول» که همان تکسلول ورودی است که اکسل مقدارش را تنظیم میکند. برای نمونه، اگر وامی قسط ماهانه $۱,۲۵۰ داشته باشد اما سقف بودجه شما $۵۰۰ باشد، هدفیابی میتواند سلول مبلغ وام، نرخ بهره یا مدت بازپرداخت را تغییر دهد تا دقیقاً نشان دهد از عهده پرداخت چه مبلغی برمیآیید.
درست مانند «جدولهای داده» و «مدیر سناریو»، ابتدا باید یک محدوده پایه را به این شکل آماده کنید:
| ستون الف | ستون ب | |
|---|---|---|
| 1 | مبلغ وام | 200000 |
| 2 | نرخ بهره سالانه | ۵.۰٪ |
| 3 | مدت وام (ماه) | 360 |
| 4 | قسط ماهانه | =PMT(B2/12, B3, -B1) |
در این حالت، «سلول هدف» B4 و «مقدار مقصد» ۵۰۰ خواهد بود؛ «سلول متغیر» نیز بسته به وضعیت عددی مسئله یا مقداری که ترجیح میدهید تغییر دهید، میتواند B1، B2 یا B3 باشد.
فرضها را دیگر دستی نیازمایید
من از اشتباهم درس گرفتهام و حالا میدانم نادیدهگرفتن منوی «تحلیل چهمیشوداگر» در اکسل، فقط ساعتهای بیشتری را صرف آزمودن دستی مقادیر میکند تا ببینم کدامیک برایم مناسب است. بسته به هدفتان، هرکدام از این سه ابزار میتواند اکسل را از یک ماشینحساب ساده به بستری پویا برای تصمیمگیری تبدیل کند.
منبع: این مطلب ترجمه و بومیسازی مقالهای از MakeUseOf به قلم Adaeze Uche است. مشاهده مقاله اصلی