Excel에서 AGGREGATE 함수의 8가지 용도 요약 예

이 글은 excel에 대한 관련 지식을 제공하며, AGGREGATE 함수에 대한 관련 내용을 주로 소개합니다. 이 함수의 사용법은 SUBTOTAL 함수와 유사하지만 SUBTOTAL 함수보다 더 강력한 기능을 살펴보겠습니다. 모두에게 도움이 되었으면 좋겠습니다.

관련 학습 권장 사항: excel 튜토리얼

AGGREGATE 함수는 SUBTOTAL 함수와 유사하지만 SUBTOTAL 함수보다 더 강력합니다. SUM, AVERAGE, COUNT, LARGE 등 19가지 함수만 구현할 수 있는 것은 아닙니다. , MAX 등의 기능을 수행하며 숨겨진 행, 오류 값, Null 값 등도 무시할 수 있으며 상수 배열을 지원합니다.

이 함수의 첫 번째 매개변수는 사용할 집계 방법을 지정하는 데 사용되는 1에서 19 사이의 숫자입니다.

두 번째 매개변수는 0에서 7 사이의 숫자이며 계산 영역에 지정됩니다. 값은 무시해야 합니다:

다음으로 이 함수의 몇 가지 일반적인 용도에 대해 이야기해 보겠습니다.

1. 여러 불연속 영역은 오류 값을 무시하고 직접 합산합니다.

이 함수의 장점은 다음과 같습니다. 2. 오류 값을 무시하고 직접 계산하도록 매개 변수를 지정할 수 있습니다. 아래 그림에 표시된 대로 파란색 영역에는 서로 다른 오류 값이 포함되어 있습니다. 이제 이러한 불연속 영역을 합산해야 합니다.

공식은 다음과 같습니다:

=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)

=AGGREGATE(9,6,A3:A7,C3:C4,D6:F7)

2、筛选状态下忽略错误值

如下图，在筛选后的数据区域中包含有错误值，如何对可见单元格进行统计呢？

公式为：

=AGGREGATE(9,7,B6:B18)

第一参数使用9，表示求和，第二参数使用7，表示忽略隐藏行和错误值。

3、一个公式解决多种统计效果

如下图，A3:B14单元格区域中是筛选后的的数据，要分别统计在可见区域和所有数据的最大、最小、平均、总和、计数和中位数。

只要一个公式就够了：

=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)

注意是区域数组公式，先选取c17:d22区域，然后在编辑栏写上公式，最后按ctrl+shift+enter三键录入。

4、向上求和你们都会，哪怕是筛选下的，向下呢？

=AGGREGATE(9,3,A4:A)*2-AGGREGATE(9,7,A4:A)

除了向下求和的方向外还有隐藏和错误值，这是subtotal+sum(if)都无法实现的统计效果

(录入方法是选取区域定位空值后编辑栏写完公式ctrl+enter批量填充)

5、这条开始才是重点-条件极值统计

这个函数提早五年就实现了2016才有的maxifs和minifs函数的统计效果，而且不需要三键。

如下图，要计算1车间对应的最小值，公式为：

=AGGREGATE(15,6,B4:B15/(A4:A15="1车间"),1)

公式中的第一参数使用15，表示使用SMALL函数，第二参数使用6，表示忽略错误值。要统计的区域是B4:B15/(A4:A15=”1车间”)

A4:A15=”1车间”部分，先对比A列的车间是不是等于指定的条件。如果A4:A15单元格区域中等于”1车间”，就返回逻辑值TRUE，否则返回逻辑值FALSE。然后再用B4:B15除以这组内存数组，结果为：

{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;19;47}

最后，AGGREGATE函数忽略里面的错误值，得到第一个最小值。

如果要计算1车间对应的第三个最小值，只需要将最后的1，变成3就好了。

如果要计算1车间对应的最大值，咱们可以修改一下第一参数，使用14，就是第k个最大值了。

6、 一对多查询

如果想要一对多查询，很多人想到的是INDEX+SAMLL+IF函数的三键客组合。其实，用aggregate函数替代也是能实现的。

如下图，要提取出二车间的所有工号，可以使用以下公式：

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(:)/(A:A=D),ROW(A1))),"")

2. 필터링 상태에서 오류 값을 무시🎜🎜아래와 같이 필터링된 데이터 범위에 오류 값이 포함되어 있습니다. 표시되는 셀 수를 계산하는 방법은 무엇입니까? 🎜🎜공식은 다음과 같습니다: 🎜🎜=AGGREGATE(9,7,B6:B18)🎜🎜🎜🎜 첫 번째 매개변수는 합산을 의미하는 9를 사용하고, 두 번째 매개변수는 숨겨진 행 및 오류 값을 무시한다는 의미인 7을 사용합니다. . 🎜🎜3. 하나의 공식으로 여러 통계 효과를 해결합니다🎜🎜아래와 같이 필터링된 데이터는 A3:B14 셀 영역에 표시되는 영역의 최대값, 최소값, 평균값, 개수 및 합계가 계산되어야 합니다. 각각 중앙값. 🎜🎜단 하나의 수식이면 충분합니다: 🎜🎜=AGGREGATE({4;5;1;9;3;12},{5,0},B4:B14)🎜🎜🎜🎜영역 배열이라는 점에 유의하세요. 먼저 c17:d22 영역을 선택한 후 편집바에 수식을 작성하고 마지막으로 Ctrl+Shift+Enter를 눌러 입력합니다. 🎜🎜4. 위쪽으로 합산하는 방법은 다들 아시죠, 필터링되더라도 아래쪽은요? 🎜🎜=AGGREGATE(9,3,A4:A$18)*2-AGGREGATE(9,7,A4:A$18)🎜🎜🎜🎜하향 합산 방향 외에도 숨겨진 값과 오류 값도 있습니다. 소계+합(if)으로는 얻을 수 없는 통계적 효과입니다🎜🎜(입력방법은 Null 값이 위치할 영역을 선택한 후 편집바에 Ctrl+Enter 수식을 써서 일괄적으로 채우는 것입니다) 🎜🎜5. 시작이 핵심 - 조건부 극값 통계🎜🎜 이 기능은 5년 전인 2016년에만 가능했던 maxifs, minifs 함수의 통계 효과를 구현했으며, 세 개의 키가 필요하지 않습니다. 🎜🎜아래 표시된 것처럼 1개 워크샵에 해당하는 최소값을 계산하는 공식은 다음과 같습니다. 🎜🎜=AGGREGATE(15,6,B4:B15/(A4:A15="1 워크샵"),1) 🎜🎜<img src="/static/imghwm/default1.png" data-src="https://img.php.cn/upload/image/467/651/536/1652758471757546.jpg?x-oss-process=image/resize,p_40" class="lazy" title="1652758471757546.jpg" alt="Excel에서 AGGREGATE 함수의 8가지 용도 요약 예"> 🎜🎜Formula 첫 번째 매개변수에 15를 사용하면 SMALL 함수를 사용하고, 두 번째 매개변수에 6을 사용하면 오류 값을 무시합니다. 계산되는 영역은 B4:B15/(A4:A15="1 작업장")🎜🎜A4:A15="1 작업장" 섹션입니다. 먼저 A열의 작업장이 지정된 조건과 동일한지 비교합니다. 셀 범위 A4:A15가 "1 Workshop"과 같으면 논리 값 TRUE가 반환되고, 그렇지 않으면 논리 값 FALSE가 반환됩니다. 그런 다음 이 메모리 배열을 B4:B15로 나누면 결과는 다음과 같습니다. 🎜🎜{70;69;87;77;55;46;#DIV/0!;#DIV/0!;#DIV/0!;# DIV/ 0!;19;47}🎜🎜마지막으로 AGGREGATE 함수는 내부의 오류 값을 무시하고 첫 번째 최소값을 가져옵니다. 🎜🎜워크숍 1에 해당하는 세 번째 최소값을 계산하려면 마지막 1을 3으로 변경하기만 하면 됩니다. 🎜🎜워크숍 1에 해당하는 최대값을 계산하려면 첫 번째 매개변수를 수정하여 k번째 최대값인 14를 사용하면 됩니다. 🎜🎜6. 일대다 쿼리🎜🎜 일대다 쿼리를 원한다면 INDEX+SAMLL+IF 함수의 3키 조합을 생각하시는 분들이 많습니다. 실제로 이를 집계함수로 대체하는 것도 가능하다. 🎜🎜아래와 같이 두 번째 워크샵의 모든 작업 번호를 추출하려면 다음 공식을 사용할 수 있습니다. 🎜🎜<code>=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($3:$12)) /(A$3 :A$12=D$3),ROW(A1))),"")🎜

이 공식의 아이디어는 기본적으로 다섯 번째 공식과 동일합니다.

7. 동일한 셀에 최대값 계산

아래 그림과 같이 B열에 여러 사람의 평가 상태가 동일한 셀에 기록되며, 최대값을 계산해야 합니다. 공식은 다음과 같습니다:

=AGGREGATE(14,6,--MID(B4,ROW($1:$50),COLUMN(A:AZ)),1)=AGGREGATE(14,6,--MID(B4,ROW(:),COLUMN(A:AZ)),1)

公式中的MID(B4,ROW(:),COLUMN(A:AZ))部分，使用MID函数，依次从第1~50个字符处开始，各提取长度为1~50的字符串，得到一个巨长的内存数组。再使用两个负号，把内存数组中的文本变成错误值，数值仍然是其本身的值。

最后使用AGGREGATE函数，忽略内存数组中的错误值，计算出其中的第一个最小值。

8、同时统计指定条件的最大最小值

如下图所示，要同时统计1车间对应的最大和最小值。

先同时选中F4:G4单元格，编辑栏输入以下公式，按Ctrl+Shift+回车。

=AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)

MID(B4,ROW( $1: $50), COLUMN(A:AZ)), MID 함수를 사용하여 1~50번째 문자부터 시작하여 길이가 1~50인 문자열을 추출하여 거대한 메모리 배열을 얻습니다. 그런 다음 두 개의 음수 기호를 사용하여 메모리 배열의 텍스트를 오류 값으로 바꾸고 그 값은 여전히 ​​고유한 값입니다.

마지막으로 AGGREGATE 함수를 사용하여 메모리 배열의 오류 값을 무시하고 그 중 첫 번째 최소값을 계산합니다.

8. 지정된 조건의 최대값과 최소값을 동시에 계산합니다.

아래 그림과 같이 1개의 작업장에 해당하는 최대값과 최소값을 동시에 계산해야 합니다.

먼저 F4:G4 셀을 동시에 선택하고 편집 막대에 다음 수식을 입력한 후 Ctrl+Shift+Enter를 누릅니다. =AGGREGATE({16,15},6,B4:B15/(A4:A15=E4),1)

🎜🎜🎜🎜AGGREGATE는 상수 배열 {16,15를 첫 번째 매개변수 }는 각각 최대값과 최소값을 사용하는 계산 규칙을 ​​나타냅니다. 🎜🎜최종 결과 역시 메모리 배열이므로 두 개의 셀을 동시에 선택하여 입력해야 합니다. 🎜🎜이 함수의 특징은 첫 번째 매개변수가 14~19일 때 네 번째 매개변수를 사용할 수 있다는 점입니다. 이때 네 번째 매개변수는 배열을 지원하므로 다양한 응용프로그램에서 사용할 수 없는 오류 값을 대체할 수 있습니다. SMALL, LARGE 및 기타 기능은 직접 무시됩니다. 🎜🎜관련 학습 권장 사항: 🎜excel 튜토리얼🎜🎜

위 내용은 Excel에서 AGGREGATE 함수의 8가지 용도 요약 예의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!