[엑셀팁] 조건에 맞는 최대,최소,평균 및 중간값 구하기(DMAX,DMIN,DAVG)

■ 지정한 조건에 맞는 값들에 대하여 개수 및 합 구하기 

 엑셀을 통해서 자료를 분석하다 보면 지정한 조건에 맞는 값을 구해야 하는 경우가 많이 있습니다. 조건에 맞는 개수(COUNT) 를 구하거나 조건에 맞는 값의합(SUM) 을 구해야 하는 경우가 있습니다. 이럴경우 엑셀에서는 개수를 구할때는 COUNTIF, COUNTIFS 를 사용하면 되고, 합을 구할때는 SUMIF, SUMIFS 를 사용하면 됩니다. 

* 참고글 :  [팁] 엑셀 SUMIF COUNTIF 무작정 따라하기 : 조건 검색 업무 활용하기(예제파일포함)

■ 지정한 조건에 맞는 값들에 대하여 최댓값, 최솟값, 평균값, 중간값 구하기 

 특정 조건을 충족 시키는 값에 대한 개수와 합은 엑셀에서 기본 함수를 제공하지만, 최댓값(최대), 최솟값(최소), 평균값, 중간값을 구하는 것은 조금 복잡합니다. 엑셀에서는 아래 2가지 방법을 이용하여 구할 수 있습니다. 

[방법1] 배열수식과 MAX, MIN, AVERAGE, MEDIAN  함수의 조합을 이용하여 조건을 충족하는 값들 구하기

 - 장점 : 배열에 대한 개념을 인식하면 다양한 응용으로 활용이 가능함. 조건을 거는 열의 순서가 관계없음. MEDIAN 및 기타 함수를 사용가능함.

 - 단점 : 배열을 쓰기 위해서는 배열 단축키(Ctrl + Shift + Enter) 를 매번 눌러줘야함.

[방법2] DMAX(Database MAX), DMIN(Database MIN), DAVERAGE(Database AVERAGE)  함수를 이용하여 조건을 충족하는 값들 구하기

 - 장점 : 배열에 대한 이해 없이 하나의 함수만으로 원하는 기능을 쓸수 있음

 - 단점 : 지정된틀에 의해서만 사용이 가능함. 데이터베이스의 개념이 이해하기 쉽지만 응용이 어려움

  -> 참고로 COUNTIF, SUMIF 대신에 사용이 가능한 DCOUNT, DSUM 함수도 엑셀에서 제공됩니다. 

< 예제 파일 >

조건에_맞는_최대_최소_평균구하기_예제.xlsx

 이제 하나씩 기능을 알아 보겠습니다. 

■ 예제 데이터 설명 

  국토부(국토교통부)의 서울시 아파트 및 연립주택 실거래가 매매내역(7월,8월,9월 합계) 입니다.  아파트나 연립주택을 매매하면 등기등록을 구청에 신고하게 되어 있습니다. 서울특별시에서 이 내역을 취합해서 매월 국토부 매매가격 공개 사이트에 데이타를 올려주고 있습니다. 충분한 데이터를 얻기 위해 7월, 8월, 9월 전체 데이터를 합하여 함수를 테스트 하였습니다. 

▲ 국토교통부 실거래가 공개 시스템 웹사이트 

* 참고글 : 2015/10/15 - [금융할인정보] - 국토부 실거래가로 매매가격 조회(공덕동 래미안 아파트)

▲ 데이터 예제 : 데이터 분석을 위해 거래지역(구), 전용면적, 거래금액, 건축년도으로 편집함

  

■ 방법 1 : 배열수식과 기존 함수의 이용 방법 

  1. 조건을 만족하는 최댓값 구하기 

  * 목적 : 거래지역(구) 별로 거래된 주거면적(평수 기준) 중 최솟값을 구함

   IF 조건을 만족하는 MAX 값을 구하는 것입니다. 거래구 전체(K열)에 대해서 강남구(Cell O2) 를 만족하는 평수에 대한 최대값 구하도록 수식을 입력합니다. 

   수식은  {=MAX(IF($K:$K=$O2,$L:$L,""))}   입니다.  여기서 주의할 점은 배열을 나타내는 중괄호  { } 입니다. 이부분은 입력 값을 배열로 인식하여 인수로 수식을 전달하는 과정입니다.  사용하는 방법은 일반 수식 =MAX(IF($K:$K=$O2,$L:$L,"")) 을 입력하고 키보드로  배열 단축키(Ctrl + Shift + Enter) 를 입력하면 앞뒤로 중괄호가 생기며 함수가 인식 됩니다. 

 정확하게 함수를 입력하셨다면 강남구 지역에서 거래된 내역중 최대 평수는 77.26 평을 구할 것입니다. 

▲ IF 와 MAX 의 조합으로 강남구 최대 평수를 구한 예제 

  2. 조건을 만족하는 최솟값 구하기 

  * 목적 : 거래지역(구) 별로 거래된 주거면적(평수 기준) 중 최솟값을 구함

   IF 조건을 충족하는 MIN 값을 구하는 것입니다. 거래구 전체(K열)에 대해서 강남구(Cell O2) 를 만족하는 평수에 대한 최솟값 구하도록 수식을 입력합니다. 

   수식은 MAX 와 비슷하고 MAX 대신 MIN 을 입력하시면  됩니다.  결과는  {=MIN(IF($K:$K=$O2,$L:$L,""))}   입니다.  여기서도 최댓값을 구할때 처럼 배열 구조를 사용하기 위해 중괄호  { } 를 꼭 입력하십시요. 

 정확하게 함수를 입력하시면 강남구 지역에서 거래된 내역중 최소 평수는 4.07 평을 구할 것입니다. 

▲ IF 와 MIN 의 조합으로 강남구 최소 평수를 구한 예제 

  3. 조건을 만족하는 평균값 구하기 

  * 목적 : 거래지역(구) 별로 거래된 주거면적(평수 기준)을 기준으로 평균값을 구함

   IF 조건을 충족하는 AVERAGE 값을 구하는 것입니다. 거래구 전체(K열)에 대해서 강남구(Cell O2) 를 만족하는 전체 내역의 평수 평균값을 구하도록 수익을 입력하십시요. 

   수식은 MAX, MIN 과 비슷하고 MAX 대신 AVERAGE 을 입력하시면  됩니다.  결과는  {=AVERAGE(IF($K:$K=$O2,$L:$L,""))}   입니다.  이번에도 배열 구조를 위한 중괄호  { } 를 꼭 입력 바랍니다. 

 정확하게 함수를 입력하면 강남구 지역에서 거래 내역 전체 평균은 25.64 입니다. 

▲ IF 와 AVG 의 조합으로 강남구 전체 평균 평수를 구한 예제 

 4. 조건을 만족하는 중간값 구하기 

  * 목적 : 거래지역(구) 별로 거래된 주거면적(평수 기준)을 기준으로 전체 내역 중 중간값을 구함

 중간값은 가운데 값을 의미합니다. 평균은 전체 합을 개수로 나누기 때문에 정확하게 중간이라고 볼수 없습니다. 아주 큰값 하나로 전체 평균이 오를 수 있고, 아주 작은 값 하나로 평균이 내려갈수 있지만, 중간값은 최대값과 최소값에 영향을 받지 않습니다. 중간값은 총 9개의 값일 경우  정 가운데인 5번째 값을 의미하기 때문입니다. 

 수식은 기존 MAX, MIN 들과 비슷하고 MEDIAN 을 입력하십시요. 결과는  {=MEDIAN(IF($K:$K=$O2,$L:$L,""))}  입니다.  배열 구조를 위한 중괄호  { } 를 잊지 마십시요. 

 정확하게 함수를 입력하면 강남구 지역에서 거래 내역 전체 평균은 25.58 입니다. 다른 행정구역(구)를 입력하실때면 위 수식대로 입력하시고 마우스를 입력해 드래그 하시면 수식 변경 없이 최대,최소,평균, 중간에 해당하는 값들을 계산하실 수 있습니다. 

▲ IF 와 MEDIAN 의 조합으로 강남구 전체 중 중간값의 평수를 구한 예제 

 5. 면적, 매매금액, 건축년도를 기준으로 동일한 수식 적용 

 기초 자료에는 거래면적, 매매금액, 건축년도 데이타가 있습니다. 동일한 방법으로 구별로 MAX,MIN,AVG,MEDIAN 값을 구했습니다. 전체 내역에 대해서 좀 더 명확하게 구별하기 위해서 '조건부 서식'내에 '색조'를 사용하였습니다. 최댓값, 최솟값에 대하여 한눈에 인식하실 수 있습니다. 

* 참고글 :  [팁] 엑셀 표 스타일 적용해 데이터 분석 및 꾸미기

▲ 서울지역 아파트 거래내역 데이터 분석 결과

■ 방법 2 : 데이터베이스 기준 함수를 이용하여 값 구하기 

 1. DMAX 로 조건을 만족하는 최댓값 구하기 

  * 목적 : 강남구 거래내역 중 최대값 찾기

  * 함수 정의 : DMAX(database, field, criteria) 

  - database : 데이터 베이스나 목록을 지정할 값으로 필수 입력 요소입니다. 

  - field : 함수에서 지정할 값으로 1은 첫번째열, 2는 두번째열... n 은 n 번째 열입니다. 

  - criteria : 지정한 조건을 나타내는 열배열로 첫번째는 열레이블(헤더값)이고 아래는 열값(조건 값)을 나타냅니다. 

  * 입력 값 은 =DMAX($K:$L,2,Q$31:Q$32) 입니다. 조금 전 '방법 1' 에서는 행정구역(구)들을 새로로 나열하였습니다. 하지만 DMAX 에서는 열에이블(거래구)와 열 조건값(강남구)가 세로로 있을때만 함수 사용이 가능합니다. 결국 구 이름들을 옆으로 나열해야 합니다. '방법 1' 에서 구할때와 달리 열과 행이 서로 바뀌어야만 결과를 얻을 수 있습니다. 

▲ DMAX로 조건에 맞는 최대값 구하기 

 2. DMIN 로 조건을 만족하는 최솟값 구하기 

  * 목적 : 강남구 거래내역 중 최소값 찾기

 DMIN 도 DMAX 와 함수 정의가 동일합니다. DMAX 대신 DMIN 을 사용하시며 됩니다.

 입력값은 =DMIN($K:$L,2,Q$31:Q$32) 입니다. 

▲ DMIN로 조건을 충족하는 최소값 구하기 

3. DAVERAGE 로 조건을 만족하는 평균값 구하기 

  * 목적 : 강남구 거래내역 중 평균값 계산하기

 DAVERAGE 도 DMAX, DMIN  와 함수 정의가 동일합니다. DMIN  대신 DAVERAGE 를 사용하십시요. 

 입력값은 =DAVERAGE($K:$L,2,Q$31:Q$32)  입니다. 

▲ DAVERAGE로 조건을 충족하는 값들의 평균 구하기 

4. 전체 내역 조회 결과 

 함수를 제대로 사용하셨다면 함수를 드래그 하여 모든 구에 대해서 동일하게 최대, 최소, 평균에 해당하는 값을 구하실수 있을 것입니다. 

■ 정리하며

 방법1 과 방법2 모두 기능은 동일하고 동일한 값을 산출해 줍니다. 하지만, 개인적인 능력에 따라 선호하는 방법이 틀릴 것입니다. 저는 자유도가 높은 첫번째 방법을 선호합니다. 하지만 전체 내역이 아닌 특정 조건에 맞는 한두개의 값만을 구하는 경우 두번째 방법이 편합니다. 

 자신에 이해하고 활용하기 편한 방법으로 잘 활용하시길 바랍니다. 

 도움이 되셨다면 아래 Facebook "좋아요" 버튼을 눌러주세요!! 큰힘이 됩니다.