سبد خرید
0

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

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

رسم نمودار شرطی در اکسل

نمودار شرطی
۴.۷/۵ - (۱۲ امتیاز)

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

منظور از نمودار شرطی نموداری است که مثلا برای اعداد بزرگتر از یک عدد خاص، قرمز و کمتر از یک عدد خاص، به رنگ سبز نمایش داده بشه.

رسم نمودار شرطی ستونی

فرض کنید میخواهیم نمودار وضعیت خطاهای پیش آمده در یک آزمایش رو طی هفته های متوالی رسم کنیم. اگر تعداد خطاها زیر ۵ باشه، قابل قبوله و مایلیم به رنگ سبز نمایش داده بشه و اگر تعداد خطا بالاتر از این عدد بود، غیرقابل قبول هست و باید به رنگ قرمز نمایش داده بشه.

داده های مربوط به تعداد خطای هر آزمایش

شکل ۱- داده های مربوط به تعداد خطای هر آزمایش

مرحله اول: تفکیک داده ها

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

=IF(B2<5,B2,NA())

تفکیک داده ها به دو گروه کوچکتر و بزرگتر از شرط مورد نظر

شکل ۲- تفکیک داده ها به دو گروه کوچکتر و بزرگتر از شرط مورد نظر

نکته:
استفاده از NA() در قسمت دوم تابع IF به این دلیل هست که در نمودار، سلولهایی که مقدار NA() دارند، نمایش داده نمیشوند.

 

مرحله دوم: انتخاب داده ها و رسم نمودار

مطابق شکل ۳ داده ها رو انتخاب کرده و نمودار Clustered Column رو میزنیم. دقت کنید محدوده داده ها بصورت ناپیوسته انتخاب میشه.(ستون A،  Cو D)

انتخاب داده ها

شکل ۳- انتخاب داده ها و رسم نمودار

با انتخاب نمودار مورد نظر، نمودار مطابق شکل ۴ رسم میشه.

Clustered Column

شکل ۴- انتخاب نمودار Clustered Column

مشاهده می کنید که ستون های قرمز رنگ بالای ۵ و ستون های آبی رنگ زیر ۵ هستند. همونطور که توضیح داده شد، مقادیر NA() در نمودار نمایش داده نمیشه.

حالا کافیه تنظیمات مربوط به فرمت رو انجام بدیم و فاصله بین ستون ها رو به دلخواه تنظیم کنیم.

در نمودار شکل ۵، Overlap Series نمودار روی ۱۰۰% و Gap Width آن روی ۹۰% تنظیم شده است.

نمودار ستونی شرطی

شکل ۵- نمودار ستونی شرطی

پس همونطور که مشاهده کردید، برای رسم نمودار شرطی با توجه به داده ها نمیشه یک نمودار رو فرمول نویسی کرد. بلکه داده ها رو طوری تفکیک میکنیم که داده های هر دسته، یک نمودار مجزا رو تشکیل بده و بعد با ترفند استفاده از NA() به خواسته مورد نظر میرسیم.

چند مقاله جالب دیگه که پیشنهاد میکنم حتما ببینی:

دانلود فایل اکسل این آموزش

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

کلیدواژه : پیشرفته
133

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

دیدگاه کاربران
  • f ۱۵ خرداد ۱۴۰۲ / ۱۰:۳۷ ق٫ظ

    سلام _ اصلا شفاف نگفتید که تعریف رنگ سبز و قرمز رو کجا باید انجام دهیم؟!

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

      درود
      تا شکل ۴ که واضح و مشخصه نمودار رو برای د سری داده رسم میکیند، بصورت پیشفرض با دو رنگ استاندارد نمایش میده مثلا نارنجی و ابی
      حالا کافیه تنظیمات نموودار رو انجام بدید مثلا روی یکی از میله های نارنجی کلیک کنید، از تب format رنگ رو سبز بذارید
      یا یک میله ابی رو انتخاب کنید (همه ابی ها انتخاب مبشه) بعدش رنگ دلخواه رو از تب فرمت مشخص کنید

      نتغییر گرافیک ظاهری نمودار در آموزش های قبلی سای هم اموزش داده شده
      ولی کلا از تب format همش رو میتونید ببینید

  • ملیحه ۷ اردیبهشت ۱۳۹۹ / ۵:۰۵ ب٫ظ

    سلام. ممنون از سایت مفید و خوبتون. من وقتی data lable را برای نشان دادن مقادیر در این نمودار فعال می کنم همراه با مقادیر خطای na# را هم نشان می دهد. لطفا راهنمایی کنین چطور مشکل را برطرف کنم. بسیار سپاسگزارم

    • آواتار
      حسنا خاکزاد ۸ اردیبهشت ۱۳۹۹ / ۱۲:۳۰ ب٫ظ

      درود
      داده های خطا دار رو با iferror مدیریت کنید

      • ملیحه ۱۰ اردیبهشت ۱۳۹۹ / ۶:۲۳ ب٫ظ

        سلام. ممنون از راهنمایی تون. ولی وقتی از iferror استفاده می کنم بجای خطای na بر روی نمودار عدد صفر نمایش داده می شود. در اینترنت نوشته شده که تنها خطایی که در نمودار نشان داده نمی شود و مقدار خالی را بر می گرداند خطای na است ولی متأسفانه روی نمودار برای من خود خطای na را نمایش می دهد

        • آواتار
          حسنا خاکزاد ۱۰ اردیبهشت ۱۳۹۹ / ۷:۰۷ ب٫ظ

          شما فرمودید data lable!
          اونجا خطا رو نشون میده
          در مقادیر مثلا مودار ستونی ، این خطا نمایش داده نمیشه.
          همین مقاله بالا رو ببینید
          لیبل رو باید مدیریت کنید

          • ملیحه ۱۱ اردیبهشت ۱۳۹۹ / ۹:۳۰ ب٫ظ

            سلام. بالاخره مشکلم با مدیریت Data lable ها حل شد. بسیار بسیار سپاسگزارم

  • msalimi ۱۵ آذر ۱۳۹۷ / ۷:۲۳ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۱۵ آذر ۱۳۹۷ / ۸:۱۶ ب٫ظ

      درود بر شما
      بله همونطور که داخل آموزش تشریح شده، با شاخص Gap و Overlap میشه ای فاصله رو کنترل کرد

  • soorim91 ۱۲ مرداد ۱۳۹۷ / ۶:۱۹ ب٫ظ

    سلام و خداقوت به تیم خوب شما
    یه سوال؟
    اگر من فرمول رو این طور می‌نوشتم باز درست بود ؟
    IF(B4<10,B4,"") یعنی
    NA دیگه از تابع بدون آرگومان استفاده نمیکردم

    • سامان چراغی ۱۲ مرداد ۱۳۹۷ / ۹:۱۷ ب٫ظ

      سلام و تشکر
      مزیت استفاده از تابع NA در این فرمول اینه که دیگه مقداری برای سطر مورد نظر در نمودار نمایش داده نمیشه، اما اگه به جای NA از “” استفاده کنید در واقع در نمودار برای سطر موردنظر مقدار صفر در نظر گرفته میشه و خیلی جالب به نظر نمیرسه.

  • داود ۳ خرداد ۱۳۹۷ / ۰:۰۸ ق٫ظ

    مرسی .. از راهنمایی شما ….
    ولی اصلا متوجه نشدم …..

    • آواتار
      حسنا خاکزاد ۳ خرداد ۱۳۹۷ / ۹:۲۹ ق٫ظ

      این فرمول آرایه ای هست و مبحث پیشرفته. مجال تشریحش اینجا نیست.
      این لینک رو بخونید که با فمرول نویسی آرایه ای آشنا بشید.

      https://excelpedia.net/array-formula/

      بعد این فرمول رو کپی کنید و بعد از کپی کردن Ctrl+Shift+Enter رو بزنید و بعد درگ کنید. لیستی از داده هاییکه Yes هستن رو میاره.

    • آواتار
      حسنا خاکزاد ۲ خرداد ۱۳۹۷ / ۱۰:۳۴ ق٫ظ

      درود بر شما
      فرمول زیر رو بصورت آرایه ای ثبت کنید و درگ کنید.
      فرمول آرایه ای با Ctrl+Shift+Enter ثبت میشه.

    • سامان چراغی ۲ خرداد ۱۳۹۷ / ۱۰:۳۵ ق٫ظ

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

ارسال دیدگاه

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

توسط
تومان