엑셀에서 조건에 맞는 값을 불러오는 방법은 INDEX & MATCH를 사용하거나 VLOOKUP 함수를 사용하면 쉽게 불러올 수 있습니다. 하지만 조건에 맞는 데이터가 여러 개일 때 해당하는 모든 데이터를 불러오기 쉽지 않은데요. 오늘은 조건에 맞는 데이터 나열하기에 대해서 알아보겠습니다.
1. 조건에 맞는 데이터 한 셀에 나열하기
아래 그림 같이 구분 항목에 온라인, 오프라인, 전자제품이라는 고유값이 있을 때 항목에 해당하는 여러 브랜드명 하나에 셀에 합쳐서 표를 작성해야 할 때가 있습니다. 이렇게 고유항목에 관련된 모든 데이터 내용을 한 셀에 나열하기 위해 사용하는 함수는 TEXTJOIN입니다. TEXTJOIN 함수는 엑셀 2019 또는 오피스 365 이상 버전에서 지원합니다. 아래 예제를 다운받아 내용을 확인해보시기 바랍니다.
TEXTJOIN = (구분 기호 , 빈칸 처리 방법, 텍스트1, [텍스트2]….)
– 구분 기호 : 텍스트와 텍스트를 나눌 경계 설정
– 빈칸 처리 방법 : TRUE로 설정하면 빈칸은 무시하고 텍스트를 결합
– 텍스트 1 : 결합할 텍스트 또는 범위를 설정 (생략 불가)
– 텍스트 2 : 결합할 텍스트 또는 범위 (생략 가능)
▼ D2 셀에 “온라인”에 해당하는 B 열의 데이터를 나열하기 위해 E2에 사용된 수식은 다음과 같습니다.
= TEXTJOIN(구분 기호, TRUE, IF(조건 범위=조건, 데이터 범위, “”))
E2 = TEXTJOIN(“/”, TRUE, IF($A$2:$A$18=D2, $B$2:$B$18, “”))
이 수식은 배열 수식이므로, 엑셀에서 Ctrl+Shift+Enter를 눌러 배열 수식으로 입력해야 합니다. 엑셀 365와 엑셀 2019에서는 일반 Enter로도 작동합니다. 함수를 실행하면 조건에 해당하는 “구분” 항목이 일치하는 브랜드명을 한 셀에 표현할 수 있습니다. D 열의 “고유 항목”은 수동으로 입력하거나 UNIQUE(A2:A18)을 사용하여 추출할 수 있습니다. 텍스트를 나누는 구분 기호는 용도에 맞게 변경하여 사용하시기 바랍니다.
2. 조건에 맞는 데이터 행으로 나열하기
▼ 조건에 맞는 데이터를 여러 행에 나열하는 방법을 알아보겠습니다. 아래 표와 같이 고유 항목과 일치하는 데이터를 행에 모두 입력하도록 수식을 작성해보겠습니다. 사용하는 수식은 아래와 같습니다.
G2 셀에 입력된 조건값이 입력되면, INDEX 함수를 통해 조건에 맞는 데이터 범위에서 조건과 일치하는 모든 데이터를 행으로 나열합니다. ROW(1:1) 의 용도는 배열 수식에서 인덱스를 제공하여 각 행의 번호를 나타냅니다. 이를 통해 SMALL 함수가 올바른 순서로 조건에 맞는 값들을 반환할 수 있게 합니다. H2 셀에 사용한 수식은 아래와 같습니다.
= IFERROR(INDEX(데이터 범위, SMALL(IF(조건 범위=조건, ROW(조건 범위)-MIN(ROW(조건 범위))+1, “”), ROW(1:1))), “”)
H2 = IFERROR(INDEX($B$2:$B$18, SMALL(IF($A$2:$A$18=$G$2, ROW($A$2:$A$18)-MIN(ROW($A$2:$A$18))+1, “”), ROW(1:1))), “”)
수식 살펴보기
– ROW($A$2:$A$18)는 조건 범위의 행 번호를 추출하여 {2,3,4,5,6….. 18} 행 번호를 생성합니다.
– MIN(ROW($A$2:$A$18))은 {2,3,4,5,6….. 18}에서 가장 작은 2를 추출합니다.
– ROW($A$2:$A$18)-MIN(ROW($A$2:$A$18))+1은 {1,2,3,4,5,6…..18}의 행 번호를 생성합니다.
– IF($A$2:$A$18=$G$2, ROW($A$2:$A$18)-MIN(ROW($A$2:$A$18))+1, “”)는 조건과 일치하는 행 번호 {1,2,3,4,5}가 생성됩니다.
– INDEX(데이터 범위, SMALL({1,2,3,4,5}, ROW(1:1))을 통해 INDEX 범위에서 첫 번째 항목에 해당하는 “11번가”라는 브랜드명이 추출됩니다. 수식을 자동 채우기 핸들을 통해 드래그하면 조건 G2와 일치하는 모든 브랜드 추출할 수 있습니다.
▼ G2 셀에 입력된 조건을 변경하면 조건과 일치하는 모 데이터를 행 방향으로 추출할 수 있습니다.
