Day 22 单元格数据相关操作2

Day 22 单元格数据相关操作2

1. 快速选择多个数据

一、可以使用前边说过的“表格的选取”中的操作

二、也可以使用下方操作
步骤：选定起始单元格，使用快捷键Ctrl + Shift + 方向键

优点：可以快速选择规模较大的区域数据
缺点：如果起始行或者起始列出现空值，会出现选择不全的问题，需要稍加注意。

2. 拆分

操作：数据 --> 分列—逗号分割----目标区域----输入放入的区域

也可以用快速填充（写下一个需要的内容，按右键下拉，系统会自动匹配所需的数据）

数据	名字	姓氏	公司名称
Nancy,Smith,Contoso Ltd.	Nancy	Smith	Contoso Ltd.
Andy,North,Fabrikam Inc.	Andy	North	Fabrikam Inc.
Jan,Kotas,Relecloud	Jan	Kotas	Relecloud
Mariya,Jones,Contoso Ltd.	Mariya	Jones	Contoso Ltd.
Steven,Thorpe,Relecloud	Steven	Thorpe	Relecloud
Michael,Neipper,Fabrikam Inc.	Michael	Neipper	Fabrikam Inc.
Robert,Zare,Relecloud	Robert	Zare	Relecloud
Yvonne,McKay,Contoso Ltd.	Yvonne	McKay	Contoso Ltd.

3.选择性粘贴

要求：使用选择性粘贴方法在增加比例列实现“金额”数据列数据增加25%。

操作步骤：

**1.**在新的一列输入整体比例，整列填充。

**2.**将需要变化的数据复制。

3.在新的一列选择性粘贴即可。(数值**—乘法)**

4. 添加辅助列

辅助列就是辅助统计及分析的一列数据，它能让数据统计与分析变得更加的简单快捷，提高我们的工作效率，有时候辅助列能将非常复杂的问题变得非常的简单，善用辅助列能一定程度的提高我们的工作效率

先把数据添加序号，然后再把序号在下面复制一遍，然后选中所有的序号，右键----升序

5. 数据验证

5.1 数据有效性

• 数据有效性不仅能够对单元格的输入数据进行条件限制，还可以在单元格中创建下拉列表菜单方便用户选择输入。

• 选中单元格 --> 数据 --> 数据验证 --> 数据验证 --> 设置 --> 允许：序列 --> 来源（直接鼠标选中范围）

5.2 圈释无效数据

方法：

选中单元格 --> 数据 --> 数据验证 --> 数据验证 --> 设置 --> 允许：数值-------介于--------选择B32：D50设置数据验证1-10的范围；2.圈释无效数据。

6. INDIRECT函数

INDIRECT函数的作用：
一个间接引用函数，返回字符串类型的单元格地址指代的单元格的值。

语法：=INDIRECT(ref_text, [a1])
第一参数：定义的名称或者文本字符构成的引用的数据区域
第二参数：单元格引用类型，一般直接将其省略掉即可

7.下拉列表

下拉列表，是使用很频繁的功能之一，适用于各种数据统计的工作中，那么如果你公司的主要办公软件为Excel，那么如何在Excel中设置下拉列表，我们继续向下看。

一、如何设置一级下拉列表

设置一级下拉列表其实就是设置数据有效性的过程。

数据—数据验证----序列-----来源：A1-D1

二、如何设置二级（多级）下拉列表

INDIRECT函数的应用

建立映射：CTRL+G----定位条件----常规（选中的除了空值的内容）----根据所选名称创建----首行 建立映射后可以在：名称管理器中查看

2. 公式----据所选名称创建----首行

选中城市下面的空格-----数据验证----序列-----来源：=INDIRECT(G2)

8.数据类型

Excel中数据类型，分别是：

数值型数据类型、字符型数据类型、逻辑型数据类型、日期型数据类型、错误值型数据类型

字符型数据类型：姓名、性别、住址以及一些不需要计算的数值，比如：手机号、银行卡号等。

数值型数据类型：整数、小数、科学计数等需要计算的数字。

逻辑型数据类型：TRUE、FALSE。

日期型数据类型：2021/12/12等表示日期、时间的数据格式。

思考：2021/12/12和44542是否相等？（时间序列号值）

在Excel中，日期和时间的值是按数值的格式连续存储的，因此，可以将日期数据当成一种系列编号。

错误值型数据类型：

错误值通常是在使用公式时，由于种种原因没能返回需要的结果，而是返回了一串不同字符组成的错误值代码。

常见错误值：看链接中 的视频：：：下方链接点击即可

• #VALUE!
  公式语法不正确、

引用公式带有空字符单元格、

运算时带有文本单元格、

数组计算未使用正确格式

• #DIV/0!
• 在除法运算中，分母为0导致的计算错误、

在计算平均值时也容易出现错误值#DIV/0!

• #NULL!
• 在公式中引用单元格区域时，未加正确的区域运算符，产生了空的引用区域，导致#NULL!错误、

公式中引用不相交的单元格区域，未加正确的区域运算符

• #REF!
• 表格计算中误删了数据行列、

将单元格剪切粘贴到公式所引用的单元格上、

公式中引用了不正确的区域

• #NUM!
  公式生成的数字太大或太小，会显示#NUM!错误、

公式中引用了无效的参数，会显示#NUM!错误、

使用迭代计算RATE 和IRR函数，会显示#NUM!错误

• #NAME?
• 公式名称拼写错误、

公式中的文本值未添加双引号、

区域引用缺少冒号、

引用未定义的名称或已定义名称出现拼写错误等

• #N/A

  常见的错误原因有查找区域不存在查找值、

数据类型不匹配、

查找数据源引用错误、

引用了返回值为#N/A的函数或公式

9 定位条件

选中下表中的空格：

操作：开始 --> 查找和选择 --> 定位条件（Ctrl + G或者F5） --> 空值

要求：使用定位条件选择区域中空单元格，填入100，按组合键Ctrl+Enter填充

10. 导入数据

• Excel支持从外部快速导入数据进行数据处理、分析、呈现，导入途径包括从本地各类存储数据的文件、数据库、Azure、各类在线服务等。

​ 操作步骤：数据 --> 获取数据 --> 选择你需要使用的功能。

​ 以Excel导入CSV文件为例，大家先去拿到CSV文件。

• 也可以直接爬取网站中的列表数据（只能爬表格，其他都不行）

  操作步骤：数据 --> 获取数据 -->自其他源----网站----输入网址----获取数据

11. 查找和替换（ctrl+f 查找 ctrl+h 替换）

一、单元格的查找和替换功能你真的会用吗？接下来给大家介绍超牛逼的查找和替换功能。

我们大致分为四个方面：

1. 替换单元格的“0”
2. 替换单元格中指定的英文字母。
3. 去掉数字的小数部分
4. 颜色的替换

11.1替换单元格的“0”

将单元格中考试成绩为0的全部替换为补考。
注意：查看使用单元格匹配和不使用单元格匹配的区别

11.2将单元格中所有书写不规范的pASS修改为pass或者PASS

注意：查看使用匹配大小写和不使用匹配大小写的区别

11.3 替换中的通配符

Excel中有两种通配符，一个是*，另一个是？

*表示任意数量的字符，？表示一个字符
Excel中还一个符号_{，能够将通配符变得没有意义，}使用在通配符前面

11.4 设置背景改变字体颜色

一、将表格中所有的0分替换为不及格，并且使用白色文字、红色背景标注

使用替换操作，在替换时同时设置格式，即可实现对应操作。

• Ctrl+h------格式------字体-----设置背景和颜色

12 单元格地址引用

• F4或Fn + F4----锁定行和列

​ 原公式中的公式 地址（h1） 会随着鼠标的拖动变化，所以按f4取锁定

• 单列变多列-地址引用

​ 1.找到第一行的名字是在A列的哪个位置，建立一个等差数列

​ 2.右键下拉填充完100个单元格

​ 3.ctrl+f把A替换成=A（不开单元格匹配）

• 多列转换为一列

  1.在第一列数据的最后一个值下面，引用第二列的第一个值

  2.往右边拉得到后面每一列的第一行的值

  3.选中5列一直往下拖到100行，就有100行的数据了

  4.然后选中第一列的所有数据，选择性粘贴数值，去掉公式即可

• 知识点：跨工作表公式计算。

  “！”的作用。： =(‘06-2-一班’!B2+‘06-3-二班’!B2+‘06-4-三班’!B2+‘06-5-四班’!B2)/4