데이터베이스 함수의 기본 개념과 DCOUNTA, DGET으로 개수 및 단일 값 추출을, 2부에서 DSUM, DAVERAGE, DMAX, DMIN으로 조건부 집계를 배웠습니다. 이제 데이터베이스 함수의 활용 범위를 더욱 넓혀, 특정 조건을 만족하는 데이터에 대한 통계적 분석(개수, 곱, 표준 편차, 분산)을 수행하는 방법을 알아볼 차례입니다.
이번 '엑셀 데이터베이스 함수 마스터하기' 시리즈 3부에서는 DCOUNT, DPRODUCT, DSTDEV, DVAR 함수를 집중적으로 다룰 예정입니다. 이 함수들은 데이터의 분포와 특성을 조건부로 파악하는 데 유용하며, 복잡한 통계 분석을 위한 전처리 단계에서 강력한 도구가 됩니다. 엑셀 데이터 분석 역량을 한 단계 더 끌어올려 봅시다!
왜 데이터베이스 조건부 통계 함수를 알아야 할까요?
비즈니스 분석에서는 단순히 합계나 평균을 넘어, 데이터의 퍼짐 정도(분산, 표준 편차), 개수(숫자 데이터만), 또는 특정 값들의 곱을 조건부로 파악해야 할 때가 많습니다. 예를 들어:
- "특정 기간 동안 특정 생산 라인에서 불량률이 발생한 횟수(숫자만 해당)는 몇 건인가?"
- "영업 팀 A의 신입 사원들 실적의 변동성은 어느 정도인가?"
- "특정 프로모션 기간 동안 제품 X의 일별 성장률 곱은 얼마인가?"
이러한 질문들은 데이터의 통계적 특성을 조건부로 분석해야 답할 수 있습니다.
데이터베이스 통계 함수들은 '조건 범위'의 유연성을 바탕으로 이러한 복잡한 분석을 간결하게 수행할 수 있도록 도와줍니다.
주요 함수들
다음 데이터베이스 함수들을 실무 예제와 함께 자세히 살펴보겠습니다.
- DCOUNT: 지정된 조건에 맞는 데이터베이스의 열에서 숫자의 개수 계산
- DPRODUCT: 지정된 조건에 맞는 데이터베이스의 열에서 숫자의 곱 계산
- DSTDEV: 지정된 조건에 맞는 데이터베이스의 열에서 표본 표준 편차 계산
- DVAR: 지정된 조건에 맞는 데이터베이스의 열에서 표본 분산 계산
실무 예제로 배우는 엑셀 함수 마스터하기
각 함수를 실제 비즈니스 시나리오에 적용하여 데이터베이스 함수의 강력한 조건부 통계 분석 능력을 보여드리겠습니다.
1. DCOUNT: 조건에 맞는 숫자 셀의 개수 세기
DCOUNT 함수는 지정된 조건에 맞는 데이터베이스의 열에서 숫자가 포함된 셀의 개수를 셉니다. (DCOUNTA가 숫자, 텍스트 등 비어 있지 않은 모든 셀을 세는 것과 다릅니다.)
- 기본 형식: =DCOUNT(데이터베이스, 필드, 조건)
- 데이터베이스: 전체 데이터 범위 (열 머리글 포함).
- 필드: 개수를 셀 열의 이름(텍스트) 또는 열 번호. 반드시 숫자 데이터가 있는 열이어야 합니다.
- 조건: 조건을 지정하는 셀 범위 (열 머리글 포함).
실무 예제: 우리 회사의 생산 과정별 불량률 데이터가 A1:D9 범위에 있습니다.
| 공정 단계 | 제품 ID | 불량 여부(1:불량,0:정상) | 불량율(%) |
| 검수 A | P-001 | 1 | 2.5 |
| 검수 B | P-002 | 0 | 0 |
| 조립 A | P-003 | 1 | 1.8 |
| 검수 A | P-004 | 0 | 0 |
| 조립 B | P-005 | 1 | 3.2 |
| 검수 B | P-006 | 1 | 1.5 |
| 조립 A | P-007 | 0 | 0 |
| 검수 A | P-008 | 1 | 2.0 |
조건 범위 설정: F1:G2 셀에 조건을 입력합니다. F1에 공정 단계, G1에 불량 여부 (1: 불량, 0: 정상) F2에 검수 A, G2에 1
예제 목표: '검수 A' 단계에서 불량(불량 여부가 1)이 발생한 제품의 불량률 데이터 개수를 세세요.
해결 방법:
- 조건 범위 생성: F1:G2에 위와 같이 조건을 입력합니다.
- H2 셀에 다음 수식을 입력합니다. =DCOUNT(A1:D9, "불량률 (%)", F1:G2)
결과: 3 (P-001, P-003(조립A지만 불량여부 1이라 공정단계 조건이 다름), P-008의 불량률 데이터를 셈)
- 주의: '불량 여부'와 '불량률' 열 모두 숫자이므로 필드 인수에 둘 중 아무거나 넣어도 '숫자가 있는 셀의 개수' 자체는 동일하게 나옵니다. 하지만 의미를 명확히 하기 위해 '불량률 (%)'을 넣었습니다. DCOUNT는 불량 여부 필드의 개수를 세도 동일한 3이라는 결과가 나옵니다.
최상급 실무 활용: DCOUNT는 '특정 기준을 충족하는 유효한 숫자 데이터의 개수', '특정 기간 동안 발생한 거래 건수(수량이 숫자인 경우)', '특정 설문조사 항목에 대한 숫자 응답 개수' 등 조건에 맞는 숫자 데이터의 수를 파악할 때 유용합니다.
COUNTA와 COUNT의 조건부 버전이라고 할 수 있으며, 특히 OR 조건이 포함된 숫자 개수 계산 시 강점을 보입니다.
2. DPRODUCT: 조건에 맞는 숫자들의 곱 계산
DPRODUCT 함수는 지정된 조건에 맞는 데이터베이스의 열에서 숫자 값들의 곱을 계산합니다.
- 기본 형식: =DPRODUCT(데이터베이스, 필드, 조건)
실무 예제: 특정 프로젝트의 월별 성장률 데이터가 있습니다. 특정 분기 동안의 누적 성장률을 계산해야 합니다.
| 프로젝트 | 월 | 월별 성장률 (소수점) |
| A | 1월 | 1.05 |
| A | 2월 | 1.08 |
| A | 3월 | 1.10 |
| A | 4월 | 1.03 |
| B | 1월 | 1.02 |
| B | 2월 | 1.07 |
조건 범위 설정: E4:E5 셀에 조건을 입력합니다. E4에 월 E5에 1월 (2월, 3월도 각각 다른 행에 입력하여 OR 조건 생성)
예제 목표: '1월, 2월, 3월'에 해당하는 '월별 성장률'의 곱을 계산하여 1분기 누적 성장률을 파악하세요.
해결 방법:
- 조건 범위 생성: E4에 월, E5에 1월, E6에 2월, E7에 3월을 입력합니다.
- H4 셀에 다음 수식을 입력합니다. =DPRODUCT(A1:C7, "월별 성장률 (소수점)", E4:E7)
결과: 약 1.2474 (1.05 * 1.08 * 1.10)
최상급 실무 활용: DPRODUCT는 '누적 성장률', '복리 이자율 적용 시 총 자산 증가율', '다단계 생산 과정의 총 수율' 등 특정 조건 하에서 값들의 곱을 계산할 때 사용됩니다. 일반적인 PRODUCT 함수로는 조건부 계산이 어렵기 때문에, DPRODUCT는 이러한 특정 시나리오에서 매우 유용합니다.
3. DSTDEV: 조건에 맞는 데이터의 표본 표준 편차 계산
DSTDEV 함수는 지정된 조건에 맞는 데이터베이스의 열에서 표본의 표준 편차를 계산합니다. (모집단 표준 편차는 DSTDEVP)
- 기본 형식: =DSTDEV(데이터베이스, 필드, 조건)
실무 예제: 위 생산 불량률 데이터(A1:D9)를 사용합니다.
조건 범위 설정: F9:F10 셀에 조건을 입력합니다. F9에 공정 단계 F10에 검수 B
예제 목표: '검수 B' 단계의 제품들에서 불량률의 표준 편차를 계산하여 변동성을 파악하세요.
해결 방법:
- 조건 범위 생성: F9:F10에 위와 같이 조건을 입력합니다.
- H9 셀에 다음 수식을 입력합니다. =DSTDEV(A1:D9, "불량률 (%)", F9:F10)
결과: 약 1.06066 (검수 B의 불량률은 0%, 1.5%이므로 표준편차 계산)
최상급 실무 활용: DSTDEV는 '특정 생산 라인의 품질 일관성', '신입 사원 그룹의 판매 실적 변동성', '특정 기간 동안의 주식 가격 변동성' 등 조건부로 데이터의 퍼짐 정도(위험도, 일관성)를 측정할 때 사용됩니다. 이는 데이터의 안정성을 평가하고, 개선이 필요한 영역을 식별하는 데 중요한 통계적 지표를 제공합니다.
4. DVAR: 조건에 맞는 데이터의 표본 분산 계산
DVAR 함수는 지정된 조건에 맞는 데이터베이스의 열에서 표본의 분산을 계산합니다. (모집단 분산은 DVARP)
- 기본 형식: =DVAR(데이터베이스, 필드, 조건)
실무 예제: 위 생산 불량률 데이터(A1:D9)를 사용합니다.
조건 범위 설정: F12:F13 셀에 조건을 입력합니다. F12에 공정 단계 F13에 조립 A
예제 목표: '조립 A' 단계의 제품들에서 불량률의 분산을 계산하여 변동성을 파악하세요.
해결 방법:
- 조건 범위 생성: F12:F13에 위와 같이 조건을 입력합니다.
- H12 셀에 다음 수식을 입력합니다. =DVAR(A1:D9, "불량률 (%)", F12:F13)
결과: 약 0.0162 (조립 A의 불량률은 1.8%, 0%이므로 분산 계산)
최상급 실무 활용: DVAR는 DSTDEV와 함께 조건부 데이터의 산포도를 평가하는 데 사용됩니다. 표준 편차가 실제 데이터와 동일한 단위를 가지는 반면, 분산은 계산 과정에서 사용되는 원시적인 퍼짐 정도의 척도입니다. 품질 관리, 공정 최적화, 위험 관리 등 다양한 분야에서 특정 조건 하의 데이터 변동성을 분석하고, 이상치를 탐지하는 데 기여합니다.
DCOUNT, DPRODUCT, DSTDEV, DVAR 함수를 통해 특정 조건을 만족하는 데이터의 개수, 곱, 표준 편차, 분산을 계산하는 방법을 살펴보았습니다.
이 함수들은 OR 조건을 포함하거나 복잡한 기준 하에서 데이터의 통계적 특성을 분석하는 데 강력한 유연성을 제공합니다.