Spreadsheets

学习笔记：主题 8 – 电子表格 (9626 AS Level IT)

你好，未来的 IT 专业人士！电子表格或许是你在这门课程中掌握的最重要的实用技能。它们不仅仅是用来计算加减乘除的；它们是全球通用的强大工具，广泛应用于财务分析、数据建模、报告撰写和问题解决。

本章将教会你如何构建稳健、安全、经过测试且外观精美的电子表格。掌握这些技能对于 Paper 2（实践考试）至关重要，并将显著加深你对数据分析（主题 17）和建模（主题 9）等相关主题的理解。让我们开始吧！

8.1 创建电子表格：结构、公式与函数

页面结构与呈现

设计良好的电子表格必须满足受众和任务需求。这通常需要在录入数据前进行仔细的布局规划。

• 页面方向： 根据要打印的数据选择纵向（Portrait）或横向（Landscape）。例如，拥有多列的月度预算表通常需要使用横向。
• 页面尺寸： 通常为 A4 或 Letter，具体取决于地区标准。
• 适应页面： 使用缩放选项确保整个工作表整齐地打印在指定数量的页面上（例如，将所有列缩放到页面宽度之内）。
• 页边距： 调整打印区域周围的留白。
• 页眉和页脚： 用于包含标准信息，如标题、日期、页码或文件路径，以方便文档管理。

工作簿与工作表管理

为了处理复杂数据，你需要能够高效地操作网格结构。

• 插入/删除/隐藏行与列： 这是整理新数据或暂时移除敏感信息的必要手段。
• 调整行高与列宽： 调整大小以确保所有数据可见（例如，使用“自动调整列宽”功能）。
• 合并单元格： 将多个单元格组合在一起（通常用于在多列中间居中显示标题）。（使用合并时要小心，因为它有时会使计算变得复杂！）
• 保护内容： 确保数据完整性。你必须能够保护单个单元格、行、列、整个工作表或整个工作簿结构。这通常涉及设置密码，以防止未经授权的人员更改公式或结构。
• 冻结窗格： 锁定特定的行（通常是标题行）或列，使其在用户滚动查看大数据集时始终保持可见。

小贴士： 在商业环境中，保护功能非常关键。如果你为员工创建了一个录入销售额的表格，你需要保护包含计算公式的单元格，以防止产生错误。

控制数据输入（数据验证）

数据验证可确保输入的数据合理且符合要求。

• 验证技术： 使用电子表格功能限制输入。例如，确保日期以正确的格式输入，或者数字处于特定范围内。
• 下拉菜单（列表验证）： 最常见的技术，强制用户从预定义的列表中选择（例如，选择“男”或“女”，而不是手动输入“M”或“F”）。
• 输入信息与出错警告： 当用户选中单元格时提供有用的提示（输入信息），并在输入无效数据时提供清晰、具体的警告（出错警告）。

8.1 公式、函数与引用

理解引用（核心概念）

引用决定了公式在复制到其他单元格时如何变化。

类比： 想象一下指路：

• 相对引用（例如 A1）： 引用会根据其新位置相对变化。如果你将公式从 B1 复制到 C1，A1 就会变成 B1（“去隔壁那家。”）
• 绝对引用（例如 $A$1）： 列和行都被锁定。复制时引用永远不会改变。（“永远去主街1号这个确切地址。”）
• 混合引用（例如 $A1 或 A$1）： 一部分（列或行）被锁定，另一部分是相对的。（“$A1”意味着始终查看 A 列，但行号会随移动而改变。）

你还需要学会使用命名单元格和命名区域（例如，引用“TaxRate”而不是“$B$5”）。这提高了可读性，并使跨工作表或工作簿的引用变得更简单。

核心计算与汇总函数

这些函数执行数学和统计运算：

• 基本算术： 加法 (\(+\))、减法 (\(-\))、乘法 (\(*\))、除法 (\(/\)) 以及指数（幂运算，例如 \(A1^2\)）。
• SUM： 将一系列数字相加。
• AVERAGE： 计算算术平均值。
• MIN, MAX： 分别查找最小值和最大值。
• MAXA / MINA： 查找列表中的最大值或最小值，包括文本和逻辑值（通常仅在数值数据中使用 MIN/MAX）。
• INT： 向下舍入到最接近的整数。
• ROUND： 将数字四舍五入到指定的位数。
• SUBTOTAL： 为经过筛选或分组的数据计算汇总函数（如 SUM 或 AVERAGE）。

计数与条件汇总函数

这些函数用于根据特定条件统计单元格数量或计算总和/平均值。

• COUNT： 统计包含数字的单元格数量。
• COUNTA： 统计非空单元格（包含文本或数字）。
• COUNTBLANK： 统计范围内的空单元格数量。
• COUNTIF / COUNTIFS： 统计符合单个条件 (COUNTIF) 或多个条件 (COUNTIFS) 的单元格数量。
• SUMIF / SUMIFS： 对符合单个条件 (SUMIF) 或多个条件 (SUMIFS) 的单元格求和。
• AVERAGEIF / AVERAGEIFS： 计算符合单个或多个条件的单元格平均值。
• MAXIF / MAXIFS / MINIF / MINIFS： 根据指定条件查找范围内的最大值或最小值。

查找函数（数据检索）

查找函数根据匹配值从表格中检索数据。

• VLOOKUP： 在区域的第一列中搜索值，并返回该行中指定列的值（垂直查找）。
• HLOOKUP： 在区域的第一行中搜索值，并返回该列中指定行的值（水平查找）。
• XLOOKUP： 一种现代且强大的查找函数，在某些软件中正取代 VLOOKUP/HLOOKUP，允许在任意列进行搜索。
• LOOKUP： 一种基础且灵活性较低的查找函数。
• INDEX / MATCH： 两者结合使用通常比 VLOOKUP 更灵活高效，允许你根据区域内的位置进行搜索和返回结果。

决策、逻辑、日期与文本函数

决策/逻辑函数： 用于做出选择或组合条件。

• IF / 嵌套 IF： 检查条件是真还是假，并根据结果返回一个值。嵌套 IF 是指将一个 IF 函数放在另一个 IF 函数内，以处理多个结果。
• IFS： 一种现代函数，无需复杂的嵌套即可处理多个条件。
• AND / OR： 在 IF 语句中使用，用于检查所有条件是否为真 (AND) 或至少有一个条件为真 (OR)。

日期与时间函数： 用于处理基于时间的数据。

• DATE / TIME： 用于构建有效的日期或时间值。
• DAY / MONTH / YEAR： 从日期中提取相应的分量。
• WEEKDAY： 返回给定日期是一周中的第几天。

字符串/文本函数： 用于处理文本数据。

• 连接字符串： 将两个或多个文本字符串连接在一起（例如，合并姓氏和名字）。
• 提取数值： 从文本字段中提取数字。
• LEFT, RIGHT, MID： 从字符串的左侧、右侧或中间提取指定数量的字符。
• FIND： 查找一个文本字符串在另一个文本字符串中的位置。
• 测试单元格内容： 检查单元格是否包含文本值、数值或为空（例如，使用 IF 语句结合 ISNUMBER 或 ISTEXT 等函数）。

错误捕获与数据旋转

• ISERROR / IFERROR： 用于检查公式是否导致错误（例如 #DIV/0!）。IFERROR 常用于显示空白单元格或自定义信息，而不是难看的错误代码。
• TRANSPOSE： 旋转数据，将列转换为行，将行转换为列。

重点总结（函数）： 将函数视为快捷方式。与其手动添加 100 个单元格，不如使用 SUM。在解决实际问题时，首先识别所需的函数类型：计算、计数、逻辑还是查找。

8.2 测试电子表格

满是错误的电子表格毫无用处！无论是计算（公式）还是结构（布局、保护、验证），都需要进行测试。

设计测试计划

测试计划必须系统地检查电子表格结构的所有关键部分：

• 测试函数：计算（SUM、AVERAGE、IF 语句）是否返回预期结果？
• 测试验证规则：系统能否正确拒绝异常数据并接受正常/边界数据？
• 测试条件格式：当单元格值满足定义条件时，其颜色或样式是否正确改变？

基本测试数据类型

测试时，你必须选择能够确保稳健性的数据。

• 正常数据： 预期输入且有效的数据（例如，如果范围是 1 到 100，正常数据为 50）。
• 边界数据（Extreme Data）： 处于可接受范围边界或极限的有效数据（例如，范围的边界值，即 1 和 100）。
• 异常数据： 应该被验证拒绝的无效数据（例如，如果期望输入数字，异常数据可以是文本，或者是范围之外的数字，如 101 或 0）。

常见错误： 学生常忘记测试*异常*数据。如果无法拒绝错误数据，验证功能将毫无价值！

8.3 使用与分析数据

数据提取与搜索

为了关注特定信息，你需要使用数据提取和搜索技术，这通常通过筛选（Filter）工具完成。

• 按数据类型搜索：文本、数值或日期和时间值。
• 使用布尔运算符 (AND, OR, NOT)：例如，筛选部门为“销售”且工资大于 50,000 美元的员工。
• 使用比较运算符：>（大于）、<（小于）、=（等于）、>=、<=。
• 使用文本筛选：搜索包含、以……开头或以……结尾的特定文本字符串。

数据排序

排序将数据排列成有意义的顺序。

• 升序： A 到 Z，1 到 10，或日期从旧到新。
• 降序： Z 到 A，10 到 1，或日期从新到旧。
• 数据可以在单列或多列上进行排序（例如，先按“部门”排序，再按“姓氏”排序）。

汇总数据

在处理海量数据集时，汇总对于管理和分析至关重要。

• 分类汇总： 当指定列中的数据发生变化时，自动计算并显示汇总数据（如 SUM 或 COUNT）。它还会创建大纲组，以便折叠或展开数据。
• 数据透视表（Pivot Tables）： 一种功能强大的工具，用于快速对大数据集进行交叉制表、分组和汇总。它允许用户在不更改原始数据的情况下动态地重新排列和分析数据。
• 数据透视图： 直接由数据透视表创建的图表，确保在调整底层数据透视表时视觉显示即时更新。

数据导入与导出

• 导入数据： 将外部数据引入电子表格，通常来自 CSV（逗号分隔值）或 TXT（纯文本）文件。
• 导出数据： 以不同格式保存电子表格或报告，如 CSV、TXT 或 PDF（用于静态报告），甚至将特定区域导出为图表。

你知道吗？ CSV 文件对于数据交换至关重要，因为它们简单且通用，这意味着几乎任何软件（数据库、电子表格、分析工具）都能识别它们。

8.4 图表

数据可视化对于清晰交流至关重要，尤其是面对非专业人士时。你必须选择并格式化合适的图表类型。

创建图表（选择数据）

第一步是选择正确的数据序列：

• 使用连续数据（数据序列相邻，例如 A 列和 B 列）。
• 使用不连续数据（数据序列分开，例如 A 列和 D 列）。这通常需要选择第一个区域，按住 Ctrl/Cmd 键，然后选择第二个区域。
• 使用指定区域（例如 A1:A10）。

选择正确的图表类型

你的选择完全取决于目的：

• 条形图 / 比较条形图： 非常适合比较离散的数据类别或并排显示多个序列（例如，比较 5 个不同地区的销售额）。
• 饼图： 显示整体的比例或百分比（例如，市场份额）。你必须能够分离出一块（扇区）以作强调。
• 折线图 / 比较折线图： 用于显示连续时间内的趋势或变化（例如，一年的股票价格波动）。
• 组合图： 显示两种不同类型的数据（例如，在同一张图表上显示气温（折线）和降雨量（条形））。

基本的图表格式化

格式化能将基础图表转化为专业报告。

• 标题： 清晰且具有描述性的标题。
• 图例： 解释每种颜色/图案/线条代表什么。
• 坐标轴标签（类别和数值）： 清晰标注 X 轴（类别）和 Y 轴（数值）。
• 刻度（最大值和最小值）： 在数值轴上设置适当的限制，以避免图形失真。
• 数据间隔： 定义坐标轴上标记的步长或频率。
• 数据标签、值和百分比： 特别适用于饼图，确保扇区通过数值或百分比清晰标识。
• 序列标签： 标注单个数据线或条。
• 添加次坐标轴： 当两个数据序列的量级差异巨大时，在组合图中使用（例如，将数千个销售单位与利润百分比绘制在一起）。

重点总结（图表）： 当被要求创建图表时，务必证明你所选类型的理由（条形图用于比较，折线图用于趋势，饼图用于比例），并确保所有必要的标签和格式元素都已包含，以确保清晰易懂。