تقریباً هر کاربر Excel کارش را با SUM شروع میکند؛ تابعی ساده، سریع و قابل اعتماد برای جمعزدن. اما همین فرمول محبوب وقتی دادهها از حالت تمیز و آموزشی خارج میشوند، محدودیتهایش را نشان میدهد. کافی است ردیفهایی را فیلتر یا دستی مخفی کنید، یا یکی از سلولها خطای فرمول داشته باشد؛ آن وقت عدد نهایی ممکن است دیگر چیزی نباشد که واقعاً روی صفحه میبینید.
SUBTOTAL یک قدم جلوتر است، چون میتواند با ردیفهای فیلترشده بهتر کنار بیاید و در بعضی حالتها ردیفهای مخفی را هم نادیده بگیرد. با این حال، اگر وسط محدوده یک خطای #DIV/0! یا خطای مشابه داشته باشید، SUBTOTAL هم مثل SUM از کار میافتد. اینجا همان جایی است که تابع کمترشناختهشده اما حرفهایتر AGGREGATE ارزش خودش را نشان میدهد.
AGGREGATE دقیقاً چه مشکلی را حل میکند؟
فرض کنید یک جدول فروش دارید که درآمد چند منطقه را نشان میدهد. وقتی همه ردیفها پیدا هستند، هیچ فیلتری فعال نیست و خطایی هم در ستون درآمد وجود ندارد، سه تابع SUM، SUBTOTAL و AGGREGATE معمولاً یک جواب میدهند. در چنین شرایطی سادهترین راه همان SUM است:
=SUM(F11:F60)اما کافی است چند ردیف را دستی مخفی کنید. SUM همچنان آن ردیفها را در جمع لحاظ میکند. SUBTOTAL با شماره تابع 9 هم ردیفهای مخفیشده دستی را کنار نمیگذارد؛ فقط ردیفهایی را که با فیلتر حذف شدهاند نادیده میگیرد. اگر از SUBTOTAL(109,…) استفاده کنید، رفتار بهتر میشود و ردیفهای مخفی هم از جمع خارج میشوند.
تا اینجا SUBTOTAL هنوز گزینه قدرتمندی است. مشکل اصلی وقتی شروع میشود که یکی از سلولهای محدوده خطا برگرداند؛ مثلاً فرمولی در ستون درآمد تقسیم بر صفر انجام دهد. در این وضعیت SUM و SUBTOTAL بهجای عدد نهایی، همان خطا را نشان میدهند. AGGREGATE میتواند آن سلول خراب را نادیده بگیرد و جمع بقیه محدوده را ادامه دهد.
ساختار فرمول AGGREGATE
شکل کلی این تابع در Excel چنین است:
=AGGREGATE(function_num, options, ref1, [ref2], ...)آرگومان اول، یعنی function_num، مشخص میکند چه محاسبهای میخواهید انجام دهید. عدد 9 برای SUM است، عدد 1 میانگین میگیرد، عدد 4 بزرگترین مقدار را برمیگرداند و عدد 5 کوچکترین مقدار را. Excel برای این بخش فهرستی از عملیاتهای رایج مثل AVERAGE، COUNT، MAX، MIN، LARGE و SMALL دارد.
آرگومان دوم، یعنی options، به Excel میگوید هنگام محاسبه چه چیزهایی را نادیده بگیرد. چند گزینه مهم آن را میتوان اینطور خلاصه کرد:
| گزینه | رفتار |
|---|---|
| 0 | نادیده گرفتن توابع تودرتوی SUBTOTAL و AGGREGATE |
| 1 | نادیده گرفتن ردیفهای مخفی و توابع تودرتو |
| 2 | نادیده گرفتن خطاها و توابع تودرتو |
| 3 | نادیده گرفتن ردیفهای مخفی، خطاها و توابع تودرتو |
| 4 | نادیده نگرفتن هیچ موردی |
| 5 | نادیده گرفتن ردیفهای مخفی |
| 6 | نادیده گرفتن مقادیر خطادار |
| 7 | نادیده گرفتن همزمان ردیفهای مخفی و خطاها |
برای مثال، اگر بخواهید محدوده F11:F60 را جمع بزنید، اما هم خطاها و هم ردیفهای مخفی را کنار بگذارید، فرمول کاربردی این است:
=AGGREGATE(9,7,F11:F60)اگر از راست به چپ مفهوم فرمول را بخوانیم، یعنی: مقادیر محدوده F11:F60 را جمع بزن، اما سلولهای خطادار و ردیفهای پنهان را وارد نتیجه نکن. مزیت اصلی AGGREGATE همین ترکیب محاسبه و قانون پاکسازی داده در یک فرمول واحد است.
این تابع فقط برای جمعزدن نیست
نام AGGREGATE شاید بیشتر با جمعزدن به چشم بیاید، اما محدود به SUM نیست. میتوانید از آن برای میانگین، کمینه، بیشینه و حتی انتخاب چندمین مقدار بزرگتر یا کوچکتر استفاده کنید. بعضی حالتها به آرگومان اضافه نیاز دارند؛ مثلاً برای گرفتن سومین مقدار بزرگ از محدودهای که خطاها را نادیده میگیرد، ساختار فرمول میتواند چنین باشد:
=AGGREGATE(14,6,F11:F60,3)یک محدودیت مهم هم وجود دارد: رفتار AGGREGATE برای نادیده گرفتن ردیفهای مخفی، عمدتاً برای محدودههای عمودی طراحی شده است. مخفی کردن ردیف میتواند روی نتیجه اثر بگذارد، اما مخفی کردن ستونها الزاماً همان رفتار را ندارد. پس اگر دادههایتان افقی چیده شدهاند، قبل از اعتماد کامل به خروجی باید آن را آزمایش کنید.
اگر در Google Sheets کار میکنید چه؟
Google Sheets تابع AGGREGATE ندارد، اما میتوانید بخشهایی از رفتارش را با ترکیب چند تابع بازسازی کنید. برای جمعزدن یک محدوده و نادیده گرفتن خطاها، این فرمول ساده کاربرد دارد:
=SUM(IFERROR(F11:F60,0))در این روش، IFERROR سلولهای خطادار را به صفر تبدیل میکند و بعد SUM جمع را انجام میدهد. اگر هدف فقط کنار گذاشتن خطاها باشد، همین ترکیب معمولاً کافی است. گزینه دیگر این است که فقط مقدارهای عددی را از محدوده بیرون بکشید:
=SUM(FILTER(F11:F60,ISNUMBER(F11:F60)))اما تقلید کامل AGGREGATE، یعنی نادیده گرفتن همزمان خطاها و ردیفهای پنهان یا فیلترشده، پیچیدهتر میشود. یکی از راهها چنین فرمولی است:
=SUM(FILTER(IFERROR(F11:F60,0),SUBTOTAL(103,OFFSET(F11,ROW(F11:F60)-ROW(F11),0))))اینجا SUBTOTAL بررسی میکند هر ردیف قابل مشاهده است یا نه، FILTER فقط مقدارهای قابل مشاهده را نگه میدارد، IFERROR خطاها را صفر میکند و SUM نتیجه نهایی را میسازد. فرمول کار میکند، اما همین پیچیدگی نشان میدهد چرا AGGREGATE در Excel برای فایلهای جدیتر اینقدر مفید است.
جمعبندی: AGGREGATE برای فایلهایی است که کمی سختی کشیدهاند
اگر جدول شما کوچک، تمیز و بدون فیلتر است، SUM همچنان بهترین انتخاب است. لازم نیست برای جمعزدن چند سلول ساده سراغ تابعی بروید که آرگومانهای عددی بیشتری دارد. اما فایلهای واقعی معمولاً تمیز نمیمانند: ردیفها مخفی میشوند، فیلترها تغییر میکنند، فرمولها میشکنند و گاهی یک جمع فرعی وسط محدوده ظاهر میشود.
در چنین شرایطی، AGGREGATE نقش یک نسخه مقاومتر از SUM و SUBTOTAL را بازی میکند. شاید حفظ کردن شماره گزینهها کمی زمان ببرد، اما وقتی یک Workbook پیچیده شود، همین تابع میتواند بین یک عدد گمراهکننده و یک خروجی قابل اعتماد تفاوت ایجاد کند.
منبع: این مطلب یک بومیسازی و بازنویسی تحریریهای بر اساس مقالهای از MakeUseOf است: مشاهده مقاله اصلی.