ایجاد نمودار KPI (واقعی و هدف) در اکسل

اگر کار شما گزارش دهی داده های واقعی و هدف است، ممکن است نمایش مقادیر واقعی (Actual) در مقابل مقادیر هدف (Actual or Plan) در یک نمودار اکسل را جهت ارائه مفید بدانید.

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

در نمودار سمت چپ، مقادیر هدف به صورت میله های خاکستری پهن نشان داده شده است و مقادیر به دست آمده واقعی به صورت میله های آبی باریک  نشان داده شده است.

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

1- نمودار مقادیر واقعی در مقابل هدف در اکسل – مقادیر  هدف به عنوان میله ها

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

بهتر است که مقادیر واقعی را همانطور که در نمودار نشان داده شده است، در سایه های تیره داشته باشیم. گام هایی برای ایجاد نمودار مقادیر واقعی در مقابل هدف وجود دارد که در ذیل ارائه شده‌اند:

1- داده های مقادیر هدف و واقعی را انتخاب کنید.

2- به تب Insert بروید.

3- در گزینه Charts Group، روی آیکون “Clustered Column Chart” کلیک کنید.

4- در نمودار وارد شده در برگه کاری، بروی هر یک از میله ها برای مقدار واقعی کلیک کنید.

5- راست کلیک کرده و Format Data Series را انتخاب کنید.

6- در پنجره Format Series (در اکسل 2010 یا نسخه های قبلی یک کادر محاوره ای باز می شود)، در Series options ، گزینه Secondary Axis را انتخاب کنید.

7- نمودار شما باید تا حدودی به صورت زیر نمایش داده شود.

8- حالا، هر یک از میله های مقادیر هدف را انتخاب کنید (به سادگی بر روی نوار آبی رنگ کلیک کنید)، راست کلیک کنید و «Format Series Data» را انتخاب کنید.

9- در کادر «Format Data Series» (یا کادر محاوره ای اگر از اکسل 2010 یا نسخه های قبلی استفاده می کنید)، مقدار Gap Width را کاهش دهید (من آن را به 100٪ تغییر دادم). نظر این است که عرض میله ها را افزایش دهیم تا آن را از حالت نرمال عریض تر کنیم.

10- نمودار شما باید مانند شکل زیر باشد.

11- (گام اختیاری) هر یک از میله های مقدار واقعی را انتخاب کنید. روی فرم راست کلیک کرده و Format Data Series را انتخاب کنید. مقدار gap width را 200٪ کنید. این کار عرض میله های مقادیر واقعی را کاهش می دهد.

12- روی مقادیر secondary axis در سمت راست نمودار کلیک کنید و Delete را بزنید.

13- نمودار شما آماده است. نمودار را فرمت کنید میله مقادیر هدف را در یک رنگ روشن برای ایجاد یک کنتراست، سایه دار کنید.

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

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

2- نمودار مقادیر واقعی در مقابل هدف در اکسل – مقادیر هدف به عنوان خطوط نشانه

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

مراحل ایجاد  نمودار مقادیر واقعی در مقایل هدف در اکسل در ذیل ارائه شده اند:

1- کل مجموعه داده را انتخاب کنید.

2- به تب Insert بروید.

3- در گزینه Charts Group، روی آیکون ‘Clustered Column Chart’ کلیک کنید.

4- در نمودار وارد شده در برگه، بروی هر یک از میله ها  برای مقدار هدف کلیک کنید.

5- همراه با انتخاب ستون های هدف ، راست کلیک کنید و گزینه ‘Change Chart Type’ را انتخاب کنید.

6- برای تغییر نوع نمودار، در کادر محاوره ای نمودار خطی با نشانگرها را انتخاب کنید.  که میله های مقادیر هدف را به یک خط با نشانگر تغییر می دهد.

7- OK را کلیک کنید.

8- نمودار شما باید چیزی شبیه به تصویر زیر باشد.

9- خط را انتخاب کنید، راست کلیک کنید و Format Data Series را انتخاب کنید.

10 -در قسمت Format Series، آیکون «Fill & Line» را انتخاب کنید.

11- در گزینه های Line آیتم No Line را انتخاب کنید. این مورد خط را در نمودار حذف خواهد کرد و تنها نشانگرها باقی خواهند ماند.

12- آیکون “Marker” را انتخاب کنید. اگر از نسخه اکسلی استفاده می کنید که کادر محاوره ای را به جای پنجره نشان می دهد، باید « Marker Options» را در کادر محاوره ای انتخاب کنید.

13- در گزینه های Marker ، Built-in را انتخاب کنید و نشانگری را که به شکل یک خط تیره است، انتخاب کنید.

14- سایز را به 20 تغییر دهید. شما می توانید چک کنید چه سایزی برای نمودار شما بهتر است و سایز را بر همان اساس تنظیم کنید.

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