Excel 常用函数一览表Word文件下载.docx
《Excel 常用函数一览表Word文件下载.docx》由会员分享,可在线阅读,更多相关《Excel 常用函数一览表Word文件下载.docx(22页珍藏版)》请在冰豆网上搜索。
apples"
。
MicrosoftExcel提供其他函数,可用来基于条件分析数据。
例如,若要计算基于一个文本字符串或某范围内的一个数值的总和,可使用SUMIF工作表函数。
若要使公式返回两个基于条件的值之一,例如某指定销售量的销售红利,可使用IF工作表函数。
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工作表函数。
MAX
返回一组值中的最大值。
MAX(number1,number2,...)
是要从中找出最大值的1到30个数字参数。
∙可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。
如果参数为错误值或不能转换成数字的文本,将产生错误。
∙如果参数为数组或引用,则只有数组或引用中的数字将被计算。
数组或引用中的空白单元格、逻辑值或文本将被忽略。
如果逻辑值和文本不能忽略,请使用函数MAXA来代替。
∙如果参数不包含数字,函数MAX返回0(零)
MIN
返回一组值中的最小值,用法同上。
SUM
返回某一单元格区域中所有数字之和。
SUM(number1,number2,...)
为1到30个需要求和的参数。
∙直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。
∙如果参数为数组或引用,只有其中的数字将被计算。
数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。
∙如果参数为错误值或为不能转换成数字的文本,将会导致错误。
SUMIF
根据指定条件对若干单元格求和。
SUMIF(range,criteria,sum_range)
为用于条件判断的单元格区域。
为确定哪些单元格将被相加求和的条件,其形式可以为数字、表达式或文本。
Sum_range
是需要求和的实际单元格。
∙
只有在区域中相应的单元格符合条件的情况下,sum_range中的单元格才求和。
如果忽略了sum_range,则对区域中的单元格求和。
MicrosoftExcel还提供了其他一些函数,它们可根据条件来分析数据。
例如,如果要计算单元格区域内某个文本字符串或数字出现的次数,则可使用COUNTIF函数。
如果要让公式根据某一条件返回两个数值中的某一值(例如,根据指定销售额返回销售红利),则可使用IF函数。
RANK
返回一个数字在数字列表中的排位。
数字的排位是其大小与列表中其他值的比值(如果列表已排过序,则数字的排位就是它当前的位置)。
RANK(number,ref,order)
Number
为需要找到排位的数字。
Ref
为数字列表数组或对数字列表的引用。
Ref中的非数值型参数将被忽略。
Order
为一数字,指明排位的方式。
∙如果order为0(零)或省略,MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表。
∙如果order不为零,MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表。
∙函数RANK对重复数的排位相同。
但重复数的存在将影响后续数值的排位。
例如,在一列按升序排列的整数中,如果整数10出现两次,其排位为5,则11的排位为7(没有排位为6的数值)。
∙由于某些原因,用户可能使用考虑重复数字的排位定义。
在前面的示例中,用户可能要将整数10的排位改为5.5。
这可通过将下列修正因素添加到按排位返回的值来实现。
该修正因素对于按照升序计算排位(顺序=非零值)或按照降序计算排位(顺序=0或被忽略)的情况都是正确的。
重复数排位的修正因素=[COUNT(ref)+1–RANK(number,ref,0)–RANK(number,ref,1)]/2。
在下列示例中,RANK(A2,A1:
A5,1)等于3。
修正因素是(5+1–2–3)/2=0.5,考虑重复数排位的修改排位是3+0.5=3.5。
如果数字仅在ref出现一次,由于不必调整RANK,因此修正因素为0。
SIN
返回给定角度的正弦值。
SIN(number)
为需要求正弦的角度,以弧度表示。
如果参数的单位是度,则可以乘以PI()/180或使用RADIANS函数将其转换为弧度。
PMT
基于固定利率及等额分期付款方式,返回贷款的每期付款额。
PMT(rate,nper,pv,fv,type)
有关函数PMT中参数的详细说明,请参阅函数PV。
Rate
贷款利率。
Nper
该项贷款的付款总数。
Pv
现值,或一系列未来付款的当前值的累积和,也称为本金。
Fv
为未来值,或在最后一次付款后希望得到的现金余额,如果省略fv,则假设其值为零,也就是一笔贷款的未来值为零。
Type
数字0或1,用以指定各期的付款时间是在期初还是期末。
Type值
支付时间
0或省略
期末
1
期初
说明
∙PMT返回的支付款项包括本金和利息,但不包括税款、保留支付或某些与贷款有关的费用。
∙应确认所指定的rate和nper单位的一致性。
例如,同样是四年期年利率为12%的贷款,如果按月支付,rate应为12%/12,nper应为4*12;
如果按年支付,rate应为12%,nper为4。
二、数学与三角函数
INT
将数字向下舍入到最接近的整数。
INT(number)
需要进行向下舍入取整的实数。
MOD
返回两数相除的余数。
结果的正负号与除数相同。
为被除数。
Divisor
为除数。
如果divisor为零,函数MOD返回错误值#DIV/0!
函数MOD可以借用函数INT来表示:
MOD(n,d)=n-d*INT(n/d)
PI
返回数字3.14159265358979,即数学常量pi,精确到小数点后14位。
PI()
RAND
返回大于等于0及小于1的均匀分布随机数,每次计算工作表时都将返回一个新的数值。
RAND()
∙若要生成a与b之间的随机实数,请使用:
RAND()*(b-a)+a
∙如果要使用函数RAND生成一随机数,并且使之不随单元格计算而改变,可以在编辑栏中输入“=RAND()”,保持编辑状态,然后按F9,将公式永久性地改为随机数。
ROUND
返回某个数字按指定位数取整后的数字。
ROUND(number,num_digits)
需要进行四舍五入的数字。
Num_digits
指定的位数,按此位数进行四舍五入。
∙如果num_digits大于0,则四舍五入到指定的小数位。
∙如果num_digits等于0,则四舍五入到最接近的整数。
∙如果num_digits小于0,则在小数点左侧进行四舍五入。
COMBIN
计算从给定数目的对象集合中提取若干对象的组合数。
利用函数COMBIN可以确定一组对象所有可能的组合数。
COMBIN(number,number_chosen)
Number为对象的总数量。
Numberchosen
为每一组合中对象的数量。
∙数字参数截尾取整。
∙如果参数为非数值型,则函数COMBIN返回错误值#VALUE!
∙如果number<
0、number_chosen<
0或number<
number_chosen,COMBIN返回错误值#NUM!
∙不论其内部顺序,对象组合是对象整体的任意集合或子集。
组合与排列不同,排列数与对象内部顺序有关。
FACT
返回数的阶乘,一个数的阶乘等于1*2*3*...*该数。
FACT(number)
要计算其阶乘的非负数。
如果输入的Number不是整数,则截尾取整。
POWER
返回给定数字的乘幂。
POWER(number,power)
底数,可以为任意实数。
Power
指数,底数按该指数次幂乘方。
可以用“^”运算符代替函数POWER来表示对底数乘方的幂次,例如5^2。
PRODUCT
将所有以参数形式给出的数字相乘,并返回乘积值。
PRODUCT(number1,number2,...)
为1到30个需要相乘的数字参数。
∙当参数为数字、逻辑值或数字的文字型表达式时可以被计算;
当参数为错误值或是不能转换成数字的文字时,将导致错误。
SQRT
返回正平方根。
SQRT(number)
要计算平方根的数。
如果参数Number为负值,函数SQRT返回错误值#NUM!
SUBTOTAL
返回列表或数据库中的分类汇总。
通常,使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。
一旦创建了分类汇总,就可以通过编辑SUBTOTAL函数对该列表进行修改。
SUBTOTAL(function_num,ref1,ref2,...)
Function_num
为1到11(包含隐藏值)或101到111(忽略隐藏值)之间的数字,指定使用何种函数在列表中进行分类汇总计算。
Function_num
(包含隐藏值)
(忽略隐藏值)
函数
101
2
102
3
103
COUNTA
4
104
5
105
6
106
7
107
STDEV
8
108
STDEVP
9
109
10
110
VAR
11
111
VARP
Ref1,ref2,
为要进行分类汇总计算的1到29个区域或引用。
∙如果在ref1,ref2,…中有其他的分类汇总(嵌套分类汇总),将忽略这些嵌套分类汇总,以避免重复计算。
∙当function_num为从1到11的常数时,SUBTOTAL函数将包括通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。
当您要分类汇总列表中的隐藏和非隐藏值时,请使用这些常数。
当function_num为从101到111的常数时,SUBTOTAL函数将忽略通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。
当您只分类汇总列表中的非隐藏数字时,使用这些常数。
∙SUBTOTAL函数忽略任何不包括在筛选结果中的行,不论使用什么function_num值。
∙SUBTOTAL函数适用于数据列或垂直区域。
不适用于数据行或水平区域。
例如,当function_num大于或等于101时需要分类汇总某个水平区域时,例如SUBTOTAL(109,B2:
G2),则隐藏某一列不影响分类汇总。
但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。
∙如果所指定的某一引用为三维引用,函数SUBTOTAL将返回错误值#VALUE!
SUMSQ
返回参数的平方和。
SUMSQ(number1,number2,...)
为1到30个需要求平方和的参数,也可以使用数组或对数组的引用来代替以逗号分隔的参数。
TRUNC
将数字的小数部分截去,返回整数。
TRUNC(number,num_digits)
需要截尾取整的数字。
用于指定取整精度的数字。
Num_digits的默认值为0。
函数TRUNC和函数INT类似,都返回整数。
函数TRUNC直接去除数字的小数部分,而函数INT则是依照给定数的小数部分的值,将其四舍五入到最接近的整数。
函数INT和函数TRUNC在处理负数时有所不同:
MODE
返回在某一数组或数据区域中出现频率最多的数值。
同MEDIAN一样,MODE也是一个位置测量函数。
MODE(number1,number2,...)
是用于众数计算的1到30个参数,也可以使用单一数组(即对数组区域的引用)来代替由逗号分隔的参数。
∙如果数据集合中不含有重复的数据,则MODE数返回错误值N/A。
在一组数值中,众数是出现频率最高的数值,而中位数是位于中间的值,平均数是平均后的值,所有这些求中函数都不能单独地完全描绘所有数据。
例如,假设数据分布在三个区域中,其中一半分布在一个较小数值区中,另外一半分布在两个较大数值区中。
函数AVERAGE和函数MEDIAN可能会返回位于数据点稀疏处的中间值;
而函数MODE则会返回位于数据点密集处的较小值。
SMALL
返回数据集中第k个最小值。
使用此函数可以返回数据集中特定位置上的数值。
SMALL(array,k)
Array
为需要找到第k个最小值的数组或数字型数据区域。
K
为返回的数据在数组或数据区域里的位置(从小到大)。
∙如果array为空,函数SMALL返回错误值#NUM!
∙如果k≤0或k超过了数据点个数,函数SMALL返回错误值#NUM!
∙如果n为数组中的数据点个数,则SMALL(array,1)等于最小值,SMALL(array,n)等于最大值。
TRIMMEAN
返回数据集的内部平均值。
函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点,然后再求平均值。
当希望在分析中剔除一部分数据的计算时,可以使用此函数。
TRIMMEAN(array,percent)
为需要进行整理并求平均值的数组或数值区域。
Percent
为计算时所要除去的数据点的比例,例如,如果percent=0.2,在20个数据点的集合中,就要除去4个数据点(20x0.2):
头部除去2个,尾部除去2个。
∙如果percent<
0或percent>
1,函数TRIMMEAN返回错误值#NUM!
∙函数TRIMMEAN将除去的数据点数目向下舍入为最接近的2的倍数。
如果percent=0.1,30个数据点的10%等于3个数据点。
函数TRIMMEAN将对称地在数据集的头部和尾部各除去一个数据。
三、字符串函数
&
将多个单元格字符串连接成一个字符串
用法:
numb1&
numb2&
…
LEFT
也应用于:
LEFTB
LEFT基于所指定的字符数返回文本字符串中的第一个或前几个字符。
LEFTB基于所指定的字节数返回文本字符串中的第一个或前几个字符。
此函数用于双字节字符。
LEFT(text,num_chars)
LEFTB(text,num_bytes)
Text
是包含要提取字符的文本字符串。
Num_chars
指定要由LEFT所提取的字符数。
Num_chars必须大于或等于0。
如果num_chars大于文本长度,则LEFT返回所有文本。
如果省略num_chars,则假定其为1。
Num_bytes
按字节指定要由LEFTB所提取的字符数。
EXACT
该函数测试两个字符串是否完全相同。
如果它们完全相同,则返回TRUE;
否则,返回FALSE。
函数EXACT能区分大小写,但忽略格式上的差异。
利用函数EXACT可以测试输入文档内的文本。
EXACT(text1,text2)
Text1
待比较的第一个字符串。
Text2
待比较的第二个字符串。
FIND
FINDB
FIND用于查找其他文本字符串(within_text)内的文本字符串(find_text),并从within_text的首字符开始返回find_text的起始位置编号。
也可使用SEARCH查找其他文本字符串中的某个文本字符串,但是,FIND和SEARCH不同,FIND区分大小写并且不允许使用通配符。
FINDB用于查找其他文本字符串(within_text)内的文本字符串(find_text),并基于每个字符所使用的字节数从within_text的首字符开始返回find_text的起始位置编号。
也可使用SEARCHB查找其他文本字符串中的某个文本字符串。
FIND(find_text,within_text,start_num)
FINDB(find_text,within_text,start_num)
Find_text
是要查找的文本。
Within_text
是包含要查找文本的文本。
Start_num
指定开始进行查找的字符。
within_text中的首字符是编号为1的字符。
如果忽略start_num,则假设其为1。
提示
使用start_num可跳过指定数目的字符。
例如,假定使用文本字符串“AYF0093.YoungMensApparel”,如果要查找文本字符串中说明部分的第一个“Y”的编号,则可将start_num设置为8,这样就不会查找文本的序列号部分。
FIND将从第8个字符开始查找,而在下一个字符处即可找到find_text,于是返回编号9。
FIND总是从within_text的起始处返回字符编号,如果start_num大于1,也会对跳过的字符进行计数。
∙如果find