夜雨聆风学习资料网

ARTICLE · 1381176

Excel 365天 · 第3天 | 工资表和花名册列顺序不一样,6 列全填错?

Excel 365天 · 第3天 | 工资表和花名册列顺序不一样,6 列全填错?

工资表和花名册的列顺序不一样,6 列全填错?COLUMN 的三个边界

一个朋友看了上一篇文章,给我发消息。

「对我很多表格有用,但有一个表格我照着做了,结果岗位那一列全是部门,部门那一列全是岗位。八百多行,改了一中午。」

她没做错什么。她只是抄了一个公式,而这个公式有一个前提,我昨天没讲透。

今天这篇,就把那个前提补上。顺便说清楚:同样一件事,除了 COLUMN,还有两种更稳的做法。

如果您正在用电脑看这篇文章,可打开微信扫一扫,扫描文末的二维码,关注“办公技能修炼”公众号,持续学习办公技能。

一、先把错误原样复现出来

她的花名册是这样的,七列;工资表也是七列,但顺序不一样。

列
花名册(源表)
她的工资表(目标表)
A
工号
工号
B
姓名
姓名
C
部门
岗位
D
岗位
部门
E
银行卡号
银行卡号
F
开户行
开户行
G
社保基数
社保基数

图 1|C 和 D 对调了,这是全文所有麻烦的起点

她还是用了昨天那个公式:

=VLOOKUP($A2, 花名册!$A:$G, COLUMN(B1), 0)

拖到 C 列,COLUMN(C1) 等于 3,于是取源表第 3 列。源表第 3 列是部门。可她要填的位置是「岗位」列。

拖到 D 列,COLUMN(D1) 等于 4,取源表第 4 列,是岗位。可她这一列要的是部门。两列就这么换了位置。

这一列本该是
应该显示
实际显示
C 列 · 岗位
会计
财务部
D 列 · 部门
财务部
会计

图 2|填进去的全是正常中文,不报错,肉眼扫不出

最要命的是:表格不报错。填进去的全是正常的中文,一眼扫过去没有任何异样。如果不是她随手抽查了一个熟悉的同事,这张表会直接发出去,然后社保和工资全按错的岗位核算。

这就是 COLUMN 的死穴:它只负责让列号一个接一个往上涨,它不负责判断「这一列该取第几列」。列号涨得对不对是一回事,涨到的那个位置对不对,是另一回事。

二、COLUMN 到底返回什么

先把这个小函数说清楚,它一共四种写法。

写法
返回
含义
COLUMN()
3
公式所在格的列号(写在 C5 里就是 3)
COLUMN(B1)
2
B1 的列号,跟公式写在哪无关
COLUMN(B:B)
2
整列引用,同样是 2
COLUMN(A1:C1)
1 2 3
区域,返回一组数,自动向右溢出

图 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 这个表头(姓名),到花名册的第一行里找,看它在第几个位置,返回那个位置号。

于是「姓名」永远对应「姓名」,「岗位」永远对应「岗位」,两张表列顺序怎么变都不会错,源表中间插了一列也不会错。

这是三种解法里唯一一劳永逸的。它明天单独讲,今天你只要记住:有这么一条路,而且它比前两条都稳。

解法
要改表吗
能防插列
改顺序成本
改表顺序
要
能
低
数组常量
不用
能
中
MATCH
不用
能
极低

图 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 要的是相对列号,两个东西混着用,最容易出事。

区域 C:I 里的位置
col_index 该写
COLUMN 会返回
C 列
1
3
D 列
2
4
E 列
3
5

图 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。

常见报错对照

报错
原因
#VALUE!
COLUMN 的参数写成了带引号的文本
#REF!
引用的单元格或区域已经被删除
#SPILL!
数组公式要溢出的位置被别的内容挡住了,清空右侧或下方即可
#NAME?
函数名拼错,最常见的是把 COLUMN 写成 COLUMNS

适用场景清单

· 批量带出多列字段,且目标表列顺序与源表不一致

· 按自定义顺序重排字段,比如把社保基数挪到最前

· 生成 1 到 12 月的横向表头

· 生成连续的行编号,删行后自动重排

· 隔列、隔行抽取数据

· 把宽表里的预算列、实际列分别抽出来

· 给明细数据编分组号,用于分批打印或分批发送

· 配合 INDEX 做「取当前行第 N 个值」

· 在数组公式里生成序号数组

· 检查某个区域一共有多少列,配合 COLUMNS 使用

九、明天见

今天提到第三种解法,用 MATCH 按表头名字找列。它解决的不只是列顺序问题。

你有没有遇到过这种情况:源表每个月都会多一列、少一列,你的公式每月都要跟着改一遍?

下一篇第 4 篇,重点讲 MATCH。学会之后,公式只写一次,以后源表怎么增删列,它都自己找得到。

聊聊你的事

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

Excel 365 天 · 第 3 篇· 共 365 篇

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料