Sparkline و نمودار پویا با جدول، لیست کشویی و نام تعریف‌شده

نمودار به اندازه‌ی یک سلول، نمودار به اندازه‌ی یک انتخاب

دو ابزار این درس داشبورد را از «تصویر ثابت» به «گزارش زنده» تبدیل می‌کنند: Sparkline که روند هر سطر را درون یک سلول نشان می‌دهد، و نمودار پویایی که با انتخاب از یک لیست کشویی یا اضافه شدن داده‌ی جدید، خودش به‌روز می‌شود.

Sparkline: روند ۱۲ ماه کنار هر نماینده

جدولی دارید که هر سطرش یک نماینده و ستون‌های B تا M فروش فروردین تا اسفند است. ستون N را انتخاب کنید ← Insert ← Sparklines، و Data Range را B2:M20 بدهید.

در تب Sparkline تیک High Point، Low Point و Negative Points را بزنید. مهم‌ترین تنظیم پنهان در منوی Axis است: به‌طور پیش‌فرض هر Sparkline مقیاس خودش را دارد، پس نماینده‌ای با فروش ۲ میلیارد و نماینده‌ای با ۲۰ میلیارد، خطوط هم‌اندازه دارند. برای مقایسه‌ی منصفانه، Axis ← Vertical Axis Minimum و Maximum ← Same for All Sparklines.

نمودار پویا ۱: منبع جدول

ساده‌ترین نمودار پویا، نموداری است که روی Excel Table ساخته شود. با اضافه شدن ماه جدید به جدول، سری نمودار خودکار بزرگ می‌شود و نیازی به ویرایش Select Data نیست. اگر نمودارتان هر ماه دستی اصلاح می‌شود، اول منبعش را جدول کنید.

نمودار پویا ۲: انتخاب نماینده از لیست کشویی

در سلول H1 یک لیست کشویی از نام نمایندگان بسازید (فصل ۵). سپس یک محدوده‌ی کمکی بسازید که با انتخاب عوض شود و نمودار را به آن وصل کنید. چون کادر Series values ارجاع Spill (علامت #) را مستقیم نمی‌پذیرد، از نام تعریف‌شده استفاده می‌کنیم:

Excel 365:
H3:  =TRANSPOSE(tblRep[[#Headers],[فروردین]:[اسفند]])
I3:  =TRANSPOSE(XLOOKUP($H$1, tblRep[نماینده], tblRep[[فروردین]:[اسفند]]))

Name Manager:
  chMonths   =Dash!$H$3#
  chSales    =Dash!$I$3#

Excel 2016/2019 (بدون Spill):
  chSales    =INDEX(tblRep[[فروردین]:[اسفند]], MATCH(Dash!$H$1, tblRep[نماینده], 0), 0)

Select Data ← Edit:
  Series values:   ='Sales-1403.xlsx'!chSales
  Axis labels:     ='Sales-1403.xlsx'!chMonths

عنوان پویا: H5 =  "فروش ماهانه‌ی " & $H$1
عنوان نمودار را انتخاب کنید، در نوار فرمول بنویسید  =Dash!$H$5

حالا با انتخاب «رضا کاشانی» از لیست، نمودار و عنوانش هم‌زمان عوض می‌شوند. نسخه‌ی INDEX یک ارجاع (نه کپی مقدار) برمی‌گرداند، به همین دلیل در نام تعریف‌شده و نمودار کار می‌کند و برخلاف OFFSET تابع Volatile هم نیست.

نکته‌هایی که کمتر کسی می‌داند

• Sparkline در «پس‌زمینه»ی سلول رسم می‌شود؛ می‌توانید در همان سلول عدد یا متن هم بنویسید. Delete آن را پاک نمی‌کند؛ باید از تب Sparkline ← Clear استفاده کنید.
• Sparklineها به‌صورت گروه ساخته می‌شوند و قالب‌بندی روی همه اعمال می‌شود؛ برای رنگ متفاوت یک سطر (مثلاً نماینده‌ی برتر)، اول Ungroup کنید.
• برای داده‌ای با فاصله‌ی زمانی نامنظم (بازدیدهای کنترل کیفیت)، Axis ← Date Axis Type را بزنید تا فاصله‌ی افقی متناسب با تاریخ واقعی باشد.
• نام تعریف‌شده در کادر سری نمودار باید پیشوند فایل یا شیت داشته باشد؛ بدون آن پیام «formula contains an error» می‌آید. با تغییر نام فایل، اکسل پیشوند را خودش به‌روز می‌کند.
• آیکون قیف کنار نمودار (Chart Filters) یک سری یا دسته را بدون دست زدن به داده موقتاً پنهان می‌کند؛ برای ارائه‌ی جلسه بدون خراب کردن فایل اصلی.