鼻炎缓解药:Excel函数实例

自动计算饭帖

某公司员工刷卡吃饭补贴，要求是只补贴中餐，花费3元以上的补贴3元，3元以下的花费多少补贴多少，同时刷卡多次的只补贴一次。

   从消费软件里导出员工消费明细，假如第一个员工第一次消费在d2单元格里，在补贴计算一列里输入以下函数即可得到补贴
=IF(d2<=-3,IF(AND(d2>-3),-d2,"3"),-d2)
   判断是否同时多次刷卡,假如消费日期在f1——fn列里，在补贴后一列输入以下函数：
=if（f1=f2，1，" "）标记1的表示有重复刷卡记录。

自动录入性别

根据现行的居民身份证号码编码规定，正在使用的18位的身份证编码。它的第17位为性别(奇数为男，偶数为女)，第18位为效验位。而早

期使用的是15位的身份证编码，它的第15位是性别(奇数为男，偶数为女)。

(1)函数分解

　　LEN函数返回文本字符串中的字符数。

　　语法:LEN(text)

　　Text是要查找其长度的文本。空格将作为字符进行计数。

　　MOD函数返回两数相除的余数。结果的正负号与除数相同。

　　语法:MOD(number,divisor)

　　Number为被除数;Divisor为除数。

　　MID函数返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。

　　语法:MID(text,start_num,num_chars)

　　Text为包含要提取字符的文本字符串;Start_num为文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，以此类

推;Num_chars指定希望MID从文本中返回字符的个数。

　　(2)实例分析

　　为了适应上述情况，必须设计一个能够适应两种身份编码的性别计算公式，在D2单元格中输入“=IF(LEN(C2)=15,IF(MOD(MID

(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))”。回车后即可在单元格获得该职工的性别，而后只要把公式复制到D3

、D4等单元格，即可得到其他职工的性别。

　　为了便于大家了解上述公式的设计思路，下面简单介绍一下它的工作原理:该公式由三个IF函数构成，其中“IF(MOD(MID(C2,15,1),2)=1,"

男","女")”和“IF(MOD(MID(C2,17,1),2)=1,"男","女")”作为第一个函数的参数。公式中“LEN(C2)=15”是一个逻辑判断语句，LEN函数提取

C2等单元格中的字符长度，如果该字符的长度等于15，则执行参数中的第一个IF函数，否则就执行第二个IF函数。

　　在参数“IF(MOD(MID(C2,15,1),2)=1,"男","女")”中。MID函数从C2的指定位置(第15位)提取1个字符，而MOD函数将该字符与2相除，获取

两者的余数。如果两者能够除尽，说明提取出来的字符是0(否则就是1)。逻辑条件“MOD(MID(C2,15,1),2)=1”不成立，这时就会在D2单元格中

填入“女”，反之则会填入“男”。

　　如果LEN函数提取的C2等单元格中的字符长度不等于15，则会执行第2个IF函数。除了MID函数从C2的指定位置(第17位，即倒数第2位)提取1

个字符以外，其他运算过程与上面的介绍相同。

自动录入出生日期

1、函数分解
　　CONCATENATE函数将几个文本字符串合并为一个文本字符串。

　　语法:CONCATENATE(text1,text2,...)

　　Text1,text2,...为1～30个要合并成单个文本项的文本项。文本项可以为文本字符串、数字或对单个单元格的引用。

2、实例分析

　　与上面的思路相同，我们可以在E2单元格中输入公式“=IF(LEN(C2)=15,CONCATENATE("19",MID(C2,7,2),"年",MID(C2,9,2),"月",MID

(C2,11,2),"日"),CONCCTENCTE(MID(C2,7,4),"年",MID(C2,11,2),"月",MID(C2,13,2),"日"))”。其中“LEN(C2)=15”仍然作为逻辑判断语句

使用，它可以判断身份证号码是15位的还是18位的，从而调用相应的计算语句。

　　对15位的身份证号码来说，左起第7至12个字符表示出生年、月、日，此时可以使用MID函数从身份证号码的特定位置，分别提取出生年、

月、日。然后用CONCATENATE函数将提取出来的文字合并起来，就能得到对应的出生年月日。公式中“19”是针对早期身份证号码中存在2000年

问题设计的，它可以在计算出来的出生年份前加上“19”。对“18”位的身份证号码的计算思路相同，只是它不存在2000年问题，公式中不用

给计算出来的出生年份前加上“19”。

　　注意：CONCATENATE函数和MID函数的操作对象均为文本，所以存放身份证号码的单元格必须事先设为文本格式，然后再输入身份证号。

计算保险收益

    保险公司开办了一种平安保险，具体办法是一次性缴费12000元，保险期限为20年。如果保险期限内没有出险，每年返还1 000元。请问在

没有出险的情况下，它与现在的银行利率相比，这种保险的收益率如何。

1、函数分解

RATE函数返回投资的各期利率。该函数通过迭代法计算得出，并且可能无解或有多个解。

语法: RATE(nper,pmt,pv,fv,type,guess)

Nper为总投资期，即该项投资的付款期总数;Pmt为各期付款额，其数值在整个投资期内保持不变;Pv为现值，即从该项投资开始计算时已经入帐

的款项，或一系列未来付款当前值的累积和;Fv为未来值，或在最后一次付款后希望得到的现金余额;Type为数字0或1。

2、实例分析

新建一个工作表，在其A1、B1、C1、D1单元格分别输入“保险年限”、“年返还金额”、“保险金额”、“年底返还”和“现行利息”。然后

在A2、B2、C2、D2和E2单元格分别输入“20”、“1000”、“12000”、“1”(表示年底返还，0表示年初返还)和“0.02”。然后选中F2单元格

输入公式“=RATE(A2,B2,C2,,D2,E2)”，回车就可以获得该保险的年收益率为“0.06”。要高于现行的银行存款利率，所以还是有利可图的。

上面公式中的C2后面有两个逗号，说明最后一次付款后账面上的现金余额为零。