Excel에서 Power Query 및 Power Pivot을 전문가처럼 사용하는 방법

스프레드시트 파워 유저가 되고 싶다면 Excel의 Power Query 및 Power Pivot 기능에 대해 배워야 합니다. Excel만으로도 많은 작업을 수행할 수 있지만, 이러한 내장된 “Power” 기능을 사용하면 고급 전문 Excel 사용자로 거듭날 수 있습니다. 이 가이드에서는 Power Query를 사용하여 수백 개의 다양한 파일을 수백만 개의 행과 함께 가져오고, Power Pivot을 사용하여 가져온 방대한 데이터 모델에 대해 복잡한 분석을 생성하는 방법을 배웁니다.

목차

• 1. 여러 파일에서 데이터 가져오기
• 2. 가져온 파일 및 행 필터링
• 3. 필터링된 데이터 데이터 모델에 로드하기
• 4. PivotTable로 데이터 분석하기
• 5. PowerChart로 데이터 프레젠테이션하기
• 자주 묻는 질문

작업을 엿보는 눈으로부터 보호하고 싶으신가요? Excel 통합 문서에 비밀번호를 추가하는 방법을 배우세요.

1. 여러 파일에서 데이터 가져오기

Power Query를 사용하면 단일 폴더의 여러 파일에서 데이터를 가져올 수 있습니다. 그러나 모든 데이터가 동일한 형식을 따르도록 해야 합니다. 열 수와 열 헤더 이름을 동일하게 유지하고 각 열의 값에 대한 데이터 유형이 일관되도록 하세요.

이 가이드에서는 Kaggle에서 가져온 약 1억 개의 행으로 구성된 5개의 파일을 사용합니다. 따라 해보고 싶다면 다운로드하거나 자신의 데이터를 사용할 수 있습니다. 이를 염두에 두고 데이터를 가져오는 단계는 다음과 같습니다:

1. 리본에서 “데이터” 탭을 클릭합니다.
2. “데이터 가져오기 -> 파일에서 -> 폴더에서”를 선택한 다음, 폴더를 찾아 선택합니다.

1. 새로운 창이 열립니다. 이 경우, 크기에 따라 이름이 지정된 5개의 테스트 파일이 있습니다: “adult10m”은 1000만 개의 데이터 행을 가지고 있고, “adult1m”은 100만 개의 행, “adult100k”는 10만 개의 행을 가지고 있습니다. “데이터 변환“을 클릭하여 Power Query 편집기를 엽니다.

2. 가져온 파일 및 행 필터링

Power Query 편집기에서 설정한 기준에 맞는 파일만 가져오도록 필터를 적용할 수 있습니다. 이는 원하지 않는 데이터 소스를 제거하는 데 매우 유용합니다.

1. 파일 확장자가 없는 4개의 파일과 .DATA 확장자를 가진 1개의 파일이 있습니다. “확장자” 옆의 화살표를 클릭하여 이상한 파일을 제거합시다.

1. 모든 파일의 파일 확장자 목록이 표시됩니다. 이 예에서는 (빈)과 .data만 있습니다. 원하지 않는 확장자의 선택을 해제합니다.

1. 적용한 각 필터는 “적용된 단계” 섹션에 표시됩니다.
2. “이름” 옆의 화살표를 클릭하여 파일 이름으로 필터링할 수도 있습니다.

1. “k”가 포함된 파일을 필터링하고 싶다고 가정해 보겠습니다. “adult100k”와 같이 100만 개 미만의 행을 포함하는 작은 파일로 작업하고 싶지 않기 때문입니다. 텍스트 필터를 적용할 수 있습니다. 여러 옵션이 있지만, 우리의 목적에 맞는 “포함하지 않음” 필터가 적합합니다.

1. 파일 이름에서 보고 싶지 않은 텍스트를 입력합니다.

1. “수정된 날짜” 및 “생성된 날짜” 열이 보일 때까지 오른쪽으로 스크롤합니다. 특정 시간 범위 내에서 생성된 파일만 선택하도록 필터링합시다. “생성된 날짜” 옆의 화살표를 클릭합니다.

1. “날짜/시간 필터 -> 사이”를 선택합니다.

1. 두 조건에 대한 시간 값을 입력하고 “확인”을 눌러 확인합니다.

1. 파일별로 데이터 필터링을 완료했습니다.
2. 남은 파일을 결합하여 데이터 자체를 필터링할 수 있도록 합시다. “내용” 헤더 옆의 두 개의 화살표를 클릭합니다.

1. 이제 개별 행의 모든 값을 볼 수 있으며, 각 행에는 “Source.Name”이라는 추가 열이 있어 원본 파일을 나타냅니다.

1. 데이터를 더욱 개선하기 위해: CSV 파일은 원래 열 헤더 이름이 없으므로, 일부 열 헤더를 마우스 오른쪽 버튼으로 클릭하고 “이름 바꾸기”를 클릭하여 적절한 이름을 부여할 수 있습니다.

1. 필터도 여기에서 작동합니다. 숫자 범위 필터를 적용할 수 있습니다. 예를 들어, “나이” 열 헤더 옆의 화살표를 클릭한 다음 “숫자 필터 -> 초과”를 선택합니다.

1. 30과 같은 숫자를 입력한 후 “확인”을 클릭합니다. 지정된 숫자보다 낮은 모든 행은 제외됩니다.

팁: Microsoft Excel에서 스프레드시트를 작업할 때 열을 이동하는 방법을 아는 것이 유용할 수 있습니다.

3. 필터링된 데이터를 데이터 모델에 로드하기

데이터를 계속 정리하고 필터링할 수 있지만, 분석을 시작하기 위해 데이터 모델에 로드합시다.

1. “닫기 및 로드 -> 닫기 및 데이터 모델에 로드”를 클릭하여 Power Query를 마무리합니다.
2. 열리는 팝업에서 “연결만 생성”을 선택하고 “이 데이터를 데이터 모델에 추가“를 체크합니다.

1. 데이터가 로드될 때까지 기다립니다. 데이터 양이 많으면 몇 분이 걸릴 수 있습니다.

Excel에서 Power Pivot 데이터 모델 사용하기

1. 데이터 로딩이 완료되면 “데이터 -> 데이터 도구 -> 데이터 모델 관리”를 클릭하여 데이터 모델에 변경 사항(예: 새 열 추가)을 적용할 수 있습니다.

1. Power Pivot 창이 열립니다. 데이터 분석 표현식(DAX) 수식을 사용하여 열을 추가합시다. DAX 수식은 아마도 이미 익숙한 Excel 수식과 매우 유사합니다. 데이터를 오른쪽으로 스크롤하여 마지막 열을 본 다음 “열 추가“를 클릭합니다.
2. 이 열에 대해 모든 행에 간단한 계산을 적용하는 수식을 작성합시다. Excel 수식과 비교할 때 DAX 구문은 약간 다릅니다. 예를 들어, “Description”이라는 열을 추가하고 수식 =CONCATENATE([Column9],[Column10])을 입력할 수 있습니다. 대괄호([ ])의 사용과 열 헤더 이름을 통한 접근을 주목하세요.

1. 원하신다면 열 헤더를 마우스 오른쪽 버튼으로 클릭하여 이름을 바꾸거나 제거하는 등의 추가 수정을 수행할 수 있습니다.

4. PivotTable로 데이터 분석하기

데이터 모델에 대한 변경을 완료한 후, Excel 워크시트에서 PivotTable을 사용하여 데이터를 분석할 시간입니다.

1. Power Pivot 창의 리본에서 “홈 -> PivotTable”을 클릭하여 “PivotTable 만들기” 창을 엽니다.

1. “새 워크시트”를 선택하고 “확인”을 누릅니다.

1. PivotTable 필드 패널이 Excel 메인 창의 새 시트에 열립니다. 검색창 아래의 상자에서 데이터 소스가 나열되어 있는 것을 볼 수 있습니다. 이 예에서는 “kaggle_adult_csv”입니다. 이를 클릭하여 확장하고 모든 열을 표시합니다.

1. 데이터에서 각 “Description”의 수를 세는 분석을 해봅시다. 이름 옆의 체크박스를 클릭하여 작업할 열을 선택할 수 있습니다. “Description”을 선택하면 자동으로 “행” 영역으로 초점이 이동하는 것을 알 수 있습니다. 이는 Power Pivot이 지능적으로 선택에 적합한 영역을 추정하는 것입니다. 빈 PivotTable에서 시작하므로 첫 번째 선택이 행인 것이 합리적입니다.

1. 체크된 “Description” 열을 “값”의 빈 공간으로 드래그합니다. Power Pivot이 데이터 유형이 텍스트이기 때문에 “Description의 수“를 원한다고 다시 한번 정확하게 추정한 것을 알 수 있습니다. 또한, 테이블이 각 “Description” 값의 발생 수를 제공하는 열로 업데이트되었습니다.

1. 카운팅에 또 다른 차원을 추가할 수 있습니다. 예를 들어, “Age” 열을 빈 “열” 영역으로 드래그하여 각 나이에 따른 설명 수를 세는 테이블에 더 많은 세부 정보를 추가합니다.

1. 테이블에 필터 선택 기능을 추가하려면 “WorkClass” 열을 “필터” 영역으로 드래그합니다. 테이블 위에 새로운 대화형 셀이 나타나는 것을 확인하세요.

1. “모두” 옆의 화살표를 클릭한 다음 “Never-worked”를 선택합니다. 이렇게 하면 계산에서 “Never-worked”의 WorkClass를 가진 사람만 포함되도록 테이블이 변경됩니다. 언제든지 필터를 변경하여 다른 관점에서 테이블을 볼 수 있습니다.

Excel 통합 문서에서 많은 중복을 처리하고 있나요? 중복을 찾고 제거하여 스프레드시트를 정리하는 방법을 배우세요.

5. PowerChart로 데이터 프레젠테이션하기

데이터를 다양한 방식으로 분석하기 위해 더 많은 워크시트를 만들 수 있습니다. 이번에는 PowerChart를 사용하여 더 시각적으로 인상적인 결과를 만들어 봅시다.

1. “Power Pivot for Excel” 창으로 돌아갑니다. 닫혀 있다면 Excel 창에서 “데이터 -> 데이터 도구 -> 데이터 모델 관리”를 통해 열 수 있습니다.
2. “PivotTable -> PivotChart -> 새 워크시트”를 클릭합니다.

1. 오른쪽 패널에서 데이터 소스(“kaggle_adult_csv”)를 클릭하여 확장하고 모든 열을 표시합니다.
2. 데이터 소스에서 열을 드래그하여 아래의 네 개 영역 중 하나에 놓아 차트를 만듭니다. “OccupationType” 열을 “축(카테고리)” 영역으로 이동합니다.

1. “OccupationType”을 다시 드래그하여 이번에는 “값” 영역에 놓습니다. 그러면 자동으로 막대 그래프가 생성됩니다. 각 직업 유형의 수를 보여줍니다.

1. “Ethnicity” 열을 “범례(시리즈)” 영역으로 드래그할 수도 있습니다. 그러면 각 직업을 구성하는 각 민족의 수를 비교하는 더 세분화된 차트를 즉시 볼 수 있습니다.

1. 다음으로, 차트를 단순화하고 특정 값만 보려면 범례에 필터를 적용할 수 있습니다. “Ethnicity” 위로 마우스를 이동하면 오른쪽에 화살표가 나타납니다. 화살표를 클릭합니다.

1. 제거할 값 옆의 체크박스를 클릭하여 두 개만 남깁니다.

1. 새로운 것을 시도해 봅시다. “값” 섹션에서 “OccupationType의 수“를 클릭한 다음 “필드 제거”를 클릭합니다. “Age” 열을 드래그하여 이를 대체합니다.

1. 값이 자동으로 “Age의 합계”로 정의되는 것을 알 수 있지만, 이는 실제 세계에서 그리 유용하지 않습니다. 이를 변경합시다. 클릭하고 “값 필드 설정”을 선택합니다.

1. 나이는 숫자이므로 이 섹션에 적용할 수 있는 계산이 많습니다. 예를 들어 “평균”을 선택해 보세요.

1. 시각이 업데이트되어 특정 민족(우리가 필터링한)별로 각 직업의 평균 연령을 보여줍니다.

Excel 기본 사항을 복습하고 싶으신가요? Excel의 모든 키보드 단축키에 대한 포괄적인 가이드가 있습니다.

자주 묻는 질문

Power Pivot의 기원은 무엇인가요?

Microsoft는 2010년에 출시된 Microsoft SQL Server 2008 R2에서 제공되는 별도의 추가 기능으로 Power Pivot을 도입했습니다. 그 당시에는 “PowerPivot”이라는 이름으로 공백 없이 불렸습니다. 2013년에 “Power Pivot”으로 이름이 변경되었습니다. 2016년부터 Excel의 내장 기능이 되었습니다.

데이터 분석 표현식은 무엇이며 Excel 수식과 어떻게 다른가요?

데이터 분석 표현식은 Excel의 Power Pivot 및 Power BI에서 사용되는 수식 언어입니다. 본질적으로, 이는 앞서 언급한 애플리케이션에서 계산을 위한 수식을 작성하는 방법입니다: 사용하는 문장, 구문 및 함수입니다.

DAX의 주요 장점은 집계 데이터에서 작동하도록 설계되었다는 점입니다. 표준 Excel 수식은 행 단위로 계산을 수행할 수 있지만(예: 행의 여러 값의 합계 계산), DAX 수식은 열 단위로 작동할 수 있습니다(예: 특정 값의 수를 세는 것) 또는 전체 테이블에서 작동할 수 있습니다.

“이 테이블은 쿼리에 의해 생성되었습니다. 이 테이블을 변경하려면 쿼리를 대신 변경하십시오” 오류를 어떻게 수정하나요?

Excel Power Pivot에서 이 오류는 Power Query를 통해 원래 로드된 데이터 모델 내에서 수정하려고 할 때 발생합니다. 이를 수행할 수 없으므로 원래 로드 방법인 Power Query를 통해 수정해야 합니다.

Excel 오른쪽에서 “쿼리 및 연결” 아이콘을 클릭한 다음, 쿼리 데이터 소스를 찾아 두 번 클릭하여 테이블을 수정할 수 있는 창을 엽니다.

이미지 출처: Pexels. 모든 스크린샷은 Brandon Li가 촬영했습니다.