📌 阶段四 · 进阶函数 · 第18讲 LOOKUP和数组 · 约 50 分钟

一式抵一列：数组公式按「商品+日期区间」一击算出出入库合计，LOOKUP 玩转多条件精确匹配

学习目标

  • 理解数组公式的运算逻辑，记住三条使用铁律
  • 会用 SUM((条件)*(条件)*求和列) 替代多条件求和
  • 会用 SUMPRODUCT 免组合键直接算多条件结果
  • 会用 LOOKUP(1,0/((条件1)*(条件2)),结果列) 做多条件精确查找
  • 会用 LOOKUP 做阶梯区间的模糊匹配

知识点讲解

从 SUMIF 到数组：换一种思路求和

多条件求和你已经会两条路：SUMIFS（第 10 讲）和辅助列（第 10 讲）。数组是第三条，也是最灵活的一条：=SUM((条件1)*(条件2)*求和列)。

原理拆开看：每个条件式对整列一次性判断，匹配的位置得 TRUE（参与运算时当 1）、不匹配得 FALSE（当 0）；两组 0/1 相乘后，只有「所有条件都满足」的位置剩 1，再乘上金额列求和——等于把辅助列的活儿压缩进一条公式。

讲义原例：=SUM(($A$2:$A$22=K15)($B$2:$B$22=L15)$E$2:$E$22)，销售区域和部门双条件筛金额。

=SUM(($A$2:$A$22=K15)*($B$2:$B$22=L15)*$E$2:$E$22)

条件 A 成立得 1、条件 B 成立得 1，两个 1 相乘再乘金额才留下数——数组版多条件求和。

💡 提示:

  • 想亲眼看看数组怎么算：编辑栏里选中条件式按 F9，能看到一串 TRUE/FALSE（按 Esc 退出，新版自动计算）
  • 下拉复制数组公式时记得给条件区域加绝对引用

数组三铁律（老版本必须知道）

第一，老版本 Excel 写完 =SUM((条件)*(金额)) 这类公式要按 Ctrl+Shift+Enter 三键确认，Excel 自动在公式两端加花括号 {}；直接回车会算错。新版 Excel/365 直接回车即可。

第二，相乘的几个区域长度必须一致，错一位就全盘皆错。

第三，不要引用整行整列——百万行的 0/1 数组会把表格拖垮，也容易混进无关数据，务必写准数据范围（如 $2:$301）。

💡 提示:

  • 花括号是 Excel 自动加的，手工敲上去无效
  • 修改老式数组公式时，确认完要重新三键，否则公式会「退化」

SUMPRODUCT：数组公式的免按键版

把上一节 =SUM((条件)*(金额)) 的 SUM 换成 SUMPRODUCT，就不用按三键、直接回车：=SUMPRODUCT((MONTH(出库单!$B$2:$B$301)=A2)*出库单!$I$2:$I$301)。

这一条直接解决月度汇总难题：MONTH 把每行出库日期变成月份号，与目标月份相等的位置得 1，乘出金额后总加——12 个月就是 12 行公式。讲义明确说：SUMPRODUCT 作用与 SUM 的数组形式相同，但直接回车即可。

=SUMPRODUCT((MONTH(出库单!$B$2:$B$301)=A2)*出库单!$I$2:$I$301)

按月收割出库金额：日期月份等于 A2 的行留 1，其余归 0，乘金额后求和。

💡 提示:

  • 范围写准到数据末行（如 2:301），既是铁律也保速度
  • 想按年月同时过滤，再加一组 (TEXT(日期,"yyyy")="2024") 之类的条件相乘

LOOKUP：只有三个参数的老将

=LOOKUP(查找值,查找区域,[结果区域])，从单行或单列中查找并返回值。参数 2 只能是一行或一列且必须升序；参数 3 是返回值所在的一行/列，必须与参数 2 等长。

和 VLOOKUP 比：① 参数 2 只有一列，查找列和返回列彻底解耦，更灵活；② 只有 3 个参数，不能设置精确匹配；③ 只要查找列升序排列，运算结果与 VLOOKUP 的精确查找一致。

最适合阶梯区间：按订货量查折扣、按金额查运费档——档位下限升序一排，一条 LOOKUP 定档。

=LOOKUP(D2,$L$2:$L$6,$M$2:$M$6)

在升序的档位下限列 L 里找不大于 D2 的最大档，返回 M 列对应档位的值——区间匹配比 VLOOKUP 模糊匹配写得还短。

💡 提示:

  • 忘了升序会得到「看似有理」的错值，用前先排序
  • 参数 2 与参数 3 等长是硬要求

0/() 套路：LOOKUP 的多条件精确匹配

LOOKUP 不能设精确匹配，但它会自动回避错误值——于是有了经典套路：=LOOKUP(1,0/((条件1)*(条件2)),结果区域)。

原理拆开：条件式产出一串 TRUE/FALSE；0/TRUE 得 0，0/FALSE 是除以 0 的错误值。于是匹配的位置全是 0、不匹配的位置全是错误值；LOOKUP 拿 1 去找，找不到 1，就返回小于等于 1 的最大值——正是那些 0 所在的位置，错误值全部被自动忽略。这就是精确匹配，还天生支持多条件。

讲义两例：按客户 ID 查公司名称 =LOOKUP(1,0/($A$2:$A$92=G4),$B$2:$B$92)；按区域加部门双条件查金额同理相乘。

=LOOKUP(1,0/($A$2:$A$92=G4),$B$2:$B$92)

单条件精确查找：匹配处化 0、其余化错误值被忽略，LOOKUP 锁定唯一 0 的位置返回结果。

=LOOKUP(1,0/((入库单!$C$2:$C$201=A2)*(入库单!$F$2:$F$201=B2)),入库单!$H$2:$H$201)

双条件实战：商品编号和供应商同时命中的行化 0，返回其单价——查出某供应商给某商品的进价。

💡 提示:

  • 这招不怕乱序、不用三键、天然多条件，是老 Excel 里最好用的精确查找套路
  • 条件再多就继续相乘：* (条件3)

数组思想小结：条件当开关，乘法做筛选

这一讲的本质是一个思想：把条件写成 TRUE/FALSE（1/0）的「开关数组」，与数据数组对应相乘——0 把不要的行清零，1 保留要的行，最后 SUM 或 SUMPRODUCT 收口。

往后看到「按月汇总」「按类别汇总」「按日期区间汇总」，都能一式搞定，不再依赖辅助列；条件涉及函数运算（如 MONTH、TEXT 截取）时，数组更是唯一解。

选型经验：条件是简单的「等于/大于」且不用嵌函数时，SUMIFS 可读性更好；条件要经过函数加工时，轮到数组出场。

=SUMPRODUCT((条件A=α)*(条件B=β)*金额列)

万能模板背下来：开关相乘定去留，乘金额再求和。

💡 提示:

  • 调试数组公式多用 F9 看中间结果，一看开关串对不对，二看金额串错没错位

实战演练

期末冲刺：这一讲建造「销售分析」工作表——月度汇总区用 SUMPRODUCT 按月收割出库金额与数量；类别占比区回答「文具、纸品、设备各卖了多少」；最后搭一个多条件查询面板，选商品、选起止日期，出入库合计一秒出数，还用 0/() 套路查出商品最近一次进价。这三块正是期末成品「销售分析」页的原型。可先在右侧练习数据里试手感，再回到进销存工作簿正式建造（区间末行按实际数据行数调整，示例按入库单 200 行、出库单 300 行）。

  1. 销售分析：月度汇总区 — 新建工作表「销售分析」。A 列 A2:A13 输入 1~12，表头：月份、入库金额、出库金额、出库数量。B2 输入出库金额 =SUMPRODUCT((MONTH(出库单!$B$2:$B$301)=A2)*出库单!$I$2:$I$301)，下拉 12 行。
  2. 出库数量与入库金额 — D2 复制 B2 公式，把金额列 I 换成数量列 G：=SUMPRODUCT((MONTH(出库单!$B$2:$B$301)=A2)*出库单!$G$2:$G$301)，下拉；C2 改用入库单：=SUMPRODUCT((MONTH(入库单!$B$2:$B$201)=A2)*入库单!$I$2:$I$201)，下拉。
  3. 类别占比区：先补一列辅助 — 出库单 J1 写表头「类别」，J2 输入 =VLOOKUP(C2,商品档案!A:C,3,FALSE) 下拉（第 11 讲回锅）。销售分析 F1:H1 写表头「类别、销售额、占比」，F2:F4 写三个类别名称。
  4. 类别销售额与占比 — G2 输入 =SUMPRODUCT((出库单!$J$2:$J$301=F2)*出库单!$I$2:$I$301)，下拉 3 行；H2 输入 =G2/SUM($G$2:$G$4) 设为百分比格式，下拉。三行占比相加应等于 100%。
  5. 多条件查询面板 — K2 放商品编号（数据有效性序列，来源库存台账 A2:A31），K3、K4 放开始、结束日期，K5 输入出库合计 =SUMPRODUCT((出库单!$C$2:$C$301=$K$2)(出库单!$B$2:$B$301>=$K$3)(出库单!$B$2:$B$301<=$K$4)*出库单!$I$2:$I$301)；旁边再各写一条入库金额合计、出库数量合计，条件同构。
  6. 0/() 套路：查最近一次进价 — 面板加一行「该商品最近进价」：=LOOKUP(1,0/(入库单!$C$2:$C$201=$K$2),入库单!$H$2:$H$201)——单条件即可，数据按日期升序时返回最后一次出现的进价。换个供应商再嵌一个条件试试双条件版。
  7. 交叉验算 — B 列 12 个月相加应等于 =SUM(出库单!I2:I301)；类别区 G 列相加应等于总销售额；查询面板选「全年日期范围」时结果应等于该商品台账累计出库。三道关全过，销售分析才算建成。

在线练习

下面的表格可以直接编辑,照着演练步骤在本页练习;需要菜单操作的步骤请下载配套练习文件,在自己电脑的 Excel 中完成。

表格加载中…

验收清单

逐项自查,全部通过即完成本讲:

  • ☐ 12 个月的出库金额、出库数量都有值，且 B 列合计等于出库单金额总计
  • ☐ 类别占比三行相加为 100%（或 G 列合计等于总销售额）
  • ☐ 查询面板切换商品和日期后结果正确，至少手工验算过一组
  • ☐ 0/() 的 LOOKUP 能返回商品最近一次进价
  • ☐ 所有数组公式引用的都是精确区间，没有整列引用
  • ☐ 能说出什么时候用 SUMIFS、什么时候必须用数组（条件含函数加工时）