نحوه ایجاد و استفاده از جدول داده (Data Table) در اکسل
این آموزش نحوه‌ی استفاده از جداول داده‌ها (Data Table) برای تحلیل حساسیت (What-If analysis) در اکسل را نشان می‌دهد. می‌آموزید که چگونه می‌توانید یک جدول یک-متغیره و دو-متغیره ایجاد کنید تا اثرات یک یا دو مقدار ورودی را روی فرمول خود ببینید، و چگونه می‌توانید یک جدول داده برای ارزیابی هم‌زمان چندین فرمول تنظیم کنید.
شما یک فرمول پیچیده‌ی وابسته به متغیرهای مختلف ایجاد کرده‌اید و می‌خواهید بدانید که تغییر این ورودی‌ها چگونه نتایج را تغییر می‌دهد. به جای آزمایش هر متغیر به صورت جداگانه، یک جدول داده‌ی تحلیل حساسیت ایجاد کنید و با یک نگاه سریع تمام نتایج ممکن را رصد کنید!
جدول داده‌ها (Data Table) در اکسل چیست؟
در مایکروسافت اکسل، جدول داده‌ها یکی از ابزارهای تحلیل حساسیت (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… کلیک کنید
- در پنجره‌ی محاوره‌ای Data Table، روی جعبه‌ی Column Input cell کلیک کنید (زیرا مقادیر سرمایه‌گذاری ما در یک ستون است) و سلول متغیر ارجاع‌شده در فرمول خود را انتخاب کنید. در این مثال B3 را انتخاب می‌کنیم که حاوی مقدار اولیه‌ی سرمایه‌گذاری است.
- OK را کلیک کنید، و اکسل بلافاصله سلولهای خالی را با نتایج مربوط به مقدار متغیر در همان ردیف حساب می‌کند.
- فرمت عددی مورد نظر را بر روی نتایج (در مثال ما Currency) اعمال کنید، و همه چیز آماده است!
اکنون می‌توانید نگاهی گذرا به جدول داده‌های یک متغیره‌ی خود بیندازید، ترازهای احتمالی را بررسی کرده و اندازه بهینه سپرده را انتخاب کنید:
جدول داده‌های ردیف‌محور
مثال بالا نحوه تنظیم جدول داده عمودی یا ستون‌محور را در اکسل نشان می‌دهد. اگر یک طرح افقی را ترجیح می‌دهید، آن‌چه باید انجام دهید در این‌جا آمده‌است:
- مقادیر متغیر را در یک ردیف تایپ کنید، حداقل یک ستون خالی به سمت چپ (برای فرمول) و یک ردیف خالی در زیر (برای نتایج) بگذارید. برای این مثال مقادیر متغیر را در سلولهای F3:J3 وارد می‌کنیم.
- فرمول را در سلول وارد کنید که یک ستون در سمت چپ اولین مقدار متغیر شما و یک سلول زیر آن باشد (در مثال ما E4).
- همان‌طور که در بالا بحث شد یک جدول داده درست کنید، اما مقدار ورودی (B3) را در جعبه‌ی Row input cell وارد کنید:
- OK را کلیک کنید، و نتیجه‌ی زیر را خواهید گرفت:
نحوه‌ی تهیه‌ی جدول داده‌های دو متغیره در اکسل
یک جدول داده‌ی دو-متغیره نشان می‌دهد که چگونه ترکیب‌های مختلف 2 مجموعه از مقادیر متغیر بر نتیجه‌ی فرمول تأثیر می‌گذارد. به عبارت دیگر، نشان می‌دهد که چگونه تغییر دو مقدار ورودی با همان فرمول، خروجی را تغییر می‌دهد.
مراحل ایجاد یک جدول داده‌ی دو متغیره در اکسل اساساً همانند مثال بالا است، با این تفاوت که شما دو بازه (محدوده) از مقادیر ورودی ممکن را وارد می‌کنید، یکی در یک ردیف و دیگری در یک ستون.
برای دیدن نحوه‌ی عملکرد، بیایید از همان ماشین حساب بهره‌ی مرکب استفاده کنیم و اثرات اندازه‌ی سرمایه‌گذاری اولیه و تعداد سال‌های موجود در تراز را بررسی کنیم. برای انجام آن، جدول داده‌های خود را از این طریق تنظیم کنید:y:
- فرمول خود را در یک سلول خالی وارد کنید یا آن سلول را به فرمول اصلی خود پیوند دهید. اطمینان حاصل کنید که ستون‌های خالی کافی در سمت راست و ردیف‌های خالی در زیر برای جا دادن مقادیر متغیر خود دارید. مانند قبل، سلول E2 را به فرمول FV اصلی که تراز را محاسبه می‌کند، لینک می‌دهیم: =B8
- مجموعه‌ای از مقادیر ورودی را زیر فرمول، در همان ستون (مقادیر سرمایه‌گذاری در E3:E8) تایپ کنید.
- مجموعه‌ای دیگر از مقادیر متغیر را در سمت راست فرمول، در همان ردیف (عدد سال‌ها در F2:H2) وارد کنید.
در این مرحله، جدول داده‌های دو متغیره‌ی شما باید شبیه به این باشد:
- کل بازه‌ی جدول داده‌ها شامل فرمول، ردیف و ستون مقادیر متغیر و سلول‌هایی را که مقادیر محاسبه شده در آن‌ها ظاهر می‌شود، انتخاب کنید. ما بازه‌ی E2:H8 را انتخاب می‌کنیم.
- یک جدول داده به روش قبلی که آشنا شدید ایجاد کنید: تب Data > دکمه‌ی 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 (فرمول بهره) لینک کنید:
- بازه‌ی جدول داده‌های گسترش‌یافته را انتخاب کنید (D2:F8).
- با کلیک بر روی Data > What-If Analysis > Table Data کادر محاوره‌ای Data Table را باز کنید
- در جعبه‌ی Column Input cell، سلول ورودی (B3) را ارائه کنید و بر روی OK کلیک کنید.
این‌جا، شما می‌توانید اثر مقادیر متغیر خود را در هر دو فرمول مشاهده کنید:
جدول داده‌ها در اکسل – 3 چیزی که باید بدانید
برای استفاده‌ی مؤثر از جداول داده در اکسل، این 3 واقعیت ساده را در نظر داشته باشید:
- برای ایجاد جدول داده‌ها با موفقیت، سلول(های) ورودی باید در همان شیت جدول داده‌ها باشد.
- مایکروسافت اکسل برای محاسبه‌ی نتایج جدول داده از تابع TABLE (row_input_cell، column_input_cell) استفاده می‌کند:
- در جدول داده‌های یک متغیره، بسته به طرح (ستون‌محور یا ردیف‌محور) یکی از آرگومان‌ها حذف می‌شود. به عنوان مثال، در جدول داده‌های یک متغیره‌ی افقی ما، فرمول =TABLE(, B3) است که B3 سلول ورودی ستون است.
- در جدول داده‌های دو متغیره، هر دو آرگومان موجود هستند. به عنوان مثال، =TABLE(B6, B3) که B6 سلول ورودی ردیف و B3 سلول ورودی ستون است.
تابع TABLE به صورت فرمول آرایه‌ای وارد می‌شود. برای اطمینان از این موضوع، یک سلول با مقدار محاسبه‌شده را انتخاب کنید، به نوار فرمول نگاه کنید و به {آکولادها}ی دور فرمول توجه داشته باشید. با این حال، این یک فرمول آرایه معمولی نیست – شما نمی‌توانید آن را در نوار فرمول تایپ کنید و هم‌چنین نمی‌توانید یک فرمول موجود را ویرایش کنید. این فقط “برای نمایش” است.
- از آن‌جا که نتایج جدول داده‌ها با فرمول آرایه‌ای محاسبه می‌شود، سلولهای حاصل نمی‌توانند به صورت جداگانه ویرایش شوند. فقط می‌توانید بازه‌ای از سلول‌ها را مطابق توضیحات زیر ویرایش یا حذف کنید.
نحوه‌ی حذف جدول داده‌ها در اکسل
همان‌طور که در بالا گفته شد، اکسل اجازه‌ی حذف مقادیر در سلولهای منفرد حاوی نتایج را نمی‌دهد. هر زمان که سعی کنید این کار را انجام دهید، یک پیام خطای “Cannot change part of a data table” ” (نمی‌توان بخشی از جدول داده‌ها را تغییر داد) نشان داده می‌شود.
با این حال، شما می‌توانید به راحتی کل آرایه‌ی مقادیر حاصل را پاک کنید. در این‌جا نحوه‌ی آن آمده است:
- بسته به نیاز شما، تمام سلولهای جدول داده یا فقط سلولهای دارای نتایج را انتخاب کنید.
- کلید Delete را فشار دهید.
انجام شد!
نحوه‌ی ویرایش نتایج جدول داده‌ها
از آن‌جا که امکان تغییر بخشی از یک آرایه در اکسل وجود ندارد، نمی‌توانید سلولهای منفرد را با مقادیر محاسبه‌شده ویرایش کنید. فقط با انجام این مراحل می‌توانید تمام آن مقادیر را با مقدار مورد نظر خود جایگزین کنید:
- تمام سلولهای حاصل را انتخاب کنید.
- فرمول TABLE را در نوار فرمول حذف کنید.
- مقدار مورد نظر را تایپ کرده و Ctrl+Enter را فشار دهید.
این کار مقدار مشابهی را در تمام سلولهای انتخاب‌شده وارد می‌کند:
پس از حذف فرمول TABLE، جدول داده‌های قبلی به یک بازه‌ی معمول تبدیل می‌شود، و شما می‌توانید هر سلول منفرد را به طور عادی ویرایش کنید.
نحوه‌ی محاسبه‌ی مجدد جدول داده‌ها به صورت دستی
اگر یک جدول داده‌ی بزرگ با چندین مقادیر متغیر و فرمول اکسل شما را کند می‌کند، می‌توانید محاسبات خودکار را در آن و سایر جداول داده غیرفعال کنید.
برای این کار، به تب Formulas > سپس گروه Calculation بروید، بر روی دکمه‌ی Calculation Options کلیک کنید، و سپس بر روی Automatic Except Data Tables کلیک کنید.
این کار محاسبات خودکار جدول داده را خاموش کرده و محاسبه‌ی مجدد کل ورک‌بوک را تسریع می‌کند.
برای محاسبه‌ی مجدد جدول داده‌های خود، سلولهای حاصل از آن، یعنی سلولهای دارای فرمول TABLE() را انتخاب کنید، و F9 را فشار دهید.
به این صورت می‌توانید یک جدول داده را در اکسل ایجاد و استفاده کنید. برای مشاهده دقیق‌تر نمونه‌هایی که در این آموزش مورد بحث قرار گرفته‌است، شما می‌توانید نمونه ورک‌بوک جداول داده‌ی ما را دانلود کنید. از شما بخاطر خواندن این مطلب تشکر می‌کنیم و خوشحال خواهیم شد که دوباره شما را ببینیم!
| دانلود |
با سلام
من میخوام روی یه سر ستون فرمول بنویسم ولی بعد از زدن کلید اینتر فرمول از بین میره و درست عمل نمی کنه…
راه حلی هست برای این مشکل ؟؟
درود. در سرستون جدول در اکسل نمی‌توان فرمول نوشت.