티스토리 뷰
엑셀 VLOOKUP 함수 예제 3탄
엑셀 VLOOKUP 함수 사용 예제 3탄입니다.
엑셀에서 VLOOKUP 함수는 실무에서도 자주 사용하는 함수로 VLOOKUP 함수의 사용법만 제대로 이해한다면 쉽게 응용이 가능한 함수이기도 합니다.
이번 내용은 VLOOKUP 함수 고급 내용으로 VLOOKUP 함수를 제대로 사용하기 위해서는 상대참조, 절대참조, 혼합참조 등 참조방식에 대한 이해와 VLOOKUP 함수의 기본 내용을 숙지하고 있어야 합니다.
참조방식 및 VLOOKUP 함수의 기본에 대해 정리가 부족한 분은 아래의 글을 먼저 숙지하신 후 읽어주시기 바랍니다.
마이크로소프트사에서 제공하는 VLOOKUP 함수 설명도 참고하시기 바랍니다.
엑셀의 VLOOKUP 함수 고급 사용방법 또는 엑셀 VLOOKUP 함수의 응용 방법에 대한 설명을 드리기 위해 공사내역서를 샘플을 이용해서 설계변경한 경우의 예를 보이겠습니다.
위의 그림에서 아래쪽의 데이터표는 참조할 테이블이고, 위쪽의 데이터표는 아래의 참조테이블을 참고하여 내역서를 작성하는 부분으로, 화면에 동시에 표시해야 이해하기 쉬울 것 같아서 편의상 참조테이블을 아래에 표시했지만 실무에서는 참조테이블을 별도의 시트로 작성하시기 바랍니다.
아래의 참조테이블을 참조하여 위쪽의 공종, 재료비 단가, 노무비 단가, 경비 단가를 VLOOKUP 함수로 작성하는 예입니다.
이 부분은 VLOOKUP 함수의 기본 수준으로 앞의 글에서 설명한 내용의 수준이므로 자세한 설명은 생략하더라도 이해해 주시기 바랍니다.
설명 부족으로 인해 이해가 안되는 부분이 있을 수 있을 것 같아서 아래에 엑셀 파일을 첨부하니 필요하신 분은 다운받아서 확인해 보시기 바랍니다.
VLOOKUP.xlsx
위의 그림은 수량을 임의로 입력해 두었으며, 단가 합계 및 단가 금액, 재료비 금액, 노무비 금액, 경비 금액은 아래의 수식으로 작성했습니다.
단가 합계 수식(D6셀) : =SUM(F6,H6,J6)
금액 합계 수식(E6셀) : =SUM(G6,I6,K6)
재료비 금액 수식(G6셀) : =$C6*F6
노무비 금액 수식(I6셀) : =$C6*H6
경비 금액 수식(K6셀) : =$C6*J6
B6셀은 A6셀을 참조해서 터파기 공종을 가져오기 위해 다음과 같이 수식을 작성했습니다.
B6셀 수식 : =VLOOKUP(A6,$A$16:$E$21,2,0)
VLOOKUP 함수의 1번째 인수는 A6셀을 참조하기 때문에 A6을 입력하고,
2번째 인수는 데이터를 검색하고 추출하려는 표의 범위로 A16부터 E21의 영역을 범위 지정 후 F4키를 눌러서 절대참조 형식인 $A$16:$E$21 로 변경했습니다.
3번째 인수는 추출할 열 번호이며, 공종이 아래의 참조테이블에서 2번째 열에 있기 때문에 2를 입력했습니다.
4번째 인수는 정확하게 일치하는 것을 찾을 것인지, 아니면 비슷하게 일치하는 것을 찾을 것인지를 선택하는 것으로, 정확하게 일치하는 것을 찾을 것이므로 0(또는 FALSE)을 입력했습니다.
B6셀의 내용을 채우기핸들 기능으로 B11셀까지 채워줍니다.
재료비 단가를 구하기 위해 사용된 VLOOKUP 함수 사용방법은 위의 설명과 유사하며, 수식은 다음과 같습니다.
F6셀의 수식 : =VLOOKUP(A6,$A$16:$E$21,3,0)
노무비 단가를 구하기 위해 사용된 VLOOKUP 함수 사용방법은 위의 재료비 단가 설명과 유사하며, 수식은 다음과 같습니다.
H6셀의 수식 : =VLOOKUP(A6,$A$16:$E$21,4,0)
경비 단가를 구하기 위해 사용된 VLOOKUP 함수 사용방법은 위의 재료비 단가 설명과 유사하며, 수식은 다음과 같습니다.
J6셀의 수식 : =VLOOKUP(A6,$A$16:$E$21,5,0)
위의 그림은 공종, 재료비 단가, 노무비 단가, 경비 단가를 VLOOKUP 함수를 사용하여 작성한 예입니다.
참고로 여기까지의 내용은 VLOOKUP 함수의 기초 수준입니다.
공사내역서 설계변경 샘플을 활용한 VLOOKUP 함수 예제
이제 VLOOKUP 함수의 고급 기능(?)으로 들어가겠습니다.
공사를 진행하다보면 설계변경을 자주 진행하게 되는데요.
위의 그림처럼 각 공종별로 1개의 행이 2개의 행이 되도록 추가해 줍니다.
이 경우 재료비 단가, 노무비 단가, 경비 단가를 가져오는 방법이 2가지 있는데요.
간단한 방법으로는 재료비 단가(F7셀)에서 VLOOKUP 함수의 1번째 인수를 한행 위의 값인 A6을 참조하는 방법입니다.
하지만 여기에서는 다른 상황에서도 응용하는 예를 보이기 위해 약간은 복잡한 예(그렇게 복잡하지는 않음)를 들어보겠습니다.
위의 그림처럼 A7셀에 =A6+100을 입력합니다.
재료비 단가인 F7셀에는 =VLOOKUP(A7-100,$A$22:$E$27,3,0) 라고 함수를 입력합니다.
수식 중 1번째 인수의 내용을 보시면 A7-100 이라는 값을 보실 수 있는데요.
A7셀에 100을 더해 주었기 때문에 참조테이블에서 찾을 때는 100을 뺀 값을 기준으로 찾아야 하기 때문에 A7-100 이라고 수식을 입력했습니다.
노무비 단가인 H7셀의 수식은 =VLOOKUP(A7-100,$A$22:$E$27,4,0) 을 입력해 줍니다.
노무비 단가도 역시 VLOOKUP함수의 1번째 인수에는 A7-100을 입력해 주었습니다.
경비 단가인 J7셀의 수식은 =VLOOKUP(A7-100,$A$22:$E$27,5,0) 입니다.
이렇게 작성된 셀들을 아래쪽에도 모두 계산해 주게 되면 위의 그림과 같이 완성됩니다.
투입비 내역서를 활용한 VLOOKUP 함수 예제
아래의 예제는 건설현장에서 자주 사용하는 투입비 내역 중 일부입니다.
위 그림의 아래쪽 참조테이블을 보시면 한사람의 데이터가 2줄로 되어 있습니다.
이 경우 VLOOKUP 함수를 이용해서 데이터를 가져오기 위해서는 A22셀의 내용처럼 A21의 내용에 100을 더하는 방식으로 코드를 작성해서 사용하면 쉽게 해결이 가능합니다.
A22 셀 수식 : =A21+100
B6셀의 직종, 성명, 주민등록번호 앞쪽, 주민등록번호 뒷쪽, 은행명, 계좌번호를 가져오기 위한 수식은 다음과 같습니다.
VLOOKUP 함수 사용방법은 위에서 설명한 방식과 유사하기에 자세한 설명은 생략합니다.
상세한 수식이 필요하신 분은 위쪽에서 첨부한 엑셀파일의 일용노무비지급명세서 시트를 참조하시기 바랍니다.
B6 : =VLOOKUP(A6,$A$21:$M$30,2,0)
C6 : =VLOOKUP(A6,$A$21:$M$30,3,0)
D6 : =VLOOKUP(A6,$A$21:$M$30,4,0)
D7 : =VLOOKUP(A6+100,$A$21:$M$30,4,0)
X6 : =VLOOKUP(A6,$A$21:$M$30,5,0)
X7 : =VLOOKUP(A6+100,$A$21:$M$30,5,0)
이상 엑셀에서 자주 사용하는 VLOOKUP 함수에 대한 응용방법의 예제를 마치겠습니다.