SUBTOTAL و AGGREGATE: جمع درست روی داده‌ی فیلترشده و خطادار

چرا SUM در داده‌ی فیلترشده دروغ می‌گوید

وقتی جدول فروش را روی استان کاشان فیلتر می‌کنید، SUM همچنان ردیف‌های پنهان را جمع می‌زند. SUBTOTAL و AGGREGATE توابعی هستند که «می‌بینند» چه چیزی قابل‌مشاهده است. Total Row جداول و دکمه‌ی AutoSum روی داده‌ی فیلترشده هم در واقع SUBTOTAL می‌نویسند.

SUBTOTAL

=SUBTOTAL(شماره_تابع, محدوده)

شماره‌ها: 1 AVERAGE  2 COUNT  3 COUNTA  4 MAX  5 MIN  9 SUM   (1 تا 11)
          101 تا 111 = همان توابع، اما ردیف‌های مخفی‌شده‌ی دستی را هم نادیده می‌گیرند

=SUBTOTAL(9, E2:E5000)      ← جمع ردیف‌های فیلترنشده
=SUBTOTAL(109, E2:E5000)    ← جمع ردیف‌های فیلترنشده و Hide نشده
=SUBTOTAL(103, $B$2:B2)     ← شماره‌ردیف پیوسته که با فیلتر از نو شماره می‌خورد

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

AGGREGATE؛ نسخه‌ی قدرتمندتر

AGGREGATE(شماره_تابع, گزینه, محدوده, [k]) نوزده تابع دارد و علاوه بر ردیف‌های پنهان، می‌تواند خطاها را هم نادیده بگیرد؛ چیزی که SUM و MAX ندارند.

جمع ستونی که چند #N/A دارد:
=AGGREGATE(9, 6, E2:E500)

بزرگ‌ترین فاکتور کاشان بدون فرمول آرایه‌ای (ترفند تقسیم بر شرط):
=AGGREGATE(14, 6, E2:E500/(C2:C500="کاشان"), 1)

دومین فروش کوچک:
=AGGREGATE(15, 6, E2:E500, 2)

ترفند دوم را بفهمید: تقسیم بر شرط، ردیف‌هایی که شرط ندارند را به #DIV/0! تبدیل می‌کند و گزینه‌ی 6 آن‌ها را نادیده می‌گیرد. توابع 14 تا 19 (LARGE، SMALL، PERCENTILE، QUARTILE) این شکل آرایه‌ای را بدون Ctrl+Shift+Enter می‌پذیرند؛ یک MAXIFS برای Excel 2010 تا 2016.

ابزار Data ← Subtotal

این ابزار (در گروه Outline) به داده‌ی مرتب‌شده بر اساس یک ستون، سطرهای جمع میانی درج می‌کند و دکمه‌های سطح ۱، ۲ و ۳ برای جمع‌کردن گزارش می‌سازد. برای گزارش چاپی سریع مناسب است؛ برای تحلیل، Pivot بهتر است.

نکته‌هایی که کمتر کسی می‌داند

• SUBTOTAL و AGGREGATE ستون‌های پنهان را نادیده نمی‌گیرند؛ فقط ردیف‌ها. برای جمع افقی روی ستون‌های پنهان راه‌حل داخلی وجود ندارد.
• پس از استفاده از ابزار Subtotal، سطح ۲ را بزنید، ناحیه را انتخاب و Alt+; (فقط قابل‌مشاهده) و Copy کنید تا فقط سطرهای جمع به شیت دیگر منتقل شوند.
• Alt+Shift+فلش راست ردیف‌ها یا ستون‌های انتخاب‌شده را Group می‌کند (دکمه‌ی + و −)؛ جایگزین تمیزتر برای Hide که کاربر بعدی آن را می‌بیند.
• شماره‌ردیف با SUBTOTAL(103, …) در Table باعث می‌شود اکسل گاهی ردیف آخر را جزء ردیف جمع فرض کند و در فیلتر همیشه نشانش دهد؛ در جدول‌ها این ستون را بیرون از فیلتر بگذارید یا از AGGREGATE(3,5,…) استفاده کنید.