견적서를 만들거나 단가를 직접 입력해야 할 때 입력 오류를 제거하기 위해 입력돼있는 단가표에서 직접 불러오는 경우가 많습니다 이럴 때 사용하는 함수가 엑셀 vlookup함수이고, 이것을 기반으로 납품가격의 합계를 구하는 sumif함수와 sumifs함수에 대해 알아보겠습니다.
▣ 엑셀 vlookup함수
| 엑셀vlookup함수 [ =vlookup(찾는 값, 찾는 값의 범위, 열번호, range_lookup) |
위 시트는 2021년 9월 부품 납품수량 현황표입니다. 부품단가를 직접 입력하지 않고 부품단가표에서 단가를 불러오겠습니다. [E5] 셀을 선택하고 수식을 입력합니다. VLOOKUP함수에서 찾는 값은 부품 코드 AS001이고 찾는 값의 범위는 [I5 : J8]이고 여기에서 표의 범위는 절대 참조해야 하므로 F4를 누릅니다. 열 번호는 부품단가이니 [ 2 ] 열이 됩니다. range_lookup은 정확하게 일치하는 값을 찾아야 하므로 false를 선택합니다.
함수 인수 팝업창은 수식 입력 줄에 =vlookup( 을 입력한 상태에서 수식 입력 줄 왼쪽에 있는 fx를 누르면 나타납니다. 수식을 입력하고 [확인]을 누르면 단가가 불려 옵니다. 이렇게요!
부품단가가 불려 온 [E5] 셀을 선택하고 자동 채우기 핸들이 나타나면 더블클릭합니다. 그러면 수식이 복사되며 각 셀에 부품단가가 채워집니다.
부품단가가 구해졌으니 [납품 가격]은 부품단가 X 납품수량이므로 [G5] 셀을 선택하고 [=E5 X F5]를 입력합니다. 그런 후 자동 채우기 핸들을 더블클릭하면 수식이 복사되며 각 셀에 납품 가격이 계산됩니다.
▣ 엑셀 SUMIF함수로 각 부품 코드별 납품가격 합계액 구하기
| 엑셀SUMIF함수 [ =SUMIF( range, criteria, sum_range) |
위 시트에서 일자별 부품 코드를 보면 [AS001]이 5개가 있습니다. 이 5개의 납품가격의 합계액을 구하는 방법은 엑셀 SUMIF를 사용하여 구할 수 있습니다. [=SUMIF(ranse, criteria, sum_range)]입니다. 여기서 [range]는 찾고자 하는 부품 코드의 범위이므로 [D5:D20]을 지정하고 고정값이므로 F4를 눌러 절대 참조합니다. [criteria]는 찾는 값이므로 [I15] 셀을 선택해야 합니다. 간혹 보면 부품코드에서 선택하는 실수가 있기 때문이죠! 잊으면 안 됩니다. 마지막으로 [sum_range]는 합계를 구하는 범위이니 [G5:G20]을 지정하고 마찬가지로 절대 참조하기 위해 F4를 눌러줍니다.
수식 입력창에 [=SUMIF(]를 입력하고 좌측의 [ fx ]를 누르면 함수 인수 팝업창이 뜨면 수식을 입력하고 [확인]을 누릅니다.
위 시트를 보면 [J15] 셀에 [AS001]의 납품 가격 합계액이 구해졌죠! 마찬가지로 채우기 핸들을 더블클릭하면 값이 각 셀에 입력됩니다.
▣ 엑셀 SUMIFS함수로 조건을 붙인 납품가격 합계액 구하기
| 엑셀 SUMIFS 함수 [ =SUMIFS( sum_range, criteria_range 1, criteria 1, criteria_range 2, criteria 2) ] |
위 시트를 보면 [AS001]의 납품가격 합계액을 구했는데 이번에는 [AS001]의 납품가격 중 1,000만 원 이상 납품된 가격의 합계액을 구하는 방법입니다. 다소 복잡할 수 있으나 어렵지는 않습니다. 집중하여 봐주세요!
[sum_range]는 납품가격의 범위이므로 [G5:G20]을 설정하고 절대 참조를 위해 F4를 누릅니다. [criteria_range 1]은 부품코드의 범위 이므로 [D5:D20]을 지정하고 마찬가지로 절대참조를 위해 F4를 누릅니다. [ctiteria 1]은 찾는 값이므로 [I15]인 [AS001]을 선택합니다. 다음은 조건식을 입력해야 합니다. [criteria_range 2]는 찾는 값의 범위이므로 [G5:G20]이고 절대참조를 잊지 말아야 합니다. F4를 눌러 절대참조합니다. 마지막으로 찾는 갑의 조건인 [criteria 2]은 1,000만 원 이상이므로 ">= 10,000,000"이라고 입력하고 [확인[을 누릅니다.
값이 구해졌습니다. 자동 채우기 핸들을 더블클릭하면 수식이 복사되며 빈셀이 채워집니다.
이렇게 해서 1,000만 원 이상 납품가격 합계액을 구했습니다. 오늘은 vlookup함수, sumif함수, sumifs함수에 대해 알아보았습니다. 함수는 이렇게 이해가 돼도 쓰지 않고 놔두면 다음에 보면 아무것도 생각나지 않는 것이 신기합니다. 꾸준한 학습으로 자기 것으로 만들기 바랍니다.
