【干货】学会这几招，轻松搞定Excel！

2016-08-02 本文已影响1251人小昀

上一篇文章介绍了工作中常用的Windows和Word的一些快捷键，本篇我们来介绍下Excel中常用的一些快捷键及函数，工具的熟练使用不是看完教程就可以掌握的，下去记得多练习几遍哟~

一、excel基本组成：字段+数据

excel基本组成：字段+数据

二、数据导入

1、自文本导入

自文本导入数据

数据->自文本->在弹出的对话框里选择要导入的txt文件->选择文件类型，包括分隔符号和固定宽度->如果不需要将某列导入Excel中，可在“数据预览”中选择此列，然后勾选“不导入此列（跳过）”；如果无需删除任何一列，点击“完成”，弹出“导入数据 ”对话框->选择存放数据的位置，拖动单元格位置，点击“确定”，即完成。

2、自网站导入

自网站导入数据

数据->自网站->地址栏里输入网址->点击表格左上角的箭头，使之变为钩钩->点击“导入”

表格左上角箭头，点击变成勾勾

若要自动更新数据，选择导入的外部数据所在的任何一格，右键选择“刷新”即可。

（示范网页地址：http://data.house.163.com/gz/index.html）

三、常用快捷键

1、Alt+Enter：在表格内换行

2、Ctrl+Shift+上/下：选择该列所有数据，当然加上左右可选择多列

3、Ctrl+上/下：跳至表格最上/下方

4、Ctrl+C/V：不仅仅复制表格内容，也可以复制格式和公式！

5、Ctrl+D/R：复制上行数据/左列数据

6、Ctrl+Shift+~/1/2/3/4/5/6/7

Ctrl+Shift+~/1/2/3/4/5/6/7

四、数据处理

1、重复数据的处理

（1）COUNTIF函数

COUNTIF(range,criteria)：对区域中满足单个指定条件的单元格进行计数。

range：要计数的单元格范围；

criteria：计算条件，形式可以是数字、表达式或文本。

eg：=COUNTIF(A:A,A2)

计算A列每一个数据的重复次数

eg：=COUNTIF(A$2:A2,A2)

计算A列每一个数据在整列中第几次出现，可用于筛除重复项

*其中A表示所在行。

图1

绿色方框输入的是【=COUNTIF(A$2:A2,A2)】函数，如蓝色方框所示，回车后即可计算出A2单元格中的数据【张三】从A2到A2第1次出现。将鼠标放在红色箭头处，会出现一个黑色的十字箭头，拖动下拉即可计算出A列中的每个数据第几次出现，如图2所示。

图2

利用COUNTIF函数计算出每个数据第几次出现后，选中F列，使用工具栏中的筛选工具，筛选掉除了1以外的数据项，即可筛除所有的重复项。

（2）高级筛选法

数据->排序和筛选->高级->将筛选结果复制到其他位置->"列表区域“选择要筛选的数据所在的区域，“复制到”选择要存放数据的位置，勾选“选择不重复的记录”->确定。

（3）条件格式法

选中要筛选的数据->开始->条件格式->突出显示单元格规则->重复值

即可将重复的数据及所在单元格标为不同的颜色，此方法适合查重但当数据过多时不便于过滤重复数据。

2、缺失数据处理

（1）定位输入

选中要筛选的数据部分->“Ctrl+G”，弹出“定位”对话框->“定位条件”->“空值”->确定，则所有空值被一次性选中。

“Ctrl+Enter”快捷键

在不连续的区域中同时输入同一个数据或公式：选择一个单元格或区域，按住“Ctrl”不放，继续选择其他单元格，直至选中所有要处理的单元格，输入内容，按“Ctrl+Enter”，则所有选中的单元格就输入了同样的内容。

（2）查找替换

查找——“Ctrl+F”

替换——“Ctrl+H”

3、检查数据逻辑错误

（1）IF函数

IF(logical_test,value_if_true,value_if_false)

logical_test——计算公式

value_if_true——计算结果为true时返回的值

value_if_false——计算结果为false时返回的值

（2）与或函数

AND(logical1,logical2,...) ：所有参数都为真时返回true

OR(logical1,logical2,...)：至少一个参数为真就返回true

4、数据加工

（1）字段分列

菜单法

选择要转换的数据区域->数据->分列->分隔符号->选择分隔符号如“空格”->完成。

函数法

LEFT(text,[num_chars])

RIGHT(text,[num_chars])

text：包含要提取字段的文本字符串，num_chars：指定要由LEFT或RIGHT提取的字符的数量。

（2）字段合并

CONCATENATE(text1,text2,...)：将几个文本字符串合并成一个文本字符串，N≤30。

=A2&"text1"&B2&"text2"：表示 A2单元格数据、text1文本数据、B2单元格数据、text2文本数据的合并显示。

=A2&“text”&TEXT(B2,"0%")：表示A2单元格数据、text文本数据及一百分法表示的B2单元格数据的合并显示。

如图所示：

不同字段合并方式及其效果

（3）字段匹配

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup_value——要在表格或区域的第一列中查找的值，其参数可以是值或引用

table_array——包含数据的单元格区域，第一列中的值是由table_array搜索的值

col_index_num——希望返回的匹配值的序列号，参数为1时返回table_array第一列中的值，为0时返回table_array第二列中的值

range_lookup——近似匹配（TRUE）或精确匹配（FALSE）

图3

举个例子，如图3所示，有这样两张表，欲在右边的表格中输入张三、李四、赵七、林八对应的编号和成绩，我们可以在表格H2中输入函数【=VLOOKUP(F2,A:D,2,FALSE)】，然后将鼠标放在表格右下角拖动黑十字箭头下拉，即可得到这几个人对应的编号，同理在I2中输入【=VLOOKUP(F2,A:D,4,FALSE)】，处理后的效果如图4所示：