ARTICLE · 1389329
Excel 365天 · 第3天 | 花名册增加或减少一列,公式就得改一遍?
花名册增加或减少一列,公式就得改一遍?让 MATCH 替 VLOOKUP 找列。
先算一笔账。
你们公司的人事花名册,1 月是 7 列。3 月加了「用工性质」,6 月加了「社保缴纳地」,9 月把「银行卡号」挪到了最后一列。
你的工资表公式,是 =VLOOKUP($A2, 花名册!$A:$G, 5, 0)。
那个 5,指的是第 5 列。1 月的时候第 5 列是银行卡号,9 月之后第 5 列变成了别的。你每个月打开文件,第一件事不是算工资,是把表头从左往右数一遍,看这次该写几。
一年下来,公式改了十二次。
而且,数错了列号也不报错,这是最要命的,上一篇刚讲过。今天讲一个办法,让你以后一次都不用数:把那个写死的数字,换成一个会自己找的 MATCH。
如果您正在用电脑看这篇文章,可打开微信扫一扫,扫描文末的二维码,关注“办公技能修炼”公众号,持续学习办公技能。
一、MATCH 只干一件事:告诉你「它在第几个」
先单独认识它。花名册第一行是表头,随便找个空白格,敲这个:
=MATCH("岗位", 花名册!$A$1:$G$1, 0)回车,结果是 4。
图 1|MATCH 只报位置号,不取数据
它在说:你让我找「岗位」这两个字,我在 A1 到 G1 这一排里从头数,第 4 个就是它。
就这么简单。它不取数据,不认格式,只报一个位置号。而这个位置号,正好就是 VLOOKUP 第三个参数想要的东西。
二、把 MATCH 塞进第三个参数里
回到工资表。B2 要填姓名,写:
=VLOOKUP($A2, 花名册!$A:$G, MATCH(B$1, 花名册!$A$1:$G$1, 0), 0)回车,还是那个姓名。看起来结果跟昨天一样,但里面已经换了逻辑。拆开看,四段:
$A2 找谁 | 花名册!$A:$G 去哪找 | MATCH(B$1, 表头行, 0) 自己算出第几列 | 0 要多准 |
图 2|第三段从「写死的数字」变成了「算出来的数字」
第三段的意思是:拿我头顶上这个表头(B1 里的「姓名」),去花名册的第一行里找,找到它在第几个,就用那个数当列号。
于是,「姓名」永远对应「姓名」,「岗位」永远对应「岗位」。
花名册哪天在中间插了一列「身份证号」,你的公式不用动。哪天把「社保基数」挪到最后,你的公式也不用动。它自己会找。
往下拖、往右拖,一路通到底。这是四种写法里唯一不怕源表改结构的。
三、那个美元符号,为什么必须写在数字 1 前面
这一节请慢慢看,全文最容易错的地方就在这。公式里写的是 B$1,不是 $B$1,也不是 B1。
B1 行也变,列也变 往下拖 → B2,掉进数据里 | $B$1 行锁,列也锁 往右拖 → 六列全是姓名 |
B$1 行锁,列不锁 正确 往右拖换表头,往下拖不掉行 | $A2 列锁,行不锁 工号永远在 A 列,人一行行换 |
图 3|四种美元符号,管的是四件事
正确的是 B$1:列不锁,行锁死。
往右拖,B 变 C 变 D,表头跟着换,取到的列也就跟着换。
往下拖,$1 把行钉死在第一行,永远是表头那一行,不会掉到数据里去。
一个符号,管两件事:表头要跟着列走,但不能跟着行走。
顺便说,公式里其他几个美元符号是另一个逻辑:$A2 的列锁住、行不锁,因为工号永远在 A 列,但每一行要换人;$A:$G 和 $A$1:$G$1 都是整段锁死,因为它们代表「那张表的那块区域」,不能跟着你拖拽漂移。
四、三个最容易翻车的地方
坑一:美元符号写错位置
现象:往右拖,六列全是同一个字段;或者往下拖,第二行开始全变成 #N/A。
原因:写成了 $B$1 或 B1。
解法:记住 B$1。判断方法很简单,把光标放在公式里的 B$1 上,按 F4 循环切换,你能看到四种形态转一圈,找到那个「$ 只在数字前面」的。
坑二:表头里有看不见的空格
现象:公式写好,返回 #N/A。你盯着 B1 里的「姓名」和花名册里的「姓名」看了三分钟,一模一样。
原因:其中一个后面跟了个空格,或者是从系统里复制过来的、带了换行符。肉眼看不出,MATCH 一比对就不相等。
解法:先单独测 MATCH。把 MATCH 那一段单独拎出来放到一个空格子里,如果它返回 #N/A,问题就在表头,不在 VLOOKUP。这一步能省你半小时。确认是空格之后,用 TRIM 清一遍,或者直接在花名册的表头上重新手打一遍。
坑三:表头区域和查找区域,左边没对齐
现象:MATCH 单独测返回 4,看着没错,但填进去的数据错了一列。
原因:MATCH 报的是「在它自己那个区域里的第几个」,不是工作表的列号。查找区域是 $A:$G,表头区域也得从 A 开始;你要是把表头写成 $B$1:$G$1,MATCH 返回的 4 就指向了 E 列,整体右移一格。
图 4|同一个 4,起点不同,落点就差一列
解法:两个区域的左边界必须一致。要么都从 A 起,要么都从 B 起。
这一条跟昨天坑三是一回事:col_index 数的是相对位置,MATCH 返回的也是相对位置。两个相对位置要对上,前提是起点相同。
一句话记住:MATCH 的返回值能不能直接塞进 VLOOKUP,取决于这两个区域的起点是不是同一列。
五、举一反三,MATCH 单独的用处
就算不配 VLOOKUP,MATCH 本身也很能打。
场景一 判断这个科目有没有编进预算
=IF(ISNA(MATCH(A2, 预算表!$A:$A,0)), "未编预算", "已编")MATCH 找不到就返回 #N/A,用 ISNA 接住,一句话判断完。比 VLOOKUP 轻,因为它不取数,只找人。
场景二 找第一个满足条件的行
=MATCH(TRUE, B2:B500 > 5000, 0)返回第一条金额超过 5000 的记录在第几行。旧版要按 Ctrl+Shift+Enter,365 直接回车。查异常单据的时候很好用。
场景三 一次算出六列的列号
=MATCH(B$1:G$1, 花名册!$A$1:$G$1, 0)一次返回六个位置号,横向铺开。这就是昨天那个 {2,4,3,5,6,7} 的自动版——不用你手敲数字,它自己按表头算出来。
场景四 配合 INDEX 做双向查找
=INDEX(花名册!$A:$G, MATCH($A2, 花名册!$A:$A, 0), MATCH(B$1, 花名册!$A$1:$G$1, 0))行用 MATCH 定位,列也用 MATCH 定位,横竖都能查。这个组合第 8 天专门讲,今天知道有这么个东西就行。
六、今日份参数详解(建议收藏)
函数语法
MATCH(lookup_value, lookup_array, [match_type])参数一 lookup_value 查找值(必填)
含义 你要找的那个东西。今天是表头文字「姓名」「岗位」。
合法取值 数字、文本、逻辑值、单元格引用都可以。文本不区分大小写,最长 255 个字符。支持通配符问号和星号,但仅在 match_type 为 0 时有效。
限制 不能是数组(除非新版配合其他函数),也不能是区域。
参数二 lookup_array 查找区域(必填)
含义 到哪一排里去找。今天是一行表头 $A$1:$G$1。
合法取值 单行或单列的区域,必须是一维的。写成 A1:G5 这种二维方块会返回 #N/A。
返回值的含义 返回的是「在这个区域里的第几个」,从 1 开始数,不是工作表的行号或列号。这是坑三的根源。
参数三 match_type 匹配方式(可选)
合法取值 只有三个:1、0、-1。省略时默认是 1,不是 0,这一点要特别注意。
写 0 精确匹配,找不到返回 #N/A。日常九成场景用这个,尤其是查文字。
写 1 或省略 近似匹配,找小于等于查找值的最大值,要求区域升序。第 2 天那个 VLOOKUP 近似匹配,底层就是这个逻辑。
写 -1 反向近似匹配,找大于等于查找值的最小值,要求区域降序。用得少,知道有就行。
特殊行为 区域里有重复值时,MATCH 只返回第一个匹配到的位置,后面的不管。所以表头重名的时候,第二个「金额」永远取不到。
常见报错对照
适用场景清单
· 按表头名字取列号,配 VLOOKUP 用
· 判断某个值是否存在于某张表的某一列
· 定位某个值在列表中的位置,配 INDEX 用
· 找第一个满足条件的记录在第几行
· 一次性生成多列的列号数组
· 核对两张表的表头是否一致
· 按季度、月份名称定位到对应的列
· 在长名单里判断某人是否已报名、已录入
· 比对两个清单的差异,找出只在 A 表不在 B 表的项
· 作为 IF 的判断条件,替代 COUNTIF 做存在性检查
七、明天见
今天这套写法有个副作用:如果花名册里没有这个人,公式会返回 #N/A,一整列红字。
这四篇,我们一直在讲「怎么取到」。下一篇讲「取不到的时候怎么收场」。
下一篇讲 IFERROR。它只有两个参数,却能让一整张报表从「满是报错」变成「干净可交」。顺便讲一个它的兄弟 IFNA,很多人不知道什么时候该用哪个。
四篇下来,VLOOKUP 的四个参数已经全部拆完了。下一篇开始,我们给它穿上衣服。

聊聊你的事
你每个月打开工资表,要做的第一件事是什么?数表头、删空行、还是改公式?评论区说说,我看看哪个动作被吐槽最多。
Excel 365 天 · 第 4 篇 · 共 365 篇















































































