تجمیع هزینه‌ها بر اساس دپارتمان؛ خلاصه‌سازی با GROUPBY

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

تجمیع داده چیست و چرا در گزارش مالی لازم است؟

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

تجمیع در چند موقعیت مالی کاربرد مستقیم دارد:

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

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

مثال عددی: هشت ردیف هزینه

فرض کنید دفتر هزینه‌ی یک ماه، هشت ردیف ساده دارد (ارقام به واحد دلخواه):

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

تجمیع با یک فرمول

در نسخه‌های جدید اکسل (Microsoft 365) تابعی به نام GROUPBY وجود دارد که گروه‌بندی و جمع را همزمان انجام می‌دهد. اگر نام دپارتمان‌ها در ستون A از A2 تا A9 و مبلغ‌ها در ستون B از B2 تا B9 باشند، کافی است در یک سلول خالی بنویسید:

=GROUPBY(A2:A9, B2:B9, SUM)

سه ورودی فرمول معنی روشنی دارند:

1. ستون گروه‌بندی: مقدارهایی که می‌خواهید بر اساس آن‌ها تفکیک شود (دپارتمان).
2. ستون مقدار: عددی که قرار است جمع شود (مبلغ).
3. تابع تجمیع: در اینجا SUM؛ می‌توانید AVERAGE، COUNT، MAX یا MIN هم بگذارید.

نتیجه به‌صورت خودکار در چند سلول پایین‌تر «سرریز» می‌شود و جدول زیر را می‌سازد:

برای ستون سهم، هر جمع را بر کل (۶۲٬۰۰۰) تقسیم کنید. مثلاً ۲۶٬۵۰۰ تقسیم بر ۶۲٬۰۰۰ برابر ۴۲٫۷ درصد است.

اگر GROUPBY ندارید

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

• جدول محوری (PivotTable): دپارتمان را در بخش ردیف‌ها و مبلغ را در بخش مقدارها بگذارید. برای گزارش‌های بزرگ و تعاملی انتخاب مناسبی است، ولی باید پس از تغییر داده، آن را «تازه‌سازی» کنید.
• SUMIF: برای هر دپارتمان جمع شرطی بگیرید، مثلاً جمع مبلغ‌هایی که دپارتمانشان «فروش» است. فقط باید فهرست دپارتمان‌ها را خودتان بنویسید.

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

سه کنترل پیش از ارسال گزارش

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

1. جمع کل را تطبیق دهید. مجموع جدول خلاصه باید دقیقاً با مجموع ستون خام برابر باشد. در مثال بالا هر دو ۶۲٬۰۰۰ است. اگر نبود، یا ردیفی خالی است یا مبلغی به‌صورت متن ثبت شده است.
2. نام‌ها را یکدست کنید. «فناوری اطلاعات» و «فناوری اطلاعات» (با دو فاصله) برای اکسل دو گروه متفاوت‌اند. فاصله‌ی اضافی و تفاوت‌های نگارشی را پیش از تجمیع اصلاح کنید.
3. ردیف‌های خالی و منفی را بشناسید. برگشت هزینه یا اصلاحیه ممکن است مبلغ منفی باشد. این ردیف‌ها طبیعی‌اند، اما باید بدانید کجا هستند.

برای اینکه جدول خلاصه به گزارش واقعاً مدیریتی تبدیل شود، آن را کنار بودجه بگذارید. اگر بودجه‌ی بازاریابی ۲۵٬۰۰۰ بوده و هزینه‌ی واقعی ۲۶٬۵۰۰ شده، انحراف ۱٬۵۰۰ واحد یا ۶ درصد است. روش بررسی چنین انحرافی را در تحلیل بودجه در برابر عملکرد خوانده‌اید. فهرست گزارش‌های رایج و کاربرد هرکدام هم در گزارش‌های مدیریتی FP&A آمده است.

چه چیزی را نباید با تجمیع از دست داد؟

تجمیع اطلاعات را فشرده می‌کند، اما بخشی از آن را هم حذف می‌کند. چند هشدار:

• میانگین‌ها گمراه‌کننده‌اند. میانگین هزینه‌ی هر ردیف فروش ۱۰٬۲۵۰ است، ولی تفاوت یک ردیف ۹٬۵۰۰ و یک ردیف ۱۱٬۰۰۰ را پنهان می‌کند.
• یک گروه بزرگ همه‌چیز را می‌پوشاند. وقتی ۴۳ درصد هزینه به یک واحد تعلق دارد، همان واحد را باید جداگانه و دقیق‌تر بررسی کرد.
• هر گزارش را با ریتم مناسب بسازید. اینکه جدول را روزانه، هفتگی یا ماهانه ببینید به تصمیمی بستگی دارد که با آن می‌گیرید؛ نگاهی به گزارش‌های مالی روزانه، هفتگی و ماهانه کمک می‌کند.

پرسش‌های متداول

GROUPBY در کدام نسخه‌ی اکسل کار می‌کند؟ این تابع در نسخه‌های جدید Microsoft 365 در دسترس است. اگر فرمول را نمی‌شناسد، از جدول محوری یا SUMIF استفاده کنید.

فرق GROUPBY و جدول محوری چیست؟ جدول محوری ابزار تعاملی است و پس از تغییر داده باید تازه‌سازی شود. GROUPBY یک فرمول معمولی است و با تغییر داده خودکار به‌روز می‌شود.

می‌توانم به‌جای جمع، میانگین یا تعداد بگیرم؟ بله. به‌جای SUM می‌توانید AVERAGE، COUNT، MAX یا MIN را به‌کار ببرید. مثلاً با COUNT می‌شمارید هر دپارتمان چند تراکنش ثبت کرده است.

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

جمع‌بندی

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