《信息技术》课件-项目4 数据信息加工与处理

知识模块1-4

数据信息加工与处理数据可视化数据分析高效排版数据输入目

录能力目标能够从源头控制好数据的输入能够美化数据表能够对数据进行有效分析能够将数据可视化输出学习目标3知识目标了解数据输入了解高效排版了解数据分析了解数据可视化目标分析教学重点教学难点Excel的数据输入Excel的高效排版Excel的数据分析Excel的数据可视化公式和函数排序筛选数据透视表数据透视图项目概述电子表格处理是信息化办公的重要组成部分，在数据分析和处理中发挥着重要的作用。某公司是一家集生产和销售为一体的小型口罩生产企业，因生产的口罩质量良好，疫情期间口罩生产供不应求。随着口罩生产企业的增多和国内疫情形势的好转，企业面临产能过剩问题。其中一个原因是企业信息化水平较低，销售数据分析不透彻，导致了目前的困境。本项目以该公司2020年口罩销售表、客户表和员工表3个Excel文档（本素材参考秒可督学，图1.4.1）为主要内容展开，针对该公司面临的问题，使用Excel建立简易、轻巧、高效的小型数据处理系统，助力企业实时掌握口罩销售、客户和员工情况，以便为下一步的管理决策提供依据。情境1

数据输入知识模块1-4

数据信息加工与处理任务1-规范数据的录入

规范数据录入包括两部分：

一是录入不同类型的Excel数据；二是设置数据验证，即在录入数据前给相关单元格预设数据的有效性，当用户录入不符合限定条件的数据时，系统限制录入，并出现错误提示，通过此种约束录入行为的方法来实现提高录入准确率的效果。

本任务以“员工表”工作簿中的“员工信息表”（图1.4.2）为例，介绍规范录入部分数据的方法。职工号是员工在公司的标识，需设置为唯一。

选中A列，单击“数据”选项卡“数据工具”组中的“数据验证”按钮，在打开的“数据验证”对话框的“允许”项中选择“自定义”，在“公式”项中录入“=COUNTIF(A:A,A1)=1”，然后单击“确定”按钮，如图1.4.3所示。１.录入职工号任务1-规范数据的录入注意：本题目中出现的COUNTIF()函数是有条件计数，这里是限制A列中不能出现重复值。在“数据验证”对话框中“输入信息”项的“输入信息”内输入“请输入唯一的职工号，谢谢。”

在“出错警告”项的“错误信息”内输入“您输入的信息重复，请重新输入。”如图1.4.4所示。１.录入职工号任务1-规范数据的录入在“数据验证”对话框中“输入信息”项的“输入信息”内输入“请输入唯一的职工号，谢谢。”

在“出错警告”项的“错误信息”内输入“您输入的信息重复，请重新输入。”效果如图1.4.5

所示。１.录入职工号任务1-规范数据的录入企业内员工的文化程度有高中、大专、本科、研究生和博士5种类型，部门有生产部、销售部、财务部和人事部4个部门，职位有经理、助理和职员3种类型。２.录入文化程度、部门和职位任务1-规范数据的录入选中E2:E21单元格区域，单击“数据”选项卡“数据工具”组中的“数据验证”按钮，在打开的“数据验证”对话框的“允许”项中选择“序列”，在“来源”项中录入“高中,大专,本科,研究生,博士”5种类型的文化程度（注意分隔符号是英文状态下的逗号），然后单击“确定”按钮，如图1.4.6所示。２.录入文化程度、部门和职位任务1-规范数据的录入选中E2:E21单元格区域，单击“数据”选项卡“数据工具”组中的“数据验证”按钮，在打开的“数据验证”对话框的“允许”项中选择“序列”，在“来源”项中录入“高中,大专,本科,研究生,博士”5种类型的文化程度（注意分隔符号是英文状态下的逗号），然后单击“确定”按钮，如图1.4.6所示。２.录入文化程度、部门和职位任务1-规范数据的录入同理，可对F2:F21、G2:G21单元格区域设置部门、职位数据验证，此处不再赘述。２.录入文化程度、部门和职位任务1-规范数据的录入同理，可对F2:F21、G2:G21单元格区域设置部门、职位数据验证，此处不再赘述。２.录入文化程度、部门和职位任务1-规范数据的录入

身份证号码是18位，联系方式是11位的手机号码，为了保证录入的准确性，可以分别设置其数据验证。３.录入身份证号码、联系方式任务1-规范数据的录入

选中H2:H21单元格区域，单击“数据”选项卡“数据工具”组中的“数据验证”按钮，在打开的“数据验证”对话框的“允许”项中选择“文本长度”，在“数据”项中选择“等于”，在“长度”项中输入“18”，然后单击“确定”按钮。３.录入身份证号码、联系方式任务1-规范数据的录入

同理，对I2:I21单元格区域设置手机号码数据验证（如果手机号码输入位数不是11位，即出现如图所示的“出错警告”提示）。３.录入身份证号码、联系方式任务1-规范数据的录入在统计人员信息时，身份证号、出生年月、年龄是常见的信息之一。

利用Excel中的函数，可以方便地从身份证号中获取出生年月信息并计算出年龄。职场技巧

（1）获取出生年月日获取出生年月日是使用了从字符串中间取值的MID函数。

其基本原理是，从身份证号中获取中间的一段数字。出生年月日在身份证号码中是从第7位开始的，长度是8位。

公式：=MID(C2,7,8)，如图1.4.8所示。

公式的含义：C2表示身份证号码所在的单元格地址，7,8表示从身份证号码第7个字符开始取8个字符。职场技巧（2）计算年龄

年龄每年会变，所以Excel表格中储存的人员年龄每年都会不同，要保证表格中人员年龄的准确性，需要每年对表格内容进行更新，但要是每年都手动逐个更新表格中的年龄，工作量会比较大。如果表格中存有身份证号码，我们可以根据身份证号码中的出生年月日信息，计算出这个人的实际年龄，而且这个年龄会根据电脑中的系统时间自动更新，从而避免手动更新的麻烦。职场技巧（2）计算年龄

使用的函数是获取年份的函数YEAR、获取当前系统日期和时间的函数NOW、从字符串左边取值的函数LEFT。公式：=YEAR(NOW())-LEFT(D2,4)，如图1.4.9所示。

公式的含义：YEAR(NOW())表示取当前日期的年份；LEFT(D2,4)表示从D2（出生年月日）单元格中取最左边的4个字符，即出生年份。职场技巧任务2-数据填充

在实际工作中，经常需要录入大量的有序数据，如录入1～100，那么如何高效录入呢？可以使用自动填充功能迅速完成。

本任务以“员工表”工作簿“员工信息表”中的“职工号”为例，讲解数据填充的常用方法。

在A2单元格中输入“2021001”，A3单元格中输入“2021002”，然后选中A2:A3两个单元格，用鼠标指向填充柄，当鼠标指针变为黑十字时，如图1.4.10所示，按住鼠标左键拖动到所需位置，然后松开鼠标，即可完成数据填充。１.填充柄填充任务2-数据填充

在数值型数据填充时，按Ctrl键可以实现自动填充，如图1.4.11所示。２.使用Ctrl键辅助填充任务2-数据填充

选中A2:A21的单元格区域，在A2单元格输入“2021001”的职工编号，单击“开始”选项卡“编辑”组中的“填充”按钮，在弹出的下拉菜单中选择“序列”项，在打开的“序列”对话框的“序列产生在”项中选择“列”，“类型”项中选择“等差序列”，“步长值”项中选择“1”，然后单击“确定”按钮，如图1.4.12所示。３.使用“填充”按钮填充任务2-数据填充任务3-数据清洗

Excel中的数据处理是根据数据分析的目的将收集到的数据进行加工、整理，使数据保持准确性、一致性和有效性。数据处理是数据分析前必不可少的工作，占据了整个数据分析工作量的大部分。

常见的数据处理方法包括数据清洗、数据合并、数据抽取、数据计算、数据转换等。其中，数据清洗就是将多余重复的数据筛选清除，将缺失的数据补充完整，将错误的数据纠正或者删除，为后续数据分析提供完整、准确的数据。

常见的数据清洗方法有空格数据处理、重复数据处理和缺失数据处理。

空格数据的处理除了在Word中借助“替换”功能实现外，还可以利用Excel的“替换”功能实现。只不过，“替换”功能会把所有空格去除。例如，英文名字“VivianBrown”中间的空格在使用“替换”功能时也会被删除，事实上这个空格必须存在。在这种情况下，可以使用函数TRIM删除字符串前后的空格，而字符串中间的空格不会被删除，如图1.4.13所示。１.空格数据的处理任务3-数据清洗

重复数据的处理在工作中非常常见。如果依靠人工统计，效率低，错误率高。Excel自带删除重复值功能，可以轻而易举地删除重复值。２.重复数据的处理任务3-数据清洗方法：选中带有重复值的数据，在“数据”选项卡“数据工具”组中选择“删除重复项”，如图1.4.14所示，然后在弹出的“删除重复项”对话框中，选择“全选”以确保在选定区域内删除重复值，最后单击“确定”按钮即可，如图1.4.15所示。２.重复数据的处理任务3-数据清洗

在实际应用中，相同的数据我们经常采用合并单元格的方式呈现，但用于数据分析的表格不能有合并的单元格，应该是由相同行和列组成的表格。取消合并及居中后会产生一些空值单元格，这就需要批量填充。３.缺失数据的处理任务3-数据清洗方法：选中需要取消合并的单元格区域，选择“开始”选项卡“编辑”组“查找和选择”下拉列表中的“定位条件”，如图1.4.16所示，在弹出的“定位条件”对话框中选择“空值”，如图1.4.17所示，则此时取消了单元的合并。３.缺失数据的处理任务3-数据清洗

方法：一次性选中所有空值单元格，然后按键盘上的“=”，再按键盘方向键中的“↑”，最后按Ctrl+Enter组合键，则所有空值都填充所在组对应的第一个单元格的值。此时，填充的数据是由公式产生的，通过选择性粘贴的“值”选项，可以批量去除公式，如图1.4.18所示。３.缺失数据的处理任务3-数据清洗情境2高效排版知识模块1-4

数据信息加工与处理任务1-表格排版的原则

表格一般包含行标签和数据内容两部分，但在打印时为了明确表明表格的主题，会在最上面加上一个表格标题。

表格标题在排版时应尽量相对于整个表格居中，标题内容应尽量简明扼要地说明表格所呈现的内容。在排版时，表格的格式调整要考虑到整体效果，使之主题明确、美观大方、重点突出，同时具有易读性。

Excel中提供了非常多的字体，总结起来有两类：衬线字体和无衬线字体。默认的等线是无衬线字体，而衬线字体在整体字号较小的情况下，会出现模糊不清的情况，不建议使用。１.字体任务1-表格排版的原则

参差不齐的数据会让人很难快速看懂表格要表达的含义。可以将单元格内容设置为对齐，为使用者节省时间，并提供舒适的阅读体验。２.对齐任务1-表格排版的原则

了解一些色彩心理学，会让表格更出彩。

在Excel表格设计中，建议采用“主题颜色”面板中同一列的颜色来设置表格的边框、字体颜色、填充颜色。这样可以使表格整体给人以协调、和谐的感觉。另外，应尽量避免大片使用饱和度较高的颜色，否则会让使用者有视觉不适感。３.颜色任务1-表格排版的原则

隔行填充颜色能对数据行进行视觉上的区分，让使用者更容易获取信息。４.间隔填充任务1-表格排版的原则

（1）打印前要设置边框，Excel界面下方显示的灰色网格线并不是单元格边框，若不设置边框，打印出来的表格是没有边框的。（2）设置的线框、边框颜色不要喧宾夺主，可以采用较细的线条、比文字淡的颜色来弱化线框对表格内容的视觉冲击。职场技巧任务2-设置表格格式

本任务以“客户表”工作簿中的“客户档案”工作表为例来讲解手动排版的方法。任务2-设置表格格式1.单击左上角的选中按钮，然后单击“开始”选项卡“对齐方式”组中的“水平居中”和“垂直居中”按钮，实现所有数据的水平居中和垂直居中。任务2-设置表格格式2.选中A1:G19单元格区域，单击“开始”选项卡“字体”组中的“下框线”按钮，在弹出的下拉菜单中选择“所有框线”选项，为表格添加边框。任务2-设置表格格式3.选中第1行，单击“开始”选项卡的“单元格”组中的“格式”按钮，在出现的下拉菜单中选择“行高”选项，并在弹出的“行高”对话框中将“行高”设置为“22.5”，如图1.4.19所示。同理，选中第2～19行，将其“行高”设置为“16.5”。任务2-设置表格格式4.选中A列和E列，单击“开始”选项卡“单元格”组中的“格式”按钮，在弹出的下拉菜单中选择“列宽”选项，并在弹出的“列宽”对话框中将“列宽”设置为“9”，如图1.4.20所示。同理，分别将B列、C列和F列的列宽设置为“12”，G列的列宽设置为“15”，D列的列宽设置为“45”。任务2-设置表格格式5.选中A2:G19单元格区域，单击“开始”选项卡“字体”组中的“字体设置”按钮，在打开的“设置单元格格式”对话框中将“字体”设置为“等线”，“字形”设置为默认“常规”，“字号”设置为“10”，如图1.4.21所示。任务2-设置表格格式6.选中A1:G1单元格区域，单击“开始”选项卡“字体”组中的“填充颜色”按钮，在弹出的下拉菜单中选择“其他颜色”选项，在打开的“颜色”对话框中的“自定义”选项卡中将“十六进制”设置为“#196B5B”，并将“字体”设置为“微软雅黑”，“字号”设置为“11”，“字体颜色”设置为“白色”，加粗显示，设置后的效果如图1.4.22所示。任务2-设置表格格式7.选中A2:G2单元格区域，单击“开始”选项卡“字体”组中的“填充颜色”按钮，在弹出的下拉菜单中选择“其他颜色”选项，在打开的“颜色”对话框中的“自定义”选项中将“十六进制”设置为“#ECF6F4”，并将“字体”设置为“微软雅黑”，“字号”设置为“10”，“字体颜色”设置为“黑色”，然后将D2单元格（快递地址）的对齐方式设置为“左对齐”，F2单元格（客户等级的星级标）的颜色设置为“#196B5B”，设置后的效果如图1.4.23所示。任务2-设置表格格式8.选中A3:G3单元格区域，单击“开始”选项卡“字体”组中的“填充颜色”按钮，在弹出的下拉菜单中选择“无填充颜色”选项，并将“字体”设置为“微软雅黑”，“字号”设置为“10”，“字体颜色”设置为“黑色”，然后将D3单元格（快递地址）的对齐方式设置为“左对齐”，F3单元格（客户等级的星级标）的颜色设置为“#196B5B”，效果如图1.4.24所示。任务2-设置表格格式9.选中A2:G3单元格区域，双击“开始”选项卡“剪贴板”组中的“格式刷”按钮，将第4～19行刷成同样的格式，最终效果如图1.4.25所示。任务3-优化表格排版

排版后的数据易读性大大提高，故应注意结合排版原则对其进行排版。本任务以“员工表”工作簿中的“员工信息表”（图1.4.2）为例，讲解使用套用表格格式快速实现表格排版的方法。任务3-优化表格排版1.选中A1:I21单元格区域，单击“开始”选项卡“样式”组中的“套用表格格式”按钮，在弹出的下拉菜单中选择“中等色”中第1行第2列的表格样式，整个表格即自动更改为该表格的格式效果，如图1.4.26所示。任务3-优化表格排版2.单击选中全表按钮，然后单击“开始”选项卡“对齐方式”组中的“水平居中”和“垂直居中”按钮，实现所有数据的水平居中和垂直居中，如图1.4.27所示。任务3-优化表格排版3.单击“开始”选项卡“字体”组中的“下框线”按钮，在弹出的下拉菜单中选择“所有框线”选项，为表格添加边框，如图1.4.28所示。最终效果如图1.4.29所示。任务3-优化表格排版3.单击“开始”选项卡“字体”组中的“下框线”按钮，在弹出的下拉菜单中选择“所有框线”选项，为表格添加边框，如图1.4.28所示。最终效果如图1.4.29所示。情境3

数据分析知识模块1-4

数据信息加工与处理

数据分析是指用适当的统计分析方法对收集来的数据进行分析，并将它们加以汇总、理解与消化，以求最大化地开发数据的功能，发挥数据的作用。数据分析的目的是提取数据中有用的信息。

下面简单介绍一些Excel中常用的分析方法。情境导入任务1-公式

本任务以“客户表”工作簿中的“客户档案表”为例，介绍通过使用VLOOKUP函数及数据验证功能，实现快速查询任一客户档案资料的效果，如图1.4.30所示。任务1-公式1.单击“学生操作区”工作表，选中B4单元格，输入“李鑫”，如图1.4.31所示，希望能根据客户姓名查找到联系电话、省份、快递地址等相关的客户档案信息。任务1-公式2.选中A8单元格，先查询李鑫的“联系电话”：单击“公式”选项卡“函数库”组中的“插入函数”按钮，在打开的“插入函数”对话框中选择“VLOOKUP”函数，然后单击“确定”按钮，如图1.4.32所示。任务1-公式3.在“函数参数”对话框中分别设置“Lookup_value（查找值）”为“B4”，“Table_array（数据表）”为“客户档案!A1:G19”，“Col_index_num（列序数）”为“2”，“Range_lookup（匹配条件）”为“0”（0代表精确匹配，1代表模糊匹配），这样即可完成跨表查询李鑫“联系电话”的信息，如图1.4.33所示。任务1-公式4.使用复制函数的功能，将A8单元格的公式复制到B8单元格，修改参数“Col_index_num（列序数）”为“3”，其余的参数不变，单击“确认”按钮，即可得到客户李鑫所在的“省份”信息，如图1.4.34所示。任务1-公式5.同理，复制公式到C8单元格，修改参数“Col_index_num（列序数）”为“4”，其余的参数不变，单击“确认”按钮，即可得到客户李鑫的“快递地址”信息。分别复制公式到D8、E8、F8单元格，对应修改参数“Col_index_num（列序数）”为“5”“6”“7”，其余的参数不变，单击“确认”按钮，可得到客户李鑫的“是否开票”“客户等级”“备注”信息。最终效果如图1.4.35所示。对于客户档案信息的查询，肯定不只是希望了解客户李鑫的档案信息，还希望可以查询所有客户的档案信息。将“客户姓名”改为“王强”，则所有客户档案信息随之改变，这是VLOOKUP函数的智能所在。但是手动输入客户姓名的方式不太灵活，可以通过数据验证功能，把客户姓名处做成下拉菜单的方式：单击“学生操作区”工作表，选中B4单元格，单击“数据”选项卡“数据工具”组中的“数据验证”按钮，在打开的“数据验证”对话框的“允许”项中选择“序列”，在“来源”项中选择“=客户档案!$A$2:$A$19”，然后单击“确定”按钮，如图1.4.36所示，即可从下拉菜单中选择任一客户进行档案查询。最终效果如图1.4.37所示。职场技巧职场技巧任务2-排序

在Excel数据处理中，排序是比较常见的方法。未进行排序的原始数据没有规律，看不出数据趋势，而排序可以让数据之间的关系更清晰。

本任务使用“员工表”工作簿中的“员工信息表”，先将“部门”按升序进行单列排序，然后在此基础上，将“职位”由高到低进行多条件排序。任务2-排序1.选中“部门”所在列的任一有效数据所在的单元格，单击“数据”选项卡“排序和筛选”组中的“升序”按钮，即可实现按单列排序，如图1.4.38所示。任务2-排序2.上述方法只适用于按一个关键字进行排序，若要按多关键字排序，比如按“部门”升序排序后，会发现虽然员工在同一个部门，但是“职位”排序是无序的，此处需要将“职位”按“总经理、助理、职员”的顺序进行排列。任务2-排序2.多列排序①选中A1:I21单元格区域，单击“数据”选项卡“排序和筛选”组中的“排序”按钮，则出现图1.4.39所示的“排序”对话框。任务2-排序2.多列排序②单击“添加条件”，将出现的“次要关键字”选择“职位”选项，“次序”选择“自定义序列”选项，如图1.4.40所示。任务2-排序2.多列排序③在弹出的“自定义序列”对话框中，将“输入序列”处依次输入“总经理”“助理”和“职员”后，依次单击“添加”→“确定”按钮，如图1.4.41所示，然后在返回的“排序”对话框中单击“确定”按钮，即可得到图1.4.42所示的最终效果（部分）。任务3-筛选

在工作中，当面对大量的数据时，如果只想聚焦部分数据，可以使用“筛选”功能。

任务以“客户表”工作簿中的“客户档案表”为例，对客户进行单一条件及多条件筛选：根据“客户等级”筛选出“四星级”和“五星级”的客户资料，并按“降序”排列，如图1.4.43所示；除了上述条件，再将“备注”信息中付款方式为“按季度结算”的客户筛选出来，如图1.4.44所示。任务3-筛选1.选中任意一个有数据的单元格，单击“数据”选项卡“排序和筛选”组中的“筛选”按钮，表格第1行单元格的右下角出现向下的箭头，单击“客户等级”右下角的箭头，在弹出的下拉列表中选择“降序”，再勾选“客户等级”中“四星”和“五星”前的复选框，如图1.4.45所示，此时其他数据便被隐藏了。任务3-筛选2.继续单击“备注”右下角的箭头，在弹出的下拉列表中勾选“备注”中“按季度结算”前的复选框即可，如图1.4.46所示。

（1）筛选按钮的外观职场技巧职场技巧（2）取消筛选如果需要恢复所有数据都显示的状态，单击“数据”选项卡的“排序和筛选”组中的“清除”按钮即可，如图1.4.47所示。情境4

数据可视化知识模块1-4

数据信息加工与处理

将Excel中的数据进行可视化处理，可以大大提高查看数据的效率，有助于我们迅速掌握关键信息。情境导入任务1-数据透视表

本任务以“2020年口罩销售表”为基础，通过使用数据透视表、数据透视图以及图表等功能来实现动态分析各销售员在不同城市的销售金额总和，形成一个动态看板，如图1.4.48所示。任务1-数据透视表1.在“销售数据”工作表中单击任何一个单元格，然后单击“插入”选项卡“表格”组中的“数据透视表”，则出现图1.4.49所示的“创建数据透视表”对话框。任务1-数据透视表2.选择“现有工作表”后，单击“位置”输入框（此时不要关闭对话框或点击“确定”按钮），然后单击“学生操作区”工作表，如图1.4.50所示，并在“学生操作区”工作表中单击C11单元格后，再单击“创建数据透视表”对话框中的“确定”按钮，此时会出现一个空的数据透视表。任务1-