데이터를 분석할 때, 단순히 합계나 평균을 아는 것을 넘어 데이터가 어떻게 분포되어 있는지, 특정 값이 전체 데이터에서 어느 정도의 위치를 차지하는지 이해하는 것은 매우 중요합니다.
이 데이터의 분포와 상대적인 위치를 파악하는 데 필수적인 함수들을 집중적으로 다룰 예정입니다. 이 함수들을 통해 데이터에 숨겨진 패턴과 특이점을 발견하고, 더욱 깊이 있는 통찰력을 얻을 수 있습니다.
왜 데이터 분포 및 순위 함수를 알아야 할까요?
수많은 데이터 속에서 의미 있는 정보를 찾아내려면, 각 데이터 포인트가 전체 집합 내에서 어떤 의미를 가지는지 파악하는 것이 중요합니다.
예를 들어, "우리 팀에서 가장 높은 성과를 낸 직원은 누구인가?", "특정 가격대의 제품은 얼마나 팔렸는가?", "이번 시험 점수는 상위 몇 퍼센트에 해당하는가?"와 같은 질문에 답하려면 데이터의 빈도, 순위, 백분위수 등을 분석해야 합니다.
이 함수들은 데이터에 대한 우리의 이해를 심화시키고, 더욱 전략적인 의사결정을 가능하게 합니다.
주요 함수들
다음 함수들을 실무 예제와 함께 자세히 살펴보겠습니다.
- FREQUENCY: 특정 값의 빈도 분포 계산
- RANK.EQ, RANK.AVG: 데이터 내에서 값의 순위 계산 (동점 처리 방식 차이)
- PERCENTILE.EXC, PERCENTILE.INC: 데이터 집합에서 특정 백분위수에 해당하는 값 찾기
- QUARTILE.EXC, QUARTILE.INC: 사분위수 계산
- MODE.SNGL, MODE.MULT: 최빈값 (가장 자주 나타나는 값) 계산
- MEDIAN: 중앙값 (데이터를 정렬했을 때 가운데 위치하는 값) 계산
- LARGE, SMALL: 특정 번째로 크거나 작은 값 찾기
실무 예제로 배우는 엑셀 함수 마스터하기
각 함수를 실제 비즈니스 시나리오에 적용하여 데이터 기반의 의사결정을 지원하는 방법을 보여드리겠습니다.
1. FREQUENCY: 데이터 빈도 분석의 핵심
FREQUENCY 함수는 데이터 배열 내에서 각 값의 발생 빈도(개수)를 계산합니다.
이는 배열 함수이므로, 결과를 표시할 셀 범위를 미리 선택하고 Ctrl + Shift + Enter로 입력해야 합니다.
- 기본 형식: =FREQUENCY(데이터_배열, 구간_배열)
- 데이터_배열: 빈도를 셀 숫자 데이터 집합
- 구간_배열: 데이터를 그룹화할 구간(범위)을 정의하는 숫자 집합
실무 예제: 아래와 같이 고객 만족도 설문조사 점수(100점 만점)가 B2:B11 셀에 있다고 가정해봅시다.
| 고객 ID | 만족도 점수 |
| 001 | 75 |
| 002 | 82 |
| 003 | 68 |
| 004 | 91 |
| 005 | 70 |
| 006 | 88 |
| 007 | 95 |
| 008 | 65 |
| 009 | 78 |
| 010 | 80 |
예제 목표: 만족도 점수를 60점대, 70점대, 80점대, 90점대로 그룹화하여 각 점수대의 고객 수를 파악하세요.
해결 방법:
- 구간 설정: D2:D5 셀에 구간 경계를 입력합니다. (예: 69, 79, 89, 99)
- 69: 60점대 (60~69)
- 79: 70점대 (70~79)
- 89: 80점대 (80~89)
- 99: 90점대 (90~99)
- 결과 셀 선택: E2:E5 셀을 선택합니다 (결과가 표시될 4개 셀).
- 수식 입력: 선택된 셀에 다음 수식을 입력합니다. =FREQUENCY(B2:B11, D2:D5)
- 배열 수식 완료: Ctrl + Shift + Enter를 누릅니다.
결과:
| 구간 | 경계빈도 |
| 69 | 2 |
| 79 | 3 |
| 89 | 3 |
| 99 | 2 |
최상급 실무 활용: 고객 만족도, 직원 성과 점수, 제품 판매 가격대 등 다양한 데이터의 분포를 한눈에 파악하여 주요 그룹을 식별할 수 있습니다.
히스토그램을 그리는 데 필수적인 데이터 전처리 과정이며, 마케팅 전략 수립 시 특정 가격대 제품의 수요를 예측하거나 교육 프로그램 효과 분석 시 점수대별 분포 변화를 관찰하는 데 활용됩니다.
2. RANK.EQ, RANK.AVG: 데이터 순위 매기기
RANK.EQ는 동점일 경우 모두 같은 순위를 부여하고 다음 순위를 건너뜁니다 (예: 1등, 1등, 3등). RANK.AVG는 동점일 경우 해당 순위들의 평균을 부여합니다 (예: 1등, 1등, 2.5등).
- 기본 형식: =RANK.EQ(숫자, 참조, [내림차순_여부]) / =RANK.AVG(숫자, 참조, [내림차순_여부])
- 숫자: 순위를 구할 값
- 참조: 순위를 비교할 숫자 목록이 있는 범위 (절대 참조 $)
- 내림차순_여부: 0 또는 생략 시 내림차순(높은 값이 1위), 1 또는 다른 값 입력 시 오름차순(낮은 값이 1위)
실무 예제: 아래와 같은 분기별 영업 사원 판매 실적 데이터가 A2:B6 셀에 있다고 가정해봅시다.
| 영업 사원 | 판매 실적 |
| 김영업 | 120 |
| 이영업 | 150 |
| 박영업 | 120 |
| 최영업 | 180 |
| 정영업 | 100 |
예제 목표: 각 영업 사원의 판매 실적에 대한 순위를 계산하세요. (높은 실적이 1위)
- 동점일 경우 같은 순위 부여 (RANK.EQ)
- 동점일 경우 평균 순위 부여 (RANK.AVG)
해결 방법:
- C2 셀에 RANK.EQ 수식을 입력하고 아래로 채웁니다. =RANK.EQ(B2, $B$2:$B$6, 0)
- D2 셀에 RANK.AVG 수식을 입력하고 아래로 채웁니다. =RANK.AVG(B2, $B$2:$B$6, 0)
결과:
| 영업사원 | 판매실적 | RANK.EQ | RANK.AVG |
| 김영업 | 120 | 3 | 3.5 |
| 이영업 | 150 | 2 | 2 |
| 박영업 | 120 | 3 | 3.5 |
| 최영업 | 180 | 1 | 1 |
| 정영업 | 100 | 5 | 5 |
최상급 실무 활용: 직원 성과 평가, 제품 판매 순위, 시험 성적 등 데이터의 상대적 위치를 파악하는 데 필수적입니다.
특히, 동점 처리 방식에 따라 순위가 달라질 수 있으므로, 보고서의 목적에 맞는 함수를 선택하는 것이 중요합니다. 인센티브 지급 기준, 제품 프로모션 대상 선정 등에 직접적으로 활용될 수 있습니다.
3. PERCENTILE.EXC, PERCENTILE.INC: 백분위수 이해하기
PERCENTILE 함수는 데이터 집합에서 특정 백분위수에 해당하는 값을 반환합니다.
EXC는 0과 100 백분위수를 제외하고, INC는 포함합니다 (대부분의 경우 INC 사용).
- 기본 형식: =PERCENTILE.EXC(배열, k) / =PERCENTILE.INC(배열, k)
- 배열: 백분위수를 구할 숫자 데이터 집합
- k: 0에서 1 사이의 백분위수 값 (예: 0.1은 10분위수, 0.9는 90분위수)
실무 예제: 위 고객 만족도 점수 데이터(B2:B11)를 사용하여:
예제 목표:
- 고객 만족도 점수의 상위 10%에 해당하는 점수 (90분위수)
- 고객 만족도 점수의 하위 25%에 해당하는 점수 (25분위수)
해결 방법:
- 상위 10% 점수 (90분위수): =PERCENTILE.INC(B2:B11, 0.9) → 93.1
- 하위 25% 점수 (25분위수): =PERCENTILE.INC(B2:B11, 0.25) → 70.75
최상급 실무 활용: 고객 분류, 성과 기준 설정, 데이터 이상치 탐지에 매우 유용합니다.
예를 들어, 상위 10% 고객에게 특별 프로모션을 제공하거나, 하위 25% 고객에게는 개선된 서비스를 제안하는 등 고객 세분화 전략에 활용할 수 있습니다. 또한, 시험 점수 분석 시 특정 백분위수에 해당하는 합격 커트라인을 설정하는 데도 사용됩니다.
4. QUARTILE.EXC, QUARTILE.INC: 사분위수 계산
QUARTILE 함수는 데이터를 4등분(사분위수)하여 각 구간의 경계값을 반환합니다.
EXC는 0과 4분위수를 제외하고, INC는 포함합니다.
- 기본 형식: =QUARTILE.EXC(배열, quart) / =QUARTILE.INC(배열, quart)
- 배열: 사분위수를 구할 숫자 데이터 집합
- quart: 반환할 사분위수 값 (0, 1, 2, 3, 4)
- 0: 최솟값 (INC에만 해당)
- 1: 첫 번째 사분위수 (Q1, 25분위수)
- 2: 두 번째 사분위수 (Q2, 중앙값, 50분위수)
- 3: 세 번째 사분위수 (Q3, 75분위수)
- 4: 최댓값 (INC에만 해당)
실무 예제: 위 고객 만족도 점수 데이터(B2:B11)를 사용하여 각 사분위수를 계산하세요.
예제 목표: 고객 만족도 점수 분포를 사분위수로 나누어 파악합니다.
해결 방법:
- Q1 (25분위수): =QUARTILE.INC(B2:B11, 1) → 70.75
- Q2 (중앙값, 50분위수): =QUARTILE.INC(B2:B11, 2) → 79
- Q3 (75분위수): =QUARTILE.INC(B2:B11, 3) → 87.25
최상급 실무 활용: 데이터의 중심 경향성과 산포도를 빠르게 이해하는 데 유용합니다.
특히 상자 그림(Box Plot)을 그릴 때 필요한 값을 제공하여 데이터 분포의 시각화에 기여합니다. 아웃라이어(이상치)를 식별하거나, 데이터의 치우침(Skewness)을 직관적으로 파악하는 데 활용됩니다.
5. MODE.SNGL, MODE.MULT, MEDIAN: 데이터의 대표값 찾기
MODE 함수는 데이터 집합에서 가장 자주 나타나는 값(최빈값)을 반환합니다.
SNGL은 단일 최빈값, MULT는 여러 최빈값 모두를 반환하는 배열 함수입니다.
MEDIAN 함수는 데이터를 정렬했을 때 가운데 위치하는 값(중앙값)을 반환합니다.
- 기본 형식: =MODE.SNGL(숫자1, [숫자2], ...) / =MODE.MULT(숫자1, [숫자2], ...) / =MEDIAN(숫자1, [숫자2], ...)
실무 예제: 아래와 같이 고객 연령 데이터가 B2:B7 셀에 있다고 가정해봅시다.
| 고객 ID | 연령 |
| 001 | 25 |
| 002 | 30 |
| 003 | 35 |
| 004 | 30 |
| 005 | 25 |
| 006 | 40 |
예제 목표:
- 가장 많이 분포된 연령대를 찾으세요 (최빈값).
- 연령 분포의 중간값을 찾으세요 (중앙값).
해결 방법:
- 최빈값: =MODE.SNGL(B2:B7) → 25 (25와 30이 두 번씩 나타나지만 SNGL은 첫 번째 최빈값을 반환)
- MODE.MULT를 사용하려면 결과를 표시할 셀을 두 개 선택 후 ={MODE.MULT(B2:B7)} 입력 후 Ctrl+Shift+Enter -> {25; 30}
- 중앙값: =MEDIAN(B2:B7) → 30 (정렬하면 25, 25, 30, 30, 35, 40 → 중간 두 값의 평균)
최상급 실무 활용: 데이터의 대표값을 파악하여 모집단의 특성을 이해하는 데 중요합니다.
특히 평균(AVERAGE)이 이상치에 크게 영향을 받을 수 있는 반면, 중앙값과 최빈값은 데이터의 실제 중심 경향을 더 잘 나타낼 수 있습니다.
고객 연령대별 마케팅 전략 수립, 제품 디자인 시 주요 사용자층의 특성 파악 등에 활용될 수 있습니다.
6. LARGE, SMALL: 특정 번째로 크거나 작은 값 찾기
LARGE 함수는 데이터 집합에서 특정 번째로 큰 값을 반환하고, SMALL 함수는 특정 번째로 작은 값을 반환합니다.
- 기본 형식: =LARGE(배열, k) / =SMALL(배열, k)
- 배열: 값을 찾을 숫자 데이터 집합
- k: 반환할 값의 위치 (예: 1은 가장 큰 값, 2는 두 번째로 큰 값)
실무 예제: 위 영업 사원 판매 실적 데이터(B2:B6)를 사용하여:
예제 목표:
- 가장 판매 실적이 좋은 상위 3명의 판매 실적을 모두 찾으세요.
- 가장 판매 실적이 낮은 하위 2명의 판매 실적을 모두 찾으세요.
해결 방법:
- 상위 3명 판매 실적:
- 1등: =LARGE(B2:B6, 1) → 180
- 2등: =LARGE(B2:B6, 2) → 150
- 3등: =LARGE(B2:B6, 3) → 120
- 하위 2명 판매 실적:
- 5등: =SMALL(B2:B6, 5) → 180 (오름차순 5번째는 최댓값)
- 4등: =SMALL(B2:B6, 4) → 150 (오름차순 4번째)
- SMALL(B2:B6, 1) → 100 (가장 작은 값)
- SMALL(B2:B6, 2) → 120 (두 번째로 작은 값)
최상급 실무 활용: 특정 기준에 따라 상위 또는 하위 그룹을 신속하게 식별하는 데 사용됩니다.
예를 들어, 상위 5%의 고객에게만 제공되는 특별 혜택을 설계하거나, 하위 10%의 제품 재고를 줄이는 계획을 수립할 때 유용합니다.
성과 관리, 재고 관리, 리스크 분석 등 다양한 분야에서 핵심적인 의사결정을 지원합니다.
데이터의 빈도, 순위, 백분위수, 그리고 대표값을 파악하는 데 필수적인 함수들(FREQUENCY, RANK, PERCENTILE, QUARTILE, MODE, MEDIAN, LARGE, SMALL)을 깊이 있게 다루었습니다.
이 함수들은 단순히 숫자를 계산하는 것을 넘어, 데이터가 가진 고유한 특성과 패턴을 이해하고, 이를 바탕으로 더욱 심층적인 분석과 현명한 의사결정을 내릴 수 있도록 돕습니다.