فهرست فروشی با هزاران ردیف پیش روی شماست و مدیرتان می‌پرسد: «کدام محصول در کدام ماه چقدر فروخته است؟» می‌توانستید با فرمول‌ها دست‌وپنجه نرم کنید و فیلترها را یکی‌یکی تنظیم کنید؛ اما Excel ابزاری دارد که همین پرسش را در چند کلیک جواب می‌دهد: جدول محوری (PivotTable). این ابزار داده شما را با هر تفکیکی که انتخاب کنید، با کشیدن و رها کردن خلاصه می‌کند، بدون اینکه داده‌ها را حذف یا تغییر دهد.
در این راهنما گام‌به‌گام از ساخت جدول محوری از صفر، چیدن درست فیلدها، گروه‌بندی تاریخ، افزودن اسلایسر و به‌روز نگه داشتن جدول می‌گذریم. نام منوها ممکن است بسته به نسخه Excel و تنظیم زبان کمی فرق کند، اما منطق تقریباً یکسان است.
جدول محوری چیست و برای چه کاری است؟
جدول محوری یک گزارش خلاصه تعاملی است که روی داده خام شما کار می‌کند. محاسباتی مانند مجموع، میانگین، شمارش، بیشترین یا کمترین مقدار را بر اساس ردیف‌ها و ستون‌ها تفکیک می‌کند. داده اصلی را لمس نمی‌کند؛ فقط یک «نما» تولید می‌کند. به این ترتیب، از همان مجموعه داده، گزارش بر اساس منطقه، محصول یا ماه را در چند ثانیه می‌گیرید.
موارد استفاده معمول شامل گزارش فروش، پیگیری هزینه، خلاصه موجودی، شمارش مشتریان، نتایج نظرسنجی و تحلیل بودجه است.
گام ۱: آماده‌سازی داده برای جدول محوری
برای اینکه جدول محوری درست کار کند، داده باید مرتب باشد. پیش از شروع این موارد را بررسی کنید:
- ردیف اول باید سرستون‌ها را داشته باشد و هر ستون سرستون داشته باشد.
- سرستون‌ها منحصربه‌فرد باشند؛ دو ستون با یک نام، سردرگمی ایجاد می‌کنند.
- در محدوده داده، ردیف یا ستون کاملاً خالی نباشد.
- از سلول‌های ادغام‌شده استفاده نکنید.
- هر ستون فقط یک نوع داده داشته باشد: ستون تاریخ نباید متن داشته باشد و ستون عدد نباید متنی مانند «ندارد» داشته باشد.
- ردیف‌های جمع را در داده خام نگه ندارید؛ جدول محوری خودش جمع‌ها را حساب می‌کند.
انتخاب محدوده داده و تبدیل آن به یک جدول Excel با Ctrl+T بسیار کمک‌کننده است. وقتی ردیف جدیدی به جدول اضافه کنید، محدوده منبع جدول محوری خودکار بزرگ می‌شود و نیازی به به‌روزرسانی دستی نیست.
گام ۲: درج جدول محوری
- روی هر سلولی درون داده کلیک کنید.
- از برگه بالایی Insert، دکمه PivotTable را بزنید.
- در پنجره‌ای که باز می‌شود، بررسی کنید محدوده منبع درست انتخاب شده باشد.
- انتخاب کنید در یک کاربرگ (Worksheet) جدید ساخته شود؛ این کار داده خام و گزارش را با هم قاطی نمی‌کند.
- OK را بزنید. یک ناحیه خالی و پنل «PivotTable Fields» در سمت راست ظاهر می‌شود.
گام ۳: قرار دادن فیلدها در جای درست
پنل فیلدها سرستون‌های شما را فهرست می‌کند. با کشیدن آن‌ها به چهار ناحیه، جدول را شکل می‌دهید:
| ناحیه | کارکرد | مثال |
|---|---|---|
| Rows (ردیف‌ها) | به‌صورت عمودی در سمت چپ فهرست می‌شود | نام محصول |
| Columns (ستون‌ها) | کنار هم به‌صورت سرستون در بالا فهرست می‌شود | ماه یا منطقه |
| Values (مقادیر) | خلاصه عددی محاسبه‌شده | مجموع مبلغ فروش |
| Filters (فیلترها) | یک کشویی بالای جدول برای فیلتر کل جدول | نماینده فروش |
برای مثال، اگر داده فروش شما ستون‌های «Product»، «Region»، «Date» و «Amount» دارد: Product را به Rows، Region را به Columns و Amount را به Values بکشید. جدول مجموع فروش هر محصول را به تفکیک منطقه نشان می‌دهد. وقتی فیلدها را جابه‌جا کنید، جدول فوراً بازچیدمان می‌شود؛ می‌توانید آزادانه آزمایش کنید، چون داده آسیب نمی‌بیند.
گام ۴: تغییر محاسبه مقادیر
به‌طور پیش‌فرض، Excel فیلدهای عددی را جمع و فیلدهای متنی را شمارش می‌کند. برای تغییر، روی فیلد در ناحیه Values کلیک کنید و Value Field Settings را انتخاب کنید. گزینه‌های این بخش شامل این موارد است:
- Sum، Count، Average، Max، Min
- Show values as: درصد از کل، درصد از جمع ردیف، تفاوت با دوره قبل و محاسبات مشابه
- قالب عدد: ارز، تعداد اعشار و تنظیمات نمایشی مشابه
اگر قالب عدد را اینجا تنظیم کنید، هنگام به‌روزرسانی جدول حفظ می‌شود. قالب‌دهی دستی سلول چنین تضمینی ندارد.
گام ۵: گروه‌بندی تاریخ‌ها
اگر می‌خواهید داده روزانه را بر اساس ماه، فصل یا سال خلاصه کنید، فیلد تاریخ را به Rows اضافه کنید. در بیشتر نسخه‌ها Excel تاریخ‌ها را خودکار به سال، فصل و ماه تقسیم می‌کند. اگر گروه‌بندی خودکار انجام نشد، روی هر سلول در ستون تاریخ راست‌کلیک کنید و گزینه Group را به کار ببرید. اگر ماه و سال را انتخاب کنید، ژانویه دو سال متفاوت با هم قاطی نمی‌شوند.
اگر گروه‌بندی کار نکرد، معمولاً در ستون تاریخ یک مقدار متنی یا سلول خالی هست که تاریخ واقعی نیست. داده منبع را بررسی کنید و تاریخ‌های متنی را به تاریخ واقعی تبدیل کنید.
گام ۶: افزودن اسلایسر و Timeline
کشویی‌های ناحیه Filters کار می‌کنند، اما عملی نیستند. اسلایسرها جایگزین بصری‌تر و سریع‌تری هستند. پس از کلیک درون جدول محوری، از برگه PivotTable Analyze گزینه Insert Slicer را انتخاب کنید و فیلدی مانند منطقه یا محصول را تیک بزنید. دکمه‌هایی روی صفحه ظاهر می‌شوند؛ با کلیک روی هر کدام، جدول بر اساس آن معیار فیلتر می‌شود. حتی می‌توانید با وصل کردن چند جدول محوری به یک اسلایسر، داشبورد گزارش بسازید. برای فیلدهای تاریخ، می‌توانید یک Timeline اضافه کنید تا فیلتری قابل اسکرول بر اساس ماه یا فصل داشته باشید.
گام ۷: نمودار محوری و چیدمان بصری
تبدیل جدول خلاصه به نمودار یک گام است: داخل جدول کلیک کنید و از برگه PivotTable Analyze گزینه PivotChart را انتخاب کنید. نمودار همراه اسلایسرها و فیلترها تغییر می‌کند. برای ارائه گزارش، استفاده از یک یا دو نوع نمودار و نگه داشتن ساده جدول، پیام را قوی‌تر می‌کند. با انتخاب سبک‌های آماده از برگه Design می‌توانید ظاهر جدول را به‌راحتی تغییر دهید.
فیلد محاسباتی و نکات تکمیلی
گاهی به محاسبه‌ای نیاز دارید که در داده وجود ندارد؛ برای مثال ستون‌های «Amount» و «Quantity» هست، اما قیمت واحد نیست. می‌توانید با انتخاب Calculated Field از منوی Fields, Items & Sets در برگه PivotTable Analyze، یک فیلد جدید تعریف کنید (مثلاً Amount / Quantity). این فیلد مخصوص جدول محوری است و داده منبع را تغییر نمی‌دهد.
چند نکته کوچک دیگر: اگر بیش از یک فیلد به Rows اضافه کنید، نمای سلسله‌مراتبی (منطقه، با محصولات زیر آن) می‌گیرید. از برگه Design برای جمع و باز کردن زیرجمع‌ها استفاده کنید. برای اینکه عرض ستون‌ها با هر به‌روزرسانی به هم نریزد، در PivotTable Options گزینه «Autofit column widths on update» را بردارید. در جدول‌های بزرگ، برای دیدن ده رکورد با بیشترین مقدار، از گزینه «Top 10» در فیلتر برچسب ردیف استفاده کنید.
به‌روزرسانی: وقتی داده تغییر می‌کند چه کنیم
جدول محوری وقتی داده منبع تغییر می‌کند خودکار به‌روز نمی‌شود. روی جدول راست‌کلیک کنید و Refresh را انتخاب کنید، یا از میانبر Alt+F5 استفاده کنید. برای به‌روزرسانی همه جداول محوری یک کتاب کار با هم، دکمه Refresh All وجود دارد. اگر می‌خواهید هنگام باز شدن فایل خودکار به‌روز شود، در PivotTable Options گزینه «Refresh data when opening the file» را تیک بزنید. اگر محدوده منبع را به جدول Excel تبدیل کرده باشید، ردیف‌های جدید هنگام به‌روزرسانی در جدول قرار می‌گیرند.
اشتباهات رایج
- سرستون خالی: ستونی بدون سرستون ساخت جدول محوری را غیرممکن می‌کند.
- فراموش کردن به‌روزرسانی: این رایج‌ترین دلیل نشان دادن داده قدیمی در جدول است.
- عدد ذخیره‌شده به‌صورت متن: اگر ستون عددی به‌عنوان «Count» شمرده می‌شود، سلول‌ها در قالب متن هستند؛ آن‌ها را به عدد تبدیل کنید.
- افزودن ردیف جمع به داده خام: جمع دوبار حساب می‌شود.
- تایپ داخل جدول محوری: مقادیر داخل جدول محوری را دستی تغییر ندهید؛ هنگام به‌روزرسانی از بین می‌روند.
به کدام نسخه Excel نیاز است؟
جدول محوری تقریباً در همه نسخه‌های دسکتاپ Excel موجود است. امکانات جدید (گروه‌بندی خودکار تاریخ، پیشنهاد جدول محوری و غیره) از نسخه‌ای به نسخه دیگر فرق دارند. یک نسخه با لایسنس دائمی ممکن است کافی باشد: Office 2024 نامحدود یا Office 2021 نامحدود ابزارهای تحلیل پایه مانند جدول محوری، اسلایسر و Timeline را دارند. اگر امکانات به‌روز مداوم و همگام‌سازی ابری می‌خواهید، Office 365 یک‌ساله یا Office 365 اختصاصی تک‌کاربره ممکن است مناسب‌تر باشد. کاربران Mac باید به نسخه‌های مخصوص Mac مانند Office 2024 BIND for MacOS نگاه کنند. همه آن‌ها را در دسته لایسنس نرم‌افزار ببینید؛ در LisansX تحویل دیجیتال است و دامنه در صفحه محصول آمده است.
پرسش‌های متداول
تفاوت جدول محوری با جدول معمولی چیست؟
جدول معمولی داده خام را ردیف به ردیف نشان می‌دهد. جدول محوری این داده را با جمع، شمارش یا میانگین‌گیری خلاصه می‌کند؛ با کشیدن و رها کردن شما تصمیم می‌گیرید هر فیلد کجا باشد.
آیا جدول محوری داده منبع مرا تغییر می‌دهد؟
نه. جدول محوری یک نمای فقط‌خواندنی تولید می‌کند. داده خام همان‌طور که هست باقی می‌ماند.
آیا می‌توانم جدول محوری را از داده یک فایل دیگر بسازم؟
بله. با تنظیم یک اتصال داده خارجی یا استفاده از مدل داده می‌توانید جدول محوری از منابع مختلف بسازید. با این حال، در ابتدا آوردن داده به همان کتاب کار ساده‌ترین مسیر است.
آیا می‌توانم دو محاسبه متفاوت را در یک جدول محوری نشان دهم؟
بله. همان فیلد را دو بار به Values بکشید و یکی را مجموع و دیگری را محاسبه درصدی قرار دهید.
