ARTICLE · 1381176
Excel 365天 · 第3天 | 工资表和花名册列顺序不一样,6 列全填错?
工资表和花名册的列顺序不一样,6 列全填错?COLUMN 的三个边界
一个朋友看了上一篇文章,给我发消息。
「对我很多表格有用,但有一个表格我照着做了,结果岗位那一列全是部门,部门那一列全是岗位。八百多行,改了一中午。」
她没做错什么。她只是抄了一个公式,而这个公式有一个前提,我昨天没讲透。
今天这篇,就把那个前提补上。顺便说清楚:同样一件事,除了 COLUMN,还有两种更稳的做法。
如果您正在用电脑看这篇文章,可打开微信扫一扫,扫描文末的二维码,关注“办公技能修炼”公众号,持续学习办公技能。
一、先把错误原样复现出来
她的花名册是这样的,七列;工资表也是七列,但顺序不一样。
图 1|C 和 D 对调了,这是全文所有麻烦的起点
她还是用了昨天那个公式:
=VLOOKUP($A2, 花名册!$A:$G, COLUMN(B1), 0)拖到 C 列,COLUMN(C1) 等于 3,于是取源表第 3 列。源表第 3 列是部门。可她要填的位置是「岗位」列。
拖到 D 列,COLUMN(D1) 等于 4,取源表第 4 列,是岗位。可她这一列要的是部门。两列就这么换了位置。
图 2|填进去的全是正常中文,不报错,肉眼扫不出
最要命的是:表格不报错。填进去的全是正常的中文,一眼扫过去没有任何异样。如果不是她随手抽查了一个熟悉的同事,这张表会直接发出去,然后社保和工资全按错的岗位核算。
这就是 COLUMN 的死穴:它只负责让列号一个接一个往上涨,它不负责判断「这一列该取第几列」。列号涨得对不对是一回事,涨到的那个位置对不对,是另一回事。
二、COLUMN 到底返回什么
先把这个小函数说清楚,它一共四种写法。
图 3|四种写法,只有第二种是「带锚点」的
昨天用的是第二种,COLUMN(B1)。为什么不用第一种?
因为第一种会跟着公式的位置变。你在 B2 里写 COLUMN(),得到 2,正好是要取的源表第 2 列,看起来能用。可一旦你把这一整行公式复制到另一张表、或者插入了新的一列,它就变成 3、变成 4,起始点跑了,后面全错。
第二种写法里的 B1,是一个锚点。它的意思是:不管我在哪,第一个要取的数,是源表第 2 列。从这个 2 开始,往右一格加一,往右两格加二。
锚点找对了,公式才稳。
三、解法一:把两张表的列顺序改成一样
最笨,但最稳。把工资表的 C 列和 D 列换回来,让两边都是工号、姓名、部门、岗位、银行卡号、开户行、社保基数。三十秒的事。
很多人不愿意做,觉得「我这表是系统导出的,改不了」「领导习惯看这个顺序」。那就用下面两种。
四、解法二:把列号写成数组,一次溢出六列
这个写法,很多人不知道 VLOOKUP 还能这么用。在 B2 里输入:
=VLOOKUP($A2, 花名册!$A:$G, {2,4,3,5,6,7}, 0)注意第三个参数,不是 2,而是 {2,4,3,5,6,7},一对花括号里面六个数,用逗号隔开。
这六个数是什么意思?就是「我要按顺序取源表的第 2 列、第 4 列、第 3 列、第 5 列、第 6 列、第 7 列」。对应过来正好是:姓名、岗位、部门、银行卡号、开户行、社保基数。
2 姓名 | 4 岗位 | 3 部门 | 5 银行卡号 | 6 开户行 | 7 社保基数 |
图 4|六个数,就是六个字段的自定义顺序
按回车,你会看到公式从 B2 一路自动铺开到 G2,六列一次出完。一个公式,取代六个公式。
这个写法的好处是:列号是你自己排的,顺序怎么变都不怕。哪天领导说「把社保基数挪到前面」,你只需要把花括号里的数字调个位置,六列立刻重排。
两个注意点:
第一 旧版 Excel 里这么写不会自动铺开。要先选中 B2 到 G2 六个格子,在编辑栏输入公式,然后按 Ctrl+Shift+Enter,公式外面会自动加一对花括号。
第二 花括号要手动敲,是英文输入法下的 Shift 加左右中括号键。手敲不出来,也可以选中区域后按 F9 生成。
进阶一步:在 365 版里,把查找值也写成一个区域,比如 =VLOOKUP(A2:A501, 花名册!$A:$G, {2,4,3,5,6,7}, 0),一个公式能直接算出 501 行、6 列共 3006 个格子,连往下拖都省了。日常还是建议按列写,改起来方便。
五、解法三:用 MATCH 按表头名字找列
前两种解法,本质都还是「人告诉公式取第几列」。还有一种办法,是让公式自己去表头里找。
=VLOOKUP($A2, 花名册!$A:$G, MATCH(B$1, 花名册!$A$1:$G$1, 0), 0)第三个参数不再是数字,而是一个 MATCH。它干的事是:拿 B$1 这个表头(姓名),到花名册的第一行里找,看它在第几个位置,返回那个位置号。
于是「姓名」永远对应「姓名」,「岗位」永远对应「岗位」,两张表列顺序怎么变都不会错,源表中间插了一列也不会错。
这是三种解法里唯一一劳永逸的。它明天单独讲,今天你只要记住:有这么一条路,而且它比前两条都稳。
图 5|三种解法怎么选,看这张表
六、三个最容易翻车的地方
坑一:括号里什么都不写,用了 COLUMN()
现象:公式在自己这张表上试了没问题,复制到别的表、或者插入新列之后,整片错位。
原因:COLUMN() 返回的是「当前这一格的列号」,不是「我要取的源表列号」。它是相对的,位置一变,起点就变。
解法:永远写带锚点的形式,COLUMN(B1)。锚点那一格的列号,必须等于你要取的第一个源表列号。
坑二:中间插了一列,锚点跟着跑
现象:用了 COLUMN($B$1),觉得加了美元符号就锁死了。结果同事在 B 列前面插了一列,全表错位。
原因:Excel 会自动调整公式里的单元格引用。你在 B 列前插入一列,原来的 $B$1 自动变成 $C$1,COLUMN 返回 3,锚点挪了。美元符号锁得住「拖拽」,锁不住「插入删除」。
解法:要防插列,就别用 COLUMN,用解法二的数组常量,或者解法三的 MATCH。这两者都不依赖单元格位置。
坑三:把相对列号当成绝对列号
现象:源表区域从 C 列开始,你写 C:I,然后在 col_index 里写 3,想取 C 列,结果取到了 E 列。
原因:col_index 是「相对于查找区域第一列」的序号,不是工作表的列字母序。区域是 C:I,那 C 列就是 1,D 列是 2,E 列才是 3。
解法:数一遍。手指点在区域第一列上,往右数,第一列是 1。这一点跟 COLUMN 正好相反——COLUMN 返回绝对列号,col_index 要的是相对列号,两个东西混着用,最容易出事。
图 6|同一个人,两种数法,差了一个字母的距离
昨天那个公式之所以能跑通,纯粹是因为区域从 A 列开始,绝对列号和相对列号刚好相等。这是运气,不是设计。
七、举一反三,COLUMN 和 ROW 还能这么用
这两个函数名字像,用途也像,一个管列一个管行,都是「生成一串连续数字」。
场景一 一次生成 12 个月的表头 在 B1 输入 =COLUMN(A1)&"月",往右拖到 M1,你得到 1月、2月一直到 12月。做年度预算表、费用对比表,表头再也不用一个个敲。
场景二 自动连续编号 A2 输入 =ROW()-1,往下拖,得到 1、2、3。好处是删掉中间某一行,编号自动重排,不会出现缺号。
场景三 隔列取数 有时候你的表是「预算、实际、预算、实际」这样交替排列的宽表,要单独把预算列抽出来。用 =INDEX($B2:$M2,COLUMN(A1)*2-1),往右拖就能依次取到第 1、3、5、7 列。
场景四 分批打印的分组号=ROUNDUP(ROW(A1)/20,0),每 20 行编一个组号,打印或者分批发送的时候按组号筛选,一千行也能拆得整整齐齐。
八、今日份参数详解(建议收藏)
函数一 COLUMN 语法
COLUMN([reference])参数 reference 引用(可选)
含义 要查询列号的单元格或区域。
合法取值 单个单元格(B1)、整列引用(B:B)、单元格区域(A1:C1)。不能是文本——写成 COLUMN("B1") 带引号会返回 #VALUE!。也不能是多个不连续的区域。
省略时的行为 返回公式所在单元格的列号。这就是坑一的来源。
返回值范围 1 到 16384。Excel 最右边的列是 XFD,就是第 16384 列。
区域引用时 返回一组数。COLUMN(A1:C1) 返回 1、2、3,365 版自动向右溢出;旧版要按 Ctrl+Shift+Enter。
函数二 ROW 语法
ROW([reference])参数含义、合法取值、省略行为,跟 COLUMN 完全对称。区别只有两个:它返回行号,范围是 1 到 1048576;区域引用时返回的是竖着的一组数。
参数三 VLOOKUP 的 col_index_num,今天要重新认识
含义 要返回的列,是「相对于查找区域第一列」的序号。
合法取值 大于等于 1 的整数。也可以是一组数,写成 {2,4,3,5,6,7},此时返回多列。
三个边界 写 1 返回查找列本身;写小数直接截断,2.9 当 2;写 0 或负数返回 #VALUE!,超过区域列数返回 #REF!。
今天新增 它数的是相对位置,不是列字母。区域从 C 列开始时,C 列是 1。
常见报错对照
适用场景清单
· 批量带出多列字段,且目标表列顺序与源表不一致
· 按自定义顺序重排字段,比如把社保基数挪到最前
· 生成 1 到 12 月的横向表头
· 生成连续的行编号,删行后自动重排
· 隔列、隔行抽取数据
· 把宽表里的预算列、实际列分别抽出来
· 给明细数据编分组号,用于分批打印或分批发送
· 配合 INDEX 做「取当前行第 N 个值」
· 在数组公式里生成序号数组
· 检查某个区域一共有多少列,配合 COLUMNS 使用
九、明天见
今天提到第三种解法,用 MATCH 按表头名字找列。它解决的不只是列顺序问题。
你有没有遇到过这种情况:源表每个月都会多一列、少一列,你的公式每月都要跟着改一遍?
下一篇第 4 篇,重点讲 MATCH。学会之后,公式只写一次,以后源表怎么增删列,它都自己找得到。

聊聊你的事
你有没有过这种经历:公式没错,结果全错,还不报错?评论区说说你踩过最狠的一次,我挑几个典型的下周做成「排错专场」。
Excel 365 天 · 第 3 篇· 共 365 篇















































































