Excel 里有哪些鲜为人知而又简单、逆天的操作技巧？

发布时间：

2024-03-30 23:00

阅读量：

我这里主要介绍 excel 的三个主要技巧，包括操作技巧，这个很大一部分是快捷方式，如果能记住其中的操作，能节省很大一部分时间。

第二块是函数，我们知道，函数是 excel 的强势功能，承载了excel的数据分析能力，我这里介绍一些重要的函数，一定会对我们有所帮助。

第三块是数据透视表，这个是很多职场人经常用到的功能，但是我发现很多人对这个功能一知半解，很多人尝试过后，发现遇到了各种各样的问题，于是就止步不前，不再学习了，用之前的老方法继续操作excel，大大影响了工作效率。

所以我准备了这三大块，希望能帮助到大家。在回答的过程中，我还穿插了学习方法，帮助大家快速习得excel。

那么，我们开始吧。

一、快捷键

对于excel的使用者来说，是需要熟悉excel的所有菜单栏按钮的。因为据我观察，大部分人对于excel的功能并不熟悉，当他们需要使用某些操作的时候，会在菜单栏里翻来翻去，而且还找不到。因此excel的使用者应该牢记菜单栏功能。

我这里有个记忆技巧。

文字单元在【开始】

插图表在【插入】

【页面布局】是打印

数据处理是【数据】

【审阅】一般领导用

【视图】只为冻结生

哈哈哈，这个就是我自己的小技巧，分享给大家。这个主要是记住菜单最基本的功能，其他细节需要各位个性化自己记忆。

除了菜单栏，你还需要记住各种快捷方式，这些快捷方式，都是很常用的功能，能帮助你提高工作效率

对于EXCEL的新手，如果你想系统学习 Excel，或者想要让自己的学习更加扎实，首先需要买一本excel的入门教程，不过看书是一个很累的事情，如果不想看书可以看一个系列课程，系统的学习excel，这样可以学的快一些。

我个人推荐知乎知学堂的一个课程，教程制作人是前IBM数据分析大佬，知乎顶级大 V猴子，课程是结合国内互联网一线大厂的案例，从基础讲起，用案例讲知识点，带练数据分析工具的同时，也构建数据分析思维，能让大家先有系统扎实的基础，对小白来说是很友好的选择。带你轻轻松松学习 Excel ！

二、操作技巧

01.快速复制上一行

选择一行，按Ctrl+D快速复制上一行的内容。

02.删除重复的内容。

先选择目标单元格范围，点击【数据】-【删除重复】，在弹出的对话框中点击【确定】，删除重复值。

03.最快求和

选择数据区和求和结果存储区，按Alt+=键，mac 是Command+Option+=，就可以完成多行多列的快速求和。

04.按单元格颜色求和

在目标单元格中输入公式:= Subtotal (109，数据区域)；

在要求和的单元格上单击鼠标右键，然后点击筛选-按所选单元格的颜色筛选；

计算结果就会显示在目标单元格中。

05.快速小计和

选中求和数据区，按Ctrl+G打开定位窗口；

选择【定位条件】-【空值】-【确定】；

然后按我们之前学的求和，按Alt+=键，mac 是Command+Option+=，就可以完成多行多列的快速求和。

06.输入以0开头的数字。

当我们输入0开头的数字的时候，它通常是不显示的，因此我们可以采取如下方法

方法一:在输入0之前输入英文单引号“'”，然后输入一个数字；

方法二:输入前选择输入单元格区域，用鼠标右键将单元格格式设置为[文本]。

07.对齐“名称”

选择"名称"单元格区域；然后右键单击选中区域，在弹出菜单中选择【设置单元格格式】；

选择对齐选项卡-水平对齐下拉框-分散对齐，缩进调整为2；

之后单击[确定]完成。

08.快速调整列宽

选择要调整的列，然后将光标移动到所选列号的右侧，光标会变成一个带有左右箭头的十字形。双击完成列宽调整。

09.找出重复的内容。

单击开始选项卡上的条件格式，从下拉列表中选择突出显示单元格规则-重复值，选择突出显示的填充颜色，然后单击确定标记出所有重复值。

10.分数的输入方法(1/2)

当我们在表单中输入1/2，并按enter确认时，它就变成了日期。怎么才能输入分数？

先输入0，然后空格，然后输入1/2。

11.快速输入产品编号。

一般产品编号是固定的前缀加数字或字母组合，如E-ACC-1001。

选择需要输入产品编号的单元格区域，右键单击并选择设置单元格格式。在编号页面下，选择用户自定义按类别，在类型框中输入“e-ACC-”0000，点击确定，这样在输入编号时就可以只输入后面的“1001”了。

12.单元格内容换行。

双击单元格进入编辑状态，将光标放在需要换行的位置，按Alt+ ENTER强制换行。

13.快速输入√和×

按住Alt键，然后在数字键盘上按41420，表示√，按41409，表示×

15.简单的下拉菜单

我们首先选择单元格，然后按Alt+↓(向下箭头键)，会出现一个下拉菜单可供选择，这些选项就是上面出现过的。

16、交错填充颜色

首先用颜色交替填充前两行，之后选中他们，点击【格式刷】按钮

只刷剩下的需要填充颜色的单元格区域即可。

17.快速去除小数点

首先选择数据区域，然后按按Ctrl+Shift+1即可快速去掉小数点。

18.快速删除空行

选择需要删除空行的单元格区域，按Ctrl+G打开定位窗口；

选择位置条件-空值-确定，空行被选中；

最后，按CTRL+–(减号)，选择删除整行，然后点击确定。

19.将日期转换为星期。

选择日期单元格区域，单击鼠标右键，选择设置单元格格式

在设置窗口中，选择【数字】页，分类选择【自定义】，然后在类型框中输入aaaa。

点击确定。

这些技巧都是可以在网上学到的。

你可以在网上找一些教程，也可以跟着比较厉害的老师学习。目前国内厉害的老师很多，像什么猴子啊，沈浩老师，刘万祥，张文彤老师啊，他们的课程都是很厉害的。

然后知乎最近和猴子老师一起研发了一个数据分析课程，我看了下，课程是从从基础讲起，用大厂实际案例手把手带练数据分析工具和数据分析思维，既能让大家先有系统扎实的基础，又能学完就上手工作，对0基础和漏洞多的人来说是很友好的选择。

我在上完课之后，觉得热血沸腾，当年对数据的热情又回来了，这才决定把数据分析的知识分享出来给大家。

而且我觉得这个课程最厉害的就是整个课程通俗易懂。

比如10大分析方法，依次列觉了案例，让人理解非常透彻

比如为了给同学讲清楚分析防范，举了一个APP日活异动分析的例子

通过这个例子，来解释假设检验分析法

通过对用户问题，产品问题，竞品问题的假设，最终发现是B渠道发生了问题

这么个简单的案例，就把比较高大上的假设分析法讲明白了，这个真的非常厉害。

并且课程不断强调数据分析的万能三步法，这个真的是我们数据分析师经常用到的。

这种方法教给大家，即便是不会excel，不会python，等工具，也能非常快速，通过对数据的处理，完成任务！

所以说这么牛逼的课程，我是第一次见到，所以推荐给各位！

问题；这个课程哪里去报名？学费高吗？

答：目前这个课程是近乎免费学习的，三天的课程对于我们小白来说，是可以学到我上面说到的一些知识的。对于工作来说会有很大的进步。

想要报名的话，点击下面链接就能报名了！⬇️

三、重要函数

不要以为掌握了所有的菜单功能和快捷键就是EXCEL高手。现在要开始学习一些基本的函数，比如sum，sumif，mid，left，right，vlookup。学完这些函数，我们就可以整理数据源，然后学习透视表，做一个简单的表，画一个图什么的。这已经达到初级水平了。

以下是10个最受欢迎的功能。

SUM 函数

最基本函数之一，此函数用于对单元格中的值求和。

IF 函数

它可以对值和期待值进行逻辑比较。因此IF 语句可能有两个结果。第一个结果是比较结果为True，第二个结果是比较结果为False。

例如，=IF(E2>60,"合格","不合格")，表示如果E2>60,那么就输出“超出预算合格”，否则就输出“不合格”

VLOOKUP 函数

很多同学都接触过VLOOKUP，经常使用 Excel 的人应该都很熟悉这个函数。通过该函数，你可以查找垂直列中的数据。例如，如果你要处理一个按月份排列的利润列表，VLOOKUP 可以显示你所选择月份的数据

该函数的语法规则如下：

VLOOKUP(要查找的值,要查找的区域,返回数据在查找区域的第几列数,[精确匹配/近似匹配])

比如我们要查找小王成绩，已知查询区域为A2:E9，分数在第五列，精确输出分数，则单元格写

=VLOOKUP(A13,A2:E9,5,0)

XLOOKUP 函数

本人已经弃用vlookup，改用xlookup了，他的优点就是不用再框选那么大的数据区域，不用再数第几列，只需要选择好条件列和结果列，即可。

语法：=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

第一参数（必须参数）：lookup_value，想要查找值

第二参数（必须参数）：lookup_array，想要在哪个数据区域中查找

第三参数（必须参数）：return_array，要返回的数据区域，就是结果所在的区域

如下图所示，在这里我们同样想要查找小王的成绩，只需要将公式设置为：

=XLOOKUP(A13,A1:A9,E1:E9)

就能找到正确的结果，在这里我们需要注意的是前三个参数是必须参数，后三个参数是可选参数，也就是说前三个参数必须填写，后三个参数可以选择性填写，在普通查找中，一般将其省略即可

MATCH 函数

使用MATCH 函数在范围单元格中搜索特定的项，然后返回该项在此区域中的相对位置。

例如，如果A2:A5 区域中包含值推广费、注册费和办公费，那么公式=MATCH(“办公费",A2:A5,0) 返回数字3，因为办公费是该区域中的第3项。

案例来源于网络

CHOOSE 函数

该功能用于根据索引号从最多254个值中选择一个。例如，如果value1到value7表示一周中的7天，当1到7之间的数字用作index_num时，CHOOSE将返回其中的一天。

比如下面这个案例

我们要回传第一个同事的名字

CHOOSE 中的第一项，则为小刘，输出小刘

SUMIF 函数

SUMIF 函数是数据分析领域的一个重要函数。通过该函数，你可以将符合特定要求的数字单元格中的值相加。

在下面的例子中，我们通过此函数将单元格中成绩大于 70 的数字相加。

DATEDIF函数

用于计算两个日期之间的天数、月数或年数。

假如A1单元格写的也是一个日期，那么下面的三个公式可以计算出A1单元格的日期和编辑当天的时间差，分别是年数差，月数差，天数差。注意下面公式中的引号和逗号括号都是在英文状态下输入的。

=DATEDIF(A1,TODAY(),"Y")计算年数差

=DATEDIF(A1,TODAY(),"M")计算月数差

=DATEDIF(A1,TODAY(),"D")计算天数差

DAYS 函数

此函数用于返回两个日期之间的天数。

FIND、FINDB 函数

FIND和FINDB函数用于在第二个文本字符串中定位第一个文本字符串。这两个函数返回第一个文本字符串的起始位置值，该值是根据第二个文本字符串的第一个字符计算的。

假如要查找 Kg 在“26865kg”中位置。双击单元格，输入=FIND("kg",B10)，就可以知道kg在第五个位置

FINDB函数是把汉字算作2个字符，FIND是把汉字算作1个字符，这个函数常常和right，left，mid函数一起，截取一定字符。

INDEX 函数

该函数用于返回一个数值或对表格或区域中数值的引用。

INDEX(数组或区域, 行号, 列号)

比如说下面这个案例，要引用Eze，可以在表格中输入公式=INDEX(A2:A4,3)

A2:A4的第3个单元格，就是“Eze”

INDEX 的数据区域可以不是一行或者一列，可以是一个区域.

要在数据区域中调取注册费3月份的费用

可以在单元格中输入以下公式。

=INDEX(A2:D3,2,4)

我这边建议是，如果你真的想学数据分析，可以考虑知乎知学堂的这个数据分析课程，从基础讲起，用大厂实际案例手把手带练数据分析工具和数据分析思维，既能让大家先有系统扎实的基础，又能学完就上手工作。正好适合想要尝试学习的人，可以看看自己喜不喜欢、适不适合数据分析，就算最后没做这方面工作，也是学了点技术，不亏。

参加课程还能1v1咨询专业老师，并领取免费的excel自学资料包，链接放下面，需要的可以自取