Marisela Ordaz가 번역한 이 문서의 버전 여기에서 데이터 세트를 다운로드하여 튜토리얼을 따라하세요.
재무, 재무 분석, 금융 시장 및 재무 투자를 아우르는 다양한 분야와 하위 분야에서 Microsoft Excel은 왕입니다. 그러나 수십 년간의 데이터 집계 및 축적, 저렴한 클라우드 스토리지의 출현 및 사물 인터넷(예:전자 상거래, 소셜 미디어, 장치의 상호 연결성)의 등장으로 인해 빅 데이터의 도래 및 기하급수적인 성장이 이루어짐에 따라 Excel의 레거시 기능이 한계에 도달했습니다.
더 구체적으로 말하면 이전 세대 Excel의 인프라 및 처리 제한(행 제한 1,048,576행) 또는 대용량 데이터 세트, 데이터 테이블 및 상호 연결된 스프레드시트와 관련된 불가피한 처리 속도 저하로 인해 효과적인 빅 데이터 도구로서의 유용성이 감소했습니다. 그러나 2010년에 Microsoft는 Power Pivot이라는 새로운 차원을 Excel에 추가했습니다. Power Pivot은 처리 속도 저하 없이 거의 무제한에 가까운 데이터 세트를 추출, 결합 및 분석하는 기능을 통해 Excel에 차세대 비즈니스 인텔리전스 및 비즈니스 분석 기능을 제공했습니다. 그러나 8년 전에 출시되었음에도 불구하고 대부분의 재무 분석가는 여전히 Power Pivot을 사용하는 방법을 모르며 많은 사람들이 Power Pivot이 있는지조차 모릅니다.
이 기사에서는 Power Pivot을 사용하여 일반적인 Excel 문제를 극복하고 몇 가지 예를 사용하여 소프트웨어의 추가 주요 이점을 살펴보는 방법을 보여 드리겠습니다. 이 Power Pivot 자습서는 이 도구를 사용하여 달성할 수 있는 작업에 대한 가이드 역할을 하기 위한 것이며, 마지막에는 Power Pivot이 종종 가치가 있는 것으로 입증되는 몇 가지 샘플 사용 사례를 살펴봅니다.
Power Pivot은 Excel 2010 및 2013에 추가 기능으로 도입된 Microsoft Excel의 기능이며 이제 Excel 2016 및 365의 기본 기능입니다. Microsoft가 설명하는 것처럼 Power Pivot for Excel을 사용하면 "수백만 행을 가져올 수 있습니다. 여러 데이터 원본의 데이터를 단일 Excel 통합 문서로 변환하고, 이기종 데이터 간의 관계를 만들고, 수식을 사용하여 계산된 열과 측정값을 만들고, 피벗 테이블과 피벗 차트를 작성한 다음, 데이터를 추가로 분석하여 IT 지원 없이 적시에 비즈니스 결정을 내릴 수 있도록 합니다. "
Microsoft가 Power Pivot에서 사용하는 기본 표현 언어는 DAX(Data Analysis Expressions)이지만 특정 상황에서는 다른 언어도 사용할 수 있습니다. 다시 말하지만, Microsoft는 "DAX는 하나 이상의 값을 계산하고 반환하기 위해 수식 또는 표현식에서 사용할 수 있는 함수, 연산자 및 상수의 모음입니다. 간단히 말해서 DAX는 모델에 이미 있는 데이터에서 새로운 정보를 생성하는 데 도움이 됩니다.” 다행스럽게도 이미 Excel에 익숙한 사용자에게는 DAX 수식이 친숙해 보일 것입니다. 많은 수식에 유사한 구문이 있기 때문입니다(예:SUM , AVERAGE , TRUNC ).
명확성을 위해 Power Pivot과 기본 Excel을 사용할 때의 주요 이점은 다음과 같이 요약할 수 있습니다.
다음 섹션에서는 위의 각 항목을 실행하고 Power Pivot for Excel이 어떻게 도움이 될 수 있는지 보여드리겠습니다.
이전에 언급했듯이 Excel의 주요 제한 사항 중 하나는 매우 큰 데이터 세트로 작업하는 것과 관련이 있습니다. 다행스럽게도 Excel은 이제 100만 행 제한을 훨씬 초과하여 Power Pivot에 직접 로드할 수 있습니다.
이를 입증하기 위해 9개의 서로 다른 제품 범주와 4개 지역을 보유한 스포츠 용품 소매업체의 2년치 판매액에 대한 샘플 데이터 세트를 생성했습니다. 결과 데이터 세트는 2백만 개의 행입니다.
데이터 사용 탭에서 새 쿼리를 만들었습니다. CSV 파일에서(새 쿼리 만들기 참조 아래에). 이 기능은 이전에는 PowerQuery라고 했지만 Excel 2016 및 365에서는 Excel의 데이터 탭에 더 긴밀하게 통합되었습니다.
Excel의 빈 통합 문서에서 200만 행 모두를 Power Pivot에 로드하는 데 약 1분이 걸렸습니다! 첫 번째 행을 열 이름으로 승격하여 간단한 데이터 형식 지정을 수행할 수 있었습니다. 지난 몇 년 동안 파워 쿼리 기능은 Excel 추가 기능에서 도구 모음의 데이터 탭에 밀접하게 통합된 부분으로 크게 향상되었습니다. 파워 쿼리는 옵션 제품군과 자체 언어인 M을 통해 데이터를 피벗, 평면화, 정리 및 형성할 수 있습니다.
Power Pivot for Excel의 다른 주요 이점 중 하나는 여러 원본에서 데이터를 쉽게 가져올 수 있다는 것입니다. 이전에는 많은 사람들이 다양한 데이터 원본에 대해 여러 워크시트를 만들었습니다. 종종 이 프로세스에는 VBA 코드를 작성하고 이러한 서로 다른 소스에서 복사/붙여넣기가 포함되었습니다. 하지만 다행스럽게도 Power Pivot을 사용하면 위에서 언급한 문제를 겪지 않고도 다양한 데이터 원본의 데이터를 Excel로 직접 가져올 수 있습니다.
Exhibit 1의 쿼리 기능을 사용하여 다음 소스에서 가져올 수 있습니다.
또한 쿼리 기능이나 파워 피벗 창에서 여러 데이터 원본을 결합하여 데이터를 통합할 수 있습니다. 예를 들어 Excel 통합 문서에서 생산 비용 데이터를 가져오고 Power Pivot으로 쿼리를 통해 SQL 서버에서 실제 판매 결과를 가져올 수 있습니다. 여기에서 생산 배치 번호를 일치시켜 두 데이터 세트를 결합하여 단위당 총 마진을 생성할 수 있습니다.
Power Pivot for Excel의 또 다른 주요 이점은 관련 결론 및 분석을 도출하기 위해 대규모 데이터 세트를 조작하고 작업할 수 있다는 것입니다. 도구의 힘을 이해하기 위해 아래에서 몇 가지 일반적인 예를 살펴보겠습니다.
Excel 중독자는 의심할 여지 없이 피벗 테이블이 가장 유용하고 동시에 우리가 수행하는 가장 실망스러운 작업 중 하나라는 데 동의할 것입니다. 더 큰 데이터 세트로 작업할 때 특히 짜증이 납니다. 다행히 Power Pivot for Excel을 사용하면 더 큰 데이터 집합으로 작업할 때 피벗 테이블을 쉽고 빠르게 만들 수 있습니다.
측정값 생성이라는 제목의 아래 이미지에서 , Power Pivot 창이 두 개의 창으로 분리되는 방식을 확인하세요. 위쪽 창에는 데이터가 있고 아래쪽 창에는 측정값이 있습니다. 측정값은 전체 데이터 세트에서 수행되는 계산입니다. 강조 표시된 셀에 입력하여 측정값을 입력했습니다.
Total Sales:=SUM('Accounting Data'[Amount])
이렇게 하면 금액 열의 합계를 계산하는 새 측정값이 생성됩니다. 마찬가지로 아래 셀에 다른 측정값을 입력할 수 있습니다.
Average Sales:=AVERAGE('Accounting Data'[Amount])
거기에서 대규모 데이터 세트에서 친숙한 피벗 테이블을 만드는 속도를 확인하세요.
Excel을 사용하는 재무 분석가로서 우리는 복잡한 공식을 사용하여 기술을 원하는 대로 구부리는 데 능숙합니다. VLOOKUP 마스터 , SUMIF , 그리고 심지어 두려운 INDEX(MATCH()) . 하지만 Power Pivot을 사용하면 그 대부분을 창 밖으로 던질 수 있습니다.
이 기능을 보여주기 위해 각 범주를 유형에 할당한 작은 참조 테이블을 만들었습니다. "데이터 모델에 추가"를 선택하면 이 테이블이 Power Pivot에 로드됩니다(Power Pivot 모델에 사용자 생성 테이블 추가 참조). 위).
또한 데이터세트와 함께 사용할 날짜 테이블을 만들었습니다(날짜 테이블 만들기 참조). 아래에). Power Pivot for Excel을 사용하면 월, 분기 및 요일별로 통합하기 위해 날짜 테이블을 빠르고 쉽게 만들 수 있습니다. 사용자는 주, 회계 연도 또는 조직별 그룹별로 분석하기 위해 보다 맞춤화된 날짜 테이블을 만들 수도 있습니다.
측정값 외에도 계산된 열이라는 또 다른 유형의 계산이 있습니다. Excel 사용자는 데이터 테이블에 수식을 작성하는 것과 매우 유사하기 때문에 이러한 수식을 작성하는 데 익숙할 것입니다. 아래에 새 계산 열을 만들었습니다(계산 열 만들기 참조). 아래) 금액별로 회계 데이터 테이블을 정렬합니다. $50 미만의 판매는 "소형"으로 표시되고 나머지는 모두 "대형"으로 표시됩니다. 공식이 직관적이지 않나요?
그런 다음 다이어그램 보기를 사용하여 회계 데이터 테이블의 범주 필드와 범주 테이블의 범주 필드 간의 관계를 만들 수 있습니다. 또한 Accounting Data 테이블의 Sales Date 필드와 Calendar 테이블의 Date 필드 간의 관계를 정의할 수 있습니다.
이제 SUMIF 없이 또는 VLOOKUP 함수가 필요한 경우 트랜잭션 크기에 대한 슬라이서를 사용하여 연도 및 유형별로 총 판매액을 계산하는 피벗 테이블을 만들 수 있습니다.
또는 새로운 달력 테이블을 사용하여 요일별 평균 판매 차트를 만들 수 있습니다.
이 차트는 단순해 보이지만 판매 데이터에 새 열을 추가하지 않고 2백만 행 이상의 데이터를 통합하는 데 10초도 채 걸리지 않는다는 점이 인상적입니다.
이러한 모든 통합 보고 시나리오를 수행할 수 있으면서도 항상 개별 항목으로 드릴다운할 수 있습니다. 우리는 매우 세분화된 데이터를 유지합니다.
지금까지 내가 보여준 대부분의 분석은 비교적 간단한 계산이었다. 이제 이 플랫폼의 고급 기능 중 일부를 보여드리고자 합니다.
종종 재무 결과를 조사할 때 전년도의 비슷한 기간과 비교하려고 합니다. Power Pivot에는 몇 가지 기본 제공 시간 인텔리전스 기능이 있습니다.
Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date]))
YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())
예를 들어 Power Pivot의 Accounting Data 테이블에 위의 두 가지 측정값만 추가하면 몇 번의 클릭으로 다음 PivotTable을 생성할 수 있습니다.
재무 분석가로서 내가 종종 해결해야 하는 한 가지 문제는 일치하지 않는 세분성의 문제입니다. 이 예에서는 실제 판매 데이터가 범주 수준으로 표시되지만 계절 수준에만 있는 예산을 준비하겠습니다. 이러한 불일치를 심화시키기 위해 판매 데이터가 일일 데이터임에도 불구하고 분기별 예산을 마련할 것입니다.
Power Pivot for Excel을 사용하면 이러한 불일치가 쉽게 해결됩니다. 두 개의 추가 참조 테이블 또는 데이터베이스 명명법의 차원 테이블을 생성함으로써 이제 적절한 관계를 생성하여 예산 금액에 대한 실제 판매를 분석할 수 있습니다.
Excel에서는 다음 피벗 테이블이 빠르게 결합됩니다.
또한 다음과 같이 실제 판매액과 예산 판매액 간의 차이를 계산하는 새로운 측정값을 정의할 수 있습니다.
Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1
이 측정값을 사용하여 피벗 테이블에 분산을 표시할 수 있습니다.
마지막으로 특정 카테고리의 판매를 전체 판매의 퍼센트(예:전체 판매에 대한 카테고리 기여도)로 조사하고 특정 카테고리의 판매를 같은 유형의 모든 판매에 대한 퍼센트(예:계절 유형에 대한 카테고리 기여도)로 조사해 보겠습니다. 매상). 아래 두 가지 측정값을 만들었습니다.
Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'))
Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))
이제 이러한 측정값을 새 피벗 테이블에 배포할 수 있습니다.
둘 다에서 계산이 어떻게 수행되는지 확인하세요. 범주 및 계절 유형 수준. 저는 이러한 대규모 데이터 세트에서 이러한 계산이 얼마나 빠르고 쉽게 수행되는지 좋아합니다. 이것은 Power Pivot의 우아함과 순수한 계산 능력을 보여주는 몇 가지 예일 뿐입니다.
또 다른 이점은 파일 크기가 줄어든다는 것입니다. 원래 파일 크기는 91MB였는데 지금은 4MB 미만입니다. 원본 파일의 96%를 압축한 것입니다.
어떻게 이런 일이 발생합니까? Power Pivot은 xVelocity 엔진을 사용하여 데이터를 압축합니다. 간단히 말해서 데이터는 행이 아닌 열에 저장됩니다. 이 저장 방법을 사용하면 컴퓨터에서 중복 값을 압축할 수 있습니다. 예제 데이터 세트에는 2백만 개의 모든 행에 걸쳐 반복되는 영역이 4개뿐입니다. Power Pivot for Excel은 이 데이터를 보다 효율적으로 저장할 수 있습니다. 결과적으로 반복되는 값이 많은 데이터의 경우 이 데이터를 저장하는 데 비용이 훨씬 적게 듭니다.
한 가지 주목해야 할 점은 이 샘플 데이터 세트에서 1달러 금액을 사용했다는 것입니다. 센트를 반영하기 위해 두 개의 소수점을 포함했다면 압축 효과는 원본 파일 크기의 여전히 인상적인 80%로 줄어들 것입니다.
Power Pivot 모델은 기업 전체로 확장할 수도 있습니다. 조직에서 많은 사용자를 확보하기 시작하는 Power Pivot 모델을 구축하거나 데이터가 천만 행으로 증가하거나 둘 다라고 가정해 보겠습니다. 이 시점에서 30명의 다른 사용자가 모델을 새로 고치거나 변경하는 것을 원하지 않을 수 있습니다. 모델을 SSAS 테이블 형식으로 원활하게 변환할 수 있습니다. 모든 테이블과 관계는 유지되지만 이제 새로 고침 빈도를 제어하고 다양한 사용자에게 역할(예:읽기 전용, 읽기 및 처리)을 할당하고 테이블 형식 모델에 연결되는 작은 Excel 프런트 엔드만 배포할 수 있습니다. 결과적으로 사용자는 작은 통합 문서로 배포된 테이블 형식 모델에 액세스할 수 있지만 공식 및 측정값에는 액세스할 수 없습니다.
내 고객의 끊임없는 요청 중 하나는 엄격하게 정의된 레이아웃을 준수하는 보고를 만드는 것입니다. 특정 열 너비, RGB 색상 코드, 미리 정의된 글꼴 이름 및 크기를 요청하는 클라이언트가 있습니다. 다음 대시보드를 고려하십시오.
모든 판매가 Power Pivot for Excel을 사용하는 경우 피벗 테이블을 생성하지 않고 판매 수를 채우려면 어떻게 해야 합니까? CUBE 공식 사용하기! 모든 Excel 셀 내에서 CUBE 수식을 작성할 수 있으며 이미 구성한 Power Pivot 모델을 사용하여 계산을 수행합니다.
예를 들어 "2016 Total Sales" 아래의 셀에 다음 수식이 입력됩니다.
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Calendar].[Year].[2016]")
노란색으로 강조 표시된 수식의 첫 번째 부분은 Power Pivot 모델의 이름을 나타냅니다. 일반적으로 최신 버전의 Power Pivot for Excel의 경우 일반적으로 ThisWorkbookDataModel입니다. 녹색 부분은 Total Sales 측정값을 사용하려는 것을 정의합니다. 파란색 부분은 판매 날짜가 2016년인 연도가 있는 행만 필터링하도록 Excel에 지시합니다.
백그라운드에서 Power Pivot은 데이터, 계산된 열 및 측정값을 사용하여 OLAP(온라인 분석 처리) 큐브를 구성했습니다. 이 디자인을 통해 Excel 사용자는 CUBE 기능으로 직접 가져와 데이터에 액세스할 수 있습니다. CUBE 공식을 사용하여 미리 정의된 레이아웃을 준수하는 전체 재무제표를 구성할 수 있었습니다. 이 기능은 재무 분석에 Power Pivot for Excel을 사용하는 주요 기능 중 하나입니다.
Power Pivot for Excel의 또 다른 장점은 빌드한 Power Pivot 통합 문서를 신속하게 Power BI 모델로 변환할 수 있다는 것입니다. Excel 통합 문서를 Power BI Desktop 앱 또는 Power BI Online으로 직접 가져와서 데이터를 분석하고 시각화하고 조직의 모든 사람과 공유할 수 있습니다. 기본적으로 Power BI는 Power Pivot, PowerQuery 및 SharePoint가 모두 하나로 통합된 것입니다. 아래에서는 이전 Power Pivot for Excel 통합 문서를 Power BI 데스크톱 애플리케이션으로 가져와 대시보드를 만들었습니다. 인터페이스가 얼마나 상호작용하는지 주목하십시오:
Power BI의 좋은 점 중 하나는 자연어 Q&A입니다. 시연하기 위해 온라인 Power BI 계정에 Power BI 모델을 업로드했습니다. 웹 사이트에서 질문을 하면 Power BI가 다음을 입력할 때 적절한 분석을 구성합니다.
이러한 유형의 쿼리 기능을 통해 사용자는 데이터 모델에 대해 질문하고 Excel보다 더 쉽게 데이터와 상호 작용할 수 있습니다.
Power BI의 또 다른 이점은 Microsoft의 개발자가 지속적으로 업데이트를 릴리스한다는 것입니다. 많은 사용자가 요청한 새로운 기능이 매월 출시됩니다. 무엇보다도 Excel용 Power Pivot에서 원활하게 전환됩니다. 따라서 DAX 수식을 학습하는 데 투자한 시간을 Power BI에 배포할 수 있습니다! 다양한 장치에서 많은 사용자와 분석을 공유해야 하는 분석가의 경우 Power BI를 살펴볼 가치가 있습니다.
시작하고 나면 따라야 할 몇 가지 모범 사례가 있습니다.
첫 번째는 처음에 무엇을 가져올지 신중하게 결정하는 것입니다. 영업 사원의 집 주소를 사용하시겠습니까? 이 통합 문서의 맥락에서 내 고객의 이메일 주소를 알아야 합니까? 데이터를 대시보드로 집계하는 것이 목표인 경우 사용 가능한 일부 데이터는 해당 계산에 필요하지 않습니다. 들어오는 데이터를 관리하는 데 시간을 할애하면 나중에 데이터 세트가 확장될 때 문제와 메모리 사용량을 크게 줄일 수 있습니다.
또 다른 모범 사례는 Power Pivot이 Excel이 아니라는 점을 기억하는 것입니다. Excel에서는 워크시트를 오른쪽으로 계속 확장하여 계산을 만드는 데 익숙합니다. 명시적 운명에 대한 이러한 욕구를 제한하는 경우 Power Pivot for Excel은 데이터를 가장 효율적으로 처리합니다. 데이터 오른쪽에 계산된 열을 계속 생성하는 대신 하단 창에서 측정값을 작성하는 방법을 배웁니다. 이 습관은 더 작은 파일 크기와 더 빠른 계산을 보장합니다.
마지막으로 측정값에 일반 영어 이름을 사용하는 것이 좋습니다. 이것은 내가 채택하는 데 오랜 시간이 걸렸습니다. 처음 몇 년은 SumExpPctTotal과 같은 이름을 만드는 데 보냈습니다. , 하지만 다른 사람들이 같은 통합 문서를 사용하기 시작하면 설명해야 할 것이 많았습니다. 이제 새 통합 문서를 시작할 때 Expense Line Item as Percent of Total Expenses과 같은 측정값 이름을 사용합니다. . 이름은 길지만 다른 사람이 사용하기 훨씬 쉽습니다.
이 기사에서는 Power Pivot for Excel을 사용하여 평범한 Excel 이상의 중요한 단계를 수행할 수 있는 몇 가지 방법만 제시했습니다. Power Pivot for Excel이 매우 유용하다는 사실을 알게 된 몇 가지 실제 사용 사례를 강조하는 것이 유용할 것이라고 생각했습니다.
다음은 몇 가지입니다.
재무 분석가로서 우리는 끊임없이 확장되는 데이터 세트에 대해 복잡한 계산을 수행해야 합니다. Excel은 이미 기본 분석 도구이기 때문에 Power Pivot 학습 곡선이 쉽고 많은 기능이 Excel의 기본 기능을 반영합니다.
CUBE 기능을 사용하여 Power Pivot for Excel은 기존 Excel 통합 문서와 원활하게 통합됩니다. 계산 효율성 향상을 간과할 수 없습니다. 보수적으로 처리 속도가 20% 더 빠르다고 가정하면 Excel에서 하루 6시간을 보내는 재무 분석가는 1년에 300시간을 절약할 수 있습니다!
또한 이제 기존 Excel을 사용하여 이전에 수행할 수 있었던 것보다 훨씬 더 큰 데이터 세트를 분석할 수 있습니다. 모델을 효율적으로 설계하면 빠른 분석 민첩성을 유지하면서 기존 Excel에서 이전에 허용되었던 10배의 데이터 양을 쉽게 확보할 수 있습니다. 모델을 Power Pivot에서 SSAS 테이블 형식으로 변환하는 기능을 통해 처리할 수 있는 데이터의 양은 Excel에서 얻을 수 있는 것의 100~1,000배입니다.
Power Pivot for Excel은 많은 양의 데이터에 대해 번개처럼 빠른 속도로 계산을 수행하면서도 세부 정보를 계속해서 파악할 수 있어 재무 분석을 투박한 스프레드시트에서 최신 통합 문서로 변환할 수 있습니다.
Power Pivot for Excel을 사용하는 데 관심이 있는 경우 시작하는 데 유용한 몇 가지 자료가 아래에 나와 있습니다.
Collie, R., &Singh, A. (2016). Power Pivot 및 Power BI:Excel 2010-2016의 DAX, Power Query, Power BI 및 Power Pivot에 대한 Excel 사용자 가이드입니다. 미국:신성한 매크로! 책.
<블록 인용>Ferrari, A., &Russo, M. (2015). DAX에 대한 최종 가이드:Microsoft Excel, SQL Server Analysis Services 및 Power BI를 사용한 비즈니스 인텔리전스. 미국:Microsoft Press, USA.