عدم نمایش صفر در نمودار

سلام، برای نمودارهای متصل به هم مثل خطی، دایره ای، اسکاتر و غیره این امکان وجود دارد که داده های صفر یا #NA یا خالی را نادیده گرفت. (مشاهده آموزش در سایت مایکروسافت)

اما در نمودارهای جدا از هم مثل میله ای و غیره، خیر، این امکان نیست ولی با یک ترفند ساده می توان موضوع رو حل کرد.

اگر داده اصلی شما فیلتر شود، ردیف هایی که در فیلتر مخفی هست، در نمودار نشان داده نمی شوند

پس شما باید با هربار تعویض سال و یا ماه، داده های مرجع برای نمودار را فیلتر کرده که مقدار 0 را نشان ندهند و نیاز به کد VBA هست چون فیلتر اکسل با تغییر مقادیر فرمول، خودکار رفرش نمی شود.

برای اینکار:

1. محدوده ای که نمودار از روی آن رسم شده است (J4:O22) از شیت calc را به جدول (table) تبدیل کنید. کلید میانبر ctrl+t

2. از تب Table Design و سپس پنل properties نامی برای جدول خود بزارید (من فرض می کنم نوشتیم test)

3. ALT+F11 صفحه کلید را بزنید تا پنجره VBA در اکسل باز شود.

4. در پنجره باز شده از طریق پنل سمت چپ (بنام project) روی شیت Dashboard دوبار کلیک کنید و کد زیر را در آن قرار دهید:

Sub autoFilterData()
    Sheets("calc").ListObjects("test").Range.autoFilter Field:=4, Criteria1:="<>0", Operator:=xlAnd
End Sub

حالا کافیه در شیتی که نمودار دارید یک شکل (shap) دلخواه هم رسم کنید و روی آن راست کلیک کنید و گزینه Assign Macro... را بزنید. از توی لیست ماکرویی که آخر اسمش autoFilterData هست را انتخاب و اوکی کنید.

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

یادتون نره فایل با کد VBA را باید save as کنید و دومین فرمت لیست یعنی (*.xlsm) را انتخاب کنید و برای اجرا در دفعات بعد، پیام زرد رنگ هشدار روی macro enable کلیک کنید.

خیلی ممنونم بابت پاسخ گوییتون. الان میخواستم برای نمودار روبروش هم این کد رو وارد کنم ولی در هنگام اجرای کد خطای زیر رو میده

combine eror

ambigous name detected: auto filter data

ای نرو چجوری برطرف کنم؟

شما نمی تونید دو تابع با یک اسم داشته باشید.

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

فرض: اسم جدول جدید در مرحله 2 رو می زاریم test1

Sub autoFilterData()
    Sheets("calc").ListObjects("test").Range.autoFilter Field:=4, Criteria1:="<>0"
    Sheets("calc").ListObjects("test1").Range.autoFilter Field:=4, Criteria1:="<>0"
End Sub

خیلی ممنونم

تقریبا داشبوردم اماده شده ولی یه سری ایراد داره

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

از ظرفی در نمودار های سیلندر "بخش خالص پرداختی" در انتهای داشبورد نیز بعضی از سیلندها با اینکه تنظیمات رنگشون رو درست کردم اما باز هم با تغییر ماه به تنظیمات اولیه بازمیگردند و درست نمایش داده نمیشوند مثلا در نمودار های فروردین "در سمت دفتر مرکزی" و آبان "در سمت بندرعباس" این موضوع مشخص است.

ممنون میشم راهنمایی بفرمایید جناب یاوری

فایل پیوست پرسش است

سلام

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

در صورت وجود ارتباط و نیاز به اشاره به این سوال و پاسخ‌ها، لینک این سوال را در سوال جدید قرار دهید.