오늘 엑셀 내용으로는 Vlookup 함수를 사용해서 결과값을 가져올 때 찾을 조건에 대한 결과값이 여러 개 있을 때 중복으로 표기 되는 값 없이 표기 할 수 있는 수식 작성 방법입니다.
오늘 내용도 많은 분들께 유용하게 사용할 수 있는 내용이 될 수 있기를 바라겠습니다.
■ Vlookup 함수 조건에 일치 하는 값이 여러개일 때
중복된 결과값 없이 순서대로 표기 하는 수식.
왼쪽 표에서 A종류의 수량을 결과값으로 가져 오기 위해서 Vlookup 함수로 F4셀에 수식을 작성하게 되면 =VLOOKUP($F$3,$B$3:$C$11,2,FALSE) 으로 수식을 작성 할 수 있고, 결과값은 8,000으로 표기 됩니다.
다만 위 수식으로 A종류의 모든 결과값을 순서대로 표기 하지는 못하고 A종류의 두번째 결과값, 세번째, 네번째 결과값도 모두 동일하게 8,000으로 표기 됩니다.
또한 A종류는 3개의 결과값만 존재 하게 되는데 수식을 계속해서 작성하더라도 결과값은 8,000이 나오게 되는 것이죠.
찾을 값의 조건이 B열의 종류가 A라면 결과값은 ① 8,000, ② 3,000, ③ 7,000 이렇게 3개의 값이 순서대로 작성되야 합니다.
그렇다면 오른쪽 표에 찾을값의 첫번째, 두번째, 세번째.... 값을 표기 하기 위해서 수식을 어떻게 작성해야 할까요?
방법은 참조 할 수 있는 보조 열을 사용해서 수식을 작성 할 수 있는데 보조열을 수식으로 작성 하는 수식과 Vlookup 함수 수식을 중복값 없이 순서대로 표기 하는 수식을 작성해 보도록 하겠습니다.
보조열을 사용해서 Vlookup 함수로 찾을 조건인 A의 결과값을 순서대로 입력할 수 있습니다.
우선 보조열인 A열에 각각의 종류의 순서를 참조 할 수 있도록 A1, B1...A2, B2...으로 표기 해 주기 위해서 수식을 작성하면 다음과 같이 작성 할 수 있습니다.
=$B3&COUNTIFS($B$3:B3,B3)
Countifs함수를 응용해서 개수와 문자를 연결 시켜 줄 수 있도록 작성한 수식 입니다. 수식끼리 연결해서 숫자 또는 문자를 연속해서 표기 하기 위해서는 "&" 기호를 연결 고리로 사용해 주시면 됩니다.
보조열에 작성한 수식의 결과값을 참조해서 Vlookup함수로 오른쪽 표에 찾을 값의 조건이 A일때 결과값을 순서대로 표기 하기 위한 수식은 다음과 같이 작성 할 수 있습니다.
=VLOOKUP($F$3&$E4,$A$3:$C$11,3,FALSE)
찾을 조건은 F3셀인 A에다가 첮번째 값인 1이 작성되어 있는 E4셀을 연결해 주어 A1값을 찾을 수 있도록 Vlookup함수를 응용해서 수식을 작성해 주었다고 보시면 되겠습니다.
즉, A1의 수량, A2의 수량, A3의 수량의 결과값을 구하는 수식으로 변경 된 것입니다.
그런데, 여기서 표를 보기에 지저분한 수식 오류값이 표기 되는데 이것을 보기 좋게 표기 하기 위해서는 Iferror함수를 사용해 주시면 보기 깔끔하게 결과값을 표기할 수 있겠습니다. Iferror함수를 추가로 사용해서 수식을 작성해 주면 다음과 같이 작성 할 수 있겠습니다.
엑셀tip - vlookup함수를 이용한 중복값(데이터)찾기
엑셀(excel 2007,2010)에서 vlookup함수를 이용해서 중복된 데이터값을 가지고 원하는 값을 찾아낼 수있습니다. 다른 말로 중복된 데이터값을 찾기를 통해서 표시할 수 있습니다. 이때 사용하는 함수가 vlookup함수인데 함수 중 가장 많이 사용하는 함수 중 하나입니다.
■ 엑셀 데이터에서 중복된 값
하단의 좌측은 원본데이터이고 우측을 참조할 데이터입니다. 즉, 좌측에 있는 이름은 우측의 이름과 중복된 값이 있습니다. 원본데이터 값은 필기시험이고 참조할 데이터 값은 필기시험입니다. 여기서 하고자 하는 작업은 원본데이터의 [실기시험]에 해당하는 값은 참조할 데이터의 [실기시험값을 끌어다가 표시를 하는 작업입니다. 이러한 작업을 엑셀에서 할 수 없다면 중복된 값이나 항목을 하나하나 찾아서 기록해야 합니다.
■ vlookup함수로 중복값(데이터) 찾기
[함수삽입(fx)클릭 - 함수마법사 창 - 함수검색(vlookup) - 확인]
● lookup_value
lookup_value는 두개의 데이터 중에서 공통된 값을 의미하는데 원본데이터의 공통값입니다
● Table_array
Table_array는 참조하고자 하는 데이터의 전체범위를 의미합니다. 즉, 참조할 데이터를 말합니다. 해당 위의 셀을 마우스로 드래그 하여 범위를 지정을 한 후 그 값을 F4를 클릭하여 절대값으로 바꿉니다.
● Col_index_num
Col_index_num는 참조할 데이터에서 값을 찾아서 원본데이터에 표시하고자 하는 값(실기시험값)의 "열의 순서"입니다. 즉, [이름]이 1열이고 [실기시험]이 2열이기 때문에 2를 써 넣습니다. 그리고 하단의 ● Rsnge_ lookup는 0을 써 넣었는데 이 말의 의미는 실기시험값을 그대로 가져오라는 의미입니다
■ 결과값 표시하기
하단과 같이 박유천의 실기시험값이 우측의 [참조할 데이터]에서 40점을 찾아서 표시하였습니다. 해당 셀[D3]를 클릭하여 하단으로 드래그 하면 나머지 값들도 찾아와서 표시가 됩니다.
위와 같이 VLOOKUP함수를 이용할 경우에는 반드시 [원본데이터]와 중복된 값이 [참조할 데이터]에 있어야 합니다. 위에서는 이름이 중복값이기 때문에 이를 바탕으로 해서 실기시험값은 VLOOKUP으로 찾아서 추출해서 표시를 했습니다.
공유하기
게시글 관리
구독하기가치있는 일상을 찾아저작자표시
- 카카오스토리
- 트위터
- 페이스북
티스토리툴바
가치있는 일상을 찾아구독하기