ثبت نام دوره آنلاین VBA به زبان ساده
سبد خرید
0

سبد خرید شما خالی است.

جستجو
Generic filters

مقایسه نحوه عملکرد واقعی و برنامه ای با نمودار در اکسل

مقایسه دو نمودار در اکسل
۵/۵ - (۱۰ امتیاز)

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

همونطور که قبلا هم در مورد رسم نمودار صحبت کردیم، نمودار در اکسل مبحث خیلی مفصل و پرکاری هست که پر از جزئیات و مسائل حرفه ای هست. یکی از راه های تسلط به مبحث رسم نمودار در اکسل دیدن نمونه های مختلف و بررسی همه جزئیات هست. در این مقاله میخوایم نمودار عملکرد واقعی در برابر عملکرد برنامه ریزی شده ( در واقع مقایسه دو نمودار در اکسل ) رو به همراه میزان اختلاف آنها نمایش بدیم.

نمودار نمایش داده برنامه ای در برابر داده واقعی

شکل ۱- مقایسه دو نمودار در اکسل برای نمایش داده برنامه ای در برابر داده واقعی

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

گام اول مقایسه دو نمودار: آماده کردن داده ها

همونطور که در شکل ۲ می بینید، عملکرد برنامه ریزی شده هر فعالیت در مقابل عملکرد واقعی نمایش ثبت شده. برای اینکه بتونیم میزان اختلاف این دو عدد رو برای هر فعالیت محاسبه کنیم باید محاسباتی رو انجام بدیم. برای محاسبه میزان اختلاف به درصد، کافیه عملکرد برنامه ای رو از عملکرد واقعی کم کنیم و نتیجه رو تقسیم بر عملکرد برنامه ای کنیم. محاسبات رو در شکل ۲ می بینید:

داده های واقعی و برنامه ای

شکل ۲- داده های واقعی و برنامه ای

گام دوم مقایسه دو نمودار: ایجاد نمودار ستونی

داده های برنامه ای و واقعی رو انتخاب کرده و نمودار ستونی رو انتخاب میکنیم.

رسم نمودار ستونی از داده های واقعی و برنامه ای

شکل ۳- رسم نمودار ستونی از داده های واقعی و برنامه ای

گام سوم مقایسه دو نمودار: تکرار مرحله دوم

در این مرحله باید دوباره داده های واقعی و برنامه ای رو به نمودار اضافه کنیم. برای این کار از تب Design و قسمت select data گزینه add رو زده و دو ستون واقعی و برنامه ای رو مجدد اضافه میکنیم.

اضافه کردن مجدد محدوده داده واقعی و برنامه ای به نمودار

شکل ۴- اضافه کردن مجدد محدوده داده واقعی و برنامه ای به نمودار

گام چهارم مقایسه دو نمودار: تبدیل دو سری جدید اضافه شده، از ستونی به خطی

حالا باید دو سری که مجددا به نمودار اضافه کردیم رو به نمودار خطی تبدیل کنیم. برای این کار نمودار رو انتخاب کرده و از تب Design گزینه Change Chart Type رو میزنیم. از پنجره باز شده و مطابق شکل ۵، دو سری جدید رو به نمودار line تغییر میدیم.

تبدیل دو سری جدید اضافه شده به نمودار 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

شکل ۱۶- ایجاد نمادهای دلخواه با استفاده از Symbol

گام دوازدهم: تخصیص برچسب به نمودار

حالا باید دو ستون اختلاف مثبت و منفی که در واقع نمایانگر برچسب هر ستون هست، رو روی نمودار نشون بدیم. برای این کار برچسب مربوط به داده برنامه ای رو انتخاب میکنیم و Format Data Labels رو میزنیم. از قسمت Label Options گزینه Value From Cells رو میزنیم و داده های ستون “اختلاف منفی” رو انتخاب میکنیم.

تخصیص برچسب

شکل ۱۷- تخصیص برچسب به نمودار

همین کار رو برای داده های مربوط به “واقعی” هم انجام میدیم. برچسب مورد نظر رو انتخاب کرده و از قسمت value From Cells محدوده مربوط به اختلاف مثبت را انتخاب میکنیم.

حالا فقط کافیه که label ها رو ی مقدار بیاریم بالاتر که روی ستون ها قرار نگیره. برای اینکار کافیه روی یکی از لیبل ها کلیک کنیم و با زدن + مطابق شکل ۱۸ از قسمت  Data Labels گزینه Above رو بزنیم. همین کار و برای برچسب های سری دوم (واقعی) هم انجام میدیم.

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

شکل ۱۸- جانمایی برچسب ها در بالای ستون نمودار برای مقایسه دو نمودار در اکسل

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

دانلود فایل آموزش مقایسه دو نمودار در اکسل

برای دانلود فایل این آموزش بر روی دکمه زیر کلیک کنید:

[esd id=”28″ link=”https://dl.dropboxusercontent.com/s/ginq67vv2jelveb/Excelpedia-budget-vs-actual-chart.rar?dl=0″ subject=”لینک دانلود فایل آموزش نمودار مقایسه ای” btntext=”دریافت فایل این آموزش” type=”mail_type” theme=”theme_d” mailer_group=”8466450″ position=”right” icon=”fa fa-download” description=”برای دانلود فایل کلیک کنید”]

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

دیدگاه کاربران
  • فروغ ۳۰ آذر ۱۳۹۹ / ۱۰:۰۶ ق٫ظ

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

    • حسنا خاکزاد ۳۰ آذر ۱۳۹۹ / ۱۲:۱۵ ب٫ظ

      درود بر شما
      ممنون از توجهتون
      این نمودار داره نشون میده که نسبت به برنامه در چه وضعیتی هستیم
      مثلا در خصوص فعالیت A داره میگه برنامه ۵۰۰ بوده، اما ۳۵۰ تا محقق شده. پس ۱۵۰ تا نسبت به برنامه عقبه
      پس با این تفسیر، درسته

  • علیرضا ۲۸ مرداد ۱۳۹۹ / ۴:۵۰ ب٫ظ

    سلام وقتتون بخیر
    شما دوتا نمودار را باهم مقایسه کردین ولی من میخوام ۳ تا نمودار رو با یه نمودار در یه شکل مقایسه کنم اگه امکانش هست راهنمایی بفرماییدنمودار هام هم ستونی هستند

    • حسنا خاکزاد ۲۸ مرداد ۱۳۹۹ / ۵:۵۷ ب٫ظ

      درود
      منطق کار رو یاد بگیرید، هر طور میخواید میتونید تغییر بدید

  • بهرنگ ۷ آبان ۱۳۹۸ / ۴:۳۰ ب٫ظ

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

    • سامان چراغی ۱۲ آبان ۱۳۹۸ / ۹:۰۰ ق٫ظ

      سلام
      تشکر از ارسال نظرتون
      قطعا نمیشه یک نسخه واحد برای هر نمودار پیچید و بسته به نیاز میتونه هر جایی استفاده بشه.
      . مهم تنوع و جذابیت نمودار هست که خب مقداریش هم سلیقه هست!

      اما میتونید نسخه مورد نظر خودتون رو ارسال بفرمایید.
      اگر به ایمیل [email protected] ارسال بفرمائید خوشحالمون میکنید.

    • forough ۳۰ آذر ۱۳۹۹ / ۲:۲۵ ب٫ظ

      سلام ممکنه برای من هم ارسال بفرمایید
      ممنون

  • مونا مهرزاد ۴ تیر ۱۳۹۸ / ۲:۲۱ ب٫ظ

    سلام
    خسته نباشین
    در قسمت آخر که فرمول میزنم که عدد و سیمبل را ترکیب کنه، به مشکل میخوره و اون سیمبل ها میاد با چند سیمبل دیگه
    عدد نشون نمیده
    چیکار کنم؟

    • حسنا خاکزاد ۴ تیر ۱۳۹۸ / ۳:۱۷ ب٫ظ

      درود بر شما
      نباید سلول مقصد رو فرمتشو عوض کنید. فقط برای فراخوانی سیمبل ها ، از سلولی استفاده کنید که قبلا اون کاراکتر رو تعیین کردید

      فایل نمونه رو مشاهده کنید متوحه میشید

      • مونا مهرزاد ۴ تیر ۱۳۹۸ / ۳:۳۸ ب٫ظ

        مشکل در فونته
        تو فراخوانی مشکلی نداره
        اونجا که قراره ۳۰% رو در کنار سیمبل دست نشون بده
        نیتونه عدد رو نشون بده جاش چند سیمبل میاد و اون سیمبل های دست

        • حسنا خاکزاد ۴ تیر ۱۳۹۸ / ۷:۱۶ ب٫ظ

          به نظر میبرسه فونت سلولتون رو عوض کردیذ که همه عبارات رو به اون فونت نشون میده
          در حالیکه در سلول جداگانه باید اون علامت ها وجود داشته باشن و بعد مورد استفاده قرار بگیرن

  • سید علیرضا حسینی ۷ اسفند ۱۳۹۷ / ۵:۳۸ ب٫ظ

    با سلام
    در مرحله ۵ گزینه Up – down bar در اکسل من غیر فعال هست .چیکار باید کرد؟

    • حسنا خاکزاد ۸ اسفند ۱۳۹۷ / ۹:۱۶ ق٫ظ

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

  • مجتبی خانی ۳۰ بهمن ۱۳۹۷ / ۸:۱۲ ب٫ظ

    سلام و خسته نباشید ببخشید من همه قسمت و به جزء قسمت برچسب رو با تابع Vlookup رو متوجه نشدم

    • حسنا خاکزاد ۳۰ بهمن ۱۳۹۷ / ۸:۱۷ ب٫ظ

      درود بر شما
      سوالتون و متوجه نشدم
      کجا رو متوجه نشدید؟

  • arash mohammadi ۱۸ مهر ۱۳۹۷ / ۱۰:۵۸ ق٫ظ

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

    • اکسل پدیا ۱۸ مهر ۱۳۹۷ / ۱۲:۲۸ ب٫ظ

      دوست عزیز
      این فایل مشکلی نداره
      چندین بار امتحان شده
      نسخه نرم افزار ZIP خودتون رو اپدیت کنید

  • سید حمید حسینی کیا ۵ شهریور ۱۳۹۷ / ۱:۱۵ ب٫ظ

    سلام
    خدا خیرتون بده
    ممنون از زحمتی که کشیدید
    دارم استفاده می کنم
    کارمند هستم و بسیار مطالب تون مفیده
    البته تازه شروع کردم به مطالعه
    فقط اگر سوالی داشتیم، چطور می تونیم در ارتباط باشیم؟
    باتشکر فراوان

    • اکسل پدیا ۵ شهریور ۱۳۹۷ / ۱:۵۷ ب٫ظ

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

      ارتباط مستقیم با اساتید فقط برای دانشجویان و اکسل آموزانی هست که در دوره های (حضوری و غیرحضوری) اکسل پدیا شرکت کرده اند.
      موفق باشید

  • f.razeghi ۳۰ مرداد ۱۳۹۷ / ۱:۱۷ ب٫ظ

    فایل زیپ باز نمیشه و ارور میده

    • اکسل پدیا ۳۰ مرداد ۱۳۹۷ / ۱:۳۹ ب٫ظ

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

ارسال دیدگاه

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

توسط
تومان