중복 값 조회 – Excel 및 Google 스프레드시트
이 튜토리얼에서는 Excel과 Google 스프레드시트에서 VLOOKUP을 사용하여 중복된 값을 조회하는 방법을 보여드립니다. 사용 중인 Excel 버전이 XLOOKUP을 지원하는 경우, 훨씬 더 쉽게 해결할 수 있으므로 대신 XLOOKUP을 사용하는 것이 좋습니다.
VLOOKUP N번째 일치
VLOOKUP 함수는 항상 첫 번째 일치 항목을 반환합니다. 중복된 값(또는 n번째 일치)을 반환하려면 다음이 필요합니다.
- 모든 중복 값을 구분하기 위한 새로운 고유 식별자
- 테이블 배열의 새 조회 열(첫 번째 열) 역할을 할 고유 ID 목록이 포함된 도우미 열
- 고유 ID의 형식과 일치하도록 수정된 조회 값
최종 솔루션은 다음과 같습니다.
=VLOOKUP(F3&"-"&G3,B3:D7,3,FALSE)
이제 솔루션을 살펴보겠습니다.
COUNTIF를 사용한 고유 ID
이 데이터 집합에서 Student ID = 2021-A에 대한 두 번째 점수를 조회하려고 한다고 가정해 보겠습니다.
먼저, 원래 학생 ID(C열)를 COUNTIF 함수로 조인하여 B열에 고유 ID를 만듭니다.
=C3&"-"&COUNTIF($C$3:C3,C3)
COUNTIF 함수는 각 학생 ID의 수를 계산합니다. COUNTIF 함수에서 한 셀은 절대 참조로 다른 셀은 상대 참조로 하여 중복된 학생 ID의 각 인스턴스를 고유 번호로 식별합니다.
=COUNTIF($C$3:C3,C3)
그런 다음 이전 수식에서와 같이 & 연산자를 사용하여 모든 것을 연결하기만 하면 됩니다.
N번째 일치 항목 VLOOKUP
이제 조회 값이 새 고유 ID의 형식과 일치하는 이 수식을 사용하여 두 번째 일치 항목을 조회할 수 있습니다.
=VLOOKUP(F3&"-"&G3,B3:D7,3,FALSE)
모든 일치 항목 VLOOKUP
대신 모든 일치 항목을 조회하려면 어떻게 해야 할까요?
찾고자 하는 각 일치 항목을 수동으로 나열할 수 있습니다
이 방법도 괜찮은 해결책이 될 수 있지만 얼마나 많은 일치 항목이 존재하는지 모른다면 어떻게 해야 할까요? 이 수식은 “2021-A”에 대한 모든 일치 항목을 출력합니다.
=IFNA(VLOOKUP($F$2&"-"&ROW(1:1),$B$3:$D$7,3,FALSE),"")
위의 공식을 살펴봅시다.
ROW 함수
ROW 함수는 셀 또는 범위의 행 번호를 반환하는 데 사용합니다. 1부터 연속적으로 카운트되는 숫자의 목록을 생성하는 수식을 만들려면 ROW 함수에 전체 행 참조를 입력한 후 열에 복사하여 붙여넣으면 됩니다.
=ROW(1:1)
새 조회 값
다음으로, ROW 함수의 결과를 조회 값(G열 헤더)과 결합하여 고유한 조회 값을 만듭니다.
=$G$2&"-"&F3
참고: 원래 조회 값이 절대 참조(예: $G$2)인지 확인하십시오.
VLOOKUP 함수
다음으로 VLOOKUP을 수행합니다.
=VLOOKUP(G3,$B$3:$D$7,3,FALSE)
IFNA 함수
존재하지 않는 값에 대해 #N/A 오류가 발생하는 것을 확인할 수 있습니다. 다음으로 IFNA 함수를 사용하여 대신 공백으로 출력해 보겠습니다.
=IFNA(H3,"")
모든 함수를 결합하면 원래 수식이 됩니다.
=IFNA(VLOOKUP($F$2&"-"&ROW(1:1),$B$3:$D$7,3,FALSE),"")위의 공식보다 더 편리하고 간단한 해결책이 있는데, 바로 새로운 FILTER 함수를 사용하는 것입니다. FILTER 함수를 사용하면 데이터를 필터링하고 중복 항목을 한 번에 추출할 수 있지만, 이 기능은 현재 Microsoft 365 버전에서만 사용할 수 있습니다. Microsoft 365를 사용 중이라면 대신 XLOOKUP 함수를 사용해야 합니다.
VLOOKUP 중복 조회 값
이제 중복된 조회 값을 조회 열에서 해당 인스턴스에 일치시키려면 어떻게 해야 할까요?
이 경우 이전 섹션과 동일한 방법을 조회 값에 적용합니다.
=VLOOKUP(F3&"-"&COUNTIF($F$3:F3,F3),$B$3:$D$7,3,FALSE)
구글 스프레드시트에서 n번째 일치 항목 찾기
위에서 설명한 모든 VLOOKUP 수식은 Google 스프레드시트에서 동일한 방식으로 작동합니다.
=VLOOKUP(F3&"-"&G3,B3:D7,3,FALSE)
