تابع AGGREGATE اکسل – جمع در فیلتر و خطا

تابع AGGREGATE اکسل – جمع در فیلتر و خطا

تابع AGGREGATE مشابه تابع Subtotal اکسل عمل می کند ولی این تابع ایجاد گردید تا برخی امکاناتی که تابع Subtotal ندارد را برطرف نماید. البته این امکانات به معنای ضعف تابع Subtotal نمی باشد. در ادامه متوجه این مطلب خواهید شد.این تابع از ویرایش 2010 اکسل به بعد وجود دارد.

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

سوال: چرا همین امکان در تابع Subtotal قرار نگرفت؟

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

ساختار تابع AGGREGATE به دو فرم می باشد:

در حالت محدوده:

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

 AGGREGATE(محدوده دوم, محدوده اول , تنظیمات, شماره تابع, …)

در حالت آرایه:

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

AGGREGATE( اختیاری در بعضی توابع, محدوده , تنظیمات,شماره تابع)

function_num:

در این قسمت یک شماره بین 1 تا 19 وارد می کنیم.هر کدام از این اعداد کار یک تابع را برای ما انجام می دهند.به عنوان مثال عدد 9 برای ما عملیات جمع زدن را انجام می دهد.لیست زیر به شما در درک استفاده از این قسمت، کمک خواهد کرد.

options:

در این قسمت تنظیمات رفتار تابع AGGREGATE را تعیین می کنیم.این تنظیمات با اعداد بین 0 تا 7 صورت می گیرد و هر عدد رفتار خاص تابع را فعال خواهد کرد.به لیست زیر توجه کنید درک بهتری خواهید داشت.

شماره                       رفتار

0 یا خالی                    نادیده گرفتن تابع  AGGREGATE و Subtotal به صورت تو در تو (میان داده ها)
1                               نادیده گرفتن تابع  AGGREGATE و Subtotal به صورت تو در تو (میان داده ها) و ردیف پنهان
2                               نادیده گرفتن تابع  AGGREGATE و Subtotal به صورت تو در تو (میان داده ها) و خطا ها
3                               نادیده گرفتن تابع  AGGREGATE و Subtotal به صورت تو در تو (میان داده ها) و ردیف پنهان و خطا ها
4                               نادیده گرفتن هیچ چیز ( یعنی توابع تو در تو و ردیف های پنهان و خطاها را نادیده نخواهد گرفت)
5                               نادیده گرفتن ردیف های پنهان
6                               نادیده گرفتن خطاها
7                              نادیده گرفتن ردیف های پنهان و خطا ها

اگر این تنظیمات و به خاطر سپردن آن برای شما سخت می باشد.بهتر است از عدد 3 اسفاده کنید.

ref1 یا ref2 یا array :

در این قسمت می توانید محدوده یا رنج یا آرایه مورد نظر را وارد کنید. ref1 یا array اجباری بوده و تا 253 ref می توانید تعریف کنید.

[k]:

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

مثال:

جدول زیر را در نظر بگیرید.می خواهیم جمع ستون B2:B16 که پرداخت های ماهانه می باشد را به دست آوریم و برای گزارش گیری از ابزار فیلتر استفاده نماییم.نتیجه را با دو فرمول AGGREGATE و Subtotal به شما نشان داده ایم.

=AGGREGATE(9,3,B2:B16)

=SUBTOTAL(109,B2:B16)

همان طور که مشاهده می کنید، تابع Subtotal به علت وجود خطا در میان داده ها با اشکال روبرو شده است و به ما پیغام خطا #DIV/0! را نمایش می دهد.

اگر دقت کنید.استفاده از از Subtotal راحت رمی باشد.

همان طور که بیان شد، تابع AGGREGATE  از توابع بیشتری نسبت به Subtotal پشتیبانی می کند.توابع زیر در تعریف خود K را دارا می باشند.برای درک بهتر بر روی توابع Small و Large کلیک کنید تا کاربرد K را بدانید.

LARGE(array,k)

SMALL(array,k)

PERCENTILE.INC(array,k)

QUARTILE.INC(array,quart)

PERCENTILE.EXC(array,k)

QUARTILE.EXC(array,quart)

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

=AGGREGATE(14,3,B2:B16,10)

نکته: تابع AGGREGATE برای محاسبه داده های ستونی کاربرد دارد و ویژگی های گفته شده برای داده هایی که در ردیف قرار می گیرند، کاربردی ندارد.