Excel의 놀라운 AGGREGATE 기능을 알아보세요. 하나는 19의 가치가 있습니다!

이전 기사 "실용적인 Excel 기술 공유: "찾기 및 바꾸기"를 사용하여 날짜 데이터 필터링"에서 "찾기 및 바꾸기"의 몇 가지 실제 작업에 대해 배웠습니다. 오늘 우리는 실제로 19의 가치를 지닌 마법의 Excel 통계 함수에 대해 이야기할 것입니다. 그것은 단순히 인공물일 뿐입니다! 빨리 수집하세요.

오늘 소개해드릴 기능은 AGGREGATE 입니다. 엑셀 2010에도 존재하는 함수이지만, 이 함수에 대해 아는 사람이 많지 않은 것이 매우 아쉬운 점이다. AGGREGATE 함수는 SUM, AVERAGE, COUNT, LARGE 등 19가지 함수의 기능만 구현할 수 없기 때문이다. 그리고 숨겨진 행, 오류 값, Null 값 등을 무시할 수 있습니다. 범위에 오류 값이 포함되어 있으면 SUM과 같은 함수는 오류를 반환하는데, 이 경우 AGGREGATE 함수가 매우 편리합니다.

말만 하고 가짜 동작을 연습하지 말고, 애그리게이트의 실력을 살펴보자.

점수 테이블을 사용하여 AGGREGATE의 기본 사용법을 설명합니다. 데이터 소스는 그림과 같습니다.

각 학생은 6번의 테스트를 치르고 결과에 따라 파란색 영역의 5가지 통계 내용이 표시됩니다. 대부분의 친구들에게 이 양식을 작성하는 것은 AVERAGE(평균 점수), SUM(총점), MAX만 익히면 어렵지 않습니다. (최고점수), MIN(최저점수), COUNT(실제점수)는 각각 5개 항목에 대한 통계를 수행합니다. 위에서 언급한 5가지 기능을 이해하지 못하는 새로운 친구들이 있을 수 있으므로 만약을 대비해 위 데이터의 통계를 구현하려면 AGGREGATE 기능만 배우면 됩니다.

AGGREGATE의 기본 형식은 = AGGREGATE(통계함수, 무시값, 데이터 영역) 입니다. 예제에서 5가지 통계 내용을 완성하는 방법을 살펴보겠습니다. = AGGREGATE(统计功能，忽略哪些值，数据区域)，以下分别来看看如何完成例子中的五项统计内容。

一、统计平均分

当前表格平均分统计公式为：=AGGREGATE(1,,B2:G2)。在H2单元格中输入公式再整列填充公式即可获得各学生的平均分。

说明：当统计功能为1的时候，函数实现计算平均值的功能。本例中我们并不需要指定忽略统计的数据，因此第二参数可以省略（此处写了两个逗号，中间省略了一个参数），最后一个参数就是要计算的数据区域B2:G2，函数用法非常简单，那么结果是否正确呢？不妨使用AVERAGE函数来验证一下：

可以看到，结果完全一致！

接下来我们再看看如何用AGGREGATE函数统计总分。

二、统计总分

当前表格总分统计公式为：=AGGREGATE(9,,B2:G2)。在I2单元格中输入公式再整列填充公式即可获得各学生总分。

只需要将第一个参数改为9即可，因为9对应的就是求和功能。

说到这里，可能会有些朋友担心，第一个参数里1代表平均值， 9代表求和，这个函数一共有19个功能，会不会很难记住。

实际上完全不需要有这种担心，Excel为我们提供了非常智能的提醒功能，当我们输入函数之后，就有对应参数功能的选项：

只要对照这个提示，选择自己需要的功能即可。

三、统计最高分

了解这个功能以后，最后的三个统计项目就很容易完成了，最高分肯定是选择4，因此J2单元格公式为：=AGGREGATE(4,,B2:G2)

四、统计最低分

最低分选择5，K2单元格公式为：=AGGREGATE(5,,B2:G2)

1. 통계 평균 점수

현재 테이블 평균 점수 통계 공식은 =AGGREGATE(1,,B2:G2)입니다. 셀 H2에 수식을 입력하고 전체 열을 수식으로 채워 각 학생의 평균 점수를 구합니다.

🎜설명 : 통계 함수가 1일 때, 평균을 계산하는 기능을 구현하는 함수입니다. 이 예에서는 통계를 무시하기 위해 데이터를 지정할 필요가 없으므로 두 번째 매개변수는 생략할 수 있습니다(여기에는 쉼표 2개를 쓰고 중간에 매개변수 1개가 생략됨). 함수 사용법이 매우 간단해서 결과가 맞나요? AVERAGE 함수를 사용하여 이를 확인할 수도 있습니다. 🎜🎜🎜🎜결과가 똑같다는 것을 알 수 있어요! 🎜🎜다음으로 AGGREGATE 함수를 사용하여 총점을 계산하는 방법을 살펴보겠습니다. 🎜🎜🎜🎜2. 통계 총점 🎜🎜🎜🎜현재 테이블 총점 통계 공식은 =AGGREGATE(9,,B2:G2)입니다. 셀 I2에 수식을 입력하고 전체 열을 수식으로 채워 각 학생의 총점을 구합니다. 🎜🎜🎜🎜만 9는 합산 함수에 해당하므로 첫 번째 매개변수를 9로 변경해야 합니다. 🎜🎜이렇게 말하면 첫 번째 매개변수의 1이 평균이고 9가 합을 의미한다고 걱정하는 친구들도 있을 것입니다. 이 함수에는 총 19개의 함수가 있으므로 기억하기 어려울 것입니다. 🎜🎜실제로 이에 대해 전혀 걱정할 필요가 없습니다. Excel은 함수를 입력한 후 해당 매개변수 함수에 대한 옵션을 제공합니다. 🎜🎜🎜🎜이 프롬프트를 따르고 필요한 기능을 선택하세요. . 🎜🎜🎜🎜3. 최고 점수의 통계🎜🎜🎜🎜이 함수를 이해하면 마지막 세 가지 통계 항목을 쉽게 완료할 수 있으므로 최고 점수는 4가 되어야 하므로 J2 셀 공식은 다음과 같습니다. 4,,B2:G2)🎜🎜🎜🎜🎜🎜4. 가장 낮은 점수에 대한 통계🎜🎜🎜🎜 가장 낮은 점수에 대해 5를 선택하세요. 셀 K2의 공식은 다음과 같습니다. =AGGREGATE(5,,B2:G2) 🎜 🎜🎜🎜🎜🎜🎜5. 통계 실제 참고 과목🎜🎜🎜

실제 참조 계정은 통계 데이터 영역에 있는 숫자의 개수입니다. COUNT 함수를 사용하여 2를 선택하면 수식은 =AGGREGATE(2,,B2:G2)=AGGREGATE(2,,B2:G2)

好了，通过以上五个例子，朋友们对于AGGREGATE的基本用法应该有所掌握，虽然说只用了一个函数就完成了五个函数的工作，相比之前要分别使用五个函数来完成工作提高了一定的效率，但每个公式还是要修改一下才能用。如果能够使用一个公式右拉下拉的话，那才爽呢。（有同感的朋友可以在文末留言哦）

六、五种统计一步到位

对于有这种想法的朋友，应该提出表扬，毕竟我们学习Excel的函数公式，不仅仅是为了完成工作，更加希望能够提高效率。那么有没有可能使用一个公式右拉下拉来完成例子中的五项统计呢？答案是肯定的：有！不过要用到一对函数组合，那就是choose和column。

在揭晓公式之前，先对问题进行简单的分析，在我们使用AGGREGATE完成五项数据统计的公式中，只有第一参数也就是统计方式在发生变化，依次为：1、9、4、5、2。如果要想使用一个公式右拉下拉来完成的话，就得让公式在右拉时第一参数按照这个顺序来进行变化（下拉时不需要变化，因为统计方式相同）。

通常要使用公式右拉得到顺序变化的数据时就会用到column这个函数：

Column这个函数的作用是得到参数对应的列号，例如column（a1）就得到a1这个单元格的列号也就是1，右拉时由于a1会变成b1、c1……，公式结果就会按照1、2、3……这个顺序变化。

在本例中，我们需要得到的并不是一个很有规律的数列，而是1、9、4、5、2这样一个无序的数列，这时候就要用到choose函数来实现：

Choose函数的基本格式为：=choose(选择指数，值1，值2，值3……)

Choose函数根据第一个参数的数字来返回参数列表中的值。例如上图，当第一参数为1时，就返回参数列表中的第1个值“1”；当第一参数为2时，就返回参数列表中的第2个值“9”，以此类推，使用column作为choose的第一参数，就可以返回指定的序列了。

以上是对choose和column这对函数组合的说明，现在回到我们的问题，可以用来右拉下拉的这个公式就是：=AGGREGATE(CHOOSE(COLUMN(A1),1,9,4,5,2),,$B2:$G2)

可能有些新手还是会觉得晕乎乎的，这很正常，相信通过持续地学习，你就可以对这种公式运用自如了。

七、第一参数功能集锦

通过以上介绍，可以看到当我们合理运用了AGGREGATE函数之后，工作效率成倍增长。这个函数的第一参数到底有哪19种功能呢，通过下面这个对照表可以一目了然：

实际上比较常用的就是那么几种。

八、第二参数功能集锦

接下来我们再来看看第二参数又是什么功能，还是通过一个对照表来直观地了解：

1.忽略空值

以下通过两个例子看看如何使用第二参数来选择忽略的内容：=AGGREGATE(9,1,B2:B15)

자, 위 내용을 통해 다섯 가지 예를 통해 친구들은 AGGREGATE의 기본 사용법을 이해해야 합니다. 하나의 함수만 사용하여 다섯 가지 기능을 완료하지만 이전에 다섯 가지 기능을 사용하여 작업을 완료한 것에 비해 효율성이 어느 정도 향상되었지만 각 기능은 다음과 같습니다. 수식을 사용하려면 먼저 수정해야 합니다. 오른쪽으로 당기는 공식을 사용할 수 있다면 좋을 것입니다. (같은 생각을 가진 친구들은 글 마지막에 메시지를 남겨주세요)

한 단계로 6~5가지 통계 강>

이런 아이디어가 있는 친구는 칭찬받아 마땅합니다. 결국 우리는 업무를 완성하기 위해서만이 아니라 효율성을 높이기 위해서도 엑셀 함수 공식을 배웁니다. 그러면 예제의 5개 통계를 완성하기 위해 오른쪽으로 풀다운하는 수식을 사용하는 것이 가능합니까? 대답은 '그렇다'입니다. 그렇습니다! 그러나 한 쌍의 기능 조합, 즉 choose와 Column이 필요합니다.

공식을 공개하기 전에, 문제에 대한 간단한 분석을 해보겠습니다. 공식에서는 AGGREGATE를 사용하여 5개의 데이터 통계를 완성하는데, 통계 방법인 첫 번째 매개변수만 1, 9, 4의 순서로 변경됩니다. , 5 ,2. 오른쪽으로 풀다운하는 수식을 사용하려면 오른쪽으로 풀다운할 때 수식의 첫 번째 매개변수를 이 순서대로 변경해야 합니다. (풀다운할 때는 통계 방법이 동일하므로 변경할 필요가 없습니다.) ). 🎜🎜일반적으로 순차적으로 변경되는 데이터를 가져오기 위해 오른쪽으로 당겨 수식을 사용해야 하는 경우 열 기능을 사용합니다. 🎜🎜🎜🎜Column 이 함수는 매개변수에 해당하는 열 번호를 가져오는 데 사용됩니다. 예를 들어 열 (a1)은 셀 a1의 열 번호, 즉 1입니다. 오른쪽으로 당기면 a1이 b1, c1...이 되기 때문에 수식 결과는 1, 2, 3...의 순서로 변경됩니다. 🎜🎜이 예에서 우리가 얻어야 할 것은 매우 규칙적인 시퀀스가 ​​아니라 1, 9, 4, 5, 2의 순서가 없는 시퀀스입니다. 이때 다음을 달성하려면 선택 기능을 사용해야 합니다. 🎜 🎜🎜🎜기능 선택 기본 형식은 다음과 같습니다. =choose(인덱스 선택, 값 1, 값 2, 값 3...)🎜🎜Choose 함수는 첫 번째 매개변수의 수를 기준으로 매개변수 목록의 값을 반환합니다. 예를 들어, 위 그림에서 첫 번째 매개변수가 1이면 매개변수 목록의 첫 번째 값 "1"이 반환되고, 첫 번째 매개변수가 2이면 매개변수 목록의 두 번째 값 "9"가 반환됩니다. , 선택의 첫 번째 매개변수로 열을 사용하면 지정된 시퀀스를 반환할 수 있습니다. 🎜🎜위는 선택과 열의 함수 조합에 대한 설명입니다. 이제 문제로 돌아가서 오른쪽 풀다운에 사용할 수 있는 공식은 다음과 같습니다. =AGGREGATE(CHOOSE(COLUMN(A1),1, 9, 4,5,2),,$B2:$G2)🎜🎜🎜🎜 아직 초보분들이 ​​어지러움을 느끼실 수도 있는데 이는 정상적인 현상입니다. 지속적인 학습을 통해 이 공식을 자유롭게 활용하실 수 있을 것이라 믿습니다. 🎜🎜7. 첫 번째 매개변수 함수 모음🎜🎜위 소개를 통해 AGGREGATE 함수를 올바르게 사용하는 것을 확인할 수 있습니다. , 작업 효율성이 기하급수적으로 증가합니다. 이 함수의 첫 번째 매개변수의 19가지 기능은 무엇인가요? 다음 비교표를 보면 명확하게 알 수 있습니다: 🎜🎜🎜🎜사실 일반적으로 사용되는 것은 몇 가지뿐입니다. 🎜🎜8. 두 번째 매개변수 함수 모음🎜🎜다음으로 두 번째 매개변수가 어떤 함수인지 살펴보겠습니다. 비교표를 통한 시각적 이해: 🎜🎜🎜🎜1. null 값 무시🎜🎜 두 가지 예를 통해 두 번째 매개변수를 사용하여 무시된 콘텐츠를 선택하는 방법을 살펴보겠습니다. =AGGREGATE(9,1, B2:B15)🎜🎜🎜🎜🎜첫 번째 매개변수로 합계를 나타내는 9를 선택하고, 두 번째 매개변수로 숨겨진 행 무시를 나타내는 1을 선택합니다. 모든 데이터가 표시되면 AGGREGATE 함수를 사용하여 합산합니다. 및 SUM 함수 결과는 일관됩니다(행 16의 총 점수는 SUM 함수를 사용하여 합산됩니다). 특정 데이터 행을 숨기면 차이를 확인할 수 있습니다. 🎜🎜🎜🎜

행 4, 8, 11을 숨긴 후 수식 =AGGREGATE(9,1,B2:B15)은 현재 표시된 데이터만 요약합니다.

이렇게 말하면 SUBTOTAL 기능을 배운 학생들은 분명히 SUBTOTAL에도 그런 기능이 있다고 생각할 것입니다. 하지만 오늘 등장한 AGGREGATE 함수는 SUBTOTAL 함수보다 더 강력합니다. SUBTOTAL은 오류 값과 중첩된 요약 요약을 처리할 수 없지만 AGGREGATE는 여전히 처리할 수 있기 때문입니다.

2. 오류 값 무시

오늘 마지막 예에서 오류 값이 발생하면 어떤 일이 발생하는지 살펴보겠습니다.

위 그림에 표시된 대로 각 학생의 중국어 점수는 vlookup 함수를 사용하는 vlookup 함수 점수 테이블에서 얻습니다(이 함수는 이전 튜토리얼에서 논의되었으며, 아직 모르는 경우 링크를 클릭하여 자세히 알아볼 수 있습니다: 링크 삽입). 점수표에 이름이 없으면 Li Si, Zhang San과 같은 오류 값이 얻어집니다. 이때 SUM 함수나 SUBTOTAL 함수를 사용해도 정확한 총점을 얻을 수 없습니다. 중국어 점수. AGGREGATE만 오류 값을 무시하여 올바른 결과를 얻을 수 있습니다. 물론 iferror 및 기타 함수를 사용하여 처리한 다음 SUM을 사용하여 합계를 계산할 수 있지만 이로 인해 AGGREGATE의 기능이 가려지지는 않습니다.

19가지 통계 함수와 7가지 무시 항목 이 놀라운 통합 기능은 정말 일반 기능과 비교할 수 없습니다! AGGREGATE는 통계 함수의 왕입니다. 지금 수집하세요!

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

위 내용은 Excel의 놀라운 AGGREGATE 기능을 알아보세요. 하나는 19의 가치가 있습니다!의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!