信息技术基础教程 课件 第5章 电子表格Excel 2021

信息技术基础教程第5章电子表格Excel2021认识Excel2021122Excel2021的基本操作3工作表的格式化4公式和函数的使用5数据库管理6图表7打印

使用WPS表格处理工资数据85.1认识Excel20215.1.1Excel2021的操作界面5.1.2Excel的基本概念345.1.1Excel2021的操作界面Excel2021窗口与Word2021窗口基本相同，由快速访问工具栏、标题栏、选项卡、功能区面板、文档编辑区、状态栏等部分组成，

55.1.1Excel2021的操作界面65.1.1Excel2021的操作界面Excel2021提供了7个选项卡：开始、插入、页面布局、公式、数据、审阅和视图。75.1.1Excel2021的操作界面2．数据编辑栏（1）名称框：显示、定位单元格或单元格区域。单击“名称框”右侧的下拉按钮将显示用户自定义的单元格名称，快速选中已定义区域。（2）编辑栏：输入、显示和编辑活动单元格的内容。当活动单元格处于编辑状态时，数据编辑栏将出现3个数据编辑按钮。85.1.1Excel2021的操作界面3．Excel工作区

工作区是显示和编辑数据的区域，由工作表和单元格构成。通过工作区底端的工作表标签“Sheet1”、“Sheet2”等可以了解当前工作簿文件的工作表构成情况，利用左侧的“工作表按钮”可实现工作表快速切换。95.1.2Excel的基本概念工作簿Excel生成或处理的文档叫做工作簿，是工作表、图表及宏表的集合，其文件扩展名为“.xlsx”。工作表用于存储和处理数据的一个二维表格，由行、列和单元格组成，纵向为列，横向为行，一个工作表最多包括1048576行和16384列。行号用数字表示，自上而下从1到1048576；列标用字母表示，由左到右分别是A~Z，B~Z，…，AAA~XFD。单元格工作表各行与各列交叉形成的区域就是单元格，是Excel用于数据存储或公式计算的最小单位。一个工作表有1048576×16384个单元格。105.1.2Excel的基本概念单元格区域工作表中一个或多个单元格组成的矩形区域成为单元格区域。区域地址由矩形对角线的两个单元格地址（即左上角和右下角的单元格）组成，中间用“：”号连接。如“A1:K22”以A1为左上角K22为右下角的单元格区域，共242个单元格。地址

[工作簿]工作表!单元格地址[学生成绩表.xlsx]成绩表!A1:K22

5.2Excel2021的基本操作5.2.1工作簿的操作5.2.2工作表的操作5.2.3单元格及单元格区域的操作5.2.4数据的输入5.2.5数据的快速填充和编辑5.2.6实例：创建学生成绩表11125.2.1工作簿的操作Excel工作簿包括创建、打开、保存和关闭等操作，与Word文档类似，不再赘述。135.2.2工作表的操作1．选择工作表2．重命名工作表3．插入工作表4．删除工作表5．移动或复制工作表6．更改工作表标签颜色工作表删除是永久性不可恢复操作，慎重！同一工作簿中移动工作表:鼠标拖曳;按住【Ctrl】键实现“复制”操作。145.2.2工作表的操作7．多窗口操作拆分窗口冻结窗格155.2.3单元格及单元格区域的操作1．选择单元格选择对象操作方法单个单元格单击单元格连续单元格区域①选择左上角单元格，拖动鼠标到右下角单元格后释放鼠标②选择左上角单元格，按住【Shift】键并单击右下角单元格③在名称框中输入单元格区域名称(如B5:D10)按【Enter】键一整行将鼠标指向行号，当鼠标形状为

时单击鼠标左键一整列将鼠标指向列标，当鼠标形状为

时单击鼠标左键多行或多列单击开始行或列，拖动鼠标到目标行或列不连续区域先选中一个对象，按住【Ctr】键不放，逐个选择欲选的其它对象整个工作表①单击工作表左上角的全选按钮

②使用组合键【Ctr+A】5.2.3单元格及单元格区域的操作2．单元格区域的命名——名称框输入3．复制与移动鼠标拖曳：按住【Ctr】键——复制插入剪切的单元格165.2.3单元格及单元格区域的操作特殊复制操作——选择性粘贴175.2.3单元格及单元格区域的操作4．清除单元格有选择删除单元格信息：数据内容、格式、批注等。【Delete】==清除内容185.2.3单元格及单元格区域的操作5．插入与删除19插入n行或n列：先选中n行或n列后，再执行插入操作。5.2.4数据的输入1、文本数据——左对齐包括汉字、英文字母、数字、空格及其它符号的组合。2、数字数据——右对齐由数字0～9、.、+、-、％、（）、$、E等组成的数字组合。日期和时间3、逻辑数据——居中对齐仅有TRUE（真）和FALSE（假）两个值，常由公式产生，用于条件判断。20英文单引号‘：转为“数字字符串”，如电话号码、身份证、学号等。5.2.4数据的输入数字类型输入方法目标值输入值负数负号“-”或将数字放在括号“（）”中-15“-15”或“（15）”科学计数法由字母“e”或“E”、正号“+”和负号“-”组成1.25×1031.25E+03带分数由一个空格和“/”号组成“11/2”千位分隔符由数字和逗号“，”组成123,456,000“123,456,000”金额表示法由数字金额符号(如“$”)组成$123.456“$123.456”百分数由数字和百分号“％”56％“56％”日期输入“yyyy-mm-dd”或“yyyy/mm/dd”2023年09月01日“2023-9-1”或“2023/9/1”时间输入“hh:mm”或“hh:mmAM/PM”15:30“15:30”或“3:30PM”日期时间在日期和时间中间加入一个空格2023-9-115:30“2023-9-115:30”21数字输入方法5.2.5数据的快速填充和编辑1．填充柄—实心黑十字“十”。

位于当前单元格或区域右下角的小黑方块。复制文本数据等差数列填充自定义序列复制公式基于相邻单元格的自动填充225.2.5数据的快速填充和编辑23原始数据结果数据5.2.5数据的快速填充和编辑2、序列填充245.2.5数据的快速填充和编辑3、自定义序列255.2.5数据的快速填充和编辑4．查找与替换（【Ctrl+F】【Ctrl+H】）265.2.5数据的快速填充和编辑5．数据有效性设置录入规则、增加提示信息、出错警告等功能。275.2.5数据的快速填充和编辑5．数据有效性设置录入规则、增加提示信息、出错警告等功能。285.2.6实例：创建学生成绩表295.3工作表的格式化5.3.1格式化单元格5.3.2行高和列宽的调整5.3.3复制格式5.3.4条件格式5.3.5套用表格格式5.3.6实例：修饰学生成绩表305.3.1格式化单元格1、简单格式化——开始选项卡315.3.1格式化单元格2、“单元格格式”对话框325.3.2行高和列宽的调整1．定值行高或列宽2．最适合行高或列宽鼠标指向行或列的分隔线，双击分隔线335.3.2行高和列宽的调整3．行、列的隐藏345.3.3复制格式1．格式刷单击格式刷按钮：单次使用双击格式刷按钮：多次使用2．选择性粘贴在“选择性粘贴”对话框完成相应操作。355.3.4条件格式将符合条件的数据用特定格式显示出来，使用户可以更快、更方便地了解重要的信息。365.3.4条件格式37条件格式设置方法5.3.5套用表格格式38利用Excel提供的“套用表格格式”功能可快速完成工作表修饰。5.3.6实例：修饰学生成绩表391．操作要求（1）成绩表标题：合并居中及修改字体格式。（2）成绩项标题：修改字体及填充效果。（3）为A、B、C、D4列应用最适合的列宽。（4）统一调整E~J列列宽。（5）突出显示优秀和不及格成绩。（6）为成绩表添加边框。5.4公式和函数的使用5.4.1单元格引用5.4.2公式5.4.3实例：公式应用5.4.4函数5.4.5实例：统计学生成绩表405.4.1单元格引用1．相对引用：列标行号=A1+B12．绝对引用：$列标$行号=$A$1+$B$13．混合引用：“列标$行号”或“$列标行号”=A$1+$B1相对引用公式的调整规则为：新行地址＝原行地址+行地址偏移量新列地址＝原列地址+列地址偏移量41在引用单元格地址时，按【F4】键可实现地址转换。5.4.2公式公式以等号“＝”开始，由操作数和运算符组成的式子，它可以对工作表中的数据进行加、减、乘、除、比较和合并等运算。公式的操作数可以是各种数据、单元格地址、函数等。425.4.2公式类型运算符含

义示

例算术运算符

加5

6－减A1－C2

乘2

3.14

A1／除A1／5%百分比80%∧乘幂9∧2比较运算符=等于A1＝B1＞大于A1>B1＜小于A1<B1＞=大于等于A1>=B1＜=小于等于A1<=B1文本运算符&连接两个或多个字符串“广东”&“揭阳”431、Excel公式运算符5.4.2公式2．公式的输入和编辑公式的输入和编辑操作与文本数据类似，唯一不同的是输入公式时要先输入等号“＝”，然后再输入公式内容。445.4.3实例：公式应用1．操作要求使用公式计算“学生成绩表.xlsx”的总分和平均分。455.4.4函数1．函数格式

函数名（参数1，参数2，……）函数名：代表了该函数的功能；参数：数字、文本、逻辑值、单元格引用、名称甚至其他公式或函数等。465.4.4函数2．函数的调用475.4.4函数3．常见错误信息48错误信息出错原因解决方法####单元格中的数值太长，单元格显示不下适当增加列宽#VALUE！公式或函数中使用了错误的参数或运算对象类型确认参数、运算符或引用地址的正确性#DIV/0！公式或函数中使用0作为除数采取方法避免0作为除数#NAME?公式或函数包含Excel不能识别的文本或引用了一个不存在的名称检查使用的文本或名称的正确性#N/A公式或函数中没有可用数值检查引用参数的正确性#REF！公式或函数中引用的单元格无效引用有效的单元格#NUM！公式或函数中引用的某个数字无效确保参加运算的参数为数字型数据#NULL！指定的数据区域为空集检查引用的数据区域5.4.4函数4．常用函数49函

数函

数功

能SUMSUM(n1,n2,…)返回所有有效参数之和AVERAGEAVERAGE(n1,n2,…)返回所有有效参数的算术平均值COUNTCOUNT(n1,n2,…)返回包含数字的单元格以及参数列表中的数字的个数COUNTACOUNTA(n1,n2,…)返回所有参数中非空单元格的个数MAXMAX(n1,n2,…)返回所有有效参数的最大值MINMIN(n1,n2,…)返回所有有效参数的最小值ABSABS(n)返回数n的绝对值MODMOD(n,d)返回数n除以数d的余数SQRTSQRT(n)返回数n的平方根ROUNDROUND(n1,n2)按n2数值对n1进行四舍五入，n2=0，保留整数RANDRAND()函数不带任何参数，返回0至1之间的随机数COUNTIFCOUNTIF(n1,r)返回参数n1中满足条件r的单元格个数，表达式r通常放于英文双引号中RANKRANK(n,r,o)返回数n在区域r中的排位名次，o为排序方式IFIF(n1,n2,n3)根据逻辑条件n1判断返回结果，n1为“真”，返回n2，否则返回n35.4.4函数4．常用函数50函

数函

数功

能FREQUENCYFREQUENCY(n1,n2)根据数据接收区域n2统计数据区域n1的频率分布VLOOKUPVLOOKUP(n,t,c,r)根据n在区域t中进行查询，返回参数c指定列的数值，r确定为精确或模糊匹配PMTPMT(r,n,p,f,t)在固定利率下，贷款的等额分期偿还额。r为各期利率，n为总期数，p为当前存款额，f为目标存款值FVFV(r,n,p,pv,t)在固定利率和等额分期付款方式下，计算投资未来值。r为各期利率，n为总期数，p为各期支出额，pv为现值NOWNOW()返回当前日期时间TODAYTODAY()返回当天日期DAYDAY(d)返回日期d的日号MONTHMONTH(d)返回日期d的月份YEARYEAR(d)返回日期d的年份DATEDATE(y,m,d)返回由年份y、月份m和日号d设置的日期MIDMID(Text,Start_num,Num_chars)中间截取：从文本字符串中指定的起始位置起返回指定长度的字符LEFTLEFT(Text,Num_chars)左截取：从一个文本字符串的第一个字符开始返回指定个数的字符RIGHTRIGHT(Text,Num_chars)右截取：从一个文本字符串的最后一个字符开始返回指定个数的字符5.4.5实例：统计学生成绩表515.4.5实例：统计学生成绩表（1）各科平均分——AVERAGE（2）各科最高分——MAX（3）各科最低分——MIN（4）优秀(>85)——COUNTIF（5）80分段人数（80~89）)——COUNTIF相减（6）70分段人数（70~79）——COUNTIFS（7）60分段人数（60~69）（8）不及格（<60）——COUNTIF（9）各科优秀率——COUNT（10）各科及格率——COUNT（11）名次——RANK525.5数据库管理5.5.1数据清单5.5.2数据排序5.5.3分类汇总5.5.4数据筛选5.5.5数据库统计函数5.5.6数据透视表5.5.7数据统计分析 535.5.1数据清单Excel数据清单由一组连续的行、列构成，满足关系数据库二维表的各种特点，第一行定义了表的结构，从第二行开始对应逐个具体的记录，每一列则相当于数据表中的字段，列的第一行为字段名。545.5.1数据清单1、数据清单创建原则在一个工作表中，最好只建立一个数据清单；在一个数据清单区域内，某个记录的某个字段值可以空白，但不能出现空白行或空白列；若一个工作表内包含除数据清单以外的其它数据，必须以空白行或空白列分隔，以示区分；字段名必须位于数据清单区域的第一行；同一列数据的类型应相同；若要突出显示列标题，必须采用单元格格式，不能使用空行；555.5.2数据排序——简单排序单击升序按钮“

”或降序按钮“

”565.5.2数据排序——复杂排序575.5.3分类汇总1．概述分类汇总是在数据清单中快速汇总统计各项数据的方法。该功能分为两部分操作：（1）排序：对数据按指定列（分类字段）排序，即完成分类操作（可以通过排序操作完成）；（2）汇总统计：对同类别的数据进行汇总统计（包括求和、平均值、计数、最大或最小值等等）；585.5.3分类汇总2．应用实例在工作簿“学生成绩表.xlsx”中，使用分类汇总统计显示不同专业（降序）学生各科平均分。595.5.4数据筛选1．自动筛选（1）简单的自动筛选605.5.4数据筛选1．自动筛选（2）自定义自动筛选615.5.4数据筛选——高级筛选2．高级筛选——复杂的筛选高级筛选的操作步骤如下：建立筛选条件区域。在“数据”选项卡的“排序和筛选”组中，单击“高级筛选”按钮

，打开“高级筛选”对话框。设置筛选参数，单击“确定”按钮即可。625.5.4数据筛选——高级筛选（1）筛选条件区域条件区域中不可出现空行；不同字段的“与”关系：当使用数据库不同字段的多重条件时，必须在同一行的不同列中输入条件；相同字段的“与”关系：当在数据库同一字段中使用多重条件时，必须在条件区域中重复使用该字段名，这样可在同一行的不同列中输入每个条件；“或”关系：在一个条件区域中使用不同字段或同一字段的逻辑OR关系时，必须在不同行中输入条件；635.5.4数据筛选——高级筛选（2）建立筛选条件①简单比较条件：由单个字段名构成的条件。

性别为男

英语不及格645.5.4数据筛选——高级筛选②组合条件：由多个字段名构成的条件。

英语成绩大于90或英语不及格（英语>90or英语<60）数学成绩在80和90之间

（90>数学>=80）655.5.4数据筛选——高级筛选数学成绩在80和90之间的男生数学大于95或英语>95665.5.4数据筛选——高级筛选③计算条件。语文和数学两科成绩之和小于100675.5.4数据筛选——高级筛选（3）应用高级筛选方式：原有区域显示筛选结果、将筛选结果复制到其他位置；列表区域：数据清单区域；条件区域：条件区域地址；复制到：数据输出区域的单元格地址；选择不重复的记录：重复记录在数据输出时仅显示一个；685.5.4数据筛选——高级筛选（4）应用实例695.5.5数据库统计函数数据库统计函数具有统一的格式：函数名（Database，Field，Criteria）各参数含义如下：Database：包含字段的数据库区域地址；Field：待统计的数据列，可以是带引号的字段名或字段名所在的单元格地址，也可以是代表数据库中数据列位置的序号；Criteria：条件区域地址——建立方法与高级筛选一样；705.5.5数据库统计函数常用数据库统计函数如下：DAVERAGE：计算满足给定条件的列表或数据库的列中数值的平均值。DCOUNT：从满足给定条件的数据库记录的字段（列）中，计算数值单元格数目。DCOUNTA：对满足指定条件的数据库中记录字段（列）的非空单元格进行计数。DMAX：返回满足给定条件的数据库记录的字段（列）中数据的最大值。DMIN：返回满足给定条件的数据库记录的字段（列）中数据的最小值。DSUM：求满足给定条件的数据库中记录的字段（列）数据的和。715.5.6数据透视表利用数据透视表，统计不同专业男、女生各科成绩的平均分情况。725.5.7数据统计分析在“Excel选项”对话框的“加载项”列表框中选择“分析工具库”735.5.7数据统计分析统计各分数段人数（包括90分以上、80分段、70分段、60分段和不及格）情况。74输入区域：待分析数据源；接收区域：用于分段的数据点，如0~59、60~69、70~79、80~89、>90；输出区域：数据输出区域左上角的单元格；图表输出：在输出结果的同时绘制图表；5.6图表5.6.1图表概述 5.6.2图表创建实例5.6.3图表编辑755.6.1图表概述1．图表类型76图表类型用途柱形图用于一个或多个数据系列中的各项值的比较折线图按类别显示一段时间内数据的变化趋势饼图在单组中描述部分与整体的关系，没有X轴、Y轴条形图在水平方向上比较不同类别数据，实际上是翻转了的柱形图面积图显示数据在某一段时间内的累计变化XY散点图描述两种相关数据的关系，一般用于科学计算股价图用于描绘股票走势，也可以用于科学计算曲面图用于寻找两组数据间的最佳组合树状图提供数据的分层视图，树分支表示为矩形，每个子分支显示为更小的矩形，用于比较层次结构内的数据相对大小。瀑布图用来表达两个数据点之间数量的演变过程，采用绝对值与相对值结合的方式，适用于表达数个特定数值之间的数量变化关系。雷达图表明数据或数据频率相对于中心点的变化5.6.1图表概述2．图表的组成775.6.1图表概述3．创建图表步骤①选择图表数据源；②分析数据，确定图表类型；③通过“插入”选项卡的“图表”组选择对应的图表类型按钮创建图表；④编辑、格式化图表；785.6.2图表创建实例在工作簿“成绩统计表.xlsx”中按下列要求完成图表制作：（1）以数据清单前10位同学的数学、语文、英语、计算机为数据源，创建成绩簇状柱形图表。（2）利用数据分析工具得到的分数段人数制作计算机成绩分布饼图。795.6.3图表编辑805.6.3图表编辑815.6.3图表编辑825.7打印5.7.1页面设置5.7.2打印835.7.1页面设置84页面页边距5.7.1页面设置85页眉/页脚工作表5.7.2打印865.8使用WPS表格处理工资数据WPS表格和MicrosoftOfficeExcel高度兼容，软件界面和操作方法非常相似，前面介绍的Excel应用技能大部分都可以在WPS表格中使用。875.8使用WPS表格处理工资数据（1）修饰标题行（2）套用表格格式（3）修改行、列格式（4）获取部门编号（5）工龄计算（6）职务津贴计算（7）工龄补贴计算（7）应发工资计算（8）设置数据格式88891．下列操作中，不能在Excel工作表的选定单元格中输入公式的是________。A．使用“开始”选项卡的“自动求和”按钮B．单击“插入”选项卡的“插入函数”按钮C．单击“编辑栏”中的“插入函数”按钮D．单击“公式”选项卡的“自动求和”按钮2．在Excel中，若想突出显示优秀的学生成绩（如用红色加粗字体表示），应使用_______。A．查找 B．条件格式 C．高级筛选 D．定位BB习题903．当向Excel工作表单元格输入公式时，使用单元格地址D$2引用D列2行单元格，该单元格的引用称为________。

A．交叉地址引用 B．混合地址引用 C．相对地址引用 D．绝对地址引用4．Excel广泛应用于________。

A．统计分析、财务管理分析、数据库统计、数据图表制作等各个方面B．工业设计、机械制造、建筑工程C．多媒体制作D．美术设计、图片制作等各个方面5．在Excel中，图表是_______。

A．用户通过“绘图”工具栏的工具绘制的特殊图形B．由数据透视表派生的特殊表格C．由数据清单生成的用于形象表现数据的图形D．一种将表格与图形混排的对象CB