مقایسه دو نمودار در اکسل با استفاده از نمودار ستونی
همونطور که قبلا هم در مورد رسم نمودار صحبت کردیم، نمودار در اکسل مبحث خیلی مفصل و پرکاری هست که پر از جزئیات و مسائل حرفه ای هست. یکی از راه های تسلط به مبحث رسم نمودار در اکسل دیدن نمونه های مختلف و بررسی همه جزئیات هست. در این مقاله میخوایم نمودار عملکرد واقعی در برابر عملکرد برنامه ریزی شده ( در واقع مقایسه دو نمودار در اکسل ) رو به همراه میزان اختلاف آنها نمایش بدیم.
شکل ۱- مقایسه دو نمودار در اکسل برای نمایش داده برنامه ای در برابر داده واقعی
در این نمودار از نمودار ستونی و خطی استفاده شده که یک سری ترفندهای جالب بر روی آنها اعمال شده است. گام به گام مراحل اجرای این نمودار رو می بینیم:
گام اول مقایسه دو نمودار: آماده کردن داده ها
همونطور که در شکل ۲ می بینید، عملکرد برنامه ریزی شده هر فعالیت در مقابل عملکرد واقعی نمایش ثبت شده. برای اینکه بتونیم میزان اختلاف این دو عدد رو برای هر فعالیت محاسبه کنیم باید محاسباتی رو انجام بدیم. برای محاسبه میزان اختلاف به درصد، کافیه عملکرد برنامه ای رو از عملکرد واقعی کم کنیم و نتیجه رو تقسیم بر عملکرد برنامه ای کنیم. محاسبات رو در شکل ۲ می بینید:
شکل ۲- داده های واقعی و برنامه ای
گام دوم مقایسه دو نمودار: ایجاد نمودار ستونی
داده های برنامه ای و واقعی رو انتخاب کرده و نمودار ستونی رو انتخاب میکنیم.
شکل ۳- رسم نمودار ستونی از داده های واقعی و برنامه ای
گام سوم مقایسه دو نمودار: تکرار مرحله دوم
در این مرحله باید دوباره داده های واقعی و برنامه ای رو به نمودار اضافه کنیم. برای این کار از تب Design و قسمت select data گزینه add رو زده و دو ستون واقعی و برنامه ای رو مجدد اضافه میکنیم.
شکل ۴- اضافه کردن مجدد محدوده داده واقعی و برنامه ای به نمودار
گام چهارم مقایسه دو نمودار: تبدیل دو سری جدید اضافه شده، از ستونی به خطی
حالا باید دو سری که مجددا به نمودار اضافه کردیم رو به نمودار خطی تبدیل کنیم. برای این کار نمودار رو انتخاب کرده و از تب Design گزینه Change Chart Type رو میزنیم. از پنجره باز شده و مطابق شکل ۵، دو سری جدید رو به نمودار line تغییر میدیم.
شکل ۵- تبدیل دو سری جدید اضافه شده به نمودار line
گام پنجم مقایسه دو نمودار: اضافه کردن ستونهای بالا و پایین
یکی از نمودار های خطی رو انتخاب کنید و با زدن علامت + کنار نمودار، مطابق شکل ۶ تیک گزینه Up/Down Bars رو بزنید. این ستون ها اختلاف نقاط رو بصورت یک ستون نمایش می دهند.
شکل ۶- اضافه کردن ستون بالا و پایین به نمودار
گام ششم: فرمت دهی به ستون های (Bar) نمودار
ستون های اختلاف مثبت (واقعی بزرگتر از برنامه) به رنگ سفید و ستون های اختلاف منفی (برنامه بزرگتر از واقعی) به رنگ تیره نشان داده شده. برای تغییر فرمت هر یک از این ستون ها، روی یکی از ستون های هر یک از چهار نوع کلیک کرده و از قسمت Format رنگ مورد نظر رو انتخاب میکنیم. به این ترتیب رنگ هر ۴ نوع ستون رو مطابق شکل ۷ تنظیم میکنیم.
شکل ۷- تغییر فرمت ستون های نمودار
گام هفتم: تنظیم فاصله و همپوشانی ستون ها
ستون اول رو انتخاب و کلیک راست کنید. سپس از قسمت (Overlap, format series (Ctrl+1 رو %۰ و Gap رو به %۱۵۰ تغییر بدید. بعد خط (نمودار Line) رو انتخاب کنید و Gap رو به ۳۰۰% افزایش بدید با این کار، ستونهای اختلاف مثبت و منفی باریک تر از ستون های برنامه و واقعی میشه.
برای تنظیم Gap و Overlap سعی کنید اعداد مختلف رو انتخاب کنید. هر حالتی که از نظر خودتون بهتر بود رو انتخاب کنید.
شکل ۸- تغییر فرمت ستون های نمودار
گام هشتم: حذف نمودار خطی
حالا میخوایم نمودار خطی رو حذف کنیم. برای این کار کافیه روی یکی از خط ها کلیک کرده و از تب Format مطابق شکل ۹ نمودار خطی رو حذف کنیم:
شکل ۹- حذف نمودار خطی
شکل ۱۰- حذف نمودار خطی
گام نهم: اضافه کردن عنوان نمودار و حذف بقیه اجزای اضافی نمودار
برای اضافه کردن عنوان نمودار، هم میتونیم داخل Chart Title کلیک کنیم و عنوان مورد نظر رو تایپ کنیم. هم اینکه این عنوان رو از سلول بگیریم. مطابق شکل ۱۱
شکل ۱۱- تنظیم عنوان نمودار و حذف راهنمای اضافه نمودار
گام دهم: اضافه کردن برچسب به نمودار خطی
در این مرحله برچسب (Label) دو نمودار خطی رو اضافه میکنیم. برای اینکار باید نمودار خطی (که الان دیده نمیشه) رو انتخاب کنیم و از قسمت + گزینه Data Labels رو تیک بزنیم. شکل نمودار مطابق شکل ۱۲ خواهد شد.
شکل ۱۲- اضافه کردن برچسب داده ها به نمودار
برای انتخاب راحت تر اجزای مختلف نمودار، میتونیم از تب Format، از قسمت Current Selection و از لیست فروریز هر قسمت از نمودار رو انتخاب کنیم.
شکل ۱۳- انتخاب اجزای مختلف نمودار
گام یازدهم: محاسبه برچسب ها
در این مرحله میخوایم تعیین کنیم که هر میزان اختلاف، چه شکلی رو نمایش بده. برای این کار باید از تابع Vlookup در حالت بازه ای استفاده کنیم. پس باید جدولی برای این موضوع تهیه کنیم. به جدول شکل ۱۴ نگاه کنید. مفهوم بازه عدد نوشته شده در ستون “%” در ستون “بازه” نمایش داده شده.
شکل ۱۴- نماد مورد نظر برای نمایش درصد
این جدول نشون میده که اگر درصد بین هر یک از بازه های نمایش داده شده در ستون “بازه” بود، چه نمادی بجاش نمایش داده بشه. حالا باید بر اساس این جدول، فرمول نویسی کنیم و به ازای هر درصد، نماد مورد نظر رو فراخوانی کنیم.
شکل ۱۵- محاسبه برچسب های نمودار جهت استفاده در مقایسه دو نمودار در اکسل
با تابع Vlookup نماد مورد نظر رو فراخوانی میکنیم و بعد با ترکیب If و Text درصد های مثبت و منفی رو تفکیک میکنیم:
=VLOOKUP ( F6 ; $C$22:$D$26 ; ۲ ) & ” “
برای دیدن نحوه عملکرد Vlookup بازه ای حتما مقاله جستجوی بازه ای رو مطالعه کنید. انتهای فرمول برای ایجاد فاصله بین نماد و عدد ” “& گذاشته شده. (شکل ۱۵)
این فرمول رو در ستون “نماد” نوشته و کپی کنید.(شکل ۱۵)
=IF ( $F6<0 ; $G6 & TEXT (ABS ($F6) ; “۰%” ) ; “” )
تابع ABS برای این هست که اگر درصد منفی بود، مثبت نمایش داده بشه و تابع Text برای این هست که فرمت عدد به درصد نمایش داده بشه.
این فرمول رو در ستون اختلاف منفی نوشته و درگ کنید به پایین و سمت راست (ستون اختلاف مثبت)
برای ایجاد نمادهای دلخواه از تب Insert/ Symbol مطابق شکل ۱۶ عمل میکنیم:
شکل ۱۶- ایجاد نمادهای دلخواه با استفاده از Symbol
گام دوازدهم: تخصیص برچسب به نمودار
حالا باید دو ستون اختلاف مثبت و منفی که در واقع نمایانگر برچسب هر ستون هست، رو روی نمودار نشون بدیم. برای این کار برچسب مربوط به داده برنامه ای رو انتخاب میکنیم و Format Data Labels رو میزنیم. از قسمت Label Options گزینه Value From Cells رو میزنیم و داده های ستون “اختلاف منفی” رو انتخاب میکنیم.
شکل ۱۷- تخصیص برچسب به نمودار
همین کار رو برای داده های مربوط به “واقعی” هم انجام میدیم. برچسب مورد نظر رو انتخاب کرده و از قسمت value From Cells محدوده مربوط به اختلاف مثبت را انتخاب میکنیم.
حالا فقط کافیه که label ها رو ی مقدار بیاریم بالاتر که روی ستون ها قرار نگیره. برای اینکار کافیه روی یکی از لیبل ها کلیک کنیم و با زدن + مطابق شکل ۱۸ از قسمت Data Labels گزینه Above رو بزنیم. همین کار و برای برچسب های سری دوم (واقعی) هم انجام میدیم.
شکل ۱۸- جانمایی برچسب ها در بالای ستون نمودار برای مقایسه دو نمودار در اکسل
تو این آموزش در مورد مقایسه دو نمودار در اکسل و نحوه استفاده از فرمت سلول ها در نمودار صحبت کردیم حتما فایل نمونه این آموزش رو دانلود کنید و یکبار دیگه ایجاد این نوع نمودار رو تمرین کنید تا به اندازه کافی مسلط بشید. همچنین برای تمرین بیشتر میتونید مقاله رسم نمودار کاربردی گلوله ای در اکسل رو هم نگاه کنید.
دانلود فایل آموزش مقایسه دو نمودار در اکسل
برای دانلود فایل این آموزش بر روی دکمه زیر کلیک کنید:
با سلام
ممنون از سایت خوبتون
فقط در منطق انجام محاسبات اشتباهی انجام دادید
باید بودجه واقعی رو از بودجه برنامه ربزی شده کسر نکنید نه برعکس
تمام مثبت منفی ها بر عکس شده
درود بر شما
ممنون از توجهتون
این نمودار داره نشون میده که نسبت به برنامه در چه وضعیتی هستیم
مثلا در خصوص فعالیت A داره میگه برنامه ۵۰۰ بوده، اما ۳۵۰ تا محقق شده. پس ۱۵۰ تا نسبت به برنامه عقبه
پس با این تفسیر، درسته
سلام وقتتون بخیر
شما دوتا نمودار را باهم مقایسه کردین ولی من میخوام ۳ تا نمودار رو با یه نمودار در یه شکل مقایسه کنم اگه امکانش هست راهنمایی بفرماییدنمودار هام هم ستونی هستند
درود
منطق کار رو یاد بگیرید، هر طور میخواید میتونید تغییر بدید
با سلام خدمت شما
نموداری که شما رسم نمودید زیاد کار بردی نمی باشد با روشی خیلی ساده تر دو نمودار را می توان در هم ادغام نمود و نموداری زیبا و کاربردی ایجاد کرد
مدیر پروژه بایستی با یک نگاه میزان پیشرفت واقعی را در مقابل برنامه را بدون هیچ توضیحی مشاهده کند
متاسفانه باکسی برای ارسال فایل قرار ندادین که اصلاح شده نمودار بالا را برای شما ارسال کنم
سلام
تشکر از ارسال نظرتون
قطعا نمیشه یک نسخه واحد برای هر نمودار پیچید و بسته به نیاز میتونه هر جایی استفاده بشه.
. مهم تنوع و جذابیت نمودار هست که خب مقداریش هم سلیقه هست!
اما میتونید نسخه مورد نظر خودتون رو ارسال بفرمایید.
اگر به ایمیل info@excelpedia.net ارسال بفرمائید خوشحالمون میکنید.
سلام ممکنه برای من هم ارسال بفرمایید
ممنون
سلام
خسته نباشین
در قسمت آخر که فرمول میزنم که عدد و سیمبل را ترکیب کنه، به مشکل میخوره و اون سیمبل ها میاد با چند سیمبل دیگه
عدد نشون نمیده
چیکار کنم؟
درود بر شما
نباید سلول مقصد رو فرمتشو عوض کنید. فقط برای فراخوانی سیمبل ها ، از سلولی استفاده کنید که قبلا اون کاراکتر رو تعیین کردید
فایل نمونه رو مشاهده کنید متوحه میشید
مشکل در فونته
تو فراخوانی مشکلی نداره
اونجا که قراره ۳۰% رو در کنار سیمبل دست نشون بده
نیتونه عدد رو نشون بده جاش چند سیمبل میاد و اون سیمبل های دست
به نظر میبرسه فونت سلولتون رو عوض کردیذ که همه عبارات رو به اون فونت نشون میده
در حالیکه در سلول جداگانه باید اون علامت ها وجود داشته باشن و بعد مورد استفاده قرار بگیرن
با سلام
در مرحله ۵ گزینه Up – down bar در اکسل من غیر فعال هست .چیکار باید کرد؟
درود بر شما
دقت کنید حتما نمودار رو درست انتخاب کرده باشید
سلام و خسته نباشید ببخشید من همه قسمت و به جزء قسمت برچسب رو با تابع Vlookup رو متوجه نشدم
درود بر شما
سوالتون و متوجه نشدم
کجا رو متوجه نشدید؟
فایل زیپ مشکل داره و باز نمیشه
لطفا بررسی کنید
دوست عزیز
این فایل مشکلی نداره
چندین بار امتحان شده
نسخه نرم افزار ZIP خودتون رو اپدیت کنید
سلام
خدا خیرتون بده
ممنون از زحمتی که کشیدید
دارم استفاده می کنم
کارمند هستم و بسیار مطالب تون مفیده
البته تازه شروع کردم به مطالعه
فقط اگر سوالی داشتیم، چطور می تونیم در ارتباط باشیم؟
باتشکر فراوان
درود بر شما
هم میتونید همینجا و در قالب کامنت سوالاتتون و مطرح کنید
هم اینکه در گروه تلگرامی اکسل پدیا که لینکش در فوتر سایت موجود هست.
ارتباط مستقیم با اساتید فقط برای دانشجویان و اکسل آموزانی هست که در دوره های (حضوری و غیرحضوری) اکسل پدیا شرکت کرده اند.
موفق باشید
فایل زیپ باز نمیشه و ارور میده
دوست عزیز
بررسی شد و مشکلی وجود نداره.
لطفا مجدد اقدام بفرمایید