excel常见函数使用.docx
- 文档编号:9324721
- 上传时间:2023-02-04
- 格式:DOCX
- 页数:18
- 大小:39.21KB
excel常见函数使用.docx
《excel常见函数使用.docx》由会员分享,可在线阅读,更多相关《excel常见函数使用.docx(18页珍藏版)》请在冰豆网上搜索。
excel常见函数使用
ETWORKDAYS.INTL函数
返回两个日期之间的所有工作日数,使用参数指示哪些天是周末,以及有多少天是周末。
周末和任何指定为假期的日期不被视为工作日。
语法
NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])
NETWORKDAYS.INTL函数语法具有以下参数:
©start_date和end_date必需。
要计算其差值的日期。
start_date可以早于或晚于end_date,也可以与它相同。
©weekend可选。
表示介于start_date和end_date之间但又不包括在所有工作日数中的周末日。
weekend是周末数值或字符串,用于指定周末时间。
Øweekend数值表示以下周末日:
周末数周末日
1或省略星期六、星期日
2星期日、星期一
3星期一、星期二
4星期二、星期三
5星期三、星期四
6星期四、星期五
7星期五、星期六
11仅星期日
12仅星期一
13仅星期二
14仅星期三
15仅星期四
16仅星期五
17仅星期六
Øweekend字符串值为7个字符长,该字符串中的每个字符代表一周中的一天,从星期一开始。
1代表非工作日,0代表工作日。
该字符串中只允许使用字符1和0。
使用1111111将始终返回0。
例如,0000011得到的结果是星期六和星期日为周末。
©holidays可选。
一个包含一个或多个日期的可选集合,这些日期将从工作日日历中排除。
假期应该是包含日期的单元格区域,也可以是代表这些日期的序列值的数组常量。
假期中的日期或序列值的顺序可以是任意的。
说明
©如果start_date晚于end_date,则返回值将为负数,数量将是所有工作日的数量。
©如果start_date在当前日期基准值的范围之外,则NETWORKDAYS.INTL返回错误值#NUM!
。
©如果end_date在当前日期基准值的范围之外,则NETWORKDAYS.INTL返回错误值#NUM!
。
©如果weekend字符串的长度无效或包含无效字符,则NETWORKDAYS.INTL返回错误值#VALUE!
。
示例
公式说明
=NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,1,31))结果为22(31天;5个星期六,4个星期日)
=NETWORKDAYS.INTL(DATE(2006,1,31),DATE(2006,1,1))结果为-22
=NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,2,1),7,{"2006/1/2","2006/1/16"})结果为22(32天;4个星期五,4个星期六,2个假日)
=NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,2,1),"0000110",{"2006/1/2","2006/1/16"})结果为22
WEEKDAY函数
返回某日期为星期几。
默认情况下,其值为1(星期天)到7(星期六)之间的整数。
语法
WEEKDAY(serial_number,[return_type])
WEEKDAY函数语法具有下列参数:
©Serial_number必需。
一个序列号,代表尝试查找的那一天的日期。
应使用DATE函数输入日期,或者将日期作为其他公式或函数的结果输入。
例如,使用函数DATE(2008,5,23)输入2008年5月23日。
如果日期以文本形式输入,则会出现问题。
©Return_type可选。
用于确定返回值类型的数字。
RETURN_TYPE返回的数字
1或省略数字1(星期日)到数字7(星期六),同MicrosoftExcel早期版本。
2数字1(星期一)到数字7(星期日)。
3数字0(星期一)到数字6(星期日)。
11数字1(星期一)到数字7(星期日)。
12数字1(星期二)到数字7(星期一)。
13数字1(星期三)到数字7(星期二)。
14数字1(星期四)到数字7(星期三)。
15数字1(星期五)到数字7(星期四)。
16数字1(星期六)到数字7(星期五)。
17数字1(星期日)到7(星期六)。
说明
©MicrosoftExcel可将日期存储为可用于计算的序列数。
默认情况下,1900年1月1日的序列号是1,而2008年1月1日的序列号是39448,这是因为它距1900年1月1日有39448天。
©如果serial_number不在当前日期基数值范围内,则返回#NUM!
错误。
©如果return_type不在上述表格中指定的范围内,则返回#NUM!
错误。
示例
数据
2008-2-14
公式说明(结果)
=WEEKDAY(A2)具有数字1(星期日)到数字7(星期六)的星期号(5)
=WEEKDAY(A2,2)具有数字1(星期一)到数字7(星期日)的星期号(4)
=WEEKDAY(A2,3)具有数字0(星期一)到数字6(星期日)的星期号(3)
注释2008-2-14为星期四。
YEARFRAC函数
返回start_date和end_date之间的天数占全年天数的百分比。
使用YEARFRAC工作表函数可判别某一特定条件下全年效益或债务的比例。
语法
YEARFRAC(start_date,end_date,[basis])
YEARFRAC函数语法具有下列参数:
©Start_date必需。
一个代表开始日期的日期。
©End_date必需。
一个代表终止日期的日期。
©Basis可选。
要使用的日计数基准类型。
BASIS日计数基准
0或省略US(NASD)30/360
1实际天数/实际天数
2实际天数/360
3实际天数/365
4欧洲30/360
要点应使用DATE函数输入日期,或者将函数作为其他公式或函数的结果输入。
例如,使用函数DATE(2008,5,23)输入2008年5月23日。
如果日期以文本形式输入,则会出现问题。
ROMAN函数
将阿拉伯数字转换为文本形式的罗马数字。
语法
ROMAN(number,[form])
ROMAN函数语法具有下列参数:
©Number必需。
需要转换的阿拉伯数字。
©Form可选。
一数字,指定所需的罗马数字类型。
罗马数字的样式范围可以从经典到简化,随着form值的增加趋于简单。
请参见下面的示例ROMAN(499,0)。
Form类型
0或省略经典。
1更简明。
请见下例。
2更简明。
请见下例。
3更简明。
请见下例。
4简化。
TRUE经典。
FALSE简化。
说明
©如果数字为负,则返回错误值#VALUE!
。
©如果数字大于3999,则返回错误值#VALUE!
。
ROUND函数
ROUND函数可将某个数字四舍五入为指定的位数。
例如,如果单元格A1含有23.7825并且希望将该数字四舍五入为小数点后两位,则可以使用以下公式:
=ROUND(A1,2)
此函数的结果为23.78。
语法
ROUND(number,num_digits)
ROUND函数语法具有下列参数:
©number必需。
要四舍五入的数字。
©num_digits必需。
位数,按此位数对number参数进行四舍五入。
说明
©如果num_digits大于0(零),则将数字四舍五入到指定的小数位。
©如果num_digits等于0,则将数字四舍五入到最接近的整数。
©如果num_digits小于0,则在小数点左侧进行四舍五入。
©若要始终进行向上舍入(远离0),请使用ROUNDUP函数。
若要始终进行向下舍入(朝向0),请使用ROUNDDOWN函数。
©若要将某个数字四舍五入为指定的倍数(例如,四舍五入为最接近的0.5倍),请使用MROUND函数。
示例
公式说明结果
=ROUND(2.15,1)将2.15四舍五入到一个小数位2.2
=ROUND(2.149,1)将2.149四舍五入到一个小数位2.1
=ROUND(-1.475,2)将-1.475四舍五入到两个小数位-1.48
=ROUND(21.5,-1)将21.5四舍五入到小数点左侧一位20
CEILING函数
将参数Number向上舍入(沿绝对值增大的方向)为最接近的significance的倍数。
例如,如果您不愿意使用像―分‖这样的零钱,而所要购买的商品价格为¥4.42,可以用公式=CEILING(4.42,0.05)将价格向上舍入为以―角‖表示。
语法
CEILING(number,significance)
CEILING函数语法具有下列参数:
©Number必需。
要舍入的值。
©Significance必需。
要舍入到的倍数。
说明
©如果参数为非数值型,CEILING返回错误值#VALUE!
。
©无论数字符号如何,都按远离0的方向向上舍入。
如果数字已经为Significance的倍数,则不进行舍入。
©如果number和significance都为负,则对值按远离0的方向进行向下舍入。
©如果number为负,significance为正,则对值按朝向0的方向进行向上舍入。
示例
公式说明(结果)
=CEILING(2.5,1)将2.5向上舍入到最接近的1的倍数(3)
=CEILING(-2.5,-2)将-2.5向上舍入到最接近的-2的倍数(-4)
=CEILING(-2.5,2)将-2.5向上舍入为最接近的2的倍数(-2)
=CEILING(1.5,0.1)将1.5向上舍入到最接近的0.1的倍数(1.5)
=CEILING(0.234,0.01)将0.234向上舍入到最接近的0.01的倍数(0.24)
把当天日期转化为季度:
=CEILING((MONTH(TODAY())/3),1)
DATEDIF函数
两个日期之间的间隔。
DATEDIF(起始日期start_date,结束日期end_date,返回类型Unit)
返回值类型:
"Y"时间段中的整年数,"M"时间段中的整月数,"D"时间段中的天数;"MD"日期中天数的差,忽略日期中的月和年;"YM"日期中月数的差,忽略日期中的年;"YD"日期中天数的差,忽略日期中的年。
例:
=DATEDIF(A1,TODAY(),"Y")
VALUE函数
将代表数字的文本字符串转换成数字。
语法
VALUE(text)
VALUE函数语法具有以下参数:
©Text必需。
带引号的文本,或对包含要转换文本的单元格的引用。
说明
©Text可以是MicrosoftExcel中可识别的任意常数、日期或时间格式。
如果Text不为这些格式,则函数VALUE返回错误值#VALUE!
。
©通常不需要在公式中使用函数VALUE,Excel可以自动在需要时将文本转换为数字。
提供此函数是为了与其他电子表格程序兼容。
示例
公式说明(结果)
=VALUE("$1,000")字符串的等价数字(1000)
=VALUE("16:
48:
00")-VALUE("12:
00:
00")等价于4小时48分钟的序列号,由16:
48:
00减去12:
00:
00得到(0.2或4:
48)
注释若要将数字显示为时间,请选择单元格,然后在―开始‖选项卡上的―数字‖组中,单击―数字格式‖旁边的箭头,然后单击―时间‖
TEXT函数
TEXT函数可将数值转换为文本,并可使用户通过使用特殊格式字符串来指定显示格式。
需要以可读性更高的格式显示数字或需要合并数字、文本或符号时,此函数很有用。
例如,假设单元格A1含有数字23.5。
若要将数字格式设置为人民币金额,可以使用
以下公式:
=TEXT(A1,"¥0.00")
在本例中,Excel会显示¥23.50。
也可以使用功能区上―开始‖选项卡上的―数字‖组中的命令来设置数字格式。
但是,只有整个单元格都为数字时,这些命令才起作用。
如果需要设置数字格式并将其与其他文本合并,使用TEXT函数是最佳选择。
例如,可以向前一个公式中添加文本:
=TEXT(A1,"¥0.00")&"每小时"
Excel会显示¥23.50每小时。
语法
TEXT(value,format_text)
TEXT函数语法具有以下参数:
©value必需。
数值、计算结果为数值的公式,或对包含数值的单元格的引用。
©format_text必需。
使用双引号括起来作为文本字符串的数字格式,例如,"m/d/yyyy"或"#,##0.00"
LEFT、LEFTB函数
根据所指定的字符数,LEFT返回文本字符串中第一个字符或前几个字符。
LEFTB基于所指定的字节数返回文本字符串中的第一个或前几个字符。
LEFT(text,[num_chars])
LEFTB(text,[num_bytes])
LEFT和LEFTB函数语法具有下列参数:
©Text必需。
包含要提取的字符的文本字符串。
©Num_chars可选。
指定要由LEFT提取的字符的数量。
ØNum_chars必须大于或等于零。
Ø如果num_chars大于文本长度,则LEFT返回全部文本。
Ø如果省略num_chars,则假设其值为1。
©Num_bytes可选。
按字节指定要由LEFTB提取的字符的数量。
示例
LEFTB(将您的计算机设置为支持DBCS的一种默认语言)
在下面的示例中:
LEFTB返回前两个字符,因为每个字符按2计数。
LEFT返回前4个字符,因为每个字符按1计数。
无论您计算机上的默认语言设置如何,函数LEFT都返回前4个字符。
=LEFTB("北京天安门",4)等于―北京
=LEFT("北京天安门",4)等于―北京天安
MID、MIDB函数
MID返回文本字符串中从指定位置开始的特定数目的字符,该数目由用户指定。
MIDB根据您指定的字节数,返回文本字符串中从指定位置开始的特定数目的字符。
MID(截取目标text,截取起始字符位start_num,截取字节数num_chars)
实例
数据
FluidFlow
公式说明(结果)
=MID(A2,1,5)上面字符串中的5个字符,从第一个字符开始(Fluid)
=MID(A2,7,20)上面字符串中的20个字符,从第七个字符开始(Flow)
=MID(A2,20,5)因为要提取的第一个字符的位置大于字符串的长度,所以返回空文本()
COUNTIF函数
COUNTIF函数对区域中满足单个指定条件的单元格进行计数。
COUNTIF(range,criteria)
COUNTIF函数语法具有下列参数:
©range必需。
要对其进行计数的一个或多个单元格,其中包括数字或名称、数组或包含数字的引用。
空值和文本值将被忽略。
©criteria必需。
用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或文本字符串。
Sumif函数
sumif使用SUMIF函数可以对区域中符合指定条件的值求和。
sumif(条件区域range,求和条件Criteria,实际求和区域Sum_range)
SUMIF函数语法具有以下参数:
range必需。
用于条件计算的单元格区域。
每个区域中的单元格都必须是数字或名称、数组或包含数字的引用。
空值和文本值将被忽略。
criteria必需。
用于确定对哪些单元格求和的条件,其形式可以为数字、表达式、单元格引用、文本或函数。
例如,条件可以表示为32、">32"、B5、32、"32"、"苹果"或TODAY()。
要点任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号(")括起来。
如果条件为数字,则无需使用双引号。
sum_range可选。
要求和的实际单元格(如果要对未在range参数中指定的单元格求和)。
如果sum_range参数被省略,Excel会对在range参数中指定的单元格(即应用条件的单元格)求和。
IF函数
IF函数如果指定条件的计算结果为TRUE,IF函数将返回某个值;如果该条件的计算结果为FALSE,则返回另一个值。
IF(logical_test,[value_if_true],[value_if_false])
IF函数语法具有下列参数:
logical_test必需。
计算结果可能为TRUE或FALSE的任意值或表达式。
value_if_true可选。
value_if_false可选。
说明
最多可以使用64个IF函数作为value_if_true和value_if_false参数进行嵌套,以构造更详尽的测试。
示例
数据
5023
公式说明结果
=IF(A2<=100,"预算内","超出预算")
如果单元格A2中的数字小于等于100,公式将返回―预算内;否则,函数显示―超
出预算。
=IF(A2=100,A2+B2,"")如果单元格A2中的数字为100,则计算并返回A2与B2的和;否则,返回空文本("")。
示例2
实际费用预期费用
1500900
500900
500925
公式说明结果
=IF(A2>B2,"超出预算","OK")检查第2行的费用是否超出预算超出预算
=IF(A3>B3,"超出预算","OK")检查第3行的费用是否超出预算OK
示例3
分数
45
90
78
公式说明结果
=IF(A2>89,"A",IF(A2>79,"B",IF(A2>69,"C",IF(A2>59,"D","F"))))给单元格A2中的分数指定一个字母等级F
上面的示例演示了如何嵌套IF语句。
在每个公式中,第四个IF语句同时也是第三个IF语句的value_if_false参数。
同样,第三个IF语句是第二个IF语句的value_if_false参数,第二个IF语句是第一个IF语句的value_if_false参数。
例如,如果第一个logical_test参数(Average>89)的计算结果为TRUE,则返回―A‖;如果第一个logical_test参数的计算结果为FALSE,则计算第二个IF语句,依此类推。
您也可以将其他函数用作参数。
MATCH函数
MATCH函数可在单元格区域中搜索指定项,然后返回该项在单元格区域中的相对位置。
MATCH(需查找的值lookup_value,查找范围lookup_array,查找方式match_type)
查找方式:
为1时,查找小于或等于lookup_value的最大数值在lookup_array中的位置,lookup_array必须按升序排列:
为0时,查找等于lookup_value的第一个数值,lookup_array按任意顺序排列:
为-1时,查找大于或等于lookup_value的最小数值在lookup_array中的位置,lookup_array必须按降序排列。
利用MATCH函数查找功能时,当查找条件存在时,MATCH函数结果为具体位置(数值),否则显示#N/A错误。
产品数量
香蕉25
橙子38
苹果40
香梨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
Vlookup函数
vlookup函数搜索某个单元格区域的第一列,然后返回该区域相同行上任何单元格中的值。
vlookup(查找值lookup_value,查找区域table_array,查找列数col_index_num,false)
一般使用精确查找fasle
密度粘度温度
0.4573.55500
0.5253.25400
0.6062.93300
0.6752.75250
0.7462.57200
0.8352.38150
Excel公式释义及示例大全(2010版)
-270-
0.9462.17100
1.091.9550
1.291.710
公式说明结果
=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(0.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
SUBTOTAL函数
返回列表或数据库中的分类汇总。
通常,使用“数据”选项卡上“大纲”组中的“分类汇总”命令更便于创建带有分类汇总的列表。
一旦创建了分类汇总列表,就可以通过编辑SUBTOTAL函数对该列表进行修改。
语法
SUBTOTAL(function_num,ref1,[ref2],...])
SUBTOTAL函数语法具有以下参数:
Function_num为1到11(包含隐藏值)或101到111(忽略隐藏值)之间的数字,指定使用何种函数在列表中进行分类汇总计算。
ref1……refn参数为要对其进行分类汇总计算的第1至29个命名区域或引用。
必须是对单元格区域的引用。
Function_num(包
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 常见 函数 使用
![提示](https://static.bdocx.com/images/bang_tan.gif)