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

من همیشه این‌طور احساس کرده‌ام که خسته‌کننده است که پرکاربردترین توابع اکسل، مانند SUM، AVERAGE، MIN و MAX، به‌محض اینکه داده‌های شما حتی یک مقدار خطا داشته باشد، از کار می‌افتند. یک #DIV/0! یا #VALUE! ناخواسته در بازهٔ شما باعث می‌شود کل فرمول خطا بدهد. راه‌حل معمول این است که همه‌چیز را در IFERROR بپیچید، که کار می‌کند اما نوار فرمول را شلوغ می‌کند.

من همیشه این را ناامیدکننده می‌دانم که توابع پرطرفدار اکسل، مانند SUM، AVERAGE، MIN و MAX، به‌محض اینکه داده‌های شما یک مقدار خطا داشته باشد، از کار می‌افتند. یک #DIV/0! یا #VALUE! ناخواسته در بازه شما باعث می‌شود کل فرمول خطا برگرداند. راه حل معمول این است که همه را در IFERROR بپیچید، که کار می‌کند اما نوار فرمول را شلوغ می‌کند.

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

فرمول‌های شما از شما سپاسگزار خواهند شد.

AGGREGATE کاری که توابع دیگر نمی‌توانند انجام دهند

نحوه کارکرد سینتکس

اکثریت افراد به‌طور پیش‌فرض به IFERROR روی می‌آورند وقتی فرمول‌هایشان خراب می‌شود. این کار می‌کند، اما مانند باندی است چون شما پیچیدن هر فرمول در منطق اضافی فقط برای مدیریت داده‌های خراب. AGGREGATE این را حذف می‌کند زیرا مدیریت خطا در خود تابع تعبیه شده است.

از سینتکس زیر استفاده می‌کند:

=AGGREGATE(function_num, options, ref1, [ref2], …

در اینجا توضیح هر آرگومان آورده شده است:

• function_num: یک عدد از ۱ تا ۱۹ که به AGGREGATE می‌گوید چه محاسبه‌ای را انجام دهد. برای مثال، ۱ برابر AVERAGE، ۴ برابر MAX، ۵ برابر MIN و ۹ برابر SUM است.
• options: یک عدد از ۰ تا ۷ که تعیین می‌کند AGGREGATE چه چیزی را نادیده بگیرد. مفیدترین‌ها ۵ (نادیده گرفتن سطرهای مخفی)، ۶ (نادیده گرفتن مقادیر خطا) و ۷ (نادیده گرفتن هر دو) هستند.
• ref1: محدوده سلولی که می‌خواهید محاسبه کنید.

بنابراین به‌جای

=IFERROR(SUM(F1:F10),""

, می‌توانید بنویسید

=AGGREGATE(9, 6, F1:F10)

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

=IFERROR(SUM(F1:F10),"")

=AGGREGATE(9, 6, F1:F10)

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

محاسبات شما حتی وقتی سطرها خارج از دید هستند دقیق می‌مانند

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

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

=AGGREGATE(9, 5, F2:F10)

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

=AGGREGATE(9, 5, F2:F10)

این معمولاً در کتاب‌کارهای مشترک مهم است که افراد مختلف فیلترهای متفاوتی اعمال می‌کنند. یک SUM عادی به آن‌ها مجموع اشتباهی می‌دهد به‌محض اینکه یک فیلتر فعال باشد، و ممکن است حتی متوجه نشوند. با این حال، AGGREGATE به‌صورت خودکار تنظیم می‌شود و نتیجه همیشه آنچه در صفحه نمایش است را نشان می‌دهد.

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

AGGREGATE می‌تواند ۱۹ تابع مختلف را جایگزین کند

و چندتای آن‌ها بسیار مفید هستند

AGGREGATE فقط یک SUM بهتر نیست. این تابع از ۱۹ تابع داخلی پشتیبانی می‌کند، که هر کدام شماره‌ای اختصاص دارد: ۱ برای AVERAGE، ۲ برای COUNT، ۴ برای MAX، ۵ برای MIN، ۹ برای SUM و غیره. اما آن‌چه من عملی‌تر می‌دانم، LARGE (۱۴)، SMALL (۱۵) و MEDIAN (۱۲) هستند — توابعی که به‌ویژه زمانی که داده‌های شما حفره یا خطا دارند، به راحتی خراب می‌شوند.

برخی افراد متوجه نیستند که AGGREGATE می‌تواند توابع رتبه‌بندی را مدیریت کند. این همان جایی است که این تابع از SUBTOTAL متمایز می‌شود، که فقط ۱۱ تابع را پوشش می‌دهد و شامل LARGE، SMALL یا PERCENTILE نیست.

یافتن مقادیر بالا و پایین در داده‌های نامنظم

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

=LARGE(F1:F10, 1)

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

با AGGREGATE، فرمول به صورت

=AGGREGATE(14, 6, F1:F10, 1)

می‌شود. تابع ۱۴ به آن می‌گوید از LARGE استفاده کند؛ گزینه ۶ مقادیر خطا را نادیده می‌گیرد؛ و آرگومان آخر رتبه را مشخص می‌کند. در این مجموعه داده، فرمول ۳۷۵۰ را برمی‌گرداند که بالاترین فروش است، از ورودی Electronics جان اسمیت در منطقه شمال.

=AGGREGATE(14, 6, F1:F10, 1)

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

=AGGREGATE(15, 6, F1:F10, 1)

استفاده کنید، که ۱٬۸۰۰ را باز می‌گرداند — فروش Home & Garden مایک ویلسون از منطقه شرق. هیچ‌یک از این فرمول‌ها نیاز به پاک‌سازی داده‌ها قبل از محاسبه ندارند.

=AGGREGATE(15, 6, F1:F10, 1)

سناریوهای روزمره‌ای که AGGREGATE سودآور است

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

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

نیاز نیست تمام ۱۹ شماره تابع را به‌خاطر بسپارید. وقتی در یک سلول

=AGGREGATE(

تایپ می‌کنید، اکسل یک منوی کشویی نشان می‌دهد که هر تابع پشتیبانی‌شده را همراه با شماره مربوطه لیست می‌کند.

=AGGREGATE(

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

سعی کنید آن را با منطق شرطی ترکیب کنید برای کنترل بیشتر

AGGREGATE خود به‌تنهایی موضوعات زیادی را پوشش می‌دهد، اما وقتی آن را با توابعی مانند IF، CHOOSE یا INDEX ترکیب می‌کنید، جالب‌تر می‌شود. به‌عنوان مثال، می‌توانید AGGREGATE را داخل یک فرمول شرطی تو در تو کنید تا فقط سطرهای قابل مشاهده‌ای که معیار خاصی را برآورده می‌کنند جمع کنید، کاری که در غیر این صورت نیاز به فرمول آرایه چندمرحله‌ای دارد.

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

منبع مقاله