특정 표를 이용하여 보다 쉽게 업무처리하는 방법은?
회사에서 일을 하다보면 어떤 항목에 대한 값을 특정 표의 값을 이용하여 매칭시켜야하는 경우가 많이 있습니다. 이때 엑셀을 많이 사용하는데요. 엑셀의 어떤 기능들을 이용해야만 자신이 원하는 값을 매칭시킬수 있는지 배워보는 시간을 갖도록 하겠습니다. 많이 사용하는 엑셀 함수가 있는데 VLOOKUP 과 HLOOKUP 입니다. 여기서는 VLOOKUP 에 대해서만 실습해보도록 하겠습니다. 나중에 HLOOKUP 도 만들어보도록 할께요.
함수에 대해 알아보기
VLOOKUP :
VLOOKUP에서 V는 vertical(세로)을 의미합니다. 비교값이 찾으려는 데이터의 왼쪽 열에 있으면 HLOOKUP 대신 VLOOKUP을 사용합니다.
구문 :
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
lookup_value
표 배열 (배열: 여러 가지 결과를 만들거나 행과 열로 구성되는 인수 그룹에 대해 연산이 이루어지는 한 개의 수식을 작성하기 위해 사용됩니다. 배열 범위는 공통 수식을 공유하며 배열 상수는 한 개의 인수로 사용되는 상수 그룹입니다.)의 첫 번째 열에서 찾을 값입니다. lookup_value는 값 또는 참조일 수 있습니다. lookup_value가 table_array의 첫 번째 열에 있는 최소값보다 작으면 #N/A 오류 값이 반환됩니다.
table_array
2개 이상의 데이터 열입니다. 범위에 대한 참조 또는 범위 이름을 사용합니다. table_array의 첫 번째 열의 값은 lookup_value로 검색된 값입니다. 이러한 값은 텍스트, 숫자 또는 논리값이 될 수 있습니다. 대/소문자는 구분하지 않습니다.
col_index_num
비교값과 같은 행에 있는 값을 표시할 table_array의 열 번호입니다. col_index_num이 1이면 table_array의 첫 번째 열에서 값을 반환하고, col_index_num이 2이면 table_array의 두 번째 열에서 값을 반환합니다. col_index_num의 조건에 따라 다음과 같은 결과가 나타납니다.
- 1보다 작으면 #VALUE! 오류 값이 반환됩니다.
- table_array의 열 수보다 크면 #REF! 오류 값이 반환됩니다.
range_lookup
정확하게 일치하는 값을 찾을 것인지, 근사값을 찾을 것인지를 결정하는 논리값입니다.
- TRUE이거나 생략되면 정확한 값이나 근사값을 반환합니다. 정확하게 일치하는 값이 없으면 lookup_value보다 작으면서 그 다음으로 가장 큰 값을 반환합니다.
table_array의 첫 번째 열 값은 오름차순으로 정렬해야 합니다. 그렇지 않으면 VLOOKUP 함수를 실행하여 올바른 결과를 얻을 수 없습니다.
- FALSE이면 정확하게 일치하는 값만 찾습니다. 이 경우 table_array의 첫째 열에 있는 값을 정렬할 필요가 없습니다. table_array의 첫째 열에 lookup_value와 일치하는 값이 두 개 이상 있으면 먼저 발견된 값이 사용됩니다. 정확하게 일치하는 값이 없으면 #N/A 오류 값이 반환됩니다.
VLOOKUP 을 이용한 값 매칭 실습하기
시험성적에 대한 등급표를 만든다고 해보자.
점수별 등급에 대한 기준 표가 있는 시트를 이용하여 특정 과목의 점수에 대한 등급이 자동으로 표시되도록 하려고 한다.
|
특정 과목의 점수에 대한 등급 표시
( sheet1 ) | |
|
점수 별 등급에 대한 기준 표
( sheet2 ) | |
점수별 등급에 대한 기준표가 위에서 아래로 세로방향으로 정리가 된 것이다 이때는 VLOOKUP 을 활요하여 값을 찾아볼수가 있다.
영어점수 옆 등급표 란을 클릭 후 " =vlookup " 을 입력하면 위와 같은 예시가 나온다.
|
|
|
C2 값에 들어갈 내용이다. |
|
|
|
|
VLOOKUP 을 통해서 찾은 등급니다.
Sheet2 의 기준표에 90 점은 2등급으로 되어 있다. |
|
위에 적은 산식을 복사해서 수학, 과학, 사회, 제2회국어 값에 넣었더니 아래 그림 어떤 값은 잘 나오고 어떤 값들은 에러가 발생하였다. | |
|
함수 내용이 나온 부분을 보자.
VLOOKUP(B2,Sheet2!A1:B7,2,FALSE)
이렇게 되어 있는것이 보인다. |
|
에러가 발생한 부분을 보면
VLOOKUP(B4,Sheet2!A3:B9,2,FALSE)
시트2번째 데이터값이 있는 부분 시작과 끝 위치가 바뀌어 있는것이 보인다.
해당 내용을 수정해 줘야만 제대로된 비교표를 사용할수가 있다.
에너난 부분만 고치면 안된다. 모든 비교구문이 들어가는 내용을 같이 바꿔 줘야한다. 사실 하나만 바꾸면 다른 부분은 쉽게 바꿀수가 있다.
맨 처음 함수를 사용했던 C2 의 값을
VLOOKUP(B2,Sheet2!A1:B7,2,FALSE) 에서
VLOOKUP(B2,Sheet2!A$1:B$7,2,FALSE) 이렇게 바꿔보다. $ 표시는 고정된다는 의미이다. 우리가 비교하는 값이 위에서 아래로 바뀌면서 사용하니까 숫자 앞에만 $를 붙여줬지만 만약 좌우로도 움직인다고 하면
VLOOKUP(B2,Sheet2!$A$1:$B$7,2,FALSE) 이렇게 비교되는 값의 기준이 변화가 없도록 해야 합니다.
여기서는 간단하게 VLOOKUP(B2,Sheet2!A$1:B$7,2,FALSE) 를 사용해서 테스트 해보겠습니다.
다 수정을 하였으면 위 이미지의 위치를 더블클릭을 하시면 아래까지 쭈욱 함수가 바뀌면서 값도 바뀌게 됩니다.
처음 있었던 함수와 지금 쓰여진 함수가 다르죠?
VLOOKUP(B4,Sheet2!A3:B9,2,FALSE) => VLOOKUP(B4,Sheet2!A$1:B$7,2,FALSE)
기준이 되는 표의 시작과 끝이 변화가 없는게 보이시죠?
|
맨 마지막 제2외국어 값은 여전히 에러네요.
옆에 표를 보시면 40점에 해당 되는 값이 없죠. 나머지는 6등급이네요. | |
|
여기서 위와 같이 에러가 발생했을 경우 간단하게 조치해보도록 할께요.
아래 함수 위치 보이시죠?
IFERROR 라는 함수를 이용해서 해결을 해봤습니다. 위에서 수정했을때 처럼 C2 위치 값부터 수정해서 전체 적용으로 해보았습니다.
IFERROR(VLOOKUP(B2,Sheet2!A$1:B$7,2,FALSE), 6)
자~! 어떠세요? 천천히 따라하다 보니 쉽게 해결이 되었죠? 하시는 엑셀 작업 휘리릭~ 빨리 끝내세요. 수고하셨습니다.
