EXCEL电子表格常用函数解释及用法示例.docx
《EXCEL电子表格常用函数解释及用法示例.docx》由会员分享，可在线阅读，更多相关《EXCEL电子表格常用函数解释及用法示例.docx（19页珍藏版）》请在冰豆网上搜索。
EXCEL电子表格常用函数解释及用法示例
EXCEL电子表格
常用函数解释及示例
1．LARGE
返回数据集中第k个最大值。
使用此函数可以根据相对标准来选择数值。
例如，可以使用函数LARGE得到第一名、第二名或第三名的得分。
语法
LARGE（array,k）
Array 为需要从中选择第k个最大值的数组或数据区域。
K 为返回值在数组或数据单元格区域中的位置（从大到小排）。
说明
∙如果数组为空，函数LARGE返回错误值#NUM!
。
∙如果k≤0或k大于数据点的个数，函数LARGE返回错误值#NUM!
。
如果区域中数据点的个数为n，则函数LARGE（array,1）返回最大值，函数LARGE（array,n）返回最小值。
示例
如果您将示例复制到空白工作表中，可能会更易于理解该示例。
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
3.按Ctrl+C。
4.在工作表中，选中单元格A1，再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换，请按Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。
1
2
3
4
5
6
A
B
数据
数据
3
4
5
2
3
4
5
6
4
7
公式
说明（结果）
=LARGE（A2:
B6,3）
上面数据中第三个最大值（5）
=LARGE（A2:
B6,7）
上面数据中第七个最大值（4）
2．SMALL
返回数据集中第k个最小值。
使用此函数可以返回数据集中特定位置上的数值。
语法
SMALL（array,k）
Array 为需要找到第k个最小值的数组或数字型数据区域。
K 为返回的数据在数组或数据区域里的位置（从小到大）。
说明
∙如果array为空，函数SMALL返回错误值#NUM!
。
∙如果k≤0或k超过了数据点个数，函数SMALL返回错误值#NUM!
。
∙如果n为数组中的数据点个数，则SMALL（array,1）等于最小值，SMALL（array,n）等于最大值。
示例
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
3.按Ctrl+C。
4.在工作表中，选中单元格A1，再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换，请按Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。
1
2
3
4
5
6
7
8
9
10
A
B
数据
数据
3
1
4
4
5
8
2
3
3
7
4
12
6
54
4
8
7
23
公式
说明（结果）
=SMALL（A2:
A10,4）
第一列中第四个最小值（4）
=SMALL（B2:
B10,2）
第二列中第二个最小值（3）
3．SUMIF
根据指定条件对若干单元格求和。
语法
SUMIF（range,criteria,sum_range）
Range 为用于条件判断的单元格区域。
Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。
例如，条件可以表示为32、"32"、">32"或"apples"。
Sum_range 是需要求和的实际单元格。
说明
∙只有在区域中相应的单元格符合条件的情况下，sum_range中的单元格才求和。
∙如果忽略了sum_range，则对区域中的单元格求和。
∙MicrosoftExcel还提供了其他一些函数，它们可根据条件来分析数据。
例如，如果要计算单元格区域内某个文本字符串或数字出现的次数，则可使用COUNTIF函数。
如果要让公式根据某一条件返回两个数值中的某一值（例如，根据指定销售额返回销售红利），则可使用IF函数。
示例
1.请在“帮助”主题中选取示例。
不要选取行或列标题。
2.按Ctrl+C。
3.在工作表中，选中单元格A1，再按Ctrl+V。
4.若要在查看结果和查看返回结果的公式之间切换，请按Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。
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）
4．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
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
3.按Ctrl+C。
4.在工作表中，选中单元格A1，再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换，请按Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。
1
2
A
数据
50
公式
说明（结果）
=IF（A2<=100,"Withinbudget","Overbudget"）
如果上面的数字小于等于100，则公式将显示“Withinbudget”。
否则，公式显示“Overbudget”。
（Withinbudget）
=IF（A2=100,SUM（B5:
B15）,""）
如果上面数字为100，则计算单元格区域B5:
B15，否则返回空文本（""）
示例2
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
3.按Ctrl+C。
4.在工作表中，选中单元格A1，再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换，请按Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。
1
2
3
4
A
B
实际费用
预算费用
1500
900
500
900
500
925
公式
说明（结果）
=IF（A2>B2,"OverBudget","OK"）
判断第1行是否超出预算（OverBudget）
=IF（A3>B3,"OverBudget","OK"）
判断第2行是否超出预算（OK）
示例3
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
3.按Ctrl+C。
4.在工作表中，选中单元格A1，再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换，请按Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。
1
2
3
4
A
成绩
45
90
78
公式
说明（结果）
=IF（A2>89,"A",IF（A2>79,"B",IF（A2>69,"C",IF（A2>59,"D","F"））））
为第一个成绩指定一个字母等级（F）
=IF（A3>89,"A",IF（A3>79,"B",IF（A3>69,"C",IF（A3>59,"D","F"））））
为第二个成绩指定一个字母等级（A）
=IF（A4>89,"A",IF（A4>79,"B",IF（A4>69,"C",IF（A4>59,"D","F"））））
为第三个成绩指定一个字母等级（C）
如果成绩是
则返回
大于89
A
80到89
B
70到79
C
60到69
D
小于60
F
在上例中，第二个IF语句同时也是第一个IF语句的参数value_if_false。
同样，第三个IF语句是第二个IF语句的参数value_if_false。
例如，如果第一个logical_test（Average>89）为TRUE，则返回“A”；如果第一个logical_test为FALSE，则计算第二个IF语句，以此类推。
用下列关键字指定数字的字母等级。
5．CEILING
将参数Number向上舍入（沿绝对值增大的方向）为最接近的significance的倍数。
例如，如果您不愿意使用像“分”这样的零钱，而所要购买的商品价格为$4.42，可以用公式=CEILING（4.42,0.1）将价格向上舍入为以“角”表示。
语法
CEILING（number,significance）
Number要四舍五入的数值。
Significance 是需要四舍五入的乘数。
说明
∙如果参数为非数值型，CEILING返回错误值#VALUE!
。
∙无论数字符号如何，都按远离0的方向向上舍入。
如果数字已经为Significance的倍数，则不进行舍入。
∙如果Number和Significance符号不同，CEILING返回错误值#NUM!
。
示例
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
3.按Ctrl+C。
4.在工作表中，选中单元格A1，再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换，请按Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。
1
2
3
4
5
6
A
B
公式
说明（结果）
=CEILING（2.5,1）
将2.5向上舍入到最接近的1的倍数（3）
=CEILING（-2.5,-2）
将-2.5向上舍入到最接近的-2的倍数（-4）
=CEILING（-2.5,2）
返回错误值，因为-2.5和2的符号不同（#NUM!
）
=CEILING（1.5,0.1）
将1.5向上舍入到最接近的0.1的倍数（1.5）
=CEILING（0.234,0.01）
将0.234向上舍入到最接近的0.01的倍数（0.24）
6．INT
将数字向下舍入到最接近的整数。
语法
INT（number）
Number 需要进行向下舍入取整的实数。
示例
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
3.按Ctrl+C。
4.在工作表中，选中单元格A1，再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换，请按Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。
1
2
A
数据
19.5
公式
说明（结果）
=INT（8.9）
将8.9向下舍入到最接近的整数（8）
=INT（-8.9）
将-8.9向下舍入到最接近的整数（-9）
=A2-INT（A2）
返回单元格A2中正实数的小数部分（0.5）
7．TRUNC
将数字的小数部分截去，返回整数。
语法
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。
Num_digits的默认值为0。
说明
函数TRUNC和函数INT类似，都返回整数。
函数TRUNC直接去除数字的小数部分，而函数INT则是依照给定数的小数部分的值，将其四舍五入到最接近的整数。
函数INT和函数TRUNC在处理负数时有所不同：
示例
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。
不要选取行或列标题。
3.按Ctrl+C。
4.在工作表中，选中单元格A1，再按Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换，请按Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。
1
2
3
4
A
B
公式
说明（结果）
=TRUNC（8.9）
8.9的整数部分（8）
=TRUNC（-8.9）
-8.9的整数部分（-8）
=TRUNC（PI（））
pi的整数部分（3）
8．LOOKUP
函数LOOKUP有两种语法形式：
向量和数组。
向量为只包含一行或一列的区域。
函数LOOKUP的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。
如果需要指定包含待查找数值的区域，则可以使用函数LOOKUP的这种形式。
函数LOOKUP的另一种形式为自动在第一列或第一行中查找数值。
函数LOOKUP的数组形式是在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值。
如果需要查找的数值在数组的第一行或第一列，就可以使用函数LOOKUP的这种形式。
当需要指定列或行的位置时，可以使用函数LOOKUP的其他形式。
语法1
向量形式
LOOKUP（lookup_value,lookup_vector,result_vector）
Lookup_value 为函数LOOKUP在第一个向量中所要查找的数值。
Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。
Lookup_vector 为只包含一行或一列的区域。
Lookup_vector的数值可以为文本、数字或逻辑值。
要点 Lookup_vector的数值必须按升序排序：
...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则，函数LOOKUP不能返回正确的结果。
文本不区分大小写。
Result_vector 只包含一行或一列的区域，其大小必须与lookup_vector相同。
说明
∙如果函数LOOKUP找不到lookup_value，则查找lookup_vector中小于或等于lookup_value的最大数值。
∙如果lookup_value小于lookup_vector中的最小值，函数LOOKUP返回错误值#N/A。
示例
1
2
3
4
5
6
A
B
频率
颜色
4.14
red
4.91
orange
5.17
yellow
5.77
green
6.39
blue
公式
说明（结果）
=LOOKUP（4.91,A2:
A6,B2:
B6）
在A列中查找4.91，并返回同一行B列的值（orange）
=LOOKUP（5.00,A2:
A6,B2:
B6）
在A列中查找5.00，并返回同一行B列的值（orange）
=LOOKUP（7.66,A2:
A6,B2:
B6）
在A列中查找7.66（最接近的下一个值为6.39），并返回同一行B列的值（blue）
=LOOKUP（0,A2:
A6,B2:
B6）
在A列中查找0，由于0小于查找向量A2:
A7中的最小值，所以返回错误值（#N/A）
语法2
提示：
通常情况下，最好使用函数HLOOKUP或函数VLOOKUP来替代函数LOOKUP的数组形式。
函数LOOKUP的这种形式主要用于与其他电子表格兼容。
数组形式
LOOKUP（lookup_value,array）
Lookup_value 为函数LOOKUP在数组中所要查找的数值。
Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。
∙如果函数LOOKUP找不到lookup_value，则使用数组中小于或等于lookup_value的最大数值。
∙如果lookup_value小于第一行或第一列（取决于数组的维数）的最小值，函数LOOKUP返回错误值#N/A。
Array 为包含文本、数字或逻辑值的单元格区域，它的值用于与lookup_value进行比较。
函数LOOKUP的数组形式与函数HLOOKUP和函数VLOOKUP非常相似。
不同之处在于函数HLOOKUP在第一行查找lookup_value，函数VLOOKUP在第一列查找，而函数LOOKUP则按照数组的维数查找。
∙如果数组所包含的区域宽度大，高度小（即列数多于行数），函数LOOKUP在第一行查找lookup_value。
∙如果数组为正方形，或者所包含的区域高度大，宽度小（即行数多于列数），函数LOOKUP在第一列查找lookup_value。
∙函数HLOOKUP和函数VLOOKUP允许按行或按列索引，而函数LOOKUP总是选择行或列的最后一个数值。
要点 数组中的数值必须按升序排序：
...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则，函数LOOKUP不能返回正确的结果。
文本不区分大小写。
示例
1
2
3
A
B
公式
说明（结果）
=LOOKUP（"C",{"a","b","c","d";1,2,3,4}）
在数组的第一行中查找“C”，并返回同一列中最后一行的值（3）
=LOOKUP（"bump",{"a",1;"b",2;"c",3}）
在数组的第一行中查找“bump”，并返回同一行中最后一列的值
（2）
9．VLOOKUP
在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。
当比较值位于数据表首列时，可以使用函数VLOOKUP代替函数HLOOKUP。
在VLOOKUP中的V代表垂直。
（注：
verticala.垂直的，竖直的）
语法
VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）
Lookup_value 为需要在数组第一列中查找的数值。
Lookup_value可以为数值、引用或文本字符串。
Table_array 为需要在其中查找数据的数据表。
可以使用对区域或区域名称的引用，例如数据库或列表。
∙如果range_lookup为TRUE，则table_array的第一列中的数值必须按升序排列：
…、-2、-1、0、1、2、…、-Z、FALSE、TRUE；否则，函数VLOOKUP不能返回正确的数值。
如果range_lookup为FALSE，table_array不必进行排序。
∙通过在“数据”菜单中的“排序”中选择“升序”，可将数值按升序排列。
∙Table_array的第一列中的数值可以为文本、数字或逻辑值。
∙文本不区分大小写。
Col_index_num 为table_array中待返回的匹配值的列序号。
Col_index_num为1时，返回table_array第一列中的数值；col_index_num为2，返回table_array第二列中的数值，以此类推。
如果col_index_num小于1，函数VLOOKUP返回错误值值#VALUE!
；如果col_index_num大于table_array的列数，函数VLOOKUP返回错误值#REF!
。
Range_lookup 为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。
如果为TRUE（非零）或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果range_value为FALSE（零），函数VLOOKUP将返回精确匹配值。
如果找不到，则返回错误值#N/A。
说明
∙如果函数VLOOKUP找不到lookup_value，且range_lookup为TRUE，则使用小于等于lookup_value的最大值。
∙如果lookup_value小于table_array第一列中的最小数值，函数VLOOK
升级会员 