شما میتوانید برای اطلاعات بیشتر در زمینه what if analysis در اکسل و انواع مختلف ابزارهای این تحلیل در اکسل به مطالب زیر مراجعه کنید:
جدول داده (data table) در اکسل چیست؟
Data table در اکسل یکی از ابزارهای تحلیل what if است که به شما امکان میدهد مقادیر ورودی مختلفی را برای یک فرمول استفاده کنید و تاثیر تغییرات ایجاد شده را مشاهده کنید.
به خصوص زمانی که یک فرمول به مقادیر مختلفی وابسته است جدول های داده در اکسل میتواند کمک قابل توجهی به کاربران اکسل کند به این صورت که شما میتوانید ترکیبات مختلفی از ورودی ها را بر روی فرمول تست کنید و نتیجه را مشاهده کنید.
در حال حاضر شما میتوانید از جدول های یک متغیره و دو متغیره در تحلیل what if استفاده کنید. با وجود اینکه سلول های ورودی این جدول ها محدود هستند ولی شما میتوانید بدون محدودیت متغیرهای مورد نظر خود را تست کنید.
نکته: در طول این مطلب همواره به یاد داشته باشید که جدول های داده (data table) با جدول های اکسل (excel table ) که شما با استفاده از آن داده های خود را سازماندهی میکنید، متفاوت است. کارکردها و خاصیت ها و روش استفاده از این دو نوع جدول در اکسل بسیار متفاوت است.
نحوه ایجاد یک جدول داده یک متغیره در اکسل
شما میتوانید با استفاده از جدول های داده تک متغیره در اکسل مجموعه ای از مقادیر را برای یک سلول ورودی تست کنید و نشان دهید که این مقادیر زمانی که بعنوان ورودی فرمول قرار میگیرند چه تغییراتی را در نتیجه ایجاد میکنند.
برای اینکه بهتر این مفهوم را درک کنید، با استفاده از یک مثال روش استفاده از این جدول ها را نشان میدهیم.
فرض کنید میخواهید در یک بانک حساب پس انداز افتتاح کنید. این بانک 5 درصد سود سالانه را به صورت ماهانه و مرکب پرداخت میکند. برای بررسی گزینه های مختلف شما میتوانید جدول زیر را تشکیل دهید.
- سلول B8 حاوی یک فرمول است که مقدار ارزش آتی تراز نهایی را محاسبه میکند.
- B3 متغیری است که شما میخواهید تست کنید(مقدار اولیه سرمایه گذاری)
حال شما میتوانید با استفاده از تحلیل what if در اکسل بررسی کنید که با توجه به مقدار وجهی که در حساب پس انداز قرار میدهید ( از 1000 تا 6000 دلار) ، مقدار پول شما پس از 5 سال چه مقدار خواهد بود.
برای ایجاد یک جدول داده تک متغیره گام های زیر را طی کنید:
- مقادیر متغیر را در یک ستون یا در طول یک ردیف وارد کنید. در این مثال، ما داده های خود را به صورت ستونی سازماندهی میکنیم. بنابراین داده های متغیر خود را در سلول های D3:D8 قرار میدهیم.
- یک ردیف بالاتر و در سلول سمت راست مقادیر متغیر، فرمول خود را تایپ کنید( در این مثال سلول E2). و یا در صورتی که از یک پایگاه داده استفاده میکنید، این سلول را مساوی سلولی که فرمول را در آن تایپ کرده اید قرار دهید. ما از روش دوم استفاده میکنیم و فرمول ساده
b8= را در سلول E2 تایپ میکنیم.
- محدوده جدول داده شامل فرمول، سلول های مقادیر متغیر، و سلول های خالی که نتیجه در آن قرار میگیرد را انتخاب کنید(D2:E8)
- در قسمت data tools از تب data دکمه What-If Analysis را انتخاب کنید و گزینه data table را کلیک کنید.
- در پنجره data table، در باکس Column Input cell کلیک کنید، زیرا داده های متغیر ما به صورت ستونی هستند. سلول حاوی متغیر ورودی مورد نظر را انتخاب کنید. در این مطلب ما سلول B3 را که شامل مقدار اولیه سرمایه گذاری است انتخاب میکنیم.
- Ok را کلیک کنید. اکسل به صورت اتوماتیک نتیجه را بر اساس ورودی انتخاب شده در سلول خالی مربوط به همان ردیف قرار میدهد. حال میتوانید بر روی نتیجه ظاهر شده فرمت مورد نظر خود را انتخاب کنید.
حال شما میتوانید با مشاهده جدول داده یک متغیره خود، مقدار تراز خود را پس از 5 سال مشاهده کنید و در مورد مقدار پس انداز فعلی تصمیم گیری کنید.
جدول های داده ردیفی
در مثال بالا همانطور که مشاهده کردید داده های خود را به صورت ستونی یا عمودی سازماندهی کردیم . اما شما میتوانید داده های خود را به صورت افقی نیز ساماندهی کنید. برای انجام این کار:
- مقادیر متغیر خود را در یک ردیف تایپ کنید، حداقل یک ستون خالی در سمت راست ( برای فرمول) و یک ردیف خالی در پایین( برای نتایج) قرار دهید. در این مثال، ما مقادیر متغیر را در سلول های F3:J3 قرار میدهیم.
- فرمول را در سلولی که در یک ستون سمت چپ و یک ردیف پایین( E4) اولین مقدار متغیر شما قرار دارد تایپ کنید.
- همانطور که در مثال قبل توضیح داده شد یک جدول داده ایجاد کنید با این تفاوت که مقدار ورودی(سلول B3) را در باکس Row input cellوارد کنید.
- OK را انتخاب کنید تا نتایج به شکل زیر ظاهر شود.
نحوه ایجاد یک جدول داده دو متغیره در اکسل
زمانی که میخواهیم مقدار تاثیرگذاری دو مجموعه از متغیرها را بر روی فرمول خود متوجه شویم از جدول های داده دو متغیره استفاده میکنیم. به عبارت دیگر این جدول نشان میدهد تغییر در دو ورودی یک فرمول چگونه باعث تغییر در نتیجه میشود.
مراحل ایجاد یک جدول دو متغیره داده شبیه به جدول تک متغیره است با این تفاوت که در اینجا دو محدوده از مقادیر ورودی را داریم که یک مجموعه را به صورت ستونی و یک مجموعه را به صورت ردیفی قرار میدهیم.
در اینجا نیز از همان مثال سرمایه گذاری استفاده شده در قسمت قبل استفاده میکنیم با این تفاوت که در اینجا علاوه بر تغییر مقدار اولیه سرمایه گذاری تعداد سالهای سرمایه گذاری را نیز تغییر میدهیم. برای انجام این کار، داده های خود را به صورت زیر سازماندهی کنید:
- فرمول خود را در یک سلول خالی تایپ کنید. یا سلول مورد نظر را به فرمول اصلی خود متصل کنید. حتما می بایست ستون های خالی در سمت راست و ردیف های خالی در پایین این سلول وجود داشته باشد تا مقادیر متغیر خود را در آن تایپ کنیم. شبیه به قبل، ما سلول E2 را به فرمول FV که در سلول B8 قرار دارد و مقدار تراز نهایی را محاسبه میکند متصل میکنیم. B8=
- یک مجموعه از مقادیر ورودی را در زیر فرمول و در ستون مشابه تایپ کنید( مقادیر سرمایه گذاری در سلول های E3:E8)
- دیگر مجموعه داده را در سمت راست فرمول و در ردیف مشابه تایپ کنید( تعداد سال در سلول های F2:H2
حال جدول داده دو متغیره شما می بایست شبیه به شکل زیر باشد:
- کل محدوده جدول داده را شامل فرمول، ردیف و ستون های متغیر، و سلول هایی که مقدار محاسبه شده در آن ها قرار میگیرد، انتخاب کنید(E2:H8)
- شبیه به مثال قبل یک جدول داده ایجاد کنید. Data tab > What-If Analysis > Data Table
- در باکس Row input cell ، آدرس سلولی را که متغیرهای ورودی موجود در ردیف، در آن قرار می گیرد، وارد کنید.( در این مثال سلول B6)
- در باکس Column input cell آدرس سلولی که متغیرهای ورودی موجود در ستون، می بایست در آن قرار گیرند وارد کنید. ( در این مثال سلول B3)
- OK را انتخاب کنید.
حال میتوانید فرمت مورد نظر را بر روی نتایج اعمال کنید. برای مثال در اینجا میتوانید از فرمت currency استفاده کنید.
استفاده از جدول داده در اکسل برای مقایسه نتایج مختلف
در صورتی که میخواهید به صورت همزمان بیش از یک فرمول را مورد بررسی قرار دهید، جدول داده خود را شبیه به مثال قبل طراحی کنید و فرمول یا فرمول های اضافی را به شکل زیر وارد کنید:
- زمانی که جدول داده شما عمودی است و داده های شما در ستون ها قرار گرفته اند، در سمت راست اولین فرمول وارد کنید.
- زمانی که داده های شما افقی است در زیر اولین فرمول وارد کنید.
برای اینکه جدول های چند فرمولی به درستی کار کنند، تمام فرمول های می بایست به یک سلول ورودی متصل باشند.
برای روشن تر شدن موضوع ، فرض کنید میخواهیم یک فرمول جدید که مقدار بهره را محاسبه میکند را به جدول تک متغیره خود اضافه کنیم. برای انجام این کار:
- در سلول B10 مقدار بهره را با استفاده از این فرمول محاسبه کنید: B8-B3=
- شبیه به کاری که قبلا انجام دادیم، داده های منبع جدول داده را سازماندهی کنید( مقادیر متغیر را در سلول های D3:D8 قرار دهید و سلول E2 را به B8 ( فرمول محاسبه تراز نهایی) متصل کنید. )
- یک ستون دیگر به محدوده جدول داده اضافه کنید( ستون F) و F2 را به B10 ( فرمول بهره) متصل کنید
- محدوده گسترش یافته جدول داده ر انتخاب کنید.
- با انتخاب What-If Analysis > Data Table در تب data باکس مربوط به data table را باز کنید.
- B3 را در باکس Column Input cell قرار دهید و OK را انتخاب کنید.
همانطور که در تصویر مشاهده میکنید شما میتوانید تاثیر متغیرهای خود را بر روی هر دو فرمول خود به صورت همزمان مشاهده کنید.
جدول های داده در اکسل – سه چیزی که می بایست بدانید
برای اینکه در استفاده از جدول های داده اکسل به مشکل برنخورید، همیشه سه نکته را در ذهن داشته باشید:
الف. همیشه می بایست سلول های ورودی و جدول داده در یک شیت قرار داشته باشند.
ب. اکسل همیشه برای محاسبه نتایج جدول های داده از تابع TABLE(row_input_cell, colum_input_cell) استفاده میکند:
- در جدول های تک متغیره بسته به اینکه از حالت ستونی یا ردیفی استفاده میکنید یکی از پارامترهای این تابع حذف میشوند. برای مثال در حالت افقی، پارامتر row_input_cell حذف میگردد.
- در حالت دو متغیره هر دو پارامتر این فرمول موجود هستند.
تابع جدول داده در اکسل به صورت یک فرمول آرایه ای وارد میشود. با اینال این فرمول یک فرمول آرایه ای معمولی نیست. به همین دلیل شما نمیتوانید این فرمول را در قسمت formula bar تایپ کنید یا آن را اصلاح کنید.
از آنجا که برای جدول داده از یک فرمول آرایه ای استفاده میشود، سلول های حاوی نتیجه را نمیتوان به صورت جداگانه اصلاح کرد. شما فقط میتوانید کل آرایه را اصلاح یا حذف کنید که در زیر نحوه انجام این کار آورده شده است:
نحوه حذف یک جدول داده در اکسل
از آنجایی که فرمول جدول داده در اکسل یک فرمول آرایه ای است در صورتی که تلاش کنید مقادیر را به صورت جداگانه اصلاح کنید با پیغام “Cannot change part of a data table” مواجه خواهید شد. با اینحال شما میتوانید به سادگی کل آرایه را به صورت همزمان حذف کنید.
برای انجام این کار، کل جدول داده یا فقط سلول های نشان دهنده نتیجه را انتخاب کنید، و کلید delete را فشار دهید.
نحوه اصلاح نتایج جدول داده
بنا به دلیل گفته شده، شما نمیتوانید هرکدام از سلول های جدول داده را به صورت جداگانه اصلاح کنید، با اینحال شما میتوانید به شیوه زیر تمام مقادیر را تغییر دهید:
- تمام سلول های نتیجه را انتخاب کنید.
- فرمول جدول را که در قسمت Formula bar نوشته شده است، حذف کنید
- مقدار مورد نظر را به جای آن تایپ کنید و Ctrl+enter را فشار دهید.
همانطور که مشاهده میکنید مقدار مورد نظر در تمام سلول ها جایگزین شد.
زمانی که فرمول جدول داده را حذف کردید، محدوده داده ها تبدیل به یک محدوده معمولی میشود و شما میتوانید هر تغییری که میخواهید اعمال کنید.
نحوه محاسبه مجدد جدول داده به صورت دستی
در صورتی که جدول داده شما بسیار بزرگ شده و تعداد زیاد متغیرها و فرمول ها باعث شده سرعت صفحه گسترده شما کاهش یابد، شما میتوانید محاسبات اتوماتیک را در آن جدول یا تمام جداول داده غیر فعال کنید.
برای انجام این کار، به قسمت Formulas tab > Calculation مراجعه کنید، گزینه Calculation Options را انتخاب کنید و سپس Automatic Except Data Tablesرا کلیک کنید. 
با این کار محاسبه اتوماتیک جداول داده غیر فعال میشود و سرعت صفحه گسترده افزایش پیدا میکند. برای محاسبه مجدد دستی جدول داده خود، سلول های نتیجه را انتخاب کنید( سلول هایی که حاوی فرمول های TABLE() هستند) و کلید F9 را فشار دهید.
در این مطلب یکی دیگر از قسمت های تجزیه و تحلیل what if در اکسل را بررسی کردیم. تحلیل what if analysis در اکسل و انواع مختلف ابزارهای آن را در تصمیم های شغلی و عمومی خود استفاده کنید، مطمئن باشید که تصمیم های قدرتمندتری خواهید گرفت.
با سلام
ممنون از آموزش های جامع و عالییییتون
من ۹ فایل اکسل جداگانه دارم، همه فایلها به صورت جدول هستن، و جمع ستونها در آخرین سطر آورده میشود.
خواستم با پاور کویری که جدولها خوانده میشود دستور فرمول هم اضافه شود
خواهش میکنم. بهتر سوالات مربوط به پاور کوئری رو در صفحه زیر مطرح کنید:
پاور کوئری در اکسل
قسمت آخر که گفتید دستور فرمول هم اضافه شود منظروتون متوجه نشدم.