تابع AGGREGATE در اکسل

 

دانلود ویدئو آموزشی

دانلود فایل اکسل آموزشی فرمول AGGREGATE

تابع AGGREGATE

در اینجا فرمول و نحوه استفاده از تابع AGGREGATE در مایکروسافت اکسل به همراه یک فیلم آموزشی کوتاه توضیح داده شده است.

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

فرمول تابع:

Reference form

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

Array form

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

 

Function_num: عددی بین 1 تا 19 می باشد، که برای  استفاده از هر تابع می توانید عدد مربوط به آن تابع که در جدول زیر موجود می باشد را وارد کنید. مثلا برای استفاده از تابع میانگین (AVERAGE) می بایست عدد 1 را وارد کنید.

 

 Function_num Function
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

 

Options: عددی بین 0 تا 7 می باشد، با انتخاب هر یک از اعداد موجود در جدول زیر می توانی مشخص کنید که از کدام یک از موارد موجود صرف نظر گردد. برای مثال اگر عدد 5 را وارد کنید، ردیف های پنهان شده در محاسبات نظر گرفته نمی شود. و یا در صورت وارد کردن عدد 6 ارورهای موجود را در نظر نمی گیرد.

 

Option Behavior
0 or omitted Ignore nested SUBTOTAL and AGGREGATE functions
1 Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2 Ignore error values, nested SUBTOTAL and AGGREGATE functions
3 Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4 Ignore nothing
5 Ignore hidden rows
6 Ignore error values
7 Ignore hidden rows and error values

 

Ref1 مجموعه ای از اعداد که شما می خواهید تابع AGGREGATE را روی آن اعمال کنید (یک محدوده از سلول ها).

Ref2، ورود این داده اختیاری است. آرگمان عددی بین 2 الی253 است که شما می خواهید تابع AGGREGATE را روی آن اعمال کنید. آرگومان دوم (Ref2)  برای توابع خاص مورد نیاز است.

توابع زیر (که تعدادی از توابع موجود در جدول اول می باشند) نیاز به ref2 دارند:

تابع

Function
LARGE(array,k)
SMALL(array;k)
PERCENTILE.INC(array;k)
QUARTILE.INC(array;quart)
PERCENTILE.EXC(array;k)
QUARTILE.EXC(array;quart)

 

نکته: هر گاه فرمولی رو کپی و پیست کردید و با خطای زیر رو به رو شدید، علت این موضوع این است که جدا کننده متغییر ها در ویندوز کامپیوتر شما ممکن است بر روی چیز دیگری تنظیم شده باشد، که در بیشتر موارد یا از سمی کالون “;” و یا از ویلگول “,”  استفاده می گردد. (نحوه تغییر این جدا کننده را در اینجا میتوانید مشاهده کنید.)

 

ملاحظات:

 :Function_num به محض این که شما در محل آرگمان function_num تایپ کنید، یک لیست از تمام توابع موجود که می توانید به عنوان آرگومان استفاده کنید (همان توابع موجدو در جدول اول)، ظاهر خواهد شد.

خطاها:

 اگر از تابعی استفاده گردد که نیاز به آرگمان دوم دارد، اما این آرگمان ارائه نگردد، تابع خطای #VALUE!  را برمی گرداند.

 اگر یک یا چند تا از مراجع، مرجع 3D باشند، تابع خطای #VALUE!  را برمی گرداند.

نوع محدوده:

تابع AGGREGATE برای ستونی از داده ها در نظر گرفته شده است ، یا برای محدوده عمودی طراحی شده است، و برای ردیفی از داده ها، یا محدوده افقی طراحی نشده است.به عنوان مثال:، هنگامی که شما جمع محدوده افقی با استفاده از گزینه 1، مانند AGGREGATE(1, 1, ref1)، ستون های پنهان شده بر روی مجموع کل تاثیر نمی گذارد. اما، یک ردیف پنهان شده در محدوده عمودی بر روی مجموع تاثیر می گذارد.

به عنوان مثال:

داده های جدول زیر را کپی کنید و آنها را در سلول A1 از اکسل پیست کنید. شما می توانید عرض ستون را تنظیم کنید تا بتوانید همه داده ها را ببینید.

#DIV/0! 82  
72 65  
30 95  
#NUM! 63  
31 53  
96 71  
32 55  
81 83  
33 100  
53 91  
34 89  
فرمول توضیحات نتیجه
=AGGREGATE(4; 6; A1:A11) Calculates the maximum value while ignoring error values in the range 96
=AGGREGATE(14; 6; A1:A11; 3) Calculates the 3rd largest value while ignoring error values in the range 72
=AGGREGATE(15; 6; A1:A11) Will return #VALUE! error. This is because AGGREGATE is expecting a second ref argument, since the function (SMALL) requires one. #VALUE!
=AGGREGATE(12; 6; A1:A11; B1:B11) Calculates the median while ignoring error values in the range 68
=MAX(A1:A2) Will return error value, since there are error values in the evaluation range. #DIV/0!

 

 

دیدگاهتان را بنویسید

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