지난 100 년 동안 특정 로케일에 대한 모든 강우 판독 값이 포함 된 거대한 워크 시트가 있다고 가정합니다. A2 : A37987 셀에는 1903 년 1 월 1 일부터 2006 년 12 월 31 일까지의 날짜가 있습니다.

B2 : B37987 셀에는 각 날짜에 대한 측정 값이 있습니다. 또한 측정 값 중 일부는 0 (하루 동안 강우량이없는 경우) 또는 공백 (특정 날짜에 판독 값이없는 경우) 일 수 있습니다. 이 모든 정보를 사용하여 연중 특정 날짜에 대한 평균 역사적 강우량을 계산하려고합니다.

한 가지 해결책은 다음과 같이 배열 수식을 사용하는 것입니다.

  1. 날짜가 포함 된 A 열의 모든 셀을 선택하고이 범위에 날짜 이름을 지정합니다.

  2. 강우량 데이터가 포함 된 B 열의 모든 셀을 선택하고이 범위에 Rainfall이라는 이름을 지정합니다.

  3. D2 셀에서 시작하여 D 열에 연중 모든 날짜를 배치합니다. D2에서 D366까지 날짜로 채워야합니다.

  4. E2 셀에 다음 배열 수식을 입력합니다 (Shift + Ctrl + Enter를 눌러 수식 종료). 수식의 결과는 D2 셀에 지정된 날짜에 대해 강우량 범위에있는 모든 셀의 합계입니다.

=SUM((MONTH(Dates)=MONTH(D2))(DAY(Dates)=DAY(D2))Rainfall)

F2 셀에 다음 배열 수식을 입력합니다 (Shift + Ctrl + Enter를 눌러 수식 종료). 수식의 결과는 D2 셀의 날짜에 대해 값이있는 강우량 범위의 셀 수입니다.

=SUM((MONTH(Dates)=MONTH(D2))(DAY(Dates)=DAY(D2))(Rainfall<>""))

G2 셀에 다음 일반 수식을 입력하십시오. 이것은 D2 셀의 날짜에 대한 평균입니다.

=IF(F2<>0,E2/F2,"")

E2 : G2 범위를 선택하고 D 열에 표시된 모든 날짜를 복사합니다.

이 방법은 작동하지만 계산하는 데 시간이 많이 걸립니다. 이는 각각 37,000 개 이상의 셀을 확인하는 730 개의 배열 수식을 효과적으로 입력했기 때문입니다. 이것은 많은 작업이며 결과적으로 7 단계를 완료 한 후 시스템이 “중단”된 것처럼 보일 수 있습니다. 계산을 완료하는 데 시간이 걸립니다.

수행해야하는 계산 수를 줄이려면 위 단계에서 변형을 사용할 수 있습니다. 설명 된대로 1-3 단계를 수행 한 후 다음 배열 수식을 E2 셀에 배치합니다.

=AVERAGE(IF(ISNUMBER(Dates)ISNUMBER(Rainfall)(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))

그런 다음 D 열에 표시된 모든 날짜에 대해 수식을 복사 할 수 있습니다.

이 공식의 결과는 이전 접근법에서 G 열에 표시된 것과 동일한 실제 평균 강우량입니다.

이 수식은 Excel에서 부울 산술이 작동하는 방식 때문에 작동합니다. ISNUMBER 함수는 True 또는 False를 반환하고 비교 (MONTH 및 DAY)는 True 또는 False를 반환합니다. 이러한 결과는 모두 서로 곱해 지므로 모든 개별 테스트가 True 인 경우에만 True가됩니다. 모두 True 인 경우에만 해당 특정 날짜에 대한 강우량의 평균이 계산됩니다.

연중 매일 평균을 계산하는 모든 테이블을 제거하기 만하면 계산 오버 헤드를 훨씬 더 줄일 수 있습니다. A 열과 B 열에 날짜와 강우량이 있으면 다음 단계를 따르세요.

  1. 날짜가 포함 된 A 열의 모든 셀을 선택하고이 범위에 날짜 이름을 지정하십시오.

  2. 강우량 데이터가 포함 된 B 열의 모든 셀을 선택하고이 범위에 Rainfall이라는 이름을 지정합니다.

  3. D2 셀에 평균 강우량을 확인하려는 날짜를 입력합니다. (연도는 중요하지 않으며 월과 일만 계산에 사용됩니다.)

  4. E2 셀에 다음 수식을 입력합니다.

=AVERAGE(IF(ISNUMBER(Dates)ISNUMBER(Rainfall)(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))

그게 다야. 이제 셀 D2의 날짜를 원하는대로 변경할 수 있으며 셀 E2는 항상 해당 날짜의 평균 강우량을 나타냅니다. E2 셀의 수식은 마지막 방법에서 사용한 수식과 동일합니다. 차이점은 1 년의 모든 날에 대해 계산하지 않기 때문에 계산이 훨씬 더 빠르다는 것입니다.

또 다른 접근 방식은 Excel의 필터링 기능을 사용하는 것입니다.

그러나 올바르게 사용하기 전에 데이터의 각 날짜에 대한 월과 일만 표시하는 열을 만들어야합니다. C2 셀에서 다음 수식을 사용하십시오.

=MONTH(A2) & "-" & DAY(A2)

이제 자동 필터링 (데이터 | 필터 | 자동 필터)을 켜고 새 열 상단의 드롭 다운 목록을 사용하여 평균을 원하는 날짜를 선택합니다. 그런 다음 원하는 셀에 다음 공식을 사용하여 선택한 날짜의 평균 강우량을 표시합니다.

=SUBTOTAL(1,B:B)

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (2350)은 Microsoft Excel 97, 2000, 2002 및 2003에 적용됩니다. 여기에서 Excel (Excel 2007 이상)의 리본 인터페이스에 대한이 팁의 버전을 찾을 수 있습니다.

link : / excelribbon-Calculating_Averages_by_Date [날짜 별 평균 계산].