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

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

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

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

Pivot Table در اکسل چیست؟

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

فرض کنید جدولی شامل تاریخ فروش، نام مشتری، نام کالا، مبلغ فروش و روش پرداخت دارید. با Pivot Table می‌توانید در چند مرحله گزارش‌هایی مانند موارد زیر بسازید:

• مجموع فروش هر مشتری
• فروش هر ماه
• فروش هر کالا
• تعداد فاکتورهای هر مشتری
• مقایسه فروش نقدی و غیرنقدی
• گزارش فروش به تفکیک ماه و کالا

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

مرحله اول: آماده‌سازی داده‌های حسابداری

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

فرض کنید داده‌های فروش شما به شکل زیر است:

این اطلاعات نمونه هستند و مبالغ بر حسب ریال درج شده‌اند.

برای آماده‌سازی درست داده‌ها، نکات زیر را رعایت کنید:

1. هر ستون باید یک عنوان مشخص داشته باشد.
2. هر ردیف باید مربوط به یک تراکنش یا رکورد باشد.
3. بین داده‌ها ردیف یا ستون خالی غیرضروری قرار ندهید.
4. مبلغ‌ها را به‌صورت عددی ثبت کنید، نه متن.
5. عنوان ستون‌ها باید یکتا و مشخص باشد.
6. اطلاعات هر ستون باید تا حد امکان قالب یکسانی داشته باشد.

نکته مهم: اگر تاریخ‌ها به‌صورت متن وارد شده باشند، ممکن است گروه‌بندی ماهانه در Pivot Table به‌درستی کار نکند. در چنین شرایطی، ابتدا قالب و نوع واقعی داده‌های تاریخ را بررسی کنید.

مرحله دوم: تبدیل اطلاعات به جدول اکسل

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

مراحل انجام کار:

1. یکی از سلول‌های جدول اطلاعات را انتخاب کنید.
2. کلیدهای Ctrl + T را فشار دهید.
3. محدوده داده‌ها را بررسی کنید.
4. گزینه My table has headers را فعال نگه دارید.
5. روی OK کلیک کنید.

اکنون اطلاعات شما به یک جدول اکسل تبدیل شده است.

برای مثال، می‌توانید از تب Table Design نام جدول را tblSales قرار دهید. نام‌گذاری جدول به مدیریت فایل‌های حسابداری کمک می‌کند.

مرحله سوم: ساخت اولین Pivot Table

حالا نوبت ساخت گزارش است.

1. یکی از سلول‌های جدول فروش را انتخاب کنید.
2. وارد تب Insert شوید.
3. گزینه PivotTable را انتخاب کنید.
4. محدوده داده‌ها را بررسی کنید.
5. مشخص کنید گزارش در یک شیت جدید ایجاد شود یا در شیت فعلی.
6. روی OK کلیک کنید.

اکسل یک گزارش خالی ایجاد می‌کند و پنل PivotTable Fields را در اختیار شما قرار می‌دهد.

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

مرحله چهارم: آشنایی با چهار بخش اصلی Pivot Table

در پنل PivotTable Fields چهار بخش اصلی وجود دارد:

۱. Filters؛ فیلتر گزارش

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

۲. Columns؛ ستون‌های گزارش

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

۳. Rows؛ ردیف‌های گزارش

این بخش برای دسته‌بندی اصلی گزارش است. برای مثال، اگر نام مشتری را در Rows قرار دهید، هر مشتری در یک ردیف نمایش داده می‌شود.

۴. Values؛ مقادیر محاسباتی

این بخش برای محاسبه جمع، تعداد، میانگین و سایر خلاصه‌های آماری استفاده می‌شود. برای مثال، قرار دادن مبلغ فروش در Values معمولاً مجموع فروش را نمایش می‌دهد.

مرحله پنجم: گزارش مجموع فروش هر مشتری

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

در پنل PivotTable Fields:

• فیلد مشتری را به بخش Rows منتقل کنید.
• فیلد مبلغ فروش را به بخش Values منتقل کنید.

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

با داده‌های نمونه این مقاله، نتیجه باید به شکل زیر باشد:

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

اگر به‌جای عبارت Sum of مبلغ فروش عنوان کوتاه‌تری می‌خواهید، می‌توانید نام فیلد مقدار را تغییر دهید؛ فقط دقت کنید نام جدید با نام فیلد اصلی جدول تداخل نداشته باشد.

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

Pivot Table همیشه مجبور نیست مجموع اعداد را محاسبه کند. بسته به نوع گزارش می‌توانید نوع محاسبه را تغییر دهید.

برای این کار:

1. روی یکی از مقادیر بخش Values کلیک کنید.
2. گزینه Value Field Settings را باز کنید.
3. در قسمت Summarize Values By، نوع محاسبه را انتخاب کنید.

گزینه‌های پرکاربرد عبارت‌اند از:

• Sum: مجموع مبالغ
• Count: تعداد رکوردها یا مقادیر غیرخالی
• Average: میانگین
• Max: بیشترین مقدار
• Min: کمترین مقدار

برای مثال، اگر بخواهید تعداد رکوردهای فروش هر مشتری را بدانید، می‌توانید فیلد مبلغ فروش را در Values قرار دهید و محاسبه را روی Count تنظیم کنید.

توجه کنید که Count تعداد مقادیر غیرخالی را می‌شمارد، نه لزوماً تعداد فاکتورهای یکتا. اگر هر فاکتور چند ردیف کالا داشته باشد، برای شمارش فاکتورهای یکتا باید ساختار داده و روش گزارش‌گیری را متناسب با آن تنظیم کنید.

مرحله هفتم: ساخت گزارش فروش به تفکیک روش پرداخت

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

در پنل PivotTable Fields:

• فیلد روش پرداخت را به Rows منتقل کنید.
• فیلد مبلغ فروش را به Values منتقل کنید.

خروجی گزارش با داده‌های نمونه:

اگر می‌خواهید روش‌های پرداخت در ستون‌های جداگانه باشند، فیلد روش پرداخت را به‌جای Rows در بخش Columns قرار دهید.

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

مرحله هشتم: گزارش فروش ماهانه

یکی از کاربردهای مهم Pivot Table در حسابداری، تهیه گزارش فروش ماهانه است.

اگر ستون تاریخ شما شامل تاریخ‌های واقعی اکسل باشد:

1. فیلد تاریخ را به Rows منتقل کنید.
2. فیلد مبلغ فروش را به Values منتقل کنید.
3. روی یکی از تاریخ‌های گزارش کلیک راست کنید.
4. گزینه Group را انتخاب کنید.
5. در صورت پشتیبانی از ساختار تاریخ داده‌ها، گزینه Months و در صورت نیاز Years را انتخاب کنید.

اکنون می‌توانید فروش را بر اساس ماه یا سال مشاهده کنید.

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

مرحله نهم: استفاده از فیلتر در Pivot Table

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

برای فیلتر کردن گزارش:

1. فیلد موردنظر را به بخش Filters منتقل کنید.
2. از فهرست فیلتر بالای گزارش، گزینه‌های موردنظر را انتخاب کنید.
3. گزارش را بررسی کنید تا فقط اطلاعات مطابق فیلتر نمایش داده شوند.

برای مثال، اگر فیلد روش پرداخت را در Filters قرار دهید، می‌توانید گزارش را فقط به فروش‌های نقدی محدود کنید.

همچنین می‌توانید از ابزار Slicer برای ساخت فیلترهای تصویری و قابل کلیک استفاده کنید. این ابزار برای گزارش‌های مدیریتی و داشبوردهای مالی بسیار کاربردی است.

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

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

1. روی یکی از مبالغ مجموع فروش کلیک کنید.
2. کلیک راست کنید.
3. گزینه Sort را انتخاب کنید.
4. حالت Largest to Smallest را انتخاب کنید.

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

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

مرحله یازدهم: تغییر قالب و ظاهر گزارش

گزارش‌های مالی باید خوانا و قابل بررسی باشند. برای بهبود ظاهر Pivot Table:

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

برای تغییر قالب اعداد، روی یکی از مقادیر کلیک راست کنید و از Value Field Settings گزینه Number Format را انتخاب کنید. این کار بهتر از قالب‌بندی جداگانه سلول‌هاست، چون قالب مقادیر در گزارش بهتر حفظ می‌شود.

مرحله دوازدهم: به‌روزرسانی Pivot Table پس از ورود اطلاعات جدید

یکی از پرسش‌های رایج کاربران این است که چرا پس از اضافه کردن اطلاعات جدید، Pivot Table تغییر نمی‌کند.

برای به‌روزرسانی گزارش:

1. اطلاعات جدید را در جدول منبع وارد کنید.
2. روی Pivot Table کلیک کنید.
3. از تب PivotTable Analyze گزینه Refresh را انتخاب کنید.

می‌توانید روی گزارش کلیک راست کرده و Refresh را نیز انتخاب کنید.

اگر داده‌های منبع یک Excel Table باشند، اضافه کردن ردیف جدید به جدول معمولاً مدیریت محدوده منبع را آسان‌تر می‌کند؛ با این حال، گزارش همچنان ممکن است به Refresh نیاز داشته باشد.

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

مرحله سیزدهم: ساخت گزارش‌های ترکیبی حسابداری

پس از یادگیری مراحل اولیه، می‌توانید چند فیلد را هم‌زمان در گزارش قرار دهید.

برای مثال، برای تهیه گزارش فروش هر مشتری به تفکیک روش پرداخت:

• فیلد مشتری را در Rows قرار دهید.
• فیلد روش پرداخت را در Columns قرار دهید.
• فیلد مبلغ فروش را در Values قرار دهید.

در این حالت، میزان فروش هر مشتری در هر روش پرداخت جداگانه نمایش داده می‌شود.

همچنین می‌توانید گزارش‌هایی مانند موارد زیر بسازید:

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

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

تفاوت Pivot Table با فرمول‌های SUMIF و SUMIFS

Pivot Table و توابع SUMIF و SUMIFS هر دو برای خلاصه‌سازی داده‌ها کاربرد دارند، اما شیوه استفاده از آن‌ها متفاوت است.

اگر می‌خواهید سریع گزارش‌های متنوع از داده‌های زیاد بسازید، Pivot Table انتخاب مناسبی است. اگر به نتیجه‌ای در یک سلول مشخص نیاز دارید یا قرار است گزارش در قالب ثابت دیگری استفاده شود، SUMIF و SUMIFS می‌توانند مناسب‌تر باشند.

خطاهای رایج هنگام کار با Pivot Table

چرا مبلغ‌ها به‌جای Sum به‌صورت Count نمایش داده می‌شوند؟

معمولاً برخی مقادیر ستون مبلغ به‌صورت متن ذخیره شده‌اند یا داده‌ها به‌درستی شناسایی نشده‌اند. نوع داده‌های منبع را بررسی کنید و مطمئن شوید مقادیر واقعاً عددی هستند.

چرا اطلاعات جدید در Pivot Table دیده نمی‌شوند؟

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

چرا گروه‌بندی تاریخ کار نمی‌کند؟

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

آیا می‌توان از Pivot Table برای حسابداری دوبل استفاده کرد؟

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

پرسش‌های متداول درباره آموزش Pivot Table

آیا Pivot Table برای افراد مبتدی مناسب است؟

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

آیا برای استفاده از Pivot Table باید فرمول‌نویسی بلد باشیم؟

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

آیا Pivot Table به‌صورت خودکار به‌روزرسانی می‌شود؟

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

آیا می‌توان از Pivot Table نمودار ساخت؟

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

جمع‌بندی

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

برای شروع، یک جدول منظم از داده‌های واقعی یا نمونه بسازید، آن را به Excel Table تبدیل کنید و سپس با انتقال فیلدها به بخش‌های Rows، Columns، Values و Filters اولین گزارش خود را تهیه کنید.

پس از تسلط بر این مراحل، می‌توانید از Slicer، PivotChart و گزارش‌های ترکیبی برای ساخت داشبوردهای مالی حرفه‌ای‌تر استفاده کنید.