21.SEARCH或SEARCHB
用途:返回从start_num开始首次找到特定字符或文本串的位置编号。其中SEARCH以字符数为单位,SEARCHB以字节数为单位。
语法:SEARCH(find_text,within_text,start_num),SEARCHB(find_text,within_text,start_num)。
参数:Find_text是要查找的文本,可以使用通配符,包括问号“?”和星号“*”。其中问号可匹配任意的单个字符,星号可匹配任意的连续字符。如果要查找实际的问号或星号,应当在该字符前键入波浪线“~”。Within_text是要在其中查找find_text的文本。Start_num是within_text中开始查找的字符的编号。如果忽略start_num,则假定其为1。
实例:如果A1=学习的革命,则公式“=SEARCH("的",A1)”返回3,=SEARCHB("的",A1)返回5。
22.SUBSTITUTE
用途:在文字串中用new_text替代old_text。如果需要在一个文字串中替换指定的文本,可以使用函数SUBSTITUTE;如果需要在某一文字串中替换指定位置处的任意文本,就应当使用函数REPLACE。
语法:SUBSTITUTE(text,old_text,new_text,instance_num)。
参数:Text是需要替换其中字符的文本,或是含有文本的单元格引用;Old_text是需要替换的旧文本;New_text用于替换old_text的文本;Instance_num为一数值,用来指定以new_text替换第几次出现的old_text;如果指定了instance_num,则只有满足要求的old_text被替换;否则将用new_text替换Text中出现的所有old_text。
实例:如果A1=学习的革命、A2=电脑,则公式“=SUBSTITUTE(A1,"的革命",A2,1)”返回“学习电脑”。
23.T
用途:将数值转换成文本。
语法:T(value)。
参数:value是需要进行测试的数据。如果value本身就是文本,或是对文本单元格的引用,T函数将返回value;如果没有引用文本,则返回""(空文本)。
实例:如果A1中含有文本“电脑”,则公式“=T(A1)”返回“电脑”。
24.TEXT
用途:将数值转换为按指定数字格式表示的文本。
语法:TEXT(value,format_text)。
参数:value是数值、计算结果是数值的公式、或对数值单元格的引用;format_text是所要选用的文本型数字格式,即“单元格格式”对话框“数字”选项卡的“分类”列表框中显示的格式,它不能包含星号“*”。
注意:使用“单元格格式”对话框的“数字”选项卡设置单元格格式,只会改变单元格的格式而不会影响其中的数值。使用函数TEXT可以将数值转换为带格式的文本,而其结果将不再作为数字参与计算。
实例:如果A1=2986.638,则公式“=TEXT(A5,"#,##0.00")”返回2,986.64。
25.TRIM
用途:除了单词之间的单个空格外,清除文本中的所有的空格。如果从其他应用程序中获得了带有不规则空格的文本,可以使用TRIM函数清除这些空格。
语法:TRIM(text)。
参数:Text是需要清除其中空格的文本。
实例:如果A1=FirstQuarterEarnings,则公式“=TRIM(A1)”返回“FirstQuarterEarnings”。
26.UPPER
用途:将文本转换成大写形式。
语法:UPPER(text)。
参数:Text为需要转换成大写形式的文本,它可以是引用或文字串。
实例:公式“=UPPER("apple")”返回APPLE。
27.value
用途:将表示数字的文字串转换成数字。
语法:value(text)。
参数:Text为带引号的文本,或对需要进行文本转换的单元格的引用。它可以是Excel可以识别的任意常数、日期或时间格式。如果Text不属于上述格式,则value函数返回错误值#value!。
注意:通常不需要在公式中使用value函数,Excel可以在需要时自动进行转换。value函数主要用于与其他电子表格程序兼容。
实例:公式“=value("¥1,000")”返回1000;=value("16:48:00")-value("12:00:00")返回0.2,该序列数等于4小时48分钟。
28.WIDECHAR
用途:将单字节字符转换为双字节字符。
语法:WIDECHAR(text)。
参数:Text是需要转换为双字节字符的文本或包含文本的单元格引用。
注意:因为汉字本身是双字节字符,所以使用此函数转换汉字时得到的是汉字的原形。
实例:公式“=WIDECHAR("apple")”返回apple,=WIDECHAR("电脑")返回“电脑”。
三、函数应用案例──算账理财
1.零存整取储蓄
“零存整取”是工薪阶层常用的投资方式,这就需要计算该项投资的未来值,从而决定是否选择某种储蓄方式。
(1)函数分解
FV函数基于固定利率及等额分期付款方式,返回某项投资的未来值。
语法:FV(rate,nper,pmt,pv,type)
Rate为各期利率;Nper为总投资期,即该项投资的付款期总数;Pmt为各期所应支付的金额,其数值在整个年金期间保持不变;Pv为现值,即从该项投资开始计算时已经入账的款项,或一系列未来付款的当前值的累积和;Type为数字0或1,用以指定各期的付款时间是在期初还是期末。
(2)实例分析
新建一个工作表,在其A1、B1、C1、D1单元格分别输入“投资利率”、“投资期限”、“投资金额”和“账户初始金额”。假设妻子新建一个账户每月底存入300元,年利2.1%(即月息0.00175),连续存款5年,可以在A2、B2、C2、D2单元格分别输入“0.00175”、“60”、“500”和“1”。
然后选中E2单元格输入公式“=FV(A2,B2,-C2,D2,1)”,回车即可获得该投资的到期本金合计为“¥18,994.67”。公式中的“-C2”表示资金是支出的,“C2”前不加负号也可,这样计算出来的结果就是负值。
如果丈夫也有“零存整取”账户,每月初存入200元,年利1.28%(即月息0.001667),连续存款3年,可以在A3、B3、C3、D3单元格分别输入“0.001667”、“36”、“200”和“0”。然后把E2单元格中的公式复制到E3单元格(将光标指向E2单元格的拖动柄,当黑色十字光标出现后向下拖动一格),即可得知该投资的到期本金合计“¥7,426.42”。
提示:上述计算结果包括本金和利息,但不包括利息税等其他费用。
2.还贷金额
如今贷款购买住房进行消费的家庭越来越多,计算贷款的月偿还金额是决策的重要依据,下面我们就来设计如何知道自己每月的还款金额。
(1)函数分解
PMT函数基于固定利率及等额分期付款方式,返回贷款的每期付款额。
语法:PMT(rate,nper,pv,fv,type)
Rate为贷款利率;Nper为该项贷款的付款总数;Pv为现值,或一系列未来付款的当前值的累积和;Fv为未来值,或在最后一次付款后希望得到的现金余额;Type为数字0或1。
(2)实例分析
新建一个工作表,在其A1、B1、C1、D1单元格分别输入“贷款利率”、“还贷年限”、“贷款金额”和“还贷时间”。假设贷款年利为4.1%(即月息0.00342),预计的还贷时间为10年,贷款金额为10万元,且每月底还贷。可以在A2、B2、C2、D2单元格分别输入“0.00342”、“360”、“100000”和“1”(表示月末还贷,0表示月初还贷)。然后选中E2单元格输入公式“= PMT(A2,B2,C2,,D2)”,回车就可以获得每月的还款金额为“¥-481.78”。
上式中C2后的两个逗号之间还有一个参数,表示还贷期限结束时账户上的余额,对这个例子来说应该是0,所以可以忽略该参数或写成“= PMT(A2,B2,C2,0,D2)”。
3.保险收益
保险公司开办了一种平安保险,具体办法是一次性缴费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后面有两个逗号,说明最后一次付款后账面上的现金余额为零。
4.个税缴纳金额
假设个人收入调节税的收缴标准是:工资在800元以下的免征调节税,工资800元以上至1 500元的超过部分按5%的税率征收,1 500元以上至2 000元的超过部分按8%的税率征收,高于2 000元的超过部分按20%的税率征收。我们可以按以下方法设计一个可以修改收缴标准的工作簿:
新建一个工作表,在其A1、B1、C1、D1、E1单元格分别输入“姓名”、“工资总额”、“扣款”、“个税”和“实付工资”。为了方便个税标准的修改,我们可以另外打开一个工作表(例如Sheet2),在其A1、B1、C1、D1、E1单元格中输入“免征标准”、“低标准”、“中等标准”和“高标准”,然后分别在其下方的单元格内输入“800”、“1500”、“2000”、“2000”。
接下来回到工作表Sheet1中,选中D列的D2单元格输入公式“=IF(C2<=Sheet2!A2," ",IF((C2-Sheet2!A2)<=Sheet2!B2,(C2-Sheet2!A2)*0.05,IF(C2-Sheet2!C2<=Sheet2!C2,(C2-Sheet2!C2)*0.08,IF(C2>Sheet2!D2,(C2-Sheet2!D2)*0.2))))”,回车后即可计算出C2单元格中的应缴个税金额。此后用户只需把公式复制到C3、C4等单元格,就可以计算出其他职工应缴纳的个税金额。
上述公式的特点是把个税的征收标准放到另一个工作表中,如果征税标准发生了变化,用户只需修改相应单元格中的数值,不需要对公式进行修改,可以减少发生计算错误的可能。公式中的IF语句是逐次计算的,如果第一个逻辑判断“C2-Sheet2!A2)<=Sheet2!B2”成立,即工资收入低于征收标准,则个税计算公式所在单元格被填入空格;如果第一个逻辑判断式不成立,则计算第二个IF语句,直至计算结束。假如征税标准多于4个,可以按上述继续嵌套IF函数(最多7个)。
四、函数应用案例──信息统计
使用Excel管理人事信息,具有无须编程、简便易行的特点。假设有一个人事管理工作表,它的A1、B1、C1、D1、E1、F1、G1和H1单元格分别输入“序号”、“姓名”、“身份证号码”、“性别”、“出生年月”等。自第2行开始依次输入职工的人事信息。为了尽可能减少数据录入的工作量,下面利用Excel函数实现数据统计的自动化。
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个字符以外,其他运算过程与上面的介绍相同。
2.出生日期输入
(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函数的操作对象均为文本,所以存放身份证号码的单元格必须事先设为文本格式,然后再输入身份证号。
3.职工信息查询
Excel提供的“记录单”功能可以查询记录,如果要查询人事管理工作表中的某条记录,然后把它打印出来,必须采用下面介绍的方法。
(1)函数分解
INDEX函数返回数据清单或数组中的元素值,此元素由行序号和列序号的索引值给定。
INDEX函数有两种语法形式:数组和引用。数组形式通常返回数值或数值数组,引用形式通常返回引用。当函数INDEX的第一个参数为数组常数时,使用数组形式。
语法1(数组形式):INDEX(array,row_num,column_num)
Array为单元格区域或数组常量。如果数组只包含一行或一列,则相对应的参数row_num或column_num为可选。如果数组有多行和多列,但只使用row_num或column_num,函数INDEX返回数组中的整行或整列,且返回值也为数组;Row_num为数组中某行的行序号,函数从该行返回数值。如果省略row_num,则必须有column_num;Column_num为数组中某列的列序号,函数从该列返回数值。如果省略column_num,则必须有row_num。
语法2(引用形式):INDEX(reference,row_num,column_num,area_num)
Reference表示对一个或多个单元格区域的引用。如果为引用输入一个不连续的区域,必须用括号括起来。如果引用中的每个区域只包含一行或一列,则相应的参数row_num或column_num分别为可选项;Row_num引用中某行的行序号,函数从该行返回一个引用;Column_num引用中某列的列序号,函数从该列返回一个引用;Area_num选择引用中的一个区域,并返回该区域中row_num和column_num的交叉区域。选中或输入的第一个区域序号为1,第二个为2,以此类推。如果省略area_num,函数INDEX使用区域1。
MATCH函数返回在指定方式下与指定数值匹配的数组中元素的相应位置。
语法:MATCH(lookup_value,lookup_array,match_type)
Lookup_value为需要在数据表中查找的数值;Lookup_value为需要在Look_array中查找的数值;Match_type为数字-1、0或1。
(2)实例分析
如果上面的人事管理工作表放在Sheet1中,为了防止因查询操作而破坏它(必要时可以添加只读保护),我们可以打开另外一个空白工作表Sheet2,把上一个数据清单中的列标记复制到第一行。假如你要以“身份证号码”作为查询关键字,就要在C2单元格中输入公式“=INDEX(Sheet1!C2:C600,MATCH( SC S5,Sheet1! SC S2: SC S600,0),1)”。其中的参数“ SC S5”引用公式所在工作表中的C5单元格(也可以选用其他单元格),执行查询时要在其中输入查询关键字,也就是待查询记录中的身份证号码。参数“Sheet1!C2:C600”设定INDEX函数的查询范围,引用的是数据清单C列的所有单元格。MATCH函数中的参数“0”指定它查找“Sheet1! SC S2: SC S600”区域中等于 SC S5的第一个值,并且引用的区域“Sheet1! SC S2: SC S600,0”可以按任意顺序排列。
上面的公式执行数据查询操作时,首先由MATCH函数在“Sheet1! SC S2: SC S600”区域搜索,找到“ SC S5”单元格中的数据在引用区域中的位置(自上而下第几个单元格),从而得知待查询数据在引用区域中的第几行。
接下来INDEX函数根据MATCH函数给出的行号,返回“Sheet1!C2:C600”区域中对应行数单元格中的数据。假设其中待查询的“身份证号码”是“3234567896”,它位于“Sheet1! SC S2: SC S600”区域的第三行,MATCH函数就会返回“3”。接着INDEX函数返回“Sheet1!C2:C600”区域中行数是“3”的数据,也就是“3234567896”。
然后,我们将光标放到C2单元格的填充柄上,当十字光标出现以后向右拖动,从而把C2中的公式复制到D2、E2等单元格(然后再向左拖动,以便把公式复制到B2、A2单元格),这样就可以获得与该身份证号对应的性别、籍贯等数据。
注意:公式复制到D2、E2等单元格以后,INDEX函数引用的区域就会发生变化,由C2:C600变成D2:D600、E2:E600等等。但是MATCH函数返回的(相对)行号仍然由查询关键字给出,此后INDEX函数就会根据MATCH函数返回的行号从引用区域中找到数据。
在Sheet2工作表中进行查询时只要在查询输入单元格中输入关键字,回车后即可在工作表的C2单元格内看到查询出来的身份证号码。如果输入的身份证号码关键字不存在或输入错误,则单元格内会显示“#N/A”字样。
4.职工性别统计
(1)函数分解
COUNTIF函数计算区域中满足给定条件的单元格的个数。
语法:COUNTIF(range,criteria)
Range为需要计算其中满足条件的单元格数目的单元格区域;Criteria为确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式或文本。
(2)实例分析
假设上面使用的人事管理工作表中有599条记录,统计职工中男性和女性人数的方法是:选中单元格D601(或其他用不上的空白单元格),统计男性职工人数可以在其中输入公式“="男"&COUNTIF(D2:D600,"男")&"人"”;接着选中单元格D602,在其中输入公式“="女"&COUNTIF(D2:D227,"女")&"人"”。回车后即可得到“男399人”、“女200人”。
上式中D2:D600是对“性别”列数据区域的引用,实际使用时必须根据数据个数进行修改。“男”或“女”则是条件判断语句,用来判断区域中符合条件的数据然后进行统计。“&”则是字符连接符,可以在统计结果的前后加上“男”、“人”字样,使其更具有可读性。
5.年龄统计
在人事管理工作中,统计分布在各个年龄段中的职工人数也是一项经常性工作。假设上面介绍的工作表的E2:E600单元格存放职工的工龄,我们要以5年为一段分别统计年龄小于20岁、20至25岁之间,一直到55至60岁之间的年龄段人数,可以采用下面的操作方法。
(1)函数分解
FREQUENCY函数以一列垂直数组返回某个区域中数据的频率分布。
语法:FREQUENCY(data_array,bins_array)
Data_array为一数组或对一组数值的引用,用来计算频率。如果data_array中不包含任何数值,函数FREQUENCY返回零数组;Bins_array为间隔的数组或对间隔的引用,该间隔用于对data_array中的数值进行分组。如果bins_array中不包含任何数值,函数FREQUENCY返回data_array中元素的个数。
(2)实例分析
首先在工作表中找到空白的I列(或其他列),自I2单元格开始依次输入20、25、30、35、40...60,分别表示统计年龄小于20、20至25之间、25至30之间等的人数。然后在该列旁边选中相同个数的单元格,例如J2:J10准备存放各年龄段的统计结果。然后在编辑栏输入公式“=FREQUENCY(YEAR(TODAY())-YEAR(E2:E600),I2:I10)”,按下Ctrl+Shift+Enter组合键即可在选中单元格中看到计算结果。其中位于J2单元格中的结果表示年龄小于20岁的职工人数,J3单元格中的数值表示年龄在20至25之间的职工人数等。
6.名次值统计
在工资统计和成绩统计等场合,往往需要知道某一名次(如工资总额第二、第三)的员工的工资是多少。这种统计的操作方法如下。
(1)函数分解
LARGE函数返回数据集中第K个最大值。使用此函数可以根据相对标准来选择数值。
语法:LARGE(array,k)
Array为需要从中选择第K个最大值的数组或数据区域;K为返回值在数组或数据单元格区域中的位置(从大到小排)。
SMALL函数返回数据集中第K个最小值。使用此函数可以返回数据集中特定位置上的数值。
语法:SMALL(array,k)
Array为需要找到第K个最小值的数组或数字型数据区域;K为返回的数据在数组或数据区域里的位置(从小到大)。
(2)实例分析
假设C2:C688区域存放着员工的工资,首先在D列选取空白单元格D3,在其中输入公式“=LARGE(C2:C688,D2)”。其中D2作为输入名次变量的单元格,如果你在其中输入3,公式就可以返回C2:C688区域中第三大的数值。
如果我们把上述公式修改为“=SMALL(C2:C688,D1)”,然后在D1单元格中输入6,就可以获得C2:C688区域倒数第六(小)的数值。
为方便起见,你可以给C2:C688区域定义一个名称“职工工资”。此后可以把上述公式修改为“=LARGE(职工工资,D2)”或“=SMALL(职工工资,D1)”。
7.位次阈值统计
与上例相似,在工资统计和成绩统计等场合,需要知道排名达到总体的前1/3的工资总额或分数(称为“阈值”)是多少。这种统计的操作方法如下:
(1)函数分解
PERCENTILE函数返回区域中数值的第K个百分点的值。可以使用此函数来建立接受阈值。
语法:PERCENTILE(array,k)
Array为定义相对位置的数组或数据区域;K为0到1之间的百分点值,包含0和1。
(2)实例分析
假设C2:C200区域存放着学生的考试成绩,首先在D列选取空白单元格D3,在其中输入公式“=PERCENTILE(C2:C200,D2)”。其中D2作为输入百分点变量的单元格,如果你在其中输入0.33,公式就可以返回名次达到前1/3所需要的成绩。
五、函数应用案例──管理计算
企业、学校等单位均存在许多管理计算问题,例如计算一个学期有几个授课日、企业在多少个工作日之后交货等等。下面介绍有关问题的几种计算方法。
1.授课日数
(1)函数分解
NETWORKDAYS函数专门用于计算两个日期值之间完整的工作日数值。这个工作日数值将不包括双休日和专门指定的其他各种假期。
语法:NETWORKDAYS (Start_date,End_date,Holidays)
Start_date表示开始日期;End_date为终止日期,Holidays表示作为特定假日的一个或多个日期。这些参数值既可以手工输入,也可以对单元格的值进行引用。
(2)实例分析
假设新学期从2003年9月1日开始到2004年1月15日结束,希望知道本学期有多少个授课日,也就是排除双休日和国家法定假日外的授课工作日。这就是计算授课日数或工作日数的问题。
首先打开一个空白工作表,在A1、B1、C1单元格输入“开学时间”、“结束时间”、“法定节日”,然后在其下面的单元格内输入“2003-9-1”、“2004-1-15”、“2003-10-1” “2003-10-2”、“2003-10-3”和“2004-1-1”(后四项必须在C列的“法定假日”下)。
接着可以选中D2单元格,输入公式“=NETWORKDAYS(A2,B2,C2:C5)”。公式中A2引用的是学期(或工作)的开始日期,B2引用的是学期结束的日期,C2:C5区域引用的是作为法定假日的多个日期。输入结束回车即可获得结果95,即2003年9月1日到2004年1月15日,排除四个法定假日后的实际授课日是95天。
2.折旧值计算
无论单位还是家庭,许多固定资产和耐用消费品都存在折旧问题,随着使用时间的延长,其残值在不断减少。假设某单位有一批2000年购进原价8 500元/每台的电脑,预计使用寿命6年,寿命期结束时的资产残值约为1 000元,要求使用第二年内的折旧值。
(1)函数分解
DB函数使用固定余额递减法,计算一笔资产在给定期间内的折旧值。
语法:DB(cost,salvage,life,period,month)
Cost为资产原值;Salvage为资产在折旧期末的价值(也称为资产残值);Life为折旧期限(有时也称作资产的使用寿命);Period为需要计算折旧值的期间。Period必须使用与life相同的单位;Month为第一年的月份数,如省略,则假设为12。
(2)实例分析
为了在参数改变以后仍能进行计算,我们打开一个空白工作表,在A1、B1、C1、D1、E1单元格输入“电脑原值”、“资产残值”、“使用寿命”、“折旧时间”和“折旧值”,然后在其下面的单元格内输入“8500”、“1000”、“6”、“2”。然后选中E2单元格在其中输入公式“=DB(A2,B2,C2,D2)”,回车后即可得到结果“¥1,785.00”,就是说使用期第二年的折旧值为1 785元。如果你要计算其他设备或财产的折旧值,只需改变A2、B2、C2、D2单元格内的数值即可。
3.客流均衡度计算
假设某超市周一到周六的客流人数是16 359、17 254、18 654、15 398、21 689和220 867,总经理需要知道这种情况下的客流分布是否平坦。可以按如下方法计算:
(1)函数分解
KURT函数返回数据集的峰值。峰值反映与正态分布相比某一分布的尖锐度或平坦度。正峰值表示相对尖锐的分布。负峰值表示相对平坦的分布。
语法:KURT(number1,number2,...)
Number1,number2,...是用于计算峰值的1~30个参数。也可以不使用这种用逗号分隔参数的形式,而用单个数组或数组引用的形式。
(2)实例分析
打开一个空白工作表,在A1单元格中输入“一周客流统计”,然后将上述数据依次输入A2、A3等单元格。然后选中A8单元格,在其中输入公式“=KURT(D2:D7)”,回车即可获得结果“-1.719218897”,这说明超市的客流分布与正态分布相比是相对平坦的。假如星期天搞特价促销,客流增加到了50 867人,则计算结果就会变为“5.45379941”。说明超市的客流分布与正态分布相比比较尖锐了,特价促销对客流的影响还是非常大的。
需要指出的是,KURT函数在教育统计等领域也有广泛用途,假如把函数引用的区域修改为许多分数的集合,就可以知道考试成绩的分布是否尖锐或平坦。
4.销售额预测
假设某超市周一到周日的日销售额分别为13、17、16、15、19、21和22(万元),总经理需要预测今后一周内的日销售额的最高值和最低值。可以按如下方法进行预测:
(1)函数分解
TREND函数返回一条线性回归拟合线的值。即找到适合已知数组known_y's和known_x's的直线(用最小二乘法),并返回指定数组new_x's在直线上对应的y值。
语法:TREND(known_y's,known_x's,new_x's,const)
Known_y's是关系表达式y=mx+b中已知的y值集合;Known_x's是关系表达式y=mx+b中已知的可选x值集合;New_x's为需要函数 TREND返回对应y值的新x值;Const为一逻辑值,用于指定是否将常量b强制设为0。
(2)实例分析
首先要打开一个空白工作表,在A1单元格中输入“日销售额”,然后将上述数据依次输入A2、A3至A8单元格。然后选中B2至B8区域,在Excel的编辑栏输入公式“=TREND(A2:A8)”,回车即可在B2至B8区域获得7个结果,其中最高销售额为21.64万元,最低销售额为13.5万元。
与KURT函数一样,TREND函数可以用于教育统计中的学生入学数的峰值和低谷,铁路运输领域的客流高峰和低谷等的预测。
5.客流与营业额的相关分析
(1)函数分解
CORREL函数返回单元格区域array1和array2之间的相关系数。使用相关系数可以确定两种属性之间的关系。
语法:CORREL(array1,array2)
Array1为第一组数值单元格区域;Array2为第二组数值单元格区域。
(2)实例分析
假设一个超市要分析客流量与营业额是否相关。首先运行Excel打开一个空白工作表,在A1至A31单元格输入八月份的每日客流人数,然后在B1至B31输入八月份每日的营业额,再将上述两个区域的名称定义为“日客流人数”和“日营业额”。
接下来就可以选中工作表中的某个空白单元(例如B32),作为存储运算结果的位置。在Excel的编辑栏输入公式“=CORREL(日客流人数,日营业额)”,回车后即可在公式所在单元格看到相关系数的计算结果。
上式中CORREL函数返回“日客流人数”和“日营业额”两个数据集合的相关系数,实际应用中必须根据要分析的数据集合对引用区域进行修改。
与其他计算不同,CORREL函数计算出的相关系数必须进行分析,才能得出两个数值之间是否相关的结论。统计理论根据各种因素(如“日客流人数”和“日营业额”)相互影响的关系,把相关分为正相关、负相关和零相关三种类型。所谓正相关就是两个因素的变化方向相同,即同时变大或变小,例如气温和冷饮销量就是正相关;负相关就是两个因素的变化方向相反,即一个变大(小)另一个变小(大),例如气温上升和羽绒服销量就是负相关;零相关就是两个因素的变化方向无规律,即不存在相互之间影响的情况,例如学生的考试成绩和面粉的销量就是零相关。
为了帮助不太熟悉统计理论的用户掌握CORREL函数的使用,这里使用相关程度分析的理论修改公式“=CORREL(日客流人数,日营业额)”,使之成为下面这种形式,从而更加直观的给出两列数据相关程度的结论。
“=IF((ABS(CORREL(日客流人数,日营业额)))<=0.3,"相关程度低",IF((ABS(CORREL(日客流人数,日营业额)))<=0.5,"相关程度一般",IF((ABS(CORREL(日客流人数,日营业额)))<=0.7,"相关程度较高",IF((ABS(CORREL(日客流人数,日营业额)))<=0.9,"相关程度高",IF((ABS(CORREL(日客流人数,日营业额)))<=1,"相关程度极高")))))”
公式中的“CORREL(日客流人数,日营业额)”部分还是计算日客流人数和日营业额两列数据的相关系数。由于这里只需要了解相关程度,所以使用ABS函数返回相关系数的绝对值。整个公式中由左往右的下一个IF语句就是上一个IF语句的参数。例如第一个逻辑判断表达式“(ABS(CORREL(日客流人数,日营业额)))<=0.3”为“真”(成立),则公式所在单元格就会被填入“相关程度低”;如果第一个逻辑判断表达式“(ABS(CORREL(日客流人数,日营业额)))<=0.3”为“假”(不成立),则计算第二个IF语句“IF((ABS(CORREL(日客流人数,日营业额)))<=0.5”;以此类推直至计算结束。