EXCEL函数课程教案资料.docx
《EXCEL函数课程教案资料.docx》由会员分享，可在线阅读，更多相关《EXCEL函数课程教案资料.docx（14页珍藏版）》请在冰豆网上搜索。
EXCEL函数课程教案资料
EXCEL函数教材（中级）
函数类型IF、COUNT、SUMIF、COUNTIF、VLOOKUP、INDEX、MATCH
一、IF函数
执行真假值判断，根据逻辑计算的真假值，返回不同结果。
可以使用函数IF对数值和公式进行条件检测。
语法
IF（logical_test,value_if_true,value_if_false）
Logical_test 表示计算结果为TRUE或FALSE的任意值或表达式。
例如，A10=100就是一个逻辑表达式，如果单元格A10中的值等于100，表达式即为TRUE，否则为FALSE。
本参数可使用任何比较运算符。
Value_if_true logical_test为TRUE时返回的值。
例如，如果本参数为文本字符串“预算内”而且logical_test参数值为TRUE，则IF函数将显示文本“预算内”。
如果logical_test为TRUE而value_if_true为空，则本参数返回0（零）。
如果要显示TRUE，则请为本参数使用逻辑值TRUE。
Value_if_true也可以是其他公式。
Value_if_false logical_test为FALSE时返回的值。
例如，如果本参数为文本字符串“超出预算”而且logical_test参数值为FALSE，则IF函数将显示文本“超出预算”。
如果logical_test为FALSE且忽略了Value_if_false（即value_if_true后没有逗号），则会返回逻辑值FALSE。
如果logical_test为FALSE且Value_if_false为空（即value_if_true后有逗号，并紧跟着右括号），则本参数返回0（零）。
Value_if_false也可以是其他公式。
函数IF可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。
请参阅下面最后一个示例。
在计算参数value_if_true和value_if_false后，函数IF返回相应语句执行后的返回值。
如果函数IF的参数包含数组 （数组：
用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。
数组区域共用一个公式；数组常量是用作参数的一组常量。
），则在执行IF语句时，数组中的每一个元素都将计算。
MicrosoftExcel还提供了其他一些函数，可依据条件来分析数据。
例如，如果要计算单元格区域中某个文本字符串或数字出现的次数，则可使用COUNTIF工作表函数。
如果要根据单元格区域中的某一文本字符串或数字求和，则可使用SUMIF工作表函数。
请了解关于根据条件计算值。
1
2
A
数据
50
公式
说明（结果）
=IF（A2<=100,"Withinbudget","Overbudget"）
如果上面的数字小于等于100，则公式将显示“Withinbudget”。
否则，公式显示“Overbudget”。
（Withinbudget）
=IF（A2=100,SUM（B5:
B15）,""）
如果上面数字为100，则计算单元格区域B5:
B15，否则返回空文本（""）
二、COUNT函数
返回包含数字以及包含参数列表中的数字的单元格的个数。
利用函数COUNT可以计算单元格区域或数字数组中数字字段的输入项个数。
语法
COUNT（value1,value2,...）
Value1,value2,... 为包含或引用各种类型数据的参数（1到30个），但只有数字类型的数据才被计算。
说明
∙函数COUNT在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。
∙如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。
如果要统计逻辑值、文字或错误值，请使用函数COUNTA。
示例
1
2
3
4
5
6
7
8
A
数据
销售
2008-12-8
19
22.24
TRUE
#DIV/0!
公式
说明（结果）
=COUNT（A2:
A8）
计算上列数据中包含数字的单元格的个数（3）
=COUNT（A5:
A8）
计算上列数据的最后4行中包含数字的单元格的个数
（2）
=COUNT（A2:
A8,2）
计算上列数据中包含数字的单元格以及包含数值2的单元格的个数（4）
三、COUNTIF
计算区域中满足给定条件的单元格的个数。
语法
COUNTIF（range,criteria）
Range 为需要计算其中满足条件的单元格数目的单元格区域。
Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。
例如，条件可以表示为32、"32"、">32"、"apples"或B4。
说明
MicrosoftExcel还提供了其他一些函数，它们可根据条件来分析数据。
∙若要计算基于一个文本字符串或某范围内的一个数值的总和，可使用SUMIF工作表函数。
∙若要使公式返回两个基于条件的值之一，例如某指定销售金额的销售红利，可使用IF工作表函数。
∙若要计算空或非空单元格的个数，可使用COUNTA和COUNTBLANK函数。
1.示例
1
2
3
4
5
A
B
数据
数据
apples
32
oranges
54
peaches
75
apples
86
公式
说明（结果）
=COUNTIF（A2:
A5,"apples"）
计算第一列中苹果所在单元格的个数
（2）
=COUNTIF（A2:
A5,A4）
计算第一列中桃所在单元格的个数
（2）
=COUNTIF（A2:
A5,A3）+COUNTIF（A2:
A5,A2）
计算第一列中柑桔和苹果所在单元格的个数（3）
=COUNTIF（B2:
B5,">55"）
计算第二列中值大于55的单元格个数
（2）
=COUNTIF（B2:
B5,"<>"&B4）
计算第二列中值不等于75的单元格个数
（2）
=COUNTIF（B2:
B5,">=32"）-COUNTIF（B2:
B5,">85"）
计算第二列中值大于或等于32且小于或等于85的单元格个数（3）
四、SUMIF
根据指定条件对若干单元格求和。
语法
SUMIF（range,criteria,sum_range）
Range 为用于条件判断的单元格区域。
Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。
例如，条件可以表示为32、"32"、">32"或"apples"。
Sum_range 是需要求和的实际单元格。
说明
∙只有在区域中相应的单元格符合条件的情况下，sum_range中的单元格才求和。
∙如果忽略了sum_range，则对区域中的单元格求和。
∙MicrosoftExcel还提供了其他一些函数，它们可根据条件来分析数据。
例如，如果要计算单元格区域内某个文本字符串或数字出现的次数，则可使用COUNTIF函数。
如果要让公式根据某一条件返回两个数值中的某一值（例如，根据指定销售额返回销售红利），则可使用IF函数。
示例
1
2
3
4
5
A
B
属性值
佣金
100,000
7,000
200,000
14,000
300,000
21,000
400,000
28,000
公式
说明（结果）
=SUMIF（A2:
A5,">160000",B2:
B5）
属性值超过160,000的佣金的和（63,000）
五、VLOOKUP
在表格数组的首列查找值，并由此返回表格数组当前行中其他列的值。
VLOOKUP中的V表示垂直方向。
当比较值位于需要查找的数据左边的一列时，可以使用VLOOKUP，而不用HLOOKUP。
语法
VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）
Lookup_value 为需要在表格数组 （数组：
用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。
数组区域共用一个公式；数组常量是用作参数的一组常量。
）第一列中查找的数值。
Lookup_value可以为数值或引用。
若lookup_value小于table_array第一列中的最小值，VLOOKUP将返回错误值#N/A。
Table_array 为两列或多列数据。
请使用对区域的引用或区域名称。
table_array第一列中的值是由lookup_value搜索的值。
这些值可以是文本、数字或逻辑值。
不区分大小写。
Col_index_num 为table_array中待返回的匹配值的列序号。
Col_index_num为1时，返回table_array第一列中的数值；col_index_num为2，返回table_array第二列中的数值，以此类推。
如果col_index_num：
∙小于1，VLOOKUP返回错误值#VALUE!
。
∙大于table_array的列数，VLOOKUP返回错误值#REF!
。
Range_lookup 为逻辑值，指定希望VLOOKUP查找精确的匹配值还是近似匹配值：
∙如果为TRUE或省略，则返回精确匹配值或近似匹配值。
也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。
table_array第一列中的值必须以升序排序；否则VLOOKUP可能无法返回正确的值。
可以选择“数据”菜单上的“排序”命令，再选择“递增”，将这些值按升序排序。
有关详细信息，请参阅默认排序次序。
∙如果为FALSE，VLOOKUP将只寻找精确匹配值。
在此情况下，table_array第一列的值不需要排序。
如果table_array第一列中有两个或多个值与lookup_value匹配，则使用第一个找到的值。
如果找不到精确匹配值，则返回错误值#N/A。
说明
∙在table_array第一列中搜索文本值时，请确保table_array第一列中的数据没有前导空格、尾随空格、不一致的直引号（'或"）、弯引号（‘或“）或非打印字符。
在上述情况下，VLOOKUP可能返回不正确或意外的值。
有关用于清除文本数据的函数的详细信息，请参阅文本和数据函数。
∙在搜索数字或日期值时，请确保table_array第一列中的数据未保存为文本值。
否则，VLOOKUP可能返回不正确或意外的值。
有关详细信息，请参阅将保存为文本的数字转换为数字值。
∙如果range_lookup为FALSE且lookup_value为文本，则可以在lookup_value中使用通配符、问号（?
）和星号（*）。
问号匹配任意单个字符；星号匹配任意字符序列。
如果您要查找实际的问号或星号本身，请在该字符前键入波形符（~）。
示例1
本示例搜索大气特征表的“密度”列以查找“粘度”和“温度”列中对应的值。
（该值是在海平面0摄氏度或1个大气压下对空气进行测定的结果。
）
1
2
3
4
5
6
7
8
9
10
A
B
C
密度
粘度
温度
0.457
3.55
500
0.525
3.25
400
0.616
2.93
300
0.675
2.75
250
0.746
2.57
200
0.835
2.38
150
0.946
2.17
100
1.09
1.95
50
1.29
1.71
0
公式
说明（结果）
=VLOOKUP（1,A2:
C10,2）
使用近似匹配搜索A列中的值1，在A列中找到小于等于1的最大值0.946，然后返回同一行中B列的值。
（2.17）
=VLOOKUP（1,A2:
C10,3,TRUE）
使用近似匹配搜索A列中的值1，在A列中找到小于等于1的最大值0.946，然后返回同一行中C列的值。
（100）
=VLOOKUP（.7,A2:
C10,3,FALSE）
使用精确匹配在A列中搜索值0.7。
因为A列中没有精确匹配的值，所以返回一个错误值。
（#N/A）
=VLOOKUP（0.1,A2:
C10,2,TRUE）
使用近似匹配在A列中搜索值0.1。
因为0.1小于A列中最小的值，所以返回一个错误值。
（#N/A）
=VLOOKUP（2,A2:
C10,2,TRUE）
使用近似匹配搜索A列中的值2，在A列中找到小于等于2的最大值1.29，然后返回同一行中B列的值。
（1.71）
六、INDEX
回表或区域中的值或对值的引用。
INDEX函数有两种形式：
数组 （数组：
用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。
数组区域共用一个公式；数组常量是用作参数的一组常量。
）形式和引用形式。
如果要
请查看
返回指定单元格或单元格数组的值。
数组形式
返回对指定单元格的引用
引用形式
数组形式
返回由行和列编号索引选定的表或数组 （数组：
用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。
数组区域共用一个公式；数组常量是用作参数的一组常量。
）中的元素值。
如果INDEX的第一个参数是数组常量，请使用数组形式。
INDEX（array,row_num,column_num）
Array 是一个单元格区域或数组常量。
∙如果数组中只包含一行或一列，则可以不使用相应的row_num或column_num参数。
∙如果数组中包含多个行和列，但只使用了row_num或column_num，INDEX将返回数组中整行或整列的数组。
Row_num 用于选择要从中返回值的数组中的行。
如果省略row_num，则需要使用column_num。
Column_num 用于选择要从中返回值的数组中的列。
如果省略column_num，则需要使用row_num。
说明
∙如果同时使用了row_num和column_num参数，INDEX将返回row_num和column_num交叉处单元格中的值。
∙如果将row_num或column_num设置为0（零），INDEX将分别返回整列或整行的值数组。
要将返回的值用作数组，请在行的水平单元格区域和列的垂直单元格区域以数组公式 （数组公式：
数组公式对一组或多组值执行多重计算，并返回一个或多个结果。
数组公式括于大括号（{}）中。
按Ctrl+Shift+Enter可以输入数组公式。
）的形式输入INDEX函数。
要输入数组公式，请按Ctrl+Shift+Enter。
∙Row_num和column_num必须指向数组中的某个单元格；否则，INDEX将返回#REF!
错误值。
示例1
1
2
3
A
B
数据
数据
苹果
柠檬
香蕉
梨子
公式
说明（结果）
=INDEX（A2:
B3,2,2）
所选区域中第二行与第二列交叉处的值（梨子）
=INDEX（A2:
B3,2,1）
所选区域中第二行与第一列交叉处的值（香蕉）
七、MATCH
返回在指定方式下与指定数值匹配的数组 （数组：
用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。
数组区域共用一个公式；数组常量是用作参数的一组常量。
）中元素的相应位置。
如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数而不是LOOKUP函数。
语法
MATCH（lookup_value,lookup_array,match_type）
Lookup_value 为需要在数据表中查找的数值。
∙Lookup_value为需要在Look_array中查找的数值。
例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。
∙Lookup_value可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。
Lookup_array 可能包含所要查找的数值的连续单元格区域。
Lookup_array应为数组或数组引用。
Match_type 为数字-1、0或1。
Match-type指明MicrosoftExcel如何在lookup_array中查找lookup_value。
∙如果match_type为1，函数MATCH查找小于或等于lookup_value的最大数值。
Lookup_array必须按升序排列：
...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。
∙如果match_type为0，函数MATCH查找等于lookup_value的第一个数值。
Lookup_array可以按任何顺序排列。
∙如果match_type为-1，函数MATCH查找大于或等于lookup_value的最小数值。
Lookup_array必须按降序排列：
TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...，等等。
∙如果省略match_type，则假设为1。
说明
∙函数MATCH返回lookup_array中目标值的位置，而不是数值本身。
例如，MATCH（"b",{"a","b","c"},0）返回2，即“b”在数组{"a","b","c"}中的相应位置。
∙查找文本值时，函数MATCH不区分大小写字母。
∙如果函数MATCH查找不成功，则返回错误值#N/A。
∙如果match_type为0且lookup_value为文本，lookup_value可以包含通配符、星号（*）和问号（?
）。
星号可以匹配任何字符序列；问号可以匹配单个字符。
示例
1
2
3
4
5
A
B
Product
Count
Bananas
25
Oranges
38
Apples
40
Pears
41
公式
说明（结果）
=MATCH（39,B2:
B5,1）
由于此处无正确的匹配，所以返回数据区域B2:
B5中最接近的下一个值（38）的位置。
（2）
=MATCH（41,B2:
B5,0）
数据区域B2:
B5中41的位置。
（4）
=MATCH（40,B2:
B5,-1）
由于数据区域B2:
B5不是按降序排列，所以返回错误值。
（#N/A）
升级会员 