每位职场人士都应掌握的核心电子表格公式

电子表格上的财务数据

电子表格是全球使用最广泛的编程环境。数百万职场人士每天依靠那些三年前从教程里半懂不懂学来的公式做出关键决策。掌握一套核心公式，能让电子表格从华而不实的表格摇身变为强大的数据分析工具。

公式的基本结构

每个公式都以 = 开头，可以引用以下内容：

单元格引用：A1、B2:D10（相对引用）或 $A$1、$B$2:$D$10（绝对引用）
命名区域：用 =SUM(Revenue) 代替 =SUM(B2:B100)
函数：SUM、VLOOKUP、IF、TEXT 等
常量：数字、用引号括起来的文本、TRUE/FALSE

相对引用与绝对引用： 复制公式时，相对引用会随之偏移，而绝对引用（带 $）则保持不变。

=B2*C2       → 向下复制后变为 =B3*C3、=B4*C4...
=B2*$C$1     → C1 保持固定（例如税率），B2 随行偏移

查找公式

VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index, [range_lookup])

在表格第一列中查找某个值，并返回另一列中对应的值：

=VLOOKUP(A2, $E$2:$G$100, 2, FALSE)

A2 — 要搜索的值
$E$2:$G$100 — 查找区域（用 $ 锁定）
2 — 返回表格第 2 列的值
FALSE — 精确匹配（查找时始终使用 FALSE）

VLOOKUP 的局限性： 只能从左向右查找，插入列会导致列索引号错位。

INDEX / MATCH（更优的替代方案）

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

比 VLOOKUP 更灵活，可向任意方向查找：

=INDEX($G$2:$G$100, MATCH(A2, $E$2:$E$100, 0))

返回 G 列中与 E 列匹配 A2 的对应值，插入列也不会导致公式出错。

XLOOKUP（新版 Excel / Google Sheets）

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

两者的现代替代方案：

=XLOOKUP(A2, $E$2:$E$100, $G$2:$G$100, "Not found")

语法更简洁，能优雅处理未找到的情况，支持任意方向查找。

条件公式

IF

=IF(condition, value_if_true, value_if_false)
=IF(B2>1000, "High", "Low")
=IF(C2="", "Missing", C2)

嵌套 IF（建议改用 IFS）

=IF(B2>=90,"A", IF(B2>=80,"B", IF(B2>=70,"C","F")))

-- 用 IFS 更简洁：
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"F")

在条件中使用 AND / OR

=IF(AND(B2>100, C2="Active"), "Priority", "Normal")
=IF(OR(D2="VIP", E2>10000), "Discount", "")

聚合公式

SUMIF / SUMIFS

对满足条件的值求和：

=SUMIF(range, criteria, sum_range)
=SUMIF(B:B, "Electronics", C:C)   -- 对 B 列等于 "Electronics" 的行求 C 列之和

-- 多条件：
=SUMIFS(C:C, B:B, "Electronics", D:D, ">2025-01-01")

COUNTIF / COUNTIFS

=COUNTIF(A:A, "Active")           -- 统计 A 列等于 "Active" 的行数
=COUNTIF(B:B, ">1000")            -- 统计 B 列大于 1000 的行数
=COUNTIFS(A:A, "Active", C:C, "US")  -- 同时满足两个条件

AVERAGEIF

=AVERAGEIF(B:B, "Electronics", C:C)  -- 对 B 列等于 "Electronics" 的行求 C 列平均值

文本公式

合并文本

=CONCATENATE(A2, " ", B2)          -- 旧语法
=A2 & " " & B2                     -- 现代语法（推荐）
=TEXTJOIN(", ", TRUE, A2:A10)      -- 用分隔符连接，跳过空值

提取文本

=LEFT(A2, 3)                       -- 前 3 个字符
=RIGHT(A2, 4)                      -- 后 4 个字符
=MID(A2, 3, 5)                     -- 从第 3 位开始的 5 个字符
=LEN(A2)                           -- 总字符数
=FIND("@", A2)                     -- @ 在字符串中的位置

清理文本

=TRIM(A2)                          -- 去除首尾空格
=UPPER(A2) / =LOWER(A2)           -- 转换大小写
=PROPER(A2)                        -- 首字母大写
=SUBSTITUTE(A2, " ", "_")         -- 将空格替换为下划线
=CLEAN(A2)                         -- 删除不可打印字符

日期与时间公式

=TODAY()                           -- 当前日期
=NOW()                             -- 当前日期和时间
=YEAR(A2) / =MONTH(A2) / =DAY(A2) -- 提取日期各部分
=DATEDIF(A2, B2, "D")             -- 两个日期之间的天数
=DATEDIF(A2, B2, "M")             -- 两个日期之间的完整月数
=DATEDIF(A2, B2, "Y")             -- 完整年数（适合计算年龄）
=NETWORKDAYS(A2, B2)              -- 两个日期之间的工作日数（不含周末）
=EDATE(A2, 3)                     -- A2 日期之后 3 个月的日期
=EOMONTH(A2, 0)                   -- A2 所在月份的最后一天

数据处理：实用操作模式

使用 UNIQUE 去除重复项（新版）

=UNIQUE(A2:A100)                   -- 列出唯一值

使用 FILTER 实现动态筛选

=FILTER(A2:C100, B2:B100="Active")   -- 筛选 B 列等于 "Active" 的行
=FILTER(A2:C100, (B2:B100="Active")*(C2:C100>1000))  -- 同时满足两个条件

使用 SORT 动态排序

=SORT(A2:C100, 2, -1)              -- 按第 2 列降序排列

数据转换与导出

当电子表格工作完成，需要将数据迁移到其他系统时：

使用我们的 Excel to CSV 转换器导出为 CSV，实现通用兼容
使用 CSV Merger 合并多个 CSV 文件
使用 JSON to Excel 将数据转换为 JSON 以供 API 调用（支持双向转换）

常见错误及规避方法

在公式中硬编码数值 — 请改用单元格引用。=B2*0.08 日后会让人费解；而 =B2*$D$1（D1 标注为"税率"）则一目了然。
复制公式时忘记锁定引用 — 漏写 $ 会导致公式引用错误的单元格。
使用 TRUE 参数的 VLOOKUP（近似匹配）— 此模式要求数据已排序，数据未排序时会产生意外结果。
循环引用 — 公式引用自身（或引用了引用它的单元格），Excel 会发出警告。
数据类型混用 — 将数字以文本形式存储（单元格中左对齐显示）会导致 SUM 和排序失效。

精通电子表格是一项核心竞争力。上述公式能应对 90% 的实际数据处理需求。在真实数据集上反复练习，你将能够轻松驾驭各种复杂公式。