第讲Excel中数据处理.docx

《第讲Excel中数据处理.docx》由会员分享，可在线阅读，更多相关《第讲Excel中数据处理.docx（18页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第5讲Excel中数据处理第5讲Excel中数据处理学习目标在本讲中将学习在Excel2019中如何对数据进行排序、如何筛选数据、如何插入数据透视表以及如何插入数据透视图，并通过制作一份工资表来巩固前面学习的知识。主要内容对数据进行排序筛选数据插入数据透视表插入数据透视图应用实践编制工资表5.1 对数据进行排序在序列中输入数据后，可以对序列数据进行升序或降序排列，选中要排序的单个序列中的任意单元格，单击“编辑”面板中的“排序”按钮，在弹出的菜单中选择“升序”或“降序”命令，即可对序列中的数据按照升序或降序进行排序了。5.2 筛选数据筛选数据就是将表格中符合条件的数据显示，而将不符合条件的数据隐

2、藏，从而方便直观地对数据进行查看、对比以及分析。筛选数据的具体操作方法如下：对数据进行任意排序后，单击“编辑”面板中的“排序和筛选”按钮，在弹出的菜单中选择“筛选”命令，此时序列字段所在单元格中将显示一个下拉按钮。单击某个字段后的下拉按钮，将打开一个列表框。在列表框中取消勾选某个数据，单击“确定”按钮，即可在表格中隐藏该数据所在的序列。如果要恢复显示，只要再次单击字段后的下拉按钮，在弹出的列表框中重新选中，单击“确定”按钮即可。通过数据筛选功能还可以筛选出指定范围内的数据，如在上表中要筛选出销售总价在“300000900000”之间的数值，其方法如下：单击“销售总价”下拉按钮，在弹出的列表中指

3、向“数字筛选介于”命令。在打开的“自定义自动筛选方式”对话框中将“销售总价”设置为“大于或等于300000”与“小于或等于900000”，单击“确定”按钮。此时即可筛选出销售总价大于300000且小于900000之间的所有数据，并且将不符合该条件的数据全部隐藏。如果要取消筛选，只要单击“销售总价”下拉按钮，在弹出的列表中选择“从销售总价中清除筛选”命令即可。如果要清除所有筛选，只要再次单击“排序和筛选”按钮，在弹出的菜单中选择“筛选”命令即可。5.3 插入数据透视表数据透视表是一种对大量数据进行快速汇总和建立交叉列表的交互式表格，通过数据透视表可以转换行和列以查看源数据的不同汇总结果，可以显示

4、不同的页面数据。创建数据透视表的具体操作方法如下：在数据表中选中任意一个单元格，切换到“插入”选项卡，单击“表”面板中的“数据透视表”按钮，打开“创建数据透视表”对话框。选中“选择一个表或区域”选项，单击按钮，在工作表中选择要创建透视表的源数据表。在“选择防止数据透视表的位置”区域中选择“新工作表”，单击“确定”按钮。此时将切换到数据透视表并显示“数据透视表字段列表”任务窗格，在列表框中选择要显示的字段。将列表框中字段拖动到窗格下方的项目中，可以调整数据透视表的结构。5.4 插入数据透视图使用数据透视表可以准确计算和分析数据，但有时候数据源较大，数据透视表中的数据将非常多，数据排列将非常复杂。

5、此时使用数据透视图将能更直观地分析数据。创建数据透视图的具体操作方法如下：选中要创建数据透视图的表格区域，单击“表”面板中的“数据透视表”下拉按钮，在弹出的菜单中选择“数据透视图”命令，打开“创建数据透视图”对话框。选择在新工作表中插入，单击“确定”按钮，即可建立一个对应的数据透视图，在“数据透视表字段列表”任务窗格中调整字段在数据表中的布局后，数据透视表就会进行相应的显示。插入数据透视图后，将显示“设计”、“布局”、“格式”与“分析”4个选项卡，通过选项卡可以对数据透视图进行相应的设置。5.5 应用实践编制工资表任务设计本例制作的是工资表，在制作时首先录入相应的数据，然后对表格进行相应的修饰

6、、计算保存，最后根据数据制作透视表和透视图。完成任务1.建立工资表下面我们首先录入工资表的基础数据，然后应用公式，对数据进行运算和填充，具体的操作方法如下：1：新建一个空白Excel工作簿，将其重命名为“工资表”，并进行保存。2：在A1单元格中输入标题名称“工资表”。3：在单元格区域A2：K2单元格中输入项目标题名称。4：接着输入部门的名称，每个部门有多少员工，就重复多少次。5：继续输入每个员工的详细信息。2.使用公式自动计算下面使用公式来计算工资数据，具体的操作步骤如下。1：在本例中，由于“实得工资=基本工资+奖金+全勤+用餐补贴+交通补贴-预计”。所以先在单元格K3中输入公式“=E3+F3

7、+G3+H3+I3-J3”，然后按下“Enter”键计算出“实得工资”的值。2：用填充公式的方法计算出其它员工的实得工资。选中单元格K3，将鼠标移动到该单元格右下角的填充柄上，当鼠标指针变为时，按住鼠标左键并向下拖动，当虚框线到达单元格K17时松开鼠标即可算出其它员工的“实得工资”。3.美化工资表下面接着美化工资表的文字，以及为工资表添加边框和底纹，具体的操作方法如下：1：选中单元格区域A1：K1，单击“合并后居中”按钮。2：接着为标题设置字体格式。3：选中单元格区域A2：K17，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”选项。4：在弹出“设置单元格格式”对话框中单击“边框”选项卡

8、，为选中单元格设置较粗的外边框，较细的内部框线，颜色选择“红色”，单击“确定”按钮。5：接着选中单元格区域A2：K2，单击“字体”工具组中的“填充颜色”按钮，在弹出的列表中选择一种颜色作为单元格的底纹。6：再单击“字体颜色”按钮，在弹出的列表框中，将字体填充为白色。7：按着为工资表其他文字设置字体样式和字体大小。8：双击“Sheet1”标签，将其重命名为“工资表”。4.使用“记录单”查找信息一般在大型的企业中，员工的人数可能不是几十人，而是几百人甚至上千人，如果继续使用滚动屏幕的方法来查找某个员工的工资，会为你的工作添加麻烦更浪费时间，如果使用下面讲述的方法来查找员工工资，相信会让你事半功倍。

9、使用“记录单”查找某个员工工资的方法如下：1：记录单的功能不在功能区，需要将其添加进来，单击“自定义快速访问工具栏”右侧的下拉按钮，在弹出的快捷菜单中选择“其他命令”选项，弹出“Excel选项”对话框，单击“常用命令”后面的下拉按钮，选择“不在功能区中的命令”选项，然后在显示的命令列表中找到“记录单”将其选中，单击“添加”按钮，然后单击“确定”按钮。2：选中整个表格数据，然后单击“自定义快速访问工具栏”中的“记录单”按钮，此时将一个对话框，单击“条件”按钮。3：在“姓名”后的文本框中录入想要查找的员工姓名。再单击“下一条”按钮，该员工的相关信息就显示出来了。5.自动筛选数据现在，我们需要筛选出

10、“实得工资”大于5000而又小于8000的员工工资，可以使用Excel的自动筛选功能来完成这项工作，具体的操作方法如下：1：先单击需要筛选的数据清单中任一单元格，单击“数据”选项卡，然后单击“排序和筛选”工具组中的“筛选”按钮。2：这时在数据清单标题行上字段右边出现下拉按钮；单击此按钮会弹出下拉列表，在列表中可以对数据清单进行各种方式的自动筛选。3：如果要显示所有行，在下拉菜单中勾选上“全选”选项。4：如果要显示在由数据项或百分比指定的上限或下限范围内的所有数据行，请在下拉菜单中选择“数字筛选”选项，在子菜单中选择“10个最大的值”选项。5：如果要将所在列 作为升序字段进行排序，请在下拉菜单中

11、选择“升序”选项。 如果要将所在列作为降序字段进行排序，请在下拉菜单中选择“降序”选项。6：下面介绍一下自定义筛选，单击“实得工资”后的下拉按钮，在弹出的下拉菜单中选择“数字筛选”选项，然后在子菜单中选择“自定义筛选”命令，打开“自定义自动筛选方式”对话框。7：输入第一个条件：在左边的列表框中选择大于，在右边的列表框中输入5000；单击“与”单选框；输入第二个条件：在左边的列表中选择小于，在右边的列表框中输入8000。完成后单击“确定”按钮。8：筛选后的字段箭头标记和列号，将呈蓝色显示。6.数据分类汇总分类汇总是指根据数据库中的某一列数据将所有记录分类，然后对每一类记录进行分类汇总。分类汇总可

12、进行分类求和、求平均值、求最大值、求最小值等。在进行分类汇总前需先对数据清单进行排序。下面按“部门”进行汇总，具体的操作方法如下：1：首先单击“部门”字段中的任一单元格，然后单击“数据”选项卡，接着单击“排序和筛选”工具组中的“排序”按钮。2：在弹出的“排序”对话框中选择主要关键字为“部门”，选择次序为“升序”，然后单击“确定”按钮。3：单击“分级显示”工具组中的“分类汇总”按钮，打开“分类汇总”对话框。4：在“分类字段”下拉列表中，选择需要用来分类汇总的数据列。这里选择“部门”。在“汇总方式”下拉列表中，选择需要用来计算分类汇总的方式。这里选择“求和”。在“选定汇总项”列表框中，选择需要对其

13、汇总计算的数值列对应的复选框。这里选择“实得工资”。5：单击“确定”按钮，返回到工作簿中即可看到汇总的结果。7.为工资表添加密码为了保护工资表的完整性，还需要对其添加密码，具体的操作步骤如下：1：选中任一表格，单击“审阅”选项卡，然后单击“更改”工具组中的“保护工作表”按钮。2：弹出“保护工作表”对话框，选择允许其他用户编辑的内容，如果不允许任何人编辑，则所有选项都不勾选，接着输入密码，单击“确定”按钮。3：在弹出的“确认密码”对话框中再次输入密码，然后单击“确定”按钮。4：如果要取消对工作表的保护，只需单击“更改”工具组中的“撤销工作表保护”按钮。5：在弹出的“撤消工作表保护”对话框中输入密

14、码，然后单击“确定”按钮即可。第6讲图表的制作与编辑学习目标在本讲中将学习在Excel2019中如何创建图表、如何使用文本注释以及如何调整图表位置和大小，并通过制作一份公司年度销售报表来巩固前面学习的知识。主要内容创建图表使用文本注释图表位置和大小的调整应用实践制作公司年度销售报表6.1 创建图表图表是数据的图形化表示。在创建图表前，必须有一些数据。图表本质上是按照工作表中的数据而创建的对象。对象由一个或者多个以图形方式显示的数据系列组成。数据系列的外观取决于选定的图表类型。采用合适的图表类型来显示数据将有助于理解数据。在Excel中创建图表的具体操作步骤如下：01打开要创建图表的工作表，选中

15、包含图表数据的单元格数据区。02单击“插入”选项卡，在“图表”工具组中单击“柱形图”按钮，在打开的下拉菜单中选择“所有图表类型”选项。03接着弹出“插入图表”对话框，在该对话框中选择一种图表类型，如“柱形图”，然后在右边窗格中一种合适的柱形图类型。04单击“确定”按钮确认选择，返回工作表窗口可看到创建的图表浮动在表格上方，单击“布局”选项卡，在“标签”工具组中单击“图表标题”按钮，在弹出的下拉列表中选择“图表上方”选项。05确认选择返回工作表窗口，可以看见在图表上方出现一个“图表标题”的文本框。06将光标定位到“图表标题”文本框中选中“图表标题”文本，然后输入图表的标题替换选中的文本，例如“2

16、019年销售记录”。07在“标签”工具组中单击“数据标签”按钮，在弹出的下拉菜单中选择“数据标签外”选项。08确认选择返回工作表窗口，可以看见一个完整的图表创建成功。6.2 使用文本注释在制作好图表后，为了方便别人理解图表需要传达的意思，有时需要添加一些文本注释。添加文本注释的具体操作步骤如下：01打开需要添加文本注释的图表，单击“插入”选项卡，在“插图”工具组中单击“形状”按钮，在弹出的下拉列表中选择一种图形。02确认选择返回工作表窗口，在需要使用文本注释的区域按住鼠标左键拖动，即可插入一个文本框。02如果需要修改图形样式，可以在“形状样式”工具组中选择图形样式。03在文本框上单击鼠标右键，

17、然后在弹出的快捷菜单中选择“添加文字”命令，这样就可以在文本框中输入要添加的注释，如果不能够显示所有文字，可以通过拖动图形四周的控制按钮来调节图形大小。如果不需要有底色，可以在“形状颜色”和“形状轮廓”中选择“无填充颜色”和“无轮廓”。6.3 图表位置和大小的调整我们在创建好一个新的工作图表后，它一般是重叠在原始数据表上，所以需要移动图表的位置。在Excel中，如要移动图表的位置，只需在图表的任意位置单击鼠标选中图表。此时，在图表的四周将出现8个尺寸控点。把鼠标放在图表区，待光标变为图标时，按住左键不放，拖动到目标位置松开即可实现图表的移动。如果要改变图表的大小，只需在图表的任意位置处单击鼠标

18、，此时在图表的四周将出现8个尺寸控点，移动光标到某个控点上，待光标变为图标时，按住鼠标左键不放，拖动调整图表的大小。6.4 应用实践制作公司年度销售报表任务设计本例制作的是公司年度销售报表，在制作时首先录入相应的数据后，然后对表格进行相应的修饰和数据计算，最后制作价格走势图和销售走势图。完成任务1.创建表格结构创建汇总表格应先制作好表格的结构和录入相关的数据资料，具体操作步骤如下。1：新建一个Excel工作薄，将其另存为“年度销售报表”，修改第1个工作表的标签为“汇总表”。2：将第2个工作表的标签修改为“图表”，删除第3个工作表。2.录入数据现在需要在“汇总表”中输入该公司卖场中“空调销量”的

19、汇总数据，具体操作步骤如下：1：单击工作表中需要输入数据的相应单元格，按照实际销售数据录入到对应的项目中。2：因为是年度销售报表，需要将各种产品的销售总额分别计算，然后再汇总，所以需要添加各种产品的销售总额以及所有产品销售总额的合计项。3.设置表格格式下面为工作表设置格式，具体操作步骤如下。1：选中单元格A1：J1，然后单击“对齐方式”工具组中的“合并后居中”按钮。2：对标题文字进行合并居中后，并为文字设置字体样式和字号大小。3：选中单元格区域A2：J2，然后单击“开始”选项卡，在“字体”工具组中设置中文字体为“黑体”，西文字体为“Aril”，字号为“12”，字体颜色为“深蓝”，单击“对齐方式

20、”工具组中的“居中”按钮。4：选中单元格区域A3：J14，然后单击“开始”选项卡，在“字体”工具组中设置西文字体为“Aril”，字号为“10”，字体颜色为“浅蓝”，单击“对齐方式”工具组中的“居中”按钮。5：选中单元格区域A16：J17，然后单击“开始”选项卡，在“字体”工具组中设置中文字体为“黑体”，字号为“12”，字体颜色为“红色”，在“对齐方式”工具组中单击“居中”按钮。4.添加表格边框和底纹为了让工作表看起来美观整齐，数据分类详细，我们需要为表格添加边框和底纹，添加表格边框和底纹的具体操作步骤如下。1：选中单元格区域A2：J14，单击“开始”选项卡，在“单元格”工具组中点击“格式”按钮

21、，然后在弹出的菜单中选择“设置单元格格式”选项。2：弹出“设置单元格格式”对话框，在该对话框中点击“边框”标签，然后在颜色下拉列表中选择线条颜色“紫色”，在样式栏中选择一种粗线条样式，然后点击“外边框”按钮。3：接着在样式栏中选择一种细线条样式，然后点击“内部”按钮。 4：单击“确定”按钮返回工作表窗口，可以看见被选中的单元格区域已经被添加上了外部边框和内部线条。5：选中单元格区域A16：J17，按照前面的方法添加外部边框和内部线条。 6：选中单元格区域B16：C16，然后点击“对齐方式”工具组中的“合并后居中”按钮，然后将其他需要合并的单元格按照这样的方法进行合并。7：选中单元格A2：J2，

22、单击“字体”工具组中的“填充颜色”按钮，为其设置一种填充颜色。8：接着选中单元格A16：J16，也为其设置一种填充颜色。 5.使用公式计算销售额现在需要输入公式来计算各种产品的销售额，在输入公式以前最好在纸上先计划出相应单元格中应该输入的公式，输入公式的具体操作步骤如下。1：选中工作表中所有的价格数据，然后单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”选项。2：在弹出的“设置单元格格式”对话框中的“分类”列表中选择“货币”，然后选择货币符号为“￥中文（中国）”，设置完成后单击“确定”按钮。3：返回到文档中，即可看到为价格数据添加货币符号后的效果。 4：选中单元格“D3”，然后在编辑栏中

23、输入公式“=B3*C3”，点击“输入”按钮确认输入。5：将鼠标移动到单元格D3右下角的黑色填充柄上，当鼠标指针变为黑色的十字图标时，按下鼠标左键向下拖动至单元格D14，将鼠标拖动经过的单元格都填充上公式。6：按照前面的步骤在“冰箱”和“电脑”的销售额列表中输入公式并填充公式。7：点击选中单元格BC17，然后点击“编辑”工具组中的“求和”按钮，这时系统会自动在单元格中输入求和公式，并以闪动的虚线指定求和单元格区域。8：将鼠标移动到工作表窗口中，选中单元格D3，然后按住鼠标左键不放向下拖动至单元格D14，选中单元格区域D3：D14。 9：点击“输入”按钮确认输入，返回表格窗口，可以看见在单元格BC

24、17中已经按照公式计算出空调的销售总额，10：按照这样的方法在“冰箱”、“电脑”和“合计”单元格中输入对应的公式并计算出结果。如在单元格中出现符号，表示该单元格宽度不够，不能够完全显示单元格中的数据，可以通过拖动对应列标来调整单元格的宽度即可解决。6.制作商品销量走势图表折线图可以显示了相同间隔内数据的预测趋势，因此它来显示商品销量的走势，可以使销量高低起伏表现得淋漓尽致，制作销量走势图表具体的操作步骤如下。1：同时选中3件商品的全年销量，单击“插入”选项卡，然后单击“图表”工具组中的“折线图”按钮，在弹出的下拉菜单中选择一种折线图样式。2：这时在工作表中会插入一个折线图表。选中图表，单击鼠标

25、右键，在弹出的快捷菜单中选择“移动图表”选项。这时会弹出一个“移动图表”对话框，设置对象位于“图表”，然后单击“确定”按钮。7.美化商品销量走势图表在嵌入图表之后，会发现图表中都是数据，比较单调，我们可以对图表进行美化处理，让图表看起来更加的美观，美化图表的具体操作步骤如下。1：选中图表，单击“布局”选项卡，然后单击“标签”工具组中的“图表标题”按钮，在弹出的下拉菜单中选择“图表上方”选项。这时图表上方会显示一个文本框，输入图表的标题名称，为文字设置字体格式。2：单击“格式”选项卡，单击“形状样式”工具组中的下拉按钮，在弹出的列表中选择一种图表样式。3：保持图表的选中状态，单击“格式”选项卡，然后单击“形状样式”工具组中的“形状效果”按钮，在弹出的下拉菜单中选择“预设”选项，在子菜单中选择一种预设效果。4： 拖动图表四周的控制点，调整图表的宽度和高度。到此，这个实例就全部制作完成了。第 18 页