Excel中函数嵌套功能的实际应用

【摘要】excel是办公自动化中非常重要的一款软件,它不仅能够方便地处理表格和进行图形分析,强大的功能体现在对数据的自动处理和计算。本文介绍了excel函数的基本结构和excel函数的种类,并通过部分实例介绍了嵌套函数的用法。

【关键字】excel 函数嵌套

excel是办公自动化中非常重要的一款软件,它的数据处理功能在现有的文字处理软件中独占鳌头,特别是excel的函数功能更是十分强大,在生活和工作实践中得到重要应用。excel中所提的函数其实是一些预定义的公式,它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算。

excel函数一共有11类,分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。

函数的基本结构以函数名称开始,后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现,函数名称的前面必须键入等号(=)。

嵌套函数,是指在某些情况下,用户可能需要将某函数作为另一函数的参数使用,它返回的数值类型必须与参数使用的数值类型相同。在处理复杂问题时,一个或两个函数的单独使用无法有效解决问题,甚至无法解决,那么,通过嵌套函数的使用就能方便解决问题。下面,通过几个实例介绍嵌套函数的运用。

1 单个函数的嵌套:个人所得税的计算

假设个人收入所得税的稽征办法是:工资在1000元以下的免征个人所得税,工资1000元以上至1500元的超过部分按5%的税率征收,1500元以上至2000元的超过部分按10%的税率征收,高于2000元的超过部分按30%的税率征收。

假如工作表的b列存放职工姓名,c列存放工资,选中d列的空白单元格输入公式“=if(c2<=1000,″″,if((c2-1000)<=1500,(c2-1000)*0.05,if(c2-e2<=1500,(c2-1500)*0.1,if(c2>2000,(c2-2000)*0.3))))”,回车后即可计算出c2工资应缴纳的收入调节税。公式中的if语句是逐次计算的,如果第一个逻辑判断c2<=1000成立,则公式所在单元格被填入空格;如果第一个逻辑判断式不成立,则计算第二个if语句;直至计算结束。如果税率征收标准发生了变化,只须改变逻辑和计算式中的值,如1000、1500和2000等即可。

例子中用到的函数是if函数,它用于执行真假值判断后,根据逻辑测试的真假值返回不同的结果,因此if函数也称为条件函数。它的应用很广泛,可以使用函数if 对数值和公式进行条件检测。

2 多个函数嵌套

2.1 员工奖金计算

假如有一张某公司员工出勤表,且该公司规定一个月奖金基数为1000元,病假1天扣10元,事假一天扣20元,旷工一天扣50元,扣完为止。计算出每个员工的当月奖金. 选中bm4单元格,输入公式:=if(1000-countif(c4:bl4,"病假")*5-countif(c4:bl4,"事假")*10-countif(c4:bl4,"旷工")*25>0,1000-countif(c4:bl4,"病假")*5-countif(c4:bl4,"事假")*10-countif(c4:bl4,"旷工")*25,0),确认,计算出第一位员工的当月奖金额。

上述公式的含义是:用“countif(c4:bl4,"病假")、countif(c4:bl4,"事假")、countif(c4:bl4,"旷工")”分别用于统计每位员工的病假、事假、旷工的次数,再分别乘以“5、10、25”(每“半天”为一次)计算出所要扣去的奖励额,再用基数“1000”减去上述扣去的金额。如果(if)差大于“0”,则显示差(即员工的奖金额),否则显示“0”(扣完为止)。再次选中bm4单元格,用“填充柄”将上述公式复制到下面的单元格中,用于计算其它员工的当月奖励额。

例子中用到的函数:

条件计数函数countif

语法:countif(range,criteria)。

参数:range为需要统计的符合条件的单元格区域;criteria为参与计算的单元格条件,其形式可以为数字、表达式或文本(如36、″>160″和″男″等)。条件中的数字可以直接写入,表达式和文本必须加引号。

2.2 人民币小写金额转换为大写金额

很多会计人员都无法准确而快速地将会计报表中的小写金额转化为大写金额,如果手工输入这些大写金额,或者或少总会出现种错误,并且输入的速度慢了很多,这样就大大影响了工作效率。那么通过excel函数多次嵌套可以轻松解决人民币金额从小写转换大写,下面以实例进行说明。

第1步 在excel工作表中输入相关数据,并在a列中输入一些具有代表性的人民币小写数据用来测试函数的功能。

第2步 在b2单元格中输入公式"=if(abs(a2)<0.005,"",if(a2<0,"负",)&if(int(abs(a2)),text(int(abs(a2)),"[dbnum2]")&"元",)&if(int(abs(a2)*10)-int(abs(a2))*10,text(int(abs(a2)*10)-int(abs(a2))*10,"[dbnum2]")&"角",if(int(abs(a2))=abs(a2),,if(abs(a2)<0.1,,"零")))&if(round(abs(a2)*100-int(abs(a2)*10)*10,),text(round(abs(a2)*100-int(abs(a2)*10)*10,),"[dbnum2]")&"分","整"))"。

第3步 仔细检查公式输入无误后,按回车键即可将a2单元格中人民币小写金额转换为人民币大写金额。

第4步 拖动b2单元格中的填充柄将公式复制到以下相关的单元格中,即可将a列中数字转换为人民币大写。从公式的执行结果来看,此公式可以处理各种情况。

例子中用到的函数:

绝对值函数abs(number),返回给定数值的绝对值取整函数int(number),返回给定数值的整数文本函数text(value,format_text),根据指定的数字格式将数值转换成文本舍入函数round(number,num_digits),按指定的位数对数值进行四舍五入

excel应用软件提供了大量函数,函数之间的嵌套更是十分丰富,虽然嵌套函数看似十分复杂,但是经过深入的学习和实践,掌握正确的函数并相互嵌套,将会快捷有效地解决实际问题。

参考文献

[1] 周文勤.excel实用范例教学三例[j].电脑知识技术.2009(1).