엑셀 타이거밤 심사 포뮬러 "INDEX-SMALL-IF-ROW" 심층 분석

이 문서에서는 Excel에서 수식 필터링을 사용하여 일대다 검색을 완료하는 방법을 공유합니다. 이는 수식 데이터를 자동으로 찾는 비교적 고전적인 Excel 필터링 기능 수식입니다.

전문가들이 타이거밤 공식이 있다는 말을 늘 듣는데, 타이거밤 공식은 정확히 무엇이며, 이 엑셀 공식은 무엇을 할 수 있나요? 아래 렌더링을 살펴보겠습니다.

이 예는 일반적인 일대다 검색입니다. 데이터 소스의 각 부서는 여러 데이터에 해당합니다. 뱀기름 공식의 목적은 일대다 검색과 같은 비교적 복잡한 문제를 해결하는 것입니다. 위 애니메이션의 공식은 다음과 같습니다.

=IFERROR(INDEX($A$2:$D$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99 ) ,ROW(A1)),MATCH(F$3,$A$1:$D$1,0)),"")=IFERROR(INDEX($A:$D,SMALL(IF($C:$C=$F,ROW(:),99),ROW(A1)),MATCH(F,$A:$D,0)),"")

看到这个公式，或许很多朋友都会惊叹：这么长的公式，看不懂哇！

今天就和大家一同破解这个看不懂但又很强悍的公式套路，耐心往下看哦……

上面这个公式一共用了六个函数：IFERROR、INDEX、SMALL、IF、ROW和MATCH，其中的IFERROR和MATCH是本例中辅助性的两个函数，其余的四个INDEX-SMALL-IF-ROW就是万金油公式啦。

因此我们先来学习这个核心部分的原理：

F4单元格的公式为：

=INDEX($A:$A,SMALL(IF($C:$C=$F,ROW(:),99),ROW(A1)))

先从INDEX说起，这个函数基本功能是给出一个区域，然后根据对应的行列位置返回查找结果，上图中INDEX查找的数据区域就是姓名所在的区域$A:$A。

INDEX函数的基本结构是：INDEX（查找区域，第几行，第几列），如果区域是单行或者单列的话，后面两个参数可以省略一个。通俗点说，你拿着电影票去找座位，整个大厅的座位就是区域，第几排第几座就是公式中的后面两个参数，通过这种方式可以准确找到目标位置。

在上面这个例子里，区域是在一列，所以我们只需要确定每个数据在第几行就行。

明白这一点的话，我们的重点就该放到INDEX的第二个参数了：

SMALL(IF($C:$C=$F,ROW(:),99),ROW(A1))

注意看上面这个图，销售部一共有四条记录，分别在数据区域的第5、8、9和16行（数据区域是从第二行开始）。

因此我们希望公式下拉的时候，INDEX的第二个参数分别是5、8、9和16这四个数字（这一点一定要想明白）。

注意，接下来我们即将接触到万金油最核心的部分，请保持高度集中的注意力……

SMALL函数的基本结构：SMALL（一组数，第几小的数）

이 공식을 보면 많은 친구들이 놀랄 것입니다. 이렇게 긴 공식입니다. 모르겠어요!

오늘 저는 이 이해하기 어렵지만 매우 강력한 수식 루틴을 해독하기 위해 여러분과 협력할 것입니다. 인내심을 가지고 읽어주세요...

위 수식은 총 6개의 함수인 IFERROR, INDEX, SMALL, IF, ROW 및 MATCH를 사용합니다. IFERROR 및 MATCH는 이 예에서 두 개의 보조 함수이고 나머지 4개의 INDEX-SMALL-IF-ROW는 뱀기름 공식입니다.

그러므로 이 핵심 부분의 원리를 먼저 배워 보겠습니다.

셀 F4의 공식은 다음과 같습니다.

=INDEX($A$2:$A$21,SMALL(IF($C$2:$C$21= $F$2,ROW($1:$20),99),ROW(A1)))

INDEX부터 시작해 보겠습니다. 이 함수의 기본 기능은 영역을 제공한 다음 해당 검색 결과에 따라 검색 결과를 반환하는 것입니다. 해당 행과 열 위치에 따라 위 그림에서 INDEX로 검색한 데이터 영역은 해당 이름이 위치한 $A$2:$A$21 영역입니다.

INDEX 함수의 기본 구조는 INDEX(검색 영역, 어느 행, 어느 열)입니다. 영역이 단일 행 또는 열인 경우 마지막 두 매개 변수 중 하나를 생략할 수 있습니다. 평신도 입장에서는 좌석을 찾기 위해 영화표를 뽑을 때, 홀 전체의 좌석이 면적이고, 행과 좌석이 공식의 마지막 두 매개변수가 되는 방식으로 대상 위치를 정확하게 찾을 수 있습니다. .
위 예에서는 영역이 하나의 열에 있으므로 각 데이터가 어느 행에 있는지 확인하면 됩니다.

이를 이해하려면 INDEX의 두 번째 매개변수인

SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20), 99),ROW(A1에 초점을 맞춰야 합니다. ))

위 그림을 주목하세요. 영업부에는 데이터 영역의 5, 8, 9, 16행에 각각 4개의 레코드가 있습니다(데이터 영역은 두 번째 행부터 시작).

🎜그래서 수식을 풀면 INDEX의 두 번째 매개변수가 4개의 숫자 5, 8, 9, 16이기를 바랍니다(이것을 이해해야 합니다). 🎜🎜주의하세요 다음으로 타이거밤의 핵심부분을 접하게 되는데, 높은 집중력을 유지해주세요... 🎜🎜SMALL 함수의 기본 구조: SMALL(숫자 집합, 가장 작은 숫자) code>🎜🎜이 함수를 완전히 이해하려면 간단한 데이터를 시뮬레이션하는 것이 좋습니다. 방법은 다음과 같습니다. 🎜🎜🎜🎜🎜🎜열 A에 숫자를 입력하세요. 수식은 가장 작은 숫자를 의미합니다. 이 열의 결과는 2입니다. 이해하기 쉽습니다. 공식의 두 번째 매개변수를 2로 변경하고 결과를 살펴보세요. 🎜🎜🎜🎜🎜🎜두 번째에서 4입니다. 🎜🎜계속해서 세 번째로 작은 숫자를 얻으려면 어떻게 해야 할지 다들 생각하실 거라 생각하는데, 두 번째 매개변수는 수동으로만 수정할 수 있고 풀다운을 통해서는 이 매개변수를 변경할 수 없다는 문제가 있을 것입니다. 원하는 경우 두 번째 매개변수에 대해 ROW 함수를 사용해야 하며 다음과 같이 수정됩니다. 🎜🎜🎜🎜🎜🎜 ROW 함수는 매우 간단합니다. 이 공식을 통해 A열에 데이터를 입력했습니다. 가장 작은 것부터 큰 것 순으로 순위를 매겼는데, 재미있을 것 같나요? 🎜🎜Tiger Balm 공식으로 돌아가서 5, 8, 9, 16이라는 네 숫자가 무엇을 의미하는지 기억하세요. 이 네 숫자를 순서대로 얻으려면 SMALL 함수를 사용해야 합니다. C열이 F2와 일치하는지 판단하는 것입니다. . 줄 번호가 같으면 다르면 최대 줄 번호보다 큰 번호를 얻습니다. (발견을 방지하기 위한 목적): 🎜🎜🎜🎜🎜🎜이 목표를 달성하기 위해 IF 함수 개입이 필요하므로 여기까지입니다: 🎜<p><code>IF($C$2:$C$21=$F$2,ROW($1:$20),99), 이 단락을 SMALL의 첫 번째 매개변수로 사용합니다. IF($C:$C=$F,ROW(:),99)，用这一段来作为SMALL的第一个参数。

关于这段IF，就比较容易理解了，我们可以借助F9来看看这段公式的结果：

因为我们的数据就20个，所以IF的第三个参数使用99就足够了，如果数据量比较大的话，可以用9^9，表示9的9次方，反正足够大就行。

搞清楚这个IF的话，再来看这段SMALL(IF($C:$C=$F,ROW(:),99),ROW(A1))

이 IF에 대해서는 F9를 사용하여 다음 공식의 결과를 확인하는 것이 더 쉽습니다.

우리 데이터가 20밖에 안 되므로 IF의 세 번째 매개변수로 99를 사용해도 충분합니다. 양이 상대적으로 크면 9^9를 사용할 수 있습니다. 즉, 9의 9승을 의미합니다. 어쨌든 충분히 커야 합니다.

이 IF를 이해했다면 이 문단 SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1))을 보세요. 이제 너무 어지럽지 않나요?

SMALL 부분에 관해서는 수식이 풀다운되면서 우리가 원하는 숫자를 하나씩 얻은 다음 이 숫자를 INDEX의 두 번째 매개변수로 사용하여 필요한 최종 결과를 얻는다는 점을 이해해야 합니다.

타이거밤의 핵심은 INDEX, SMALL, IF, ROW 인데 이 부분을 원리를 이해하기 위해 계속해서 생각해 보시기 바랍니다. 강조해야 할 또 다른 매우 중요한 점이 있습니다. 호랑이 연고 공식은 배열 공식이므로 Ctrl과 Shift를 누른 상태에서 Enter를 눌러야 합니다.

처음의 수식은 여러 열의 내용을 찾아야 하는 점을 고려하여 INDEX의 데이터 영역은 $A$2:$D$21을 사용하므로 열이 여러 개 있을 경우 해당 열을 제공해야 합니다. 위치를 사용하여 목표 값을 찾을 수 있으므로 MATCH(F$3,$A$1:$D$1,0)를 사용하여 데이터가 어느 열에 있는지 확인합니다.

각 부서의 데이터가 다릅니다. 이때 수식의 가장 바깥쪽 레이어에 있는 IFERROR 함수를 사용하여 몇 줄을 더 내려야 합니다. 값을 사용하면 쿼리 결과가 매우 깨끗해 보입니다. 🎜🎜오늘은 타이거밤 포뮬러의 원리를 설명하기 위해 일대다 검색 예시를 사용했습니다. 사실 타이거밤 포뮬러는 여러 가지가 있지만 마음에 드시면 관련 예시를 계속 공유하겠습니다. 물론, 이 글을 읽으시면 어떤 복잡한 공식을 직접 해석하시는 것이 더 나을 것입니다. 🎜🎜관련 학습 권장 사항: 🎜excel 튜토리얼🎜🎜

위 내용은 엑셀 타이거밤 심사 포뮬러 "INDEX-SMALL-IF-ROW" 심층 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!