欢迎来到电子表格：创建数据模型！

你好，未来的 ICT 专家！本章的核心是构建功能强大且灵活的工具——电子表格 (spreadsheets) 或 数据模型 (data models)。
为什么这很重要？数据模型允许企业预测结果、管理预算并自动计算数据。如果你能掌握这一点，你就能将原始数据转化为有意义的决策！

快速回顾：什么是电子表格模型？

电子表格模型是现实世界系统的一种数字化呈现，用于模拟流程或根据输入的数据计算结果。你可以把它想象成一个超级智能的计算器与庞大归档系统的结合体。

1. 构建结构（创建与编辑）

在开始计算之前，你需要一个整洁的结构！设计合理的电子表格易于理解且不易出错。

核心结构技能 (20.1 实践)
  • 插入/删除： 你必须能够插入或删除单个单元格 (cells)、整行 (rows) 和整列 (columns)，以便调整你的布局。
  • 合并单元格： 这可以将多个单元格（例如 A1 和 B1）合并为一个大单元格，通常用于在数据上方创建清晰的标题 (headings)。

关键要点： 保持布局逻辑清晰！明确的标签和正确的列使用能让模型更易于管理。

2. 公式与函数：厘清区别

这两个术语经常被混淆，但它们是用于计算的不同工具。理解它们的区别对于考试理论部分至关重要！

公式与函数的区别
  • 1. 公式 (Formulae - 手动方法)

    公式是你手动输入到单元格中以执行计算的指令，以等号 (=) 开头。它使用算术运算符。

    示例： 要计算总销售额，你可以输入 =B5 + C5 + D5

  • 2. 函数 (Functions - 内置快捷方式)

    函数是电子表格软件内置的预定义命令或程序，可以快速执行复杂的计算。

    示例： 要使用函数计算总销售额，你可以输入 =SUM(B5:D5)

算术运算符

你必须知道如何在公式中使用标准的数学符号：

  • 加法：+
  • 减法：-
  • 乘法：*
  • 除法：/
  • 幂运算（指数）：^ (例如：=A1^2 表示 A1 的平方)
运算优先级 (BODMAS/PEMDAS)

电子表格在计算公式时遵循严格的数学顺序。

BODMAS（或 PEMDAS）规则如下：

  1. Brackets（括号）
  2. Orders（指数/幂）
  3. Division 和 Multiplication（除法和乘法，从左到右）
  4. Addition 和 Subtraction（加法和减法，从左到右）

重要提示： 使用括号 () 来强制电子表格先计算公式中的特定部分。
示例： 如果你想在除以 2 之前先将 A1 和 A2 相加，你必须输入 =(A1+A2)/2。如果你输入 =A1+A2/2，它会先执行 A2 除以 2，这通常不是你想要的结果。

快速回顾：公式 vs 函数
公式 = 你手动编写运算（+、-、*）。
函数 = 电子表格执行复杂的预设命令（SUM、AVG）。

3. 单元格引用：复制的力量

电子表格最重要的技能之一是向下或向右复制（Replicating）公式。要正确完成此操作，你必须掌握相对引用和绝对引用的区别。

3.1 相对引用 (默认)


当你复制使用相对引用 (relative reference)（如 A1）的公式时，单元格引用会随着新位置的变化而自动调整。

  • 类比： 你告诉朋友：“走到那扇门（向右走 2 步，向前走 3 步）。”当你的朋友从新的起点出发时，他们仍会从那个新点出发向右走 2 步、向前走 3 步。
  • 实践： 如果你将单元格 D2 中的公式 =B2*C2 向下复制到 D3，公式会自动变为 =B3*C3。
3.2 绝对引用 (锁定)


绝对引用 (absolute reference) 是指在复制公式时不会发生变化的单元格引用。你使用美元符号 ($) 来“锁定”行、列或两者。\n

\n
    \n
  • 类比： 你告诉朋友：“去地图上那个叫‘喷泉’的固定地点。”无论你的朋友站在哪里，他们都必须去那个固定的点。
    • \n
  • 目的： 当公式需要引用一个单一的固定值（例如税率、折扣百分比或存储在特定单元格中的转换系数）时，绝对引用至关重要。
    • \n
\n\n

有三种绝对锁定方式：

\n
    \n
  1. 完全锁定 (绝对)： $A$1
    (复制时列和行都不会改变。)
    2. \n
  2. 混合锁定 (列绝对)： $A1
    (列 A 被锁定，但行 1 可以改变。)
  3. 混合锁定 (行绝对)： A$1
    (行 1 被锁定，但列 A 可以改变。)
    4. \n
\n\n

感到困惑？试试这个技巧！ 构建模型时问问自己：“无论我复制到哪里，这个引用是否总是指向同一个单元格？” 如果答案是 YES，那么你需要 $ 符号！

关键要点： 对于对应的数据（同一行上的数据），使用相对引用；对于所有计算必须使用的固定值（常量），使用绝对 ($) 引用。

4. 高级建模工具：命名区域与函数

为了让复杂的公式更易于阅读和管理，我们使用命名单元格和命名区域。

4.1 命名单元格和区域
  • 它们是什么？ 你可以给 C4 这样的单元格起一个描述性名称，比如 Tax_Rate。
  • 优势： 这会让你的公式更清晰。与其输入 =Sales*C4，不如输入 =Sales*Tax_Rate。这在审计或调试复杂模型时非常有用。
4.2 必备函数（工具箱）

你需要掌握各种函数，既要能在实践任务中使用，也要理解它们的理论用途。

A. 统计与数学函数
  • SUM： 求和。(例如：=SUM(A1:A10))
  • AVERAGE： 计算平均值。(例如：=AVERAGE(B1:B10))
  • MAXIMUM (MAX)： 找出区域中的最大值。
  • MINIMUM (MIN)： 找出区域中的最小值。
  • COUNT： 计算区域中包含数字数据的单元格数量。
B. 取整函数

这些函数对于确保涉及货币或整数（如人数或商品数量）的计算在数学上准确至关重要。

  • ROUND： 将数字四舍五入到指定的小数位数。(例如：=ROUND(A1, 2) 将 A1 保留两位小数。)
  • INTEGER (INT)： 取整，丢弃小数部分，只返回整数。(它总是向下取整，向零的方向靠拢。)
C. 逻辑函数 (IF)
  • IF： 执行逻辑测试，如果条件为真 (TRUE) 返回一个值，如果条件为假 (FALSE) 返回另一个值。
    结构为：=IF(Logical_Test, Value_if_True, Value_if_False)
  • 示例： =IF(B2>50, "PASS", "FAIL")
D. 查找函数 (VLOOKUP, HLOOKUP, XLOOKUP)

查找函数在一个区域（通常是表格）中搜索特定值，并从不同的列或行中返回相应的值。这对于处理费率、价格表或代码非常重要。

  • VLOOKUP： 垂直查找（沿列向下搜索）。
  • HLOOKUP： 水平查找（沿行向右搜索）。
  • XLOOKUP： 一种更现代、更灵活的查找函数，在区域中搜索并返回相应区域的结果。

你知道吗？ 查找表是一种管理复杂定价结构的高效方法，无需编写数十个 IF 语句！

4.3 嵌套函数

嵌套函数是指将一个函数完全放在另一个函数内部。先计算内部函数，其结果作为外部函数的参数。

示例： 如果你想计算平均销售额，并将结果四舍五入到最接近的整数：

=ROUND(AVERAGE(B1:B10), 0)

这里，AVERAGE 函数先算出平均值，其结果立即被传递给 ROUND 函数。

常见错误： 使用嵌套函数时，请务必确保右括号 )) 的数量匹配。漏掉一个括号会导致错误。

5. 使用外部数据源

电子表格模型有时并不包含所有必要的数据。你必须学会如何链接存储在其他地方的数据。

函数内的外部数据

你经常需要使用函数（特别是查找函数）从其他地方检索信息：

  • 其他工作表 (Other Sheets)： 数据可能位于同一个工作簿中的不同工作表中。
  • 其他文件 (Other Files)： 数据可以从完全独立的电子表格文件中链接过来。

这一过程允许你的模型提取定价信息、员工编号或统计常数，而无需手动复制和粘贴，从而保持主模型的整洁且易于更新。

关键要点： 熟练运用公式、函数和引用，就是将简单的网格变成强大、实用的数据模型的关键。