آموزش Pivot Table در اکسل

Pivot Table یکی از قدرتمندترین ویژگی های اکسل است. واژه “Pivot” از این واقعیت ناشی می شود که می توانید سطر و ستون گزارش را در جدول بچرخانید و جابجا کنید تا بتوانید آن را از منظر دیگری مشاهده کنید.

برای ایجاد Pivot Table، چیزی به داده ها اضافه یا از داده ها کم نمی کنید و آن ها را تغییر نمی دهید. فقط داده ها را به سادگی سازماندهی می کنید تا بتوانید اطلاعات مفیدی را از آن ها استخراج کنید. از این رو امکان تهیه گزارش های بهتری برای شما فراهم می شود. این مطلب مقدمه ای برای آشنایی با Pivot Table و آموزش گام به گام با داده های نمونه است.
جدول زیر رو در نظر بگیرید که شامل داده هایی مناسب برای ساخت Pivot Table است.

درج Pivot Table

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

برای شروع یکی از سلول های حاوی داده را انتخاب می کنیم و مطابق شکل زیر از تب Insert روی گزینه Pivot Table کلیک می کنیم (همچنین می توانیم از کلید میانبر Ctrl + T استفاده کنیم).

سپس اکسل پنجره Create Pivot Table را نشان می دهد که شامل سه بخش زیر هست:

Choose the data that you want to analyze:

این بخش به داده هایی که قرار است تحلیل کنیم مرتبط است و شامل گزینه های زیر است:
Select a table or range: با انتخاب این گزینه می توانیم یک جدول یا آدرس یک محدوده را برای تحلیل داده ها انتخاب کنیم. چون ما قبل از ایجاد Pivot Table روی یکی از سلول های جدول کلیک کرده بودیم، محدوده مورد نظر به طور خودکار توسط اکسل در این بخش تعریف شده که می توانیم در صورت لزوم آن را تغییر دهیم.
Use an external data source: با انتخاب این گزینه می توانیم داده ها را از جایی خارج از اکسل وارد نرم افزار کرده و تحلیل کنیم.

Choose where you want the PivotTable report to be placed:

در این بخش با استفاده از گزینه های زیر می توانیم انتخاب کنیم که Pivot Table در یک شیت جدید ایجاد شود یا در یکی از شیت های موجود قرار داده شود.
New Worksheet: در صورتی که این گزینه را انتخاب کنیم، Pivot Table در یک شیت جدید ایجاد می شود.
Existing Worksheet: در صورتی که این گزینه را انتخاب کنیم می توانیم یک سلول در یکی از شیت های موجود انتخاب کنیم تا Pivot Table در آن قسمت ایجاد شود.

Choose whether you want to analyze multiple tables:

این بخش در اکسل ۲۰۱۳ اضافه شده و مربوط به ویژگی جدید Data Model هست که به ما اجازه میدهد اطلاعات چند جدول مختلف رو به طور همزمان تحلیل کنیم که توضیح آن خارج از محدوده این مطلب است.

بعد از انجام تنظیمات دلخواه، OK را کلیک می کنیم تا Pivot Table ایجاد شود. در شکل زیر جزئیات و تنظیمات جدول نشان داده شده است.

همانطور که در تصویر بالا مشاهده می کنید، در سمت راست قسمت بالا نام فیلدهای جدول داده‌های صفحه اکسل شما قرار گرفته است. هر ستون از داده‌های اصلی به عنوان یک فیلد با همان عنوان نشان داده می ‌شود و در قسمت پایین هم چهار بخش مختلف Pivot Table قرار دارد که می توانیم این فیلدها رو بوسیله ماوس به یکی از این چهار بخش بکشیم (Drag & Drop).

اجزای تشکیل دهنده Pivot Table در اکسل

Rows: در این قسمت فیلدهایی که قرار است بر اساس آن ها گزارش گیری انجام شود را قرار می دهیم و در واقع ردیف‌ هایی هستند که در سمت چپ یک Pivot Table ظاهر می ‌شوند.

Values: در این بخش مقادیری که قصد بررسی آن ها را داریم قرار می دهیم. در مثال بالا ما به دنبال مجموع سود حاصل از صادرات هر محصول هستیم. پس فیلد “میزان سود” را به این بخش می کشیم.

Pivot Table به شما اجازه می‌دهد تا عملیات مختلفی را روی این مقادیر اعمال کنید. در تصویر مشخص است که تابع تجمیعی پیش فرض SUM است. اگر بخواهیم از سایر توابع تجمیعی استفاده کنیم باید چه کار کنیم؟
انجام این کار بسیار آسان است، بر روی Pivot Table راست کلیک کرده و سپس گزینه Summarize Values By را انتخاب و پس از آن بر روی تابع مورد نظر کلیک کنید.

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

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

برای درک بهتر جایگاه هر فیلد در Pivot Table، به تصویر زیر توجه کنید:

با یک مرتب سازی ساده می توانیم بیشترین سود حاصل را مشاهده کنیم. به این منظور روی یکی از مقادیر ستون “Grand Total” راست کلیک کرده سپس Sort و بعد از آن Sort Largest to Smallest را انتخاب می کنیم.

به روز رسانی Pivot Table در اکسل:

برای تغییر اطلاعات Pivot Table، ابتدا Pivot Table را انتخاب و به تب Analyze بروید و بر روی گزینه Change Data Source کلیک کنید حال می‌توانید یک ستون جدید را انتخاب و در آن تایپ کنید یا بر روی ستون ها یا ردیف ها کلیک کنید و اطلاعات جدید خود را وارد کنید.

برای بازسازی یا رفرش Pivot Table، ابتدا Pivot Table را انتخاب کنید سپس راست کلیک کرده و Refresh را انتخاب کنید و یا می ‌توانید بر روی تب Analyze کلیک کنید و گزینه Refresh را انتخاب کنید.

برای آموزش Refresh کردن خودکار Pivot Table بصورت لحظه ای با کمک ماکرو نویسی یا همان VBA به لینک زیر مراجعه کنید.