در این آموزش میخوایم با یکی از ابزارهای 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 رو انتخاب میکنیم.
با انتخاب این گزینه پنجره زیر باز میشه.
حالا روی فلش قرمز رنگ گزینه Row Input Cell کلیک میکینم. با کلیک روی این گزینه پنجره زیر باز میشه که ما باید در اون سلول مربوط به متغیر مورد نظرمون رو انتخاب کنیم که این سلول همون سلول A1 هست.
پس از اینکه سول A1 رو انتخاب کردیم با کلیک مجدد روی فلش قرمز رنگ به پنجره اصلی برمیگردیم و OK رو کلیک میکنیم. همونطور که مشاهد میکنید هزینه مربوط به کیلومترهای مختلف در سلولهای B2 تا K2 نمایش داده میشه.
تحلیل دو متغیره در 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 گزینه Data Table رو انتخاب میکنیم و در بخش Row Input Cell سلول A1 و در بخش Column Input Cell سلول A2 رو قرار میدیم و پنجره Data Table رو OK میکنیم. با اینکار درصد تغییرات فروش برای مقادیر مختلف افزایش تبلیغات و کاهش کیفیت محصول در جدول نمایش داده میشه.
به عنوان مثال اگر کیفیت محصول یک واحد افت کنه و ما 4 واحد تبلیغاتمون رو افزایش بدیم فروش سازمان 38درصد افزایش پیدا میکنه یا اگر کیفیت محصول ما 3 واحد افت کنه، علیرغم افزایش 5 واحدی تبلیغات هم فروش سازمان 5درصد افت خواهد داشت.
نکات قابل توجه در استفاده از ابزار Data Table اکسل:
- زمانی که شما از ابزار Data Table اکسل استفاده میکنید و جدول تحلیلی تون رو ایجاد میکنید، با هر تغییر در ورکبوکتون فارغ از اینکه این تغییر در نتیجه Data Table شما تاثیری داشته باشه یا خیر، کل جدول شما دوباره محاسبه میشه که این مساله ممکنه باعث کندی فایل اکسل شما بشه. برای اینکه محاسبه خودکار Data Table مون رو در اکسل غیر فعال کنید مثل تصویر زیر از تب Formulas و بخش Calculation، روی گزینه Automatic Except For Data Tables کلیک میکنیم.
با اینکار با تغییرات مقادیر در ورکبوکتون جدول تحلیلی شما مجدداً محاسبه نخواهد شد. در این حالت برای اینکه اطلاعات جدولتون مجدد محاسبه بشه کافیه سلول مربوط به فرمول Data Table خودتون رو انتخاب کنید و کلید F9 رو فشار بدید. - همونطور که دیدیم از ابزار Data Table حداکثر دو متغیر داره. برای تحلیل حساسیت بیش از دو متغیر میتونیم از ابزار Scenario استفاده کنیم.
- در حالت تک متغیره گاهی ممکنه لازم باشه جدولمون رو به صورت عمودی تنظیم کنیم. برا اینکا کافیه فرمول و مقادیر متغیرهامون رو به شکل جدول زیر تنظیم کنیم و بعد از ابزار 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
دانلود فایل آموزش:
DATATABLE.sample.worksheet.ir.xlsx
تمرین:
قصد داریم با سرمایه ای که داریم دستگاهی رو خریداری کنیم و از طریق کار با این دستگاه برای خودمون درآمد ایجاد کنیم. هزینه خرید دستگاه 4000 واحد هست. دستگاه به طور متوسط در هرسال 1500 واحد سودآوری داره. همینطور به ازای هر سال استفاده از دستگاه باید هزینه نگهداری و لوازم مصرفی اون پرداخت بشه. تحلیل حساسیت رو برای این مساله انجام بدید. دامنه متغیر هزینه نگهداری و لوازم مصرفی دستگاه 500 تا 1000 واحد (گام افزایش 100 واحد) و دامنه تغییر متغییر سالهای استفاده رو هم بین 1 تا 7 سال درنظر بگیرید. در چه شرایطی خرید و کار با دستگاه سودآور هست؟
سلام
با تشکر از مطالب مفیدتون من 2 سوال داشتم ممنون می شم کمکم کنید.
1. آیا می شه توی حالت تک متغیره مانند 2 متغیره درصد تغییرات را بیان کرد؟
2. آیا می شه این درصد تغییرات را به صورت کلی در نمودار نشان داد؟
در واقع مساله من تک متغیره است و می خواهم درصد تغییرات تابع اصلی را با نمودار نشان بدهم.
با تشکر
سلام سپیده خانوم
1. بله امکانش هست تغییرات رو در حالت تک متغیره هم به درصد نمایش بدیم. فقط کافیه فرمولمون رو به صورت درصدی تعریف کنیم و فرمت سلول ها رو هم درصد قرار بدیم. کلا حالت تک متغیره و دو متغیره تفاوتشون فقط در تعداد متغیرهاست و در باقی مسایل شبیه به هم هستن.
2. اگر منظورتون PLOT کردن کامل نمودار بر اساس فرمول و به شکل پیوسته هست خیر تا جایی که من میدونم راه ساده ای در اکسل برای این کار وجود نداره. برای نمایش نمودار باید تعداد نقاط X و Y کافی رو در شیتتون محاسبه و بوسیله اونها نمودارتون رو رسم کنید (کافی بودن بستگی به نمودار داره به عنوان مثال برای یک رابطه خطی محاسبه نقطه ابتدا و انتها کافیه. اما مثلا برای یک رابطه مثل سینوس باید تعداد نقطه های بیشتری تعریف کنید بسته به اینکه بخواید چقدر نمودارتون دقیق باشه و با انحنای سینوس منطبق باشه).
ممنونم از راهنماییتون.
خواهش میکنم.
واقعاً عالی بود ممنونم.
ممنون دوست عزیز
سلام
آیا راهی برای تحلیل دو متغیر به بالا هم وجود داره؟
سلام نوید جان
تا جایی که من میدونم در اکسل راه ساده و ابزار خاصی برای اینکار وجود نداره. یا شما باید از تحلیل سناریو استفاده کنی که داینامیک نیست مثل data table و یا باید با استفاده از تکنیک هایی مثل Offset یا ماکرو نویسی خودت به اونچیزی که میخای برسی.
با سلام جناب مهندس آیا میشه از این تکنیک برای تحلیل حساسیت irr مثلا با تغییر دو متغیر درآمد و هزینه استفاده کرد؟
بله احسان جان. چرا که نه. فرقی نمیکنه ماهیت متغیر
سلام عرض میکنم آقای اسماعیل پور
ممنون بابت مطالب مفیدتون
سلام سحر جان. سپاس
با سلام من روی 2 متغیره کار کردم جواب نمی گیرم
دوباره امتحان کنید با دقت بیشتر 🙂
متشکرم
خواهس میکنم سعیده جان
مرسی از لطف و محبتتون خیلی کمک می کنید
سلام پری جان. ممنون. انجام وظیفست
بسیار کار ارزنده ای میکنید. ممنون از لطفتون.
سلام من نیاز به تحلیل حساسیت برای یک کار پژوهشی دارم میتونین بیشتر راهنماییم کنین
سلام آقای مهندس
اولا ممنون که مرا در جمعتون پذیرفتید و ضمنا از مطالب آموزندتون بسیار سپاسگزارم
سلام خسته نباشین…
ببخشید من دقیقا متوجه نشدم که این ابزار کاربردش چیه! الان این مثالی زدین با اکسل عادی هم انجام میشه!
بنظر من یکم بیشتر توضیح بدین
ممنون از شما
وقتی فرمولتون خیلی طولانی و پیچیده باشه بهتره از دیتا تیبل استفاده کنی تا مجبور نباشی مثلا یه فرمولو ده بار بنویسی
ممنون از این همه مسئولیت شناسی شما.
مرسی 🙂
من از دیتا تیبل برای ارزش گذاری شرکت استفاده کردم متغیرام بتا و نرخ بهره بود عالی بود مرسی
خواهش میکنم 🙂
با سلام
روش خوب و مفیدی، ممنونم.
یه سوال دارم من این روش را برای دو متغییره کار کردم ولی جواب تمام سلول ها را ماکزیمم مقدار سلول ردیفی می دهد طبق مثال شما تمام سلول ها را عدد 3 نشان می دهد. اشکال کار بنده کجاست؟
لطفا راهنمایی بفرمایید.
با سپاس فراوان
با سلام
من يك شيت دارم كه اطلاعات پرسنل با افراد تحت تكفل انها وارد شده مي خواهم دريك شيت ديگر از توابع جستجو استفاده كنم ولي وقتي از لوك اپ استفاده ميكنم فقط يك نفر از افراد تحت تكفل رو نمايش ميدهد از چه ابزاري استفاده كنم تا زماني كه نام خانوادگي شخص حقيقي وارد مي شود تمامي افراد نحن تكفل ان را در شيت جداگانه نمايش دهد
سلام. افراد تحت تکفل به چه صورت ذخیره میشن؟ هر کدوم یه ستون دارند در جدول اصلی؟
یک جدول دارم که نام نام خانوادگی مشخصات شناسایی هر فرد تحت تکفل به صورت فیلد های جدول هستند مثلا اقای اعتمادی با کد پرسنلی 55234 دارای دو فرزند پرنیا و پوریا است که مشخصاتشون توی جدول به صورت رکورد نوشته شده وقتی کد پرسنلی اقای اعتمادی رو سرچ میکنم فقط یکی از فرزندانش نمایش داده میشه من میخوام کاری کنم که تمام افراد تحت تکفلش با وارد کردن کد پرسنلیش توی شیت جداگانه نمایش داده بشه
سلام سمانه جان
جواب سوال شما یکم مفصله انشالل به زودی یک مطلب کامل در موردش مینویسم
سلام
با این توضیحی که برای این ابزار دادید به نظر میاد با وابسته کردن سلول ها و ضربشون در هم میتونستیم انجام بدیم ایا DATA table فراتر از این کاری انجام میده ؟
سلام
سلام
آیا می توان این تحلیل حساسیت را برای چند متغیره انجام داد یا خیر؟
خیر
در تلاش برای استفاده از مطالب هستم
برای استفاده از مطالب کامل هر بخش با توجه به اینکه عضو سایت هستم چه کاری باید انجام بدم؟
از مطالب آموزشی بسیار مفید و کاربردیتون متشکرم
با سلام و عرض ادب. اگر در پژوهش 3 متغیر داشته باشیم می تونیم با این برنامه رابطه آنها رو پیدا کنیم
سلام من یک جدول دارم که اطلاعاتی را روزانه در آن وارد میکنم و میخام این اطلاعات به شیت دیگری مقابل متغییر خود بنشیند چگونه میتوانم این کار را انجام دهم
مثلا: در شیت اول نام دانش آموز است که اطلاعاتی روزانه با آیتم های متفاوت مثل نحوه درس خواندن و …و میخواهم این به شیت دیگری مقابل نام آن دانش آموز برود و بعدا بتوانم گزارش هفتگی یا ماهانه بگیرم برای یک کلاس این کار را چگونه انجام دهم؟
سلام.بببخشید دامنه بررسی رو در تحلیل دو متغیره بر چه اساسی قرار میدیم؟
سلام ممنون از مطالبتان…سوال سمانه سوال من هم هست میشه توضیح بدین منم نیاز دارم به جواب این سوال ممنون
سلام جناب اسماعیل پور عزیز
یک فایل اکسل دارم با ۲۷ شیت که هر کدام از شیت ها متعلق به یک شهرستان است.
در هر شیط سر ستونها اسم ادارات و در ردیف ها مشخصاتی شامل(راه پله.آسانسور.رمپ و غیره)هست که این مشخصات مربوط به محیط داخلی ادارات هست.
اگر ادارات شهرستان در آیتم مثلا راه پله اصول مناسب سازی برای تردد معلولین رو رعایت کرده باشه کد۱ و پاسخ منفی کد ۰ و کد ۲ به معنای عدم مصداق است.
حال می خواهیم تحلیل کنیم و نمودار ایجاد کنیم که مثلا فرمانداری چند درصد از اداراتش در سطح استان مناسب سازی شده(کد۱)و چند درصد نشده(کد۰) و عدم مصداق(کد۲).
لطفا در صورت امکان راهنمایی بفرمایید با چه تابع و چه روشی در اکسل می توان تحلیل نمود.
با تشکر فراوان
سلام،. پاسخ تمرینی رو که گفتید برام می‌فرستین داخل یک فایل اکسل، ممنون
سلام. من تمرین هایی که اینجا قرار میدم رو پاسخشون رو آماده نمیکنم متاسفانه
سلام اگه امکانش هست در مورد گزینه data analysis و رگرسیون آموزش قرار دید
مثلا بدست آوردن مقدار p
سلام منصور جان. اگر فرصتی پیش بیاد و تقاضای دوستان براش زیاد باشه حتما اینکار رو میکنم
سلام من چجوری میتونم فایل های اکسلی پیدا کنم که توش داده باشه بتونم این آموزش ها رو روشون پیاده سازی کنم ؟
سلام. در آخر آموزش یه فایل نمونه برای دانلود وجود داره میتونید از اون استفاده کنید.
سپاس برای همه ی زحماتتون واقعاً همه ی فایل هاتون عالی و کاربردی هستند تشکر
خواهش میکنم. وظیفست
سلام اقا ممنون
ممکنه در باره این که چطور رمز فراموش شده کاربرگ ها را ازبین ببریم مطلب بنویسید. ممنون …
سلام جواب تمرین را ندارید
سلام
گزینه data analysis در اکسل 2013 کجاست>؟؟؟؟؟ برای آنالیز رگرسیون چند متغیره میخوام. ممنون میشم راهنماییم بفرمایید.
سلام
جواب سوال
درآمد
هزینه
-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