안녕하세요 짤막한 강좌 한정희 강사입니다.
이번 시간에는 분석에 적합한 형태로 데이터를 전처리하는 방법을 두 가지 사례를 통해 배워보겠습니다.
데이터 전처리가 왜 필요한가?
우리는 현업에서 엑셀 데이터를 필요에 맞게 정렬하고 필터링해 원하는 데이터만 뽑아 보고 피벗 테이블을 만들어 분석 자료를 작성합니다. 이때 엑셀은 분석이 가능한 데이터 조건을 몇 가지 요구합니다.
간단히 한 두가지 예를들면 데이터 중간에는 빈 줄이 없어야 하고 병합된 셀도 없어야 하며 등등..
어렵지 않지만 분석할 데이터 양이 많다면 꽤나 시간이 걸리고 힘든 작업이 됩니다.
데이터 전처리는 엑셀 일반 기능으로 처리할 수 있지만 파워 쿼리를 사용하면 더욱 쉬워집니다
실습 파일 다운로드
첫 번째 사례
현재 데이터는 3년간의 비용을 월별로 정리해 놓은 것인데 사용처를 기준으로 기계실, 수영장, 일반 순서로 오름차순 정렬해 보겠습니다.
먼저 사용처 필드 범위에서 임의의 셀을 선택하고 [데이터] 탭 - [정렬 및 필터] 그룹 - [텍스트 오름차순 정렬]을 선택합니다.
그런데 이 작업을 수행하려면 병합하려는 모든 셀의 크기가 동일해야 한다는 오류 메시지 창이 뜨네요.
이유는 데이터 내부에 합계를 구한 행이 병합되어 있기 때문입니다. 이렇게 데이터 중간에 병합된 셀이 있으면 정렬을 할 수 없습니다.
이번에는 발의 연도가 2024년에 해당하는 비용을 필터링 해보겠습니다.
'발의 일자' 필터 버튼을 누르고 '(모두 선택')의 체크를 해제한 다음 '2024년'에 체크 표시를 하고 [확인]을 누릅니다.
필터는 되었지만 1월 비용 데이터만 필터가 됩니다. 이유는 1월과 2월 사이에 비냉이 있어 서로 다른 데이터베이스로 인식했기 때문입니다.
[정렬 및 필터] 그룹에서 [지우기]를 눌러 방금 필터한 결과를 취소합니다.
데이터를 다루는데 있어 정렬과 필터는 아주 기본적인 기능이죠.
그리고 이렇게 정리한 데이터를 피벗 테이블 보고서를 만들 수 있는데 이런 작업이 가능하려면 데이터베이스 규칙에 맞게 데이터가 잘 정리되어 있어야 합니다.
그럼 이 데이터를 파워 쿼리를 활용해서 분석이 가능하도록 데이터를 전처리하는 방법을 배워보겠습니다.
파워 쿼리를 사용하는 네 가지 단계 중에 첫 번째 단계가 연결입니다.
연결 작업하겠습니다. 여러 개의 표를 [파워 쿼리 편집기]에 연결하려면 [데이터 가져오기 및 변환] 그룹 - [데이터 가져오기]를 선택하고 [파일에서] - [Excel 통합 문서에서]를 선택하면 됩니다.
'실습파일.xlsx'가 선택되어 있는 상태에서 [가져오기]를 누릅니다.
그러면 [탐색 창]이 실행됩니다.
가져오려고 하는 시트는 시설 운영비 시트이므로 '시설 운영비'를 선택하고 [데이터 변환]을 누릅니다.
왼쪽에 [쿼리] 창을 보면 '시설 운영비' 쿼리가 생성되어 있습니다.
더블클릭해서 '전처리'로 이름을 변경해 기존의 '시설 운영비' 시트와 구분 하겠습니다.
🔔파워 쿼리의 장점은 원본 데이터는 그대로 두고 사본을 만들어 작업을 한다는 장점이 있습니다. 그러면 원본이 유지되어 실수를 하더라도 원본에서 다시 작업을 하면 됩니다.
데이터를 연결하는 작업이 끝났다면 두 번째 변환 작업이 필요합니다.
[홈] 탭을 선택하고 [첫 행을 머리글로 사용]을 선택해 [1] 행의 내용을 필드명으로 변경합니다.
중간중간 반복되는 합계 행과 빈 행, 연도와 월을 표시하는 행이 필요가 없어 제거하겠습니다.
'통계목' 필드의 필터 버튼을 눌러 '(Null)'에 체크를 해제하고 '연도'와 '월'을 표시하는 부분의 항목도 체크 표시를 없앱니다. 그리고 맨 아래쪽 '통계목'과 '합계'도 체크를 해제합니다.
[확인]을 누르면 필요 없는 행들이 모두 제거됩니다.
파워 쿼리를 활용하면 빠르고 쉽게 필요 없는 행들을 제거할 수 있습니다.
그리고 이 변환 과정에서 꼭 해야 할 작업이 있는데, 각 필드가 형식에 맞는지 확인한 후 형식에 맞지 않으면 데이터 형식을 변경해줘야 합니다.
'통계목'에서 '적요'까지는 텍스트 형식이 맞습니다. 그래서 변경할 필요가 없고 '품의 금액'은 [정수]로 변경합니다
그리고 '발의 일자'는 [날짜]로 변경하고 나머지 '작성자'와 '계약 여부'는 [텍스트 형식] 그대로 두면 됩니다.
이제 변환한 데이터를 워크시트로 로드하겠습니다.
파워 쿼리 사용 단계의 세 번째가 결합이지만 현재 데이터는 결합할 일이 없습니다.
[홈] 탭 - [닫기 및 로드] - [닫기 및 다음으로 로드]를 선택합니다.
워크시트로 데이터를 가져오는 방법 중 '표' 또는 '피벗 테이블 보고서' 등으로 선택할 수 있습니다.
바로 피벗 테이블을 만들 수 있지만 표로 로드한 다음 필요하다면 피벗 테이블을 만들 수도 있습니다.
지금은 '표'로 로드한 후 데이터를 원하는 형태로 정렬하고 필터링을 해보겠습니다.
'표' 그리고 '새 워크시트'가 선택된 상태에서 [확인]을 누릅니다.
그랬더니 앞에서 변경한 쿼리 이름으로 새로운 시트가 하나 추가되고 전 처리한 데이터가 워크시트로 로드가 됩니다.
필요하다면 [테이블 디자인] 탭에서 '줄무늬 행'은 제거하고 표 스타일도 변경해 줄 수 있습니다.
품의 금액은 단위를 읽기 쉽도록 범위 지정하고 <Ctrl + Shift + 1>을 누르면 천 단위 구분 기호가 표시됩니다.
그럼 사용처를 정렬해 보겠습니다.
'사용처' 필터 버튼을 누르고 [텍스트 오름처순 정렬]을 하면 기계실, 수영장, 그리고 일반 순서로 데이터가 정렬됩니다. 오류 없이 아주 쉽게 정렬되죠?
이 번에는 발의 연도 2024년 데이터를 필터링해 보겠습니다.
'(모두 선택)'에 체크를 없애고 '2024년'에 체크 표시한 후 [확인]을 누르면 발의 연도 2024년 데이터만 필터링 됩니다.
이번에는 연도별 품의 금액의 합계를 나타내는 간단한 피벗 테이블도 작성해 보겠습니다. 먼저 [데이터] 탭을 선택하고 [지우기]를 눌러 필터 결과를 지웁니다.
[삽입] 탭 - [피벗 테이블]을 선택합니다. 그리고 '기존 워크시트'를 선택하고 현재 워크시트 오른쪽 [K1] 셀을 선택합니다. 그리고 [확인]을 누르면 피벗 테이블을 만들 준비가 됩니다.
'발의 일자'에 체크 표시하면 행 영역에 발의 일자가 추가되고 행 레이블에는 연도별로 그룹화되어 표시됩니다.
그리고 품의 금액의 합계를 표시할 것이므로 '품의 금액'에 체크 표시합니다. 그러면 값 영역에 필드가 추가됩니다.
한눈에 연도별 품위 금액의 합계를 확인할 수 있습니다.
각 연도에서 마우스 오른쪽 버튼을 눌러 [전체 필드 확장]을 누르면 연도별, 분기별 품의 금액의 합계를 단계별로 확인할 수 있습니다.
다시 마우스 오른쪽 버튼을 눌러 [그룹] 메뉴를 선택합니다.
분기를 선택 해제해 연도별, 월별로 확인할 수 있습니다. 다시 마우스 오른쪽 버튼을 눌러 [확장 축소] - [전체 필드 축소]를 선택합니다.
이번에는 '사용처'를 열 영역으로 추가하면 연도별, 사용처별로 품의 금액을 나타내는 피벗 테이블이 작성됩니다.
이렇게 파워 쿼리를 활용하면 데이터 전처리가 아주 쉽고 데이터 전처리가 되면 정렬, 필터, 피벗 테이블을 작성해 데이터 분석이 쉬워집니다.
두 번째 사례
'장비 구입 내역' 시트를 선택합니다.
일반 사용자들은 엑셀에서 데이터를 입력하고 계산표를 작성할 때 워드 문서처럼 표를 작성하는 문제가 있습니다.
현재 데이터는 장비 구입 내역을 학교별로 정리해 놓았는데 학교명이 같고 장비/규격이 같은 경우에는 셀이 병합되어 있습니다.
이 데이터를 취득일 기준 오름차순으로 정렬해 보겠습니다.
'취득일' 필드에서 임의의 셀을 선택하고 [데이터] 탭 - [정렬 및 필터] 그룹 - [텍스트 오름차순 정렬]을 선택합니다.
경고 메시지 창이 뜹니다.
학교명과 장비/규격 필드가 병합되어 있기 때문에 정렬할 수 없습니다. 이 경우에도 파워 쿼리를 활용해 데이터 분석이 가능하도록 전처리를 하면 됩니다.
하나의 테이블을 파워 쿼리로 연결하려면 [데이터 가져오기 및 변환] 그룹 - [테이블 범위]에서 를 선택하면 됩니다.
표 만들기가 실행되면 [확인]을 누릅니다.
데이터가 [파워 쿼리 편집기]에 바로 연결 됩니다.
왼쪽 [쿼리] 창에서 '표4' 쿼리를 더블클릭해 '장비구입내역전처리'로 이름을 변경합니다. 그러면 기존 시트 이름과 구분할 수 있습니다.
현재 데이터의 문제점은 학교명과 장비/규격명이 같은 경우에 셀이 병합되었죠?
[파워 쿼리 편집기]로 가져왔더니 병합은 자동으로 해제되면서 나머지 셀들이 빈 셀이 표시되었습니다.
빈 셀에는 실제 범일자동차고 입력되어 합니다. '학교명'과 <Ctrl> 키를 눌러 '장비/규격' 열을 선택합니다.
마우스 오른쪽 버튼을 눌러 [채우기] - [아래로]를 선택합니다. 바로 위에 있는 데이터가 채워집니다. 파워 쿼리를 사용하면 이 작업이 쉽게 처리됩니다.
이제 각 필드별 형식을 변경하겠습니다. '취득일' 필드는 시간이 필요 없어 형식을 [날짜]로 변경합니다.
그리고 '수량'에서 '취득 금액'은 형식이 [정수]가 맞아 그대로 두고 그리고 오른쪽으로 스크롤을 해 '기준 연월일'을 [날짜]로 변경합니다.
'잔존가액'에서 '경제적 수리 한계'는 선택하고 마우스 오른쪽 버튼을 눌러 [정수]로 변경합니다.
변환 작업이 끝나면 워크시트로 로드하겠습니다.
[홈] 탭 - [닫기 및 로드] - [닫기 및 다음으로 로드]를 선택합니다.
'표', '새 워크시트'가 선택되어 있는 상태에서 [확인]을 누릅니다.
그랬더니 '장비구입내역전처리'라는 시트가 기존 시트 왼쪽에 추가됩니다.
[테이블 디자인] 탭에서 '줄무늬 행' 체크 해제하고 눈이 편한 표 스타일로 변경하겠습니다.
이제 워크시트로 가져온 '단가'와 '취득 금액'의 범위를 선택하고 <Ctrl + Shift + 1>을 눌러 쉼표 스타일을 적용합니다.
그리고 '잔존가액', '수리비', '경제적 수리 한계 검토' 범위 범위에도 <Ctrl + Shift + 1>을 눌러 쉼표 스타일을 적용합니다.
이제 취득일을 기준으로 최근 날짜 순으로 내림차순 정렬하겠습니다.
'취득일' 필드를 [내림차순 정렬]을 선택합니다.
데이터를 워크시트로 로드한 이후에도 다시 [파워 쿼리 편집기]를 실행하고 싶다면 [쿼리 및 연결] 창에서 해당 쿼리를 더블 클릭하면 [파워 쿼리 편집기]를 실행할 수 있습니다.
만일 작업 중에 [쿼리 및 연결] 창이 열려 있지 않다면 [데이터] - [쿼리 및 연결]을 선택하면 [쿼리 및 연결] 창을 실행할 수 있습니다.
파워 쿼리를 사용하면 데이터 전처리가 아주 쉬워집니다.
현업에서 활용해보세요.
회사에서 쓰는 진짜 엑셀! [된다! 7일 실무 엑셀] 구매하기
- 예스24 http://www.yes24.com/Product/Goods/76182394
- 교보문고 https://product.kyobobook.co.kr/detail/S000001817934
- 알라딘 https://www.aladin.co.kr/shop/wproduct.aspx?ItemId=198405108
더 많은 파워 쿼리 영상을 시청하려면 아래 링크를 클릭하세요 https://youtube.com/playlist?list=PLxKIudZ9zp0NrM0bS8i51o6_IyvtJOGUs&si=iYl7igYEBN-m1cpE
쉽게 배워 바로 써먹는 엑셀 32강 순서대로 학습하려면 아래 링크를 클릭하세요.https://youtube.com/playlist?list=PLxKIudZ9zp0MO-gHV3en8oBCKyoHzU7KZ&si=2SEuO_mg486LwYlY
'파워쿼리 PowerQuery' 카테고리의 다른 글
| 파워 쿼리(Power Query) 사용 4가지 단계 | 어려울 것이라는 선입견을 한 방에 날려줍니다👍 (0) | 2024.01.29 |
|---|---|
| 엑셀 파워 쿼리] 열 피벗 해제 | 피벗 테이블 작성이 안 되는 데이터가 있어요~ 해결 방법을 알려드립니다 (0) | 2023.02.18 |
| 엑셀 파워 쿼리] 두 시트를 비교해 일치 or 불일치 데이터 추출 | 칼퇴를 부르는 파워 쿼리 (0) | 2022.11.17 |
| 엑셀 파워 쿼리] 실무 예제를 사용해 중복 데이터 추출하기 (0) | 2022.11.16 |
| 엑셀 파워 쿼리] 두 개 이상의 조건에 일치하는 값을 찾을 때 VLOOKUP(×) 파워 쿼리(○) | 너무 쉽게 원하는 결과를 얻을 수 있습니다! 강추👍 (0) | 2022.11.10 |