PivotTable؛ انقلابی در تحلیل داده

اگر قرار باشد فقط یک ابزار از اکسل یاد بگیرید که بیشترین تأثیر را در توانایی‌های تحلیلی شما داشته باشد، بدون شک PivotTable (جدول محوری) است. این ابزار به شما اجازه می‌دهد تا حجم عظیمی از داده‌های خام را در چند ثانیه خلاصه‌سازی، دسته‌بندی و تحلیل کنید، همه بدون نیاز به نوشتن یک خط فرمول. PivotTable با قابلیت کشیدن و رها کردن (Drag & Drop) فیلدها، انعطاف‌پذیری بی‌نظیری در تغییر زاویه دید تحلیل ارائه می‌دهد. در این پست، از ساخت یک PivotTable ساده تا تحلیل‌های پیشرفته با فیلترهای تعاملی و محاسبات سفارشی را یاد می‌گیرید.

ساختار داده مناسب برای PivotTable

قبل از ایجاد PivotTable، باید داده‌های خود را به شکل صحیح سازماندهی کنید. داده‌ها باید به صورت یک جدول با سرستون‌های مشخص باشند. هر ستون یک فیلد (مثل «تاریخ فروش»، «نام فروشنده»، «محصول» و «مبلغ فروش») و هر ردیف یک رکورد است. هیچ سطر یا ستون خالی درون داده‌ها نباید وجود داشته باشد. برای تبدیل داده‌ها به بهترین شکل، از گزینه Format as Table استفاده کنید که باعث می‌شود با افزودن ردیف‌های جدید، محدوده PivotTable به طور خودکار به‌روز شود. برای شروع، روی هر سلول از داده‌های خود کلیک کرده و از زبانه Insert، گزینه PivotTable را انتخاب کنید. پنجره‌ای باز می‌شود که محدوده داده و محل قرارگیری PivotTable را مشخص می‌کند (کاربرگ جدید یا موجود).

اجزای PivotTable؛ سطرها، ستون‌ها، مقادیر و فیلترها

وقتی یک PivotTable خالی ایجاد می‌شود، پنجره Field List در سمت راست صفحه ظاهر می‌شود. این پنجره چهار ناحیه اصلی دارد: Filters (فیلترها)، Columns (ستون‌ها)، Rows (سطرها) و Values (مقادیر). با کشیدن هر فیلد به هر یک از این نواحی، جدول محوری شما شکل می‌گیرد. مثلاً اگر فیلد «منطقه» را به Rows ببرید و فیلد «مبلغ فروش» را به Values، جدولی می‌بینید که مجموع فروش را بر اساس منطقه نشان می‌دهد. اگر «محصول» را به Columns اضافه کنید، جدول به صورت ماتریسی (منطقه در سطرها و محصول در ستون‌ها) نمایش داده می‌شود. ناحیه Filters برای فیلتر کردن کل جدول بر اساس یک فیلد خاص استفاده می‌شود (مثل نمایش فقط داده‌های سال ۱۴۰۴).

تغییر نوع محاسبه در Values

به طور پیش‌فرض، PivotTable فیلدهای عددی را با تابع Sum (جمع) و فیلدهای متنی را با Count (شمارش) خلاصه می‌کند. اما می‌توانید این محاسبه را تغییر دهید. روی هر فیلد در ناحیه Values کلیک کنید و گزینه Value Field Settings را انتخاب کنید. در این پنجره می‌توانید محاسبه را به Average (میانگین)، Max (بزرگ‌ترین)، Min (کوچک‌ترین)، Count (تعداد)، Product (ضرب) و… تغییر دهید. همچنین در زبانه Show Values As، می‌توانید مقادیر را به صورت درصد از کل، درصد از ردیف، درصد از ستون، و رتبه‌بندی نمایش دهید. مثلاً می‌توانید سهم فروش هر فروشنده از کل فروش شرکت را به صورت درصد مشاهده کنید که برای تحلیل سهم بازار داخلی بسیار مفید است.

فیلترهای تعاملی با Slicer و Timeline

یکی از جذاب‌ترین قابلیت‌های PivotTable، افزودن فیلترهای تعاملی با Slicer و Timeline است. این ابزارها در زبانه PivotTable Analyze (یا Insert) در دسترس هستند. Slicer یک دکمه‌های فیلتر بصری است که با یک کلیک، می‌توانید داده‌ها را بر اساس یک فیلد خاص (مثل نام فروشنده) فیلتر کنید. می‌توانید چندین Slicer را به هم متصل کنید تا با انتخاب در یکی، بقیه نیز به‌روز شوند. Timeline مخصوص فیلدهای تاریخ است و به شما اجازه می‌دهد داده‌ها را بر اساس سال، ماه، روز یا بازه‌های زمانی کوارتر فیلتر کنید. این ابزارها گزارش‌های شما را به داشبوردهای تعاملی و کاربرپسند تبدیل می‌کنند که حتی کاربران غیرحرفه‌ای نیز می‌توانند با آن کار کنند.

PivotChart؛ مصورسازی پویای داده‌های محوری

PivotChart نسخه گرافیکی PivotTable است که با هر تغییر در جدول محوری، به‌طور خودکار به‌روز می‌شود. برای ایجاد PivotChart، روی PivotTable کلیک کرده و از زبانه PivotTable Analyze، گزینه PivotChart را انتخاب کنید. سپس نوع نمودار (ستونی، خطی، دایره‌ای و…) را انتخاب کنید. هر فیلتری که به نواحی مختلف PivotTable اضافه یا حذف کنید، نمودار نیز متناظراً تغییر می‌کند. این ویژگی برای ارائه‌های تعاملی و گزارش‌های مدیریتی بسیار ارزشمند است. PivotChart همچنین دارای فیلترهای تعاملی است که با کلیک روی عناصر نمودار، می‌توانید داده‌ها را فیلتر کنید.

گروه‌بندی و دسته‌بندی داده‌ها در PivotTable

گاهی داده‌های شما دارای سطوح جزئی هستند و می‌خواهید آن‌ها را به گروه‌های بزرگ‌تر دسته‌بندی کنید. مثلاً تاریخ‌های روزانه را به ماهانه یا فصلی تبدیل کنید. برای این کار، روی یک تاریخ در PivotTable راست‌کلیک کرده و گزینه Group را انتخاب کنید. می‌توانید بر اساس Seconds، Minutes، Hours، Days، Months، Quarters و Years گروه‌بندی کنید. برای فیلدهای عددی نیز می‌توانید گروه‌بندی بر اساس محدوده (مثلاً ۰-۵۰، ۵۰-۱۰۰) انجام دهید. این قابلیت به شما امکان می‌دهد داده‌های خود را در سطوح مختلف مشاهده کنید و به سرعت از جزئیات به خلاصه‌ها حرکت کنید.

محاسبات سفارشی و فیلدهای محاسبه‌شده

اگر محاسبه مورد نظر شما در گزینه‌های استاندارد نیست، می‌توانید یک فیلد محاسبه‌شده (Calculated Field) به PivotTable اضافه کنید. برای این کار، از زبانه PivotTable Analyze، گزینه Fields, Items & Sets و سپس Calculated Field را انتخاب کنید. نام فیلد و فرمول آن را وارد کنید. مثلاً اگر فروش و هزینه دارید، می‌توانید فیلد «سود» را با فرمول =فروش – هزینه ایجاد کنید. توجه داشته باشید که فرمول‌های فیلد محاسبه‌شده از مجموع کل فیلدها استفاده می‌کنند، نه مقادیر ردیف به ردیف. برای محاسبات دقیق‌تر سطر به سطر، باید از Power Pivot استفاده کنید که در پست نهم توضیح داده خواهد شد.