Excel常用公式组合

真实工作里，一个函数往往不够用，真正提效的是组合公式。下面我按“应用场景 → 示例数据 → 公式 → 函数解释”的方式，带你把这套常用组合拳过一遍。

一、先准备一张示例表

假设你有这样一张销售明细表，区域在 A1:I9：

另外准备产品单价表，放在 J1:K5：

再准备一个交叉汇总表，放在 M1:P4。注意：这里李四华东已经修正为 1600。

二、IF + AND + OR：多条件判断

应用场景

根据金额和客户等级，自动给客户打标签：重点客户、跟进客户、普通客户。

示例公式

在 Q2 输入，Q1 写“客户标签”：

=IF(AND(G2>=1000,H2="金牌"),"重点客户",IF(OR(H2="金牌",G2>=800),"跟进客户","普通客户"))

✅函数解释

• AND：所有条件同时满足才返回 TRUE。这里要求金额大于等于 1000，且客户等级是金牌。

• OR：任意一个条件满足就返回 TRUE。这里金牌客户，或者金额大于等于 800，都算跟进客户。

• IF：按条件返回不同结果。先判断是不是重点客户，不是再判断是不是跟进客户，最后归为普通客户。

✅提醒：

公式里的括号、逗号、引号都必须是英文标点。比如 H2="金牌"，引号不能是中文引号。

三、VLOOKUP + IFERROR：查找并容错

应用场景

根据产品名称，从产品单价表里查单价。如果产品没维护，不要显示 #N/A，而是显示“产品X未维护”，让人一眼知道是哪个产品缺资料。

示例公式

不要把公式写在 K2，因为 K2 是产品单价表里的单价。建议写在 L2，L1 写“查得单价”：

=IFERROR(VLOOKUP(D2,$J$2:$K$5,2,FALSE),IF(D2="","未维护","产品"&D2&"未维护"))

如果嫌“产品E未维护”啰嗦，也可以简短写成：

=IFERROR(VLOOKUP(D2,$J$2:$K$5,2,FALSE),D2&"未维护")

显示效果，假设 D2 填 E，而产品表里没有 E：

注意：原产品表里已经有 D=400，所以不要拿 D 演示“未维护”，要用 E 这种没维护的产品。

函数解释

• VLOOKUP(D2,$J$2:$K$5,2,FALSE)：拿 D2 的产品名称，去 $J$2:$K$5 第一列精确查找，找到后返回第 2 列，也就是单价。

• IFERROR(..., 出错时显示什么)：查不到时，不显示 #N/A。

• IF(D2="","未维护","产品"&D2&"未维护")：如果产品为空，显示“未维护”；如果产品非空但没维护，显示“产品E未维护”这种带具体产品名的提示。

✅提醒

• 公式不要写在 K2，K2 是产品单价表本身。

• 写在 L2 或 F2。如果写 F2，会覆盖原来的单价列，建议先用辅助列。

• 区域 $J$2:$K$5 要加 $，防止拖动时跑偏。

四、INDEX + MATCH：双向查找

应用场景

你有一张交叉表：行是销售员，列是地区。现在想根据“销售员 + 地区”查金额。

示例公式

假设查询条件：M8 是销售员，N7 是地区。在 N8 输入：

=INDEX($N$2:$P$4,MATCH($M8,$M$2:$M$4,0),MATCH(N$7,$N$1:$P$1,0))

函数解释

• MATCH($M8,$M$2:$M$4,0)：找销售员在第几行。

• MATCH(N$7,$N$1:$P$1,0)：找地区在第几列。

• INDEX($N$2:$P$4,行号,列号)：返回交叉位置的值。

✅提醒

INDEX + MATCH 比 VLOOKUP 更灵活，因为可以向左查，也可以双向查。旧版 Excel 里多条件查找常配合数组公式，新版可以直接用 XLOOKUP 或 FILTER。另外，交叉表里李四华东已经改成 1600，否则这里查出来会和明细对不上。

五、SUMIFS + COUNTIFS + IFERROR：多条件求和与平均

应用场景

按“销售员 + 地区”统计总金额，再算平均每单金额。如果该组合没有订单，不显示错误，而显示“无数据”。

示例公式

假设查询条件放在 R10 和 S10，R10 是销售员，S10 是地区。

总金额：

=SUMIFS($G$2:$G$9,$B$2:$B$9,$R$10,$C$2:$C$9,$S$10)

平均每单金额：

=IFERROR(SUMIFS($G$2:$G$9,$B$2:$B$9,$R$10,$C$2:$C$9,$S$10)/COUNTIFS($B$2:$B$9,$R$10,$C$2:$C$9,$S$10),"无数据")

函数解释

• SUMIFS：多条件求和。求和区域是金额 G2:G9，条件区域分别是销售员和地区。

• COUNTIFS：多条件计数，统计满足销售员和地区的订单数。

• IFERROR：当分母为 0 时，避免出现 #DIV/0!，返回“无数据”。

✅提醒

SUMIFS 和 COUNTIFS 的条件区域大小必须一致。不要一个写 B2:B9，另一个写 B2:B100。查询条件建议放 R10:S10，不要放 J、K 列，避免和产品单价表混淆。

六、LEFT + FIND + MID：文本提取组合

应用场景

订单编号类似 CN-2026-001-A，你想提取国家码和年份。这里建议另起一张工作表，不要把 A 列订单编号和前面的订单日期混在一起。

示例数据

提取国家码，在 B2 输入：

=LEFT(A2,FIND("-",A2)-1)

提取年份，在 C2 输入：

=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)

函数解释

• FIND("-",A2)：找第一个横杠的位置。

• LEFT(A2,位置-1)：从左边截取到横杠之前，得到国家码。

• MID：从指定位置开始截取指定长度。

• 第二个 FIND 从第一个横杠后面继续找下一个横杠，用来确定年份的长度。

提醒

FIND 区分大小写，SEARCH 不区分。如果分隔符不固定，建议先用“分列”或 TEXTSPLIT，不要硬写公式。

七、IF + ISNUMBER + SEARCH：关键字判断

应用场景

备注里只要出现“加急”，就标记为“优先处理”，否则“正常处理”。

示例公式

在 T2 输入，T1 写“处理标记”：

=IF(ISNUMBER(SEARCH("加急",I2)),"优先处理","正常处理")

函数解释

• SEARCH("加急",I2)：在备注里找“加急”。找到返回位置数字，找不到返回错误。

• ISNUMBER：判断结果是不是数字。是数字，说明找到了。

• IF：根据 TRUE/FALSE 输出“优先处理”或“正常处理”。

提醒

SEARCH 不区分大小写，适合中文关键字。如果你要区分大小写，用 FIND。公式不要写 J2，J2 是产品单价表。

八、SORT + FILTER + UNIQUE：动态数组筛选组合

应用场景

一键筛出华东地区所有销售明细，并按金额从高到低排序；或者只列出华东地区出现过的销售员。

示例公式

筛选华东明细并按金额降序：

=SORT(FILTER(A2:G9,C2:C9="华东"),7,-1)

提取华东地区销售员名单并去重：

=UNIQUE(FILTER(B2:B9,C2:C9="华东"))

函数解释

• FILTER：按条件筛选数据。这里筛选地区为“华东”的行。

• SORT：对筛选结果排序。第 7 列是金额，-1 表示降序。

• UNIQUE：对筛选出来的销售员去重。

提醒

FILTER、SORT、UNIQUE 需要 Excel 365 或 Excel 2021 及以上版本。WPS 部分版本也支持，但要看具体版本。动态数组公式会溢出，结果下方要留空。

九、VLOOKUP + MATCH + IFERROR：动态列查找

应用场景

你想根据订单日期，查任意字段。今天查“金额”，明天查“销售员”，不想每次都改 VLOOKUP 的列序号。

示例公式

假设 A12 是订单日期，B11 是字段名，比如“金额”。在 B12 输入：

=IFERROR(VLOOKUP($A12,$A$2:$I$9,MATCH(B$11,$A$1:$I$1,0),FALSE),"订单"&$A12&"的"&B$11&"未找到")

如果不想写这么长，也可以保留：

=IFERROR(VLOOKUP($A12,$A$2:$I$9,MATCH(B$11,$A$1:$I$1,0),FALSE),"未找到")

函数解释

• MATCH(B$11,$A$1:$I$1,0)：根据标题“金额”，找到它在第几列。

• VLOOKUP($A12,$A$2:$I$9,列号,FALSE)：用订单日期在 A 列查找，返回对应列。

• IFERROR：找不到时显示提示。