엑셀 SUMPRODUCT로 수량을 반영한 가중평균 단가 계산하기

 

거래별 수량이 다르면 단가를 단순 평균하지 말고 SUMPRODUCT로 단가와 수량의 곱을 합산한 뒤 총수량으로 나누세요. 이 수량 가중평균이 전체 거래의 평균 단가이며, 총금액÷총수량 값으로 다시 대조해야 합니다.

핵심 요약

단순 평균은 거래 8건을 같은 비중으로 보지만, 수량 가중평균은 각 단가를 실제 수량만큼 반영합니다. 아래 예제에서는 두 결과가 10,625.00원과 8,500.00원으로 갈리며, 총수량이 0이 아니고 모든 단가가 숫자일 때만 계산값을 채택합니다.

목차

1. 수량이 다른 거래에서 평균 기준부터 정하기
2. 단순 평균과 수량 가중평균을 고르는 기준
3. 8건 거래로 가중평균 단가 계산하고 검산하기
4. 분모 0·텍스트 단가·범위 불일치 점검하기
5. 8,500원과 PASS로 계산 완료 판정하기

---

1

수량이 다른 거래에서 평균 기준부터 정하기

적용 대상 같은 품목을 여러 단가와 수량으로 매입하거나 판매한 뒤 전체 평균 단가를 보고해야 하는 표에 적용합니다. 거래 건수의 평균이 아니라 전체 수량 1개당 평균 단가가 필요한 상황입니다.

단가만 AVERAGE로 계산하면 1개가 거래된 단가와 200개가 거래된 단가가 같은 비중을 갖습니다. 소량 고가 거래와 대량 저가 거래가 함께 있으면 이 방식은 실제 물량 구성을 반영하지 못합니다.

가중치는 수량이며, 계산 구조는 ‘단가×수량의 합계÷수량 합계’입니다. 회계상 재고평가 방법이나 가격의 적정성을 판단하는 계산이 아니라, 입력된 거래표의 수량 기준 평균만 구합니다.

2026년 9월 12일 확인 기준으로 Microsoft 공식 문서는 SUMPRODUCT를 Microsoft 365와 Excel 2024·2021·2019·2016에 적용되는 함수로 안내합니다.

---

2

단순 평균과 수량 가중평균을 고르는 기준

비교 아래 표는 같은 8건의 단가와 수량을 세 가지 방식으로 계산한 결과입니다.

같은 거래 8건을 대상으로 한 평균 계산 방식 비교

계산 방식	엑셀 수식	결과	선택 기준
단순 평균	=AVERAGE(B6:B13)	10,625.00원	각 거래를 같은 비중으로 볼 때
수량 가중평균	=SUMPRODUCT(B6:B13,C6:C13)/SUM(C6:C13)	8,500.00원	전체 수량 기준 평균 단가가 필요할 때
독립 검산	=SUM(D6:D13)/SUM(C6:C13)	8,500.00원	가중평균 수식을 다른 계산 경로로 대조할 때

수량이 모두 같으면 단순 평균과 가중평균이 같지만, 수량이 다르면 전체 수량 기준 보고에는 가중평균을 사용합니다. SUMPRODUCT는 대응하는 요소를 곱해 합산하므로 단가 범위와 수량 범위를 나란히 지정할 수 있습니다.

자료: Microsoft Support, SUMPRODUCT function, 2026-09-12 확인

---

3

8건 거래로 가중평균 단가 계산하고 검산하기

재현 예제 B6:B13에는 거래단가, C6:C13에는 수량, D6:D13에는 행별 거래금액을 입력합니다. D열은 가중평균과 다른 계산 경로를 만들기 위한 독립 검산값입니다.

수량 편차가 큰 거래 8건의 입력값과 행별 거래금액

품목	거래단가(원)	수량(개)	거래금액(원)
거래 1	14,000원	2개	28,000원
거래 2	11,000원	5개	55,000원
거래 3	10,000원	10개	100,000원
거래 4	9,500원	30개	285,000원
거래 5	9,000원	50개	450,000원
거래 6	8,500원	100개	850,000원
거래 7	8,000원	200개	1,600,000원
거래 8	15,000원	1개	15,000원
합계	해당 없음	398개	3,383,000원

가중평균과 독립 검산 수식 입력 순서

1단계

거래표 입력

A6:D13에 8건의 품목, 거래단가, 수량, 거래금액을 입력합니다.

2단계

가중평균 계산

E6에 =IF(SUM(C6:C13)=0,"분모 0",SUMPRODUCT(B6:B13,C6:C13)/SUM(C6:C13))을 입력합니다.

3단계

총금액으로 재계산

F6에 =IF(SUM(C6:C13)=0,"분모 0",SUM(D6:D13)/SUM(C6:C13))을 입력합니다.

4단계

결과 비교

E7의 단순 평균과 E6의 가중평균을 비교하고, E6과 F6이 일치하면 G6에 PASS가 표시되도록 검사합니다.

E6은 3,383,000원÷398개와 같은 8,500.00원이 되고, F6도 8,500.00원으로 계산됩니다. E7의 단순 평균은 10,625.00원이므로 수량 반영 여부에 따라 2,125.00원 차이가 생긴 것을 확인할 수 있습니다.

계산 예제 통합 문서와 검증 화면

예제 통합 문서는 A6:G13의 8건 입력과 E6:F6 비교값, G6:G10의 입력·범위 검사를 담습니다.

example-workbook.xlsx

0.01MB

---

4

분모 0·텍스트 단가·범위 불일치 점검하기

주의 정상 결과가 보이더라도 입력 구조가 깨지면 계산값을 그대로 사용하면 안 됩니다. 특히 SUMPRODUCT는 숫자가 아닌 배열 항목을 0으로 취급하므로 텍스트 단가가 조용히 계산에서 빠질 수 있습니다.

결과를 채택하기 전의 중단 기준

수량 가중평균이 맞는지 판단할 때는 SUMPRODUCT 결과만 보지 말고, 행별 거래금액 합계를 총수량으로 나눈 값과 일치하는지 대조하세요. 단가가 텍스트이거나 총수량이 0이면 계산을 멈추고 입력부터 바로잡아야 합니다.

복사본에서 예외 조건 재현하기

1단계

복사본 준비

원본 통합 문서를 복사하고 예외 검사는 복사본에서만 진행합니다.

2단계

텍스트 단가 검사

B13을 15000원이 포함된 텍스트로 바꾸면 G7이 ‘텍스트 단가 있음’으로 바뀌고 G6은 FAIL이 되어야 합니다.

3단계

분모 0 검사

C6:C13을 모두 0개로 바꾸면 E6과 F6에 ‘분모 0’, G8에 ‘분모 0’이 표시되어야 합니다.

4단계

범위 크기 확인

단가와 수량 범위는 모두 8행으로 유지합니다. 한쪽 범위만 줄이면 SUMPRODUCT가 #VALUE! 오류를 반환합니다.

G9의 ‘범위 일치’는 두 배열이 같은 행 수인지 확인하고, G10의 ‘거래금액 일치’는 단가×수량 합계와 D열 합계를 대조합니다. Microsoft는 성능 저하를 피하기 위해 B:B와 C:C 같은 전체 열 참조도 사용하지 말라고 안내합니다.

자료: Microsoft Support, SUMPRODUCT function의 Remarks, 2026-09-12 확인

중요한 업무 파일은 원본을 보존하고 복사본에서 텍스트 단가와 총수량 0 조건을 시험한 뒤 정상 입력값으로 되돌리세요.

---

5

8,500원과 PASS로 계산 완료 판정하기

완료 확인 E6과 F6이 모두 8,500.00원이고 G6이 PASS이면 두 계산 경로가 일치한 상태입니다. G7부터 G10까지 정상 문구가 유지되는지도 함께 확인해야 입력 예외를 남기지 않을 수 있습니다.

 

G7이 ‘단가 숫자’, G8이 ‘분모 정상’인지 확인합니다.

 

G9가 ‘범위 일치’, G10이 ‘거래금액 일치’인지 확인합니다.

 

가중평균 단가가 총금액을 총수량으로 나눈 독립 계산값과 일치한다.

 

예외 문구나 FAIL이 남아 있으면 보고값으로 사용하지 말고 입력 범위와 데이터 형식을 수정합니다.

참고 자료

Microsoft Support

SUMPRODUCT 구문, 적용 버전, 배열 크기·텍스트 값·전체 열 참조 주의사항

* 본문의 기능과 절차는 위 공식 문서와 재현 예제를 기준으로 확인했습니다. 프로그램·버전과 확인일은 해당 설명에 표시했습니다.