别再死磕 VLOOKUP!4 套 Excel 高阶组合函数,90% 表格难题一步搞定

日常查表、多条件求和、动态区域汇总、多列极值提取，普通基础函数很难灵活搞定，本文 4 个实战案例，覆盖VLOOKUP+MATCH、SUMIF数组、ATCH+OFFSET+SUM、OFFSET+SUBTOTAL+MAX四大组合套路，看完直接套用。

案例 1：VLOOKUP+MATCH，双条件动态查表（按姓名 + 学科取分数）

场景

左侧数据源：A1:D10，A 列姓名、B/C/D 为对应科目；右侧 F 列姓名、G 列科目，自动匹配对应分数。

公式（H2 单元格）

=VLOOKUP(F2,A:D,MATCH(G2,$A$1:$D$1,0),0)

拆解逻辑

MATCH(G2,$A$1:$D$1,0)：横向查找 G2 的科目在表头 A1:D1 中是第几列，返回动态列序号；

VLOOKUP(F2,A:D,动态列序号,0)：精准匹配 F2 姓名，调取 MATCH 返回列的数值；

优势

不用手动改数字列号，新增科目、切换查询学科，公式完全不用修改，是双条件查表最优组合。

案例 2：SUMIF 数组批量多条件求和（多个人销量汇总）

场景

A:C 为月份、姓名、销量，一次性计算刘备、关羽、张飞三人全部销量总和。

公式（E3 单元格）

=SUM(SUMIF(B:B,{"刘备","关羽","张飞"},C:C))

拆解逻辑

SUMIF(B:B,单条件,C:C)：单独计算一个人的销量；

{"刘备","关羽","张飞"}：常量数组，同时生成 3 个 SUMIF 计算结果；

外层 SUM：把数组算出的多组结果相加，一次性汇总多个人数据；

优势

无需辅助列、不用多次相加，多条件求和极简写法，适配不限数量的筛选对象。

案例 3：MATCH+OFFSET+SUM，动态整行横向求和（按姓名汇总全周销量）

场景

A1:F10，A 列姓名、B-F 为 5 周销量；输入指定姓名，自动汇总该人全部 5 周销量。

公式（B13 单元格）

=SUM(OFFSET(A1,MATCH(A13,A2:A10,0),1,,5))

拆解逻辑

MATCH(A13,A2:A10,0)：纵向定位目标姓名在 A 列的偏移行数；

OFFSET(A1,偏移行数,1,,5)：以 A1 为起点，跳到目标姓名行，向右偏移 1 列，提取宽度为 5 列的销量区域；

SUM：对动态提取的单行 5 列区域求和；

优势

行顺序打乱、新增人员，仅需修改 A13 查询姓名，自动抓取对应整行数据汇总。

案例 4：OFFSET+SUBTOTAL+MAX，多列汇总后提取最大值（多周总销量峰值）

场景

B-F 为 5 周销量，先分别算出每周所有人合计，再提取每周合计里的最高数值。

公式（B13 单元格）

=MAX(SUBTOTAL(9,OFFSET($A$2,,ROW(1:5),9,)))

拆解逻辑

ROW(1:5)：生成 1~5 序列，OFFSET 依次向右偏移 1~5 列，分别提取 B2:B10、C2:C10…F2:F10 五列数据；

SUBTOTAL(9,区域)：分别对 5 列区域独立求和，得到每周总销量；

MAX：在 5 个周合计数值里提取最大值；

优势

一键完成「多列分别汇总→提取汇总极值」两步操作，无需辅助列存放每周合计。