信息技术基础 课件 项目四 Excel 2019电子表格处理软件及应用

项目四

Excel2019电子表格处理软件及应用

教材编写组2024.06学习目标1.掌握Excel2019在电子表格制作、数据运算、数据图表化和数据管理方面的主要功能。

2.能够通过分析问题，运用Excel2019解决实际生活工作中的问题。

3.増强学生对待数据科学严谨的工作作风和精益求精的工匠精神。

4.培养追求高标准的数据精准理念。项目三Word2019文字处理软件及应用制作数据电子表格数据的运算数据管理与分析可视化数据模拟分析工作表数据创建复杂图表任务五任务六制作数据电子表格任务一任务一--任务描述制作如图所示的“学生成绩统计表”，要求工作表中字体设置为宋体、11磅，标题18，不及格的科目成绩用浅红色底纹红色字体显示，数值数据保留整数，非数字数据蓝色显示，所有数据水平垂直居中，并将A1:N1单元格区域合并后居中并设置浅绿色底纹，添加双实线蓝色外框和细实线黑色内分隔线，并调整行高和列宽。1.Excel2019软件启动Excel2019的启动桌面上的excel图标开始菜单固定项中的excel图标开始菜单应用程序2.Excel2019窗口结构工作区（1）编辑栏名称框插入函数编辑栏确定取消显示活动单元格地址显示、编辑活动单元格的数据和公式活动单元格的函数、公式输入（2）工作区工作区全选按钮列标行号共16384列共1048576行（3）工作薄、工作表和单元格工作薄、工作表和单元格工作薄Excel2019创建或处理的文件叫工作薄，扩展名为.xlsx,如文件“个税计算.xlsx”工作表一个工作薄默认只有Sheet1工作表单元格是excel的最小组成单位，是基本的“存储单元”单元格地址：列标、行号并列在一起表示，如A5、D6单元格区域地址：A3:F10，表示A3到F10之间的区域，冒号隔开标签滚动按钮活动工作表添加工作表3.新建工作薄新建工作薄启动excel2019,直接建立新工作薄Windows窗口右击菜单，新建MicrosoftExcel工作表在打开的excel2019窗口中，执行“文件”--“新建”命令4.数据的输入单元格数据文本型默认左对齐非数字型文本数字型文本不参与运算的数字，如身份证号，考号先输入英文标点‘再输入数字数值型参与运算的数字，默认右对齐分数的输入：输入0和空格和分数如需带货币符号，或保留小数位置，则使用“设置单元格格式”对话框进行设置日期型输入时用“/”或“-”分隔,默认为右对齐时间型格式为:hh:mm:ssam/pm，默认为右对齐单元格中如同时有日期和时间，则中间由空格隔开5.数据的填充输入数据填充填充普通数据数字型拖动填充柄填充相同数据Ctrl+拖动填充柄，填充递增为1的数据文本型Ctrl+拖动填充柄填充相同数据拖动填充柄填充递增为1的数据填充序列数据内置序列数据，如星期、日期，方法同文本型数据填充非内置序列数据选中两个已输入数据的单元格，使用填充柄向下填充使用序列对话框进行设置填充自定义序列文件—选项—高级--常规—编辑自定义列表数据验证数据选项卡—数据工具组—数据验证6.数据的编辑数据编辑修改单元格数据全部修改：选定单元格直接输入新数据部分修改双击单元格，选定需要修改内容输入新数据选中单元格，在编辑栏中修改移动和复制数据使用剪贴板使用鼠标移动：拖动鼠标复制：ctrl+拖动鼠标选择性粘贴数据清除开始—编辑—清除全部清除、清除格式、清除内容（delete键）、清除批注、清除链接7.单元格的编辑单元格的编辑插入单元格选定单元格打开“插入”对话框菜单项：开始—插入—插入单元格右击单元格，选择“插入”选项快捷键ctrl+”+”选择“活动单元格右移”或“活动单元格下移”选项，“确定”。插入与选定单元格数相同的单元格。插入行或列选定与所需插入行数（列数）相同的行（列）打开“插入”对话框菜单项\快捷菜单\快捷键选择“整行”（整列），“确定”。删除单元格选定单元格删除对话框开始—单元格—删除快捷菜单“删除”选项选择“右侧单元格左移”或“下方单元格上移”选项删除行或列选定与所需删除相同数量的行或列选择删除菜单项开始—单元格—删除—删除工作表行或删除工作表列快捷菜单，打开删除对话框，选择“整行”或“整列”8.工作表的格式化工作表的格式化单元格格式“开始”选项卡—“字体”（或“段落”或“对齐”）中的按钮“开始”选项卡—“字体”（或“段落”或“对齐”）组对话框启动按钮，打开设置单元格格式对话框右击选定的单元格，在快捷菜单中选择“单元格格式”，打开单元格格式对话框行高\列宽快捷方法：鼠标指针指向行号或列号的分隔线上，待光标变成双向箭头时，拖动双向箭头改变行高或列宽精确调方法：单击”开始”→“单元格”→“格式”下拉按钮，选择“行高”或“列宽”命令，对话框中输入列宽或行高的值，然后，单击“确定”按钮。自动调整：“开始”选项卡—“单元格”组—“格式”—“自动调整行高”/“自动调整列宽”条件格式为同一类数据设置统一格式使其突出显示设置方法：“开始”选项卡—“样式”组—“条件格式”—“新建规则”边框和底纹选定区域，“设置单元格格式”对话框的“边框”和“填充”选项卡“边框”选项卡：分别设置线条样式、线条颜色、内边框、外边框“填充”选项卡：背景色、填充效果、图案填充颜色和样式9.工作表快速格式化工作表快速格式化套用单元格样式套用单元格样式选定单元格区域“开始”选项卡—“样式”组—“单元格样式”选择内置单元格样式新建单元格样式“开始”选项卡—“样式”组—“单元格格式”—“新建单元格样式”，打开对话框，进行设置合并单元格样式用于工作薄之间新建样式相互套用。新建样式若要应用于另外的工作薄，需要先合并单元格样式对话框：“开始”选项卡—“样式”组—“单元格格式”—“合并样式”套用表格样式套用表格样式“开始”选项卡—“样式”组—“套用表格格式”新建表格样式“开始”选项卡—“样式”组—“套用表格样格式”—“新建表格样式”数据的运算任务二任务二--任务描述在如图所示的学生成绩统计资表中计算每个学生的总分、名次和分班，以及各科平均分，最高分和最低分。学生成绩统计数据运算数据运算函数计算函数对话框打开“开始”选项卡—“求和”下拉按钮—“其他函数”“公式”选项卡—“插入函数”按钮“公式”选项卡—“函数库”组—相应函数类别组成函数名、各参数、函数说明、参数说明、计算结果公式计算格式：等号，函数、常量、运算符、单元格地址组成的表达式方法：确定计算公式输入公式单元格地址的引用相对引用Excel默认的单元格引用方式，基本单元格间相对位置关系的引用方式复制公式时，跟随单元格地址的改变而相应改变绝对引用在行号、列标前都加上符号“$”指向公式中固定位置的单元格单元格地址不随公式的复制发生改变混合引用部分使用相对引用，部分使用绝对引用，如A$8，$D6跨表引用在当前工作表的单元格中引用其他工作表中的单元格同类型计算，使用填充柄完成公式填充Excel2019中的常用函数及用法常用函数数据库函数日期与时间函数工程函数财务函数信息函数逻辑函数查询和引用函数数学和三角函数统计函数文本函数用户自定义函数Excel2019中的常用函数及用法（1）常用函数①SUM函数。函数名称：SUM主要功能：计算所有参数数值的和。使用格式：SUM(Number1,Number2…)参数说明：Number1、Number2、…代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等。特别说明：如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。Excel2019中的常用函数及用法（1）常用函数②AVERAGE函数。函数名称：AVERAGE主要功能：求出所有参数的算术平均值。使用格式：AVERAGE(number1,number2,…)参数说明：number1,number2,…是需要求平均值的数值或引用单元格（区域），参数不超过30个。特别提醒：如果引用区域中包含0值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。4.Excel2019中的常用函数及用法（1）常用函数③MAX函数。函数名称：MAX主要功能：求出一组数中的最大值。使用格式：MAX(number1,number2，…)参数说明：number1,number2，…代表需要求最大值的数值或引用单元格（区域），参数不超过30个。特别提醒：如果参数中有文本或逻辑值，则忽略。4.Excel2019中的常用函数及用法（1）常用函数④MIN函数。函数名称：MIN主要功能：求出一组数中的最小值。使用格式：MIN(number1,number2，…)参数说明：number1,number2，…代表需要求最小值的数值或引用单元格（区域），参数不超过30个。特别提醒：如果参数中有文本或逻辑值，则忽略。4.Excel2019中的常用函数及用法（1）常用函数⑤COUNT函数函数名称：COUNT主要功能：统计某个单元格区域中的单元格数目。使用格式：COUNT(value1,value2,…)参数说明：value1,value2,…代表1～30个可以包含或引用各种不同类型数据的参数，但只对数字型数据进行计数。4.Excel2019中的常用函数及用法（1）常用函数⑥ABS函数函数名称：ABS主要功能：求出相应数字的绝对值。使用格式：ABS(number)参数说明：number代表需要求绝对值的数值或引用的单元格。特别提醒：如果number参数不是数值，而是一些字符（如A等），则B2

中返回错误值#VALUE！。此处是指什么？4.Excel2019中的常用函数及用法（2）高级函数①IF函数函数名称：IF主要功能：判断单元格区域中的数值是否满足条件，如果满足返回一个值，如果不满足返回另一个值。使用格式：IF(Logical_test,Value_if_true,Value_if_false)参数说明：Logical_test是判断条件，Value_if_true是满足判断条件时返回的值，Value_if_false是不满足判断条件时返回的值。特别提醒：IF函数的参数还可以是IF函数（或其他函数），这称为函数的嵌套，IF函数可以嵌套七层，可构造复杂的判断条件。4.Excel2019中的常用函数及用法（2）高级函数②SUMIF函数函数名称：SUMIF主要功能：计算符合指定条件的单元格区域内的数值和。使用格式：SUMIF(Range,Criteria,Sum_Range)参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式；Sum_Range代表需要计算的数值所在的单元格区域。特别提醒：文本型数据需要放在英文半角状态下的双引号（"男"、"女"）中。4.Excel2019中的常用函数及用法（2）高级函数③COUNTIF函数函数名称：COUNTIF主要功能：统计某个单元格区域中符合指定条件的单元格数目。使用格式：COUNTIF(Range,Criteria)参数说明：Range代表要统计的单元格区域；Criteria表示指定的条件表达式。特别提醒：允许引用的单元格区域中有空白单元格出现。4.Excel2019中的常用函数及用法（2）高级函数④RANK.EQ函数函数名称：RANK.EQ主要功能：返回一个数字在数字列表中的排位次序。使用格式：RANK.EQ(number,ref,order)参数说明：number代表要求次序的单元格；ref代表与number参数相比较的一组数，即一个数据区域；order代表排名的方式，0代表降序，1代表升序，默认为0。特别提醒：计算排名大多数时候数据区域引用方式为绝对引用。4.Excel2019中的常用函数及用法（2）高级函数⑤FREQUENCY函数函数名称：FREQUENCY主要功能：计算数值在某个区域内的出现频率，然后返回一个垂直数组。使用格式：FREQUENCY(data_array,bins_array)参数说明：Data_array

是一个数组或对一组数值的引用，您要为它计算频率；Bins_array

是一个区间数组或对区间的引用，该区间用于对data_array中的数值进行分组。特别提醒：FREQUENCY函数使用前要提前进行区间的设定；数组函数对话框完成设置后，使用【Ctrl】+【Shift】+【Enter】组合键完成设置。4.Excel2019中的常用函数及用法（2）高级函数⑥MID函数函数名称：MID主要功能：从文本字符串中的指定位置开始返回指定个数的字符。使用格式：MID(text,start_num,num_chars)参数说明：text代表要提取字符的文本字符串；start_num代表要提取的第1个字符在文本字符串text中的位置；num_chars指定希望从文本字符串中提取并返回字符的个数。如“=mis(A3,7,2)“表示从单元格A3的文本字符串的第7个字符开始，提取2个字符。4.Excel2019中的常用函数及用法（2）高级函数⑦DATE函数函数名称：date主要功能：返回特定日期。使用格式：DATE（year,month,day）参数说明：year代表日期中的年份,month代表月份,day代表天数。4.Excel2019中的常用函数及用法（2）高级函数⑧YEAR函数函数名称：year主要功能：返回指定日期中的年份。使用格式：YEAR(serial_number)参数说明：Serial_number为一个日期值，其中包含要查找的年份。4.Excel2019中的常用函数及用法（2）高级函数⑨MONTH函数函数名称：month主要功能：返回指定日期中的月份。使用格式：MONTH(serial_number)参数说明：Serial_number表示一个日期值，其中包含着要查找的月份。4.Excel2019中的常用函数及用法（2）高级函数⑩DAY函数名称：DAY主要功能：返回指定日期中的天数。使用格式：DAY(serial_number)参数说明：Serial_number为要查找的天数日期。4.Excel2019中的常用函数及用法公式中的运算符算术运算符完成基本的数学运算，连接常量、函数、单元格和区域，运算结果为数值型运算符优先级别：括号()>百分比%>乘方^>乘*、除/>加+、减-文本运算符运算符是”&”,用来将一个或多个文本连接成一个组合文本关系运算符用来对两个数值进行比较，结果为逻辑值True或False关系运算符：等于（=）、大于（>）、小于(<)、大于等于(>=)、小于等于(<=)、不等于(<>)引用运算符用于对单元格区域进行合并计算运算符：逗号和冒号可视化数据任务三任务三--任务描述打开文件，建立7个工作表复本，分别命名为“简单排序”、“多条件排序“、“自动筛选”、“高级筛选”、“简单汇总”、“嵌套汇总”、“数据透视表”1.在“简单排序”工作表中按总分的高低排序，再在“多条件排序”工作表中按各门课程成绩的高低排序。2.在“自动筛选”工作表中筛选出英语成绩在80~90分之间的记录，在“高级筛选”工作表中筛选出总分230分以上并且数学或语文85分以上的学生。3.在“简单汇总”工作表中计算各班学生各门课程的平均成绩，在“嵌套汇总”工作表中计算各班学生各门课程的平均成绩并统计人数，在“数据透视表”工作表中统计各班学生数学平均成绩、语文最高分、英语最低分并统计人数。1、数据清单数据清单特点第一行为表头，输入每列的列标题列称为字段，列标题称为字段名，行称为记录列标题名必须唯一且同一列数据的数据类型必须完全相同数据清单中不能有数据完全相同的两行数据清单中不能包括空行和空列将数据清单和其他数据隔开时，在它们之间至少留出一个空白的行或列修改清单时应先将隐藏的行或列显示数据清单中不能有合并单元格、斜线表头等特殊格式2.数据排序数据排序简单排序对一个指定字段重新排列数据清单中的记录方法选定关键字段中任意一个单元格“数据”选项卡—“排序和筛选”组—“升序”或“降序”按钮或“开始”选项卡—“编辑”组—“排序和筛选”—“升序”或“降序”排序方式文本型：按字母顺序排序数值型：按数字大小排序多条件排序在简单排序中，排序字段有相同值时，可指定多个“次要关键字”对值相同的记录继续排序方法选定数据清单中任一个单元格打开“排序”对话框，依次设置主要关键字和各次要关键字的排序方式“数据”选项卡—“排序”按钮“开始”选项卡—“排序和筛选”按钮—“自定义排序”3.数据筛选数据筛选自动筛选对单个字段建立的一种数据筛选方式，多个字段之间是逻辑与的关系。打开方式“开始”选项卡—“编辑”组—“排序和筛选”—“筛选”按钮“数据”选项卡—“排序和筛选”—“筛选”按钮多种筛选方式按颜色筛选：筛选出相同颜色的单元格按数字筛选：筛选出大于、等于、小于某数值的单元格，使用率最高的一种筛选方式按关键字筛选：筛选出包含某个文字或某个数字的数据模糊筛选：使用通配符“？”或“*”进行筛选高级筛选对多个字段进行复杂条件的筛选方法建立筛选条件在独立于数据清单的区域建立筛选条件筛选条件的首行输入筛选条件标题第二行起输入筛选条件，“逻辑与”的关系放在同一行，“逻辑或”的关系放在不同行高级筛选对话框“数据”选项卡—“排序和筛选”组—“高级”方式、选择列表区域、选择条件区域、选择“复制到”设置筛选结果目标位置4.分类汇总分类汇总实际包括分类和汇总两种操作，分类通过排序实现，汇总方式包括求和、求平均、计数、求方差等运算。简单汇总方法按分类字段排序分类汇总对话框设置“数据”选项卡—“分级显示”组—“分类汇总”选择分类字段、汇总方式、选定汇总项嵌套汇总是在上一次汇总的基础上对数据清单进行新的汇总.方法：重复简单汇总操作步骤，注意：分类汇总对话框中需取消勾选“替换当前分类汇总”项.5.数据透视表数据透视表一种可以快速提取并汇总大量数据的交互式表格，将排序、筛选和分类汇总三者有机结合。方法选定数据清单，选择“插入”选项卡—“表格”组—“数据透视表”，打开数据透视表对话框拖动字段到相应位置，选择汇总方式制作数据电子表格任务四任务四--任务描述为所有同学的数学、语文、英语成绩创建一个簇状柱形图。可