تابع جمع بندی AGGREGATE در اکسل — راهنمای کاربردی

۱۹۸۵ بازدید
آخرین به‌روزرسانی: ۱۷ خرداد ۱۴۰۲
زمان مطالعه: ۸ دقیقه
تابع جمع بندی AGGREGATE در اکسل — راهنمای کاربردی

یکی از مسائلی که روزانه به حل آن‌ها مشغول هستیم، جداسازی اعداد و جمع‌بندی آن‌ها است. برای این کار معمولا مقادیر و داده‌های عددی را در یک جدول، منظم کرده، سپس محاسباتی نظیر، جمع، میانگین، شمارش تعداد و ... را انجام می‌دهیم. در این نوشتار می‌خواهیم چنین اعمالی و محاسباتی را با استفاده از اکسل اجرایی کنیم. به همین علت از تابع جمع بندی AGGREGATE در اکسل استفاده خواهیم کرد. البته توابع و روش‌های دیگری در اکسل برای جمع‌بندی وجود دارند ولی به علت اینکه تابع جمع بندی AGGREGATE از خصوصیات ویژه‌ای برخوردار است، جمع‌بندی را با بهره‌گیری از آن انجام خواهیم داد ولی گاهی نکاتی نیز در مورد روش‌های دیگر جمع‌بندی در این نوشتار ارائه خواهیم کرد.

برای آشنایی و نحوه کار با اکسل به نوشتارهای آموزش مقدماتی اکسل (Excel) — به زبان ساده و فرمول نویسی در اکسل – آموزش مقدماتی را مطالعه کنید. همچنین خواندن شاخص های آمار توصیفی در علم داده — راهنمای کاربردی و  جامعه آماری — انواع داده و مقیاس‌های آن‌ها نیز خالی از لطف نیست.

تابع جمع بندی AGGREGATE در اکسل

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

نکته: این تابع در نسخه‌های جدید اکسل مانند اکسل 2010 به بعد ارائه شده است و در نسخه‌های قدیمی‌تر برای جمع‌بندی باید از تابع SUBTOTAL استفاده شود.

در حقیقت ضعف‌هایی که تابع SUBTOTAL برای انجام محاسبات دارد، توسط تابع AGGREGATE برطرف شده است تا کاربران به راحتی و البته تنوع محاسباتی بیشتر، عمل جمع‌بندی را انجام دهند.

عملکرد و پارامترهای تابع جمع بندی AGGREGATE

در این قسمت به معرفی نحوه کار و معرفی پارامترهای تابع AGGREGATE خواهیم پرداخت و با مثال‌هایی نحوه کار با این تابع را فرا خواهیم گرفت.

خوشبختانه تابع AGGREGATE دارای دو شکل و دو نوع پارامتر با توجه به نحوه عملکرد آن است. در یک حالت، فرم تابع برای نوشتن مراجع متعدد قابل استفاده است که به آن «قالب مراجع» (REFERENCE FORM) گفته می‌شود و شیوه دوم به شکل برداری عمل کرده که ورودی‌های آن بردار هستند و به «قالب برداری» (ARRAY FORM) مشهور است.

ساختار دستوری تابع AGGREGATE در قالب مراجع به صورت زیر است:

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

در حالیکه شکل دستوری این تابع در قالب برداری به شکل زیر خواهد بود.

AGGREGATE(function_num, options, array, [k])

AGGREGATE PARAMETERS
پارامترهای aggregate و توابع محاسباتی

پارامتر function_num در تابع جمع بندی AGGREGATE

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

جدول ۱: کدهای مربوط به توابع محاسباتی در پارامتر‌ اول AGGREGATE

کد تابع محاسباتی (function_num)تابع محاسباتیعملکردموجود یا قابل استفاده در تابع SUBTOTAL
1AVERAGEمحاسبه میانگین مقادیر یک لیستقابل استفاده است
2COUNTشمارش سلول‌های عددی در یک لیستقابل استفاده است
3COUNTAشمارش سلول ها با مقادیر عددی و متنیقابل استفاده است
4MAXنمایش بزرگترین مقدار در یک لیستقابل استفاده است
5MINنمایش کوچکترین مقدار در یک لیستقابل استفاده است
6PRODUCTمحاسبه حاصل‌ضرب مقادیر یک لیستقابل استفاده است
7STDEV.Sمحاسبه انحراف استاندارد نمونه‌ای روی یک لیستقابل استفاده است
8STDEV.Pمحاسبه انحراف استاندارد جامعه روی یک لیستقابل استفاده است
9SUMمحاسبه جمع مقادیر یک لیستقابل استفاده است
10VAR.Sمحاسبه واریانس نمونه‌ای روی یک لیستقابل استفاده است
11VAR.Pمحاسبه واریانس جامعه روی یک لیستقابل استفاده است
12MEDIANمحاسبه میانه مقادیر یک لیستموجود و قابل استفاده نیست.
13MODE.SNGLمحاسبه مد یا نمای منحصر به فرد در یک لیستموجود و قابل استفاده نیست.
14LARGEمحاسبه $$K$$امین مقدار در یک لیست مرتبا شده نزولی ($$K$$ بزرگترین مقدار)موجود و قابل استفاده نیست.
15SMALLمحاسبه $$K$$امین مقدار در یک لیست مرتب شده صعودی ($$K$$ کوچکترین مقدار)موجود و قابل استفاده نیست.
16PERCENTILE.INCمحاسبه صدک‌های یک لیستموجود و قابل استفاده نیست.
17QUARTILE.INCمحاسبه چارک‌های یک لیستموجود و قابل استفاده نیست.
18PERCENTILE.EXCمحاسبه صدک‌های یک لیستموجود و قابل استفاده نیست.
19QUARTILE.EXCمحاسبه چارک‌های یک لیستموجود و قابل استفاده نیست.

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

پارامتر Options در تابع جمع بندی AGGREGATE

پارامتر دوم در این تابع به موضوع نحوه برخورد با سطرهای مخفی یا فیلتر شده در محاسبات مرتبط است. به این معنی که با تنظیم این پارامتر می‌توانید محاسبات مربوط به جمع‌بندی را به همه سطرهای جدول (سطرهای آشکار یا سطرهای مخفی) نسبت دهید. جدول زیر، مقادیر مربوط به این پارامتر را مشخص کرده است.

جدول ۲: مقادیر پارامتر Options در تابع AGGREGATE

مقدار پارامترعملکرد
0صرف نظر کردن از زیرجمع‌هایی که توسط تابع SUBTOTAL یا AGGREGATE در لیست اطلاعاتی وجود دارد.
1صرف نظر کردن از سطرهای مخفی (Hidden Rows)، زیرجمع‌های مربوط به توابع SUBTOTAL و AGGREGATE
2صرف‌ نظر کردن از مقادیر خطا (Error Value) و زیرجمع‌های مربوط به توابع SUBTOTAL و AGGREGATE
3صرف نظر کردن از سطرهای مخفی (Hidden Rows)، مقادیر خطا (Error Values) و زیرجمع‌های مربوط به توابع SUBTOTAL و AGGREGATE
4بدون صرف نظر کردن از هیچ مقداری و در نظر گرفتن همه سطرهای مربوط به ناحیه REF یا لیست اطلاعاتی
5صرف نظر کردن از سطرهای مخفی (Hidden Rows)
6صرف نظر کردن از مقادیر خطا (Error Values)
7صرف نظر کردن از سطرهای مخفی (Hidden Rows) و مقادیر خطا (Error Values)

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

نکته: توجه داشته باشید که تابع SUBTOTAL و AGGREGATE فقط قادر به محاسبه زیرجمع یا جمع‌بندی برحسب سطرهای کاربرگ هستند و محاسبات را روی گروهی از سطرهای انجام می‌دهند. به همین دلیل ستون‌های مخفی یا دسته‌بندی ستون‌ها تاثیری در محاسبات توسط این توابع ندارد. به این معنی که محاسبات را روی سطرهای کاربرگ نمی‌توانید انجام دهید.

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

پارامترهای ref1 ،ref2 ،k

پارامتر ref1 یک پارامتر اجباری بوده و ناحیه‌ای را مشخص می‌کند که باید جمع‌بندی روی آن صورت گیرد. به این ترتیب شکل این پارامتر به صورت آدرس نوشته می‌شود. مثلا ناحیه A1:A100 می‌تواند این پارامتر را مشخص کرده و تعیین کند که برای مثال با کد تابع ۹، جمع این ناحیه محاسبه شود. برای توابعی که به صورت برداری عمل می‌کنند، این پارامتر همان نقش array را در حالت برداری تابع AGGREGATE ایفا می‌کند.

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

تابعref2k
LARGE(ref2,k)ناحیه مربوط به محاسبه بزرگترین مقدارتعیین رتبه بزرگترین مقدار (kامین عنصر از لیست مقادیر مرتب شده به صورت نزولی)
SMALL(ref2,k)ناحیه مربوط به محاسبه کوچکترین مقدارتعیین رتبه کوچکترین مقدار (kامین عنصر از لیست مقادیر مرتب شده به صورت صعودی)
PERCENTILE.INC(array,k)ناحیه مربوط به محاسبه مقدار صدکتعین شماره صدک (مثلا صدک ۲۰ام)- مقدار k در بازه ۰ تا ۱ تغییر می‌کند.
QUARTILE.INC(array,quart)ناحیه مربوط به محاسبه مقدار چارکتعین شماره چارک (مثلا چارک سوم)
PERCENTILE.EXC(array,k)ناحیه مربوط به محاسبه مقدار صدکتعین شماره صدک (مثلا صدک ۲۰ام)- مقدار k در بازه ۰ تا ۱ تغییر می‌کند و $$ \dfrac{1}{N+1} < k < \dfrac{N}{N+1}$$
QUARTILE.EXC(array,quart)ناحیه مربوط به محاسبه مقدار چارکتعین شماره چارک (مثلا چارک سوم)

نکته: پسوندهای INC و EXC برای توابع یاد شده مربوط به نحوه محاسبه آن‌ها بوده که به ترتیب مخفف Include و Exclude هستند، به این معنی که کران‌های بازه مقدار k را در حالت Include تعیین می‌کنند در حالیکه در Exclude این کران‌ها، شامل مقدار k نخواهند بود.

به این موضوع توجه داشته باشید که اگر در پارامتر ref2، فرمول محاسباتی وجود داشته باشد، پارامتر Options نمی‌تواند از حضور زیرجمع‌ها یا سطرهای مخفی شده در محاسبات جلوگیری کند. برای مثال تابع زیر، با توجه به پارامتر options=3، باید سلول‌هایی که در ناحیه A1 تا A100 به صورت مخفی درآمده‌اند (فیلتر شده‌اند) را در محاسبات تابع AGGREGATE به کار نبرد در حالیکه عملا چنین اتفاقی نمی‌افتد زیرا پارامتر ref2 به شکل یک فرمول ارائه شده است.

=AGGREGATE(14,3,A1:A100*(A1:A100>0),1)

خطاهای معمول در تابع جمع بندی AGGREGATE

ممکن است هنگام استفاده از تابع جمع بندی AGGREGATE در اکسل با پیغام خطای !Value# مواجه شوید. معمولا این امر به علت عدم استفاده از پارامتر ref2 هنگامی است که تابع معرفی شده به آن احتیاج دارد. همچنین اگر پارامترهای ref1 , ref2 از نوع سه بُعدی (3D reference) باشند، تابع AGGREGATE، مقدار خطای !Value# را باز می‌گرداند.

مثال‌های محاسباتی برای تابع جمع بندی AGGREGATE در اکسل

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

aggregate function spreadsheet
ناحیه سلول‌های کاربرگ مربوط به جدول اطلاعاتی

حال در نظر بگیرید که محاسبات جمع‌بندی به واسطه فرمول‌هایی ارائه در جدول زیر بدست آمده‌اند.

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

تابعشرح عملکردنتیجه

=AGGREGATE(4, 6, A2:A12)

محاسبه بزرگترین مقدار با صرف نظر کردن از مقادیر خط در ناحیه A2 تا A12

96

=AGGREGATE(14, 6, A2:A12, 3)

نمایش سومین مقدار از لیست داده‌های مرتب شده به صورت نزولی

72

=AGGREGATE(15, 6, A2:A12)

با توجه به مشخص نکردن پارامتر ref2 در این تابع با خطا مواجه شده‌ایم. در صورت اصلاح کوچکترین مقدار باید محاسبه شود.

#VALUE!

=AGGREGATE(12, 6, A2:A12, B2:B12)

محاسبه میانه برای مجموعه داده‌های A2 تا B12 با صرف نظر کردن از مقادیر خطا در ناحیه مشخص شده

68

=MAX(A2:A3)

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

#DIV/0!

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

همچنین گسترش نحوه محاسبه در جمع‌بندی با استفاده از توابع SMALL, LARGE و دیگر توابع، یکی دیگر از مزایای مربوط به استفاده از چنین تابعی محسوب می‌شود. به این ترتیب اکسل تبدیل به یک ابزار مهم در بررسی و تحلیل داده‌های جدولی شده و کاربران با کمی تلاش، می‌توانند از ابزارات گسترده اکسل بهره ببرند.

نکته: در روش‌های دیگر جمع‌بندی مانند جدول محوری (PivotTable) یا ابزار زیرجمع (Subtotal) نیز توابع محدودی برای محاسبات وجود دارد. البته به کارگیری PowerPivot این محدودیت‌ها را در نسخه‌های جدید اکسل از بین برده است. در نوشتارهای بعدی اکسل به قدرت PowerPivot نیز خواهیم پرداخت و نحوه به کارگیری آن را فرا خواهیم گرفت.

خلاصه و جمع‌بندی

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

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

مطلبی که در بالا مطالعه کردید بخشی از مجموعه مطالب «چگونه در اکسل جمع کنیم؟» است. در ادامه، می‌توانید فهرست این مطالب را ببینید:

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

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *