分享

excel函数应用小技巧(添加ing)

 tigeress 2009-12-17
在EXCEL中如何把B列与A列不同之处标识出来?
(一)、如果是要求A、B两列的同一行数据相比较:
假定第一行为表头,单击A2单元格,点“格式”->“条件格式”,将条件设为:
“单元格数值”“不等于”=B2
点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。
用格式刷将A2单元格的条件格式向下复制。
B列可参照此方法设置。
(二)、如果是A列与B列整体比较(即相同数据不在同一行):
假定第一行为表头,单击A2单元格,点“格式”->“条件格式”,将条件设为:
“公式”=COUNTIF($B:$B,$A2)=0
点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。
用格式刷将A2单元格的条件格式向下复制。
B列可参照此方法设置。
按以上方法设置后,AB列均有的数据不着色,A列有B列无或者B列有A列无的数据标记为红色字体。



数字转大写金额
=SUBSTITUTE(SUBSTITUTE(IF(A2>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A2))),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A2),2),"[dbnum2]0角0分;;"&IF(ABS(A2)>1%,"整",)),"零角",IF(ABS(A2)<1,,"零")),"零分","整")
方法二:NUMBERSTRING函数
EXCEL中隐藏了一个数字转大写的函数NUMBERSTRING,但它仅支持正整数,用中文版的朋友可以试试这个函数:
=NUMBERSTRING(VALUE,TYPE)
    NumberString(1234567890,1) = 一十二亿三千四百五十六万七千八百九十
    NumberString(1234567890,2) = 壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾
    NumberString(1234567890,3) = 一二三四五六七八九○

用vlookup实现非首列查找
这里面有三种方法帮你解决该问题
=VLOOKUP(B3,IF({1,0},E2:E7,D2:D7),2,0)
或:
=VLOOKUP(B3,CHOOSE({1,2},E2:E7,D2:D7),2,0)
或:
=INDEX($D$2:$D$7,MATCH($B$3,$E$2:$E$7,0))
上例是通过在内存空间中交换E列与D列实现查找
 
 如何判断单元格里是否包含指定文本?
假定对A1单元格进行判断有无"指定文本",以下任一公式均可:
=IF(COUNTIF(A1,"*"&"指定文本"&"*")=1,"有","无")
=IF(ISERROR(FIND("指定文本",A1,1)),"无","有")
 


计算两个天数之间的年月日数
DATEDIF(start_date,end_date,unit)
Start_date 为一个日期,它代表时间段内的第一个日期或起始日期。日期有多种输入方法:带引号的文本串(例如 "2001/1/30")、系列数(例如,如果使用 1900 日期系统则 36921 代表 2001 年 1 月 30 日)或其他公式或函数的结果(例如,DATEVALUE("2001/1/30"))。有关日期系列数的详细信息,请参阅 NOW。
End_date 为一个日期,它代表时间段内的最后一个日期或结束日期。
Unit 为所需信息的返回类型。
Unit 返回
"Y" 时间段中的整年数。
"M" 时间段中的整月数。
"D" 时间段中的天数。
"MD" start_date 与 end_date 日期中天数的差。忽略日期中的月和年。
"YM" start_date 与 end_date 日期中月数的差。忽略日期中的日和年。
"YD" start_date 与 end_date 日期中天数的差。忽略日期中的年。

DATEDIF("2001/1/1","2003/1/1","Y") 等于 2,即时间段中有两个整年。
DATEDIF("2001/6/1","2002/8/15","D") 等于 440,即在 2001 年 6 月 1 日和 2002 年 8 月 15 日之间有 440 天。
DATEDIF("2001/6/1","2002/8/15","YD") 等于 75,即在 6 月 1 日与 8 月 15 日之间有 75 天,忽略日期中的年。
DATEDIF("2001/6/1","2002/8/15","MD") 等于 14,即开始日期 1 和结束日期 15 之间的差,忽略日期中的年和月。
=DATEDIF(B2,TODAY(),"y")
=DATEDIF(B2,TODAY(),"ym")
=DATEDIF(B2,TODAY(),"md")
=DATEDIF(B2,TODAY(),"y")&"年"&DATEDIF(B2,TODAY(),"ym")&"月"&DATEDIF(B2,TODAY(),"md")&"日"
 
身份证号码算年龄(来源:excel精英培训论坛,xueweil)
中国的身份证号码有两种,一种是旧的15位,一种是新的18位。
15位为:市号(3位)+ 区县号(3位)+ 出生年(后两位)+ 月(两位)+ 日(两位)+顺序号(3位)。
18位为:市号(3位)+ 区县号(3位)+ 出生年(4位)+ 月(两位)+ 日(两位)+顺序号(4位)。
摹拟一下这两种身份证号码,比如一个在1998年1月12号出生的家伙,那么他的身份证中的日期应该是这样的:
(15位,只取出生日期)000000980112000
(18位,只取出生日期)000000198401120000
DATEDIF函数可以很快的求出两个日期间的间隔,这个函数的运用已经在另一个帖子里说明:
http://excelhome.bbs./page/bbs/pages/bbstopic/getTopicById.aspx?id=429507
那我们怎么把980112这样的个东东换成日期型呢?
日期型的数值,我们可以把它当成文本形式的输入,1999-mar-13他是一个日期型,也是一个文本型的数值。
同时也可以当成小数输入,1999-3-13,把格式换成常规,就变成了36232,从这个角度说,他也可以是一个数字型的数值。

从上面的身份证号码我们可以考虑从文本型入手。如何把文本型的转换成日期型的数值?我们这里用到TEXT函数,这个函数我在另一个帖子里已经做了说明,要了解的网友可以去看一下。http://excelhome.bbs./page/bbs/pages/bbstopic/getTopicById.aspx?id=429509
也就是说,text("19980112","0000-00-00")就可以把19980112转换成1998-01-12
由此得到公式: =IF(LEN(A2)=15,DATEDIF(TEXT(MID(A2,7,6),"00-00-00"),TODAY(),"y"),IF(LEN(A2)=18,DATEDIF(TEXT(MID(A2,7,8),"0000-00-00"),TODAY(),"y"),"身份证号码有误"))
更简便的公式:(对1930年以前出生的可能会算错)
=IF(SUM(N(LEN(A1)={15,18}))=1,TEXT(NOW()-TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),"y"),"身份证号码有误")
 

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多