چطور در اکسل Pivot Table بسازیم؟

جد‌ول‌های محوری یا پیوت تیبل (Pivot Table) از کاربردی‌ترین و قدرتمندترین ویژگی‌های اکسل به‌شمار می‌روند که از آن‌ها برای خلاصه‌سازی داده‌های ثبت‌شده در یک جدول بهره می‌بریم. به کمک پیوت تیبل می‌توانید داده‌های بسیار زیادی را بررسی کرده و اطلاعات مورد نیاز خود را به‌شکلی واضح، مختصر و قابل کنترل مشاهده کنید.

خبر خوب این است که یادگیری ساخت pivot table در اکسل بسیار آسان‌تر از آن چیزی است که تصور می‌کنید. قصد داریم شما را در فرآیند ساخت یک جدول محوری راهنمایی کنیم و به شما نشان دهیم که چگونه این‌کار را به‌سادگی انجام دهید؛ اما ابتدا، بگذارید یک قدم به عقب برداریم و اطمینان حاصل کنیم که دقیقاً می‌دانید جدول محوری چیست و چرا باید از آن استفاده کرد.

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

به‌عبارت دیگر، جدول‌های محوری این امکان را به شما می‌دهند که داده‌هایتان را به شیوه‌های متفاوتی دسته‌بندی کنید و به این ترتیب، تحلیل و نتیجه‌گیری راحت‌تری داشته باشید.

هدف از جدول‌های محوری، فراهم‌آوردن روش‌های کاربرپسند برای خلاصه‌سازی سریع حجم زیادی از داده‌ها است. این جداول می‌توانند برای فهم بهتر، ارائه و تحلیل دقیق داده‌های عددی به‌کار روند. در ادامه دو سناریوی فرضی را بیان می‌کنیم که در آن‌ها ایجاد جدول محوری می‌تواند مفید باشد.

۱- مقایسه مجموع فروش محصولات مختلف

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

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

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

۲- نمایش فروش محصول به عنوان درصدی از کل فروش

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

فرض کنید که اطلاعات فروش فصلی سه محصول متفاوت را در یک صفحه‌ی اکسل به یک جدول محوری تبدیل کرده‌اید. جدول محوری به‌طور خودکار سه جمع کل را در انتهای هر ستون نمایش می‌دهد که این اعداد نشان‌دهنده‌ی جمع فروش سه‌ماهه‌ی هر محصول هستند. اما اگر بخواهید درصد فروش یک محصول را از کل فروش شرکت بدانید، چه کاری باید انجام دهید؟شما می‌توانید هر ستون را به گونه‌ای تنظیم کنید که درصد فروش هر سه محصول به شما نشان داده شود.

فرض کنید که کل فروش سه محصول ۲۰۰,۰۰۰ تومان بوده و فروش محصول اول ۴۵,۰۰۰ تومان است. می‌توانید جدول محوری را به‌گونه‌ای ویرایش کنید که نشان دهد این محصول ۲۲٫۵ درصد از کل فروش را تشکیل داده است.

برای نمایش فروش هر محصول به عنوان درصدی از کل فروش در جدول محوری، کافی است روی سلول نمایش‌دهنده‌ی کل فروش راست‌کلیک کنید و روی Show Values ​​As و % of Grand Total بزنید.

حالا که با جدول‌های محوری (PivotTable) آشنایی بیشتری پیدا کرده‌اید، بیایید به چگونگی ایجاد آن بپردازیم.

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

ما فهرستی از افراد به‌همراه وضعیت تحصیلی و وضعیت تأهل آن‌ها داریم. با استفاده از یک جدول محوری، می‌توان اطلاعات متعددی را استخراج کرد. به‌عنوان مثال، می‌توان فهمید که چند نفر با مدرک کارشناسی ارشد متأهل هستند.

در این مرحله، باید هدف مشخصی برای جدول محوری خود داشته باشید. چه نوع اطلاعاتی را می‌خواهید با دستکاری داده‌ها به‌دست آورید؟ این اهداف به شما کمک خواهند کرد تا در مراحل بعدی، جدول محوری خود را به طور موثرتری طراحی کنید.

• روی یکی از سلول‌ها در مجموعه‌ی داده‌ها کلیک کنید.
• در منوی بالا به تب Insert بروید و در گروه Tables، روی PivotTable کلیک کنید.

• در پنجره‌ی بازشده، اکسل داده‌های شما را به‌طور خودکار انتخاب می‌کند. مطمئن شوید که جدول/محدوده (Table/Range) صحیح است.
• در قدم بعدی، محل قرارگیری گزارش PivotTable را انتخاب کنید. اگر گزینه‌ی New worksheet را بزنید، PivotTable در یک کاربرگ جدید ایجاد می‌شود. در صورت انتخاب گزینه‌ی Existing worksheet، می‌توانید مکانی را برای نمایش PivotTable در همان کاربرگ موجود تعیین کنید. برای مثال، طبق تصویر پایین، من گزینه‌ی Existing worksheet و سلول F3 را انتخاب کردم.
• وقتی همه چیز را بررسی کردید، روی OK کلیک کنید.

نتیجه‌ی خالی‌ای مانند تصویر زیر (سمت چپ) مشاهده خواهید کرد.

این بخش ممکن است کمی گیج‌کننده باشد؛ اما نگران نباشید. در ادامه، فیلدهای جدول محوری را به نحوی ویرایش خواهیم کرد که یک جدول ایجاد شود.

اکنون ساختار کلی جدول محوری خود را در اختیار دارید و زمان آن رسیده است که تکمیلش کنید. پس از کلیک روی دکمه‌ی OK، صفحه‌ای با عنوان PivotTable Fields برای ویرایش فیلدهای جدول محوری به‌نمایش درخواهد آمد.

در این پنجره، می‌توانید هر یک از فیلدهای جدول موجود خود را مانند نام، نام خانوادگی، وضعیت تحصیلی و وضعیت تأهل انتخاب کرده و آن‌ها را در فیلدهای زیر قرار دهید.

ردیف (Row)

در این بخش، فیلد انتخابی شما به ردیف‌های افقی در جدول محوری تبدیل خواهد شد. به‌طور مثال، اگر فیلد وضعیت تحصیلی را به قسمت ردیف‌ها (Row) بکشیم، نتیجه مطابق با تصویر زیر خواهد بود.

مقادیر (Value)

در این قسمت، مقادیر مورد نظر را برای بررسی قرار می‌دهیم. به‌عنوان مثال، ما به‌دنبال تعداد افراد مجرد و متأهل در سطوح تحصیلی مختلف هستیم؛ بنابراین، فیلد نام را به قسمت Value انتقال می‌دهیم.

ستون (Column)

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

فیلتر (Filter)

با کشیدن فیلدی به این بخش، می‌توانیم از آن برای فیلترکردن گزارش خود استفاده کنیم. به‌عنوان مثال، اگر فیلد وضعیت تحصیلی را در بخش Filters قرار دهیم، در بالای Pivot Table یک ستون به نام وضعیت تحصیلی ایجاد می‌شود. با کلیک روی منوی کشویی، فهرستی از گزینه‌ها ظاهر می‌شود که می‌توانید گزینه‌ی مورد نظر را برای فیلتر کردن انتخاب کنید و سپس دکمه‌ی Ok را بزنید.

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

پس از آماده‌سازی جدول محوری، زمان آن رسیده است که به سؤال مطرح‌شده‌ی ابتدای مقاله پاسخ دهید. هدف شما از دستکاری داده‌ها چه بوده است؟

به‌عنوان مثال، ما می‌خواستیم بدانیم چه تعداد از افراد در مقاطع تحصیلی مختلف متأهل یا مجرد هستند؛ بنابراین ستون‌های وضعیت تأهل، ردیف‌های وضعیت تحصیلی و مقادیر نام را ساختیم. البته می‌توانستیم از نام‌خانوادگی هم استفاده کنیم.

مقادیر را می‌توان جمع کرد یا اگر اعداد باشند محاسبه کرد؛ اما در مثال ما، قسمت نام، شامل متن است. جدول به‌طور خودکار آن را روی تابع Count (شمارش) تنظیم می‌کند، به این معنی که تعداد نام‌ها مطابق با هر دسته شمارش می‌شود. نتیجه به‌صورت جدول زیر می‌شود.

در نهایت، در مقاطع تحصیلی دیپلم، کارشناسی، کارشناسی ارشد و دکترا، این تعداد از افراد متاهل یا مجرد هستند:

• دیپلم: یک مجرد، دو متاهل
• کارشناسی: یک مجرد، یک متاهل
• کارشناسی ارشد: دو مجرد، یک متاهل
• دکترا: یک مجرد، دو متاهل

اگر منبع داده‌تان تغییر کرد یا داده‌های جدیدی اضافه کردید، به یاد داشته باشید که جدول محوری را به‌روزرسانی کنید.

برای به‌روزرسانی پیوت تیبل با جدید‌ترین داده‌ها، این مراحل را پیش ببرید:

• روی هر جایی از پیوت تیبل کلیک کنید. راه‌های مختلفی برای به‌روزرسانی جدول محوری وجود دارد:

• در هر نقطه از جدول محوری راست‌کلیک کرده و Refresh را از منوی بازشده انتخاب کنید.

• یا به تب PivotTable Analyze یا PivotTable Tools در نوار اکسل (بسته به نسخه‌ی اکسل خود) بروید و در بخش Data روی دکمه‌ی Refresh کلیک کنید.
• یا از میانبر کیبورد Alt+F5 استفاده کنید.
• در این مرحله، پیوت تیبل با جدیدترین داده‌ها به‌روز می‌شود.

با دنبال‌کردن این مراحل، می‌توانید به‌راحتی جدول محوری خود را به‌روزرسانی کنید تا هرگونه تغییر در داده‌هایتان اعمال شود. این‌کار تضمین می‌کند که جدول محوری شما همیشه جدید‌ترین اطلاعات را نشان می‌دهد.

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

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