Slicer تعاملی در اکسل + مثال

Slicer تعاملی در اکسل: اگر تا به حال در اکسل یک Pivot Table یا Pivot Chart ایجاد کرده باشید، می‌دانید که جمع‌آوری ورودی‌های جدول بر اساس برخی سنجه‌ها، مانند «واحد فروخته‌شده» یا «فروش کل» چقدر آسان است. حتی ممکن است یک Slicer اضافه کرده باشید تا حس تعامل را به نمودار خود بدهید. اما Slicer صرفاً یک فیلتر فانتزی است که کل را به یک زیر مجموعه کاهش می‌دهد.

اگر بخواهید از Slicer برای تغییر روش تجمیع استفاده کنید چه می‌کنید؟ به عبارت دیگر، آیا استفاده از Slicer برای تغییر روش تجمیع، مثلاً بین کل، تعداد، یا میانگین فروش، جالب نیست؟

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

مثل همیشه، کار خودمان رو با یک سری داده‌هایی که از قبل داشتیم، شروع می کنیم. شما می‌تونید تمام فایل‌های این مقاله از جمله همین داده ها رو در انتهای همین مقاله دانلود کنید.

گام اول: ایجاد یک Pivot Table با استفاده از مدل داده

اولین قدم، ایجاد یک Pivot Table است، اما نه فقط یک Pivot Table معمولی. قصد داریم مدل داده را با این Pivot Table ترکیب کنیم تا به نتیجه نهایی، امکانات بیشتری را اضافه کرده باشیم.

مجموعۀ دادۀ ما یک جدول اکسل (Table) مناسب با نام “Orders” است.

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

Table Design (tab) -> Tools (group) -> Summarize with Pivot Table

در کادر محاوره‌ای Pivot Table، تمام تنظیمات موجود را همانطور که هست رها کنید و کادری با عنوان «Add this data to the Data Model» را علامت بزنید و روی OK کلیک کنید.

اکنون می‌توانیم با یک جدول «Power Pivot» به جای یک Pivot Table معمولی کار کنیم. آماده هستید که فراتر از کاربرانی کار کنید که در سطح یک Pivot Table معمولی کار می‌کنند؟

گام دوم: ایجاد سنجه‌های KPI در مدل داده

اکنون که داده های خود را در مدل داده بارگذاری کرده‌ایم، از آن برای ایجاد سنجه‌ها (Measures) در مدل استفاده می کنیم. این سنجه‌ها به ما کمک می‌کنند تا KPI Slicer را تحت کنترل داشته باشیم.

سنجه KPI برای “Number of Orders” (تعداد کل سفارشات)

به این آدرس بروید:

Power Pivot (tab) -> Calculations (group) -> Measures -> New Measure

در کادر محاوره‌ای Measure، فرمول‌های KPI را ایجاد می‌کنیم که به دنبال آن Slicer ایجاد می‌شود.

اولین KPI ما “Number of Orders” نامیده می‌شود و از فرمول زیر استفاده می کند:

COUNTROWS(orders)=

پس از کلیک بر روی OK، شاخص کلیدی عملکرد “Number of Orders” را می بینیم که به جدول خروجی Power Pivot اضافه شده است که تعداد کل سفارش ها را نشان می دهد.

بیایید با قرار دادن فیلد «Company Name» در ناحیه Rows، این مقدار کلی (98) را به اجزا ریزتر تقسیم کنیم.

سنجه KPI برای “Distinct Count of Product” (تعداد متمایز محصول)

دوباره به آدرس زیر بروید:

Power Pivot (tab) -> Calculations (group) -> Measures -> New Measure

KPI دوم ما “Distinct Count of Product” نامیده می شود و از فرمول زیر استفاده می کند:

DISTINCTCOUNT(Orders[Product])=

پس از کلیک روی OK، ستون «تعداد متمایز محصول» را می بینیم که به جدول خروجی Power Pivot اضافه شده است که تعداد اقلام متمایز فروخته شده توسط هر شرکت را نشان می دهد.

سنجه KPI برای “Total Quantity” (تعداد کل)

مجدداً به این آدرس بروید:

Power Pivot (tab) -> Calculations (group) -> Measures -> New Measure

سومین KPI ما “تعداد کل” نامیده می شود و از فرمول زیر استفاده می کند:

SUM(Orders[Quantity])=

پس از کلیک روی OK، ستون «تعداد کل» را می بینیم که به جدول خروجی Power Pivot اضافه شده که تعداد کل اقلام سفارش داده شده توسط هر شرکت را نشان می دهد.

گام سوم: ایجاد منبع Slicer

اکنون می‌خواهیم عنوان ستون‌های جدول Power Pivot را به عنوان متن روی دکمه‌های Slicer هم نشان دهیم.

از آنجایی که در اینجا ما فقط سه عنوان بیشتر نداریم و لیست ما هم Static است، از ایجاد یک لیست به‌روزرسانی پویا اجتناب می‌کنیم و به سادگی فهرست کوچکی از آنچه می‌خواهیم در دکمه‌های Slicer نمایش داده شود را تایپ می‌کنیم.

لیست جدید ایجاد شده را از قسمت Views به عنوان یک جدول اکسل (CTRL-T) انتخاب کنید و نام جدول را “View” بگذارید. دقت کنید هنگام ساخت جدول، گزینه My Table has Headers را تیک بزنید.

گام چهارم: اضافه کردن View به مدل داده

ما باید جدول اکسل جدید ایجاد شده با نام “View” را به مدل داده اضافه کنیم.

برای انجام این کار، در حالی که جدول “View” را انتخاب کرده‌اید، به مسیر زیر بروید:

Power Pivot (tab) -> Tables (group) -> Add to Data Model

می بینید که جدول “View” به مدل داده اضافه شده است.

پنجره Power Pivot را ببندید و به اکسل برگردید.

گام پنجم: ایجاد Slicer از جدول View در مدل داده

اکنون که جدول “View” را به Data Model اضافه کرده‌ایم، می‌توانیم از آن به عنوان Slicer برای کنترل فیلتر کردن جدول Power Pivot استفاده کنیم.

در پانل PivotTable Fields، از قسمت بالا دسته «All» را انتخاب کنید و جدول «View» را باز کنید تا فیلد «View» نمایان شود.

بر روی فیلد “View” کلیک راست کرده و “Add as Slicer” را انتخاب کنید.

اگر روی دکمه‌های Slicer روی جدول Power Pivot کلیک کنید، می‌بینید که هیچ تغییری در نمایش داده‌ها ایجاد نمی‌شود، زیرا هنوز باید اسلایسر «View» را به جدول «Orders» در مدل داده مرتبط کنیم.

چگونه نمودار طلوع خورشید (Sunrise Chart) را در اکسل ترسیم کنیم؟ + مثال

گام ششم: ایجاد یک سنجه برای کنترل Pivot Table با استفاده از Slicer

اکنون که اسلایسر KPI خود را ایجاد کرده‌ایم، باید دو سنجه در مدل بسازیم: اولی سنجه‌ای که تشخیص می‌دهد کدام آیتم در Slicer توسط نمایشگر گزارش انتخاب شده است، و سنجه دوم برای اجرا کردن آن انتخاب.

سنجه Slicer (اسلایسر)

مجدداً به این آدرس بروید:

Power Pivot (tab) -> Calculations (group) -> Measures -> New Measure

در کادر محاوره ای Measure، سنجه ما در جدول “View” ایجاد و “Slicer” نامیده می شود و از فرمول زیر استفاده می کند:

VALUES(View[View])=

پس از کلیک روی OK، ستون «Values» مربوط به جدول View را می بینیم که به جدول خروجی Power Pivot اضافه شده است. با انتخاب هر کدام از اسلایسرها، ستون View جلوی «Company Names» نیز اصلاح و نمایش داده می‌شود.

توجه: اگر هنگام افزودن اسلایسر “View” به ناحیه Values در جدول Power Pivot خطایی دریافت کردید، مطمئن شوید که فقط یک دکمه را در Slicer انتخاب کرده اید. انتخاب بیش از یک دکمه در Slicer باعث بروز خطای زیر می‌شود.

برای جلوگیری از انتخاب های متعدد در Slicer، فرمول را به گونه‌ای تغییر می‌دهیم که اسلایسر “View” قابلیت انتخاب چند دکمه‌ای را نادیده بگیرد و فقط حق یک انتخاب را داشته باشیم.

به این آدرس بروید:

Power Pivot (tab) -> Calculations (group) -> Measures -> Manage Measures

سنجه “Slicer” را انتخاب کرده و روی Edit کلیک کنید.

برای اطمینان از اینکه فقط یک دکمه Slicer انتخاب شده است، فرمول را به صورت زیر به روز کنید:

IF(HASONEVALUE(View[View]), VALUES(View[View]))=

دقت کنید علامت جداکنندۀ شما یا کاما (،) است یا ;

با این کار اتفاقی که رخ می‌دهد این است که ببیند آیا یک مورد از قسمت اسلایسر “View” انتخاب شده است یا خیر. اگر فقط یک مورد انتخاب شده باشد، قسمت “View” پردازش می‌شود. در غیر این صورت، اگر CTRL را نگه داریم و چند دکمه Slicer را انتخاب کنیم، هیچ اتفاقی نمی‌افتد.

اگر تست کنید و چند آیتم اسلایسر را انتخاب کنید، انتخاب بیش از یک دکمه در Slicer اتفاق خاصی در جدول رخ نمی‌دهد.

سنجه KPI (شاخص کلیدی عملکرد)

به این آدرس بروید:

Power Pivot (tab) -> Calculations (group) -> Measures -> New Measure

در کادر محاوره‌ای Measure، سنجه ما در جدول Orders ایجاد می‌شود و KPI نامیده می‌شود و از فرمول زیر استفاده می‌کنیم:

SWITCH([Slicer],”Number of Orders”, [Number of Orders],”Product Count”, [Distinct Count of Product],”Quantity Sold”, [Quantity Sold])=

به‌روزرسانی مقادیر جدول Power Pivot

در مرحله بعد باید جدول Power Pivot را به‌روزرسانی کنیم تا فقط سنجه KPI را در قسمت VALUES داشته باشد. ورودی های موجود در قسمت VALUES را حذف کنید و فقط سنجه KPI را نگه دارید.

نتیجه، یک جدول Power Pivot همانند شکل زیر است.

گام هفتم: ایجاد نمودار

اکنون زمان ایجاد نموداری است که به صورت کاملاً پویا کار می‌کند و به انتخاب‌های اسلایسر KPI ما واکنش نشان می‌دهد.

روی یکی از سلول‌های جدول Power Pivot کلیک کنید و از طریق مسیر زیر، نمودار خود را ترسیم کنید:

Insert (tab) -> Charts (group) Clustered Column Chart

نمودار ما به صورت زیر ظاهر می‌شود:

جذاب کردن ظاهر نمودار

اکنون قصد داریم تغییراتی را اعمال کنیم تا نموداری زیباتر و جذاب‌تر داشته باشیم:

Legend را حذف کنید.
عنوان نمودار (Chart Title) را حذف کنید.
برچسب‌های داده (Data Labels) را اضافه کنید.
Major Horizontal Gridlines را حذف کنید.
Vertical Axis را بردارید.
label های محور افقی را Bold کنید.
به این مسیر بروید و Field Buttons را مخفی کنید: PivotChart Analyze (tab) -> Show/Hide (group) -> Field Buttons -> Hide All
Data Labels را اضافه کنید (روی یکی از Bar ها کلیک راست کرده و Add Data Labels را انتخاب کنید).
اندازه Plot Area را کاهش دهید تا مقداری فضا در بالای نمودار فراهم شود. این قسمت برای نمایش دکمه‌های Slicer استفاده می‌شود.

گام هشتم: سفارشی کردن Slicer و ادغام کردن آن با نمودار ایجاد شده

اسلایسر KPI را انتخاب کنید و سفارشی‌سازی‌های زیر را اعمال کنید:

اسلایسر KPI را به روترین لایه بیاورید (راست کلیک کنید و گزینه Bring to Front را انتخاب کنید).
گزینه Columns را روی 3 قرار دهید.
عنوان Slicer را حذف کنید (راست کلیک کنید -> Slicer Settings -> علامت Display Header را بردارید).
سایز نمودار را برای داشتن نموداری زیبارتر تغییر دهید.

حذف حاشیه Slicer

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

روی استایل Slicer هایلایت شده کلیک راست کرده و Duplicate را انتخاب کنید.
به استایل Slicer جدید نامی مانند «Slicer – No Border» بدهید.
آیتم “Whole Slicer” را انتخاب کنید و روی Format کلیک کنید.

کادر محاوره ای به روز شده استایل Modify Slicer باید به صورت زیر ظاهر شود:

با کلیک بر روی OK، به‌روزرسانی‌ها انجام می‌شود، اما استایل Slicer جدید ایجاد شده روی Slicer موجود اعمال نخواهد شد. برای اختصاص دادن استایل Slicer جدید ایجاد شده به Slicer موجود، به ترتیب روبرو عمل کنید: Slicer (tab) -> Slicer Styles (group) -> Slicer – No Border

نمودار به‌روزشده با استایل جدید Slicer خیلی بهتر به نظر می رسد.

نکته کوچک دیگری که برای زیباسازی نمودار در دستور کار قرار می‌دهیم، این است که ستون‌های نمودار را به ترتیب نزولی مرتب می‌کنیم. روی یکی از ستون‌ها در نمودار کلیک راست کرده و Sort -> Sort Largest to Smallest را انتخاب کنید.