excel数据处理_excel数据处理小技巧-CSDN博客

日常工作中常用的一些可以提升效率的excel小技巧，虽然很小，但真的很实用，分享给大家。

一、批量更改数据格式

有时候我们从数据平台上导出的数据虽然是excel表格，但是里面的数据却是文本格式的，没办法进行计算操作，所以需要先转换格式。如果只是需要转换某个单元格中的数据格式，只需要选中该单元格，然后从开始à数据选项卡的下拉菜单中选择需要的数据格式，再双击单元格之后回车即可。但是如果需要转换的是某一列或者几列数据的格式，选中这些数据之后直接更改数据格式往往是不奏效的，除非你更改之后逐个双击单元格再回车……这样效率就会非常的低。

分享两种快速批量更改数据格式的方法：

1、利用excel的错误检查功能。

图1：转换为数字

想要把文本格式的数字转化成常规数字格式，在数据量不是很大的情况下，直接单击鼠标左键，选中任意一个需要转化的单元格，然后ctrl+A全选，此时选中区域的右上角会有一个方形黄底的感叹号小图标出现，再用鼠标左键点击该图标，选择“转换为数字”，即可。

优点：可以针对整个表格批量操作

缺点：数据量大的时候执行比较慢，容易导致excel卡死，而且这个方法只能把文本格式的数字转化为常规格式的数字。

2、利用分列功能。

选中需要转换格式的列，点击数据选项卡下的分列按钮，然后点击完成即可。如果需要转换成其他格式，可以在点击分列之后点击2次下一步，选择需要转换的格式之后再点完成。

图2：分列

优点：可以批量转换为常规、文本、日期等多种格式，且执行效率较高。

缺点：每次只能转换一列。

二、利用公式自动引用某行/列中倒数第几个数据

需要在报表中引用另一张表格中最后第几行/列中的数据，并且每次所引用的数据表有新的数据追加到后面时，都能自动更新引用的位置，确保所引用的数据永远排在源数据表中倒数第几的位置，可以采用INDEX和MATCH的组合公式实现。

用法举例：

假设我想引用表1中第2行数据的最后一个非空单元格的值，公式可以写成：

=INDEX(表1！2:2,1,MATCH(9E+307,表1!2:2,1))。

其中，INDEX函数返回的是指定区域中第几行第几列的值。要找到某一行中最后一列的值，先将行号设置为1(因为公式中只选中了一行作为查找的区域)，列号则通过MATCH函数返回。而MATCH函数中的第一个参数则是要查找的值，设置为9E+307，即9后面加307个零，这个数值足够大到在所选的区域中不可能找到，然后MATCH函数就会返回所选区域的最后一个非空单元格所在的位置。整个公式如果需要引用的是某一行或一列倒数第几个位置的值，则在MATCH函数后面减相应的数字即可。

如下图所示，如果我在下面的表格后面加了一列2019/11/28的数据，那么上面表中的数据就会自动更新为2019/11/28的数据。

图3：index+match

三、巧用公式生成数据可自动更新的文字说明

更新数据周报的时候，往往一些常规的说明文字是可以不变的，每次只需要更新文字中的数据即可。但是每次手动更改文字中的数据不仅麻烦，而且还容易出错，巧用公式则可提升效率。

CONCATENATE()——将多个文本字符串合并成一个。

用法举例：

=CONCATENATE("各大版本活跃占比：7.0版本",TEXT(C18,"0.00%"),"，6.0版本",TEXT(C17,"0.00%"),"，5.0版本",TEXT(C16,"0.00%"),"，4.0版本",TEXT(C15,"0.00%"),"，3.0版本",TEXT(C14,"0.00%"),"，2.0版本",TEXT(C13,"0.00%"),"，1.0版本",TEXT(C12,"0.00%。"))

公式中引用的单元格是对应各版本的占比，公式中的TEXT函数是将数据转化为文本格式。

这个公式对应显示的文本是：

各大版本活跃占比：7.0版本66.40%，6.0版本9.37%，5.0版本2.28%，4.0版本2.09%，3.0版本19.04%，2.0版本0.75%，1.0版本0.06%。

图4：concatenate

只要更新引用的单元格中的数值，就可以自动更新文本中的数字。

四、针对连续多列的数据查找，VLOOKUP用法。

通常我们使用VLOOKUP查找数据时，是根据某一列的值来查找并返回另外一列的值。如果需要返回的是多列的值，就需要重复多次的输入VLOOKUP的公式进行查找。

例如：

我需要根据表1中的C列的值，来查找表2中的A列的值并返回对应的B~I列的值，按照常规的用法，需要在表1中D~K列分别输入

=VLOOKUP(C2,表2!$A:$I,2,0)

=VLOOKUP(C2,表2!$A:$I,3,0)

=VLOOKUP(C2,表2!$A:$I,4,0)

……

手动输入多个公式，效率比较低，还容易出错。

改进方法：

在D2单元格输入=VLOOKUP($C2,表2!$A:$I,COLUMN(B2),0)后回车，选中D2单元格，把鼠标移到选中区域的右下角，光标变成黑色十字形时，向右拖动复制到E2~K2单元格中，最后选中D2~K2单元格，把鼠标移到选中区域的右下角，光标变成黑色十字形时，双击向下复制，即可完成整个表格的查找引用。

五、超好用的excel快捷键

1、自动求和ALT+"="号：选中需要求和的数据区域（包括用来放置求和结果的行或列）后，同时按下ALT键和=号键即可，或者直接点击需要求和的某行/列的末尾第一个空单元格，然后同时按下ALT键和=号键。