课件与题目空白2excel应用技术

第二章Excel应用技术§2.1公式

Excel中的公式，类似于数学中的表达式，它是一个能够进行运算的计算式。注意：

公式必须以“=”开头，否则系统将视其为文字，从而失去计算的功能。

当公式中含有单元格地址时，由该单元格中的数据参与运算。一、Excel运算符

公式由运算符和参与运算的操作数组成。▲运算符用于完成各种运算的符号。Excel的运算符包括4种：

算术运算符

比较运算符

文本运算符

引用运算符▲操作数进行计算的元素。可以是常数、单元格地址、函数等。▲算术运算符用于完成基本的数学运算。▲比较运算符用于比较两个值，其结果是一个逻辑值，即TRUE或FALSE。▲文本运算符（&）连接两个字符串。▲引用运算符将单元格区域合并起来进行计算。运算符名称运算符用途区域运算符:对区域内的所有单元格进行引用联合运算符,将多个引用合并为一个引用

如果公式中含有多个运算符时，它们运算的先后顺序如下表（教材P38表2–4）所列。顺序运算符顺序运算符1:6^2（空格）7*和/3,8+和-4-（负号）9&5%二、单元格地址

Excel中，对单元格的引用有两种方法：

▲A1引用（单元格地址的引用）

▲RxCx引用（相对当前单元格位置的引用）引用方式的切换：菜单命令【工具】/【选项】/【常规】引用含义R[-2]C对同一列、上面两行的单元格的相对引用R[2]C[2]对下面两行、右面两列的单元格的相对引用R2C2对工作表的第二行、第二列的单元格的绝对引用R[-1]对活动单元格整个上面一行单元格区域的相对引用R对当前行的绝对引用

R表示行，C表示列。

数字是对指定行或列的引用；无数字则是对当前行或列的引用。

方括号内的数字是相对引用，否则为绝对引用。

引用方向向下或向右数字为正，反之为负。

单元格地址由列序号和行序号组成。单元格地址有3种不同的形式：▲相对引用▲绝对引用▲混合引用1、相对引用公式中相对引用的单元格地址是基于单元格的相对位置的。

当公式所在的单元格位置发生了改变（如插入或删除了行或列），单元格的相对引用也将随之发生改变；

当公式复制后，相对引用将自动作出调整。相对引用是直接用列号和行号的组合来表示单元格地址，如：A1、B3。

默认情况下，公式使用相对引用。在D1中输入：=A1+B1，显示30。那么当将D1公式复制到D2，则D2中公式及显示值是什么？D2中的公式是：=A2+B2，显示的值是90ABCDE11020302405060370809049030在D1中输入：=SUM(A1:B2)，显示120。那么当将D1公式复制到E2，则E2中公式及显示值是什么？E2中的公式是：=SUM(B2:C3)显示的值是280ABCDE1102030240506037080904280120在C1中输入：=A1+B1，显示30。那么当在A列后插入一个新列，原来的C1成为D1，其中的公式将是什么？显示值是多少？ABCDE110202405037080ABCDE11020?2405037080D1中的公式是：=A1+C1，显示的值是303030在C1中输入：=SUM(A1:B2)，显示120。那么当在A列后插入一个新列，原来的C1成为D1，其中的公式将是什么？显示值是多少？ABCDE110202405037080ABCDE11020?2405037080D1中的公式是：=SUM(A1:C2)，显示的值是120120120在C1中输入：=SUM(A1:B2)，显示120。那么当在B列后插入一个新列，原来的C1成为D1，其中的公式将是什么？显示值是多少？ABCDE110202405037080ABCDE11020?2405037080D1中的公式是：=SUM(A1:B2)，显示值仍是120120120在C1中输入：=SUM(A1:B1)，显示30。如果将C1的公式复制到C2，则C2中的公式将是什么？显示值是多少？C2中公式是：=SUM(A2:B2)，显示值是90如果将C1的公式复制到B3，则B3中将显示什么？B3中显示：#REF!（单元格引用无效）ABCDE110202405033090#REF!2、绝对引用公式中绝对引用的单元格地址是指定的单元格位置。

当公式所在的单元格位置发生了改变，单元格的绝对引用保持不变；

当公式复制后，绝对引用不作出调整。绝对引用是在列号和行号前加“$”符号，如：$A$1、$B$3。3、混合引用所谓混合引用是在引用单元格地址时，行和列中的一个是相对引用，而另一个是绝对引用。如：$A1、B$3。

当公式所在的单元格位置发生了改变，相对引用部分将随之发生改变，而绝对引用部分将保持不变；

当公式复制后，相对引用部分将自动作出调整，绝对引用部分不作任何调整。

如果选中公式中的单元格地址，再按F4功能键，即可将该地址在这3种引用中进行切换。ABCDE110203024050603708090在D1中输入：=$A$1+B2，那么当将D1的公式复制到E2，则E2中显示的是什么？E2中的公式是：=$A$1+C3，显示的值是100在D1中输入：=$A1+$B2，那么当将D1的公式复制到E2，则E2中显示的是什么？E2中的公式是：=$A2+$B3，显示的值是120在D2中输入：$A$1+$A$2，那么当将D2中的内容复制到E3，则E3中显示的值是什么？E3中显示是：$A$1+$A$2100$A$1+$A$26030120

当需要引用其它工作表或其它工作簿中的数据时，就要使用三维引用。其基本格式为：

[文件名

]工作表名

!单元格地址

引用其它工作簿时，其文件名必须加方括号；源数据在同一工作簿内则可以省略。

文件名后是工作表名称，同时引用多个工作表相同位置的数据时，用冒号（:）标识工作表的范围。

工作表名称后必须加感叹号（!）如：=SUM(sheet1:sheet3!A1)

=[book1]sheet2!A1+sheet3!B2【例】在B2单元格中输入一个含混合地址的公式，并通过公式复制可以快速产生一个九九乘法表。在B2单元格中应该输入一个什么公式？（[第2章公式与函数实例]九九乘法表）B2单元格中的公式为：=$A2*B$1三、数组公式

通常的公式只返回一个结果。数组公式可以同时进行多个计算并返回一个或多个结果。

数组公式所引用的参数是数组参数，包括区域数组(单元格区域)和常量数组（一系列常数）。每个数组参数必须有相同数量的行和列。

数组公式返回的结果可以是一维或二维的。

数组公式输入时按普通公式输入，输入完毕后需要按Ctrl+Shift+Enter组合键，系统将自动给公式加上花括号（{}）。

人工在公式外加上花括号是无效的。【例】在B7单元格中求销售合计金额。

[第2章公式与函数实例]数组公式计算在B7单元格中输入：=SUM(B2:B5*C2:C5)注意：

结束输入时必须按Ctrl+Shift+Enter组合键。1、数组常量普通公式中的数值或单元格引用称为常量。数组公式中的数值数组和数组引用称为数组常量。数组常量必须按特定的格式输入。

数组常量可以包含数字、文本、逻辑值（TRUE、FLASE或错误值#N/A）。

数组常量中可以包含不同类型的数值。

数组常量中的数字可以使用整数、小数或科学记数格式；文本必须包含在西文双引号内。

数组常量不包含单元格的引用、长度不等的行或列、公式或特殊字符（美元符号、括号或百分号）

数组常量置于花括号内。不同的行用分号（;）隔开不同的列用逗号（,）隔开。【例】数组常量：{1,2,

"ABC"

,TRUE;10,20,

"xyz"

,FALSE}相当于对一个2行4列的区域的引用，此区域为：12ABCTRUE1020xyzFALSE=SUM({1,2,3}*{3,2,1})=SUM({1;2;3}*{3,2,1})2、数组公式举例（1）利用数组公式进行分类统计【例】对不同商品分别计算其销售金额。[第2章公式与函数实例]数组公式求和在C12单元格中输入公式：

=SUM(IF(A2:A10="商品1",B2:B10*C2:C10,0))P41公式分析：C12单元格中的公式：

=SUM(IF(A2:A10="商品1",B2:B10*C2:C10,0))

此公式的功能是：将A2:A10区域中数据为“商品1”的单元格的相应B列与C列相乘后求和。

条件判断使用IF函数；求和时使用SUM函数。

A2:A10区域中数据的判断使用IF函数，符合条件的则相应的B列和C列相乘，否则按零参加求和。

C13单元格按上述方法处理。

C14是对C12和C13求和，即：=C12+C13。IF函数语法格式：

IF(logical,value1,value2)功能：根据logical的值，返回value1或value2的值。说明：

如果logical的值为TRUE，则返回value1的值；如果value1为空（logical后的逗号必须存在），则返回0（零）；如果要返回TRUE，则必须要将value1设置为：TRUE。

如果logical的值为FALSE，则返回value2的值；如果value2为空（value1后的逗号存在），则返回0（零）；如果value2被忽略（即：value1后的逗号不存在），则返回FALSE。P68（2）利用数组公式排名次【例】根据平均成绩排名次。[第2章公式与函数实例]利用数组公式统计名次例题分析：

在I3至I9单元格中输入了数组公式：

=MATCH(H3:H9,LARGE(H3:H9,ROW(1:7)),-1)

公式中使用了3个函数：

用ROW函数产生数值1至7

使用LARGE函数进行数据的排序

用MATCH函数来查找指定数值的位置P41▲ROW函数语法格式：

ROW(reference)功能：返回引用的行号。说明：

参数reference为需要得到其行号的单元格或单元格区域。

如果省略参数reference，则是对函数ROW所在行的引用。P70【例】

工作表中的数据如下图所示：ABC1731925320365534896559873620847198988498

如果在C1中输入：

=ROW(A5)则C1中显示的值为：

如果在C3中输入：

=ROW()则C3中显示的值为：5353▲LARGE函数语法格式：

LARGE(array,k)功能：返回数据集array中第k个最大值。说明：

参数array为需要从中选择第k个最大值的数组或单元格区域。

参数k为返回值在区域中按从大到小排序的排序次序。P53

如果数组或区域为空（即无数据），则函数返回错误值#NUM!。

如果k小于等于0或k大于区域中的数据个数，则函数返回错误值#NUM!（公式或函数中某些数字有问题）。

若区域中数据个数为n，则LARGE(array,1)返回最大的值，LARGE(array,n)返回最小的值。【例】

工作表中的数据如下图所示：ABC1738919253988436520364

如果在B4中输入：=LARGE(A1:C3,1)则B4中显示的值为：

如果在C4中输入：=LARGE(A1:C3,12)则C4中显示的值为：98#NUM!98#NUM!▲SMALL函数语法格式：

SMALL(array,k)功能：返回数据集array中第k个最小值。说明：

参数array为需要从中选择第k个最小值的数组或单元格区域。

参数k为返回值在区域中按从小到大排序的排序次序。P53【例】

工作表中的数据如下图所示：ABC1738919253988436520364

如果在B4中输入：=SMALL(A1:C3,1)则B4中显示的值为：

如果在C4中输入：=SMALL(A1:C3,12)则C4中显示的值为：19#NUM!19#NUM!▲MATCH函数语法格式：

MATCH(value,array,type)功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置。说明：

参数value为需要在数据表中查找的数值。可以是数字、文本或逻辑值，或者是对它们所在单元格的引用。

参数array为所要查找的数据可能所在的数组或连续的单元格区域（必须是一维数组或同一行或同一列的单元格区域）。P61

参数type为查找的方式，为数字1、0或-1。

如果为1，则查找小于或等于value的最大值，数组array必须按升序排列；

如果为0，则查找等于value的第一个数值，数组array可以按任意顺序排列；

如果为-1，则查找大于或等于value的最小值，数组array必须按降序排列；

参数type缺省时，系统默认为1。

函数返回的是查找值（value）在区域（array）中的位置，而不是查找值的本身。

函数在查找时不区分字母的大小写。

如果查找的是文本数据，且type是0，则value可以包含通配符（*或?

）。

如果查找不成功，则返回错误值#N/A（公式或函数中没有可用的数值）。【例】

工作表中的数据如下图所示：ABC1731925320365534896559873620847198988498

如果在C1中输入：=MATCH(98,A1:A8,0)则C1中显示的值为：

如果在C3中输入：=MATCH(70,B1:B8,1)则C3中显示的值为：5454公式分析：例题中，从I3单元格至I9单元格输入了以下公式：=MARCH(H3:H9,LARGE(H3:H9,ROW(1:7)),-1)计算时的步骤为：①计算公式ROW(1:7)，其功能是在I3:I9区域内产生1至7的数。②计算公式LARGE(H3:H9,ROW(1:7))其功能是将H3:H9区域内的数据从大到小排列，并显示在I3:I9区域内。③计算公式

MARCH(H3:H9,LARGE(H3:H9,ROW(1:7)),-1)其功能是查找H3:H9区域内的数（即平均分）在区域I3:I9内的位置（即排在第几位，也是其名次）。▲RANK函数语法格式：

RANK(number,ref,order)功能：返回number在ref中的排位。本题也可以使用rank函数排序说明：

number是要找到排位的数字；ref为数字列表或对数字列表的引用。

ref中的非数值型数据将被忽略。

order为一数字，指明排位的方式。若order为0或省略，对数字的排位是基于ref的按照降序排列的列表；若order不为0，则数字的排位是基于ref的按照升序排列的列表。【例】

工作表中的数据如下图所示：ABC178269365489553694758880

如果在C1中输入：=RANK(78,A1:A8,1)则C1中显示的值为：

如果在C3中输入：=RANK(78,A1:A8)则C3中显示的值为：5454注意：函数RANK对重复数的排位相同，但重复数的存在将影响后续数值的排位。【例】

工作表中的数据如下图所示：ABC178289365489553694

如果在C1中输入：=RANK(89,A1:A6)则C1中显示的值为：

如果在C3中输入：=RANK(78,A1:A6)则C3中显示的值为：2424四、常见错误信息▲####错误原因：输入到单元格中的数据长度超过了列的宽度，单元格中无法容纳。▲#div/0错误原因：公式中的分母为零。▲#N/A错误原因：函数或公式中没有可用的数值。▲#

NAME?错误原因：公式中使用了Excel不能识别的文本。▲#NULL!错误原因：试图为两个并不相交的区域指定交叉点。▲#NUM!错误原因：公式或函数中的某些数字有问题。▲#

REF!错误原因：单元格引用无效。▲#VALUE!错误原因：公式中的参数或运算对象的数据类型错误。§2.2排序与筛选一、排序

Excel中，按递增方式排序的数据类型及其数据的顺序为：

数字从小数到大数。

文本（包含纯数字文本、符号、字母、汉字）空格数字符号字母汉字

逻辑值

FALSETRUE

空白单元格（无论升序还是降序，总在最后）▲字母排序时默认按字母顺序，且不分大小写。▲汉字可按拼音（默认）或笔划排序。1、利用工具按钮排序（单字段排序）在对数据清单进行排序时，只需将当前单元格置于要排序的数据列，使用【排序】工具按钮（升序按钮或降序按钮）进行排序。如果选中整个数据列，则只对选中的数据进行排序，这样将引起数据清单中数据的对应错误。2、利用菜单命令排序（可实现多字段排序）★操作步骤：

①将当前单元格置于数据清单中的任意位置

②菜单命令：【数据】/【排序】

主要、次要、第三关键字及升（降）序

有（无）标题行

选项

区分大小写

按行（列）排序

按字母（笔划）排序

自定义排序3、自定义排序

需要首先建立一个自定义序列。思考：

自定义序列如何建立？例：[第2章公式与函数实例]自定义排序二、筛选

筛选是快速从大量数据中获取所需要的信息。1、数据检索(不常用)★操作步骤：

①菜单命令：【数据】/【记录单】

②单击【条件】按钮，输入检索的条件后回车

操作前当前单元格必须置于数据清单的区域内。

输入的检索条件中可以使用比较运算符号。

单击对话框中的按钮，可以进行相应的操作2、自动筛选

数据检索是从数据清单中逐条查看检索出的记录。自动筛选是批量查看符合条件的记录。★操作步骤：

①菜单命令：【数据】/【筛选】/【自动筛选】

②单击列标记下的下拉按钮，设置筛选条件

自定义筛选的对话框中可以使用统配符，且最多设置两个条件。这两个条件之间既可以是“与”的关系，也可以是“或”的关系。

各列条件之间是“与”的关系。

不满足条件的记录将被隐藏。

菜单命令：【数据】/【筛选】/【自动筛选】可以取消自动筛选状态。3、高级筛选

高级筛选包括两个部分：数据清单和条件区域。条件区域是一组包含筛选条件的单元格区域，其所在的位置应该与数据区域位置无关。

条件区域与数据清单之间至少要有一个空行或列

条件区域的首行是数据清单的字段名

条件区域的其它行即为条件行

同一条件行上的各条件之间是“与”的关系；不同条件行上的各条件之间是“或”的关系注意：

教材P46上所述的单列多条件筛选方法仅适用于多条件之间的“或”关系。

多列单条件是指的一行多列，多条件之间是“与”的关系。

多行多条件★操作步骤：

①建立条件区域（包括字段行和条件行）

②菜单命令：【数据】/【筛选】/【高级筛选】

③作相应设置：

显示方式

在原有区域显示

复制到其它位置

列表区域

条件区域

复制到

选择不重复的记录▲Excel规定：筛选结果只能显示在数据清单所在的工作表。▲选择“在原区域显示”，则筛选后原区域只显示符合筛选条件的记录；可由菜单命令：【数据】/【筛选】/【全部显示】来恢复所有数据。例：[第2章公式与函数实例]成绩表高级筛选§2.4图表一、基本图表的制作

图表用于整理、分析和总结数据的。不同的图表，对数据的展示效果是不同的。1、图表的种类

▲柱形、折线、散点图反映数据变化趋势及对比。▲曲面图适合进行多组数据的对比与变化趋势分析。▲饼图、圆环图、雷达图用于观察数据之间的比例。▲面积图反映一组数据在全部数据中所占的比例。▲气泡图、股价图气泡图可以看成是散点图的扩展，它用气泡大小反应数据点的另一个属性股价图是反应类似股市行情的图表。2、图表创建【例】创建一个图表，以三维簇状柱形图显示各饮料的销售量。★操作步骤：①选择需要用图表表示的数据区域②菜单命令：【插入】/【图表】，启动图表向导③按图表向导的提示进行操作

选择图表类型

图表数据源选择

数据区域选择

系列选择

所谓系列是在图表中绘制的相关数据点，这些数据来自于数据表的行或列（按行时，将第一列的各行作为数据系列；按列时，将第一行的各列作为数据系列）。

图表中的每个数据系列具有唯一的颜色或图案并且在图表的图例中表示。

可以在图表中绘制一个或多个数据系列。饼图只有一个数据系列。

图表选项设置

标题

图例

数据标志

数据标志包括：系列名称、类别名称、值等。

图表中的条形、面积、圆点、扇面或其他符号，代表源于数据表单元格的单个数据点或值。

图表中的相关数据标志构成了数据系列。

图表位置设置（作为新工作表或对象插入）

在图表制作完成后，可以鼠标右击图表，并在快捷菜单中选择有关命令，以便打开相应的对话框对图表的各部分选项进行修改或添加。3、图表修饰

在图表制作完成后，可以鼠标右击图表，并在快捷菜单中选择有关命令，以便打开相应的对话框对图表的各部分选项进行修改或添加。

在Excel的图表制作中，应用更为广泛的是动态图表，即能根据控件的选择，动态地改变显示值的图表。由于图表的数据区域是指定不变的，所以为了使图表达到动态的效果，必须通过窗体控件（通常为组合框）来动态选择显示数据的区域。二、常用窗体控件窗体控件是Excel中允许用户利用图形界面的工具来显示或输出数据。在使用窗体控件前，先要将“窗体”工具栏打开，工具栏中灰色的控件表示目前不能使用。注意：千万不要混淆“窗体”工具栏与“控件”工具栏P311、分组框分组框控件是个容器类控件，它通常与选项按钮或复选框组合在一起使用。▲分组框的格式设置

标题（编辑文字）

大小（高度和宽度）

保护（工作表保护时才生效）

属性（位置属性）2、选项按钮

选项按钮可以作为分组框的选项之一。同一分组框中的多个选项按钮，只有其中的一个可以被选定。▲选项按钮的格式设置

标题（编辑文字）

大小（高度和宽度）

保护（工作表保护时才生效）

属性（位置属性）

控制

值（初始值，可设置未选择或已选择）

单元格链接（用于显示控件值的单元格）

如果选项按钮作为一个独立的控件存在于工作表中时，则该控件所链接的单元格的值为1时表示按钮已选中，其它值表示按钮未选中。

控件的状态值与所链接的单元格的值之间是双向传递的。3、复选框

复选框用于选定某个选项。工作表或同一分组框中的多个复选框可以同时有多个复选框被选定。▲复选框的格式设置

标题（编辑文字）

颜色与线条（填充色及线条样式）

大小（高度和宽度）

保护（工作表保护时才生效）

属性（位置属性）

控制

单元格链接（显示复选框状态值的单元格）

值

未选择（FALSE、0或空）

已选择选择（TRUE或非零）

混合型（#N/A）

如果链接的单元格为空，则Excel将复选框的状态解释为False。4、列表框

列表框用于显示项目列表，即显示指定区域中的数据，并从中选择其中之一。▲列表框的格式设置

控制

数据源区域（列表中显示的数据）

单元格链接（返回列表框中选定项的编号）

3维阴影

选定类型（指定列表中选定项目的方式）

单选 只能选择列表中的一项

复选 允许选择列表中的多项

扩展 通过同时按Ctrl或Shift键，可选择列表中的多项

如果选定类型选择“复选”或“扩展”，则“单元格链接”框中指定的单元格将被忽略。5、组合框

组合框的功能与列表框相同，区别只在于组合框平常状态下是折叠的，即只显示其中的一项列表项，而列表框将显示所有的列表项。▲组合框的格式设置

控制

数据源区域（列表中显示的数据）

单元格链接（返回组合框中选定项的编号）

下拉显示项数

3维阴影6、滚动条

滚动条的功能是用鼠标拖动滚动条的滚动块或单击滚动箭头来改变控件的值。▲滚动条的格式设置

控制

当前值（滚动块在滚动条中的相应位置）

最小值（滚动块处于最上端时对应的值）

最大值

步长（单击箭头时滚动块移动的距离）

页步长（单击滚动条时滚动块移动的距离）

单元格链接（返回滚动块的位置值）7、微调按钮

微调按钮用于增大或减小数值，其功能与滚动条相似。▲微调按钮的格式设置

控制

当前值（控件当前的数值）

最小值（控件可取的最小的值）

最大值

步长（单击控件箭头时增大或减小的值）

单元格链接（返回控件当前的值）三、动态图表制作（控件应用举例）

（详见教材P34）例1：购买汽车的控件实例例2：素材中动态图表模拟题INDEX函数（P60）语法格式：INDEX(reference,row_num,column_num)功能：返回数据清单或者数组中的指定位置的单元格的值，此单元格的值由行序号和列序号的索引值给定。P34说明：

reference为一个或多个单元格区域的引用

row_num和column_num为返回元素的行序号和列序号，函数即从该行和该列返回一个引用

如果单元格区域的引用只包含一行或一列，则相应的参数row_num或column_num分别为可选项

如果将row_num或column_num设置为0，函数INDEX则分别返回整个列或行的引用【例】ABC1水果价格数量2苹果.69403香蕉.34384柠檬.55155柑桔.25256梨.5950公式：=INDEX(A2:C6,2,3)返回A2:C6区域中第2行第3列的引用38

公式：=SUM(INDEX(A2:C6,0,3))返回A2:C6区域中第3列(即区域C2:C6)的和168公式：=INDEX(C2:C6,2)返回C2:C6区域中第2行的引用38

公式：=INDEX(B6:C6,2)返回B6:C6区域中第2列的引用50

PMT函数（P58）语法格式：PMT(rat,nper,pv,fv,type)功能：基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。语法：PMT(rat,nper,pv,fv,type)说明：

rate为各期利率，是一固定值

nper为总投资（贷款）期，即该项投资（或贷款）的付款期总数

pv为现值，或一系列未来付款的当前值的累积和，即本金

fv为未来值，或在最后一次付款后希望得到的现金余额。若省略，则假设其值为零

type为0或1，用以指定各期的付款时间是期初还是期末。若省略，这假设其值为零【例】计算10个月付清的年利率为6%的10000贷款的月支额。公式：=PMT(6%/12,10,10000)计算值：-1027.71三、数据透视分析

所谓数据的透视分析是从不同的分析角度，对同一张报表根据不同的指标进行分类汇总。数据透视分析有：

▲分类汇总

▲数据透视表

▲数据透视图1、分类汇总

分类汇总之前必须：▲确定分类的依据▲按此进行排序。【例】分类汇总各种饮料的销售额。★操作步骤：

按饮料的品名进行排序

菜单命令：【数据】/【分类汇总】

设置分类汇总的各选项

分类字段

汇总方式（求和、平均值、计数、标准差等）

选定汇总项（可以是一个或多个）

分类汇总必须按分类的依据进行排序，否则将不能得到正确的结果。

单击汇总结果左侧的“＋”和“－”可实现显示/隐藏明细数据。

单击左侧上方的“1”、“2”、“3”可以实现显示数据的级数：

1：总计数2：分类汇总数3：明细数据

取消汇总可以单击对话框中的【全部删除】按钮。2、数据透视表使用数据透视表可以：▲从不同的角度查看汇总数据▲按照不同方式对数据进行汇总：求和、求平均值、求最大值或最小值、计数、乘积等▲筛选数据或显示合计值的明细数据▲动态地改变其版面布置利用数据透视表进行分类汇总有两种方法：

在Excel数据清单上利用数据透视表进行汇总

利用数据透视表直接从数据库中查询数据并汇总数据【例】

制作各地区不同类别商品的销售量和销售额的数据透视表（数据来源于Excel工作表）。★操作步骤：

菜单命令：【数据】/【数据透视表】

按向导提示设置各选项

数据来源

数据源区域

布局（页、行、列、数值）

选项（列总计、行总计）

数据透视表位置【例】

统计“销售表”工作表中不同规模销售数量的发生次数以及频率分布（如下图所示）。★操作步骤：

启动数据透视表向导

将“地区”、“分类”字段拖至页区域，将“数量”拖至行区域和数值区域，并将数值区域的汇总方式改为计数

取消“列总计”和“行总计”

在数据透视表中右击行字段“销售数量”，并调用快捷菜单命令：【组及显示明细数据】/【组合】

设置分组的起始值、终值及步长。【例】

在饮料销售的数据透视表中，将金陵啤酒、青岛啤酒合并为啤酒，雪碧、可乐合并为饮料统计其销售额，并显示其占总销售额的百分比（如下图所示）。【例】

在“销售数据”工作表中建立一个数据透视表（不导入Access数据库中的数据），要求：（1）输出字段为：产品名称、订购日期、货主地区、销售额；（2）按年和月统计不同地区不同产品的销售总额；（3）显示每年销售额最大的3个月。注：销售额=单价×数量

×(1－折扣)

首先创建查询，需要3张表：产品表（产品名称）、订单表（订购日期和货主地区）和订单明细表（单价、数量和折扣）。产品表和订单表示主表，订单明细表是子表。

如果需要输出的数据不在查询数据源中，则必须在查询中添加计算字段；如果在数据透视表中添加，可能会导致数据的计算错误。

字段的组合只能在行区域内完成，如果该字段是位于其它区域，则必须先将其放置于行区域并完成组合后，再移至其它区域。3、数据透视图

数据透视图与数据透视表相似，只不过它是以图表的方式显示数据之间的关系的。数据透视图的建立方式与数据透视表的建立方式完全相同，启动向导后按提示一步一步地进行设置。§2.3函数Excel中的函数实质上是一个预定义的计算公式，它使用一个或多个数值（参数）执行一系列的运算，然后返回一个运算结果（函数值）。一、函数简介1、函数结构函数由三部分组成：▲函数名表示函数要执行的运算▲参数

指定函数使用的值（可以是常数、单元格名称或区域名称）▲圆括号将各参数括于其中（无参数时也不能缺省）

当函数中含有多个参数时，各个参数之间用逗号隔开

函数名与括号之间不能有空格或其它字符

当函数的参数是单元格或区域地址时，参与运算的是其中的值

不同的函数，其参数个数是各不相同的；同一函数的各个参数的数据类型也不尽相同。因此，在使用函数时，一定要正确输入函数的参数。2、使用函数★操作步骤：①单击编辑栏中的【插入函数】按钮②选择函数的类别和函数③设置函数的参数3、函数的种类

Excel有数据库函数、日期与时间函数、财务函数等11种（各种函数功能详见教材P49~P50）。▲数据库函数当需要分析数据清单中的数值是否符合特定条件时，将使用数据库函数。数据库函数共有12个，这些函数统称D函数。每个数据库函数都有3个参数：

database

指定数据清单区域

field

指定数据清单中的列

criteria

指定条件区域二、常用函数1、日期函数▲DATE函数语法：DATE(year,month,day)功能：返回指定的日期。说明：

year可以是1至4位数。当年份是1至2位数时，返回的是20世纪的年份。

month的值大于正常的月份数或为负，则从指定的年份的一月开始往上加算（或往下减算）。

day的值大于正常的月份数或为负，则从指定的月份的1日开始往上加算（或往下减算）。【例】DATE(2012,13,34)返回2013年2月3日▲NOW函数语法：NOW()功能：返回当前日期和时间。说明：

可以根据需要设置单元格的格式，只显示日期或时间。▲TODAY函数语法：TODAY()功能：返回今天的日期序号。说明：

如果将单元格的显示格式设置为时间，那么显示为：00:00:00。

这两个函数均无参数（教材P51举例有错）。2、数学函数▲FLOOR函数语法：FLOOR(number,significance)功能：将number沿绝对值减小的方向取值，使其值等于最接近的significance倍数。说明：

任一参数为非数值参数，则返回：#VALUE！

如果两个参数符号相反，则返回：#NUM！

无论参数的符号是正或负，取值时，参数的绝对值都将减小。【例】FLOOR(12.4,3.3)FLOOR(-43,-13.4)9.940.2【例】FLOOR(0,7)FLOOR(0,0)FLOOR(7,0)00#DIV/0!▲CEILING函数语法：CEILING(number,significance)功能：将number沿绝对值增大的方向取值，使其值等于最接近的significance倍数。说明：

任一参数为非数值参数，则返回：#VALUE！

如果两个参数符号相反，则返回：#NUM！

无论参数的符号是正或负，取值时，参数的绝对值都将增大。【例】CEILING(12,7)CEILING(-4,-3.4)CEILING(7,0)CEILING(0,7)146.800▲MOD函数语法：MOD(number,divisor)功能：返回两个数相除后的余数。说明：

number为被除数，divisor为除数。

divisor为零时返回：#DIV/0！

结果的正负号与divisor相同。

如果两个数的符号相同，函数返回值的绝对值即为两数相除后的余数。

如果两个数的符号不相同，则其余数要被除数相减。【例】MOD(12,7) 5MOD(-12,-7)

-5MOD(12.5,7.3)

5.2MOD(-12,7) 2MOD(12,-7)

-2MOD(-12.5,7.3)

2.1

如果两个数的符号相同，函数返回值的绝对值即为两数相除后的余数。

如果两个数的符号不相同，则其余数要与除数相减。

以下公式成立：MOD(n,d)=n-d*INT(n/d)▲ROUNDUP函数语法：ROUNDUP(number,num_digits)功能：对number远离零值，向上舍入数字。说明：

取舍位数由参数num_digits决定。

ROUNDUP函数和ROUND函数的功能基本相同，两者区别的是：ROUNDUP函数在进行取舍时总是向上取舍，即进行远离0的取舍。

当取舍位数为0时，取舍到整数；取舍位数为负值时，即表示小数点的左侧应有多少个零。【例】ROUND(18.435,2) ROUNDUP(18.435,2) 18.44 18.44ROUND(18.435,1) ROUNDUP(18.435,1) 18.4 18.5ROUND(-18.435,1) ROUNDUP(-18.435,1) -18.4 -18.5ROUND(18.435,0) ROUNDUP(18.435,0) 18 19ROUND(18.435,-1) ROUNDUP(18.435,-1) 20 20ROUND(18.435,-2) ROUNDUP(18.435,-2) 0 100▲ROUNDDOWN函数语法：ROUNDDOWN(number,num_digits)功能：对number进行靠近零值，向下舍入数字。说明：

ROUNDDOWN函数和ROUNDUP函数的功能差不多，只不过ROUNDUP函数在进行取舍时总是向上取舍即远离0的取舍；而ROUNDDOWN函数在进行取舍时总是向下取舍即进行靠近0的取舍。【例】ROUNDDOWN(18.435,2) 18.43ROUNDDOWN(18.435,1) 18.4ROUNDDOWN(-18.435,2) -18.43ROUNDDOWN(18.435,0) 18ROUNDDOWN(18.435,-1) 10ROUNDDOWN(18.435,-2) 0▲MAX函数、MIN函数

MAXA函数、MINA函数语法格式：

MAX(number1,number2,…)MIN(number1,number2,…)功能：返回一组值中的的最大值（最小值）。说明：

参数是数字或数字的文本表达式时：

TRUE作为1计算；FALSE作为0计算

参数为错误值或不能转换为数字的文本，将产生错误。【例】MAX(12,56,9,48)MAX(-12,-23,TRUE,0)MIN(2,23,FALSE,1)MIN(5^2,4^3,"10")561010

参数为数组或引用时：

只有数组或引用中的数字将被计算

数组或引用中的空白单元格、逻辑值或文本将被忽略。

如果引用参数不包含数字，则结果返回为0。

如果逻辑值和文本不能忽略，则使用MAXA函数和MINA函数。在此两个函数中，TRUE作为1计算；FALSE和文本作为0计算。

▲RANK函数语法格式：

RANK(number,ref,order)功能：返回number在ref中的排位。说明：

number是要找到排位的数字；ref为数字列表或对数字列表的引用。

ref中的非数值型数据将被忽略。

order为一数字，指明排位的方式。若order为0或省略，对数字的排位是基于ref的按照降序排列的列表；若order不为0，则数字的排位是基于ref的按照升序排列的列表。【例】

工作表中的数据如下图所示：ABC178269365489553694758880

如果在C1中输入：=RANK(78,A1:A8,1)则C1中显示的值为：

如果在C3中输入：=RANK(78,A1:A8)则C3中显示的值为：5454注意：函数RANK对重复数的排位相同，但重复数的存在将影响后续数值的排位。【例】

工作表中的数据如下图所示：ABC178289365489553694

如果在C1中输入：=RANK(89,A1:A6)则C1中显示的值为：

如果在C3中输入：=RANK(78,A1:A6)则C3中显示的值为：2424思考题：教材P41根据平均分排名次的例题，是否能用RANK函数来实现？▲COUNT函数语法格式：

COUNT(value1,value2,…)功能：返回包含数字及包含参数列表中的数字的单元格的个数。说明：

参数value1,value2,…为包含或引用各种类型数据的参数（1至30个），但只有数字类型的数据才被计算。

函数将把数字、日期或以文本代表的数字计算在内；但错误值或其它无法转换为数字的文字将被忽略。

如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文本或错误值都将被忽略。

如果要统计逻辑值、文本或错误值需要用COUNTA函数。【例】=COUNT(23,78,"123",TRUE,2012-5-1,"A")

返回值：5

如果在A6中输入：

=COUNT(A1:A5)

则A6中显示： 如果在A6中输入：

=COUNTA(A1:A5)

则A6中显示：A12323"123"4TRUE52012-5-162（仅指23和2012-5-1）24（A2单元格除外）SUM、AVERAGE函数也有类似的性质，即：

参数是常数时，函数将把数字、日期或以文本代表的数字计算在内；但错误值或其它无法转换为数字的文字将被忽略。

如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文本或错误值都将被忽略。▲SUMIF函数语法格式：

SUMIF(range,criteria,sum_range)功能：根据指定条件对若干单元格求和。说明：

range是需要条件判断的单元格区域；criteria为条件，可以是数字、文本或表达式且必须加双引号；sum_range是需要求和的实际区域。

只有range区域中符合指定条件的单元格，其在sum_range区域相应的单元格才求和。

如果参数sum_range缺省，则对range区域中符合条件的数据求和。【例】统计奶制品的销售合计可以使用公式：

=SUMIF(B2:B11,"奶制品",C2:C11)（教材P55有错误）

如果要进行多条件统计，则需要用数组公式来完成。【例】统计南部肉类销售合计和东部和南部销售合计。C13和C14公式分别为：=SUM(IF((A2:A11="南部")*(B2:B11="肉类"),C2:C11))=SUM(IF((A2:A11="东部")+(A2:A11="南部"),C2:C11))

公式中的“*”，相当于AND连接；“+”相当于OR连接。▲COUNTIF函数语法格式：

COUNTIF(range,criteria)功能：计算区域中满足指定条件的单元格数。说明：

range是需要计算其中满足条件的单元格数目的区域；criteria是指定的条件。【例】统计奶制品的销售记录数：

=COUNTIF(B2:B11,"奶制品")统计销售品种数：

=SUM(1/COUNTIF(B2:B11,B2:B11))▲SUBTOTAL函数语法格式：

SUBTOTAL(function_num,ref1,ref2,…)功能：返回数据清单或数据库中的分类汇总。说明：

function_num为1到11（或101至111）的数字，指定使用何种函数在数据清单中进行分类汇总。

ref1,ref2,…为要进行分类汇总计算的区域。Function_num函数Function_num函数1AVERAGE7STDEV2COUNT8STEDVA3COUNTA9SUM4MAX10VAR5MIN11VARA6PRODUCTfunction_num参数对照表【例】 =SUBTOTAL(1,A1:A5)

3 =SUBTOTAL(9,A1:B5) 165AB11102220333044405550▲PRODUCT函数语法格式：

PRODUCT(number1,number2,…)功能：将所有以参数形式出现的数字相乘。说明：

当参数是数字、逻辑值或数字文本时可以被计算；当参数为错误值或是不能转换成数字的文字时，将会导致错误。

当参数是数组或引用时，只有其中的数字才被计算；其中的空白单元格、逻辑值、文本或错误值将被忽略。【例】=PRODUCT(A1:B2)

400=

PRODUCT(A3:B4) 360=PRODUCT(10,3-1,"3") 60

=

PRODUCT(A3:B4)

120=PRODUCT(10,"2",FALSE) 0=PRODUCT(10,"ABC")

#VALUE!3、查找函数▲INDEX函数语法：

INDEX(array,row_num,column_num)功能：返回引用中指定单元格区域的引用，此单元格区域由行序号和列序号的索引值给定。说明：

array为单元格区域或数组常量。

row_numn和column_numn为引用中的行序号和列序号，函数即从该行和该列返回一个引用。

如果引用中只包含一行或一列，则相应的参数row_num或column_num分别为可选项。P60

如果将row_num和column_num同时存在，函数则返回row_num和column_num的交叉单元格中的值。

如果将row_num或column_num设置为0，函数INDEX则分别返回整个列或行的数组数值。【例】ABC1水果价格数量2苹果.69403香蕉.34384柠檬.55155柑桔.25256梨.5950公式：=INDEX(A2:C6,2,3)返回A2:C6区域中第2行第3列的引用38

公式：=SUM(INDEX(A2:C6,0,3))返回A2:C6区域中第3列(即区域C2:C6)的和168▲CHOOSE函数语法：

CHOOSE(index_num,value1,value2,…)功能：根据索引值返回数值参数列表中相应的数值。说明：

index_num为索引值。其值必须是从1至29之间的数字、或包含数字1至29的单元格引用。

index_num的值若小于1或大于数值参数列表中最后一个参数的序号，则返回错误值#VALUE！。

如果index_num为小数，则在函数计算前将被截尾取整。【例】

CHOOSE(2,"ABC",3*4,56,TRUE)

12CHOOSE(2+1,"ABC",3*4,2012-1-1)

2010▲VLOOKUP函数语法：

VLOOKUP(value,table,c_index,range_lookup)功能：在表格或数值数组的首列查找指定的数值，并由此返回该数值所在行中指定列处的数值。说明：

value为需要在数组第一列中查找的数值。value可以是数值、引用或文本字符串。

table为需要在其中查找数据的数据表。可以是区域、数据库或列表。

c_index为table中待返回匹配值的列序号。

如果小于1，函数返回错误值#VALUE!；

如果大于table的列数，函数返回错误值#REF!。

range_lookup是一个逻辑值，指明函数查找时，是精确匹配查找还是近似匹配查找。

range_lookup为TRUE（默认值）时，函数为近似匹配值查找，即查找等于或小于value的最大数值；此时table第一列的数值必须按升序排序，否则函数不能返回正确的数值。

range_lookup为FALSE时，函数为精确匹配值查找。若找不到，则返回错误值#N/A。此时table的第一列可以不排序。【例】ABC1.34551002.37424003.45672504.68323005.79801506.8577700=VLOOKUP(.5,A1:C6,2) 67=VLOOKUP(.5,A1:C6,2,FALSE) #N/A=VLOOKUP(1,A1:C6,3) 700=VLOOKUP(.1,A1:C6,3) #N/A▲HLOOKUP函数语法：

HLOOLUP(value,table,r_index,range_lookup)功能：在表格或数值数组的首行查找指定的数值，并由此返回该数值所在列中指定行处的数值。▲LOOKUP函数

LOOKUP函数具有两种语法形式：向量形式和数组形式。

语法一：

LOOKUP(value,lookup_vector,[result_vector])功能：在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。说明：

value需要查找的值，可以是数字、文本、逻辑值、名称或对值的引用。

lookup_vector查找的区域，是单行或单列的区域，且必须按升序排序。

result_vector是函数返回值所在区域。此参数是一个可选项，且是个只包含一行或一列的区域。result_vector参数必须与lookup_vector参数大小相同。若缺省，则返回所查找的值。

如果函数找不到value，函数将与lookup_vector中小于或等于value的最大值进行匹配。

如果value小于lookup_vector中的最小值，则函数返回#N/A错误值。【例】ABC1水果价格2柑桔4.503梨2.304柠檬5.005苹果3.506香蕉3.30=LOOKUP("苹果",A2:A6,C2:C6) 3.50语法二：

LOOKUP(value,array)功能：在数组的第一行或第一列中查找指定的值，然后返回数组的最后一行或最后一列中相同位置的值说明：

value是在数组中查找的值，可以是数字、文本、逻辑值、名称或对值的引用。

array是包含要与value进行比较的文本、数字或逻辑值的单元格区域，必须按升序排序。

如果找不到value值，将使用数组中小于或等于value的最大值。

如果value的值小于第一行或第一列中的最小值（取决于数组的维数），函数返回#N/A错误值。

如果数组包含宽度比高度大的区域（列数多于行数），函数将在第一行中搜索；反之，在第一列中搜索。【例】ABC1水果价格2柑桔4.503梨2.304柠檬5.005苹果3.506香蕉3.30=LOOKUP("香蕉",A2:A6,C2:C6) 3.30=LOOKUP(65,{12,34,65,79;86,50,42,68;93,66,200,99})

200

查找函数在制作动态图表时的作用极为重要。不同的查找函数其功能是有所不同的。按其查找内容及返回值，可以将查找函数分为：

函数通过索引号在指定区域中进行查找，查找后返回相应单元格的值。如：INDEX函数、CHOOSE函数等;

函数在指定区域中查找指定的值，查找后返回该值在查找区域中的位置。如：MATCH函数；

函数在指定区域中查找指定的值，查找后返回与该值在查找区域中位置相对应的另一个区域中的值。如：LOOKUP函数（包括HLOOKUP和VLOOKUP函数）。

按其查找方式，可以将查找函数分为：精确查找。如LOOKUP函数（可以使用统配符）、参数为0的MATCH函数、LOOKUP函数以及参数为FALSE的HLOOKUP和VLOOKUP函数;

模糊查找。如：MATCH函数、参数为TRUE的HLOOKUP和VLOOKUP函数。4、文本函数▲CHAR函数语法：

CHAR(number)功能：返回对应的ASCII码的字符。▲CODE函数语法：

CODE(text)功能：返回字符串中第一个字符的ASCII码。▲LEFTB和RIGHTB函数语法：

LEFTB(text,num_bytes)RIGHTB(text,num_bytes)功能：根据指定的字节数返回文本中开始（或最后）的若干个字符。说明：

num_bytes为需要从字符串中提取的字节数。

num_bytes必须大于等于0。

如果num_bytes大于字