ترسیم یک نمودار میله ای شرطی در اکسل + مثال

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

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

در این مقاله دو نمودار را به گونه ای سفارشی سازی می کنیم که به واقع مخاطبان شما را شگفت زده می کند. حتی ممکن است در ارتقاء سطح شغلی نیز به شما کمک کند. اما شما هم قول بدید که این مقاله رو با دوستانتون به اشتراک بگذارید. 😊

سفارشی سازی شماره ۱

در این سفارشی سازی قصد داریم میله های مثبت و منفی را با رنگ بندی های متفاوت از همدیگر متمایز کنیم.

سفارشی سازی شماره ۲

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

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

نکته دوم: بسیاری از این سفارشی‌سازی‌ها را می‌توان برای انواع نمودار اعمال کرد. بنابراین میزان توانایی و خلاقیت خودتان را به نمودارهای ستونی یا میله ای محدود نکنید.

داده های نمودار میله ای

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

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

۱. سلول های A2 تا B7 را انتخاب کنید و از طریق مسیر زیر یک نمودار میله ای دو بعدی Clustered را بسازید:

Insert (tab) –> Charts (group) –> 2-D Bar –> Clustered Bar

نکته: دقت کنید نمودار میله ای افقی (۲D Bar) بسازید نه نمودار ستونی (۲D Column).

۲. خطوط شبکه (Grid lines) اصلی محور افقی را حذف کنید.

۳. محور افقی را حذف کنید.

۴. روی یکی از میله ها کلیک راست کنید و با انتخاب گزینه Add Data Labels برچسب اعداد را برای میله ها فعال کنید.

۵. گروه نام محصولات را از محور عمودی انتخاب کنید و فونت آنها را به اندازه ۱۰ و Bold تغییر دهید. در صورتی که دوست دارید، فونت فارسی اضافه کنید.

۶. در این مرحله فاصله (Gap) بین میله ها را کم می کنیم. یکی از میله ها را با یکبار کلیک کردن انتخاب می کنیم، سپس کلید CTRL + 1 را فشار می دهیم تا پنل Format Data Series باز شود. آیکون Series Option را انتخاب کنید و مقدار Gap Width را به ۸۰% تغییر دهید.

۷. در حالی که میله ها انتخاب شده اند، از طریق مسیر زیر رنگ آنها را به خاکستری متوسط تغییر دهید:

Format (tab) –> Shape Styles (group) –> Shape Fill

۸. در این مرحله Border و مرز نمودار را حذف می کنیم. کناره نمودار را انتخاب کنید و سپس از مسیر زیر اقدام کنید:

Format (tab) –> Shape Styles (group) –> Shape Outline –> No Outline

۹. پهنای نمودار را با استفاده از دستگیره های کناری آن، به میزان ۵۰ درصد کم کنید.

۱۰. در این مرحله ترتیب نام محصولات را در نمودار تغییر می دهیم. روی محور محصولات دو بار کلیک می کنیم و از پنل باز شده و از قسمت Axis Options تیک Categories in Reverse Order را می زنیم.

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

Chart Design (tab) –> Chart Layouts (group) –> Add Chart Element –> Grindlines –> Primary Major Horizontal

نکته: مطمئن شوید که منطقه پرینتی (plot Area) با توجه به نشان دادن بیشترین مقدار محصول نسبت به منطقه نمودار (Chart Area)، نسبت مورد نظر را رعایت کرده باشید. در کل اینکار با سعی و خطا، بهتر و بهتر خواهد شد.

با اعمال همه این تغییرات، نمودار نهایی ما به شکل زیر خواهد شد:

ساخت نمودار میزان تفاوت مقدار واقعی و پیش بینی ها

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

ممکن است این سوال به ذهن شما برسد که: “من نمی خوام تمام این کارها رو دوباره تکرار کنم. آیا کلید میانبری هم برای این کار هست؟” بله هست.

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

• در سلول E1 برای داده های جدید، یک عنوان درج می کنیم با نام “آماده سازی داده ها”.
• در سلول E2 هم یک زیرعنوان درج می کنیم با نام “میزان اختلاف”.
• و در نهایت فرمول زیر را در سلول E3 درج می کنیم و این فرمول را تا سلول E7 ادامه می دهیم:

B3 – C3 =

• ستون نام محصولات و ستون مقدار اختلاف داده ها را انتخاب کنید (سلول های A2 تا A7 را هایلایت کنی، دکمه CTRL را نگه دارید و سلول های E2 تا E7 را انتخاب کنید).
• مجددا به منوی Insert بروید و این بار، یک نمودار میله ای دو بعدی ایجاد کنید:

Insert (tab) –> Charts (group) –> 2-D Bar -> Clustered Bar

خب حالا به سراغ کلید میانبری می رویم که قبلاً بیان کردیم…

• لبه های نمودار اول را (که قبلاً تنظیم کردیم) انتخاب کنید و کلیدهای CTRL + C را بزنید (یا به هر روش دیگری کپی بگیرید).
• لبه های نمودار دوم را انتخاب کنید و به این طریق عمل می کنیم: به منوی Home بروید و سپس گروه Clipboard به بخش Paste بروید و زیربخش Paste Special و سپس گزینه Formats را انتخاب کنید.

این هم از این کلید میانبر!!!! راستش رو بگید چند نفر این حقه رو بلد بودید!؟ 😎

الان دو نمودار داریم با جزئیات بصری یکسان، با همه مشخصات ظاهری. تنها آیتمی که با این کلید میانبر کپی نمی شود، پهنای نمودار است که چاره ای نداریم جز اینکه به صورت دستی این مورد را هم برطرف و تنظیم کنیم.

با انجام برخی سفارشی سازی ها برای نمودار دوم، کار را ادامه می دهیم:

می خواهیم نام محصولات را از محور عمودی حذف کنیم: بر روی نام محصولات در نمودار دوم دوبار کلیک کنید تا پنل تنظیمات در سمت راست باز شود. از قسمت Axis Options به قسمت Labels بروید و برای Label Position مقدار None را انتخاب کنید.

سفارشی سازی شماره ۱: میله های با رنگ بندی متفاوت برای مقادیر منفی و مثبت

برای شروع، قصد داریم نمودارها را به یکدیگر نزدیکتر کنیم تا تصور دو نموداره بودن از ذهن ما برداشته شود و تصور شود یک نمودار داریم:

1. در حالی که میله های نمودار دوم انتخاب شده است، به آدرس زیر می رویم و رنگ داخل این میله ها را بر می داریم و عملاً آنها را فاقد رنگ می کنیم.
2. هر دو نمودار را کنار یکدیگر قرار می دهیم.
3. سپس یکی از میله های همان نمودار دوم را انتخاب کنید، کلیک راست و گزینه Format Data Series بزنید. از قسمت Fill & Line گزینه Fill و سپس گزینه رادیویی Solid Fill را انتخاب کنید و تیک مربوط به Invert if Negative را بزنید. انتخاب این گزینه باعث می شود دو رنگ فعال شود: یکی برای مقادیر مثبت و یکی هم برای مقادیر منفی.

الان ما رنگ بندی های شرطی را برای نمودارهایمان فعال کرده ایم.

سفارشی سازی شماره ۲: تعیین رنگی متفاوت برای میله ای که بیشترین مقدار را دارد

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

برای این مثال، می‌خواهیم میله ای که بیشترین مقدار را دارد، با رنگی متفاوت نمایش داده شود. حتی اگر مثلاً مقدار دو میله با هم برابر باشد و آن مقدار بیشترین مقدار آن نمودار باشد، هر دو میله یک رنگ متمایز از میله های دیگر خواهند گرفت.

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

• در سلول F2 برای داده های جدید، یک عنوان درج می کنیم با نام “بیشترین شرط”.
• و در نهایت فرمول زیر را در سلول F3 درج می کنیم و این فرمول را تا سلولF7 ادامه می دهیم:

IF (B3 = MAX($B$3:$B$7), B3, “”) =

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

• نمودار اول را انتخاب کنید و به آدرس زیر بروید:

Chart Design (tab) –> Data (group) –> Select Data

در پنجره Select Data Source، زیر گزینه Legend Entries روی گزینه Add کلیک کنید و پارامترهای زیر را در پنجره Edit Series وارد کنید:

یا اضافه کردن سری داده های جدیدی که الان اعمال کردیم، نمودار ما به شکل زیر در می آید:

برای اینکه میله نارنجی رنگ نشان داده شده در شکل بالا روی میله خاکستری رنگ قرار گیرد و همپوشانی انجام شود، میله نارنجی را انتخاب کنید و راست کلیک کنید و Format Data Series را کلیک کنید و از قسمت Series Option بخش Series Overlap را ۱۰۰% بگذارید.

در حالی که میله نارنجی رنگ همچنان انتخاب است، به آدرس زیر بروید و رنگ آن را به رنگ دلخواه تغییر دهید:

Format (tab) -> Shape Styles (group) -> Shape Fill -> Blue

حال اگر مقدار داده ها را به گونه ای تغییر دهیم که دو مقدار به صورت مشترک، بیشترین مقدار شوند، میله مربوط به هر دو مقدار آبی رنگ می شوند:

تنظیمات پایانی برای نمودارها

یکی از عناوین نمودارها را طوری جابجا کنید که به وسط هر دو نمودار آورده شود و در ضمن، این عنوان جدید را به مقدار سلول B1 لینک کنید.

هر دو نمودار را انتخاب کنید و از طریق آدرس زیر آنها را با یکدیگر ادغام کنید:

Shape Format (tab) -> Arrange (tab) -> Group