raw데이터에서 원하는 데이터 추출하기
feat. VLOOKUP, Index, MATCH 함수
간단하지만 강력한 함수인 VLOOKP 함수와 INDEX 및 MATCH 함수를 이용하여
실전 데이터 분석을 연습해보자.
가상 의류 기업인 브래드 사의 1월 주문 실적을 담은 raw 데이터를 가지고 연습해보자.
해당 raw 데이터를 가지고 1) 각 상품별 주문건수를 입력하는 상황과 2) BEST 주문건수 및 주문금액, WORST 주문건수 및 주문금액을 구해보자.
1. raw 데이터에서 원하는 데이터 가져오기
(좌)raw 데이터, (우)입력해야 하는 표브래드 사의 1월 주문 실적을 담은 raw 데이터가 왼쪽과 같다고 가정해보자.
오른쪽 표에 주문건수를 입력해야 하는데, 사실 이 정도 데이터 양이면 눈대중으로 하나씩 입력할 수도 있다.
그러나 데이터 양이 해당 사례보다 10배, 100배, 1000배 많다면 어찌할 것인가?
그러한 상황을 대비하여 VLOOKUP함수를 익혀 사용할 수 있다.
VLOOKUP 함수의 인수는 "주어, 범위, 해당 열, 0 or 1"로 구성된다.
해당 함수에서 유의해야 할 점은 주어를 가진 범위가 반드시 첫 열에 있어야 한다. 즉, 여기선 무지티를 담은 상품 열이 첫 번째에 있어야 하는 것이다.
또한, 범위는 움직이지 않아야 하므로 F4를 눌러 절대참조를 해야 한다.
마지막 인수는 0 혹은 1로 구성되는데, 정확히 일치하는 값을 찾으려면 0을 입력하고, 근접한 값을 찾기 위해서는 1을 입력한다.
우리는 예시에서 무지티의 주문건수를 구해야 하므로, 주어는 무지티이고 범위는 위 빨간색 구간이다. 또한 주문건수는 3번째 열이므로 세 번째 인수로 3을 입력하고, 정확한 값을 찾아야 하므로 마지막 인수는 0을 입력한다.
무지티 주문건수를 완성한 후 채우기 핸들을 이용하여 아래 값들도 차례대로 찾을 수 있다.
직접 눈으로 찾으며 입력하여 10분 넘게 소요할 일을 단, 15초 만에 해결할 수 있다.
위에서 언급한 대로 범위를 절대참조 하지 않으면 원하는 데이터를 얻을 수 없으므로 절대참조를 반드시 해야 한다는 것을 다시 한번 인지하자.
2. BEST/WORST 주문건수 및 주문금액 구하기
이번엔 1월 주문 실적에서 BEST/WORST 주문건수 및 주문금액을 구해보자.
해당 값들을 구하기 위해서는 앞서 이용했던 VLOOKUP함수뿐만 아니라 INDEX, MATCH 함수를 이용해야 한다.
INDEX와 MATCH 함수를 이용하기 전에 해당 함수들에 대해 간단히 살펴보면 다음과 같다.
-INDEX함수
: 범위 값에서 특정 행, 열을 가리키는 위치의 값을 반환하는 함수
-MATCH 함수
: 나열된 열이나 행에서 어떤 요소의 위치를 알고 싶을 때 사용하는 함수
BEST 주문건수를 알기 위한 함수 식은 다음과 같다. 조금은 복잡하지만 하나씩 풀어서 살펴보자.
먼저 INDEX의 범위를 전체로 잡아 절대참조를 하고, MATCH함수와 LARGE함수의 범위는 우리가 구해야 하는 주문건수로 입력해야한다.
주문건수가 가장 큰 상품을 가져와야 하므로, LARGE의 두 번째 인수를 1로 입력한다. MATCH의 마지막 인수는 0으로 입력해야 하고 INDEX 함수의 마지막 인수는 반환 위치이므로 INDEX 범위 내 상품의 반환 위치인 1을 입력하면 함수가 완성된다.
완성된 함수를 입력하면 다음과 같이 BEST 주문건수에 좌표티가 입력됨을 알 수 있다.
BEST 주문금액도 같은 방식으로 구하고자 하는 범위만 다르게 입력하면 값을 구할 수 있다.
또한, WORST 주문건수/주문금액은 LARGE 함수가 아닌 SMALL 함수를 이용하여 값을 구해야 한다는 점을 인지하자.
방대한 양의 데이터를 분석하기 위해서는 오늘 사용된 함수들을 반드시 인지해야 한다고 생각한다. 우리가 실전에서 다루어야 할 데이터는 오늘 다룬 예시와는 차원이 다른 크기의 데이터이다.
함수의 쓰임새를 기억하고, 직접 raw 데이터를 구축하여 데이터를 다뤄보는 연습을 꾸준히 해보자.
