前言

在工作和生活中，数字无处不在。数字技术已经成为推动人类社会进步和发展的重要力量。学习和掌握数字技术，是正确抉择、追求卓越的开始。为便于学习、掌握和运用数字技术，人们开发了计算机电子表格平台。如果将这种平台与过去的算盘、计算器相比较，不难发现新平台不再是简单的计算，而是集成了人类历史上的各种数学技术和成就，可以完成数据转换、建模、统计分析、规划求解等各种科学运算任务的工具。

1. Excel的功能与用途

Excel是一种功能强大、操作简便的电子表格软件。它以使用列和行定位单元格和工作表格的形式组织数据，可以通过公式和函数、加载项、嵌入的外部工具或应用程序对数据进行分析和计算。其主要功能和用途可概括如下。

(1) 记录、管理业务与生活数据。可以用工作簿记录、存储和管理日常业务和生活数据，如业绩、薪金、血压、体重等，以便查阅、分析和比较不同时段的数据，为改进业务、改善生活、强健体魄提供必要依据。

(2) 获取和转换外部数据。在工作簿中，可以从数据文件、数据库、在线平台等各种资源中获取需要的数据，并将其转换为便于阅读和分析的报表。

(3) 数据运算。可以在行、列定位的工作表中输入公式和函数运算数据，如求和、均值、最值、方差、行列式、矩阵乘积等。

(4) 数据分析。可使用数据透视表(PivotTable)、PowerPivot、数据分析、模拟分析、预测工作表等工具和统计函数，对数据的相关性、分布、规律或趋势进行分析，为决策提供量化依据。

(5) 会计和财务管理。可以调用或制作会计记账模板，建立和使用科目表、凭证表、日记账、分类账、余额表、折旧计提、资产负债表、利润表等会计报表，并进行财务状况分析。

(6) 数据可视化。在工作簿中可以使用“图表”工具，用柱形图、饼图、折线图、散点图、瀑布图、曲面图、地图等显示数据的统计特性或地理特性。

(7) 行政和人力资源管理。借鉴或修改现有管理类模板，按需要建立行政和人力资源计划、招聘与调配、培训、关键绩效指标(KPI)、薪酬福利、劳动合同等管理系统。

(8) 规划建模与求解。运用工作表中的计算功能、公式、函数或规划求解工具，解决线性规划、非线性规划、整数规划、目标规划、动态规划及运输问题的建模与求解问题。

(9) 决策分析。运用公式及函数、图形等功能，形象地分解、分析复杂的决策问题，并求解最优方案，如求解图与网络、网络计划、排队论、存储论、对策论和决策分析等问题。

(10) 商业智能分析与多媒体报表。可以连接Power BI(Power Business Intelligence)，将工作簿数据转换为关系数据模型，通过交互式和人工智能(AI)过程形成可视化见解和多媒体报表，并可使用移动设备与同事共享和分析数据，发表见解。

(11) 按特定需要开发和扩展应用。可使用VBA、宏和XML等工具，按照特定需要录制或编写业务流程、分析和计算程序，进一步提高效率。

〖1〗〖3〗Excel+Power BI数据分析与应用实践前言〖3〗2. Excel的版本演变

1982年，微软(Microsoft)公司推出了首款电子表格软件，其名称为Multiplan。当时，Lotus Development公司也发布了著名的Lotus 123电子表格应用程序。20世纪80年代中期，Lotus 123主导了电子表格软件市场。1987年，微软推出了适用于Windows操作系统的Excel 2.0，到1988年，Excel 2.0的销量开始超过Lotus 123。

1990年，微软发布了Excel 3.0，在工作表界面增加了工具栏、3D图表、绘图和提纲等功能。两年后升级为Excel 4.0，增加了自动填充等功能。1993年，微软又推出了Excel 5.0，并开发了VBA(Visual Basic for Applications)语言，也就是Excel宏语言。VBA为优化、拓展Excel的自动化和可操作性提供了无限可能。例如，可使用VBA代码处理数字、创建流程自动化、为企业呈现数据。

1995年以后，微软开始采用年代作为Excel版本编号，先后发布了Excel 95、Excel 97、Excel 2000、Excel 2002(Office XP)、Excel 2007、Excel 2010、Excel 2013、Excel 2016、Excel 2019、Excel 2020。每次升级或更新，都会改进、增加或拓展一些功能或工具。例如，Excel 2007优化了压缩文件格式、扩大了文件容量、提升了文件的兼容性，并开始使用xlsx和xlsm为文件的后缀名；Excel 2013增加了文件“自动恢复”等功能和Power Query、Power View、Power Pivot等工具；Excel 2016新增了搜索框、墨迹重播、联机图片等功能；Excel 2019版新增IFS等函数和漏斗图、插入图标等功能。

随着不断改进、升级和拓展，Excel已成为使用最广泛的电子表格应用软件。2021年10月，微软推出了Excel 2021，进一步拓展了网络功能，可以在线与他人协作，分享数据、交流见解、共同创作。Excel 2021是随Office 365一并发布的，Office套件中还包括Word、Outlook、PowerPoint、OneNote、Publisher。微软同时还推出了包含Office 2021的Microsoft 365家庭版和商业版。获取Excel 2021的方式有两种: 一次性购买Office 2021；或按月或年付费订阅Microsoft 365个人版、家庭版或商业版。

3. 从Excel到Power BI

在Excel工作表(Sheet)中，每个单元格的数据都由行、列定位或引用，使用Excel公式可以计算单元格或区域的数据。为扩展关系数据计算、分析功能，微软在Excel 2.0就引入了CrossTab插件，到Excel 5.0更新为PivotTable，也就是数据透视表。数据透视表是一种行、列矩阵表，它明确了数据之间的关系，在计算、分析数据时，可以直接引用行、列或表数据，提高交互式分析数据的性能。从Excel 2010开始，关系数据处理、分析功能进一步扩展，陆续增加了PowerPivot、Power Query、Power View和Power Map等插件。

2015年，微软集成Excel中的PowerPivot、Power Query、Power View和Power Map等插件的功能，发布了商业智能软件Power BI(Business Intelligence)，并且每月更新，截至本书成书时，其版本为Power BI 2.119(July 2023)。Power BI是桌面应用(Desktop)、软件服务(SoftwareasaService)和移动应用(Mobile Apps)的集合，它们一起协同工作。桌面应用集成查询引擎、数据建模和可视化等技术，为用户提供数据连接及查询、建模和可视化报表等功能，并可轻松与他人共享。软件服务是一种在线应用模块，使用该模块，用户可打开发布的报表，与组织成员动态协同地分析报表、交流见解。移动应用是可安装在手机或其他移动设备上的App程序，用户可通过该程序连接到云和本地数据，查看信息和互动，也可登录Power BI服务(https://powerbi.com)，在“软件服务”中查看仪表板和报表，并可发表见解。

4. 下载、安装合适的Excel版本

在大多数情况下，用户所用的台式计算机、平板电脑或笔记本电脑都可满足安装Excel的硬件要求。用户可以根据自己的需要和所使用的操作系统，选择下载一个合适的版本安装、使用Excel。目前比较流行或被广泛使用的为Excel 2010至Excel 2021版。Excel 2021版的下载地址为

https://www.microsoft.com/zhcn/microsoft365/excel

对于一般性学习和工作，可以根据实际需要，选择安装旧版Excel，企业用户或需要使用Power Query、Power BI等功能的用户，可以先免费试用Microsoft 365，然后按需要订阅或一次性购买。

5. 本书结构与主要内容

本书详解了Excel在数理统计、运筹决策方面的运算和分析功能，并以教学习题和具体问题为示例，讲解了运用公式分析、求解数理统计和运筹规划问题的方法。作为Excel数据分析的延伸或补充，本书还讲解了Power BI数据建模与可视化分析等内容。全书分为以下8章。

第1章讲解如何运用公式与函数，主要内容有运算符和表达式、在公式中使用函数、公式中的单元格引用、在公式中定义和使用名称、三维引用、数组公式和审核公式等内容。

第2章讲解运用公式和109个统计函数计算或求解数理统计问题的方法，主要内容包括平均值及度量平均趋势，最值、排位与数据量分析，离散型随机变量分布，连续型随机变量分布，随机变量的数字特征，区间估计，假设检验、回归分析及预测等。

第3章讲解数据分析工具，主要内容包括删除重复值与数据验证、合并计算、分级显示、模拟分析、预测工作表、“数据分析”工具等。

第4章结合管理科学及运筹学中的规划建模与求解问题，讲解如何运用工作表中的计算功能、公式及函数解决线性、非线性及动态规划问题，内容包括规划求解工具、图解法与单纯形法、运输问题、目标规划、整数规划、非线性规划、动态规划等。

第5章运用了公式及函数、图形等功能，采用分解、演算、可视化方法，详细讨论管理科学和运筹学中的决策分析问题，内容主要包括图与网络分析、网络计划、排队论、存储论、对策论和决策分析等。

第6～8章详解了Power BI的功能与操作方法，主要内容包括安装和运行Power BI，导入和转换数据，创建报表，钻取、交互与插入元素，视图，AI视觉对象，R脚本视觉对象，Python脚本视觉对象，分页报表，建模，度量值及其视觉对象，在Power BI中使用DAX，使用外部工具编辑数据，Power BI服务，Power Apps，Power Automate等。

为了适应不同专业背景读者的需要，本书还补充了如下电子书内容，读者可以根据需要下载。

(1) 本书为从零开始学习的初学者补充了“快速入门”和“工作簿基础”，主要内容包括创建新工作簿、输入数据、设置格式及表格线、制作并打印简单报表、保存和管理工作簿文件等基础知识和操作方法。

(2) 为便于读者运用函数，提高数据分析效率，在上述统计函数基础上，本书补充了逻辑(包括新增的LET、LAMBDA)、查找与引用、日期与时间、文本、信息、财务、数学、工程、数据库、数据集、Web等其他356个函数的用法，以及创建自定义函数的方法。

(3) 本书补充讲解了表格(Excel Tables)、数据透视表(Pivot Tables)、超级透视表(PowerPivot)和超级视图(Power View)4个实用列表数据管理工具或程序的用法。

(4) 本书围绕工作表“插入”“页面布局”“视图”选项卡，补充了插图、绘图、图表、三维地图、文本与符号、页面布局和视图等选项或工具的用法。

(5) 本书为正在或将要使用Power BI的读者，补充讲解了Power Query，主要内容包括界面与功能、获取数据、整理数据、转换数据、添加列、自定义函数、高级编辑器与M语言等。

(6) 本书为学习和使用VBA、XML的读者补充讲解了VBA、宏与XML，主要内容包括VB编辑器、对象、方法、属性、事件、语法、语句、运算符、函数、录制宏、编辑宏代码、测试代码、运行宏、XML编辑器、XML语法、导入XML文件、编辑XML表、导出XML文件等。

6. 本书的特点

笔者在读硕、读博阶段，用Excel完成了许多学习、研究和写论文任务。在那段快乐的时光里，Excel使笔者认识到数学的魅力和神奇，提升了学习和研究兴趣，也提高了建模和决策分析能力。当时，笔者的课题和论文中一个核心问题是地价模型。在完成课题建模过程中，令笔者非常有成就感的是向同学们展示调研提纲、建模思路，组织同学实地调查研究，阅读分析同学们的调查报告，用调研获得的数据建立模型。当然，设计调研表格、建模和模型评价等工作都是用Excel完成的。学习与创新的重要动力是兴趣，使用Excel不仅可以完成数据分析工作，还可以感受数字的魅力与神奇，提高学习数学的兴趣。本书将详解功能、探究原理与培养解题兴趣相结合，突出了如下特点。

(1) 将功能用途与相关教材及教学内容相结合，解剖式详解函数或工具。本书的讲解从基本定义和原理切入，用公式和示例解剖复杂函数、求解或分析工具，使读者能够按照教学或考试中书写公式和计算的要求，在电子表格中写入公式进行求解和分析，然后用函数或工具进行验证。

(2) 将公式及统计函数、数据分析(Data Analysis)工具和散点图结合，讲解二项分布、超几何分布、泊松分布、正态分布、伽马分布、贝塔分布、卡方分布、学生T分布、韦布尔分布、方差分析、偏度、峰度、协方差、相关系数、区间估计、假设检验、回归分析与预测等数据分析方法。

(3) 将运筹学教材有关决策分析中的习题作为示例，创新运用公式及函数、图形等功能，分析、演算、求解复杂的决策问题，包括图与网络、网络计划、排队论、存储论、对策论、风险型决策、不确定型决策、多目标决策、层次分析、数据包络分析等。

(4) 用散点图解二元线性规划及目标规划。运用散点图工具绘制约束条件直线和目标函数直线或曲线，图解可行域和最优解。

(5) 在工作表中，按照课堂作业或考试相同的格式、步骤，建立单纯形法、大M及两阶段单纯形法、改进单纯形法、对偶单纯形法和参数规划、运输问题、目标规划、整数规划、非线性规划、动态规划计算表，并巧妙运用相对、绝对引用等方法创建公式，求最优解。

(6) 注重可操作性。可操作性是正确使用软件各种功能的关键环节。在编写每个章节之前，笔者会对要解析的工具或函数进行专题研究，认知其背景、定义、定理、公式及参数，并通过实例完成测试，然后从专业角度叙述其功能、用途、使用方法和步骤，确保每个选项、每个函数、每个工具的可操作性。

(7) 注重系统性。Excel及Power Query、Power BI内容丰富，功能强大，工具及函数繁多，关系复杂，本书连同补充内容按逻辑关系，将列表数据管理、数据分析、规划求解、数据转换等工具或插件归类讲解，将所有函数按专业用途划分为33个类别，分类讨论，全书结构层次清晰，便于阅读和实践。

(8) 注重长效性。为尽可能延长计算机软件类图书的寿命周期，本书以Excel中汇集的函数或算法工具为重点详解其定理、功能及用途，并用相关教材习题为示例讲解操作方法和步骤。这些函数或算法工具与初等、高等数学和工程、财务、统计等教材中的公式及定理一样，具有较长的时效或寿命周期。

7. 本书的约定

为节省篇幅，本书使用了如下简写符和简化措施。

 连续单击“→”，如单击“公式”→“审核公式”→“显示公式 ”。

 组合键“+”，如Ctrl+Shift+F3。

 可选参数符“［ ］”，如［number2］、［sum_range］。

 简化函数的参数说明，相同或类似参数在首次出现时予以说明，后续不再赘述。

此外，本书中“汽车销售”“学生成绩”以及未注明引用的示例，均由笔者根据讲解功能和操作方法的需要虚构或改编，无特定指向和含义。

希望本书能够帮助读者轻松、愉快地学习和掌握Excel和Power BI数据分析技术，也希望本书能帮助读者解决数理统计、运筹和管理学中遇到的问题，更希望读者能够使用Excel工具，在神奇的数字领域取得突破。

Excel和Power BI涉及的专业领域非常广泛，尽管笔者对本书内容做了深入的研究，但精力和水平有限，书中难免有疏漏之处，欢迎广大读者批评指正。

翁东风2024年11月于北京