단일 검색 기준 여러 결과

Question

단일 검색 기준만 사용하여 정렬된 데이터 열의 결과를 출력해야 합니다. 동일한 기준이 여러 번 나타나는 경우도 있습니다. LOOKUP은 첫 번째 항목만 찾습니다. 일치하도록 셀 셀에 입력해야 하는 것은 J8:J581이고 표시할 해당 데이터는 N8:N581입니다.

J   K L M N
bob       RED
bob       BLUE
Bob       Green
Sue       yellow
Sue       white
fred      grey
pete      brown

.

input=bob
output=    bob  RED
                BLUE
                Green

Answer 1

위의 샘플과 같은 행을 반환하려는 경우 Excel 표를 사용하세요.

1. 데이터를 선택하세요
2. 테이블 만들기 Insert>Tables>Table(내 테이블에 헤더가 있음 확인란 선택)
3. 테이블이 생성되면 필터 버튼(테이블 머리글 행의 아래쪽 화살표)을 선택하고 필터 값(예: 샘플에서 Bob 선택)을 선택하면 "Bob" 행만 반환됩니다.

이전 이미지는 다음과 같습니다.

그리고 그 이후에는:

편집하다: 추가 정보를 바탕으로 데이터 테이블을 기반으로 피벗 테이블을 추가하는 것이 좋습니다. 이렇게 하면 정보의 "개요" 보기를 생성하여 ID가 ​​하나만 있는 캠프장과 여러 개의 ID가 있는 캠프장을 더 잘 확인할 수 있습니다. 다음과 같이 보일 것입니다 :

또한 @pnuts에 따르면 Excel은 드롭다운(예: 필터) 목록의 항목이 10,000개로 제한되지만 테이블 구성원에 대한 문서화된 제한은 없습니다. 10,000개의 행이 있는 테이블이 있습니다.

편집2:반복되는 값만 쉽게 찾고 싶다면 피벗 테이블이 가장 좋습니다.

1. 초기 데이터 테이블을 가져와서 "카운터" 행을 추가하세요. =IF([@Name]=D1,F1+1,1)이는 동일한 이름을 가진 행 수를 반환합니다.
2. Insert>Tables>Pivot Table데이터에서 피벗 테이블을 만듭니다 .
3. 피벗 테이블의 형식을 다음과 같이 지정하세요.
   • 행 레이블 = Name, Code, Counter, 모든 값에 대한 소계 및 총합계를 끕니다.
   • 필터 카운터 = 지우기 1, 이는 여러 값을 가진 이름만 표시합니다.
   • 필터 이름 = 관심 있는 특정 캠프장 이름.

Answer 2

입력 셀이 A1이고 출력 셀이 이름이 A3이고 출력 범위가 B3:B..라고 가정해 보겠습니다(B 범위의 끝은 표시할 것으로 예상하는 최대 결과 수까지 내려갑니다). .

A3의 경우 수식을 입력합니다 =A1.

B3의 경우 수식을 입력하세요.

  =IFERROR(INDEX($N$1:$N$99,SMALL(IF($J$1:$J$99=$A$3,ROW($N$1:$N$99)-ROW($N$1)+1),ROWS($N$1:$N1))),"")

이는 배열 수식이므로 Control- Shift- Enter키 조합으로 입력해야 합니다. 그런 다음 출력 범위의 맨 아래까지 복사할 수 있습니다.

수식은 최대 99개 행이 있는 입력 목록에 대해 하드 코딩됩니다. 필요한 길이로 변경할 수 있습니다. 전체 열(J:J 및 N:N)을 참조하는 것이 가능하지만 피하고 싶은 성능 저하가 있을 수 있습니다.

수식이 작동하는 방식

내부에서 바깥쪽으로 작업하면서 수식은 먼저 조회를 수행할 이름(셀 A3)을 전체 이름 목록(J1:J99 범위의 최대 99개 이름)과 비교합니다. 해당 비교는 아래 표시된 함수 설명의 6행에 표시됩니다.

해당 비교의 결과는 일치 항목에 대한 True 값과 불일치 항목에 대한 False 값(예: {False, False, False, False, True, True, False, ... etc.})이 있는 배열입니다.

그런 다음 이름 목록의 "줄 번호"로 간주할 수 있는 배열({1, 2, 3, 4, 5, 6, ... 99})을 사용하여 해당 배열을 비교합니다. 이 비교는 수식 차트의 6~8행에 있는 IF 문을 통해 수행됩니다.

비교는 요소별로 이루어집니다. 이름 비교 배열의 요소가 True이면 IF는 해당 줄 번호를 반환합니다. 요소가 False이면 IF는 FALSE를 반환합니다. 위의 두 예제 배열을 사용하면 IF 문의 결과는 {False, False, False, False, 4, 5, False, ...}가 됩니다.

계속해서 SMALL 함수(함수 개요의 8행에서 시작)를 사용하여 IF에서 이 새 배열의 k번째로 작은 요소를 가져옵니다. 이 경우 "k"는 ROWS($N$1:$N1) 표현식으로 제공됩니다. 이 표현식은 전체 수식을 1행에서 99행(ROWS($N$1)까지 복사할 때 1부터 99까지만 계산됩니다. :$N1) = 1, ROWS($N$1:$N2) = 2 등).

따라서 SMALL은 먼저 IF에서 생성된 배열의 가장 작은 요소를 찾습니다.묵살False인 요소. 즉, 비교되는 이름이 이름 조회 목록의 이름과 일치하는 첫 번째 줄 번호를 반환합니다. 이 예에서는 아래 표의 6번째 열에 표시된 대로 숫자 4입니다.

마무리 단계에서는 조회 값에 대해 INDEX를 사용하여 계산된 줄 번호에 해당하는 요소를 가져옵니다. 이 경우 질문에 대한 색상 목록 예제의 4번째 항목은 "노란색"입니다. (IFERROR는 수식이 일치하는 항목을 찾지 못한 경우 공백이 표시되도록 합니다.)

이것이 전체 공식의 첫 번째 사본에서 나온 결과였습니다. 다음 셀로 복사할 때 계산에서 변경되는 유일한 것은 SMALL 함수의 "k" 값이며 2로 증가합니다. 그리고 발견된 두 번째로 작은 줄 번호는 5입니다. 값은 "흰색"입니다.