맥북 Mac OS 엑셀 파워쿼리를 사용해본적 있나요? 윈도우 엑셀에 비해서 Mac OS 엑셀 파워쿼리 기능은 제한적 이었습니다. 그러나, 최근 업데이트로 이제 Mac OS 엑셀 파워쿼리 기능도 상당히 많은 부분이 업데이트 되어서 맥북 유저들에게도 데이터 가공이 쉬워졌습니다.
강력한 엑셀 파워쿼리 기능
파워쿼리는 데이터의 구조를 변환하는 툴입니다. 실무에서는 방대한 양의 데이터를 관리할 일이 많습니다. 이 때, 함수를 사용하기 보다는 데이터를 피벗테이블로 분석하고 매번 추가되는 데이터를 피벗테이블 새로 고침 한번으로 관리합니다. 그리고 슬라이서를 만들고 차트를 만들어서 데이터를 실시간 업데이트할 수 있습니다. 여기서 중요한 것은 새로 추가되는 데이터의 구조를 자동으로 변환시키는 기능을 파워쿼리에서 할 수 있다는 것입니다.
지저분한 raw 데이터의 구조를 변환하고 피벗테이블로 분석하기 쉽게 처리하는 엑셀의 파워쿼리 기능을 윈도우 컴퓨터가 아닌 Mac OS 엑셀에서도 사용할 수 있는 방법을 실무에서 가장 많이 사용하는 몇 가지 예를 통해서 알아보겠습니다. 여러 시트를 취합하는 방법, 데이터를 가공하는 방법, 파워쿼리 편집기 실행 방법 등에 대해서 설명하겠습니다.
아래와 같은 엑셀 문서는 실무에서 상당히 많이 접하는 로우 데이터 입니다. 하지만, 이러한 데이는 피벗테이블로 집계할 수 없기 때문에 데이터가 쌓일 수록 데이터를 업데이트 하는 일은 쉽지 않습니다. 하지만, 파워쿼리를 사용하면 데이터의 구조를 쉽게 바꿀수 있고 최종적으로 피벗테이블로 집계 및 자동화 단계로 관리할 수 있습니다.
Mac OS 엑셀 파워쿼리 여러 시트 데이터 결합
맥에서 엑셀 파워쿼리 실행 방법은 데이터 – 가져오기 – 데이터 가져오기(파워 쿼리)를 클릭합니다.
데이터 원본 석택에서 Excel 통합 문서를 클릭합니다.
원본 데이터를 불러 왔고 파워 쿼리 편집기에서 가공할 데이터를 선택합니다. 판매팀 1월 시트와 판매팀 2월 시트 모두를 중복 선택 하고 데이터 변환을 클릭합니다.
파워 쿼리 편집기를 실행하면 원본 데이터의 병합은 모두 해제된 상태로 데이터 편집기가 실행 됩니다. 먼저, 첫 번째 행은 필요 없기 때문에 첫 번째 행만 선택해서 삭제를 해도 되고 지금은 필터링을 클릭해서 null 을 선택 해제 해서 첫 번째 행을 제거하겠습니다.
제거 하고 난 후에 첫 번째 행을 머리글로 사용하겠습니다. 아래 사진 처럼 홈 탭에서 첫 행을 머리글로 사용을 클릭합니다.
첫 번째 행을 머리글로 사용했고 아래 처럼 부서 아래 판매 팀의 열을 채워야 합니다. 엑셀이라면 함수를 사용해서 드래그를 하거나 전체 선택에서 빈 셀만 선택해서 수식을 작성해야 합니다. 하지만!! 🙂 파워쿼리 에서는 채우기 – 아래로 한번에 해결할 수 있습니다.
처음에 두 시트를 불러왔었습니다. 2월 데이터도 1월 처럼 마우스 몇 번 클릭으로 가공을 해줍니다. 그리고 쿼리 추가를 통해서 두 데이터를 합치도록 하겠습니다. 홈 탭에서 쿼리 추가 – 쿼리를 새 항목으로 추가를 클릭합니다.
쿼리 병합: 데이터와 데이터를 양 옆으로 즉 열으로 데이터를 추가하는 기능입니다.
쿼리 추가: 위아래로 즉 행으로 데이터를 추가하는 기능입니다.
쿼리 추가와 쿼리를 새 항목으로 추가: 파워쿼리 편집기에 있는 쿼리 2개가 있습니다. 여기서 쿼리 추가는 1월 데이터에 2월 데이터를 추가 하는 것이고 새 항목으로 추가는 1월, 2월 쿼리 데이터를 그대로 두고 새로운 쿼리 데이터로 1,2 월 데이터를 합치는 기능입니다.
1월 2월 데이터 2개가 결합이 되었고 홈 탭에서 닫기 및 로드를 클릭해서 엑셀에서 데이터를 확인할 수 있습니다.
엑셀에서 다시 기존의 데이터에 해당하는 파워쿼리 편집기를 실행하고 싶다면 데이터 – 가져오기 – 파워 쿼리 편집기 시작을 클릭하면 위와 같은 파워 쿼리 편집기가 실행됩니다.
Mac OS 엑셀 파워쿼리 가로 데이터 세로로 변환
Mac OS 엑셀 파워쿼리 편집기를 통해서 가로로 입력된 데이터를 세로로 변환하는 방법을 알아보겠습니다. 세로 방향 블럭으로 데이터를 쌓아야 결국엔 피벗테이블을 통해서 데이터를 분석하고 집계 및 자동화할 수 있습니다.
아래와 같은 데이터가 있습니다. 가로로 길게 입력된 데이터는 파워쿼리를 사용하면 아주 쉽게 세로 데이터로 변환할 수 있습니다.
데이터 탭에서 파워쿼리를 실행해서 데이터를 가져 옵니다. 아래 데이터를 보면 세 번째 행까지는 불필요 하기 때문에 제거 하겠습니다.
행 제거 – 상위 행 제거를 클릭 합니다.
상위 행 제거에서 세 번째 행까지 제거하기 위해서 3을 입력합니다.
제거 후 첫 번째 행을 머리글로 사용해서 1월, 2월, 3월 등이 있는 행이 머리글이 되게 하고 첫 번째 열의 이름을 더블 클릭해서 이름을 수정합니다.
1월에서 12월 까지 데이터를 모두 선택한 후 열 피벗 해제 – 선택한 열만 피벗 해제를 클릭해서 가로 데이터를 세로 데이터로 바꾸어 주겠습니다.
닫기 및 로드를 클릭하면 엑셀에서 아래와 같이 데이터가 세로형 데이터로 변경되었음을 확인할 수 있습니다.
윈도우 파워 쿼리와 비교한 Mac OS 엑셀 파워쿼리
윈도우 엑셀의 파워쿼리와 비교하면 Mac OS 엑셀 파워쿼리로 모든 기능을 사용할 수 있는 것은 아닙니다. 아직 추가 적인 업데이트가 필요하지만 맥 컴퓨터에서도 엑셀 자체의 데이터를 불러와서 파워쿼리 편집기를 실행하고 데이터를 가공할 수 있다는 점은 맥 유저들에게도 좋은 소식이 아닐 수 없습니다. Mac OS 엑셀 파워쿼리에서 아직 사용할 수 없는 기능은 몇 가지가 있지만, 직장인들이 실무에서 가장 많이 사용하는 두가지 기능은 폴더에서 데이터 불러오기와 PDF 형태의 데이터를 불러오는 것이 대표적입니다.
폴더에서 데이터 불러오기
윈도우 엑셀 파워쿼리에서는 폴더 자체에서 데이터를 불러오기가 가능하기 때문에 데이터의 구조가 동일하다면 폴더내로 엑셀 파일을 추가만 한다면 엑셀에서 새로고침 클릭 한 번으로 데이터의 업데이트 관리가 가능합니다. 하지만 Mac OS 엑셀 파워쿼리는 아직 폴더로 부터 가져오기를 할 수 없습니다. 폴더 내의 데이터를 각각 데이터 변환/로드를 통해서 가져와야 합니다.
PDF 파일 불러오기
윈도우 엑셀에서는 파워쿼리를 통해서 PDF 형태의 데이터를 불러올 수 있고 테이블 형태의 데이터를 파워쿼리 편집기로 가공/편집 할 수 있습니다. 하지만, 맥 컴퓨터에서는 PDF 형태의 파일을 불러오는 기능은 아직 제공하지 않습니다.
엑셀 파워쿼리란? 마이크로소프트 공식 홈페이지 바로가기
엑셀 구글 시트 사용법에 관한 유용한 링크는 아래를 참고해주세요.