COLUMN函数你会用吗？跟VLOOKUP函数组合使用简直是最好搭档

用VLOOKUP函数查找多列数据时，由于多个公式之间只是列参数的变化，所以只需要改变VLOOKUP函数的第3个参数就可以，但由于刚开始学函数时，对COLUMN函数不熟悉，所以就有了下面这个动图。每个单元格都在手动去修改公式中的第3个参数，我相信很多人都跟我一样操作，一个看似很简单的问题却被我花了大半个钟！

在工作中，我们要处理的数据不仅仅是我上图中的那几条，少则几百，多则上万，如果每个单元格都需要手动去修改公式，这需要花多长时间？工作效率有多高？没有人敢保证。

上图中，C12单元格公式为“=VLOOKUP($B12,$B$2:$I$8,2,0)”，D12单元格公式为“=VLOOKUP($B12,$B$2:$I$8,3,0)”，E12单元格公式为“=VLOOKUP($B12,$B$2:$I$8,4,0)”，...，I12单元格公式为“=VLOOKUP($B12,$B$2:$I$8,8,0)”。我们可以发现公式中，只是返回结果所在的列号发生了变化，所以今天我们要用COLUMN函数来取代VLOOKUP函数的第3个参数，这样就不需要我们手动去修改了。

下面一起看看COLUMN函数的含义及用法。

含义：返回给定单元格引用的列号。

语法：COLUMN([reference])。

参数reference可以省略，也可以为一个单元格或者单元格区域。

用法：

（1）省略reference。

如果参数省略，则返回单元格所在的列值。下图中B2单元格的公式为“=COLUMN()”，结果为2，也就是B列的列号。

（2）reference为具体的某个单元格。

如果参数为某个具体的单元格，则返回单元格所在的列号。下图中B2单元格的公式为“=COLUMN(C4)”，结果为3，也就是C4单元格所在列的列号。

（3）reference为具体的单元格区域。

如果参数为一个单元格区域，则返回单元格区域第1列的列号。下图中B2单元格的公式为“=COLUMN(D2:G5)”，结果为4，也就是D2:G5单元格区域第1列（D2所在列）的列号。

了解了COLUMN函数的用法，我们就可以将C2单元格的公式改为“=VLOOKUP($B12,$B$2:$I$8,COLUMN(B1),0)”，然后将公式右拉填充至I12单元格，下拉填充至I14单元格即可。

公式右拉的过程中，第3个参数COLUMN(B1)中的B1会变成C1，D1，E1，...，列号依次为2，3，4，5，...，也就是说，我们将手动修改列号的方式改成自动变化，这样子效率不仅提高了，也不容易出错，你觉得呢？

给大家留一个问题：制作工资条的方法有很多，如果用VLOOKUP+COLUMN函数来实现，具体应该怎么操作？欢迎大家评论区留言！

COLUMN函数你会用吗？跟VLOOKUP函数组合使用简直是最好搭档

用VLOOKUP函数查找多列数据时，由于多个公式之间只是列参数的变化，所以只需要改变VLOOKUP函数的第3个参数就可以，但由于刚开始学函数时，对COLUMN函数不熟悉，所以就有了下面这个动图。每个单元格都在手动去修改公式中的第3个参数，我相信很多人都跟我一样操作，一个看似很简单的问题却被我花了大半个钟！

在工作中，我们要处理的数据不仅仅是我上图中的那几条，少则几百，多则上万，如果每个单元格都需要手动去修改公式，这需要花多长时间？工作效率有多高？没有人敢保证。

上图中，C12单元格公式为“=VLOOKUP($B12,$B$2:$I$8,2,0)”，D12单元格公式为“=VLOOKUP($B12,$B$2:$I$8,3,0)”，E12单元格公式为“=VLOOKUP($B12,$B$2:$I$8,4,0)”，...，I12单元格公式为“=VLOOKUP($B12,$B$2:$I$8,8,0)”。我们可以发现公式中，只是返回结果所在的列号发生了变化，所以今天我们要用COLUMN函数来取代VLOOKUP函数的第3个参数，这样就不需要我们手动去修改了。

下面一起看看COLUMN函数的含义及用法。

含义：返回给定单元格引用的列号。

语法：COLUMN([reference])。

参数reference可以省略，也可以为一个单元格或者单元格区域。

用法：

（1）省略reference。

如果参数省略，则返回单元格所在的列值。下图中B2单元格的公式为“=COLUMN()”，结果为2，也就是B列的列号。

（2）reference为具体的某个单元格。

如果参数为某个具体的单元格，则返回单元格所在的列号。下图中B2单元格的公式为“=COLUMN(C4)”，结果为3，也就是C4单元格所在列的列号。

（3）reference为具体的单元格区域。

如果参数为一个单元格区域，则返回单元格区域第1列的列号。下图中B2单元格的公式为“=COLUMN(D2:G5)”，结果为4，也就是D2:G5单元格区域第1列（D2所在列）的列号。

了解了COLUMN函数的用法，我们就可以将C2单元格的公式改为“=VLOOKUP($B12,$B$2:$I$8,COLUMN(B1),0)”，然后将公式右拉填充至I12单元格，下拉填充至I14单元格即可。

公式右拉的过程中，第3个参数COLUMN(B1)中的B1会变成C1，D1，E1，...，列号依次为2，3，4，5，...，也就是说，我们将手动修改列号的方式改成自动变化，这样子效率不仅提高了，也不容易出错，你觉得呢？

给大家留一个问题：制作工资条的方法有很多，如果用VLOOKUP+COLUMN函数来实现，具体应该怎么操作？欢迎大家评论区留言！

Excel复习一下分列的组合公式TRIM+MID+SUBSTITUTE+REPT+ROW

题目：从一个字符串中找到4个数字，然后取4个数字的最大值

实际工作中会遇到产品规格的数字与单位组合在一起的情况：

尺寸

1000 mmx450 mm-1000 mmx450 mm

1000 mmx600 mm-1000 mmx600 mm

1000 mmx600 mm-600 mmx600 mm

1000 mmx600 mm-800 mmx600 mm

1000 mmx700 mm-1000 mmx700 mm

1000 mmx700 mm-600 mmx500 mm

1000 mmx700 mm-600 mmx600 mm

1000 mmx700 mm-700 mmx600 mm

1000 mmx700 mm-700 mmx700 mm

1000 mmx700 mm-800 mmx500 mm

1000 mmx800 mm-1000 mmx800 mm

1000 mmx800 mm-800 mmx600 mm

我们要找到最大的长度：每行4个数值中取最大值。

这个问题有三种解决办法：

使用符号分列、固定宽度分列，或者批量替换字符，找到4个数值，然后用MAX函数取最大值。虽然看起来步骤挺多的，不过思路清晰的化，应该很容易得到正确答案。

Power Query中分离出字符的操作变得更加简单，从非数字到数字，从数字到非数字，很容易就分离出来。

删除非数字的列，修改数据类型，添加索引列：

逆透视数字列，根据索引分组取最大值：

然后加载到表就可以了。

我们之前学习过的分列公式组合：

这个问题中的情况要稍微复杂点，应为多了些字母，需要预先处理：

用嵌套的SUBSTITUTE公式替换掉“mmx”，“mm-”，“ mm”，替换后的字符串应该是这个样子的“1000 450 1000 450”

替换后的字符串=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,\"mmx\",),\"mm-\",),\" mm\",)

然后我们再用分列公式，拆分数字：

4个数字=--TRIM(MID(SUBSTITUTE(替换后的字符串,\" \",REPT(\" \",LEN(E2))),(ROW($1:$4)-1)*LEN(E2)+1,LEN(E2)))

--是为把文本转换成数值。

最后就是取最大值：

MAX=MAX(4个数字）

完整的公式：

=MAX(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,\"mmx\",),\"mm-\",),\" mm\",),\" \",REPT(\" \",LEN(E2))),(ROW($1:$4)-1)*LEN(E2)+1,LEN(E2))))

这是一个单个单元格的数组公式，需要用CTRL+SHIFT+ENTER三键结束。

这其实就是分列公式组合的一个扩展，增加了替换嵌套，和MAX函数。

如果不记得分列公式组合可以到这里复习一下：

分列公式