第章电子表格处理软件课件

第4章电子表格处理软件Excel20101大学计算机《大学计算机基础》

4.1Excel2010工作环境

4.2Excel2010的基本操作

4.3制作图表

4.4数据管理和分析

4.5打印工作表

4.6Excel2010高级应用实例本章主要内容本章重点：工作表的建立、编辑和格式化，图表制作，数据管理和分析《大学计算机基础》

电子表格处理软件的功能Office家族成员之一——电子表格软件表格标题数据文字数值自动填充公式函数图表图形对象数据清单排序筛选分类汇总公式自动处理图表直观显示数据管理功能3《大学计算机基础》编辑栏列号名字框行号当前单元格工作表标签工作表区域工作簿655363－255256Sheet1!A1Excel2010的工作窗口4.1Excel2010工作环境

Excel文件扩展名为.xlsx4《大学计算机基础》编辑栏显示和选定单元格、图表项、绘图对象等取消操作确定操作“编辑公式”状态EnterTabEsc或或编辑输入区5《大学计算机基础》

工作簿是计算和存储数据的文件，其扩展名.xlsx。新建的第一个工作薄的默认名：book1.xls。每一个工作簿可由一个或多个工作表组成，在默认的情况下是由3个工作表组成的。

工作簿与工作表工作簿工作表

一个工作表最多有1048576行、16384列。

行号：1~1048576

列标：A~Z;AA~AZ…

工作表由工作表标签区别，如sheet1、sheet2、sheet36《大学计算机基础》

工作表中行和列的交叉部分称为单元格，是存放数据的最小单元。

单元格的地址表示形式：

列标+行号如：A5、D8

为了区分不同工作表的单元格，需要在地址前加工作表名称，如Sheet1!B3单元格7《大学计算机基础》⑴输入数据

直接输入

快速输入数据

自动填充数据

获取外部数据⑵使用公式和函数计算数据4.2.1创建工作表（输入＋计算）

“记忆式输入”“选择列表输入”4.2Excel2010的基本操作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）调整工作表的行高和列宽精确调整行高和列宽，通过“开始”选项卡“单元格”组“格式”按钮下拉菜单中的“行高”和“列宽”命令执行。25（3）设置对齐方式“开始”选项卡“对齐方式”组中的相应按钮来完成。或单击该组右下角的对话框启动器

，打开“设置单元格格式”对话框，在“对齐”标签中进行。《大学计算机基础》（4）添加边框和底纹在右键快捷菜单中选择“设置单元格格式”命令打开“设置单元格格式”对话框，在“边框”和“填充”选项卡中进行设置。26工资表格式化效果《大学计算机基础》27

条件格式可以使数据在满足不同的条件时，显示不同的格式，非常实用。单击“开始”选项卡“单元格”组中的“条件格式”按钮

，在下拉菜单中选择对应的命令。（5）使用条件格式设置条件格式效果《大学计算机基础》28

对工作表设置条件格式：将基本工资大于1000的单元格设置成“浅红填充色深红色文本”效果，基本工资小于500的单元格设置成蓝色、加双下划线。设置条件格式效果《大学计算机基础》（6）自动套用格式

自动套用格式是一组已定义好的格式的组合，包括数字、字体、对齐、边框、颜色、行高和列宽等格式。Excel提供了许多种漂亮专业的表格自动套用格式，可以快速实现工作表格式化。通过“开始”选项卡“样式”组中的“套用表格格式”按钮

来实现。29《大学计算机基础》创建图表（只需要选择源数据，然后单击“插入”选项卡“图表”组中对应图表类型的下拉按钮，在下拉列表中选择具体的类型即可）编辑图表

调整图表的位置和大小更改图表的类型添加和删除数据修改图表项添加趋势线设置三维视图格式

选中图表，通过“图表工具”选项卡中的相应功能来实现。该选项卡在选定图表后便会自动出现，它包括3个标签，分别是：“设计”、“布局”和“格式”4.3制作图表30《大学计算机基础》图表组成31《大学计算机基础》图表制作根据工作表中的姓名、基本工资、奖金、实发工资产生一个三维簇状柱形图。32《大学计算机基础》数据清单，又称为数据列表。是Excel工作表中单元格构成的矩形区域，即一张二维表。它与工作表的不同之处在于：数据清单中的每一行称为记录，每一列称为字段，第一行为表头数据清单中不允许有空行或空列；不能有数据完全相同的两行记录；字段名必须唯一，每一字段的数据类型必须相同；4.4数据管理和分析4.4.1建立数据清单33《大学计算机基础》

作用：以便于比较、查找、分类

简单排序（1个关键字）

单击“数据”选项卡“排序和筛选”组中的“升序排序”按钮

、，降序排序”按钮

、或“排序”按钮

或复杂排序（两个以上）

“排序”按钮

4.4.2数据排序“排序”对话框对员工工资表排序，首先按“部门”升序排列，然后按“基本工资”降序排列，基本工资相同时再按“奖金”降序排列。34《大学计算机基础》1、概念数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来，只显示那些满足条件的数据行。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()=2013/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。可编辑48=AND(B2="男,D2>=40)=OR(AND(MOD(YEAR(C2),4)=0,MOD(YEAR(C2),100)<>0),MOD(YEAR(C2),400)=0)（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《大学计算机基础》50=RANK(D3,$D$3$:$D$10)D3为第一个学生机试成绩，$D$3$:$D$10为所有学生机试成绩所占的单元格区域，没有第三个参数择排名按降序排列，即分数高者名次靠前。绝对引用是为了保证公式复制的结果正确。Rank函数的应用《大学计算机基础》51

SUMIF函数和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")”《大学计算机基础》（4）查找函数①HLOOKUP函数功能：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。格式：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)。②VLOOKUP函数VLOOKUP函数的用法与HLOOKUP基本一致，不同在于table_array数据表的数据信息是以行的形式出现，而VLOOKUP的table_array数据表是以列的形式出现。需要注意的是，模糊查找时，table_array的第1列数据必须按升序排列，否则找不到正确的结果。52《大学计算机基础》53VLOOKUP函数的使用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)《大学计算机基础》（5）文本函数①REPLACE函数功能：使用其它文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。格式：REPLACE(old_text,start_num,num_chars,new_text)。②MID函数功能：返回文本字符串中从指定位置开始的特定数目的字符。格式：MID(text,start_num,num_chars)。③CONCATENATE函数功能：将几个文本字符串合并为一个文本字符串。格式：CONCATENATE(text1,text2,...)。54《大学计算机基础》55REPLACE函数的使用=REPLACE（F2,5,0,8）升级方法是在区号（0731）后面加上“8”。《大学计算机基础》56MID、CONCATENATE函数的使用=CONCATENATE(MID(B3,7,4),"年",MID(B3,11,2),"月",MID(B3,13,2),"日")《大学计算机基础》（6）财务函数①PMT函数功能：基于固定利率及等额分期付款方式，返回贷款的每期付款额。格式：PMT(rate,nper,pv,fv,type)。②IPMT函数功能：基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿还额。格式：IPMT(rate,per,nper,pv,fv,type)。57《大学计算机基础》58PMT、IPMT函数的使用==PMT(B2/12,B3*12,B4)利用商业贷款买房，计算贷款月还款额与第1个月的还款利息。假定贷款10万元，年利率为7.05%，贷款10年，每月末等额还款。=IPMT(B2/12,1,B3*12,B4)《大学计算机基础》4.5打印工作表Excel2010工作表的打印根据打印内容有3种情况：选定区域(最常用）活动工作表整个工作簿通过“文件”按钮下拉菜单中的“打印”命令在“打印”标签“设置”区中进行相应设置。59《大学计算机基础》4.6Excel2010高级应用实例60见[例4.26]，[例4.27]。《