نمودار موزائیکی و یک مسئله پیچیده

نمودار موزائیکی | Marimekko Chart

نمودار موزائیکی در اکسل نموداری است از ترکیب دو نمودار ۱۰۰% stacked column و ۱۰۰% stacked bar. ویژگی اصلی این نمودار اینه که هم پهنا و هم ارتفاع این نمودار متغیر هست. البته این از نمودارهای آماده در اکسل نیست و باید اونو بسازیم. اما همونطور که قبلا گفتیم برای ساخت نمودار با توضیحاتی که داده شد، باید اول داده های مناسبی براش ایجاد کنیم. نمودار پایه ای که برای رسم نمودار موزاییکی استفاده میکنیم نمودار Stacked area هست.

فرض کنیم میخوایم نموداری برای داده های زیر رسم کنیم:

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

شکل ۱- نمودار موزاییکی – داده ها

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

مرحله اول:

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

شکل ۲- نمودار موزاییکی – محاسبه سهام تجمعی بازارها

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

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

=INDEX($B$11:$B$16,ROUNDDOWN((ROW(A1)-1)/3,0)+1)

ابتدای داده ها دو تا صفر هم اضافه میکنیم.

شکل ۳- نمودار موزاییکی – ایجاد داده های مورد نیاز نمودارسطحی

حالا باید جدول رو کامل کنیم و سهام مربوط به هر شرکت رو در جدول قرار بدیم. آرایش داده ها باید بصورتی باشه که اولین داده صفر و دومین و سومین داده سهم مربوط به هر شرکت قرار بگیره. برای این کار از تابع Index استفاده میکنیم که از روی داده های اصلی این الگو رو ایجاد کنه. فرمول زیر میاد چک میکنه و در هر ردیفی که مضرب سه هست، صفر میذاره و در غیر اینصورت داده مورد نظر از جدول شماره ۱.

=IF(MOD(ROWS(C10:C$10)-1,3)=0,0,INDEX(C$2:C$7,QUOTIENT(ROWS(C10:C$10)-1,3)+1))

شکل ۴ – نمودار موزاییکی – چیدن داده های موجود بر اساس الگوی مورد نیاز

با اعمال فرمول در کل جدول داده ها بصورت شکل ۶ ساختاردهی میشن.

شکل ۵- نمودار موزاییکی – آماده سازی داده ها برای رسم نمودار سطحی

مرحله دوم:

حالا داده ها رو انتخاب میکنیم و از تب insert نمودار Stacked area رو انتخاب میکنیم.

شکل ۶- نمودار موزاییکی – رسم نمودار سطحی روی داده ها

نمودار بصورت شکل ۷ نمایش داده میشه.

شکل ۷- نمودار موزاییکی – نمودار سطحی رسم شده بر اساس داده های محاسبه شده

مرحله سوم:

حالا باید ظاهر نمودار رو مطابق با خواستمون تنظیم کنیم. برای این موضوع اولین کار اینه که محور افقی رو تنظیم کنیم. روی محور X کلیک میکنیم و از قسمت Format Axis تنظیمات رو مطابق با شکل ۸ انجام میدیم. Major رو ۲۰ و minor رو روی ۱۰۰ تنظیم میکنیم.

شکل ۸- نمودار موزاییکی – تغییر تنظیمات محور افقی

بعد از تنظیم محور افقی، رنگ و فرمت ظاهری نمودار رو بصورت دلخواه تنظیم میکنیم و نموداری مشابه با شکل ۹ خواهیم داشت.

شکل ۹ – نمودار موزاییکی – تغییر فرمت ظاهری نمودار

حالا در مرحله آخر باید برچسب های هر قسمت نمودار رو تنظیم کنیم.

مرحله چهارم:

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

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

شکل ۱۰ – نمودار موزاییکی – انجام محاسبات برای نمایش برچسب نمودار

فرمول های نوشته شده رو در محدوده های مشخص شده در شکل ۱۰، درگ میکنیم. حالا باید سری های ایجاد شده برای مکان برچسب ها رو به نمودار اضافه کنیم. برای این کار یک بعد از انتخاب نمودار، از تب design گزینه select data رو میزنیم و سری جدید رو برای برچسب شرکت A اضافه میکنیم و نوع نمودار رو Scatter قرار میدیم. (شکل ۱۱)

شکل ۱۱- نمودار موزاییکی – اضافه کردن نمودار نقطه ای به نمودار سطحی برا یتعیین مکان برچسب

حالا همین کار رو برای همه سری ها انجام میدیم و اونها رو اضافه میکنیم به نمودار. محور X که برای همه سری ها مشترک هست، محور Y هم مطابق با محاسباتی خواهد بود که در شکل ۱۰ نمایش داده شد. بعد از اضافه کردن همه سری ها، نمودار مشابه شکل ۱۲ خواهد شد.

شکل ۱۲- نمودار موزاییکی –تعیین مکان برچسب ها

حالا کافیه نقاط نمودار Scatter یعنی (مارکرها) رو به حالت عدم نمایش در بیاریم و بجای نقطه ها، لیبل ها رو با مقادیر مورد نظر نمایش بدیم. لیبل هر نقطه، مقدار سهم هر شرکت از هر بازار هست که در داده های اصلی داشتیم. برای نمایش دادن این داده ها بجای برچسب مراحل زیر رو انجام میدیم:

1. روی یکی از نقاط سبز (محل قرار گیری لیبل شرکت A) کلیک میکنیم و مطابق مسیر زیر عمل میکنیم:

Design/ Add Chart Element/ Data Labels/ Center

2. حالا روی لیبل نمایش داده شده کلیک راست میکنیم و Format Data Label رو میزنیم و مطابق شکل ۱۳ داده های مربوط به شرکت A رو انتخاب میکنیم.

شکل ۱۳- نمودار موزائیکی – تعیین لیبل های نمودار

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

شکل ۱۴- نمودار موزاییکی – حذف نقاط نمودار نقطه ای

این سه مرحله رو برای چهار شرکت دیگه انجام میدیم و برچسب های نمودار به درستی و در جای درست نمایش داده میشه.

شکل ۱۵- نمودار موزائیکی

 برای اینکه در بالای نمودار، بازارها هم نمایش داده بشن، یک سری دیگه اضافه میکنیم (در ادامه سری های اضافه شده برای برچسب ها) و لیبل این سری رو بازارها قرار میدیم.

پس اول داده ها رو آماده میکنیم. برا یاین کار یک سری با مقدار ۱۰۰% (چون میخواهیم در بالای نمودار نمایش داده بشه) و مقادیری که نام بازار رو به سهم بازار وصل میکنه. مطابق شکل ۱۶.

شکل ۱۶- نمودار موزائیکی – نمایش سهام بازار در بالای نمودار

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

مراحل مشابه سه مرحله قبلی هست که مفصل روش کار رو توضیح دادیم:

1. سری ایجاد شده رو به نمودار اضافه میکنیم.
2. لیبل ها رو نمایش میدیم و مقادیر مورد نظر رو از سلول ها اننتخاب میکنیم.
3. مارکر رو از حالت نمایش خارج میکنیم.

با انجام همه مراحل بالا، نمودار نهایی بصورت زیر نمایش داده خواهد شد.

شکل ۱۷ – نمودار موزائیکی نهایی

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

در این آموزش علاوه بر نحوه رسم نمودار موزائیکی، نکات خیلی زیادی برای حرفه ای تر کردن نمودار ها مثل تعیین مکان قرار گیری برچسب نمودار، اضافه کردن سری های مختلف، تنظیمات گرافیکی و … یاد گرفتیم.

دانلود فایل نمونه این آموزش

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

ثبت ایمیل جهت دانلود فایل

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

آدرس ایمیل

گروه تخصصی اکسل پدیا | همراه شما در مسیر یادگیری

بارگذاری...