快速整理统计数据必备技巧：EXcel【Vlookup+COLUMN】函数使用

安静的克格莫

已于 2024-07-06 12:01:54 修改

阅读量4.7k

点赞数

文章标签：数据库经验分享大数据

于 2022-10-03 09:59:34 首次发布

本文链接：https://blog.csdn.net/weixin_42988629/article/details/127149499

版权

今天给大家分享的是在本人收集数据过程中总结出的非常实用的小技巧，对于需要进行数据收集的小伙伴非常非常有帮助，一方面是方便大家，另一方面也是怕自己忘掉，所以记录下来！建议大家收藏起来，觉得有用也可以转发给需要的朋友。在进行方法之前必须要说明一下使用的场景，这个是十分有必要的！这里我用具体例子进行说明。
使用场景介绍：
假如我们要收集江苏省各个市的数据，包括很多很多指标，时间是1995-1999年，我们首先设定了自己的基准数据，以后都以我们的基准数据为标准进行数据收集，如下图所示：我们想的是以后收集的数据都按照这个地名和时间顺序去填充数据，从而建立自己的数据库，方便使用。
在这里插入图片描述
图一：基准数据示意图
但是，实际收集数据过程中，由于数据来源等问题，收集的数据往往不是按照上图基准进行排列的，而且数据往往会有地方和时间上的缺失（图二），因此对于我们按照统一基准汇总数据带来很多麻烦！！！那么使用什么快捷方法，可以使图二收集的杂乱数据能按照图一基准顺序自动排列呢？而不是复制粘贴手动去调整。接下来就给大家介绍使用【VLOOKUP+COLUMN】函数，来实现自动按基准数据排序新收集的数据。

在这里插入图片描述
图二：新收集的数据示意图

方法介绍：
首先，在基准数据中选择数据函数的单元格，如下图所示：

图三：输入公式的单元格

VLOOKUP函数总共分为三步，第一步选择按照基准数据排列的字段，称为lookup_value，单元格内输入=VLOOKUP($L $46 :$ L$58,)，这里使用绝对引用，因为我们后面是需要复制公式进行填充其他单元格的，来达到批量排序的目的。

在这里插入图片描述
图四：vlookup第一步lookup_value（查阅值）
2.第二步，输入查阅值所在的区域，即需要按基准数据排序的新收集的数据区域，称为，table_array,单元格输入：=VLOOKUP($L $46 :$ L $58,$ D $46 :$ I$56)，依旧绝对引用。

在这里插入图片描述
图五：输入查阅值所在的区域

3.第三步是非常重要的一步。称为col_index_num,即区域中包含返回值的列号，也就是我们所收集数据中，需要匹配数据的列号。假如我们需要排序新收集数据中1995年的数据，那么就是新收集数据的第二列，我们只需要在公示中输入：2，即可，也就是，=VLOOKUP($L $46 :$ L $58,$ D $46 :$ I $56, 2 ）但是这样以后我们就没有办法进行复制公式填充其他单元格，所以我们使用 CO LU MN 函数，表示返回列的值。继续第三步输入 = V L OO K U P ($ L $46 :$ L $58,$ D $46 :$ I$56,COLUMN(B46），由于B46在第二行，因此返回值为2，依然达到上述排序1995年，即第二列数据的目的。

在这里插入图片描述
图六：输入需要排序数据的列
4.第四步，range_lookup,选择近似匹配(TURE),还是精确匹配(FALSE)，我们选择精确匹配(FALSE)。最终完整输入公式为：=VLOOKUP($L $46 :$ L $58,$ D $46 :$ I$56,COLUMN(B46),FALSE)

在这里插入图片描述
添加图片注释，不超过 140 字（可选）
结果：验证一下排序结果是否正确。新收集的数据中1995年南京的数据是663，在基准数据中排序的数据也是663，因此是正确的。

图七：结果
最后，我们进行复制公式到基准数据中的其他需要填充的单元格，查看结果。选中基准数据中663单元格，上下拖动复制单元格公式填充即可。可以看到，排序后的数据，是完全按照基准数据来排序的，而且完全正确，对于没有进行收集的连云港和苏州市的数据，它自动显示错误值，而宿迁、镇江等某些年份缺失的数据，它自动赋值为0。这就已经达到了我们的快速按照基准数据收集数据的目的。