ده هزار سطر، یک گزارش در یک دقیقه
PivotTable بدون حتی یک فرمول، داده‌ی خام را بر اساس هر ترکیبی از ستون‌ها خلاصه می‌کند: فروش هر استان در هر ماه، تعداد فاکتور هر نماینده، میانگین قیمت هر طرح. شرط موفقیت فقط یکی است: داده‌ی منبع باید جدول استاندارد باشد؛ یک سطر سرستون، هر سطر یک رکورد، بدون سطر جمع یا ردیف خالی. بهترین منبع، یک Excel Table یا خروجی Power Query است.
ساختن و چیدمان
روی جدول کلیک کنید، Insert ← PivotTable ← New Worksheet. در پنل فیلدها، هر ستون را به یکی از چهار ناحیه بکشید:
| ناحیه | نقش | مثال |
|---|---|---|
| Rows | دسته‌بندی عمودی | استان، سپس نماینده |
| Columns | دسته‌بندی افقی | ماه شمسی |
| Values | مقدار خلاصه‌شده | جمع مبلغ، تعداد فاکتور |
| Filters | فیلتر کلی گزارش | سال، نوع مشتری |
چیدمان پیش‌فرض (Compact) برای خواندن خوب است اما برای کپی و استفاده‌ی بعدی نه. از Design ← Report Layout گزینه‌ی Show in Tabular Form و Repeat All Item Labels را بزنید و Subtotals را در صورت نیاز خاموش کنید. قالب عددی را هم از Value Field Settings ← Number Format بدهید، نه از قالب سلول، تا با Refresh از بین نرود.
Group: گروه‌بندی تاریخ، عدد و متن
- تاریخ: روی یک تاریخ راست‌کلیک ← Group ← Years، Quarters، Months. این گروه‌بندی میلادی است؛ برای ماه شمسی ستون «سال-ماه شمسی» را در منبع بسازید (درس ۵) و آن را به Columns ببرید.
- عدد: روی مبلغ در Rows راست‌کلیک ← Group ← Starting at 0، By 50000000؛ بازه‌های ۵۰ میلیونی برای توزیع اندازه‌ی فاکتورها.
- متن: چند استان را با Ctrl انتخاب ← Group Selection؛ مثلاً «منطقه‌ی مرکزی» از تهران، قم و مرکزی.
Show Values As: از عدد خام به بینش
| گزینه | پاسخ به سؤال |
|---|---|
| % of Grand Total | سهم هر استان از کل فروش چقدر است؟ |
| % of Column Total | در هر ماه، سهم هر استان چقدر بود؟ |
| % of Parent Row Total | سهم هر نماینده از فروش استان خودش |
| Difference From (previous) | تغییر فروش نسبت به ماه قبل |
| % Difference From (previous) | درصد رشد ماه به ماه |
| Running Total In | فروش تجمعی از ابتدای سال |
| Rank Largest to Smallest | رتبه‌ی نمایندگان |
یک فیلد را می‌توانید چند بار در Values بگذارید: یک بار جمع مبلغ، یک بار همان مبلغ با % of Grand Total و یک بار با Rank. نام هر ستون را در Value Field Settings به عنوانی فارسی و کوتاه تغییر دهید.
ستون کمکی در جدول منبع برای ماه شمسی (به‌جای گروه‌بندی میلادی):
=TEXT([@Date], "[$-fa-IR,16]yyyy-mm")
یا با جدول تقویم:
=XLOOKUP([@Date], Cal[Miladi], Cal[YM])نکته‌هایی که کمتر کسی می‌داند
- دوبار کلیک روی هر عدد Pivot یک شیت جدید با همه‌ی رکوردهای سازنده‌ی آن عدد می‌سازد (Show Details)؛ بهترین پاسخ به «این عدد از کجا آمده؟».
- Pivot خودکار به‌روز نمی‌شود؛ Alt+F5 یک Pivot و Ctrl+Alt+F5 همه را Refresh می‌کند. در PivotTable Options ← Data گزینه‌ی Refresh data when opening the file را روشن کنید.
- بعد از حذف یک نماینده از داده، نامش در فهرست فیلتر Pivot باقی می‌ماند. PivotTable Options ← Data ← Number of items to retain per field: None و یک Refresh آن را پاک می‌کند.
- در PivotTable Options ← Layout & Format تیک Autofit column widths on update را بردارید تا با هر Refresh عرض ستون‌های داشبورد به هم نریزد.
- گروه‌بندی خودکار تاریخ هنگام کشیدن فیلد تاریخ را می‌توان در File ← Options ← Data ← Disable automatic grouping of Date/Time columns خاموش کرد.