Excel中有哪些能瞬间提高工作效率但是不为人知的技巧_java

资料来源：网络整理时间：2023/3/9 1:27:05 共计：3571 浏览

Excel中有哪些能瞬间提高工作效率但是不为人知的技巧？

2020年了，Excel需要分两部分学。第一是传统的Excel，比如函数、公式、VBA等。另外一部分就是Excel的报表自动化工具，PowerQuery和PowerPivot。当然我更建议你学习Excel的Powerquery和PowerPivot功能，因为这是以后的趋势。处理数据的效率是传达Excel的十倍百倍。

甚至有人说，PowerPivot是Excel20年来最好的发明。

传统Excel知识掌握常用的就可以了。

文末免费发送电子书啦，要看到最后哈

VLOOKUP

VLOOKUP可谓是白领必学函数。该函数简单易学，是职场人必备的办公技巧了。但是我们在使用该函数的时候需要学会变通，并且配合其他函数进行学习才能真正意义上提高我们的数据处理效率。函数语法如下图；

01、查找数据列顺序一致

用VLOOKUP函数查找多列数据时，如果需要查找的数据列在查找区域中是连续的。那么这时我们只需要修改VLOOKUP的第三个参数，也就是查找数据所在列数就可以完成查找。但是单独地一个一个修改VLOOKUP函数的第三个参数，有时也是很耗时间的。如下图，示例中只有4列也许不觉得时间很久，随着列数的增加浪费的时间将会越多。

如果需要查找的列在原始区域的列数是递增的，我们可以使用COLUMN函数来自动化参数的输入。

我们可以先看看，COLUMN函数的语法：

COLUMN([reference])

我们可以看到COLUMN函数的参数是用方括号表示的，这说明该函数的参数是可以缺省的。就是说我们可以不输入参数值。COLUMN 函数返回给定单元格引用的列号。例如，在B列任意单元格公式=COLUMN （）返回2，因为B列的列号是2。

那么我们将COLUMN函数向右拖动将分别生成2、3、4、5。刚好是我们VLOOKUP需要的第三个参数。

所以结合相对引用可以将上述B9中单元格的公式改为：

=VLOOKUP($A$9,$A$2:$E$6,COLUMN(),FALSE)

然后往右拖动填充公式就可以一次性完成数据查找了。是不是超快捷？

这里切换绝对引用使用了快捷键 F4。

02、查找数据列顺序不一致

上面的例子是理想情况下的，实际情况是很多时候我们需要查找的数据是从原数据区域中挑选几列，甚至会将原来的列顺序打乱。虽然这种情况下，需要做的也是修改第三个参数的数值，但是简单的使用COLUMN函数却无法满足需求，因为第三参数并不是递增的。这时我们需要用的MATCH函数。

MATCH的英文名称是匹配。所以顾名思义，使用 MATCH 函数在指定单元格区域内中搜索匹配的项，然后返回该项在此区域中的相对位置。

MATCH(lookup_value, lookup_array, [match_type])

例如，在B8中输入公式 =MATCH(B8,A1:E1,0) 返回数字 2，因为物品是该区域中（标题行中）的第二项。

以此类推，可以快速返回其他标题所在的列数。返回的结果正是我们VLOOKUP需要的第三个参数。所以B9中单元格的公式可以改为：

=VLOOKUP($A$9,$A$2:$E$6,MATCH(B8,$A$1:$E$1,0))

接下来只需要拖动公式填充就可以快速查找需要的数据了。

几个隐藏函数

今天向大家介绍 Excel 中三个实用的隐藏函数，对于 HR 和财会小伙伴来说，一定会觉得相见恨晚哦。这三个函数分别是DATEDIF、NUMBERSTRING、DATESTRING。

那么，什么是隐藏函数？

普通函数在输入时都有智能填充功能，输入部分函数以后可以按 Tab 键进行补齐。

而隐藏函数输入时没有智能提示，也无法使用 Tab 键补齐，需要手工输入整个函数。

普通函数可以点击“编辑栏”旁边的“插入函数”按钮，进行查找使用，隐藏函数无法找到。

隐藏函数在函数列表里是找不到，甚至连帮助文件中也没有相关说明。

那么，又为什么隐藏这些函数呢？

微软没有官方声明将这几个函数隐藏的原因。对于 DATEDIF 函数，微软为了兼容当时流行的电子表格软件 Lotus 1-2-3 而提供了这个函数。1983 年由莲花公司出品 Lotus 1-2-3，出现时间早于微软的 Excel，在 1980 年代中期如日中天。但在后面的竞争中逐渐被淘汰。

贴个图，缅怀一下电子表格软件的先驱 Lotus 1-2-3

下面正式给大家介绍这三个函数

1、DATEDIF 函数

DATEDIF 函数用于计算两个日期之间相隔的天数、月数或年数。其函数语法为：

DATEDIF(start_date,end_date,unit)

第一和第二个参数是日期，第三参数是计算的间隔单位，可以为 D、M、Y、YD、YM、MD。

前面两个参数很容易理解，下面通过实际案例来理解最后一个参数。

结合 TODAY 函数，第三个参数选择「Y」可以计算员工年龄：

第三个参数选择「D」可以计算员工工龄（具体到天）：

身份证号码可以提取出生日信息，所以可以直接用身份证号码计算年龄：

使用以下公式：

=DATEDIF(--TEXT(MID(B2,7,8),"0000-00-00"),TODAY(),"Y")

HR 小伙伴们经常需要在员工生日的时候送上慰问，所以需要在员工表上设置生日提醒。将第三个参数选择「YD」可以计算日期时间段中天数的差，而忽略日期中的年，进而实现生日提醒：

使用以下公式实现 10 天之内的生日提醒：

=TEXT(10-DATEDIF(C3,NOW()+10,"YD"),"0 天后生日;;今日生日")

涉及的计算员工的考勤工时，需要计算两个时间的小时、分钟或者秒数差，可惜的是 DATEDIF 的第三个参数没有提供这两个间隔上的计算参数。这时可以考虑使用 Power Pivot 的 DATEDIFF 函数。这两个函数真不能不说是相似啊。不过后者强大很多。

参考阅读: 这个函数微软都藏起来，不想太早给你知道

2、NUMBERSTRING

财务人员制作报销单、发票模板等需要将阿拉伯数字转化为中文大写数字。如果一个一个手工输入的话，工作量就不可估量了。隐藏函数 NUMBERSTRING 就是实现这种转换的函数。其基本语法为：

NUMBERSTRING(Value,Type)

第一个参数是数值，值得注意的是该参数不支持包含小数的数值。第二个参数指定返回结果的类型，有三种 1，2，3。第二个参数选择为 2 就是我们财务上需要的中文大写数字啦。

我们还可以使用另外两种方法实现中文大写数字转换。

使用快捷键「Ctrl + 1」设置单元格格式为中文大写数字

使用 TEXT 函数也可以实现转换，TEXT 函数基本语法：

TEXT(Value,Format_text)

将 Format_text 设置为 [DBNum2] 就可以实现转换

3、DATESTRING

DATESTRING 用于将日期转换为“yy 年 mm 月 dd 日”格式的文本日期。基本语法为：

DATESTRING(Date)

在 Excel 中输入以下公式，将返回 20 年 2 月 23日（返回系统当天日期）

=DATESTRING(TODAY())

这个函数比较简单，使用场景比较少。小编就不多做介绍了，在这里抛砖引玉，供需要的朋友们使用哈。

关于PowerQuery的几个重要功能

替代VLOOKUP,一键查找关联列

将两个数据表合并以增加数据分析维度是我们使用 Excel 经常会面对的问题。过去，我们只能用 VLOOKUP函数，复杂一点就甚至需要使用 INDEX 及 MATCH 函数，然而很多人很难理解函数的使用逻辑。

参考阅读 :

/ XLOOKUP 还没出现时，VLOOKUP 就已经被它干掉了 /

学会 Excel 的 PQ 功能以后，点击鼠标就可以完成复杂函数组合才能实现的数据丰富功能。我们有两张表，一张库存信息表，一张销售明细。两张表通过 SKU Number 进行关联。

要分析每一笔交易的收入、成本就要将 Sale Price 和 Unit Cost 匹配到销售明细表。

要分析不同产品类型、不同分机构的销量情况就要将 Type、Brand 匹配到销售明细表。

库存信息表

销售明细表

我们可以使用 PQ 的「合并查询」功能进行匹配。

一、仅创建连接

为了使用 PQ 的「合并查询」，需要将两个数据表都加载进 PQ 编辑器。我们都知道将数据加载到 PQ 以后关闭并上载到 Excel 以后，会复制一份原始数据表到 Excel 的新工作表中，这样就显得多余。

所以我们需要将查询仅创建成链接。

1、使用数据选项卡下「自表格/区域」功能，将库存信息表加载进 PQ 编辑器。

2、库存信息表加载到 PQ 编辑器，这时你可以对数据进行转换和清洗，当然本例中不需要执行其他操作。

3、点击「主页」>「关闭并上载」下拉弹出菜单中，选择「关闭并上载至」。

4、在导入数据窗口中选择「仅创建连接」。

这时建立的查询会显示在「查询 & 连接」中，而不会新建一个工作表。

同样的方式将销售明细表也加载到 PQ 编辑器中。这时两个数据表都仅仅以链接的形式存在于 Excel 中，而不会重复加载。

使用仅创建链接的方式加载数据，可以让我们的Excel工作簿简约，不冗余。一般情况下，我们会将原始链接数据创建成链接，而把最终的数据清洗结果加载到工作表中。

二、单条件合并查询

将两个表格加载到 PQ 以后，就可以使用合并查询功能。

1、将鼠标悬停在查询上方，点击鼠标右键弹出的菜单中选择「合并」。

2、在弹出的窗口中，上方表格选择为 Sales 表，下方的表格选择为 Inventory 表。

3、鼠标点击 Inventory 表的 SKU Number 列，同样点击 Sales 表的 SKU Number 列。选择两列作为两个表的关联列。

4、点击确定就完成了数据匹配，下面只需要展开合并后的列，选择需要的列并加载到 Excel 就可以了。

几次鼠标点击就替代了复杂的函数组合才能实现的功能。这就是 PQ 的强大之处。短时间的练习就可以在数据处理上取得突破性的进展。

三、多条件合并查询

PQ 的合并查询功能也很简单，只要选择关联字段时按一致的顺序选择好字段，点击确定就可以实现多条件合并了。选择多个字段按住「Ctrl」键点选即可。PQ 界面字段上方的小数字代表了关联字段的顺序。

四、模糊查询

如果我们的需求是进行模糊匹配的话，最新版的 PQ 还给我们提供了模糊匹配的选项。勾选「模糊匹配执行合并」，然后设置相似性阈值，默认的阈值是0.8。阈值设置的越小，对于匹配字段相似性要求就越低。所以我们需要尝试查找出最合适的阈值进行模糊匹配。

最后，虽然 PQ 提供了模糊匹配功能，但是这种匹配方式有时并不能准确的提供答案。因此还是必须得重视数据的规范性。

一秒合并工作簿，一键刷新

上文我们通过单独给每一个文件加载成查询，然后使用 Power Query 的「追加」功能将结构一致的文件合并，这种方法虽然避免了复制黏贴的重复工作，但是将文件加载成查询的过程也是一个枯燥的动作。

/ 参考阅读：Power Query 追加功能，快速合并文件的好方法 /

如果需要合并的文件再次增加，重复操作不可避免。Power Query 的「从文件夹」功能可以解决这个问题。

一、从文件夹合并

我们将上一篇文章中所有销售数据文件都放同一文件夹Begin中，然后新建一个空白 Excel 文件，用来加载合并的文件。

从文件夹功能使用操作如下：

新建查询 > 获取数据 > 来自文件 > 从文件夹导航到目标文件夹，点击确定 > 转换数据

点击「转换数据」进入 Power Query 编辑界面。可以看到 Begin 文件夹里面的文件清单和它们的各种属性。比如：文件名、扩展名（文件类型）、获取时间、文件创建时间等。

值得注意的是第一列（Content），该列存储的就是文件夹中的二进制文件，里面就是我们需要合并的数据。而列右边的图标就是合并文件的按钮。我们可以选中该列，删除其他列。

二、合并文件

我们可以点击 Content 列右上角的合并文件功能，实现文件合并。

操作完这个步骤以后在查询窗口可以看到 Power Query 自动生成的查询。其中我们需要的合并好的文件就在其他查询 Begin 里面。

同时在 Begin 这个查询的查询设置窗口也能看到很多 Power Query 自动应用的步骤。其中从「筛选的隐藏文件1」到「扩展的表格列1」都是文件合并的操作。这个最后一个步骤「更改的类型」将其删除。

三、转换示例文件

在查询窗口中我们可以看到有一个新建的查询「转换的示例文件」。这个文件是 Power Query 转换文件时的参考文件，它是对合并时我们选择的示例文件进行操作的查询。对这个文件的所有操作都会应用到其它的文件中。

所以如果合并的过程中需要修改步骤的话可以在该查询上操作，Power Query 会对其他文件执行同样的操作。

四、一键刷新

随着时间的推移，我们需要合并的文件会越来越多。而我们并不需要再次操作以上的步骤，将相应的文件放到 Begin 文件夹刷新查询就可以自动就文件夹中所有文件合并了。

如果文件是放在子文件夹里，Power Query 同样可以将子文件夹中数据合并。

关于PowerQuery，我将前段时间更新的 #PQ in Excel 专辑内的文章进行整理，制作成了一本小型电子书。

内容来自于之前的专辑分享 #PQ in Excel，我挑选了其中入门案例、基础界面介绍及数据清洗十招的内容，共 52 页，汇总为《PQ入门手册》。

关注我，私信「PQ入门」，即可获得下载链接。一起学习微软 Excel 最新最酷技能。

版权说明：
本网站凡注明“广州京杭原创”的皆为本站原创文章，如需转载请注明出处！
本网转载皆注明出处，遵循行业规范，如发现作品内容版权或其它问题的，请与我们联系处理！
欢迎扫描右侧微信二维码与我们联系。

·上一条：什么语言编译最快_java | ·下一条：Spring的单例模式怎么保证线程安全_java