第4章电子表格处理软件Excel2010

第4章电子表格处置软件Excel2021大学计算机14.1Excel2021任务环境4.2Excel2021的根本操作4.3制造图表4.4数据管理和分析4.5打印任务表4.6Excel2021高级运用实例本章主要内容本章重点：任务表的建立、编辑和格式化，图表制造，数据管理和分析电子表格处置软件的功能Office家族成员之一——电子表格软件表格标题数据文字数值自动填充公式函数图表图形对象数据清单排序挑选分类汇总公式自动处置图表直观显示数据管理功能3编辑栏列号名字框行号当前单元格任务表标签任务表区域任务簿655363－255256Sheet1!A1Excel2021的任务窗口4.1Excel2021任务环境Excel文件扩展名为.xlsx4编辑栏显示和选定单元格、图表项、绘图对象等取消操作确定操作“编辑公式〞形状EnterTabEsc或或编辑输入区5任务簿是计算和存储数据的文件，其扩展名.xlsx。新建的第一个任务薄的默许名：book1.xls。每一个任务簿可由一个或多个任务表组成，在默许的情况下是由3个任务表组成的。任务簿与任务表任务簿任务表一个任务表最多有65536行、256列。行号：1~65536列标：A~Z;AA~AZ…任务表由任务表标签区别，如sheet1、sheet2、sheet36任务表中行和列的交叉部分称为单元格，是存放数据的最小单元。单元格的地址表示方式：列标+行号如：A5、D8为了区分不同任务表的单元格，需求在地址前加任务表称号，如Sheet1!B3单元格7⑴输入数据直接输入快速输入数据自动填充数据获取外部数据⑵运用公式和函数计算数据4.2.1创建任务表〔输入＋计算〕“记忆式输入〞“选择列表输入〞4.2Excel2021的根本操作8直接输入数据包括：文字。假设要将一个数字作为文字输入，数字前要加单引号“’〞数值。可采用十进制方式或科学计数方式；假设数字以“+〞或数字“0〞开头，“+〞和“0〞会自动省略；分数的输入应先输入“0〞和空格日期和时间。日期输入可用“/〞或“－〞分隔符，如：2006/10/08，2006-10-08；时间输入用冒号“：〞分隔，如：21：56：15。两者之间用空格分隔前往9快速输入数据“记忆式〞输入“下拉列表〞输入〔仅对文本数据有效〕“从下拉列表中选择〞命令，或者按“Alt+↓〞键前往10自动填充数据分三种情况：复制数据。选中一个单元格，直接拖曳，便会产生一样数据，假设不是一样数据，那么需求在拖曳的同时按Ctrl键填充序列数据。可经过“编辑/填充/序列〞命令，在“序列〞对话框中进展序列有关内容的选择填充用户自定义序列数据自动填充数据前往11选择“数据〞选项卡“获取外部数据〞组中的相应按钮，可导入其他数据库〔如Access、FoxPro、Lotus123等〕产生的文件和文本文件。在数据输入过程中，有能够会输入无效数据，因此通常在输入前利用“数据〞选项卡“数据工具〞组中“数据有效性〞按钮下拉菜单中的“数据有效性〞命令设置数据的有效性规那么。获取外部数据前往12计算数据〔1〕公式Excel的公式由运算符、数值、字符串、变量和函数组成。公式必需以等号“=〞开场Excel中的运算符运算符运算功能优先级()括号1-负号2%算术运算符3^4*与/5+与-6&文本运算符7=、<、>、<=、>=、<>关系运算符813引用运算符含义示例：区域运算符：包括两个引用在内的所有单元格的引用SUM(A1:A2)，联合操作符：对多个引用合并为一个引用SUM（A2，C4，A10）空格交叉操作符：产生对同时隶属于两个引用的单元格区域的引用SUM（C2：E10B4：D6）援用运算符：援用运算符可以将单元格区域合并起来进展计算四类运算符的优先级从高到低依次为：“援用运算符〞、“算术运算符〞、“文本运算符〞、“关系运算符〞，当优先级一样时，自左向右进展计算。14例：有“公司员工工资表.xlsx〞,运用公式计算每位员工的实发工资。15〔2〕函数是预先定义好的公式，它由函数名、括号及括号内的参数组成。其中参数可以是常量、单元格、单元格区域、公式及其他函数。多个参数之间用“，〞分隔。函数输入有两种方法：直接输入法和插入函数法〔更为常用〕16以下图为用函数计算例如17相对援用。当公式在复制或填入到新位置时，公式不变，单元格地址随着位置的不同而变化，它是Excel默许的援用方式，如：B1，A2：C4绝对援用。指公式复制或填入到新位置时，单元格地址坚持不变。设置时只需在行号和列号前加“$〞符号，如$B$1混合援用。指在一个单元格地址中，既有相对援用又有绝对援用，如$B1或B$1。$B1是列不变，行变化；B$1是列变化，行不变。〔3〕公式中单元格的援用18例：设公式在F2单元格，公式：=D2+E2其中D2、E2为相对援用公式：=$D$2+$E$2其中D2、E2为绝对援用公式：=$D2+$E2其中D2、E2为混合援用留意：该公式复制到其他单元格，各种援用的区别就会显现。单元格援用方式举例19“绝对援用〞例如－－工资评价20任务表的选定操作如下表：选取范围

方法单元格鼠标单击或按方向键多个连续单元格从选择区域左上角拖曳至右下角；单击选择区域左上角单元格，按住Shift键，单击选择区域右下角单元格多个不连续单元格按住Ctrl键的同时，用鼠标作单元格选择或区域选择整行或整列单击工作表相应的行号和列号相邻行或列鼠标拖曳行号或列号整个表格单击工作表左上角行列交叉的按钮；选择“编辑/全选”命令；按快捷键Ctrl+A单个工作表单击工作表标签连续多个工作表单击第一个工作表标签，然后按住Shift键，单击所要选择的最后一个工作表标签21单元格数据的编辑单元格内容的去除和删除、挪动和复制单元格、行、列的编辑任务表的编辑任务表的插入、挪动、复制、删除、重命名等4.2.2编辑任务表22格式化数据设置数据格式，对数据进展字符格式化调整任务表的列宽和行高设置对齐方式添加边框和底纹运用条件格式自动套用格式4.2.3格式化任务表23经过“开场〞选项卡“数字〞组中的相应按钮，或单击其右下角的对话框启动器翻开“设置单元格格式〞对话框，在“数字〞标签中完成。〔1〕格式化数据24〔2〕调整任务表的行高和列宽准确调整行高和列宽，经过“开场〞选项卡“单元格〞组“格式〞按钮下拉菜单中的“行高〞和“列宽〞命令执行。〔3〕设置对齐方式“开场〞选项卡“对齐方式〞组中的相应按钮来完成。或单击该组右下角的对话框启动器，翻开“设置单元格格式〞对话框，在“对齐〞标签中进展。25〔4〕添加边框和底纹在右键快捷菜单中选择“设置单元格格式〞命令翻开“设置单元格格式〞对话框，在“边框〞和“填充〞选项卡中进展设置。工资表格式化效果26条件格式可以使数据在满足不同的条件时，显示不同的格式，非常适用。单击“开场〞选项卡“单元格〞组中的“条件格式〞按钮，在下拉菜单中选择对应的命令。〔5〕运用条件格式设置条件格式效果27对任务表设置条件格式：将根本工资大于1000的单元格设置成“浅红填充色深红色文本〞效果，根本工资小于500的单元格设置成蓝色、加双下划线。设置条件格式效果28〔6〕自动套用格式自动套用格式是一组已定义好的格式的组合，包括数字、字体、对齐、边框、颜色、行高和列宽等格式。Excel提供了许多种美丽专业的表格自动套用格式，可以快速实现任务表格式化。经过“开场〞选项卡“款式〞组中的“套用表格格式〞按钮来实现。29创建图表〔只需求选择源数据，然后单击“插入〞选项卡“图表〞组中对应图表类型的下拉按钮，在下拉列表中选择详细的类型即可〕编辑图表调整图表的位置和大小更改图表的类型添加和删除数据修正图表项添加趋势线设置三维视图格式

选中图表，经过“图表工具〞选项卡中的相应功能来实现。该选项卡在选定图表后便会自动出现，它包括3个标签，分别是：“设计〞、“规划〞和“格式〞4.3制造图表30图表组成31图表制造根据任务表中的姓名、根本工资、奖金、实发工资产生一个三维簇状柱形图。32数据清单，又称为数据列表。是Excel任务表中单元格构成的矩形区域，即一张二维表。它与任务表的不同之处在于：数据清单中的每一行称为记录，每一列称为字段，第一行为表头数据清单中不允许有空行或空列；不能有数据完全一样的两行记录；字段名必需独一，每一字段的数据类型必需一样；4.4数据管理和分析4.4.1建立数据清单33作用：以便于比较、查找、分类简单排序〔1个关键字〕单击“数据〞选项卡“排序和挑选〞组中的“升序排序〞按钮、，降序排序〞按钮、或“排序〞按钮或复杂排序〔两个以上〕“排序〞按钮4.4.2数据排序“排序〞对话框对员工工资表排序，首先按“部门〞升序陈列，然后按“根本工资〞降序陈列，根本工资一样时再按“奖金〞降序陈列。341、概念数据挑选就是将数据表中一切不满足条件的记录行暂时隐藏起来，只显示那些满足条件的数据行。2、Excel的数据挑选方式自动挑选(“数据〞选项卡“排序和挑选〞组中的“挑选〞按钮来实现〕〔实现单字段挑选、多字段的逻辑与关系〕高级挑选〔运用“数据〞选项卡“排序和挑选〞组中的“高级〞按钮。〔实现多字段的逻辑或关系〕4.4.3数据挑选例如，挑选出销售部根本工资>=1000,奖金>＝1000的记录例如，挑选出销售部根本工资>=1000,奖金>＝1000或财务部根本工资<1000的记录35数据挑选自动挑选〔如以下图〕〔实现单字段挑选、多字段的逻辑与关系〕高级挑选〔实现多字段的逻辑或关系〕例如，挑选出销售部根本工资>=1000,奖金>＝1000的记录例如，挑选出销售部根本工资>=1000,奖金>＝1000或财务部根本工资<1000的记录36高级挑选在公司员工工资表中挑选出销售部根本工资大于等于1000，奖金大于等于1000的记录。条件区域37分类汇总是对数据清单按某个字段进展分类，将字段值一样的延续记录作为一类，进展求和、平均、计数等汇总运算。分为：简单汇总〔指对数据清单的某个字段仅一致做一种方式的汇总〕嵌套汇总〔指对同一个字段进展多种方式的汇总〕4.4.4分类汇总前提：对分类字段排序38在公司员工工资表中，求各部门根本工资、实发工资和奖金的平均值。步骤：1〕首先对“部门〞排序2〕单击“数据〞选项卡“分级显示〞组“分类汇总〞按钮简单汇总39在上例求各部门根本工资、实发工资和奖金的平均值的根底上再统计各部门人数。步骤：先按上例的方法求平均值，再在平均值汇总的根底上计数嵌套汇总留意：“交换当前分类汇总〞复选框不能选中。40假设要对多个字段进展分类汇总，需求需求利用数据透视表。单击“插入〞选项卡“表格〞组中的“数据透视表〞的下拉按钮，选择“数据透视表〞命令，翻开“创建数据透视表〞对话框，确认下选择要分析的数据的范围以及数据透视表的放置位置。然后单击“确定〞按钮。此时出现“数据透视表字段列表〞窗格，把要分类的字段拖入行标签、列标签位置，使之成为透视表的行、列标题，要汇总的字段拖入∑数值区。4.4.5数据透视表41数据透视表统计各部门各职务的人数。数据透视表字段列表〞窗格数据透视表统计结果42创建好数据透视表之后，可以经过“数据透视表工具〞选项卡修正它，主要内容有：更改数据透视表规划改动汇总方式数据更新43〔1〕数据链接Excel允许同时操作多个任务表或任务簿，经过任务簿的链接，使它们具有一定的联络。修正其中一个任务簿的数据，Excel会经过它们的链接关系，自动修正其他任务表或任务簿中的数据。链接让一个任务簿可以共享其他任务簿中的数据，可以链接单元格、单元格区域、公式、常量或任务表。经过“复制〞和“选择性粘贴〞建立链接。4.4.6数据链接与合并计算44〔2〕合并计算Excel提供了“合并计算〞的功能，可以对多张任务表中的数据同时进展计算汇总。包括求和〔SUM〕，求平均数〔AVERAGE〕，求最大、最小值(MAX、MIN)，计数〔COUNT〕，求规范差〔STDDEV〕等运算。按位置进展合并计算是最常用的方法，它要求参与合并计算的一切任务表数据的对应位置都一样，即各任务表的构造完全一样，这时，就可以把各任务表中对应位置的单元格数据进展合并。经过单击“数据〞选项卡“数据工具〞组中的“合并计算〞按钮，弹出“合并计算〞对话框进展操作。45各地域第二季度电视机销售统计各地域第一季度电视机销售统计“合并计算〞对话框创建了链接源数据的合并结果46〔1〕日期时间函数①YEAR函数功能：前往某日期对应的年份，前往值为1900到9999之间的整数。格式：YEAR(serial_number)。例：YEAR〔1996/8/1〕=1996

②TODAY函数功能：前往当前日期。格式：TODAY()例：TODAY()=2021/10/1③MINUTE函数功能：前往时间值中的分钟，即一个介于0到59之间的整数。格式：MINUTE(serial_number)例：MINUTE〔18：06：55〕=6④HOUR函数功能：前往时间值的小时数。即一个介于0到23之间的整数。格式：HOUR(serial_number)例：Hour〔18：06：55〕=184.4.7常用统计分析函数及其运用47〔2〕逻辑函数①AND〔与〕函数功能：在其参数组中，一切参数逻辑值为TRUE，即前往TRUE。格式：AND(logical1,logical2,...)。

②OR〔或〕函数功能：在其参数组中，任何一个参数逻辑值为TRUE，即前往TRUE。格式：OR(logical1,logical2,...)。阐明：logical1,logical2,...

为需求进展检验的1到30个条件，结果分别为TRUE或FALSE。=AND(B2="男,D2>=40)=OR(AND(MOD(YEAR(C2),4)=0,MOD(YEAR(C2),100)<>0),MOD(YEAR(C2),400)=0)48<大学计算机根底>〔3〕算术与统计函数①MOD函数功能：前往两数相除的余数。格式：MOD(number,divisor)。②MAX函数功能：前往一组值中的最大值。格式：MAX(number1,number2,...)。③RANK函数功能：为指定单元的数据在其所在行或列数据区所处的位置排序。格式：RANK(number,reference,order)。阐明：number是被排序的值，reference是排序的数据区域，order是升序、降序选择，其中order取0值按降序陈列，order取1值按升序陈列。④IF函数功能：执行真假值判别，根据逻辑计算的真假值，前往不同结果。格式：IF(logical_test,value_if_true,value_if_false)。⑤SUMIF函数功能：根据指定条件对假设干单元格求和。格式：SUMIF(range,criteria,sum_range)。⑥COUNTIF函数功能：计算区域中满足给定条件的单元格的个数。格式：COUNTIF(range,criteria)49=RANK(D3,$D$3$:$D$10)D3为第一个学活力试成果，$D$3$:$D$10为一切学活力试成果所占的单元格区域，没有第三个参数择排名按降序陈列，即分数高者名次靠前。绝对援用是为了保证公式复制的结果正确。Rank函数的运用50SUMIF函数和COUNTIF函数的运用“=SUMIF($C$3:$C$12,H3,$D$3:$D$12)〞，表示在区域C3:C12中查找单元格H3中的内容，即在C列查找“男〞所在的单元格，找到后，前往D列同一行的单元格〔由于前往的结果在区域D3:D10〕，最后对一切找到的单元格求和。，在I8至I12单元格依次输入公式“=COUNTIF(D3:D12,">=90")〞、“=COUNTIF(D3:D12,">=80")-COUNTIF(D3:D12,">=90")〞、“=COUNTIF(D3:D12,">=70")-COUNTIF(D3:D12,">=80")〞、“=COUNTIF(D3:D12,">=60")-COUNTIF(D3:D12,">=70")〞、“=COUNTIF(D3:D12,"<60")〞51〔4〕查找函数①HLOOKUP函数功能：在表格或数值数组的首行查找指定的数值，并由此前往表格或数组当前列中指定行处的数值。格式：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)。②VLOOKUP函数VLOOKUP函数的用法与HLOOKUP根本一致，不同在于table_array数据表的数据信息是以行的方式出现，而VLOOKUP的table_array数据表是以列的方式出现。需求留意的是，模糊查找时，table_array的第1列数据必需按升序陈列，否那么找不到正确的结果。52VLOOKUP函数的运用I10单元格：=VLOOKUP(I9,A3:E12,2,0)I11单元格：=VLOOKUP(I9,A3:E12,4,0)I12单元格：VLOOKUP(I9,A3:E12,5,0)“=VLOOKUP(D3,$H$2:$I$6,2,1)53〔5〕文本函数①REPLACE函数功能：运用其它文本字符串并根据所指定的字符数交换某文本字符串中的部分文本。格式：REPLACE(old_text,start_num,num_chars,new_text)。②MID函数功能：前往文本字符串中从指定位置开场的特定数目的字符。格式：MID(text,start_num,num_chars)。③CONCATENATE函数功能：将几个文本字符串合并为一个文本字符串。格式：CONCATENATE(text1,text2,...)。54REPLACE函数的运用=REPLACE〔F2,5,0,8〕晋级方法是在区号〔0731〕后面加上“8〞。55MID、CONCATENATE函数的运用=CONCATENATE(MID(B3,7,4),"年",MID(B3,11,2),"月",MID(B3,13,2),"日")56〔6〕财务函数①PMT函数功能：基于固定利率及等额分期付款方式，前往贷款的每期付款额。格式：PMT(rate,nper,pv,fv,type)。②IPMT函数功能：基于固定利率及等额分期付款方式，前往给定期数内对投资的利息归还额。格式：IPMT(rate,per,nper,pv,fv,type)。57PMT、IPMT函数的运用==PMT(B2/12,B3*12,B4)利用商业贷款买房，计算贷款月还款额与第1个月的还款利息。假定贷款10万元，年利率为7.05%，贷款10年，每月末等额还款。=IPMT(B2/12,1,B3*12,B4)584.5打印任务表Excel2021任务表的打印根据打印内容有3种情况：选定区域(最常用〕活动任务表整个任务簿经过“文件〞按钮下拉菜单