EXCEL函数应用学习 自己收集整理

函数名称：DATEDIF主要功能：计算返回两个日期参数的差值。使用格式：=DATEDIF(date1,date2,"y")、=DATEDIF(date1,date2,"m")、=DATEDIF(date1,date2,"d")参数说明：date1代表前面一个日期，date2代表后面一个日期；y（m、d）要求返回两个日期相差的年（月、天）数。应用举例：在C23单元格中输入公式：=DATEDIF(A23,TODAY(),"y")，确认后返回系统当前日期[用TODAY()表示）与A23单元格中日期的差值，并返回相差的年数。特别提醒：这是Excel中的一个隐藏函数，在函数向导中是找不到的，可以直接输入使用，对于计算年龄、工龄等非常有效。函数名称：INT主要功能：将数值向下取整为最接近的整数。使用格式：INT(number)参数说明：number表示需要取整的数值或包含数值的引用单元格。应用举例：输入公式：=INT(18.89)，确认后显示出18。特别提醒：在取整时，不进行四舍五入；如果输入的公式为=INT(-18.89)，则返回结果为-19。函数名称：NOW主要功能：给出当前系统日期和时间。使用格式：NOW()参数说明：该函数不需要参数。应用举例：输入公式：=NOW()，确认后即刻显示出当前系统日期和时间。如果系统日期和时间发生了改变，只要按一下F9功能键，即可让其随之改变。特别提醒：显示出来的日期和时间格式，可以通过单元格格式进行重新设置。函数名称：TODAY主要功能：给出系统日期。使用格式：TODAY()参数说明：该函数不需要参数。应用举例：输入公式：=TODAY()，确认后即刻显示出系统日期和时间。如果系统日期和时间发生了改变，只要按一下F9功能键，即可让其随之改变。小写数字转换成大写这里笔者给出将阿拉伯金额数字转换成中文大写形式的两种方法：内置函数法、自定义函数法，供大家参考。本文所涉及到的Excel函数如下，点击可查阅具体的函数用法。1、ABS(number)

2、CONCATENATE(Text1，Text……)

3、INT(number)

4、TEXT(value,format_text)

5、自定义函数一、内置函数法如图1所示，我们来将C12单元格中的数值转换为中文大写形式，并将结果保存在D12单元格中。1、分别选中E13、F13、G113单元格，依次输入公式：=TEXT(INT(ABS(C12)),"[DBNum2]")、=TEXT(INT(ABS(C12*10))-INT(ABS(C12))*10,"[DBNum2]")、=TEXT(INT(ABS(C12*100))-INT(ABS(C12*10))*10,"[DBNum2]")。公式含义解析：分别确定统计结果的元、角、分的数值，并将其转换为中文大写格式。2、选中E10单元格，输入公式：=E13&"元"&F13&"角"&G13&"分"，或者：=CONCATENATE(E13,"元",F13,"角",G13,"分")。公式含义解析：将E13至G13单元格中的文本组合为一个整体，并在相应位置加上单位“元、角、分”文字。3、再次选中E12单元格，执行“格式→条件格式”命令，打开“条件格式”对话框（如图1）。按“条件1”下面的下拉按钮，选中“公式”选项，在后面的方框中输入“=$C$12<0”（如图2），再按“格式”按钮，在随后弹出的“单元格格式”对话框中（如图3），将“字体”颜色设置为红色，确定退出。4、选中C12单元格，执行“格式→单元格”命令，打开“单元格格式”对话框（如图4），切换到“数字”标签下，选中“分类”下面的“货币”选项后，再选中右边“负数”下面的红色选项，确定退出。注意：经过以上两步操作后，当C12中的数值小于零时，大、小写结果均以红色显示，与财务要求相一致。5、选中13行，右击鼠标，在随后弹出的快捷菜单中，选“隐藏”选项，将13行隐藏起来，不影响正常打印效果。至此，转换工作结束，效果参见图1所示。二、自定义函数法

显然上面的转换方法比较麻烦。其实，我们可以事先自定义一个函数，然后再用自定义的函数来转换。1、执行“工具→宏→VisualBasic编辑器”命令，进入“VisualBasic编辑”窗口（如图5）。执行“插入→模块”命令，插入一个模块（如“模块1”），双击“模块1”在窗口右边展开“模块1（代码）”编辑窗口，输入如下代码。（图片较大，请拉动滚动条观看）Functiondxje(q)

ybb=Round(q*100)'将输入的数值扩大100倍，进行四舍五入

y=Int(ybb/100)'截取出整数部分

j=Int(ybb/10)-y*10'截取出十分位

f=ybb-y*100-j*10'截取出百分位

zy=Application.WorksheetFunction.Text(y,"[dbnum2]")'将整数部分转为中文大写

zj=Application.WorksheetFunction.Text(j,"[dbnum2]")'将十分位转为中文大写

zf=Application.WorksheetFunction.Text(f,"[dbnum2]")'将百分位转为中文大写

dxje=zy&"元"&"整"

d1=zy&"元"

Iff<>0Andj<>0Then

dxje=d1&zj&"角"&zf&"分"

Ify=0Then

dxje=zj&"角"&zf&"分"

EndIf

EndIf

Iff=0Andj<>0Then

dxje=d1&zj&"角"&"整"

Ify=0Then

dxje=zj&"角"&"整"

EndIf

EndIf

Iff<>0Andj=0Then

dxje=d1&zj&zf&"分"

Ify=0Then

dxje=zf&"分"

EndIf

EndIf

Ifq=""Then

dxje=0'如没有输入任何数值为0

EndIf

EndFunction

注意：①上面代码中英文单引号“'”后面的字符可以不输入，只是用来解释代码的含义。②代码中“dxje”是自定义函数名称，你可以修改为其他字符。2、输入完成后，关闭VBA编辑窗口返回工作表状态。3、如果要转换C2单元格的数值，并将结果保存的D2单元格中（如图6），只要在D2单元格中输入公式：=dxje(C2)，确认即可（参见图6）。注意：通常情况下，自定义的函数只适应于定制的工作簿中，如果要在其它工作簿中使用，请将其制作为加载宏，然后加载一下即可。身份证号码转换出生年月用函数自动提取身份证号码中的出生日期和性别信息在用Excel制作职工简明登记表时，常常要输入身份证号码、性别、出生时间等内容。其实，身份证号码中已经包含了性别、出生时间等信息，我们何不利用函数将这些信息数值提取出来，进行简单判断以后，将其自动转换成性别、出生时间等内容显示出来呢！

身份证号码有两种格式，一种是15位号码（如340501761217022），一种是18位号码（。在15位号码中，第7—12位数字（如761217）表示持证人的出生时间（如1976年12月17日），第15位数字（如2）表示持证人的性别（奇数为“男”，偶数为“女”）；在18位号码中，第7—14位数字（如19700109）表示持证人的出生时间（如1970年1月9日），第17位数字（如1）表示持证人的性别。

一、信息的提取、判断和自动显示

此处，假定身份证号码保存在C列中，性别和出生时间分别保存在D列和E列中。

1、性别的自动显示

①选中D2单元格，输入公式：=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0,"女","男")，输入完成后，按下“Enter”键进行确认，第1位员工的性别则自动显示在D2单元格中[如图1]。

上述函数式中涉及到的几个函数的含义分别是：

LEN(C2)函数，用于统计C2单元格中字符串的字符数目。

MID(C2,15,1)函数，用于从C2单元格中字符串的第15位开始提取1个字符。MOD(number,divisor)函数，用于给出数字number除以数字divisor后的余数。

IF()函数，是一个逻辑判断函数。上述函数式的意思是：

IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1))：如果[IF]C2单元格中字符串的字符数是15[LEN(C2)=15]，则从第15位开始，提取C2单元格字符串中的1个字符[MID(C2,15,1)]；如果不是15位，则从第17位开始，提取1个字符[MID(C2,17,1)]。

=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0,"女","男")：如果[IF]提取出来的数值[IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1))]除以“2”后余数为“0”[MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=0]，则显示为“女”，反之显示为“男”。②再次选中D2单元格，将鼠标移至该单元格右下角成十字线状时[如图2，我们通常称这种状态为“填充柄”状态]。