분할된 데이터에서 분석용 마스터 데이터 구성하기

이번에는 판매 데이터 세트 A1000이라는 파일을 오픈하도록 하겠습니다. 이 실습 폴더에 보면 A1000이라는 파일과 A15000이라는 파일이 있는데 데이터는 완전히 동일합니다. 그런데 1000과 15000은 데이터 행수, 표본 데이터 차이가 있다고 보시면 됩니다. A1000이라는 건 1000개의 표본 데이터로 구성되어 있고 A15000은 15000개의 표본 데이터로 만들어졌습니다. 저희가 실습하면서 물리마스터를 만들어낼 때 VLOOKUP과 같은 함수 기반으로 물리마스터를 구현해낼 때는 여러 가지 성능상의 제약 이슈가 있어요. 그런 성능상의 제약 이슈를 확인하려면 15000 파일을 가지고 실습을 한번 해보시고요. 그리고 무난한 실습을 원한다면 1000번 파일 가지고 실습을 하도록 하겠습니다. 저희는 A1000 파일을 오픈하도록 할게요. 보시면 조금 전에 실습과는 다르게 엑셀의 여러 시트로 구분되어 있습니다. 판매 데이터에는 판매에 대한 기본 데이터 정보가 있고요. 제품 데이터에는 제품에 대한 기본 제품명과 제품 카테고리 같은 기본 정보가 들어 있어요. 판매 채널에는 판매 채널에 대한 코드 값이 들어가 있고요. 주문 정보에는 주문자에 대한 주문 수량이나 발주 지역 같은 정보가 들어 있습니다. 그리고 주문 일자 상세 정보에서는 주문 번호에 따른 주문 일자 관련된 주문 연도라든가 분기라든가 월과 같은 상세 정보가 들어 있습니다. 주문 요일은 요일 코드에 대한 실제 일, 월, 화, 수, 목 같은 문자 데이터 정보가 들어 있어요. 이와 같이 데이터가 하나의 거대 테이블로 구성되지 않은 경우에는 저희가 데이터 분석을 어떻게 진행할 수 있을까요? 현실 세계에서는 하나의 거대한 물리 마스터가 아니라 지금 이와 같은 다양한 테이블이나 표 조합이 분석 대상 데이터가 되는 경우가 많습니다. 그럴 때는 일반적으로 저희가 많이 쓰는 방법부터 시작해보도록 하겠습니다. 이럴 때는 VLOOKUP, 기준이 되는 키를 찾아서 그 키를 가지고 저희가 VLOOKUP을 구성할 수 있습니다. 먼저 판매 데이터와 제품 데이터를 살펴보면 판매 데이터의 제품번호와 제품 데이터의 제품번호가 일치돼요. 그러면 제품번호를 가지고 제품명, 제품 색상, 제품 카테고리, 제품 하위 카테고리, 제조사 브랜드 6개의 열을 추가할 수가 있습니다. 그렇게 하기 위해서는 먼저 제품 데이터부터 주문 요일까지 정보를 저희는 판매 데이터 시트에 열을 추가해서 하나의 거대 물리마스터를 만들려고 해요. 그래서 우리가 데이터를 가져오려고 하는 것을 전부 다 표로 등록을 해보겠습니다. 제품 데이터에서 임의의 셀을 하나 클릭하고 Ctrl+T 누른 다음에 확인하면 이게 표로 등록되고 표 이름을 시트명과 동일하게 이름을 하도록 하겠습니다. 똑같이 제품 데이터라고 이름을 하도록 할게요. 그리고 판매 채널을 선택하고 이거 역시 Ctrl+T 자 눌러서 표로 등록하고 이 표의 이름은 판매 채널이라고 설정하도록 하겠습니다. 주문 정보 시트 선택하고 이거 역시 자 Ctrl+T 입력해서 그 보면 표로 등록하고 이 표 역시 주문 정보라고 입력하도록 하겠습니다. 주문 일자 상세 테이블 역시 Ctrl+T로 표로 등록하고 이 표 이름 역시 시트랑 동일하게 주문일자 상세로 설정하도록 하겠습니다. 마지막으로 주문요일을 선택하고 Ctrl+T로 이 녀석을 표로 등록하고 이 표 이름을 주문요일로 설정하도록 하겠습니다. 모두 다 표로 등록하고 표 이름을 각각의 시트명으로 설정했어요. 그러면 VLOOKUP할 때는 VLOOKUP은 훨씬 더 쉽게 진행할 수가 있습니다. VLOOKUP에서는 이제 이 제품 데이터란 이름에서 제품명은 두 번째 열, 제품 색상은 세 번째 열, 네 번째 열, 다섯, 여섯, 일곱 번째 열까지 데이터를 가져오면 됩니다. 그래서 판매 데이터에서 먼저 가져올 공간을 위해서 여섯 개의 열을 선택하고 한 번에 마우스 오른쪽 버튼에 삽입 선택하도록 하겠습니다. 그리고 가져올 데이터의 헤더명을 지정하기 위해서 헤더 가져올 헤더 값을 Ctrl+C로 복사해서 판매 데이터에서 헤더에다 그 값을 입력하도록 하겠습니다. 그럼 주문번호를 가지고 제품 데이터에서 제품명을 어떻게 가져올 수 있죠? 이 녀석은 이꼴 VLOOKUP, VLOOKUP 함수 선택하고 탭키 누른 다음에 주문번호와 일치하는 정보 영역은 저희가 제품 데이터로 지금 정의를 했습니다. 그쪽에서 몇 번째 열이었는지 기억나세요? 이건 두 번째 열이었습니다. 그리고 정확하게 값을 가져오라고 0을 설정하도록 하겠습니다. 그리고 이 녀석을 앞으로 이 녀석을 드래그를 계속 할 거예요. 드래그를 계속 용이하게 하기 위해서 이 녀석은 B2는 오른쪽으로 드래그를 하더라도 B열은 이동하지 않고 하단으로만 계속 변경하도록 값이 변경하도록 B3, B4, B5가 되도록 설정하려면 이건 혼합참조라는 걸 사용하면 됩니다. 혼합참조는 이 B2에서 커서가 깜빡거릴 때 F4키를 한 번 누르면 절대참조 어떤 경우에도 셀 이동이 허용되지 않는 절대 참조가 됩니다. 그리고 한 번 더 누르면 요 녀석 같은 경우는 행 고정 혼합 참조라고 해서 2번 행에서 절대 움직이지 않아요. 요 녀석은 C2, D2, E2가 돼서 열은 바뀌더라도 행은 절대 이동하지 않도록 하는 거예요. 저희가 원하는 건 열 고정 혼합 참조 B 앞에 달러를 붙이면 이제 저희가 오른쪽으로 참조를 이동시켜도 B2는 앵커가 걸려 있어서 절대 참조 값이 변하지 않고 하단으로만 B3, B4, B5처럼 내가 원하는 값에 대한 변동만 발생을 합니다. 제가 원하는 게 바로 이거예요. 엔터 친 다음에 요 녀석은 하얀 십자가를 보면 영역의 우측 하단으로 만들면 검정 십자가로 바뀝니다. 이걸 채우기 핸들이라고 하고 더블 클릭하면 모든 데이터가 다 채워집니다. 그리고 난 다음에 제품 색상, 제품 카테고리를 다 가져오기 위해서 이 녀석은 이 영역을 이렇게 오른쪽으로 변경해서 보면 값을 복사하도록 하겠습니다. 근데 보면 이 녀석을 선택하고 이렇게 값을 오른쪽으로 드래그를 했을 때 B2는 전혀 변동이 없습니다 이 녀석은 제가 가져오려는 열 번호만 세 번째로 바꾸고 엔터 치도록 하겠습니다 그리고 더블클릭하면 제품 색상 정보가 설정이 될 거예요 이것도 조금만 줄여보도록 할게요 자 마찬가지로 제품 색상을 선택하고 이 녀석을 채우기 핸들을 만들어서 그 드래그 드래그해서 오른쪽으로 가면 블랙 생산 값이 그대로 설정되고 여기서 가져오는 열 번호만 3번이 아니라 4번으로 바꾸면 제품 카테고리 제대로 가져올 거고 이거 역시 더블클릭해서 셀을 전체에 채우도록 하겠습니다 이걸 보면 3번만 더 반복을 할게요 여기 보면 다시 한번 확장을 하고 요 녀석은 열 번호만 5번으로 변경을 하고요 다시 더블클릭해서 이 녀석 값을 채우고요. 조금 확장하도록 하겠습니다. 한 번 더 값을 갖다가 오른쪽으로 이동을 하고 여기서 열 번호만 5번이 아니라 6번으로 변경하고 이거 역시 전체 다 채우도록 하겠습니다. 마지막 하나 남았네요. 다시 한 번만 이걸 이렇게 확장하고 이 녀석에 대한 번호만 7번으로 바꾸면 브랜드명까지 제품에 관련해서 제가 분석하려고 하는 데이터를 다 가져왔어요. 자, 제품 데이터는 다 끝났습니다. 자, 그러면 판매 채널로 가도록 할게요. 판매 채널에서 제가 가져오려는 기준값은 채널 코드고 가져오려고 하는 건 이 열이에요. 자, 판매 채널이에요. 이 녀석은 두 번째 열이죠. 두 번째 열이라는 열 번호만 이해하시면 다시 판매 데이터에 가서 저희가 가져오려고 하는 건 판매 데이터에 가서 저희가 가져오려고 하는 건 채널 코드죠 채널 코드 옆에 다시 한번 열을 선택하고 마우스 오른쪽 버튼 삽입하도록 하겠습니다 그리고 그 다음에 요 녀석의 네이밍만 동일하도록 만들게요 판매 채널을 복사해서 판매 데이터에서 판매 채널 데이터를 가져오려고 합니다 판매 채널 데이터는 똑같이 VLOOKUP VLOOKUP으로 채널 코드와 일치하는 판매 채널 표 이름에서 두 번째 열을 가져오도록 하겠습니다 그리고 이 녀석을 전체를 채우도록 하겠습니다 판매 채널 데이터도 제대로 가져왔어요 이번에는 주문 정보에서 저희가 가져올 건 주문 수량과 발주 지역 정보를 가져오려고 해요 그러면 이 녀석은 첫 번째가 주문번호 두 번째가 주문수량 세 번째가 발주지역이니까 저희는 2번과 3번을 가져오면 됩니다 2번과 3번을 가져오면 돼서 판매 데이터에 가서 주문번호를 기준으로 해서 저희는 요 녀석 삽입을 2개 삽입하도록 하겠습니다 2개 삽입하고 주문 정보에 있는 주문수량과 발주지역에 헤더 정보를 그대로 복사해서 저희는 갖다 쓰도록 하겠습니다. 여기다가 주문 수량 발주 지역 이 정보를 채우려고 하면 다시 equal VLOOKUP 해서 주문 번호와 일치하는 주문 정보 테이블에서 두 번째 열을 그대로 가져와라 라고 설정하도록 하겠습니다. 다 채우고요. 주문 수량 정보 채워졌고요. 이번엔 발주 지역은 동일하게 이거 역시 이번엔 복사해서 쓰도록 하겠습니다 이걸 복사해서 발주 지역에 갖다 놓고 그대로 발주 지역에 갖다 놓고 그대로 엔터 치도록 하겠습니다 요 녀석은 주문 정보에서 세 번째 열이었죠 이거면 세 번째 열로 바꿔서 엔터 치도록 하겠습니다 발주 지역 정보 다 설정이 됐어요 이제는 주문 일자 상세에서 저희가 가져올 열 번호만 보도록 할게요 주문 번호를 기준으로 했을 때 저는 주문 일자와 아 주문 일자 정보는 이미 있었네요 여기서는 주문 연도 하나 둘 세 번째와 분기 네 번째 그리고 월 그리고 요일 코드 네 개를 가져오도록 하겠습니다 아니 가져오는 김에 일까지 다 가져오죠 그게 더 편하겠네요 자 보면 주문 번호를 기준으로 했을 때 3 4 5 6 7 다섯 개를 가져오면 되겠죠 3 4 5 6 7 자 이 다섯 개를 가져오기 위해서 판매 데이터에서 데이터를 가져오기 위해서 다섯 개를 가져오니까 다섯 개를 추가하도록 하겠습니다. 추가하도록 하고 가져올 정보를 주문일자 상세에서 주문 연도부터 3번부터 시작한다는 것만 기억하시면 됩니다. 이걸 가지고 다시 VLOOKUP에 기준값은 주문번호를 가지고 주문일자 상세의 세 번째 열을 가져와 주세요 주문연도가 그대로 잘 가져와졌죠 다시 복사해서 분기에 가서 복사한 걸 붙여넣고 이번에는 네 번째 열을 가져오도록 하면 되죠 이 녀석 역시 채우기 핸들로 다 붙여넣도록 하겠습니다 마찬가지로 여기다 조금 전에 복사했던 것 그대로 붙여넣고요 이 녀석은 5번 열을 가져오도록 하면 됩니다 5번 열을 가져오도록 하고요 다시 하나 더 조금 전에 값을 복사한 다음에 이 녀석은 6번 열을 가져오도록 하겠습니다 6번 열을 가져와서 전부 다 채우고요 요일 코드는 똑같이 복사한 다음에 요 녀석은 7번 열을 가져오면 되는 거죠. 7번 열을 가져와서 7번 열 값을 전체 다 채우도록 하겠습니다. 그리고 마지막으로 요일 코드에 매핑되는 주문 요일을 가져오면 됩니다. 요 녀석은 다시 한번 삽입하고 주문 요일에서 요일 문자 이걸 복사한 다음에 문자를 복사한 다음에 판매 데이터에서 헤더에 긁어 붙여넣고요. 마지막 부분은 똑같이 해보도록 하겠습니다. VLOOKUP 가져올 것은 요일 코드와 일치하는 어디서 가져오죠? 주문 요일 표에서 두 번째 열을 정확하게 가져와라 라고 하면 됩니다. 이렇게 해서 이 데이터를 그대로 밑에 채워 넣으면 요 녀석이 바로 분석 대상 데이터가 되는 거예요. 데이터 분석의 시작점은 이렇게 우리가 확보한 데이터 여러 데이터가 있다면 데이터 의미를 잘 파악한 다음에 내가 필요한 분석 도구에게 요약 값을 도출해내고자 하는 마스터 데이터를 구성해서 이런 걸 물리 마스터라고 합니다 이걸 구성해서 여기서부터 이걸 만들어내면 조금 전에 했던 수업처럼 피벗 테이블 구성해서 다양한 요약 분석 값을 얻어낼 수가 있습니다 그러기 위해서 1차적으로 마스터 데이터를 VLOOKUP을 가지고 구성하는 방법을 살펴보았습니다.