آموزش ابزار دیتاتیبل data table در اکسل

معرفی ابزار date table

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

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

تفاوت Data Table با Goal Seek

دیتاتیبل data table در اکسل از مجموعه‌ای از مقادیر ورودی برای محاسبه نتایج احتمالی استفاده می‌کنند. در حالی که جستجوی هدف متفاوت است، برای دستیابی به یک هدف از یک متغیر استفاده می‌کند.

تفاوت سناریوها و جداول داده

سناریوها و جداول داده به شما کمک می‌کنند تا مجموعه‌ای از نتایج احتمالی را کشف کنید. تفاوت سناریوها و جداول داده در این است که یک دیتاتیبل data table در اکسل داده نمی‌تواند بیش از دو متغیر را در خود جای دهد (یکی برای سلول ورودی ردیف و دیگری برای سلول ورودی ستون) اگر می‌خواهید بیش از دو متغیر را تجزیه و تحلیل کنید از سناریوها استفاده کنید. یک سناریو می‌تواندحداکثر ۳۲ مقدار مختلف داشته باشد. می‌توانید سناریوهای زیادی را که می‌خواهید ایجاد کنید.

ما در این مطلب آموزشی، نحوه استفاده از این ابزار را با طرح مثالی مرور می کنیم.

نحوه ایجاد جدول از ابزارdate table

یک جدول داده‌ی یک متغیره در اکسل تست یک سری مقادیر برای یک سلول ورودی واحد را نشان می‌دهد و نشان می‌دهد که چگونه این مقادیر در نتیجه‌ی یک فرمول مرتبط تأثیر می‌گذارند.

Data Table در سربرگ Data و در گروه Forecast و در لیست کشویی What-if Analysis قرار دارد.

برای کمک به درک بهتر این ویژگی، ما می‌خواهیم به جای توصیف مراحل عمومی، نمونه‌ی خاصی را دنبال کنیم.

فرض کنید قصد دارید پس‌انداز خود را در بانکی واریز کنید، که 5% سود آن را به صورت ماهیانه پرداخت می‌کند. برای بررسی گزینه‌های مختلف، ماشین حساب بهره‌ی مرکب زیر را ایجاد کرده‌اید که در آن:

• B8 حاوی فرمولFV است که مانده‌ی آخر دوره را محاسبه می‌کند  .
• B2 متغیر مورد نظر برای تست است (سرمایه‌گذاری اولیه).

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

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

• مقادیر متغیر را یا در یک ستون یا در یک ردیف وارد کنید. در این مثال، ما می‌خواهیم یک جدول داده‌ی ستون گرا ایجاد کنیم، بنابراین مقادیر متغیر خود را در یک ستون (D3:D8) تایپ می‌کنیم و حداقل یک ستون خالی را در سمت راست برای نتایج می‌گذاریم.
• فرمول خود را در سلول یک ردیف بالا و یک سلول در سمت راست مقادیر متغیر تایپ کنید (در مثال ما E2). یا، این سلول را به فرمول موجود در مجموعه داده‌ی اصلی خود لینک کنید (اگر تصمیم دارید در آینده فرمول را تغییر دهید، نیاز به به‌روزرسانی تنها یک سلول خواهید داشت). گزینه‌ی دوم را انتخاب می‌کنیم و این فرمول ساده را در E2 وارد می‌کنیم: =B8

نکته: اگر می‌خواهید تأثیر مقادیر متغیر را بر فرمول‌های دیگر که به همان سلول ورودی ارجاع می‌کنند، بررسی کنید، همان‌طور که در این مثال نشان داده شده‌است، فرمول(های) اضافی را در سمت راست فرمول اول وارد کنید.

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

به تب Data > سپس Group Data Tools بروید، روی دکمه‌ی What-If Analysis کلیک کنید و سپس بر روی Data Table… کلیک کنید

تحلیل حساسیت (What-If Analysis) جدول داده‌ها (Data Table) در اکسل، روی جعبه‌ی کلیک کنید Column Input cell در پنجره‌ی محاوره‌ایdate table (زیرا مقادیر سرمایه‌گذاری ما در یک ستون است) و سلول متغیر ارجاع‌شده در فرمول خود را انتخاب می‌کنیم.

انتخاب کنید. که حاوی مقدار اولیه‌ی سرمایه‌گذاری است B3در این مثال

Creating a data table in Excel

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

فرمت عددی مورد نظر را بر روی نتایج (در مثال ما Currency) اعمال کنید، و همه چیز آماده است!

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

جدول داده‌های ردیف‌محور

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

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

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

همان‌طور که در بالا بحث شد یک جدول داده درست کنید، اما مقدار ورودی (B3) را در  وارد کنید.

Row input cell را کلیک کنید.OK و نتیجه‌ی زیر را خواهید گرفت:

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

دیتاتیبل data table در اکسل یک جدول داده‌ی دو-متغیره نشان می‌دهد که چگونه ترکیب‌های مختلف 2 مجموعه از مقادیر متغیر بر نتیجه‌ی فرمول تأثیر می‌گذارد. به عبارت دیگر، نشان می‌دهد که چگونه تغییر دو مقدار ورودی با همان فرمول، خروجی را تغییر می‌دهد.

مراحل ایجاد یک جدول داده‌ی دو متغیره در اکسل اساساً همانند مثال بالا است، با این تفاوت که شما دو بازه (محدوده) از مقادیر ورودی ممکن را وارد می‌کنید، یکی در یک ردیف و دیگری در یک ستون.

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

فرمول خود را در یک سلول خالی وارد کنید یا آن سلول را به فرمول اصلی خود پیوند دهید. اطمینان حاصل کنید که ستون‌های خالی کافی در سمت راست و ردیف‌های خالی در زیر برای جا دادن مقادیر متغیر خود دارید. مانند قبل، سلول E2 را به فرمول FV اصلی که تراز را محاسبه می‌کند، لینک می‌دهیم: =B8

مجموعه‌ای از مقادیر ورودی را زیر فرمول، در همان ستون (مقادیر سرمایه‌گذاری در E3:E8) تایپ کنید.

مجموعه‌ای دیگر از مقادیر متغیر را در سمت راست فرمول، در همان ردیف (عدد سال‌ها در F2:H2) وارد کنید.

در این مرحله، جدول داده‌های دو متغیره‌ی شما باید شبیه به این باشد:

دیتاتیبل data table در اکسل

کل بازه‌ی جدول داده‌ها شامل فرمول، ردیف و ستون مقادیر متغیر و سلول‌هایی را که مقادیر محاسبه شده در آن‌ها ظاهر می‌شود، انتخاب کنید. ما بازه‌ی E2:H8 را انتخاب می‌کنیم.

یک جدول داده به روش قبلی که آشنا شدید ایجاد کنید: تب Data > دکمه‌ی What-If Analysis > سپس Data Table…

در جعبه‌ی Row input cell، برای مقادیر متغیر موجود در ردیف، ارجاع به سلول ورودی را وارد کنید (در این مثال، B6 است که حاوی مقدار سال است).

در جعبه‌ی Column input cell، برای مقادیر متغیر موجود در ستون، ارجاع به سلول ورودی را وارد کنید (در این مثال، B3 است که حاوی مقدار سرمایه‌گذاری اولیه است.

به صورت اختیاری، خروجی‌های مورد نیاز خود را قالب‌بندی کنید(با استفاده از فرمتcurrencyدر مثلا ما) و نتایج را تجزیه و تحلیل کنید:

جدول داده‌ها برای مقایسه‌ی چندین نتیجه

اگر می‌خواهید بیش از یک فرمول را هم‌زمان ارزیابی کنید، جدول داده‌ی خود را مطابق با مثال‌های قبلی بسازید و فرمول(های) اضافی را به این صورت وارد کنید.:

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

در پایین فرمول اول، در صورتی که جدول داده‌ی افقی در ردیف‌ها سازماندهی شده‌باشد

برای کارکرد درست جدول داده‌های “چند فرموله”، همه فرمول‌ها باید به سلول ورودی مشابه مراجعه کنند.

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

در سلول B10، بهره را با این فرمول محاسبه کنید: =B8-B3

داده‌های مبدأ جدول داده‌ها را مانند گذشته مرتب کنید: مقادیر متغیر در D3:D8 و E2 به B8 (فرمول تراز) لینک می‌شوند.

یک ستون دیگر به مجموعه جدول داده‌ها اضافه کنید (ستون F) و F2 را به B10 (فرمول بهره) لینک کنید:

بازه‌ی جدول داده‌های گسترش‌یافته را انتخاب کنید (D2:F8).

با کلیک بر روی Data > What-If Analysis > Table Data کادر محاوره‌ای Data Table را باز کنید

در جعبه‌ی Column Input cell، سلول ورودی (B3) را ارائه کنید و بر روی OK کلیک کنید.

این‌جا، شما می‌توانید اثر مقادیر متغیر خود را در هر دو فرمول مشاهده کنید:

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