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

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