文本日期时间函数

1、Exceel函数数应用之之文本/日期/时间函函数（陆陆元婕20001年006月005日 09:40）编者语语：Exxcell是办公公室自动动化中非非常重要要的一款款软件，很多巨巨型国际际企业都都是依靠靠Exccel进进行数据据管理。它不仅仅仅能够够方便的的处理表表格和进进行图形形分析，其更强强大的功功能体现现在对数数据的自自动处理理和计算算，然而而很多缺缺少理工工科背景景或是对对Exccel强强大数据据处理功功能不了了解的人人却难以以进一步步深入。编者以以为，对对Exccel函函数应用用的不了了解正是是阻挡普普通用户户完全掌掌握Exxcell的拦路路虎，然然而目前前这一部部份内容容的教学学文章

2、却却又很少少见，所所以特别别组织了了这一个个Exxcell函数应应用系系列，希希望能够够对Exxcell进阶者者有所帮帮助。Exccel函函数应用用系列列，将每每周更新新，逐步步系统的的介绍EExceel各类类函数及及其应用用，敬请请关注！所谓文文本函数数，就是是可以在在公式中中处理文文字串的的函数。例如，可以改改变大小小写或确确定文字字串的长长度；可可以替换换某些字字符或者者去除某某些字符符等。而而日期和和时间函函数则可可以在公公式中分分析和处处理日期期值和时时间值。关于这这两类函函数的列列表参看看附表，这里仅仅对一些些常用的的函数做做简要介介绍。一一、文本本函数（一）大大小写转转换LOOW

3、ERR-将将一个文文字串中中的所有有大写字字母转换换为小写写字母。UPPPER-将文文本转换换成大写写形式。PROOPERR-将将文字串串的首字字母及任任何非字字母字符符之后的的首字母母转换成成大写。将其余余的字母母转换成成小写。这三种种函数的的基本语语法形式式均为 函数名名（teext）。示例例说明：已有字字符串为为：pLLeasse CComEE Heere! 可以以看到由由于输入入的不规规范，这这句话大大小写乱乱用了。通过以以上三个个函数可可以将文文本转换换显示样样式，使使得文本本变得规规范。参参见图11Lowwer（pLeeasee CoomE Herre!）= ppleaase co

4、mme hheree!uppperr（pLLeasse CComEE Heere!）= PLEEASEE COOME HERRE!ppropper（pLeeasee CoomE Herre!）= PPleaase Comme HHeree! 图1（二）取取出字符符串中的的部分字字符 HYPERLINK /school/office/2001/06/01/70_4355.html Excel函数应用之逻辑函数 HYPERLINK /school/office/2001/05/23/70_4263.html Excel函数应用之数学和三角函数 HYPERLINK /school/office/20

5、01/05/23/70_4262.html Excel函数应用之函数简介您可以使使用Miid、LLeftt、Riightt等函数数从长字字符串内内获取一一部分字字符。具具体语法法格式为为LEFFT函数数：LEEFT(texxt,nnum_chaars)其中TTextt是包含含要提取取字符的的文本串串。Nuum_ccharrs指定定要由 LEFFT 所所提取的的字符数数。MIID函数数：MIID(ttextt,sttartt_nuum,nnum_chaars)其中TTextt是包含含要提取取字符的的文本串串。Sttartt_nuum是文文本中要要提取的的第一个个字符的的位置。RIGGHT函函数：

6、RRIGHHT(ttextt,nuum_ccharrs)其其中Teext是是包含要要提取字字符的文文本串。Numm_chharss指定希希望 RRIGHHT 提提取的字字符数。比如，从字符符串TThiss iss ann appplee.分分别取出出字符Thiis、appplee、is的具体体函数写写法为。LEFFT(Thiis iis aan aapplle,4)=ThiisRIIGHTT(TThiss iss ann appplee,55)=aapplleMIID(Thiis iis aan aapplle,6,22)=iis 图2（三）去去除字符符串的空空白在字字符串形形态中，空白也也是

7、一个个有效的的字符，但是如如果字符符串中出出现空白白字符时时，容易易在判断断或对比比数据是是发生错错误，在在Exccel中中您可以以使用TTrimm函数清清除字符符串中的的空白。语法形形式为：TRIIM(ttextt)其中中Texxt为需需要清除除其中空空格的文文本。需需要注意意的是，Triim函数数不会清清除单词词之间的的单个空空格，如如果连这这部分空空格都需需清除的的话，建建议使用用替换功功能。比比如，从从字符串串Myy naame is Marry中中清除空空格的函函数写法法为：TTRIMM(MMy nnamee iss Maary)=MMy nnamee iss Maary 参见图图3

8、 图3（四）字字符串的的比较在在数据表表中经常常会比对对不同的的字符串串，此时时您可以以使用EEXACCT函数数来比较较两个字字符串是是否相同同。该函函数测试试两个字字符串是是否完全全相同。如果它它们完全全相同，则返回回 TRRUE；否则，返回 FALLSE。函数 EXAACT 能区分分大小写写，但忽忽略格式式上的差差异。利利用函数数 EXXACTT 可以以测试输输入文档档内的文文字。语语法形式式为：EEXACCT(ttextt1,ttextt2)TTextt1为待待比较的的第一个个字符串串。Teext22为待比比较的第第二个字字符串。举例说说明：参参见图44EXAACT(Chhinaa,ch

9、iina)=FFalsse 图4二、日期期与时间间函数在在数据表表的处理理过程中中，日期期与时间间的函数数是相当当重要的的处理依依据。而而Exccel在在这方面面也提供供了相当当丰富的的函数供供大家使使用。（一）取取出当前前系统时时间/日日期信息息用于取取出当前前系统时时间/日日期信息息的函数数主要有有NOWW、TOODAYY。语法法形式均均为 函函数名（）。（二）取取得日期期/时间间的部分分字段值值如果需需要单独独的年份份、月份份、日数数或小时时的数据据时，可可以使用用HOUUR、DDAY、MONNTH、YEAAR函数数直接从从日期/时间中中取出需需要的数数据。具具体示例例参看图图5。比比如

10、，需需要返回回20001-55-300 122:300 PMM的年份份、月份份、日数数及小时时数，可可以分别别采用相相应函数数实现。YEAAR(EE5)=20001MOONTHH(E55)=55DAYY(E55)=330HOOUR(E5)=122 图5此外还有有更多有有用的日日期/时时间函数数，可以以查阅附附表。下下面我们们将以一一个具体体的示例例来说明明Exccel的的文本函函数与日日期函数数的用途途。三、示例：做一个个美观简简洁的人人事资料料分析表表1、 示例说说明在如如图6所所示的某某公司人人事资料料表中，除了编编号、员员工姓名名、身份份证号码码以及参参加工作作时间为为手工添添入外，其余

11、各各项均为为用函数数计算所所得。图6在此例中中我们将将详细说说明如何何通过函函数求出出：（11）自动动从身份份证号码码中提取取出生年年月、性性别信息息。（22）自动动从参加加工作时时间中提提取工龄龄信息。2、身身份证号号码相关关知识在在了解如如何实现现自动从从身份证证号码中中提取出出生年月月、性别别信息之之前，首首先需要要了解身身份证号号码所代代表的含含义。我我们知道道，当今今的身份份证号码码有155/188位之分分。早期期签发的的身份证证号码是是15位位的，现现在签发发的身份份证由于于年份的的扩展（由两位位变为四四位）和和末尾加加了效验验码，就就成了118位。这两种种身份证证号码将将在相当当

12、长的一一段时期期内共存存。两种种身份证证号码的的含义如如下：（1）115位的的身份证证号码：166位为地地区代码码，78位为为出生年年份(22位)，9110位为为出生月月份，111112位为为出生日日期，第第1315位位为顺序序号，并并能够判判断性别别，奇数数为男，偶数为为女。（2）118位的的身份证证号码：166位为地地区代码码，710位位为出生生年份(4位)，111122位为出出生月份份，133144位为出出生日期期，第115117位为为顺序号号，并能能够判断断性别，奇数为为男，偶偶数为女女。188位为效效验位。3、 应用函函数在此此例中为为了实现现数据的的自动提提取，应应用了如如下几个个

13、Exccel函函数。（1）IIF函数数：根据据逻辑表表达式测测试的结结果，返返回相应应的值。IF函函数允许许嵌套。语法形形式为：IF（loggicaal_ttestt, vvaluue_iif_ttruee,vaaluee_iff_faalsee）（22）COONCAATENNATEE：将若若干个文文字项合合并至一一个文字字项中。语法形形式为：CONNCATTENAATE(texxt1,texxt2)（3）MMID：从文本本字符串串中指定定的起始始位置起起，返回回指定长长度的字字符。语语法形式式为：MMID(texxt,sstarrt_nnum,numm_chharss)（44）TOODAYY

14、：返回回计算机机系统内内部的当当前日期期。语法法形式为为：TOODAYY（）（5）DDATEEDIFF：计算算两个日日期之间间的天数数、月数数或年数数。语法法形式为为：DAATEDDIF(staart_datte,eend_datte,uunitt)（66）VAALUEE：将代代表数字字的文字字串转换换成数字字。语法法形式为为：VAALUEE(teext)（7）RIGGHT：根据所所指定的的字符数数返回文文本串中中最后一一个或多多个字符符。语法法形式为为：RIIGHTT(teext,numm_chharss)（88）INNT：返返回实数数舍入后后的整数数值。语语法形式式为：IINT(nummb

15、err)4、 公式式写法及及解释（以员工工Anddy为例例说明）说明：为避免免公式中中过多的的嵌套，这里的的身份证证号码限限定为115位的的。如果果您看懂懂了公式式的话，可以进进行简单单的修改改即可适适用于118位的的身份证证号码，甚至可可适用于于15、18两两者并存存的情况况。（11）根据据身份证证号码求求性别=IF(VALLUE(RIGGHT(E4,3)/2=INTT(VAALUEE(RIIGHTT(E44,3)/22),女,男)公式式解释：a. RIGGHT(E4,3)用用于求出出身份证证号码中中代表性性别的数数字，实实际求得得的为代代表数字字的字符符串b. VAALUEE(RIIGHT

16、T(E44,3)用于将将上一步步所得的的代表数数字的字字符串转转换为数数字c. VAALUEE(RIIGHTT(E44,3)/22=INNT(VVALUUE(RRIGHHT(EE4,33)/2用于于判断这这个身份份证号码码是奇数数还是偶偶数，当当然你也也可以用用Modd函数来来做出判判断。dd. =IF(VALLUE(RIGGHT(E4,3)/2=INTT(VAALUEE(RIIGHTT(E44,3)/22),女,男)及如如果上述述公式判判断出这这个号码码是偶数数时，显显示女女，否否则，这这个号码码是奇数数的话，则返回回男。（22）根据据身份证证号码求求出生日日期=CCONCCATEENATT

17、E(19,MIID(EE4,77,2),/,MMID(E4,9,22),/,MIDD(E44,111,2)公式式解释：a. MIDD(E44,7,2)为为在身份份证号码码中获取取表示年年份的数数字的字字符串bb. MMID(E4,9,22) 为为在身份份证号码码中获取取表示月月份的数数字的字字符串cc. MMID(E4,11,2) 为在身身份证号号码中获获取表示示日期的的数字的的字符串串d. CONNCATTENAATE(199,MMID(E4,7,22),/,MIDD(E44,9,2),/,MIID(EE4,111,22)目目的就是是将多个个字符串串合并在在一起显显示。（3）根根据参加加工作

18、时时间求年年资（即即工龄）=COONCAATENNATEE(DAATEDDIF(F4,TODDAY(),y),年年,DDATEEDIFF(F44,TOODAYY(),ymm),个月月)公公式解释释：a. TOODAYY()用用于求出出系统当当前的时时间b. DAATEDDIF(F4,TODDAY(),y)用于计计算当前前系统时时间与参参加工作作时间相相差的年年份c. DAATEDDIF(F4,TODDAY(),ym)用于于计算当当前系统统时间与与参加工工作时间间相差的的月份，忽略日日期中的的日和年年。d. =CCONCCATEENATTE(DDATEEDIFF(F44,TOODAYY(),y)

19、,年,DATTEDIIF(FF4,TTODAAY(),yym),个个月)目的就就是将多多个字符符串合并并在一起起显示。5. 其他说说明在这这张人事事资料表表中我们们还发现现，创建建日期：31-05-20001时显显示在同同一个单单元格中中的。这这是如何何实现的的呢？难难道是手手工添加加的吗？不是，实际上上这个日日期还是是变化的的，它显显示的是是系统当当前时间间。这里里是利用用函数 TODDAY 和函数数 TEEXT 一起来来创建一一条信息息，该信信息包含含着当前前日期并并将日期期以ddd-mmm-yyyyyy的格格式表示示。具体体公式写写法为：=创创建日期期：&TEXXT(TTODAAY(),

20、ddd-mmm-yyyyyy)至至此，我我们对于于文本函函数、日日期与时时间函数数已经有有了大致致的了解解，同时时也设想想了一些些应用领领域。相相信随着着大家在在这方面面的不断断研究，会有更更广泛的的应用。附一：文本函函数函数名函数说明语法ASC将字符串中的全角（双字节）英文字母更改为半角（单字节）字符。ASC(text)CHAR返回对应于数字代码的字符，函数 CHAR 可将其他类型计算机文件中的代码转换为字符。CHAR(number)CLEAN删除文本中不能打印的字符。对从其他应用程序中输入的字符串使用 CLEAN 函数，将删除其中含有的当前操作系统无法打印的字符。例如，可以删除通常出现在数

21、据文件头部或尾部、无法打印的低级计算机代码。CLEAN(text)CODE返回文字串中第一个字符的数字代码。返回的代码对应于计算机当前使用的字符集。CODE(text)CONCATENATE将若干文字串合并到一个文字串中。CONCATENATE (text1,text2,.)DOLLAR依照货币格式将小数四舍五入到指定的位数并转换成文字。DOLLAR 或 RMB(number,decimals)EXACT该函数测试两个字符串是否完全相同。如果它们完全相同，则返回 TRUE；否则，返回 FALSE。函数 EXACT 能区分大小写，但忽略格式上的差异。利用函数 EXACT 可以测试输入文档内的文字

22、。EXACT(text1,text2)FINDFIND 用于查找其他文本串 (within_text) 内的文本串 (find_text)，并从 within_text 的首字符开始返回 find_text 的起始位置编号。FIND(find_text,within_text,start_num)FIXED按指定的小数位数进行四舍五入，利用句点和逗号，以小数格式对该数设置格式，并以文字串形式返回结果。FIXED(number,decimals,no_commas)JIS将字符串中的半角（单字节）英文字母或片假名更改为全角（双字节）字符。JIS(text)LEFTLEFT 基于所指定的字符数返回

23、文本串中的第一个或前几个字符。LEFTB 基于所指定的字节数返回文本串中的第一个或前几个字符。此函数用于双字节字符。LEFT(text,num_chars)LEFTB(text,num_bytes)LENLEN 返回文本串中的字符数。LENB 返回文本串中用于代表字符的字节数。此函数用于双字节字符。LEN(text)LENB(text)LOWER将一个文字串中的所有大写字母转换为小写字母。LOWER(text)MIDMID 返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。MIDB 返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。此函数用于双字节字符。MID(text

24、,start_num,num_chars)MIDB(text,start_num,num_bytes)PHONETIC提取文本串中的拼音 (furigana) 字符。PHONETIC(reference)PROPER将文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。PROPER(text)REPLACEREPLACE 使用其他文本串并根据所指定的字符数替换某文本串中的部分文本。REPLACEB 使用其他文本串并根据所指定的字符数替换某文本串中的部分文本。此函数专为双字节字符使用。REPLACE(old_text,start_num,num_chars,new_te

25、xt)REPLACEB(old_text,start_num,num_bytes,new_text)REPT按照给定的次数重复显示文本。可以通过函数 REPT 来不断地重复显示某一文字串，对单元格进行填充。REPT(text,number_times)RIGHTRIGHT 根据所指定的字符数返回文本串中最后一个或多个字符。RIGHTB 根据所指定的字符数返回文本串中最后一个或多个字符。此函数用于双字节字符。RIGHT(text,num_chars)RIGHTB(text,num_bytes)SEARCHSEARCH 返回从 start_num 开始首次找到特定字符或文本串的位置上特定字符的编号

26、。使用 SEARCH 可确定字符或文本串在其他文本串中的位置，这样就可使用 MID 或 REPLACE 函数更改文本。SEARCHB 也可在其他文本串 (within_text) 中查找文本串 (find_text)，并返回 find_text 的起始位置编号。此结果是基于每个字符所使用的字节数，并从 start_num 开始的。此函数用于双字节字符。此外，也可使用 FINDB 在其他文本串中查找文本串。SEARCH(find_text,within_text,start_num)SEARCHB(find_text,within_text,start_num)SUBSTITUTE在文字串中用

27、new_text 替代 old_text。如果需要在某一文字串中替换指定的文本，请使用函数 SUBSTITUTE；如果需要在某一文字串中替换指定位置处的任意文本，请使用函数 REPLACE。SUBSTITUTE(text,old_text,new_text,instance_num)T将数值转换成文本。T(value)TEXT将一数值转换为按指定数字格式表示的文本。TEXT(value,format_text)TRIM除了单词之间的单个空格外，清除文本中所有的空格。在从其他应用程序中获取带有不规则空格的文本时，可以使用函数 TRIM。TRIM(text)UPPER将文本转换成大写形式。UPPE

28、R(text)VALUE将代表数字的文字串转换成数字。VALUE(text)WIDECHAR将单字节字符转换为双字节字符。WIDECHAR(text)YEN使用 ￥（日圆）货币格式将数字转换成文本，并对指定位置后的数字四舍五入。YEN(number,decimals)附二、日日期与时时间函数数函数名函数说明语法DATE返回代表特定日期的系列数。DATE(year,month,day)DATEDIF计算两个日期之间的天数、月数或年数。DATEDIF(start_date,end_date,unit)DATEVALUE函数 DATEVALUE 的主要功能是将以文字表示的日期转换成一个系列数。DAT

29、EVALUE(date_text)DAY返回以系列数表示的某日期的天数，用整数 1 到 31 表示。DAY(serial_number)DAYS360按照一年 360 天的算法（每个月以 30 天计，一年共计 12 个月），返回两日期间相差的天数。DAYS360(start_date,end_date,method)EDATE返回指定日期 (start_date) 之前或之后指定月份数的日期系列数。使用函数 EDATE 可以计算与发行日处于一月中同一天的到期日的日期。EDATE(start_date,months)EOMONTH返回 start-date 之前或之后指定月份中最后一天的系列数。用函数 EOMONTH 可计算特定月份中最后一天的时间系列数，用于证券的到期日等计算。EOMONTH(start_date,months)HOUR返回时间值的小时数。即一个介于 0 (12:00 A.M.) 到 23 (11:00 P.M.) 之间的整数。HOUR(serial_number)MINUTE返回时间值中的分钟。即一个介于 0 到 59 之间的整数。MINUTE(serial_number)MONTH返回以系