[엑셀, 어렵지 않다 XIII] 피하지 말고 친해져야 할 데이터베이스의 함수들

[호영]

봄기운이 완연하다. 어디 숨었다 나오는지 도로를 달리다 보면 여기저기서 봄 꽃들이 얼굴을 내밀고 있다. 함수는 엑셀의 꽃이라 할 수 있다. 어디 숨었는지 모르지만 이것 저것 기능을 사용할라 치면 함수가 튀어나온다. 올해 들어서부터, 그러니까 지지난 시간부터 우리는 데이터베이스에 대한 공부를 하고 있다. 지난 시간에는 필요한 것만 걸러내는 필터링에 대해 배웠는데 필터링의 조건이 되는 함수들을 어렴풋이 기억하고 있으리라 믿는다. 어려운 건 없었다. ~보다 큼, ~보다 같거나 작음, 이런 식의 함수들이었으니 말이다. 내친 김에 이번 시간에는 데이터베이스의 함수들에 대해 짚고 넘어가기로 하자.

데이터베이스 함수를 소개합니다.

언뜻 보기만 해도 어려워 보이는 함수들이다. 심지어 수학시간에도 고개를 돌리던 표준 편차니, 분산이니 하는 것들이 영어로 되어있으니 더 어렵게 느껴지는 것도 사실이다. 하지만 알아둘 것은 이름과 용도를 익히는 것뿐이다. 우리가 종이에 계산해가면 끙끙 앓기 위한 것이 아니라 필요에 따라 사용할 수 있는 함수들을 알아두자는 것이다.

12개의 워크 시트 함수
엑셀에는 데이터베이스 계산에 사용하는 12개의 워크 시트 함수가 있다. 위에 설명한 데이터베이스 함수는 그 중 하나이다. 12개를 다 나열하자면 다음과 같다.
추가 기능 및 자동화 함수, 큐브 함수, 데이터베이스 함수, 날짜 및 시간 함수, 공학 함수, 정보 함수, 재무 함수, 논리 함수, 조회 및 참조 함수, 수학 및 삼각 함수, 통계 함수
텍스트 함수
이 중에는 한번쯤은 들어본 것도 있다. 엑셀을 처음 시작했을 때 날짜 및 시간 함수나 간단한 계산에 사용하는 함수와 텍스트 함수에 대해 배웠을 테니 말이다. 이렇게 머리 아프게 많은 함수들이 있지만 오늘 우리가 익힐 함수는 그 중에서 데이터베이스 함수이다.

D로 시작하는 데이터베이스 함수들
자, 이제 본격적으로 데이터베이스 함수에 대해 알아보자. 줄여서 D함수라고 표시하는 함수들은 Database, Field, Criteria의 세 인수를 공통적으로 사용한다. 그 구문은 다음과 같다.

Dfunction(database, field, criteria)

Database는 데이터베이스를 구성하는 셀 범위이다. 엑셀에서 사용되는 데이터베이스는 레코드(행)와 필드(열)로 이루어진 관련 데이터들의 목록이다. 데이터베이스의 첫째 행에는 각 열의 레이블이 들어 있다.

Field는 어떤 필드가 함수에 사용되는지를 나타낸다. 데이터베이스의 첫 열에는 필드 레이블이 정의되어 있어야 한다. “나이” 또는 “수확량”처럼 열 레이블을 큰 따옴표로 묶은 텍스트로 Field인수를 지정하거나, 첫째 필드에 1, 둘째 필드에 2 등 필드 번호로 Field를 지정할 수 있다.

Criteria는 찾을 조건을 지정하는 부분이다. 함수는 찾을 조건 범위에 지정한 조건과 일치하는 정보를 구한다. 찾을 조건 범위에는 함수로 요약할 목록의 열 레이블을 포함한다. 찾을 조건 참조 영역은 A1:F2와 같은 셀 범위로 입력하거나 “Criteria”와 같은 범위 이름으로 입력한다.

무슨 말이지 전혀 모르겠다면 그냥 예제를 한번 살펴보자.

예제 1
다음은 과수원의 데이터베이스이다. 각 레코드에는 나무에 대한 정보가 들어있다. 데이터베이스(database)를 A5:E11로 정의하고 찾을 조건(criteria)은 A1:F3으로 정의한다.



1. 높이가 10피트보다 크고 16피트보다 작은 사과 나무 중 나이 필드에 숫자가 들어있는 사과 나무의 개수를 구해보자. DCOUNT는 숫자가 있는 셀의 개수를 계산하는 함수이다. 그러므로 식은 다음과 같다. DCOUNT(Database, “나이”, A1:F2) = 1
 


먼저 결과 값을 넣을 셀에 커서를 두고 위에 함수 마법사(빨간 동그라미가 쳐진 곳)를 클릭하면 위의 그림과 같이 함수 마법사 창이 나타난다. 범주 선택에서 데이터베이스를 선택한 후 DCOUNT를 선택한다. 그런 후 함수 인수에다 Database와 Field, Criteria를 각각 입력해주면 결과 값이 나온다. 
 


2. 높이가 10에서 16피트 사이인 사과 중 수익 필드에 값이 들어있는 사과 나무의 개수를 구하려면 데이터베이스에서 비어있지 않은 셀의 개수를 구하는 DCOUNTA를 이용한다. 식은 DCOUNTA(Database, “수익”, A1:F2) = 1 이다. 

3. 사과 나무와 배 나무의 최대 수익을 구하려면 데이터베이스에서 최대값을 반환하는 함수 DMAX를 사용한다. 식은 DMAX(Database, “수익”, A1:A3)= 105 이다. 
 


4. 높이가 10피트 이상인 사과 나무의 최소 수익을 구하라. 데이터베이스 항목 중에서 최소값을 반환하는 DMIN함수를 사용하면 된다. 식은 DMIN(Database, “수익”, A1:B2) = 75 이다.



5. 사과나무의 총 수익을 구하려면 전체의 합을 구하는 DSUM 함수를 이용한다. 식은 DSUM(Database, “수익”, A1:A2) = 225



6. 높이가 10에서 16피트 사이인 사과 나무의 총 수익은 DSUM(Database, “수익”, A1:F2”)이다. 5번에서 Criteria의 범위만 바꾼다. 



7. 높이가 10에서 16피트 사이인 사과 나무의 연 수확량의 곱을 구하라. 이 문제는 조건에 맞는 특정 레코드 필드의 값을 곱하는 함수인 DPRODUCT를 사용한다. 식은 DPRODUCT(Database, “수확량”, A1:F2) = 10



8. 높이가 10피트 이상인 사과 나무의 평균 수확량을 구하려면 DAVERAGE(Database, “수확량”, A1:B2) = 12 이다. 



9. 모든 나무들의 평균 나이는 DAVERAGE(Database, “나이”, Database) = 13 이다.

10. 데이터베이스의 데이터가 과수원 모집단의 한 표본일 때 사과와 배나무의 연 수확량에 대한 추정 표준 편차를 구하려면 DSTDEV(Database, “수확량”, A1:A3) = 2.97 이다. 



11. 데이터베이스의 데이터가 전체 모집단일 때 사과와 배나무의 연 수확량에 대한 실제 표준 편차는 DSTDEVP(Database, “수확량”, A1:A3) = 2.65 이다.

12. 데이터베이스의 데이터가 과수원 모집단의 한 표본일 때 사과와 배나무의 연 수확량에 대한 추정 분산은 DVAR(Database, “수확량”, A1:A3) = 8.8 이다. 



13. 데이터베이스의 데이터가 전체 모집단일 때 사과와 배나무의 연 수확량에 대한 실제 분산은 DVARP(Database, “수확량”, A1:A3) = 7.04 이다.

14. 데이터베이스에서 찾을 조건에 맞는 레코드가 하나인 경우 그 레코드를 추출하는 DGET의 경우 이 데이터베이스에 대입하면 여러 개의 레코드가 찾을 조건과 일치하므로 #NUM!오류값을 표시한다.

지금까지 사용한 함수를 활용하기 위해 다음 예제를 따라 해보자.

예제 2 



1. DSUM 함수
DSUM 함수는 database sum의 줄임말이다. 즉 데이터베이스 중에서 원하는 필드의 합계를 계산하는 함수이다. 물론 조건을 주고 그 범위 내에서만이다.

문제 1) 위에 구축된 데이터베이스 중에서 사원의 호봉합계는 얼마인가?
물론 그냥 SUM 함수를 이용해서 =SUM으로 계산해도 된다. 위의 데이터에서는 10명의 직원 중 사원이 4명이다. 그러므로 쉽게 찾아서 더하기를 할 수 있다. 그러나 만약 직원의 수가 100명, 1000명 이상이라면 어떻게 합을 구할 수 있을까? 그런 경우에 데이터베이스 함수를 이용할 수 있다.
순서대로 따라 해보자. 먼저 지난 시간에 배운 필터링 기능을 이용한다. 위의 데이터 밑에 직위와 사원을 복사해서 아래와 같이 만든다.

1) 일단 계산을 원하는 셀로 커서를 이동한다.
2) 상단 메뉴바에서 함수를 클릭한다. 

3) 함수마법사에서 범주를 데이터베이스로 선택하고 DSUM을 선택한다.



4) 함수 인수 창에 데이터베이스 전체와 “호봉”을 클릭하고 조건으로 나중에 복사해 만든 직원과 사원을 선택하면 값을 구할 수 있다. 



문제 2) 위의 데이터베이스 중에서 사원번호 1006번 이상의 부장의 수당의 합계는?
일단 필터링 부분에 조건이 되는 사원번호와 1006번 이상의 함수, 그리고 직위, 부장을 채워 넣는다. 이렇게 같은 행에 있으면 AND(그리고)의 조건이 된다. 그러니 사원번호가 1006이상이고, 직위가 부장인 경우의 수당합계가 계산된다.   



2. DAVERAGE 함수
D(Database) + AVERAGE 의 개념이다. 주어진 데이터베이스에서 원하는 필드의 평균을 계산하는 함수이다. 실제 문제를 풀어보도록 하자.

문제) 위의 데이터베이스에서 이름이 박씨이거나 직위가 대리인 사람의 평균 급여를 구하라.
위에서 했던 것과 마찬가지로 조건이 되는 항목들을 필터링 할 곳에 복사해두고 함수를 이용해 DAVERAGE를 구하면 된다.

 

지난 시간에 설명했던 것처럼 세로 방향은 OR의 조건이 된다. 박씨이거나 대리인 사람을 구하는 조건이 되는 것이다. 



3. DMAX 함수와 DMIN 함수
MAX 함수를 안다면 이제 DMAX의 의미는 자연스레 알 수 있을 것이다. 짐작한대로 의미는 구축된 데이터베이스 중에서 최대값을 구하는 함수이다. 또한 DMAX와 반대 개념인 DMIN은 구축된 데이터베이스 중에서 최소값을 구하는 함수이다.

문제) 위의 데이터베이스에서 최고 급여와 최소 급여는? 답은 3,750,000와 1,500,000이다. 물론 데이터의 숫자가 작다보니 딱 봐도 알겠지만 DMAX의 함수와 DMIN 함수를 이용하면 된다. 이 경우 Criteria에는 아무것도 없는 셀을 넣거나 데이터베이스 전체를 넣어도 된다.

4. DCOUNT 함수
DCOUNT는 조건에 맞는 숫자가 기록되어 있는 셀의 개수를 센다.
질문) 직위가 부장인 직원 중에서 수당이 1,000,000원 이상인 사람은 몇 명일까?
 


결과는 3이 나왔다. 직접 입력해보고, 결과를 확인해보자.

5. DCOUNTA 함수
DCOUNTA 함수는 조건에 맞는 숫자든 문자든 기록되어 있는 셀의 개수를 센다. 



6. DGET 함수
데이터베이스의 필드에서 찾을 조건과 일치하는 단일 값을 추출한다.
질문) 위의 데이터베이스에서 이름이 “엠씨몽”인 사람의 직위를 구하라. 
 


필터링 조건 항목에 찾는 이름을 넣고 다음과 같이 DGET함수를 실행하면 된다. 



생각보다 만만한 데이터베이스 함수
직접 사용해보고 나면 함수란 것도 별거 아닌 것 같이 느껴지기 마련이다. 어려운 수식이 나오는 것도 아니고 개념만 알면 바로 손쉽게 사용할 수 있다. 2007 엑셀에서는 행과 열의 이름을 키보드로 칠 필요도 없이 드래그와 클릭만으로도 이 모든 데이터베이스 함수들을 사용할 수 있다. 그러나 이전 버전에서는 수식창에 DGET(A3:F13, C3, A18:A19) 이렇게 정확한 구문을 써주어야 한다. 데이터베이스의 범위가 아주 넓을 경우에는 자주 사용하는 각 셀의 주소를 따로 메모해두고 사용하는 게 좋다. 자, 그럼 데이터베이스 함수와 친해져 보자.@