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

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

در رابطه با مزایای Pivot Table می‌توان به موارد زیر اشاره کرد:

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

مراحل ایجاد گزارش

اضافه کردن Pivot Table

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

برای ایجاد Pivot Table، ابتدا روی یکی از سلول‌های محدوده کلیک کرده و PivotTable را از تب Insert انتخاب می‌کنیم.

سپس جدول یا محدوده داده‌ها و شیت گزارش را در باکس ظاهر شده وارد کرده و در نهایت دکمه OK را می‌زنیم.

*در صورتیکه گزینه “New Worksheet” را انتخاب کنید، گزارش در شیت جدیدی ایجاد می‌شود.

ساختاردهی به گزارش

برای چینش آیتم‌های جدول کافیست عناوین را کشیده و در فیلد موردنظر رها کنید. برای مثال، جهت تهیه گزارش خالص فروش به تفکیک خریدار، “خریدار” را در فیلد “Rows” و “مبلغ فروش” را در فیلد “Value” قرار می‌دهیم.

حال قصد داریم مبلغ تخفیف را در کنار مبلغ فروش اضافه کنیم تا از عادی بودن تخفیفات اطمینان حاصل کنیم. برای اینکار “تخفیفات” را در فیلد “Value” و زیر “مبلغ فروش” قرار می‌دهیم.

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

برای دیدن مبالغ “فروش” و “برگشت از فروش” در یک قاب کافیست “نوع سند” را از فیلد “Filters” به “Columns” منتقل کنیم:

برای مشخص کردن سهم هر خریدار در فروش، می‌توانید بصورت زیر اقدام کنید:

مرتب‌سازی داده‌ها

جهت سورت کردن داده‌های گزارش میبایست روی یکی از سلول‌های ستون موردنظر کلیک راست کرده و از منو ظاهر شده، sort را انتخاب کنیم.

اضافه کردن فیلد جدید

در مواقعی نیاز خواهیم داشت در گزارشاتمان محاسباتی را انجام داده و در فیلدی جداگانه نمایش دهیم. اگر بخواهیم این محاسبات را در داده‌های اولیه اعمال کنیم کمی زمانبر می‌شود و درصد خطا نیز بالا می‌رود؛ درحالیکه Pivot Table این امکان را فراهم کرده تا محاسبات مدنظرمان را در همان گزارش اضافه کنیم. برای مثال، قصد داریم مالیات بر ارزش افزوده (۹%) دریافتی از خریداران را مشخص نماییم. بدین منظور روی یکی از سلول‌های Pivot Table کلیک کرده و از تب PivotTable Analyze و بخش Calculations، گزینه Calculated Field را انتخاب می‌کنیم. در نهایت نام و محاسبات را مانند تصویر زیر وارد می‌کنیم:

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

اسلایسر

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

برای ایجاد اسلایسر، روی یکی از سلول‌های Pivot Table کلیک کرده و از تب PivotTable Analyze و بخش Filter، گزینه Insert Slicer را انتخاب می‌کنیم.

در ادامه عنوان مورد نظر جهت فیلتر را در پنجره باز شده انتخاب و OK را می‌زنیم.

*برای تغییر شکل ظاهری اسلایسرها، ابتدا آن را انتخاب کرده و سپس به تب Slicer بروید.

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

برای متصل کردن یک اسلایسر به دو Pivot Table، در گام نخست اسلایسر را انتخاب و سپس در تب و بخش Slicer، روی Report Connections کلیک می‌کنیم.

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

اصلاح برچسب‌ها

طرح پیش‌فرض برای جداول Pivot Table، طرح فشرده است. این طرح‌بندی برچسب‌های ردیف/ستون را به عنوان سرفصل‌ها در Pivot Table نمایش می‌دهد. یک راه آسان برای خلاص شدن از شر این عناوین عجیب، تغییر طرح‌بندی Pivot Table از فشرده به طرح کلی یا جدولی است. این طرح نام فیلدهای واقعی را به عنوان سرفصل در Pivot Table نمایش می‌دهد. بدین منظور، به دنبال گزینه Show in Outline Form در تب Design و بخش Layout بگردید.

گروه‌بندی داده‌ها

قصد داریم تعداد تخفیف‌ها را در بازه‌های ۱,۰۰۰,۰۰۰ریالی شمارش کنیم. برای اینکار به صورت زیر عمل می‌کنیم:

اطلاعات تفضیلی

زمانی که به گزارش تفضیلی نیاز داشته باشیم، Pivot Table به راحتی و با یک دابل‌کلیک آن را فراهم می‌کند. در مثال بالا می‌خواهیم 6صورت‌حسابی که در بازه ۹,۰۰۰,۰۰۰-۹,۹۹۹,۹۹۹ریال از تخفیفات استفاده کرده‌اند را مشخص کنیم. برای اینکار روی سلول مربوطه دابل‌کلیک می‌کنیم:

بروزرسانی داده‌ها

در صورت ایجاد هرگونه تغییری در داده‌های اولیه (اضافه شدن داده جدید، حذف یا اصلاح داده‌ها و…)، نیاز است جدول گزارش نیز آپدیت شود. بدین منظور روی یکی از داده‌های جدول Pivot Table کلیک راست کرده و از منو نمایش داده شده، گزینه Refresh را انتخاب می‌کنیم تا جدول تازه‌سازی شود.

برای آپدیت Pivot Table در زمان باز شدن فایل، از تب PivotTable Analyze گزینه Options را انتخاب می‌کنیم.

از پنجره باز شده، در تب Data تیک “Refresh data when opening the file” را زده و روی دکمه OK کلیک می‌کنیم.

اصلاح منبع داده

اگر اندازه مجموعه داده‌های خود را با افزودن یا حذف ردیف‌ها/ستون‌ها تغییر دهیم، باید منبع داده‌ها را برای Pivot Table بروزرسانی کنیم. بدین منظور از تب PivotTable Analyze و قسمت Data، بر Change Data Source کلیک کرده و از پنجره باز شده، منبع را اصلاح می‌کنیم.

تکثیر گزارش

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

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