엑셀 vlookup sum으로 부품 견적 합계 구하는 방법
조립 PC 견적표를 엑셀로 만들다 보면 부품 코드를 입력하는 순간 가격을 불러오고, 마지막에 총액까지 보고 싶어집니다. 저도 부품 가격표와 견적서를 따로 관리할 때 VLOOKUP으로 가격을 가져온 뒤 SUM으로 합계를 냈습니다. 그런데 같은 부품 코드가 가격표에 여러 번 들어가면 숫자가 맞지 않을 수 있습니다. 먼저 무엇을 더하려는지 구분해야 수식이 간단해집니다.
부품 가격을 불러와 견적 합계 만들기
가장 흔한 형태부터 보겠습니다. ‘부품표’ 시트의 A열에는 부품 코드, B열에는 판매가가 있고, 견적서의 A2부터 A10까지 구매할 부품 코드를 적었다고 가정합니다. 견적서 B2에 =VLOOKUP(A2,부품표!$A$2:$B$100,2,FALSE)를 넣고 B10까지 채운 뒤, 합계 셀에 =SUM(B2:B10)을 입력하면 됩니다.
수식의 2는 지정한 범위에서 두 번째 열인 판매가를 뜻합니다. FALSE는 코드를 정확히 일치시켜 찾겠다는 설정입니다. 가격표 범위에 붙인 $ 기호는 수식을 아래로 복사해도 참조 위치가 밀리지 않도록 고정합니다. A2의 코드가 부품표에서 찾는 범위의 첫 열에 있어야 한다는 점도 중요합니다. 코드가 B열에 있고 가격이 A열에 있다면 이 수식으로는 왼쪽 가격을 가져올 수 없습니다.
예를 들어 CPU 28만 원, 메모리 9만 원, SSD 11만 원이 각각 조회되면 SUM의 결과는 48만 원입니다. 중간에 가격이 바뀌어도 부품표의 해당 셀만 수정하면 견적 금액이 따라 바뀝니다. 품목이 여러 개라면 조회 수식을 한 줄씩 채우는 방식이 어느 행에서 금액이 달라졌는지 확인하기도 편합니다.
조회한 한 행의 여러 금액을 더할 때
부품표가 A열 코드, B열 판매가, C열 배송비로 구성돼 있다면 한 코드에서 두 금액을 찾아 더할 수 있습니다. 견적서 A2에 코드가 있을 때 =SUM(VLOOKUP(A2,부품표!$A$2:$C$100,2,FALSE),VLOOKUP(A2,부품표!$A$2:$C$100,3,FALSE))를 입력합니다. 판매가가 450000원이고 배송비가 3000원이면 결과는 453000원입니다.
여기서 SUM은 VLOOKUP이 각각 반환한 두 값을 더할 뿐입니다. 조회 범위에 같은 코드가 여러 줄 있어도 그 줄들을 모두 합산하지는 않습니다. 또 배송비가 주문 전체에 한 번만 붙는 조건이라면 이 수식을 모든 부품 행에 복사하면 배송비가 행마다 더해집니다. 실제 견적서에서는 배송비를 별도 셀에 두고 부품 가격 합계에 한 번만 더하는 편이 정확합니다.
같은 코드가 여러 줄이면 SUMIF 사용하기
입고 내역처럼 같은 SSD 코드가 여러 행에 반복되는 표에서는 VLOOKUP이 첫 번째 일치 행의 값만 가져옵니다. 이를테면 ‘입고표’의 A열에 코드, C열에 금액이 있고 SSD01 코드의 입고 금액이 80000원과 120000원으로 두 번 기록돼 있어도 VLOOKUP 결과는 먼저 찾은 80000원입니다. 두 건의 합계 200000원이 필요하다면 =SUMIF(입고표!$A$2:$A$100,A2,입고표!$C$2:$C$100)을 써야 합니다.
조건이 두 개 이상이면 SUMIFS가 맞습니다. 코드가 같으면서 상태가 ‘구매 확정’인 행만 더하려면, 상태가 B열일 때 =SUMIFS(입고표!$C$2:$C$100,입고표!$A$2:$A$100,A2,입고표!$B$2:$B$100,"구매 확정")처럼 작성합니다. VLOOKUP은 한 건을 찾아 값을 가져올 때, SUMIF나 SUMIFS는 조건에 맞는 여러 행을 합칠 때 쓰는 식으로 생각하면 수식 선택이 쉬워집니다.
합계가 틀릴 때 확인할 부분
VLOOKUP 결과가 #N/A라면 코드가 가격표에 없거나 두 시트의 코드 형식이 다른 경우가 많습니다. 숫자처럼 보이는 코드라도 한쪽은 숫자, 다른 쪽은 문자로 저장돼 있으면 일치하지 않을 수 있습니다. 앞뒤 공백이나 코드 끝자리도 확인해야 합니다. 합계가 예상보다 작다면 가격표에 중복 코드가 있는지, VLOOKUP이 그중 첫 행만 읽고 있지는 않은지 먼저 봅니다.
오류가 있는 행을 0원으로 처리하려면 =IFERROR(VLOOKUP(A2,부품표!$A$2:$B$100,2,FALSE),0)을 쓸 수 있습니다. 다만 코드 누락까지 0원으로 표시되므로 견적을 제출하기 전에는 오류가 난 행을 먼저 고치는 편이 안전합니다. 단가가 문자로 저장된 경우에도 합계가 기대와 다를 수 있으니, 가격 열이 실제 숫자 형식인지 확인합니다.
견적표에서는 조회한 가격을 행마다 보여주고 마지막에 SUM으로 더하는 구성이 가장 다루기 편했습니다. 가격이 이상할 때 어느 부품에서 틀어졌는지 바로 보이기 때문입니다. 같은 코드의 거래 기록을 모두 합쳐야 한다면 그때는 VLOOKUP 수식을 늘리기보다 SUMIF로 바꾸는 게 맞습니다.
