书名：Excel进阶指南：Power Pivot与Power Query实战
ISBN：978-7-115-63041-4
本书由人民邮电出版社发行数字版。版权所有，侵权必究。
您购买的人民邮电出版社电子书仅供您个人使用，未经授权，不得以任何方式复制和传播本书内容。
我们愿意相信读者具有这样的良知和觉悟，与我们共同保护知识产权。
如果购买者有侵权行为，我们可能对该用户实施包括但不限于关闭该帐号等维权措施，并可能追究法律责任。
著 袁佳林
责任编辑 卜一凡
人民邮电出版社出版发行 北京市丰台区成寿寺路11号
邮编 100164 电子邮件 315@ptpress.com.cn
网址 http://www.ptpress.com.cn
读者服务热线：(010)81055410
反盗版热线：(010)81055315
本书从Excel的局限性讲起，然后从零开始详细介绍智能化Excel的两大组件：Power Pivot、Power Query。本书按照由易到难、由浅入深、循序渐进的教学方式，介绍Excel BI的Power系列组件的核心计算原理及底层逻辑，以实战案例为引导，清晰地讲解使用Excel BI进行数据分析的方法，为读者综合使用Power Pivot、Power Query实现自动化报表打下坚实的基础。
本书结构清晰、通俗易懂，讲解层层递进，适合Power Pivot、Power Query入门及进阶读者，如计算机相关专业在校大学生、数据分析相关岗位的从业者、亟待提升数据分析能力的人员阅读。
Power Pivot及Power Query是 Excel 中创建自动化报表的“利器”。这本书由浅入深地介绍了它们的用法，同时总结了精彩的学习方法，能帮助读者更好地掌握它们的底层逻辑。相信这本书能在AI盛行的时代帮助读者跟上商务智能发展的步伐。
——周庆麟，Excel Home创始人，微软最有价值专家
这本书介绍了Power Pivot 和 Power Query，它们能够在一定程度上突破传统Excel 的局限。通过学习这本书，读者能了解商务智能、数据库、数据模型、事实表与维表等重要概念，可为学习 Power BI 等工具打下坚实的基础。我把这本书推荐给希望提升数据分析能力的朋友。
——减法君，微软最有价值专家，Power BI 视频博主
在“数据时代”，常规的Excel工作表用以处理更大量级和更细粒度的数据时，会有短板。幸运的是，Excel增加了Power Pivot和Power Query这两大进阶组件，分别使用DAX语言和M语言，让我们在应对复杂的数据清洗、建模和分析工作时更加得心应手。相信这本书可以成为你技能进阶的好帮手。
——刘必麟，《Excel商务智能：Power Query和Power Pivot数据清洗、建模与分析实战》作者
本书作者的另一本书《Power BI 数据可视化从入门到实战》在业内掀起了不小的波澜，让人们发现Power BI的可视化结果竟然可以这么美。正当人们沉醉于欣赏与模仿之际，作者又潜心研究Excel智能化的“内在美”，Power Pivot和Power Query也是Excel 智能化的核心。两本书搭配使用，内外兼修，你将掌握真正的商务智能本领，全面领略Excel和 Power BI世界的强大！
——陈泽满，“PowerBI生命管理大师学谦”公众号主理人
在这个数据“爆炸”的时代，如何对大量数据进行分析，从这些数据中获取真知灼见，是每个人都迫切需要解决的问题。
虽然近些年各种数据分析工具层出不穷，但不可否认的是，Excel仍然是目前世界上使用很广泛的工具，因为它简单易用、功能强大。不过，多数人对Excel的了解还停留在表面，对它的强大功能还没有充分掌握。
比如我经常听到有人抱怨说，Excel有数据量处理限制，处理不了超过百万行的数据，其实Excel还有两个功能强大的组件：Power Pivot和Power Query。用户通过它们可以更好地解决各种复杂的数据处理问题，并且提高自己在工作中的效率和竞争力。
如果你还没有听说过Power Pivot和Power Query，没有关系，这本书详细介绍了这两个组件的功能和应用，能帮助读者更好地理解现代数据分析技术，并掌握使用Power Pivot和Power Query进行数据处理和分析的方法。如果你的日常工作中经常用到Excel，或者你对商务智能和数据分析感兴趣，那么这本书绝对值得一读。
本书不仅提供了丰富的案例和技巧，还通过图片、表格等形式直观地展示多种数据处理过程。同时，作者还根据自己的理解梳理了智能化Excel的多个工具，并对它们进行通俗易懂的讲解，使得读者可以更加深入地理解并掌握这些工具。
我相信，你阅读这本书后会对Excel有更加全面的认识，并开启个人的“数智之旅”。我衷心祝愿每一位读者都能从这本书中获得真知，“功力大增”。
采悟
“PowerBI星球”创始人，
微软最有价值专家
大家好，我是郑志刚，非常荣幸能为袁佳林老师的新书《Excel进阶指南：Power Pivot与Power Query实战》作序。
作为一个有着10余年从业经历的商业数据分析师，我日常工作中最为重要的办公软件之一就是Excel，它使用灵活、函数多样，可以生成各种分析报表和可视化图表，为数据分析师提供了很大的便利和支持。
然而随着各类企业的数据量逐年增长、商业竞争的加剧，企业对分析结果的时效性及精细度的要求逐年提高，常规Excel分析方法的短板也逐渐显现，如下所示。
（1）制作报表花费时间过长：数据收集、处理和数据结果的核对都会耗费大量时间，导致观察和深入挖掘分析报表结果的时间被压缩。
（2）报表复用性较差：大部分企业数据分析专员每天重复制作相同或相似的报表，效率低下且自身能力提升有限。
（3）数据量级有限：Excel无法处理超过一百万行的数据，导致很多时间跨度较长或者粒度较细的分析难以完成。
（4）分析的指标相对简单且数量有限： Excel自带的聚合函数只能用于求和、平均值、最大值、最小值和计数等，很多相对复杂的指标不得不在数据透视表外进行二次计算。
正是由于常规的Excel分析方法有着诸多的短板，如何补齐这些短板，快速提升Excel的工作效率就成了急需解决的问题。而袁老师的图书则在此时应运而生，这本书详细介绍Power Pivot和Power Query这两款Excel自带的组件的基本使用方法，包括数据的提取、清洗和转换，以及如何使用DAX函数进行数据建模。同时，对这些组件的高级应用也进行详细的阐述，例如如何使用Power Query中的M语言自定义函数。
除此之外，这本书还通过大量的案例和实操指导，帮助读者更好地理解所介绍的知识点，并能够将其灵活地运用到日常的数据处理和建模工作中。例如，如何使用Power Pivot进行数据透视表的创建和使用、如何使用DAX函数完成多维数据分析等。这本书内容非常全面，几乎包含Excel进阶工具相关的所有内容，并且注重实战应用，是非常实用的智能化Excel学习工具。
综上所述，这本书是关于智能化Excel的实用指南。如果您想在数据分析领域深耕，并经常使用Excel，我强烈建议您进入智能化Excel领域，这本书将帮助您在这个领域书写出美丽篇章。
最后，感谢袁老师将自己多年的经验积累创作成书，感谢袁老师在智能化数据分析领域的无私奉献。祝各位读者阅读愉快！
郑志刚
《Power BI零售数据分析实战》作者，
大型零售集团数据分析师
当我第一次使用Power Query，通过一连串的单击实现了日常报表自动化以后，就对Excel中的这个报表自动化“神器”深深着迷了。后来随着学习的深入，我又接触到了另一个Excel中的数据分析“神器”：Power Pivot。它们的出现刷新了我对Excel的认知，它们通过紧密的配合让基于Excel的数据分析流程更加自动化和智能化。我坚信它们是值得投入较多时间和精力去了解和掌握的。
在学习Power Query和Power Pivot的过程中，我将自己的一些学习心得及收获分享在微信公众号“ExcelBI星球”中，点滴地记录，持续地分享，慢慢地形成了自己的学习方法。我还搭建了Power Query及Power Pivot知识框架，在此基础上我搭建了本书的框架，也就是Excel BI的知识框架，它帮助我实现了很多烦琐的数据分析工作的自动化。相信读者在阅读完本书以后，也能在实际工作中实现自动化办公，实实在在地提高工作效率。
全书一共7章。第1章从Excel讲起，梳理传统Excel的局限性，引出Power Pivot和Power Query。第2章开始进行Power Pivot与数据建模相关知识的讲解。第3章循序渐进地讲解DAX的核心知识点，比如度量值、上下文、关系、DAX函数等，为后续的实战打下坚实的基础。第4章、第5章、第6章从Power Query简介开始到M语言，由浅入深地讲解Power Query的理论知识与实战案例。第7章向读者分享高效学习和使用Excel BI的小技巧。
需要说明如下内容。
（1）在读者学习并练习初级内容后，本书后半部分内容并未详细指示每个路径，因为通过对路径的寻找，读者也能增加对内容的掌握程度。
（2）本书所指“官网”，如无特别说明，均为Microsoft 365官网。
（3）本书所有章节涉及数据对应的示例文件，已经放置于异步社区的配套资源中，读者可在阅读时配合使用。针对部分功能演示，读者也可以使用自己的数据进行操作。
（4）本书中的描述仅提供命名举例，并不代表实际命名或者必须使用的命名选项。如“销售量”“销售数量”“销售总量”来自不同的示例文件，本书尽量以不会给读者造成阅读障碍为准则进行描述。
（5）本书中的示例文件所在路径以读者下载、保存文件的位置为准。下载示例文件以后，需要修改数据源设置才能正常进行查询。具体修改方法如下图所示。
本书结构清晰、通俗易懂，讲解层层递进；理论与实战结合，适合Power Pivot和Power Query入门及进阶读者，如计算机相关专业在校大学生、数据分析相关岗位从业者、亟待提升数据分析能力的人员阅读。
本书基于Microsoft 365编写，随着Excel版本的更新，Power Pivot和Power Query的界面及功能也进行了更新。读者使用Microsoft 365进行练习操作将获得更好的学习体验。如果读者使用Excel 2016及以上版本进行实操练习，操作界面大同小异，并不影响阅读本书。
因本人知识和能力所限，书中纰漏之处在所难免，恳请读者朋友们不吝批评指正。如果您有关于本书的疑问，可以添加微信powerbi007，或者关注微信公众号“ExcelBI星球”进行反馈。我将真诚期待您对本书的宝贵意见及建议。
您还可以通过以下方式联系我。
新浪微博：JaryYuan。
知乎：JaryYuan。
微信号：powerbi007。
在本书的写作过程中，非常感谢家人的理解与支持。特别感谢我的妻子，承担起照顾家庭和培养孩子的重担，让我心无旁骛地完成了本书的写作；同样特别感谢我的儿子，虽然写书占用了很多陪伴他的时间，但我得到了他的理解。
感谢本书的各位编辑老师的耐心指导与认真审稿。感谢为本书撰写推荐词与序的各位老师和朋友，感谢他们对本书的认可和支持。感谢我的微信公众号关注者，他们的关注和留言对我写成本书提供了很大的帮助。
袁佳林
2023年12月20日
本书由异步社区出品，社区（https://www.epubit.com）可为您提供相关资源和后续服务。
本书提供如下资源：
● 示例文件；
● 思维导图。
您可以扫描下方的二维码，根据指引获取配套资源。
您也可以在异步社区的本书页面中单击
，跳转到下载页面，按提示进行操作获取配套资源。注意：为保证购书读者的权益，该操作会给出相关提示，要求输入提取码进行验证。
作者和编辑尽最大努力来确保书中内容的准确性，但难免会存在疏漏。欢迎您将发现的问题反馈给我们，帮助我们提升图书的质量。
当您发现错误时，请登录异步社区，按书名搜索，进入本书页面，单击“发表勘误”，输入错误信息后，单击“提交勘误”即可。本书的作者和编辑会对您提交的错误信息进行审核，确认并接受后，您将获得异步社区的100积分。积分可用于在异步社区兑换优惠券、样书或奖品。
我们的联系邮箱是contact@epubit.com.cn。
如果您对本书有任何疑问或建议，请您发电子邮件给我们，并请在电子邮件标题中注明书名，以便我们更高效地做出反馈。
如果您有兴趣出版图书、录制教学视频，或者参与图书翻译、技术审校等工作，可以发电子邮件给我们；有意出版图书的作者也可以到异步社区在线投稿（直接访问www.epubit.com/contribute即可）。
如果您所在的学校、培训机构或企业，想批量购买本书或异步社区出版的其他图书，也可以发电子邮件给我们。
如果您在网上发现有针对异步社区出品图书的各种形式的盗版行为，包括对图书全部或部分内容的非授权传播，请您将怀疑有侵权行为的链接发电子邮件给我们。您的这一举动是对作者权益的保护，也是我们持续为您提供有价值的内容的动力之源。
异步社区是人民邮电出版社旗下IT专业图书社区，致力于出版精品IT图书和相关学习产品，为作译者提供优质出版服务。异步社区创办于2015年8月，提供大量精品IT图书和电子书，以及高品质技术文章和视频课程。更多详情请访问异步社区官网。
异步图书是由异步社区编辑团队策划出版的精品IT专业图书的品牌，依托于人民邮电出版社近40年的计算机图书出版积累和专业编辑团队，相关图书在封面上印有异步图书的Logo。异步图书的出版领域包括软件开发、大数据、人工智能、测试、前端、网络技术等。
Excel在职场中的应用兼具深度及广度，它在效率提升、数据分析及可视化方面的价值得以充分体现。众多Excel使用者及爱好者对Excel的功能进行深挖，研究出来很多非常实用的用法，如专业的商务图表制作、基于函数及VBA（Visual Basic for Applications，Visual Basic的一种宏语言）建立报表自动化模型、可视化仪表板设计等。
但近年来商务智能（Business Intelligence，BI）崛起，传统Excel的功能难以满足职场人士的数据分析需求。我们不仅需要知道Excel在数据处理中有哪些局限，还需要“刷新”对Excel的认知，进一步学习和掌握Excel进阶功能。
1.1 Excel在数据处理方面的局限性随着业务分析场景越来越丰富，人们对Excel数据处理能力的要求也越来越高。产品经理、会计师、人力资源管理人员、数据分析人员等的数据分析工作或多或少地需要通过Excel来处理。不断增加的数据处理需求正在不断地挑战Excel，传统Excel的局限性渐渐地显露出来。
能熟练使用Excel的用户都知道自Excel 2007以后，微软公司将Excel的最大行数限制从之前的65,536行提升到了1,048,576行，在可容纳的行数方面已经有非常大的进步了，但是仍然没有跟上现在大数据的发展速度。虽然新版Excel可以容纳的数据量增加了，但是当数据量较多时，Excel的运行效率会急速降低。因为除了需要存储数据以外，Excel还需要完成数据处理操作，比如使用函数、数据透视表、宏和VBA等进行数据引用和计算，这些操作会增加Excel的运行负担。所以在数据量没有达到数据存储上限时，Excel可能就会出现打开缓慢、卡顿、无响应等现象。
大部分Excel工作簿里面的公式对单元格的引用错综复杂，此外可能还会用到数组公式、VBA、结构查询语言（Structure Query Language，SQL）等。这就导致Excel处理数据具有不透明性，我们需要花费大量的时间和精力去维护Excel工作簿。特别是当创建这类Excel工作簿的同事离职以后，接手的人很难厘清其中的逻辑，也很难为适应业务新需求而做出修改。虽然Excel的“公式”选项卡中提供了一整套的公式审核功能，比如追踪引用单元格、追踪从属单元格及显示公式等，如图1-1所示，它们可以在一定程度上帮助我们梳理工作表中单元格的引用关系及公式的计算逻辑，但是并不能从根本上解决传统Excel数据分析模型的“黑箱”问题。
图1-1 Excel中的公式审核功能
基于传统Excel功能建立数据模型，往往需要在同一个工作簿中建立多个工作表，工作表不断地增加，直接导致工作表之间的导航变得麻烦。Excel在大部分数据分析情况下都是无法直接复用已有的数据表（Data Table）的。这就是Excel数据处理紧凑性不足的原因。数据处理紧凑性不足通常表现在两个方面：数据存储冗余及数据处理冗余。
数据存储冗余。Excel在处理多表数据时需要宽表化，也就是使用VLOOKUP()、MATCH()、INDEX()等引用函数将匹配信息合并到同一个表中。这个过程造成许多的数据存储冗余。智能化Excel引入Power Pivot数据模型以后，通过关系可以直接实现跨表透视，有效减少整体数据量。通过将重复的描述性信息转化为维表的方式可以有效降低数据存储冗余度。
数据处理冗余。Excel进行数据处理时都需要实实在在的可见表，无法基于内存中的表进行计算。比如基于数据透视表计算环比时，通常需要分别计算出当期及上期数据，然后将两期数据匹配后相比。在Excel中当分析维度增加或者需要自定义数值的汇总方式时，不可避免地需要使用中间表。在使用中间表时就会产生数据处理冗余。虽然数据透视表的功能非常强大，但它是高度定制化的分析工具，只能用于实现字段求和、计数、求平均值等简单的统计汇总，无法实现不重复计数、自定义汇总方式，如图1-2所示。
图1-2 Excel数据透视表支持的值字段汇总方式
1.2 BI与智能化Excel美国计算机科学家H. P. Luhn在IMB Journal上发表了文章“A Business Intelligence System”，该文章提出了搭建BI系统的流程，描述了业务数据的合理加工和展示给商业带来的价值。一直以来，BI都属于专业程度高、需要通过代码搭建系统的IT领域，直到自助式BI（Self-Service BI），尤其是智能化Excel的兴起和发展，才帮助我们将不同来源的数据进行整合，实现了多维度的分析，减少了业务部门对IT部门的依赖。
BI指的是一套完整的解决方案，用于将企业中现有的数据进行有效的整合，从而快速、准确地提供报表及可视化分析，为企业经营提供有效的决策依据。以前BI的实施需要涉及诸多技术，比如数据仓库技术、数据挖掘技术等，非IT人员无法自助使用BI。
随着大数据、云技术和移动互联网的发展，BI进一步发展为自助式BI。自助式BI的特点是高度易用、贴合大众、移动端应用广泛，并且搭建过程中不过度依赖IT的支持。目前自助式BI的代表性工具有Power BI、Tableau及Excel等。我们可以把Excel中用于BI分析的工具统称为“Excel BI”。
微软最有价值专家Ken Puls在M is for (DATA) MONKEY : The Excel Pro's Definitive Guide to Power Query一书中，用“A New Revolution”来形容Power Query的数据抽取、转换和装载（Extract Transformation Load，ETL）的功能。另一位微软最有价值专家，MrExcel.com的创始人Bill Jelen曾经这样描述Power Pivot及度量值（Measure）：“The best thing to happen to Excel in 20 years”。由此可见，Excel已经悄无声息地发生了重大变革。笔者希望通过本书的详解及案例向读者展示Excel中发生的变革，帮助读者重构对Excel的认知。
笔者根据自己的理解梳理了智能化Excel知识图谱，如图1-3所示。
图1-3 智能化Excel知识图谱
本书将对智能化Excel知识图谱中的部分内容进行详细的讲解。通过学习智能化Excel中的Power Pivot及Power Query功能，读者可以开启“数智之旅”，掌握使用Excel实现数据分析自动化及可视化的技巧。
1.3 数据库概念与数据模型要掌握智能化Excel中的数据清洗及建模功能就必须换一种思维来理解数据。了解数据库的核心概念对于学习Excel的数据建模功能大有帮助。因为Excel的智能化过程中会引用许多数据库的相关知识。智能化Excel中的数据模型和传统数据库中的数据模型的基本理念其实是相通的。
数据库是一个计算机领域的术语，指的是按照数据结构来组织、存储和管理数据的仓库。Excel存储的数据一般是非结构化的数据，也就是单元格与列没有严格的数据类型。可以在同一列的单元格中存储数值、文本或者公式等。而数据库中同一列的数据类型严格一致，必须同时为数值类型或者文本类型等。数据表是数据库的基础单元，一般情况下数据库由多个互相关联的数据表组成。数据库中的表是规范化的，具有较小的冗余度。Excel中的数据表约束较少，灵活性较强。
在数据库中，事实表（Fact Table）主要包含用于计算、汇总的数值字段以及用于关联分析维度的索引字段，如销售明细、交易记录等。维表（Dimension Table）包含数据分析维度，是用户分析数据的窗口，如客户信息表、产品表及日期表等。事实表存储业务发生时可度量的数值指标，维表存储详细描述性信息，理解事实表与维表的概念对于我们理解Power Pivot的数据模型有很大帮助，也有助于我们搭建一个合理、高效的数据模型。
表是由行和列组成的，记录是表的一行，字段是表的一列。Power Pivot使用的是列存储式表，相比于行存储式表，列存储式表在执行计算时能有效地减少数据读取时间。比如，我们需要计算销售额列的总和，一种直观的方式是直接取出销售额列，并对该列进行求和。这就是Power Pivot对列进行求和的方式，也是比较符合人类数据处理思维的方式。而在Excel中，需要通过行号（1、2、3等）和列标（A、B、C等）定位每一个值，然后逐行扫描取数，最后进行求和，这样数据处理效率自然会降低。这也是 Excel中数据超过一定行数以后计算效率会大大降低的原因之一。
查询是数据库中的术语，它是指通过SQL从数据库的表中提取满足特定条件的数据子集，它不会破坏数据库中的源表，查询结果与源表保持连接。无论是在Power Pivot中还是在Power Query中，导入的数据都是以查询的形式存在的。导入Power Pivot或者Power Query中的数据只是原始数据的映射，也就是说导入的数据以查询的形式与源表保持连接。Power Pivot或者Power Query中的添加列、删除列等操作不会改变数据源表；如果在数据源表中添加列或者删除列，刷新后Power Pivot或者Power Query中的数据会同步更新。智能化Excel是摆脱复制粘贴数据的“神器”。
关系是两个独立的表格互相关联的一种机制。在日常的数据处理中，如果两个表可以通过索引列进行合并，则它们存在关系。当多个表之间存在关系时，这几个表就构成了数据模型。在简单的场景中，可以将关系看作VLOOKUP()函数。如果关联两个表的字段（匹配字段）在其中一个表中是唯一不重复的，而在另一个表中存在重复值，则这两个表存在一对多关系。
多个表通过关系关联起来构成的数据模型如图1-4所示。当然数据模型并不一定要包含多个表。单个表导入Power Pivot后也可构成一个数据模型，只是这种情况下的数据分析需求简单，将表导入Power Pivot中的意义不大。多表数据模型能够帮助我们实现更多维度的分析、跨表透视等，并且可突破数据量的限制，具备更快的计算速度。
图1-4 由多个互相关联的表构成的数据模型
