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

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

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

Actual Vs. Target Chart in Excel 2 Examples Q - ایجاد نمودار KPI (واقعی و هدف) در اکسل

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

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

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

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

Actual vs. Target Chart in Excel Target Values as Bars - ایجاد نمودار KPI (واقعی و هدف) در اکسل

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

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

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

Insert Tab in the Ribbon - ایجاد نمودار KPI (واقعی و هدف) در اکسل۳- در گزینه Charts Group، روی آیکون “Clustered Column Chart” کلیک کنید.

Insert a Clustered Column Chart - ایجاد نمودار KPI (واقعی و هدف) در اکسل۴- در نمودار وارد شده در برگه کاری، بروی هر یک از میله ها برای مقدار واقعی کلیک کنید.

Click on the actual value bar in the chart - ایجاد نمودار KPI (واقعی و هدف) در اکسل۵- راست کلیک کرده و Format Data Series را انتخاب کنید.

Select Format Data Series in the Achievement Vs Target Chart - ایجاد نمودار KPI (واقعی و هدف) در اکسل۶- در پنجره Format Series (در اکسل ۲۰۱۰ یا نسخه های قبلی یک کادر محاوره ای باز می شود)، در Series options ، گزینه Secondary Axis را انتخاب کنید.

Select secondary axis in the Format Data Series dialog box - ایجاد نمودار KPI (واقعی و هدف) در اکسل۷- نمودار شما باید تا حدودی به صورت زیر نمایش داده شود.

Actual Vs Target Chart in Excel when secondary Axis is selected - ایجاد نمودار KPI (واقعی و هدف) در اکسل۸- حالا، هر یک از میله های مقادیر هدف را انتخاب کنید (به سادگی بر روی نوار آبی رنگ کلیک کنید)، راست کلیک کنید و «Format Series Data» را انتخاب کنید.

Select Target value bar and right click - ایجاد نمودار KPI (واقعی و هدف) در اکسل۹- در کادر «Format Data Series» (یا کادر محاوره ای اگر از اکسل ۲۰۱۰ یا نسخه های قبلی استفاده می کنید)، مقدار Gap Width را کاهش دهید (من آن را به ۱۰۰٪ تغییر دادم). نظر این است که عرض میله ها را افزایش دهیم تا آن را از حالت نرمال عریض تر کنیم.

Actual Vs Target Chart in Excel change gap width to 100 - ایجاد نمودار KPI (واقعی و هدف) در اکسل۱۰- نمودار شما باید مانند شکل زیر باشد.

Actual Vs Target Chart in Excel chart when the gap width is lowered - ایجاد نمودار KPI (واقعی و هدف) در اکسل۱۱- (گام اختیاری) هر یک از میله های مقدار واقعی را انتخاب کنید. روی فرم راست کلیک کرده و Format Data Series را انتخاب کنید. مقدار gap width را ۲۰۰٪ کنید. این کار عرض میله های مقادیر واقعی را کاهش می دهد.

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

Delete the secondary Axis Line - ایجاد نمودار KPI (واقعی و هدف) در اکسل۱۳- نمودار شما آماده است. نمودار را فرمت کنید میله مقادیر هدف را در یک رنگ روشن برای ایجاد یک کنتراست، سایه دار کنید.

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

Actual Vs Target Chart in Excel Final output bars Blue - ایجاد نمودار KPI (واقعی و هدف) در اکسل

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

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

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

Actual Vs Target Chart in Excel Using Marker Lines - ایجاد نمودار KPI (واقعی و هدف) در اکسل

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

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

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

Insert Tab in the Ribbon 1 - ایجاد نمودار KPI (واقعی و هدف) در اکسل۳- در گزینه Charts Group، روی آیکون ‘Clustered Column Chart’ کلیک کنید.

Insert a Clustered Column Chart 1 - ایجاد نمودار KPI (واقعی و هدف) در اکسل۴- در نمودار وارد شده در برگه، بروی هر یک از میله ها  برای مقدار هدف کلیک کنید.

Planned vs Actual Chart in Excel Select target bars - ایجاد نمودار KPI (واقعی و هدف) در اکسل۵- همراه با انتخاب ستون های هدف ، راست کلیک کنید و گزینه ‘Change Chart Type’ را انتخاب کنید.

Planned vs Actual Chart in Excel Change series chart type - ایجاد نمودار KPI (واقعی و هدف) در اکسل

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

Planned vs Actual Chart in Excel Line with markers - ایجاد نمودار KPI (واقعی و هدف) در اکسل۷- OK را کلیک کنید.

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

Planned vs Actual Chart in Excel Chart after changing to line chart - ایجاد نمودار KPI (واقعی و هدف) در اکسل۹- خط را انتخاب کنید، راست کلیک کنید و Format Data Series را انتخاب کنید.

Actual Vs Target Chart in Excel Format data series of the line chart - ایجاد نمودار KPI (واقعی و هدف) در اکسل۱۰ -در قسمت Format Series، آیکون «Fill & Line» را انتخاب کنید.

Actual Vs Target Chart in Excel select the fill and line icon - ایجاد نمودار KPI (واقعی و هدف) در اکسل۱۱- در گزینه های Line آیتم No Line را انتخاب کنید. این مورد خط را در نمودار حذف خواهد کرد و تنها نشانگرها باقی خواهند ماند.

Forecasted vs Target Chart No line - ایجاد نمودار KPI (واقعی و هدف) در اکسل۱۲- آیکون “Marker” را انتخاب کنید. اگر از نسخه اکسلی استفاده می کنید که کادر محاوره ای را به جای پنجره نشان می دهد، باید « Marker Options» را در کادر محاوره ای انتخاب کنید.

Select the marker option in Format Data Series - ایجاد نمودار KPI (واقعی و هدف) در اکسل۱۳- در گزینه های Marker ، Built-in را انتخاب کنید و نشانگری را که به شکل یک خط تیره است، انتخاب کنید.

Select the Marker that looks like a dash. - ایجاد نمودار KPI (واقعی و هدف) در اکسل

۱۴- سایز را به ۲۰ تغییر دهید. شما می توانید چک کنید چه سایزی برای نمودار شما بهتر است و سایز را بر همان اساس تنظیم کنید.

Change the marker size - ایجاد نمودار KPI (واقعی و هدف) در اکسل

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




17a408a89ac284e4fc1eee3787fd81bb?s=300&d=mm&r=g - ایجاد نمودار KPI (واقعی و هدف) در اکسل

پوریا بغدادی

من مباحث BI را در دانشگاه تهران آموخته ام. مجری و مشاور سیستم های هوش تجاری (BI) هستم. آموزش را برای علاقه شخصی پی میگیرم.

3
دیدگاه بگذارید

avatar
2 Comment threads
1 Thread replies
1 Followers
 
Most reacted comment
Hottest comment thread
3 Comment authors
ترانهپوریا بغدادیایوب Recent comment authors
  عضویت  
جدید ها قدیمی ها بیشترین رای
به دوستتان خبر دهید.
ایوب
مهمان
ایوب

خدا قوت میگم بهتون جناب بغدادی، هم کلاساتون و هم مطالب سایتتون عالی هستن.

ترانه
مهمان
ترانه

بسیار عالی