엑셀에서 정성 데이터(Qualitative Data)를 분석하는 방법을 찾고 계신가요? 그렇다면 이 글이 딱 맞는 가이드입니다. 숫자로 세거나 수치화하기 어려운 데이터를 정성 데이터라고 합니다. 이러한 정성 데이터는 포커스 그룹 토론, 심층 인터뷰, 문장 완성 테스트, 단어 연상, 일상적인 대화 등을 통해 수집할 수 있습니다.
엑셀에서 정성 데이터를 분석하는 8단계
여기서는 설문지의 세 가지 답변 예시를 통해 분석 방법을 살펴보겠습니다. 이 예시에서 XYZ는 동네 끝자리에 있는 카페로, 학생들이 종종 시간을 보내는 곳입니다. 세 가지 설문 문항은 다음과 같습니다.
- 리커트 척도(Likert Scale) 문항: “나는 XYZ에 만족한다” — 총 5단계로 답변합니다.
- 객관식 문항: “일주일에 몇 번 XYZ에서 식사하십니까?” — 참여자는 3개 선택지 중 하나를 고릅니다.
- 개방형 문항: “XYZ에 어떤 음식을 추가해야 할까요?” — 답변 길이는 자유입니다.
데이터셋은 일반적으로 “ID”, “성별(Gender)”, “문항 및 응답”의 3개 열로 구성됩니다. 아래 화면에서처럼 세 문항은 압축된 형태로 표시되어 있으며, 각 유형별로 정보를 얻는 방법을 하나씩 알아보겠습니다.
1단계: 정성 데이터 코딩 후 정렬하기
먼저 코드를 활용해 정성 데이터를 수치값으로 변환하고, 다음 단계를 준비하기 위해 데이터를 정렬합니다. 본 예시의 리커트 척도는 5단계이므로 값은 다음과 같이 매핑됩니다.
- 매우 동의(Strongly Agree) → 5
- 동의(Agree) → 4
- 보통(Neutral) → 3
- 반대(Disagree) → 2
- 매우 반대(Strongly Disagree) → 1
- 이 기준으로 셀 범위 E6:E11에 값을 입력합니다.
- 그다음 “성별”과 “코딩(Coding)” 열을 별도의 셀 범위로 분리합니다.
- 이후 셀 범위 C14:D19를 선택하고 마우스 오른쪽 버튼을 클릭해 바로 가기 메뉴를 엽니다.
- 정렬(Sort) >>> “A~Z 오름차순 정렬(Sort A to Z)”을 선택합니다.
- 이렇게 하면 같은 성별의 값들이 한데 모이게 됩니다.
2단계: 분석 도구(Analysis ToolPak) 활성화하기
통계 검정을 수행하기 전에 엑셀에서 데이터 분석(Data Analysis) 기능을 먼저 활성화해야 합니다.
- ALT → F → T 키를 차례로 눌러 Excel 옵션 창을 엽니다.
- 추가 기능(Add-ins) >>> “이동(Go)…”을 선택합니다.
- 추가 기능 대화 상자가 나타납니다.
- “Analysis Toolpak”을 선택하고 확인(OK)을 누릅니다.
- 완료되면 데이터 탭 안에 데이터 분석 명령이 표시됩니다.
3단계: T-검정으로 평균 비교하기
정성 데이터 분석에는 ‘두 표본 t-검정(Two-Sample t-test)’, 즉 ‘독립표본 t-검정’을 활용합니다. 두 가지 가설을 세웁니다.
귀무가설(H₀): “두 그룹은 XYZ 카페에 대한 만족도가 같다.”
대립가설(Hₐ): “두 그룹은 XYZ 카페에 대한 만족도가 같지 않다.”
p-값이 0.05 미만이라면 귀무가설을 기각하고, 그렇지 않다면 귀무가설을 기각하지 못합니다.
- 앞서 활성화한 분석 도구는 분석(Analysis) 섹션 아래에 나타납니다.
- “데이터 분석”을 클릭합니다.
- “t-검정: 등분산이 아니라고 가정하는 두 표본(t-test: Two-Sample Assuming Unequal Variances)”을 선택하고 확인을 누릅니다.
- 대화 상자가 뜨면 다음 옵션을 지정합니다.
- 변수 1 범위: D14:D16
- 변수 2 범위: D17:D19
- 두 범위의 순서를 바꿔도 결과는 동일합니다.
- “출력 범위(Output Range)”를 선택하고 출력 위치로 셀 C21을 지정합니다.
- 확인을 누릅니다.
- 결과가 아래와 같이 출력됩니다.
- 평균은 각각 3과 4.33입니다. 이 차이가 유의미한지 p-값으로 확인합니다. 또한 분산은 1과 0.33으로 차이가 있으므로, 등분산이 아니라는 가정이 맞았습니다. 만약 분산 값이 거의 같다면 “t-검정: 등분산이 같다고 가정하는 두 표본”으로 변경해야 합니다.
- 따라서 P(T<=t) 양측(two-tail) 값만 확인하면 됩니다. 이 값이 0.05 미만이어야 통계적으로 유의합니다. 본 예시에서는 약 0.14(반올림 기준)로 0.05보다 크므로, 귀무가설을 기각하지 못합니다.
- 즉, 분석 결과 남성과 여성은 XYZ 카페에 대한 만족도가 다르며, 이는 통계적으로 유의한 차이입니다.
4단계: 카이제곱 검정을 위한 범주형 데이터 준비하기
이번 단계에서는 SUM 함수와 COUNTIFS 함수를 사용합니다. 두 번째 문항 분석 방법을 살펴보겠습니다. 카이제곱(Chi-Square) 검정은 두 범주형 데이터 간의 관계를 파악하는 데 사용하며, 기대값과 관찰값의 차이를 확인할 수 있습니다. 여기서는 성별과 XYZ 카페 방문 횟수 사이에 관계가 있는지 알아보겠습니다.
귀무가설(H₀): “성별과 주당 XYZ 카페 방문 횟수 사이에는 관계가 없다.”
대립가설(Hₐ): “성별과 주당 XYZ 카페 방문 횟수 사이에는 관계가 있다.”
- 먼저 범위 C6:C11을 “Gender”, D6:D11을 “Times”로 이름 정의합니다.
- 다음으로 카이제곱 값을 계산할 서식(템플릿)을 만듭니다.
- 셀 범위 C7:E7을 선택하고 아래 수식을 입력합니다.
=COUNTIFS(Gender,$B$7,Times,C6)
이 수식은 남성이면서 주당 XYZ 카페에서 1회 식사한 셀의 개수를 구합니다.
- CTRL+ENTER를 누르면 수식이 자동으로 채워집니다(AutoFill).
- 이어서 셀 범위 C8:E8을 선택하고 아래 수식을 입력합니다.
=COUNTIFS(Gender,$B$8,Times,C6)
이 수식은 여성이면서 주당 XYZ 카페에서 1회 식사한 셀의 개수를 구합니다.
- CTRL+ENTER를 누릅니다.
- 이제 행과 열의 합계를 구합니다.
- 셀 범위 C9:E9를 선택하고 아래 수식을 입력합니다.
=SUM(C7:C8)
- CTRL+ENTER를 누릅니다.
- 셀 범위 F7:F8을 선택하고 아래 수식을 입력합니다.
=SUM(C7:E7)
- CTRL+ENTER를 누릅니다.
- 응답자가 6명이므로 셀 F9에 6을 입력합니다.
- 이제 기대값을 구합니다. 기대값 공식은 행 합계 × 열 합계 ÷ 전체 합계입니다.
- 셀 범위 I7:K7을 미리 선택한 뒤 아래 수식을 입력합니다.
=$F$7*C9/$F$9
- CTRL+ENTER를 누릅니다.
- 셀 범위 I8:K8을 선택하고 아래 수식을 입력합니다.
=$F$8*C9/$F$9
- CTRL+ENTER를 누릅니다.
- 이제 카이제곱 값을 구합니다.
- 셀 범위 C13:E14를 선택하고 아래 수식을 입력합니다.
=(C7-I7)^2/I7
- CTRL+ENTER를 누릅니다.
- 마지막으로 셀 I12에 아래 수식을 입력해 이 값들을 모두 더합니다.
=SUM(C13:E14)
- ENTER를 누릅니다.
- df는 자유도(degrees of freedom)를 의미하며, (열 개수 − 1) × (행 개수 − 1) 공식으로 구합니다. 본 예시는 행이 2개, 열이 3개이므로 자유도는 (3−1)×(2−1) = 2가 됩니다.
5단계: 카이제곱 검정으로 범주형 정성 데이터 분석하기
이 검정의 p-값을 구하기 위해 CHISQ.DIST.RT 함수를 사용합니다.
- 셀 I14에 아래 수식을 입력합니다.
=CHISQ.DIST.RT(I12,I13)
이 함수는 “카이제곱 분포의 우측 꼬리 확률(right-tailed probability)”을 반환합니다.
- ENTER를 누르면 0.2라는 값이 나오는데, 이는 0.05보다 큽니다. 따라서 귀무가설을 기각하지 못하며, 쉽게 말해 두 범주 사이에는 유의한 관계가 없다고 해석할 수 있습니다.
6단계: 개방형 정성 데이터 감성 분석하기
이제 마지막 문항과 응답을 살펴보겠습니다. 응답의 주제(theme)를 추출하는 수동 방식을 사용하겠습니다. 데이터셋에 “Topic1”과 “Topic2” 두 개의 열을 추가했습니다.
- 응답을 읽고 해당하는 음식 주제를 붙입니다. 예를 들어 “Chicken Pizza”는 “Chicken”과 “Pizza”의 2개 주제로 분류하는 식입니다.
- 이후 고유한 주제만 모아 새로운 표에 정리했습니다.
7단계: COUNTIF 함수로 개방형 데이터 빈도 분석하기
COUNTIF 함수를 활용해 빈도 분포에 대한 인사이트를 얻습니다.
- 먼저 셀 범위 D13:D17을 선택하고 아래 수식을 입력합니다.
=COUNTIF($E$5:$F$10,C13)
이 수식은 범위 E5:F10에서 셀 C13의 값과 일치하는 항목의 개수를 셉니다.
- CTRL+ENTER를 눌러 수식을 자동 채우기합니다.
- 다음으로 빈도 분포를 시각화하기 위해 차트를 삽입합니다.
8단계: 묶은 세로 막대형 차트로 결과 시각화하기
이 단계에서는 묶은 세로 막대형 차트(Clustered Column Chart)를 만들어 정성 데이터를 더욱 명확하게 이해합니다.
- 셀 범위 C12:D17을 선택하고 [삽입] 탭에서 추천 차트(Recommended Charts)를 클릭합니다.
- 차트 삽입 대화 상자가 나타나면 기본적으로 묶은 세로 막대형(Clustered Column)이 선택되어 있습니다. 만약 선택되어 있지 않다면 직접 선택합니다.
- 확인을 누릅니다.
- 그 결과, 고객들은 XYZ 카페에서 치킨(Chicken), 파스타(Pasta), 피자(Pizza)를 상위 3개 메뉴로 원하는 것을 확인할 수 있습니다. XYZ 카페 경영진은 이 제품들을 더 많이 판매해 매출을 늘릴 수 있습니다.
핵심 요약
- 두 그룹 간 평균을 비교할 때는 t-검정을, 범주형 값을 다룰 때는 카이제곱 검정을 사용합니다.
- 본 예시 데이터셋의 세 가지 문항에서 도출된 결과는 다음과 같습니다.
- 남성과 여성은 XYZ 카페에 대한 만족도가 서로 다릅니다.
- 성별과 XYZ 카페 방문 횟수 사이에는 관계가 없습니다.
- 학생(고객)들은 XYZ 카페에 추가했으면 하는 메뉴로 치킨, 파스타, 피자를 상위 3개로 꼽았습니다.
연습 자료
엑셀 파일에는 각 방법별 연습용 데이터셋이 포함되어 있습니다. 이를 활용하면 위에서 소개한 방법들을 손쉽게 따라 해볼 수 있습니다.
마무리
지금까지 엑셀에서 정성 데이터를 분석하는 8단계를 살펴보았습니다. 방법을 적용하는 과정에서 어려움이 있거나 피드백이 있다면 언제든 댓글로 남겨주세요. 또한 저희 사이트 ExcelDemy를 방문하시면 더 많은 엑셀 관련 팁과 강좌를 만나보실 수 있습니다. 읽어주셔서 감사합니다!
함께 읽으면 좋은 글
- 엑셀에서 qPCR 데이터 분석하는 방법 (2가지 쉬운 방법)
- 엑셀 데이터 분석으로 케이스 스터디 수행하기
- 엑셀에서 텍스트 데이터 분석하는 방법 (5가지 방법)