실무에 바로 쓰는 일잘러의 엑셀 데이터 분석(1)
엑셀 파워 쿼리를 이용하여 데이터 전처리하기
안녕하세요, 브래드입니다.
오늘은 실무에 바로 쓰는 일잘러의 엑셀 데이터 분석 도서 내에서 엑셀 파워 쿼리를 이용하여 데이터를 분석하는 방법에 관해 함께 알아볼게요.
데이터 전처리
우리는 살아가면서 수많은 데이터를 접하고 그것을 활용하여 최적의 의사결정을 하고자 노력합니다.
다만, 데이터를 다루는 데 있어서 양질의 데이터가 우리가 원하는 형태로 존재하면 좋겠지만 대부분은 그렇지 않을 겁니다.
즉, 데이터 전처리 과정을 거치는 것이 필수적인 하나의 과정이며 데이터 전처리를 통해 신뢰성 있는 데이터를 추출할 수 있습니다.
*ETL
- Extraction: 하나 이상의 데이터 소스에서 데이터를 획득하는 기능
- Transformation: 데이터 정리, 형식 변환, 표준화, 통합 등의 기능
- Loading: 변형 처리가 완료된 데이터를 특정 시스템에 적재하는 기능
ETL은 데이터를 추출, 변환, 적재하는 것을 말합니다. ETL을 위해 파이썬, SQL 등을 활용하여 데이터를 전처리할 수 있으며, 엑셀의 파워 쿼리로도 가능합니다.
열 분할 및 텍스트 추출하여 데이터 통합하기
위 엑셀 파워 쿼리 이미지에서 볼 수 있듯이 날짜 및 상권코드가 하나로 묶여있음을 알 수 있습니다.
따라서 분석에 용이하도록 날짜와 상권코드로 각각 분리해 보는 작업을 진행해 보겠습니다.
먼저, Column1과 같이 명확한 이름이 기재되지 않은 첫 행을 바꾸기 위해 변환 탭에서 첫 행을 머리글로 사용 항목을 클릭하여 다음과 같이 변환해 줍니다.
본격적으로 분할을 위하여 변환 탭 - 열 분할 - 구분 기호 기준을 클릭하여 다음과 같은 창이 나오도록 합니다.
그리고 현재 날짜 및 상권코드 칼럼 내에서 직접적으로 알 수 있듯이 날짜와 상권코드가 공백으로 구분됨을 알 수 있습니다.
따라서 공백으로 구분함을 클릭한 후 다음과 같이 설정을 마쳐줍니다.
변환을 완료한 후, 칼럼 첫 행의 이름을 각각 날짜, 상권코드로 수정하면 모든 변환 과정이 완료된 것입니다
이미지에서 확인할 수 있듯이, 데이터가 깔끔하게 분리되어 우리가 원하는 데이터를 더욱 쉽게 필터링할 수 있음을 알 수 있습니다.
만약, 날짜와 분기로 결합된 데이터 내에서 분기만을 추출하고 싶다면 변환 - 추출 - 구분 기호 뒤 텍스트 탭을 클릭하여 ':' 뒤에 있는 분기 만을 다음과 같이 추출할 수 있습니다.
우리가 추출한 분기는 분기 옆 글자(ABC)에서 알 수 있듯이 해당 데이터는 숫자 형식이 아닌 텍스트 형식임을 알 수 있습니다.
따라서 분기를 숫자 데이터로 변환하는 과정이 필요합니다.
ABC를 클릭하여 데이터 유형을 정수로 클릭하여 다음과 같이 데이터 형식을 변형해 줍니다.
이렇게 데이터 형식을 변환해 줌으로써 후에 데이터 분석에 더욱 용이한 형태로 만들 수 있습니다.
조건 열 기능 활용하여 여러 범위 데이터 통합하기
위 이미지는 도서 구매 후 기본적으로 제공되는 데이터 셋입니다.
이미지에서 파악할 수 있듯이 담당자별 데이터가 정렬되어 있습니다. 다만, 담당자별 데이터 따로 구분되어 있어서 데이터 분석에는 용이해 보이지 않습니다.
따라서 엑셀 파워 쿼리를 통해 데이터 분석에 용이하게끔 데이터를 마사지할 필요가 존재합니다.
데이터를 파워 쿼리로 불러오기 위하여 데이터 탭 - 데이터 가져오기 - 기타 원본에서 - 테이블/범위에서 항목을 클릭합니다.
그리고 표를 만들기 위해 다음과 같이 데이터 전체 범위를 지정해 주고 머리글 포함 항목을 체크해제합니다.
확인을 클릭하면 다음과 같이 파워 쿼리로 데이터가 불러와짐을 알 수 있습니다.
이제 우리는 불러온 데이터를 기반으로 담당자를 구분해야 합니다. 이를 위해선 조건 열 기능을 활용하여 담당자 구분을 진행할 수 있습니다.
열 추가 탭 - 조건 열을 클릭하여 다음과 같은 창을 불러와줍니다.
담당자로 구분을 진행할 것이므로 새 열 이름은 담당자로 설정한 후 열 이름은 열 1로, 연산자는 같음, 값은 담당자로, 결과는 열 선택으로 변경 후 열 2를 클릭해 줍니다.
기타 옵션에서는 null을 입력하여 입력을 끝마쳐줍니다.
입력을 마친 후 다음과 같이 담당자 열이 추가되면서 담당자 이름 및 null이 입력됨을 알 수 있습니다.
null이 입력된 항목을 담당자 이름으로 바꾸어야 하므로 담당자 열에서 마우스 우클릭 - 채우기 - 아래로를 클릭하여 다음과 같이 담당자 이름이 입력되게 만들어줍니다.
다음으로 열 1에서 담당자별로 구분하기 위해 비워놓은 담당자가 포함된 행을 제거하기 위해 필터기능을 활용하여 null과 담당자를 체크 해제하여 확인버튼을 누릅니다.
그리고 변환 탭 - 첫 행을 머리글로 사용을 클릭하여 위 이미지와 같이 열 이름이 설정될 수 있도록 합니다.
회사 열 또한 필터를 활용하여 체크 해제하여 회사 열을 제거합니다.
마지막으로 조건 열을 활용하여 배송비 여부를 확인하는 작업을 진행해 보겠습니다.
열 추가 - 조건 열을 클릭하여 배송비 열이 0보다 큰 경우 TRUE를 출력하고, 아닐 시에는 FALSE를 출력하도록 입력해 줍니다.
배송비 여부 열까지 추가한 후, 홈 - 닫기 및 로드를 클릭하여 엑셀 시트에 우리가 작업한 쿼리가 로드될 수 있도록 만들어줍니다.
다음과 같이 데이터 분석이 용이하게끔 데이터 셋이 만들어졌음을 알 수 있으며, 담당자 및 배송비 여부 열을 추가로 확인해 볼 수 있습니다.
오늘은 실무에 바로 쓰는 일잘러의 엑셀 데이터 분석 도서 내 데이터를 활용하여 엑셀 파워쿼리 내에서 데이터를 다루어보았습니다.
좀 더 상세한 내용 및 오늘 활용한 데이터는 해당 도서를 구매한 후 활용할 수 있으므로 관심 있으신 분들은 도서 구매 후 심도 있는 공부를 이어나가셨으면 합니다.
다음 시간에도 엑셀 파워 쿼리를 이어서 학습하는 시간을 가져볼게요.
브래드였습니다. 감사합니다.
