PowerPivot 2010：数据分析师的Excel神器

PowerPivot

需积分: 50 74 浏览量更新于2024-07-18 1 收藏 23.69MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

《PowerPivot for the Data Analyst: Microsoft® Excel 2010》是一本专注于Microsoft Excel 2010中PowerPivot功能的实用指南。作者Bill Jelen在书中深入解析了PowerPivot在数据分析领域的潜力和优势，帮助读者掌握如何高效利用这项工具进行复杂数据处理和可视化。 1. **简介**：本书首先通过引言部分介绍PowerPivot的基本概念，阐述它为何能成为数据分析师的得力助手，以及与传统Excel PivotTable的区别。 2. **安装与下载**：章节2详细指导用户如何下载和安装PowerPivot，确保读者能够顺利开始使用。 3. **PowerPivot的承诺**：作者在第三章探讨PowerPivot的优势，如其强大的数据连接、模型构建和性能优化，以及如何提升数据分析的效率。 4. **数据导入**：第四章介绍了数据导入的方法，包括如何将外部数据源（如数据库、CSV文件等）整合到PowerPivot工作区。 5. **关系管理**：第五章讲解了创建和管理数据模型中的关系，这对于理解数据之间的相互关联至关重要。 6. **Data Sheet视图**：第六章着重于Data Sheet视图的使用，这是一种交互式查看模式，方便快速浏览和编辑大量数据。 7. **Pivot Table构建**：第七章深入到实际操作，教授读者如何创建和调整Pivot Table，以满足各种分析需求。 8. **原生技巧**：第八章分享了PowerPivot中的一些高级Pivot Table技巧，如数据过滤、排序和汇总。 9. **新特性**：第九章专门讨论PowerPivot新增的功能，可能包括更复杂的计算、数据透视表的增强功能等。 10. **DAX的使用**：第十和第十一章聚焦于Data Analysis Expressions (DAX)语言，这是一种强大的建模和表达工具，用于执行聚合函数和日期运算。 11. **命名集、获取PivotData和立方体公式**：第十二章介绍如何使用DAX的高级功能，如创建命名集，以及如何使用它们来获取特定数据和编写适用于多维数据的公式。 12. **最终格式化与报告设计**：第十三章关注如何将PowerPivot结果呈现得专业且美观，避免直接显示Excel表格的局限。 13. **PowerPivot Server升级**：最后一章介绍了如何将PowerPivot扩展到服务器环境，以支持更大的数据集和协作。整个书籍以实用案例和深入理论相结合的方式，全面展示了PowerPivot在Excel 2010中的应用，是数据分析师提升技能和工作效率的理想参考书。版权信息表明，这本书由Pearson Education出版，并强调未经许可不得复制或传播。

资源详情

资源推荐

ptg

I N T H I S

I n t r o d u c t i o n

INTRODUCTION

“ T h e r e ’s R o w 2 0 M i l l i o n ” . ................................2

“ T h e P i v o t T a b l e F i e l d L i s t H a s F i e l d s

from Both Tables” ...........................................2

T h e r e C o u l d B e F i v e T i t l e s f o r T h i s B o o k . ..........4

W h o

Are Those Other People in the Room? ......5

H o w T h i s B o o k I s O r g a n i z e d . ...........................7

C o n v e n t i o n s U s e d i n T h i s B o o k . .......................7

I am a PowerPivot FanBoy.

I remember the exact moment that this happened.

Once a year, Microsoft invites all of their MVPs to

Redmond, Washington, for the MVP Summit. The

event consists of two days of executive keynotes and

two useful days spent with the project managers of

your individual technology. I always go to the MVP

Summit, looking forward to spending time with the

Excel project managers. During those two days, you

go from meeting to meeting on the Redmond cam-

pus, hearing about various plans for the next version

of Excel.

On Wednesday of the 2009 Summit, the noon

meeting was not in the usual building 33 that

houses the Excel team. As I walked into that room

in another building, I noticed something strange.

The left side of the room was filled with people I

knew: the Excel MVPs. They are people whom you

know of if you read Excel books or if you’ve asked

a few questions at the MrExcel.com message board:

Curt Frye, Bob Umlas, Richard Schollar, Smitty,

Greg Truby. The right side of the room was full of

a bunch of people whom I had never seen before.

They were eyeing us suspiciously, and we were

wondering what the heck they were doing in our

meeting.

The speaker that day was Donald Farmer. Scottish.

Hair longer than JWalk’s. He comes walking in, car-

rying a desktop PC under his arm. Read that again.

He came walking in with a desktop PC. Who shows

up to a presentation carrying a desktop PC? Donald

set up the PC in the front of the room, plugged it

into the projector, and proceeded to start a demo

about something called Project Gemini.

From the Library of Wow! eBook

ptg

Introduction

“ T h e r e ’s R o w 2 0 M i l l i o n ”

Donald Farmer had carried in a 64-bit windows machine with 8GB of RAM. By Christmas

of 2009, you could walk into Best Buy and buy such a machine, but I had never seen a

machine with more than 3GB of RAM in early 2009.

Donald sets up a scenario. He had two data sets:

■ Data set 1 was every DVD rental transaction at a fictitious chain of video stores.

■ Data set 2 was box office data that he had downloaded from the Internet.

Donald fires up something that looks basically like Excel and shows us data set 1. He grabs

the vertical scrollbar on the right side of the data and starts to scroll. Like a hot knife

through butter, the PC responded snappily and Donald says, “There’s row 20 million.”

It literally did not sink in with me.

Donald continued...

“There’s row 40 million.”

“There’s row 60 million.”

“There’s row 100 million.”

I am thinking to myself that it is 2009 and seeing row 1 million in Excel was exciting at the

2006 Summit, but really why is this guy wasting our time showing us that Excel now has a

million rows?

Wait. Did. He. Just. Say. 100. Million? I looked at the screen and tried to focus on how

many digits were in the row count at the bottom of the screen.

Donald went on to sort 100 million rows. He used the AutoFilter drop-downs to filter

100 million rows.

Here is a guy who carried in a desktop PC and had a 100 million row data set in something

that looked like Excel. I decided I better close my Outlook and pay attention.

“The PivotTable Field List Has Fields from Both Tables”

Donald proceeds to build a pivot table from the 100 million rows of data. This seems only

natural. What else would you do with 100 million rows of data? Just about any time that

I get a data set, my first inclination is to build a pivot table, so making a pivot table makes

perfect sense.

There is something weird with the pivot table field list, however. It is listing fields from

both table 1 and table 2. Donald is only using data from table 1, though. He adds one of

the new slicer filters that we had just learned about the day before.

From the Library of Wow! eBook

ptg

The PivotTable Field List Has Fields from Both Tables

Then, Donald suggested that we compare rentals by genre with box office receipts by

genre.

For all of the Excel people reading this book, a very easy quiz. To mash up the data from

Sheet1 with Sheet2 what tried and true method will you have to use?

a. = VLOOKUP() functions

b. = INDEX() functions

c. Cut and paste

d. = VABRACADABRA () function, preferably while waving a magic wand

Of course, the answer is a. A nice set of VLOOKUPs will get the data from one sheet onto

another sheet. Although, I cringed, 100 million VLOOKUPs are going to take a long time.

Except Donald didn’t knock out a VLOOKUP function.

All he did was clicked a field from table 2 in the pivot table field list. This was an early ver-

sion of the product and a ToolTip popped up saying “We’ve inferred a relationship between

table 1 and table 2”. The box office data was now summarized in the same pivot table as the

DVD rental information.

A c c o r d i n g t o R o b C o l l i e , “ s t u f f t h a t l o o k s g o o d i n a d e m o m a y n o t a c t u a l l y w o r k w e l l w h e n r e a l p e o p l e

try to use the product.” Microsoft ended up backing off from the automatic relationship detection in

favor of giving people some control over setting up a relationship. It is still very easy to do, easier than

setting up a VLOOKUP.

NOTE

N o w, I L o v e V L O O K U P s

To m e , t h e r e a r e t w o k i n d s o f p e o p l e i n t h e w o r l d .

■ People who can do a VLOOKUP with their eyes closed

■ Everyone else

If you’ve happened to catch one of my live Power Excel seminars, I usually talk about how

I used to be a manager of financial analysis. When we needed to hire financial analysts, the

person in Human Resources would ask about what requirements I wanted on the job post-

ing notice. I usually had a single requirement: “Can do VLOOKUPs in their sleep.” As

long as someone could do VLOOKUPs, everything else would fall into place.

I don’t want to sound harsh, but it comes down to this; if you can do VLOOKUPs, you are

employable. If you can’t do VLOOKUPs, well, you might be employable, just not by me .

When Donald Farmer clicked a box next to field from Sheet 2 and Excel produced a pivot

table from two worksheets without having anyone enter a VLOOKUP function, that was a

game-changer.

From the Library of Wow! eBook

ptg

Introduction

Suddenly, hundreds of millions of people who (a) know how to use a mouse and (b) don’t

know how to do a VLOOKUP are suddenly able to perform jaw-dropping business intel-

ligence analyses. They all might actually be employable, by me.

“How Much Will This Cost? Well, the Client Is Free”

It was crazy talk.

Microsoft was demonstrating the greatest invention in a decade and they were giving it

away for free.

It wasn’t unprecedented. Lotus used to sell Lotus Improv for $199, and Microsoft borrowed

all of that technology and gave it away on the Excel data menu back in 1993. Of course, it

would have been a blatant rip-off if they would have put “Improv” on Data menu in Excel

5, so they changed the name to something called a “pivot table.”

Pivot tables were the greatest computing invention since VisiCalc, and Microsoft bundled

that for free with their Excel product.

I was watching the greatest computing invention since pivot tables, and now Microsoft was

planning on bundling it for free with Excel 2010.

This Book Was Pitched Eight Hours Later

Based on the two facts: (a) 100 million rows in Excel and (b) a pivot table from multiple

worksheets without a VLOOKUP, I knew that I wanted to write a book about this new

product. Loretta Yates from Que was in town for the MVP Summit, and I had tentative

approval for this new book less than eight hours after I saw the product.

There Could Be Five Titles for This Book

Any one of these mega-benefits would make PowerPivot worthy of a book:

■ 100 Million Rows: Miracle Product Allows Unlimited Rows in Microsoft Excel!

■ VLOOKUPs Shrugged: Why You Don’t Need VLOOKUPs Any More

■ ATOM & CSV: The Marriage of Disparate Data

■ L.Y. Actuals & C.Y. Budget: How Named Sets Enabled the Obvious But Previously Impossible

Pivot Table Report

■ Calculated Fields: Designed by an Intern? How DAX is Empowering Real Calculations in

Pivot Tables

This sixth title might not appeal to the Excel audience, but would make a great whitepaper:

■ Store It Columnwise, Stupid! How to achieve world-class data compression

From the Library of Wow! eBook

ptg

There Could Be Five Titles for This Book

At that first meeting with Donald Farmer, only the first two points really sunk in with me.

(1) They had busted through the one million row limit in Excel. (2) You could join Sheet1

and Sheet2 without VLOOKUP.

There are other benefits to PowerPivot that I did not begin to understand until I was work-

ing on these books, including the following:

■ PowerPivot can consume data from many places and present them all in the same

workbook. You can have low-tech data like flat text files. You can have data coming

from SQL Server. You can have data from Excel. Data from Access. RSS feeds.

■ All pivot tables are lousy at handling asymmetric reports. If you try to show last year’s

sales and this year’s budget in the same pivot report, you are going to have to endure

having last year’s budget and this year’s actuals in the report, too. This means you will

manually be hiding some columns and replacing the built-in total column with your

own column. The Excel team fixed this in Excel 2010, but only for people reporting

from OLAP data sets. Here is the funny thing about PowerPivot: It makes regular

Excel data into OLAP data sets. Saying that you want to use PowerPivot just to enable

asymmetric reporting is like saying that you want to use a $100 million laser to heat up

your coffee. It is a huge underutilization of a $100 million laser. But...the fact is that

the laser will heat up coffee, and you’ve frankly been living without a microwave since

1993 when the first pivot table hit Excel. So, if you have a need to report last year’s

actuals next to this year’s budget, well, PowerPivot is going to enable this for you.

■ Calculated fields in pivot tables were always sort of quirky. They would work for simple

things. When you see how the new DAX formula language enables you to do really

powerful things with calculated measures in PowerPivot, you will realize that calculated

fields can’t hold a candle to these new calculations.

Any one of those benefits should be enough to get the Excel data analyst to want to

upgrade to Excel 2010. Put all of the benefits into a single product, and price that product

at the price of $Free and there will be a lot of Excel people who should be drooling.

W h o Are Those Other People in the Room?

There are at least three audiences who are watching PowerPivot with great interest:

■ There is us. You know us: Yo u . M e . E x c e l p e op l e . We d o e v er y t hi n g i n E x c e l. We

think that Microsoft Word is a useless add-in for people who can’t manage to use Edit,

Fill, Justify. We are the front lines of the decision-making process.

■ There are SQL Server people: They are the people who are sitting on top of 100 mil-

lion rows of transactional data. They are the people who get us the nice and tidy one

million row summaries of the transactional data. (Between you and me, if they embrace

PowerPivot and just let us get to that 100 million rows of transactional data, we can

actually run a few different iterations before we figure out what data we actually need.)

From the Library of Wow! eBook

剩余325页未读，继续阅读

xhx88

粉丝: 1
资源: 8

PowerPivot 2010：数据分析师的Excel神器

powerpiovt 笔记

Power BI 官方中文教程完整版.pdf

power pivot和power BI

powerpivot 一段时间

power pivot

power Pivot

powerpivot时间维度表

power pivot实现多对一

mysql如何像power pivot做复杂的数据查询和分析

powerpivot多对多

power pivot dax函数

excelpowerpivot

老师 我们学到的power pivot中的关系图视图的作用是什么呀 在以后的工作中什么情况下需要设计这个图啊，他一般应用在什么地方啊

excel数据分析教学

excel pivot

excel数据分析插件

power bi教程

孙兴华 powerbi课件

pivot函数 计数

excel sql查询

最新资源

老师我们学到的power pivot中的关系图视图的作用是什么呀在以后的工作中什么情况下需要设计这个图啊，他一般应用在什么地方啊

pivot函数计数