Data Table اکسل ابزاری مفید برای تحلیل متغیرها

کاربرد ابزار Data Table در اکسل

در این آموزش میخوایم با یکی از ابزارهای What-If Analysis اکسل یعنی ابزار Data Table اکسل آشنا بشیم. Data Table اکسل یکی از ابزارهای تحلیل حساسیت در اکسل به شمار میره. در این مطلب با نحوه استفاده از ابزار Data Table اکسل آشنا میشیم و میبینیم که چطور میشه به کمک این ابزار نتیجه تغییرات متغیرهای مختلف رو بررسی کنیم.

با استفاده ابزار Data Table اکسل میتونیم تاثیر تغییرات یک یا دو متغیر رو روی متغیر وابسته مدنظرمون بررسی کنیم. در ادامه با چگونگی استفاده از ابزار Data Table اکسل برای تحلیل تک متغیره و دو متغیره آشنا میشیم.

تحلیل تک متغیره در Data Table اکسل:

از این حالت زمانی استفاده میکنیم که بخوایم نتیجه تغییرات صرفاً یک متغیر رو در فرمول خودمون مشاهده کنیم. بیاید با یه مثال خیلی ساده شروع کنیم. فرض کنید هزینه سوخت خودرو به ازای هر کیلومتر 240 واحد هست و ما میخوایم تاثیرات مسافت طی شده خودرو رو بر هزینه سوخت اون به ازای مسافت های یک تا ده کیلومتر تحلیل کنیم. برای اینکار ابتدا فرمول A1*240= رو در سلول A2 مینویسیم. سپس اعداد 1 تا 10 رو در سلول های B1 تا K1 قرار میدیم. حالا مثل تصویر زیر سلولی که فرمولمو رو در اون نوشتیم یعنی سلول A2 رو به همراه سلول های B1 تا K1 که مقادیر متغیرمون در اونها قرار دارند رو انتخاب میکنیم و از تب DATA و بخش Data Tools رو گزینه What-If Analysis کلیک میکنیم و در منویی که باز میشه گزینه Data Table رو انتخاب میکنیم.

کاربرد ابزار Data Table اکسل

با انتخاب این گزینه پنجره زیر باز میشه.

کاربرد ابزار Data Table اکسل

حالا روی فلش قرمز رنگ گزینه Row Input Cell کلیک میکینم. با کلیک روی این گزینه پنجره زیر باز میشه که ما باید در اون سلول مربوط به متغیر مورد نظرمون رو انتخاب کنیم که این سلول همون سلول A1 هست.

کاربرد ابزار Data Table اکسل

پس از اینکه سول A1 رو انتخاب کردیم با کلیک مجدد روی فلش قرمز رنگ به پنجره اصلی برمیگردیم و OK رو کلیک میکنیم. همونطور که مشاهد میکنید هزینه مربوط به کیلومترهای مختلف در سلولهای B2 تا K2 نمایش داده میشه.

کاربرد ابزار Data Table اکسل

تحلیل دو متغیره در Data Table اکسل:

در بخش قبل حالت تک متغیره ابزار Data Table اکسل رو بررسی کردیم. حالا میخوایم حالت دو متغیره Data Table رو امتحان کنیم بنابراین به یک مثال کاملتر احتیاج داریم. فرض کنید رشد یا کاهش فروش سازمان ما تابع دو متغیر مقدار افزایش تبلیغات و میزان افت کیفی محصول باشه به طوری که با هر واحد افزایش تبلیغات 17درصد به فروش سازمان افزوده بشه و با هر واحد کاهش کیفیت محصول 30درصد  از فروش سازمان کم بشه. میخوایم اثرات تغییر این دو متغیر رو روی افزایش یا کاهش فروش سازمان بررسی کنیم. دامنه بررسی ما برای متغیر کاهش کیفیت محصول بین 0.5 تا 3 واحد و برای متغیر افزایش تبلیغات بین 0 تا 5 واحد هست. برای شروع ابتدا در سلول A3 فرمول افزایش فرمول تغییرات فروش سازمان رو که A1*-0.3+A2*0.17= مینویسیم. با توجه به فرمولی که ما نوشتیم سلول A1 معرف متغییر کاهش کیفیت و سلول A2 معرف متغییر افزایش تبلیغات هست. حالا در سلول های B3 تا G3 اعداد مربوط به دامنه تغییرات کاهش کیفیت یعنی اعداد 0.5 تا 3 و در سلول های A4 تا A9 اعداد مربوط به دامنه تغییرات افزایش تبلیغات یعتی 0 تا 5 رو قرار میدیم تا به جدولی شبیه تصویر زیر برسیم:

کاربرد ابزار Data Table اکسل

حالا مثل حالت تک متغیره از تب DATA گزینه Data Table رو انتخاب میکنیم و در بخش Row Input Cell سلول A1 و در بخش  Column Input Cell سلول A2 رو قرار میدیم و پنجره Data Table رو OK میکنیم. با اینکار درصد تغییرات فروش برای مقادیر مختلف افزایش تبلیغات و کاهش کیفیت محصول در جدول نمایش داده میشه.

کاربرد ابزار Data Table اکسل

به عنوان مثال اگر کیفیت محصول یک واحد افت کنه و ما 4 واحد تبلیغاتمون رو افزایش بدیم فروش سازمان 38درصد افزایش پیدا میکنه یا اگر کیفیت محصول ما 3 واحد افت کنه، علیرغم افزایش 5 واحدی تبلیغات هم فروش سازمان 5درصد افت خواهد داشت.

نکات قابل توجه در استفاده از ابزار Data Table اکسل:

مخصوص کاربران ویژه سایت
کاربر گرامی دسترسی کامل به مطالب مربوط به این بخش با اشتراک در بخش کاربران ویژه امکانپذیر میباشد. لطفا برای اشتراک در بخش کاربران ویژه از بخش عضویت ویژه استفاده نمایید

دانلود فایل آموزش:

مخصوص کاربران ویژه سایت
کاربر گرامی دسترسی کامل به مطالب مربوط به این بخش با اشتراک در بخش کاربران ویژه امکانپذیر میباشد. لطفا برای اشتراک در بخش کاربران ویژه از بخش عضویت ویژه استفاده نمایید

تمرین:

مخصوص کاربران ویژه سایت
کاربر گرامی دسترسی کامل به مطالب مربوط به این بخش با اشتراک در بخش کاربران ویژه امکانپذیر میباشد. لطفا برای اشتراک در بخش کاربران ویژه از بخش عضویت ویژه استفاده نمایید

37 Comments on “Data Table اکسل ابزاری مفید برای تحلیل متغیرها”

  1. سپیده

    سلام
    با تشکر از مطالب مفیدتون من 2 سوال داشتم ممنون می شم کمکم کنید.
    1. آیا می شه توی حالت تک متغیره مانند 2 متغیره درصد تغییرات را بیان کرد؟
    2. آیا می شه این درصد تغییرات را به صورت کلی در نمودار نشان داد؟
    در واقع مساله من تک متغیره است و می خواهم درصد تغییرات تابع اصلی را با نمودار نشان بدهم.
    با تشکر

    1. یاسین اسماعیل پور

      سلام سپیده خانوم
      1. بله امکانش هست تغییرات رو در حالت تک متغیره هم به درصد نمایش بدیم. فقط کافیه فرمولمون رو به صورت درصدی تعریف کنیم و فرمت سلول ها رو هم درصد قرار بدیم. کلا حالت تک متغیره و دو متغیره تفاوتشون فقط در تعداد متغیرهاست و در باقی مسایل شبیه به هم هستن.
      2. اگر منظورتون PLOT کردن کامل نمودار بر اساس فرمول و به شکل پیوسته هست خیر تا جایی که من میدونم راه ساده ای در اکسل برای این کار وجود نداره. برای نمایش نمودار باید تعداد نقاط X و Y کافی رو در شیتتون محاسبه و بوسیله اونها نمودارتون رو رسم کنید (کافی بودن بستگی به نمودار داره به عنوان مثال برای یک رابطه خطی محاسبه نقطه ابتدا و انتها کافیه. اما مثلا برای یک رابطه مثل سینوس باید تعداد نقطه های بیشتری تعریف کنید بسته به اینکه بخواید چقدر نمودارتون دقیق باشه و با انحنای سینوس منطبق باشه).

    1. یاسین اسماعیل پور

      سلام نوید جان
      تا جایی که من میدونم در اکسل راه ساده و ابزار خاصی برای اینکار وجود نداره. یا شما باید از تحلیل سناریو استفاده کنی که داینامیک نیست مثل data table و یا باید با استفاده از تکنیک هایی مثل Offset یا ماکرو نویسی خودت به اونچیزی که میخای برسی.

  2. احسان

    با سلام جناب مهندس آیا میشه از این تکنیک برای تحلیل حساسیت irr مثلا با تغییر دو متغیر درآمد و هزینه استفاده کرد؟

  3. پریسیما

    سلام من نیاز به تحلیل حساسیت برای یک کار پژوهشی دارم میتونین بیشتر راهنماییم کنین

  4. sepideh_samiei

    سلام آقای مهندس
    اولا ممنون که مرا در جمعتون پذیرفتید و ضمنا از مطالب آموزندتون بسیار سپاسگزارم

  5. مصطفی

    سلام خسته نباشین…
    ببخشید من دقیقا متوجه نشدم که این ابزار کاربردش چیه! الان این مثالی زدین با اکسل عادی هم انجام میشه!
    بنظر من یکم بیشتر توضیح بدین
    ممنون از شما

    1. ansari_mahdiye

      وقتی فرمولتون خیلی طولانی و پیچیده باشه بهتره از دیتا تیبل استفاده کنی تا مجبور نباشی مثلا یه فرمولو ده بار بنویسی

  6. ansari_mahdiye

    من از دیتا تیبل برای ارزش گذاری شرکت استفاده کردم متغیرام بتا و نرخ بهره بود عالی بود مرسی

  7. heydarzadeh

    با سلام
    روش خوب و مفیدی، ممنونم.
    یه سوال دارم من این روش را برای دو متغییره کار کردم ولی جواب تمام سلول ها را ماکزیمم مقدار سلول ردیفی می دهد طبق مثال شما تمام سلول ها را عدد 3 نشان می دهد. اشکال کار بنده کجاست؟
    لطفا راهنمایی بفرمایید.
    با سپاس فراوان

  8. سمانه

    با سلام
    من يك شيت دارم كه اطلاعات پرسنل با افراد تحت تكفل انها وارد شده مي خواهم دريك شيت ديگر از توابع جستجو استفاده كنم ولي وقتي از لوك اپ استفاده ميكنم فقط يك نفر از افراد تحت تكفل رو نمايش ميدهد از چه ابزاري استفاده كنم تا زماني كه نام خانوادگي شخص حقيقي وارد مي شود تمامي افراد نحن تكفل ان را در شيت جداگانه نمايش دهد

    1. یاسین اسماعیل پور

      سلام. افراد تحت تکفل به چه صورت ذخیره میشن؟ هر کدوم یه ستون دارند در جدول اصلی؟

  9. samaneh.khodabakhsh

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

    1. یاسین اسماعیل پور

      سلام سمانه جان
      جواب سوال شما یکم مفصله انشالل به زودی یک مطلب کامل در موردش مینویسم

  10. negar1007

    سلام
    با این توضیحی که برای این ابزار دادید به نظر میاد با وابسته کردن سلول ها و ضربشون در هم میتونستیم انجام بدیم ایا DATA table فراتر از این کاری انجام میده ؟

  11. ag_ghaemi

    سلام
    سلام
    آیا می توان این تحلیل حساسیت را برای چند متغیره انجام داد یا خیر؟

  12. sajediyan1989

    برای استفاده از مطالب کامل هر بخش با توجه به اینکه عضو سایت هستم چه کاری باید انجام بدم؟
    از مطالب آموزشی بسیار مفید و کاربردیتون متشکرم

  13. آذری

    با سلام و عرض ادب. اگر در پژوهش 3 متغیر داشته باشیم می تونیم با این برنامه رابطه آنها رو پیدا کنیم

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *