John은 약 60 년 동안의 일일 데이터가 포함 된 거대한 워크 시트를 가지고 있습니다. 그는 데이터가 다루는 기간의 매월 중앙값을 계산하는 공식을 만들고자합니다.

솔루션을 제안하기 전에 몇 가지 가정을 배치하는 것이 가장 좋습니다. 이 팁에서는 일일 데이터가 A 열과 B 열에 있다고 가정하겠습니다. A 열에는 데이터와 관련된 날짜가 있고 B 열에는 각 날짜에 대한 실제 데이터 값이 있습니다. 또한 A1 및 B1 셀에는 각 열의 머리글이 포함됩니다. 이는 실제 데이터가 대략 A2 : B22000 범위에 있음을 의미합니다.

수식을 더 쉽게 사용하려면 A와 B 열 모두에서 데이터 이름을 정의해야합니다. 날짜 범위 (예 : A2 : A22000)를 선택하고 AllDates와 같은 이름을 할당합니다. 해당 데이터 범위 (예 : B2 : B22000)를 선택하고 동일한 프로세스를 사용하여 AllData와 같은 이름을 만듭니다.

배열 수식을 사용하여 실제 중앙값을 계산할 수 있습니다. 여기에는 중앙값을 포함하도록 다른 데이터 테이블을 설정하는 작업이 포함됩니다. 셀 E1에 “Month”, 셀 F1에 “Median”과 같은 제목을 배치합니다. E2 셀에 1/1/1940과 같이 데이터 세트의 첫 번째 달의 첫 번째 날을 배치하십시오. 셀 E3에 1940 년 2 월 1 일과 같이 한 달 뒤의 날짜를 입력합니다. 이 두 셀 (E2 : E3)을 선택하고 데이터 테이블에서 원하는 개월 수만큼 채우기 핸들을 아래쪽으로 끕니다.

60 년 동안의 데이터에 공백이 없으면 다음 수식을 F2 셀에 입력합니다.

=MEDIAN(IF(DATE(YEAR(AllDates),MONTH(AllDates),1)=E2,AllData))

Ctrl + Shift + Enter를 눌러 수식을 완성합니다. 그러면 Excel에 이것이 배열 수식임을 알립니다. 그런 다음 F2의 수식을 E 열에 해당 월이있는 F 열의 각 셀에 복사 할 수 있습니다. 수식은 B 열의 날짜를 분석하고 연도와 월이 셀 E2에 입력 한 날짜와 같은 경우 이면 해당하는 모든 데이터 포인트에서 중앙값이 계산됩니다.

60 년 동안의 데이터에 공백이있는 경우 (A 열에 B 열에 해당 값이없는 날짜) 공식은 공백을 0 값인 것처럼 처리합니다. 공백이 있으면 중앙값이 기울어 질 수 있습니다. 이 문제를 해결하려면 공백 값을 확인하고 무시하는 다른 배열 수식을 사용할 수 있습니다.

=MEDIAN(IF((DATE(YEAR(AllDates),MONTH(AllDates),1)=E2)*ISNUMBER(AllData),AllData))

이러한 방식으로 배열 수식을 사용하는 데는 한 가지주의 사항이 있습니다. 60 년 동안의 데이터와 약 22,000 개의 개별 값이있는 경우 여전히 많은 달 (약 720 개)입니다. 즉, 720 개의 배열 수식을 만들고 각각 22,000 개의 데이터 값을 분석하여 답을 얻습니다. 많은 계산이 진행되므로 워크 시트를 다시 계산할 때마다 Excel의 응답 속도가 느려집니다.

부진이 문제가되는 경우 워크 시트의 각 행이 개별 월을 나타내도록 원본 데이터를 재구성 할 수 있습니다. A 열에는 행의 월 (1/1/1940, 2/1/1940, 3/1/1940 등)이 포함될 수 있으며 B : AF 열은 매월 1 일부터 31 일까지입니다. 그러면 테이블의 교차 셀에 해당 월의 각 날짜에 대한 데이터 요소가 포함될 수 있으며 AG 열의 MEDIAN 함수를 사용하여 각 월의 중앙값을 계산할 수 있습니다. 여전히 720 개의 수식이 생성되지만 60 년 분량의 데이터를 처리하는 데 필요한 배열 수식 대신 한 달 분량의 데이터 만 처리하면되는 일반 수식입니다. 결과는 훨씬 더 빠른 계산입니다.

물론, 대부분의 사람들에게 이렇게 방대한 양의 데이터를 재구성한다는 생각만으로도 밤에 깨어있을 수 있습니다. 대신 데이터 분석에 완전히 다른 접근 방식을 취할 수 있습니다. 이 접근 방식은 중앙값이 수동으로 계산하기 매우 쉬운 통계 함수이기 때문에 가능합니다. 데이터 세트를 정렬하고 데이터 세트의 항목 수가 홀수이면 중간 항목을 선택하기 만하면됩니다. 항목 수가 짝수이면 두 중간 항목의 평균을 취합니다.

데이터 분석을 준비하려면 몇 가지해야 할 일이 있습니다.

첫째, 각 데이터 포인트의 월을 고유하게 식별하는 방법이 있으면 편리합니다. C2 셀에 다음 수식을 입력하십시오.

=100*Year(A2)+Month(A2)

이로 인해 194001, 194002, 194003 등과 같은 값이 C 열에 저장됩니다. 이것은 고유 한 월 값입니다. 이제 데이터를 C 열을 기준으로 정렬 한 다음 B 열을 기준으로 정렬해야합니다. 두 정렬은 모두 오름차순이어야합니다. 따라서 데이터는 먼저 연 / 월별로 정렬 된 다음 연도 / 월 내 값별로 정렬됩니다.

다음으로 데이터에 부분합을 추가해야합니다. 소계 대화 상자를 표시하는 데이터 메뉴에서 소계를 선택합니다. C 열이 변경 될 때마다 소계를 추가하려고합니다. 사용할 함수는 Count이고 B 열에 소계를 추가하려고합니다. 확인을 클릭하면 데이터에서 매월 하나씩 720 개의 소계가 생성됩니다. 범위, 각 항목은 해당 월에 있었던 데이터 항목 수를 표시합니다.

중앙값을 얻으려면 D2 셀에 수식을 추가하십시오.

IF(RIGHT(B2,5)="Count", IF(MOD(C2,2)=1, INDIRECT("B"&(ROW()-1)-C2/2+1/2), (INDIRECT("B"&(ROW()-1)-C2/2)+INDIRECT("B"&(ROW()-1)-C2/2+1))/2), "")

이 수식은 B2 셀에있는 항목을 검사하고 “Count”라는 단어가 포함 된 경우 이것이 부분합 행임을 알고 있습니다. 이 경우 C2 셀의 개수가 홀수인지 짝수인지 확인합니다. 홀수이면 INDIRECT 함수를 사용하여 해당 월의 중앙값을 가져옵니다. 짝수이면 해당 월의 두 중간 값을 합산하여 반으로 나눕니다.

결과적으로 이제 소계와 동일한 행에 매달 중앙값이 있습니다. 따라서 데이터의 개요를 축소 할 수 있습니다 (데이터 왼쪽의 개요 영역에서 더하기 기호 클릭)

해당 부분합 행만 표시합니다.

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

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

link : / excelribbon-Deriving_Monthly_Median_Values ​​[월간 중앙값 도출].