Excel基础

一、Excel中数据类型

数据类型又称数据型态，不同的数据类型具有不同的数据表示方法、不同的数据结构和不同的取值范围等。就如同生活中可将食物分谷类、豆类、蔬菜水果类、水产类、肉类等。在Excel单元格中包括4种基本数据类型：数值、日期、文本和公式。此外还有逻辑值、错误值等一些特殊的数据类型。针对不同数据类型，Excel会采用不同的方式来处理。用户如果在Excel中输入错误的数据类型，那必然也会导致错误的计算结果。

1.1.Excel数据类型分类

Excel中主要包含包含三种数据类型：文本类型、数值类型、逻辑类型

• 文本类型：汉字、空格、字母
• 数值类型：日期、时间、百分比、会计、科学计数、自定义等
• 逻辑类型：True或False，当参与运算时True为1，False为0

注意，以文本类型存储的数据一律不会参加函数运算，只有数值型和逻辑类型的才可以

科学计数说明：

• 科学计数法是一种用来表示非常大或非常小的数字的方法，它使用科学记数法的形式来简洁地表示这些数字。科学计数法的表示形式为：a × 10^n，其中 a 是一个在 1 到 10 之间的数（称为尾数或有效数字），n 是一个整数（称为指数）。在这种表示法中，a 乘以 10 的 n 次方，可以表示一个较大或较小的数字。
• 举个例子，假设要表示 3000000，可以用科学计数法表示为 3 × 10^6。这里的尾数是 3，指数是 6，表示 3 乘以 10 的 6 次方，即 3000000。
• 同样地，如果要表示 0.000005，可以用科学计数法表示为 5 × 10^-6。这里的尾数是 5，指数是 -6，表示 5 乘以 10 的负 6 次方，即 0.000005。
• 在科学计数法中，通过调整尾数和指数的值，可以方便地表示非常大或非常小的数字，使得数字的表达更加简洁和直观。
• 在 Excel 中，科学计数法通常用于显示较大或较小的数字，当数字太大或太小时，Excel 会自动采用科学计数法的形式来显示。如果需要手动将数字转换为科学计数法，可以在单元格中输入数字后，选择“科学”格式即可。

1.2.数据类型的区别

当单元格未设置具体格式或格式设置为常规时：

• 文本类型：默认左对齐
• 数值类型：默认右对齐
• 逻辑类型：默认居中对齐

如下图所示：

1.3.文本转数字的方法

文本转数字说明如下：

• 逐个转换：比较适合于需要转换的值不多的情况
• 分列转化：适合于针对某一列的数据进行转换的情况
• 通过数字1进行转换：用于比较零散、比较多需要去转化的情况

1.4.日期格式的应用

日期格式如下：

• 规范日期的输入
• 间隔天数的计算
• 分列转换格式

1.5.自定义及特殊格式的应用

自定义特殊格式：

• 定小数位数
• 数值加单位
• 设置工号
• 设置编号
• 中文大写数字

二、Excel数据清洗

数据清洗

• 合并的单元格：取消合并、ctrl+g定位空格、=上个单元格、ctrl+enter自动填充
• 空值、空行：ctrl+g定位空值空行，数据量大时通过辅助列来查找空值
• 重复值：条件格式->突出显示规则；数据->删除重复值
• 脏数据：图片、批注、空格、对齐格式

2.1.查找合并单元格

2.1.1.取消合并单元格

⑴.先取消合并，ctrl+g定位空格，点击定位空格

⑵.选择空值

⑶.点击确定后，会选中输入框，=上个单元格、ctrl+enter自动填充

2.1.2.查找表格中所有的合并单元格

选择查找，查找合并单元格。

2.2.空值、空行处理

⑴.ctrl+g定位空值空行，

⑵.数据量大时通过辅助列来查找空值，

COUNTA(A2:I2)用于统计指定范围的非空单元格

⑶.下拉统计每一行的数据，上面的非空行有9个单元格，不足的就是有空的单元格，可以通过筛选实现查看

⑷.查看筛选的结果

2.3.重复值处理

⑴.选中某一列，这里选择姓名。在条件格式中选择：突出显示规则

⑵.标记显示重复值

⑶.数据->删除重复值

对于上面的单列数据中重复的情况，选中后即可根据自己的情况 删除或者修改数据，但是更多的时候可能涉及到多列数据的重复，例如我们的表中，姓名是可以允许重复的，但是身份证是不允许重复的，我们需要根据姓名、身份证号码这两个条件进行筛选，重复的就删除掉，如下所示

选择筛选的多个条件，删除数据

2.4.脏数据处理

对于脏数据：图片、批注、空格、对齐格式的处理，

⑴.图片的查找

ctrl+g进行查找，选中对象，即可查找到对应的图标对象，如下：

⑵.查找批注

ctrl+g进行查找，选中批注，即可查找到对应的批注，如下：

如果需要删除批注，则右键点击删除即可：

⑶.空格

选中学历，进行筛选。发现有很多重复都是由于空格导致的。

对于空格进行替换

再次筛选，发现已经没有空格了

 

⑷.对齐格式的处理，

上面替换空格后，发现数据中还有两个比较特殊的地方，如下：

上面的空格是由于数据的对齐格式导致的，所以调整后对齐格式为左对齐即可：

三、数据的提取、分离和校验

数据的提取、分离和校验

• 直接提取信息：ctrl+E自动填充（文本格式）、分列（分列适合字符、固定宽度的情况）
• 提取并计算：运用相关函数计算
• 数据的分离：vlookup映射表
• 数据校验：通过设置数据校验确保单元格值的正确性

3.1.直接提取信息并计算

这里以从身份证中提取出生日期为例进行演示：

⑴.方式一：通过分列实现

①.选择分列：固定宽度

②.从身份证设置固定宽度，获取出生日期

③.获取后需要将格式转换为日期格式，通过分列实现，如下：

选择日期格式：

④.通过上面获取的出生日期计算年龄

通过：NOW() 函数返回当前的日期和时间。如下：NOW()-H2可以获取出生日期到当前时间的天数，除以365才是具体的年龄

⑤.发现保留了小数点后两位，年龄需要是整数，所以取消小数点

⑵.方式二：ctrl+E自动填充（文本格式），实现通过身份证获取出生日期

①.这里先自己复制两行出生日期：

②.光标放在第一行，按下ctrl+E自动填充

3.2.数据的分离和映射

以办公场地和城市字段进行分列建立映射

①.将办公场地字段复制到一个新的sheet中，进行去重操作，在旁边创建一列作为城市

②.建立映射表

通过vlookup实现映射表，VLOOKUP函数是Excel中非常常用的函数之一，用于在一个区域中查找某个值，并返回该值所在行的指定列的数值。VLOOKUP函数的基本语法如下：

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

其中各参数的含义如下：

• lookup_value：要在查找区域中查找的值。
• table_array：包含要在其中进行查找的数据的区域，通常是一个表格或数据区域。
• col_index_num：要返回的值所在列的索引号，即在table_array中的第几列。
• range_lookup：可选参数，用于指定查找方式。如果为TRUE（或省略），则表示采用近似匹配；如果为FALSE，则表示采用精确匹配。

变形如下：

=VLOOKUP(@L:L,Sheet1!D:E,2,FALSE)

说明：

• @L:L指的是表格中办公场地
• Sheet1!D:E，指的是从sheet1中D到E列中查询，这是一个范围
• 2指的是要返回的值所在列的索引号，也就是Sheet1!D:E中的E列
• FALSE表示的是精确匹配

操作如下所示：

③.处理后效果如下：

3.3.数据校验

Excel数据校验是一种在输入数据时对数据进行验证和限制的功能。它可以帮助确保输入的数据符合特定的规则和条件，从而提高数据的准确性和完整性。Excel数据校验可以应用于单元格、单元格范围或整个工作表，以确保输入的数据满足特定的要求。

数据校验可以包括以下内容：

1. 数据类型验证：确保输入的数据类型符合要求，例如数字、日期、文本等。
2. 数据范围验证：限制输入的数据必须在指定的范围内，例如限制数字的最小值和最大值。
3. 列表验证：限制输入的数据必须是预先定义的列表中的数值之一。
4. 自定义公式验证：使用自定义公式对输入的数据进行验证，例如根据特定条件进行验证。
5. 输入长度验证：限制输入的文本长度，确保不超过指定的字符数。

通过数据校验，可以有效地避免输入错误和不符合要求的数据，提高数据的质量和可靠性。Excel数据校验功能可以在数据输入时自动进行验证，并在不符合规则的情况下提供警告或错误提示，帮助用户及时发现并纠正错误。下面以学历字段为例进行演示说明：

①.准备数据，作为单元格只能输入的数据使用，如下：

②.选择数据====>数据验证

注意需要情况学历列的部分数据，不然之前的数据并不合法

③,数据校验设置

选择序列

选择数据来源

选择后，在学历的单元格就可以自由选择，如下：

但是这样设置后，输入不合法的数据只会给提示，如下：

上面输入不合法信息会给出提示，但有时候我们需要限制，只能输入某种内容，不合法的不允许输入，设置如下：

输入不合法的信息，会给出提示，无法输入如下：

四、Excel中数据展示

如何做到数据展现的友好性

• 子表格重命名，不要默认为sheet123的形式
• 想让人看到哪里，就在哪里退出
• 设置行高、列宽、字体
• 表头配色、冻结首行（视图->冻结首行）、设置筛选

数据条件格式设置

• 突出数据显示：>, <, =, 文本包含、重复值、前后10项/10%，高/低于均值
• 数据条方式直观显示进度
• 通过图标直观表示数据变动情况
• 利用色阶区分不同数据

4.1.显示操作要求：

4.1.1.高亮显示已完成业绩大于20w到22w之间的数据、身份证号尾号包含X的人的记录

⑴.选中已完成业绩，在条件格式中点击 突出显示单元格规则。选择 介于，表示设置区间

⑵.设置区间范围，中间可以调整高亮颜色，确定后即可看到结果

⑶.设置身份证号尾号包含X的人的记录，这里需要选择文本包含

⑷.添加包含的文本：x

⑸.设置后效果如下：

4.1.2.计算业绩进度，并设置数据条视觉化显示进度值

⑴.设置计算业绩进度

计算公式：

=实际销售额 / 目标销售额

如下设置：

⑵.设置数据条视觉化显示进度值

4.1.3.针对排名变化列，设置数据图标，红色向下的箭头表示负数，黄色表示0，绿色向上的箭头表示正数

⑴.选择排名变化这一列，点击 条件格式===>图标集，选择箭头

⑵.但是这样设置是不符合要求的，题目需要红色向下的箭头表示负数，黄色表示0，绿色向上的箭头表示正数，就需要单独设置，选中管理规则进行设置

⑶.在条件格式规则管理器中，点击编辑规则

⑷.类型选择数字，设置大于0使用绿色箭头，值为0的时候显示黄色，小于0的时候用红色

4.1.4.将业绩排名和上期排名两列为偶数的行，用红色高亮显示出来

⑴.选择需要高亮显示的区域

首先，在Excel中选择包含业绩排名和上期排名两列的区域，确保选中了需要进行高亮显示的数据范围。

⑵.添加条件格式规则

点击Excel菜单栏中的“开始”选项卡，然后点击“条件格式”按钮，在下拉菜单中选择“新建规则”。

⑶.选择规则类型：

在弹出的对话框中，选择“使用公式确定要设置格式的单元格”这个规则类型。

⑷.编写条件格式公式：

在弹出的对话框中，输入以下公式来判断业绩排名和上期排名两列为偶数的行

=AND(ISNUMBER($K1),ISNUMBER($L1),MOD(ROW(),2)=0)

这个公式中涉及到了几个Excel函数，让我逐个说明它们的作用：

ISNUMBER($K1)：

• 这个函数用于检查单元格$K1中的值是否为数字。
• 如果$K1中的值是数字，ISNUMBER函数返回TRUE；否则返回FALSE。

ISNUMBER($L1)：

• 这个函数用于检查单元格$L1中的值是否为数字。
• 如果$L1中的值是数字，ISNUMBER函数返回TRUE；否则返回FALSE。

MOD(ROW(),2)=0：

• 这个函数用于检查当前行的行号除以2的余数是否等于0。
• 如果当前行的行号除以2的余数等于0，MOD函数返回TRUE；否则返回FALSE。

AND：

• 这个函数用于判断多个条件是否同时成立。
• 如果所有条件都成立，AND函数返回TRUE；否则返回FALSE。

综合以上函数的作用，这个公式的含义是：当K列和L列同时包含数字，并且当前行为偶数行时，返回TRUE；否则返回FALSE。这个公式可能用于条件格式或筛选数据，以便在Excel中根据这些条件对数据进行高亮显示或筛选。

⑸.设置格式：

在输入完公式后，点击“格式”按钮，选择要应用的格式，比如红色高亮。

点击格式后，选择填充颜色

点击确定保存后，效果如下：

4.2.筛选和排序操作

4.2.1. 筛选出入职日期在2016/12/1到2017/7/1的记录

要在Excel中筛选出入职日期在2016/12/1到2017/7/1的记录，你可以按照以下步骤进行操作：

⑴.选择数据范围：首先，选中包含入职日期的列和其他相关列的数据范围。

⑵.打开筛选功能：在Excel的菜单栏或工具栏中，找到“数据”选项卡，然后点击“筛选”按钮。这将在数据范围的列标题上添加筛选箭头。

⑶.筛选入职日期：点击入职日期列标题上的筛选箭头，然后选择“日期筛选”或“自定义筛选”选项（具体名称可能因Excel版本而异）。

⑷.设置日期筛选条件：在弹出的日期筛选对话框中，选择“在”或“介于”选项，然后输入开始日期2016/12/1和结束日期2017/7/1。

⑸.应用筛选：点击“确定”或“应用”按钮，Excel将会根据你设置的条件筛选出入职日期在指定范围内的记录。效果如下：

4.2.2. 筛选出生在1986年的所有记录

要在Excel中筛选出生日期在1986年的所有记录，你可以按照以下步骤进行操作：

⑴.选择数据范围：首先，选中包含生日日期的列和其他相关列的数据范围。这个表中需要从身份证号码中获取出生日期

⑵.打开筛选功能：在Excel的菜单栏或工具栏中，找到“数据”选项卡，然后点击“筛选”按钮。这将在数据范围的列标题上添加筛选箭头。

⑶.筛选生日日期：点击生日日期列标题上的筛选箭头，然后选择“日期筛选”或“自定义筛选”选项（具体名称可能因Excel版本而异）。

⑷.设置日期筛选条件：在弹出的日期筛选对话框中，选择“自定义筛选”选项，然后输入筛选条件。在这里，你可以输入“大于或等于1986/1/1”并且“小于或等于1986/12/31”，这样就可以筛选出1986年的记录。

⑸.应用筛选：点击“确定”或“应用”按钮，Excel将会根据你设置的条件筛选出生日日期在1986年的记录。

⑹.如果直接是需要从身份证信息列获取1986年的记录。则可以通过文本筛选实现

然后通过 通配符来实现

4.2.3. 多条件排序：按职业序列升序、级别进行降序排列。（假定职业序列的升序为HPRS）

⑴.首先，选中您要排序的数据区域。

⑵.然后，在 Excel 的菜单栏中，点击“数据”选项卡，选择“排序”。

⑶.在弹出的“排序”对话框中，您可以添加多个排序级别：

• 第一个排序级别：选择“职业序列”，并设置为自定义顺序（假定职业序列的升序为HPRS）。
• 第二个排序级别：选择“级别”，并设置为降序。

⑷.点击“确定”，Excel 将按照先职业序列升序，再级别降序的方式进行排序。

4.2.4. 自定义排序：按照学历进行降序排序（研究生、本科、大专）

⑴.首先，选中您要排序的数据区域。
⑵.然后，在 Excel 的菜单栏中，点击“数据”选项卡，选择“排序”。

⑶.在弹出的“排序”对话框中，您可以添加自定义排序规则：

• 主要关键字：选择“学历”。
• 次序：选择“自定义序列”。

⑷.在自定义序列对话框中，输入学历的降序排列顺序，例如：“研究生, 本科, 大专”并点击“添加”按钮。

⑸.点击“确定”，Excel 将按照您所定义的学历顺序进行降序排序。