Pivot در سطح حرفه‌ای
وقتی Pivot پایه را ساختید، سؤال‌های بعدی پیش می‌آیند: «میانگین قیمت هر متر مربع هر طرح چقدر است؟»، «هر نماینده به چند مشتری متمایز فروخته؟»، «چطور چند گزارش را با یک کلیک فیلتر کنم؟» و «چطور یک عدد Pivot را در یک کارت داشبورد نشان دهم؟».
Calculated Field
PivotTable Analyze ← Fields, Items & Sets ← Calculated Field. فرمول روی جمع فیلدها اعمال می‌شود، نه سطر به سطر. این برای نسبت‌ها دقیقاً درست است: =Amount/Area یعنی جمع مبلغ تقسیم بر جمع متراژ، که همان میانگین وزنی قیمت هر متر است. اما فرمولی مثل =IF(Amount>1E8, 1, 0) روی جمع‌ها عمل می‌کند و «تعداد فاکتورهای بالای ۱۰۰ میلیون» را نمی‌دهد؛ چنین منطقی را در جدول منبع به‌صورت ستون کمکی بسازید.
Slicer و Timeline
PivotTable Analyze ← Insert Slicer دکمه‌های فیلتر بصری می‌سازد (استان، طرح، شانه). Insert Timeline برای فیلدهای تاریخ یک نوار زمانی می‌دهد. برای این‌که یک Slicer چند Pivot را هم‌زمان کنترل کند: روی Slicer راست‌کلیک ← Report Connections و تیک همه‌ی Pivotها. شرطش این است که Pivotها از یک منبع (یک Pivot Cache یا یک Data Model) ساخته شده باشند.
Data Model و Distinct Count
هنگام ساختن Pivot تیک Add this data to the Data Model را بزنید. مزیت‌ها:
| امکان | Pivot معمولی | Pivot روی Data Model |
|---|---|---|
| Distinct Count (تعداد مشتری متمایز) | ندارد | در Value Field Settings |
| چند جدول مرتبط (فروش + نماینده + تقویم) | باید با VLOOKUP یکی شوند | با Data ← Relationships |
| بیش از یک میلیون سطر | خیر | بله (از Power Query) |
| Measure با DAX | خیر | بله (Power Pivot) |
| Calculated Field و Group دستی | بله | خیر؛ به‌جایش Measure و ستون در منبع |
-- Measure در Power Pivot (Power Pivot ← Measures ← New Measure)
Avg Price per m2 := DIVIDE ( SUM ( Sales[Amount] ), SUM ( Sales[Area] ) )
Customers := DISTINCTCOUNT ( Sales[Customer] )
Big Invoices := CALCULATE ( COUNTROWS ( Sales ), Sales[Amount] >= 100000000 )GETPIVOTDATA؛ پل Pivot به داشبورد
اگر در یک سلول = بزنید و روی یک عدد Pivot کلیک کنید، اکسل به‌جای ارجاع ساده، GETPIVOTDATA می‌سازد. این تابع عدد را بر اساس نام فیلد و آیتم پیدا می‌کند، پس با تغییر چیدمان یا اضافه شدن سطر، باز هم عدد درست را برمی‌گرداند:
=GETPIVOTDATA("Amount", Pivot!$A$3, "Province", "کاشان")
=GETPIVOTDATA("Amount", Pivot!$A$3, "Province", H2, "YM", H3) ← آیتم‌ها از سلولنکتههایی که کمتر کسی می‌داند
- اگر GETPIVOTDATA نمی‌خواهید، PivotTable Analyze ← فلش کنار Options ← تیک Generate GetPivotData را بردارید.
- Pivotهایی که از یک منبع با کپی ساخته شوند Cache مشترک دارند و Group یکی روی دیگری هم اثر می‌گذارد؛ اگر گروه‌بندی مستقل لازم است، Pivot دوم را از نو با Insert بسازید.
- روی Pivot مبتنی بر Data Model، PivotTable Analyze ← OLAP Tools ← Convert to Formulas کل گزارش را به توابع CUBEVALUE تبدیل می‌کند؛ هر سلول مستقل می‌شود و می‌توانید چیدمان آزاد داشبورد بسازید.
- PivotTable Analyze ← Options ← Show Report Filter Pages برای هر آیتم فیلتر (مثلاً هر نماینده) یک شیت جداگانه با همان Pivot می‌سازد؛ گزارش شخصی هر نماینده در یک کلیک.
- Slicer در حالت چندانتخابی با Ctrl کار می‌کند؛ برای انتخاب با کلیک ساده، دکمه‌ی Multi-Select بالای Slicer (یا Alt+S) را روشن کنید.