Excel에서 주간 평균을 계산하는 방법은 무엇입니까?

아래 스크린샷과 유사한 구매 테이블이 있다고 가정해 봅시다. 이 예제는 도우미 열과 내장 함수를 사용하여 주간 평균을 계산하는 방법을 상세히 설명합니다. 이 방법은 단계별 수동 작업을 선호하며 계산 과정을 완전히 확인하고자 하는 대부분의 사용자에게 적합합니다.

1단계: 각 행을 주 번호로 레이블링하기 위한 도우미 열 추가하기:

입력하기 주차 번호 셀 D1에 입력하고, 셀 D2에는 다음 수식을 입력합니다. =WEEKNUM(A2,2)(여기서 A2는 날짜/시간 열의 구매 날짜를 참조합니다. 두 번째 인수 2는 Excel에 주가 월요일에 시작한다고 간주하도록 지시하며, 이는 대부분의 비즈니스 시나리오에 적합합니다. 주가 일요일에 시작한다면 1을 사용할 수 있습니다). 그런 다음 채우기 핸들을 아래로 드래그하여 전체 데이터 범위에 대해 주 번호를 채웁니다. 이제 각 행이 어느 주에 속하는지 확인할 수 있습니다.

2단계: 이 단계에서는 데이터를 주별로 그룹화할 수 있지만, 다른 연도의 동일한 주를 구분하고 싶을 수도 있습니다. 이런 경우 연도를 위한 추가 도우미 열을 추가하세요:

입력하기 연도 셀 E1에 입력합니다. 셀 E2에는 다음을 입력하세요 =YEAR(A2) (다시 말하지만 A2는 구매 날짜 셀입니다). 채우기 핸들을 아래로 드래그하시면 데이터에 보다 정확한 그룹화를 위해 주와 연도 열이 포함됩니다.

3단계: 이제 주간 평균을 계산합니다. 셀 F1에 입력하세요 평균셀 F2에 다음 수식을 입력하세요: =IF(AND(D2=D1,E2=E1),"",AVERAGEIFS($C$2:$C$39,$D$2:$D$39,D2,$E$2:$E$39,E2))그런 다음 채우기 핸들을 원하는 범위까지 아래로 드래그하세요.

이 수식은 동일한 연도의 동일한 주에 대한 금액 평균을 계산하며, 각 고유한 (주, 연도) 조합의 첫 번째 인스턴스에서만 결과를 표시합니다(같은 그룹의 다른 행은 비워둠).

참고:
(1) 동일한 연도에 한 주에 여러 항목이 나타나는 경우, 첫 번째 해당 행에만 평균 값이 표시되고 명확성을 위해 다른 행은 비워둡니다.
(2) D1과 D2는 주 번호 열을 참조하고, E1과 E2는 연도 열을 참조합니다. $C$2:$C$39는 평균을 계산하려는 금액 값의 범위이며, $D$2:$D$39는 주 번호 열이고, $E$2:$E$39는 연도 열입니다; 데이터셋에 맞게 이러한 범위와 참조를 조정하세요.
(3) 연도를 고려하지 않고 주 번호만으로 평균을 계산하려면 F2에서 다음을 사용하세요: =AVERAGEIF($D$2:$D$39,D2,$C$2:$C$39) 그리고 아래로 채우세요. 그러면 연도를 구분하지 않는 단순한 주간 평균을 얻을 수 있습니다. 아래 예시 결과를 참조하세요:

팁: 매우 큰 데이터셋의 경우 수식을 아래로 복사하면 Excel이 느려질 수 있습니다. 그런 경우에는 더 확장 가능한 솔루션인 VBA 자동화 또는 피벗 테이블 접근 방식을 참조하세요.

#DIV/0! 오류가 발생하면 일반적으로 데이터셋에 해당 주에 대한 일치하는 항목이 없다는 것을 의미합니다. 도우미 열과 금액 열에 빈칸이나 불일치하는 데이터 형식이 없는지 확인하세요.

이 방법은 Kutools for Excel의 고급 행 결합 유틸리티를 소개하며, 수식을 수동으로 입력하지 않고도 모든 주간 평균을 일괄 처리로 계산할 수 있습니다. Kutools는 중복되는 그룹화 및 계산이 포함된 중대형 목록을 처리할 때 시간을 절약하고 수작업 오류를 줄여 복잡한 그룹화 및 평균 작업을 간소화합니다.

1. 도우미 열에서 주 셀 D1에 입력합니다. 그런 다음 D2에 다음을 입력하세요 =WEEKNUM(A2,2) (A2 = 날짜 셀). 아래로 채워 각 레코드에 주 번호를 태그합니다. 이 열을 통해 Kutools는 레코드를 주별로 그룹화할 수 있습니다.

2. 새 주 열을 포함한 테이블을 선택하고 클릭하세요 Kutools > 텍스트 > 고급 행 결합Kutools는 선택된 범위에서 작동하므로, 결합할 열과 주별 그룹화 열을 포함한 관련 데이터를 모두 선택했는지 확인하세요.

3. 열기 Combine Rows Based on Column 대화상자에서 다음 작업을 설정하세요:
(1) Fruit 열을 클릭하고 결합 (쉼표를 구분 기호로 사용)하여 주별로 과일 이름을 함께 유지합니다.
(2) Amount 열에 대해 작업을 설정하세요 계산 > 평균 자동으로 주간 평균을 제공합니다.
(3) 주 열을 주요 키 설정하여 그룹화가 주별로 이루어지도록 합니다.
(4) 클릭하세요 확인 처리하기 위해.

Kutools는 모든 레코드를 주별로 신속하게 그룹화하고 관련된 모든 과일을 나열하며 깔끔한 새 테이블에 평균을 표시합니다. 아래에 표시된 것처럼:

장점: 대규모 테이블 처리가 빠르고, 수작업 오류가 최소화되며, 출력이 명확합니다. 참고: 시작하기 전에 도우미 열이 올바른지 확인하세요. 잘못된 경우 결과에 영향을 미칠 수 있습니다. 주 번호가 여러 연도의 항목에 반복되는 경우 정확성을 위해 연도 도우미 열을 추가하고 주와 연도로 그룹화해야 할 수 있습니다.

피벗 테이블 방법은 Excel의 대화형 요약 기능을 활용하여 데이터를 주별로 그룹화하고 주간 평균을 빠르게 계산합니다. 이 접근 방식은 시각적 드래그 앤 드롭, 유연한 데이터 탐색, 소스 데이터 변경 시 결과를 쉽게 업데이트하기를 선호하는 사용자에게 가장 적합합니다. 수동 수식 없이 대규모 데이터 세트를 처리하는 데 실용적입니다.

1단계: 테이블에 주 번호를 위한 도우미 열을 추가하세요. 빈 열(예: D2)의 첫 번째 셀에 다음과 같이 입력하세요: =WEEKNUM(A2,2), 구매 날짜를 참조합니다. 각 행에 해당 연도의 주 번호로 태그를 지정하기 위해 아래로 채우세요.

2단계: 새 Week 열을 포함한 전체 테이블을 선택한 상태에서 '삽입' > '피벗 테이블'로 이동하세요. 대화상자에서 범위를 확인하고 배치할 새 워크시트 또는 기존 워크시트를 선택하세요.

3단계: 피벗 테이블 필드 목록 창에서:

• Week 열을 행 영역으로 드래그하세요.
• 선택적으로, 데이터를 연도별로 구분하려면 Year 열(=YEAR(A2)로 생성됨)을 Week 위의 행으로도 드래그하세요.
• Amount 열을 값 영역으로 드래그하세요. 드롭다운 > 값 필드 설정을 클릭하고 평균으로 설정하세요.

이제 피벗 테이블은 각 주 또는 (연도, 주) 그룹에 대한 평균 금액을 자동으로 표시합니다. 새 데이터를 추가한 후 언제든지 테이블을 새로 고쳐 결과를 즉시 업데이트할 수 있습니다. 이 방법은 다른 요약 통계를 탐색하거나 필요에 따라 다른 기간으로 재그룹화하는 데 특히 유연합니다.

팁: 데이터가 1년 이상에 걸쳐 있는 경우, 다른 연도의 동일한 주를 결합하는 것을 방지하기 위해 항상 연도와 주 필드를 포함하세요. 주의 날짜 범위를 표시하려면 각 주의 시작 날짜를 나열하는 계산된 도우미 열을 추가하면 됩니다. 빈 행이나 예상치 못한 결과가 보이면 도우미 열에 빈칸이 없는지 확인하고 테이블이 제대로 Excel 테이블(Ctrl+T)로 포맷되어 있는지 확인하세요.

장점: 수동 수식 없음, 데이터 변경 시 동적 업데이트, 대규모 데이터 세트 지원. 제한 사항: 주 번호를 위한 도우미 열이 필요함; 원본 날짜는 인식된 날짜 형식이어야 함.

Excel에서 년/월/날짜별 평균 계산하기

Excel에서 일별 타임스탬프 평균 계산하기

Excel에서 피벗 테이블을 사용하여 하루/한 달/분기/시간별 평균 계산하기