EXCEL常见数据处理操作（一）_excel数据

一、数据输入

（一）导入数据

案例：要求导入网页数据。导入第四次全国经济普查公报（第七号）中的表格信息。

1.在“数据”选项卡，选择“获取外部数据”，单击“自网站”。

2.输入其中的网址链接即可http://www.stats.gov.cn/tjsj/zxfb/201911/t20 191119_1710340.html。

（二）数据验证

数据验证，能够建立特定的规则，限制单元格输入的内容，主要用于验证数据输入的准确性，当输入非法值以及超范围值时，能给出提示或者警告。

案例：为下列表格制作数据验证，规范数据输入。

 

二、数据类型

（一）常规数据类型

Excel数据类型包括文本类型、数值类型、逻辑类型

如何快速识别这三种数据类型？

1.未设置对齐方式以及为常规格式时，文本类型默认为左对齐；

2.数值类型默认为右对齐（时间时一种特殊的数值形式存储在单元格中）；

3.逻辑类型默认为居中对齐。

（二）自定义数据类型

1.为数量添加量词“个”。

选择要修改的数据区域，右键鼠标，在弹出的快捷菜单中选择“设置单元格格式”选项，选择“自定义”，修改为“0个”即可。

2.为单价添加货币符号“￥”。

选择要修改的数据区域，右键鼠标，在弹出的快捷菜单中选择“设置单元格格式”选项，选择对一个的符号即可。

3.为总价添加单位“元”。

选择要修改的数据区域，右键鼠标，在弹出的快捷菜单中选择“设置单元格格式”选项，选择“自定义”，修改为“#元”即可。

4.把序号改成“0001”。

选择要修改的数据区域，右键鼠标，在弹出的快捷菜单中选择“设置单元格格式”选项，选择“自定义”，修改为“0000”即可，序号会自动变成0001及之前的数字编号。

三、填充

（一）按指定序列自动填充

小写字母：a=CHAR(ROW(A97))

1.查看自定义填充序列

2.自动填充到指定序列

（二）用公式生成指定内容再填充

1.生成辅助列，包括序号，指定商品名称，单价等

2.使用公式=VLOOKUP(INT(INDEX(B:B,COUNTIF(B:B,"<>"))*RAND())+1,B:C,2,0)，生成随机的商品名称，快速下拉填充；

使用公式=VLOOKUP(E2,$C$2:$D$10,2,0)，生成E列“商品名称”对应的单价，快速下拉填充；

使用公式=F2*G2，生成H列的总价，快速下拉填充；

（三）快速填充

利用快捷键进行快速有规律的填充数据。

案例：如下数据为图书的书籍名称。要求：快速添加书名号。

 

思路：

用CTRL+E直接快速填充。

 

（四）批量修改

案例：替换数据中的空白单元格。

快捷键CTRL+H使用“替换”功能，把“下 等”改成“下等”，再选择全部替换，其他还有空白单元格的内容也是同等操作。

 

四、空白数据的处理

在工作中，经常遇到一些空白的单元格，这些空白的单元格极大的影响了数据的完整性，对于空白单元格的数据，可以通过删除或者是填充方式来进行处理。

（一）填充单元格的数据

在汇总形式的数据中，经常会只在首行中输入内容，如何快速填充数据是我们需要掌握的操作。

案例：如下数据为各城市下职业类型的数据，要求：将单元格下方的数据快速填充为与上一行相同的数据。

 

1.通过CTRL+G打开定位对话框，在定位条件下选择空值。

2.在编辑栏中输入：”=A2“，按住CTRL+ENTER。

 

（二）删除空白单元格所在行

案例：在上述职业数据中，其中的空白单元格为汇总型数据。要求：删除所有汇总型数据。

1.通过CTRL+G打开定位对话框，在定位条件下选择空值。

2.选择删除，在删除下删除整行即可处理数据。

 

（三）快速删除整行为空数据

当数据中即存在空白单元格，也存在整行为空数据，要求：只删除整行为空数据。

案例：在上述各城市职业数据中，存在一些空白的整行数据。要求：只删除整行为空数据。

 

思路：

1.使用筛选功能，单击“城市”下拉按钮，筛选出“空白”，取消其余项目的勾选，单击“确定”；

2.将空白区域全部选中，CTRL+A，将其删除。

 

（四）快速删除整列为空数据

 

思路：

1.CTRL+A全选工作表，CTRL+G打开定位，点击定位条件，选择“常量”，点击确定；

2.在“开始”选项卡下，单击”格式“，选择”隐藏列“，

3.全选工作表，CTRL+G打开定位，点击定位条件，选择“空值”，点击确定；

4.在“单元格”功能组中，单击“删除”下拉按钮，执行“删除单元格”命令；

5.在数据中取消隐藏。

五、标记重复值

案例：如下数据已经在“工号”列添加了员工工号，现在需要判断有无重复工号。

1.创建一列”姓名“；

2.创建随机数在100-5000的工号，使用公式“=RANDBETWEEND(100,5000)”,下拉完成；

3.CTRL+1打开“设置单元格格式”选项，选择“自定义”，修改为“000000”即可，序号会自动变成000100到005000之间的随机数字编号；

4.全选这一列数据，复制粘贴，选择粘贴”值“；

5.在旁边建一个新列，第一行输入"JD"，再把前一列的数字复制粘贴到"JD"后面，得出”JD000567“；

6.选择”JD000567“单元格，使用快捷键CTRL+E快速把下方的所有空白单元格按这个格式填充；

7.把里面的一些名字和工号给复制粘贴，形成几个重复值。

 

思路：

1.进入“数据验证”对话框。在“设置”的“允许”中选择“自定义”，在公式中输入“=COUNTIF($B$23:B23,B23)=1”，单击“确定”按钮；

2.单击“数据验证”，选择“圈释无效数据”选项，此时将圈出重复的员工编号；

 

3.如果需要清除标记，可选择数据区域，选择“清除验证标识圈”。

六、删除重复值

如果需要快速删除原始数据中的重复值，保证留下来的数据唯一不重复，可以通过删除重复值功能进行完成。

案例：如下数据为部分各部门费用，其中包含了一些重复值，要求：快速删除重复值。

思路：选择任一单元格，点击“删除重复值”，根据需要选择合适的列。

 

七、分列

案例：按照分隔符进行分列。

如下数据为应聘人员的基本信息，如何对出生日期的「“年”」，「“月”」，「“日”」进行提取？

出生日期中包含了特殊符号”-“，因而可以采用分隔符进行分列提取。

 

案例：通过固定宽度进行分列。

现在有如下数据，如何提取出其中的出生日期？

思路：

可以采用固定宽度进行分列提取。结果如下图所示：

七、辅助列的应用

案例：工资条制作

怎么在每个人的数据上面加上标题，制作工资条呢？

思路：

1.添加辅助列，并标号。

2.把辅助列多复制一列。

3.利用筛选排序实现。结果如下图所示：