1.3 常用函数及其应用

Excel中有大量的内置函数可供用户使用，利用这些函数对数据进行计算与分析，不仅可以大大提高工作效率，而且不容易出错。

在Excel 2003中，除了有大量的内置函数以外，系统还提供了分析工具库，其中也有一些非常有用的函数，特别是一些日期函数，具有很广泛的用途。

与Excel 2003相比，Excel 2007提供了更多的函数，特别是在Excel 2003中只能在加载分析工具库后才能使用的函数，在Excel 2007中不用加载分析工具库就可以正常使用。

除此之外，用户还可以自己编写自定义函数，这些函数也可以像工作表函数那样使用。

1.3.1 快速输入嵌套函数的方法

当一个函数中的参数为另外一个函数时，就是在使用嵌套函数。

在实际工作中，很多问题单独使用一个函数是无法解决的，而是需要使用多个函数，其中某些函数又是其他函数的参数，这样就构成了函数套函数形式的计算公式。

在Excel 2003和Excel 2007中输入嵌套函数的方法是完全相同的。输入嵌套函数公式可以直接在单元格或公式编辑栏中进行，也可以利用“函数参数”对话框进行。除非读者非常熟悉所使用的各函数的语法要求和使用方法，否则最好使用“函数参数”对话框一步一步进行操作，这样可以避免在输入公式的过程中出现错误，而且还有助于读者了解函数的功能和使用方法。

例如，要在单元格A2中输入公式“=SUM（A1,B1,AVERAGE（C1:E1））”，具体步骤如下。

1 单击单元格A2，再单击公式编辑栏左边的“插入函数”按钮，在“插入函数”对话框中选择SUM函数，则系统会出现SUM函数的“函数参数”对话框，如图1-45所示。

2 在SUM函数对话框的第一个参数输入栏中输入A1，在第二个参数输入栏中输入B1。

3 当出现第3个参数输入栏时，单击公式编辑栏左侧的函数下拉箭头，系统会弹出函数下拉列表框，如图1-46所示。

4 在函数下拉列表框中选择函数AVERAGE，则系统会弹出函数AVERAGE的“插入函数”对话框，在此函数的“函数参数”对话框中输入C1:E1。

5 单击“确定”按钮，即可完成嵌套函数的输入工作。

1.3.2 数据的逻辑判断与处理

对数据进行逻辑判断与处理，一般要使用IF函数、AND函数、OR函数。

IF函数用于对真假值进行判断，并根据逻辑计算的真假值，返回不同结果。可以使用IF函数对数值和公式进行条件检测。

AND函数用于判断几个条件是否都满足。如果所有的条件都满足，AND函数会返回TRUE，否则，只要有一个条件不满足就返回FALSE。

OR函数用于判断几个条件是否有一个或多个都满足。只要有一个条件满足时， OR函数即返回TRUE，只有当所有条件都不满足时才返回FALSE。

此外，Excel 还提供了一些信息函数，利用这些函数可以获得指定单元格的相关信息。这些函数很少单独使用，一般是与 IF函数一起使用，用于构造一些比较复杂的条件。常用的信息函数有ISERROR、ISNUMBER、ISTEXT函数等。

ISERROR函数用于检验单元格数据是否为任意错误值，如果是错误值，其返回结果是TRUE；如果不是错误值，其返回结果是FALSE。

ISNUMBER函数用于判断单元格数据是否为数值，如果是数值，其返回结果是TRUE；如果不是数值，其返回结果是FALSE。

ISTEXT函数用于判断单元格数据是否为文本，如果是文本，其返回结果是TRUE；如果不是文本，其返回结果是FALSE。

1.3.3 处理日期数据

Excel提供了各种日期和时间函数，在日常会计核算和财务管理工作中，最常用的日期函数有TODAY函数、DATE函数、YEAR函数、MONTH函数、DAY函数、EOMONTH函数、EDATE函数、WEEKDAY函数、WEEKNUM函数等。在Excel 2003中，一些不常用的特殊函数被放到了分析工具库中，并作为加载宏供用户选择使用。如果读者使用的是Excel 2003版本，并且想要使用分析工具库中的函数，可以单击“工具”→“加载宏”命令，打开“加载宏”对话框，在“可用加载宏”列表中选择“分析工具库”复选框，如图1-47所示，然后单击“确定”按钮。这样就会新增许多函数可供使用了。

下面分别介绍上述的各种日期函数的功能。

1.TODAY函数

TODAY函数返回计算机系统当前日期的序列号。该函数没有参数，但在使用它时，不能省略TODAY后面的左右小括号。

2.YEAR、MONTH、DAY函数

YEAR函数用于获取指定日期序列号的年份数字，值为1900到9999之间的整数。

MONTH函数用于获取指定日期序列号的月份数字，值为1到12之间的整数。

DAY函数函数用于获取指定日期序列号的日数字，值为1到31之间的整数。

3.D AT E函数及其应用

DATE函数用于将三个数字组成一个日期序列号，其语法格式为：

=DATE（年,月,日）

例如，公式“=DATE（2010,2,23）”的运行结果是 2010-2-23。

4.WEEKDAY函数及其应用

WEEKDAY函数用于获取某日期为星期几。默认情况下，其值为1（星期天）到7（星期六）之间的整数。

5.EDATE函数及其应用

EDATE函数的功能是获取指定日期往前或往后几个月的日期。对Excel 2003来说，该函数是分析工具库里的函数，但在Excel 2007中该函数可随时使用。

6.EOMONTH函数及其应用

EOMONTH函数的功能是获取指定日期往前或往后几个月的特定月份的月底日期。对Excel 2003来说，该函数是分析工具库里的函数，但在Excel 2007中该函数可随时使用。

7.WEEKNUM函数及其应用

WEEKNUM函数用于判断指定日期是该年的第几周。对Excel 2003来说，该函数是分析工具库里的函数，但在Excel 2007中该函数可随时使用。

1.3.4 处理文本数据

在财会日常工作中，常用的文本函数有LEN函数、LEFT函数、RIGHT函数、MID函数、TEXT函数、FIND函数等。

1.LEN函数和LENB函数

LEN函数和LENB函数的功能都是用于获取字符串的长度。其中，LEN用于获取文本字符串中的字符数，LENB用于获取文本字符串中用于代表字符的字节数。

2.LEFT函数和LEFTB函数

LEFT函数和LEFTB函数的功能都是用于获取字符串左边指定个数的字符，不过LEFT是以字符串的字符数为单位，而LEFTB是以字符串的字节数为单位。

3.RIGHT函数和RIGHTB函数

RIGHT函数和 RIGHTB函数的功能都是用于获取字符串右边指定个数的字符，不过RIGHT是以字符串的字符数为单位，而RIGHTB是以字符串的字节数为单位。

4.MID函数和MIDB函数

MID函数和 MIDB函数的功能都是用于获取字符串从指定位置开始指定个数的字符，不过MID是以字符串的字符数为单位，而MIDB是以字符串的字节数为单位。

5.FIND函数和FINDB函数，SEARCH函数和SEARCHB函数

FIND函数和FINDB函数的功能都是用于在区分大小写的情况下，查找某字符在字符串中第一次出现的位置，其中FIND是以字符串的字符数为单位，而FINDB是以字符串的字节数为单位。

6.文本函数综合应用案例——分离会计科目编码和科目名称

对于如图1-48所示的会计科目数据，科目编码与科目名称是紧密连在一起的，但是科目编码的长度不一。在这种情况下，无法直接利用LEFT函数将科目编码剥离出来。考虑到会计科目数据仅仅是由数字和汉字（如果有括号、冒号、逗号等符号，这些符号也是全角符号）组成，而每个汉字（也就是全角字符）有两个字节，因此可以使用LENB函数和LEN函数对数据长度进行必要的计算，再利用LEFT函数和RIGHT函数将科目编码剥离出来。

具体步骤如下：

在单元格B2中输入公式“=LEFT（A2,2*LEN（A2）-LENB（A2））”，得到科目编码数字文本。

在单元格C2中输入公式“=RIGHT（A2,LENB（A2）-LEN（A2））”，得到科目名称。

将单元格区域B2和C2向下填充复制，即得到需要的结果，如图1-49所示。

1.3.5 数据计数统计

在对数据进行计数统计时，使用最多的函数是COUNTA和COUNTIF函数。

COUNTA函数的功能是统计单元格区域内非空单元格的个数。在设置数据有效性和条件格式时，常常会用到这个函数。

COUNTIF函数的功能是统计数据区域内满足条件的单元格个数，例如利用它可以统计订单数、某部门人数、某类别的客户数等。不过，COUNTIF函数只能根据一个条件进行计数统计，如果有多个条件，就必须使用SUM函数构建数组公式或者使用SUMPRODUCT函数，或者使用Excel 2007的COUNTIFS函数。

1.3.6 数据求和汇总

在实际工作中经常会遇到要对数据进行求和汇总的问题，有时要进行无条件的求和汇总，有时要在满足各种条件下进行求和汇总。Excel中有一些常用的求和汇总函数可以很方便地用来解决这些问题。

1.常用的求和汇总函数

Excel中常用的求和汇总函数包括SUM函数、SUMIF函数、SUMPRODUCT函数等。在Excel 2007中还有SUMIFS函数。

SUM函数的功能是用于计算某一单元格区域中所有数据之和。

SUMIF函数的功能是根据指定条件对若干单元格求和。

SUMPRODUCT函数用于计算几组数组间对应元素乘积之和。

SUMIFS函数是Excel 2007的新增函数，其功能是对某一区域内满足多重条件的单元格求和。

2.求和汇总问题实际案例

在实际工作中，求和汇总的情况是多种多样的，要根据实际问题和实际表格结构，采用不同的函数和公式，如果是多条件求和，就需要使用数组公式。下面介绍几个求和汇总问题的实际案例。

（1）利用SUMIF函数简化计算

在图1-50中，由于表格要经常插入一些新产品，删除一些旧产，造成求和公式经常会出现错误。这个表格的26行是求和汇总公式，计算公式为：

=C4+C7+C10+C13+C16+C19+C22+C25

例如，如果把产品C对应的第8、9、10行删除，上述的公式就会变为下面的情形而出现错误#REF!：

=C4+C7+#REF!+C10+C13+C16+C19+C22

显然，这个公式并不科学。仔细分析表格结构和计算项目，实际上使用SUMIF函数就可克服该问题。公式修改如下：

单元格C28：=SUMIF（$B$2:$B$25,"小计",C2:C25）

（2）利用SUM或SUMPRODUCT函数简化计算

在图1-51中，由于表格中的项目很多，在计算净利润时采用了逐个总账科目金额相加的方法设计公式，一不小心就容易出错。

例如，单元格C120的计算公式为：

=C2+C7+C8+C9+C10+C11+C22+C23+C24+C70+C110+C116+C117+C118

这个计算公式是非常不科学的，除了在输入公式时容易加错单元格外，如果因会计科目增加而插入行，或者因减少而删除行，那么又得重新输入或修改公式。

实际上，使用数组公式或者使用SUMPRODUCT函数就可以快速准确地创建净利润的计算公式，因为净利润是根据总账科目计算的，而总账科目的编码总是4位数字，因此可以从A列的科目编码长度来判断而进行加总，这样的公式永远是不会错的。

本例中，可以将单元格C120中的计算公式修改为如下的两种形式。

公式1：=SUM（（LEN（$A$2:$A$118）=4）*C2:C118），是一个数组公式。

公式2：=SUMPRODUCT（（LEN（$A$2:$A$118）=4）*1,C2:C118），是一个普通公式

1.3.7 数据查找与汇总

数据查询是日常财务管理工作中经常遇到的问题，是在Excel中执行得最频繁的操作，也是很复杂的操作。在利用函数进行数据查询时，往往只使用一个函数的情况，而是多个函数联合使用。下面介绍几个常用的查找和引用函数及其在数据查询中的应用案例。

Excel提供了10多个查找和引用函数，这些函数都是非常有用的，其中应用最多的有VLOOKUP函数、MATCH函数、INDEX函数、CHOOSE函数、INDIRECT函数、OFFSET函数、ROW函数、COLUMN函数等。值得注意的是，一个数据查询问题，可能有很多种解决方法，至于采用什么方法，主要取决于读者的喜好。查找数据函数的使用原则是：简单和准确。下面结合实际案例介绍这些常用函数的使用方法和技巧。

1.MATCH函数

MATCH函数用于在指定方式下查找与指定数值匹配的数组中元素的相应位置。

2.INDEX函数

INDEX函数用于涉及两个变量的查询，也就是取出列表或数组中的指定由行序号和列序号交叉处的值。

3.VLOOKUP函数

VLOOKUP函数用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

4.INDIRECT函数

INDIRECT函数的功能是返回由文本字符串指定的引用。

5.OFFSET函数

OFFSET函数是一个功能非常强大的查找和引用函数，它在动态定位单元格和单元格区域方面，有着巨大的优越性和方便性。尤其是利用OFFSET函数定义动态区域名称，可以绘制动态图表，制作动态的数透视表，进行动态的求和汇总计算。

OFFSET函数的功能是以指定的引用为参照系，通过给定偏移量得到新的引用。OFFSET函数返回的引用可以为一个单元格或单元格区域。

动态查询某月销售额及累计销售额

对于图1-52所示的销售数据记录清单，现要求设计一个查询系统：指定月份和商品后，将该商品在指定月份以前各月（包括指定月份）的销售额显示出来，同时计算出截止到指定月份该商品的累计销售额。

查询各个月份的数据可以使用OFFSET函数来解决（当然也可以使用其他函数，可以多列举几种方法，并进行比较），而计算截止到指定月份该商品的累计销售额，则需要先利用OFFSET函数得到一个动态的数据区域，然后再利用SUM函数对这个数据区域求和。

下面我们介绍这个查询系统的具体设计方法和步骤。

1 选择单元格B12，设置数据有效性的“序列”条件规则，以便于快速准确输入月份。

2 选择单元格B13，设置数据有效性的“序列”条件规则，以便于快速准确输入商品名称。

3 合并单元格区域A15:M15，并输入公式：

="截止到"&B12&"商品"&B13&"的销售额明细数据"。

4 在单元格A16和A17中分别输入“月份”和“销售额”。

5 在单元格B16输入下面的查询月份数据的公式：

=IF（COLUMN（）-1＞MATCH（$B$12,$B$2:$M$2,0）,"",OFFSET（$A$2,,COLUMN（）-1））

然后将单元格B16向右填充复制到单元格M16。

6 在单元格B17输入下面的查询销售额数据的公式：

=IF（B16="","",OFFSET（$A$2,MATCH（$B$13,$A$3:$A$9,0）,MATCH（B16,$B$2:$M$2,0）））

然后将单元格B17向右填充复制到单元格M17。

7 合并单元格区域A19:D19，并输入公式：

="截止到"&B12&"商品"&B13&"的累计销售额"。

8 在单元格E19中输入公式：

=SUM（OFFSET（$A$2,MATCH（$B$13,$A$3:$A$9,0）,,,MATCH（$B$12,$B$2:$M$2,0）+1））

计算截止到指定月份某商品的累计销售额。

这样，只要在单元格B12输入月份，在单元格B13输入商品名称，就立即得到该商品截止到指定月份的销售额数据明细并计算出截止到指定月份该商品的累计销售额，如图1-52所示。

这几个公式是根据本表格的结构来设计的，请读者仔细分析这几个公式中各个函数的使用方法和技巧。

为动态单元格区域定义名称

在有些情况下，我们需要将单元格区域、某列或者某行定义一个动态的名称，以便数据增加或减少后，该名称代表的数据区域自动调整，此时就必须联合使用OFFSET函数和COUNTA函数来进行定义了。

在如图1-53所示的工作表中，假若要定义一个名称“Data”，它是这样的一个单元格区域：以单元格A1为左上角单元格，数据区域的行数是根据A列中有数据单元格个数来确定，数据区域的列数是根据第1行中有数据单元格个数来确定，这里假定该数据清单的A列和第一行是关键数据，在数据区域内不允许有空单元格存在，那么就可以使用下面的引用公式进行定义了：

=OFFSET（$A$1,,,COUNTA（$A:$A）,COUNTA（$1:$1））

假若要将数据区域的A列定义为一个动态的名称“日期”，将B列定义为一个动态的名称“销售量”，将C列定义为一个动态的名称“销售额”，它们的大小（即行数）都是根据A列有数据的单元格个数来确定，那么就有下述的名称及其引用公式：

日期：=OFFSET（$A$1,,,COUNTA（$A:$A）,1）

销售量：=OFFSET（$B$1,,,COUNTA（$A:$A）,1）

销售额：=OFFSET（$C$1,,,COUNTA（$A:$A）,1）

在这些名称引用公式中，都要使用COUNTA函数来统计A列中不为空的单元格个数，而这个数字就是动态的单元格区域的行数。

1.3.8 处理计算误差

Excel的计算误差功能在有些情况下是差强人意的。例如，在单元格A1输入下面的公式“=6.1-6.2+1”，其结果应该是0.9。不过，如果使用公式“=A1=0.9”进行判断的话，就会得到FALSE的结果，原因就是单元格A1的结果并不等于0.9，将单元格的小数位数显示为15位，就会发现单元格的数字实际上是0.899999999999999。显然，将这个数字与0.9进行比较，结果无疑是FALSE。

造成这种现象的原因是，对于小数的计算，Excel会先对6.1-6.2进行浮点计算，得出一个近似值，并以二进制储存这个中间计算结果，最后才将这个中介结果取出并加1，从而造成了计算误差。

为了解决Excel的浮点计算误差，可以使用ROUND函数。ROUND函数是大家最熟悉的四舍五入函数了，其功能就是将数值舍入到最接近的数字。语法如下：

=ROUND（数值,四舍五入位数）

这里，函数的四舍五入位数可以是正数，也可以是负数。正数时对小数点进行四舍五入，负数时对整数部分进行四舍五入。

例如：

=ROUND（204.2553,1） 结果为204.3

=ROUND（204.2553,0） 结果为204

=ROUND（204.2553,-2） 结果为200

=ROUND（284.7553,-2） 结果为300

但是，在有些情况下，使用ROUND函数又会带来舍入误差，那么，要处理这样的误差，就需要根据实际情况采用不同的处理方法了。

图1-54就是一个示例数据。黄色区域的余额数据看上去为零，依此判断在I列对应单元格中的结论应该是“平”，但是，Excel 给出的结论却是“贷”，这究竟是怎么回事呢？

将单元格I14、I17和I19的数字增加小数位数，如图1-55所示，可以看到，这三个单元格的数字并不是零，而是非常小的负数！尽管这张工作表的计算公式很简单，并没有进行什么复杂的计算，但还是造成了计算误差，导致判断错误。

要解决这样的问题，我们可以利用ROUND函数将余额四舍五入到两位小数，就会解决图1-54的问题，从而得到正确的结果，如图1-56所示。此时，单元格J12的计算公式为“=ROUND（G11-H11,2）”，其他单元格以此类推。