高级-EXCEL-365-包含-ChatGPT-提示

高级 EXCEL 365：包含 ChatGPT 提示

原文：Advanced Excel 365: Including ChatGPT Tips

译者：飞龙

协议：CC BY-NC-SA 4.0

前言

本书将探讨 ExcelTM、Visual Basic for Applications (VBA)TM 和 ChatGPT 的强大组合。这些工具结合数据分析、自动化和对话式人工智能的力量，让您在信息处理和决策领域获得力量。

在这些页面中，您将获得实际知识、动手实例和逐步说明，以掌握 Excel 的数据处理能力，解锁 VBA 自动化和定制的潜力，并研究 ChatGPT 进行自然语言交互。

不论你是寻求理解基础的初学者，还是希望提升技能的资深用户，这本书都将作为你在这些领域的指南。它将引导你了解 Excel 的基础知识，带你进入 VBA 编程的世界，并展示如何将 ChatGPT 集成到你的应用程序中，以实现动态和智能的对话。

本书将探索 Excel、VBA 和 ChatGPT 的无限可能性。以下是各章节的简要概述：

第一章：Excel 2021 概述

发现 Excel 2021 的新界面、组件和功能，包括在线文件共享、自定义功能区以及利用闪电填充和即时数据分析进行高效数据输入。

第二章：单元格引用和范围

了解几种类型的单元格引用和命名范围，以便更容易引用。通过实际练习练习这些概念。

第三章：使用公式和函数

掌握 Excel 的公式和函数，包括 IF 变体、查找函数和动态 VLOOKUP。通过实际练习巩固你的理解。

第四章：数据验证

设置数据验证规则以确保数据准确性。通过实际练习探索自定义验证技术。

第五章：保护

通过保护工作表、工作簿和特定部分来使用密码保护你的 Excel 文件。

第六章：数据库排序

使用简单、多级和自定义排序方法高效地组织数据。

第七章：数据库过滤

使用自动筛选和高级筛选技术过滤数据，以提取相关信息。

第八章：子总和数据合并

使用子总功能汇总和分析数据，并从多个来源合并数据。

第九章：数据透视表

创建并格式化数据透视表，以进行多用途数据分析，包括分组项目生成图表等高级功能。

第十章：条件格式化

应用基于单元格值和公式的格式化，包括具有多个条件的先进技术。

第十一章：假设分析

使用假设分析工具，如目标寻求和数据表，来预测数字和创建场景。

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

链接不同的工作表和软件，合并工作簿，并跟踪协作中的更改。

第十三章：使用图表

使用图表工具、模板和 Sparklines 创建和自定义图表，以增强数据可视化。

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

通过创建和记录宏来自动化任务，包括相对引用宏。

第十五章：将按钮分配给宏

通过创建和自定义宏的菜单和按钮来增强用户交互。

第十六章：VBA 中的函数和子程序

理解并编写 VBA 中的函数和子程序，包括分支技术。

第十七章：VBA 中的条件语句

使用 Select Case 和 If...End If 语句来有效地控制程序流程。

第十八章：VBA 中的变量和数据类型

声明变量和常量，理解数据类型，并使用消息框和输入框。

第十九章：VBA 中的循环结构

在 VBA 代码中实现循环，如 Do...Loop 和 For...Next，以重复操作。

第二十章：VBA 中的数组和集合

使用数组和集合有效地存储和管理多个值。

第二十一章：VBA 中的调试和错误处理

管理错误并调试 VBA 代码以高效地解决问题。

第二十二章：VBA 中的用户表单和用户输入

使用按钮和文本框等控件设计交互式用户表单，以增强用户输入。

第二十三章：高级 VBA 技术和最佳实践

探索高级编程技术，并遵循最佳实践以实现高效的 VBA 编码。

第二十四章：使用 VBA 构建自定义插件

创建自定义插件以扩展 Excel 的功能，并用密码保护它们。

第二十五章：使用 Excel 的 ChatGPT

在保持数据隐私和安全的同时，将 ChatGPT 与 Excel 集成以增强任务、内容生成和数据分析。

通过向出版商发送邮件至 info@merclearning.com 可以下载彩色图像。

到这本书的结尾，你将拥有知识和信心，利用 Excel、VBA 和 ChatGPT 的联合力量来简化你的工作流程，自动化重复性任务，并参与智能、数据驱动的对话。

致谢

我想向以下个人表示衷心的感谢和致意，他们的坚定不移的支持和爱是他们在这本书的创作过程中不断激励和鼓励的源泉：

致我支持我的丈夫，哈什·阿罗拉先生，他对我能力的坚定不移的信念和无条件的爱情是我的支柱。在写作过程中的鼓励和理解是无价的，我真的很幸运有你在我身边。

致我亲爱的母亲，阿莎·丁格拉夫人，以及我的岳父母，沙昆·阿罗拉夫人和 K. K. 阿罗拉先生，感谢他们无止境的鼓励、爱和牺牲。他们对我的梦想坚定不移的信念以及他们在我生活中的持续存在是完成这本书的驱动力。

致我珍爱的孩子们，Vansh Arora 和 Mannat Arora，你们在这段时间里的耐心和理解令人印象深刻。你们坚定不移的支持和灿烂的笑容一直是我的动力源泉，提醒我在这一过程中平衡和家庭的重要性。

致我的姐妹们，Sudha Khurana 夫人和 Namrata Lal 夫人，以及我的大家庭和朋友，感谢你们持续的支持、鼓励和对我能力的信任。你们对我坚定不移的信念给了我克服挑战和追求热情的力量。

致本书的读者，你们将宝贵的时间和好奇心交托给了我。我衷心希望，这些页面中分享的知识和洞察能够激发并赋予你们在 Excel 之旅上的力量。

我对每一位的贡献和支持都深表感激。感谢你们成为这一旅程不可或缺的一部分。

关于作者

Ritu Arora 是一位技术精湛且经验丰富的微软认证培训师，专长于 Power BI、Excel、PowerPoint、G Suite 和 ChatGPT。拥有超过 20 年的企业培训经验，包括国际任务，Ritu 在 DDFS、EY、RateGain、LG、IIMs、SMBC、爱立信、HCL、塔塔高级系统、BPCL、雀巢、花旗银行、阿迪达斯和 Hero Honda 等公司成功培训了超过 70,000 人。她的专业知识、出色的沟通技巧以及根据特定需求定制培训计划的能力使她成为备受追捧的企业培训师。

第一章

Excel 2021 概述

引言

Excel 2021 是微软 Excel 的订阅版，它是微软 365 生产力工具套件的一部分。这是一个基于云的 Excel 版本，为用户提供访问最新功能和更新的途径。以下是 Excel 2021 的一些关键功能的概述：

■协作编辑：Excel 2021 允许多个用户同时编辑电子表格，这使得团队更容易共同完成项目。

■云存储：Excel 2021 文件存储在云端，这意味着只要有互联网连接，就可以从任何地方访问。

■Power Query：Excel 2021 包括 Power Query 工具，它允许用户连接到并从各种来源导入数据。

■动态数组：使用动态数组，用户可以对一系列值进行计算，并在单个单元格中返回多个结果。

■人工智能：Excel 2021 包括 AI 驱动的功能，可以分析数据并提供见解。这包括如“想法”功能这样的工具，它建议图表、图形和其他可视化。

■新的图表类型：Excel 2021 包括新的图表类型，如漏斗图和地图图，允许用户以新的和有趣的方式展示数据。

■改进数据分析：Excel 2021 包括新的数据分析工具，如数据类型功能，它允许用户将原始数据转换为可用于计算和分析的结构化数据。

MS Excel 是一种电子表格软件，它是一种用于记录数据、支持绘图和分析输入数据的工具。这是一个功能强大的工具，具有许多功能，可用于跟踪预算、创建销售或发票记录或维护培训日志。您可以存储您产品的详细信息或服务查询，或探索其其他商业应用。

与之前版本一样，本版本在窗口顶部有一组称为功能区（Ribbon）的菜单。所有 Excel 命令都显示在菜单上。Excel 文档称为工作簿（Workbook），每个工作簿都分为一组行和列。这种表格结构的交叉点称为单元格。数据输入到单元格中。实际上，在电子表格中执行的所有操作都应用于单元格。MS Excel 有一套工具，用户可以使用这些工具格式化数据、执行分析和创建图表。

结构

本章将涵盖以下主题：

■Excel 窗口组件

■后台视图

■在线保存和共享文件

■与 Excel 交互

■使用默认设置

■格式化表格

• 特殊粘贴预览

• 快速填充

■快速数据分析

■数据挖掘

■TAT 保存技术

目标

在学习本章后，读者应该能够理解 MS Excel 的新布局，弄清楚如何更改默认设置，了解工具的一般用法，并识别不同类型的引用以及命名范围。

Excel 窗口组件

图 1.1 Excel 欢迎窗口

当您通过点击快捷方式打开 Excel 时，会出现一个独特的登录页面，称为欢迎页面。这个欢迎页面提供了各种示例电子表格，如电影列表、个人预算、趋势分析等。最重要的是，它提供了空白工作表选项，用户可以使用它打开空白工作表并根据需要输入数据。Excel 的欢迎窗口如图图 1.1 所示。

此窗口还有一个文本字段，允许用户搜索在线模板。这可以用来同步用户的 Excel 界面与在线 MS Office 模板库。

当您双击空白窗口选项时，会打开一个空白工作表，如图图 1.2 所示。

图 1.2 Excel 2016 窗口的各种组件

后台视图

图 1.3 后台视图中可用的选项

从后台视图，您可以管理您的文档及其相关数据。在这里，您可以创建、保存、发送和检查文档以查找隐藏的元数据或个人信息。文件选项卡取代了 MS Office 早期版本中使用的 MS Office 按钮和文件菜单。图 1.3 展示了后台视图中可用的各种选项：

下面是后台视图中可用的各种选项：

■快速访问工具栏：此工具栏位于窗口的左上角。它包含保存当前工作簿和撤销和重做操作的命令。可以通过添加常用命令的按钮来自定义此工具栏。它是可移动的，可以移动到功能区下方。

■功能区：功能区组织成多个选项卡，每个选项卡激活一个功能区。每个选项卡分为一组称为组的命令，其中包含与组名称相关的命令和选项。

■图库：图库可以显示在功能区中，但更常见的是一组命令或功能的下拉组。图库使用图标或其他图形来显示命令的结果，而不是命令本身。图 1.4 显示了图库选项。

图 1.4 图库选项

在线保存和共享文件

即使你没有 MS Office 365 或其任何版本，你仍然可以免费在线访问和查看基本内容。请参阅图 1.5。

图 1.5 后台视图中的共享选项

与 Excel 交互

用户可以通过多种方式与 Excel 工作表交互。这些是通过键入或使用鼠标选择命令、进行选择、点击按钮和其他操作。

■使用功能区：功能区是菜单和工具的主要容器。当你选择一个功能区选项卡时，它会显示功能区组，其中包含工具（按钮和列表）。其中一些工具可以展开以显示简单的列表和图库，如图图 1.3 所示。

■使用图库：图库是一个交互式选项列表，点击命令后会显示选项。例如，字体图库显示可用的字体列表。一些图库使用实时预览，因此当你将指针移到图库中的选项上时，每个选项都会预览。例如，如果你在工作表中选择了文本并显示字体图库，将指针移到图库中的每个字体上会导致屏幕上选中的文本以该字体显示。请参阅图 1.4。

■使用工具：当你将鼠标指针悬停在任何工具上时，会出现一个关于该工具的小描述，这被称为超级提示。它提供了关于工具的小描述，以便你可以了解工具的确切功能。

小贴士：按 Alt+F4 键可查看功能区内部选项的快捷键。

使用默认设置工作

Excel 允许你自定义与它交互的各种方面、行为和方法。你可以更改 Excel 的默认设置，包括字体、迭代次数、文件位置以及启动 Excel 时打开的文件。要选择选项对话框，需要点击文件选项卡按钮，然后选择“选项”，如图图 1.6 所示：

图 1.6 MS Excel 的选项窗口

各种选项如下：

■个性化选项：您可以通过使用个性化选项工具来更改工作簿设置，包括更改字体类型和大小、工作表数量以及激活用于宏的开发者选项卡。

■保存选项：此选项允许您更改文件的默认位置、文件格式和自动恢复设置。

■自定义功能区：在 Excel 中，您可以创建自定义选项卡和组，并重命名或更改内置选项卡和组的顺序。在“自定义功能区”列表中，自定义选项卡和组名称后有“自定义”字样，但在功能区中不会出现“自定义”一词。

■添加自定义选项卡和自定义组：这里有一系列步骤，我们可以通过这些步骤在功能区中添加自定义选项卡和自定义组。命令只能添加到自定义组中。

要添加自定义选项卡，请按照以下步骤操作：

点击“文件”选项卡。
点击“帮助”下的“选项”按钮。
点击“自定义功能区”。
点击“新建选项卡”。
要查看和保存您的自定义设置，请点击“确定”。参见图 1.7。

图 1.7 自定义自定义选项卡和自定义组的步骤

表格格式化

Excel 提供了各种预定义的表格样式，我们可以快速地使用这些样式来格式化表格。这是一个由 Excel 提供的格式，因此我们不需要更改表格的样式或字体。

您可以通过以下步骤格式化表格：

选择范围。
在功能区中选择“开始”。
选择样式组。
选择“格式化表格”。此选项以下拉列表的形式打开各种格式样式。通过点击任何样式，您可以将它应用到您的数据上。

粘贴特殊预览

Excel 为您提供了“粘贴并预览”功能，该功能可以在重复使用内容时节省时间。此选项可以帮助您查看各种可用的粘贴选项的预览，例如保留源列宽度、使用边框以及是否保留源格式。实时预览允许您在将内容粘贴到工作表之前看到粘贴内容的外观。当您将指针移到粘贴选项以预览结果时，您将看到一个包含根据上下文更改的项目菜单，这些项目最适合您正在重复使用的内容。屏幕提示提供额外信息，以帮助您做出正确的决定。

快速填充

这是一个令人兴奋的新功能，承诺可以节省时间。考虑一个例子：如果您在列 A 中写入名字，在列 B 中写入姓氏，并且想在列 C 中合并这两个名字，Excel 将使用快速填充功能自动填充整个列，如图 1.8（a）所示。作为另一个例子，假设我们在列 A 中有完整的电子邮件地址。您可以在新列中开始输入名字，Excel 将自动填充整个列，如图 1.8（b）所示。

图 1.8 快速填充

快速数据分析

快速分析是 Excel 中新增的一个工具，它允许单击访问数据分析功能，如公式、条件格式、Spark 线、表格、图表和数据透视表。您只需选择一些数据，然后右键单击，即可看到各种快速分析选项。

在此示例中，有按部门划分的薪资数据，但需要以适当的格式展示。您需要进行快速分析。请参阅图 1.9：

图 1.9 快速数据分析工具

数据挖掘

高级 Excel 几个功能可用于数据挖掘，即从大量数据集中发现模式和洞察的过程。以下是 Excel 中一些关键的数据挖掘功能：

■数据透视表：数据透视表允许您快速轻松地汇总和分析大量数据集。您可以使用数据透视表创建交互式报告、识别趋势，并在您的数据中发现模式。图 1.10 展示了数据透视表的图标：

图 1.10 数据透视表

■条件格式：条件格式允许您根据某些标准突出显示特定数据。这有助于识别异常值、发现趋势并在您的数据中识别模式。图 1.11 展示了条件格式的图标：

图 1.11 条件格式

■数据验证：数据验证允许您为电子表格中的数据输入设置规则。这有助于确保数据完整性和准确性，这对于有效的数据挖掘非常重要。图 1.12 展示了数据验证的图标：

图 1.12 数据验证

■假设分析：假设分析允许您探索不同的场景及其潜在结果。这有助于预测、风险评估和决策。图 1.13 展示了假设分析的图标：

图 1.13 假设分析

■求解器：求解器是 Excel 的一个插件，允许您优化复杂模型并解决问题。它可以用于优化问题、线性规划等。请参阅图 1.14：

图 1.14 求解器

■Power Query：Power Query 是一个数据转换和清理工具，可用于从多个来源提取、转换和加载数据。它可以自动化数据清理任务并为分析准备数据。请参阅图 1.15：

图 1.15 Power Query

■文本分列：文本分列功能允许您根据分隔符或模式将列中的数据拆分到多个列中。这有助于清理和重构您的数据。图 1.16 展示了文本分列的图标。

图 1.16 文本分列

通过使用 Excel 中的这些数据挖掘功能，您可以快速轻松地发现数据中的模式和见解，这可以帮助您做出更好的决策并实现您的业务目标。

TAT 保存技巧

Excel 中的 TAT（周转时间）减少技巧可以参考旨在加快处理电子表格过程并减少执行某些任务所需时间的不同方法。以下是一些可以帮助提高 Excel 中 TAT 的技巧：

■使用键盘快捷键：键盘快捷键可以显著提高您在 Excel 中的速度。例如，您可以按 Ctrl+C 来复制，按 Ctrl+V 来粘贴，或者使用 F2 键来编辑单元格。

■使用公式和函数：Excel 提供了广泛的内置公式和函数，可以自动化许多任务并节省时间。例如，一些常用的函数包括 SUM（求和）、AVERAGE（平均值）、COUNT（计数）、IF 和 VLOOKUP。公式栏可以在图 1.17 中看到：

图 1.17 Excel 中的公式

■使用数据验证：数据验证是 Excel 中的一个强大功能，允许您控制可以输入到单元格中的数据类型。这可以通过减少纠正错误的需要来帮助防止错误并节省时间。此选项可以在以下图 1.18 中看到：

图 1.18 数据验证

■使用条件格式：条件格式允许您突出显示满足特定标准的单元格，这使得分析数据和识别趋势变得更容易。这可以通过减少手动搜索特定值或模式的需要来节省时间。此选项可以在图 1.19 中看到：

图 1.19 条件格式

■使用数据透视表：数据透视表是汇总和分析大型数据集的强大工具。它们可以帮助您快速识别数据中的趋势和模式，并使创建报告和图表变得更容易。请参阅图 1.20：

图 1.20 数据透视表图标

■使用 Excel 模板：Excel 模板可以通过提供预设计的带有内置公式、格式和布局的电子表格来节省时间。这可以通过消除从头创建电子表格的需要来节省时间。请参阅图 1.21：

图 1.21 Excel 模板

■使用自动填充功能：Excel 中的自动填充功能可以通过自动填充所选单元格范围内的值或公式来节省时间。要使用自动填充，请选择带有所需值或公式的单元格，然后将填充句柄拖动到您希望值或公式出现的位置。请参阅图 1.22：

图 1.22 自动填充功能

■使用 Excel 的排序和筛选功能：Excel 的排序和筛选功能可以通过快速组织和分析数据来节省时间。要排序数据，选择要排序的列，然后点击排序 A-Z 或排序 Z-A 按钮。要筛选数据，点击筛选按钮并选择用于筛选数据的条件，如图 1.23 Figure 1.23 所示：

图 1.23 Excel 中的排序和筛选

通过使用这些技术，您可以在使用 Excel 电子表格时节省时间并提高生产力。

结论

总结来说，Excel 2021 是一个功能强大的工具，提供了数据管理、分析和协作的一系列功能。凭借其用户友好的界面、定制选项和数据分析能力，Excel 2021 使用户能够高效工作、获得洞察力并做出明智的决策。它是一个多功能的工具，提高了生产力，对于处理数据的个人和企业来说是必不可少的。

练习

Excel 的条件格式化功能有什么用途？

a. 执行复杂计算

b. 分析数据趋势

c. 根据特定标准格式化单元格

d. 从其他软件导入和导出数据

Excel 中哪个函数可以在单元格范围中找到最高值？

a. 最大值

b. 求和

c. 平均值

d. 计数

章节摘要：2

单元格引用和范围

简介

在本章中，我们将探讨电子表格应用中单元格引用和范围的基本概念，使我们能够有效地操作和分析数据。通过理解单元格引用和范围的工作原理，我们可以简化我们的任务，执行计算，并在我们的电子表格工作中保持一致性。

结构

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

■使用不同类型的引用

■单元格引用类型

■命名范围

目标

学习本章后，读者将理解单元格引用的含义和用法，以及范围名称的用法。读者还将能够识别各种类型的单元格引用。

使用不同类型的引用

当我们从单元格复制引用到另一个单元格时，它会自动更新。例如，我们在单元格 C1 中的引用是 A1，我们将相同的引用复制到 D1。这将自动更新为 B1。有时，我们需要保持使用过的单元格引用的一部分不变。这可以通过使用不同类型的单元格引用来实现。

单元格引用类型

有三种类型的单元格引用：

■相对单元格引用

■绝对单元格引用

■混合单元格引用

参考图 2.1 Figure 2.1：

图 2.1 单元格引用类型

相对单元格引用

这是 Excel 中的默认单元格引用。在这种引用中，当你将相对单元格引用复制并粘贴到单元格中时，单元格会根据复制源单元格的更改自动更新。例如，假设你想计算人力资源会计（HRA），它是基本工资的 50%。为此，你需要在 HRA 列的第一个单元格中输入公式 =H2*50%，如图图 2.2 所示：

图 2.2 在 HRA 列的第一个单元格中输入公式 =H2*50%

要找到所有员工的人力资源会计（HRA），请将鼠标左键放在 HRA 列第一个单元格的右下角边界上，并向下拖动到最后一条记录，如图图 2.3 所示：

图 2.3 拖动公式

小贴士！选择要填充的单元格，然后按 Ctrl + D 填充范围，或者双击填充句柄。

绝对单元格引用

如果你想要冻结单元格引用，但又不想在复制公式时改变单元格引用，你必须使用绝对单元格引用。要使单元格引用绝对，需要在列名前放置美元符号（$）和引用的行号。

假设你想找到 1000, 2000, 3000 和 4000 的 10%，如图图 2.4 所示：

图 2.4 找到 1000, 2000, 3000 和 4000 的 10%

如果你将公式写成如图所示，当公式向右复制时，它会自动变为 C1B2, D1C2，依此类推。然而，这并不是正确的计算方法。我们需要冻结单元格引用 A2，使其在每次复制公式时保持不变。要将 A2 改为$A$2 以实现所需输出，如图图 2.5 所示：

图 2.5 将 A2 写成$A$2

所需的结果可以在图 2.6 中看到：

图 2.6 使用绝对单元格引用创建的公式结果

小贴士！首先，选择 B2 到 F2 的单元格，然后按 Ctrl + R。这将把 B2 的公式复制到 C2, D2, E2 和 F2。

混合单元格引用

有时你可能只想冻结单元格引用中的行或列。在图 2.7 中，我们需要计算 1000, 2000, 3000, 4000 等的 10%，20%，30%，40%和 50%。

图 2.7 计算 1000, 2000, 3000, 4000 和 5000 的 10%，20%，30%，40%和 50%

如果你将公式向右拖动，它会变为 C2B3, D2C3，依此类推。一旦向下拖动，它会变为 B3A4, B4A5，依此类推。然而，这些都不是正确的公式。请参阅图 2.8。

图 2.8 引用单元格中的错误公式

如果我们仔细观察图 2.8，我们可以看到我们需要冻结 B2 的行号（因为它对所有向右和向下的公式都是通用的）和 A3 的列名（因为它对所有向右和向下的公式都是通用的）。当复制时，结果公式将如图图 2.9 所示。

图 2.9 结果公式

答案如图图 2.10 所示。行号或列号之一被冻结的引用称为混合单元格引用。

图 2.10 结果值

小贴士！将光标靠近单元格引用并按 F4 键，在不同的单元格引用之间切换。

命名范围

当您编写公式（也称为函数）时，您需要选择一个单元格范围。如果范围很大，这可能会很耗时。Excel 为我们提供了一种给范围命名的方法。例如，我们可以用 Sum (Basic)代替 Sum (H2:H101)。为此，我们首先需要将 H2:H10 命名为 Basic。以下是命名范围的步骤。

创建命名范围

要命名一个范围，我们可以使用以下程序之一：

选择范围（例如，H2:H101）并在名称框中输入名称（例如，Sal），如图图 2.11 所示：

图 2.11 创建命名范围

如果您想给包含在单元格中的值命名，您可以选中范围及其名称。在公式选项卡中点击从选择创建，并选择一个选项。
点击确定。
您也可以通过在公式选项卡中点击定义名称来创建一个命名范围。
在名称框中输入范围的名称。然后点击引用到框并选择您希望命名的范围。
点击确定。
现在，您可以在工作簿的任何地方使用给定的名称而不是范围。参见图图 2.12：

图 2.12 使用命名范围代替单元格引用

编辑命名范围

有时，重命名或编辑命名范围变得至关重要。可以通过以下步骤完成：

在公式选项卡中，点击名称管理器。
打开一个命名管理器对话框，如图图 2.13 所示：

图 2.13 命名管理器对话框

选择要编辑的命名范围并点击编辑按钮来编辑命名范围。
出现一个编辑名称对话框，如图图 2.14 所示：

图 2.14 编辑名称对话框

输入一个新名称或重新定义范围名称。

删除命名范围

要删除一个范围，请按照以下步骤操作：

从名称管理器列表中选择范围。
点击删除。
命名范围将被删除。

小贴士！按 Ctrl + F3 键打开命名管理器对话框。

结论

总之，掌握单元格引用和范围使用户能够有效地操作数据并简化电子表格应用程序中的操作，从而提高处理大量数据集的效率和准确性。

练习

在表 2.1 中匹配正确的相对、绝对和混合引用：

表 2.1 匹配正确的选项

A$1	相对引用
$A$1	绝对引用
$A1	混合引用

在 Excel 训练文件夹中，打开名为 Advanced Excel Assignment.xlsx 的文件。打开“混合单元格”工作表，以这种方式计算不同地区的每个产品的销售额百分比，即当你将东部销售额的单元格公式复制并粘贴到每个地区列中时，它会自动计算该地区的销售额。

第三章

与公式和函数一起工作

简介

在本章中，我们将探讨 Microsoft Excel 中公式和函数的使用。公式是执行值计算的方程，而函数是简化复杂任务的预定义公式。我们将涵盖使用工作表中的公式、数组公式、使用函数、IF 函数及其变体以及查找函数等主题。

结构

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

■在工作表中使用公式

■使用函数

■查找函数

■使 V-lookup 动态化

■索引

目标

在学习完本章后，读者将了解公式的使用和函数，能够识别不同类型的函数，并了解 IF 和其他逻辑函数的工作方式。

在工作表中使用公式

公式是执行值计算的方程。公式以等号（=）开头。它包含至少两个操作数和一个运算符。例如，以下公式将两个乘以三，并将五加到结果中。

=5+2*3

公式中的操作数可以是函数、引用或常数。运算符可以是任何算术或逻辑运算符。

注意：在 Excel 中，当涉及多个运算符时，将遵循 BODMAS 规则来解决公式。BODMAS 代表括号、幂或根的顺序、除法、乘法、加法和减法。根据此规则，具有多个运算的数学表达式应从左到右解决。

数组公式

在图 3.1 中，有五种产品，我们知道它们的数量和价格。我们需要找到总销售额，这是将所有产品的数量和价格加在一起的结果。在正常情况下，我们会分别计算每个产品的金额并将它们相加以得到答案。为了使事情更简单，我们也可以使用数组公式。选择 B8，输入 =sum (A2:A6B2:B6) 并按 Ctrl + Shift + Enter 键填充所选单元格中的公式 {=sum (A1:A3B1:B3)}，如图图 3.2 所示。这将在单元格 B8 中计算所有产品的数量乘以价格。

注意：公式周围的括号({})表示它应用于数组。

图 3.1 展示了五种产品的数量和价格。

图 3.1 五种产品的数量和价格

图 3.2 展示了所有产品的数量*价格：

图 3.2 所有产品的数量*价格

使用函数

对单元格范围内的每个值进行计算可能很复杂且耗时。例如，如果您有一个由 20 个单元格组成的范围，一个添加这些值的公式将会非常长。Excel 函数简化了复杂任务。

函数是一个预定义的公式，它对数字或文本字符串执行特定的计算或其他操作，并返回一个值。您可以指定函数执行计算的值。函数的语法以函数名开始，后跟一个开括号，函数的参数用逗号分隔，然后是一个闭括号。

如果函数以公式开始，请在函数名之前输入等号(=)。当您创建包含函数的公式时，公式栏将提供帮助，如图 3.3 所示。

图 3.3 插入函数

注意：从空单元格中，您可以点击公式栏附近的 fx 符号，以查看 Excel 中所有可用的函数。

函数的语法是：

=函数名（参数 1，参数 2，……）

示例：

=SUM (A10, B5: B10, 50, 37)

您不需要记住所有可用的函数及其每个函数所需的参数。您可以使用求和符号(Σ)进行求和，或者点击下拉菜单以获取更多函数，如最大值、最小值等。Excel 会提示您输入必需和可选的参数。

注意：您可以使用 Alt + =键组合在您的工作表中获取求和函数。

IF 函数

在第二章“单元格引用和范围”中，我们学习了诸如 HRA 和 DA 等收入项目的计算。我们看到的公式在整个数据库中都是相同的。根据某些条件，我们需要决定要应用的公式。例如，激励可能根据部门来计算。这就是条件函数如“IF”发挥作用的地方。

IF 函数可用于评估条件。根据条件是真是假，IF 函数将返回值。IF 函数的语法是：

If(logical_test, [Value_if_true], [Value_if_false])

第一个参数是需要函数评估的条件。第二个参数是在条件为真时返回的值，第三个参数是在条件为假时返回的值。第二个和第三个参数是可选的。

示例：

假设您想根据员工的职位计算 HRA。如果职位是经理，HRA 要么是 1000，要么是 500。在这种情况下，函数代码如下：

=if (C2="Manager", 1000, 500)

参考图 3.4。

图 3.4 IF 函数

如图 3.4 所示，上述函数计算经理的 HRA 为 1000，其他人为 500。

Nested IF

嵌套 IF 函数用于在第一个 IF 函数内部放置第二个 IF 函数，以便测试额外的条件。

嵌套 IF 函数的语法是：

If (logical_test, [Value_if_true], If (logical_test, [Value_if_true], [Value_if_false]))

示例：

您可以使用嵌套 IF 函数来评估复杂条件。例如，如果工资小于 5000，则税率为 5%。如果工资在 5000 到 10000 之间，则税率为 10% 或 15%。由于我们已经给工资列命名为 sal，我们也可以使用 sal 而不是 h2 =if (sal<5000, salary.05, if (sal<10000, salary.10, sal*.15))。参考图 3.5：

图 3.5 使用嵌套 IF 函数

假设您想根据名为平均分的名称给数字分配字母等级，如表 3.1 所示：

表 3.1：分配字母等级

如果平均分大于	返回
大于 89	A
从 80 到 89	B
从 70 到 79	C
从 60 到 69	D
小于 60	F

您可以使用以下嵌套 IF 函数：

IF (AverageScore>89,"A", IF (AverageScore>79,"B", IF (AverageScore>69,"C", IF (AverageScore>59,"D","F"))))

注意：您可以在单个公式中嵌套最多 64 级 IF 函数。

IF With AND

在 Excel 中，OR 是一个逻辑函数，如果任何参数返回假，则返回假。

语法：

AND (logical1, logical2...)

如果存在一个场景，其中有两个条件，它们的组合真值将决定 IF 函数的输出，我们可以使用 AND 与 IF。

语法：

If (and (Condition1, condition2….), True, False)

示例：

如果我们需要给在北方销售部门的每位员工发放基本工资的 10% 作为激励，我们会使用以下公式：

=IF (AND (Department=”sales”, Region=”north”), 10%*Basic Salary, 0)

IF With OR

OR 是 Excel 中的一个逻辑函数，如果任何参数返回假，则返回 False。

语法：

OR (logical1, logical2...)

如果存在一个场景，其中有两个条件，其中任何一个条件为假，并且 IF 应返回假参数中的值，我们可以使用 OR 与 IF。

使用 OR 与 IF 的语法

If (OR (Condition1, condition2….), True, False)

示例：

如果员工在销售、市场或人力资源部，则 HRA 为基本工资的 50%。否则，为基本工资的 30%。

If (or (Department=”Mktg”, Department =”Sales”, Department=”Hrd”), Basic salary.5, Basic salary.3)

IF With NOT

这是一个用于否定参数的逻辑函数。

语法：

NOT (逻辑)

如果有一个条件，当不满足时，需要我们应用公式，我们可以使用 NOT 与 IF 结合。

使用 NOT 与 IF 结合的语法

If (NOT (Condition), True, False)

示例：

如果我们需要给除了市场营销部门外的所有人提供激励，我们可以使用以下公式：

IF (NOT (Department=”MT”), 10%*salary, 0)

注意：AND/OR 函数可以传递的最大条件数为 255 个，而 NOT 只能传递一个条件。

我们也可以在 IF 语句中使用多个 NOT 语句。

示例：

如果您需要给除了销售和行政部门的人之外的所有人提供激励，您可以使用以下函数：

If (and (not (department=”Sales”), not (department=”admin”)), 10%* salary, 0)

查找函数

有时我们需要使用查找值在数据库中搜索一个值。例如，给定员工 ID，我们如何从其他工作表或其他文件中查找激励值？在这种情况下，根据源数据库，我们可能使用以下查找函数之一：

■VLOOKUP（如果数据库是纵向的）。参考图 3.6：

■HLOOKUP（如果数据库是横向的）。参考图 3.7：

图 3.6 VLOOKUP

图 3.7 HLOOKUP

VLOOKUP

如果我们需要根据公共字段从其他文件或工作表获取列值，可以使用 VLOOKUP。VLOOKUP 是一个函数，它在一个给定数据库（table_array）的最左侧列中搜索一个值（查找值），并从您指定的列返回同一行的值。

语法：

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

您可以通过使用内置函数参数对话框来编写此函数。点击“公式”选项卡，在查找与引用类别中搜索 VLOOKUP。您将获得一个函数参数对话框，如图图 3.8 所示：

图 3.8 VLOOKUP 函数参数

这里可以选择的不同值如下：

■Lookup_value：在表的第一个列中要查找的值。它是您正在寻找的值。Lookup_value 可以是值、引用或文本字符串。

■Table_array：查找数据的信息表。它是源数据库。使用对范围或范围名称的引用。

■Col_index_num：在 table_array 中必须返回匹配值的列号。

■范围查找：指定是否希望 VLOOKUP 查找精确匹配或近似匹配的逻辑值。如果范围查找设置为 FALSE 或 0，VLOOKUP 将查找精确匹配。如果未找到精确匹配，则返回错误值#N/A。如果设置为 TRUE 或非零，则查找小于查找值的最近值。

HLOOKUP

HLOOKUP 函数在表的顶部行中搜索一个值，然后从您指定的行返回同一列的值。

语法：

HLOOKUP（查找值，表格数组，行索引号，范围查找）

HLOOKUP 与 VLOOKUP 的工作方式相同。然而，在这种情况下，我们需要指定行索引号，而不是列索引号。

注意：你也可以通过以下方式获取函数参数框。输入=VLOOKUP（或=HLOOKUP（视情况而定）并按 Ctrl + A。

VLOOKUP 与范围 0（False）的示例：

假设你想要根据激励表添加工资表中的激励，该表的范围在激励工作表的 A1:B12。按照以下步骤操作：

选择你想要结果所在的单元格。
点击插入函数。从查找和引用类别中选择 VLOOKUP()函数。
查找值：选择 A2（员工代码）。
表格数组：选择激励表，并选择从$A$1:$B$12（员工代码和激励列）的范围。
列索引：输入 2（激励表中的第二列是激励列）。
范围查找：输入 False。（这意味着我们正在从表中搜索查找值的精确匹配）。

要移除#NA（不可用）错误，你可以使用 iferror 函数。iferror 的语法如下：

= iferror (vlookup…,"")

VLOOKUP 与范围非零（True）的示例

假设你想要根据工资档次添加激励。在这种情况下，除了使用 IF 条件外，你还可以使用具有 True 范围的 VLOOKUP。在这种情况下，我们会创建一个表格，例如表 3.2。在表格数组中，选择此表格，并在范围查找字段中输入“True”而不是“False”。

表 3.2 示例示例表

0	2%
5000	5%
10000	10%
15000	15%

注意：在这种情况下，表格将按第一列的升序排序。

示例：

在名为“emp_inf sheet”的 Excel 高级作业文件中，我们需要根据员工 ID 检索员工信息。为此，我们可以使用 VLOOKUP，如图 3.9 所示。

图 3.9 VLOOKUP 与范围非零的示例

要查找其他详细信息，你可以使用相同的公式并相应地更改列索引号。

使 VLOOKUP 动态化

当我们有一个动态数据库，数据库中经常添加新列时，当前列的位置也可能发生变化。然而，VLOOKUP 的列索引号不会随着数据库的增长而自动更新。这就是为什么我们需要使 VLOOKUP 动态化的原因：为了获取列索引号。为此，我们可以使用以下函数之一来动态检索列索引号：

■列

■匹配

在 VLOOKUP 中使用列函数

要使 VLOOKUP 动态化，我们可以使用列标题作为指示器，它会动态地获取存在所需值的特定列的索引号。在 VLOOKUP 中使用列函数的语法如下：

= vlookup (查找值，表格数组，COLUMN (参考)，范围查找）

列函数的引用参数将包含原始数据库中列标题的单元格引用。

示例：

在前面的 VLOOKUP 示例中，如果我们需要动态地找到列索引号，我们可以使用列函数，如图 3.10 所示。

图 3.10 在 VLOOKUP 中使用列函数

B1 是“Salary”工作表中“First Name”列标题的引用。

在 VLOOKUP 中使用匹配函数

如前所述，我们需要访问原始数据库，或者至少了解列的当前位置。然而，这种信息并不总是对我们有用。在这种情况下，我们需要使用一个可以按名称检索列标题位置的函数。

匹配函数执行相同的操作。匹配函数返回字符串在范围内的位置。

匹配函数的语法如下：

MATCH(lookup_value, lookup_array, [match_type])

可选的选项如下：

■查找值：这是我们正在寻找的字符串。它可以是字符串（例如，“Salary”）或存储字符串的单元格引用。

■查找数组：这是我们需要知道查找值位置的数组。

■匹配类型：这是一个可选参数，用于指定所需的匹配类型。我们使用 0 进行精确匹配，1 进行小于匹配，-1 进行大于匹配。

示例：

如果我们需要找出字符串“salary”在薪酬表第一行的位置，我们将编写：

=match("salary", salary!$1:$1, 0)

我们可以使用匹配函数而不是列索引号来动态获取列索引号。

在 VLOOKUP 中使用匹配函数的语法如下：

示例：

=vlookup(lookup_value, table_array, match(label, firstrow of source-database, 0), Range_lookup)

在 emp_inf 示例中，如果我们需要通过列标题使 VLOOKUP 更加动态，我们可以使用匹配函数与 VLOOKUP 一起使用，如图 3.11 所示。在这里，匹配函数查找薪酬数据库标题中每个字段的标签，并动态返回列的位置。

图 3.11 VLOOKUP 中的匹配函数

索引

有时，我们需要根据行号和列号查找数据。索引函数帮助我们完成这项任务。

INDEX(array, row_number, [column_number])

index 函数的语法如下：

如您所见，有两种方式可以使用索引函数。第一种语法用于在单个数据库中查找数据，第二种语法用于涉及多个数据库的情况。

示例：

假设我们需要找到数据库中行号为 3 和列号为 4 的数据。在这种情况下，我们可以使用以下函数：

=index(database, 3, 4)

索引-匹配

如我们之前所研究的，VLOOKUP 基于数据库第一列的值来查找数据。如果我们有一个数据库，我们的查找值位于中间，并且我们需要向左搜索，然而，在使用 VLOOKUP 之前，我们必须将列移动到最左侧。当与 match 一起使用时，index 函数帮助我们即使在查找值不在最左侧列中时也能搜索数据。

INDEX (数组, [MATCH (查找值, 查找数组, [匹配类型])], [Match (查找值, 查找数组, [匹配类型])])

Index-Match 的语法如下：

在这里，您可以使用 match 函数进行行号、列号或两者的匹配。

示例：

假设我们从图 3.12 中给出的数据中需要找到给定年份和季度的总销售额。然后我们可以使用以下函数：

=INDEX (数据库, MATCH (季度 3, 列标题, 0), MATCH (年份, 年份列, 0))

图 3.12 Index-Match 函数

在图 3.13 中，empcode 是第三列。如果我们需要根据 empcode 查找 DA 或工资，我们通常会复制并粘贴该列到左侧并使用 VLOOKUP。相反，我们可以使用图 3.13 中给出的 index match：

图 3.13 通过 Index Match 使用 empcode 查找 DA 或工资

结论

总之，在 Microsoft Excel 中使用公式和函数对于执行计算、数据分析以及自动化任务至关重要。公式允许用户组合值、单元格引用和运算符以执行数学计算，而函数为常见任务提供预定义的公式。通过有效地利用公式和函数，用户可以节省时间，减少错误，并在 Excel 中执行复杂的数据分析。理解和掌握这些工具对于任何在 Excel 中处理数据和电子表格的人来说都是至关重要的。

练习

从高级 Excel 作业工作簿中复制工资工作表。计算以下激励方案：

a. 激励措施 1：销售部门的每位员工将获得其工资的 10%作为激励；其他所有员工获得 0%。

b. 激励措施 2：销售或市场部门的每位员工将获得其工资的 5%作为激励；其他所有员工获得 2%。

使用 VLOOKUP 列计算 Q.1 中的激励方案。
在工资列之后创建一个名为“报告经理”的列，并根据员工的员工编号将经理的员工代码应用于员工，如下所示，使用 VLOOKUP。

第四章

数据验证

简介

有时我们希望防止用户在单元格中输入非文本值。换句话说，您可以说您希望限制用户在单元格中输入某种类型的值。数据验证为您完成这项工作。

数据验证是一个过程，它防止用户为单个单元格或单元格范围输入无效数据。借助数据验证，你可以限制数据输入到特定数据类型，如整数、分数（小数）或文本。你还可以设置有效输入的限制。

结构

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

■跟踪 precedent

■跟踪 dependent

■设置数据验证规则

■数据验证方法

目标

在学习本章后，学生将能够描述如何限制任何单元格或任何工作表中的数据输入，以及识别各种数据验证技术。

跟踪 precedent

跟踪 precedent 和跟踪 dependent 是 Excel 中的两个功能，允许你可视化并理解电子表格中单元格之间的关系。以下是每个功能的简要概述：

跟踪 precedent 允许你看到哪些单元格被选定的单元格引用。这对于理解数据如何通过你的电子表格流动以及识别任何潜在的错误或问题很有用。要使用跟踪 precedent，请选择要跟踪的单元格，然后在功能区公式审核部分单击跟踪 precedent 按钮。Excel 将绘制指向被选定单元格引用的单元格的箭头。

例如，假设你有一个电子表格，它根据销售单位和每单位价格计算公司的总收入。总收入的公式仅仅是销售单位和每单位价格的乘积。在这个例子中，单元格 C2 包含总收入的公式，即“=A2*B2”。单元格 A2 包含销售单位数，单元格 B2 包含每单位价格。

请参阅图 4.1：

图 4.1 跟踪 precedent 示例

要使用跟踪 precedent 来查看哪些单元格被单元格 C2 引用，请按照以下步骤操作：

选择单元格 C2，如图所示：

图 4.2 应用总收入公式

在功能区公式审核部分单击跟踪 precedent 按钮，如图所示：

图 4.3 公式选项卡

Excel 将绘制指向单元格 A2 和 B2 的箭头，指示它们是单元格 C2 中公式的引用单元格，如图所示：

图 4.4 跟踪 precedent

这表明单元格 C2 中的公式依赖于单元格 A2 和 B2 中的值。如果你更改这些单元格中的任何一个值，单元格 C2 中的值将相应地更改。

使用跟踪 precedent 有助于你了解数据如何通过你的电子表格流动，并且对于识别公式中的潜在错误或问题很有用。

跟踪 dependent

“追踪依赖项”允许您查看哪些单元格依赖于所选单元格。这对于理解更改特定单元格将如何影响电子表格的其他部分非常有用。要使用“追踪依赖项”，请选择您想要追踪的单元格，然后单击功能区公式审核部分中的“追踪依赖项”按钮。Excel 将绘制指向依赖于所选单元格的单元格的箭头。

“追踪前导项”和“追踪依赖项”都可以帮助您理解电子表格的结构，并解决可能出现的任何错误或问题。通过使用这些功能，您可以更轻松地导航和分析复杂的电子表格，并基于您的数据做出更明智的决策。

如何使用“追踪依赖项”

在此示例中，您有一个电子表格，它根据本金、利率和期限计算贷款的月付款。月付款的公式基于本金、利率和期限，分别存储在单元格 A1、A2 和 A3 中。单元格 A4 包含月付款的公式，该公式使用 PMT 函数计算。

要使用“追踪依赖项”来查看哪些单元格依赖于单元格 A4：

选择单元格 A4。
在功能区公式审核部分单击“追踪依赖项”按钮。
Excel 将绘制指向任何依赖于单元格 A4 的单元格的箭头。
在此示例中，单元格 B4、C4 和 D4 依赖于单元格 A4，因为它们包含月付款的分解，包括本金、利息以及任何额外的费用或收费。

参考图 4.5：

图 4.5 追踪依赖项

通过使用“追踪依赖项”，您可以查看哪些单元格会受到单元格 A4 中值变化的影響。例如，如果您要增加利率或更改贷款期限，您可以使用“追踪依赖项”来查看这将如何影响月付款及其分解。

使用“追踪依赖项”可以帮助您理解电子表格的结构，并基于您的数据做出更明智的决策。

设置数据验证规则

按照以下步骤创建数据验证的规则集：

选择您想要创建验证规则的单元格。
在“数据”选项卡上的“数据工具”组中，单击“数据验证”以打开数据验证对话框（如图 4.1 所示）。
激活“设置”选项卡。
从“允许”列表中选择数据验证选项。
从“数据”列表中选择您想要的运算符。
完成剩余的输入。
如有必要，在“输入消息”选项卡中输入输入消息。
如有必要，在“错误警报”选项卡中输入错误消息。
单击“确定”以设置验证规则。
关闭对话框。

参考图 4.6：

图 4.6 数据验证

数据验证方法

让我们看看数据验证的一种方法：创建列表。

创建列表

列表是一种有效的数据验证形式，用户可以从内置单元格的下拉列表中选择一个选项（图 4.7）。数据源可以是用户手动编写，或从同一工作表中选择。

创建列表的以下步骤：

选择一个空白单元格。
选择“数据”选项卡。
从数据工具组中选择“数据验证”。
选择“列表”。
在“源”中，选择包含值的单元格，或用逗号输入数据。

参考图 4.7 Figure 4.7：

图 4.7 创建列表

注意：如果源来自不同的工作表，为所有值创建一个命名范围，并在数据验证的源字段中使用该名称。

允许在范围内的数字：

在“允许”框中，点击“整数”或“十进制”。
在“数据”框中，选择您想要的限制类型。例如，要设置上下限，请选择“介于”。
输入允许的最小值、最大值或特定值。

允许在时间范围内输入日期或时间：

在“允许”框中，选择日期或时间。
在“数据”框中，选择您想要的限制类型。例如，要允许在某个日期之后的日期，请选择“大于”。
输入允许的开始、结束或特定日期或时间。

允许指定长度的文本：

在“允许”框中，点击“文本长度”。
在“数据”框中，点击您想要的限制类型。例如，要允许达到一定数量的字符，请点击“小于或等于”。
输入文本的最小值、最大值或特定长度。

根据另一个单元格的内容计算允许的内容：

在“允许”框中，选择您想要的数据类型。
在“数据”框中，选择您想要的运算符。
在数据框下面的框或框中，点击您想要用来指定允许内容的单元格。

例如，要仅在结果不会超过预算的情况下允许账户输入，请点击“允许”下的“十进制”，选择“小于或等于”作为数据，并在“最大值”框中点击包含预算金额的单元格。

使用公式计算允许的内容：

在“允许”框中，点击“自定义”。
在“公式”框中，输入计算逻辑值的公式（有效输入为 TRUE，无效输入为 FALSE）。例如，如果要仅在部门为销售且地区为西部时提供激励，您可以使用以下自定义公式：=and(d2="sales",e2="west")。

要在点击单元格时显示可选的输入消息，请点击“输入消息”选项卡。确保选中“选择单元格时显示输入消息”复选框，并填写消息的标题和文本。

指定当输入无效数据时 Excel 如何响应：

点击“错误警告”选项卡，并确保选中“输入无效数据后显示错误警告”复选框。
为“样式”框选择以下选项之一：

•要显示不阻止无效数据输入的信息消息，请选择“信息”。

•要显示不阻止输入无效数据的警告消息，请选择“警告”。

•为了防止输入无效数据，请选择“停止”。

填写消息的标题和文本（最多 225 个字符）。

如果您没有输入标题或文本，标题将默认为 MS Excel，消息将默认为“您输入的值无效。用户已限制可以输入此单元格的值。”

结论

在本章中，我们学习了 Microsoft Excel 中的数据验证。数据验证允许我们限制可以输入到单元格中的数据类型，确保数据准确性和一致性。我们探讨了两种数据验证方法：追踪 precedent 和追踪 dependent。这些功能帮助我们了解单元格之间的关系，并识别公式中的潜在错误。

我们还讨论了如何使用数据验证对话框设置数据验证规则。这允许我们定义允许数据的特定标准，例如整数、小数、日期、时间或指定长度的文本。我们甚至可以创建自定义公式，根据其他单元格的内容计算允许的数据。

此外，我们还研究了创建数据验证列表的方法，允许用户从下拉列表中选择选项。这有助于保持数据一致性并简化数据输入。

练习

打开高级 Excel 作业工作簿。在名为“验证”的表格中执行以下数据验证。

a. 不允许在“emp_code”中存在重复项。

b. “emp name”中只允许文本。

c. 年龄应仅包含数字数据。

d. 薪资应在 5000 至 50000 之间。

e. 加入日期应早于当前日期。

在“emp_inf”表格中，在 B3 单元格创建包含所有员工代码的下拉列表。

第五章

保护

简介

在当今数字时代，保护敏感信息并确保数据完整性至关重要。Microsoft Excel 提供了一系列功能来保护您的工作表和工作簿，防止未经授权的更改并维护数据机密性。在本章中，我们将探讨 Excel 中的各种保护方法，让您能够控制访问并保护您宝贵信息的完整性。

结构

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

■员工信息系统

■使用密码保护工作表

■保护工作簿

■保护工作表的一部分

■使用密码保护文件

■案例研究

目标

学习本章后，您将了解如何防止对工作表进行未经授权的更改，以及如何使用密码保护工作簿。

员工信息系统

在“emp_inf”示例中，如图图 5.1 所示，如果我们希望将工作表用作公共模板，我们需要防止对 VLOOKUP 公式的未经授权访问。我们需要限制对 B3 单元格的数据输入。为了实现这些目标，我们可以使用保护功能。

图 5.1 员工信息系统

在 Excel 中，有三种保护级别，如图 5.2 所示。

图 5.2 保护级别

使用密码保护工作表

按以下步骤通过密码保护工作表：

激活“审阅”选项卡。
在“更改”选项卡组中，点击“保护工作表”以打开“保护工作表”对话框。
选择您想要的选项。
输入密码。
点击“确定”。
出现“确认密码”对话框。
在“重新输入密码以继续”框中，输入相同的密码以确认。
点击“确定”以关闭密码确认框和对话框。

保护工作簿

工作簿级别的保护可以通过两种方式完成，如图 5.3 所示。

■保护工作簿结构，防止如工作表移动、删除、插入、隐藏、取消隐藏或重命名等更改。

■保护工作簿窗口并确保每次打开窗口时大小和位置相同。

图 5.3 保护工作簿

执行以下步骤以保护工作簿：

激活“审阅”选项卡。
在“更改”选项卡组中，点击“保护工作簿”。
在出现的“保护工作簿”对话框中，根据需要选择一个或两个选项（结构或窗口）。
为了防止他人移除工作簿保护，您可以设置密码。
在“保护工作簿”对话框中指定选项后，点击“确定”。参见图 5.4：

图 5.4 保护结构和窗口

保护工作表的一部分

当您保护整个工作表时，工作表中的所有单元格默认都会被锁定。这意味着用户无法更改工作表中的任何单元格。要允许用户更改特定单元格，您必须在保护工作表之前手动解锁单元格。这将允许用户仅更改解锁单元格中的数据。您可以在保护工作表之前隐藏公式，这样在激活工作表级别的保护后，用户就看不到公式了。

如果只想保护工作表的一部分，请执行以下步骤：

选择您希望用户能够修改的单元格范围。
右键单击并选择“设置单元格格式”以打开“设置单元格格式”对话框。
激活“保护”选项卡。
清除“锁定”复选框。
点击“确定”。

如果您想隐藏公式，请遵循第 6 步到第 10 步，或者直接进行第 11 步：

选择您想要隐藏公式以便用户看不到的单元格范围。
右键单击并选择“设置单元格格式”以打开“设置单元格格式”对话框。
激活“保护”选项卡。
选择“隐藏”复选框和“锁定”复选框。
点击“确定”。

继续进行第 11 步以通过密码保护工作表：

激活“审阅”选项卡。
在“更改”选项卡组中，点击“保护工作表”以打开“保护工作表”对话框。
输入密码。
点击“确定”。
将出现确认密码对话框。
在“重新输入密码以继续”框中，键入相同的密码。
点击“确定”以关闭密码确认框和对话框。

文件密码保护

您可能希望用密码保存文件，这样任何用户在查看或修改文件之前都会被要求输入密码。为此，请按照以下步骤操作：

点击“文件”按钮。
选择“另存为”。
在“另存为”对话框中，单击“工具”。
然后单击“常规选项”，如图图 5.5 所示。

图 5.5 使用密码保护文件

根据需要设置打开或修改密码。
保存文件。

注意：要将 Excel 工作表作为模板使用，请将文件保存为.xlt 扩展名。

结论

总之，在 Microsoft Excel 中保护数据安全至关重要，以确保工作表和工作簿的安全性和完整性。通过利用如工作表、工作簿和文件的密码保护等特性，您可以限制未经授权的访问并防止对数据的未授权更改。此外，保护工作表中的特定部分允许您控制用户可以修改的单元格，这将使维护数据一致性变得更加容易。

练习

在名为“Practice Assignment Product-Invoice”的文件中打开练习文件夹。通过以下步骤准备发票模板：

在 M/s 中创建一个包含所有客户名称的下拉列表。
应根据“客户”工作表中的客户名称查找地址。
产品列应包含“产品”工作表中列出的所有产品列表。
应根据相邻的产品列表中选定的产品查找费率。
第一项“序号”应由供应商输入，其余项目只有在从产品列表中选择产品时才会显示。参见图 5.6。

图 5.6 产品发票

金额应计算为数量*费率。
总金额是所有金额的总和。增值税是总金额的 14%。
如果总金额大于 15000，则折扣应计算为总金额的 10%。
净金额应计算为总金额+增值税-折扣。
将文件保存为模板。

注意：模板中不应有任何可见的错误。

第六章

数据库排序

简介

在数据库中排序数据是根据特定标准（如字母或数字值）对项目进行排列的过程。它使数据组织、比较和分析变得容易，从而实现高效的数据管理和明智的决策。本章探讨了不同的排序技术，包括简单排序、多级排序和自定义排序，为您提供有效安排和分析数据库中数据的知识。

结构

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

■排序的定义

•简单排序

• 多级排序

• 自定义排序

目标

在学习本章后，读者应该能够定义排序并识别各种排序技术。

排序的定义

排序是指任何系统性地安排项目的流程，即按照某些标准对项目进行排序。例如，按升序或降序排序数据。

简单排序

要对一个列执行简单排序，请按照以下步骤操作：

选择您想要排序的列中的任何单元格。
激活“数据”功能区选项卡。
在“排序和筛选”功能区组中，单击“升序排序”或“降序排序”按钮。如图 6.1 所示，这将排序整个数据库。

图 6.1 简单排序

多级排序

有时，您可能希望按多列对数据进行排序。例如，您想按地区和部门对员工信息进行排序。这可以通过多级排序来完成。

要根据两个或多个列对列表进行排序：

选择列表中的任何单元格。
激活“数据”功能区选项卡。
在“排序和筛选”功能区组中，单击“排序”以打开排序对话框。
从“排序依据”列表中，选择您想要按其排序的列表列标题，并选择一个排序顺序。
所有记录将根据您选择的列和排序顺序进行排序。
从“然后按”列表中，选择您想要按其排序的下一个列。
如有必要，通过单击“添加级别”来添加更多“然后按”字段。
当所有“然后按”字段都填写完毕后，单击“确定”，如图 6.2 所示。

图 6.2 多级排序

注意：在点击“确定”按钮之前，请确保至少选择了一个“然后按”字段。

自定义排序

在自定义排序中，列表将按照您指定的顺序对数据进行排序。如果我们按地区排序，它将按升序或降序排序，但想象一下，如果我们想按自定义顺序排序我们的数据。

例如，我们想按北、南、东、西排序。为此，我们需要执行自定义排序，如下述步骤所述：

选择列表中的任何单元格。
激活“数据”选项卡。
在“排序和筛选”组中，单击“排序”以打开排序对话框。
从“排序依据”列表中，选择您想要按其排序的列表列标题。
从“排序顺序”中选择“自定义列表”。
这将打开“自定义列表”对话框。
输入您希望排序的序列。
单击“添加”按钮以将列表添加到自定义排序中。
单击“确定”。

结论

总之，在数据库中对数据进行排序对于组织信息和促进高效分析至关重要。通过应用各种排序技术，如简单排序、多级排序和自定义排序，我们可以根据特定标准排列数据，并提高我们数据库的可使用性和功能性。排序使我们能够更有效地导航和比较数据，从而获得更好的见解和明智的决策。

练习

参考表 6.1Table 6.1 并回答以下问题。

表 6.1 数据库表

根据销售收入列按降序对销售数据库进行排序。
通过首先按产品类别按字母顺序排序，然后按销售数量按降序排序，对销售数据库进行多级排序。
通过为产品名称创建自定义列表并按该列表排序来自定义销售数据库的排序。
根据库存数量列按升序对库存数据库进行排序。
通过首先按产品类别按字母顺序排序，然后按重新订购级别按升序排序，对库存数据库进行多级排序。

第七章

过滤数据库

简介

在本章中，我们将探讨在 Excel 中过滤数据库的主题。过滤功能使我们能够仅显示满足特定标准的信息行，这使得分析大型数据集变得更加容易。我们将了解不同类型的过滤器，包括自动过滤器功能、数字、文本和日期过滤器，以及 Excel 的高级过滤功能。此外，我们还将发现如何在一个列表中过滤唯一记录。到本章结束时，读者将清楚地了解如何有效地使用 Excel 中的过滤器，根据特定标准提取和操作数据。

结构

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

■过滤器

•自动过滤器

•数字、文本或日期过滤器

•使用高级过滤器过滤列表

■过滤唯一记录

目标

在学习本章后，读者将能够理解各种类型的过滤器，并了解如何在他们的工作表中使用它们。

过滤器

有时，您需要仅显示满足特定标准的信息行。为了帮助您做到这一点，您可以使用过滤器。让我们更深入地讨论一下过滤器。

自动过滤器

对于常用标准，Excel 提供了自动过滤器功能。以下是它是如何工作的：

选择列表中的任何单元格。
激活“数据”选项卡。
在“排序和筛选”组中，点击“过滤器”以在每个列标题旁边显示自动过滤器箭头。
从列表中选择您想要过滤的列。
选择标准。
如图 7.1 所示，点击“确定”。

图 7.1 过滤器

要清除筛选并显示整个列表，请再次点击筛选。您可以通过使用高级筛选功能根据更复杂的条件筛选列表。例如，您可以使用显示所有工资在 7000 到 12000 之间的员工的记录。

Excel 提供了两个指定复杂筛选条件的工具：

■数字、文本或日期筛选

■高级筛选

数字、文本或日期过滤器

一旦您对数据进行筛选，您在每个字段中也会获得数字、文本或日期筛选选项，具体取决于该列中的数据类型。这些可以用于字段特定筛选，例如对于文本字段，“以...开头”和“包含”，对于数字字段，“大于”、“小于”和“介于”，对于日期字段，“之前”和“之后”。每个筛选字段都有一个自定义筛选选项，您可以在其中指定除已提供之外的其他公式或选项。参考图 7.2 图 7.2。

图 7.2 数字过滤器

参考图 7.3 图 7.3：

图 7.3 文本过滤器

参考图 7.4 图 7.4：

图 7.4 日期过滤器

从您想要创建条件的列的下拉列表中选择文本筛选器、日期筛选器或数字筛选器，然后点击自定义以显示自定义自动筛选对话框。

在包含条件标签的单元格下方输入比较条件。您可以使用同一行输入“AND”条件，也可以使用不同的行输入“OR”条件。例如，图 7.5 图 7.5 中给出的条件可以用来显示仅来自北方或南方地区的人的记录。

图 7.5 条件范围

激活数据选项卡。
在排序与筛选组中，点击高级以打开高级筛选对话框（如图 7.6 图 7.6 所示）。

图 7.6 高级筛选

在列表范围框中，选择您想要筛选的单元格范围。单元格范围必须包括相关的列标题。

使用高级筛选筛选列表

如果您希望筛选数据，以便仅显示来自北方和南方地区、销售和行政部门、工资在 7000-12000 或 15000-20000 之间的员工的记录，自动筛选将无法满足需求。这是因为自动筛选中不能对另一个数字筛选进行应用。上述查询要求我们在工资字段上执行相同的操作。为了解决这个问题，我们可能需要使用高级筛选。

在使用高级筛选时，我们需要有一个条件范围和一个列表范围。列表范围就是您的数据库。

要创建条件范围，我们需要复制数据库的列标题。
在条件范围框中，选择包含您的条件的单元格范围，然后点击确定。

注意：在设计条件范围时，最好将整个数据库的列标题复制并粘贴为条件范围的标题。

为了更好的可见性，请将条件范围和列表范围放在不同的行上。高级筛选命令像自动筛选一样就地筛选您的列表，但它不显示列的下拉列表。相反，您必须选择列表范围（您的数据），在您的工作表上的条件范围内输入条件，并选择条件范围。在输出范围中，输入您想要显示输出的单元格地址。这是可选的。

筛选唯一记录

高级筛选也可以用于在单独的位置筛选列表中的唯一值。尽管 Excel 的删除重复项功能可以帮助创建列表中的唯一值列表，但如果您需要在不同的位置使用这些唯一值，则需要复制和粘贴它们。为了避免这种情况，请使用以下高级筛选选项：

选择要筛选的列或点击范围或列表中的单元格。
在“数据”选项卡上，点击“筛选”。
点击“高级筛选”。
执行以下操作之一：

•要就地筛选范围或列表，请点击“就地筛选列表”。

•要将筛选结果复制到另一个位置，请点击“复制到另一个位置”。然后，在“复制到”框字段中，输入单元格引用。

•要选择单元格，请点击“折叠对话框”以暂时隐藏对话框。在工作表上选择单元格，然后按“展开对话框”。

•选择“仅选择唯一记录”复选框。

注意：高级筛选的“复制到”选项将复制到同一工作表。如果您想将筛选后的数据复制到不同的工作表，请在您想要放置数据的工作表中选择高级筛选命令。

结论

在 Excel 中筛选数据库允许我们根据我们定义的标准提取特定信息。在本章中，我们学习了自动筛选、数字、文本和日期筛选以及高级筛选。我们还探讨了唯一记录的筛选。通过掌握这些技术，我们可以有效地分析数据并做出明智的决策。在下一章中，我们将深入了解 Excel 中排序数据的强大功能。

练习

打开名为“筛选”的工作表。使用自动筛选根据以下标准查找记录：

a. 来自北方或南方的人

b. 在销售或行政部门工作的人

c. 在销售或行政部门工作，位于北方或南方，且工资在 7000 至 12000 元之间的人

d. 在销售或行政部门工作，位于北方或南方，且工资在 7000 至 12000 元之间或 15000 至 20000 元之间的人

第八章

小计和数据合并

简介

有时我们需要在报告末尾计算小计，然后是总计数。我们通常通过在每个组末尾添加一行并使用求和函数来完成此操作。尽管这不是做小计的错误方法，但最大程度的手动干预增加了出错的可能性。

Excel 提供了使用子总功能有效完成这项工作的方法。本章将以数据的合并结束。

结构

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

■子总计

■数据合并

目标

在学习本章后，读者将能够定义子总计，使用子总计创建单一和多级数据摘要，并识别各种函数类型。

子总计

Excel 的子总计功能可以帮助我们自动计算列表中的子总计和总计数值。

根据所需的报告类型，我们有两种工作要做：

■单一级别子总计

■多级子总计

在单一级别显示子总计

在计算数据子总计之前，我们首先需要根据子总计所需的字段对列表进行排序。假设我们想在列表中计算区域子总计，同时还需要按区域列对数据进行排序。要找到子总计，请按照以下步骤操作：

在“数据”选项卡 | “大纲”组中单击“子总计”命令。
将出现一个子总计对话框，如图 8.1 所示。

图 8.1 子总计

从“在每个更改中”列表框中选择所需的列。
从“使用函数”列表框中选择您要在数据上执行的功能。
从“添加子总计到”字段中选择您要执行子总计的列。

当您单击“确定”按钮时，Excel 将在所选列中的每个相同项目的组中插入一个子总计行。子总计对话框中还有一些其他选项，如图 8.1 所示。以下是对这些选项的解释：

■选择汇总函数：第一次使用“子总计”命令时，Excel 会根据您在“添加子总计到”框中选择的列中的数据类型建议一个汇总函数。通过在“子总计”对话框中的“使用函数”框中选择不同的汇总函数，例如平均值，来选择不同的计算方式。

■选择要汇总的值：第一次使用“子总计”命令时，“添加子总计到”框显示最右侧列的标签。您可以保留该标签为选中状态，或者选择列表中任何其他列的标签。下次您使用“子总计”命令时，Excel 将显示您最后选择的列的标签。

■在详细数据上方显示子总计行：如果您希望子总计行出现在其相关详细数据上方，并且总计数行出现在列表顶部，请清除“数据下方摘要”复选框。

显示嵌套子总计

有时您需要从数据中获取多个级别的子总计。例如，您可能需要按地区分组数据，然后按部门分组。在这种情况下，请按照以下步骤操作。

首先，如前所述，您需要按地区和部门对数据进行排序。
在“数据”选项卡 | “大纲”组中单击“子总计”命令。
从“在每个更改中”列表框中选择地区列。
从“使用函数”列表框中选择您要在数据上执行的功能。
从“添加小计到”字段中选择您要执行小计的列。
点击“确定”按钮以执行第一级小计。
选择“小计”命令，并从“在每个变化中”列表框中选择“Dept”列。
从“使用函数”列表框中选择您要在数据上执行的功能。
从“添加小计到”字段中选择您要执行小计的列。
在点击“确定”按钮之前，清除“替换当前小计”复选框，如图图 8.2 所示。

图 8.2 嵌套小计对话框

注意：如果您只想复制摘要细节，请选择包含摘要的概要。选择所需的列，按 Alt 键（以仅选择可见单元格），然后复制并粘贴。

合并数据

在 Excel 中合并数据允许您将多个范围或工作表中的数据合并成一个单独的汇总报告。以下是 Excel 中合并数据的分步指南。

打开一个新工作表，您要在其中合并数据，如图图 8.3 所示。

图 8.3 打开新工作表

选择您想要放置合并数据的单元格，如图所示

图 8.4。

图 8.4 选择单元格

在顶部菜单栏中点击“数据”标签，然后在“数据工具”组中点击“合并”按钮，如图图 8.5 所示。

图 8.5 合并选项

在合并对话框中，选择您要用于合并的功能，例如求和、平均值、计数等，如图图 8.6 所示。

图 8.6 合并对话框

通过点击参考字段旁边的折叠对话框按钮，然后选择您要合并的单元格，来选择您要合并的单元格范围。参见图图 8.7。

图 8.7 选择要合并的单元格

如果您要从多个工作表合并数据，点击“所有引用”字段中的“添加”按钮，然后选择您想要合并的附加工作表和单元格范围。参见图图 8.8。

图 8.8 从多个工作表合并数据

对于您想要包含在合并中的任何其他工作表，重复前面的步骤。
如果您不想在合并中包含这些项，请确保“顶部行”和“左侧列”复选框未被选中，如图图 8.9 所示。

图 8.9 使用标签的复选框

点击“确定”以合并数据，如图图 8.10 所示。

图 8.10 点击确定

在您合并数据后，结果将在第二步中选择的单元格中显示。您还可以使用合并功能创建数据透视表，这可以为您提供更详细的数据摘要。

合并数据的示例

假设您有三个工作表，每个工作表包含不同地区（东、西和南）的销售数据。每个工作表具有相同的格式，包括产品、销售代表和销售额列，如图 8.11 所示图 8.11。

图 8.11 样本工作表

要将这些工作表中的数据合并到单个摘要报告中，请按照以下步骤操作。

打开一个新的工作表，您想在其中合并数据，就像我们在图 8.3 中所做的那样。
选择您想要放置合并数据的单元格，例如，如图 8.12 所示图 8.12 中的单元格 A1。

图 8.12 选择一个单元格

在顶部菜单栏中点击“数据”标签，然后在“数据工具”组中点击“合并”按钮，如图 8.5 所示图 8.5。
在合并对话框中，选择您想要用于合并的函数（例如，SUM、AVERAGE、COUNT 等），就像在图 8.6 中所做的那样。
通过点击“引用”字段旁边的“折叠对话框”按钮来选择您想要合并的单元格范围，然后在东工作表上选择您想要合并的单元格，例如，A1:C10，如图 8.13 所示图 8.13。

图 8.13 选择要合并的单元格

如果您想从多个工作表中合并数据，请点击“所有引用”字段中的“添加”按钮，然后选择您想要合并的附加工作表和单元格范围（例如，选择西工作表上的范围，然后选择南工作表上的范围），如图 8.14 所示图 8.14。

图 8.14 从多个工作表中合并数据

如果您不想在合并中包含这些项，请确保“顶部行”和“左侧列”复选框未被选中，如图 8.9 所示图 8.9。
点击“确定”以合并数据，如图 8.10 所示图 8.10。

最终合并数据可以在图 8.15 中查看。

图 8.15 最终合并数据

结论

总之，本章介绍了 Excel 中的小计和数据合并的概念。小计允许自动计算列表中的小计和总计值，使数据分析更高效。您可以使用小计功能根据所选列和函数创建单级和多级数据摘要。

数据合并可以将来自多个范围或工作表的数据组合成一个单一的汇总报告。合并功能提供了各种合并函数，并允许选择特定的单元格或范围进行合并。当处理来自不同来源或工作表的数据时，此功能特别有用。

通过掌握子总金额和数据合并，用户可以简化他们的数据分析过程，并以最少的手动干预生成准确和全面的报告。

练习

任务 1：为合并打开一个新的工作表。

任务 2：计算每个地区的子总金额，并使用子总功能显示它们。

地区：东部

产品	销售代表	销售金额
A	John	$500
B	Amy	$700
C	John	$400

表 8.1 东部地区详细信息

地区：西部

产品	销售代表	销售金额
A	Sarah	$600
B	Sarah	$900
C	Jack	$350

表 8.2 西部地区详细信息

地区：南部

产品	销售代表	销售金额
A	Emma	$800
B	Emma	$600
C	David	$450

表 8.3 南部地区详细信息

任务 3：使用合并功能将所有三个地区的数据合并到一个单一的汇总报告中。

任务 4：回答以下问题：

a. 每个地区的总销售额是多少？

b. 所有三个地区的总销售额是多少？

第九章

数据透视表

简介

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

结构

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

■检查数据透视表

■推荐的透视表

■创建数据透视表

■总百分比

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

■筛选器

■时间线

■Power View

■Power Pivot

■数据模型的好处

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

目标

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

检查数据透视表

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

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

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

参考图 9.1：

图 9.1 数据透视表字段

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

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

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

激活“插入”选项卡，以打开创建数据透视表对话框。
添加字段到标题。您需要在 S 值区域放置至少一个字段。

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

推荐的数据透视表

检查数据透视表的推荐。要查看推荐，请选择数据库并单击“插入”选项卡。单击如图 9.2 所示的推荐数据透视表选项。

图 9.2 推荐的数据透视表

或者，只需选择数据以创建数据透视表。单击右下角的按钮，并选择表格选项以创建数据透视报表。

创建数据透视表

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

按照以下步骤操作。

选择整个数据范围，包括标题行。您可以通过点击并拖动鼠标跨过数据或使用键盘快捷键（例如，Ctrl+A）来完成此操作。请参阅图 9.3。

图 9.3 销售数据

在 Excel 中，转到功能区上的“插入”标签，然后单击“数据透视表”按钮，如图图 9.4 所示。

图 9.4 插入数据透视表

将会弹出一个“创建数据透视表”对话框。确保您选择的数据范围正确，然后选择您想要放置数据透视表的位置（例如，新工作表或现有工作表）。请参阅图 9.5。
点击“确定”以创建数据透视表。

图 9.5 表/范围

数据透视表字段列表将出现在屏幕的右侧。此列表包含您的销售数据中的列标题。请参阅图 9.6。

图 9.6 客户数量

将所需的字段从字段列表拖放到数据透视表的不同区域。
将 CUSTNAME 字段拖动到“行”区域以按客户名称分组数据。
将 QTY 字段拖动到“值”区域以计算销售总量。

请参阅图 9.7。

图 9.7 值字段设置

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

图 9.8 总计和平均值

总计百分比

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

在数据透视表的“数量”列中的任何值上右键单击。
从上下文菜单中选择“值字段设置”或“值设置”。
在出现的对话框中，选择“总计百分比”或“总百分比”（确切措辞可能因您的电子表格软件而异）。
点击“确定”以应用计算。
现在，数据透视表将显示每个客户或分组在“数量”列中的总百分比。

请参阅图 9.9：

图 9.9 总计百分比

在数据透视表中分组项目

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

选择数据范围中的任何单元格。
激活“选项”标签。
点击“分组字段”。
在“按”框中，单击一个或多个时间段以分组。

日期分组

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

假设您已经创建了一个包含日期字段在“行”或“列”区域的数据透视表，请继续下一步。
在数据透视表的“日期”列中的任何日期值上右键单击。
在出现的上下文菜单中，选择“分组”或“分组字段”（具体措辞可能因你的电子表格软件而异）。
在“分组”对话框中，选择“月份”选项。
点击“确定”以应用分组。
数据透视表现在将按月分组显示日期列，每个月份在数据透视表中作为一个单独的项目出现。

参考图图 9.10。

图 9.10 日期分组

月度报告

月度报告可以在以下图 9.11 中查看。

图 9.11 月度报告

使用数据透视表创建图表

你可以使用数据透视表图表来图形化显示数据透视表中的数据。单个数据透视表图表提供了相同数据的多个视图。当你创建数据透视表图表时，数据透视表的行字段成为类别，列字段成为系列。

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

图 9.12 月度图表

要按月份和年份同时分组你的数据透视表中的日期列：

在数据透视表的“日期”列中的任何日期值上右键单击并选择“分组”。
在“分组”对话框中，选择“月份”选项和“年份”选项。最后，点击“确定”以应用分组。

参考图图 9.13。

图 9.13 按年月分组

要按年份和季度同时分组你的数据透视表中的日期列，按照以下步骤操作：

在数据透视表的“日期”列中的任何日期值上右键单击并选择“分组”。
在“分组”对话框中，选择“季度”选项和“年份”选项。最后，点击“确定”以应用分组。（Qtr1 在这里指的是一月、二月和三月。）

参考图图 9.14。

图 9.14 按年季度分组

周报

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

参考图图 9.15。

图 9.15 按日分组

数据透视表现在将按日分组显示数据，让你可以看到按日汇总的周报，如图图 9.16 所示。

图 9.16 周报

数字分组（创建层）

对于数字的分组，按照以下步骤操作：

在数据透视表的“净”列中的任何值上右键单击。
从上下文菜单中选择“分组”或“分组字段”选项。参考图 9.17。

图 9.17 按数字分组

在“分组”对话框中，指定分组板块的起始值。例如，如果您想从 0 开始，请在“起始值”字段中输入 0。
在“按”字段中，输入您想要使用的板块大小。在本例中，输入 1000。
点击“确定”以应用分组。

PivotTable 现在将显示包含值分组为 1000 个板块的净列。这种分组允许您根据定义的板块分析数量值。

切片器

切片器是易于使用的筛选组件。它们包含一组按钮，允许您快速筛选 PivotTable 报告中的数据，无需打开下拉列表来查找要筛选的项目。当您使用常规的 PivotTable 报告筛选器筛选多个项目时，筛选器会指示已筛选多个项目，但您必须打开下拉列表来查找筛选详细信息。切片器清楚地标记了应用的筛选器并提供详细信息，以便您轻松理解筛选后的 PivotTable 报告中显示的数据。请参阅图 9.18。

图 9.18 按日分组

按照以下步骤操作：

选择您的 PivotTable 中的任何单元格。
在 Excel 标题栏中转到 PivotTable 分析或分析选项卡。
定位到筛选器组并单击“插入切片器”按钮。
在“插入切片器”对话框中，勾选“Custname”旁边的复选框以选择它。请参阅图 9.19。

图 9.19 切片器

点击“确定”按钮以插入切片器。请参阅图 9.20。

图 9.20 客户切片器

切片器将被添加到您的工作表中。您可以根据需要调整大小和重新定位它。
使用切片器通过选择特定的

您想要包含或排除的客户名称值。

时间线

您现在可以使用 PivotTable 时间线而不是玩弄筛选器来显示日期，它是一个您可以添加到您的 PivotTable 中的框，允许您按时间筛选并放大您想要的时期。点击“分析”|“插入时间线”来调用它。请参阅图 9.21。

图 9.21 时间线

图 9.22 显示了日期时间线。

图 9.22 日期时间线

Power View

Power View 插件允许您通过特别强调您所需的变量来定义您工作表的视觉吸引力的摘要。通过一个单独的选项卡，您可以选择以最适合您的方式查看数据。在定义相关 Power 字段后，Power View 从所选工作表中提取信息，为您提供有关指定查看筛选器的完整概述。

此外，您可以选择新的主题、背景、透明度、图片和其他内容，并自由地插入、修改、排列和分析关系，从而有效地进行数据模型构建。请按照以下步骤操作。

选择数据。
点击“插入”选项卡。
点击“Power View”选项。
您可以看到带有字段列表和数据的 Power View 图像。

利用位于此数据右侧的筛选按钮，它将允许您通过所有列字段的帮助，按需筛选数据。

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

Power Pivot

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

转到“文件”选项卡。
点击“选项”|“插件”。
在“管理”框中，点击“COM 插件”。
在 COM 插件框中勾选 MS Office Power Pivot。

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

数据模型的好处

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

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

请按照以下步骤操作。

将数据转换为表格。
将 Excel 表导入 Power Pivot。

a. 数据透视表中的表字段列表可以包含多个表。

b. 我们可以根据一个共同的字段建立表格之间的关系，这样您就不必使用查找。

c. Power View 是通过数据模型启用的。

使用 Power Pivot 创建数据透视表

我们在四个不同的工作表中拥有四个数据集：城市、客户、订单和订单详情。我们需要创建一份关于总销售量的城市报告。我们需要遵循以下步骤：

选择数据

a. 转到“开始”选项卡。

b. 选择“样式”组。

c. 格式化为表格。

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

e. 在“属性”组中，给表格命名。

选择“Power Pivot”选项卡。

a. 转到“表格”组。

b. 添加到数据模型。表格将被添加到数据模型中。

定义关系

a. 在 Power Pivot 视图中转到“开始”选项卡。

b. 从视图选项卡中选择图表视图。

c. 将常用字段拖放到两个表之间创建链接。

在视图选项卡中选择数据视图。
在“开始”选项卡上单击“数据透视表”。
创建数据透视表。
从表格中选择字段（活动字段和所有字段）。表格周围的黑色边框表示它与某个其他表有链接。
从城市详情中选择城市名称，从订单详情中选择数量。

结论

总之，关于数据透视表的章节提供了对 Excel 中这一强大工具的全面理解，它可以用于数据分析与报告。数据透视表允许用户快速有效地汇总和分析大量数据，提供对数据的多种视角和见解。

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

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

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

练习

打开包含销售数据的提供的 Excel 电子表格。
创建一个数据透视表来计算每个产品类别的总销售收入。
格式化数据透视表，使其易于阅读且具有视觉吸引力。
创建一个数据透视图来表示按产品类别划分的销售收入。
创建另一个数据透视表来确定销售收入最高的地区。
创建一个数据透视图来可视化按地区划分的销售收入。
创建一个数据透视表来分析月度销售收入。
使用条件格式突出显示任何显著的变化或趋势。
创建一个数据透视表来计算每个产品类别的平均销售收入。

第十章

条件格式

引言

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

结构

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

■条件格式化

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

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

■图标集

■具有多个条件的公式

目标

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

条件格式化

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

图 10.1 条件格式化

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

■使用双色刻度格式化所有单元格。

■使用三色刻度格式化所有单元格。

■使用数据条格式化所有单元格。

■使用图标集格式化所有单元格。

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

■仅格式化排名最高或最低的值。

■仅格式化高于或低于平均值的值。

■仅格式化唯一或重复的值。

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

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

■清除条件格式。

例如，您可以突出显示所有超过 75,000 的销售数字的绿色。条件格式化可以根据单元格值或公式应用。

注意：在应用条件格式化之前选择的区域将确定工作表中应用格式的区域。

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

要使用基于单元格值的条件格式化应用着色，请按照以下步骤操作。

选择要应用条件着色的单元格。
激活“工作表”选项卡。
在“样式”组中，单击“条件格式化”。
从出现的画廊中选择“突出显示单元格规则”。
从菜单中选择单元格中的条件或选择“更多规则”。
指定条件和单元格着色。
单击“确定”以应用格式。

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

要根据公式应用条件格式化，请按照以下步骤操作。

选择要应用格式的单元格。
在“样式”选项卡组中，单击“条件格式化”。
从菜单中选择“条件格式化规则图例”以打开格式化规则图例对话框。
单击“新建规则”以打开添加格式化规则对话框。
在“选择规则类型”框中，选择“使用公式确定要格式化的单元格”。
在“格式值，其中此公式为真”框中，输入一个评估值为真或假的公式。
点击“格式”以打开“格式单元格”对话框，如图 10.2 Figure 10.2 所示。
在“格式单元格”对话框中，指定满足条件的单元格应出现的格式，然后点击“确定”。
点击“确定”以关闭“添加格式化规则”对话框。
点击“确定”以关闭“格式化规则图例”对话框并应用格式。

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

示例：

图 10.2 Figure 10.2 展示了如果选择包括列标题，则突出显示销售部门所有人的记录的公式。

如果我们已选择不带标题的数据库，并想突出显示收入最高的记录，我们需要使用以下公式：

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

参考图 10.2 Figure 10.2。

图 10.2 新建格式化规则

图标集

在 Excel 中，你可以访问更多的图标集，包括三角形、星号和方框。你还可以混合搭配来自不同集合的图标，并更轻松地隐藏图标。例如，你可能只想为高利润值显示图标，而对于中等和低值则省略它们。

带有多个条件的公式

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

选择不带标题的范围。
点击“开始”选项卡。
选择“条件格式化”。
点击“新建规则”。
输入公式 =AND($D2="Sales"$$,$E2="East"$$)
选择所需的格式。
点击“确定”以应用并关闭。

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

假设我们想通过另一个工作表的单元格引用来突出显示记录，那么请按照以下步骤操作：

选择不带标题的整个数据。
点击“开始”选项卡 | “条件格式化” | “管理规则”。
点击“新建规则”。
选择规则类型为“使用公式确定要格式化的单元格”。
在“编辑规则描述”中，输入公式如下：=$$D2='cross sheet'!$$B$$
选择所需的格式。
点击“确定”以应用并关闭对话框。

结论

总结，Excel 中的条件格式化是一个非常有用的功能，它允许我们根据特定条件应用格式。它帮助我们突出显示重要数据，可视化趋势，并增强工作表的展示效果。通过掌握条件格式化技术，我们可以使我们的数据更具视觉吸引力，并获得宝贵的见解。

练习

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

使用 VLOOKUP 获取客户类型和利率。
创建六份发票工作表的副本并解决其他问题。
对记录进行排序：零售商或直接批发商。
根据销售价格在 3000-5000 之间过滤 6 月的记录。
过滤记录以获取零售商和批发商的 100-150 和 250-500 之间的数量。
根据类型和客户按总数量和总金额汇总记录。
通过月份和客户创建一个汇总报告，涵盖总数量/总金额和金额百分比。
高亮显示名为 Direct 的客户类型的行。

第十一章

假设分析

简介

在许多情况下，你可能需要在公式中使用多个不同的值集，以便探索所有不同的结果。在这种情况下，人工干预可能会增加，导致错误。在这种情况下，假设分析工具可以为你提供帮助。Excel 中有三个假设分析工具，分别是：

■目标寻求

■数据表

■情景管理器

结构

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

■目标寻求

■使用数据表预测数字

■单变量数据表

■双变量数据表

■假设情景

■创建场景

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

目标

学习本章后，读者将能够理解目标寻求、定义数据表和使用情景管理器。

目标寻求

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

Excel 中的目标寻求功能帮助我们计算一个值，使得给定公式的值与指定的目标相匹配。目标寻求可以节省你进行耗时的大量试错分析。

使用目标寻求命令

要找到一个特定值以解决公式，请按照以下步骤操作。

选择包含公式的单元格。
激活“数据”选项卡。
在“数据工具”组中，单击“假设分析”并选择“目标寻求”以打开目标寻求对话框。
在“设置单元格”框中，指定包含要解决的公式的单元格。
在“目标值”字段中输入你想要的结果。
在“更改单元格”字段中，指定包含要调整的值的单元格。
点击“确定”。

例如，一个人贷款 10 万美元，期限为 36 个月，每月的 EMI [PMT]为 3250 美元。如果他每月支付 5000 美元，他将在多少个月内完成付款？

参考表 11.1 Table 11.1。

表 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 目标求值

使用数据表预测数据

数据表是一个范围，当在其中一个或多个公式中更改某些值时，它会显示结果。您希望在公式中输入的不同值也包含在数据表中。数据表中可以使用一个或两个变量。

单变量数据表

此方法用于观察在一个或多个公式中改变单个变量所产生的影响。

示例：

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

图 11.2 单变量数据表示例

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

在一行或一列中输入输入值。
如果您在列中列出输入值，请在第一个输入值上方的一行和输入值右侧的列交叉处的单元格中输入公式，如图 11.2 所示。如果您在行中列出输入值，请在第一个值左侧的列和输入值行下方的一行交叉处的单元格中输入公式。
选择包含输入值和公式的范围。
在“数据”选项卡上，在“数据工具”组中，点击“假设分析”并选择“数据表”以打开“表格”对话框。
如果输入值位于一列中，请在“列输入单元格”框中指定输入单元格。如果输入值位于一行中，请使用“行输入单元格”框。
点击“确定”。

双变量数据表

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

要创建一个双变量数据表，请按照以下步骤操作。

输入包含两个输入单元格的公式。
在同一列中，公式下方输入第一个输入值列表。在同一行中，公式右侧输入第二个输入值列表。
选择包含输入值和公式的范围。
在“数据工具”组中，点击“假设分析”并选择“数据表”以打开“表格”对话框。
在“行输入单元格”框中，指定行输入单元格。
在“列输入单元格”框中，指定列输入单元格。
点击“确定”。

假设情景

场景是被称为假设分析工具集的一部分的命令。场景是一组 Excel 保存并可以自动替换到您工作表中的值。为了预测工作表模型的输出，您可以使用场景。您可以在工作表上创建和保存不同的值组，并切换到这些新场景中的任何一个以查看不同的结果。您可以为每个场景定义多达 32 个更改单元格。

您可以使用场景管理器执行以下任务。

■使用多组更改单元格创建多个场景。

■在工作表中查看每个场景的结果。

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

■将组中的场景合并到单个场景模型中。

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

■使用自动场景历史记录跟踪修改。

创建场景

让我们假设您必须分析以下示例中在最佳、最坏和当前情况下并排的业务净收入。您可以使用场景管理器以汇总格式达到所需的结果。

您可以使用场景管理器对话框创建场景。按照以下步骤操作。

激活“数据”选项卡。
从数据工具组中的“假设分析”列表中选择场景管理器以打开场景管理器对话框。
点击“添加”按钮以打开添加场景对话框。
在“场景名称”框中，指定场景的名称。
在“更改单元格”框中，指定包含您想要更改的值的单元格。（例如，选择当前场景范围的范围）。
点击“确定”以打开“场景值”对话框。
在“场景值”对话框中，指定更改单元格的值。
点击“确定”以创建场景。

如果您要创建更多场景，请再次点击“添加”，然后重复此过程。完成创建场景后，请点击“确定”。

示例：

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

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

参考以下图 11.3。

图 11.3 场景管理器

创建场景摘要报告

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

点击“汇总场景管理器”对话框。
在“结果单元格”框中，输入引用更改场景值（例如，上述示例中的净收入）的单元格的引用。多个引用之间用逗号分隔，如图 11.4 所示。

删除场景

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

激活“数据”选项卡。
在数据工具组中的假设分析列表中，选择场景管理器以打开场景管理器对话框。
点击您想要删除的场景名称。
点击删除。

参考图 11.4。

图 11.4 场景摘要

显示场景

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

激活数据选项卡。
在数据工具组中的假设分析列表中，选择场景管理器以打开场景管理器对话框。
点击您想要显示的场景名称。
点击显示。

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

从另一个工作表合并场景

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

打开包含您想要合并的场景的所有工作簿。
切换到您想要合并场景的工作表。
激活数据选项卡。
在数据工具组中的假设分析列表中，选择场景管理器以打开场景管理器对话框。
点击合并。
在工作簿框中，点击一个工作簿名称。
在工作表框中，点击包含您想要合并的场景的工作表名称。
点击确定。
如果您想要从更多工作表合并场景，请重复此过程。

保护场景

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

■防止更改

■隐藏

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

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

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

结论

总之，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 年。

生成一个场景摘要报告，显示每个场景的月供。

第十二章

与多个工作表、工作簿和应用一起工作

简介

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

结构

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

■不同工作表之间的链接

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

■审计功能

■工作组协作

■创建超链接

目标

在学习本章后，读者将能够识别与多个工作表和多个工作簿以及应用程序一起工作的各种方法。

不同工作表之间的链接

如果不同工作表中有月度数据，而我们需要在不同的工作表上拥有年度数据，如图 12.1 所示，我们可以使用跨工作表引用。

图 12.1 月度数据

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

Sheetname!Reference

图 12.2 展示了从不同工作表中查找单元格总和的示例：

图 12.2 查找总和

如果产品名称在不同工作表中不相同，或者顺序不一致，这种方法将不会证明是有用的。在这些情况下，您可以使用 Excel 的合并功能，如图 12.3 所示。

图 12.3 合并功能

要合并来自不同工作表的数据，请按照以下步骤操作。

转到“数据”选项卡。
选择“合并”。将打开“合并”对话框，如图 12.4 所示。

图 12.4 合并对话框

从函数下拉框中选择要应用的数据函数。
点击“浏览”并选择第一组数据。
点击“添加”。
对所有数据重复第二步和第三步。
如果你想获取行和列标题，请勾选“顶部行”和“左侧列”复选框。
如果您希望源数据每次更改时都更新数据，请选择“创建到源数据的链接”。

参考图 12.5。

图 12.5 结果数据

在不同软件之间创建链接

假设我们必须从 Excel 工作表复制特定的数据到 Word 文档。如果你进行常规的复制和粘贴，粘贴到 Word 文档中的内容是静态的；即使 Excel 工作表中的数据发生变化，它们也不会更新。现在让我们看看如何创建一个链接，以便即使在 Word 文档中数据也会更新。按照以下步骤操作：

从 Excel 工作表复制内容。
在 Word 文档中粘贴时，从剪贴板组点击“主页”选项卡。
选择“编辑-特殊粘贴”。
在“特殊粘贴”对话框中，选择“粘贴链接”选项，然后选择“MS Office Word 对象文档”。
点击“确定”。
现在，每当 Excel 工作表中的数据发生变化时，该变化将自动反映在 Word 文档中。

审核功能

您可以使用 Excel 中的审核功能来追踪工作表中的错误。您还可以追踪工作表上单元格和公式之间的关系。您可能想识别公式基于其值的单元格。Excel 提供了“追踪前置”和“追踪依赖”命令来指出这样的单元格。

依赖和前置单元格

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

工作组协作

共享工作簿使得工作组中的多个成员能够对同一组数据进行协作。例如，几个销售经理可以在同一工作簿中输入他们各自的区域销售数据，这样就无需手动收集和合并数据。

共享工作簿

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

打开您想要共享的工作簿。
激活“审阅”选项卡。
在“更改”选项卡组中，点击“共享工作簿”以打开“共享工作簿”对话框。
激活“编辑”选项卡。
选择“允许多个用户同时更改”，然后点击“确定”。
将工作簿保存在其他用户可以访问的位置。

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

合并工作簿

您可能需要将工作簿共享给无法同时访问同一文件的用户。在这种情况下，您可以分发共享工作簿的副本，允许用户更改他们的副本，然后将这些副本合并到一个工作簿中。要共享您打算稍后合并的工作簿，请按照以下步骤操作：

打开“共享工作簿”对话框，激活“编辑”选项卡，并选择“允许多个用户同时更改”。
在“高级”选项卡下，在“跟踪更改”部分，选择“保留更改历史记录”。在框中，输入您希望用户在 workbooks 中进行更改的天数。
点击“确定”。
复制工作簿并发给每个用户一份。

用户更改了他们工作簿副本后，您可以使用以下步骤将副本合并到一个工作簿中：

选择“文件”，然后选择“Excel 选项”以打开“Excel 选项”对话框。
在“自定义”中添加“比较和合并工作簿”到快速访问工具栏。
在快速访问工具栏上，选择“比较和合并工作簿”以打开“将文件合并到当前工作簿”对话框。
选择包含您想要合并的更改的工作簿副本。
点击“确定”。

跟踪更改

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

激活“审阅”选项卡。
在“更改”选项卡组中，点击“跟踪更改”并选择“突出显示更改”以打开“突出显示更改”对话框。
如果工作簿未共享，请选择“编辑时跟踪更改”。如果工作簿已共享，则此选项将默认选中。
指定您想要如何跟踪更改：

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

b. 如果您想根据特定用户查看所做的更改，请勾选“谁”，然后从列表中选择“所有人”或“除我之外的所有人”。

c. 如果您想查看对特定单元格范围的更改，请勾选“在哪里”，然后输入范围。

点击“确定”。

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

打开包含跟踪更改的工作簿。
激活“审阅”选项卡。
在“更改”选项组中，点击“跟踪更改”并选择“接受或拒绝更改”。
您将提示保存工作簿。点击“确定”以保存工作簿。
将出现“选择要接受或拒绝的更改”对话框。
如果您想根据更改的日期查看更改，请勾选“何时”并选择一个时间段。
点击“确定”以打开“接受或拒绝更改”对话框。
包含更改值的单元格将被突出显示。此对话框显示有关每个更改的信息，包括更改人的姓名、更改的日期和时间，以及如果您接受或拒绝建议的更改将发生的其他更改。您可以向下滚动以查看其余内容。
点击“接受”以接受更改或点击“拒绝”以恢复原始值。
下一个具有更改值的单元格将被突出显示。

创建超链接

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

要链接特定单元格，首先命名该单元格。
选择单元格，并点击“公式”选项卡。
选择“定义名称”。
将出现定义名称对话框。
在“定义”对话框中，为单元格输入一个名称并点击“添加”。
点击“关闭”。
现在点击要创建超链接的单元格。
点击“插入超链接”或按 Ctrl+K。
将出现插入超链接对话框。
点击“在此文档中放置”选项。
点击“已定义名称”选项。将出现工作簿的已定义名称。
选择我们在上一步中创建的已定义名称。
点击“确定”。
将创建链接。

创建指向不同文件的链接

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

要链接特定单元格，首先为单元格定义一个名称。
选择单元格，然后点击“公式”选项卡。
选择“定义名称”。将出现定义名称对话框。
在“定义”对话框中，为单元格输入一个名称。
点击“添加”，然后点击“关闭”。
现在点击要创建超链接的单元格，然后点击“插入超链接”或按 Ctrl+K。
将出现插入超链接对话框。
点击“现有文件或网页”选项。
浏览到您保存要链接的文件的文件夹。
点击列表中出现的文件。
文件的路径将出现在其下方的地址框中。
点击“确定”。

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

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

例如，使用 c:\test.xlsx#salary!A1 来引用位于 C 盘的 test.xlsx 文件中薪酬工作表的 A1 单元格。

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

结论

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

练习

打开一个 Excel 工作簿，创建三个工作表，分别命名为“销售”、“支出”和“摘要”。
在销售工作表中，输入不同产品的月度销售数据。
在支出工作表中，输入各种类别的月度支出数据。
使用跨工作表引用在摘要工作表中计算总销售额和总支出。
使用合并功能将销售和支出工作表中的数据合并到一个单独的工作表中。
创建 Excel 工作表和 Word 文档之间的链接。将合并后的数据从 Excel 工作表复制并粘贴到 Word 文档中作为链接对象。
使用 Excel 提供的审计功能追踪工作表中特定公式的先决条件和依赖项。
与同事共享工作簿并允许同时编辑。
将工作簿多个副本的更改合并回单个工作簿。
跟踪工作簿中不同用户所做的更改，并审查和接受或拒绝这些更改。

第十三章

处理图表

简介

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

结构

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

■使用图表工具创建图表

■图表设计

■格式化图表

■建议

■Sparklines

目标

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

使用图表工具创建图表

图表可以用来更有效地展示数据。例如，分析数据趋势需要时间，但如果以图形方式表示，则更容易理解。有许多类型的图表，如柱形、折线、饼形、条形、面积和散点图。要创建图表，请按照以下步骤操作：

选择数据。
转到“插入”选项卡。
从“图表”组中选择你想要创建的图表，如图图 13.1 所示。
我们可以选择任何类型的柱形图或点击“所有图表类型”|“插入图表”。将出现对话框。
选择所需的图表。
点击“确定”。
图表将在同一工作表中创建。Excel 将在对话框中推荐图表。

参考图图 13.1。

图 13.1 图表

图表设计

Excel 为每种图表类型提供不同的图表设计，我们可以在“设计”选项卡中选择。设计图库的示例可以在图 13.2 中看到。

图 13.2 图表设计

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

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

要这样做，请按照以下步骤操作。

使用“设计”选项卡。在图表布局组中点击“添加图表元素”选项。
添加图表元素。
在“添加图表元素”选项下，用户有许多格式化图表的选项，例如添加次要轴、给轴或图表添加名称、添加数据标签等。

参考图图 13.3。

图 13.3 图表的关联选项

格式化图表

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

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

数据图表

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

表 13.1 显示了各种类型的数据和可以用来有效表示它们的图表：

表 13.1 图表类型

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

图表模板

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

创建图表后，右键点击“图表”并选择“另存为模板”选项。
将会出现“另存为”对话框。保存您的图表。

在未来，如果您想使用此模板，请执行以下步骤：

右键点击新创建的图表。
将出现上下文菜单。点击“更改图表类型”。
在“插入图表”对话框中，点击“模板”。在这里，您可以查看所有已保存的模板。
选择要应用到当前图表的所需模板。

图表筛选选项

另一个令人印象深刻的功能是图表筛选选项，当您插入图表时，它会列出所有变量（系列）和类别，以供交互式查看。筛选图表信息可能很困难。如果您只想查看所需的信息，请使用图表筛选按钮。

瀑布图

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

选择包含类别和值数据的表格。
在 Excel 标签栏中转到“插入”选项卡。
在“图表”部分中，点击“瀑布图”类型。
选择所需的瀑布图子类型（例如，“瀑布”或“堆叠瀑布”）。
Excel 将根据图 13.4 中提供的所选数据生成瀑布图。

图 13.4 为图表提供的数据

图表可以在图 13.5 中查看。

图 13.5 瀑布图

结果的瀑布图将显示每个类别为一个条形，正值由高于起点的条形表示，负值则显示为低于起点的条形。累计总数由每个条形的长度和位置表示。

推荐

“插入”选项卡功能丰富，从推荐的透视表到推荐的图表。Excel 提供了在最少时间内实现最大效果的推荐。为了获得最佳结果，请使用 Excel 提供的推荐。

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

选择要创建图表的数据。
点击“插入”选项卡。
选择推荐的图表选项。您将获得提供各种图表类型的视图。
选择任何选项。

Sparklines

Sparklines 是适合单元格的小型图表。Sparklines 帮助用户在数据旁边查看总结趋势。它占用很少的空间。它特别适用于仪表板或其他需要以易于理解的可视化格式展示业务快照而无需添加太多细节的地方。例如，图 13.6 和图 13.7 展示了 Sparkline 如何让您一眼看出每个部门在五月份的表现。

图 13.6 Sparklines 示例 1

参考图 13.7Figure 13.7。

图 13.7 Sparklines 示例 2

创建 Sparkline

要创建 Sparklines，请按照以下步骤操作。

选择一个空单元格或一组空单元格，在其中插入一个或多个 Sparklines。
在“插入”选项卡的 Sparklines 组中，单击您想要创建的 Sparkline 类型：线形、柱形或胜负。
在“数据”框中，输入包含您想要基于之前场景的数据的单元格范围。
选择一个或多个 Sparklines 后，Sparkline 工具将出现并显示“设计”选项卡。
在“设计”选项卡上，您可以从 Sparkline、类型、显示/隐藏和样式组中选择一个或多个命令。
使用这些命令创建新的 Sparkline，更改其类型，格式化它，在折线 Sparkline 上显示或隐藏数据点，或在 Sparkline 组中格式化垂直轴。

自定义 Sparklines

在创建 Sparklines 之后，Excel 为您提供了自定义 Sparklines 的选项，例如调整高、低、第一个、最后一个或任何负值。您还可以将 Sparklines 的类型更改为其他类型，例如线形、柱形或胜负。您还可以从图库中选择样式或设置单个格式选项，设置垂直轴选项，并控制空值或零值的显示方式。

更改 Sparklines 的样式

使用“设计”选项卡上的样式图库，当您选择包含数据的单元格时，它将变得可用。执行以下步骤以更改 Sparklines 的样式：

选择单个 Sparkline 或 Sparkline 组。
要应用预定义的样式，请转到“设计”选项卡。
在“样式”组中，单击一个样式，或单击框的右下角箭头以查看更多样式。
为 Sparkline 选择特定的格式。

结论

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

练习

从您的电子表格中选择一组数据或创建一个示例数据集。
使用所选数据创建柱形图。
通过添加标题、图例和数据标签来自定义图表。
将不同的图表设计应用到创建的图表中，并观察变化。
通过更改颜色、轮廓和其他格式选项来格式化图表。
将图表保存为模板以供将来使用。
使用 Excel 中的推荐功能来探索数据集的不同图表选项。
在单元格中创建一个 sparkline 来表示数据子集中的趋势。
通过更改类型、样式和格式选项来自定义 sparkline。
与他人分享您的图表和 sparkline，以展示您数据分析可视化的技能。

第十四章

在 VBA 中创建和记录宏

简介

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

结构

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

■VBA 简介

■宏简介

■创建宏

■录制宏

■定义宏

■停止录制

■相对引用宏

■运行您的宏

■按名称运行宏

目标

本章将介绍 VBA 和宏，解释创建和记录宏的过程，定义宏及其属性，演示如何运行宏，并提供创建宏以在 Excel 中自动化任务的实用示例。

VBA 简介

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

VBA 的使用

VBA 的一些用途如下：

■驱动整个应用程序。

■将多个操作合并为一个操作，即宏。

■编写自己的函数。

宏简介

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

下面是一些宏使用示例：

■按按钮自动添加标准公司页眉到任何电子表格。

■将总账系统中的文本文件格式化为更易用的格式。

■在电子表格内部打印某些工作表，而不是逐个打印每个工作表。

创建宏

创建宏有两种方法：

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

■录制：使用宏录制器在 Excel 中记录您的操作。Excel 有一个宏录制器，它记录操作并为宏编写代码。

创建宏的最佳方法如下：

确定用户希望从宏中获得的确切问题和最终结果。
规划宏的步骤以成功获得最终结果。
通过录制、编写或结合两者来创建您的宏。

注意：记录在 Excel 中执行的操作，或将其写入。

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

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

点击“文件”按钮。
点击“选项…”按钮。
在“自定义功能区”选项卡上，然后选择“在功能区中显示开发者选项卡”，如图图 14.1 所示。

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

录制宏

要录制宏，请按照以下步骤操作：

点击“开发者”选项卡。
在“代码”组中，点击如图 14.2 所示的“录制宏”按钮，图 14.2。

图 14.2 录制宏

定义宏

要定义宏，请按照以下步骤操作：

图 14.3 定义宏

当涉及到为宏命名时，请遵循以下规则：

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

■它不应以数字开头。

■它不应该有任何特殊符号，除了下划线 (_)。

■它最多可以有 255 个字符。

■不要使用也是单元格引用的宏名称。

宏存储

不同的宏存储选项如下：

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

■当前工作簿：录制将在当前工作簿上进行，宏将存储在当前文件中。

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

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

宏快捷键

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

宏描述

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

停止录制

要停止录制宏，请按照以下步骤操作：

执行宏所需的操作。
通过在“代码”组中的“开发人员”选项卡上的“停止记录”按钮或状态栏底部单击来停止记录。

参考图 14.4。

图 14.4 停止记录宏

相对引用宏

如果您希望宏相对于活动单元格的位置运行，请使用相对单元格引用来记录它。在“开发人员”选项卡上，单击“使用相对引用”使其选中。Excel 将继续使用相对引用记录宏，直到您退出 Excel 或再次单击“使用相对引用”使其不选中。

参考图 14.5。

图 14.5 停止记录宏

注意：使用相对引用按钮是一个切换按钮。请小心使用它，并在开始记录之前检查是否已选中。

场景 1

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

为此，请参考 Training File1.xls 并按照以下步骤操作。

开始记录。
添加名称“Company_name”。
将快捷键设置为 Ctrl + Shift + C。
执行以下步骤：

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

b. 输入您的公司名称。

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

d. 选择 A1 到 H1 单元格。

e. 单击合并工具。

停止记录：

a. 转到工具菜单，然后单击宏 | 停止

记录。参考以下图 14.6。

图 14.6 场景 1

运行您的宏

宏可以通过多种方式运行。

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

■名称

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

■工作表上的按钮

通过名称运行宏

要通过名称运行您的宏，请按照以下步骤操作：

转到“开发人员”选项卡。
选择宏（快照 1）。
选择要运行的宏（快照 2）。
单击运行按钮。

参考图 14.7。

图 14.7 通过名称运行宏

场景 2

创建一个宏，显示带有表头的产品表，包括产品名称、数量、价格、总计和净额。该表必须始终从第二行第一列出现。Excel 不应接受价格和数量的任何负值。此宏始终从第二行第一列（A1 引用）开始。

要执行的步骤如下。（参考 Training File1.xls。）

开始记录（使用名称 Product_Table 和快捷键 Ctrl + Shift + P）。
选择单元格 A2（表格必须始终从第二行第一列出现）。
按照图 14.8 创建表格。
编写总计和净总计的公式。
格式化它。
对数量和价格单元格进行验证（负值被限制）。
停止记录。

参考图 14.8。

图 14.8 场景 2

场景 3

创建一个宏来显示与场景 2 相同的商品表，但这次它应该出现在用户想要的位置（使用相对引用）。此宏依赖于用户的选择。

要执行的操作步骤如下：

开始录制（使用 Product_Table_Relative 名称和快捷键）。
在停止录制工具栏上打开相对引用按钮。
根据图 14.9 创建从当前单元格开始的表格。

注意：从工作表中的任何位置开始输入；在创建相对引用宏时，无需在工作表中点击。

编写总计和净总计的公式。
格式化它。
对数量和价格单元格进行验证（负值被限制）。
关闭相对引用按钮。
停止录制。

参考图 14.9。

图 14.9 场景 3

结论

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

练习

创建一个名为“CalculateAverage”的宏，该宏计算 Excel 中一系列数字的平均值。
创建一个名为“FormatData”的宏，该宏应用于 Excel 中一系列单元格的特定格式。
创建一个名为“GenerateReport”的宏，该宏自动化 Excel 中生成报告的过程。

第十五章

将按钮分配给宏

简介

在本章中，我们将探讨在 Excel 中为宏分配按钮的过程。通过在快速访问工具栏和 Excel 工作表中创建按钮，我们可以自动化任务，提高效率，并简化复杂操作。加入我们，深入了解 Excel 自动化世界，发现按钮宏集成之力。

结构

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

■在快速访问工具栏上创建按钮

■修改菜单或按钮

■在工作表上创建按钮

■编辑录制的宏

目标

到本章结束时，读者将了解如何创建快速访问工具栏上的按钮以快速访问宏，以及如何修改菜单或按钮以自定义其外观和功能。读者还将能够探索在 Excel 工作表中直接创建按钮的过程，以及熟练编辑录制宏以增强自动化并满足特定要求。

在快速访问工具栏上创建按钮

要在快速访问工具栏上创建按钮，请按照以下步骤操作。

点击“Office”按钮。
点击“Excel 选项”。
转到自定义选项卡。
在“从以下选项中选择命令”下拉框中，选择宏。
将您的宏添加到自定义快速访问工具栏。

参考图 15.1 图 15.1。

图 15.1 在快速访问工具栏上创建按钮

修改菜单或按钮

每次您想要创建新按钮、编辑现有按钮或从快速访问工具栏中删除任何按钮时，请按照以下步骤操作。

点击“修改…”按钮。
选择您选择的图标。
输入显示名称。
点击“确定”。

参考图 15.2 图 15.2。

图 15.2 修改菜单或按钮

场景 4

创建一个宏，该宏将提取基于区域的薪资总和（使用“薪资工作表”）。点击“小计”按钮时，应在新的工作表上添加基于区域的子总计，如图 15.3 所示。

图 15.3 场景 4

为解决此场景，请按照以下步骤操作：

开始录制（使用名称“Subtotal_Macro”）。
选择“薪资”工作表（源数据）。
选择单元格 A1（数据库从 A1 开始）。
如图 15.4 所示，对区域执行排序。

图 15.4 场景 4 解决方案

注意：排序必须按照小计的要求进行。排序错误会导致小计错误。

如图 15.5 所示，对区域执行小计，并按照以下步骤操作。

a. 在“数据”选项卡中，转到“大纲”组。

b. 点击“小计”按钮。

c. 还选择“区域”。

d. 选择求和函数（您可以根据项目需求选择任何其他函数）。

e. 选择“薪资”字段。

f. 点击“确定”。

参考图 15.5 图 15.5。

图 15.5 场景 4 解决方案

点击“大纲”的第二级（只显示小计）。
选择数据。
按下 Alt + ;键组合来选择从选择中的可见单元格。
复制选择（Ctrl + C）。
插入新工作表（Shift + F11）。
粘贴复制的数据（Ctrl + V）。
转到“薪资”工作表（源数据）。
使用以下步骤删除小计：

a. 转到“数据”选项卡。

b. 点击“小计”。

c. 点击“移除所有”按钮。

激活上一个工作表（Ctrl + PageUp）。
停止录制。

参考图 15.6 图 15.6。

图 15.6 场景 4 解决方案

创建按钮的 Excel 工作表

要在 Excel 工作表中创建按钮，请按照以下步骤操作。

激活“薪资”工作表。
转到“控件”组。
选择“按钮”（表单控件），如图 15.7（b）所示。
指定 subtotal_macro，如图 15.7（c）所示。
修改标题（右键单击并编辑文本），如图 15.7（d）所示。

参考图 15.7 图 15.7。

图 15.7 在 Excel 工作表中创建按钮

场景 5

创建一个宏，该宏将从不同的部门和地区提取记录。用户将输入所需的部门和地区，然后点击筛选按钮，如图图 15.8 所示。

图 15.8 场景 5

注意：上述宏将自动化高级筛选的工作。

按照以下步骤操作。

激活工资表。
为高级筛选创建一个条件范围。
开始录制。
使用名称“Filter_Macro”。
将数据存储在此工作簿中。
点击数据库的标题（单元格 A5）。
进行高级筛选。

a. 点击数据选项卡。

b. 点击高级。

参考图 15.9：

图 15.9 场景 5 解决方案

提供列表范围（数据库范围），如图图 15.10 所示。
提供条件范围。
点击确定。
停止录制。
从“开发人员”选项卡|“控件”组创建一个按钮。
将过滤器宏分配给此按钮。

参考图 15.10。

图 15.10 场景 5 解决方案

编辑录制宏

有时录制的宏可能无法提供完整的自动化。在这些情况下，您将不得不编辑您的录制宏。或者，您可能想向您的录制宏添加一些操作，然后编辑您的宏。

例如，在场景 5 中，您的宏应询问使用哪个地区和部门，而不是在 Excel 中更改条件。用户将输入他们的响应，然后点击确定。宏将执行筛选。

要编辑您的录制宏，请按照以下步骤操作。

转到工具。
点击宏。
打开 Visual Basic 编辑器。
打开宏的代码窗口。

场景 6

打开场景 5 并修改代码以执行以下操作。用户应得到一个输入框，可以在其中输入所需的部门和地区，然后被提示点击筛选按钮。

参考图 15.11。

图 15.11 场景 6

要编辑您的录制宏，请按照以下步骤操作：

转到工具。
选择宏。
选择 Filter_Macro。
点击编辑，如图图 15.12 所示。
打开宏的代码窗口。

参考图 15.12。

图 15.12 场景 6 解决方案

参考 Training File3.xls。

宏定义从关键字 Sub 开始，以 End Sub 行结束。
Inputbox 是一个用于从最终用户那里获取输入的函数。
范围是一个类。

注意：我们将在稍后详细讨论编写过程。

参考图 15.13。

图 15.13 场景 6 解决方案

场景 7

创建一个宏，从 txt 文件（sales.txt）导入数据，并设计一个根据产品和月份显示销售额总和的交叉表。此宏将自动化从文本文件导入数据并创建交叉表报告（参考 Training File4.xls）。

表 15.1 示例数据

图表中的销售额总计
产品	月份
cd	1 月 2 月
	3 月
cd 总计	187000
监控 1 月 2 月
监控总计	35000
U 盘	1 月
	3 月
U 盘总计	148000
总计	370000

要解决此场景，请按照以下步骤操作：

开始录制（文件命名为“import_txt”）。
点击“数据”菜单。
选择“导入外部数据”|“导入数据”。
选择“sales.txt”。
选择数据类型为“分隔符”，如图 15.14 所示图 15.14（1）。
点击“下一步”。
选择分隔符（逗号），如图 15.14 所示图 15.14（2）。
点击“下一步”。

图 15.14 场景 7 解决方案

选择数据类型，如图 15.15 所示图 15.15。
点击“完成”。
选择现有的工作表选项，如图 15.15 所示图 15.15。
点击“确定”。
使用导入的数据创建一个数据透视表并将其放置在新工作表中。
停止录制。

图 15.15 场景 7 解决方案

场景 8

参考训练文件 Training File4.xls。

目的	原始代码	修改后的代码
对于文件选择	ActiveSheet.QueryTables.Ad d(Connection:=”TEXT;c:\vb a\sales.txt”,Destination:=Ran ge(“A1”))	ActiveSheet.QueryTables. Add(Connection:=”TEXT;” & Application.GetOpenFilename, Destination:=Range(“A1”))
对于可变长度数据	ActiveWorkbook.PivotCache s.Add(SourceType:=xlDatab ase, SourceData:= “Sheet31!R1C1:R7C3”).CreatePivotTable	ActiveWorkbook.PivotCaches.A dd(SourceType:=xlDatabase, SourceData:=Range(“a1”).CurrentRegion.A ddress).CreatePivotTable

练习 1

创建一个宏（Report_title），该宏将在现有工作簿的单元格地址中添加以下详细信息。

A2: 您的公司名称 A3: 标题“每日报告” A4: 日期

A6: 序号

B6: 产品

C6: 销售数量

按照以下步骤进行解决方案：

点击“工具”|“宏”。
选择“录制新宏”。
将宏的名称输入为“Report_title”。
分配一个快捷键。
选择存储位置。
在“描述”框中添加详细信息。
点击“确定”。
添加一个空白工作表（Shift+F11 或插入菜单|工作表）。
点击 A2 单元格并输入您的公司名称。
点击 A3 单元格并输入“每日报告”作为标题。
点击 A4 单元格并输入“=today()”。
从 A6 到 C6，输入“序号”、“产品”和“销售数量”。
停止录制。

练习 2

创建一个宏，用于在数据末尾添加签名（您的姓名和职位）。

注意：检查工作表格式是否与练习 1 相同。

此宏总是在当前工作表之前添加一个工作表。修改此宏，使其在当前工作表之后添加新工作表。

按照以下步骤进行解决方案：

点击“工具”|“宏”。
选择“录制新宏”。
将宏的名称输入为“summary”。
分配一个快捷键。
选择您想要存储宏的位置。
在“描述”框中添加详细信息。
点击“确定”。
点击 A6 单元格。
使用 Ctrl + 向下箭头键到达数据末尾。
点击相对引用。
现在向下移动三行，输入您的姓名和职位。
关闭相对引用。
停止录制。
将“sheets.add”语句修改为“sheets.add after:=activesheet”。

结论

在本章中，我们讨论了在 Excel 中为宏分配按钮的主题。我们学习了如何在快速访问工具栏上创建按钮，并通过修改菜单或按钮来自定义它们。此外，我们还探讨了直接在 Excel 工作表中创建按钮的过程。我们还讨论了编辑记录的宏以增强其功能。

练习

创建一个宏，提取基于区域的工资值总和并将其添加到新工作表中。
创建一个宏，根据部门和地区过滤记录。
从文本文件导入数据并创建数据透视表报告。
修改现有宏中文件选择和变量长度数据的代码。
练习创建一个宏，在工作表中添加每日报告标题。
练习创建一个宏，在数据末尾添加签名。

第十六章

VBA 中的函数和子程序

简介

本章将介绍 Excel VBA 编程中函数和子程序的概念。它解释了两者之间的区别，并深入探讨了使用 Visual Basic 编辑器在模块内编写代码。本章还涵盖了分支技术，用于控制过程内代码执行的流程。

结构

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

■编写过程

■Visual Basic 编辑器

■插入模块

■在模块内编写代码

■子过程

■函数过程

■过程分支

目标

学习本章后，读者将能够编写过程并理解 Visual Basic 编辑器，这将用于插入模块、编写代码等。读者还将了解子过程、函数过程和过程分支。

编写过程

您可以为记录的每个动作编写代码。所有过程都编写在模块内。

要编写代码，您需要打开 Visual Basic 编辑器。打开 Visual Basic 编辑器的快捷键是 Alt + F11。

模块是一系列过程的集合。有两种类型的过程：

■子过程：子过程用于自动化 Excel 操作。子过程是位于 Sub 和 End Sub 块之间的代码单元。没有参数的子过程是宏。

■函数过程：函数用于自动化任何复杂的计算。函数过程位于 Function 和 End Function 块之间。

子过程和函数过程的区别在表 16.1 中进行了探讨。

表 16.1 过程的区别

子过程	函数过程
它不能返回值。	它可以返回值。
它可以在 Excel 对象上执行操作。	它不能在 Excel 对象上执行操作。

这里有一些额外的要点您可以记住：

■两种类型的过程可能具有参数，也可能没有参数。

■无参数的子过程是宏。

■所有宏都是过程，但并非所有过程都是宏。

Visual Basic 编辑器

图 16.1 显示了 Visual Basic 编辑器中的代码窗口：

图 16.1 Visual Basic 编辑器中的代码窗口

在 Visual Basic 编辑器中的代码窗口的不同部分如下：

■项目资源管理器：此部分显示项目（Excel 工作簿）的分层列表以及每个项目包含和引用的所有项目。

■属性窗口：此窗口列出所选对象的设计时属性及其当前设置。您可以在设计时更改这些属性。当您选择多个控件时，属性窗口包含所有选定控件共有的属性列表。

■代码窗口：使用代码窗口编写、显示和编辑 Visual Basic 代码。您可以根据模块数量打开多个代码窗口。您可以轻松查看不同表单或模块中的代码，并在它们之间复制和粘贴。

图 16.2 更详细地探讨了项目资源管理器。

■查看代码：显示代码窗口，以便您可以为选定的项目编写和编辑代码。

■查看对象：显示所选项目的对象窗口，例如现有的文档或用户表单。

■切换文件夹：在显示包含其中的单个项目的同时，隐藏和显示对象文件夹。

■列表窗口：列出加载的项目以及每个项目包含的项目。

属性是任何对象的特性。图 16.2 中所示的属性窗口显示了所选对象的属性。

图 16.2 项目资源管理器和属性窗口

让我们来看看代码窗口的不同部分（参见图 16.3）。

■对象框：此视图显示当前项目中的对象列表。

■过程窗口：此窗口包含当前模块的所有过程或所选对象的全部事件。

■过程视图：此视图一次只显示一个过程。

■完整模块视图：此选项显示当前模块的所有过程。

参考图 16.3。

图 16.3 代码窗口

项目资源管理器键盘快捷键

让我们来看看各种键盘快捷键：

■ENTER + Æ: 从列表中打开所选文件，或展开和折叠列表以显示其子条目。

■SHIFT+ENTERÆ: 为选定的文件打开代码窗口。

■F7 + Æ: 为选定的文件打开代码窗口。

■SHIFT+F10 Æ: 查看快捷菜单。

■HOME + Æ: 选择列表中的第一个文件。

■END + Æ: 选择列表中的最后一个文件。

■RIGHT ARROW + Æ: 展开列表，然后每次按它时选择列表中的子条目。

■向左箭头 + Æ：选择列表中的一个子条目，然后每次按它时都会向上移动列表，直到子条目列表折叠成一个文件夹。

■向上箭头 + Æ：每次移动列表中的一个条目。

■向下箭头 + Æ：每次移动列表中的一个条目。

插入模块

要插入一个模块，请按照以下步骤操作。

选择你想要存储你的过程的书籍，如图 16.4 Figure 16.4 所示（1）。
选择插入菜单，然后选择模块，如图 16.4 Figure 16.4 所示（2）。
模块 1 已添加。你可以通过属性窗口更改其名称，如图 16.4 Figure 16.4 所示（3）。

参考图 16.4 Figure 16.4。

图 16.4 插入模块

模块内编写代码

要在模块中编写代码，请按照以下步骤操作：

双击你想要为这些过程编写代码的模块，如图 16.5 Figure 16.5 所示（a）。
编写你的代码，如图 16.5 Figure 16.5 所示（b）。参考图 16.5 Figure 16.5。

图 16.5 模块内编写代码

子过程

子过程是一系列由子过程和结束子过程语句包围的 Visual Basic 语句，执行操作但不返回值。

子过程可以接受参数，例如常量、变量或由调用过程传递的表达式。

如果子过程没有参数，子过程语句必须包含一个空的括号集合。

宏

该宏如下：

Sub HelloWorld()

消息框 "Hello World"

结束子过程

Figure 16.6 包含一个宏。

图 16.6 宏

函数过程

函数过程是一系列由函数和结束函数语句包围的 Visual Basic 语句。

函数过程类似于子过程，但函数还可以返回一个值。函数过程可以接受由调用过程传递给它的参数。

如果一个函数过程没有参数，它的函数声明必须包含一个空的括号集合。一个函数通过在过程的一个或多个语句中将值赋给其名称来返回一个值。

例如：

函数 Celsius (fDegrees)

摄氏度 = (华氏度 - 32) * 5 / 9

结束函数

参考图 16.7 Figure 16.7。

图 16.7 用户定义函数

写函数的语法

注意：参数是你希望从最终用户那里获取的输入，以计算结果。

函数 name_of_function(参数 1, 参数 2, …)

处理参数 name_of_function

= 结果

结束函数

这两个过程可能都有参数，也可能没有。

按值传递 Æ：如果你按值传递一个参数，被调用的过程只接收从调用过程传递过来的变量的一个副本。如果被调用的过程改变了值，这个改变只会影响副本，而不会影响调用过程中的变量。

通过引用传递参数 Æ: 如果在调用过程时通过引用传递参数，则过程可以访问内存中的实际变量。因此，变量的值可以通过过程进行更改。默认情况下，参数是通过引用传递的。

场景 9

编写一个根据销售价格和成本价格计算利润的函数。利润函数需要两个参数：成本价格和销售价格。

Function Profit(CP, SP)

Profit = SP - CP

End Function

参考培训文件 5.xls。

分支过程

如果要根据条件的值运行代码块，可以使用以下决策结构。

■¾ If...Then...Endif

■¾ If...Then...Else…Endif

■¾ If...Then...Elseif…Then…Else…Endif

■¾ Select Case …End Select

使用 If…Then...Endif

单个条件运行一个语句或一组语句。

使用 If...Then...Else…Endif

单个条件运行两个不同的语句或一组语句，具体取决于条件的结果。

使用 If...Then...Elseif…Then…Else…Endif OR Select Case… End

这种结构选择多个条件并运行几个语句块中的一个。

场景 10

编写一个检查一个人是否有资格投票的函数。要检查资格，Vote 函数需要年龄作为参数。

Function Vote(Age)

If Age >= 18 Then

Vote = "Eligible"

Else

Vote = "not eligible"

EndIf

End Function

参考培训文件 5.xls。

场景 11

编写一个根据基本工资和给定标准计算员工等级的函数（使用 If Elseif）：

Grade Salary

D <8000

C 8000 – 15000

B 15000 – 25000

A >=25000

Function Grade (salary)

If salary<8000 Then

Grade = "D"

ElseIf salary<15000 Then

Grade = "C"

ElseIf salary<25000 Then

Grade = "B"

Else

Grade = "A"

End If

End Function

参考培训文件 5.xls。

场景 12

编写一个根据等级计算奖金的函数（使用 Select Case）。等级奖金：

A 25000

B 20000

C 15000

D 10000

参考培训文件 5.xls。

Function bonus(grade)

Select Case grade

Case "a", "A"

bonus = 25000

Case "b", "B"

bonus = 20000

Case "c", "C"

bonus = 15000

Case Else

bonus = 10000

End Select

End Function

场景 13

编写一个根据地区计算 DA 的函数。如果地区是东部或西部，则 DA 为 5%，否则为 10%。使用 OR 运算符检查多个条件。

OR 运算符

The OR operator is used to perform a logical conjunction on two expressions. It returns true if any of the expression results are true.

Function CalcDa(Region, sal)

If Region = "east" Or Region = "west" Then

CalcDa = sal * 0.05

Else

CalcDa = sal * 0.1

EndIf

End Function

场景 14

编写一个根据地区计算 DA 的函数。如果地区是东部且工资大于 10000，则 DA 为 5%。否则，为 10%。

使用 AND 运算符检查多个条件。

AND 运算符

此运算符用于对两个表达式执行逻辑合取。AND 运算符在所有表达式都返回 true 时返回 true。

Function CalcDa(Region, sal)

If Region = "east" And sal > 10000 Then

CalcDa = sal * 0.05

Else

CalcDa = sal * 0.1

EndIf

Conclusion

函数和子程序是 VBA 中的强大工具，可以帮助您自动化任务并在 Excel 中执行计算。通过了解如何编写过程、使用 Visual Basic 编辑器和应用分支技术，您可以提高您的 VBA 编程技能，并创建更高效和动态的 Excel 应用程序。

Exercises

编写一个函数，根据矩形的长度和宽度计算其面积。
创建一个子程序，根据特定条件格式化单元格范围，例如突出显示高于特定阈值的值。
开发一个函数，将华氏温度转换为摄氏温度。
编写一个子程序，按升序排序数据列。
创建一个函数，用于计算给定数字的阶乘。

第十七章

VBA 中的条件语句

Introduction

在 Visual Basic for Applications (VBA) 中，条件语句用于根据某些条件在代码中做出决策。它们允许您根据逻辑表达式的结果执行不同的代码块。在本章中，我们将重点介绍 VBA 中常用的两个条件语句：Select Case 和 If...End If。

Structure

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

■If…End If

■Select Case

■Select Case 与 If…End If

Objectives

到本章结束时，读者将了解 VBA 中 Select Case 和 If...End If 语句的区别，并学习在不同场景中它们的适当用法。

If…End If

在 VBA 中，If...End If 语句用于评估条件，如果条件为真，则执行代码块。它还可以与 ElseIf 和 Else 子句结合使用，以处理多个条件。以下是 If...End If 语句的基本语法示例：

If condition1 Then

' 如果 condition1 为真，则执行以下代码块

ElseIf condition2 Then

' 如果 condition2 为真且 condition1 为假，则执行以下代码块

Else

' 如果前面的条件都不满足，则执行以下代码块

True

End If

在上述示例中，“condition”和“condition2”表达式是布尔表达式，其结果为真或假。

需要注意的是，“If…End If 语句可以嵌套在其他控制结构（如循环）中，并且可以与其他语句和关键字结合使用，以在 VBA 代码中创建更复杂的逻辑。

Example

如果分数等于或大于 90，则显示“Grade: A。”如果分数在 80 到 89 之间，则显示“Grade: B”，依此类推。如果分数不符合任何指定条件，则显示“Grade: F。”

Sub GradeEvaluation()

Dim score As Integer

' 提示用户输入分数 score = InputBox("Enter the score:")

' 如果分数大于等于 90，则评估分数并提供相应的等级 If score >= 90 Then

MsgBox "Grade: A"

ElseIf score >= 80 Then

MsgBox "Grade: B"

ElseIf score >= 70 Then

MsgBox "Grade: C"

ElseIf score >= 60 Then

MsgBox "成绩：D"

Else

MsgBox "成绩：F"

End If

End Sub

Select Case

VBA 中的 Select Case 语句提供了一种简洁且结构化的方式来处理多个条件，并根据单个表达式的值执行不同的代码块。以下是其语法和用法的全面解释：

Select Case 表达式

Case value1

' 如果表达式与 value1 匹配时执行的代码

Case value2

' 如果表达式与 value2 匹配时执行的代码

Case Else

' 如果表达式不匹配任何之前的案例时执行的代码

End Select

示例

在这个例子中，程序提示用户输入一个水果名称。然后 Select Case 语句评估输入并根据所选的水果显示相应的消息。如果选择的是“apple”，它会显示这是一项健康选择的消息。如果选择的是“banana”，它会提到钾含量。如果选择的是“orange”或“mandarin”，它会强调柑橘类水果的好处。对于任何其他输入，它会显示一个通用的消息。

Sub FruitSelection()

Dim fruit As String

' 提示用户输入一个水果名称 fruit = InputBox("Enter a fruit name:")

' 评估水果名称并显示相应的消息 Select Case fruit

Case "apple"

MsgBox "您选择了一个苹果。这是一个健康的选择！"

Case "banana"

MsgBox "您选择了一个香蕉。它是钾的极佳来源。"

Case "orange", "mandarin"

MsgBox "您选择了一个橙子或柑橘。享受柑橘的美味！"

Case Else

MsgBox "这是一个有趣的选择！"

End Select

End Sub

Select Case 与 If … End If 的比较

使用 Select Case 语句作为 If...Then...Else 语句中 ElseIf 的替代方案，当比较一个表达式与几个不同的值时。

虽然 If...Then...Else 语句可以为每个 ElseIf 语句评估不同的表达式，但 Select Case 语句只在一个控制结构的顶部评估一次表达式。

结论

在本章中，我们探讨了 VBA 中 Select Case 和 If...End If 语句之间的区别。我们了解到，当您需要对单个表达式进行多个条件评估时，Select Case 是一个有用的替代方案。它简化了您的代码并提高了可读性。当您考虑的是明确且无关的条件时，If...End If 语句更为合适。

练习

编写一个 VBA 程序，提示用户输入一周中的某一天（作为从一到七的数字），然后显示相应的星期名称。使用 Select Case 和 If...End If 语句来实现该程序。
编写一个 VBA 程序，提示用户输入一个数字，并确定它是正数、负数还是零。使用 Select Case 和 If...End If 语句来实现该程序。

第十八章

VBA 中的变量和数据类型

简介

在本章中，我们将探讨 VBA（Visual Basic for Applications）中变量和数据类型的基础。变量是编程中的基本元素，在程序执行过程中存储和操作数据，而数据类型定义了存储在变量中的数据的性质。理解变量和数据类型对于编写高效和有效的 VBA 代码至关重要。我们将涵盖诸如声明变量和常量、指定数据类型、使用消息框和输入框、选择单元格、行和列以及与工作表、工作簿和应用程序对象一起工作等主题。

结构

在本章中，我们将涵盖以下主题：

■变量和常量

■声明变量和常量

■变量和常量的数据类型

■消息框和输入框

■选择和激活单元格

■选择和激活行和列

■与工作表一起工作

■与工作簿一起工作

■与应用程序对象一起工作

目标

到本章结束时，读者将能够理解 VBA 中变量和常量的概念以及如何声明它们，熟悉 VBA 中可用的不同数据类型及其相应的范围，并了解使用消息框和输入框进行用户交互的用法。此外，读者还将学习在 Excel 中选择和激活单元格、行和列的技术，并获得在 VBA 中与工作表、工作簿和应用程序对象一起工作的知识。

变量和常量

让我们现在来了解变量和常量。

变量

变量的特性如下：

■变量是一个命名存储位置，包含在程序执行过程中可以修改的数据。

■每个变量都有一个名称，它在它的作用域内是唯一的。

■数据类型可以指定也可以不指定。

■变量名：

•必须以字母字符开头，

•必须在同一作用域内是唯一的，

•不能超过 255 个字符，并且

•必须包含一个嵌入的点或类型声明字符。

常量

常量是一个在程序执行过程中保持恒定值的命名项。常量可以是字符串或数值字面量。

声明变量和常量

声明变量的语法是：

DIM name_of_variable AS type_of_variable

例如：

Dim strName As String

Dim intX As Integer

Dim intX , intYAs Integer

声明常量的语法是：

Const name_of_variable AS type_of_variable = constant value

例如，Const conAge As Integer = 34.

在声明变量时，使用 Dim 语句。对于常量，使用 Const 语句。

声明语句可以放在过程中以创建过程级变量。它也可以放在模块的顶部，在声明部分中，以创建模块级变量。

变量和常量的数据类型

表 18.1 显示了数据类型的各种范围。

表 18.1 数据类型

数据类型	范围
字节	0 到 255。
整数	– 32,768 到 32,767。
长整型	– 2,147,483,648 到 2,147,483,647。
单精度浮点数	– 3.402823E38 到 – 1.401298E – 45（负值）。
1.401298E – 45 到 3.402823E38（正值）。
双精度浮点数	– 1.7200369313486231E308 到 – 4.94065645841247E – 324（负值）。4.94065645841247E – 324 到 1.7200369313486231E308（正值）。
货币	– 922,337,203,685,477.5808 到 922,337,203,685,477.5807。
字符串	零到约二十亿个字符。
变体	日期值：100 年 1 月 1 日到 9999 年 12 月 31 日。
数值：与双精度浮点数相同的范围。
字符串值：与字符串相同的范围。
也可以包含错误或空值。
布尔值	True 或 False。
日期	100 年 1 月 1 日到 9999 年 12 月 31 日。
对象	任何对象引用。

使用 Option Explicit 语句

使用 Option Explicit 强制显式声明变量。它必须出现在模块中，在所有过程之前。如果不使用，未声明的变量将为 Variant 类型。

消息框和输入框

Msgbox 函数在对话框中显示消息，等待用户点击按钮，并返回一个整数，表示用户点击了哪个按钮。

InputBox 函数在对话框中显示提示，等待用户输入文本或点击按钮，并返回一个包含文本框内容的字符串。

示例：

Sub Greet()

MsgBox "Hello " & InputBox("What is your name?")

结束子程序

选择和激活单元格

当你使用 Microsoft Excel 时，你通常会选择一个或多个单元格，然后执行操作，例如格式化单元格或在其中输入值。

请参阅表 18.2 以编写各种操作的代码。

表 18.2 各种操作的代码

要这样做	编写此代码
选择单元格 A1	Range("A1").select 或 Cells(1,1).select
选择范围 A1:B5	Range("A1:B5").select
选择范围 A1:A5 和 C2:C10	Range("A1:A5, C2:C10").select
选择当前单元格	Activecell.select
从当前单元格到 B6 选择范围	Range(Activecell, "B6").select
选择活动单元格的当前区域	Activecell.CurrentRegion.select
从活动单元格按 Ctrl + Shift + 向下箭头	Range(ActivecellActivecell.End(XlDown)).select
从单元格 A2 按 Ctrl + Shift + 向下箭头	Range("A2", Activecell.End(XlDown)).Select

选择和激活行和列

有时你需要选择特定的行和列，然后执行操作。

要这样做，请编写以下表 18.3 中显示的代码。

表 18.3 各种操作的代码

要这样做	编写此代码
选择一行	Rows("2:2").select
从 2 到 5 选择行	Rows("2:5").select
从活动单元格选择 3 行	Activecell.EntireRow.Range("1:3").select
选择一列	Columns("A:A").select
从 B 到 E 选择列	Columns("B:E").select
从活动单元格选择 3 列	Activecell.EntireColumn.Range("A:C").select
选择当前行	Activecell.EntireRow.select
选择当前列	Activecell.entirecolumn.select

在工作表中工作

你将经常需要选择一个特定的工作表，插入一个新的工作表，重命名一个工作表，等等。请参阅表 18.4。

TABLE 18.4 各种操作的代码

要做这件事	编写此代码
通过索引号选择任何工作表	Sheets(2).selectWorksheets(2).select
通过名称选择任何工作表	Sheets("Sheet1"). selectWorksheets("Sheet1").select
重命名工作表	Sheets("Sheet1").name="newname"
分配一个新名称	Worksheets("Sheet1").name=Activesheet.name
删除一个工作表	Sheets("Sheet1").delete Worksheets("Sheet1").deleteActivesheet.delete
插入一个工作表	Sheets.add before:= sheets("Sheet1") Worksheets.add before:=sheets("Sheet1")

在工作簿中工作

有时你需要处理不同的工作簿。请参阅表 18.5。

TABLE 18.5 各种操作的代码

要做这件事	编写此代码
打开一个工作簿	Workbooks.open filename:="filename with path"
打开包含自动宏的工作簿	Workbooks. openfilename: =" Activeworkbook. runautomacros"
关闭一个工作簿	Workbooks(2).close
添加一个新的工作簿	Workbooks.add

在应用程序对象中工作

有时你可能需要忽略一些 Excel 消息。为此，你需要与应用程序对象一起工作，如表 18.6 所示。

TABLE 18.6 各种操作的代码

要做这件事	编写此代码
关闭消息显示	Application.DisplayAlert = False
停止屏幕闪烁	Application.ScreenUpdating = False
停止复制/剪切模式	Application.CutCopyMode = False
计算	Application.Calculate

Scenario 15

创建一个宏，它应该接受一个人的姓名和城市，并将其存储在 Excel 工作表的 A1 和 B1 单元格中。如果用户输入“孟买”作为城市，字体颜色必须是红色。使用 InputBox 函数从用户那里获取输入。使用 MsgBox 函数显示结果。

Sub Accept_Details()

Dim e_Name , e_City As String

Name = InputBox("输入您的名字")

City = InputBox("输入您的城市")

MsgBox "您的名字是 " & Name & " 并且城市是 " & City Cells(1, 1).Value = e_Name Cells(1, 2).Value = e_City If Cells(1, 2).Value = "mumbai" Then

Cells(1, 2).Font.ColorIndex = 3

Else

Cells(1, 2).Font.ColorIndex = 0

EndIf

End Sub

Scenario 16

创建一个宏并命名为“Data_Entry”。它必须接受员工的代码、姓名、雇佣日期和薪水。将值插入“数据库”工作表。每个新记录都必须存储在最后一个记录之后。

Sub Data_Entry()

Dim EmpCode As integer, Next_Row as integer

Dim EmpName As String

Dim doj As Date

Dim Salary As Currency

EmpCode = InputBox("输入员工代码")

EmpName = InputBox("输入员工姓名")

doj = InputBox("输入加入日期 mm/dd/yy")

Salary = InputBox("输入员工薪水")

Range("a65536").select

Selection.end(xlup).select Next_Row= activecell.row+1

Cells(Next_Row, 1).Value = EmpCode Cells(Next_Row, 2).Value = EmpName Cells(Next_Row, 3).Value = Format(doj, "MMM DD YYYY") Cells(Next_Row, 4).Value = Salary

End Sub

结论

总之，本章全面概述了 VBA 中的变量和数据类型。它涵盖了变量的声明和常量的解释，不同数据类型及其范围，演示了消息框和输入框的使用，并探讨了在 Excel 中选择和操作单元格、行和列的技术。本章还简要介绍了与工作表、工作簿和应用程序对象一起工作的方法。通过理解这些基础知识，读者可以编写高效且有效的 VBA 代码。

练习

编写一个 VBA 宏，提示用户通过输入框输入他们的姓名、年龄和最喜欢的颜色。该宏应将值存储在活动工作表的 A1、B1 和 C1 单元格中。此外，如果用户的年龄大于或等于 18 岁，则相应单元格的字体颜色应设置为他们的最喜欢的颜色。通过运行宏并输入不同的值来测试该宏。

第十九章

VBA 中的循环结构

简介

在本章中，我们将深入研究 Visual Basic for Applications (VBA)中的循环结构。循环是强大的工具，能够使代码重复执行，提高效率和自动化任务。本章探讨了不同类型的循环，如 Do...Loop、For...Next 和 For Each...Next 循环，以及基于特定事件自动执行的宏。

结构

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

■使用循环（重复操作）

■使用 Do…Loop 语句

■使用 For…Next 语句

■使用 For Each…Next 语句

■自动执行宏

目标

到本章结束时，读者将学习 VBA 中的循环结构，如 Do...Loop、For...Next 和 For Each...Next。本章还涵盖了其实际应用示例。

使用循环（重复操作）

循环允许你重复运行一组语句。有些循环在条件为 False 时重复语句；有些循环在条件为 True 时重复语句。还有循环会根据特定次数或集合中的每个对象重复语句。

选择要使用的循环

你可以使用各种循环，例如：

■Do…Loop：在条件为 True 或变为 True 时循环。

■For…Next：使用计数器运行指定次数的语句。

■For Each…Next：对集合中的每个对象重复一组语句。

使用 Do…Loop 语句

你可以使用 Do...Loop 语句运行一个不定次数的语句块。这些语句要么在条件为 True 时重复，要么在条件变为 True 时重复。

语法：

Do [{While | Until} condition]

[语句]

[退出 Do]

[语句]

循环

当条件为 True 时重复语句

在 Do...Loop 语句中，有两种使用 While 关键字检查条件的方式。您可以在进入循环之前检查条件，或者可以在循环至少运行一次之后检查它。

进入循环之前检查条件

在进入循环之前检查条件的语法是：

DO WHILE (condition)

重复执行的代码

LOOP

循环至少运行一次后的条件检查

检查循环至少运行一次后的条件的语法是：

重复执行的代码

LOOP WHILE (condition)

场景 17

编写代码以接受和验证用户名。不允许空白名称。请参阅 Training File5.xls。

参阅图 19.1。

图 19.1 场景 17

Sub validate_name()

Dim name As String

name = InputBox("输入您的姓名")

Do While Trim(name) = ""

MsgBox "姓名不能为空"

name = InputBox("输入您的姓名")

循环

End Sub

注意：Trim 函数会从单词的开头和结尾删除空格。

使用 For…Next 语句

您可以使用 For...Next 语句重复执行一组语句特定次数。

■For 循环使用一个计数器变量，其值在每个循环的内部重复中增加或减少。

语法：

FOR counter_variable = initial_value TO Final_Value

STEP step_value 重复执行的代码

注意：数据类型越小，更新所需的时间越少。

场景 18

创建一个名为“fill_series”的宏，以显示从 1 到 10 的数字（从单元格 A1 开始）。

Sub fill_series()

Dim fill_val As Integer

Range("A1").Select For

fill_val = 1 To 10

ActiveCell.Value = fill_val

ActiveCell.Offset(1, 0).Select Next

End Sub

Offset 函数()用于指向或引用对象上方、下方、左侧或右侧的对象。

语法：

OFFSET(row, column) 示例

Activecell.Offset(1,0).select：这将选择位于 Activecell 下方 1 行和右侧 0 列的单元格。
Activecell.Offset(0,1).select：这将选择位于 Activecell 下方 0 行和右侧 1 列的单元格。
Activecell.Offset(-1,0).select：这将选择位于 Activecell 下方 1 行和右侧 0 列的单元格。
Activecell.Offset(0,-1).select：这将选择位于 Activecell 下方 0 行和左侧 1 列的单元格。

使用 For Each… Next 语句

For Each...Next 语句会为集合中的每个对象或数组中的每个元素重复执行一组语句。

每次循环运行时，Visual Basic 会自动设置一个变量。

可以在任何位置放置任意数量的 Exit For 语句，作为退出循环的另一种方式。

语法：

For Each 元素 In group

[语句]

[退出 For]

[语句]

Next [element]

必需：用于遍历集合或数组元素的变量。对于集合，元素只能是一个 Variant 变量、一个通用对象变量或任何特定对象变量。对于数组，元素只能是一个 Variant 变量。

组：必需。对象集合或数组的名称

语句

可选。在组中的每个项目上执行的一个或多个语句。

场景 19

创建一个名为“UPPER_CASE”的宏，将数据转换为大写字母。使用 Ucase()函数将文本转换为大写字母。

参见图 19.2。

图 19.2 场景 19

Sub UPPER_CASE()

Dim wscell As Range

For Each wscell In Selection

wscell.Value = UCase(wscell.Value)

End Sub

场景 20

创建一个名为“lower_case”的宏，将数据转换为小写字母。使用 lcase()函数将文本转换为小写字母。

参见图 19.3。

图 19.3 场景 20

Sub lower_case()

Dim wscell As Range

For Each wscell In Selection

wscell.Value = LCase(wscell.Value)

End Sub

场景 21

创建一个名为“Proper_case”的宏，将数据转换为标题大小写字母。使用 WorksheetFunction 对象在 VBA 中使用 Excel 中的任何函数。参见图 19.4。

图 19.4 场景 21

Sub Proper_Case()

Dim wscell As Range

For Each wscell In Selection

wscell.Value = Application.WorksheetFunction.Proper(wscell.Value)

End Sub

场景 22

打开场景 22 并修改它。在存储录制好的宏后，你应该询问用户是否继续，并根据用户响应执行。如果用户点击“确定”，则继续数据输入。如果用户点击“取消”，则显示“谢谢”并结束宏。参见图 19.5。

图 19.5 场景 22

Sub Data_Entry1()

Dim EmpCode As Integer, next_row As Integer

Dim EmpName As String

Dim doj As Date

Dim Salary As Currency

选择工作表“database”

选择“a65536”范围内的单元格

选择 Selection 的上一个单元格

next_row = ActiveCell.Row + 1

EmpCode = InputBox("输入员工代码")

EmpName = InputBox("输入员工姓名")

doj = InputBox("输入入职日期 mm/dd/yy")

Salary = InputBox("输入员工工资")

Cells(next_row , 1).Value = EmpCode

Cells(next_row , 2).Value = EmpName

Cells(next_row , 3).Value = Format(doj, "MMM DD YYYY")

Cells(next_row , 4).Value = Salary

next_row =next_row + 1

Loop While (MsgBox("你想继续吗?", vbOKCancel) = vbOK)

MsgBox "谢谢"

End Sub

场景 23

创建一个宏，为每个员工计算以下内容：

■HRA（工资的 75%）

■DA（工资的 60%）

■TOTAL（工资 + HRA + DA）

参见图 19.6。

图 19.6 场景 23

解决这个问题可能有两种方法。

参考 Training File6.xls。

通过宏，你可以将公式放入单元格中：

Sub Gross_Salary()

'用户将选择 H2:H101 范围内的单元格 As Range。

For Each wscell In Selection

wscell.Offset(0, 1).Value = "=rc[-1]*75%"

wscell.Offset(0, 2).Value = "=rc[-2]*60%"

wscell.Offset(0, 3).Value = "=sum(rc[-1]:rc[-3])"

End Sub

使用你的宏进行计算，并将结果仅放入单元格中：

Sub Gross_Salary()

用户将选择范围 H2:H101 Dim wscell As Range

For Each wscell In Selection

wscell.Offset(0, 1).Value= wscell.Value * .75

wscell.Offset(0, 2).Value = wscell.Value * 60%

wscell.Offset(0, 3).Value = wscell.value + wscell.Offset(0, 1).Value + wscell.Offset(0, 2).Value

End Sub

情景 24

创建一个宏来显示当前工作簿中工作表名称的列表。参考图 19.7。

图 19.7 情景 24

Sub list_sheets()

„ 声明一个工作表对象变量

Dim sht As Worksheet

For Each sht In Worksheets

ActiveCell.Value = sht.name

ActiveCell.Offset(1, 0).Select

End Sub

自动执行的宏

语法是：

fydyr

参考表 19.1(#tab19-1)。

表 19.1 自动执行的宏

到	使用
运行宏，当工作簿打开时立即执行	Sub auto_open()End Sub
运行宏，当工作簿关闭时立即执行	Sub auto_close()End Sub

练习 3

Function Search_sheet(newSht)

Dim sht As Worksheet

For Each sht In Worksheets

If UCase(sht.name) = UCase(newSht) Then

Search_sheet = "Sheet(" & newSht & ") exists"

Exit Function

End If

Search_sheet = "Sheet(" & newSht & ") does not exists"

End Function

编写一个函数（“Search_sheet”）来检查任何工作表的存在。

练习 4

编写一个宏来为每个员工增加 2000 元的薪水。

情景 25

创建一个宏来使用数据透视表生成基于地区和部门的薪水总和以及员工计数。修改代码，以便每个数据透视表都应从当前数据生成。

Sub Pivot_Summary()

Range("A2").Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=

_ Range("a2").CurrentRegion).CreatePivotTable TableDestination:="",

TableName:= _ "PivotTable2", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

ActiveSheet.Cells(3, 1).Select

ActiveSheet.PivotTables("PivotTable2").AddFields

RowFields:=Array("Region", _ "Dept", "Data")

With ActiveSheet.PivotTables("PivotTable2").PivotFields("salary")

.Orientation = xlDataField

.Position = 1

End With

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Empcode")

.Orientation = xlDataField

.Caption = "Empcode 计数"

.Function = xlCount

End With

Range("C3").Select

With ActiveSheet.PivotTables("PivotTable2").DataPivotField

.Orientation = xlColumnField

.Position = 1

End With

End Sub

参考训练文件 6.xls。

情景 26

编写代码以从“daily”工作表中删除在“master”工作表中存在的重复记录。（使用嵌套循环。）

解决方案 26

参考训练文件 7.xls。

Sub duplicates()

Dim wscell As Range, tcell As Range

SelectWorksheets("master")

Range("a2").Select

Range(ActiveCell, ActiveCell.End(xlDown)).Select

For Each wscell In Selection

SelectWorksheets("daily")

Range("a2").Select

Range(ActiveCell, ActiveCell.End(xlDown)).Select

For Each tcell In Selection

If tcell.Value = wscell.Value Then

tcell.EntireRow.Delete End If

ActiveCell.Select

End Sub

可以使用查找命令编写相同的代码，如下所示。

Sub duplicates_With_find()

Worksheets("master").Select

Rangec("a2").Select

Range(ActiveCell, ActiveCell.End(xlDown)).Select

For Each tcell In Selection

Worksheets("daily").Select

Set c = Cells.Find(What:=tcell.Value)

If Not c Is Nothing Then Rows(c.Row).Delete

End If

下一步

End Sub

情景 27

创建一个名为“Merging_Sheets”的宏，该宏将所有工作表中的数据复制到一个工作表中。

您的宏应生成一个用于按区域销售总量和合并后的员工代码的交叉表。

解决方案

Sub Merging_Sheets()

情景 27

'在末尾添加一个工作表并将其命名为 consolidate，并创建标题

Worksheets.Add After:=Worksheets(Worksheets.Count)

ActiveSheet.Name = "consolidate"

ActiveSheet.Range("a1").Select

Range("a1").Value = "产品"

Range("b1").Value = "销售"

从每个工作表复制数据到合并工作表

For Index = 1 To Worksheets.Count - 1

Worksheets(Index).Select

Range("a2").Select

Range(Selection, Selection.End(xlDown)).Select

Range(Selection, Selection.End(xlToRight)).Select

选择.复制

Worksheets("consolidate").Select

Cells(Range("a65536").End(xlUp).Row + 1, 1).Select ActiveSheet.Paste

接下来，生成合并数据的交叉表。

Sheets("consolidate").Select Range("A1").Select

Application.CutCopyMode = False

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _

Range("a1").CurrentRegion).CreatePivotTable TableDestination:="", TableName _

:="PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Product" ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").

Orientation = _ xlDataField

End Sub

结论

精通循环结构对于有效的 VBA 编程至关重要。通过使用循环，您可以自动化重复性任务，处理大量数据，并提高 VBA 程序的整体效率。本章提供了对循环及其在 VBA 中应用的全面概述，使您能够编写简洁而强大的代码，节省时间和精力。

练习

编写一个名为“PrintNumbers”的 VBA 宏，该宏在立即窗口中打印从 1 到 100 的数字。
创建一个名为“CalculateSum”的 VBA 宏，该宏计算从 1 到 10 的数字之和，并在消息框中显示结果。
编写一个名为“EvenNumbers”的 VBA 宏，该宏在立即窗口中打印从 1 到 20 的所有偶数。
创建一个名为“Factorial”的 VBA 宏，该宏计算给定数字的阶乘。该宏应提示用户输入一个数字，然后在消息框中显示阶乘结果。
编写一个名为“ReverseString”的 VBA 宏，提示用户输入一个字符串，然后在立即窗口中打印该字符串的逆序。
创建一个名为“TableOfSquares”的 VBA 宏，生成从 1 到 10 的平方表。该宏应在新的工作表中分别显示数字及其平方，并在不同的列中。
编写一个名为“CountCharacters”的 VBA 宏，用于计算给定字符串中的字符数。该宏应提示用户输入一个字符串，然后在消息框中显示计数。

第二十章

VBA 中的数组和集合

简介

数组和集合是 VBA 编程中的基本组件，能够高效地存储和操作多个值。在本章中，我们将探讨它们的概念，学习如何声明和使用它们，了解数组索引和动态数组，并检查它们在 VBA 中的应用实例。

结构

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

■数组

■声明数组

■使用数组

■数组索引

■声明动态数组

■调整动态数组大小

目标

到本章结束时，读者将了解 VBA 编程中数组和使用集合的目的，学习如何声明、调整大小和高效使用数组，并了解集合在高级数据操作任务中相对于数组的优势。

数组

数组是一组具有相同内在数据类型的顺序索引元素。数组的每个元素都有一个唯一的标识索引号。

对数组中一个元素的更改不会影响其他元素。不同类型的数组如下：

■大小已指定的数组是固定大小的数组。

■在程序运行时可以更改大小的数组是动态数组。

■单维数组只包含行。

■一个多维数组使用行和列。

声明数组

数组的声明方式与其他变量相同。

语法：

Dim name_Of_array(Size) As Data_Type

示例

单维：声明一个行大小为 10 的单维数组变量，可以存储整数值。

Dim Myarray(10) As Integer

多维（最大 60 维）：声明一个三行五列的多维数组变量，可以存储 15 个整数值。

Dim Myarray(3, 5) As Integer

使用数组

使用数组可以通过一个示例来解释。为了存储每月每一天的日常开支，你可以声明一个包含 31 个元素的数组变量，而不是声明 31 个变量。

数组中的每个元素包含一个值。

Sub Single_array()

Dim curExpense(31) As Currency

Dim intI As Integer

For intI = 0 to 31

curExpense(intI) = 20

End Sub

注意：在上面的示例中，数组索引将从零开始。

数组索引

所有数组索引从零开始。数组是否从 0 或 1 索引取决于 Option Base 语句的设置。

如果未指定 Option Base 1，则所有数组索引从零开始。

示例

Option Base 1

Sub Single_array()

Dim curExpense(31) As Currency

Dim intI As Integer

For intI = 1 to 31

curExpense(intI) = 20

End Sub

注意：在上面的例子中，数组索引将从 1 开始。

声明动态数组

通过声明动态数组，您可以在代码运行时调整数组大小。使用 Dim 语句声明数组，留空括号。

语法：

Dim Name_Of_Array() As Data_Type

调整动态数组大小

在过程中使用 ReDim 语句隐式声明数组。

使用 ReDim 语句时，请注意不要拼写数组名称错误。

数组示例

Option Base 1 ' 初始化数组索引为 1

Sub Searchdata()

Dim mycell_array() As String ' 声明动态数组

Dim a, i As Long

i = 1

Sheets(1).Select

a = Range("a65536").End(xlUp).Row - 1

ReDim mycell_array(a) ' 重新声明数组大小

Range("a2", Range("a2").End(xlDown)).Select

For Each mycell In Selection mycell_array(i) = mycell i

= i + 1

Sheets("database").Select

Range("a2", Range("a2").End(xlDown)).Select

i = 1

For i = 1 To a

For Each mycell In Selection

If mycell_array(i) = mycell Then

mycell.EntireRow.Delete 'mycell.Offset(0, 1).Value = "found"

End If

End Sub

结论

数组和集合是 VBA 编程中的强大工具，有助于管理数据集并提高代码效率。对这些概念有扎实的掌握，您可以优化 VBA 代码，提高可读性，并有效地处理复杂的数据结构。将数组和集合纳入您的编程工具箱将扩展您的功能，并使您能够处理更广泛的 VBA 项目。

练习

声明一个动态数组以存储五个员工的每日费用。
使用循环输入指定天数内每个员工的费用。
计算每个员工的总额并显示结果。
确定费用最高的员工并打印其姓名和相应的金额。
计算整个团队每天的平均费用并显示结果。

第二十一章

VBA 中的调试和错误处理

简介

在本章中，我们将探讨 Visual Basic for Applications (VBA)中调试和错误处理的重要概念。调试是识别和解决 VBA 代码中的运行时错误和逻辑错误的过程。错误处理涉及在代码执行过程中实施策略来处理和管理错误。通过理解这些概念并利用适当的技巧，我们可以创建更健壮和可靠的 VBA 宏。

结构

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

■错误

■错误处理

■错误编号

■调试宏

目标

到本章结束时，读者将了解错误、错误处理和错误编号，这些与调试一样，在 VBA 中至关重要。

错误

如果一个语句失败，将生成错误。有三种类型的错误：

■逻辑：当宏没有给出预期结果时。这些错误可以通过逻辑更改和试错法来处理

■技术：任何语句的运行时失败。使用 On Error 语句来处理这些错误。

■语法：这包括拼写错误的关键字、不匹配的括号以及各种其他错误。Excel 将标记你的语法错误，并阻止代码执行，直到它们被纠正。

错误处理

处理错误有三种方法：

■每当遇到错误时，将控制权直接导向特定的标签或代码行。

•On Error GoTo line / label On Error

■继续执行导致运行时错误的语句之后的语句。

•On Error Resume Next

■禁用当前过程中的任何启用的错误处理器。

•On Error GoTo 0

注意：错误处理例程不是一个子程序或函数程序。它是一段由行标签或行号标记的代码段。如果你没有使用 On Error 语句，任何发生的运行时错误都是致命的；将显示错误消息，并停止执行。

场景 28

打开场景 22。如果数据库工作表不存在，将生成错误。修改代码以处理此错误；如果数据库工作表不存在，你的程序应添加一个新的工作表。

注意：使用 On Error GoTo line / label。

解决方案

Sub Error_handling1()

On Error GoTo err_handler

Dim EmpCode As Integer, next_row As Integer

Dim EmpName As String

Dim doj As Date

Dim Salary As Currency

Worksheets("database").Select

Range("a65536").Select

Selection.End(xlUp).Select

next_row = ActiveCell.Row + 1

EmpCode = InputBox("Enter Employee Code") EmpName = InputBox("Enter Employee Name")

doj = InputBox("enter Date of Joining mm/dd/yy")

Salary = InputBox("Enter Salary of Employee")

Cells(next_row , 1).Value = EmpCode Cells(next_row , 2).Value = EmpName

Cells(next_row , 3).Value = Format(doj, "MMM DD YYYY")

Cells(next_row , 4).Value = Salary next_row =next_row + 1

Loop While (MsgBox("Do you want to continue?", vbOKCancel) = vbOK) MsgBox "Thanks"

Exit Sub

err_handler: Worksheets.Add

ActiveSheet.name ="database"

Range("a1").Value = "Emp Code"

Range("b1").Value = "Emp Name"

Range("c1").Value = "Date of Joining"

Range("d1").Value = "Salary"

Resume Next

End Sub

Error Number

每个运行时错误都有一个编号。如果你知道这个编号，你可以通过编号来捕获错误。例如，参考表 21.1。

TABLE 21.1 错误编号及其描述

错误编号	描述
61	磁盘满
4	应用程序定义或对象定义错误
7	内存不足
9	索引超出范围

尝试以下代码以查看特定错误编号的错误。

Sub Show_Error():

Dim ErrorNumber

For ErrorNumber = 61 To 64 ' 循环 61-64 的值。

Msgbox Error(ErrorNumber)

Next ErrorNumber

End Sub

场景 29

打开场景 28。如果你留空输入框，你的宏将生成错误。按照以下方式修改代码（使用 On error resume next）。

Sub Error_handling2()

Dim EmpCode As Integer, next_row As Integer

Dim EmpName As String

Dim doj As Date

Dim Salary As Currency

' 如果有任何错误，它应该继续执行下一行 On Error resume Next

Worksheets("database").Select Range("a65536").Select Selection.End(xlUp).Select next_row

= ActiveCell.Row + 1

EmpCode = InputBox("Enter Employee Code")

EmpName = InputBox("Enter Employee Name")

doj = InputBox("enter Date of Joining mm/dd/yy")

Salary = InputBox("Enter Salary of Employee")

Cells(next_row , 1).Value = EmpCode Cells(next_row , 2).

Value = EmpName

Cells(next_row , 3).Value = Format(doj, "MMM DD YYYY")

Cells(next_row , 4).Value = Salary next_row =next_row + 1

Loop While (MsgBox("Do you want to continue?", vbOKCancel) = vbOK) MsgBox "Thanks"

End Sub

调试宏

调试是查找和纠正运行时错误和逻辑错误的过程。按 F8 键逐行执行代码。

调试工具栏如图 21.1 所示。

图 21.1 调试工具栏

这里是各种工具栏按钮：

■设计模式：关闭和打开设计模式。

■运行子程序/用户表单或运行宏：如果光标在过程中，则运行当前过程；如果用户表单当前处于活动状态，则运行用户表单；如果没有活动代码窗口或用户表单，则运行宏。

■中断：在程序运行时停止执行并切换到中断模式。

■重置：清除执行堆栈和模块级别变量，并重置项目。

■切换断点：在当前行设置或移除断点。

■进入单步：逐条执行代码。

■单步执行：在代码窗口中逐个过程或语句执行代码。

■退出单步：执行当前执行点所在过程中的剩余行。

■局部变量窗口：显示局部变量窗口。

■立即窗口：显示立即窗口。

■监视窗口：显示监视窗口。

■快速监视：显示包含所选表达式当前值的快速监视对话框。

■调用堆栈：显示调用对话框，列出了当前活动的过程调用（已开始但未完成的应用程序中的过程）。

以下是一些帮助你将错误降到最低的技巧：

■使用 Option Explicit。这将强制你为每个使用的变量定义数据类型。这将避免常见的拼写变量名错误。

■使用缩进来格式化你的代码。如果你有多个嵌套的 For...Next 循环，一致的缩进将使跟踪它们变得容易得多。

■小心使用 On Error Resume Next。这个语句导致 Excel 忽略任何错误并继续执行。在某些情况下，使用这个语句会导致 Excel 忽略不应该忽略的错误。你可能存在错误而自己却未意识到。

■使用注释。养成写注释的习惯，这样当你再次查看代码时可以理解逻辑。添加一些注释来描述你的逻辑可以节省你大量时间。

■保持你的子程序和函数简单。将你的代码编写在更小的模块中，每个模块都有一个单一、明确的用途。

■使用宏录制器来识别属性和方法。如果你不记得属性或方法的名称或语法，录制一个宏，然后查看录制的代码。

■考虑不同的方法。如果你在使某个特定例程正常工作方面遇到困难，你可能想放弃这个想法，尝试完全不同的方法。在大多数情况下，Excel 提供了完成同一任务的几种替代方法。

■使用调试工具栏。

结论

调试和错误处理是 VBA 开发者必备的技能。通过有效地管理错误和调试我们的代码，我们可以创建更健壮且无错误的宏。应用最佳实践，如适当的代码格式化和注释，以及使用可用的调试工具将有助于高效地预防和解决错误。

练习

打开一个新的 Excel 工作簿，并导航到 Visual Basic 编辑器 (VBE)。
在 VBE 中，插入一个新的模块来编写 VBA 代码。
创建一个名为“CalculateStatistics”的子程序，该子程序不接受任何参数。
声明必要的变量以存储输入值和计算出的统计数据。
使用高级错误处理技术，如“On Error GoTo”、“On Error Resume Next”和“Err.Raise”来实现错误处理。
使用“Application.InputBox”方法提示用户从工作表输入一系列数字。
使用“WorksheetFunction”对象对所选范围执行以下计算：

a. 计算数字的总和。

b. 计算数字的平均值。

c. 计算范围内的最大值。

d. 计算范围内的最小值。

在单独的消息框中显示计算出的统计数据。
包括全面的错误处理，以处理诸如无效输入、空选择、范围中的非数值或除以零错误等场景。
通过使用不同的输入执行宏，并验证它是否有效地处理错误并提供准确的结果来测试宏。

第二十二章

VBS 中的用户表单和用户输入

简介

在 Visual Basic Scripting (VBS) 中，用户表单和用户输入是创建交互式和用户友好应用程序的基本方面。用户表单允许开发者设计直观的界面，并捕获用户输入以实现各种目的。通过利用标签、文本框、按钮等控件，VBS 开发者可以创建动态且响应灵敏的表单，从而提升整体用户体验。

在本章中，我们将探讨在 VBS 中创建用户表单的过程，包括设计表单布局、添加控件和处理事件。我们还将讨论如何将用户输入集成到脚本中，使应用程序能够响应用户操作并执行相关任务。

结构

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

■用户表单

■创建用户表单

■添加其他控制项

■处理控制项事件

目标

到本章结束时，读者将了解用户表单及其创建方法、添加其他控制项以及处理控制项事件。理解 VBS 中的用户表单和用户输入将使您能够构建满足用户特定需求的交互式和功能性的应用程序。

用户表单

用户表单可用于创建自定义对话框。参见图 22.1。

图 22.1 用户表单

创建用户表单

要创建用户表单，请按照以下步骤操作：

点击插入菜单。
如图 22.2 (a)所示，选择用户表单。
这将在您的工作簿中添加一个对象，UserForm1，如图 22.2 (b)所示。
使用属性窗口来更改表单的名称、行为和外观。例如，要更改表单上的标题，设置标题属性，如图 22.2 (c)所示。

参见图 22.2。

图 22.2 创建用户表单

添加其他控制项

要添加其他控制项，请按照以下步骤操作：

如图 22.3 (a)所示，在工具栏中点击工具箱。
将控制项拖动到表单上，如图 22.3 (b)所示。
从属性窗口更改属性，如名称、字体等，如图 22.3 (c)所示。

参见图 22.3。

图 22.3 添加其他控制项

处理控制项事件

要处理控制项事件，请按照以下步骤操作：

打开表单的代码窗口。
选择表单控制项。
如图 22.4 (a)所示，从项目资源管理器窗口中点击查看代码工具。
从图 22.4 (b)中选择您在表单中放置的控制项。
选择您的控制项事件，如图 22.4 (c)所示。
编写事件代码，如图 22.4 (d)所示。

图 22.4 处理控制项事件

场景 30

创建一个用户表单，用于接受参与者的信息以便注册培训。表单应包含插入和取消命令按钮。当点击插入按钮时，表单中输入的信息必须进入 Excel。当点击取消按钮时，表单应关闭。

解决方案

按照以下步骤操作。

插入一个用户表单。
从属性窗口更改名称和标题。
表单的名称应为“USR_enroll。”
标题应为“培训注册表单。”
按照如图 22.5 所示的样式设计表单。

图 22.5 场景 30 解决方案

此示例有几个属性。

■标签：显示描述性文本。

■文本框：文本框是最常用的控制项，用于显示用户输入的信息。

■列表框：显示一系列值，并允许您选择一个或多个。

■ComboBox：结合了 ListBox 和 TextBox 的功能。用户可以输入新值，就像在 TextBox 中一样，或者用户可以选择现有值，就像在 ListBox 中一样。

■框架：创建功能性和视觉上的控件组。

■属性：属性（属性或变量）。

■方法：对象将使用属性执行的操作。

参考以下 Table 22.1。

表 22.1 员工 UserForm

结论

总之，User Forms 和用户输入是 Visual Basic Scripting (VBS) 的基本组件，允许开发者创建交互式和用户友好的应用程序。通过使用 User Forms，开发者可以设计直观的界面，捕获用户输入，并提升整体用户体验。结合 User Forms 并有效管理用户输入可以极大地提高 VBS 应用程序的功能性和可用性。

练习

创建一个名为“RegistrationForm”的 UserForm，用于捕获事件注册的用户信息。该表单应包括以下控件：

a. 标签：“姓名：”，“邮箱：”，“电话：”，“事件：”，“支付方式：。”

b. 文本框，用户将在其中输入他们的姓名、邮箱和电话号码。

c. 包含用户可以选择的事件列表的 ComboBox。

d. 选项按钮，用户可以选择他们偏好的支付方式（例如，“信用卡”，“PayPal”，“现金”）。

e. 命令按钮：“提交”和“清除。”

您的任务是设计带有适当控制的 UserForm，设置它们的属性，并处理“提交”和“清除”按钮的事件。

第二十三章

高级 VBA 技巧和最佳实践

简介

在本章中，我们将深入探讨高级 VBA 技巧和最佳实践，以增强 Excel 应用程序的功能和效率。我们探讨了初始化控件值、处理选项按钮、创建自定义按钮和用户表单、使用插件、实现大小写转换插件以及通过代码创建菜单等主题。通过掌握这些高级技巧，读者将更深入地理解 VBA 编程，并能够构建更强大、更用户友好的 Excel 应用程序。

结构

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

■设置控制器初始值的代码

■选项按钮的代码

■插入按钮的代码

■显示 User Form 的代码

■插件

■Change Case Form 的代码

■使用代码创建菜单

目标

到本章结束时，读者将了解高级 VBA 技巧和最佳实践，以增强他们的 Excel 应用程序。

设置控制器初始值的代码

为设置控制器的初始值，请按照以下步骤操作：

从“项目资源管理器”中选择“查看代码”。
选择“UserForm”对象。
选择“初始化事件”。

参考图 23.1 Figure 23.1。

图 23.1 设置控制器的初始值

代码如下：

Private Sub UserForm_Initialize()

TXT_name.Value = ""

TXT_address.Value = ""

TXT_contact_no.Value = ""

TXT_company_name.Value = ""

CBO_subject.AddItem "MS Excel"

CBO_subject.AddItem "VBA with Excel"

CBO_subject.AddItem "MS Word"

CBO_subject.AddItem "MS Powerpoint"

CBO_subject.AddItem "MS Office"

CBO_subject.AddItem "MS Access"

CBO_subject.AddItem "MS Project" OPT_company.Value = True

End Sub

Code for Option Buttons

To apply option buttons, follow these steps:

Double-click OPT_company

Private Sub OPT_company_Click()

When the user selects this option, txt_companyname text box will be visible.

TXT_companyname.Visible = True

End Sub

Double click OPT_personal

Private Sub OPT_personal_Click()

When the user selects this option, txt_companyname text box will not be visible.

TXT_companyname.Visible = False

End Sub

Refer to the following Figure 23.2.

Figure 23.2 Adding Option Buttons

Code for Insert Button

Refer to the following Figure 23.3.

Figure 23.3 Adding the Insert Button

Double-click Insert Button

Private Sub CMD_insert_Click()

' Code to find out next blank cell

ActiveWorkbook.Sheets("Training Enrollment").Activate Range("a1").Select

If IsEmpty(ActiveCell) = False Then

ActiveCell.Offset(1, 0).Select

End If

Loop Until IsEmpty(ActiveCell) = True

' Code to put values from Form to Excel

ActiveCell.Value = TXT_name.Value

ActiveCell.Offset(0, 1).Value = TXT_address.Value ActiveCell.Offset(0, 2).Value = TXT_contactno.Value ActiveCell.Offset(0, 3).Value = TXT_email.Value ActiveCell.Offset(0, 4).Value = CBO_subject.Value If OPT_personal.

Value = True Then

ActiveCell.Offset(0, 5).Value = "Personal" Else

ActiveCell.Offset(0, 5).Value = TXT_companyname.Value

End If

TXT_name.Value = ""

TXT_address.Value = ""

TXT_contact_no.Value = ""

TXT_email.Value = ""

TXT_companyname.Value = "Enter your Company name "

TXT_companyname.Visible = False CBO_subject.Value = ""

OPT_company.Value = True

End Sub

To clear the form after inserting the values, follow these steps:

Double-click the Cancel Button.
Write the following code:

Private Sub CMD_cancel_Click() Unload me

End Sub

Code to Show User Form

To run the UserForm from Excel, insert a module and write a macro:

Sub Enrol_form()

USR_enrol.Show

End Sub

Add-ins

Add-ins are separate utilities. They provide some extra functionality to the software. The extension name of an Add In is .XLAM. In Excel, we have ready-made Add-Ins, such as Solver, Analysis Toolpack, Conditional Sum Wizard, and so on.

Scenario 31

Create an Add-in to convert the case into upper, lower, or proper, according to the option selected by the user.

Solution

Follow the steps below.

Design a form for Add-ins, as shown in Figure 23.4.

Figure 23.4 Designing a Form for Add-ins

Open a new Excel workbook.
Open Visual Basic Editor.
Insert a User Form and name it “Changecase.”
Assign the caption “Change Case.”
Drag Objects over the form.
按照表 23.1 表 23.1 中的说明进行设计。

表 23.1 选项按钮

打开 Changecase 表单的代码窗口。
为不同的控件编写代码。

Change Case 表单的代码

双击 OPTupper 控件。

Private Sub OPTupper_Click()

'当用户选择此选项时，它将转换为大写

Dim rng As Range, wscell As Range

Set rng = Range(refselect)

For Each wscell In rng

wscell.Value = UCase(wscell.Value)

End Sub

参考图 23.5 图 23.5。

图 23.5 小写

现在，双击 OPTlower 控件。

Private Sub optlower_Click()

'当用户选择此选项时，它将转换为大写

Dim rng As Range, wscell As Range

Set rng = Range(refselect)

For Each wscell In rng

wscell.Value = LCase(wscell.Value)

End Sub

参考图 23.6 图 23.6。

图 23.6 正规化大小写

双击 opt_upper 控件并编写以下代码：

Private Sub optproper_Click()

'当用户选择此选项时，它将转换为大写

Dim rng As Range, wscell As Range

Set rng = Range(refselect)

For Each wscell In rng

wscell.Value = Application.WorksheetFunction.Proper(wscell.Value)

End Sub

双击 cmdexit 控件。

Private Sub cmdExit_Click()

End

End Sub

插入一个模块并编写宏以显示 Changecase 表单。

Sub show()

Changecase.show

End Sub

使用代码创建菜单

参考以下代码：

Sub auto_open()

'此代码将在现有的菜单栏中创建一个新菜单，该菜单将有一个菜单项，即 Changecase Add-Ins

Dim newmenu As CommandBarPopup

Dim menuitem As CommandBarButton

Set newmenu = CommandBars(1).Controls.

Add(Type:=msoControlPopup, before:=CommandBars(1). Controls("help").Index)

newmenu.Caption = "E&xtras"

'当选择 Changecase 菜单项时，应显示 changecase 表单 Set menuitem = newmenu.Controls.Add(Type:=msoControlButton) menuitem.Caption = "&Change Case" menuitem.OnAction = "show"

End Sub

Sub auto_close()

'当从 Excel 的 CommandBars(1).Controls("extras").Delete 中移除 add-ins 时，这将删除菜单。

End Sub

在此示例中：

■Auto_Open(): 每次打开工作簿时都会触发此第一个事件。

■Auto_Close(): 每次打开工作簿时都会触发此最后一个事件。

结论

在本章中，我们探讨了可以显著提高 Excel 应用程序功能性和用户体验的高级 VBA 技术和最佳实践。从初始化控件值到处理选项按钮，创建自定义按钮和用户表单，利用插件，以及通过代码创建菜单，我们涵盖了广泛的主题。通过应用这些技术和遵循概述的最佳实践，读者可以创建更健壮、高效和用户友好的 Excel 应用程序。掌握这些高级技术后，读者将能够将他们的 VBA 技能提升到新的水平。

练习

让我们考虑一个基于实例的练习，该练习结合了本章讨论的几个高级 VBA 技术。

场景：您正在 Excel 中开发一个项目管理工具。该工具允许用户输入项目详细信息、跟踪进度并生成报告。您的任务是通过对以下功能进行实现来增强该工具：

添加一个名为“TaskForm”的用户表单，允许用户输入任务详细信息，包括任务名称、分配给的人员、开始日期、结束日期和状态（例如，“未开始”、“进行中”、“已完成”）。
在任务表单中实现验证检查，以确保所有必填字段都已填写，并且结束日期不早于开始日期。如果任何验证失败，显示适当的错误消息。
在任务表上创建一个自定义插入按钮，将输入的任务详细信息添加到指定的工作表，例如“ProjectTasks”。确保每个新任务都在新行中插入，然后在成功插入后清除输入字段。
使用 VBA 代码实现一个菜单系统。在 Excel 菜单栏中创建一个名为“项目管理”的新菜单项。在“项目管理”菜单下，添加打开任务表、显示所有任务列表和生成总结报告的选项。
开发一个总结报告功能，计算并显示任务总数、每个状态类别的任务数量以及已完成任务的百分比。总结报告应显示在名为“TaskSummary”的新工作表中。
实现一个名为“TaskUtilities”的插件，它提供任务管理的附加功能，例如按名称或日期排序任务、按状态过滤任务以及生成专用报告。通过在不同的工作簿中使用该插件来测试其功能。
在整个项目中应用代码组织、错误处理和优化的最佳实践，以确保 VBA 代码整洁高效。

第二十四章

使用 VBA 构建自定义插件

简介

在本章中，我们将探讨使用 Visual Basic for Applications (VBA)在 Excel 中构建自定义插件的过程。插件是可集成到 Excel 中以提高其功能和简化工作流程的附加功能或工具。我们将涵盖诸如使用密码保护插件和使用插件的有效方法等主题。

结构

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

■使用密码保护您的插件

■使用插件

目标

到本章结束时，读者将学习如何使用密码保护他们的插件，以及如何有效地利用插件来增强功能。

使用密码保护您的插件

要使用密码保护您的插件，请按照以下步骤操作：

折叠项目中的所有对象（包含您为插件插入表单和模块的 Excel 文件）。
右键单击该项目。
选择 VBA 项目属性。
选择“保护”选项卡。
选择锁定以查看。
设置密码。
点击“确定”。

参考图 24.1 图 24-1。

图 24.1 添加密码

当您准备好插件的表单和模块后，切换到 Excel 环境。
保存当前文件为插件类型（.xlam 扩展名），如图 24.2 图 24-2 所示。

图 24.2 保存当前文件

使用插件

要使用插件，请按照以下步骤操作：

点击“Office”按钮。
点击“Excel 选项”。
选择“插件”。
点击“Go…”按钮，如图 24.3 图 24-3 所示。

图 24.3 选择插件

浏览以定位您的插件，如图 24.4 图 24-4 所示。

图 24.4 定位您的插件

结论

总之，使用 VBA 构建自定义插件使用户能够扩展 Excel 提供的功能并简化他们的工作流程。使用密码保护插件确保其安全性。有效地利用插件可以提高生产力并使用户能够访问 Excel 中的额外功能和工具。

练习

打开 Microsoft Excel 并创建一个新的工作簿。
创建一个简单的 VBA 宏，该宏使用特定的字体、字体大小和背景颜色格式化选定的单元格。
将 VBA 宏保存为具有.xlam 扩展名的插件。
使用密码保护插件以确保其安全性。
通过在 Excel 中安装并使用它来格式化不同工作表中的单元格来测试插件。

第二十五章

ChatGPT 与 Excel

简介

在本章中，我们将探讨 ChatGPT 与 Excel 的集成。Excel 是数据组织和分析的有力工具，而 ChatGPT 是一个能够协助各种任务的 AI 语言模型。通过结合这些工具，您可以利用它们的能力来增强您的 Excel 体验。

结构

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

■使用 ChatGPT 与 Excel

目标

到本章结束时，读者将学习如何将 ChatGPT 与 Excel 集成，以进一步发挥 Excel 的功能并提高数据分析、公式调试和格式化等任务中的效率。

使用 ChatGPT 与 Excel

Excel 是组织和分析数据的强大工具。另一方面，ChatGPT 是一个能够提供各种领域，包括 Excel 在内的帮助的 AI 语言模型。以下是一些您可以一起使用 Excel 和 ChatGPT 的方法。

■请求 Excel 函数和公式的帮助。您可以请求 ChatGPT 帮助解决您在特定 Excel 函数或公式上遇到的问题。只需描述问题或提供一个示例，ChatGPT 就可以提出解决方案或提供逐步指南，如图 25.1 图 25-1 所示。

图 25.1 向 ChatGPT 请求 Excel 函数和公式的帮助

■获取使用 Excel 的技巧和窍门。ChatGPT 可以提供使用 Excel 更高效的方法和技巧。例如，您可以要求快捷键、格式化技巧或自动化 Excel 中任务的途径，如图 25.2 所示。

图 25.2 请求 ChatGPT 提供使用 Excel 的技巧和窍门

■从数据分析中获得见解。Excel 可以用于分析数据并生成见解，如图 25.3 所示。ChatGPT 可以帮助您解释分析结果或提出新的看待数据的方法，如图 25.4 所示。

图 25.3 Excel 中的数据

前往 ChatGPT，在聊天中写下如何分析这些数据。响应如图 25.4 所示。

图 25.4 ChatGPT 如何分析这些数据

■在 Excel 电子表格中协作。您可以使用 ChatGPT 与他人协作 Excel 电子表格。例如，您可以要求 ChatGPT 提出对电子表格的更改或改进建议，或对数据的特定方面提供反馈。图 25.5 展示了一个包含数据的电子表格。

图 25.5 包含数据的电子表格

如果您要求 ChatGPT 对图 25.5 所示的交叉表提供反馈，将生成以下响应：

图 25.6 ChatGPT 响应

■在 Excel 中进行编码。您可以使用 ChatGPT 生成任何代码，例如使用 VBA 在 Excel 中将数字转换为文字的代码。

要使用 VBA 在 Excel 中将文字转换为美元的数字，您可以创建一个自定义函数，该函数使用 Microsoft 语音对象库将文本转换为语音，然后从语音文本中提取数字。以下是一个示例，说明您如何进行此操作：

打开一个新的 Excel 工作簿并按 Alt + F11 打开 VBA 编辑器。
在编辑器中，转到插入 | 模块，以创建一个新的模块。
在新模块中，复制并粘贴图 25.7 所示的代码。

图 25.7 需要复制到新模块中的代码

保存模块并返回到您的 Excel 工作表。
在您想要将文字转换为美元的单元格中，输入以下公式：

=WordsToNumbers(“Two thousand five hundred rupees and fifty paise only”)

按下 Enter 键计算结果。
此公式将把“Two thousand five hundred rupees and fifty paise only”这些文字转换成数字 2500.50。您可以用实际要转换的文本替换示例文本。

Excel 和 ChatGPT 的结合可以帮助您更高效、更有效地处理数据，并提供对您工作的新的见解和视角。假设您有一个包含公司销售数据的电子表格。您想要计算每个月的总销售额并创建图表来可视化数据。为此，您需要遵循以下步骤。

首先，你可以使用 Excel 创建一个新的列，并使用 MONTH 函数从销售数据中的日期中提取月份。
接下来，你可以使用 SUMIFS 函数计算每个月的总销售额。你需要指定函数使用的标准来对每个月的销售数据进行求和。
一旦你有了总计，你可以创建一个图表来可视化数据。选择你的数据，然后转到“插入”选项卡，然后选择你想要创建的图表类型。
如果你不确定使用哪种图表类型或如何格式化图表，你可以向 ChatGPT 寻求建议。例如，你可以问，“可视化按月销售数据最好的图表类型是什么？”或者“我如何使我的图表更具视觉吸引力？”
ChatGPT 可以根据最佳实践和设计原则提供建议。如果你不确定如何使用 Excel 中的特定功能或设置，也可以要求 ChatGPT 解释。参见图 25.8 以获取示例。

图 25.8 向 ChatGPT 寻求帮助以了解 Excel 中的特定功能或设置

使用 Excel 和 ChatGPT 一起可以帮助你更高效、更有效地处理数据，并为你提供关于工作的新的见解和视角。

结论

总之，将 ChatGPT 与 Excel 集成为用户提供有价值的帮助和指导。通过利用 Excel 函数和公式的力量，以及 ChatGPT 的能力，用户可以提高他们在数据分析、公式故障排除和格式化等任务中的效率。ChatGPT 还可以提供使用 Excel 更有效的技巧、窍门和见解。此外，与 ChatGPT 协作在电子表格中并寻求反馈可以增强使用 Excel 的整体体验。通过结合这些工具，用户可以优化他们的数据管理和分析工作流程，从而提高生产力和更好的决策。

练习

打开 Microsoft Excel 并创建一个新的工作簿。
在工作表中输入以下样本销售数据。

表 25.1 样本销售数据

日期	销售额
01-01-2023	$500
05-01-2023	$300
10-02-2023	$750
15-02-2023	$600
03-03-2023	$900
07-03-2023	$400

创建一个执行以下任务的 VBA 宏：

a. 计算每个月的总销售额。

b. 确定销售额最高的月份。

c. 在消息框中显示计算出的总计和销售额最高的月份。

索引

绝对单元格引用，21–22

高级筛选，76–77

数组，VBA

声明，224

动态数组

声明，225–226

ReDim 语句，226

索引，225

类型，224

自动填充功能，15–16

自动筛选功能，72–73

BODMAS 规则，30

按钮

在 Excel 工作表中，168–171

修改菜单或按钮，164–168

在快速访问工具栏上，164

记录的宏，编辑，171–176

单元格引用

绝对单元格引用，21–22

混合单元格引用，22–24

相对单元格引用，20–21

图表

数据，144

设计，143

筛选选项，145

建议，147

Sparklines，147–149

模板，145

类型，142，145

瀑布图，146–147

ChatGPT

在 Excel 中编码，265

在 Excel 工作表中协作，264

数据分析，262–263

与 Excel 的比较，266

Excel 函数和公式的帮助，262

反应，265

使用 Excel 的技巧和窍门，262

条件格式

图标集，117

新的格式化规则，117

使用单元格值，115

使用公式，116–118

合并选项，82–89

定制排序，67–68

数据筛选，74

数据验证

方法，52–55

规则，51–52

Excel

后台视图

可用选项，4–5

分享选项，6

ChatGPT（见 ChatGPT）

组件，3–4

数据挖掘，特性，11–13

默认设置，7–8

特点，1

格式化表格，9

交互，6–7

目标，2

选项窗口，7

快速数据分析工具，10

转换时间缩短技术

自动填充功能，15–16

条件格式，14

数据验证，14

Excel 模板，15

公式和函数，14

快捷键，13

数据透视表，15

排序和筛选功能，16

筛选

高级筛选，76–77

自动筛选功能，72–73

图表，145

数据筛选器，74

数字筛选器，73

文本筛选器，74

公式，30–31

函数

IF 函数，32–33

IF 与 AND，35–36

IF 与 NOT，36–37

嵌套 IF 函数，34–35

语法，31，32

HLOOKUP，38–39

IF 函数，32–33

IF 与 AND 函数，35–36

IF 与 NOT 函数，36–37

索引函数

索引匹配，44–45

语法，44

查找函数

HLOOKUP，38–39

VLOOKUP

中的列函数，41–42

函数参数，38

中的匹配函数，42–43

使用非零范围，40–41

使用零范围，39–40

循环，VBA

Do...Loop 语句，208–210

Each...Next 语句，211–212

For...Next 语句，210–211

宏，VBA，152

分配按钮（见按钮）

自动执行宏，218

创建，153

调试，233–235

定义，154–155

描述，155

lcase()函数，213

Proper_case 函数，213–214

记录，154

相对引用，156–158

按名称运行，159–161

运行方法，158

快捷键，155

停止录制，156

存储，155

Ucase()函数，212

应用，152

混合单元格引用，22–24

多级排序，66–67

命名范围

创建，24–25

删除，27

重命名/编辑，25–26

嵌套 IF 函数，34–35

数字过滤器，73

数据透视表

创建，96–99

数据模型，110

总计百分比，99–101

数据透视图，102–105

Power Pivot，109–111

Power View，109

建议，95–96

切片器，105–107

时间轴，108

字段类型，94

保护

员工信息系统，58

带密码的文件，61–62

保护级别，58

工作表的一部分，60–61

工作簿级别的保护，59–60

工作表，密码保护，59

相对单元格引用，20–21

简单排序，66

切片器，105–107

排序

定制排序，67–68

定义，66

多级排序，66–67

简单排序，66

Sparklines，147–149

小计函数

多级小计，81–82

单级小计，80–81

文本过滤器，74

跟踪依赖关系，50–51

跟踪前驱，48–50

转换时间缩短技术

自动填充功能，15–16

条件格式，14

数据验证，14

Excel 模板，15

公式和函数，14

快捷键，13

数据透视表，15

排序和筛选功能，16

VBS 中的用户表单，238

添加其他控件，239

创建，238–239

处理控件事件，240–242

VBA

插件，250–251

密码保护，258–259

使用，259–260

数组

声明，224

动态数组，225–226

索引，225

类型，224

Auto_Close()，255

Auto_Open()，255

分支技术，188–191

改变大小写形式，251–254

条件语句

If...End If 语句，194

Select Case 语句，195–196

定义，152

错误

错误处理，230–231

错误号，232–233

类型，230

函数过程，186–188

InputBox 函数，202

插入按钮，248–250

循环

Do...Loop 语句，208–210

Each...Next 语句，211–212

For...Next 语句，210–211

宏，152

分配按钮（见按钮）

自动执行宏，218

创建，153

调试，233–235

定义，154–155

描述，155

lcase()函数，213

Proper_case 函数，213–214

记录，154

相对引用，156–158

按名称运行，159–161

运行方法，158

快捷键，155

停止录制，156

存储，155

Ucase()函数，212

使用，152

模块，184–185

Msgbox 函数，202

选项按钮，247–248

过程，类型，180

选择并激活

单元格，202

行和列，203

为控件设置初始值，246–247

子程序，185–186

用户表单，250

用途，152

变量和常量

数据类型，201

声明，200–201

功能，200

Option Explicit 声明，202

Visual Basic 编辑器，代码窗口，181–183

操作

应用程序对象，204

工作表，203

工作簿，204

VLOOKUP

列函数，41–42

函数参数，38

匹配函数，42–43

使用非零范围，40–41

使用零范围，39–40

假设分析工具

数据表

单变量，123–124

双变量，124–125

目标求解功能，122–123

情景

创建，126–127

删除，127

显示，128

从另一个工作表合并，128–129

汇总报告，127

工作簿

超链接，138–139

合并，136–137

共享，136

跟踪更改功能，137–138

工作表

审计功能，135

合并功能，133

工作表间引用，132

posted @ 2026-04-03 22:10 布客飞龙II 阅读(12) 评论(0) 收藏举报

刷新页面返回顶部

龙哥盟

人最大的痛苦就是说一些自己都不相信的话。

高级-EXCEL-365-包含-ChatGPT-提示

高级 EXCEL 365：包含 ChatGPT 提示

公告