Excel의 AGGREGATE 함수
Excel의 AGGREGATE 함수 는 주어진 데이터 테이블 또는 데이터 목록의 집계를 반환합니다.이 함수에는 첫 번째 인수도 함수 번호로 있고 추가 인수는 데이터 세트 범위에 대한 것입니다. 사용할 함수를 알기 위해 함수 번호를 기억해야합니다. .
통사론
AGGREGATE 수식에는 두 가지 구문이 있습니다.
- 참조 구문
= AGGREGATE (함수 _ 번호, 옵션, 참조 1, 참조 2, 참조 [3],…)
- 배열 구문
= AGGREGATE (함수 _ 번호, 옵션, 배열, [k])
Function_num 은 사용하려는 특정 함수를 나타내는 숫자이며 1-19 사이의 숫자입니다.
옵션 : 또한 0에서 7 사이의 숫자 값이며 계산 중에 무시할 값을 결정합니다.
Ref1, ref2, ref [3] : 참조 구문을 사용하는 동안 인수이며, 계산을 수행하려는 숫자 값 또는 값입니다. 최소 두 개의 인수가 필요합니다. 나머지 인수는 선택 사항입니다.
배열 : 연산을 수행하려는 값의 배열로, Excel에서 AGGREGATE 함수의 배열 구문에 사용됩니다.
K : 선택적 인수이며 숫자 값이며 Excel에서 LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC 또는 QUARTILE.EXC와 같은 함수를 사용할 때 사용됩니다.
예
이 AGGREGATE 함수 Excel 템플릿은 여기에서 다운로드 할 수 있습니다 – AGGREGATE 함수 Excel 템플릿예 – # 1
숫자 목록이 있고 평균, 값을 포함하는 셀 수인 개수, 비어 있지 않은 셀 개수, 최대 값, 최소값, 제품 및 주어진 숫자 값의 합계를 계산한다고 가정합니다. 값은 아래 표에 나와 있습니다.
먼저 주어진 모든 값에 대해 행 9의 평균을 계산해 보겠습니다. 평균의 경우 function_ num은
C 열에서 모든 값이 제공되며 값을 무시할 필요가 없으므로 옵션 4를 선택합니다 (아무것도 무시하지 않음).
숫자 값의 배열로 값 C1 : C8의 범위를 선택합니다.
' k' 는 선택적 인수이며 Excel의 LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC 또는 QUARTILE.EXC와 같은 함수가 사용될 때 사용되지만이 경우에는 Average를 계산하므로 우리는 k 값을 생략합니다.
따라서 평균값은
마찬가지로 D1 : D8 범위의 경우 다시 옵션 4를 선택합니다.
E1 : E8 범위의 경우 E6 셀에 오류 값이 포함되어 있으며 동일한 AGGREGATE 수식을 사용하면 오류가 발생하지만 적절한 옵션을 사용하면 Excel의 AGGREGATE가 오류를 무시한 나머지 값의 평균을 제공합니다. E6의 값.
오류 값을 무시하기 위해 옵션 6이 있습니다.
마찬가지로 범위 G1 : G8의 경우 옵션 6을 사용합니다 (오류 값 무시).
이제 범위 H3에 대해 값 64를 입력하고 세 번째 행을 숨기고 옵션 5를 사용하여 숨겨진 행을 무시하면 Excel의 AGGREGATE는 보이는 숫자 값에 대해서만 평균 값을 제공합니다.
행 3을 숨기지 않고 출력
행 3을 숨긴 후 출력
다른 작업에 AGGREGATE 공식을 적용하면
예 – # 2
아래와 같이 다른 채널에서 다른 날짜에 생성 된 수익에 대한 테이블이 있다고 가정합니다.
이제 다른 채널에서 발생한 수익을 확인하려고합니다. 따라서 sum 함수를 적용하면 총 수익이 발생하지만 오가닉 채널이나 직접 채널 등에서 발생한 수익을 확인하려는 경우 Excel에서 동일한 필터를 적용하면 sum 함수는 항상 총액을 주다
채널을 필터링 할 때 표시되는 값의 합계를 가져 오기를 원하므로 SUM 함수를 사용하는 대신 AGGREGATE 함수를 사용하여 필터가 표시 될 때 표시되는 값의 합계를 얻습니다. 적용된.
따라서 SUM 수식을 옵션 코드 5 (숨겨진 행과 값 무시)로 AGGREGATE 함수로 바꾸면
이제 다른 채널에 필터를 적용하면 나머지 행이 숨겨 질 때만 해당 채널에 대한 수익이 표시됩니다.
직접 채널에 대해 생성 된 총 수익 :
자연 채널에 대해 생성 된 총 수익 :
유료 채널에 대해 생성 된 총 수익 :
따라서 AGGREGATE 함수는 필터링 된 여러 채널에 대해 생성 된 수익에 대해 다른 합계 값을 계산하는 것을 볼 수 있습니다. 따라서 AGGREGATE 함수는 조건부 수식을 사용하지 않고 다른 조건에 대해 다른 함수를 대체하는 데 동적으로 사용할 수 있습니다.
동일한 테이블 채널 및 수익에 대해 일부 수익 값에 오류가 있다고 가정합니다. 이제 오류를 무시하고 동시에 필터를 적용하려면 AGGREGATE 함수가 숨겨진 행 값도 무시해야합니다.
옵션 5를 사용하면 총 수익의 SUM에 대한 오류가 발생합니다. 이제 오류를 무시하기 위해 옵션 6을 사용해야합니다.
옵션 6을 사용하면 오류 값을 무시하고 합계를 얻지 만, 예를 들어 채널 값 Direct로 필터링하는 필터를 적용하면 오류를 무시하고 동일한 합계를 얻지 만 동시에 숨겨진 값도 무시해야합니다.
따라서이 경우 오류 값을 무시하는 옵션 7과 동시에 숨겨진 행을 사용합니다.
기억해야 할 사항
- AGGREGATE 함수는 19보다 크거나 1보다 작은 함수 _ num 값을 인식하지 못하며 마찬가지로 Option number의 경우 7보다 크고 1보다 작은 값을 인식하지 못합니다. 다른 값을 제공하면 #VALUE를 제공합니다. ! 오류
- 항상 숫자 값을 받아들이고 항상 숫자 값을 출력으로 반환합니다.
- Excel의 AGGREGATE에는 제한이 있습니다. 숨겨진 행만 무시하고 숨겨진 열은 무시하지 않습니다.