تابع جمع بندی 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])
پارامتر function_num در تابع جمع بندی AGGREGATE
همانطور که مشاهده میکنید اولین پارامتر در هر دو حالت function_num یا کد تابع محاسباتی در تابع AGGREGATE است. جدول زیر تنوع کد توابع محاسباتی را مشخص کرده است. البته به منظور مقایسه این تابع با SUBTOTAL، این جدول قابل استفاده است. در ستون آخر این جدول توابعی که توسط SUBTOTAL به صورت مشترک با AGGREGATE وجود دارند مشخص شده است.
جدول ۱: کدهای مربوط به توابع محاسباتی در پارامتر اول AGGREGATE
کد تابع محاسباتی (function_num) | تابع محاسباتی | عملکرد | موجود یا قابل استفاده در تابع SUBTOTAL |
1 | AVERAGE | محاسبه میانگین مقادیر یک لیست | قابل استفاده است |
2 | COUNT | شمارش سلولهای عددی در یک لیست | قابل استفاده است |
3 | COUNTA | شمارش سلول ها با مقادیر عددی و متنی | قابل استفاده است |
4 | MAX | نمایش بزرگترین مقدار در یک لیست | قابل استفاده است |
5 | MIN | نمایش کوچکترین مقدار در یک لیست | قابل استفاده است |
6 | PRODUCT | محاسبه حاصلضرب مقادیر یک لیست | قابل استفاده است |
7 | STDEV.S | محاسبه انحراف استاندارد نمونهای روی یک لیست | قابل استفاده است |
8 | STDEV.P | محاسبه انحراف استاندارد جامعه روی یک لیست | قابل استفاده است |
9 | SUM | محاسبه جمع مقادیر یک لیست | قابل استفاده است |
10 | VAR.S | محاسبه واریانس نمونهای روی یک لیست | قابل استفاده است |
11 | VAR.P | محاسبه واریانس جامعه روی یک لیست | قابل استفاده است |
12 | MEDIAN | محاسبه میانه مقادیر یک لیست | موجود و قابل استفاده نیست. |
13 | MODE.SNGL | محاسبه مد یا نمای منحصر به فرد در یک لیست | موجود و قابل استفاده نیست. |
14 | LARGE | محاسبه امین مقدار در یک لیست مرتبا شده نزولی ( بزرگترین مقدار) | موجود و قابل استفاده نیست. |
15 | SMALL | محاسبه امین مقدار در یک لیست مرتب شده صعودی ( کوچکترین مقدار) | موجود و قابل استفاده نیست. |
16 | PERCENTILE.INC | محاسبه صدکهای یک لیست | موجود و قابل استفاده نیست. |
17 | QUARTILE.INC | محاسبه چارکهای یک لیست | موجود و قابل استفاده نیست. |
18 | PERCENTILE.EXC | محاسبه صدکهای یک لیست | موجود و قابل استفاده نیست. |
19 | QUARTILE.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 نیز در این بین نقش داشته و در محاسبات، باعث تغییر میشود.
تابع | ref2 | k |
LARGE(ref2,k) | ناحیه مربوط به محاسبه بزرگترین مقدار | تعیین رتبه بزرگترین مقدار (kامین عنصر از لیست مقادیر مرتب شده به صورت نزولی) |
SMALL(ref2,k) | ناحیه مربوط به محاسبه کوچکترین مقدار | تعیین رتبه کوچکترین مقدار (kامین عنصر از لیست مقادیر مرتب شده به صورت صعودی) |
PERCENTILE.INC(array,k) | ناحیه مربوط به محاسبه مقدار صدک | تعین شماره صدک (مثلا صدک ۲۰ام)- مقدار k در بازه ۰ تا ۱ تغییر میکند. |
QUARTILE.INC(array,quart) | ناحیه مربوط به محاسبه مقدار چارک | تعین شماره چارک (مثلا چارک سوم) |
PERCENTILE.EXC(array,k) | ناحیه مربوط به محاسبه مقدار صدک | تعین شماره صدک (مثلا صدک ۲۰ام)- مقدار k در بازه ۰ تا ۱ تغییر میکند و |
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 در هر یک از سطرهای فرمولها دقت کنید.
تابع | شرح عملکرد | نتیجه |
=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 که در نسخههای جدید اکسل معرفی شده است، بهترین گزینه برای نوشتن فرمولهای جمعبندی است.
مطلبی که در بالا مطالعه کردید بخشی از مجموعه مطالب «چگونه در اکسل جمع کنیم؟» است. در ادامه، میتوانید فهرست این مطالب را ببینید:
- جمع در اکسل — از صفر تا صد + فیلم آموزش رایگان
- انواع روش های جمع در اکسل – آموزش 13 روش + مثال
- جمع ستون در اکسل — ۵ روش کاربردی به زبان ساده + فیلم آموزشی رایگان
- جمع زدن چند سطر در اکسل – 4 روش کاربردی
- جمع محدوده در اکسل – آموزش تصویری و ساده
- جمع کردن اعداد در اکسل — آموزش سریع روش ها و فرمول ها + فیلم آموزشی
- جمع زدن اتوماتیک در اکسل – توضیح کامل جمع خودکار
- جمع سفارشی در اکسل — راهنمای کاربردی
- نوشتن فرمول جمع در اکسل — به زبان ساده + مثال و فیلم آموزشی رایگان
- تابع SUMIF در اکسل — آموزش با مثالهای کاربردی
- جمع شرطی در اکسل – آموزش تابع و فرمول به زبان ساده
- جمع در اکسل با شرط رنگ سلول – آموزش تصویری و ساده
- توابع شمارشی و جمع بندی پیشرفته در اکسل (+ دانلود فیلم آموزش گام به گام)
- تابع جمع بندی AGGREGATE در اکسل — راهنمای کاربردی(همین مطلب)
- جمع فیلتر در اکسل با 4 روش کاربردی
- جمع سلول های خاص در اکسل با 4 روش کاربردی
- جمع ساعت در اکسل – آموزش 9 روش کاربردی