Excel技能学习小结

前言

本文主要记录自己实习中用到的一些Excel功能总结。后续会根据需求进行更新。本人用的是Excel2016版，如果朋友们有更好的解决方案，欢迎留言交流。

【心得】

1. EXCEL的使用，问题基本都可以用度娘 + Excel自带的教程来解决。

2. 拿到一个任务，先分析这个任务能不能“偷懒”,具体地说就是：能不能把这个任务拆解为可以按固定标准处理，从而利用程序来减小我们的工作量。

3. 遇到问题及时度娘或者请教同事，事后总结经验和技巧，把能够标准化、流程化处理的操作保存下来，最好是用程序的形式。方便日后直接调用。

4. 对于常用的工具，可以自定义DIY工具箱和写VBA程序，放在自己习惯操作的位置。注意：一般你遇到的需求，别人基本都遇到过，所以直接百度，把程序拿过来，改一下，效率更高。

5. 方方格子是自己使用的一个插件，它是前人留在电脑上的一个插件。它的坑是无撤销操作，所以一定要用备份的表来操作。工具选择自己熟悉的用即可。大家如果有其他好的工具欢迎在下面留言交流。

需求1. VLOOKUP ()函数的使用

VLOOKUP ()函数是用于列的查找的。

VLOOKUP()函数语法：

VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)

= VLOOKUP (查阅值,包含查阅值的区域,包含返回值的区域中的列号,近似匹配 (TRUE or 1) 或完全匹配 (FALSE or 0))

使用注意事项：

1. 选定查阅值的区域时，查阅值必须在第1列。

2. 返回值的列数是以选定区域的第一列为1，第二列为2，依次类推。

3. 使用VLOOKUP()时注意先排查一下是否存在特殊不可见字符，否则会导致匹配不上。

需求2. 添加筛选

开始–>排序和筛选–>筛选

需求3. 冻结首行或者首列

视图–>冻结窗格–>冻结首行/首列

需求4. 整列/整列填充数字

方法一：直接拖拽

方法二：开始–>填充–>序列–>列/行–>等差/等比–>步长–>终止值.

需求5. 根据B表的X列顺序，来重新排列A表的顺序（A表中具有和B表相同的字段Y列）

第一步：先在B表X列后插入一个空列M，然后用数字1,…n填充M列，同时在A表Y列后插入一个空列N。

第二步：在A表空列N中输入公式=VLOOKUP(A表Y列第一个单元格,B表X列-M列,2,0)函数来查找。

第三步：在A表中第一行添加筛选，然后以N列的升序排序，即可实现需求。

需求6. Excel中查重、去重

如下图所示：目的是把A列中重复的项过滤掉。

【方法1】利用COUNTIF()函数和筛选工具

第一步：分别在B列和C列输入公式=COUNTIF(A:A,A2)和=COUNTIF(A$2:A2,A2)，注意A$2中的‘$`符号不能省略，否则得不到想要的结果。

第二步：添加首行添加筛选条件，在C1中设置筛选条件为1即可。效果如图：

【方法二】利用IF()+MATCH()组合函数
第一步：D列输入公式=IF(MATCH(A1,A:A,0)=ROW(A1),"无","重复")。

第二步：添加首行添加筛选条件，在C1中设置筛选条件为无即可。效果如图：

【方法3】标注高亮重复项
开始 --> 条件格式 --> 突出显示单元格规则 --> 重复项 效果如下图

【方法4方方格子】

方方格子 --> 随机重复 --> 高亮重复值。
注意：方方格子没有提供撤销的操作，一定要备份表！一定要备份表！！一定要备份表！！！

【方法5】Excel 2016去重
选定要去重的区域 --> 数据 --> 删除重复项 --> 勾选/确定

需求7. Excel中去除不可见字符

问题：如下图：用LEN()函数统计字符数，发现有些单元格明明只有2个汉字，却显示4个字符。

原因：因为单元格里面存在不可见的特殊字符，比如制表符、空格等。具体我们可以结合CODE()和RIGHT()函数来查看特殊字符的编码值。从而确定特殊字符。

Char(1)，空字符NULL
Char(9)，制表符
Char(10)，换行符
Char(13)，回车符
Char(32)，空格
Char(160)，这是HTML中的一种空格，复制到EXCEL中也看不见
可见，我们这里是因为存在制表符。
解决办法：
【方法1】方方格子

【方法2】

用公式=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(1),""),CHAR(9),""),CHAR(10),""),CHAR(13),""),CHAR(32),"")。结果如下图：

也可以写成 VBA程序，添加至功能区。具体参考真实用！ 一键清除不可见字符

需求8 条件匹配IF()、AND()、OR()函数

IF 函数是 Excel 中最常用的函数之一，它可以对值和期待值进行逻辑比较。

因此 IF 语句可能有两个结果。 第一个结果是比较结果为 True，第二个结果是比较结果为 False。

例如，=IF(C2=”Yes”,1,2) 表示 IF(C2 = Yes, 则返回 1, 否则返回 2)。

【IF多层嵌套】

=IF(C2=“男”,IF(E2>=60,IF(F2>90,“满足条件”,“不满足条件”),“不满足条件”),“不满足条件”)

【IF和AND联用】

=IF(AND(C2=“女装”,E2>=80,F2>800),“满足条件”,“不满足条件”)

或者

=IF((C2=“女装”)(E2>=80)(F2>800),“满足条件”,“不满足条件”)

【IF和OR联用】

=IF(OR(C2=“女装”,E2>=80,F2>800),“满足条件”,“不满足条件”)

或者

=IF(OR(C2=“女装”,E2>=80,F2>800),“满足条件”,“不满足条件”)

需求9 单元格数据分列

选中数据 --> 数据 --> 分列 --> 按照向导分列即可。

快捷键 Ctrl + E这个功能非常强大，强烈推荐使用。它可以连接单元格、拆分单元格、提取内容、替换字符、提取时间/身份证号、乱序重组、文本分段等等。以后要多用！

需求10 筛选包含英文字符的单元格

使用函数IF()、LEN()、LENB()三个函数即可。

IF 函数是 Excel 中最常用的函数之一，它可以对值和期待值进行逻辑比较。

因此 IF 语句可能有两个结果。 第一个结果是比较结果为 True，第二个结果是比较结果为 False。

例如，=IF(C2=”Yes”,1,2) 表示 IF(C2 = Yes, 则返回 1, 否则返回 2)。

LEN 返回文本字符串中的字符个数。

LENB 返回文本字符串中用于代表字符的字节数。

LEN(text)

LENB(text)

LEN 函数语法具有参数：Text 必需。 要查找其长度的文本。 空格将作为字符进行计数

测试	LEN()	LENB()	LENB()/LEN()	备注
abcd	4	4	1
abcd abcd	9	9	1
我们	2	4	2
@	1	1	1
# 1	1	1
$	1	1	1
%	1	1	1
^	1	1	1
"	1	1	1
:	1	1	1
@	1	1	1
【	1	2	2
※	1	2	2
	1	1	1	空格
。	1	2	2
“	1	2	2

回到需求，解决办法：

所用解决办法公式为：= IF(LENB(B2)/LEN(B2)=2,“中文”,“非中文”)

再用筛选，选择中文即可得到结果。

需求11 去除数据中的空白行

只适用于表格中除了空行外没有其他空单元格的情况，如果表格中有内容的行中也有空单元格，请勿使用此方法，否则会将包含空单元格的有内容的行一起删除。

选中筛选的列–>Ctrl + G --> 定位条件 --> 空值–> 确定

后记：
我从本硕药学零基础转行计算机，自学路上，走过很多弯路，也庆幸自己喜欢记笔记，把知识点进行总结，帮助自己成功实现转行。
2020下半年进入职场，深感自己的不足，所以2021年给自己定了个计划，每日学一技，日积月累，厚积薄发。
如果你想和我一起交流学习，欢迎大家关注我的微信公众号每日学一技，扫描下方二维码或者搜索每日学一技关注。
这个公众号主要是分享和记录自己每日的技术学习，不定期整理子类分享，主要涉及 C – > Python – > Java，计算机基础知识，机器学习，职场技能等，简单说就是一句话，成长的见证！