原文：Mastering Advanced Excel - With ChatGPT Integration

译者：飞龙

协议：CC BY-NC-SA 4.0

第九章数据透视表

介绍

数据透视表是一种基于交互式工作表的表格，可以快速汇总大量数据，使用您选择的格式和计算方法。它被称为数据透视表，因为您可以围绕核心数据区域旋转其行和列标题，以便为您提供源数据的不同视图。随着源数据的变化，您可以更新数据透视表。它驻留在工作表上，因此，您可以使用标准公式将数据透视表集成到更大的工作表模型中。您可以使用数据透视表分析 Excel 工作簿中的数据，或者来自外部数据库，如 MS Access 或 SQL Server。

结构

在本章中，我们将讨论以下主题：

• 检查数据透视表

• 推荐的数据透视表

• 创建数据透视表

• 总计的百分比

• 使用数据透视数据创建图表

• 切片器

• 时间轴

• Power View

• Power Pivot

• 数据模型的好处

• 使用 Power Pivot 创建数据透视表

目标

学习本章后，读者将能够创建数据透视表，使用数据透视表制作不同的报告，以及使用数据透视表的高级功能。

检查数据透视表

数据透视表所基于的数据称为源数据。每列代表一个字段或信息类别，您可以将其分配给数据透视表的不同部分，以确定数据的排列方式。您可以添加四种类型的字段，这些字段在以下表 9.1 中进一步解释：

字段	描述
报告筛选器	在数据透视表中筛选汇总数据。如果在报告筛选器中选择一个项目，则数据透视表的视图仅变化以显示与该项目相关的汇总数据。例如，如果区域是一个报告筛选器，您可以显示北部、西部或所有区域的简短数据。
行标签	行显示字段中的项目作为标签。例如，行标签季度字段包含值，这意味着表格显示每个季度的一行。
列标签	列标签是产品字段的值，这意味着表格显示每个产品的列，在显示项目为列标签的字段中。
Σ 值	包含汇总数据。这些字段通常包含数值数据，例如销售和库存，在数据出现的区域称为数据区域。

表 9.1：数据透视表中的字段类型

参考以下图 9.1：

图 9.1：数据透视表的字段

选择数据范围中包含每列顶部行的标题的任何单元格。在“表格”组中，点击“数据透视表”按钮，或点击“数据透视表”列表并选择“数据透视表”。在“表/范围”框中，选择包含数据的范围以在数据透视表中使用。选择数据透视表的位置。你可以将数据透视表放在新工作表或现有工作表中。点击“确定”创建数据透视表。

你可以向数据透视表添加字段以指定要显示的数据。源数据的字段显示在“数据透视表字段列表”任务窗格中。要添加字段，将相关字段从“数据透视表字段列表”顶部拖动到底部的四个区域之一。你可以向一个区域添加多个字段，并且不需要将所有字段都添加到表中。

要显示数据，使用“行标签”列表中的数据和“数值”中的数字：

1. 激活“插入”选项卡，打开“创建数据透视表”对话框。

2. 只需添加字段标题，你需要将至少一个字段放在“数值”区域。

字段就位后，你可以通过在筛选列、筛选行或筛选列表中从报告中选择信息来过滤出现在表中的信息。例如，你可以显示所有数据值或限制数据透视表仅总结其中的一部分。

推荐的数据透视表

检查数据透视表的推荐。为此，选择数据库并点击“插入”选项卡。点击推荐的数据透视表选项，如下所示图 9.2：

图 9.2: 推荐的数据透视表

或者，简单地选择数据以制作数据透视表。点击右下角的按钮，选择表选项以创建数据透视表报告。

创建数据透视表

要开始我们的分析，我们将使用提供的销售数据创建一个数据透视表。按照之前提到的步骤设置一个包括 REP、CUSTNAME、PRODUCT、DATE、QTY、CP、S.P. 和 NET 等必要字段的数据透视表。我们将使用这些字段从不同角度分析销售数据。

按照给定的步骤：

1. 选择包括标题在内的整个数据范围。你可以通过点击并拖动鼠标跨越数据或使用键盘快捷键（例如，Ctrl+A）来完成这个操作。参考图 9.3：

   

   图 9.3: 销售数据

2. 在 Excel 中，转到“插入”选项卡，然后点击“数据透视表”按钮，如图 9.4 所示：

   

   图 9.4: 插入数据透视表

3. 会出现一个“创建数据透视表”对话框。确保你选择的数据范围正确，并选择要放置数据透视表的位置（例如，新工作表或现有工作表）。参考图 9.5。

4. 点击“确定”创建数据透视表。

   

   图 9.5: 表/范围

5. 透视表字段列表将显示在屏幕右侧。此列表包含您的销售数据的列标题。参考图 9.6:

   

   图 9.6: 按客户分组的数量

6. 从字段列表中将所需字段拖放到透视表的不同区域。

7. 将“CUSTNAME”字段拖动到“行”区域，按客户名称对数据进行分组。

8. 将“QTY”字段拖动到“值”区域以计算总销售数量。参考图 9.7:

   

   图 9.7: 值字段设置

9. 您可以通过单击“值”区域中字段名称旁边的下拉箭头并选择不同的计算（例如，平均值、计数）来更改汇总函数。参考图 9.8:

   

   图 9.8: 总和和平均值

总计的百分比

要查找总计的百分比，请按照以下步骤操作：

1. 右键单击透视表中“Qty”列中的任何值。

2. 从上下文菜单中选择“值字段设置”或“值设置”。

3. 在弹出的对话框中，选择“总计百分比”或“总计的百分比”选项（确切的措辞可能因您的电子表格软件而异）。

4. 点击“确定”以应用计算。

5. 现在，透视表将显示每个客户或分组的总计百分比在“Qty”列中。

参考图 9.9:

图 9.9: 总计百分比

在透视表中对项目进行分组

如果您想要根据当前年份或年度季度准备报告，则可以在透视表中使用分组选项。按照以下步骤操作：

1. 选择数据范围中的任何单元格。

2. 激活“选项”选项卡。

3. 单击“分组字段”。

4. 在“按”框中，单击一个或多个时间段以进行分组。

日期分组

要对日期进行分组，请按照以下步骤操作：

1. 假设您已经创建了一个带有日期字段的透视表，并将其放置在“行”或“列”区域中，请继续下一步。

2. 右键单击透视表中日期列中的任何日期值。

3. 在弹出的上下文菜单中，选择“分组”或“分组字段”（具体措辞可能因您的电子表格软件而异）。

4. 在分组对话框中，选择“月份”选项。

5. 点击“确定”以应用分组。

6. 现在，透视表将按月份对日期列进行分组，每个月份在透视表中显示为单独的项目。

参考图 9.10:

图 9.10: 日期分组

按月份报告

月份报告可以在以下图 9.11 中查看：

图 9.11: 按月份

使用数据透视表创建图表

您可以使用数据透视图表来图形化显示数据。单个数据透视图表提供相同数据的不同视图。创建数据透视图表时，数据透视表的行字段变为类别，列字段变为系列。

要创建一个数据透视图表，选择数据透视表内的任何单元格，然后在“选项”选项卡上的“工具”组中点击“图表”。选择图表的选项，然后点击“确定”。您也可以通过在源数据中选择一个单元格，并在“插入”选项卡的“表格”组中的“数据透视图表”列表中选择“数据透视图表”来同时创建新的数据透视图表和数据透视图表。参考图 9.12：

图 9.12：按月份图表

要按月份和年份对数据透视表中的日期列进行分组：

1. 右键单击数据透视表中日期列中的任何日期值，然后选择“组”。

2. 在分组对话框中，选择“月份”选项，并同时选择“年份”选项。最后，点击“确定”应用分组。

参考图 9.13：

图 9.13：按年度月份分组

要在数据透视表中按年份和季度对日期列进行分组，请按照以下步骤进行：

1. 右键单击数据透视表中日期列中的任何日期值，然后选择“组”。

2. 在分组对话框中，选择“季度”选项，并同时选择“年份”选项。最后，点击“确定”应用分组。（这里 Qtr1 指一月、二月、三月）

参考图 9.14：

图 9.14：按年度季度分组

周报表

如果您在日期字段上进行了分组，可以按周对项目进行分组。在“按”框中点击“天”，并确保“天”是唯一选择的时间段。然后在“天数”框中点击 7。

参考图 9.15：

图 9.15：按天分组

现在，数据透视表将按天分组显示数据，使您可以查看按日汇总的周报表，如图 9.16 所示：

图 9.16：周报表

数字分组（创建区间）

对于数字的分组，请按照以下步骤进行：

1. 右键单击数据透视表中“净值”列中的任何值。

2. 从上下文菜单中选择“组”或“组字段”选项。参考图 9.17：

   

   图 9.17：按数字分组

3. 在分组对话框中，指定您的分组区间的起始值。例如，如果您想从 0 开始，就在“从”字段中输入 0。

4. 在“按”字段中，输入您想要使用的区间大小。在这种情况下，输入 1000。

5. 点击“确定”应用分组。

数据透视表现在将显示“净值”列，其值分组为 1000 的区间。这种分组方式使您可以根据定义的区间分析“数量”值。

切片器

切片器是易于使用的过滤组件，包含一组按钮，使您可以快速过滤数据透视表报告中的数据，无需打开下拉列表以查找要过滤的项目。当您使用常规数据透视表报告筛选器来筛选多个项目时，筛选器指示仅筛选了多个项目，并且您必须打开下拉列表以查找筛选详细信息。然而，切片器清楚地标记了应用的筛选器，并提供详细信息，以便您可��轻松理解在过滤的数据透视表报告中显示的数据。参考 图 9.18：

图 9.18：按天分组

按照以下步骤进行：

1. 选择数据透视表中的任何单元格。

2. 转到 Excel 标签中的数据透视表分析或分析选项卡。

3. 定位筛选组，然后点击“插入切片器”按钮。

4. 在“插入切片器”对话框中，选中“客户名称”旁边的复选框以选择它。参考 图 9.19：

   

   图 9.19 切片器

5. 点击“确定”按钮以插入切片器。参考 图 9.20：

   

   图 9.20：按客户名称切片器

6. 将一个切片器添加到您的工作表中。您可以根据需要调整大小和重新定位。

7. 使用切片器通过选择要包含或排除的特定“客户名称”值来筛选数据透视表中的数据。

时间轴

现在，您可以使用数据透视表时间轴而不是玩弄筛选器来显示日期。它是一个可以添加到您的数据透视表中的框，让您可以按时间筛选，并放大您想要的时间段。点击“分析” | “插入时间轴”来调用它。参考 图 9.21：

图 9.21：时间轴

图 9.22 显示了日期时间轴：

图 9.22：日期时间轴

Power View

Power View 加载项允许您以视觉上引人注目的方式定义工作表的摘要，特别强调您需要的变量。通过一个单独的选项卡，您可以选择最适合您的方式查看数据。在定义相关的 Power 字段之后，Power View 从所选工作表中提取信息，以便根据指定的查看筛选器给出完整的概述。

此外，您可以选择新的主题、背景、透明度、图片和其他内容，自由插入、修改、排列和分析关系，有效地。按照以下步骤进行：

1. 选择数据。

2. 点击“插入”选项卡。

3. 点击“Power View”选项。

4. 您可以看到带有数据字段的图像 Power View 外观。

利用右侧的筛选按钮，可以根据所有列字段的帮助，按照自己的意愿筛选数据。

您可以利用字段列表查看各种计算，如总和、最小值、最大值、平均值等。此外，通过使用薪资参数，您可以根据所选范围检查和查看数据。这是 Power View 选项的整体外观。

Power Pivot

Power Pivot 是一个插件，我们可以在 Excel 中进行强大的数据分析。该插件内置于 Excel 中，但未启用。要启用 Power Pivot，请按照以下步骤操作：

1. 转到“文件”选项卡。

2. 点击“选项” | “加载项”。

3. 在“管理”框中，点击“COM 加载项”。

4. 前往 COM 加载项中的 MS Office Power Pivot 进行检查。

这是一种用户友好的方式，使用您已经熟悉的 Excel 功能进行数据分析，例如 Office Fluent 用户界面、数据透视表和数据透视图以及切片器。通过 Power Pivot，我们可以快速整合来自各种来源的大量数据，快速进行信息分析，并轻松共享见解。

数据模型的优势

在 Excel 和 Power Point 中，您可以创建一个数据模型，这是一个具有关系的表格集合。在 Excel 工作簿中看到的数据模型与在 Power Pivot 窗口中看到的数据模型类似。您导入到 Excel 中的任何数据都可以在 Power Point 中使用，反之亦然。

数据模型是反映业务运营和流程之间 RE-L 世界关系的表格及其关系的集合；例如，产品与库存和销售之间的关系。

按照给定的步骤进行操作：

1. 将数据转换为表格。

2. 将 Excel 表格导入 Power Pivot。

   1. 表字段列表可以在数据透视表中有多个表。

   2. 我们可以基于一个共同字段在表格之间建立关系，这样您就不需要使用查找。

   3. Power View 是由数据模型启用的。

使用 Power Pivot 创建数据透视表

我们有四个不同工作表中的四个数据：城市详情、客户、订单和订单详情，需要创建一个按城市划分的报告，关于总销售数量。因此，我们需要按照给定的步骤进行操作：

1. 选择数据

   1. 转到主页选项卡。

   2. 选择样式组。

   3. 格式化为表格。

   4. 点击“设计”选项卡。

   5. 在“属性”组中，为表格命名。

2. 选择 Power Pivot 选项卡。

   1. 转到“表格”组。

   2. 添加到数据模型。所有表格都将添加到数据模型中。

3. 定义关系。

   1. 转到 Power Pivot 视图中的主页选项卡。

   2. 从“查看”选项卡中选择“图表”视图。

   3. 然后拖放共同字段以创建两个表格之间的链接。

4. 在“查看”选项卡中选择“数据”视图。

5. 点击主页选项卡上的数据透视表。

6. 创建数据透视表。

7. 从表格（活动和全部）中选择字段。具有黑色边框的表格表示它们与其他表格链接。

8. 从城市详情中选择城市名称，从订单详情中选择数量��

结论

总的来说，关于数据透视表的章节提供了对 Excel 中这一强大工具进行数据分析和报告的全面理解。数据透视表允许用户快速高效地总结和分析大量数据，提供不同的视角和洞察数据。

该章节涵盖了各种主题，包括检查数据透视表、推荐的数据透视表选项、格式化数据透视表报告、使用数据透视数据创建图表、利用切片器和时间线进行数据过滤，以及探索高级功能如 Power View 和 Power Pivot。

通过按照章节中提供的逐步说明和示例，读者可以学习如何创建数据透视表，根据其分析需求自定义它们，并利用高级功能来增强其数据分析和报告能力。

总体而言，数据透视表提供了一种灵活动态的探索和展示数据的方式，使用户能够根据总结的信息获得有价值的见解，并基于此做出明智的决策。通过掌握数据透视表，用户可以高效地分析数据，发现趋势，并有效地传达他们的发现。

练习

1. 打开包含销售数据的 Excel 电子表格。

2. 创建一个数据透视表来计算每个产品类别的总销售收入。

3. 格式化数据透视表，使其视觉上吸引人且易于阅读。

4. 创建一个数据透视图表来表示各产品类别的销售收入。

5. 创建另一个数据透视表来确定销售收入最高的地区。

6. 创建一个数据透视图表来可视化各地区的销售收入。

7. 创建一个数据透视表来分析每月销售收入。

8. 使用条件格式设置来突出显示任何重大变化或趋势。

9. 创建一个数据透视表来计算每个产品类别的平均销售收入。

加入我们书籍的 Discord 空间

加入书籍的 Discord 工作空间，获取最新更新、优惠、全球科技动态、新发布和与作者的交流会话：

discord.bpbonline.com

第十章条件格式设置

介绍

Excel 中的条件格式设置允许我们根据特定条件将格式设置规则应用于单元格或范围。它帮助我们突出重要信息，可视化数据趋势，并使我们的工作表更具视觉吸引力。在本章中，我们将探讨不同类型的条件格式设置，例如基于单元格值或公式的格式设置，使用图标集，以及使用数据库案例研究将条件格式设置应用于现实场景。通过本章结束时，您将对如何有效使用条件格式设置来增强 Excel 中的数据分析有扎实的理解。

结构

在本章中，我们将讨论以下主题：

• 条件格式设置

  • 使用单元格值进行条件格式设置（基于列的条件格式设置）

  • 使用公式进行条件格式设置（基于记录的条件格式设置）

• 图标集

• 具有多个条件的公式

目标

学习完本章后，读者将能够定义条件格式设置并识别应用条件格式设置的方法。他们还将学习如何应用具有多个条件的公式。

条件格式设置

当数据需要根据特定条件进行格式化时，我们可以使用条件格式设置。通过使用数据条、颜色刻度或图标集，可以轻松突出显示单元格或单元格范围，强调异常值，并通过可视化数据来可视化数据。条件格式设置会根据条件（或标准）更改单元格范围的外观。如果条件为真，则根据该条件格式化单元格范围。如果条件为假，则不会格式化单元格范围。参考图 10.1：

图 10.1：条件格式设置

您可以根据需要选择以下任何一种格式：

• 通过使用双色刻度格式化所有单元格。

• 通过使用三色刻度格式化所有单元格。

• 通过使用数据条格式化所有单元格。

• 通过图标集格式化所有单元格。

• 仅格式化包含文本、数字或日期/时间值的单元格。

• 仅格式化前几名或末尾几名的值。

• 仅格式化高于或��于平均值的值。

• 仅格式化唯一或重复值。

• 比较表列以确定要格式化的单元格。

• 使用公式确定要格式化的单元格。

• 清除条件格式。

例如，您可以将所有销售额超过 75,000 的数字标记为绿色。条件格式设置可以基于单元格值或公式应用。

注意：在应用条件格式设置之前选择的区域决定了格式将应用到工作表的区域。

使用单元格值进行条件格式设置（基于列的条件格式设置）

要根据单元格值应用底纹，可以按照以下步骤进行操作：

1. 选择要应用条件着色的单元格。

2. 激活工作表功能区。

3. 在样式组中，点击条件格式化。

4. 从出现的画廊中选择突出显示单元格规则。

5. 从菜单中选择单元格中值的条件或选择更多规则。

6. 指定条件和单元格着色。

7. 点击确定应用格式。

使用公式进行条件格式化（基于记录的条件格式化）

要基于公式应用条件格式化，请按照以下步骤操作：

1. 选择要应用格式的单元格。

2. 在样式功能区组中，点击条件格式化。

3. 从菜单中选择条件格式规则图例，打开格式规则图例对话框。

4. 点击新建规则，打开添加格式规则对话框。

5. 在“选择规则类型”框中，选择使用公式确定要格式化的单元格。

6. 在“当此公式为真时格式值”框中，输入一个评估为真或假的值的公式。

7. 点击格式，打开格式单元格对话框，如图 10.2 所示。

8. 在“单元格格式”对话框中，指定满足条件的单元格应该显示的格式，然后点击确定。

9. 点击确定关闭添加格式规则对话框。

10. 点击确定关闭格式规则图例对话框并应用格式。

注意：如果需要将格式应用于整个数据库，在应用条件格式之前选择带有或不带有标题的数据库。但是，公式应该应用于选择中的第一行。

示例：

图 10.2 描述了如果选择与列标题一起的记录，突出显示销售部门所有人员的公式。

要突出显示薪水最高的人员的记录，如果我们选择了不带标题的数据库，则需要使用以下公式：

=$h2=max($H$2:$H$101)

参考以下图 10.2：

图 10.2：新格式规则

图标集

在 Excel 中，您可以访问更多的图标集，包括三角形、星星和方框。您还可以混合和匹配来自不同图标集的图标，并更容易地隐藏图标。例如，您可以选择仅显示高利润值的图标，并省略中间和较低值的图标。

具有多个条件的公式

如果我们只想突出显示东部地区的销售部门，我们可以按照以下步骤更改公式：

1. 选择不带标题的范围。

2. 点击主页选项卡。

3. 选择条件格式化。

4. 点击新建规则。

5. 输入公式 =AND($D2="销售部门",$E2=“东部地区”)

6. 选择所需的格式。

7. 点击确定应用并关闭。

根据不同工作表的单元格引用应用条件公式

假设我们想要通过使用另一个工作表的单元格引用来突出显示记录。在这种情况下，请按照以下步骤操作：

1. 选择整个数据而不包括标题。

2. 点击主页选项卡 | 条件格式 | 管理规则。

3. 点击新规则。

4. 将规则类型选择为使用公式确定要格式化的单元格。

5. 在编辑规则描述中，键入公式：=$D2=’crosssheet’!$B$

6. 选择所需的格式。

7. 点击确定以应用并关闭对话框。

结论

总的来说，Excel 中的条件格式化是一个有价值的功能，它允许我们根据特定条件应用格式。它帮助我们突出重要数据，可视化趋势，并增强我们工作表的呈现。通过掌握条件格式化技巧，我们可以使我们的数据更具视觉吸引力，并获得有价值的见解。

练习

打开来自练习文件夹中的 Excel_Basic 的发票工作表，并按照以下步骤获取结果：

1. 使用 Vlookup 获取客户类型和费率。

2. 创建 6 份发票工作表的副本并解决其他问题。

3. 对记录进行排序[零售商，直销/批发商。

4. 筛选 6 月份销售价格在 3000-5000 之间的记录。

5. 为零售商和批发商数量在 100-150 和 250-500 之间的记录进行筛选。

6. 根据类型和客户智能总计记录的小计数量和总金额。

7. 创建一个按月份/客户智能总计数量/总金额和金额百分比的摘要报告。

8. 突出显示名称为“直销”的客户类型的行。

加入我们书籍的 Discord 空间

加入书籍的 Discord 工作区，获取最新更新、优惠、世界各地的技术动态、新发布和与作者的会话：

discord.bpbonline.com

第十一章假设分析

介绍

在许多情况下，您可能需要在一个或多个公式中使用几组不同的值，以探索各种结果。在这种情况下，手动干预可能会增加，导致错误。假设分析工具可以在这种情况下帮助您。Excel 中有 3 种假设分析工具，分别是：

• 目标搜索

• 数据表

• 情景管理器

结构

在本章中，我们将讨论以下主题：

• 目标搜索

• 使用数据表预测数字

• 单变量数据表

• 双变量数据表

• 假设情景

• 创建情景

• 从另一个工作表合并情景

目标

在学习本章后，读者将能够理解目标搜索，定义数据表并学习情景管理器。

目标搜索

假设您已经创建了一个基于公式计算 PMT 的公式。您想知道需要多少个月才能完成分期付款，前提是您每月支付 x 金额。对于这种反向分析，您可以使用目标搜索实用程序。这种类型的分析涉及更改工作表中的值，并观察这些更改如何影响公式的结果。您可以使用目标搜索来解决具有一个变量的问题。

Excel 中的目标搜索功能帮助我们计算电子表格输入的值，使得给定公式的值与您指定的目标匹配。目标搜索可以节省您进行耗时的试验和错误分析。

使用目标搜索命令

要找到解决公式的特定值，请按照以下步骤：

1. 选择包含公式的单元格。

2. 激活“数据”选项卡。

3. 在“数据工具”组中，点击“假设分析”，选择“目标搜索”以打开目标搜索对话框。

4. 在“设置单元格”框中，指定包含您想要解决的公式的单元格。

5. 在“至值”字段中，输入您想要的��果。

6. 在“更改单元格”字段中，指定包含您想要调整的值的单元格。

7. 点击确定。

例如，一个人借款 10 万卢比，分 36 个月，每月 EMI [PMT]为 3250。如果他每月支付 5000 卢比，他将在多少个月内完成付款？

参考表 11.1：

	A	B
1	贷款金额	100000
2	利率	10.50%
3	每月付款	36
4	PMT [EMI]	($3,250.24)
		PMT(B5/12,B6,B4)

表 11.1：问题的数据

参考图 11.1：

图 11.1：目标搜索

使用数据表预测数字

数据表是一个范围，当一个或多个公式中的某些值发生变化时，显示结果。您想要在公式中输入的不同值也包括在数据表中。数据表可以使用单个变量或两个变量。

单变量数据表

用于观察一个或多个公式中单个变量变化的影响。

示例：

当我们改变利率时，函数中的月付款 PMT(b5/12, 36, 100000) 会受到影响。在此函数中，A5 被称为输入单元格，各种输入值从数据表中替换。参见 图 11.2：

图 11.2：单变量数据表示例

要创建一个单变量数据表，请按照以下步骤进行：

1. 在行或列中输入值。

2. 如果将输入值列出在列中，请在第一个输入值的上方行与输入值右侧列的交汇单元格中输入公式，如 图 11.2 所示。如果将输入值列出在行中，请在第一值左侧列与输入值行的交汇单元格中输入公式，正好在输入值行下方。

3. 选择包含输入值和公式的范围。

4. 在“数据”选项卡上，“数据工具”组中，单击“假设分析”，然后选择“数据表”以打开“表格”对话框。

5. 如果输入值在列中，请在“列输入单元格”框中指定输入单元格。如果输入值在行中，请使用“行输入单元格”框。

6. 单击“确定”。

双变量数据表

您可以使用双变量数据表来查看一个或多个公式中两个变量变化的影响，如 图 11.3 所示。例如，您可以看到更改贷款金额和付款次数如何影响每月付款。

要创建双变量数据表，请按照以下步骤进行：

1. 输入包含两个输入单元格的公式。

2. 在同一列中，在公式下方输入第一个输入值列表。在同一行中，公式右边，输入第二个输入值列表。

3. 选择包含输入值和公式的范围。

4. 在“数据工具”组中，单击“假设分析”，然后选择“数据表”以打开“表格”对话框。

5. 在“行输入单元格”框中，指定行输入单元格。

6. 在“列输入单元格”框中，指定列输入单元格。

7. 单击“确定”。

假设场景

场景是有时被称为“假设分析工具”的一套命令的一部分。场景是一组值，Excel 中保存并且也可以自动替换在您的工作表中。要预测工作表模型的结果，您可以使用场景。您可以在工作表上创建并保存不同的值组，然后切换到任何这些新场景以查看不同的结果。您可以为每个场景定义多达 32 个可变单元格。

使用场景管理器可以：

• 创建具有多组可变单元格的多个场景。

• 查看工作表上每个场景的结果。

• 创建所有输入值和结果的摘要报告。

• 将一组场景合并成单个场景模型。

• 保护场景免受修改并隐藏场景。

• 使用自动场景历史跟踪修改。

创建场景

假设您需要在下面的示例中并排分析企业在最佳、最差和当前情况下的净收入。您可以使用场景管理器以摘要格式实现所需的结果。

您可以使用“场景管理器”对话框创建场景。按照以下步骤执行相同操作：

1. 激活“数据”选项卡。

2. 在“数据工具”组中的“假设分析”列表中，选择“场景管理器”以打开“场景管理器”对话框。

3. 单击“添加”按钮以打开“添加场景”对话框。

4. 在“场景名称”框中，指定场景的名称。

5. 在“更改单元格”框中，指定包含要更改的值的单元格。（例��，选择当前场景范围的范围）。

6. 单击“确定”以打开“场景值”对话框。

7. 在“场景值”对话框中，指定更改单元格的值。

8. 单击“确定”创建场景。

如果要创建其他场景，请再次单击“添加”，然后重复该过程。完成创建场景后，单击“确定”。

示例：

在下面的示例中，如果我们需要了解一系列贷款金额、利率和还款期数的 PMT，我们可以使用场景，如图 11.3 所示。

在第一次更改中，贷款金额为 1,50,000，利率为 10.6%，期数为 24。在第二次更改中，贷款金额为 2,00,000；利率为 11%，还款期数为 48。

参考以下图 11.3：

图 11.3：场景管理器

创建场景摘要报告

要创建场景摘要报告，请按照以下步骤操作：

1. 单击“摘要场景管理器”对话框。

2. 在“结果单元格”框中，输入引用单元格的引用，这些单元格的值由场景更改（如上例中的净收入）。用逗号分隔多个引用，如图 11.4 所示。

删除场景

要删除场景，请按照以下步骤操作：

1. 激活“数据”选项卡。

2. 在“数据工具”组中的“假设分析”列表中，选择“场景管理器”以打开“场景管理器”对话框。

3. 单击要删除的场景名称。

4. 单击删除。

参考以下图 11.4：

图 11.4：场景摘要

显示场景

当您显示场景时，您会更改保存为该场景一部分的单元格的值。按照以下步骤显示场景：

1. 激活“数据”选项卡。

2. 在“数据工具”组中的“假设分析”列表中，选择“场景管理器”以打开“场景管理器”对话框。

3. 单击要显示的场景名称。

4. 单击“显示”。

注意：双击“场景”框中显示的场景名称与选择名称并选择“显示值”是相同的。

从另一个工作表合并场景

当所有工作表上的“假设”模型都相同时，合并场景很容易。源工作表上的所有变化单元格必须引用活动工作表上对应的变化单元格。Excel 将源工作表上的所有场景复制到活动工作表。要从另一个工作表合并场景，请按照以下步骤操作：

1. 打开包含你想要合并场景的所有工作簿。

2. 切换到你想要合并场景的工作表。

3. 激活“数据”选项卡。

4. 在“数据工具”组中的“假设分析”列表中，选择“场景管理器”以打开“场景管理器”对话框。

5. 点击“合并”。

6. 在“工作簿”框中，单击工作簿名称。

7. 在“工作表”框中，单击包含你想要合并的场景的工作表的名称。

8. 点击“确定”。

9. 如果你想要从更多工作表合并场景，请重复此过程。

保护场景

添加场景和编辑场景对话框包含两个保护选项：

• 防止更改

• 隐藏

如果你选择“防止更改”，然后激活工作表保护，你定义的场景将无法编辑。然而，这并不妨碍你直接在工作表上查看变化单元格的值（除非单元格本身被锁定）。相反，当选择“防止更改”复选框时，场景本身将受到保护，无法修改。

此外，选择“隐藏”复选框会从定义的场景列表中移除一个场景名称，防止其显示。一旦在“添加场景”或“编辑场景”对话框中选择了保护选项，你必须激活工作表保护。要做到这一点，请使用“工具”菜单上的保护命令，然后选择“保护工作表”。

注意：激活工作表保护时，仍然可以添加场景。但是，除非清除“防止更改”复选框，否则无法编辑或删除它们。

结论

总之，Excel 中的“假设分析”工具，包括目标搜索、数据表和场景管理器，提供了宝贵的见解，并通过探索不同场景和分析变量对公式的影响来帮助做出明智的决策。这些工具对于财务规划、预算编制和预测至关重要，让用户节省时间，减少错误，并获得更好的决策见解。

练习

你在一家财务规划公司工作，正在分析不同贷款选项对月供的影响。你有以下信息：

贷款金额：$100,000

利率：5.5%

贷款期限：20 年

使用数据工具组中的“假设分析”，执行以下任务：

任务 1：目标搜索

计算给定贷款金额、利率和贷款期限的月供（EMI）。

使用目标搜索功能找出如果每月付款增加到$800，则需要多少个月才能偿清贷款。

任务 2：数据表

创建一个一变量数据表，分析改变利率（4%，5%，6%）对给定贷款金额和期限的月付款的影响。

创建一个双变量数据表，分析改变贷款金额（$80,000，$100,000，$120,000）和贷款期限（15 年，20 年，25 年）对月付款的影响。

任务 3：场景管理器

创建一个名为“最佳情况”的场景，贷款金额为$80,000，利率为 4%，贷款期限为 15 年。

创建一个名为“最坏情况”的场景，贷款金额为$120,000，利率为 6%，贷款期限为 25 年。

生成一个场景摘要报告，显示每个场景的月付款金额。

加入我们书籍的 Discord 空间

加入该书的 Discord 工作区，获取最新更新、优惠、全球科技动态、新发布和与作者的交流：

discord.bpbonline.com

第十二章使用多个工作表、工作簿和应用程序

介绍

与多个工作表、工作簿和应用程序一起工作对于有效管理和分析数据至关重要。本章探讨了建立不同工作表之间链接、创建不同软件之间链接、利用审计功能跟踪错误、在工作组中协作以及创建超链接以实现无缝导航的各种技术。通过掌握这些技能，您将能够提高处理复杂数据场景的生产力和效率。

结构

在本章中，我们将讨论以下主题：

• 不同工作表之间的链接

• 创建不同软件之间的链接

• 审计功能

• 工作组协作

• 创建超链接

目标

学习完本章后，读者将能够识别处理多个工作表的各种方法，识别处理多个工作簿和应用程序的各种方法。

不同工作表之间的链接

如果不同工作表中有按月份排列的数据，我们需要在不同工作表中有年度数据，如图 12.1 所示，我们可以使用工作表间引用：

图 12.1: 按月份排列的数据

要使用来自不同工作表的单元格引用，您可以使用以下语法：

Sheetname!引用

图 12.2 显示了在不同工作表中找到单元格求和的示例：

图 12.2: 求和

然而，如果其他工作表中的产品名称不相同或不按相同顺序排列，这种方法将不会证明有用。在这些情况下，您可以使用 Excel 的合并功能，如图 12.3 所示：

图 12.3: 合并功能

要从不同工作表合并数据，请按照以下步骤操作：

1. 转到数据选项卡。

2. 选择合并。将打开合并对话框，如图 12.4 所示：

   

   图 12.4: 合并对话框

3. 从函数下拉框中选择要应用于数据的函数。

4. 单击浏览并选择第一组数据。

5. 单击添加。

6. 重复第二步和第三步以获取所有数据。

7. 如果要选择行和列标题，请单击顶部行和左侧列复选框。

8. 选择创建到源数据的链接，如果您希望在源数据更改时获得更新的数据。

参考以下图 12.5

图 12.5: 结果数据

创建不同软件之间的链接

让我们假设我们需要从 Excel 表格中复制某些数据到 Word 文档中。如果进行普通的复制粘贴，那么粘贴到 Word 文档中的内容是静态的，即使 Excel 工作表中的数据发生变化，它们也不会更新。现在让我们看看如何创建一个链接，使得数据即使在 Word 文档中也会更新。按照以下步骤操作：

1. 从 Excel 工作表中复制内容。

2. 在 Word 文档中粘贴时，从剪贴板组中点击“开始”选项卡。

3. 选择“编辑-特殊粘贴”。

4. 在“特殊粘贴”对话框中，选择“粘贴链接”选项，然后选择 MS Office Word 对象文档。

5. 点击确定。

6. 现在每当 Excel 工作表中的数据发生变化时，这些变化将自动反映在 Word 文档中。

审计功能

你可以使用 Excel 的审计功能来跟踪工作表中的错误。你还可以跟踪工作表上单元格和公式之间的关系。你可能想要确定公式值所依赖的单元格。Excel 提供了“跟踪前导”和“跟踪从属”命令来指出这些单元格。

从属和前导单元格

前导单元格向特定单元格提供数据。从属单元格依赖于另一个单元格的值。当你在“公式”选项卡上的“公式审计”功能区组中点击“跟踪前导”和“跟踪从属”按钮时，Excel 会绘制箭头显示前导和从属单元格。

团队协作

共享工作簿使得一个工作组的多个成员能够协作处理相同的数据集。例如，几个销售经理可以在同一个工作簿中输入各自地区的销售数据，从而无需手动收集和整合数据。

共享工作簿

要共享工作簿，请按照以下步骤操作：

1. 打开你想要共享的工作簿。

2. 激活审阅功能区选项卡。

3. 在更改功能区组中，点击“共享工作簿”以打开“共享工作簿”对话框。

4. 激活编辑选项卡。

5. 勾选允许多个用户同时进行更改，然后点击确定。

6. 将工作簿保存在其他用户可以访问的位置。

你可以通过使用“共享工作簿”对话框的“高级”选项卡来控制工作簿的共享方式。例如，在更新更改下，你可以选择在文件保存时查看其他用户每次保存工作簿时的更改。你还可以设置更改将自动显示的间隔。

合并工作簿

你可能需要在无法同时访问同一文件的用户之间共享工作簿。在这种情况下，你可以分发共享工作簿的副本，允许用户对其副本进行更改，然后将这些副本合并为单个工作簿。要共享一个打算稍后合并的工作簿，请按照以下步骤操作：

1. 打开“共享工作簿”对话框，激活“编辑”���项卡，并勾选允许多个用户同时进行更改。

2. 在“高级”选项卡下的“跟踪更改”中，选择 保留更改历史。在框中，输入您希望允许用户在工作簿中进行更改的天数。

3. 点击确定。

4. 复制工作簿并将其分发给每个用户。

用户对其工作簿副本进行更改后，您可以使用以下步骤将这些副本合并为单个工作簿：

1. 选择 文件，Excel 选项 打开 Excel 选项对话框。

2. 选择 自定义，并将 比较和合并工作簿 添加到快速访问工具栏。

3. 在快速访问工具栏上，选择 比较和合并工作簿 打开 选择要合并到当前工作簿的文件 对话框。

4. 选择包含您想要合并更改的工作簿副本。

5. 点击确定。

跟踪更改

您可以通过使用“跟踪更改”功能来分析用户对工作簿所做的更改。这将告诉您谁做出了更改，何时做出了更改，原始值和更改后的值，而无需手动比较两个工作簿。如果您的工作簿未共享，Excel 在您打开“跟踪更改”功能时会自动将工作簿共享。要突出显示更改，请按照以下步骤操作：

1. 激活审阅功能区。

2. 在“更改”功能区，点击 跟踪更改 并选择 高亮显示更改 打开 高亮显示更改 对话框。

3. 如果工作簿未共享，请勾选编辑时跟踪更改。如果工作簿已共享，默认情况下将会被选中。

4. 指定您希望如何跟踪更改：

   1. 如果想要根据更改时间查看更改，例如在特定日期之后，请勾选何时，然后从列表中选择必要的设置。

   2. 如果想要查看特定用户所做的更改，请勾选谁，然后从列表中选择所有人或除了我之外的所有人。

   3. 如果想要查看对特定单元格范围所做的更改，请勾选何处，然后输入范围。

5. 点击确定。

要查看工作簿更改并接受或拒绝它们，请按照以下步骤操作：

1. 打开包含跟踪更改的工作簿。

2. 激活审阅功能区。

3. 在“更改”功能区，点击 跟踪更改 并选择 接受/拒绝更改。

4. 将提示保存工作簿。点击确定保存工作簿。

5. 出现“选择要接受或拒绝的更改”对话框。

6. 如果想要根据更改时间查看更改，请勾选何时，然后选择一个时间段。

7. 点击确定打开 接受或拒绝更改 对话框。

8. 同时，包含更改值的单元格将被突出显示。此对话框显示有关每个更改的信息，包括进行更改的人员姓名、更改日期和时间，以及如果接受或拒绝建议更改将发生的其他更改。您可以向下滚动查看其余内容。

9. 点击接受以接受更改，或点击拒绝以恢复原始值。

10. 下一个具有更改值的单元格将被突出显示。

创建超链接

要在同一工作簿内创建超链接，请按照以下步骤操作。

1. 要链接特定单元格，首先为单元格命名。

2. 选择单元格，点击公式选项卡。

3. 选择定义名称。

4. 定义名称对话框将出现。

5. 在定义对话框中，键入单元格的名称并点击添加。

6. 点击关闭。

7. 现在点击要创建超链接的单元格。

8. 点击插入-超链接或按 Ctrl+K。

9. 插入超链接框将出现。

10. 点击此文档中的位置选项。

11. 点击定义名称选项。工作簿的定义名称将显示。

12. 选择在前一步中创建的定义名称。

13. 点击确定。

14. 链接已创建。

创建到不同文件的链接

要创建到不同文件的链接，请按照以下步骤操作：

1. 要链接特定单元格，首先为单元格定义名称。

2. 选择单元格，点击公式选项卡。

3. 选择定义名称。定义名称对话框将出现。

4. 在定义对话框中，为单元格命名。

5. 点击添加，然后点击关闭。

6. 现在点击要创建超链接的单元格，然后点击插入-超链接或按 Ctrl+K。

7. 插入超链接框将出现。

8. 点击现有文件或网页选项。

9. 浏览到您保存要链接到的文件的文件夹。

10. 点击列表中出现的文件。

11. 文件路径将显示在其下的地址框中。

12. 点击确定。

您也可以使用此方法链接到网页。要执行此操作，请在地址框中输入网页的 URL。要链接到 Excel 工作簿中的特定单元格，在上述过程的第六步中，在地址框的文件路径末尾提及工作表名称和单元格引用，格式如下：

文件路径#工作表名称!命名范围

例如，使用 c:\test.xlsx#salary!A1 来引用位于 c 驱动器中 test.xlsx 文件中 Salary 工作表的 A1 单元格。

您还可以在创建超链接或超链接到电子邮件地址时使用超链接对话框中的选项创建新文档。

结���

与多个工作表、工作簿和应用程序一起工作需要对可用功能和技术有扎实的理解。本章为您提供了有关在工作表之间建立链接、在不同软件之间创建链接、利用审计功能、在工作组中协作以及创建超链接的宝贵见解。通过在日常工作中应用这些概念，您可以简化数据管理流程并改善与他人的协作。通过实践，您将能够熟练地处理多个数据源，并最大限度地发挥工作表、工作簿和应用程序的潜力。

练习

1. 打开一个 Excel 工作簿，并创建三个名为“销售”、“支出”和“汇总”的工作表。

2. 在“销售”工作表中，输入不同产品的月度销售数据。

3. 在“支出”工作表中，输入各种类别的月度支出数据。

4. 使用工作表间引用来计算“汇总”工作表中的总销售额和总支出。

5. 使用 Excel 的合并功能将“销售”和“支出”工作表中的数据合并到单独的工作表中。

6. 在 Excel 工作表和 Word 文档之间创建链接。将 Excel 工作表中的汇总数据复制并粘贴到 Word 文档中作为链接对象。

7. 使用 Excel 的审计功能来追踪一个工作表中特定公式的前导和依赖项。

8. 与同事共享工作簿并允许同时编辑。

9. 将多个副本的工作簿中的更改合并回单个工作簿。

10. 跟踪不同用户在工作簿中所做的更改，并审查和接受/拒绝这些更改。

加入我们书籍的 Discord 空间

加入书籍的 Discord 工作空间，获取最新更新、优惠、全球科技动态、新发布和与作者的会话：

discord.bpbonline.com

第十三章使用图表

介绍

在本章中，我们将探讨在 Excel 中使用图表的主题。图表是一种强大的工具，可用于将数据可视化并以有意义的方式呈现。通过创建图表，您可以快速分析趋势、比较数据并有效地传达信息。本章将指导您使用图表工具创建图表、了解不同的图表设计、格式化图表以及利用 Excel 提供的推荐。我们还将介绍火花线，这是一种紧凑的图表，可以嵌入单元格中，提供数据趋势的快照。

结构

在本章中，我们将讨论以下主题：

• 使用图表工具创建图表

• 图表设计

• 格式化图表

• 推荐

• 火花线

目标

在学习本章之后，读者将能够使用图表工具创建图表，识别不同类型的图表并格式化图表。

使用图表工具创建图表

图表可以更有效地呈现数据。例如，分析数据趋势需要时间，但如果以图形方式表示，就会变得容易。有许多类型的图表，如柱状图、折线图、饼图、条形图、区域图和散点图。要创建图表，请按照以下步骤进行：

1. 选择数据。

2. 转到插入选项卡

3. 从图表组中选择要创建的图表，如图例 13.1 所示。

4. 我们可以选择任何类型的柱状图或单击“所有图表类型”|“插入图表”。将显示对话框。

5. 选择所需的图表。

6. 单击确定。

7. 该图表将在同一工作表上创建，Excel 还将在该对话框中推荐一些图表。

参考以下图例 13.1：

图例 13.1: 图表

图表设计

Excel 为每种图表类型提供了不同的图表设计，我们可以从设计选项卡中进行选择。可以在以下图例 13.2 中看到设计画廊的示例：

图例 13.2: 图表设计

使用图表工具添加标题和值

Excel 为我们提供了一些内置布局。这些可以从图表工具的设计选项卡中选择。我们还可以创建一些自定义布局，并根据需要设置轴标题、图表标题、图例、数据标签和数据表位置。

要执行此操作，请按照以下步骤：

1. 使用设计选项卡。在图表布局组中单击“添加图表元素”选项。

2. 添加图表元素。

3. 在“添加图表元素”选项下，用户可以使用许多选项来格式化图表，例如添加次轴、为轴命名、为图表命名、数据标签等等。

参考以下图例 13.3：

图例 13.3: 图表的关联选项

格式化图表

Excel 提供了一种更改默认设计模板中各部分默认颜色的方法。

要这样做，请使用“图表工具”选项卡下的“格式”上下文选项卡。它将为您提供各种选项，以更改轮廓颜色、形状对齐和定位图表。

���的数据的图表

根据您的需求，您可以选择不同类型的图表来代表您的数据。

表 13.1 展示了各种数据类型以及可以有效表示它们的图表：

图表类型	所代表的数据
柱状图	代表一段时间内数据的变化。
条形图	代表数值比较。
折线图	代表均匀间隔的值
散点图	显示和比较数值，如统计、科学和工程数据。
饼图	代表给定值中不同值的关联。
圆环图	代表部分与整体的关系
气泡图	代表金融数据
面积图	代表两组数据之间的最佳组合
雷达图	当您想查看与一个项目相关的几个不同因素时使用

表 13.1：图表类型

图表模板

有时，在创建图表后，我们决定在将来也使用相同的设计。每次手动执行可能会很困难。Excel 提供了一种方法，可以将图表保存为模板。执行以下步骤保存图表为模板：

1. 创建图表后，在图表上右键单击并选择“另存为模板”选项。

2. 出现“另存为”对话框。在这里，保存您的图表。

如果将来想要使用此模板，请执行以下步骤：

1. 右键单击新创建的图表。

2. 出现上下文菜单。单击“更改图表类型”。

3. 在“插入图表”对话框中，单击“模板”。在这里，您可以看到所有保存的模板。

4. 现在，选择要应用于当前图表的所需模板。

图表筛选选项

另一个令人印象深刻的功能是图表筛选选项，它在插入图表时列出所有变量（系列）和类别，以便进行交互查看。过去过滤图表信息非常困难。如果您想按照自己的要求查看信息，请使用“图表筛选”按钮。

瀑布图

要在 Excel 中创建瀑布图，您可以按照以下步骤进行：

1. 选择包含类别和数值数据的表格。

2. 转到 Excel 功能区中的“插入”选项卡。

3. 在“图表”部分，单击“瀑布图”类型。

4. 选择所需的瀑布图子类型（例如，“瀑布”或“堆积瀑布”）。

5. Excel 将根据图 13.4 中提供的选定数据生成瀑布图：

   

   图 13.4：用于图表的数据

图表可以在以下图 13.5 中看到：

图 13.5：瀑布图

结果瀑布图将显示每个类别作为一个条形图，正值由上升超过起始点的条形表示，负值显示为下降到起始点以下的条形。累积总数由每个条形的长度和位置表示。

推荐

插入选项卡中拥有从推荐的数据透视表到推荐的图表的新功能。Excel 提供了建议，以在最短时间内实现最大效果。为了获得最佳结果，请使用 Excel 的建议。

要使用推荐，请执行以下步骤：

1. 选择要创建图表的数据。

2. 点击插入选项卡

3. 选择推荐的图表选项。您将看到各种图表类型。

4. 选择其中任何一个。

迷你图

迷你图是可以放入单元格中的小图表。迷你图帮助用户在数据旁边查看摘要趋势。它占用很少的空间。特别适用于仪表板或其他需要以易于理解的视觉格式显示业务快照而不添加大量细节的地方。例如，以下图 13.6 和图 13.7 展示了迷你图如何让您一目了然地看到每个部门在五月份的表现：

图 13.6：迷你图示例 1

参考图 13.7：

图 13.7：迷你图示例 2

创建迷你图

要创建迷你图，按照以下步骤进行：

1. 选择要插入一个或多个迷你图的空单元格或一组空单元格。

2. 在插入选项卡上，在迷你图组中，点击您想要创建的迷你图类型：线条、柱状图或盈亏图。

3. 在数据框中，输入包含您想要基于的数据的单元格范围。

4. 选择一个或多个迷你图后，迷你图工具将出现并显示设计选项卡。

5. 在设计选项卡上，您可以从迷你图、类型、显示/隐藏、样式组中选择一个或多个命令。

6. 使用这些命令来创建新的迷你图，更改其类型，格式化它，显示或隐藏线迷你图上的数据点，或在迷你图组中格式化垂直轴。

自定义迷你图

创建迷你图后，Excel 提供了所有控制选项来自定义迷你图，如高、低、首个、最后一个或任何负值。您还可以将迷你图类型更改为其他类型，如线条、柱状图或盈亏图。您还可以从库中选择样式或设置单独的格式选项，设置垂直轴上的选项，并控制空值或零值的显示方式。

更改迷你图的样式

在选择包含数据的单元格时，使用 Design 选项卡上的样式库，执行以下步骤来更改走势图的样式：

1. 选择单个走势图或走势图组。

2. 要应用预定义的样式，请在 Design 选项卡上。

3. 在样式组中，单击一个样式或单击框的右下角的箭头以查看其他样式。

4. 为走势图选择特定的格式。

结论

图表是 Excel 中用于以视觉和易于理解的格式呈现数据的重要工具。通过掌握本章讨论的技术，您将能够有效地创建、自定义和格式化图表。无论您需要分析趋势、比较数据还是向他人传达信息，图表都可以极大地增强数据呈现的清晰度和影响力。

练习

1. 从您自己的电子表格中选择一组数据或创建一个示例数据集。

2. 使用所选数据创建一个柱状图。

3. 通过添加标题、图例和数据标签来自定义图表。

4. 将不同的图表设计应用于创建的图表并观察变化。

5. 通过更改颜色、轮廓和其他格式选项来格式化图表。

6. 将图表保存为模板以供将来使用。

7. 使用 Excel 中的推荐功能来探索数据集的不同图表选项。

8. 在单元格中创建一个小型走势图以表示数据子集中的趋势。

9. 通过更改其类型、样式和格式选项来自定义走势图。

10. 与他人分享您的图表和走势图，展示您的数据分析和可视化技能。

加入我们书籍的 Discord 空间

加入书籍的 Discord Workspace 以获取最新更新、优惠、全球科技动态、新发布和与作者的交流：

discord.bpbonline.com

第十四章在 VBA 中创建和记录宏

简介

在本章中，我们探索了 VBA 宏的世界及其在 Microsoft Excel 中自动化重复任务中的作用。VBA 代表 Visual Basic for Applications，是嵌入在 Excel 中的强大编程语言。宏是一系列命令，允许我们自动化操作并简化工作流程。无论您是初学者还是有经验，本章都提供了创建和记录宏的全面指南。您将学习 VBA 的基础知识，宏的好处，以及如何通过编写代码或记录操作来创建宏。准备好通过 VBA 宏的力量提高在 Excel 中的生产力和效率。

结构

在本章中，我们将讨论以下主题：

• VBA 简介

• 宏简介

• 创建宏

• 记录宏

• 定义宏

• 停止录制

• 相对引用宏

• 运行您的宏

• 按名称运行宏

目标

通过本章，我们的目标是介绍 VBA 和宏，解释创建和记录宏的过程，定义宏及其属性，演示如何运行宏，并提供创建宏以自动化 Excel 任务的实际示例。

VBA 简介

VBA 代表 Visual Basic for Applications。它是包含在所有 Microsoft Office 应用程序中的编程语言，如 Excel、Word、PowerPoint 等。它也是 Excel 宏所编写的语言。VBA 是 Microsoft Visual Basic 的一个子集。

VBA 的用途

VBA 的一些用途如下：

• 驱动整个应用程序。

• 将多个操作组合为一个宏。

• 编写您自己的函数。

宏简介

宏是按逻辑顺序编写的一系列命令，以自动化任何重复任务。它存储在 Microsoft Visual Basic 模块中。它可以分配给“加载项”选项卡或“快速访问工具栏”上的按钮。

一些制作宏的示例包括：

• 在按下按钮时自动向任何电子表格添加标准公司页眉。

• 将来自总账系统的文本文件格式化为更易用的格式。

• 从工作簿内打印出特定的工作表，而不是逐个工作表打印。

创建宏

创建宏有两种方法：

• 编写：使用 VBA 语言为宏中的操作编写代码。

• 记录：使用宏记录器在 Excel 中记录您的操作。Excel 具有宏记录器，可以记录操作并为宏编写代码。

创建宏的最佳方法是按照以下步骤进行：

1. 确定用户想要宏实现的确切问题和最终结果。

2. 计划宏的步骤以成功获得最终结果。

3. 创建宏时可以选择录制、编写或两者结合。

注意：记录您在 Excel 中执行的操作，否则请写下来。

在功能区添加开发者选项卡

要在功能区上添加开发者选项卡，请按照以下步骤进行：

1. 单击“文件”按钮。

2. 点击“选项…”按钮。

3. 在“自定义功能区”选项卡上，选择在功能区中显示“开发者”选项卡，如图 14.1 所示：

   

   图 14.1：在功能区添加开发者选项卡

录制宏

要录制宏，请按照以下步骤进行：

1. 单击“开发者”选项卡。

2. 在“代码”组中，单击“录制宏”按钮，如图 14.2 所示：

   

   图 14.2：录制宏

定义宏

要定义宏，请按照以下步骤进行：

图 14.3：定义宏

在为宏分配名称时，请遵循以下规则：

• 宏名称可以由字母和数字组成。

• 宏名称不应以数字开头。

• 宏名称不应包含除下划线(_)之外的任何特殊符号。

• 最多可有 255 个字符。

• 不要使用既是宏名称又是单元格引用的名称。

宏存储

不同的宏存储方式如下：

• 个人宏工作簿：录制将在当前工作簿上执行，而宏将存储在名为 Personal.xls 的文件中。这是一个隐藏文件（位于 XLSTART 文件夹内），每当 Excel 应用程序打开时都会打开。

• 此工作簿：录制将在当前工作簿上执行，而宏将存储在当前文件中。

• 新工作簿：录制将在当前工作簿上执行，而宏将存储在新文件中。

注意：只有打开存储宏的文件才能使用宏。如果希望在使用 Excel 时始终可用宏，请选择“个人宏工作簿”选项。

宏快捷键

您可以使用 Ctrl+字母（小写字母）或 Ctrl+Shift+字母（大写字母）的组合键，其中字母是键盘上的任何字母键。您使用的快捷键字母不能是数字或特殊字符，如 @ 或 #。快捷键将在包含宏的工作簿打开时覆盖任何等效的默认 Microsoft Excel 快捷键。

宏描述

描述用于写入有关宏的详细信息，例如此宏的目的等。这有助于后续维护。

停止录制

要停止录制宏，请按照以下步骤进行：

1. 执行宏需要执行的操作。

2. 停止录制，可以通过在开发者选项卡的“代码”组中单击“停止录制”按钮或在底部状态栏上单击停止录制来实现。

参考以下图 14.4：

图 14.4：停止录制宏

相对引用宏

如果您希望宏相对于活动单元格的位置运行，请使用相对单元引用进行记录。在开发者选项卡上，单击使用相对引用，以便选择它。Excel 将继续使用相对引用记录宏，直到您退出 Excel 或再次单击使用相对引用，以便取消选择。

参考图 14.5：

图 14.5：停止录制宏

注意：使用相对引用按钮是一个切换按钮。请注意，开始录制之前，请检查是否已选择。

场景 1

创建一个宏，自动将公司名称以特定格式添加到任何电子表格的第一行。

为此，请参考 Training File1.xls 并按照给定步骤进行：

1. 开始录制。

2. 给名称为 Company_name。

3. 将快捷键设置为 Ctrl + Shift + C。

4. 执行以下步骤：

   1. 选择第一个单元格（因为名称应该出现在第一行）。

   2. 输入你公司的名称。

   3. 应用格式：字体大小 20，加粗，蓝色字体，白色背景。

   4. 选择 A1 到 H1 单元格。

   5. 点击合并工具。

5. 停止录制：

   1. 转到工具菜单，然后宏 | 停止录制。

参考以下图 14.6：

图 14.6：场景 1

运行你的宏

宏可以通过

• 快捷键（在定义宏时分配的）

• 名称

• 快速访问工具栏上的按钮

• 工作表上的按钮

按名称运行宏

要按名称运行您的宏，请按照给定步骤进行：

1. 转到开发者选项卡。

2. 选择宏，（快照 1）

3. 选择要运行的宏，（快照 2）

4. 点击运行按钮。

参考以下图 14.7：

图 14.7：按名称运行宏

场景 2

创建一个宏来显示一个产品表，其中包含表头，产品名称，数量，价格，总计和净额。表必须始终从第二行第一列开始显示。Excel 不应接受任何负值作为价格和数量。此宏将始终从第二行第一列（A1 引用）产生结果。

执行以下步骤（参考 Training File1.xls）：

1. 开始录制（命名为 Product_Table，快捷键 Ctrl + Shift + P）

2. 选择单元格 A2（表必须始终从第二行第一列开始显示）。

3. 根据图 14.8 创建一个表：

4. 编写总计和净额的公式。

5. 格式化它。

6. 对数量和价格单元格进行验证（限制负值）。

7. 停止录制。

参考以下图 14.8：

图 14.8：场景 2

场景 3

创建一个宏来显示相同的产品表（场景 2），但这次它应该出现在用户想要的任何地方（使用相对引用）。此宏取决于用户的选择。

执行以下步骤：

1. 开始录制（命名为 Product_Table_Relative 并设置快捷键）。

2. 在停止录制工具栏上打开相对引用按钮。

3. 根据图 14.9 从当前单元格创建表格。

   注意：从表格中的任何位置开始输入；在创建相对引用宏时不要不必要地点击表格。

4. 编写总和和净总额的公式。

5. 格式化它。

6. 对数量和价格单元格进行验证（限制负值）。

7. 关闭相对引用按钮。

8. 停止录制。

参考以下图 14.9：

图 14.9：场景 3

结论

总结一下，本章介绍了 VBA 宏的基础知识以及它们在 Excel 中自动化任务中的重要性。通过创建和记录宏，用户可以简化重复操作并提高生产效率。无论是通过手动编码还是录制功能，宏都为定制和优化 Excel 功能提供了强大的工具。通过利用宏，用户可以节省时间，减少错误，并提高效率。

练习

1. 创建一个名为“CalculateAverage”的宏，用于计算 Excel 中一系列数字的平均值。

2. 创建一个名为“FormatData”的宏，将特定格式应用于 Excel 中一系列单元格。

3. 创建一个名为“GenerateReport”的宏，用于自动化在 Excel 中生成报告的过程。

加入我们书籍的 Discord 空间

加入书籍的 Discord 工作区，获取最新更新、优惠、全球技术动态、新发布和与作者的交流：

discord.bpbonline.com