[고급] 엑셀 파워쿼리(Power Query) 기초 - 데이터 분석 자동화

엑셀 파워쿼리(Power Query) 기초 - 데이터 분석 자동화

엑셀(Excel)에서 대량 데이터를 정리하고 변환하는 작업은 시간이 많이 걸릴 수 있다. 특히 여러 개의 파일을 합치거나, 중복 데이터를 제거하고, 데이터를 자동으로 업데이트하는 작업은 수작업으로 하기엔 번거롭다. 이때, 파워쿼리(Power Query)를 사용하면 데이터를 자동으로 불러오고, 변환하며, 클릭 한 번으로 갱신할 수 있어 데이터 분석 자동화가 가능하다. 이번 글에서는 엑셀 파워쿼리의 개념, 주요 기능, 실전 활용법을 단계별로 자세히 설명하겠다.

➔ 파워쿼리(Power Query)란?

◆ 파워쿼리의 개념
파워쿼리(Power Query)는 엑셀에서 데이터를 가져오고, 변환하며, 자동으로 갱신할 수 있는 강력한 데이터 분석 도구이다.  엑셀의 기본 기능보다 더 강력한 데이터 정리 및 자동화 기능을 제공한다. 파일, 데이터베이스, 웹, API 등 다양한 외부 데이터 소스를 가져올 수 있으며, 이를 가공하여 분석 가능하다.

◆ 파워쿼리의 주요 기능

데이터 불러오기: 엑셀, CSV, 데이터베이스, 웹 등에서 데이터를 가져올 수 있음
데이터 정리 및 변환: 중복 제거, 필터링, 병합, 계산 열 추가 등 다양한 데이터 변환 가능
여러 파일 통합: 여러 개의 엑셀 파일, CSV 파일을 하나의 테이블로 결합 가능
자동 업데이트: 원본 데이터가 변경될 때 클릭 한 번으로 업데이트 가능

★ Tip: 엑셀 2016 이상 버전에서는 기본 기능으로 제공되며, 엑셀 2010/2013은 추가 기능(Add-in)으로 설치 가능하다.
⇒ 파워쿼리를 활용하면 반복적인 데이터 정리 작업을 자동화할 수 있다.

➔ 파워쿼리 실행 방법

◆ 파워쿼리 창 열기

1. 엑셀 2016 이상 :  데이터 → 데이터 가져오기 → 파워쿼리 편집기 클릭
2. 엑셀 2013 이하 (추가 기능 필요) : 파일 → 옵션 → 추가 기능 → COM 추가 기능에서 “Power Query” 활성화

★ Tip: Ctrl + Alt + A를 누르면 빠르게 파워쿼리 편집기를 실행할 수 있다.
⇒ 파워쿼리 창을 실행한 후, 데이터를 불러와 다양한 변환 작업을 수행할 수 있다.

 

➔ 실전 예제: 엑셀 파일에서 데이터 가져와 자동 정리하기

◆ 예제 1: 엑셀 파일에서 중복 제거 및 데이터 정리

예제 데이터 (엑셀 파일)

◇ 목표:
• 중복된 데이터 제거
• 매출액이 1000만 원 이상인 제품만 필터링

◇ 파워쿼리 적용 방법

1. 데이터 가져오기
• 데이터 → 데이터 가져오기 → 엑셀 파일 선택
• 원하는 시트 선택 후 “데이터 변환” 클릭

2. 중복 제거
• 홈 → 중복된 항목 제거 선택

3. 필터 적용 (매출액 ≥ 1000만 원)
• 매출액 열 선택 → 필터 아이콘 클릭 → 1000 이상 값만 남기기

4. 변경 내용 적용
• 닫기 & 로드 클릭하여 엑셀 워크시트에 결과 반영

★ Tip: 원본 데이터가 변경되었을 때 데이터 새로 고침 버튼을 누르면 자동으로 업데이트됨
⇒ 이제 중복을 자동으로 제거하고, 필터링된 데이터를 유지할 수 있다.

 

➔ 실전 예제: 여러 개의 CSV 파일 합치기

◆ 예제 2: 폴더에 있는 CSV 파일 자동으로 통합하기

사용 시나리오
• 매월 새로운 매출 데이터(CSV 파일)가 폴더에 추가됨
• 모든 파일을 하나로 합쳐 월별 매출 보고서를 자동 생성

◇ 파워쿼리 적용 방법

1. 폴더에서 데이터 가져오기
• 데이터 → 데이터 가져오기 → 폴더에서 선택
• CSV 파일이 있는 폴더 선택 후 “확인”

2. 파일 통합
• 파일 내용 결합 → 변환 편집 클릭
• 불필요한 열 제거

3. 새로운 데이터 추가 시 자동 업데이트 설정
• 닫기 & 로드 후, 엑셀에서 데이터 새로 고침 클릭

★ Tip: 새 CSV 파일이 추가되면 자동으로 모든 데이터를 업데이트 가능
⇒ 매월 수작업으로 데이터를 합칠 필요 없이 자동으로 데이터 통합이 가능하다.

➔ 실전 예제: 데이터베이스(SQL)에서 데이터 불러오기

◆ 예제 3: SQL 서버에서 데이터 가져오기

◇ SQL 서버에서 데이터를 불러와 엑셀에서 분석하는 경우
1. 데이터 → 데이터 가져오기 → 데이터베이스 → SQL Server 선택
2. 서버 주소 입력 후 “연결”
3. 원하는 테이블 선택 후 “데이터 변환” 클릭
4. 필요 없는 열 제거 후 “닫기 & 로드”

★ Tip: SQL에서 데이터를 불러온 후, 파워쿼리에서 가공 후 자동 업데이트 가능
⇒ SQL 데이터를 엑셀에서 실시간 분석할 때 매우 유용하다.

➔ 파워쿼리 데이터 자동화 기능

◆ 데이터 새로 고침 (자동 업데이트 설정)
◇ 데이터 → 모두 새로 고침 클릭 시 최신 데이터로 업데이트
◇ 특정 주기마다 자동으로 업데이트하려면
• 데이터 → 연결 속성 → 새로 고침 간격 설정

★ Tip: 데이터 원본이 변경되더라도 손쉽게 최신 데이터를 반영 가능
⇒ 반복적인 데이터 입력 없이 자동화된 데이터 분석이 가능하다.

[ 엑셀 파워쿼리 활용 요약 정리 ]

◇ 엑셀에서 데이터를 자동으로 가져오고 변환할 수 있는 강력한 기능
◇ 중복 제거, 필터링, 데이터 병합, 여러 파일 통합이 가능
◇ SQL 서버 등 다양한 데이터 소스와 연결 가능
◇ 새로운 데이터 추가 시 클릭 한 번으로 자동 업데이트 가능

⇒ 파워쿼리를 활용하면 반복적인 데이터 정리 작업을 자동화하여 업무 효율성을 극대화할 수 있다!


#엑셀파워쿼리 #엑셀자동화 #엑셀데이터분석 #엑셀파일통합 #엑셀중복제거 #엑셀데이터정리 #엑셀실무 #엑셀팁 #엑셀파워쿼리활용 #엑셀SQL데이터연결