DAVERAGE함수, DMAX함수, DMIN 함수

전체 목차

 - 001. SUMIF 함수, SUMIFS 함수, COUNTIF 함수, COUNTIFS 함수

 - 002. MATCH 함수, INDEX 함수 

 - 003. 엑셀 이동 단축키, 채우기 핸들, 수식 채우기 팁

 

포스트 목차.

• 01. 엑셀에서의 데이터베이스
• 02. DAVERAGE 함수, DMAX 함수, DMIN 함수의 인자
• 03. DMAX 함수 사용 예제
• 04. DMIN 함수 사용 예제

 

본 포스트에서는 엑셀에서 셀영역을 데이터베이스처럼 사용하는 데이터베이스 관련 함수를 소개한다.

 

01. 엑셀에서의 데이터베이스

데이터 베이스는 데이터의 공통된 특징을 공유하는 필드와 여러 필드가 모인 하나의 데이터를 의미하는 레코드로 구성된다.

 

예를 들어 아래와 같은 데이터가 있다고 가정해보자.

 

- 필드: 이 데이터는 소속, 이름, 이명, 키, 나이, 현상금, 인기순위라는 필드를 가지고 있다.  필드의 맨위의 값은 필드명이라고 한다.

- 레코드: 밀짚모자 일당 소속, 몽키 D. 루피가 이름이고, 이명으로 밀짚모자라 불리는, 키 174, 나이 19, 현상금 30억, 인기순위 1인 개별적인 데이터를 레코드라고 한다.

 

위의 데이터베이스는 7개의 필드와 27개의 레코드로 구성되어 있다고 볼 수 있다.

 

02. DAVERAGE 함수, DMAX 함수, DMIN 함수의 인자

위에서 정의한 데이터베이스를 이용한 여러 통계 함수가 존재한다. 본 포스트에서는 일단 다음 함수들을 소개한다.

  - 평균: DAVERAGE

  -  최대값: DMAX

  -  최소값: DMIN

 

위의 함수는 공통적으로 아래 세가지 인자를 갖는다. DAVERAGE 함수를 기준으로 알아보자.

DAVERAGE 함수의 인자

 

- Database:데이터 베이스로 해독 가능한 영역을 의미한다.필드와 레코드가 있는 영역을 선택하면된다. 가장 첫 번째 행의 데이터는 필드명이 된다는 것을 기억하자.

- Field: 통계연산의 대상이 되는 필드명 또는 필드명이 저장되어 있는 셀을 선택하면 된다.  (본 포스트에서 소개할 함수는 DAVERAGE 함수, DMAX 함수, DMIN 함수이기 때문에 여기서 통계연산이라 함은 평균, 최대값, 최소값을 의미한다.)

- Criteria: Criteria는 필드명 + 조건으로 구성되어 있는 영역을 의미한다. 다양한 활용법이 있기 때문에 좀 더 자세히 다루려고 한다.

 

 

 

Criteria의 입력 형태

Criteria로 지정하는 영역 또한 데이터베이스의 형태를 갖춰야 한다.

 

(1) 관심필드와 관심값이 하나 일 때

예를들어 위에 예시로 든 데이터에서 밀짚모자 일당의 현상금의 평균값을 구한다고 생각해보자.

이 말은 다음과 같이 바꿀 수 있다.

 

소속이 밀짚모자 일당인 레코드들의 평균 현상금

 

 

= DAVERAGE (A1:G28, L1, J1:J2)

  - Database: 데이터가 있는 영역을 지정하고, 맨 윗줄의 행은 필드명이 된다.

  - Field: 평균 현상금을 구해야 하므로 "현상금"이 저장되어 있는 L1을 입력한다.

  - Criteria: 필드명, 관심있는 값으로 구성되 있는 영역을 지정한다. (소속필드가 밀짚모자 일당인 레코드만 통계연산의 대상으로 하겠다는 의미이다.) Criteria도 데이터베이스와 유사한 형태를 가져야 한다. 소속이라는 필드에서 밀짚모자 일당에 관심이 있다는 뜻으로 해석하면 된다. 

 

Criteria의 레코드는 비교 연산자  + 기준값으로 구성되어야 하지만, 연산자가 "="일 경우 생략가능하다.

 

결과 값은 다음과 같다.

실제로 밀집 모자 일당 현상금의 평균값은 881,600,100이다. 

 

 

(2) 관심필드는 한 개 인데 관심값이 여러개 일 때 (OR연산)

 

예를들어 위에 예시로 든 데이터에서 밀짚모자 일당 또는 흰 수염 해적단의 현상금의 평균값을 구한다고 생각해보자.

이 말은 다음과 같이 바꿀 수 있다.

 

소속이 밀짚모자 일당 또는 흰 수염 해적단인 레코드들의 평균 현상금

 

(1)에서 소개한 예제에서 criteria 인자의 레코드가 하나 추가된 것으로 볼 수 있다.  이런식으로 레코드가 추가된 경우, 여러 레코드의 조건은 OR 연산이 된다. 

 

함수의 실핼 결과는 다음과 같다.

 실제로 밀집 모자 일당 또는 흰 수염 해적단인 레코드의   현상금 평균값은 895,600,083이다. 

 

 

(3) 관심필드가 여러 개 일때 (AND 연산)

 

예를들어 위에 예시로 든 데이터에서 밀짚모자 일당 중에서 키가 180을 넘는 사람들의 평균 나이를 구한다고 생각해보자.

이 말은 다음과 같이 바꿀 수 있다.

 

소속이 밀짚모자 일당이면서 키가 180을 초과하는 레코드의 평균 나이

 - Database: 데이터가 있는 영역을 지정하고, 맨 윗줄의 행은 필드명이 된다.

  - Field: 평균 나이를 구해야 하므로 "나이"가 저장되어 있는 L5을 입력한다.

  - Criteria: 필드명 소속, 필드명 키로 구성된 데이터 베이스가 입력된다. 하나의 레코드로 소속: 밀짚모자 일당, 키: ">180"이 입력되어 있다. 

같은 레코드 안에 있는 데이터는 AND 연산을 한다고 생각하면된다. 밀짚모자 일당이면서 키 필드에 저장되어 있는 데이터 >180 을 만족하는 나이 필드의 데이터가 통계 연산의 대상이 된다. 

 

결과 값은 44.6이며, 실제로 180을 초과하는 밀짚모자 일당 레코드의 나이  평균도 44.6이다.

 

 

(4) 관심필드 및 관심 값이 여러 개 일때 (OR연산과 AND 연산)

 

예를들어 위에 예시로 든 데이터에서  조건1 또는 조건2에 해당하는 레코드의 평균 키를 구하자

   조건1) 밀짚모자 일당이면서, 1억<현상금<5억 조건을 만족,

   조건2) 이명이 해적 여제를 만족

 

Criteria만 알아보자. Criteria의 첫 레코드는 소속 = 밀짚모자 일당 AND 현상금 < 500,000,000 AND 현상금 > 100,000,000을 나타낸다. Criteria의 두번째 레코드는 이명 = 해적여제를 나타낸다. 두 개의 레코드 사이는 OR연산이 적용되므로 키의 평균은 다음과 같이 계산된다.

 

실제로 계산한 평균값도 219.5이다.

 

(5) 와일드 카드 문자 (? 와  *)의 사용

비교 연산시에 와일드 카드 문자 사용이 가능하다. 이 때는 ="=비교값"  또는 '=비교값 으로 셀을 작성하기 바란다.

 

*문자는 0번 또는 N번 지정하지 않은 문자가 등장함을 의미한다.

?문자는 1번 지정하지 않은 임의의 문자가 등장함을 의미한다.

~* 또는 ~?: 실제로 *, ?를 데이터로 사용하고 싶을 때는 앞에 물결 문자를 추가한다.

 

예르 들어  Criteria를 다음과 같이 구성하면

 위의 결과대로 데이터를 특정할 수 있을 것이다. 

 

03. DMAX 함수 사용 예제

DAVEAGE 함수는 위에서 Criteria를 설명하며 많이 다뤘기 때문에 DMAX함수 사용예제를 소개하려고 한다.

이름이 "스"로 끝나는 레코드의 키 중에 최대값을 구해보자

=-

=DMAX(A1:G27, N14, I14:I15)

 - Database: 데이터가 있는 영역을 지정하고, 맨 윗줄의 행은 필드명이 된다.

  - Field: 키의 최대값을 구해야 되므로, 키가 저장되어 있는 N14지정

  - Criteria: 이름이 "스"로 끝나는 데이터를 선택해야 하므로 I14:I15 지정

 

 

 

 

함수의 결과는 다음과 같다.

 

 

샹크스와 포트거스 D. 에이스 의 키 중에서 큰 값인 199가 선택되었음을 알 수 있다.

 

 

04. DMIN 함수 사용 예제

이번에는 DMIN 함수를 알아보자. 이름이 야마토로 끝나는 레코드의 키의 최소값을 구해보자.

 

=DMIN(A1:G27, N20, I20:I21)

 - Database: 데이터가 있는 영역을 지정하고, 맨 윗줄의 행은 필드명이 된다.

  - Field: 키의 최소값을 구해야 되므로, 키가 저장되어 있는 N20지정

  - Criteria: 이름이 "야마토"로 끝나는 데이터를 선택해야 하므로 I20:I21 지정

 

비교연산은 = *야마토 이므로 이름이 야마토로 끝나는 야마토가 선택되고 데이터가 하나 뿐이지만 키의 최소값은 야마토의 키인 263이 된다.

 

실제 데이터도 263이다.