[엑셀 매핑 테이블을 이용해 데이터 카테고리화] Vlookup 함수 사용

엑셀에서 매핑 테이블(Mapping Table)을 만들어 데이터를 카테고리화 하고 정렬까지 하는 방법입니다.

 

예시 상황은 아래와 같습니다.

 

예시 상황

인사팀, 재무팀, 개발 1팀, 국내영업 1팀 등 여러 부서에 다양한 사람들이 있다.

부서에 따라 경영지원, 기술, 영업 3가지 대분류 카테고리를 만들고 경영지원, 기술, 영업 카테고리 순으로 정렬하고자 한다.

 

결국은 아래 2가지를 하는게 목표이다.

1. 여러 사람들을 부서에 따라 경영지원, 기술, 영업 3개의 대분류로 매핑 하기 <- 매핑테이블 만들기, Vlookup 사용
2. 경영지원, 기술, 영업 카테고리 순으로 정렬한다

 

경영지원, 기술, 영업 3개 카테고리로 나누고 경영지원, 기술, 영업 순으로 정렬하기 

 

 

이제 매핑테이블과 Vlookup을 활용해 실질적으로 각 부서를 3개 카테고리로 나누고 정렬하는 방법입니다.

 

목차

1. 매핑테이블 만들기

2. Vlookup() 함수로 매핑 테이블을 참조해 부서들 카테고리화 및 정렬하기

 

1. 매핑 테이블 만들기

• 매핑 테이블(Mapping Table) 만드는 방법입니다. 간단히 매핑의 의미를 짚고 넘어가면 아래와 같습니다.

• 매핑(Mapping) : 매핑이란 하나의 값을 다른 값으로 대응시키는 것을 말합니다. 한자로는 사상(寫像)이라고 합니다. [1]

• 위의 정의처럼 매핑은 하나의 값을 다른 값으로 대응시키는 것으로, 저희는 위의 문제를 해결하기 위해 각 '부서명'을 '부서 대분류명 3가지'에 대응시키는 테이블을 만들 예정입니다.

• 또한 정렬을 위해 각 '부서명'을 '부서 대분류 고유번호'로도 매핑하려고 합니다.

• 부서명 -> 대분류명, 부서 대분류 고유번호(정렬을 위해 매핑)

• 부서명에 대응한 매핑 테이블을 만든 결과는 아래와 같습니다.

• 저는 부서명 전체열을 복사한 후 '중복제거'하여 각 부서명에 대분류 대응 값을 입력했습니다.

 

예시용 매핑테이블, 예시 엑셀파일은 아래에 첨부

 

 

2. VLOOKUP() 함수로 매핑테이블을 참조해 부서들 카테고리화 및 정렬준비

• 생성한 매핑테이블과 VLOOKUP() 함수를 사용해 카테고리화 및 정렬준비하는 방법 입니다.

• 매핑테이블은 위의 예시처럼 표를 만들면 되고, 결국은 VLOOKUP() 함수 사용법 설명 입니다.

• Vlookup(Vertical Lookup, 수직 검색) : 스프레드시트에서 지정한 표(매핑테이블)의 첫번째 열을 수직으로 위에서 부터 아래로 탐색하다가 일치하는 값을 발견하면, 일치하는 값이 발견된 행의 오른쪽 특정데이터를 반환해주는 함수 입니다. <- 주의 : 해당열에 동일한 값이 여러개 있으면 가장 처음 만난(수직으로 검색하기에 가장 위에있는) 행의 오른쪽 특정 값을 반환 합니다.

• VLOOKUP 함수 사용방법

VLOOKUP 함수에는 4개의 인수값이 들어갑니다. VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup) Lookup_value : 매핑테이블에서 검색(Lookup)하려는 값 Table_array : 매핑테이블, 주의사항으로 테이블의 첫번째 열에 검색대상 값들이 있어야하고, 테이블의 범위는 절대값($)으로 지정해야 오류가 없음 (절대값 지정 단축키 F4) Col_index_num : Column_index_num, 매핑테이블에서 일치하는 값이 나왔을 때 해당 행(Row, 가로줄)의 몇번째 열(Column, 세로줄)에서 값을 가져올지 정하는 인수 Range_lookup : 매핑테이블에서 Lookup_value와 일치하는 값을 찾을 때 완전 일치(True), 부분 일치(False)를 정하는 것, 예를 들어 Lookup_value가 '인사' 이고, 매핑테이블에는 '인사팀'만 있을 때 True = 완전 일치 이므로 값이없음(#N/A) 반환, False = 부분 일치로 특정 값 반환

 

 

아래에는 VLOOUP 함수를 사용해 부서명을 카테고리화 한 예제와, 엑셀 파일 첨부합니다. 

 

 

상황설명

 

 

 

대분류 카테고리화 VLOOKUP 함수 인수 입력 예시

 

상세 입력내용

• Lookup_value : D4, 부서명
• Table_array : '부서명 매핑테이블'!$A$3:$C$16, '시트명'!매핑테이블범위 <- 반드시 $A$3 이런식으로 절대값 지정
• Col_index_num : 3, 매핑테이블에서 3번 째 Column(열)이 '부서 대분류 명'
• Range_lookup : FALSE(부분 일치)

 

최종 결과, '부서 대분류 번호' 열은 정렬을 위해서 만들었습니다.

 

• '부서 대분류 번호'가 숫자라서 간단히 오름차순, 내림차순 정렬을 할 수 있습니다.

 

 

 

 

아래에 예제 파일 첨부합니다.

 

궁금하신 사항은 댓글 남겨 주세요.

 

 

매핑테이블을 이용한 카테고리화 및 정렬.xlsx

0.02MB

 

참고 : 
1. 해시넷, 매핑, http://wiki.hash.kr/index.php/%EB%A7%A4%ED%95%91