EXCEL中比较常用的的函数.docx
- 文档编号:2412504
- 上传时间:2022-10-29
- 格式:DOCX
- 页数:23
- 大小:773.01KB
EXCEL中比较常用的的函数.docx
《EXCEL中比较常用的的函数.docx》由会员分享,可在线阅读,更多相关《EXCEL中比较常用的的函数.docx(23页珍藏版)》请在冰豆网上搜索。
EXCEL中比较常用的的函数
EXCEL中比较常用的的函数
函数DATEDIF
1、简要说明:
返回两个日期之间的年\月\日间隔数
2、基本语法:
=DATEDIF(开始日期,结束日期,单位代码)
3、实例1:
题目:
计算出生日期为1973-4-1人的年龄
公式:
=DATEDIF("1973-4-1",TODAY(),"Y")
结果:
33
简要说明TODAY()为系统时间,结束时间也可是某一单元格或一固定时间。
当单位代码为"Y"时,计算结果是两个日期间隔的年数。
、
当单位代码为"M"时,计算结果是两个日期间隔的月份数即公式为=DATEDIF("1973-4-1",TODAY(),"M")。
当单位代码为"D"时,计算结果是两个日期间隔的天数即公式为=DATEDIF("1973-4-1",TODAY(),"D")、
当单位代码为"YD"时,计算结果是忽略年数差两个日期间隔的天数,
即公式为=DATEDIF("1973-4-1",TODAY(),"D")
当单位代码为"MD"时,计算结果是两个日期间隔的天数.忽略年数和月份之差。
当单位代码为"YM"时,计算结果是不计年份的间隔月份数.
将小写金额转换为大写金额
=IF(INT(D11)=0,"",TEXT(INT(D11),"[DBNum2]")&"")&IF(OR(INT(D11*10)=0,INT(D11)=D11),"",IF(INT(D11*10)=INT(D11)*10,"零",RIGHT(TEXT(INT(D11*10),"[DBNum2]"),1)&""))&IF(INT(D11*100)=INT(D11*10)*10,"整",RIGHT(TEXT(ROUND(D11,2),"[DBNum2]"),1)&"分")
函数SUBTOTAL
返回列表或数据库中的分类汇总。
通常,使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。
一旦创建了分类汇总,就可以通过编辑SUBTOTAL函数对该列表进行修改。
语法
SUBTOTAL(function_num,ref1,ref2,...)[=SUBTOTAL(分类汇总中使用的函数代码,数据区域)]
Function_num 为1到11(包含隐藏值)或101到111(忽略隐藏值)之间的数字,指定使用何种函数在列表中进行分类汇总计算。
Function_num
(包含隐藏值)
Function_num
(忽略隐藏值)
函数
例子
1
101
AVERAGE――平均数
2
102
COUNT―――包含数据单元格的个数
3
103
COUNTA-----非空格的个数
序号=SUBTOTAL(103,$B$3:
B3)
4
104
MAX――――最大值
5
105
MIN――――最小值
6
106
PRODUCT――乘积
7
107
STDEV
8
108
STDEVP
9
109
SUM――――合计数
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!
。
SUBTOTAL(分类汇总中使用的函数代码,数据区域)
举例
数据表
A列B列C列D列
姓名语文数学英语
张三959488
……
共56条记录,
求语文的平均分,则公式
=SUBTOTAL(1,B2:
B57)
注意:
参数“1”表示分类汇总使用的平均值函数AVERAGE(参见函数帮助)。
公式等价于公式=AVERAGE(B2:
B57)。
求语文的最高分,则公式
=SUBTOTAL(4,B2:
B57)
注意:
参数“4”表示分类汇总使用的最大值函数MAX(参见函数帮助)
公式等价于公式=MAX(B2:
B57)
设置有效性条件验证
假设G列为员工“身份证号”字段,G2单元格为第一个员工的身份证号码所在的单元格。
在未输入之前,我们可先设置该列的有效性条件来确保该列数据的惟一性。
选中G2单元格,单击“数据”菜单中的“有效性”命令,弹出“数据有效性”对话框,选择“设置”选项卡,在“允许”下拉列表中选择“自定义”,在“公式”框内输入“=COUNTIF(G:
G,G2)=1”(公式内所有的字符使用半角英文,不包括双引号,如图1所示)。
设置出错警告提示信息
设置出错警告提示信息的目的在于提醒用户正确输入数据。
具体步骤是:
单击“数据有效性”对话框中的“出错警告”选项卡,在“标题”框内输入“数据输入错误”,在“错误信息”框内输入“你刚才输入的数据已经存在,请检查数据的惟一性!
”。
设置完之后,单击“确定”按钮(如图2所示)。
至此,已经设置了G2单元格的有效性条件验证和出错提示信息。
为了将这个设置应用到整个G列(除了字段名称所在的单元格即G1单元格),可用填充柄工具向下拖动将公式复制到G列其他的单元格。
输入身份证信息
以上设置完成之后我们就可以向G列中输入员工的身份证号了。
每输入一个员工的身份证号,Excel会自动对该数据进行有效性验证,如果该数据已经存在,系统将弹出出错警告提示框,如图3所示。
上述功能只能验证数据的惟一性,若数据位数输入错误,系统则检测不出这一错误。
若在输入时需要同时验证数据的位数,还是以身份证号为例,可将图一中的公式改为“=AND(COUNTIF(G:
G,G2)=1,OR(LEN(G2)=15,LEN(G2)=18))”,图二中的错误信息改为“请检查数据的惟一性或输入数据位数错!
”。
设置完后重新复制G2单元格的公式至G列其他的单元格。
该公式的含义是:
在G列输入的数据必须是惟一的且数据位数必须是15位或18位。
最后还需要提醒大家,由于G列输入的是身份证号,位数超过了11位数据,所以最好在输入数据之间,选将G列全部选定,设置“单元格格式”中的“数字分类”格式为“文本”格式,这样才能保证身份证号以正确形式输入。
CONCATENATE
主要功能:
将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。
使用格式:
CONCATENATE(Text1,Text……)
参数说明:
Text1、Text2……为需要连接的字符文本或引用的单元格。
应用举例:
在C14单元格中输入公式:
=CONCATENATE(A14,"@",B14,".com"),确认后,即可将A14单元格中字符、@、B14单元格中的字符和.com连接成一个整体,显示在C14单元格中。
特别提醒:
如果参数不是引用的单元格,且为文本格式的,请给参数加上英文状态下的双引号,如果将上述公式改为:
=A14&"@"&B14&".com",也能达到相同的目的。
将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中也可用以下方式实现:
=Text1&Text1&Text……,Text表示单元格或文本或数值
COUNTIF
主要功能:
统计某个单元格区域中符合指定条件的单元格数目。
使用格式:
COUNTIF(Range,Criteria)
参数说明:
Range代表要统计的单元格区域;Criteria表示指定的条件表达式。
应用举例:
在C17单元格中输入公式:
=COUNTIF(B1:
B13,">=80"),确认后,即可统计出B1至B13单元格区域中,数值大于等于80的单元格数目。
特别提醒:
允许引用的单元格区域中有空白单元格出现。
VLOOKUP
VLOOKUP函数
在表格或数值数组的首列查找指定的数值,并由此返回表格或数组中该数值所在行中指定列处的数值。
这里所说的“数组”,可以理解为表格中的一个区域。
数组的列序号:
数组的“首列”,就是这个区域的第一纵列,此列右边依次为第2列、3列……。
假定某数组区域为B2:
E10,那么,B2:
B10为第1列、C2:
C10为第2列……。
语法:
VLOOKUP(查找值,区域,列序号,逻辑值)
“查找值”:
为需要在数组第一列中查找的数值,它可以是数值、引用或文字符串。
“区域”:
数组所在的区域,如“B2:
E10”,也可以使用对区域或区域名称的引用,例如数据库或数据清单。
“列序号”:
即希望区域(数组)中待返回的匹配值的列序号,为1时,返回第一列中的数值,为2时,返回第二列中的数值,以此类推;若列序号小于1,函数VLOOKUP返回错误值#VALUE!
;如果大于区域的列数,函数VLOOKUP返回错误值#REF!
。
“逻辑值”:
为TRUE或FALSE。
它指明函数VLOOKUP返回时是精确匹配还是近似匹配。
如果为TRUE或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则返回小于“查找值”的最大数值;如果“逻辑值”为FALSE,函数VLOOKUP将返回精确匹配值。
如果找不到,则返回错误值#N/A。
如果“查找值”为文本时,“逻辑值”一般应为FALSE。
另外:
·如果“查找值”小于“区域”第一列中的最小数值,函数VLOOKUP返回错误值#N/A。
·如果函数VLOOKUP找不到“查找值”且“逻辑值”为FALSE,函数VLOOKUP返回错误值#N/A。
下面举例说明VLOOKUP函数的使用方法。
假设在Sheet1中存放小麦、水稻、玉米、花生等若干农产品的销售单价:
A B
1 农产品名称 单价
2 小麦 0.56
3 水稻 0.48
4 玉米 0.39
5 花生 0.51
…………………………………
100 大豆 0.45
Sheet2为销售清单,每次填写的清单内容不尽相同:
要求在Sheet2中输入农产品名称、数量后,根据Sheet1的数据,自动生成单价和销售额。
设下表为Sheet2:
A B C D
1 农产品名称 数量 单价 金额
2 水稻 1000 0.48 480
3
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 比较 常用 函数