엑셀 파워쿼리 사용법 및 오류 해결 팁 예제 활용 총정리

안녕하세요! 오늘은 엑셀 파워쿼리 사용법 및 오류 해결 팁에 대해 총정리해보겠습니다. 파워쿼리는 데이터 분석에 있어 매우 강력한 도구인데요, 이제 그 사용법을 하나하나 배워보겠습니다. 시작하기 전에 간단한 예제를 통해 쉽게 이해할 수 있도록 하겠습니다.

엑셀 쿼리란(Power Query)?

엑셀 파워쿼리는 엑셀의 데이터 연결, 변환, 조작 기능을 확장해주는 도구입니다. 데이터 정리, 필터링, 변환 등을 자동화하여 반복적인 작업을 줄여주는데요, 특히 여러 데이터 소스를 통합하거나 복잡한 데이터 처리 작업을 할 때 매우 유용합니다.

예를 들어, 여러 장부에서 데이터를 가져와 하나의 통합된 보고서를 만들고 싶을 때 파워쿼리를 사용하면 시간을 크게 절약할 수 있습니다.

엑셀 쿼리 만들기

먼저, 엑셀 쿼리를 만드는 방법을 알아보겠습니다.

엑셀 열기: 엑셀을 열고 ‘데이터’ 탭으로 이동합니다.
데이터 가져오기: ‘데이터 가져오기’ 버튼을 클릭하여 다양한 데이터 소스로부터 데이터를 가져올 수 있습니다. 예를 들어, ‘파일에서’를 선택하고 ‘엑셀 파일에서’를 클릭합니다.
파일 선택: 데이터를 가져올 엑셀 파일을 선택합니다. 그러면 ‘쿼리 편집기’가 열립니다.
쿼리 편집: 쿼리 편집기에서 데이터를 원하는 대로 변환할 수 있습니다. 필터링, 정렬, 열 추가 등을 할 수 있습니다.
쿼리 저장: 작업이 완료되면 ‘홈’ 탭에서 ‘닫기 및 로드’를 클릭하여 쿼리를 엑셀 워크시트에 로드합니다.

엑셀 파워쿼리 데이터 가져오기

파워쿼리를 사용하여 데이터를 가져오는 방법을 자세히 살펴보겠습니다.

데이터 소스 선택: ‘데이터’ 탭에서 ‘데이터 가져오기’를 클릭하고 원하는 데이터 소스를 선택합니다. 엑셀 파일, 텍스트 파일, 데이터베이스, 웹 데이터 등 다양한 소스를 선택할 수 있습니다.
데이터 연결: 데이터 소스를 선택하면 연결 설정을 해야 합니다. 예를 들어, SQL 데이터베이스에 연결할 때는 서버 이름, 데이터베이스 이름, 인증 정보 등을 입력해야 합니다.
데이터 미리보기 및 선택: 연결이 완료되면 데이터 미리보기가 표시됩니다. 필요한 데이터를 선택하고 ‘로드’를 클릭합니다.
쿼리 편집기 사용: 데이터가 로드되면 쿼리 편집기에서 데이터를 편집할 수 있습니다. 불필요한 열을 제거하거나, 데이터 형식을 변환하거나, 필터를 적용할 수 있습니다.
데이터 로드: 편집이 완료되면 ‘닫기 및 로드’를 클릭하여 데이터를 워크시트에 로드합니다.

파워쿼리 문법

파워쿼리에서는 다양한 문법을 사용하여 데이터를 변환할 수 있습니다. 기본적인 문법을 몇 가지 살펴보겠습니다.

기본 문법

let: 쿼리의 시작을 나타냅니다.
in: 쿼리의 끝을 나타냅니다.
=: 값을 할당합니다.
#” ” (Hash and quotes): 공백이나 특수문자가 포함된 이름을 사용할 때 사용합니다.

예제

let
    Source = Excel.Workbook(File.Contents("C:\Example.xlsx"), null, true),
    Data = Source{[Name="Sheet1"]}[Data],
    FilteredData = Table.SelectRows(Data, each [Age] > 30)
in
    FilteredData

위 예제는 ‘Example.xlsx’ 파일의 ‘Sheet1’ 시트를 불러오고, 나이가 30세 이상인 행만 선택하는 쿼리입니다.

엑셀 쿼리 병합

쿼리 병합은 여러 소스에서 데이터를 가져와 하나의 쿼리로 통합하는 기능입니다.

쿼리 선택: ‘쿼리 편집기’에서 병합할 쿼리를 선택합니다.
병합 옵션: ‘홈’ 탭에서 ‘쿼리 병합’을 클릭합니다.
병합 설정: 병합할 두 쿼리와 연결할 열을 선택합니다.
병합 수행: 병합된 쿼리가 생성됩니다. 필요에 따라 추가 변환 작업을 수행합니다.
쿼리 저장: ‘닫기 및 로드’를 클릭하여 병합된 쿼리를 엑셀에 로드합니다.

예제

예를 들어, 두 개의 테이블이 있다고 가정해보겠습니다.

Table1: 고객 정보 (ID, 이름)
Table2: 주문 정보 (ID, 주문 날짜, 금액)

ID를 기준으로 두 테이블을 병합하면 고객별 주문 내역을 하나의 테이블로 볼 수 있습니다.

엑셀 쿼리 피벗

쿼리 피벗은 데이터를 요약하고 집계하는 데 사용됩니다.

쿼리 선택: 쿼리 편집기에서 피벗할 쿼리를 선택합니다.
피벗 옵션: ‘변환’ 탭에서 ‘피벗 열’을 클릭합니다.
피벗 설정: 피벗할 열과 값을 선택합니다. 예를 들어, ‘상품’ 열을 피벗하고 ‘판매량’을 값으로 사용합니다.
피벗 수행: 피벗된 데이터가 생성됩니다. 필요에 따라 추가 변환 작업을 수행합니다.
쿼리 저장: ‘닫기 및 로드’를 클릭하여 피벗된 데이터를 엑셀에 로드합니다.

예제

예를 들어, 다음과 같은 판매 데이터가 있다고 가정해보겠습니다.

날짜	상품	판매량
2023-01-01	A	10
2023-01-01	B	20
2023-01-02	A	15
2023-01-02	B	25

이 데이터를 피벗하여 날짜별로 상품 판매량을 요약할 수 있습니다.

엑셀 쿼리 연결

엑셀 쿼리를 다른 쿼리와 연결하여 데이터를 통합하는 방법을 알아보겠습니다.

쿼리 선택: 쿼리 편집기에서 연결할 쿼리를 선택합니다.
쿼리 연결: ‘홈’ 탭에서 ‘쿼리 연결’을 클릭합니다.
연결 설정: 연결할 쿼리와 연결할 열을 선택합니다.
연결 수행: 연결된 쿼리가 생성됩니다. 필요에 따라 추가 변환 작업을 수행합니다.
쿼리 저장: ‘닫기 및 로드’를 클릭하여 연결된 쿼리를 엑셀에 로드합니다.

예제

예를 들어, 두 개의 테이블이 있다고 가정해보겠습니다.

Table1: 고객 정보 (ID, 이름)
Table2: 주문 정보 (ID, 주문 날짜, 금액)

ID를 기준으로 두 테이블을 연결하여 고객별 주문 내역을 하나의 테이블로 볼 수 있습니다.

엑셀 쿼리 함수

파워쿼리에서는 다양한 함수를 사용할 수 있습니다. 몇 가지 유용한 함수를 살펴보겠습니다.

기본 함수

Text.Proper: 텍스트를 첫 글자만 대문자로 변환합니다.
Number.Round: 숫자를 반올림합니다.
DateTime.Date: 날짜 및 시간에서 날짜 부분만 추출합니다.

예제

let
    Source = Excel.Workbook(File.Contents("C:\Example.xlsx"), null, true),
    Data = Source{[Name="Sheet1"]}[Data],
    ProperName = Table.TransformColumns(Data, {"Name", Text.Proper}),
    RoundedAmount = Table.TransformColumns(ProperName, {"Amount", Number.Round, 2})
in
    RoundedAmount

위 예제는 ‘Example.xlsx’ 파일의 ‘Sheet1’ 시트를 불러오고, 이름을 첫 글자만 대문자로 변환하며, 금액을 소수점 둘째 자리에서 반올림하는 쿼리입니다.

맥 엑셀 파워쿼리

맥에서도 파워쿼리를 사용할 수 있습니다. 몇 가지 제한 사항이 있을 수 있지만, 기본적인 기능은 동일합니다.

엑셀 설치: 맥용 엑셀을 설치합니다.
데이터 탭 이동: ‘데이터’ 탭으로 이동합니다.
**데

이터 가져오기**: ‘데이터 가져오기’를 클릭하여 데이터를 가져옵니다.

쿼리 편집: 쿼리 편집기를 사용하여 데이터를 편집합니다.
쿼리 저장: ‘닫기 및 로드’를 클릭하여 쿼리를 엑셀 워크시트에 로드합니다.

파워쿼리 자동화

파워쿼리의 자동화 기능을 활용하면 반복적인 작업을 자동으로 처리할 수 있습니다.

쿼리 작성: 원하는 데이터를 변환하는 쿼리를 작성합니다.
자동 갱신 설정: ‘쿼리 편집기’에서 ‘쿼리 옵션’을 클릭하고 ‘자동 갱신’을 설정합니다.
매크로 사용: VBA 매크로를 작성하여 쿼리를 자동으로 실행하도록 설정할 수 있습니다.

예제

예를 들어, 매일 아침 엑셀 파일을 열 때 자동으로 최신 데이터를 가져오도록 설정할 수 있습니다. 다음은 VBA 매크로 예제입니다.

Sub RefreshAllQueries()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    Next ws
End Sub

이 매크로는 모든 워크시트의 쿼리를 갱신합니다.

자주 묻는 질문(FAQ)

Q1: 파워쿼리를 사용하면 엑셀의 성능이 느려질까요?
A1: 파워쿼리는 대용량 데이터를 처리할 때 유용하지만, 데이터 크기에 따라 엑셀의 성능이 느려질 수 있습니다. 이 경우, 필요 없는 데이터를 제거하거나 쿼리를 최적화하여 성능을 개선할 수 있습니다.

Q2: 파워쿼리에서 데이터를 병합할 때 중복된 데이터를 제거하려면 어떻게 해야 하나요?
A2: ‘쿼리 편집기’에서 ‘제거’ 탭을 클릭하고 ‘중복 항목 제거’를 선택하여 중복된 데이터를 제거할 수 있습니다.

Q3: 파워쿼리를 사용하여 웹 데이터를 가져올 수 있나요?
A3: 네, ‘데이터 가져오기’에서 ‘웹에서’ 옵션을 선택하여 웹 데이터를 가져올 수 있습니다. 웹 페이지 URL을 입력하면 됩니다.

Q4: 파워쿼리에서 특정 열만 선택하여 가져올 수 있나요?
A4: 네, ‘쿼리 편집기’에서 ‘열 선택’ 옵션을 사용하여 필요한 열만 선택할 수 있습니다.

Q5: 맥에서도 파워쿼리 기능을 사용할 수 있나요?
A5: 네, 맥용 엑셀에서도 파워쿼리를 사용할 수 있지만, 일부 기능은 제한될 수 있습니다. 최신 버전의 엑셀을 사용하는 것이 좋습니다.

이상으로 엑셀 파워쿼리 사용법 및 오류 해결 팁에 대해 알아보았습니다. 파워쿼리를 잘 활용하면 데이터 처리 작업을 효율적으로 수행할 수 있습니다. 궁금한 점이 있으면 언제든지 질문해 주세요!

마이크로소프트 엑셀지원센터