이번 포스팅에서는 엑셀 가중평균 구하기 방법, 엑셀의 다양한 평균이 무엇이 있는지 살펴보고, 엑셀 가중평균과 산술평균의 차이와, 엑셀 가중평균 구하기 방법에 대해 예시를 통해 자세하게 살펴보았습니다.
엑셀은 다양한 데이터를 쉽고 빠르게 처리할 수 있는 강력한 기능을 갖춘 스프레드시트 프로그램입니다. Microsoft Office의 핵심 구성 요소 중 하나로서, 엑셀은 복잡한 업무 환경에서 다양한 분석과 통계를 간편히 수행할 수 있도록 도와줍니다. 특히 표 형식의 데이터를 다루면서 필터링, 피벗테이블, 매크로, 그리고 각종 수식을 사용하는 등 활용도가 매우 높습니다.
일반적으로 많은 데이터를 다룰수록, 엑셀에서 데이터를 정확하게 분석하고 처리하는 방법을 아는 것은 정말 중요합니다. 수많은 데이터를 일일이 계산하기보다는, 엑셀의 다양한 통계 함수를 잘 활용해 자동화된 계산 및 분석을 수행할 수 있기 때문입니다.
엑셀에서 데이터를 처리할 때 가장 흔히 사용하는 통계 도구 중 하나가 바로 평균입니다. 하지만 ‘평균’이라고 해도 사실 여러 종류가 존재합니다. 대표적으로 산술평균(Arithmetic Mean), 가중평균(Weighted Average), 기하평균(Geometric Mean), 조화평균(Harmonic Mean), 절사평균(Trimmed Mean) 등이 있습니다. 각각의 평균은 데이터를 바라보는 관점과 중요도(가중치) 등을 다르게 반영하기 때문에, 목적에 따라 적합한 평균 계산 방식을 선택해야 더욱 정확한 데이터를 얻을 수 있습니다.
이번 글에서는 이러한 여러 평균 중에서 가중평균에 대해 상세하게 알아보겠습니다. 가중평균은 단순히 모든 값을 동일하게 취급하지 않고, 값에 부여된 ‘가중치’를 고려하여 평균을 계산하는 방식입니다. 예를 들어, 판매 단가와 판매 수량이 있을 때, 전체 평균 단가를 구하려면 단순 산술평균이 아니라 실제 판매 비중(가중치)을 고려한 평균을 구해야 실제 상황에 부합합니다. 가중평균은 이처럼 여러 측면(비중, 수량, 빈도 등)을 반영하여 값이 지니는 상대적 중요도를 계산하는 데 매우 적합합니다.
이번 포스팅에서는 우선 엑셀 가중평균이란 무엇인지, 산술평균과 비교해 어떤 차이가 있는지, 그리고 엑셀 가중평균 구하기 방법을 단계별로 살펴보겠습니다.
엑셀 가중평균이란? (산술평균 등과의 차이)
데이터를 처리할 때 자주 사용되는 평균을 정리하면 아래와 같습니다.
- 산술평균(Arithmetic Mean)
- 산술평균은 우리가 흔히 알고 있는 가장 기본적인 평균입니다. 예를 들어, 10, 20, 30이라는 숫자 세 개가 있으면, 이들의 합인 60을 3으로 나눈 20이 바로 산술평균이 됩니다.
- 엑셀에서는 =AVERAGE(범위) 함수를 사용하여 쉽게 산술평균을 구할 수 있습니다.
- 가중평균(Weighted Average)
- 가중평균은 각 항목마다 서로 다른 ‘가중치’가 주어져 있을 때 사용하는 평균 방식입니다. 예를 들어 카페에서 여러 종류의 커피를 판매하고 있다면, 단순히 커피 종류별 판매 단가의 산술평균을 내는 것보다, 판매량(가중치)을 고려해 평균 판매 단가를 구하는 것이 보다 정확한 정보를 제공합니다.
- 간단히 말해, 가중평균 = (값 × 가중치)의 총합 / 가중치의 총합 공식으로 계산합니다.
- 엑셀에서는 =SUMPRODUCT(값범위, 가중치범위) / SUM(가중치범위) 라는 수식을 많이 사용합니다.
- 기타 평균(기하평균, 조화평균, 절사평균 등)
- 기하평균(Geometric Mean)은 여러 개의 값을 곱한 다음 n제곱근을 구하는 방식으로, 성장률처럼 ‘비율’ 혹은 ‘변동률’이 중요한 경우에 주로 사용합니다.
- 조화평균(Harmonic Mean)은 속도, 비율, 같은 차원에서 역수를 이용해 평균을 내야 하는 상황에 쓰이곤 합니다.
- 절사평균(Trimmed Mean)은 극단값(이상치)을 제거한 뒤 평균을 구하는 것으로, 편향된 데이터를 다룰 때 활용도가 높습니다.
이번 포스팅에서 집중해서 다룰 가중평균은 특히 ‘중요도’가 서로 다르거나, ‘가중치’가 분명히 존재하는 자료를 분석할 때 쓰이며, 엑셀에서 이 계산을 매우 쉽게 해줍니다. 그럼 이제 실제 예시 테이블을 만들어, 산술평균과 비교하며 가중평균을 어떻게 엑셀에서 구하는지 확인해 보겠습니다.
엑셀 가중평균 구하기 방법
이번에는 예시 데이터를 통해 직접 엑셀 가중평균을 구해 보겠습니다. 예를 들어, 아래와 같은 가상의 판매 데이터가 있다고 가정해 보겠습니다. 7개 품목에 대한 판매 단가와 판매 수량이 주어졌습니다. (실제 작업에서는 데이터 양이 훨씬 많을 수 있지만, 예시를 위해 간단하게 구성했습니다.)
| 품목 | 판매 단가 | 판매 수량 |
|---|---|---|
| 커피A | 4,000 | 150 |
| 커피B | 5,000 | 200 |
| 커피C | 6,000 | 100 |
| 커피D | 3,500 | 300 |
| 커피E | 4,500 | 120 |
| 커피F | 7,000 | 90 |
| 커피G | 5,000 | 180 |
엑셀 산술평균(단순 평균) 구하기
먼저, 산술평균을 구해 보겠습니다. 엑셀 산술평균은 =AVERAGE() 함수를 사용합니다.
- 예시로, [F2] 셀에 다음과 같이 입력합니다. =AVERAGE(B2:B8)
- 만약 B2부터 B8까지가 커피A부터 커피G까지의 판매 단가를 담고 있다고 가정합니다. 이 경우, 단순하게 모든 판매 단가의 합을 항목 개수로 나눈 것이므로, ‘실제 판매 수량’을 전혀 고려하지 않은 평균 가격이 계산됩니다.
엑셀 가중평균 구하기
같은 데이터로 가중평균을 구하기 위해서는 판매 단가 × 판매 수량을 전부 합한 뒤, 전체 판매 수량으로 나눠야 합니다. 이때 엑셀의 =SUMPRODUCT() 함수를 사용하면 매우 편리합니다.
- 예시로, [F3] 셀에 다음과 같이 입력해 봅니다. =SUMPRODUCT(B2:B8, C2:C8) / SUM(C2:C8)
- SUMPRODUCT(B4:B10, C4:C10) : 품목별 판매 단가와 판매 수량을 각각 곱한 값을 모두 더함
- SUM(C4:C10) : 판매 수량의 총합
엑셀 가중평균은 단순 평균과 달리, 판매량이 많은 품목의 가격이 평균에 더 크게 반영되므로, 실제 매출 구조를 보다 정확하게 반영해 줍니다. 예컨대, 판매량이 매우 많으나 가격이 저렴한 품목이 있을 경우, 전체 평균 단가는 낮아질 수 있고, 반대로 판매량이 적지만 단가가 매우 높은 품목이 있다면 전체 평균 단가는 크게 치솟을 수 있습니다.
참고로 마이크로소프트 지원 페이지에서 SUMPRODUCT 함수 사용법에 대해 가볍게 안내하고 있습니다.
결과 비교
- 산술평균 : 모든 품목의 판매 단가를 동일 가중으로 평균
- 가중평균 : 판매량이라는 가중치를 고려하여 평균
실제로 엑셀에서 결과를 보면, 산술평균보다 엑셀 가중평균 결과값이 더 높거나 낮게 나타날 수 있습니다. 예를 들어, 위 예시에서 산술평균으로 계산하면 5,000원이었지만, 판매량을 고려한 가중평균은 4,666.67원으로 나타나는 식입니다.
이처럼 판매 수량이나 다른 가중치를 고려할 때는 가중평균을 구하는 것이 훨씬 더 현실적인 데이터를 제공합니다.
엑셀 가중평균을 사용할 때의 주의사항 및 팁
- 데이터 범위 확인
- SUMPRODUCT 함수를 사용할 때, 값의 범위와 가중치의 범위가 정확히 일치해야 합니다. 범위가 엇갈리거나 잘못 설정되면 계산 결과가 엉뚱하게 나올 수 있으므로, 수식 입력 시 항상 시작 셀과 마지막 셀을 꼼꼼히 확인해야 합니다.
- 데이터 형태(숫자/문자) 확인
- 데이터 범위 안에 텍스트나 빈 셀이 들어있다면, 함수 계산이 안 될 수도 있고, 오류가 발생할 수도 있습니다. 잘못된 값이나 공백이 없는지 미리 확인하는 습관이 중요합니다.
- 필터 상태 및 숨겨진 셀 주의
- 엑셀에서 특정 행을 필터링하거나 숨겨둔 상태라면, SUMPRODUCT와 SUM 함수에서 그 숨겨진 셀 데이터도 포함해 계산이 이뤄집니다. 부분 합계 혹은 특정 데이터만으로 가중평균을 구해야 한다면, SUBTOTAL 함수 등 다른 방식을 고려해야 합니다.
- 여러 가중치가 있는 경우
- 때로는 단순히 한 가지가 아니라 두세 가지 가중치가 있을 수 있습니다(예: 가중치1: 판매수량, 가중치2: 우선순위 점수 등). 이럴 때는 가중치간의 상대 중요도를 미리 결정한 뒤, 그 결과를 다시 한 번 가중평균으로 구하는 과정을 거쳐야 할 때도 있습니다.
- 함수 자동 채우기와 절대/상대 참조
- 많은 행에 대해 연속적으로 계산해야 할 경우, 엑셀의 자동 채우기를 이용하되, 필요한 경우 $기호를 사용하여 셀 참조가 고정되도록 해야 합니다. 예를 들어, 가중치의 합계를 구하는 셀은 절대 참조로 고정해야 다른 셀로 복사했을 때도 변하지 않고 올바른 결과가 유지됩니다.
정리하자면, 가중평균은 ‘값에 대한 중요도’ 혹은 ‘비중’을 반영하여 평균을 구하는 방법입니다.
- 산술평균: =AVERAGE(B4:B10) 처럼 단순 평균을 내는 함수
- 가중평균: =SUMPRODUCT(B4:B10, C4:C10)/SUM(C4:C10) 처럼 값 × 가중치의 합을 가중치의 합으로 나눈 방식
이번 포스팅에서는 엑셀 가중평균 구하기 방법에 대해 알아보았습니다. 가중평균은 특히 ‘중요도’가 서로 다르거나, ‘가중치’가 분명히 존재하는 자료를 분석할 때 쓰이며, 엑셀을 통해 손쉽게 계산이 가능합니다.
앞선 포스팅에서 정리한 엑셀 관련 글은 아래와 같습니다.
엑셀 SUMIFS 함수 사용법 1분 해결, 다중 조건 합산 활용 꿀팁 실전 예제 (SUMIF 차이점)
[Excel 실무 꿀팁] 엑셀 SUBTOTAL 함수 사용법 1분 끝내기 (SUM 함수 차이점)