Excel 中级函数案例详解：动态数组、日期计算与批量处理
用三张真实 Excel 示例表讲解 REPLACE、FILTER、LET、BYROW、MAP、REDUCE 等 18 个中级函数，展示公式区域、动态结果和批量计算过程。
前面的文章已经解决了函数分类、39 个基础函数、查找匹配和通配符。这一篇继续完成剩下的 18 个中级函数。
中级函数并不是单纯把公式写得更长，而是开始解决三类问题：按日期周期计算、让结果自动扩展、把同一套规则批量应用到多行多列。
全文使用三张独立示例表。每一组先看源数据和代表性计算过程，再查看 6 个函数的紧凑案例。
中级函数的阅读方法
确认公式处理的是一个单元格、一列数据、整张表，还是一个行列矩阵。
动态数组只写一个公式，也可能返回多行多列结果。
BYROW 按行、BYCOL 按列、MAP 按元素、REDUCE 按顺序累计。
先理解每个函数负责什么，再把筛选、排序、判断和错误处理串起来。
一、文本与日期：把固定格式转换成可统计字段
这一组函数适合处理编码、平台标记、周报日期和活动周期。先建立 编码日期表：
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品编码 | 上架日期 | 平台 | 店铺 |
| 2 | BOW-KB-001 | 2026/7/1 | 淘宝 | 旗舰店 |
| 3 | BOW-MS-002 | 2026/7/2 | 京东 | 数码店 |
| 4 | BOW-CS-003 | 2026/7/5 | 拼多多 | 配件店 |
| 5 | BOW-KB-004 | 2026/7/8 | 淘宝 | 键盘店 |
=WEEKDAY(B2,2)3星期三=WEEKNUM(B2,2)27第 27 周=DATEDIF(B2,DATE(2026,7,15),“d”)14相差 14 天=EDATE(B2,3)2026/10/1三个月后REPLACE按字符位置替换把商品编码开头的 BOW 统一替换成 SKU。
=REPLACE(A2,1,3,“SKU”)- 结果
- SKU-KB-001
- 关键点
- 从第 1 个字符开始，替换 3 个字符。
CONCAT直接拼接文本把平台、店铺和商品编码拼成统一标记。
=CONCAT(C2,”-“,D2,”-“,A2)- 结果
- 淘宝-旗舰店-BOW-KB-001
- 关键点
- CONCAT 不会自动增加分隔符，需要在公式中自己写。
WEEKDAY返回星期序号判断 B2 是一周中的第几天。
=WEEKDAY(B2,2)- 结果
- 3
- 关键点
- 第二个参数写 2 时，星期一为 1，星期日为 7。
WEEKNUM返回年度周数把上架日期转换成周报使用的周数。
=WEEKNUM(B2,2)- 结果
- 27
- 关键点
- 参数 2 表示每周从星期一开始。
DATEDIF计算日期间隔计算上架日期到 2026/7/15 相差多少天。
=DATEDIF(B2,DATE(2026,7,15),“d”)- 结果
- 14
- 关键点
"d"返回完整天数，也可以使用"m"或"y"。
EDATE按月份移动日期计算上架日期三个月后的日期。
=EDATE(B2,3)- 结果
- 2026/10/1
- 关键点
- 它按自然月份移动，比直接加 90 天更适合月度周期。
二、动态数组：一个公式返回多行多列
动态数组函数的关键变化是：公式通常只写在一个单元格里，结果会自动向下或向右扩展。下面建立 运营明细：
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | 平台 | 状态 | 销量 |
| 2 | KB001 | 淘宝 | 正常 | 12 |
| 3 | MS002 | 京东 | 缺货 | 5 |
| 4 | KB001 | 拼多多 | 正常 | 8 |
| 5 | CS003 | 淘宝 | 异常 | 15 |
| 6 | KB004 | 京东 | 正常 | 6 |
| 7 | MS002 | 淘宝 | 异常 | 9 |
第 5、7 行状态为“异常”。
=SORTBY(FILTER(A2:D7,C2:C7=“异常”),FILTER(D2:D7,C2:C7=“异常”),-1)先筛出异常行，再按销量从大到小排列。
源数据变化后，结果区域会重新扩展。
FILTER按条件返回记录自动生成所有状态为异常的完整记录。
=FILTER(A2:D7,C2:C7=“异常”,“暂无异常”)- 结果
- 返回第 5、7 行
- 关键点
- 返回的是多行多列，不只是记录数量。
UNIQUE自动去重从 SKU 列生成不重复清单。
=UNIQUE(A2:A7)- 结果
- KB001、MS002、CS003、KB004
- 关键点
- 源数据增加新 SKU 后，去重结果自动扩展。
SORT按结果中的列排序按第 4 列销量从大到小排列整张表。
=SORT(A2:D7,4,-1)- 结果
- 15、12、9、8、6、5
- 关键点
- 4 表示按结果区域中的第 4 列排序。
SORTBY按指定区域排序使用 D 列销量作为排序依据，移动整张记录。
=SORTBY(A2:D7,D2:D7,-1)- 结果
- 销量从 15 排到 5
- 关键点
- 排序依据单独写，比记“第几列”更直观。
SEQUENCE生成连续序号为 6 条记录生成从 1 开始的连续编号。
=SEQUENCE(6,1,1,1)- 结果
- 1、2、3、4、5、6
- 关键点
- 四个参数依次是行数、列数、起始值和步长。
TRANSPOSE行列转换把 A2:D3 的两行四列转换成四行两列。
=TRANSPOSE(A2:D3)- 结果
- 4 行 × 2 列
- 关键点
- 新结果保持公式关联，不需要复制后再转置粘贴。
三、批量计算：把同一规则应用到整张矩阵
这一组函数解决的是“不要再向下复制几百行公式”。下面建立 七天销量，C～I 列分别代表星期一到星期日：
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SKU | 库存 | 周一 | 周二 | 周三 | 周四 | 周五 | 周六 | 周日 |
| 2 | KB001 | 30 | 2 | 3 | 4 | 2 | 5 | 3 | 1 |
| 3 | MS002 | 8 | 0 | 1 | 0 | 2 | 1 | 0 | 1 |
| 4 | CS003 | 15 | 3 | 2 | 4 | 3 | 2 | 4 | 2 |
| 5 | KB004 | 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
每个 SKU 的七天销量分别求和。
20 · 5 · 20 · 0每天的全部 SKU 销量分别求和。
5 · 6 · 8 · 7 · 8 · 7 · 4把库存数字逐个转换成状态。
正常 · 预警 · 正常 · 预警从初始值开始，把一行销量依次累加。
0 → 2 → 5 → 9 → 11 → 16 → 19 → 20LET命名中间结果先计算七天销量和日均销量，再返回保留一位小数的结果。
=LET(七天销量,SUM(C2:I2),日均,七天销量/7,ROUND(日均,1))- 结果
- 2.9
- 关键点
- 给中间结果命名，避免在长公式里重复计算。
MAKEARRAY按行列规则生成数组根据行号和列号生成一个 3 × 3 乘法表。
=MAKEARRAY(3,3,LAMBDA(r,c,r*c))- 结果
- 1 2 3 / 2 4 6 / 3 6 9
- 关键点
r和c分别代表当前结果单元格的行号与列号。
BYROW逐行计算分别计算每个 SKU 的七天总销量。
=BYROW(C2:I5,LAMBDA(row,SUM(row)))- 结果
- 20、5、20、0
- 关键点
- 每一行执行一次 SUM，结果自动向下扩展。
BYCOL逐列计算分别计算星期一到星期日的总销量。
=BYCOL(C2:I5,LAMBDA(col,SUM(col)))- 结果
- 5、6、8、7、8、7、4
- 关键点
- 同一计算规则沿列方向执行，结果向右扩展。
MAP逐项批量处理把库存低于 10 的商品批量标记为“预警”。
=MAP(B2:B5,LAMBDA(x,IF(x<10,“预警”,“正常”)))- 结果
- 正常、预警、正常、预警
- 关键点
- 数组中的每个库存值都会单独进入同一套 IF 规则。
REDUCE逐项累计从 0 开始，依次累加 KB001 的七天销量。
=REDUCE(0,C2:I2,LAMBDA(total,x,total+x))- 结果
- 20
- 关键点
total保存上一步结果，x表示当前处理的数字。
四个综合练习
单个函数掌握后，再把不同函数家族组合起来。下面四个练习都能从本文三张示例表直接继续完成。
=CONCAT(“第”,WEEKNUM(B2,2),“周-”,C2,”-“,A2)WEEKNUM 负责周数，CONCAT 负责组合成可用于报表的字段。
=LET(异常,FILTER(A2:D7,C2:C7=“异常”),SORTBY(异常,INDEX(异常,,4),-1))FILTER 生成结果，LET 命名结果，SORTBY 再按第 4 列排序。
=BYROW(C2:I5,LAMBDA(row,IF(SUM(row)=0,“无销量”,“有销量”)))BYROW 逐行处理，SUM 汇总，IF 把数字转换成状态。
=FILTER(A2:B5,B2:B5<10,“暂无预警”)这里不需要先用 MAP 生成状态，FILTER 可以直接使用库存条件。