书城计算机办公软件高级应用
8915300000017

第17章 函数与公式(4)

在建立正表之前,首先在第1行填写上统计日期,因为该日期对后面的具体销售统计有着重要的影响。其次就在下面的第3行填写上表头,这里设置7个项目,分别是:商品名称、类别、品牌、销售数量、销售金额、成本金额以及利润金额。完成了以上的基本设置之后,就可以对表进行内容的设置了,其操作步骤如下:

步骤1:填写商品名称。需要注意,所有的商品名称应该是“商品资料表”中所包含的商品,因为在后面的两项自动填写是通过函数查找商品资料表来完成的。

步骤2:利用函数来自动填写“类别”和“品牌”。以“A4”单元格商品“S900”为例,在单元格“B4”和单元格“C4”分别输入“=IF($A4="","",VLOOKUP($A4,商品资料,2,0))”和“=IF($A4="","",VLOOKUP($A4,商品资料,3,0))”。上述的两个函数表示的就是在单元格“A4”非空的情况下,查找“商品资料表”中的第2列和第3列,并取得其相对应的数值作为返回值返回。

步骤3:在输入完成所有的商品时,可利用数组公式对“销售数量”、“销售金额”、“成本金额”3项进行计算,在这里的计算需要用到“销售清单表”。对于这3项的数据输入,可以根据数组公式的输入步骤进行(详见4.4.1数组的概述)。本例在进行数据选择时,首先根据时间进行判断,因为这里统计的是月销售情况,因此从整个销售清单中,需要的只是该月所产生的销售记录,之后再对有效数据进行求和,来完成一项数据的统计工作。例如,在“销售金额”项中,输入的数组公式为“=SUM(IF(MONT H(销售清单!$A$2:$A $199)=MONT H($B $1),IF(销售清单!$B $2:$B $36=$A4,销售清单!$M$2:$M$36*销售清单!

$L $2:$L $36)))”。该公式就是先判断时间是否与单元格“B2”中时间的月份相同,然后再计算该商品所对应的每条销售记录,根据其单价和数量计算它们的销售金额,最后使用SUM 函数将所计算出的所有销售金额相加,得到最终的计算结果。

步骤4:完成了“销售数量”、“销售金额”、“成本金额”三项计算之后,最后计算“利润金额”。对于该项的计算,只需要运用简单的公式即可完成。以单元格“G4”为例,只需要在单元格“G4”中输入“=E4-F4”。

经过上述4个步骤,就可便捷、快速地建立起一张销售统计表,以供管理者参考和决策。

4.5Excel的函数介绍

4.5.1财务函数

财务函数是财务计算和财务分析的专业工具,有了这些函数的存在,可以很方便地解决复杂的财务运算,在提高财务工作效率的同时,更有效地保障了财务数据计算的准确性。

其具体操作步骤如下:

步骤1:在图4‐59中选中相应的单元格(如E1、E2、E3、E4)。

步骤2:在各个单元格中使用PMT 函数,从弹出的参数设定窗口设定相应的参数,其中在各个单元格中输入的函数为:

E1:=PMT(B3,B2,B1,0,1);

E2:=PMT(B3,B2,B1,0,0);

E3:=PMT(B3/12,B2*12,B1,0,1);

E4:=PMT(B3/12,B2*12,B1,0,0)。

步骤3:每个单元格设定好参数以后,单击“确定”即可计算出相应的还款金额。

2.使用IPMT函数计算贷款每月应付的利息额

IPMT 函数是基于固定利率及等额分期付款方式,返回投资或贷款在某一给定期限内的利息偿还额,其完整的格式为:

IPMT(rate,per,nper,pv,fv)

其中,rate表示的是各期利率;per 表示的是用于计算利息数额的期数,介于1~nper之间;nper表示总投资(或贷款)期,即该项投资(或贷款)的付款期总数;pv表示的是从该项投资(或贷款)开始计算时已经入账的款项,或一系列未来付款当前值的累积和;fv表示的是未来值,或在最后一次付款后希望得到的现金余额,如果忽略该值,将自动默认为0。

例如,以上例中的贷款偿还表为例,计算前6个月应付的利息金额为多少元。

其具体操作步骤如下:

步骤1:选中相应的单元格(如E6、E7、E8、E9、E10、E11)。

步骤2:在各个单元格中使用IPMT 函数。从弹出的参数设定窗口设定相应的参数,其中在各个单元格中输入的函数为:

E6:=IPMT($B$3/12,1,$B$2*12,$B$1,0);

E7:=IPMT($B$3/12,2,$B$2*12,$B$1,0);

E8:=IPMT($B$3/12,3,$B$2*12,$B$1,0);

E9:=IPMT($B$3/12,4,$B$2*12,$B$1,0);

E10:=IPMT($B$3/12,5,$B$2*12,$B$1,0);

E11:=IPMT($B$3/12,6,$B$2*12,$B$1,0)。

步骤3:每个单元格设定好参数以后,单击“确定”即可计算出相应的还款金额。

3.使用FV 函数计算投资未来收益值

FV 函数是基于固定利率及等额分期付款方式,返回某项投资的未来值,其完整的格式为:

FV(rate,nper,pmt,pv,type)

其中,rate 表示的是各期利率;nper 表示总投资(或贷款)期,即该项投资(或贷款)的付款期总数;pmt 表示的是各期所应支付的金额;pv 表示的是现值,即从该项投资开始计算时已经入账的款项,或一系列未来付款的当前值的累积和,也称为本金;type 是一个逻辑值,用以指定付款时间是在期初还是在期末,1表示期初,0表示期末,其默认值为0。

例如,现该店铺管理者为某项工程进行投资,先投资50000元,年利率6%,并在接下来的5年中每年再投资5000元。那么5年后应得到的金额是多少?

其具体操作步骤如下:

步骤1:选定相应的单元格(如C6)。

步骤2:在选定的单元格中使用FV函数,从弹出的参数设定窗口设定相应的参数。

步骤3:单击“确定”,即可完成FV函数的输入。

4.使用PV 函数计算某项投资所需要的金额

PV 函数计算的是一系列未来付款当前值的累积和,返回的是投资现值,完整格式为:

PV(rate,nper,pmt,fv,type)

其中,rate 表示的是贷款利率;nper 表示的是该项贷款的总贷款期限或者总投资期;pmt表示的是各期所应支付的金额;fv 表示的是未来值,或在最后一次付款后希望得到的现金余额,如果忽略该值,将自动默认为0;type 是一个逻辑值,用以指定付款时间是在期初还是在期末,1表示期初,0表示期末,其默认值为0。

例如,某个项目预计每年投资15000元,投资年限10年,其回报年利率是15%,那么预计投资多少金额?

其具体操作步骤如下:

步骤1:选定相应的单元格(如B12)。

步骤2:在选定的单元格中使用PV 函数,从弹出的参数设定窗口设定相应的参数。

步骤3:单击“确定”即可完成PV 函数的输入。

5.使用SLN 函数计算设备每日、每月、每年的折旧值

SLN 函数计算的是某项资产在一个期间中的线性折旧值,其完整的格式为:

SLN(cost,salvage,life)

其中,cost表示的是资产原值;salvage表示的是资产在折旧期末的价值,即资产残值;life表示的是折旧期限,即资产的使用寿命。

例如,该店铺企业拥有固定资产总值为50000元,使用10年后的资产残值估计为8000元,那么每天、每月、每年固定资产的折旧值为多少?

具体操作步骤如下:

步骤1:选定相应的单元格(如B4、B5、B6)。

步骤2:在选定的单元格中使用SLN 函数,从弹出的参数设定窗口设定相应的参数。在各个单元格中输入的函数为:

B4:=SLN(A2,B2,C2*365)

B5:=SLN(A2,B2,C2*12)

B6:=SLN(A2,B2,C2)

步骤3:每次设定好参数以后,单击“确定”即可计算出相应的还款金额。

4.5.2文本函数

在Excel2003中,用户常常会遇到比较两个字符串的大小,改变文本标题设置等操作,这时可以使用Excel函数库中的文本函数,来帮助用户设置关于文本方面的操作。

文本函数可以处理公式中的文本字符串,在Excel2003函数库中包含了所示的文本函数。

下面介绍几个常用的文本函数:

1.EXACT函数

EXACT函数是用来比较两个文本字符串是否相同。如果两个字符串相同,则返回“TRUE”,反之,则返回“FALSE”。需要注意的是,EXACT函数在判别字符串的时候,会区分英文的大小写,但不考虑格式设置的差异。其完整的格式为:

EXACT(text1,text2)

其中,参数text1和text2表示的是两个要比较的文本字符串。例如,在A1单元格中输入“Excel2003”,在A2单元格中输入“excel 2003”。然后在A3单元格使用EXACT函数来比较单元格A1和A2的内容,即在A3单元格中输入函数“=EXACT(A1,A2)”。由于A1单元格的第1个英文字母“E”和A2单元格的第1个英文字母“e”有大小写的区别,所以执行函数会返回“FALSE”,表示两个单元格的内容不同。另外,在字符串中如果有不同的空格,也会被视为不同。

2.CONCATENATE 函数

CONCATENATE 函数是将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中,其完整的格式为:

CONCATENATE(text1,text2)

其中,参数text1,text2表示的是需要连接的字符文本或引用的单元格,该函数最多可以附带30个参数。需要注意的是,如果其中的参数不是引用的单元格,且为文本格式的,请给参数加上英文状态下的双引号。

另外,如果将上述函数改为使用“&;”符连接也能达到相同的效果。因为“&;”是一个运算符号,也是一个连接符号,它有把两个文本字符或文本字符串连接起来的功能。例如,在单元格当中输入函数“=CONCATENATE(A14,"@",B14,".com")”和在单元格中输入公式“=A14&;"@"&;B14&;".com"”,两者达到的效果是相同的。

3.SUBSTITUTE 函数

SUBSTITUTE 函数是实现替换文本字符串中的某个特定字符串,其完整的格式为:

SUBSTITUTE(text,old_text,new_text,instance_num)

其中,参数text 是原始内容或是单元格地址,参数old_text 是要被替换的字符串,参数new_text是替换old_text的新字符串。执行函数实现的是将字符串中的old_text部分以new_text替换。如果字符串中含有多组相同的old_text 时,可以使用参数instance_num来指定要被替换的字符串是文本字符串中的第几组。如果没有指定instance_num的值,默认情况下,文本中的每一组old_text都会被替换为new_text。

4.REPLACE 函数

REPLACE 函数与SUBSTITUTE 函数具有类似的替换功能,但它的使用方式较SUBSTITUTE函数稍有不同——REPLACE函数可以将某几位的文字以新的字符串替换,例如,将一个字符串中的前5个字用“@”替换。

REPLACE 函数的具体语法结构为:

REPLACE(old_text,start_num,num_chars,new_text)

其中,参数old_text 是原始的文本数据,参数start_num 可以设置要从old_text的第几个字符位置开始替换,参数num_chars可以设置共有多少字符要被替换,参数new_text则是用来替换新的字符串。

5.SEARCH 函数

SEARCH 函数是用来返回指定的字符串在原始字符串中首次出现的位置。一般在使用时,会先用SEARCH函数来决定某一个字符串在某特定字符串的位置,再得用REPLACE函数来修改此文本。

SEARCH函数的具体语法结构为:

SEARCH(find_text,within_text,start_num)

其中,参数find_text 是要查找的文本字符串,参数within_text 则指定要在哪一个字符串查找,参数start_num则可以指定要从within_text 的第几个字符开始查找。需要注意的是,在find_text 中,可以使用通配符,例如:问号“?”和星号“*”。其中问号“?”代表任何一个字符,而星号“*”可代表任何字符串。如果要查找的字符串就是问号或星号,则必须在这两个符号前加上“~”符号。