06 · 公式助手
菜单:常用公式 / 查找组合 / 公式管理 · 共 17 个工具
本组让你不记语法也能写公式:常用公式、INDEX+MATCH 组合、公式迁移与管理。
常用公式
嵌套 IF bg_nif1
- 用途:一条条加条件,自动写出多层嵌套 IF 公式,适合「分数段定等级」「按区间给系数」;每条条件可以盯不同的列,同一组的条件还能用「且 / 或」连起来,比如「A 列 ≥90 且 B 列 ≥95 → 双优」。
- 怎么用:框选要写结果的列(或区域)→ 点按钮 → 逐条加「判断列 + 条件 + 比较值 + 连接」→ 在每组第一行填「返回结果」→ 可设都不满足时的默认值 → 生成。
- 判断列留空 = 用上面「判断依据」框选的默认列;要比别的列就填列字母(
B、AA)。 - 返回结果留空 = 这一条和上一条属于同一组,按「连接」的 且 / 或 合成一个条件(多个混着用时从左往右结合);要返回空文本请填
=""。
- 判断列留空 = 用上面「判断依据」框选的默认列;要比别的列就填列字母(
- 结果与注意:公式写在选区右边一列,用相对地址,方便往下拉;返回值可以是文字、数字,也可以填
=B5这种引用。**=开头的引用会跟行号走**(第 2 行写=B2,写到第 5 行就是B5,等于逐行对比两个格子),要钉死某个格子就写=$B$5;写完会跳到结果区并提示地址。判断用到的几列在整行都是空白时,这一行不写公式。
多条件公式 bg_mcf1
- 用途:一条规则里同时满足多个条件(AND)或满足任意一个(OR),返回同一个结果,生成一条 IF 公式。
- 怎么用:框选要写结果的列 → 点按钮 → 逐条加「哪一列 + 怎么比 + 比什么」→ 选 AND / OR → 填满足与不满足时分别返回什么 → 生成。
- 结果与注意:与「嵌套 IF」的分工:嵌套 IF 是多条规则各返回各的结果,这个是一条规则多个条件返回同一个结果;公式写在右侧。
多条件统计 bg_fs1
- 用途:按行写出 SUMIFS / COUNTIFS / AVERAGEIFS / MAXIFS,拿整片明细按若干条件算出合计、个数、平均、最大。
- 怎么用:框选明细数据(建议连表头)→ 点按钮 → 选统计方式(求和 / 计数 / 平均 / 最大)和统计列 → 逐条加「列 + 运算符 + 比较值」→ 写入。
- 比较值留空 = 用本行这一列(相对地址),一条公式往下拉,每行各算各的;填了值 = 固定条件,每行结果相同。
- 结果与注意:公式写在选区右边一列,统计区、条件区都用绝对地址(
$B$2:$B$100);条件最多 20 条;运算符有等于、不等于、大于、小于、包含、不包含、为空、不为空等十种;条件列整行全空的不写。与「多条件公式」的分工:那个生成 IF(判断这一行),这个是统计(拿满足条件的整片数据算一个数)。
AI公式助手 bg_aif1
- 用途:用中文描述需求(「按部门算金额合计」「身份证号提取出生日期」),让 AI 生成 Excel 公式。
- 怎么用:框选数据区域 → 点按钮 → 用一句话说需求 → 生成 → 在预览里看/改公式 → 确认后写入,可顺手向下填充。
- 结果与注意:只把列标题、最多 5 行示例、目标单元格地址和这句话发给阿里云百炼,不上传整个工作簿;需要联网和 Key;公式先预览再写入,不会直接改表。
查找公式 bg_lf1
- 用途:选中要填结果的那一列,逐行写出 VLOOKUP / INDEX+MATCH 公式,外面可按需要套 IFERROR / ISERROR。
- 怎么用:框选结果列 → 点按钮 → 选公式类型、查找值列、查找区域、返回列 → 选错误处理方式 → 生成。
- 结果与注意:与「精准查找」分工:精准查找是快速三步写 INDEX/MATCH,这个能选更多组合和错误包装;公式逐行写入选区。
查找组合
VLOOKUP+MATCH bg_vm1
- 用途:用 MATCH 自动算返回列号,VLOOKUP 的列序数不用手数,插列后公式不容易错。
- 怎么用:框选结果列 → 点按钮 → 指定查找值、查找区域、列标题所在行/区域 → 生成。
- 结果与注意:逐行写公式;覆盖已有内容前会问一次;整列选中会先收窄。
VLOOKUP+IFERROR bg_vif1
- 用途:VLOOKUP 外面套 IFERROR,查不到时显示「未找到」或空白,而不是
#N/A。 - 怎么用:框选结果列 → 点按钮 → 填查找参数和查不到时的显示内容 → 生成。
- 结果与注意:逐行写公式;适合交表给客户时隐藏错误值;覆盖前会确认。
VLOOKUP+ISERROR bg_vis1
- 用途:用 ISERROR 判断查找是否出错,再配合 IF 返回自定义内容,兼容老版本 Excel。
- 怎么用:框选结果列 → 点按钮 → 填查找参数和出错时的显示内容 → 生成。
- 结果与注意:写法比 IFERROR 长,但老版本都能用;逐行写公式;覆盖前会确认。
MATCH+MATCH bg_mm1
- 用途:行列双向查找,用两个 MATCH 分别定位行和列,适合交叉表查值。
- 怎么用:框选结果列 → 点按钮 → 指定行标题区域、列标题区域、数据区域 → 生成。
- 结果与注意:逐行写公式;行/列标题要能唯一匹配;覆盖前会确认。
公式管理
提取公式 bg_hs
- 用途:把公式换成文字显示出来,方便检查、讲解、留档。
- 怎么用:框选区域 → 点按钮 → 选「原地换成文字」或「写到右边一列」→ 执行。
- 结果与注意:只动真的有公式的格子,常量格不碰;文字前会加「按文字存」标记,
=开头不会重新变公式;写右边时原格不动。
恢复公式 bg_hr
- 用途:「提取公式」的反操作,把当文字存着的算式变回真正的公式。
- 怎么用:框选公式文字 → 点按钮 → 选「原地恢复」或「从右边一列读回」→ 执行。
- 结果与注意:只恢复长得像公式的文字;常量文字不碰;恢复前建议确认这些文字确实是要当公式用。
导出公式 bg_b10
- 用途:把工作簿里的公式连同单元格地址导出成文本文件,用于备份、迁移、比对。
- 怎么用:点按钮 → 选范围(仅当前选中区域 / 当前整张工作表 / 整个工作簿)→ 选保存位置 → 开始导出。
- 结果与注意:导出的是「工作表!$A$1|算式」这样的文本行,不含计算结果;文件可被「导入公式」读回。
导入公式 bg_b11
- 用途:把「导出公式」生成的文本文件恢复成真正的公式,适合换电脑、重建工作簿后快速还原。
- 怎么用:点按钮 → 选公式文件 → 选导入方式(按原工作表位置 / 导入到当前活动工作表)→ 开始导入。
- 结果与注意:按原位置导入时,工作表名要能对上;对不上的会提示。导入会覆盖目标格现有内容,建议先备份。
公式迁移 bg_fm1
- 用途:把一块公式搬到另一块区域,只搬公式,颜色、字体、边框、数字格式都不动;相对引用跟着目标位置变,
$锁定的不动。 - 怎么用:点按钮 → 框选源公式区域(一格或一小片)→ 框选目标区域 → 确认覆盖 → 执行。
- 结果与注意:目标尺寸要是源区域的整数倍(按行列平铺);源里不是公式的格子跳过,目标对应格不动;目标格是文本格式时,会先改常规再重写,并在结果里说明改了几格。
公式体检 bg_fh1
- 用途:扫出公式格里的错误值(
#N/A、#VALUE!、#REF!、#DIV/0!、#NAME?、#NUM!、#NULL!),逐条给出病因和原公式。 - 怎么用:点按钮 → 选范围(选中的区域 / 当前工作表 / 整个工作簿)→ 点「开始检查」→ 结果表里看;双击一行跳回那一格;可「复制清单」发人。
- 结果与注意:只读不写,一格都不动。范围选「选中的区域」时只点一格也行,会自动收到有数据的范围;整表太大(已用区域超过 30 万格)会跳过并说明。
公式替换 bg_frep
- 用途:只在公式内部换字,比如把
$A$1:$A$100换成$B$1:$B$100、把SUM换成SUBTOTAL;常量文字、数字一个不碰。 - 怎么用:框选区域(只点一格会自动收到数据区)→ 点按钮 → 填「查找内容 / 替换为」→ 按需勾选「区分大小写 / 跳过双引号里的文字 / 全字匹配」→ 看预览 → 替换写入。
- 结果与注意:默认跳过双引号里的文字,公式里的
"销售"、"A1"不会被误改;「全字匹配」让A1不再命中A10、SUM不再命中SUMIF;预览只列会变的格;原地改写、没有撤销,建议先另存一份。要连常量一起换请用「查找替换」。
批量套 IFERROR bg_fie1
- 用途:给已有公式外面包一层
IFERROR(原公式, 兜底值),出错时显示空白、0 或一句文字,而不是错误值。 - 怎么用:框选区域 → 点按钮 → 填「出错时显示什么」(留空 = 显示空白;填数字原样;填文字自动加引号)→ 看预览 → 包上 IFERROR。
- 结果与注意:已经套过 IFERROR 的默认跳过,不会叠第二层;只处理真公式,常量格和格式都不碰;逐格写回,某一格包不上(数组公式、表有保护)只算那一格失败,不连累整片。