다차원 척도법(Multidimensional Scaling, MDS)은 브랜드, 제품 속성 등 객체 간의 유사도를 2차원 평면 그래프, 즉 지각 지도(perceptual map) 형태로 시각적으로 표현하는 분석 기법입니다. MDS를 활용하면 복잡한 데이터를 하나의 XY 좌표평면으로 축약해 한눈에 파악할 수 있습니다. 이 글에서는 Excel에서 다차원 척도법을 수행하는 전체 과정을 단계별로 자세히 안내합니다.
다차원 척도법이란 무엇인가?
다차원 척도법은 객체 간의 유사성 또는 비유사성을 시각적으로 나타내는 방법입니다. 여기서 '객체'는 매우 다양한 의미로 해석될 수 있으며, 비즈니스 분야에서는 주로 브랜드나 제품 속성 등을 의미합니다.
정치, 과학, 미디어 등 어떤 분야에 속해 있더라도 MDS의 기본 원리는 동일합니다. 유사성을 기준으로 분석할 때, 서로 유사한 객체일수록 그래프 상에서 더 가까운 위치에 배치됩니다. 또한 MDS는 차원이 매우 많은 데이터의 차원을 축소하는 용도로도 활용할 수 있습니다.
Excel에서 다차원 척도법 수행하기: 11단계 완전 정복
Excel에서 다차원 척도법을 수행하려면 아래 단계를 순서대로 따라 하세요.
1단계: 설문조사 실시 및 초기 데이터 만들기
- 5개 브랜드를 6개 속성 기준으로 평가하는 다차원 척도법을 진행한다고 가정합니다.
- 분석에 앞서 해당 브랜드에 대한 고객 설문조사를 실시하고, 평점(1~9점)을 Excel 스프레드시트에 입력합니다.
- 아래는 예시 데이터입니다.
2단계: 데이터 역변환하기
- 이 단계는 선택 사항입니다. 평점을 10에서 빼서 값을 뒤집을 수 있으며, 수식은 다음과 같습니다.
C7= 기존 평점
함께 읽으면 좋은 글: Excel에서 데이터를 1~10 척도로 변환하는 방법
3단계: 객체 간 거리 계산 및 적절한 척도 적용하기
- 이제 'Scaled Data Comparison(척도화된 데이터 비교)'이라는 새 섹션을 만듭니다.
- K26 셀에 SUMXMY2 함수를 사용한 다음 수식을 입력합니다.
=SUMXMY2($C$16:$H$16,C17:H17)
- 이 수식은 브랜드 1과 브랜드 2 사이의 거리를 반환합니다.
- 수식을 아래로 드래그하여 복사합니다.
- 같은 방식으로 L27 셀에는 다음 수식을 입력합니다.
=SUMXMY2($C$17:$H$17,C18:H18)
- M28 셀에는 아래 수식을 입력합니다.
=SUMXMY2($C$18:$H$18,C19:H19)
- 마지막으로 N29 셀에는 다음 수식을 입력합니다.
=SUMXMY2(C19:H19,C20:H20)
- 그다음 C25 셀로 이동해 다음 수식을 작성합니다.
=IF(K25="","",K25*9/MAX($K$25:$O$29))
- 이 수식은 브랜드 간 거리를 최대 거리를 기준으로 척도화합니다.
- 곱수 9는 필수가 아니며, 상황에 맞는 적절한 숫자를 선택하면 됩니다.
- 수식을 아래쪽과 오른쪽으로 드래그하여 확장합니다.
- 같은 방법으로 6개 속성 간의 척도화된 거리도 구합니다. 관련 수식은 워크북에 포함되어 있으니 참고하세요.
4단계: 모든 거리 데이터 모으기
- 계산된 거리들을 아래 이미지처럼 별도의 표에 정리합니다.
5단계: 초기 맵 좌표 설정 후 다른 위치에 복사하기
- 아래 이미지를 보면 몇 개의 시작 좌표가 설정되어 있습니다. 이 거리들은 브랜드 1을 기준으로 산출된 값입니다.
- 예를 들어 브랜드 2는 브랜드 1에서 7.84 단위 떨어져 있으므로 (7.84, 0)으로 설정합니다.
- 나머지 점들도 같은 방식으로 지정합니다.
- 세로축 값(0)을 번갈아 배치하면 데이터가 규칙적으로 분산되어 보기 좋습니다.
- 브랜드 1에는 (0, 0)을 지정했습니다. 이후 Excel Solver로 좌표를 보정할 것이기 때문에 어떤 숫자를 넣어도 무방합니다.
- 마지막으로 이 값들을 복사합니다(참조 셀이 있다면 복사 영역에 참조 셀이 남지 않도록 주의하세요).
6단계: 새로운 거리 데이터 구하기
- 설정한 맵 좌표를 바탕으로 브랜드 및 속성 간의 새로운 거리를 계산합니다. 거리 계산에 필요한 수식은 워크북에 정리되어 있습니다.
함께 읽으면 좋은 글: Excel에서 데이터 스케일링 하는 방법
7단계: RANK.EQ 함수로 기존 거리와 새 거리의 순위 매기기
- 이제 RANK.EQ 함수를 사용해 거리들의 순위를 구합니다.
- C87 셀에 다음 수식을 입력한 뒤, 데이터 표 전체에 복사합니다.
=IF(C42="","",RANK.EQ(C42,$C$42:$M$52,1))
- 같은 방식으로 새로 계산된 거리에도 순위를 매깁니다.
8단계: 기존 거리 순위와 새 거리 순위 간 상관관계 구하기
- 이제 CORREL 함수를 사용해 기존 거리와 새로 생성된 거리 간의 상관관계를 구합니다.
- G114 셀에 다음 수식을 입력합니다.
=CORREL(C87:M97,C102:M112)
- 상관관계가 겨우 31.28%라면, 현재의 거리 값들이 실제 거리를 잘 반영하지 못하고 있다는 의미입니다. 따라서 다음 단계에서 Solver 추가 기능을 사용해 상관관계를 최대화하고, 그에 맞게 최종 맵 좌표를 조정하게 됩니다.
9단계: Solver 추가 기능 활성화하기
- Solver 추가 기능이 아직 설치되어 있지 않다면 이 단계를 진행하세요.
- Alt+F+T 키를 누르면 아래와 같은 창이 열립니다.
- 추가 기능(Add-ins) 항목으로 이동해 Excel 추가 기능을 찾고 이동(Go)을 클릭합니다.
- Solver 추가 기능을 선택하고 확인을 누릅니다.
- 이제 Solver 추가 기능이 정상적으로 추가됩니다.
10단계: Solver로 상관관계 최대화하기
- 데이터 탭으로 이동해 Solver 명령을 클릭합니다.
- Solver 매개 변수 대화 상자에서 목표값을 상관관계 셀, 즉 G114 셀로 설정합니다.
- 최대값(Max) 옵션을 선택합니다.
- 그다음 최종 맵 좌표 참조 범위인 G57:H67을 지정합니다.
- 제한 조건을 설정합니다.
- 제약이 없는 변수를 음수가 아닌 값으로 설정 체크박스에 표시하고, 실행 방법은 Evolving(진화법)을 선택합니다.
- 풀기(Solve) 버튼을 누릅니다.
- 제약 조건을 추가하려면 추가(Add) 버튼을 누르고, 열리는 창에서 적절한 조건을 설정합니다.
- 계산이 완료되면 Solver 결과를 유지하고 확인을 누릅니다.
- 상관관계 값을 확인해 보세요. 이제 94.84%로 이전보다 크게 향상되었습니다.
- 아래 이미지는 Excel Solver를 성공적으로 실행한 후 변경된 최종 맵 좌표를 보여줍니다.
11단계: 지각 지도 만들기
- 마지막으로 지각 지도(perceptual map)를 그리려면 최종 맵 좌표를 선택한 후 삽입 탭으로 이동해 분산형 차트(Scatter Charts)를 클릭하고 분산형 차트 더 보기를 누릅니다.
- XY(분산형) 항목에서 두 번째 유형을 선택하고 확인을 누릅니다.
- 생성된 분산형 차트에 서식을 적용하면 아래 이미지와 같은 지각 지도를 얻을 수 있습니다.
연습용 워크북 다운로드
마무리
Excel로 다차원 척도법을 수행하는 것은 결코 쉬운 작업이 아닙니다! 이 주제에 대해 궁금한 점이 있다면 아래에 댓글로 질문을 남겨주세요.
<< Excel 스케일링 수식 | 통계용 Excel | Excel 배우기로 돌아가기