AIGC高效办公：Office2019高级应用教程（微课版） 课件 第6、7章 Excel 表格数据计算与管理、Excel 表格数据分析

第6章Excel表格数据计算与管理AIGC高效办公：Office2019高级应用教程

（微课版）新应用·真实战·全案例信息技术应用新形态立体化丛书6.1用公式计算数据内容导航6.1.1课堂案例——计算“期末考试成绩表”表格

6.1.2认识公式中的运算符6.1.3单元格引用6.1.4输入和修改公式6.1.5填充公式6.1.6定义名称6.1.7使用数组公式6.1.8审核公式6.2用函数计算数据6.3管理数据6.4综合实训6.1.1课堂案例——计算“期末考试成绩表”表格

计算“期末考试成绩表”表格中的数据，完善表格内容。【制作要求】【操作要点】使用加、减、乘、除等常用公式计算表格数据。参考效果如右图所示。6.1.1课堂案例——计算“期末考试成绩表”表格

具体操作如下。输入提示词继续输入提示词设置页边距输入数值6.1.1课堂案例——计算“期末考试成绩表”表格

不带格式填充输入“算法设计与分析”文本输入公式“=算法设计与分析+计算机网络+操作系统+数据库概论+编译原理+软件工程”输入公式“=U4/6”6.1.2认识公式中的运算符Excel2019中的公式是对工作表中的数据进行计算和操作的等式。它以等号“=”开始，其后是公式表达式，如在公式“=2+3”中，“=”和“+”是运算符，而“2”和“3”则是操作数。Excel中常用的运算符如表所示。6.1.3单元格引用单元格和单元格区域引用的作用在于标识工作表中的单元格或单元格区域，并表明公式中所使用的数据地址。一般情况下，单元格引用分为以下3种。A相对引用B绝对引用C混合引用6.1.4输入和修改公式1．输入公式在Excel表格中输入公式的方法与输入普通数据的方法类似。选择需要输入公式的单元格，输入“=”符号，进入公式输入状态，然后在英文状态下依次输入公式所需的内容，完成后按【Enter】键或单击编辑栏中“输入”按钮

计算结果。2．修改公式修改公式的方法与修改数据类似，主要有以下3种。1．选择单元格修改公式2．双击单元格修改公式3．在编辑栏中修改公式6.1.5填充公式填充公式是计算同类数据最快捷的方法。在填充公式的过程中，Excel表格会自动改变引用单元格的地址，这样省去了手动输入大量公式的操作时间，提高了工作效率。填充公式的方法主要有两种。通过快捷键填充公式通过鼠标填充公式6.1.6定义名称进行复杂的计算或引用时，通常可以为相关的单元格或单元格区域定义名称，然后在输入公式时使用名称，从而降低错误率。定义名称时，除可以通过“新建名称”对话框定义外，还可以使用编辑栏中的名称框快速定义。选择需要自定义的单元格或单元格区域，在编辑栏的名称框中输入定义后的名称，然后按【Enter】键快速为所选单元格或单元格区域命名。6.1.7使用数组公式数组由多个数据组成，并按一行、一列或多行多列排列而成。在Excel2019中，数组分为常量数组、区域数组、内存数组和命名数组4种形式。数组公式是以数组为参数，通过按【Ctrl+Shift+Enter】组合键完成编辑的特殊公式，既可以占用一个单元格，也可以占用多个单元格，其返回的结果个数根据占用的单元格数量决定。数组数组公式在进行比较复杂的运算或批量运算时，经常会用到数组和数组公式进行操作，相对于普通公式来说，数组公式更加复杂。6.1.8审核公式单击【公式】/【公式审核】选项组中的“显示公式”按钮

，工作表中的公式将全部显示出来。或者选择【文件】/【选项】命令，打开“Excel选项”对话框，选择左侧的“高级”选项卡，在“此工作表的显示选项”选项组中选中“在单元格中显示公式而非其计算结果”复选框。A追踪引用单元格B追踪从属单元格6.1用公式计算数据内容导航6.2.1课堂案例——借助文心一言计算“产品销售统计

表”表格

6.2.2认识函数结构6.2.3插入与编辑函数6.2.4常用函数6.2.5嵌套函数6.2用函数计算数据6.3管理数据6.4综合实训6.2.1课堂案例——借助文心一言计算“产品销售统计表”表格

计算“产品销售统计表”表格中的数据，完善表格内容。【制作要求】【操作要点】使用SUM函数、AVERAGE函数、RANK.EQ函数、IF函数计算表格数据参考效果如右图所示。6.2.1课堂案例——借助文心一言计算“产品销售统计表”表格

具体操作如下。输入提示词单击“自动求和”按钮在“Number”参数框中输入参数填充函数更改函数参数输入公式“=IF(H4>80000,"优秀",IF(H4>60000,"合格","差"))”输入公式“=MIN(B4:B15)”6.2.2认识函数结构函数与公式一样，也是以等号“=”开始的，但其后是函数名称和函数参数。函数的结构为：=函数名(参数1,参数2,…)，其中函数参数用括号括起来，且用逗号隔开。6.2.3插入与编辑函数插入函数时，如果非常熟悉所使用的函数及其参数类型，那么可以直接手动输入，否则可通过以下两种方法插入所需函数。1．插入函数通过选择函数类别插入函数01通过选择函数类别插入函数02编辑函数与编辑公式的方法基本相同，如复制与显示函数只需要选择所需单元格执行相应的操作，而修改函数则需要选择所需单元格后将文本插入点定位在相应的单元格或编辑栏中进行修改操作。2．编辑函数6.2.4常用函数由于Excel2019中提供了多种函数，且每个函数的功能、语法结构及其参数的含义各不相同，因此下面只介绍几种常用的函数。1．SUM函

数3．COUNT函数5．MIN函数2．AVERAGE函数4．MAX函数6．IF函数6.2.5嵌套函数嵌套函数是指将某个函数作为另一个函数的参数使用。嵌套函数可以通过直接输入的方法创建，其方法为：将文本插入点定位到编辑栏的函数中需要创建参数的位置，单击名称框右侧的下拉按钮，在弹出的下拉列表中选择需要插入的嵌套函数，然后在打开的“函数参数”对话框中设置嵌套函数的参数，并确认设置。如下图所示。6.1用公式计算数据内容导航6.1.1课堂案例——管理“销售业绩统计表”表格

6.1.2排序数据6.1.3筛选数据6.1.4分类汇总数据6.2用函数计算数据6.3管理数据6.4综合实训6.3.1课堂案例——管理“销售业绩统计表”表格

通过Excel2019的数据管理功能整理“销售业绩统计表”表格中的数据，使数据一目了然。【制作要求】【操作要点】筛选、排序和分类汇总表格中的数据，参考效果如右图所示。6.3.1课堂案例——管理“销售业绩统计表”表格

具体操作如下。单击“升序”按钮单击“筛选”按钮选中“销售业绩”和“提成额”复选框科目数据查看筛选结果各项目的汇总项6.3.2排序数据数据的简单排序是指在工作表中以一列单元格中的数据为依据，对所有数据进行排列。要进行简单排序，可先选择需要排序的一列单元格中任意一个含有数据的单元格，然后单击【数据】/【排序和筛选】选项组中的“升序”按钮或“降序”按钮，使数据区域按照由小到大或由大到小的顺序进行排列。其中，文本列的数据将按首字母的先后进行排列；数值列的数据将按数值的大小进行排列，且其他与之对应的数据将自动进行排列。1．简单排序排序数据即按照一定的方式将表格中的数据重新排列，一般来讲，数据排序的方法有3种，分别是简单排序、多条件排序和自定义排序。6.3.2排序数据多条件排序的原理为：设置主要关键字和若干次要关键字为排序依据，当主要关键字的排序依据存在相同数据时，便按次要关键字作为排序依据，以此类推。2．多条件排序选择数据区域中的任意一个单元格后，单击【数据】/【排序和筛选】选项组中的“排序”按钮

，打开“排序”对话框，先设置主要条件的关键字、排序依据和次序，然后单击“添加条件”按钮，添加次要关键字，并设置次要条件的关键字、排序依据和次序。6.3.2排序数据选择数据区域中的任意一个单元格，打开“排序”对话框，在“次序”下拉列表中选择“自定义排序”选项，打开“自定义序列”对话框，在“自定义序列”列表框中双击具体的自定义排序列表，如日期序列、月份序列等，或在右侧的列表框中输入自定义的序列，如图所示，然后单击“添加”按钮，将其添加到“自定义序列”列表框中，最后单击“确定”按钮，将自定义的排序规则添加到“次序”下拉列表中。3．自定义排序6.3.3筛选数据数据筛选功能是分析数据时的常用操作之一，其方法主要有3种，分别是自动筛选、自定义筛选和高级筛选。自动筛选数据就是根据用户设定的筛选条件，自动将表格中符合条件的数据显示出来，而将表格中的其他数据隐藏。自动筛选的方法非常简单，只需选择任意一个有数据的单元格，单击【数据】/【排序和筛选】选项组中的“筛选”按钮，每个表头数据对应的单元格右侧将出现“筛选”按钮

，单击需要筛选字段名右侧的“筛选”按钮，在弹出的下拉列表中取消选中“全选”复选框，再选中需要筛选数据对应的复选框，完成后单击“确定”按钮，工作表中便只显示需要的数据。1．自动筛选6.3.3筛选数据自定义筛选是在自动筛选的基础上进行的，即在自动筛选后，单击需要自定义筛选字段名右侧的“筛选”按钮，在弹出的下拉列表中选择“数字筛选”或“文本筛选”选项，在其级联菜单中选择“自定义筛选”选项，打开“自定义自动筛选”对话框，在其中设置自定义筛选条件，如右图所示。2．自定义筛选6.3.3筛选数据高级筛选是自定义筛选的补充，需要先输入两个或两个以上的约束条件，然后选择条件区域与筛选区域，最后得到筛选结果。3．高级筛选在空白单元格中输入筛选条件，然后选择任意一个有数据的单元格，单击【数据】/【排序和筛选】选项组中的“高级”按钮，打开“高级筛选”对话框，在其中设置筛选结果的显示方式、参与筛选的单元格区域、筛选条件的单元格区域，以及筛选结果的显示位置，如右图所示。6.3.4分类汇总数据分类汇总是指将数据按某一类型分类显示，并按一定的计算方法汇总同类数据的操作。其原理是利用排序功能将某一项目下的数据进行排序，以便将相同的数据显示在一起，进而利用各种计算方法汇总同类数据的结果。创建分类汇总时，首先应以某一项目的数据为依据进行排序，然后单击【数据】/【分级显示】选项组中的“分类汇总”按钮

，打开“分类汇总”对话框，在其中设置分类字段（即排序依据的项目）、汇总方式和汇总项。

若需要删除表格中已经创建的分类汇总，则可单击【数据】/【分级显示】选项组中的“分类汇总”按钮，在打开的“分类汇总”对话框中直接单击“全部删除”按钮。6.1用公式计算数据内容导航6.4.1计算“员工工资表”表格6.4.2借助讯飞星火大模型管理“出差统计

表”表格6.2用函数计算数据6.3管理数据6.4综合实训6.4.1计算“员工工资表”表格员工工资表一般包括工资汇总表和工资条两部分，工资汇总表能统计所有员工的工资，包括应发工资、代扣款项和实发金额等部分；而工资条是发放依据，便于员工快速查看工资的详细情况。不同公司的员工工资管理制度不同，其员工工资项目也有所不同，表所示为某公司的“员工工资表”表格的制作任务单，任务单中给出了明确的实训背景、制作要求、设计思路和参考效果等。6.4.1计算“员工工资表”表格

.打开“员工工资表.xlsx”表格，选择“1月提成”工作表，使用公式计算提成金额，再选择“1月考勤”工作表，使用SUM函数计算考勤扣款钮。

.选择“1月工资”工作表，使用公式计算工龄工资、社保代扣和实发工资；使用VLOOKUP函数计算基本工资、考勤扣款，使用IF函数计算岗位补贴和全勤奖，使用IFERROR函数计算提成工资，使用SUM函数计算应发工资和应扣工资，使用MAX函数计算个人所得税代扣。

.

新建“1月工资条”工作表，复制粘贴“1月工资”工作表中的A1、A3:O3单元格区域，输入员工编号后，使用VLOOKUP函数引用员工的基本信息和工资数据。

.将A1:O4单元格区域下拉填充至O80单元格，再将“*月员工工资表”替换为“1月工资表”。【操作提示】6.4.2借助讯飞星火大模型管理“出差统计表”表格出差统计表是一种用于记录和统计员工出差情况的表格。出差统计表可以让公司清晰地了解员工的出差情况，以便更好地管理和安排工作。同时，这也是一种有效的管理工具，可以帮助公司节省成本，提高工作效率。表

所示为“出差统计表”表格的制作任务单，任务单中给出了明确的实训背景、制作要求、设计思路和参考效果等。6.4.2借助讯飞星火大模型管理“出差统计表”表格

.搜索并登录讯飞星火大模型的官方网站，进入其应用界面后，在下方的聊天框中输入提示词“请写一段VBA代码，将表格A列至J列第2行单元格的底纹填充为蓝色，从第3行开始，行数为奇数的行填充为浅蓝，并告诉我应该如何执行这个VBA代码”，完成后按【Enter】键获取结果。

.单击“复制代码”复制这段代码，然后打开“出差统计表.xlsx”表格，按【Alt+F11】组合键，打开VBA编辑器窗口，单击“插入”选项卡，在弹出的下拉列表中选择“模块”选项。

.

在打开的窗口中按【Ctrl+V】组合键粘贴代码，然后关闭VBA编辑器窗口。

.在表格中按【Alt+F8】组合键，打开“宏”对话框，在其中保持默认设置。

.将A2:J2单元格中文本的字体颜色设置为“白色，背景1”，然后选择“部门”列中的任意一个数据单元格，将该列数据升序排列。

.按照“部门”字段对数据进行分类汇总，分别统计出住宿费、交通费、餐饮费、通信费、其他费用和合计费用的最大值、最小值及汇总值。

.复制“Sheet1”工作表，将其重命名为“高级筛选”，然后删除分类汇总，并在该工作表筛选出研发部出差费用合计大于2500的数据。【操作提示】35Thankyou!第7章Excel表格数据分析AIGC高效办公：Office2019高级应用教程

（微课版）新应用·真实战·全案例信息技术应用新形态立体化丛书7.1使用图表分析数据内容导航7.1.1课堂案例——借助讯飞星火大模型分析“成绩表”

表格数据

7.1.2了解图表组成7.1.3创建图表

7.1.4更改图表数据7.1.5应用图表样式7.1.6更改图表类型7.2使用迷你图分析数据7.3使用数据透视表和数据透视图分析数据7.4综合实训7.1.1课堂案例——借助讯飞星火大模型分析“成绩表”表格数据通过图表分析“成绩表”表格中的数据，从而更加直观和清晰地观察数据的变化趋势。【制作要求】【操作要点】向讯飞星火大模型询问分析成绩表适用的图表类型，然后根据回复的结果创建并美化图表。参考效果如右图所示。7.1.1课堂案例——借助讯飞星火大模型分析“成绩表”表格数据具体操作如下。获取结果选中“次坐标轴”复选框选择“金色，个性色4”选项选择“无轮廓”选项设置字体格式选择“圆点”选项选择“羊皮纸”选项7.1.2了解图表组成在Excel2019中，一个图表通常由多种元素组成，这些元素在不同的图表中可能会有些许差异，但无论是哪种图表，其组成一般包括图表区、绘图区、图表标题、坐标轴、数据系列、数据标签、网格线和图例等部分。7.1.3创建图表创建图表的方法有两种。一是通过按钮快速创建图表，二是通过对话框创建图表。通过按钮快速创建图表：选择需要创建图表的单元格区域，单击【插入】/【图表】选项组中的相应下拉按钮，在弹出的下拉列表中选择相应图表的子类型。选择需要创建图表的单元格区域，单击【插入】/【图表】选项组右下角的对话框启动器按钮，在打开的“插入图表”对话框中选择所需的图表类型。通过按钮快速创建图表通过对话框创建图表7.1.4更改图表数据单击【图表工具-图表设计】/【数据】选项组中的“选择数据”按钮

，打开“选择数据源”对话框，在其中重新选择和设置数据。单击“选择数据源”对话框中的“隐藏的单元格和空单元格”按钮，打开“隐藏和空单元格设置”对话框，在其中可以设置空值单元格的显示方式，以及是否显示隐藏的数据。7.1.5应用图表样式创建图表后，为了使图表效果更美观，可以设置图表样式。其方法为：选择图表，在【图表工具-图表设计】/【图表样式】选项组的“图表样式”列表框中选择所需样式。也可以单击【图表工具-图表设计】/【图表样式】选项组中的“更改颜色”下拉按钮

，在弹出的下拉列表中选择所需的颜色。7.1.6更改图表类型若所选的图表类型不适合表达当前的数据，则可以重新更换一种新的图表类型。其方法为：选择图表，单击【图表工具-图表设计】/【类型】选项组中的“更改图表类型”按钮

，打开“更改图表类型”对话框，在其中浏览不同的图表选项，并查看每种类型的示例图表，然后根据数据特点和分析目的进行选择。7.1使用图表分析数据内容导航7.2.1课堂案例——分析“产品销量表”表格数据

7.2.2创建一组迷你图7.2.3突出显示迷你图数据点

7.2使用迷你图分析数据7.3使用数据透视表和数据透视图分析数据7.4综合实训7.2.1课堂案例——分析“产品销量表”表格数据通过图表分析“产品销量表”表格中的数据，了解产品销量的变化趋势，从而为之后的决策提供依据。【制作要求】【操作要点】使用迷你图分析表格数据，并美化迷你图。参考效果如右图所示。7.2.1课堂案例——分析“产品销量表”表格数据具体操作如下。在“数据范围”输入参数单击其他按钮选择“1.5磅”选项选择“不带格式填充”选项7.2.2创建一组迷你图迷你图是存在于单元格中的小图表，它以单元格为绘图区域，可以简单快捷地绘制出数据小图表以分析表格数据。在Excel2019中，迷你图有3种，分别是折线图、柱形图和盈亏图。在【插入】/【迷你图】选项组中选择要创建的迷你图类型，打开“创建迷你图”对话框，在“数据范围”参数框中输入或选择迷你图所基于的数据区域，在“位置范围”参数框中输入或选择放置迷你图的位置区域，然后单击“确定”按钮，即可快速创建一组迷你图。

需要注意的是，迷你图无法使用【Delete】键删除，如果要删除创建的迷你图，则需要单击【迷你图工具-迷你图】/【组合】选项组中的“清除”按钮

。7.2.3突出显示迷你图数据点创建迷你图后，为了能更清晰地看到数据的变化情况，可以突出显示迷你图中的各数据点，从而更加直观地了解数据的波动情况，并做出正确的分析和决策。低点2首点4高点1负点3尾点5标记67.1使用图表分析数据内容导航7.3.1课堂案例——分析“人员结构统计表”表格

7.3.2创建数据透视表7.3.3设置值字段7.3.4应用数据透视表样式

7.3.5创建与编辑数据透视图7.3.6插入切片器

7.3.7插入日程表

7.2使用迷你图分析数据7.3使用数据透视表和数据透视图分析数据7.4综合实训7.3.1课堂案例——分析“人员结构统计表”表格分析“人员结构统计表”表格中的数据，使复杂的数据能够轻松排列和汇总。【制作要求】【操作要点】根据数据源创建数据透视表和数据透视图，并对数据透视表和数据透视图进行编辑。参考效果如右图所示。7.3.1课堂案例——分析“人员结构统计表”表格具体操作如下。打开“来自表格或区域的数据透视表”对话框选择“添加到行标签”命令输入活动字段文本框文本在“自定义名称”文本框输入文字插入数据透视表选择“以大纲形式显示”7.3.1课堂案例——分析“人员结构统计表”表格选择“浅蓝，数据透视表样式中等深浅2”选项单击“确定”按钮显示各部门的本科人数“最大值”文本框输入参数选中“学历”复选框7.3.2创建数据透视表数据透视表是一种查询并快速汇总大量数据的交互式方式，使用它不仅可以采用多种方式查询大量数据，还可以对数据进行分类汇总等操作。创建数据透视表的方法为：在表格中选择相应的数据区域，单击【插入】/【表格】选项组中的“数据透视表”按钮

，打开“来自表格或区域的数据透视表”对话框，在其中设置数据透视表的存放位置；也可以不选择数据区域，直接在“来自表格或区域的数据透视表”对话框中设置数据区域和数据透视表的存放位置。7.3.3设置值字段在“数据透视表字段”任务窗格中的字段区域中单击所需的字段下拉按钮，在弹出的下拉列表中选择“字段设置”选项，打开“值字段设置”对话框，在“值汇总方式”选项卡中的“计算类型”列表框中选择字段计算的类型，在“值显示方式”选项卡中设置数据显示的方式。7.3.4应用数据透视表样式Excel提供可以直接应用的数据透视表样式，方便用户快速美化编辑完成的数据透视表。选择数据透视表后，在【数据透视表工具-数据透视表设计】/【数据透视表样式】选项组中选择所需样式。如果用户对预设的样式不满意，则可以单击“数据透视表样式”选项组中的“快速样式”下拉按钮

，在弹出的下拉列表中选择“新建数据透视表样式”选项，在打开的“新建数据透视表样式”对话框中自定义样式。7.3.5创建与编辑数据透视图通过数据透视表创建通过原始数据创建选择包含数据的任意单元格，单击【插入】/【图表】选项组中的“数据透视图”按钮

，打开“创建数据透视图”对话框，选择放置数据透视图的位置后，单击“确定”按钮，创建一个空白的数据透视图和数据透视表。选择数据透视表中的任意一个单元格，单击【数据透视表工具-数据透视表分析】/【工具】选项组中的“数据透视图”按钮

，打开“插入图表”对话框，选择需要使用的图表类型后，单击“确定”按钮，当前工作表中将会插入数据透视图。7.3.6插入切片器切片器相当于一种快捷的筛选条件控制器，在数据透视表或数据透视图中，单击【数据透视表工具-数据透视表分析】/【筛选】选项组中的“插入切片器”按钮，在打开的“插入切片器”对话框中即可选择需要插入的切片器。插入切片器后，通过在切片器中选择相应的选项可快速实现对数据透视表或数据透视图的筛选操作。插入切片器后，系统将自动激活【数据透视表工具-数据透视表分析】/【筛选】选项组中的“筛选器连接”按钮，单击该按钮，打开“筛选器连接”对话框，取消选中某个复选框后，单击“确定”按钮，可断开数据透视表与筛选器的连接。7.3.7插入日程表当原始数据中存在日期数据时，可以创建日程表以筛选日期。单击【数据透视表工具-数据透视表分析】/【筛选】或【数据透视图工具-数据透视图分析】/【筛选】选项组中的“插入日程表”按钮

，打开“插入日程表”对话框，选中代表日期的复选框后，单击“确定”按钮，工作表中将插入“日期”卡片，在其中可选择