标签：精通 函数 单元格 Excel ChatGPT 排序 数据 筛选

原文：Mastering Advanced Excel - With ChatGPT Integration

译者：飞龙

协议：CC BY-NC-SA 4.0

前言

欢迎阅读我的书！在这本全面指南中，我们将探索 Excel、Visual Basic for Applications（VBA）和 ChatGPT 这三大强大工具的结合。这些工具结合了数据分析、自动化和对话式人工智能的力量，让您在信息处理和决策领域更具实力。

在这些页面中，您将获得实用知识，实际示例和逐步说明，以掌握 Excel 的数据操作能力，解锁 VBA 的自动化和定制潜力，并深入 ChatGPT 的自然语言交互世界。

无论您是初学者想要了解基础知识，还是经验丰富的用户希望提升技能，本书都将成为您在这些领域脱颖而出的路线图。它将引导您了解 Excel 的基础知识，介绍您 VBA 编程的世界，并向您展示如何将 ChatGPT 集成到您的应用程序中，进行动态和智能对话。

在本书结束时，您将具备知识和信心，可以利用 Excel、VBA 和 ChatGPT 的综合功能来简化工作流程，自动化重复任务，并进行智能数据驱动对话。

加入我，一起踏上这激动人心的旅程，探索 Excel、VBA 和 ChatGPT 的无限可能性。让我们释放您的潜力，革新您对数据处理和分析的方法。以下是各章节的简要概述。

第一章：Excel 2021 概述 – 提供 Microsoft Excel 2021 的概述。它介绍了新的简洁界面，并探索了 Excel 窗口的不同组件。本章强调了在线保存和共享文件的能力，以及自定义功能区和更改默认设置的选项。它还涵盖了格式化表格，使用粘贴特殊预览功能，并利用闪填进行高效数据输入。此外，本章还涉及 Excel 的即时数据分析能力。

第二章：单元格引用和区域 – 专注于单元格引用和命名区域。它解释了使用不同类型引用的重要性，并介绍了 Excel 中可用的各种单元格引用类型。本章还涵盖了命名区域的概念，允许用户为特定单元格范围分配名称以便更容易引用。本章以练习结束，以巩固所学概念。

第三章：使用公式和函数 – 专注于使用公式和函数。它首先解释了工作表中公式的用途，并介绍了数组公式的概念。该章涵盖了 Excel 中可用的各种函数，包括 IF 函数及其变体，如嵌套 IF、带 AND 的 IF、带 OR 的 IF 和带 NOT 的 IF。它还涵盖了像 VLOOKUP 和 HLOOKUP 这样的查找函数，并提供了使 VLOOKUP 动态化的见解。该章以 INDEX 和 INDEX-MATCH 函数的用法结束。通过练习，读者可以练习并巩固他们对 Excel 中公式和函数的理解。

第四章：数据验证 – 探讨了数据验证的主题。它首先解释了设置数据验证规则以确保数据的准确性和完整性的重要性。该章涵盖了各种数据验证方法，包括设置数据输入的标准、创建自定义验证规则以及使用预定义验证选项。通过练习，读者有机会练习实施数据验证技术。

第五章：保护 – 讲解了保护。它解释了如何使用密码保护工作表、保护工作簿、保护工作表的特定部分以及为文件设置密码保护。通过按照本章的说明操作，用户可以为其 Excel 文件应用各种级别的保护，以增强安全性。

第六章：数据库排序 – 专注于数据库排序。它涵盖了不同的排序方法，包括简单排序、多级排序和自定义排序。排序使用户可以按照期望的顺序组织数据，从而更容易地分析和处理大量信息。通过理解本章中解释的技术，用户可以根据特定标准高效地对其数据进行排序。

第七章：数据库过滤 – 讲解了数据库过滤的主题。它解释了如何使用自动筛选功能根据特定条件（如数字、文本或日期）筛选数据。该章还介绍了高级筛选的概念，允许用户使用更复杂的条件对列表进行筛选。此外，它探讨了筛选唯一记录的技巧。通过应用本章所学的知识，用户可以有效地从他们的数据库中筛选和提取相关数据，以满足其特定需求。

第八章：小计和数据合并 – 重点介绍小计和数据合并。解释了如何使用小计功能以单个级别或嵌套格式显示小计值。这使用户能够有效地汇总和分析数据。本章还涵盖了数据合并，即将来自多个源的数据合并到单个工作表中。通过掌握这些技巧，用户可以轻松组织和分析数据，使其更易管理和有意义。

第九章：数据透视表 – 深入研究了数据透视表，这是一种用于数据分析的多功能工具。涵盖了创建和检查数据透视表，包括使用推荐的数据透视表进行快速生成。本章演示了数据透视表报告中的格式设置选项以及字段百分比的计算。还探讨了创建顶部/底部报告、分组项目和使用数据透视表数据生成图表等高级功能。其他主题包括使用切片器和时间线进行过滤，以及使用 Power View 和 Power Pivot 进行增强的数据分析和建模能力。

第十章：条件格式设置 – 包括基于单元格值和公式的条件格式设置，以及图标集。探讨了使用具有多个条件的公式和跨不同工作表应用条件公式等高级技术。

第十一章：假设分析 – 介绍了 Excel 中的假设分析工具，包括目标搜索、数据表以及创建假设场景。还涵盖了从另一个工作表合并场景和保护场景以确保数据完整性的内容。

第十二章：多工作表、工作簿和应用程序操作 – 探讨了在 Excel 中处理多个工作表、工作簿和应用程序的操作。涵盖了在不同工作表和软件之间创建链接、促进工作组协作、合并工作簿和跟踪更改。还讨论了创建超链接以便在工作簿内进行轻松导航的方法。

第十三章：图表操作 – 专注于在 Excel 中操作图表。涵盖了使用图表工具创建图表，包括标题和数值，并格式化以增强外观。本章还探讨了使用图表模板进行一致的图表设计，并提供了有效使用图表的建议。此外，介绍了嵌入单元格中的小图表——迷你图，并解释了如何自定义它们的外观和样式。本章以练习巩固所学概念。

第十四章：在 VBA 中创建和记录宏 – 探讨了 Excel 中宏的概念，即自动化重复任务的一系列指令。它涵盖了创建宏的过程，从记录一系列操作开始，定义宏的名称和描述。本章还介绍了相对引用宏的概念，适应不同的单元格引用。它解释了如何停止录制，并以点击按钮或按其分配的名称运行宏的说明结束。总体而言，本章全面概述了宏及其在 Excel 中的应用。

第十五章：为宏分配按钮 – 专注于在 Excel 中创建菜单或按钮以增强用户交互和简化工作流程。它涵盖了修改现有菜单或按钮的过程，以及直接在 Excel 工作表上创建自定义按钮。本章还提供了有关编辑记录的宏以完善其功能和外观的指导。通过实际练习，读者可以获得创建和定制菜单和按钮以满足其特定需求的实践经验。

第十六章：VBA 中的函数和子程序 – 介绍了 Excel 的 VBA 编程中函数和子程序的概念。它解释了两者之间的区别，并指导读者在使用 Visual Basic Editor 编写模块内的代码。本章还涵盖了控制程序执行流程的分支技术。

第十七章：VBA 中的条件语句 – 专注于 Excel 的 VBA 中的条件编程。它探讨了使用 Select Case 语句和 If...End If 语句根据不同条件控制程序流的用法。本章提供了创建具有高效和有效条件逻辑的商业级代码的示例和指导。

第十八章：VBA 中的变量和数据类型 – 介绍了 Excel 的 VBA 编程中变量和常量的概念。它涵盖了声明变量和常量、理解数据类型以及它们在 VBA 代码中的使用等主题。本章还探讨了用于显示消息和接收用户输入的消息框和输入框功能。此外，它提供了有关选择和激活单元格、行和列以及在 VBA 中处理工作表的指导。

第十九章：VBA 中的循环结构 – 专注于 Excel 的 VBA 编程中循环的概念。它解释了在 VBA 代码中使用循环重复操作的用法。本章涵盖了 Do...Loop 和 For...Next 循环语句，展示了它们的语法以及如何在不同场景中应用它们。此外，它还讨论了自动执行的宏，这些宏在特定事件发生时自动运行。本章提供了有关使用循环结构创建高效和动态 VBA 代码的宝贵见解。

第二十章：VBA 中的数组和集合 – 介绍了 Excel 的 VBA 编程中的数组和集合的概念。该章解释了数组是什么以及如何声明和使用它们来存储相同数据类型的多个值。它涵盖了数组索引、访问数组元素和处理动态数组等主题。该章还探讨了集合，这是可以存储不同数据类型的多个值的对象。它提供了关于声明和利用动态数组以及根据需要调整大小的见解。

第二十一章：VBA 中的调试和错误处理 – 专注于管理错误和排除 VBA 中的宏。它涵盖了错误处理技术和调试 VBA 代码以高效识别和解决错误的方法。

第二十二章：VBS 中的用户表单和用户输入 – 介绍了用户表单的概念，允许在 Excel 中创建交互式界面。它解释了如何设计用户表单，添加按钮和文本框等控件，并通过事件处理处理用户输入。

第二十三章：高级 VBA 技术和最佳实践 – 提供了关于高级 VBA 编程技术和推荐实践的见解。它包括用于初始化控件值、使用选项按钮、创建自定义菜单的代码片段，并强调遵循高效 VBA 编程最佳实践的重要性。

第二十四章：使用 VBA 构建自定义加载项 – 探讨了使用 VBA 在 Excel 中创建自定义加载项的过程。它介绍了如何使用密码保护加载项，并解释了使用加载项扩展 Excel 功能和简化工作流程的好处。

第二十五章：在 Excel 中使用 ChatGPT – 提供了将 ChatGPT 与 Excel 集成的最佳实践，特别关注使用 ChatGPT 增强 Excel 中的任务和沟通。它涵盖了使用 ChatGPT 进行有效内容生成、电子邮件撰写和数据分析。该章节强调了在将 ChatGPT 与 Excel 集成时的数据隐私和安全性，并提供了有效集成和工作流管理的建议。

彩色图片

请点击链接下载

书中的彩色图片：

rebrand.ly/9ev8v6o

我们有来自丰富书籍和视频目录的代码捆绑包，可在https://github.com/bpbpublications找到。快去看看吧！

要访问使用的 Excel 数据文件，请访问rebrand.ly/phafx8h

勘误

我们为在 BPB 出版社的工作感到无比自豪，并遵循最佳实践，以确保我们的内容准确无误，为我们的订阅者提供沉浸式阅读体验。我们的读者是我们的镜子，我们利用他们的反馈来反思和改进可能在出版过程中出现的人为错误。为了让我们保持质量，并帮助我们接触到由于任何意外错误而遇到困难的读者，请写信给我们：

[email protected]

感谢您对 BPB 出版社家族的支持、建议和反馈。

加入我们书籍的 Discord 空间

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

discord.bpbonline.com

第一章 Excel 2021 概述

介绍

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

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

• 云存储：Excel 2021 文件存储在云端，这意味着可以通过互联网连接从任何地方访问。

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

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

• 人工智能：Excel 2021 包含了基于人工智能的功能，可以分析数据并提供见解，比如 Ideas 功能，可以建议图表、图形和其他可视化内容。

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

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

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

与上一个版本一样，这个版本在窗口顶部有一组称为功能区的菜单，所有 Excel 命令都在其中。Excel 文档称为工作簿，每个工作簿分为一组行和列，这个表格结构的交叉点称为单元格。数据输入到单元格中，所有对电子表格的操作都应用于单元格。MS Excel 具有一组工具，可以进行格式化、分析并创建图表。

结构

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

• Excel 窗口的组件

• 后台视图

• 在线保存和共享文件

• 与 Excel 互动

• 使用默认设置工作

• 表格格式化

  • 粘贴特殊预览

  • 闪填

• 快速数据分析

• 数据挖掘

• TAT 节省技术

目标

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

Excel 窗口的组成部分

图 1.1 展示了 Excel 窗口：

图 1.1：Excel 欢迎窗口

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

这个窗口还有一个在线模板搜索文本框。通过使用这个功能，你可以与在线办公模板库同步。

当您双击空白窗口选项时，会打开一个空白的电子表格，如图 1.2 所示：

图 1.2：Excel 2016 窗口的各种组件

后台视图

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

图 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. 点击“文件”选项卡。

2. 点击“帮助”下的“选项”按钮。

3. 点击“自定义选项卡”。

4. 点击“新建选项卡”。

5. ���查看并保存您的自定义设置，请点击“确定”。

参考图 1.7：

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

表格格式

Excel 提供了各种预定义的表格样式，我们可以使用这些样式快速格式化表格。这是 Excel 给出的预定义格式，因此我们不必更改表格的样式或字体。

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

1. 选择范围。

2. 在功能区上选择“开始”。

3. 选择样式组。

4. 选择“格式表格”。此选项以下拉列表的形式打开各种格式样式，通过单击任何样式，您可以将其应用于您的数据。

粘贴特殊预览

Excel 提供了带有实时预览功能的粘贴选项，使您在重复使用内容时节省时间。此选项帮助您查看各种可用的粘贴选项的预览，例如保留源列宽度、无边框或保留源格式。实时预览使您可以在实际粘贴到工作表之前直观地确定粘贴内容的外观。当将指针移动到粘贴选项上以预览结果时，您将看到一个包含根据您正在重复使用的内容最佳适应内容的项目的菜单。屏幕提示提供额外信息，帮助您做出正确的决定。

闪填充

这是一个令人兴奋的新功能，承诺节省时间。举个例子：如果您在列 A 中写入名字，列 B 中写入姓氏，要将两个名字连接在列 C 中，Excel 具有自动填充整个列的功能，如 图 1.8 (a) 所示。再举一个例子，假设我们在列 A 中有包含全名的电子邮件地址。您可以开始在新列中输入名字，Excel 将自动填充整个列，如 图 1.8 (b) 所示。

图 1.8：闪填充

快速数据分析

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

举个例子：有一个按部门工资排列的数据，需要以适当的格式呈现。你需要在这里进行快速分析。参考 图 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 电子表格过程并减少执行某些任务所需时间的不同方法。以下是一些可以帮助改善 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 的排序和筛选功能可以通过快速组织和分析数据来节省时间。要对数据进行排序，请选择要按其排序的列，然后单击“升序排序”或“降序排序”按钮。要筛选数据，请单击“筛选”按钮，并选择要用于筛选数据的条件，如图 1.23 所示：

  

  图 1.23：Excel 中的排序和筛选

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

结论

总之，Excel 2021 是一个强大的工具，提供了各种用于数据管理、分析和协作的功能。凭借其用户友好的界面、自定义选项和数据分析功能，Excel 2021 赋予用户高效工作、获取见解和做出明智决策的能力。它是一个增强生产力的多功能工具，对于处理数据的个人和企业至关重要。

练习

1. Excel 的条件格式化功能的目的是什么？

   1. 执行复杂计算

   2. 分析数据趋势

   3. 基于特定条件格式化单元格

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

2. Excel 中哪个函数允许您找到单元格范围中的最高值？

   1. 最大值

   2. 总和

   3. 平均值

   4. 计数

答案

1. c

2. 一个

加入我们书籍的 Discord 空间

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

discord.bpbonline.com

第二章 单元格引用和范围

介绍

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

结构

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

• 使用不同类型的引用

• 单元格引用类型

• 命名范围

目标

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

使用不同类型的引用

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

单元格引用类型

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

• 相对单元格引用

• 绝对单元格引用

• 混合单元格引用

参考以下图 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(H2:H101) 的位置写 Sum(Basic)。然而，为了做到这一点，我们首先需要将范围 H2:H10 命名为 Basic。现在，让我们看看如何给范围命名。

创建命名区域

要命名一个区域，我们可以使用以下其中一种方法：

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

   

   图 2.11：创建命名区域

2. 如果您想要使用一个单元格中的值来命名单元格，可以选择范围以及名称。在公式选项卡中单击“从选定创建”，并选择其中一个选项。

3. 单击确定。

4. 您也可以通过在公式选项卡中单击“定义名称”来创建一个命名范围。

5. 在名称框中写入范围的名称。然后单击“引用”框，并选择要命名的范围。

6. 单击确定。

7. 现在，您可以在工作簿中的任何地方使用给定的名称而不是范围。参考图 2.12：

   

   图 2.12：使用命名范围而不是单元格引用

编辑命名范围

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

1. 在公式选项卡中，单击名称管理器。

2. 打开一个名称管理器对话框，如图 2.13 所示：

   

   图 2.13：名称管理器对话框

3. 选择要编辑的命名范围，然后单击“编辑”按钮以编辑命名范围。

4. 出现一个编辑名称对话框，如图 2.14 所示：

   

   图 2.14：编辑名称对话框

5. 现在，输入一个新名称或重新定义范围名称。

删除命名范围

要删除一个范围，请执行以下步骤：

1. 从名称管理器列表中选择范围。

2. 单击删除。

3. 现在，命名范围将被删除。

提示：按 Ctrl + F3 键可打开名称管理器对话框。

结论

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

练习

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

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

   表 2.1：匹配正确的选项

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

加入我们书籍的 Discord 空间

加入本书的 Discord Workspace，获取最新更新、优惠、全球技术动态、新发布内容以及与作者的交流：

discord.bpbonline.com

第三章使用公式和函数

介绍

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

结构

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

• 在工作表中使用公式

• 使用函数

• 查找函数

• 使 V-Lookup 动态

• 索引

目标

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

在工作表中使用公式

公式是对值进行计算的方程式。公式以等号(=)开头。它至少包含两个操作数和一个操作。例如，以下公式将 2 乘以 3，然后将 5 加到结果中。

=5+2*3

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

注意：在 Excel 中，当涉及多个运算符时，遵循 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)

没有必要记住所有可用的函数以及每个函数所需的参数。相反，您可以使用用于求和的 sigma 符号（∑），或单击下拉菜单以获取更多函数，如 Max、Min 等。Excel 会提示您所需和可选的参数。

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

IF 函数

在第二章中，单元格引用和范围，我们已经学习了诸如 HRA 和 DA 之类的收入头的计算。我们看到的公式对整个数据库都是相同的。根据某些条件，我们需要决定应用的公式。例如，奖励可能根据部门计算。这就是条件函数如“IF”出现的地方。

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

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

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

示例：

假设您想根据员工的职位计算 HRA。如果职位是经理，那么 HRA 是 1000，否则是 500。那么函数代码将如下所示：

=if(C2="经理", 1000, 500)

参考图 3.4：

图 3.4：IF 函数

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

嵌套 IF

当第二个 IF 函数放置在第一个顺序内以测试附加条件时，将使用嵌套 IF 函数。

嵌套 IF 函数的语法是：

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

示例：

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

图 3.5：使用嵌套 IF 函数

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

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

表 3.1：分配字母等级

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

IF（AverageScore>89，“A”，IF（AverageScore>79，“B”，IF（AverageScore>69，“C”，IF（AverageScore>59，“D”，“F”）））

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

带有 AND 的 IF

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

语法：

AND（逻辑 1，逻辑 2...）

���果存在这样的情况，即我们有两个条件，其联合真值将决定 IF 函数的输出，则可以使用 AND 与 IF。

语法：

如果（和（条件 1，条件 2….），真，假）

示例：

如果我们需要给在“销售”部门工作且在“北部”地区的每个人提供基本工资的 10%作为激励，我们将使用以下公式：

=IF (AND (Department=”sales”, Region=”north”), 10%*基本工资, 0)

带有 OR 的 IF

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

语法：

OR（逻辑 1，逻辑 2...）

如果存在这样的情况，即我们有两个条件，其中任何一个条件为假，则 IF 应返回假参数中的值，我们可以在 IF 中使用 OR。

使用 OR 与 IF 的语法

如果（OR（条件 1，条件 2….），真，假）

示例：

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

如果（或（Department=”Mktg”，Department =”Sales”，Department=”Hrd”），基本工资.5，基本工资.3）

带有 NOT 的 IF

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

语法：

NOT（逻辑）

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

使用 NOT 与 IF 的语法

如果（NOT（条件），真，假）

示例：

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

IF（NOT（Department=”MT”），10%*工资，0）

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

我们还可以在 IF 内部使用多个 NOT。

示例：

如果你需要给每个人提供激励，但不包括销售和行政部门的人员，你可以使用以下函数：

如果（并且（不是（部门=”销售”），不是（部门=”行政”）），10%*工资，0）

查找函数

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

• V-lookup（如果数据库是垂直的）。参考图 3.6：

  

  图 3.6：V-lookup

• H-lookup（如果数据库是水平的）：参考图 3.7：

  

  图 3.7：H-lookup

V Lookup

如果我们需要根据一个共同字段从其他文件或表中获取列的值，可以使用 V-lookup。V-lookup 是一个函数，它在给定数据库（表格数组）的最左列中搜索一个值（查找值），并返回你指定列中相同行的值。

语法：

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

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

图 3.8：V-lookup 函数参数

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

• Lookup_value：要在表格的第一列中找到的值。这是你要查找的值。Lookup_value 可以是一个值，一个引用或一个文本字符串。

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

• Col_index_num：从中返回匹配值的 table_array 中的列号。

• Range_lookup：逻辑值，指定是否要让 VLOOKUP 找到精确匹配还是近似匹配。如果 Range Lookup 设置为 FALSE 或 0，VLOOKUP 将找到精确匹配。如果找不到精确匹配，将返回错误值#N/A。如果设置为 TRUE 或非零，它会找到小于查找值的最近值。

H-lookup

H-lookup 函数在表格的最顶行中搜索数值，然后返回你指定行中相同列的数值。

语法：

HLOOKUP (lookup_value, table_array, row_index_num, range_lookup)

H-lookup 与 V-lookup 的工作方式相同。但在这种情况下，我们需要指定行索引号，而不是列索引号。

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

带有范围 0（False）的 Vlookup 示例：

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

1. 选择要放置结果的单元格。

2. 点击插入函数。从查找和引用类别中选择 Vlookup()函数。

3. 查找值：选择 A2 [员工代码]

4. 表数组：选择激励表并从$A\(1:\)B$12 中选择范围[即，员工代码和激励列]

5. 列索引：输入 2 [列 2 是激励表中的激励列]

6. 范围查找：输入 False [我们正在从表中搜索查找值的精确匹配]

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

= iferror (vlookup….,"")

带有非零范围的 Vlookup 示例（True）

假设您想根据工资分级添加激励。在这种情况下，您可以使用带有 True 范围的 Vlookup，而不是 IF 条件。在这种情况下，我们将创建一个表，如表 3.2。在表数组中，选择此表，并在范围查找字段中，输入“True”而不是“False”。

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

表 3.2：示例表

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

示例：

在高级 Excel 作业文件中，emp_inf 表，我们需要根据员工 ID 检索员工信息。为此，我们可以使用 vlookup，如下所示图 3.9：

图 3.9：带有非零范围的 Vlookup 示例

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

使 V-Lookup 动态化

当我们有一个动态数据库，其中新列经常添加到数据库中时，当前列的位置也可能会改变。然而，Vlookup 的列索引号不会随着数据库的增长而自动更新。这就是我们需要使 VLookup 动态化的地方，以便获取列索引号。为此，我们可以使用以下函数之一动态检索列索引号：

• 列

• 匹配

在 Vlookup 中使用列函数

要使 Vlookup 动态化，我们可以使用列标题作为指示器，动态地选择所需值存在的特定列的索引号。在 vlookup 中使用列函数的语法是：

= vlookup (lookup_value, table_array, COLUMN (reference), Range_lookup)

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

示例:

在上述 vlookup 示例中，如果我们需要动态查找列索引号，我们可以使用列函数，如下所示图 3.10：

图 3.10：在 Vlookup 中使用列函数

B1 是“工资”工作表中“名字”列的列标题的引用。

在 V-Lookup 中使用 Match 函数

正如我们在前面的案例中看到的，我们需要访问原始数据库或至少知道列的当前位置。然而，这些信息并不总是为我们所拥有。因此，在这里，我们需要使用一个可以通过名称仅检索列标题位置的函数。

Match 函数也是一样。Match 函数返回范围内字符串的位置。

match 函数的语法如下：

匹配（查找值，查找数组，[匹配类型]）

可选择的各种选项是：

• 查找值：我们正在查找的字符串。它可以是一个字符串，例如“工资”，或存储字符串的单元格引用。

• 查找数组：我们需要知道查找值位置的范围。

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

示例：

如果我们需要找出“salary”字符串在工资表第一行中的位置，我们会写，

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

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

在 vlookup 中使用 match 函数的语法是：

=vlookup(lookup_value, table_array, match(label,源数据库的第一行标签,0), Range_lookup)

示例：

在 emp_inf 示例中，如果我们需要使用列标题使 vlookup 更加动态，我们可以使用 match 与 vlookup，如图 3.11 所示。在这里，match 查找工资数据库标题中每个字段的标签，并动态返回列的位置：

图 3.11：vlookup 中的 match 函数

索引

有时，我们需要根据其行号和列号在数据库中查找数据。索引函数帮助我们做到这一点。

索引函数的语法如下：

索引（数组，行号，[列号]）

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

示例：

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

=index(database, 3, 4)

索引-匹配

正如我们之前学过的，vlookup 是根据数据库第一列的值来查找数据。然而，如果我们有一个数据库，其中我们的查找值在中间，我们需要向左搜索，我们必须将列移动到最左上角，然后再使用 vlookup。当与 match 一起使用时，索引函数帮助我们搜索数据，即使查找值不在最左列上。

index-match 的语法如下：

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

在这里，您可以使用匹配函数来获取行号或列号或两者都使用。

例子：

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

=INDEX (数据库, MATCH (qtr 3, 列标题, 0), MATCH (year, 年份列, 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 中处理数据和电子表格的人都至关重要。

练习

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

   1. 激励 1：所有在“销售”部门工作的人员将获得其工资的 10%作为激励，其他人将获得 0

   2. 激励 2：所有在“销售”或“营销”部门工作的人员将获得其工资的 5%作为激励，其他人将获得 2%。

2. 使用 V-lookup 列计算 Q.1 c 中的激励方案。

3. 在工资列之后制作一个“汇报经理”列，并根据员工编号将经理的员工代码部署给员工，如下所示使用 V-Lookup。

加入我们书籍的 Discord 空间

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

discord.bpbonline.com

第四章数据验证

介绍

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

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

结构

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

• 跟踪先行

• 跟踪从属项

• 设置数据验证规则

• 数据验证方法

目标

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

跟踪先行

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

跟踪先行：跟踪先行允许您查看所选单元格引用的哪些单元格。这对于了解数据如何在电子表格中流动以及识别任何潜在错误或问题非常有用。要使用跟踪先行，选择要跟踪的单元格，然后单击功能区中“公式审计”部分的“跟踪先行”按钮。Excel 将绘制指向所选单元格引用的单元格的箭头。

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

参考以下图示：

图 4.1：跟踪先行示例

要使用跟踪先行查看单元格 C2 引用的哪些单元格：

1. 选择单元格 C2，如下所示：

   

   图 4.2：为总收入应用公式

2. 单击功能区中“公式审计”部分的“跟踪先行”按钮，如下所示：

   

   图 4.3：公式选项卡

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

图 4.4：跟踪先行

这显示了单元格 C2 中的公式依赖于单元格 A2 和 B2 中的值。如果更改这些单元格中的任何一个的值，单元格 C2 中的值也会相应更改。

使用Trace Precedents帮助您了解数据如何在电子表格中流动，并识别公式中的任何潜在错误或问题。

Trace Dependents

Trace Dependents允许您查看依赖于所选单元格的单元格。这对于了解更改特定单元格将对电子表格的其他部分产生的影响非常有用。要使用Trace Dependents，请选择要跟踪的单元格，然后单击功能区的公式审计部分中的Trace Dependents按钮。Excel 将绘制指向依赖于所选单元格的单元格的箭头。

Trace Precedents和Trace Dependents都可以帮助您了解电子表格的结构，并排除可能出现的任何错误或问题。通过使用这些功能，您可以更轻松地浏览和分析复杂的电子表格，并根据您的数据做出更明智的决策。

这是如何使用Trace Dependents的示例：

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

要使用Trace Dependents查看依赖于单元格 A4 的单元格：

1. 选择单元格 A4。

2. 在功能区的公式审计部分单击Trace Dependents按钮。

3. Excel 将绘制指向依赖于单元格 A4 的任何单元格的箭头。

4. 在此示例中，单元格 B4、C4 和 D4 依赖于单元格 A4，因为它们包含月付款的分解，包括用于本金、利息和任何额外费用或收费的部分。

参考以下图：

图 4.5：Trace Dependents

通过使用Trace Dependents，您可以看到更改单元格 A4 的值会影响哪些单元格。例如，如果您增加利率或更改贷款期限，您可以使用Trace Dependents查看这将如何影响月付款及其分解。

使用Trace Dependents可以帮助您了解电子表格的结构，并根据您的数据做出更明智的决策。

设置数据验证规则

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

1. 选择要创建验证规则的单元格。

2. 在“数据”选项卡中，“数据工具”组中，单击“数据验证”以打开“数据验证”对话框（如图 4.1 所示）。

3. 激活“设置”选项卡。

4. 从“允许”列表中选择数据验证选项。

5. 从“数据”列表中选择您想要的运算符。

6. 然后完成剩余的条目。

7. 如果需要，在输入消息选项卡中输入输入消息。

8. 如果需要，在错误警报选项卡中输入错误消息。

9. 点击确定以设置验证规则。

10. 关闭对话框。

参考以下图：

图 4.6：数据验证

数据验证方法

现在让我们看看一种数据验证方法，即创建列表。

创建列表

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

下面是创建列表的步骤：

1. 选择空白单元格

2. 选择数据选项卡

3. 从数据工具组中选择数据验证

4. 选择列表

5. 在来源中，选择具有值的单元格，或者使用逗号键入数据

参考以下图：

图 4.7：创建列表

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

允许数字在限制内：

1. 在允许框中，点击整数或小数。

2. 在数据框中，选择您想要的限制类型。例如，要设置上限和下限，请选择介于。

3. 输入最小值、最大值或特定值以允许。

允许在一定时间范围内的日期或时间：

1. 在允许框中，选择日期或时间。

2. 在数据框中，选择您想要的限制类型。例如，要允许某一天之后的日期，请选择大于。

3. 输入开始、结束或特定的日期或时间以允许。

允许指定长度的文本：

1. 在允许框中，点击文本长度。

2. 在数据框中，点击您想要的限制类型。例如，要允许最多一定数量的字符，请单击小于或等于。

3. 输入文本的最小、最大或特定长度。

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

1. 在允许框中，选择您想要的数据类型。

2. 在数据框中，选择您想要的运算符（用于条件）。

3. 在数据框下面的框中，点击要用于指定允许内容的单元格。

例如，要仅在结果不超过预算时允许帐户条目，请单击允许，选择小于或等于数据，并在最大框中单击包含预算金额的单元格。

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

1. 在允许框中，点击自定义。

2. 在公式框中，输入一个计算逻辑值的公式（对于有效条目为 TRUE，对于无效条目为 FALSE）。例如，要只在部门为销售且地区为西部时提供奖励，您可以使用以下自定义公式 =and(d2=”销售”,e2=”西部”)。

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

指定 excel 在输入无效数据时的响应方式：

1. 单击错误警报选项卡，并确保勾选“在输入无效数据后显示错误警报”复选框。

2. 为 Style 框选择以下选项之一：

   • 要显示一个不会阻止输入无效数据的信息消息，请选择 Information。

   • 要显示一个不会阻止输入无效数据的警告消息，请选择 Warning。

   • 为防止输入无效数据，请选择 Stop。

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

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

结论

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

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

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

练习

1. 打开 Advance Excel 作业簿。在名为 Validation 的工作表中，进行以下数据验证。

   1. 不允许在 emp_code 中出现重复。

   2. 只允许在 emp name 中输入文本。

   3. 年龄应该只是数字数据。

   4. 工资应在 5000 到 50000 之间。

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

2. 在 emp_inf 工作表中，在单元格 B3 中创建所有员工代码的下拉列表。

加入我们书籍的 Discord 空间

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

discord.bpbonline.com

第五章保护

介绍

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

结构

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

• 员工信息系统

• 使用密码保护工作表

• 保护工作簿

• 保护工作表的一部分

• 文件密码保护

• 案例研究

目标

在学习本章之后，读者将能够了解如何防止未经授权对工作表进行更改，并学会使用密码保护工作簿。

员工信息系统

在 emp_inf 示例中，如图 5.1 所示，如果我们希望将工作表用作公共模板，我们需要防止未经授权访问 Vlookup 公式。我们只希望将数据输入限制在单元格 B3 中。为此，我们可以使用保护功能。

图 5.1：员工信息系统。

在 Excel 中，有 3 个保护级别，如图 5.2 所示：

图 5.2：保护级别

使用密码保护工作表

现在让我们看一下以下步骤来对工作表进行密码保护：

1. 激活“审阅”功能组。

2. 在“更改”功能组中，点击“保护工作表”以打开“保护工作表”对话框。

3. 选择您想要的选项。

4. 输入密码。

5. 点击确定。

6. 出现“确认密码”对话框。

7. 在“重新输入密码以继续”框中，输入相同的密码以确认。

8. 点击确定以关闭密码确认框和对话框。

保护工作簿

工作簿级别的保护可以通过两种方式实现，如图 5.3 所示：

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

• 保护工作簿窗口，确保工作簿的窗口在每次打开时都是相同的大小和位置。

  

  图 5.3：保护工作簿

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

1. 激活“审阅”功能组。

2. 在“更改”功能组中，点击“保护工作簿”。

3. 在出现的“保护工作簿”对话框中，根据需要选择结构或窗口中的一个或两个选项。

4. 为防止他人移除工作簿保护，您可以设置密码。

5. 在“保护工作簿”对话框中指定选项后，点击确定。参考以下图 5.4：

   

   图 5.4：保护结构和窗口

保护工作表的一部分

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

要仅对工作表的部分进行密码保护，请执行以下步骤：

1. 选择要允许用户修改的单元格范围。

2. 右键单击并选择“格式单元格”以打开“格式单元格”对话框。

3. 激活“保护”选项卡。

4. 清除“锁定”复选框。

5. 点击“确定”。

只有在希望隐藏公式或直接进行第 11 步时才需按照第 6 至 10 步进行：

1. 选择要隐藏用户不可见的带有公式的单元格范围。

2. 右键单击并选择“格式单元格”以打开“格式单元格”对话框。

3. 激活“保护”选项卡。

4. 勾选隐藏复选框以及锁定复选框。

5. 点击“确定”。

从第 11 步开始保护工作表的密码：

1. 激活“审阅”功能区选项卡。

2. 在“更改”功能组中，点击“保护工作表”以打开“保护工作表”对话框，

3. 输入密码。

4. 点击“确定”。

5. 出现“确认密码”对话框。

6. 在“重新输入密码以继续”框中，输入相同的密码。

7. 点击“确定”以关闭密码确认框和对话框。

使用密码保护文件

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

1. 点击“文件”按钮。

2. 选择“另存为”。

3. 在“另存为”对话框中，点击“工具”。

4. 然后点击“常规选项”，如图 5.5 所示。

   

   图 5.5：使用密码保护文件

5. 设置打开或修改密码，视情况而定。

6. 保存文件。

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

结论

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

练习

在名为“练习作业产品-发票”的文件中打开练习文件夹。通过执行以下步骤准备发票模板：

1. 在 M/s 中创建包含所有客户名称的下拉列表。

2. 地址应根据“客户”工作表中的客户名称查找。

3. 产品列将包含在“产品”工作表中列出的所有产品的列表。

4. 费率应根据相邻产品列表中选择的产品进行查找。

5. 第一个“序号”将由供应商填写，其余内容仅在从产品列表中选择产品时才会出现，如下图所示：

   

   图 5.6：产品发票

6. 金额将按照数量*费率计算。

7. 总金额是所有金额的总和。增值税是总金额的 14%。

8. 如果总金额大于 15000，则折扣应按照总金额的 10%计算。

9. 净金额应按照总金额+增值税-折扣计算。

10. 将文件保存为模板。

注意：模板中不应该有明显的错误。

加入我们书籍的 Discord 空间。

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

discord.bpbonline.com

第六章 数据库排序

介绍

数据库中的数据排序是根据诸如字母或数字值等标准按特定顺序排列项目的过程。它能够轻松组织、比较和分析数据，从而实现高效的数据管理和明智的决策。本章探讨了不同的排序技术，包括简单排序、多级排序和自定义排序，为您提供了有效地排列和分析数据库中数据的知识。

结构

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

• 排序的定义

  • 简单排序

  • 多级排序

  • 自定义排序

目标

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

定义

排序是任何一种有序排列项目的过程，即按照某种标准将项目按顺序排列。例如，按照递增或递减顺序对数据进行排序。

简单排序

要对列执行简单排序，请按照以下步骤进行：

1. 选择要排序的列中的任意单元格。

2. 激活“数据”功能区。

3. 在“排序和筛选”组中，单击“升序排序”或“降序排序”按钮。如图 6.1 所示，这将对整个数据库进行排序：

   

   图 6.1：简单排序

多级排序

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

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

1. 选择列表中的任意单元格。

2. 激活“数据”功能区。

3. 在“排序和筛选”功能区中，单击“排序”以打开“排序”对话框。

4. 从“排序依据”列表中，选择您想要按照哪一列的列标题对列表进行排序，选择排序顺序。

5. 所有记录将首先根据您选择的列和排序顺序进行排序。

6. 从“然后按”列表中，选择您想要排序的下一列。

7. 如有必要，通过单击“添加级别”来添加更多“然后按”字段。

8. 当所有“然后按”字段都填写完毕后，单击“确定”，如图 6.2 所示：

   

   图 6.2：多级排序

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

自定义排序

在自定义排序中，列表将按照您指定的顺序对数据进行排序。考虑一个例子，当我们按区域对数据进行排序时，它会按照升序或降序排序，但我们希望按照自定义顺序对数据进行排序。

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

1. 选择列表中的任意单元格。

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

3. 在“排序和筛选”组中，单击“排序”以打开“排序”对话框。

4. 从“排序方式”列表中，选择要按其排序列表的列标题。

5. 从排序顺序中，选择“自定义列表”。

6. 它将打开“自定义列表”对话框。

7. 输入您希望排序的顺序。

8. 点击“添加”按钮将列表添加到自定义排序中。

9. 点击“确定”。

结论

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

练习

参考以下表 6.1 并回答以下问题：

产品	地区	销售代表	销售收入	销售数量
产品 A	北部	约翰	$10,000	50
产品 B	南部	丽莎	$8,500	45
产品 C	西部	马克	$12,500	65
产品 D	东部	萨拉	$9,200	48
产品 A	西部	约翰	$11,800	60
产品 B	北部	丽莎	$9,700	52
产品 C	东部	马克	$10,300	55
产品 D	南部	萨拉	$8,900	47
产品 A	南部	约翰	$9,500	51
产品 B	东部	丽莎	$11,200	58
产品 C	北部	马克	$10,900	57
产品 D	西部	萨拉	$12,700	66

表 6.1：数据库表

1. 根据销售收入列，按销售数据库中的销售收入列降序排序。

2. 通过首先按产品类别按字母顺序排序，然后按销售数量按降序排序，在销售数据库上执行多级排序。

3. 通过为产品名称创建自定义列表并基于该列表进行排序，定制销售数据库的排序方式。

4. 根据库存列中的库存数量升序对库存数据库进行排序。

5. 通过首先按产品类别按字母顺序排序，然后按重新订购级别按升序排序，在库存数据库上执行多级排序。

加入我们书籍的 Discord 空间

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

discord.bpbonline.com

第七章 数据库筛选

介绍

在本章中，我们将探讨在 Excel 中对数据库进行筛选的主题。筛选允许我们仅显示符合特定标准的信息行，使得分析和处理大型数据集变得更加容易。我们将了解不同类型的筛选器，包括自动筛选功能，数字、文本和日期筛选器，以及 Excel 的高级筛选功能。此外，我们将发现如何筛选列表中的唯一记录。通过本章的学习，读者将清楚地了解如何有效地在 Excel 中使用筛选器根据特定标准提取和操作数据。

结构

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

• 筛选器

  • 自动筛选

  • 数字、文本或日期筛选器

  • 使用高级筛选筛选列表

• 筛选唯一记录

目标

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

筛选器

有时，您需要仅显示符合特定标准的信息行。为了帮助您做到这一点，您可以使用筛选器。让我们更多地讨论筛选器。

自动筛选

对于常用标准，Excel 提供了自动筛选功能。以下是它的工作原理：

1. 选择列表中的任何单元格。

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

3. 在“排序和筛选”组中，单击“筛选”以显示每个列标题旁边的自动筛选箭头。

4. 从您想要筛选的列的列表中选择。

5. 选择标准。

6. 点击确定，如图 7.1 所示：

   

   图 7.1：筛选器

要清除筛选并显示整个列表，请再次单击筛选。您可以使用 Excel 的高级筛选功能根据更复杂的标准筛选列表。例如���您可以显示所有那些薪水在 7000 到 12000 之间的员工的记录。

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

• 数字、文本或日期筛选器

• 高级筛选

数字、文本或日期筛选器

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

图 7.2：数字筛选器

参考以下图 7.3：

图 7.3：文本筛选器

参考以下图 7.4：

图 7.4：日期筛选器

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

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

   

   图 7.5：条件范围

2. 激活数据选项卡。

3. 在“排序和筛选”组中，单击“高级”以打开高级筛选对话框（如图 7.6 所示）。

   

   图 7.6：高级筛选

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

使用高级筛选筛选列表

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

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

1. 要创建条件范围，我们需要复制数据库的列标题。

2. 在“条件范围”框中，选择包含您的条件的单元格范围，然后单击“确定”。

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

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

筛选唯一记录

高级筛选也可用于在不同位置筛选列表中的唯一值。虽然 Excel 的删除重复功能可以帮助创建唯一值列表，但如果需要在不同位置复制粘贴唯一值，则需要这样做。为了避免这种情况，请按照以下步骤使用高级筛选选项：

1. 选择要筛选的列或单击范围或列表中的单元格。

2. 在“数据”选项卡上，单击“筛选”。

3. 单击“高级筛选”。

4. 执行以下操作之一：

   • 要在原地筛选范围或列表，类似于使用自动筛选，请单击“在原地筛选列表”。

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

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

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

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

结论

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

练习

1. 打开名为 Filter 的工作表。使用 AutoFilter 根据以下标准查找记录：

   1. 来自北方或南方的人员

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

   3. 在销售或行政部门工作的人员，北方或南方，薪水在 7000 至 12000 之间

   4. 在销售或行政部门工作的人员，北方或南方，薪水在 7000 至 12000 之间或在 15000 至 20000 之间

加入我们书籍的 Discord 空间

加入书籍的 Discord Workspace，获取最新更新、优惠、全球科技动态、新发布和作者讲座：

discord.bpbonline.com

第八章小计和数据合并

介绍

有时，我们需要在报告末尾计算小计，然后是总计。通常，我们通过使用 SUM 函数在每个组的末尾添加一行来执行此操作。尽管这不是做小计的错误方式，但手动干预的数量最大化了错误的可能性。

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

结构

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

• 子总计

• 合并数据

目标

在学习本章后，读者将能够定义小计，学习如何使用小计创建数据的单层和多层摘要，以及识别各种类型的函数。

子总计

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

根据我们所拥有的报告类型，我们需要执行以下两种工作：

• 单层子总计

• 多层子总计

显示单层子总计

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

1. 从数据选项卡 | 大纲组中单击子总计命令。

2. 出现一个子总计对话框，如图 8.1 所示：

   

   图 8.1：子总计

3. 从“在每次更改时”列表框中选择所需的列。

4. 从“使用函数”列表框中选择要对数据执行的函数。

5. 从“添加小计到：”字段中选择要执行小计的列。

单击“确定”按钮后，Excel 会为所选列中相同项目组插入一个小计行。子总计对话框中还有一些其他选项，如图 8.1 所示。具体解释如下：

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

• 选择要总结的值：第一次使用子总计命令时，“添加小计到”框显示最右列的标签。您可以保留所选的标签，也可以选择列表中任何其他列的标签。下次使用子总计命令时，Excel 会显示您上次选择的最后一列的标签。

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

显示嵌套小计

有时，您需要对数据执行多层小计。例如，您需要按区域和部门对数据进行分组。在这种情况下，请按照以下步骤操作：

1. 首先，如前所述，您需要按区域和部门对数据进行排序。

2. 从数据选项卡 | 大纲组中点击“小计”命令。

3. 从“在每次更改时”列表框中选择“区域”列。

4. 从“使用函数”列表框中选择要对数据执行的函数。

5. 从“添加小计到”字段中选择要执行小计的列。

6. 单击“确定”按钮，执行第一层小计。

7. 再次选择“小计”命令，并从“在每次更改时”列表框中选择“部门”列。

8. 从“使用函数”列表框中选择要对数据执行的函数。

9. 从“添加小计到”字段中选择要执行小计的列。

10. 在单击“确定”按钮之前，清除“替换当前小计”的复选框，如图 8.2 所示。

    

    图 8.2：用于嵌套小计的子总对话框

注意：如果您只想复制摘要详细信息，请选择摘要所在的大纲。选择所需的列，按下 Alt 键（选择仅可见单元格），然后复制并粘贴。

合并数据

在 Excel 中合并数据可以将来自多个范围或工作表的数据合并为单个摘要报告。以下是在 Excel 中合并数据的逐步指南：

1. 打开一个新的工作表，您想要合并数据的地方，如图 8.3 所示：

   

   图 8.3：打开一个新的工作表

2. 选择要放置合并数据的单元格，如图 8.4 所示：

   

   图 8.4：选择单元格

3. 单击顶部菜单栏中的“数据”选项卡，然后单击“数据工具”组中的“合并”按钮，如图 8.5 所示：

   

   图 8.5：合并选项

4. 在合并对话框中，选择要用于合并的函数，例如 SUM、AVERAGE、COUNT 等，如图 8.6 所示：

   

   图 8.6：合并对话框

5. 通过单击“引用”字段旁边的“折叠对话框”按钮并选择要合并的单元格来选择要合并的单元格范围。参考图 8.7：

   

   图 8.7：选择要合并的单元格

6. 如果您想 consolide 来自多个工作表的数据，请单击“所有引用”字段中的“添加”按钮，然后选择要 consolide 的附加工作表和单元格范围。参考 图 8.8：

   

   图 8.8：从多个工作表 consolide 数据

7. 对于要包含在 consolide 中的任何其他工作表，请重复上一步骤。

8. 确保如果您不想在 consolide 中包括这些项目，则取消选中“顶部行”和“左列”复选框，如 图 8.9 所示：

   

   图 8.9：标签使用复选框

9. 单击“确定”以 consolide 数据，如 图 8.10 所示：

   

   图 8.10：单击“确定”

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

consolide 数据示例

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

图 8.11：示例工作表

要将这些工作表中的数据 consolide 到单个摘要报告中，请按照给定的步骤进行：

1. 打开一个新的工作表，您想在其中 consolide 数据，就像我们在 图 8.3 中所做的那样。

2. 选择要放置 consolide 数据的单元格，例如，如 图 8.12 所示的单元格 A1：

   

   图 8.12：选择一个单元格

3. 单击顶部菜单栏中的“数据”选项卡，然后单击“数据工具”组中的“consolide”按钮，就像 图 8.5 中所示的那样。

4. 在 consolide 对话框中，选择要用于 consolide 的函数（例如，SUM、AVERAGE、COUNT 等），就像 图 8.6 中所做的那样。

5. 通过单击“引用”字段旁边的“折叠对话框”按钮选择要 consolide 的单元格范围，然后在东工作表上选择要 consolide 的单元格，例如 A1:C10，如 图 8.13 所示：

   

   图 8.13：选择要 consolide 的单元格

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

   

   图 8.14：从多个工作表 consolide 数据

7. 确保如果您不想在 consolide 中包括这些项目，则取消选中“顶部行”和“左列”复选框，如 图 8.9 所示。

8. 点击“确定”以合并数据，如图 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: 回答以下问题：

1. 每个区域的总销售额是多少？

2. 三个区域的总销售额是多少？

加入我们书籍的 Discord 空间

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

discord.bpbonline.com

标签：精通,函数,单元格,Excel,ChatGPT,排序,数据,筛选
From： https://www.cnblogs.com/apachecn/p/18114981