آموزش Pivot Table در اکسل برای حسابداران؛ ساخت گزارش مالی در چند دقیقه

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

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

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

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

برای مثال، ممکن است یک جدول فروش شامل تاریخ، نام مشتری، نام کالا، مبلغ فروش و نام فروشنده باشد. Pivot Table می‌تواند به‌سرعت نشان دهد هر مشتری چه مقدار خرید داشته است یا فروش هر ماه چقدر بوده است.

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

کاربرد Pivot Table برای حسابداران

Pivot Table در اکسل برای بسیاری از کارهای روزمره حسابداری کاربرد دارد. چند نمونه مهم عبارت‌اند از:

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

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

اطلاعات مناسب برای ساخت Pivot Table

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

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

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

تاریخ
شماره فاکتور
نام مشتری
نام کالا
تعداد
مبلغ فروش
نام فروشنده

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

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

برای ساخت Pivot Table، ابتدا روی یکی از سلول‌های جدول اطلاعات کلیک کنید. سپس از نوار بالای اکسل وارد تب Insert شوید و گزینه PivotTable را انتخاب کنید.

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

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

در پایین این بخش، چهار ناحیه مهم وجود دارد:

Filters

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

Columns

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

Rows

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

Values

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

مثال Pivot Table برای گزارش فروش

فرض کنید یک شرکت در فایل اکسل خود اطلاعات فروش روزانه را ثبت کرده است. این اطلاعات شامل تاریخ فروش، نام مشتری، نام کالا و مبلغ فروش هستند.

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

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

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

مثال Pivot Table برای تحلیل هزینه‌ها

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

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

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

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

تغییر نوع محاسبه در Pivot Table

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

برای تغییر نوع محاسبه، روی یکی از اعداد گزارش کلیک راست کنید و گزینه Value Field Settings را انتخاب کنید. سپس می‌توانید یکی از حالت‌های زیر را انتخاب کنید:

جمع مبلغ‌ها
تعداد رکوردها
میانگین مبلغ‌ها
بیشترین مبلغ
کمترین مبلغ

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

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

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

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

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

راهنمای رسمی مایکروسافت نیز تأکید می‌کند که داده‌های منبع باید عنوان ستون مشخص داشته باشند و بدون ردیف یا ستون خالی تنظیم شوند. برای مطالعه بیشتر می‌توانید صفحه Create a PivotTable to analyze worksheet data را ببینید.

تفاوت Pivot Table و فرمول‌های اکسل

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

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

به همین دلیل، حسابداران حرفه‌ای معمولاً از فرمول‌ها و Pivot Table در کنار هم استفاده می‌کنند.

خطاهای رایج در ساخت Pivot Table

انتخاب‌نشدن تمام محدوده داده‌ها

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

وجود سلول خالی در عنوان ستون‌ها

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

ثبت مبلغ به‌صورت متن

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

فراموش‌کردن Refresh

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

استفاده از جمع‌های دستی در جدول منبع

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

ساخت Pivot Chart از گزارش مالی

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

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

اگر فیلترهای گزارش Pivot Table را تغییر دهید، نمودار مرتبط نیز به‌روزرسانی می‌شود. این قابلیت برای ارائه گزارش به مدیران بسیار مفید است.

سؤالات متداول

Pivot Table در اکسل چه کاربردی دارد؟

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

آیا Pivot Table اطلاعات اصلی را تغییر می‌دهد؟

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

چرا Pivot Table مبلغ‌ها را جمع نمی‌زند؟

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

چگونه اطلاعات جدید را به Pivot Table اضافه کنیم؟

پس از واردکردن اطلاعات جدید، روی گزارش کلیک راست کنید و گزینه Refresh را بزنید. اگر داده‌ها را در قالب Table ثبت کرده باشید، به‌روزرسانی آسان‌تر انجام می‌شود.

Pivot Table بهتر است یا فرمول‌های اکسل؟

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

جمع‌بندی

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

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

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