نسخه 3 افزونه تقویم شمسی با امکانات بیشتر منتشر شد!!! (برای مشاهده اینجا کلیک کن)
سبد خرید
0

محصولی در سبد خرید نیست.

بازگشت به فروشگاه
تمام دسته ها
  • تمام دسته ها
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • Power BI
  • Power Pivot
  • Power Query
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

Marimekko Chart
۵/۵ - (۱ امتیاز)

نمودار موزائیکی | 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

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

تعیین لیبل های نمودار

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

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

حذف نقاط نمودار نقطه ای

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

نکته:
گاهی اوقات ممکنه انتخاب اجزای نمودار با موس براحتی قابل انجام نباشه، در این مواقع از تب Format و قسمت Current selection به همه اجزای نمودار دسترسی خواهیم داشت.

 

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

نمودار موزائیکی

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

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

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

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

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

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

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

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

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

نمودار موزائیکی نهایی

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

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

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

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

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

آواتار
144

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

دیدگاه کاربران
  • محدثه ۶ دی ۱۴۰۳ / ۹:۵۲ ق٫ظ

    سلام قسمتی که سری جدید رو برای برچسب شرکت A اضاقه میکنیم رو متوجه نمیشم دقیقا میشه بگید چیکار باید کرد

    • آواتار
      حسنا خاکزاد ۶ دی ۱۴۰۳ / ۱۲:۲۳ ب٫ظ

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

  • حسین ۱ بهمن ۱۴۰۲ / ۳:۵۷ ب٫ظ

    سلام و وقت بخیر
    من به تازگی تو حوزه تحلیل داده شروع به یادگیری کردم… خواستم تشکر ویژه کنم از نویسنده محتوی اکثر مقالاتی که دارید… به شدت کاربردی و مفید بوده تا الان برام و مستقیم و غیر مستقیم ازش استفاده کردم … سربلند و شاد باشید …

    • آواتار
      حسنا خاکزاد ۲ بهمن ۱۴۰۲ / ۴:۲۲ ب٫ظ

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

  • مهدی ۱۹ بهمن ۱۳۹۸ / ۹:۳۸ ق٫ظ

    سلام. خسته نباشید.
    چرا وقتی می‌خوام تنظیمات Major و Minor را تعیین کنم توی تنظیمات محور افقی چنین گزینه‌ای ندارم؟
    http://s7.picofile.com/file/8387486718/Capture.JPG

    • سامان چراغی ۲۴ بهمن ۱۳۹۸ / ۷:۰۶ ب٫ظ

      تنظیمات Minor و Major برای محورها زمانی در دسترس هست که محور ها مثل محورهای نمودار Scatter یا Bar Chart برای نمایش فاصله استفاده بشوند.
      اما در نمودارهایی مثل نمودار Column که محور افقی صرفا برای تفکیک داده ها به گروه های مختلف هست امکان تعیین چنین تنظیماتی نیست.
      به بیان دیگه محورهای افقی نمودارهایی مثل نمودار Column صرفا توضیحی (هر چند به صورت عددی) برای طبقه بندی اطلاعات نمایش داده شده در نمودار هست اما در نموداری مثل Scatter نمودار افقی مثل یک خط کش عمل میکنه.

      • مهدی ۲۷ بهمن ۱۳۹۸ / ۱۰:۱۳ ق٫ظ

        ولی الان توی این آموزش از نمودار Area استفاده شده و این تنظیمات را هم اعمال کرده!

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

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

ارسال دیدگاه

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

توسط
تومان