엑셀 텍스트 최빈값, INDEX MATCH COUNTIF 조합으로 한방에 끝내기! (VBA 필요 없음)
안녕하세요! 😎
"가장 많이 접수된 고객 불만 유형은 뭐지?", "이번 달 설문조사에서 가장 많이 나온 답변은 뭘까?" 처럼 텍스트 데이터에서 가장 빈번하게 나타나는 값(최빈값)을 찾아야 할 때, 막막하셨죠? 숫자 최빈값을 구하는 MODE 함수는 텍스트에선 작동하지 않으니까요!
하지만 걱정 마세요! 오늘 이 포스팅 하나면, 복잡한 VBA 코드 없이 INDEX, MATCH, COUNTIF 함수 3가지를 조합해서 텍스트 최빈값을 빠르고 정확하게 찾아내는 전문가가 될 수 있습니다.
📌 [핵심] 숫자만 되던 최빈값 찾기, 이제 텍스트 데이터도 함수 조합으로 완벽 정복!
🤔 왜 MODE 함수는 텍스트에 안될까?
엑셀의 MODE 함수는 기본적으로 숫자 데이터 집합에서 가장 자주 발생하는 값을 반환하도록 설계되었어요. 그래서 범위 안에 텍스트가 있으면 그냥 무시해버리죠.
그렇기 때문에 우리는 함수를 조합해서 "각 텍스트가 몇 번 나왔는지 세고(COUNTIF), 가장 큰 횟수를 찾은 다음(MAX+MATCH), 그 횟수에 해당하는 텍스트가 무엇인지(INDEX) 가져오는" 논리적인 과정을 만들어야 합니다.
바로 이 과정의 주인공이 INDEX, MATCH, COUNTIF입니다!
🚀 실전 예제 1: INDEX + MATCH + COUNTIF 조합법
자, 이제 본격적으로 텍스트 최빈값을 찾는 마법의 공식을 배워볼까요? 아래와 같이 고객들이 제출한 '선호하는 과일' 설문조사 데이터가 있다고 가정해 봅시다. 여기서 어떤 과일이 가장 인기가 많은지 찾아볼게요!
우리가 사용할 최종 공식은 이렇습니다. (미리 겁먹지 마세요! 차근차근 분해해 드릴게요)
=INDEX(A2:A10, MATCH(MAX(COUNTIF(A2:A10, A2:A10)), COUNTIF(A2:A10, A2:A10), 0))
⚠️ 중요! 이 수식은 배열 수식입니다.
- Microsoft 365 또는 Excel 2021 이상: 그냥 Enter를 누르면 됩니다.- Excel 2019 및 이전 버전: 수식을 입력하고 반드시 Ctrl + Shift + Enter를 함께 눌러야 {=...} 형태로 입력됩니다.
단계별 공식 해부하기 🧐
이 복잡해 보이는 공식이 어떻게 작동하는지 속을 들여다볼까요?
1️⃣ 수식 안쪽 핵심
COUNTIF(A2:A10, A2:A10)- 역할: 각 과일이 전체 목록에서 몇 번씩 등장하는지 COUNTIF 함수로 개수를 셉니다.
- 작동 원리: 배열 수식으로 사용되면, A2셀의 '사과' 가 전체(A2:A10)에서 몇 개인지, A3셀의 '바나나' 가 몇 개인지... 이렇게 각 셀마다의 빈도를 계산해서 메모리에 가상의 배열(숫자 목록)을 만듭니다.
- 결과 (메모리 속 가상 값):
{2; 2; 3; 3; 1; 2; 3; 1; 2} // 포도는 2번, 바나나는 2번, 사과는 3번, ...
2️⃣ 중간 과정: MAX(...) 와 MATCH(..., ..., 0)
- 역할: 가장 높은 빈도수(가장 큰 숫자)가 '몇 번째' 위치에 있는지 찾아냅니다.
- MAX(COUNTIF(...)): 위 COUNTIF 가 만든 가상 배열 {2; 2; 3; 3; 1; 2; 3; 1; 2} 에서 가장 큰 값, 즉 3 을 찾아냅니다.
- MATCH(3, COUNTIF(...), 0): "가장 큰 값인 3 이, 저 가상 배열에서 정확히 일치(0)하는 첫 번째 자리가 어디야?" 라고 묻습니다. 배열의 세 번째 자리에 바로 3 이 있으니, 결과는 3 이 됩니다.
3️⃣ 최종 마무리: INDEX(...)
- 역할: 찾아낸 위치 번호를 이용해서 원본 목록에서 실제 텍스트 값을 가져옵니다.
- INDEX(A2:A10, 1): "원본 데이터인 A2:A10 범위에서, MATCH 가 알려준 3번째 자리에 있는 값을 가져와!" 라는 명령입니다.
- 최종 결과: A2:A10의 세번째 값인 "사과" 를 화면에 보여줍니다! 🎉
💼 실전 예제 2: 가장 빈번한 불량 원인 찾아내기
이번엔 제조 공장에서 제품 검수 후 기록한 '불량 원인' 데이터를 가지고 분석해 보겠습니다. 어떤 원인으로 불량이 가장 많이 발생하는지 파악하면 품질 개선에 큰 도움이 되겠죠?
다음과 같은 불량 원인 데이터가 있다고 상상해 보세요.
제품 코드,불량 원인 (B2:B12)
P-001,포장 불량
P-002,스크래치
P-003,포장 불량
P-004,기능 오류
P-005,단순 변심
P-006,스크래치
P-007,기타
P-008,포장 불량
P-009,도색 불량
P-010,기능 오류
P-011,스크래치
자, 이 데이터에서 가장 자주 발생한 불량 원인을 찾아봅시다! 결과를 표시할 셀에 아래 수식을 입력해 보세요.
=INDEX(B2:B12, MATCH(MAX(COUNTIF(B2:B12, B2:B12)), COUNTIF(B2:B12, B2:B12), 0))
⚠️ 다시 한번 강조!
- Microsoft 365, Excel 2021 이상: 그냥 Enter!
- Excel 2019 이전 버전: 수식 입력 후 Ctrl + Shift + Enter를 함께 눌러주세요!
단계별 수식 풀이 ⚙️
이번에도 공식이 어떻게 작동하는지 차근차근 살펴볼까요?
1️⃣ COUNTIF(B2:B12, B2:B12)
먼저 B2부터 B12까지 각 항목이 몇 번씩 나왔는지 계산합니다.
- '포장 불량' 은 3번, '스크래치' 는 3번, '기능 오류' 는 2번... 이런 식으로 가상의 숫자 목록(배열)을 만듭니다.
- 메모리 속 결과: {3; 3; 3; 2; 1; 3; 1; 3; 1; 2; 3}
2️⃣ MAX(...) 와 MATCH(...)
가장 많이 발생한 횟수와 그 위치를 찾습니다.
- MAX({3; 3;...}): 위 숫자 목록에서 가장 큰 값인 3 을 찾아냅니다.
- MATCH(3, {3; 3;...}, 0): "가장 큰 값 3 이 숫자 목록에서 처음으로 나타나는 위치는 어디인가?" 라고 묻습니다. 목록의 첫 번째 자리에 바로 3 이 있으므로, 결과는 1 이 됩니다.
3️⃣ INDEX(...)
최종적으로 해당 위치의 텍스트 값을 가져옵니다.
- INDEX(B2:B12, 1): "원본 데이터 범위 B2:B12에서, 1번째 에 있는 값을 알려줘!"
- 최종 결과: B2:B12의 첫 번째 값인 "포장 불량" 이 반환됩니다.
💡 잠깐, 여기서 질문!
"스크래치" 도 3번 나왔는데 왜 "포장 불량" 이 나오나요?
좋은 질문이에요! 👍 MATCH 함수는 기본적으로 조건을 만족하는 '첫 번째' 위치를 반환하기 때문입니다. 데이터 상에서 '포장 불량' 이 '스크래치' 보다 먼저 나왔기 때문에 '포장 불량' 이 결과로 나온 것이죠.
만약 최빈값이 여러 개일 때 모두 표시하고 싶다면 필터나 피벗 테이블, 또는 더 복잡한 배열 수식이 필요하답니다. 하지만 '가장 빈번한 원인 중 하나' 를 빠르게 찾는 데는 이보다 좋은 방법이 없죠!
💜 마무리하며
어떠셨나요? INDEX, MATCH, COUNTIF 의 조합, 정말 강력하지 않나요? 이 공식 하나만 제대로 익혀두시면, 어떤 텍스트 데이터라도 가장 많이 등장하는 값을 손쉽게 찾아낼 수 있습니다. 고객 피드백 분석, 재고 관리, 설문조사 결과 정리 등 활용할 수 있는 곳이 무궁무진하답니다.
처음에는 수식이 길어서 어렵게 느껴질 수 있지만, 각 함수가 어떤 역할을 하는지 단계별로 이해하고 나면 생각보다 간단합니다. 꼭 직접 예제를 만들어 연습해보세요! 여러분의 데이터 분석 능력이 한층 더 업그레이드될 겁니다.
오늘 내용이 도움이 되셨다면 '공감' 꾹! 궁금한 점은 댓글로 남겨주세요!
작성자: 마늘빵
📱 모바일용 초간편 요약
🎯 목표: 텍스트 데이터에서 가장 많이 나온 값 찾기
🛠️ 도구: INDEX + MATCH + COUNTIF 함수 조합
✅ 핵심 공식:
=INDEX(범위, MATCH(MAX(COUNTIF(범위, 범위)), COUNTIF(범위, 범위), 0))
(배열 수식이라 구버전 엑셀은 Ctrl+Shift+Enter 필수!)
⚙️ 작동 순서:
- COUNTIF: 각 텍스트의 개수를 센다. (사과→3개, 바나나→2개...)
- MAX: 가장 많은 개수(→3)를 찾는다.
- MATCH: '3개' 가 있는 첫 위치(→1번째) 를 찾는다.
- INDEX: '1번째' 위치의 실제 값(→"사과") 을 가져온다. 끝!