我的个人技术笔记

变量与函数

12 min read

变量与函数

在 Excel 中，传统公式并不能像编程语言那样显式声明变量，这导致复杂公式常常需要多次重复相同的计算片段，既影响性能又降低可读性。LET 函数的出现彻底改变了这一局面，它允许我们在一个公式内部定义和使用变量，把原本冗长、嵌套的表达式梳理得层次分明。

以下是对 Excel 中“变量”概念及 LET 函数的全面解析，结合你之前关注过的查找、筛选、类型判断与字符串操作，给出实战级的用法。

一、Excel 中“变量”的演进

在 LET 问世之前，Excel 主要有三种方式模拟变量：

方式原理缺点
辅助单元格把中间结果写在额外单元格中，再在主公式中引用占用工作表空间，可读性差，数据源变动时代码散乱
名称管理器将某个计算表达式定义为名称（如“税率”），再在公式中用名称调用名称是工作簿级或工作表级，修改不便，且不易传递参数
公式内重复计算直接在同一公式中多次复制相同的子表达式增加计算负担，复杂公式极难维护

LET 函数（Excel 2021 / Microsoft 365）可以让你在 一个公式内部 用类似 变量 = 值 的方式定义命名变量，然后在后续计算中反复引用该变量。变量的值只会被计算一次，之后都使用缓存结果。

二、LET 函数语法详解

=LET(name1, value1, calculation_or_name2, [value2, ...], calculation)
参数说明
name1第一个变量的名称，不需要引号，不能是单元格引用或数字开头（如 x, myVal）
value1第一个变量的值（可以是常量、引用、表达式）
name2, value2...（可选）最多可定义 126 个变量，成对出现
calculation最终返回的计算表达式，必须位于最后一参数，可以使用前面定义的所有变量

基本执行逻辑：

  1. 从左到右依次计算 value1, value2, …，并把结果赋给对应的名称。
  2. 执行最后的 calculation，返回其结果到单元格。
  3. 所有变量只在当前 LET 公式内有效，无法跨单元格引用。

三、经典应用实例

假设仍沿用之前的员工数据 A1:D9（员工ID、姓名、部门、工资）。

1. 消除重复计算，提升性能

传统写法如果要根据“张三”的工资计算提成和税后金额，工资查找会计算两次：

=XLOOKUP("张三", B2:B9, D2:D9)*0.1  +  XLOOKUP("张三", B2:B9, D2:D9)*0.85

使用 LET 后，只计算一次：

=LET(
   salary, XLOOKUP("张三", B2:B9, D2:D9),
   salary*0.1 + salary*0.85
)

如果 XLOOKUP 需要遍历大量数据，性能提升明显。

2. 分步计算，彻底提高可读性

计算员工“王五”的实发工资（基本工资+绩效-社保），绩效根据工资分段：

=LET(
   base, XLOOKUP("王五", B2:B9, D2:D9),
   perf, IF(base>6000, base*0.15, base*0.1),
   insurance, 500,
   base + perf - insurance
)

变量名让逻辑一目了然，后期修改也只需调整对应的变量定义。

3. 与 FILTER 结合，构建动态汇总

筛选“销售部”员工，计算该部门工资总额与平均工资，并一次性返回多个结果（通过 HSTACK 或数组常量）：

=LET(
   sales, FILTER(A2:D9, C2:C9="销售部"),
   total, SUM(INDEX(sales, , 4)),
   avg, AVERAGE(INDEX(sales, , 4)),
   HSTACK("总工资", total, "平均工资", avg)
)

输出两列结果，无需重复执行 FILTER。

4. 字符串处理中的变量妙用

前面你已经掌握了大量字符串函数，用 LET 可以轻松解开多层嵌套。

示例：提取邮箱中 @ 前后的用户名和域名，并组合成“用户名[at]域名”

=LET(
   email, A2,
   pos, FIND("@", email),
   user, LEFT(email, pos-1),
   domain, RIGHT(email, LEN(email)-pos),
   user & "[at]" & domain
)

如果不使用 LET，FIND 和 LEN 的重复会使公式难以阅读。

5. 处理错误与类型判断

结合 ISNUMBER、TYPE 等判断数据类型，并给出安全输出。

示例：判断 B 列是否为合法的文本型日期（如 “2023-12-25”），若合法则转为真日期，否则返回“无效”

=LET(
   txt, B2,
   dateVal, DATEVALUE(txt),
   IF(ISNUMBER(dateVal), dateVal, "无效")
)

更复杂的场景可嵌套多个变量，将检测逻辑拆分。

6. 多层变量嵌套与顺序依赖

变量可以基于前面已定义的变量计算，形成推导链。

示例：已知半径，计算圆的周长、面积及球体积（π 用常量定义）

=LET(
   r, 5,
   pi, 3.14159265358979,
   circumference, 2*pi*r,
   area, pi*r^2,
   volume, (4/3)*pi*r^3,
   "周长:" & TEXT(circumference,"0.00") & "，面积:" & TEXT(area,"0.00") & "，体积:" & TEXT(volume,"0.00")
)

四、LET + LAMBDA：迈向自定义函数

LAMBDA 函数允许将一段计算逻辑封装成可调用的自定义函数，其参数本身就是变量。将 LET 与 LAMBDA 结合，可以构建结构清晰、无副作用的函数库。

示例：创建一个名为 TAX 的函数，根据收入计算个税（采用简易规则：5000 以下免税，超过部分 10%）

  1. 定义名称（公式 → 名称管理器）：
名称: TAX
引用位置: =LAMBDA(income, LET(excess, income-5000, IF(excess<=0, 0, excess*0.1)))
  1. 工作表中直接使用：
=TAX(D2)   // D2 为工资

LAMBDA 的 income 作为外部变量传入，内部 LET 再用它计算超额部分，结构干净。

如果没有 LET，LAMBDA 内部往往需要重复计算 income-5000，现在一次定义即可。

五、LET 与其他“变量”方式的对比

特性辅助单元格/列名称管理器LET 函数
作用域工作表全局工作簿/工作表级当前公式内部
是否动态传参否否（只能引用固定单元格）是（在公式内可以按需构建）
是否会占据单元格是否否
可复用性弱中等低（仅单个公式内），但与 LAMBDA 结合可复用
性能影响可能有大量中间单元格很好优秀（减少重复计算）
调试友好度需查看中间单元格需去名称管理器查看可在公式栏直接阅读，但内部变量无法直接追踪

推荐原则：

  • 仅在单个单元格计算中间步骤使用 → LET
  • 需要全局常量或通用计算且不传递参数 → 名称管理器
  • 需要多次调用同一逻辑并传入不同参数 → LAMBDA 自定义函数
  • 需要跨公式共享中间结果且数据量不大 → 辅助列仍是最简单的选择

六、性能与最佳实践

  1. 优先用 LET 消除重复计算：尤其涉及 XLOOKUP、FILTER、SUMIF 等扫描大范围的操作，定义一次变量可让公式快一个数量级。
  2. 变量命名要有意义：别用 a, b, c，推荐用 totalSales、taxRate 这类能说明用途的名称（支持中文命名，如 工资, 税率）。
  3. 注意顺序：后续变量只能使用之前定义的变量，不能向前引用。
  4. 错误处理提前：可在第一个变量中就用 IFERROR 包裹可能出错的查找，避免整个公式崩溃。
  5. 避免过度嵌套：虽然可定义 126 个变量，但超过 5~10 个应考虑拆分为 LAMBDA 或辅助列，以保持维护性。
  6. 版本兼容：保存为 .xlsx 并确保接收方使用 Excel 2021 或 365，否则公式显示 #NAME?。

七、完整示例：综合查找、筛选与字符串格式化

假设你需要一个单元格完成：根据部门（放在 F1）筛选该部门所有员工的姓名和格式化工资，并连接成一个摘要字符串。

传统方法可能需要数个辅助列，用 LET 可以一气呵成：

=LET(
   dept, F1,
   filtered, FILTER(B2:D9, C2:C9=dept),
   names, INDEX(filtered, , 1),
   salaries, INDEX(filtered, , 3),
   formatted, names & "(" & TEXT(salaries,"¥#,##0") & ")",
   TEXTJOIN(", ", TRUE, formatted)
)

如果 F1 为“销售部”，将显示类似：张三(¥6,000), 王五(¥5,500)。

这里 filtered 只执行一次，后续多次取列、格式化均使用缓存，简洁高效。

八、速查总结

场景LET 解决方案
减少重复计算将重复部分定义为变量，末尾引用
提升公式可读性用变量名替代嵌套表达式
构建中间逻辑分步定义，逐步推导到最终结果
与 FILTER/XLOOKUP 配合将查找或筛选结果存入变量，再加工
复杂字符串处理提取位置、分割、替换等每一步都存为变量
创建自定义函数结合 LAMBDA，在名称管理器中封装逻辑

LET 函数让你在 Excel 公式中拥有了真正的局部变量能力，把过去需要多个辅助列或庞大嵌套公式才能完成的任务，压缩在一个单元格内，同时保持清晰和高效。掌握 LET，是迈向 Excel 高级公式自动化的重要一步。

Graph View