오늘은 VLOOKUP의 한계라고도 할 수 있는 중복값 찾기 문제 관련하여, 어떻게 하면 VLOOKUP을 가지고 중복값을 찾아낼 수 있는지 알아보도록 하겠습니다. 실무에서 자주 쓰이는 COUNTIF로 응용하는 방법인만큼 이번 기회에 알아두면 좀 더 쉽게, 좀 더 오래 기억 속에 남을 겁니다.
① VLOOKUP 함수는 중복값을 못 찾는다?
| A | B | |
| 1 | 이름 | 나이 |
| 2 | 김샛별 | 19 |
| 3 | 오하늘 | 22 |
| 4 | 이태양 | 29 |
| 5 | 김샛별 | 36 |
이전 시간에도 언급했지만 VLOOKUP함수는 방대한 데이터 자료에서 열 기준으로 필요 값을 뽑아내는 함수라고 했습니다.
VLOOKUP 공식은 =VLOOKUP(찾을값, 데이터 전체범위, 열번호, 0or1) 인데요.
예를 들어 위 표를 예시로 김샛별의 나이를 알고 싶다면,
= VLOOKUP("김샛별", A1:B5, 2, 0)으로 입력하면 됩니다.
· 찾을값은 김샛별 (김샛별은 문자이니 김샛별 좌우로 따옴표를 해줌 - 엑셀에서 문자는 따옴표로 표시함)
· 데이터 전체범위는 A1:B5
· 김샛별 기준 추출할 값은 나이인데 나이는 전체 범위에서 2열에 있으므로 2
· 일치하는 값을 찾는 거니 마지막은 0 (보통 실무에선 일치값을 찾으므로 마지막은 그냥 0을 쓴다고 외워도 됨)
자 그렇다면 값은 무엇이 나오냐? 19가 나올 겁니다.
김샛별은 표에 두 명이 있는데 19만 나오는 이유는 VLOOKUP은 처음으로 찾았다! 하는 값만 표시해 주기 때문입니다. 36살 김샛별보다 19살 김샛별이 위에 있으니 컴퓨터는 19 김샛별을 먼저 찾겠죠? 그러니 값은 19만 나오는 겁니다.
동명이인, 동일 상품명이 있는 데이터에서 필요한 정보를 뽑아내야 할 경우에, VLOOKUP을 사용한다면 정확한 값을 뽑아낼 수 없을 겁니다. 그럼 이럴 땐 어떻게 써야 할까요?
② VLOOKUP 함수로 중복값 찾아내자! COUNTIF 이용하세요!
| A | B | |
| 1 | 이름 | 나이 |
| 2 | 김샛별 | 19 |
| 3 | 오하늘 | 22 |
| 4 | 이태양 | 29 |
| 5 | 김샛별 | 36 |
중복값이 있을 경우 먼저 COUNTIF 함수를 사용해 줍니다.
COUNTIF는 조건에 맞는 셀의 개수를 세어주는 함수로 COUNTIF(지정범위,조건)으로 적습니다.
A2의 김샛별은 =COUNTIF(A2:A2,A2) 즉 A2:A2 범위에서 조건 A2인 김샛별을 충족하는 셀의 개수는? 1개입니다.
A3의 오하늘은 =COUNTIF(A2:A3,A3) 즉 A2:A3 범위에서 조건 A3인 오하늘을 충족하는 셀의 개수는? 1개입니다.
A4의 이태양은 =COUNTIF(A2:A4,A4) 즉 A2:A4 범위에서 조건 A4인 이태양을 충족하는 셀의 개수는? 1개입니다.
A5의 김샛별은 =COUNTIF(A2:A5,A5) 즉 A2:A5 범위에서 조건 A5인 김샛별을 충족하는 셀의 개수는? 2개입니다.
A2셀은 고정이고 그 아래로 지정범위가 점점 커지는 거니 A2는 고정 즉, 절대참조($)로 설정해 주세요! F4 누름 됨!
정리하면 =COUNTIF($A$2:A2,A2) 쓰고 아래로 쭉 당기면 함수가 자동 설정됨.
| A | B | C | |
| 1 | COUNTIF함수 이용 | 이름 | 나이 |
| 2 | =COUNTIF($B$2:B2,B2) =1 여기서 이름과 COUNTIF함수 연결 (참고로 연결은 &로 이용) =B2&COUNTIF($B$2:B2,B2) =김샛별1 |
김샛별 | 19 |
| 3 | =COUNTIF($B$2:B3,B3) =1 여기서 이름과 COUNTIF함수 연결 =B3&COUNTIF($B$2:B3,B3) =오하늘1 |
오하늘 | 22 |
| 4 | =COUNTIF($B$2:B4,B4) =1 여기서 이름과 COUNTIF함수 연결 =B4&COUNTIF($B$2:B4,B4) =이태양1 |
이태양 | 29 |
| 5 | =COUNTIF($B$2:B5,B5) =2 여기서 이름과 COUNTIF함수 연결 =B5&COUNTIF($B$2:B5,B5) =김샛별2 |
김새별 | 36 |
이렇게 생겨난 새로운 이름 김샛별1, 오하늘1, 이태양1, 김샛별2를 가지고 중복값을 찾아봅시다.
=VLOOKUP(찾을값, 데이터 전체범위, 열번호, 0 or 1)
· 찾을값 : VLOOKUP의 찾을 값은 항상 왼쪽에 위치해야 한다고 했죠? 그러므로 가장 왼쪽 A열에서 찾을값을 설정해야 합니다. A열은 보시다시피 COUNTIF로 이름 옆에 숫자가 붙어있게 해 놓은 상태입니다.
| E | F | |
| 6 | 김샛별 | |
| 7 | 1 | |
| 8 | 2 | |
| 9 | 3 |
그러므로 따로 위와 같이 표를 만들어 줍니다.
만약 =F6&E7을 하면 김샛별1이라는 값이, =F6&F8을 하면 김샛별2가 나올 겁니다. 이걸 응용하면 되겠죠?
여기서 F6값은 고정이고 E7, E8, E9 만 값만 변하는데 여기서 E도 고정되어 있으니 $F$6&$E7로 적어줍시다.
· 데이터 전체범위 : 데이터 전체범위는 A1:C5 입니다.
· 열번호 : 추출값은 나이이니 나이가 있는 열 3열, 즉 3을 적어줍니다.
· 0 or 1 : 일치하는 값을 찾는 거니 마지막은 0을 적어줍니다. 그냥 마지막은 0 적는 걸로 외우기로 했죠?
정리하면 =VLOOKUP($F$6&E7, A1:C5, 3, 0)
| E | F | |
| 6 | 김샛별 | |
| 7 | 1 | =VLOOKUP($F$6&$E7, A1:C5, 3, 0) = 19 |
| 8 | 2 | =VLOOKUP($F$6&$E8, A1:C5, 3, 0) = 36 |
| 9 | 3 | =VLOOKUP($F$6&$E9, A1:C5, 3, 0) = #N/A |
F9값이 #N/A가 뜨는 이유는 VLOOKUP함수 안에 찾을값 $F$6&$E9인 김샛별3이라는건 A1:C5 범위에서 존재하지 않기 때문에 뜨는 겁니다.
이런 에러표시가 나는 게 싫다면 에러 하면 떠오르는 IFERROR를 이용해 주면 됩니다.
=IFERROR(값, 그 값이 에러일 때 어떤 값으로 표시하겠음)
=IFERROR(VLOOKUP($F$6&$E7, A1:C5, 3, 0) , "존재하지 않음")
라고 표현해 주면 에러가 날 시 존재하지 않음으로 표시됩니다.
③ 정리!
* 기본적으로 알아야 할 부분
VLOOKUP은 =VLOOKUP(찾을값, 데이터 전체범위, 열번호, 0 or 1) 이다.
여기서 찾을값은 데이터 전체 범위에서 맨 왼쪽 열에 위치해야 한다.
그렇지 않을 경우 VLOOKUP함수는 사용하지 못한다. (이건 룰이라고 생각하면 됨)
* VLOOKUP의 한계
VLOOKUP은 중복되는 값이 있더라도 첫 번째로 찾은 값만 표시해주고 말기 때문에 동명이인, 동일 상품명 등의 데이터 파일에서 VLOOKUP을 사용하면 정확한 값을 도출해내지 못한다.
* VLOOKUP 함수 이용해 중복값 찾기
VLOOKUP 함수를 이용해 중복값을 찾고 싶다면 새로운 열을 생성한 후 COUNTIF 함수를 이용하자!
COUNTIF는 조건에 충족하는 셀의 개수를 세워주는 함수이다. =COUNTIF(지정범위,조건)
예시에서는 이름 범위를 점차 넓혀가며 그 이름과 같은 셀이 몇 개인지 세어주는 식으로 설정했다.
COUNTIF 작성까지 완료되었다면 =VLOOKUP(찾을값, 데이터 전체범위, 열번호, 0 or 1)로 돌아가 값을 넣어주자!
데이터 전체범위는 A1:C5이고 이 범위에서 가장 왼쪽에 위치하는 열이 찾을 값이다. 왼쪽에 위치한 A열은 이름 옆에 숫자가 붙어 있으므로 아래와 같은 표를 만들어서 작업해 준다!
김샛별 F6과 숫자가 적혀있는 E7, E8, E9와 연결(엑셀에서 연결은 &)해주면 COUNTIF처럼 이름 옆에 숫자가 붙음.
F6은 그대로 고정되어야 하니 절대참조 $F$6
E7, E8, E9에서 E는 고정이고 행만 바뀌니 E만 절대참조 $E7
$F$6&$E7 = 김샛별1
$F$6&$E8 = 김샛별2
$F$6&$E9 = 김샛별3
이 나올 테니 VLOOKUP의 찾을값으로 이 아이들을 넣어줘서 작성한다.
김샛별3은 예시에서 존재하지 않으므로 에러가 뜨는데 에러 뜨는 게 보기 싫다면 IFERROR로 작업해 주자!
=IFERROR(값, 그 값이 에러일 때 어떤 값으로 표시하겠음)
=IFERROR(VLOOKUP($F$6&$E7, A1:C5, 3, 0) , "존재하지 않음")
오늘은 VLOOKUP을 이용해서 중복값을 찾는 방법에 대해 알아봤습니다. VLOOKUP뿐 아니라 COUNTIF, IFEEROR 함수 역시 실무에서 자주 쓰이기 때문에 이번 기회에 두루 알아두면 일하는데 훨씬 더 도움이 될 거입니다. 다음 포스팅도 유익한 정보로 찾아뵙겠습니다. 끝까지 읽어주셔서 감사드립니다.
[ 참고하면 좋은 글 ]
엑셀실무 VLOOKUP INDEX MATCH 사용법
오늘은 엑셀실무로 알아두면 좋은 함수 중 하나인 VLOOKUP 함수 사용법에 대해 알아보겠습니다. 또한 VLOOKUP함수처럼 사용할 수 있는 MATCH함수와 INDEX함수까지 같이 살펴볼게요! ① VLOOKUP 함수 실무
subari90.tistory.com
컴활 2급 실기 엑셀 계산작업 함수 문제은행 핵심 요약 1
컴활 2급 실기의 경우 기본작업 20점, 계산작업 40점, 분석작업 20점, 매크로 및 차트 작업 20점으로 나뉩니다. 직접 공부해보니 실기도 필기처럼 결이 비슷한 문제들이 반복적으로 나오는 문제은
subari90.tistory.com
컴활 2급 실기 엑셀 계산작업 함수 문제은행 핵심 요약 2
컴활 2급 실기 계산작업 함수 편 챕터1에 이어 오늘은 챕터2를 정리해보겠습니다. 이전에 말했듯이 실기도 필기처럼 문제를 풀다 보면 결이 비슷한 문제들이 반복적으로 나오는 문제은행식인데
subari90.tistory.com
'엑셀 정보' 카테고리의 다른 글
| 직장인 엑셀 INDEX MATCH 함수 사용법 (1) | 2023.03.10 |
|---|---|
| VLOOKUP함수 다중조건 찾을값 여러 개일 때 응용법 (0) | 2023.03.06 |
| 엑셀실무 VLOOKUP INDEX MATCH 사용법 (1) | 2023.02.02 |
| 컴활 2급 합격방법 책 없이 2주 독학으로 자격증 따기! (0) | 2022.11.20 |
| 컴활 2급 실기 엑셀 계산작업 함수 문제은행 핵심 요약 2 (0) | 2022.11.18 |
댓글