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

کاربرد ابزار 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 اکسل:

[private role=specialsubscriber]

  1. زمانی که شما از ابزار Data Table اکسل استفاده میکنید و جدول تحلیلی تون رو ایجاد میکنید، با هر تغییر در ورکبوکتون فارغ از اینکه این تغییر در نتیجه Data Table شما تاثیری داشته باشه یا خیر، کل جدول شما دوباره محاسبه میشه که این مساله ممکنه باعث کندی فایل اکسل شما بشه. برای اینکه محاسبه خودکار Data Table مون رو در اکسل غیر فعال کنید مثل تصویر زیر از تب Formulas و بخش Calculation، روی گزینه Automatic Except For Data Tables کلیک میکنیم.کاربرد ابزار Data Table اکسل با اینکار با تغییرات مقادیر در ورکبوکتون جدول تحلیلی شما مجدداً محاسبه نخواهد شد. در این حالت برای اینکه اطلاعات جدولتون مجدد محاسبه بشه کافیه سلول مربوط به فرمول Data Table خودتون رو انتخاب کنید و کلید F9 رو فشار بدید.
  2. همونطور که دیدیم از ابزار Data Table حداکثر دو متغیر داره. برای تحلیل حساسیت بیش از دو متغیر میتونیم از ابزار Scenario استفاده کنیم.
  3. در حالت تک متغیره گاهی ممکنه لازم باشه جدولمون رو به صورت عمودی تنظیم کنیم. برا اینکا کافیه فرمول و مقادیر متغیرهامون رو به شکل جدول زیر تنظیم کنیم و بعد از ابزار Data Table استفاده کنیم.
    A B
    1 A1*240=
    2 1
    3 2
    4 3
    5 4
    6 5
    7 6
    8 7
    9 8
    10 9
    11 10

[/private]

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

[private role=specialsubscriber]DATATABLE.sample.worksheet.ir.xlsx[/private]

تمرین:

[private role=specialsubscriber]قصد داریم با سرمایه ای که داریم دستگاهی رو خریداری کنیم و از طریق کار با این دستگاه برای خودمون درآمد ایجاد کنیم. هزینه خرید دستگاه 4000 واحد هست. دستگاه به طور متوسط در هرسال 1500 واحد سودآوری داره. همینطور به ازای هر سال استفاده از دستگاه باید هزینه نگهداری و لوازم مصرفی اون پرداخت بشه. تحلیل حساسیت رو برای این مساله انجام بدید. دامنه متغیر هزینه نگهداری و لوازم مصرفی دستگاه 500 تا 1000 واحد (گام افزایش 100 واحد) و دامنه تغییر متغییر سالهای استفاده رو هم بین 1 تا 7 سال درنظر بگیرید. در چه شرایطی خرید و کار با دستگاه سودآور هست؟[/private]

53 Comments
  • فلا حی
    Posted at 19:07h, 19 دسامبر پاسخ

    سلام
    جواب سوال
    درآمد
    هزینه
    -4000 500 600 700 800 900 1000
    1 -3000 -3100 -3200 -3300 -3400 -3500
    2 -1500 -1600 -1700 -1800 -1900 -2000
    3 0 -100 -200 -300 -400 -500
    4 1500 1400 1300 1200 1100 1000
    5 3000 2900 2800 2700 2600 2500
    6 4500 4400 4300 4200 4100 4000
    7 6000 5900 5800 5700 5600 5500

  • fatemeh
    Posted at 08:07h, 27 جولای پاسخ

    سلام
    گزینه data analysis در اکسل 2013 کجاست>؟؟؟؟؟ برای آنالیز رگرسیون چند متغیره میخوام. ممنون میشم راهنماییم بفرمایید.

  • مرتضی
    Posted at 11:28h, 21 سپتامبر پاسخ

    سلام اقا ممنون
    ممکنه در باره این که چطور رمز فراموش شده کاربرگ ها را ازبین ببریم مطلب بنویسید. ممنون …

    • محبوبه
      Posted at 17:13h, 19 نوامبر پاسخ

      سلام جواب تمرین را ندارید

  • هوشنگ
    Posted at 08:30h, 10 جولای پاسخ

    سپاس برای همه ی زحماتتون واقعاً همه ی فایل هاتون عالی و کاربردی هستند تشکر

    • یاسین اسماعیل پور
      Posted at 19:32h, 10 جولای پاسخ

      خواهش میکنم. وظیفست

  • مرجان
    Posted at 02:29h, 24 ژوئن پاسخ

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

    • یاسین اسماعیل پور
      Posted at 15:01h, 24 ژوئن پاسخ

      سلام. در آخر آموزش یه فایل نمونه برای دانلود وجود داره میتونید از اون استفاده کنید.

  • منصور
    Posted at 16:56h, 05 ژانویه پاسخ

    سلام اگه امکانش هست در مورد گزینه data analysis و رگرسیون آموزش قرار دید
    مثلا بدست آوردن مقدار p

    • یاسین اسماعیل پور
      Posted at 16:22h, 08 ژانویه پاسخ

      سلام منصور جان. اگر فرصتی پیش بیاد و تقاضای دوستان براش زیاد باشه حتما اینکار رو میکنم

  • Ali
    Posted at 15:57h, 13 دسامبر پاسخ

    سلام،. پاسخ تمرینی رو که گفتید برام می‌فرستین داخل یک فایل اکسل، ممنون

    • یاسین اسماعیل پور
      Posted at 14:56h, 18 دسامبر پاسخ

      سلام. من تمرین هایی که اینجا قرار میدم رو پاسخشون رو آماده نمیکنم متاسفانه

  • مهرداد
    Posted at 20:54h, 13 سپتامبر پاسخ

    سلام جناب اسماعیل پور عزیز
    یک فایل اکسل دارم با ۲۷ شیت که هر کدام از شیت ها متعلق به یک شهرستان است.
    در هر شیط سر ستونها اسم ادارات و در ردیف ها مشخصاتی شامل(راه پله.آسانسور.رمپ و غیره)هست که این مشخصات مربوط به محیط داخلی ادارات هست.
    اگر ادارات شهرستان در آیتم مثلا راه پله اصول مناسب سازی برای تردد معلولین رو رعایت کرده باشه کد۱ و پاسخ منفی کد ۰ و کد ۲ به معنای عدم مصداق است.
    حال می خواهیم تحلیل کنیم و نمودار ایجاد کنیم که مثلا فرمانداری چند درصد از اداراتش در سطح استان مناسب سازی شده(کد۱)و چند درصد نشده(کد۰) و عدم مصداق(کد۲).
    لطفا در صورت امکان راهنمایی بفرمایید با چه تابع و چه روشی در اکسل می توان تحلیل نمود.
    با تشکر فراوان

  • مهرنگار
    Posted at 16:38h, 10 جولای پاسخ

    سلام ممنون از مطالبتان…سوال سمانه سوال من هم هست میشه توضیح بدین منم نیاز دارم به جواب این سوال ممنون

  • mahya
    Posted at 08:20h, 03 جولای پاسخ

    سلام.بببخشید دامنه بررسی رو در تحلیل دو متغیره بر چه اساسی قرار میدیم؟

  • aazam
    Posted at 12:12h, 29 می پاسخ

    سلام من یک جدول دارم که اطلاعاتی را روزانه در آن وارد میکنم و میخام این اطلاعات به شیت دیگری مقابل متغییر خود بنشیند چگونه میتوانم این کار را انجام دهم
    مثلا: در شیت اول نام دانش آموز است که اطلاعاتی روزانه با آیتم های متفاوت مثل نحوه درس خواندن و …و میخواهم این به شیت دیگری مقابل نام آن دانش آموز برود و بعدا بتوانم گزارش هفتگی یا ماهانه بگیرم برای یک کلاس این کار را چگونه انجام دهم؟

  • آذری
    Posted at 22:23h, 04 آوریل پاسخ

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

  • sajediyan1989
    Posted at 10:29h, 28 ژانویه پاسخ

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

  • sajediyan1989
    Posted at 10:25h, 28 ژانویه پاسخ

    خیر
    در تلاش برای استفاده از مطالب هستم

  • ag_ghaemi
    Posted at 16:56h, 07 دسامبر پاسخ

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

  • negar1007
    Posted at 12:34h, 17 آگوست پاسخ

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

  • samaneh.khodabakhsh
    Posted at 17:34h, 15 آگوست پاسخ

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

    • یاسین اسماعیل پور
      Posted at 11:07h, 17 آگوست پاسخ

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

  • سمانه
    Posted at 10:46h, 15 آگوست پاسخ

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

    • یاسین اسماعیل پور
      Posted at 15:02h, 15 آگوست پاسخ

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

  • heydarzadeh
    Posted at 17:50h, 07 آگوست پاسخ

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

  • ansari_mahdiye
    Posted at 09:34h, 07 آگوست پاسخ

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

    • یاسین اسماعیل پور
      Posted at 09:52h, 07 آگوست پاسخ

      خواهش میکنم 🙂

  • abaee
    Posted at 02:11h, 30 جولای پاسخ

    ممنون از این همه مسئولیت شناسی شما.

    • یاسین اسماعیل پور
      Posted at 08:06h, 02 آگوست پاسخ

      مرسی 🙂

  • مصطفی
    Posted at 16:28h, 06 ژوئن پاسخ

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

    • ansari_mahdiye
      Posted at 09:35h, 07 آگوست پاسخ

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

  • sepideh_samiei
    Posted at 07:25h, 25 آوریل پاسخ

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

  • پریسیما
    Posted at 15:50h, 20 آوریل پاسخ

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

  • borhanpour
    Posted at 13:35h, 01 آوریل پاسخ

    بسیار کار ارزنده ای میکنید. ممنون از لطفتون.

  • pari
    Posted at 21:33h, 19 ژانویه پاسخ

    مرسی از لطف و محبتتون خیلی کمک می کنید

    • یاسین اسماعیل پور
      Posted at 00:37h, 23 ژانویه پاسخ

      سلام پری جان. ممنون. انجام وظیفست

  • سعیده
    Posted at 13:57h, 13 ژانویه پاسخ

    متشکرم

    • یاسین اسماعیل پور
      Posted at 01:12h, 18 ژانویه پاسخ

      خواهس میکنم سعیده جان

  • rosha9154
    Posted at 16:22h, 05 ژانویه پاسخ

    با سلام من روی 2 متغیره کار کردم جواب نمی گیرم

    • یاسین اسماعیل پور
      Posted at 18:52h, 06 ژانویه پاسخ

      دوباره امتحان کنید با دقت بیشتر 🙂

  • Sahar bertashk
    Posted at 11:31h, 12 دسامبر پاسخ

    سلام عرض میکنم آقای اسماعیل پور
    ممنون بابت مطالب مفیدتون

    • یاسین اسماعیل پور
      Posted at 01:53h, 15 دسامبر پاسخ

      سلام سحر جان. سپاس

  • احسان
    Posted at 21:37h, 10 سپتامبر پاسخ

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

    • یاسین اسماعیل پور
      Posted at 23:36h, 16 سپتامبر پاسخ

      بله احسان جان. چرا که نه. فرقی نمیکنه ماهیت متغیر

  • navid reza
    Posted at 12:31h, 08 سپتامبر پاسخ

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

    • یاسین اسماعیل پور
      Posted at 14:03h, 09 سپتامبر پاسخ

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

  • flotfalian
    Posted at 12:02h, 04 سپتامبر پاسخ

    واقعاً عالی بود ممنونم.

    • یاسین اسماعیل پور
      Posted at 13:13h, 04 سپتامبر پاسخ

      ممنون دوست عزیز

  • سپیده
    Posted at 20:19h, 04 ژوئن پاسخ

    ممنونم از راهنماییتون.

    • یاسین اسماعیل پور
      Posted at 20:49h, 04 ژوئن پاسخ

      خواهش میکنم.

  • سپیده
    Posted at 16:12h, 04 ژوئن پاسخ

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

    • یاسین اسماعیل پور
      Posted at 20:03h, 04 ژوئن پاسخ

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

Post A Comment