Excel 公式与函数系统教程

第三编 公式与函数系统教程

0. 公式与函数模块导学

文件编码：UTF-8 ｜ 适用平台：Windows、macOS 与 Microsoft 365 网页版

约定：示例采用英文函数名与半角标点；若本机参数分隔符是分号，请把公式中的逗号改为分号。日期示例按公历理解，显示方式受区域设置影响。

模块导学

本节能力目标｜公式与函数模块导学

完成本模块后，学习者应能：

1. 理解公式的计算顺序、数据类型、运算符、隐式转换和浮点误差。

2. 正确选择相对、绝对、混合、跨表、三维和结构化引用。

3. 对文本、数值、日期时间进行可靠清洗与转换。

4. 用条件聚合、查找引用、动态数组及函数式工具建立可维护模型。

5. 正确解释财务与统计函数的参数、符号、假设和边界。

6. 使用审核工具定位错误，并从计算范围、易失函数和模型结构三个层面优化性能。

版本标签

全模块示例数据：销售明细表 tblSales

先把下表粘贴到 A1:I9，按 Ctrl+T 转换为表格，并在“表设计”中命名为 tblSales。

可在 J 列增加“金额”：=[@数量]*[@单价]。表格公式会自动填充整列，也会随新增记录扩展。

第一章 公式基础与引用

1.1 公式的组成 [全版本]

公式以等号 = 开头，可包含常量、单元格引用、运算符、函数、名称和括号。例如：

=ROUND((F2*G2)*(1-$M$2),2)

逐项解释：

• F2*G2 求原价。
• $M$2 是固定折扣率。
• 1-$M$2 得到实付比例。
• 外层 ROUND(...,2) 把结果四舍五入到两位小数。输入公式后按 Enter。
• 按 F2 编辑当前单元格。
• 按 Ctrl+` 显示或隐藏全部公式。

运算符与优先级

不要凭感觉判断复杂表达式，主动加括号。=1+2*3 得 7，=(1+2)*3 得 9。

数据类型与转换

Excel 常见底层类型是数值、文本、逻辑值、错误值和空值。日期是序列号，时间是一天的小数。文本“00123”与数值 123 显示可能相似，但查找和汇总行为不同。用 ISTEXT、ISNUMBER 检验，不要只看对齐方式。数值转文本用 TEXT，文本转数值优先 VALUE 或“数据→分列→完成”。

浮点误差案例： =0.1+0.2=0.3 在某些计算中可能出现意外，因为二进制不能精确表示所有十进制小数。金额比较可写 =ROUND(A1+B1,2)=ROUND(C1,2)，不要开启“以显示精度为准”，后者会永久改写存储值。

1.2 引用方式 [全版本]

在编辑引用时按 F4 循环切换四种状态。假设 B1:D1 是月份，A2:A4 是产品，B2 输入 =$A2*B$1 后向右、向下填充，产品基数始终取 A 列，月份系数始终取第 1 行。

• 跨工作表：='销售数据'!J2；表名有空格时单引号不能省。
• 跨工作簿：='[预算.xlsx]一月'!$B$5；源文件移动后可能产生断链。
• 三维引用：=SUM(一月:十二月!B2)，把连续工作表同一位置相加。插入到首尾工作表之间的新表会被纳入，适合结构完全相同的月表。
• 命名范围：在“公式→名称管理器”创建 税率，公式可写 =J2*(1+税率)。名称应明确作用域，避免工作簿级与工作表级同名。
• 表格结构化引用：=SUM(tblSales[金额])；当前行用 [@金额]；表头到汇总行可用 tblSales[[#All],[金额]]。

基础案例： M2 为税率 13%，J2 输入 =F2*G2*(1+$M$2) 后向下填充。

进阶案例： =SUM(tblSales[数量]*tblSales[单价]) 在动态数组版可直接计算；旧版用 SUMPRODUCT(tblSales[数量],tblSales[单价])。

错误案例： 把税率写成 M2 并向下复制，第二行会引用 M3 空值，结果悄然错误。另一个常见错误是全列结构化引用包含文本表头；应使用数据列语法而不是整张工作表列。

本章动手题｜公式与函数模块导学

1. 在 M2 输入折扣 8%，写出金额乘折后价且复制时固定折扣的公式。

2. 写出对“一月”到“十二月”各表 B5 求和的公式。

3. 解释 $A2 与 A$2 的区别。

第二章 文本、数值与日期的识别和转换

2.1 示例数据

2.2 核心方法

• VALUE(text)：把可识别的文本数字、日期或时间转为数值。区域设置不一致时可能失败。
• NUMBERVALUE(text,[decimal_separator],[group_separator])：显式指定小数与千位符，例如 =NUMBERVALUE("1.234,56",",",".") 得 1234.56。
• TEXT(value,format_text)：将数值格式化为文本，例如 =TEXT(A2,"yyyy-mm-dd")。结果不能直接参与可靠数值汇总，除非再次转换。
• 一元负号 --A2：可把 TRUE/FALSE 或简单文本数值强制转数值，简洁但可读性较差。
• DATEVALUE、TIMEVALUE：分别把可识别的文本日期、时间转成序列值。
• SUBSTITUTE 配合 TRIM、CLEAN：先清理再转换。清除不间断空格可用 =TRIM(SUBSTITUTE(A2,CHAR(160)," "))。

参数逐项： NUMBERVALUE 的第一参数是待转换文本；第二参数是源数据使用的小数分隔符；第三参数是源数据的分组分隔符。它比直接 VALUE 更适合跨国家导入数据。

基础案例： =VALUE(SUBSTITUTE(A2,"￥",""))，若 A2 为 ￥2580 得 2580。

进阶案例： =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)) 将固定格式 20260308 转为日期。更稳健的新版写法仍应先验证长度与数字性。

错误案例： 使用 TEXT(A2,"0.00") 后再与数字比较，可能发生文本排序“100”排在“20”前。编码 00123 则不应转数值，否则前导零丢失。

第三章 条件汇总：SUMIFS、COUNTIFS 与 SUMPRODUCT

3.1 SUMIFS [全版本]

语法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2]...)。

• sum_range：真正被加总的范围。
• criteria_range1：第一个被检查范围，尺寸应与求和范围相同。
• criteria1：条件，可为文本、数字、单元格引用或含运算符的字符串。
• 后续条件成对出现，条件之间是“并且”。

=SUMIFS(tblSales[金额],tblSales[区域],"华东",tblSales[状态],"已完成")
=SUMIFS(tblSales[金额],tblSales[日期],">="&DATE(2026,1,1),tblSales[日期],"<"&DATE(2026,2,1))

第二式使用“>=月初且<下月月初”，比“<=本月最后一天”更能兼容带时间值的日期。运算符必须放在引号中，再用 & 连接日期或单元格。

3.2 COUNTIFS [全版本]

语法：COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]...)。它统计同时满足各条件的行数。通配符 * 代表任意多个字符，? 代表一个字符，查找文字星号用 ~*。

=COUNTIFS(tblSales[区域],"华南",tblSales[状态],"<>退货")
=COUNTIFS(tblSales[产品],"*器",tblSales[数量],">=2")

“或”条件不能简单增加一个条件对，因为多个条件是“且”。可写 =SUM(COUNTIFS(tblSales[区域],{"华东","华南"},tblSales[状态],"已完成"))；旧版也可把两个 COUNTIFS 相加，并注意不要重复计数。

3.3 SUMPRODUCT [全版本]

语法常见为 SUMPRODUCT(array1,[array2]...)。对应位置先相乘，再求和；也可用双负号把条件数组转成 1/0。

=SUMPRODUCT(tblSales[数量],tblSales[单价])
=SUMPRODUCT((tblSales[区域]="华东")*(tblSales[状态]="已完成")*tblSales[数量]*tblSales[单价])
=SUMPRODUCT(--(MONTH(tblSales[日期])=3),tblSales[金额])

基础案例： 统计华东已完成金额，优先用 SUMIFS，语义清晰、速度通常更好。

进阶案例： 需要对日期先取月份或对文本先截取再判断时，SUMPRODUCT 能处理计算后的数组。

错误案例： SUMIFS 的范围分别从第 2 行和第 3 行开始会错位；SUMPRODUCT(A:A,B:B) 对整列进行百万级数组运算，可能极慢；文本数字在乘法中可能被转为 0 或引发不一致，应先清洗。

版本与旧版替代： 三者长期可用。动态数组版可用 SUM(FILTER(...))，但 FILTER 无结果时要提供第三参数；旧版首选 SUMIFS/COUNTIFS/SUMPRODUCT，而不是复杂 CSE 数组公式。

章节练习｜公式与函数模块导学

1. 求 2026 年 3 月“已完成”的金额。

2. 统计数量大于等于 5 且不是退货的记录数。

3. 不使用辅助列，求“华东或华北”的数量×单价合计。

参考答案：

=SUMIFS(tblSales[金额],tblSales[日期],">="&DATE(2026,3,1),tblSales[日期],"<"&DATE(2026,4,1),tblSales[状态],"已完成")
=COUNTIFS(tblSales[数量],">=5",tblSales[状态],"<>退货")
=SUMPRODUCT(ISNUMBER(MATCH(tblSales[区域],{"华东","华北"},0))*tblSales[数量]*tblSales[单价])

第四章 文本函数

4.1 示例数据

4.2 截取、定位与长度 [全版本]

• LEFT(text,[num_chars]) 从左取字符；RIGHT 从右取；MID(text,start_num,num_chars) 从指定位置取。
• LEN(text) 返回字符数；FIND(find_text,within_text,[start_num]) 区分大小写且不支持通配符；SEARCH 不区分大小写并支持通配符。

=LEFT(A2,2) 得国家码；=MID(A2,4,2) 得城市码；=RIGHT(A2,4) 得序号。MID 的位置从 1 开始。=SEARCH("ny",A3) 可找到 NY，FIND 则因大小写不同返回 #VALUE!。

4.3 清洗、替换与连接

• TRIM(text) 删除首尾空格，并把连续普通空格压成一个；不会可靠删除 CHAR(160)。
• CLEAN(text) 删除部分不可打印字符，但不能解决所有 Unicode 隐藏字符。
• SUBSTITUTE(text,old_text,new_text,[instance_num]) 按内容替换；REPLACE(old_text,start_num,num_chars,new_text) 按位置替换。
• CONCAT 连接项目但不能指定分隔符；TEXTJOIN(delimiter,ignore_empty,text1,...) 可指定分隔符并选择是否忽略空值。

=TRIM(SUBSTITUTE(B2,CHAR(160)," "))
=SUBSTITUTE(C2,"|"," / ")
=TEXTJOIN("；",TRUE,D2:F2)

4.4 新版文本拆分 [M365 新版]

TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with]) 会溢出拆分结果；TEXTBEFORE(text,delimiter,[instance_num],...) 取分隔符前；TEXTAFTER 取分隔符后。

=TEXTSPLIT(C2,"|")
=TEXTBEFORE(A2,"-",2)
=TEXTAFTER(A2,"-",-1)

第二式取第二个连字符之前的 CN-SH；第三式从末尾寻找分隔符，得到 0008。旧版替代是“数据→分列”、Power Query，或 LEFT/MID/FIND 组合。复杂多分隔符拆分在旧版不宜硬写超长公式。

4.5 大小写与字符代码

UPPER、LOWER、PROPER 改变英文大小写；EXACT(text1,text2) 区分大小写比较。CHAR/CODE 面向当前字符集，UNICHAR/UNICODE 更适合 Unicode。换行连接常用 CHAR(10)，并开启“自动换行”。

基础案例： =LEFT(A2,2)&RIGHT(A2,4) 得 CN0008。

进阶案例： =LET(x,TRIM(SUBSTITUTE(B2,CHAR(160)," ")),SUBSTITUTE(x," ","")) 先处理隐藏空格，再删除姓名内部空格。

错误案例： 直接 RIGHT(A2,4)+0 会把编码尾号变成数值 8，前导零丢失；SEARCH 找不到文本时返回错误，可用 IFERROR 处理，但不能用 IFERROR 掩盖源数据异常。

第五章 逻辑、条件与错误控制

5.1 IF、IFS、SWITCH [全版本/2019+]

IF(logical_test,value_if_true,value_if_false)：第一参数是能得到 TRUE/FALSE 的测试；第二、三参数是两条分支。

=IF(J2>=5000,"重点订单","普通订单")
=IF(AND(J2>=1000,H2="已完成"),"计奖","不计奖")

AND 要求全部为真，OR 任一为真，NOT 取反，XOR 在奇数个条件为真时返回 TRUE。IFS(test1,value1,...) 按顺序返回第一个真条件；末尾可用 TRUE,"其他" 作为默认。SWITCH(expression,value1,result1,...,[default]) 适合把同一表达式与多个精确值比较。

=IFS(J2>=10000,"A",J2>=5000,"B",J2>=1000,"C",TRUE,"D")
=SWITCH(H2,"已完成",1,"待付款",0.5,"退货",-1,0)

顺序非常重要：等级判断应从高到低。把 J2>=1000 放在最前面会截获所有更高金额。

5.2 IFERROR 与 IFNA

• IFERROR(value,value_if_error) 捕获任意错误。
• IFNA(value,value_if_na) 只捕获 #N/A，更能暴露除零、引用损坏等真正故障。

查找缺失时优先 =IFNA(XLOOKUP(...),"未找到")。不要把整个财务模型包成 IFERROR(...,0)，因为 0 与“模型失败”含义完全不同。

基础案例： =IF(F2=0,"无数量",J2/F2) 防止除零。

进阶案例： =IF(OR(H2="退货",H2="取消"),0,IF(J2>=5000,J2*3%,J2*1%))。

错误案例： 数字阈值写成文本 "5000" 可能发生隐式转换；深层嵌套 IF 难维护，应改 IFS、SWITCH 或建立规则表查找。

版本与旧版替代： IF/AND/OR 全版本；IFS、SWITCH 为较新版本，旧版使用嵌套 IF 或查表。动态数组条件不要用 AND(FILTER条件数组)，因为 AND 会压缩成单一值；应使用数组乘法表示“且”、加法表示“或”。

第六章 查找引用：VLOOKUP、INDEX/MATCH、XLOOKUP、XMATCH

6.1 查找表示例 tblProduct

6.2 VLOOKUP [全版本]

语法：VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])。

1. lookup_value：待查值。

2. table_array：查找表，待查列必须位于最左侧。

3. col_index_num：从表最左列起计数的返回列序号。

4. range_lookup：FALSE/0 精确匹配；TRUE/1 或省略为近似匹配，首列必须升序。

=VLOOKUP(A2,tblProduct[[产品编码]:[标准价]],4,FALSE)

错误案例： 省略第四参数会默认近似匹配，未排序时可能返回“看似合理”的错值；在中间插列后固定列序号 4 可能指向错误列；VLOOKUP 不能自然向左查找。

6.3 INDEX 与 MATCH [全版本]

MATCH(lookup_value,lookup_array,[match_type]) 返回位置；精确匹配用 0。INDEX(array,row_num,[column_num]) 根据位置返回值。

=INDEX(tblProduct[标准价],MATCH(A2,tblProduct[产品编码],0))

优点是查找列可位于任意方向，插列不易破坏。二维查找可写：

=INDEX(B2:M10,MATCH(P2,A2:A10,0),MATCH(P3,B1:M1,0))

第一 MATCH 找行，第二 MATCH 找列。近似 MATCH 的 1/-1 模式要求严格排序，除非明确做分档，否则不要使用。

6.4 XLOOKUP [M365/2021+]

语法：XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])。

• lookup_value：要查的值。
• lookup_array：一行或一列查找区域。
• return_array：返回区域，可为多列。
• if_not_found：未找到的自定义结果。
• match_mode：
• 0 精确。
• -1 精确或下一个较小项。
• 1 精确或下一个较大项。
• 2 通配符。
• search_mode：1 从首到尾；-1 从尾到首；2/-2 对升序/降序数据二分查找，数据必须正确排序。

=XLOOKUP(A2,tblProduct[产品编码],tblProduct[[产品]:[标准价]],"未找到")
=XLOOKUP(A2,记录[客户],记录[金额],"无记录",0,-1)

第一式一次溢出三列；第二式从尾到首取得客户最后一条记录。旧版替代为 INDEX/MATCH；返回多列时分别写公式或使用旧版数组 INDEX。

6.5 XMATCH [M365/2021+]

XMATCH(lookup_value,lookup_array,[match_mode],[search_mode]) 是 MATCH 的增强版，精确匹配默认值为 0，并支持反向和二分搜索。

=XMATCH("显示器",tblProduct[产品],0)
=INDEX(tblProduct[标准价],XMATCH(A2,tblProduct[产品编码]))

基础案例： 按产品编码取标准价。

进阶案例： 多条件查找：=XLOOKUP(1,(表[客户]=A2)*(表[日期]=B2),表[金额],"未找到")。旧版用 INDEX(返回列,MATCH(1,(条件1)*(条件2),0))，早期版本可能需 CSE。

错误案例： 一边是文本编码 001，另一边是数值 1，精确匹配失败；通配符查询中真实 * 要写 ~*；二分搜索用于未排序数据会给错误结果而不一定报错。

本章检查题｜公式与函数模块导学

1. 用 VLOOKUP 按编码精确返回类别。

2. 用 INDEX/MATCH 返回标准价。

3. 用 XLOOKUP 返回产品和类别两列，找不到显示“待维护”。

参考答案：

=VLOOKUP(A2,tblProduct[[产品编码]:[类别]],3,FALSE)
=INDEX(tblProduct[标准价],MATCH(A2,tblProduct[产品编码],0))
=XLOOKUP(A2,tblProduct[产品编码],tblProduct[[产品]:[类别]],"待维护")

第七章 动态数组与数组整形

7.1 溢出机制 [M365/2021+]

• 一个公式可返回多个结果，并从公式单元格向周围“溢出”。蓝色边框表示溢出区域。
• A2# 引用 A2 产生的整个溢出结果。
• @ 表示隐式交叉，只取与当前行对应的单值。溢出目标被占用会产生 #SPILL!。
• 动态数组公式不能在 Excel 表格内部向多格溢出，应放在表格外，但可以引用表格列。

7.2 FILTER、SORT、SORTBY、UNIQUE、SEQUENCE

=FILTER(tblSales,tblSales[状态]="已完成","无结果")
=IF(COUNTIF(tblSales[区域],"华东")=0,"无结果",SORT(FILTER(tblSales[[销售员]:[金额]],tblSales[区域]="华东"),7,-1))
=UNIQUE(tblSales[销售员])
=SEQUENCE(12,1,1,1)

• FILTER(array,include,[if_empty])：include 必须与数组行数或列数匹配；“且”用乘法，“或”用加法。
• SORT(array,[sort_index],[sort_order],[by_col])：排序列序号相对于传入数组。
• SORTBY(array,by_array1,[sort_order1]...)：直接按独立数组排序，插列更稳健。
• UNIQUE(array,[by_col],[exactly_once])：第三参数 TRUE 只返回仅出现一次的值，而不是普通去重。
• SEQUENCE(rows,[columns],[start],[step])：生成序列。

多条件筛选：=FILTER(tblSales,(tblSales[区域]="华东")*(tblSales[金额]>=1000),"无结果")。若“华东或华南”，改用 ((区域="华东")+(区域="华南"))>0。

7.3 TAKE、DROP、CHOOSECOLS [M365 新版]

• TAKE(array,rows,[columns])：取开头若干行/列；负数从末尾取。
• DROP(array,rows,[columns])：删除开头若干行/列；负数从末尾删除。
• CHOOSECOLS(array,col_num1,...)：按列号选择并可重排；负列号可从末尾计。

=TAKE(SORTBY(tblSales,tblSales[金额],-1),5)
=DROP(tblSales,1)
=CHOOSECOLS(tblSales,2,1,4,10)

第一式取金额最高 5 单；第二式丢弃第一条数据，不是丢弃表头，因为结构化引用默认只含数据体；第三式按指定顺序输出订单号、日期、销售员、金额。

7.4 VSTACK 与 HSTACK [M365 新版]

VSTACK(array1,[array2]...) 纵向追加，列数不足处补 #N/A；HSTACK 横向拼接，行数不足处补 #N/A。

=VSTACK(一月!A2:J100,二月!A2:J100)
=HSTACK(A2:A10,C2:C10,E2:E10)

追加前应确保字段顺序和数据类型一致。空白尾行也会被追加，可先 FILTER 非空记录。跨工作簿动态数组在源工作簿关闭时可能受限。旧版替代是 Power Query“追加/合并”、复制粘贴或 INDEX 辅助区域。

基础案例： =SORT(UNIQUE(tblSales[产品])) 生成排序后的产品下拉源。

进阶案例： =TAKE(SORTBY(CHOOSECOLS(tblSales,2,4,10),tblSales[金额],-1),3) 输出金额前三名的订单号、销售员、金额。

错误案例： 把结果放在已有内容旁导致 #SPILL!；用整列 FILTER(A:A,A:A<>"") 造成巨大数组；输出区域包含合并单元格也会阻止溢出。

第八章 LET、LAMBDA、MAP、REDUCE、BYROW、BYCOL

8.1 LET [M365/2021+]

语法：LET(name1,name_value1,[name2,name_value2...],calculation)。名称与值成对定义，最后一个参数是返回表达式。名称只能在 LET 内部使用，不能与引用语法冲突。

=LET(qty,tblSales[数量],price,tblSales[单价],amount,qty*price,SUM(amount))

qty、price、amount 分别缓存数量、单价和逐行金额；最后求和。优点是减少重复计算、提高可读性并可能提升性能。

8.2 LAMBDA [M365 新版]

语法：LAMBDA([parameter1,...],calculation)。直接在单元格中测试时需立刻调用：

=LAMBDA(x,rate,x*(1+rate))(1000,13%)

要复用，可在名称管理器创建名称 含税价，引用位置填 =LAMBDA(x,rate,ROUND(x*(1+rate),2))，然后在表中写 =含税价(J2,13%)。参数不能包含句点，参数数量有限；递归没有终止条件会返回 #NUM!。

8.3 MAP [M365 新版]

MAP(array1,[array2...],lambda) 对数组对应元素逐一调用 LAMBDA。

=MAP(tblSales[数量],tblSales[单价],LAMBDA(q,p,ROUND(q*p,2)))
=MAP(A2:A10,LAMBDA(x,IF(x="","",TRIM(x))))

LAMBDA 参数个数必须与输入数组个数一致。MAP 适合逐元素转换，不适合在每次调用中返回大小不一的嵌套数组。

8.4 REDUCE [M365 新版]

REDUCE([initial_value],array,lambda(accumulator,value,body)) 从初始值开始逐项累计，最终返回一个结果。

=REDUCE(0,tblSales[金额],LAMBDA(acc,v,IF(v>=1000,acc+v,acc)))
=REDUCE("",A2:A5,LAMBDA(acc,v,IF(v="",acc,IF(acc="",v,acc&"、"&v))))

第一式累计金额至少 1000 的记录；第二式自定义连接非空文本。acc 保存上一步结果，v 是当前元素。求普通总和时 SUM 更清晰，REDUCE 应用于需要自定义状态的累计过程。

8.5 BYROW 与 BYCOL [M365 新版]

BYROW(array,LAMBDA(row,calculation)) 对每行返回一个结果；BYCOL 对每列返回一个结果。

=BYROW(B2:M10,LAMBDA(rowValues,SUM(rowValues)))
=BYCOL(B2:M10,LAMBDA(colValues,AVERAGE(colValues)))

BYROW 的 LAMBDA 通常必须为每行返回单值；试图返回整行排序结果可能报 #CALC!。若要保留整形数组，应考虑 MAP、MAKEARRAY 或其他组合。

8.6 组合实战

=LET(
 data,FILTER(tblSales,tblSales[状态]="已完成"),
 compact,CHOOSECOLS(data,2,3,4,10),
 sorted,SORTBY(compact,CHOOSECOLS(compact,4),-1),
 VSTACK({"订单号","区域","销售员","金额"},TAKE(sorted,5))
)

逐行解释：

• data 筛出已完成。
• compact 选择四列。
• sorted 按第 4 列降序。
• 最后添加自定义表头并取前五。若不足五行，TAKE 会返回现有行。

旧版替代：

• LET 可用辅助列或重复表达式。
• LAMBDA 可用普通命名公式/VBA 自定义函数。
• MAP 用逐行公式。
• REDUCE 用 SUMPRODUCT、辅助累计列或 VBA。
• BYROW/BYCOL 用 SUM、MMULT、逐行复制。旧版替代更应重视可读性，不建议为了“一格公式”制造不可维护表达式。

错误案例：

• LET 最后漏写返回表达式。
• LAMBDA 在名称管理器中测试不足导致 #CALC!。
• 把易失函数放进 MAP 对十万行逐项运行造成卡顿。
• 变量名 c 在 R1C1 环境可能冲突，建议用 colData 等明确名称。

本章动手题｜公式与函数模块导学

1. 用 LET 只计算一次 数量*单价，返回平均金额。

2. 创建 LAMBDA，把数值限制在 0 到 100。

3. 用 BYROW 计算 B2:E10 每行最大值与最小值之差。

4. 用 REDUCE 计算正数之和。

参考答案：

=LET(a,tblSales[数量]*tblSales[单价],AVERAGE(a))
=LAMBDA(x,MAX(0,MIN(100,x)))
=BYROW(B2:E10,LAMBDA(r,MAX(r)-MIN(r)))
=REDUCE(0,A2:A20,LAMBDA(acc,v,IF(v>0,acc+v,acc)))

第九章 日期与时间函数

9.1 日期序列与构造 [全版本]

在 Windows 默认 1900 日期系统中，日期由序列号表示；时间为一天的分数，例如 12:00 约为 0.5。不同工作簿若采用 1904 日期系统，复制日期可能相差 1462 天，需在选项中核对。

• DATE(year,month,day) 构造日期，月份和日可溢出：DATE(2026,4,0) 得 3 月最后一天。
• TIME(hour,minute,second) 构造时间。
• YEAR、MONTH、DAY 提取年月日；HOUR、MINUTE、SECOND 提取时间。
• TODAY() 返回当前日期；NOW() 返回当前日期时间，二者均为易失函数，会在重新计算时变化。

9.2 日期推移与区间

• EDATE(start_date,months) 按月移动并尽量保留日号。
• EOMONTH(start_date,months) 返回偏移月的月末。
• DATEDIF(start_date,end_date,unit) 计算完整年/月/日等；它是兼容函数，函数提示可能不完整。
• 直接相减得到天数；时间差乘 24 得小时数。

=EDATE(A2,3)
=EOMONTH(A2,0)
=DATEDIF(A2,B2,"y")&"年"&DATEDIF(A2,B2,"ym")&"个月"

DATEDIF 的 "md"、"ym"、"yd" 在某些边界日期可能不符合直觉；严谨合同天数优先直接日期相减并明确是否含首尾日。

9.3 工作日函数

NETWORKDAYS(start_date,end_date,[holidays]) 返回含首尾的工作日数，默认周六周日休息。WORKDAY(start_date,days,[holidays]) 推算若干工作日后的日期。

NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays]) 和 WORKDAY.INTL 可自定义周末。weekend 可用代码或 7 位字符串，字符串从星期一到星期日，1 表示休息、0 表示工作。例如周五周六休息："0000110"。

=NETWORKDAYS.INTL(A2,B2,"0000011",假日表[日期])
=WORKDAY.INTL(A2,10,"0000011",假日表[日期])

9.4 WEEKDAY、WEEKNUM 与 ISO 周

WEEKDAY(serial_number,[return_type]) 推荐第二参数 2，使周一=1、周日=7。WEEKNUM(date,2) 不是严格 ISO 周；ISO 周使用 ISOWEEKNUM(date)。跨年周次尤其需要明确计算规则。

基础案例： 月初 =EOMONTH(A2,-1)+1，月末 =EOMONTH(A2,0)。

进阶案例： 统计 2026 年 3 月工作日：=NETWORKDAYS(DATE(2026,3,1),EOMONTH(DATE(2026,3,1),0),假日范围)。

错误案例： 把文本 2026/3/8 直接写在公式中会被当作除法；应使用 DATE(2026,3,8)。结束时间跨午夜时 结束-开始 为负，可用 =MOD(B2-A2,1)，但超过 24 小时的持续时间不能这样处理。

版本与旧版替代： DATE/TODAY/WORKDAY 等广泛可用；INTL 与 EOMONTH 在较老版本曾需分析工具库。新版生成日期日历可用 =SEQUENCE(结束-开始+1,,开始)，旧版用填充序列。

第十章 财务函数

财务函数必须统一现金流符号：流入为正、流出为负。利率与期数单位必须一致；月供使用月利率和总月数。下列结果受舍入、期初/期末付款及实际现金流日期影响。

10.1 年金五件套：PMT、PV、FV、RATE、NPER

示例：贷款 300000 元，年利率 4.2%，期限 20 年，按月期末等额还款。

PMT(rate,nper,pv,[fv],[type])：

• rate 每期利率，本例 4.2%/12；
• nper 总期数，本例 20*12；
• pv 现值，即当前收到的贷款 300000；
• fv 期末余额，默认 0；
• type 0/省略为期末付款，1 为期初付款。

=PMT(4.2%/12,20*12,300000,0,0)
=-PMT(4.2%/12,20*12,300000)

第一式返回负数，表示付款流出；第二式为了展示正的月供金额加负号。

PV(rate,nper,pmt,[fv],[type]) 求未来现金流的现值：=PV(5%/12,36,-3000,0,0)。

FV(rate,nper,pmt,[pv],[type]) 求终值：每月月末投入 2000，年化 6%，5 年后 =FV(6%/12,60,-2000,0,0)。

RATE(nper,pmt,pv,[fv],[type],[guess]) 迭代求每期利率：=RATE(36,-3000,100000)*12 为名义年率近似。guess 是初始猜测，默认 10%；不收敛时尝试合理 guess，并检查现金流是否至少一正一负。

NPER(rate,pmt,pv,[fv],[type]) 求期数。

进阶案例： 年有效利率不是简单月利率乘 12。若月利率在 B2，年有效利率 =(1+B2)^12-1；由年有效利率转月利率 =(1+年有效利率)^(1/12)-1。

10.2 NPV 与 XNPV

NPV(rate,value1,[value2]...) 假定各现金流等间隔且第一笔发生在第 1 期末，不会自动把初始投资当作第 0 期。

示例现金流：期初投资 -100000，随后四年分别 30000、35000、40000、25000，折现率 8%。

=NPV(8%,B3:B6)+B2

其中 B2 是期初现金流，不能放进 NPV 后再不调整，否则会多折现一期。

XNPV(rate,values,dates) 使用实际日期折现，第一日期为基准。values 与 dates 数量必须一致，日期应为真实日期，且至少一正一负。

=XNPV(8%,B2:B6,A2:A6)

10.3 IRR 与 XIRR

IRR(values,[guess]) 求等间隔现金流使 NPV=0 的每期内部收益率。XIRR(values,dates,[guess]) 适用于不规则日期，并返回年化率。

=IRR(B2:B6)
=XIRR(B2:B6,A2:A6)

多次符号变化可能存在多个 IRR，或无经济意义的解；此时应绘制 NPV 对折现率曲线，并结合 XNPV、资金成本和项目规模判断，不能只比较 IRR。IRR 还隐含中间现金流按 IRR 再投资的争议。

10.4 财务函数错误案例

1. 年利率直接配月数：PMT(4.2%,240,...) 会把 4.2% 当月利率，严重高估月供。

2. 所有现金流同号：IRR/RATE 无法找到根，常见 #NUM!。

3. 日期是文本：XNPV/XIRR 可能 #VALUE!；用 ISNUMBER 验证日期底层值。

4. 把 NPV 的第 0 期现金流放入函数范围，却忘记它会被折现一期。

5. 使用显示为百分比但实际值是 8 的单元格，8 表示 800%，而 8% 应存为 0.08。

10.5 版本与旧版替代

PMT/PV/FV/NPV/IRR/RATE 长期可用；XNPV/XIRR 在现代桌面版普遍可用，极老版本可能依赖分析工具库。没有 XNPV 时可手工写：=SUMPRODUCT(values/(1+rate)^((dates-MIN(dates))/365))；求 XIRR 则可用“单变量求解”令该表达式为 0。手工替代应固定日计数约定，并记录假设。

章节练习｜公式与函数模块导学

1. 借款 500000，年利率 3.6%，30 年，求期末支付月供。

2. 每月期初存 1500，年化 5%，10 年后的终值。

3. 解释为何 NPV(10%,全部现金流) 可能错误。

4. 不规则日期项目应选 IRR 还是 XIRR？

参考答案：

• =-PMT(3.6%/12,30*12,500000,0,0)。
• =FV(5%/12,120,-1500,0,1)。
• 因为 NPV 把范围首项视为第 1 期末，期初投资应在函数外相加。
• 选 XIRR，并确保 values 与 dates 对齐。

第十一章 统计函数与数据解释

11.1 示例数据

11.2 集中趋势与离散程度 [全版本]

• AVERAGE(number1,...) 算术平均；忽略引用范围内空白和文本，但显式逻辑值行为需留意。
• MEDIAN 中位数，对极端值更稳健。
• MODE.SNGL 单众数；MODE.MULT 返回多个众数，旧版可能需数组输入。
• MIN、MAX 极值。
• STDEV.S、VAR.S 用于样本；STDEV.P、VAR.P 用于完整总体。选择不是看数据量大小，而是看数据是否为总体或抽样。

=AVERAGE(B2:B9)
=MEDIAN(B2:B9)
=STDEV.S(B2:B9)

11.3 排名、分位数与频数

RANK.EQ(number,ref,[order]) 排名，相同值同名次；order 0/省略降序，非 0 升序。RANK.AVG 对并列者给平均名次。

PERCENTILE.INC(array,k) 返回包含端点定义的第 k 分位数，k 在 0 到 1；PERCENTILE.EXC 排除端点，样本太小时可能 #NUM!。QUARTILE.INC 的 quart 为 0~4。报告中必须注明使用 INC 还是 EXC。

COUNT 统计数值单元格，COUNTA 统计非空，COUNTBLANK 统计空白。公式返回 "" 的单元格在不同函数中不等同真正空白，审计时要区分。

FREQUENCY(data_array,bins_array) 返回各区间频数；动态数组版会溢出，比 bins 多一个“超过最高上限”的组。旧版需先选中输出区域并用 CSE。

11.4 相关、回归与分布

• CORREL(array1,array2) 返回皮尔逊相关系数，范围 -1 到 1；相关不代表因果。
• COVARIANCE.S/P 样本/总体协方差。
• SLOPE(known_y's,known_x's) 线性回归斜率；INTERCEPT 截距；RSQ 决定系数。
• FORECAST.LINEAR(x,known_y's,known_x's) 线性预测；外推风险需要业务判断。
• LINEST 返回更完整回归统计，动态数组版可直接溢出；旧版需数组输入。
• NORM.DIST(x,mean,standard_dev,cumulative) 正态分布，cumulative=TRUE 返回累计概率，FALSE 返回密度。
• NORM.INV(probability,mean,standard_dev) 返回给定累计概率的分位点。

=CORREL(B2:B9,C2:C9)
=SLOPE(B2:B9,C2:C9)
=FORECAST.LINEAR(15,B2:B9,C2:C9)
=NORM.DIST(80,75,10,TRUE)

11.5 条件统计与稳健分析

AVERAGEIF(range,criteria,[average_range]) 与 AVERAGEIFS(average_range,criteria_range1,criteria1,...) 做条件平均。注意 AVERAGEIFS 的参数顺序与 SUMIFS 相似，而 AVERAGEIF 的平均范围在最后。

截尾平均 TRIMMEAN(array,percent) 从两端合计剔除指定比例，实际剔除数会向下调整为偶数。大样本异常值分析可同时报告均值、中位数、标准差和四分位距：Q3-Q1。

基础案例： A 组平均分 =AVERAGEIF(D2:D9,"A",B2:B9)。

进阶案例： z 分数 =(B2-AVERAGE($B$2:$B$9))/STDEV.S($B$2:$B$9)；复制时锁定总体范围。

错误案例： 把缺考记为 0 会拉低平均，而空白会被忽略；两种处理都应有业务定义。相关分析的两个数组长度必须相同，且缺失值应成对处理，不能分别删除后错位。

11.6 版本与旧版替代

.S/.P 命名在现代版本推荐，兼容函数 STDEV、VAR 仍可能存在但不应新建使用。新版可用 FILTER 先筛选再统计；旧版用 AVERAGEIFS、SUMPRODUCT 或辅助列。动态数组中 =PERCENTILE.INC(FILTER(...),0.9) 很自然，旧版可用数组 IF，但维护成本较高。

本章检查题｜公式与函数模块导学

1. 求分数中位数和样本标准差。2. 求 A 组第 90 百分位。3. 学时为 15 时做线性分数预测。4. 解释 STDEV.S 与 STDEV.P 的选择。

参考答案：

• =MEDIAN(B2:B9)。
• =STDEV.S(B2:B9)。
• 新版 =PERCENTILE.INC(FILTER(B2:B9,D2:D9="A"),0.9)。
• =FORECAST.LINEAR(15,B2:B9,C2:C9)。
• 样本推断总体用 S，数据覆盖定义总体用 P。

第十二章 公式错误、审核与性能优化

12.1 常见错误值诊断

12.2 系统化审核流程

1. 复现与界定： 保存副本，记录预期、实际、触发条件，不要立即用 IFERROR 包住。

2. 检查输入： 用 =ISNUMBER(A2)、=LEN(A2)、=A2=TRIM(A2) 识别类型与隐藏字符。

3. 分解公式： 在编辑栏选中子表达式按 F9 临时求值，查看后按 Esc，切勿按 Enter 把公式替换成结果。

4. 公式→公式审核： 使用“错误检查”“追踪引用单元格”“追踪从属单元格”“公式求值”“监视窗口”。

5. 显示公式： Ctrl+` 检查是否存在硬编码、断裂引用或某一行公式模式不同。

6. 检查名称与链接： 名称管理器查 #REF!；“数据→查询和连接/工作簿链接”核对外部源。

7. 建立对账： 总额、行数、控制合计与源系统对比；关键结果旁保留“差额应为 0”的控制单元格。

12.3 循环引用与迭代

循环引用可能是错误，也可能是刻意的迭代模型。状态栏会显示循环引用地址。优先重构消除；确需迭代时，在“文件→选项→公式”设置最大迭代次数和最大误差，并在模型说明中记录收敛逻辑。迭代可能依赖初始值与计算顺序，不应用于无法验证的关键报表。

12.4 性能原则

1. 限制范围： 用 A2:A100000、Excel 表格列或动态命名范围代替数组函数中的整列 A:A。

2. 避免重复： 用 LET 缓存昂贵表达式；把重复查找存入辅助列。

3. 减少易失函数： NOW、TODAY、RAND、RANDBETWEEN、OFFSET、INDIRECT 每次重算；可改 INDEX、表格引用或固定参数日期。TODAY 合理，但不要在百万行重复调用，可在一个参数单元格调用一次。

4. 减少单线程与间接引用： INDIRECT 还会阻止追踪、关闭源文件时受限；OFFSET 可改 INDEX(起点,行数) 构造非易失范围。

5. 优先内置聚合： 能用 SUMIFS 就不要用对整列的 SUMPRODUCT；能一次 XLOOKUP 返回多列就不要重复多次查找同一键。

6. 控制动态数组尺寸： 先 FILTER/CHOOSECOLS 缩小，再 SORT；不要生成百万行 SEQUENCE。

7. 拆分层次： 输入层、清洗层、计算层、输出层分离。适量辅助列往往比一个巨型公式更快、更容易审核。

8. 谨慎手动计算： 大模型可临时“公式→计算选项→手动”，但交付前必须按 Ctrl+Alt+F9 完整重算、核对并恢复约定设置。手动模式是 Excel 应用级状态，会影响其他工作簿。

9. 测量而非猜测： 复制工作簿，在相同数据上比较重算时间。排查时逐块替换公式，定位最慢区域。

12.5 性能重构案例

低效公式在 100000 行中重复：

=IFERROR(VLOOKUP(A2,产品表!A:Z,18,FALSE),0)+TODAY()*0

问题：全列宽表、固定列号、IFERROR 掩盖错误、每行调用 TODAY。可改为：在参数单元格 P1 写 =TODAY()；主公式 =IFNA(XLOOKUP(A2,tblProduct[产品编码],tblProduct[标准价]),NA())。若旧版则用 INDEX/MATCH，并把查找范围限制为表格列。NA() 让缺失项可见，待业务确认后再决定显示 0 还是“未维护”。

12.6 审核练习

1. =VLOOKUP(A2,产品表!A:D,4) 偶尔返回错误商品价格，指出原因并修正。

2. =SUMPRODUCT(A:A,B:B) 很慢，提出两种优化。

3. =IFERROR(复杂财务公式,0) 为什么危险？

4. 动态数组出现 #SPILL! 时列出三项检查。

第十三章 综合项目：可维护的销售分析公式模型

项目目标

从 tblSales 构建一个可选择月份和区域的摘要区，输出 KPI、明细、前三名与错误控制。假设 B2 为所选月份任意日期，B3 为区域，允许填写“全部”。

步骤一：设置参数与验证

B2 使用真实日期并显示为 yyyy-mm；B3 使用数据验证列表。C2 写月初 =EOMONTH(B2,-1)+1，D2 写下月初 =EOMONTH(B2,0)+1。这比拼接文本月份可靠。

步骤二：筛选数组

=LET(
 start,$C$2,
 next,$D$2,
 region,$B$3,
 keep,(tblSales[日期]>=start)*(tblSales[日期]<next)*IF(region="全部",1,tblSales[区域]=region),
 FILTER(tblSales,keep,"没有符合条件的订单")
)

IF 在这里返回 1 或区域布尔数组，使“全部”不再限制区域。

步骤三：KPI

=SUMIFS(tblSales[金额],tblSales[日期],">="&$C$2,tblSales[日期],"<"&$D$2,tblSales[区域],IF($B$3="全部","*",$B$3))

若区域可能为空或含真实星号，通配符方案不够严谨。更稳妥可用：

=LET(k,(tblSales[日期]>=$C$2)*(tblSales[日期]<$D$2)*IF($B$3="全部",1,tblSales[区域]=$B$3),SUMPRODUCT(k*tblSales[金额]))

订单数可对订单号做 ROWS(UNIQUE(FILTER(...)))；无结果时需先判断，避免把提示文本算作一行。

步骤四：前三名

=LET(
 k,(tblSales[日期]>=$C$2)*(tblSales[日期]<$D$2)*IF($B$3="全部",1,tblSales[区域]=$B$3),
 d,FILTER(CHOOSECOLS(tblSales,2,4,5,10),k),
 VSTACK({"订单号","销售员","产品","金额"},TAKE(SORTBY(d,CHOOSECOLS(d,4),-1),3))
)

步骤五：控制与验收

• 源金额控制：=SUM(tblSales[金额])-SUM(tblSales[数量]*tblSales[单价])，应为 0。
• 缺失编码数：=SUM(--ISNA(XMATCH(tblSales[产品],tblProduct[产品])))。
• 日期异常：最小日期、最大日期与文本日期数量分别检查。
• 抽查：随机选择三条记录手算；分别测试无结果、单条结果、全部区域、退货状态、月底带时间五类边界。
• 性能：用一次 LET 产生 k，不要在同一公式中反复计算筛选条件。

项目练习与答案要点

1. 增加“仅已完成”开关：在条件数组乘以 IF(B4="全部状态",1,tblSales[状态]=B4)。

2. 输出各销售员金额：新版可先 u=UNIQUE(FILTER(销售员,k))，再 HSTACK(u,MAP(u,LAMBDA(x,SUM(FILTER(金额,k*(销售员=x))))))；旧版用数据透视表或 SUMIFS 摘要表。

3. 输出累计占比：金额降序数组 a 后，用 SCAN 最简；若限定本模块函数，可对每个序号用 MAP 加 TAKE 后 SUM，再除总额。对于大数据更建议辅助列或数据透视表。

附录 A 函数选型速查

附录 B 交付前公式检查清单

• [ ] 输入、计算、输出区域清晰，参数单元格有单位和数据验证。
• [ ] 日期为真实序列值，金额和比例的显示格式不代替数据类型。
• [ ] 复制公式后，相对/绝对/混合引用符合预期。
• [ ] 查找明确指定精确、近似、反向或通配模式；重复键有业务规则。
• [ ] 条件范围与求和/平均范围尺寸一致。
• [ ] 财务函数的利率期数同单位，现金流正负号一致，第 0 期处理正确。
• [ ] 样本/总体统计规则明确，空白、0、缺失值处理有说明。
• [ ] 未用 IFERROR 把未知错误全部变成 0。
• [ ] 动态数组留有溢出空间，并提供无结果处理。
• [ ] 没有不必要的全列数组、易失函数、INDIRECT/OFFSET 和重复查找。
• [ ] 已完成完整重算、边界测试、手工抽查、源数据对账与版本兼容测试。

附录 C 综合自测题

1. 为什么日期条件建议写成“>=月初且<下月月初”？

2. SUMIFS 如何表达“华东或华南”？

3. VLOOKUP 省略第四参数的风险是什么？

4. XLOOKUP 的 match_mode 与 search_mode 分别控制什么？

5. FILTER 的多条件“且”和“或”如何表示？

6. LET 为什么可能提升性能？

7. MAP 与 REDUCE 的核心差异是什么？

8. TAKE 的负数行参数表示什么？

9. NPV 为什么常把初始投资放在函数外？

10. IRR 出现多个解的根本原因是什么？

11. STDEV.S 与 STDEV.P 应如何选？

12. 为什么 IFERROR 不是审核工具？

1. 可正确包含整月中带时间的记录，避免月底 23:59:59 边界遗漏。2. 两个 SUMIFS 相加，或数组常量后 SUM；需考虑重复条件。3. 默认近似匹配，未排序数据可返回错误且不报错。4. 前者控制精确/近似/通配，后者控制搜索方向或二分算法。5. 条件数组相乘表示且，相加后大于 0 表示或。6. 它为重复表达式命名并缓存，减少重复计算。7. MAP 逐元素返回数组，REDUCE 维护累加器并最终汇总。8. 从数组末尾取指定行数。9. NPV 将第一参数现金流视为第 1 期末，初始投资发生在第 0 期。10. 非常规现金流多次改变符号，使净现值方程可能有多个根。11. 抽样推断总体用 S，完整总体用 P。12. IFERROR 只改变显示结果，可能掩盖根因；审核应先定位输入、引用和计算步骤。

模块结束。 建议学习者使用本模块示例建立独立练习工作簿，并在每章完成后同时提交“正确结果、公式可读性、错误测试、版本兼容”四项证据。

附录 D 重点函数逐项回顾检查卡

SUMIFS 回顾检查卡

作用边界： 条件必须成对出现；先确定求和列，再逐一口头读出“哪一列满足什么”。文本条件直接加引号，比较条件把运算符置于引号并用 & 连接参数单元格。

基础验证： 先用 3～5 行微型数据手算预期，再输入公式；随后改变一个条件，确认结果方向正确。

进阶验证： 增加空值、文本数字、重复键、错误值和边界日期，观察公式是传播错误、忽略项目还是返回替代结果。

错误验证： 故意删去一列、改变数据类型或制造无匹配情况，确认控制单元格能暴露问题。

版本处理： 新版函数交付给旧版用户前，必须在目标环境打开测试；不能仅凭文件扩展名判断兼容。旧版替代优先选择辅助列、INDEX/MATCH、SUMPRODUCT 或 Power Query，而不是不可读的超长数组式。

COUNTIFS 回顾检查卡

作用边界： 每个条件区域必须具有相同形状。空字符串、真正空白和含空格单元格可能得到不同计数，清洗后再统计。

SUMPRODUCT 回顾检查卡

作用边界： 布尔数组通过双负号或乘法转为 1/0。它能处理计算型条件，但大数组成本高，且错误值会传播。

TEXTJOIN 回顾检查卡

作用边界： ignore_empty 只忽略真正空值和部分空字符串情形，不能自动去掉只含空格的文本；必要时先 TRIM/FILTER。

XLOOKUP 回顾检查卡

作用边界： 默认精确匹配是重要改进；返回数组高度或宽度必须与查找数组方向一致。重复键默认取首个，反向搜索可取最后一个。

LET 回顾检查卡

作用边界： 名称应表达业务含义，如 validRows、netAmount，而不是 a、b。把筛选条件、清洗结果和中间数组分别命名，便于公式求值。

LAMBDA 回顾检查卡

作用边界： 先在单元格内以立即调用方式测试，再放入名称管理器；给名称添加注释，说明参数、返回值、错误约定和版本。

XIRR 回顾检查卡

作用边界： 结果是按实际日期折算的年化率。日期顺序最好升序，首日为折现基准；极端猜测值和非常规现金流可能不收敛。

BYROW/BYCOL 回顾检查卡

作用边界： 它们用于按行或按列压缩维度，每一行或列通常只返回一个标量。若需要多值结果，应重新设计数组形状。