1. 어따 쓰지?
엑셀을 하다 보면 반복 작업을 할 때가 많다. 어지간한 정도면 그냥 막일하면 되지만 개수가 100개 1000개가 넘어가면 실수도 많아지고 눈도 아프고 퇴사도 하고 싶고 그렇다. 이런 일을 도와주는 함수로 VLOOKUP 함수와 HLOOKUP 함수가 있다. 둘 다 테이블에서 내가 원하는 값을 찾아 주는 함수이고 VLOOKUP은 열에서, HLOOKUP은 행에서 찾아준다.
그림 1의 왼쪽에 재료의 물성이 있고 오른쪽에 있는 표에 각 파트의 안전 여유(MoS, margin of safety)를 계산해야 한다. 안전 여유는 아래 식을 통해 계산한다.
$$ MoS = \frac{\sigma_{y,ult}-\sigma}{\sigma} $$
2. VLOOKUP 사용법
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value는 찾을 값이다. table_array는 검색할 영역을 범위로 선택한다. 검색할 영역의 첫번째 열은 찾을 값이 있는 열이어야 한다. col_index_num은 검색할 영역에서 값을 받아올 열의 번호이다. 이때 검색할 영역에서 열 인덱스는 1부터 시작한다. 따라서 그림 2에서 Yield [MPa]의 인덱스는 2가 되고 Ultimate [MPa]은 3이 된다.
[range_lookup]은 완전히 일치하는 값을 찾고 싶으면 FALSE를, 유사한 값을 찾고 싶으면 TRUE를 입력한다. 아무것도 입력 안 하면 기본 값은 TRUE여서 괴상한 값을 주게 되니 꼭 FALSE를 입력하자.
이제 VLOOKUP 함수는 table_array의 첫번째 열에서 lookup_value와 일치하는 경우를 찾고 col_index_num으로 지정한 열에서 같은 위치의 값을 가져온다. 이제 수백 개 파트의 안전 여유를 계산하기 위해 셀마다 일일이 수식을 입력할 필요 없이 자동 완성으로 한 번에 채울 수 있게 됐다.
3. HLOOKUP 사용법
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
HLOOKUP 사용법도 VLOOKUP과 완전히 같다. 단지 검색 영역의 첫행에서 lookup_value를 찾고 지정한 행에서 값을 가져온다. 상황에 따라 VLOOKUP과 HLOOKUP을 골라서 사용하면 된다.
4. 구간을 찾는 방법
완벽히 일치하는 값이 없거나 어떤 구간 안에 들어오면 값을 찾고 싶을 경우 [range_lookup]으로 TRUE를 쓴다. 엑셀에서 함수를 사용할 때 유사한 값을 찾는다는 설명이 나와서 아리송할 수 있는데 유사한 값이 아니고 구간에 들어가는 값을 찾는 것이다. 따라서 가장 가까운 값을 찾아 줄 것으로 기대하고 쓰면 전혀 엉뚱한 값이 나오니 주의해야 한다.
예를 들어 아래 김부장님은 근속연수가 19년이지만 가장 비슷한 근속년수 20년을 찾아서 가산점 20점을 가져오는 것이 아니고(!) 근속년수 12년-20년 구간에 들어가므로 근속연수 12년에 해당하는 8점을 가져오게 된다. 마찬가지로 이사 원과 정 차장은 근속연수가 3년에 도달하지 못했으므로 1년 점수인 빵점을 가져온다.
5. 메뉴에서 찾기
이렇게 VLOOKUP이나 HLOOKUP과 같이 자주 쓰이는 함수는 메뉴에서 쉽게 골라서 쓸 수도 있다. 아래 그림 5처럼 [수식]-[찾기/참조 영역]으로 찾아가서 선택 후 마우스로 드륵드륵해서 쓰면 편리하다.
최근댓글