Excel과 관련된 비즈니스 인텔리전스(BI) 도구로는 파워 쿼리, 파워 피벗, 파워 BI 등이 있습니다. 이들 세 가지 비즈니스 인텔리전스 도구를 소개하고 파워 쿼리를 사용 예제를 살펴봅니다. 생소하고 약간 어려울 수는 있지만 데이터를 전문적으로 다루는 분이라면 참고할 만합니다.
※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: BI tools: Power Query, Power Pivot, and Power BI
- URL: https://www.fm-magazine.com/news/2024/jan/bi-tools-power-query-power-pivot-power-bi.html
BI 도구 개요
Excel을 사용한다면 꼭 알아야 할 세 가지 비즈니스 인텔리전스(BI) 도구가 있습니다. 각 도구의 개념과 특징에 대해 간단히 살펴봅니다.
파워 쿼리
파워 쿼리(Power Query)는 추출, 변환 및 로드(ETL) 도구입니다. 이 소프트웨어를 사용하면 다양한 소스(예: Excel 파일, 인터넷 데이터, SAP 비즈니스 웨어하우스, Access 파일 및 기타 데이터베이스)의 데이터에 연결한 다음 불필요한 데이터 제거, 추가 공백 제거, 요소 피벗 해제 등의 조작을 할 수 있습니다. 뿐만 아니라 버튼 클릭 한 번으로 유사한 데이터에 대해 동일한 작업 세트를 반복할 수 있습니다. BI 도구 모음에서 가장 흥미로운 기능은 아니지만 꼭 필요한 기능입니다.
[참고] Excel 2016 이상 버전과 Office 365에서는 [데이터 가져오기 및 변환]이라고 불렀으며 [데이터] 탭에 있습니다.
파워 피벗
파워 피벗(Power Pivot)은 종종 '피벗테이블의 스테로이드'라고 불립니다. 이 소프트웨어는 '빅 3' BI 도구 중 가장 먼저 시장에 출시되었지만 큰 주목을 받지sms 못했습니다. [파일] - [옵션] - [추가 기능] - [관리] - [COM 추가 기능] - [이동]으로 가서 활성화하기만 하면 2013년부터 대부분의 Excel 버전에 내장되어 있습니다. Excel용 Microsoft Office 파워 피벗 확인란을 선택하면 리본에서 파워 피벗 탭이 활성화됩니다.
파워 BI
Power BI는 그 범위가 진화해 왔습니다. 처음에는 파워 맵과 지금은 없어진 파워 뷰, 파워 피벗 및 파워 쿼리와 같은 전체 Microsoft BI 도구 제품군을 의미했습니다. 하지만 지금은 대시보드 및 보고 측면을 가리키는 말로 바뀌었습니다.
Power BI Desktop은 powerbi.com에서 무료로 다운로드할 수 있는 독립 실행형 소프트웨어입니다. 여기에는 Excel 버전보다 훨씬 더 많은 유형의 데이터 원본에 연결할 수 있는 강력한 버전의 Power Query 엔진이 포함되어 있습니다. Power BI는 Power 피벗과 동일한 언어를 사용하여 측정값(피벗 테이블 내에서 사용할 수 있는 계산) 및 비주얼리제이션을 만들고 몇 초 만에 정보를 분석할 수 있습니다.
만든 결과가 만족스러우면 Power BI 서비스를 사용하여 클라우드에 게시하여 인사이트를 공유하고 대화형 대시보드 및 보고서를 만들 수 있습니다. 사용자의 역할에 따라 데이터가 제한될 수 있습니다.
파워 쿼리 사용 예
전반적인 소개를 마쳤으니, 이제 파워 쿼리를 사용한 적용 사례를 보겠습니다. 직원의 신용카드 지출 내역을 은행에서 다운로드 받는다고 가정해 보겠습니다(아래 이미지 참고).
익숙해 보이시나요? 분석가들은 이 데이터를 보다 사용 가능한 형식으로 변환하기 위해 반복적인 수작업 절차를 거치지 않으려고 하기 때문에 상당한 어려움을 겪습니다(아래 이미지 참고).
이 작업을 몇 초 만에 자동화하여 생성할 수 있다면 멋지지 않을까요? 파워 쿼리/데이터 가져오기 및 변환을 사용하면 이 작업을 수행할 수 있습니다.
(1) 원본 데이터를 선택하고 [데이터] 탭 - [데이터 가져오기 및 변환] 그룹 - [테이블/범위에서]를 클릭합니다(Excel 버전에 따라 인터페이스가 조금씩 다를 수 있습니다).
(2) 표 영역을 확인하고 제목이 없음을 표시하면 파워 쿼리 편집기가 열리고 전체 목록이 강조 표시된 상태로 표시됩니다. 옆에 있는 십자 표시를 사용하여 필드에 텍스트, 숫자, 날짜 등이 포함되어 있는지 여부를 결정하는 변경된 유형 단계를 삭제할 수 있습니다. 열에 데이터 유형이 혼합되어 있으므로 나중에 유형을 분할할 때 유형을 정의할 수 있습니다.
(3) [열 추가] 탭으로 이동하여 인덱스 열의 드롭다운 목록에서 0부터를 선택합니다. 그러면 0에서 시작하는 두 번째 열에 순차 카운터가 생성됩니다. 파워 쿼리가 단계를 기록하므로 이러한 변환을 한 번만 수행하고 그 이후에는 자유롭게 사용하면 됩니다.
(4) 이제 인덱스 열을 선택하고 리본의 [열 추가] 탭에 있는 표준 드롭다운 상자에서 나누기(정수)를 선택합니다. 이렇게 하면 인덱스를 숫자로 나눌 수 있는 다른 열을 추가할 수 있으며, 이 열은 정수만 반환합니다. 하지만 우리가 원하는 숫자는 두 번째 데이터 블록의 첫 번째 행에 대한 인덱스 번호입니다(여기서는 5).
(5) [확인]을 클릭하면 두 번째 열이 추가됩니다.
(6) 세 번째 열인 Integer-Division은 데이터의 행 번호를 나타냅니다(따라서 동일한 Integer-Division 번호를 가진 모든 행은 생성될 최종 테이블의 동일한 행에 위치하게 됩니다). 이를 생성하려면 두 번째 열인 Index가 필요했습니다. 이제 다른 용도로 Index를 사용해야 합니다.
(7) 인덱스 열을 다시 선택하고 리본의 변환 탭에 있는 표준 드롭다운 상자에서 모듈로를 선택합니다. 여기서도 앞에서와 같은 이유로 숫자 5를 사용합니다.
(8) [확인]을 클릭하면 색인 필드에 반복 시퀀스 0, 1, 2, 3, 4가 생성됩니다. 이게 무슨 뜻인지 이해가 되시나요? 세 번째 열(정수-분할)을 행 번호로 사용하고 Index를 열 번호로 사용하면 Column1의 각 값에 대한 좌표를 얻을 수 있습니다. 인덱스 열을 선택하고 리본의 변환 탭에서 피벗 열을 클릭하기만 하면 됩니다.
(9) 결과 대화 상자에서 값에 대해 Column1을 선택합니다. 하지만 항목의 개수만 가져오기 때문에 이것만으로는 충분하지 않습니다. 고급 옵션 상자의 값 집계 함수에서 집계하지 않음을 선택합니다(이렇게 하면 값이 텍스트일 때 표시할 수 있습니다). 다운로드 가능한 스크린샷 "Column1 선택 및 집계하지 않기"를 참조하세요.
(10) [확인]을 클릭하면 모든 항목이 표시됩니다.
(11) 정리하려면 첫 번째와 마지막 열을 제거하고 각 필드의 이름을 바꿉니다. 각 열을 우클릭하면 이러한 옵션에 액세스할 수 있습니다. 그런 다음 Ctrl + A를 사용하여 모든 열을 선택하고 변환 탭의 데이터 유형 검색을 사용하여 파워 쿼리 알고리즘을 통해 적절한 데이터 유형을 선택합니다.
(12) 완료되면 [홈] 탭 - [닫기 및 로드]를 선택하기만 하면 됩니다.
'Excel' 카테고리의 다른 글
| Excel 시트에 데이터를 가져오거나 제외하는 쉬운 방법 (120) | 2024.01.29 |
|---|---|
| [Excel 입문] 09. 잘못된 자료 입력 막기 ㅡ 데이터 유효성 검사 (203) | 2024.01.26 |
| AI 도구를 사용하여 Excel 기술을 업그레이드하는 방법 (4) | 2024.01.22 |
| [Excel 입문] 08. 조건에 맞는 자료만 골라서 보기 ㅡ 필터 (6) | 2024.01.19 |
| Microsoft Word 및 Excel 스프레드시트를 잠그는 방법 (2) | 2024.01.17 |
