031

이 기사에서는 테이블에서 모든 값을 일치시키고 다른 셀에서 검색하는 방법에 대해 설명합니다. 이는 여러 값을 조회하는 것과 유사합니다.

일반 공식

{=INDEX(names,SMALL(IF(groups=group_name,ROW(names)-MIN(ROW(names))+1),COLUMNS(expanding ranges))), “--List Ends--”)}

너무 많은 함수와 변수 !!!. 이 변수가 무엇인지 봅시다.

이름 : 이름 목록입니다.

그룹 : 이러한 이름이 속한 그룹 목록입니다.

Group_name : 그룹 이름의 참조입니다.

link : / excel-range-name-expanding-references-in-excel [확장 범위]: 오른쪽으로 복사 할 때 숫자를 늘리는 데 사용되는 확장 범위입니다.

예 : 회사에 따라 다른 열에서 직원 이름을 추출합니다.

030

회사별로 그룹화 된 직원 테이블이 있다고 가정 해 보겠습니다. 첫 번째 열에는 직원 이름이 포함되고 두 번째 열에는 회사 이름이 포함됩니다.

이제 회사에 따라 각 직원의 이름을 다른 열에 가져와야합니다. 즉, 그룹을 해제해야합니다.

여기서는 A2 : A10을 Employee로, B2 : B10을 Company로 지정하여 수식을 쉽게 읽을 수 있습니다.

이 배열 수식을 F2에 씁니다. 이 수식을 입력하려면 Ctrl + Shift + Enter를 사용하십시오.

{=INDEX(Employee,SMALL(IF(Company=$E2,ROW(Employee)-MIN(ROW(Employee))+1),COLUMNS($E$1:E1))), “--List Ends--”)}

이 수식을 모든 셀에 복사합니다. 그룹에 따라 다른 열에서 각 개별 이름을 추출합니다.

031

위 이미지에서 볼 수 있듯이 각 직원은 서로 다른 셀에 분리되어 있습니다.

그렇다면이 공식은 어떻게 작동합니까?

공식을 이해하기 위해 G2에서 = IFERROR (INDEX (Employee, SMALL (IF (Company = $ E3, ROW (Employee) -MIN (ROW (Employee)) + 1), COLUMNS ($ E $ 1 : F2))), “-목록 끝-“)

메커니즘은 간단하며`link : / lookup-formulas-vlookup-multiple-values ​​[multiple VLOOKUP formula]`와 거의 동일합니다. 트릭은 다른 그룹에서 각 직원의 색인 번호를 가져 와서 INDEX 공식에 전달하는 것입니다. 이것은 공식의이 부분에 의해 수행됩니다.

IF (회사 = $ E3,link : / lookup-and-reference-excel-row-function [ROW](직원) -MIN (`link : / lookup-and-reference-excel-row-function [ROW] `(직원 *)) + 1) :

이 부분은 “Rankwatch”를 포함하는 $ E3의 회사 이름에 대해 인덱스 배열과 false를 반환합니다.

\ {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}

어떻게? 안쪽에서 떼어 내자.

여기서는 $ E3의 회사 이름을 회사 범위 (회사 = $ E3)의 각 값과 일치시킵니다.

이것은 true와 false의 배열을 반환합니다. \ {FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE}.

이제 IF 함수는 TRUE에 대해 TRUE 문을 실행합니다. 이는`link : / lookup-and-reference-excel-row-function (Employee) -MIN (`link : / lookup-and-reference-excel-입니다. row-function [ROW](직원)) + 1.

이 부분은이 부분을 반환합니다. 1부터 시작하여 직원 수 \ {1; 2; 3; 4; 5; 6; 7; 8; 9}까지 인덱스 배열을 반환합니다. if 함수는 TRUE에 대한 값만 표시하여 \ {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}를 제공합니다.

현재 공식은 = IFERROR (INDEX (Employee, SMALL (\ {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}, COLUMNS ($ E $ 1 : F2))), “-로 단순화됩니다. 목록 끝- “). 우리가 알고 있듯이 작은 함수는 배열에서 n 번째로 작은 값을 반환합니다. COLUMNS ($ E $ 1 : F2) 2를 반환합니다. SMALL 함수는 위의 배열에서 두 번째로 작은 값인 4를 반환합니다.

이제 수식이 단순화되었습니다. = IFERROR (INDEX (Employee, 4), “-List Ends–“). 이제 INDEX 함수는 직원 배열에서 “Sam”을 제공하는 네 번째 이름을 반환합니다.

예, 이것은`link : / excel-range-name-expanding-references-in-excel ,`link : / statistical-formulas-excel-small-function [SMALL '을 사용하여 다른 열의 그룹에서 이름을 추출하는 방법입니다. ],link : / lookup-and-reference-excel-row-function [ROW], COLUMNS 및 IF 함수. 이 기능에 대해 궁금한 점이 있거나 작동하지 않는 경우 아래 의견 섹션을 알려주십시오.

파일 다운로드 :