برای سال‌ها، من از تابع SUBTOTAL در نرم‌افزار مایکروسافت اکسل برای ایجاد جمع‌های جزئی قابل‌مشاهده در بالای صفحات کاری استفاده می‌کردم. این تابع روشی ساده و مؤثر برای نمایش خلاصه‌ای از داده‌ها ارائه می‌داد و به من این امکان را می‌داد که به راحتی نتایج را در کنار اطلاعات دیگر مشاهده کنم.
با این حال، همه چیز زمانی تغییر کرد که با تابع AGGREGATE آشنا شدم. این تابع به ابزار اصلی من برای انجام محاسبات مشابه، اما با انعطاف‌پذیری بیشتر تبدیل شد.
قبل از اینکه وارد جزئیات شویم، ابتدا بیایید نگاهی بیندازیم به اینکه SUBTOTAL دقیقاً چگونه کار می‌کند.
آشنایی با تابع SUBTOTAL
تابع SUBTOTAL در نرم‌افزار اکسل یکی از توابع ساده و قابل‌درک برای کاربران است. این تابع به شما اجازه می‌دهد محاسبات مختلفی از جمله جمع، میانگین، حداکثر و حداقل را برای یک محدوده خاص از سلول‌ها انجام دهید.
ویژگی‌های تابع SUBTOTAL
- محاسبات متنوع: با استفاده از تابع SUBTOTAL، شما می‌توانید انواع مختلفی از محاسبات را انجام دهید، که شامل جمع (SUM)، میانگین (AVERAGE)، حداکثر (MAX)، حداقل (MIN) و چندین محاسبه دیگر می‌شود.
- مدیریت ردیف‌های مخفی‌شده: یکی از ویژگی‌های منحصر به فرد این تابع این است که شما می‌توانید مشخص کنید که آیا ردیف‌های مخفی‌شده به‌صورت دستی در محاسبات لحاظ شوند یا نه. به عبارت دیگر، شما می‌توانید به راحتی از محاسبه داده‌های پنهان صرف‌نظر کنید.
نکات مهم درباره SUBTOTAL
- فیلتر کردن داده‌ها: ردیف‌هایی که توسط فیلتر مخفی شده‌اند به‌طور خودکار از محاسبات تابع SUBTOTAL حذف می‌شوند. این ویژگی به شما این امکان را می‌دهد که فقط بر روی داده‌های مشاهده‌شده تمرکز کنید.
- مدیریت خطا: اگر داده‌های شما شامل خطا باشند، تابع SUBTOTAL به‌طور صحیح عمل نخواهد کرد و نیاز به استفاده از توابعی مانند IFERROR برای مدیریت این خطاها خواهید داشت.
- محدودیت در انواع توابع: یکی دیگر از محدودیت‌های تابع SUBTOTAL این است که تنها از 11 نوع تابع مختلف پشتیبانی می‌کند. این موضوع می‌تواند در انجام محاسبات آماری پیچیده‌تر محدودیت ایجاد کند و ممکن است برای برخی از کاربران نیاز به استفاده از توابع دیگر را پیش آورد.
به طور کلی، تابع SUBTOTAL ابزاری مفید و کاربرپسند برای انجام محاسبات ساده است، اما کاربران باید از محدودیت‌ها و چالش‌های آن آگاه باشند تا بتوانند به‌طور مؤثر از آن استفاده کنند.
فرمول کلی تابع SUBTOTAL به شکل زیر است:
=SUBTOTAL(a, b, c)
-
a (ضروری): عددی که نشان‌دهنده نوع تابع موردنظر شماست.
-
b (ضروری): اولین محدوده سلولی که می‌خواهید روی آن محاسبه انجام دهید.
-
c (اختیاری): محدوده‌های اضافی که تا 252 مورد می‌توان اضافه کرد.
مقدار a تعیین می‌کند که چه تابعی اجرا شود؛ به‌عنوان مثال:
| مقدار a | تابع |
|---|---|
| 1 یا 101 | AVERAGE |
| 2 یا 102 | COUNT |
| 9 یا 109 | SUM |
| 4 یا 104 | MAX |
| 5 یا 105 | MIN |
| … | … |
نکته مهم دیگر این است که اعداد 1 تا 11 شامل ردیف‌های مخفی‌شده هستند، اما اعداد 101 تا 111 این ردیف‌ها را نادیده می‌گیرند.
در یکی از مثال‌ها، تابع SUBTOTAL برای محاسبه میانگین ستون B استفاده شده است. اگر ردیف 6 (تیم E) به‌صورت دستی مخفی شده باشد، مقدار موجود در سلول E1 آن را لحاظ می‌کند (چون مقدار a برابر با 1 است)، اما سلول E2 آن را نادیده می‌گیرد (مقدار a برابر با 101 است).
اما اگر تیم E از طریق فیلتر مخفی شود، هر دو مقدار SUBTOTAL یکی خواهند بود. چرا که در هیچ حالتی نمی‌توان SUBTOTAL را وادار کرد تا مقادیر فیلترشده را نیز لحاظ کند.
در نهایت، اگر در بازه داده‌ها خطا وجود داشته باشد، SUBTOTAL نیز خطا برمی‌گرداند، مگر اینکه با IFERROR ترکیب شود.
معرفی تابع AGGREGATE: جایگزینی قدرتمندتر برای SUBTOTAL
تابع AGGREGATE در اکسل از بسیاری جهات مشابه تابع SUBTOTAL است، اما قابلیت‌ها و انعطاف‌پذیری بسیار بیشتری را ارائه می‌دهد. این تابع نه‌تنها امکان انتخاب گسترده‌تری از توابع آماری را فراهم می‌کند، بلکه به شما این امکان را می‌دهد که تعیین کنید چه مواردی (مانند ردیف‌های مخفی، خطاها یا توابع تو در تو) در محاسبات نادیده گرفته شوند.
ویژگی‌های کلیدی تابع AGGREGATE
- انتخاب گسترده توابع آماری: تابع AGGREGATE از 19 تابع آماری مختلف پشتیبانی می‌کند، از جمله SUM، AVERAGE، COUNT، MAX، MIN و بسیاری دیگر. این تنوع به شما این امکان را می‌دهد که محاسبات پیچیده‌تری را انجام دهید.
- مدیریت بهتر خطاها: AGGREGATE به شما این امکان را می‌دهد که خطاها را در محاسبات خود نادیده بگیرید. این ویژگی به ویژه زمانی مفید است که با داده‌های ناپایدار کار می‌کنید و نمی‌خواهید محاسبات شما تحت تأثیر خطاها قرار گیرد.
- فیلتر کردن ردیف‌های مخفی: مشابه SUBTOTAL، AGGREGATE می‌تواند ردیف‌های مخفی‌شده را به‌صورت خودکار از محاسبات خارج کند، اما همچنین می‌توانید انتخاب کنید که آیا این ردیف‌ها در محاسبات لحاظ شوند یا نه.
- حالت‌های نوشتاری: تابع AGGREGATE دارای دو حالت نوشتاری است: یکی برای مرجع (reference) و دیگری برای آرایه (array). این به شما این امکان را می‌دهد که بسته به نوع داده‌هایی که دارید، از حالت مناسب استفاده کنید. خوشبختانه، اکسل به‌طور خودکار حالت مناسب را انتخاب می‌کند، بنابراین نیاز به تشخیص دستی آن نیست.
ساختار کلی AGGREGATE در حالت مرجع:
=AGGREGATE(a, b, c, d)
-
a (ضروری): شماره تابعی که باید اجرا شود (مشابه SUBTOTAL، اما با گزینه‌های بیشتر)
-
b (ضروری): مشخص می‌کند که چه چیزهایی در محاسبه نادیده گرفته شوند
-
c (ضروری): محدوده داده‌ها
-
d (اختیاری): آرگومان‌های اضافی، مخصوص توابعی مثل LARGE، SMALL و…
در اینجا لیستی از توابعی که AGGREGATE پشتیبانی می‌کند (برای a) آورده شده است:
| مقدار a | تابع |
|---|---|
| 1 | AVERAGE |
| 9 | SUM |
| 12 | MEDIAN |
| 13 | MODE.SNGL |
| 14 | LARGE |
| 15 | SMALL |
| 16 | PERCENTILE.INC |
| 19 | QUARTILE.EXC |
| … | … |
اما نکته‌ای که AGGREGATE را واقعاً از SUBTOTAL متمایز می‌کند، پارامتر b است که به شما اجازه می‌دهد تعیین کنید چه چیزهایی نادیده گرفته شوند:
| مقدار b | موارد نادیده‌گرفته‌شده |
|---|---|
| 0 | هیچ چیز به جز توابع تو در تو |
| 1 | ردیف‌های مخفی + توابع تو در تو |
| 2 | خطاها + توابع تو در تو |
| 3 | ردیف‌های مخفی + خطاها + توابع تو در تو |
| 5 | فقط ردیف‌های مخفی |
| 6 | فقط خطاها |
| 7 | ردیف‌های مخفی و خطاها |
مثال‌های عملی با تابع AGGREGATE
در یک مثال کاربردی، فرمول زیر را وارد می‌کنیم:
=AGGREGATE(1,7,Team_Scores[Score])
در اینجا:
-
a = 1 برای محاسبه میانگین
-
b = 7 برای نادیده گرفتن ردیف‌های مخفی و خطاها
نتیجه در سلول E2، میانگین مقادیر قابل‌مشاهده است، بدون در نظر گرفتن خطا و داده مخفی‌شده.
تابع SUBTOTAL نمی‌تواند چنین محاسبه‌ای را بدون استفاده از IFERROR انجام دهد.
حال فرض کنید بخواهیم فقط خطاها را نادیده بگیریم اما داده فیلترشده را لحاظ کنیم:
=AGGREGATE(1,6,Team_Scores[Score])
در این حالت، مقدار سلول E3 شامل داده‌های فیلترشده است، اما خطاها را نادیده می‌گیرد.
همچنین، AGGREGATE می‌تواند محاسباتی انجام دهد که SUBTOTAL از انجام آن‌ها ناتوان است، مثل محاسبه میانه:
=AGGREGATE(12,6,Team_Scores[Score])
یا مثلا پیدا کردن دومین مقدار بزرگ:
=AGGREGATE(14,6,Team_Scores[Score],2)
چرا AGGREGATE انتخاب بهتری است؟
در حالی که تابع SUBTOTAL برای سال‌ها یکی از ابزارهای مفید در اکسل بود، محدودیت‌هایش در مواجهه با داده‌های پیچیده، خطاها و نیازهای آماری خاص، باعث می‌شود دیگر گزینه مناسبی برای کاربران حرفه‌ای نباشد.
تابع AGGREGATE با فراهم‌کردن گزینه‌های بیشتر، کنترل بهتر بر استثناها و پشتیبانی از طیف وسیع‌تری از محاسبات، به‌وضوح به عنوان یک جایگزین قدرتمند برای SUBTOTAL محسوب می‌شود.
اگر همچنان از تابع SUBTOTAL استفاده می‌کنید، وقت آن رسیده که به AGGREGATE مهاجرت کنید. این تغییر نه‌تنها عملکرد شما را بهبود می‌دهد، بلکه فایل‌های اکسل شما را قابل‌اطمینان‌تر و حرفه‌ای‌تر خواهد کرد. با استفاده از AGGREGATE، می‌توانید مطمئن باشید که تحلیل‌ها و محاسبات شما دقیق‌تر و کارآمدتر انجام می‌شوند.