九九淘商城:Excel在学籍信息管理中的妙用_有关技巧

我设定一个时间,在表格里,想知道它与今天还相差几天怎么弄,如:我有个工作计划是2007-10-1日完成,要知道今天离完成日期还有几天?

A1 = 2007-10-1任何一格输入公式:=datedif(today(),a1,"d") 

在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.pepo.cn/page/bbs/pages/bbstopic/getTopicById.aspx?id=429507
那我们怎么把980112这样的个东东换成日期型呢？
日期型的数值，我们可以把它当成文本形式的输入，1999-mar-13他是一个日期型，也是一个文本型的数值。
同时也可以当成小数输入,1999-3-13,把格式换成常规，就变成了36232，从这个角度说，他也可以是一个数字型的数值。

从上面的身份证号码我们可以考虑从文本型入手。如何把文本型的转换成日期型的数值？我们这里用到TEXT函数，这个函数我在另一个帖子里已经做了说明，要了解的网友可以去看一下。http://excelhome.bbs.pepo.cn/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"),"身份证号码有误")