[MS Excel 2010] 중복된 데이터에서 고유값을 남겨 놓고 각 그룹의 데이터 개수, 평균, 최대값, 최소값, 중앙값, 표준편차 구하기

Sheet1에 다음과 같이 우리 동네에 있는 자동차 브랜드와 가격(천만원) 정보가 담긴 엑셀 파일이 있다고 가정해보겠습니다.

 

 

저는 Sheet2에서 각 브랜드별 차량 개수, 평균 금액, 금액의 표준편차, 최대값, 최소값, 중앙값을 일목요연하게 보고 싶습니다.

 

1. 우선 브랜드에 해당하는 영역을 복사해서 Sheet2에 적절한 위치에 붙여넣어줍니다.

 

 

 

 2. Sheet2에서 열 A를 선택한 후에 데이터 탭에서 "중복된 항목 제거"를 클릭합니다. 그 다음에 열 A가 잘 체크되어 있는지 확인한 후에 확인 버튼을 클릭합니다.

 

 

 

고유값만 남은 것을 확인하실 수 있습니다.

 

3. COUNTIF 함수를 사용하여 Sheet1에서 브랜드가 현대로 되어 있는 행의 개수를 구합니다. Sheet1에서 A열에서 Sheet2의 A2 셀(현대)과 같은 값을 갖는 것의 개수를 파악하는 것이므로 다음과 같이 수식을 작성합니다.

 

=COUNTIF(Sheet1!A:A, Sheet2!A2)

 

 

 

기아, 아우디, 볼보 등의 개수는 이미 수식이 적용된 B2 셀을 클릭한 후에 쭉 아래로 드래그하면 됩니다.

 

 

4. AVEREGEIF 함수를 사용하여 현대차의 평균 금액을 구합니다. 현대차의 평균 금액은 다음과 같은 수식을 통해 구할 수 있습니다. 

 

=AVERAGEIF(Sheet1!A:A, Sheet2!A2, Sheet1!B:B) 

 

 

여기서 첫번째 인수는 조건값의 전체 범위이고, 두번째 인수는 조건값이고, 세번째 인수는 평균값을 구해야하는 값들의 범위입니다. 

 

 

나머지 브랜드의 평균 금액도 드래그해줌으로 구합니다. 

 

 

5. MAX와 IF 함수를 사용하여 현대차의 최고 금액을 구합니다. 현대차의 최고 금액은 다음과 같은 수식을 통해 구할 수 있습니다. 

 

=MAX(IF(Sheet1!A:A=Sheet2!A2, Sheet1!B:B))

 

위와 같이 수식을 입력한 후에 그냥 엔터를 치면 안 되고 Ctrl + Shift + Enter를 입력해야 합니다. 배열 수식이라서 그렇다고 하는데 엑셀 초짜인 저로써는 일단 그렇구나 하고 넘어가겠습니다.

 

 

나머지 차량 브랜드의 최대값도 구해지도록 D2 셀을 아래로 드래그하겠습니다.

 

 

6. MIN과 IF 함수를 사용하여 현대차의 최저 금액을 구합니다. 

 

=MIN(IF(Sheet1!A:A=Sheet2!A2, Sheet1!B:B))

 

마찬가지로 위 수식을 완성한 후에 Ctrl + Shift + Enter를 입력해야 합니다.

 

 

 

 

7. 이번에는 MEDIAN과 IF 함수를 활용하여 현대차의 중앙값을 구해보겠습니다. 

 

=MEDIAN(IF(Sheet1!A:A=Sheet2!A2, Sheet1!B:B))

 

마찬가지로 위 수식을 완성한 후에 Ctrl + Shift + Enter를 입력해야 합니다.

 

 

 

 

8. 이제 마지막으로 STDEV.P와 IF 함수를 활용하여 현대차의 표준편차를 구해보겠습니다. 

 

=STDEV.P(IF(Sheet1!A:A=Sheet2!A2, Sheet1!B:B))

 

마찬가지로 위 수식을 완성한 후에 Ctrl + Shift + Enter를 입력해야 합니다.

 

 

 

 

 

오늘은 엑셀에서 개수, 평균, 최대값, 최소, 중앙값, 표준편차와 같은 기술 통계량을 정리하는 방법에 대해서 살펴봤습니다. 제가 한 방법보다 더 나은 방법이 있다면 댓글로 알려주시면 감사하겠습니다 ㅎㅎ