Excel中级讲义 下载本文

Excel中级讲义

1.其他函数使用

1.1 SUMIF() 条件求和函数。

格式:=SUMIF(Range,Criteria,Sum_range) 功能:根据指定的条件对选定的单元格数据求和。 参数:

Range—检测判断的单元格区域(条件区域);

Criteria—条件,可以以数字表达式或文本形式定义; Sum_range—求和计算的实际单元格。

如“成绩”表中左则是全部学生的各门课程成绩,要检查出某一个学生(如李林在H3单元格)的总成绩,在i3单元格中输入如下函数:

=SUMIF(B2:B64,H3,E2:E64)

如果要用此函数求出每个同学的总成绩,则要在检测区域B2:B64,计算区域E2:E64使用绝对引用,如下,然后再复制公式。

=SUMIF($B$2:$B$64,H3,$E$2:$E$64) 练习操作文件:成绩.XLS。

1.2 RANK() 排名次函数

格式:=RANK(Number,ref,Order) 功能:确定某一数据在一个数据区域中的排序,可以是升序,也可以是降序。 参数:

Number—确定某数据,或选择某单元格; ref—确定检测排列的区域; Order—排序方式,“0”或空时,为升序,如果为>=1为降序。 注意事项:被检测区域必须为绝对引用。 公式:=RANK(N3,N$3:N$23,0) 练习操作文件:成绩.XLSA。 1.3 条件统计函数 Countif()

格式:=Countif(Range,Criteria)

功能:统计在选定的区域中,符合条件的单元格数。

参数:Range—为参与统计的非空单元格区域;Criteria—为定义的条件,条件可以是文本、数字表达式。数字直接写上,文本需要加引号。

如下列:统计学生总成绩>=250分的人数,公式如下: =COUNTIF(N3:N23,\练习操作文件:成绩.XLS

4.PMT() 求利率(利息+本金)函数

功能:基于固定利率及等额分期付款方式,返回投资或贷款的每期付款额。 格式:=PMT(rate,nper,pv,fv,type) Rate----为各期利率,是一固定值。

Nper----总投资(或贷款)期,即该项投资(或贷款)的付款期总数。

Pv----为现值,即从该项投资(或贷款)开始计算时已经入账的款项,或一系列未来付款当前值的累积和,也称为本金。

Fv----为未来值,或在最后一次付款后希望得到的现金余额,如果省略 fv,则假设其值为零(例如,一笔贷款的未来值即为零)。

Type----数字 0 或 1,用以指定各期的付款时间是在期初还是期末。如果省略 type,则假设其值为零。

说明:PMT 返回的支付款项包括本金和利息,但不包括税款、保留支付或某些与贷款有关的费用。

练习操作文件:函数运算.XLS

2.数组的使用

数组是单个公式的组合使用。即一次要计算多组数据,即可用数组,多组数据必须是矩形的,有相同数目的单元格数据。

方法:选择结果单元格区域→输入“=”→再按组选择输入计算符号→按CTRL+Shift+回车。

练习操作文件:数组使用.XLS。

3.数据处理

以下练习操作文件均为:数据处理.XLS 3.1 排序

排序分为按钮方式和菜单方式。按钮方式为为简单排序,菜单方式为复杂排序。

练习操作文件:数据处理.XLS 1.按钮方式

把光标放在要排序的某一列的任意一个单元格点击(别选择列或区域,否则只排序该列→点击工具栏上的“升序”/“降序”按钮。

2.菜单方式

光标放在数据区域内,数据→排序→对话框→选择“主要关键字” →“次要关键字” →“第三关键字”等。

注意三个关键字之间的关系:第二关键字是在第一关键字数据相同的范围内进行排序,第三关键字是在第二关键字数据相同的范围内排序。

3.2 数据筛选

筛选分自动筛选和高级筛选。

1. 自动筛选

需要不同的数据显示或打印时,对一列数据进行不超过两个条件进行筛选,使用自动筛选是最方便快捷的方法。表中建立的筛选按钮是只显示不打印的。

1)将光标放置于数据区任何处(放数据区外不能进行)→选择数据→筛选→自动筛选。

2)按照要求选择表头名上的按扭→选择筛选条件。

2.高级筛选

超过两个筛选条件的要使用高级筛选。

1)建立筛选条件,如下表。

学号 姓名 成绩1 成绩2 成绩3 成绩4 >=85 >=78 >=70 >=82

2)数据→筛选→高级筛选→选择“方式”下“将筛选结果复制到其他位置”→选择数据区域、条件区域、复制到结果的区域,如果不需要重复记录则选择→确定。

3.3 合并计算

合并计算,是指在一个EXCEL工作表中输入了许多不同类型的数据,重复类型比较多,需要对同一类型进行汇总,可以进行合并计算。如:不同部门的人员工资按部门进行计算等。

1)选择合并后数据存放的区域→选择“数据”→“合并计算”, 出现对话框。

2)选择“函数”为求和(或者均值等)→选择引用位置(包括汉字类别)。 3)如多个合并计算内容,则使用“添加”,再依次选择 → 选择左列(如果需要标题则选择首行)→确定。

3.4 分类汇总

分类汇总,比合并计算更直观,可以显示分类汇总的结果,也可以显示各分类的明细情况。方法如下:

1)对分类汇总的数据首先要进行排序,排序要按照“分类字段”进行。 2)选择“数据”→“分类汇总” 出现对话框。 3)按要求选择“分类字段”为“课程名称”, →“ 汇总方式”为“求和” →选定“汇总项”为“人数”、“课时” →确定

4)将分类汇总的数据拆叠,即并带“-”号的点击一下,成为“+”号,或者点击1、2、3中的2。

3.5 数据透视表

数据透视表是将排序、筛选和分类汇总等功能集于一体,可以方便地进行选择地查询,属于立体型的数据表。

数据透视表非常关键的组成部分是:

页字段:应该是查询最主要的分类,如果以单位为主要查询对象,就将单位作为页字段,如果要以姓名为主要查询内容,则可以将姓名作为主要查询内容。

行字段:隶属于页字段的内容。

列字段:隶属于行字段的内容,显示最具体详细的数据。 见实际操作。

透视表样表1----以课程名称为页,授课班级为行,改名为列格式。 透视表样表2---以授课班为页,姓名为行,课程名称为列格式。 透视表样表3---以姓名为页,授课班级为行,课程名称为列格式。

4.图表制作

练习操作文件:图表制作.XLS

图表是数据的图形化表现形式,使数据表现的更形象、更直观、更便于分析和更富有吸引力和说服力。