作者：韩良智
出版社：电子工业出版社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
Excel高效财务管理应用之道(修订版)(无光盘提供)试读：
前言
在实际工作中，企事业单位的财会人员经常需要处理大量的财务数据以及设计和使用各种财务表格并解决各种不同类型的财务管理问题。熟练地掌握、运用Excel，高效地处理财务数据和解决各种财务管理问题的方法和技巧，对于财会人员从繁重的日常工作中解脱出来具有重要的现实意义。
本书适合的读者
尽管目前有些财会人员已经具备了运用Excel对财务数据进行一般的计算和编制简单报表的能力，但从总体情况来看，Excel提供的很多功能并未在实际工作中得到广大财会人员的充分利用。本书正是为了满足广大财会人员系统地学习Excel基本知识并快速地提高运用Excel解决实际财务管理问题的技能水平而设计的。此外，对于那些即将走向社会的高等院校财会专业以及相关的经济管理类的学生来说，学习和掌握、运用Excel解决实际财务问题的方法和技能无疑会有助于他们在职场竞争中获得更多的机会，并可以为今后从事实际工作奠定坚实的基础，因此本书对于他们也同样会是非常有用的。
本书的主要内容
本书的内容分为两篇12章。第1篇“不可忽视的基础工作”共包括5章，结合实际案例介绍Excel的基础知识，以及运用Excel做好日常财务管理基础工作的实用方法和技巧。其中，第章介绍了Excel公式和函数及其灵活使用的相关知识，包括创建公式和快速复制公式、引用单元格的方式、将公式转换为数值、显示或隐藏和保护公式、运用名称公式和数组公式，以及调用函数和快速输入嵌套函数的方法，还进一步介绍了处理日期数据、数据计数统计、数据求和汇总、数据查找与汇总、处理计算误差等常用函数的功能和使用方法；第2章介绍了运用Excel的筛选、排序、分类汇总和分级显示功能处理财务数据的方法；第3章分别介绍了使用相关函数、使用合并计算工具和使用数据透视表汇总财务数据的方法，并进一步介绍了采用导入数据SQL语言方法以及采用Microsoft Query工具导入和汇总财务数据的方法；第4章介绍了运用xcel高效地对各种不同类型的财务数据进行规范与整理的方法；第5章介绍了灵活运用数据有效性和条件格式，以及自定义数字格式等功能设计与美化财务表格的实用技巧。第2篇“切实可行的财务案例”共包括7章，结合大量的实际案例介绍了运用第1篇介绍的各种Excel功能以及单变量求解、模拟预算表、规划求解和其他数据分析工具、窗体控件和图表工具等高效地解决成本费用管理、销售收入管理、量本利分析、流动资产管理、负债管理、投资项目决策、财务预测与财务报表分析等方面的各种财务管理问题，以及绘制各种精美的组合财务图表和动态财务图表的方法。
本书的第一版自2011年1月出版以来，受到了广大读者的欢迎，曾多次重印。为了更好地满足读者的需求，作者在对本书第一版进行修改的基础之上出版本书的修订版。本书第一版中7.3.1节介绍的是制作精美销售图表的方法，由于这部分内容属于制作和编辑图表的基本知识，且占用篇幅较多，所以在修订版中将这部分内容全部删掉，与制作和编辑图表有关的内容改为在后边的有关案例中介绍。本书第一版中第12章的12.2节介绍的财务报表分析使用的财务报表是旧格式的报表，并且介绍的财务报表的结构分析、趋势分析和财务比率分析的内容有些烦琐，在修订版书中对第12章12.2节中介绍的案例报表的格式进行了更新，有关的财务报表的结构分析、趋势分析和财务比率分析的内容也进行了简化。此外，在修订版书中重点对第一版书中没有编入的各案例文件的编号和格式进行了调整，确保各案例文件的编号全部保留在书中，以方便读者查找相应的案例文件并对照学习本书的相关内容。
本书层次分明，条理清晰，内容丰富，案例实用，操作步骤明确，图文并茂，适合各不同层次的企事业单位财会人员和其他管理人员阅读和学习，也可作为大专院校经济类本科生、研究生和MBA学员的教材或参考书，以及各类Excel财会应用培训班的参考资料。
致谢
在编写本书的过程中，韩小良、翟永俭、贾春雷、韩贵珍、冯岩、张河兵、韩雪珍、韩永坤、韩亦菲、韩舒婷为作者提供了很多帮助。此外，电子工业出版社的张慧敏、俆津平等编辑为本书的编辑和出版付出了大量辛勤的劳动，在此一并向他们表示衷心的感谢。
由于作者水平有限，书中难免存在疏漏和错误之处，敬请读者批评指正。北京科技大学东凌经济管理学院韩良智 教授2013年1月第1篇 不可忽视的基础工作Excel是一个博大精深的软件，在社会经济生活中得到了越来越广泛的应用。在企业的财务管理领域，尽管不少企业目前已经安装了专门的财务软件，从而使财务会计人员免去了手工编制会计报表的麻烦，但使用专门的财务软件不仅成本较高，而且由于其固有的在应用方面灵活性差的缺陷，仍然不能使财会人员完全地从繁重的处理大量财务数据的日常工作中解脱出来。为了透彻地了解企业的财务状况和经营成果，科学合理地预测企业未来的情况，并有效地做出各种财务决策，企业的财务管理人员在日常工作中经常需要使用Excel来处理各种财务会计数据并制作各种图表。现实中，很多财会人员对于Excel的使用技能尚不熟练，往往仅能使用Excel的很少一部分功能，因而处理数据耗时又费力，极大地影响了工作效率。针对这种状况，本篇将结合大量的实际案例介绍Excel在财务管理应用中的一些基础知识和使用技能，期望读者在学习本章的内容后能快速地提高运用Excel解决实际问题的技能水平。第1章 Excel公式和函数及其灵活使用公式和函数是Excel的灵魂。Excel表格与其他表格最本质的区别就在于，用户可以在Excel表格中创建计算公式和使用Excel中的函数来解决实际工作中的各种问题。本章主要介绍Excel的公式和函数及其在财务管理中应用的常用方法和技巧，希望读者通过学习本章的内容，可以快速提高运用Excel公式和函数的技能水平。1.1 公式基础知识
简单地说，公式是指以等号“=”开头的、以运算符将多个元素连接起来的数学表达式。一个公式的最大字符允许长度，对于Excel 2003来说是1024个字符，对于Excel 2007来说是8192个字符。
在Excel中，凡是在单元格中先输入等号（=）然后再输入其他数据的，系统就自动将其判为公式。例如，若在某单元格中输入了“=100”，尽管该单元格显示出的数据为100，但它的真正面目并不是数字100，而是一个公式，其计算结果是100。
从本质上来说，函数也是公式，因此，如果要在单元格中使用函数进行计算，就必须先输入等号，然后再输入函数名称及相应的参数。例如，要计算单元格区域B2:B30的数据之和，必须输入公式“=SUM（B2:B30）”，而不能仅仅输入“SUM（B2:B30）”。
1.1.1 Excel公式的构成
输入到Excel单元格中的计算公式，可由以下几种基本元素组成。
等号（=）：任何公式前面必须是以等号（=）开头。
运算符：运算符是将多个参与计算的元素连接起来的运算符号，Excel公式的运算符包括引用运算符、算术运算符、文本运算符和比较运算符。
常数或字符串：常数是指值永远不变的数据，如10.02，2000等；字符串是指用双引号引起来的文本，如"47838"，"日期"等。
数组：在公式中还可以使用数组，以创建更加复杂的公式。例如，公式“=MMULT（{1，2，3;4，5，6}，{8;9;11}）”的功能是计算两个矩阵{1，2，3;4，5，6}和{8;9;11}的乘积，得到一个新的矩阵，这里在函数MMULT中，就使用了常量数组{1，2，3;4，5，6}和{8;9;11}。
单元格引用：单元格引用是指以单元格地址或名称来代表单元格中的数据进行计算。例如，公式“=A1+B2+200”的含义是将单元格A1的数据和B2的数据，以及常数200相加；公式“=SUM（销售量）”的含义是利用SUM函数对名称“销售量”所代表的单元格区域进行加总计算。
工作表函数和它们的参数：公式的元素可以是函数，例如公式“=SUM（A1:A10）”就使用了函数SUM。
括号：括号主要用于控制公式中各元素运算的先后顺序。要注意区别函数中的括号，函数中的括号是函数不可分割的一部分。
1.1.2 公式的运算符
Excel公式的运算符有引用运算符、算术运算符、文本运算符和比较运算符。下面简要介绍这些公式运算符的含义及其使用方法。
1.引用运算符
引用运算符用于对单元格区域进行合并计算。常见的引用运算符有冒号（:）、逗号（，）和空格，其中：
冒号（:）是区域运算符，用于对两个引用单元格之间所有的单元格进行引用，如A1:B10表示以A1为左上角、B10为右下角的连续单元格区域；A:A表示整个A列；5:5表示第5行。
逗号（，）是联合运算符，用于将多个引用合并。例如，公式“=SUM（A2:A3，A5:D5，E2）”
用于计算单元格区域A2:A3和A5:D5，以及单元格E2的数据总和。
空格是交叉运算符，用于对两个单元格区域的交叉单元格进行引用，例如，公式“=B5:C5 C5:D5”的结果为返回C5单元格的数据。
2.算术运算符
算术运算符用于完成基本的算术运算，按运算的先后顺序，算术运算符包括负号（?）、百分号（%）、幂（^）、乘（*）、除（/）、加（+）、减（?）。
例如，公式“=A1*B1+C1”的含义是将单元格A1和B1数据相乘后，再加上单元格C1数据。公式“=?A1”的含义是将单元格A1的数据转变为负数后，输入到含有该公式的单元格。
3.文本运算符
文本运算符用于将两个或多个值连接起来产生一个连续的文本值，文本运算符主要是文本连接运算符“&”。例如，公式“=A1&A2&A3”的含义是将单元格A1、A2、A3的数据连接起来组成一个新的文本。
4.比较运算符
比较运算符用于比较两个值，并返回逻辑值TRUE（真）或FLASE（假）。比较运算符包括等于（=）、小于（<）、小于等于（<=）、大于（>）、大于等于（>=）、不等于（<>）。当用这些运算符比较两个值时，计算结果为逻辑值TRUE 或 FALSE。
例如，公式“=A1=A2”的含义是比较单元格A1和A2的值，如果A1的值等于A2的值，将返回TRUE，否则将返回FALSE。注意这个公式的左边第一个等号是公式的等号，而第二个等号是比较运算符。
需要注意的是，在对英文字符串利用上述比较运算符进行比较时，是不区分字母大小写的。例如，假设在单元格A1中的数据为字母“a”，在单元格A2中的数据为字母“A”，那么公式=A1=A2”的运算结果会是TRUE。
1.1.3 引用单元格的方式
在Excel表格中要对数据进行计算，既可以在公式中输入数值，也可以输入某数值所在的单元格地址，还可以输入单元格的名称，这种通过输入单元格地址或单元格名称来获取单元格中的数据的方式称为单元格的引用。
例如，公式“=B5*D6+A4”的含义是，把单元格B5的数据与单元格D6的数据相乘后再加上单元格A4的数据，这里的公式就对单元格B5、D6、A4进行了引用。
公式“=SUM（销售额）”的含义是对名称为“销售额”代表的单元格区域进行加总计算，这里就是引用了名称为“销售额”代表的单元格区域。
如果仅需要在一个单元格中输入公式，并不需要对公式进行复制，那么在公式中以什么方式对单元格进行引用是无关紧要的。但是，如果在某个单元格中输入完公式以后，还想要进一步将该单元格中的公式复制到其他单元格或单元格区域，那么就必须首先了解公式中对单元格的引用采用的是什么引用方式，以免得到的公式不是所希望得到的结果。
根据公式所在单元格与被引用的单元格之间的位置关系，可以将公式中引用单元格的方式分为以下四种。
1.相对引用
相对引用是指对单元格的引用会随公式所在位置的变化变而改变，公式的值将会依据更改后的单元格地址的值重新计算。相对引用的表现形式是用列标和行号直接表示单元格地址，如A2、B5等，这样表示的单元格地址称为相对地址。在这种引用方式下，当某个单元格的公式被复制到另一个单元格时，原单元格内公式中的地址在新的单元格中就要发生变化，但其引用的单元格地址之间的相对位置间距保持不变。
在默认情况下，输入新公式时采用相对引用方式。
2.绝对引用
绝对引用是指公式中的单元格或单元格区域地址不随着公式位置的改变而发生改变，无论含有公式的单元格处在什么位置，公式中所引用的单元格位置都是其在工作表中的确切位置。绝对引用的表现形式是，在单元格的列标和行号前加“$”符号，这样表示的单元格地址称为绝对地址。绝对引用方式的特点是在将含有公式的单元格复制到新的单元格时，公式中引用的单元格地址始终保持不变。
例如，在某单元格中输入了公式“=SUM（$A$1:$A$10）”，无论将该公式复制到何处，函数UM计算的区域总是单元格区域A1:A10，即对该区域的数据进行加总计算。
3.混合引用
混合引用是指列固定不变但行不固定，或者行固定不变而列不固定的单元格引用方式，即混合引用包括绝对列和相对行或者绝对行和相对列两种引用情况。绝对引用列采用$A1、$B1等形式，也就是对于列采用绝对引用方式，而对于行采用相对引用方式；绝对引用行采用A$1、B$1等形式，也就是对于行采用绝对引用方式，而对于列采用相对引用方式。
如果公式所在单元格的位置改变，则相对引用将改变，而绝对引用将不变。如果多行或多列地复制或填充公式，相对引用将自动调整，而绝对引用将不做调整。
例如，假设单元格A2的公式是“=A$1”，那么当将单元格A2复制到单元格B3时，单元格B3的计算公式就会调整为“=B$1”。
在需要快速输入大量公式，而这些公式中总是引用某个固定的行或固定的列时，混合引用方式会是非常有用的。
4.三维引用
三维引用是指在一个工作簿中，从公式所在的工作表以外的其他工作表中引用单元格，或者从公式所在的工作簿以外的其他工作簿中引用单元格。
当从同一工作簿的其他工作表中引用单元格时，应在引用工作表的名称后面加一个惊叹号（！），然后再接单元格地址，例如“=Sheet2!B5”。
如果工作表名称是数字或者是以数字开头的字符串，在引用该工作表单元格时，必须用单引号将工作表名引起来，然后在后面跟一个惊叹号（！），最后是单元格地址。例如，若要分别引用工作表“22”和工作表“1月”中单元格A1的数据，则公式分别如下所示：
='22'!A1
='1月'!A1
当从其他工作簿的某个工作表中引用单元格时，需要先用方括号将其他工作簿的名称括起来，然后是某个工作表名称及惊叹号，最后是单元格地址，例如“=[Book2.xls]Sheet1!$B$2”。
如果要引用的某个工作簿关闭了，那么就必须加上该工作簿的具体路径，引用的格式应为：
='工作簿保存文件夹路径\[工作簿名.xls]工作表名'!单元格地址
注意这里引用的具体写法是用单引号将包括工作簿路径、工作簿名及工作表名称在内的字符串引起来，然后在后面跟一个惊叹号（！），最后是单元格地址，例如：
='C:\TEMP\[Book2.xls]Sheet1'!$B$2
需要注意的是，三维引用不能用于数组公式，也不能与交叉引用运算符（即空格）一起使用。
小技巧：不同引用方式之间转换的快捷方式是按【F4】键。依次按【F4】键，就会依照相对引用→绝对引用→列相对行绝对→列绝对行相对→相对引用……这样的顺序循环转换下去。在创建公式的过程中合理使用引用方式，可以确保在复制公式时不出差错，并且能够事半功倍地解决问题。
1.1.4 快速复制公式
复制和移动公式是运用Excel过程中最常见的操作之一，尤其是在需要输入大量计算公式的场合。复制和移动公式有很多种方法和小窍门，用户可以根据自己的喜好和实际情况采用某种方法。
复制公式的基本方法是，在一个单元格中输入公式以后，将鼠标指针对准该单元格右下角的黑色小方块，按住左键不放，然后向下、向右、向上或者向左拖曳鼠标，从而完成在其他单元格中输入相应计算公式的工作。
除了上面介绍的复制公式的基本方法以外，还可以利用一些小技巧来实现对公式的快速复制，例如双击法、快速填充法、快速复制法等。下面简要介绍这几种方法。
1.双击法
在某个单元格中输入完公式以后，如果要将该单元格的公式向下填充复制，可在选取该单元格后，将鼠标指针对准该单元格右下角的黑色小方块，双击左键，这样就可以迅速得到复制的公式。
不过，这种方法只能快速向下复制公式，无法向上、向左或向右快速复制公式。而且这种方法也不适用于数据区域中间有空行的场合，如果数据区域中间有空行，复制公式的过程就会停止在空行处。此外，输入公式的单元格所在列必须与数据区域紧密相邻。
2.快速填充法
如果要在包含有很多行和很多列的单元格区域中输入相同的公式，若首先在一个单元格中输入公式，然后采用前述的通过拖曳鼠标的方法来复制公式可能就会显得比较笨拙。在这种情况下，可以先选取包括第一个单元格在内的要输入公式的全部单元格区域，再输入计算公式，按【F2】键，使公式处于编辑状态，最后再按【Ctrl+Enter】组合键，即可快速得到所有的计算公式。
3.快速复制法
对于要在很大的单元格区域中输入公式的情况，除了可以利用上述的“快速填充法”输入公式以外，还可以采用快速复制法来得到全部公式，具体方法是，首先在该单元格区域的一个单元格中输入公式，然后按【Ctrl+C】组合键，再选取要输入公式的其他所有单元格或整个单元格区域，按【Ctrl+V】组合键，也可迅速得到所有的计算公式。
4.原样复制完整公式或公式的一部分
在一般情况下，将某个单元格中的公式复制到其他单元格或单元格区域时，所得到的公式中引用的单元格往往会发生变化，除非原来的公式中引用单元格采用的是绝对引用方式。但有时用户可能希望将某个单元格中的公式本身（也就是公式表达式字符串）复制到其他的单元格或单元格区域中，而不改变公式中对单元格的引用，这种情况下则需要采用特殊的复制方法。
若要把公式本身完整地从一个单元格复制到另外一个单元格，但不改变公式中单元格的引用位置，最基本的方法是将公式作为文本进行复制，具体操作步骤如下。（1）选择要复制公式的单元格，然后按【F2】键，使单元格中的公式处于编辑状态，此时光标会出现在单元格公式的最后。（2）按【Shift+Home】组合键，选择整个公式文本。（3）按【Ctrl+C】组合键，将选取的公式文本复制到剪切板。（4）按【Enter】键或者【ESC】键，退出编辑状态。（5）单击目标单元格，按【Ctrl+V】组合键。
除了上述介绍的在单元格内选取公式文本外，还可以在公式编辑栏中选取公式文本，即单击要复制公式的单元格，然后在公式编辑栏中用鼠标选择公式文本，再执行复制和粘贴操作。
另外一种复制公式本身的方法是先将单元格公式前面的等号删除，然后再将该单元格复制到其他单元格，最后在单元格的公式字符串前面加上等号。
利用上述介绍的方法，也可以仅复制公式文本的一部分，这种情况下只要在单元格内或者公式编辑栏中选取公式的一部分，然后再进行复制粘贴就可以了。
1.1.5 将公式转换为数值
在利用公式对数据进行计算和处理以后，如果不再需要公式，而仅仅需要公式的结果，可以将公式转换为值，这样可以防止发生在意外地把公式所引用的单元格中的数据删除后，会造成公式返回结果出错的情况。
将公式的运算结果转换为不变的数据，可以采用选择性粘贴的方法。
例如，图1-1中的A列单元格中的数据是包含邮政编码和地址信息在内的原始数据，B2:C6单元格区域中的数据是通过使用函数LEFT和函数MID将A列的邮政编码与通信地址分离后的数据，即B列和C列的数据都是公式的计算结果。图1-1 用公式将A列的邮政编码和地址分离后的结果
由于在一般情况下，邮政编码和通信地址是不会随意改变的，因此可以将这些公式的值转换为不变的数据，具体方法和步骤如下所述（仅以如图1-1所示的数据为例）。（1）选取单元格区域B2:C6，按【Ctrl+C】组合键。（2）单击鼠标右键，执行快捷菜单中的“选择性粘贴”命令，打开“选择性粘贴”对话框，在“粘贴”选项中选择“数值”单选框，如图1-2所示。图1-2 执行选择性粘贴命令（3）单击【确定】按钮，即可将公式的值转换为不变的数据，如图1-3所示。图1-3 将公式的值转换为不变的数据（4）如果不再需要A列数据，可将其删除。
说明：上面介绍的是在原数据位置进行选择性粘贴。如果要把公式的值选择性粘贴到其他位置，则可以在按【Ctrl+C】组合键后，单击要复制数据的单元格区域的左上角单元格，再进行选择性粘贴。
1.1.6 显示、隐藏和保护公式
在输入完毕所有的公式并检查无误后，很有必要将建立好的公式保护起来，以免不小心损坏公式。此外，用户有时也可能要查看各单元格中已经建立的公式，或者希望将计算公式隐藏起来，不随意让别人看到。下面就分别介绍隐藏、显示和保护公式的基本方法和技巧。
1.显示公式和显示计算结果
在默认的情况下，公式输入完毕以后，在单元格中只会显示计算的结果，而所建立的公式只能在公式编辑栏中看到。实际上，用户可以根据需要设置在单元格中显示计算结果或显示公式的状态，实现这一目的最简单的方法是使用【Ctrl+`】组合键进行切换，即按一次【Ctrl+`】组合键，会在含有公式的单元格中显示输入的公式，再次按该组合键，则会回到显示计算结果的状态。
图1-4是根据图1-1中的数据，按一次【Ctrl+`】组合键以后，显示出的计算公式。图1-4 按【Ctrl+`】组合键显示公式
2.隐藏公式
已经建立好的公式还可以隐藏起来，在这种情况下，不仅在单元格里看不到公式，在公式编辑栏里也看不到公式。要达到这样的目的，需要首先选择公式所在单元格，然后执行“格式”菜单中的“单元格格式”命令，在“单元格格式”对话框的“保护”选项卡中选定“隐藏”复选框，如图1-5所示，最好还要再对工作表实施保护。保护工作表的具体方法将在下面详细介绍。图1-5 准备隐藏公式
3.保护公式
当用户辛辛苦苦地在工作表的一些单元格中输入好了计算公式以后，往往需要将含有公式的单元格保护起来，但对于其他没有公式的单元格不进行保护，如果需要保密的话，还可以将公式隐藏起来，使任何人都看不见单元格中的公式。
保护并隐藏公式的具体步骤如下所述。（1）选择要实施保护的工作表。（2）按【Ctrl+A】组合键，或单击工作表左上角的“全选”按钮，选择整个工作表。（3）打开“单元格格式”对话框，在“保护”选项卡中取消“锁定”复选框，如图1-6所示。最后单击“确定”按钮。
这一步的操作是为了解除工作表全部单元格的锁定。否则，在执行保护工作表命令以后就会保护工作表的全部单元格。图1-6 解除工作表全部单元格的锁定（4）选择要保护的含有计算公式的单元格区域，可以按【F5】键，打开“定位”对话框，如图1-7所示，单击该对话框上的“定位条件”按钮，打开“定位条件”对话框，选择“公式”单选框，如图1-8所示，再单击“确定”按钮即可。图1-7 “定位”对话框图1-8 “定位条件”对话框（5）选择所有的含有公式的单元格以后，再次打开“单元格格式”对话框，在“保护”选项卡中重新选择“锁定”复选框。如果要隐藏计算公式，则需要再选择“隐藏”复选框，如图1-9所示。然后单击“确定”按钮，关闭“单元格格式”对话框。图1-9 锁定有计算公式的单元格，并隐藏其公式（6）对于Excel 2003，单击“工具”→“保护”→“保护工作表”命令，打开“保护工作表”对话框。若不仅需要隐藏和保护含有公式的单元格，而且也不允许任何人选择这些单元格，可在“保护工作表”对话框中取消“选定锁定单元格”复选框，如图1-10所示。图1-10 不允许任何人操作被锁定的单元格
对于Excel 2007，保护工作表需要在“审阅”选项卡的“更改”功能组中进行，如图1-11所示，单击“保护工作表”按钮，即可打开“保护工作表”对话框。图1-11 在Excel 2007中准备保护工作表（7）输入保护密码，并进行有关设置，最后单击“确定”按钮。
上述步骤完成以后，就会对含有公式的所有单元格进行保护，并且也隐藏了计算公式，任何用户都将无法操作这些单元格，也看不见这些单元格中的计算公式，但其他的单元格还是可以进行正常操作的。注意：第2步和第3步非常关键。如果忽略这两个步骤，那么保护的是整张工作表，这样就会无法对工作表中那些没有计算公式而需要输入数据的单元格进行任何操作。1.2 公式的高级应用
为了解决一些比较复杂的实际问题，并快速地创建计算公式，有时需要使用更高级的应用公式方面的知识和技能。例如，可以使用表达式解决某些特殊问题，使用名称来简化公式，使用数组公式解决更加复杂的汇总计算问题等。本节将介绍这方面的内容。
1.2.1 在公式中使用表达式
在实际财务管理工作中，有时需要根据多个条件对数据进行处理，例如计算某个客户在某个账龄区间的应收账款，计算某个时间段内每种产品的销售额，计算每个分公司在每个城市销售每种产品的销售额等，解决这些问题都涉及多条件数据处理问题。
对于各种条件下的数据处理分析问题，往往需要联合使用IF函数、AND函数和OR函数来进行判断。不过，由于Excel对函数的嵌套层数有限制，因此在很多情况下，由于嵌套的限制导致无法用一个公式来解决问题。而若在公式中合理地使用条件表达式就可以克服嵌套函数的缺点，这样也会使公式的结构和逻辑更加清楚。
1.什么是条件表达式
条件表达式是指根据指定的条件准则对两个项目进行比较，得到TRUE或者FALSE的判定值。逻辑值TRUE和FALSE分别以1和0来代表，在Excel中也遵循这个规定，因此在公式中逻辑值TRUE和FALSE分别以1和0来参与运算。
例如，考虑下面的公式：
= IF（A1>100，1，0.9）* A1
这个公式的含义是利用函数IF来进行判断：如果单元格A1的数值大于100，则公式的运算结果等于单元格A1中的数据乘以1，否则公式的运算结果等于单元格A1中的数据乘以0.9，这里就在IF函数的第一个参数中使用了条件表达式“A1>100”。
上面的这个公式如果利用条件表达式来代替IF函数的话，可表示为：
=（（A1>100）*1+（A1<=100）*0.9）*A1
由此可见，在公式中可以使用各种条件表达式，这样可以更方便地创建能够完成更复杂的任务的公式。
2.简单的条件表达式
如果只需要对两个项目（常数、公式、单元格引用、函数等）进行比较，那么利用简单的比较运算符就可以建立简单的条件表达式。
例如，下面的公式都是简单的条件表达式，它们可对两个项目进行比较：
= A1>B1
= A1<>（C1-200）
= A1="彩电"
= SUM（A1:A10）>=2000
这些条件表达式都会返回逻辑值TRUE或FALSE。
逻辑运算符是条件表达式中表示逻辑关系的基本元素，例如，在表达式“= A1>B1”中，大于号“>”就是一个逻辑运算符，它用来比较单元格A1和B1的数据大小关系。
要特别注意的是，在公式中使用条件表达式进行逻辑测试时，逻辑运算符是所有运算符中运算顺序最低的，因此，为了得到正确的计算结果，最好使用一组小括号将每个条件表达式括起来。例如，上面的公式写成如下的形式会是一种比较好的习惯：
= （A1>B1）
= （A1<>（C1-200））
= （A1="彩电"）
= （SUM（A1:A10）>=2000）
3.复杂的条件表达式
在实际工作中，为了完成更为复杂的任务，有时可以将两个以上的条件表达式组合在一起，建立更为复杂的条件表达式。例如，可以使用AND函数或OR函数来构造复杂的条件表达式，或者使用乘号（*）或加号（+）建立更加复杂的条件表达式。
下面的两个公式是分别使用AND函数和乘号（*）构造的条件表达式，它们的运算结果是一样的：
=IF（AND（A1>=100，A1<1000），0.9，0.8）*B1
=IF（（A1>=100）*（A1<1000），0.9，0.8）*B1
下面的两个公式是分别使用OR函数和加号（+）构造的条件表达式，它们的运算结果是一样的：
=IF（OR（A1="彩电"，A1="冰箱"），0.9，0.8）*B1
= IF（（A1="彩电"）+（A1="冰箱"），0.9，0.8）*B1
下面的两个公式是分别使用AND函数、OR函数、加号（+）和乘号（*）构造的条件表达式，它们的运算结果是一样的：
= IF（AND（OR（商品="彩电"，商品="冰箱"），数量>2000），单价*0.9，单价）
= IF（（商品="彩电"）+（商品="冰箱"）*（数量>2000），单价*0.9，单价）
AND函数与乘号（*）的功能是一样的，它们都是构建多个条件的与关系，也就是这些条件必须同时满足。
OR函数和加号（+）的功能是一样的，它们都是构建多个条件的或关系，也就是这些条件只要有一个满足即可。
乘号（*）和加号（+）在构造复杂的条件表达式方面，比AND函数和OR函数更加方便，构建的公式也更容易理解。
1.2.2 在公式中使用名称
默认情况下，工作表中的单元格的名称是A1、B1等用列标和行号组成的形式。用户根据需要还可以给工作表中某些单元格或单元格区域定义新的名称，并用所定义的名称代替原来的单元格或单元格区域的地址；或者对工作表的某些单元格或单元格区域进行运算后得到一个新的数据或数据数组，并将此数据或数据数组用一个新名称来代表。
使用名称建立公式有许多方面的好处，例如，合理地使用名称不仅可以增强公式的可读性，使公式易于被人理解，而且还有助于快速准确地创建公式，提高公式编辑的准确性；并且名称还可以当做变量来使用，这样可以简化计算公式，从而使对数据进行分析和处理的工作变得更加快捷和高效。
1.定义名称的规则
定义名称要遵循一定的规则，具体要求如下所述。
名称的长度不能超过255个字符。但建议用户最好不要使用这么长的名称，因为名称太长就违背了定义名称的初衷。
名称中不能含有空格，但可以使用下画线和句点。例如，名称不能是“Month Total”，但可以是“Month_Total”或“Month.Total”。
名称中不能使用除下画线和句点以外的其他符号。尽管Excel的帮助信息中没有说明是否可以使用某些符号，但在Excel中定义名称时确实可以使用问号（?）和反斜线（\），不过，这两个符号不能作为名称的开头。
名称的第一个字符必须是字母或汉字，不能使用单元格地址或阿拉伯数字。
避免使用Excel本身预设的一些有特殊用途的名称，如Extract、Criteria、Print_Area、Print_Titles、Database等。
名称中的字母不区分大小写。例如，名称“MYNAME”和名称“myname”是相同的，在公式中使用哪个都是可以的。
2.在Excel 2003中为单元格或单元格区域定义名称的方法
在Excel 2003中，为单元格或单元格区域定义名称有三种基本方法：（1）使用“定义名称”对话框定义名称；（2）利用名称框定义名称；（3）使用“指定名称”对话框定义名称。下面结合案例1-1说明这三种定义名称的方法。【案例1-1】某公司全年12个月的销售量数据如图1-12的B列所示。要求分别采用三种不同的方法将单元格区域B2:B13的名称定义为“销售量”。
方法1：使用“定义名称”对话框定义名称
使用“定义名称”对话框来定义名称的具体步骤如下所述。（1）选取要定义名称的单元格或单元格区域。（2）单击“插入”→“名称”→“定义”命令，打开“定义名称”对话框。（3）如果选择的单元格区域上面的一个单元格或左边的一个单元格是文本数据，Excel会自动将该文本作为默认的名称，如图1-12所示，单击“确定”按钮即可将该选定的单元格或单元格区域命名为默认的名称。图1-12 使用“定义名称”对话框定义名称
如果用户想要使用其他名称，可在“在当前工作簿中的名称”文本框中输入新的名称，然后单击“确定”按钮即可。
如果发觉选择的单元格区域有误，可以在“引用位置”输入框中修改单元格区域的引用。
使用“定义名称”对话框可以定义多个名称，即每定义一个名称后单击“添加”按钮，可以在不关闭“定义名称”对话框的情况下继续定义其他单元格的名称。其具体方法是：首先在在当前工作簿中的名称”文本框中输入名称，在“引用位置”输入框中输入单元格区域（最好用鼠标选取要命名的单元格或单元格区域），然后单击“添加”按钮，就定义好了一个名称。然后再定义其他的名称。
另外还需要说明的是：尽管按上述过程定义名称时对单元格的引用没有指明该单元格所属的工作表，但当完成名称定义后，Excel会自动为引用的单元格加上工作表的限制。例如，名称销售量”的单元格引用为
=Sheet1!$B$2:$B$13
这里，在具体的单元格区域前面有工作表的引用“Sheet1!”的含义是，名称“销售量”指向的是工作表“Sheet1”的单元格区域“$B$2:$B$13”，而不是其他工作表的单元格区域$B$2:$B$13”。
方法2：使用名称框定义名称
使用名称框定义名称是一种比较简单，且适用性更强的方法。其基本步骤是：首先选取要定义名称的单元格区域（不论是整行、整列、连续的单元格区域，还是不连续的单元格区域），然后在名称框中输入名称，按【Enter】键即可。如图1-13所示。图1-13 使用名称框定义名称
在名称框中输入名称后，千万不要忘了按【Enter】键，这样才能完成名称的定义工作。如果在名称框中输入名字后，又用鼠标单击了其他单元格，那么定义名称的工作并没有完成。
需要注意的是，用户无法利用名称框来修改名称指定的单元格引用范围。要想这么做，只能在“定义名称”对话框里进行。
方法3：使用“指定名称”对话框定义名称
使用“指定名称”对话框定义名称适合于要批量定义名称的场合。当工作表的数据区域有行标题或列标题时，使用“指定名称”对话框可快速定义多个名称。
例如，要把数据区域的每行分别定义一个名称，每行的名称正好是该行最左边一列单元格中的文字，那么批量定义名称的具体步骤如下所述。（1）选择要定义行名称和列名称的工作表数据区域（要包含行标题或列标题）。（2）单击“插入”→“名称”→“指定”命令，打开“指定名称”对话框，如图1-14所示。对话框中的复选框选择标记是Excel根据所选区域分析的结果，它是将每行的单元格区域定义为最左列单元格文本表示的名称。图1-14 使用指定命令根据行标题或列标题定义名称（3）单击“确定”按钮，Excel就会建立一系列的名称。打开“定义名称”对话框，可以查看定义的名称，如图1-15所示。图1-15 定义好的一系列名称
这种批量定义的名称代表的是固定的行数据区域或者列数据区域。当要定义名称的数据列增加或者数据行增加以后，可以再次按上述过程进行操作，这样可以对数据区域重新指定一次名称。
3.在Excel 2007中为单元格或单元格区域定义名称的方法
在Excel 2007中，定义和使用名称的相应功能被放到了“公式”选项卡的”定义的名称”功能组中，如图1-16所示。图1-16 Excel 2007的“公式”选项卡的”定义的名称”功能组
在Excel 2007工作表中定义名称与在Excel 2003中定义名称相比，所使用的命令和定义名称对话框有所不同，但使用名称框来定义名称的方法则是完全相同的，即都是先选取单元格区域，然后在名称框中输入名称后按【Enter】键。此外，当批量定义名称时，Excel 2007与Excel 003也是基本相同的，只是使用的命令不同。
在Excel 2007中，使用定义名称对话框定义名称有3种基本的方法：使用“新建名称”对话框、使用“名称管理器”对话框和使用“以选定区域创建名称”对话框。
方法1：使用“新建名称”对话框
使用“新建名称”对话框定义名称的具体步骤是：首先选择要定义名称的单元格或单元格区域，单击”定义的名称”功能组中的“定义名称”按钮，就打开“新建名称”对话框，如图-17所示；然后在此对话框中，在“名称”文本框中输入名称，在“范围”下拉列表中指定该名称的适用范围（即名称的级别），如果需要还可以重新在“引用位置”文本框中输入单元格区域地址；最后单击“确定”按钮。图1-17 使用Excel 2007的“新建名称”对话框定义名称
如果需要为定义的名称添加一些说明信息，还可以在“备注”文本框中输入说明文字，这样便于以后了解该名称的用途、使用方法等。
方法2：使用“名称管理器”对话框
使用“名称管理器”对话框定义名称的具体步骤是：单击“定义的名称”功能组中的“名称管理器”按钮，打开“名称管理器”对话框，如图1-18所示；在此对话框中，列出了本工作簿中已经定义的所有名称的详细信息，包括名称、名称所代表的数值、引用位置、范围（即级别）以及备注信息。图1-18 使用Excel 2007的“名称管理器”对话框定义名称
在“名称管理器”对话框的左上角，有一个“新建”按钮，单击它就打开如图1-17所示的“新建名称”对话框，这样就可以在该对话框中定义名称了。名称定义完毕后则返回“名称管理器”对话框，同时新定义的名称被添加到“名称管理器”对话框列表中，如图1-19所示。图1-19 新定义的名称被添加到“名称管理器”对话框列表中
使用“名称管理器”对话框定义名称要比单独使用“新建名称”对话框有更大的优越性，可以随时查看定义的名称的详细信息，便于了解已定义的名称是不是所需要要的或者是否有错误。如果定义的名称不对，还可以单击“名称管理器”对话框上的“编辑”按钮，打开“编辑名称”对话框，对名称的文字和引用位置进行编辑，如图1-20所示。图1-20 在“编辑名称”对话框中编辑已定义的名称
如果已定义的某个名称不是所需要的，可以在选择该名称后，单击“删除”按钮将其删除。
方法3：使用“以选定区域创建名称”对话框
在Excel 2007中，使用“以选定区域创建名称”对话框可以批量定义名称，其基本方法与xcel 2003中利用“插入”→“名称”→“指定”命令自动建立名称的方法基本上是一样的。即单击“定义的名称”功能组中的“根据所选内容创建”按钮，就可以打开“以选定区域创建名称”对话框，如图1-21所示，那么就可以根据实际情况勾选相应的复选框定义名称了。图1-21 使用“以选定区域创建名称”对话框定义名称
4.名称的实际应用案例
在了解了名称的概念和定义名称的方法以后，下面介绍一个通过使用定义的名称简化计算公式的例子。【案例1-2】图1-22所示的是某公司某月份的销售数据，现在要求按照如图1-23所示的表格结构按大区对本月计划和实际销售额数据进行汇总计算。图1-22 部分销售数据图1-23 按照大区进行分类汇总的表格结构
为了解决这个问题，可以使用SUMIF函数进行汇总计算。但是如果不使用名称公式，那么在SUMIF函数中必须使用单元格的引用。例如，东北大区的本月计划销售额总和的计算公式如下：
=SUMIF（销售!B:B，A2，销售!G:G）
若不去查看工作表“销售”的数据情况，单凭上面这个公式，是难以理解“销售!B:B”代表什么、“销售!G:G”代表什么的。此外，如果要计算东北大区的实际销售额，则需要重新设计公式并引用新的单元格区域，即：
=SUMIF（销售!B:B，A2，销售!H:H）
由此可见，在对不同的项目进行汇总计算时，必须在两个工作表之间不断切换，以便引用相应的单元格区域，这显然是很麻烦的，稍有不慎就会出现引用错误的情况。但是若能对有关的单元格区域定义名称并使用名称公式的话，就会使公式变得非常简单。只要定义完毕名称，那么在当前的汇总工作表中即可迅速完成汇总公式的输入，具体步骤如下所述。
首先，切换到“销售”工作表，单击数据区域的任一单元格，按【Ctrl+A】组合键，选择整个数据区域，按照前面介绍的“指定名称”对话框批量定义名称的方法（Excel 2003）或者“以选定区域创建名称”对话框批量定义名称的方法（Excel 2007），将数据区域的每列从第2行开始的数据列，定义为第1行的标题名称，如图1-24所示。图1-24 批量定义各列数据区域的名称：左图是Excel 2003，右图是Excel 2007
其次，在定义完毕名称以后，就可以快速地输入下列汇总计算公式：
单元格B2：=SUMIF（大区，A2，本月指标）
单元格C2：=SUMIF（大区，A2，实际销售金额）
单元格F2：=SUMIF（大区，A2，销售成本）
汇总计算结果如图1-25所示。图1-25 按照大区进行分类汇总的计算结果
1.2.3 数组公式及其应用
数组公式是指对数组进行计算的公式。前面介绍的公式都是只进行一个简单的计算过程并返回一个计算结果。当需要对两组或两组以上的数据进行计算并返回一个或多个计算结果时，则就需要使用数组公式。使用数组公式能大大简化计算过程，减少计算工作量，提高数据处理效率。
1.数组公式的特征
数组公式有以下的特征。
单击数组公式所在的任一单元格，就可以在公式编辑栏中可看到公式前后出现的大括号“{ }”，如果在公式编辑栏中单击鼠标，大括号就会消失。
输入数组公式的每个单元格中的公式是完全相同的。
必须同时按【Ctrl+Shift+Enter】组合键才能得到数组公式，否则，如果只按【Enter】键，那样得到的是普通公式。
公式中必定有单元格区域的引用，或者必定有数组常量。
不能单独对数组公式所涉及的单元格区域中的某一个单元格进行编辑、删除或移动等操作。
数组公式可以存在于多个单元格，也可以是仅输入到一个单元格。即使输入到一个单元格的数组公式，也必须同时按【Ctrl+Shift+Enter】组合键。
2.数组公式的基本输入方法
数组公式的输入方法和基本步骤如下。（1）选定单元格或单元格区域。如果数组公式只返回一个结果，则单击需要输入数组公式的某个单元格；如果数组公式将返回多个结果，则选定需要输入数组公式的单元格区域。（2）输入数组公式。（3）同时按【Ctrl+Shift+Enter】组合键，则Excel 自动在公式的两边加上大括号{ }。
特别要注意的是，第3步相当重要，只有输入公式后同时按【Ctrl+Shift+Enter】组合键，系统才会把公式视为一个数组公式。否则，如果只按【Enter】键，则输入的只是一个简单的公式，也只是在选中的单元格区域的第1个单元格显示出一个计算结果。
此外还应注意的是，数组公式前后的大括号是系统自动加上去的，仅仅表明该公式是一个数组公式。切不可在输入数组公式的时候在数组公式的两端添加大括号。
下面我们举例说明数组公式的输入方法。
对于如图1-26所示的数据，现要计算各种产品的销售额以及销售总额。一般的方法是计算某种产品的销售额，然后复制公式，从而得到所有产品的销售额，最后再将各种产品的销售额进行加总，得到销售总额。图1-26 一般公式计算销售额
上述这种类型的计算，也可以使用数组公式来完成，具体步骤和方法如下。（1）选取单元格区域D2:D6，如图1-27所示。图1-27 选取要输入公式的单元格区域（2）在键盘上直接输入等号“=”，如图1-28所示。图1-28 输入等号“=”（3）用鼠标拾取单元格区域B2:B6，如图1-29所示。图1-29 输入单元格区域B2:B6（4）在键盘上输入乘号“*”，如图1-30所示。图1-30 输入乘号“*”（5）用鼠标拾取单元格区域C2:C6，如图1-31所示。图1-31 输入单元格区域C2:C6（6）同时按【Ctrl+Shift+Enter】组合键，则Excel自动在公式的两边加上大括号{ }，并在所选取的各单元格中显示计算结果，如图1-32所示。图1-32 在所选取的各单元格中得到计算公式和计算结果
上面的数组公式是在连续的单元格区域内输入的。有时候，也可能仅需要在一个单元格中输入数组公式，以便将某个复杂的计算过程用一个公式来完成。例如，要计算各种产品的销售总额，一般的方法是先计算各种产品的销售额，然后再将各种产品的销售额进行加总。实际上，完全可以用一个公式计算所有产品的销售总额，具体方法和步骤如下所述。（1）选取单元格D7，如图1-33所示。图1-33 选取要输入公式的单元格（2）单击编辑栏上的“插入函数”按钮，打开“插入函数”对话框，找到SUM函数并打开SUM函数的“函数参数”对话框，在对话框的第一个参数框中输入“B2:B6*C2:C6”，这个过程可先用鼠标拾取单元格区域B2:B6，手工输入一个乘号“*”，然后再用鼠标拾取单元格区域C2:C6，如图1-34所示。图1-34 在SUM函数参数框中输入单元格区域引用及运算符（3）按【Ctrl+Shift+Enter】组合键，则会在单元格D7中得到一个数组公式=SUM（B2:B6*C2:C6）”，并显示计算结果，如图1-35所示。图1-35 在单元格D7中得到数组公式和计算结果
由此可见，只利用一个数组公式，就可以立即计算出各种产品的销售总额，这个过程非常简便，省去了计算每种产品销售额的中间计算过程。在这种情况下，即使删除了每种产品销售额的中间计算结果，仍能得到所有产品的销售总额。
特别要注意的是，不能在合并单元格中输入数组公式。若一定要在合并单元格中输入数组公式，应当先取消单元格的合并，然后在拟合并的任何一个单元格中输入数组公式，最后再重新合并单元格，这样才能将数组公式存放到合并单元格中，其结果就如同在合并单元格中输入了数组公式一样。
3.数组公式应用案例
在了解了数组公式的基本原理之后，下面结合案例1-3说明数组公式的应用方法。【案例1-3】仍以“案例1-2”中的数据资料为例，若想要根据如图1-36所示的汇总表格结构按照性质、大区、省份对销售额等指标进行多层次的汇总，那么该如何创建计算公式？图1-36 多层次汇总计算的表格结构
要汇总不同门店性质、不同大区的门店数，会涉及两个条件；同样，要汇总不同门店性质、不同大区、不同省份的销售数据，会涉及三个条件，这些问题都属于多条件计数和多条件求和的问题，不能使用SUMIF函数来解决。
在Excel 2003中，没有多条件计数和多条件求和的函数，但可以使用数组公式，也可以使用SUMPRODUCT函数（这个函数是数组运算函数）来解决上述的问题。
在Excel 2007中，新增了多条件计数函数COUNTIFS和多条件求和函数SUMIFS，这样就使得多条件计数和多条件求和的问题变得简单了，不过，这两个函数仅适用于解决只包含几个与条件情况下的计数与求和问题，而对于包含几个或条件，或者与条件和或条件的混合问题，还是需要使用数组公式来解决的。
使用数组公式解决本案例的问题的具体方法和步骤如下所述。（1）在单元格C2中输入下面的数组公式，计算东北地区的自营门店数：
=SUM（（性质="自营"）*（大区="东北"））
在这个公式中，条件表达式“（性质="自营"）”的含义是判断各个店铺的性质是否为“自营”，如果是，返回的结果会是TRUE，否则返回的结果会是FALSE，由此可见，条件表达式“（性质="自营"）”的运行结果是由TRUE和FALSE组成的一个数组。
同样，条件表达式“（大区="东北"）”的含义是判断各个店铺所在的大区是否是“东北”，如果是，返回的结果会是TRUE，否则返回的结果会是FALSE，因此，条件表达式“（大区="东北"）”的运行结果也是由TRUE和FALSE组成的一个数组。
上述两个条件表达式的结构都是维数相同的、由TRUE和FALSE组成的数组，用乘号（*）将两个条件表达式连接起来，其计算结果也是一个数组，但这个数组的结果是由数字1和0组成的，数字1表示某个店铺的性质是“自营”，所在大区又是“东北”；而数字0则表示某个店铺的性质是“自营”但所在大区不是“东北”，或者某个店铺的性质不是“自营”但所在大区是东北”。
最后使用SUM函数把上面所得到的由数字1和0组成的数组的各个元素相加，就得到了东北地区的自营门店数。（2）在单元格E2中输入下面的数组公式，计算东北地区辽宁省自营门店的本月指标总和：
=SUM（（性质="自营"）*（大区="东北"）*（省份=$D2）*本月指标）
这个公式是由3个与条件组成的，条件表达式“（性质="自营"）*（大区="东北"）*（省份=$D2）”的计算结果是由数字1和0组成的数组，将这个数组的各个元素（1或0）与名称“本月指标”代表的各个单元格中的数据相乘，得到一个由同时满足3个条件的实际数据和不同是满足3个条件的数据0组成的数组，最后使用SUM函数该数组的各个元素相加，就得到了东北地区辽宁省自营门店的本月指标总和来。（3）按照与上述过程同样的道理，可以创建其他的汇总计算公式。例如汇总实际销售金额单元格F2）的计算公式为：
=SUM（（性质="自营"）*（大区="东北"）*（省份=$D2）*实际销售金额）
汇总销售成本（单元格I2）的计算公式为：
=SUM（（性质="自营"）*（大区="东北"）*（省份=$D2）*销售成本）
本案例部分已完成的公式如图1-37所示。对本案例感兴趣的读者，可在如图1-37所示的汇总表中完成其他公式的设置。图1-37 多条件计数和多条件求和公式的运用
说明：如果使用SUMPRODUCT函数解决这个问题，输入的计算公式应是普通公式（即不需要按【Ctrl+Shift+Enter】组合键）。例如，在单元格C2和单元格E2中应输入的公式分别为：
单元格C2：=SUMPRODUCT（（性质="自营"）*1，（大区="东北"）*1）
单元格E2：=SUMPRODUCT（（性质="自营"）*1，（大区="东北"）*1，（省份=$D2）*1，本月指标）
注意要将表达式乘以数字1，以便将逻辑值TRUE和FALSE转换为数字1和0。
如果用户使用的是Excel 2007，这种多个与条件计数和多条件求和的计算问题就会变得很简单，只需使用函数COUNTIFS和SUMIFS就可以了。
例如，在单元格C2中可以输入下面的公式
=COUNTIFS（性质，"自营"，大区，"东北"）
在单元格E2中可以输入下面的公式
=SUMIFS（本月指标，性质，"自营"，大区，"东北"，省份，$D2）
1.2.4 快速准确创建复杂的计算公式
很多实际问题不是使用简单的函数和公式就可以解决的，而是往往需要创建非常复杂的计算公式。对于很复杂的计算公式，如果直接在单元格中输入，是很容易出错的。下面结合案例1-4，介绍一种快速准确创建复杂计算公式的方法，即公式分解与综合法。【案例1-4】如图1-38所示的A列至C列数据为从数据库中导出的数据，其中C列的批次是包含数量和单位信息的混合字符串。要求：把批次一列的数据分成数量和单位两列数据，分别保存在D列和E列，以便于以后对这些数据进行统计分析。图1-38 由数量和单位组成的批次数据（无法进行统计分析）
显然，这样的问题不是利用一个简单的公式就能解决的，而是需要使用数组公式。
要将批次中的数量和单位分开，需要了解批次数据中从左边开始哪些字符是数字，到哪个字符为止由数字变为了文本（汉字或字母），这样才能利用文本函数LEFT把其中的数量取出来，再利用文本函数MID把批次取出来。下面以第3行中单元格C3的批次数据为例，说明解决这个问题的思路，以及创建公式的方法和步骤。
要想判断批次数据中从左边开始哪些字符是数字，到哪个字符为止由数字变为了文本，首先需要知道批次数据有多少个字符，然后利用MID函数把每个字符取出来，再使用LEN函数得到批次数据的长度，最后利用INDIRECT函数和ROW函数得到一个从1开始到批次数据长度结束的自然数序列。具体操作方法为：选择单元格区域G2:G15（或者G列从第2行开始到某行为止，这个行数要大于批次数据字符长度），输入数组公式“=ROW（INDIRECT"1:"&LEN（C3）））”，如图1-39所示。图1-39 生成一个从1开始到批次数据长度结束的自然数序列
有了这个自然数序列，就可以利用MID函数把批次数据的各个字符分别取出来了，具体方法为：选择单元格区域H2:H15，输入数组公式“=MID（C3，G2:G15，1）”，如图1-40所示。图1-40 批次数据的各个字符被分别取出
由于利用MID函数从字符串中取出的数据无论原来是数字还是文本，都是被处理成文本数据的，因此需要把提取出来的批次数据中代表数量的数字转换成数值型数据，具体方法是：选择单元格区域I2:I15，输入数组公式“=1*H2:H15”，如图1-41所示。图1-41 把提取出的批次数据各字符转换成数值
显然，从批次数据左侧开始取出的各个数据，只要开始出现了错误值，就表明从该字符开始就是单位了，因此需要判断从哪个位置开始出现了错误值，具体方法是：选择单元格区域J2:J15，输入数组公式“=ISERROR（I2:I15）”，如图1-42所示。图1-42 把提取出的批次数据各个字符转换成数值
这样，就得到了批次数据各个字符串是否是数值的结果（FALSE表示是数值，TRUE表示是文本），而第一个出现TRUE的位置就是单位的位置，因此可以利用MATCH函数把这个位置确定出来，具体方法是：选择单元格K2，输入公式“=MATCH（TRUE，J2:J15，0）”，得到这个位置的序号数，如图1-43所示。图1-43 得到批次数据中哪个位置的字符是单位
知道了批次数据中哪个字符的位置是单位的情况后，利用LEFT函数就可以很容易地把数量数字取出，利用MID函数把单位取出，具体方法是：选择单元格L2，输入公式“=1*LEFT（C3，K2-1）”，得到批次数据中的数量数字，如图1-44所示。
最后，再按照与上述过程相反的顺序，将中间的计算过程逐一消除，把中间计算过程的公式综合成一个公式，具体步骤如下。（1）单元格L3的公式引用了单元格K2的结果，而单元格K2的计算公式为“=MATCH（TRUE，J2:J15，0）”，因此，应把单元格K2的计算公式去掉等号，仅仅复制等号后面的函数本身，并把它替换掉单元格L3计算公式中的单元格引用K2，得到新的计算公式：=1*LEFT（C3，MATCH（TRUE，J2:J15，0）-1）图1-44 得到批次数据中的数量数字（2）上面这个公式中引用了单元格区域J2:J15的结果，而单元格区域J2:J15的计算公式为=ISERROR（I2:I15）”，因此，应把上述公式中的单元格引用J2:J15替换为ISERROR（I2:I15），得到新的计算公式（需要注意从此开始是数组公式了，因此要按【Ctrl+Shift+Enter】组合键）：
=1*LEFT（C3，MATCH（TRUE，ISERROR（I2:I15），0）-1）（3）上面这个公式中引用了单元格区域I2:I15的结果，而单元格区域I2:I15的计算公式为=1*H2:H15”，因此，应把上述公式中的单元格引用I2:I15替换为1*H2:H15，得到新的计算公式：
=1*LEFT（C3，MATCH（TRUE，ISERROR（1*H2:H15），0）-1）（4）上面这个公式中引用了单元格区域H2:H15的结果，而单元格区域H2:H15的计算公式“=MID（C3，G2:G15，1）”，这样，把上述公式中的单元格引用H2:H15替换为MID（C3，G2:G15，1），得到新的计算公式：
=1*LEFT（C3，MATCH（TRUE，ISERROR（1*MID（C3，G2:G15，1）），0）-1）（5）上面这个公式中引用了单元格区域G2:G15的结果，而单元格区域H2:H15的计算公式为“=ROW（INDIRECT（"1:"&LEN（C3）））”，因此，应把上述公式中的单元格引用G2:G15替换为ROW（INDIRECT（"1:"&LEN（C3））），得到新的计算公式：
=1*LEFT（C3，MATCH（TRUE，ISERROR（1*MID（C3，ROW（INDIRECT（"1:"&LEN（C3））），1）），0）-）（6）上面这个公式就是最终需要的公式，将这个公式本身原封不动地复制到单元格D3（要注意将公式复制到单元格后，按【Ctrl+Shift+Enter】组合键），就得到了批次数据中的数量。（7）在单元格E3中输入公式“=MID（C3，LEN（D3）+1，999）”，即得到批次数据中的单位。
通过本案例的分析与公式的创建过程可以看出，很多实际问题是非常复杂的，为了解决这样复杂的问题一般需要使用多个函数，通过一系列的计算才能得到正确的结果，这不仅要求读者熟练掌握函数的应用，更为重要的是要弄懂题意，分析问题的本质，要有一个解题的思路，并把计算过程进行分解，最后再对中间计算过程进行综合，得到最终的计算公式。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-45 SUM函数的参数对话框图1-46 常用函数下拉列表框注意：如果一个嵌套函数公式中有很多函数，在输入完毕某个函数的参数后，千万不要单击“确定”按钮或【Enter】键，而是要采用上述方法逐个地输入参数值，待所有函数的参数都输入完毕后，再单击“确定”按钮或【Enter】键。
1.3.2 数据的逻辑判断与处理
对数据进行逻辑判断与处理，一般要使用IF函数、AND函数、OR函数。
IF函数用于对真假值进行判断，并根据逻辑计算的真假值，返回不同结果。可以使用IF函数对数值和公式进行条件检测。该函数的使用方法是：
=IF（条件式，条件值为真时的回应，条件值为假时的回应）
IF函数的使用方法是很简单的。但值得注意的是，对于Excel 2003而言，IF函数最多可以嵌套七层，而对于Excel 2007而言，IF函数最大的嵌套层数是64层。
AND函数用于判断几个条件是否都满足。如果所有的条件都满足，AND函数会返回TRUE，否则，只要有一个条件不满足就返回FALSE。该函数的使用方法是：
=AND（条件表达式1，条件表达式2，……）
OR函数用于判断几个条件是否有一个或多个都满足。只要有一个条件满足时，OR函数即返回TRUE，只有当所有条件都不满足时才返回FALSE。该函数的使用方法是：
=OR （条件表达式1，条件表达式2，……）
此外，Excel还提供了一些信息函数，利用这些函数可以获得指定单元格的相关信息。这些函数很少单独使用，一般是与IF函数一起使用，用于构造一些比较复杂的条件。常用的信息函数有ISERROR、ISNUMBER、ISTEXT函数等。
ISERROR函数用于检验单元格数据是否为任意错误值，如果是错误值，其返回结果是RUE；如果不是错误值，其返回结果是FALSE。该函数的使用方法为：
=ISERROR （单元格引用或数据）
ISNUMBER函数用于判断单元格数据是否为数值，如果是数值，其返回结果是TRUE；如果不是数值，其返回结果是FALSE。该函数的使用方法为：
=ISNUMBER（单元格引用或数据）
ISTEXT函数用于判断单元格数据是否为文本，如果是文本，其返回结果是TRUE；如果不是文本，其返回结果是FALSE。该函数的使用方法为：
=ISTEXT （单元格引用或数据）
在Excel 2007中，还新增了一个逻辑函数IFERROR，它用来对单元格的错误值进行处理，其使用方法是：
= IFERROR（要检查是否有错误的值或表达式，有错误时的返回值）
例如，在单元格C3输入公式“=A3/B3”后，若B3单元格为空或者为零，就会出现#DIV/0!错误，此时就可以使用IFERROR函数对计算错误进行处理，可以输入下面的公式来解决这个问题：
=IFERROR（A3/B3，""）
这个公式的含义是：如果A3/B3出现了错误，单元格C3就不输入任何值；如果A3/B3没有错误，单元格C3就输入A3/B3的结果。
Excel 2007的IFERROR函数实际上是将IF函数和ISERROR函数的功能综合在了一起。而在Excel 2003中如果要处理公式错误的话，就必须联合使用IF函数和ISERROR函数，即创建下面的公式：
=IF（ISERROR（A3/B3），""，A3/B3）
1.3.3 处理日期数据
Excel提供了各种日期和时间函数，在日常会计核算和财务管理工作中，最常用的日期函数有TODAY函数、DATE函数、YEAR函数、MONTH函数、DAY函数、EOMONTH函数、EDATE函数、WEEKDAY函数、WEEKNUM函数等。在Excel 2003中，一些不常用的特殊函数被放到了分析工具库中，并作为加载宏供用户选择使用。如果读者使用的是Excel 2003版本，并且想要使用分析工具库中的函数，可以单击“工具”→“加载宏”命令，打开“加载宏”对话框，在“可用加载宏”列表中选择“分析工具库”复选框，如图1-47所示，然后单击“确定”按钮。这样，就会新增许多函数可供使用了。
下面分别介绍上述的各种日期函数的功能及其应用方法。
1.TODAY函数
TODAY函数返回计算机系统当前日期的序列号。该函数没有参数，但在使用它时，不能省略TODAY后面的左右小括号。
例如，在任一单元格中输入公式“=TODAY（）”，即可得到计算机系统当前的日期。图1-47 加载“分析工具库”
2.YEAR、MONTH、DAY函数
YEAR函数用于获取指定日期序列号的年份数字，值为1900到9999之间的整数。
MONTH函数用于获取指定日期序列号的月份数字，值为1到12之间的整数。
DAY函数函数用于获取指定日期序列号的日数字，值为1到31之间的整数。
这三个函数的语法格式如下：
=YEAR（日期）
=MONTH（日期）
=DAY（日期）
3.DATE函数及其应用
DATE函数用于将三个数字组成一个日期序列号，其语法格式为：=DATE（年，月，日）
例如，公式“=DATE（2010，2，23）”的运行结果是 2010-2-23。
假设单元格A1中是年份数字、B1中是月份数字，C1中是日数，利用DATE函数将这三个单元格中的数据组合成一个具体的日期数据的公式如下：
=DATE（A1，B1，C1）
再如，假设单元格A1中为某个日期数据，要计算距该日期3年零8个月零15天后的日期，计算公式为
=DATE（YEAR（A1）+3，MONTH（A1）+8，DAY（A1）+15）
DATE函数还有以下一些特殊的用法。
如果将DATE函数的参数day设置为0，那么该函数会返回指定月份上个月的最后一天。例如，公式“=DATE（2010，7，0）”的运行结果是2010-6-30。这个技巧是非常有用的，它可用来确定某个月的最后一天的日期。
如果DATE函数的参数day大于31，那么该函数会将超过部分的天数算到下一个月份，例如公式“=DATE（2010，1，42）”的运行结果是2010-2-11。
如果DATE函数的参数day小于0，那么该函数将会往前推算日期，例如公式“=DATE（2010，1，-15）”的运行结果是2009-12-16。
如果将DATE函数的参数month设置为0，那么该函数会返回指定年份上年的最后一月。例如，公式“=DATE（2010，0，22）”的运行结果是2009-12-22。这个技巧也是非常有用的，它可用于确定上年最后一个月的日期。
如果DATE函数的参数month大于12，那么该函数会将超过部分的月数算到下一年，例如公式“=DATE（2010，15，21）”的运行结果是2011-3-21。
如果DATE函数的参数month小于0，那么该函数会往前推算月份和年份，例如公式“=DATE（2010，-3，21）”的运行结果是2009-9-21。
下面通过案例1-5说明使用YEAR、MONTH、DAY和DATE函数计算应收账款到期日的方法。【案例1-5】某公司记录的应收账款的有关数据如图1-48中的A至E列所示。假定应收账款到期日按照销售日期间隔信用期限对应的月份数的那天的前一天计算，例如销售日期是2009年10月19日，信用期限4个月，那么应收账款到期日是2010年2月18日。要求计算该公司各笔应收账款的到期日。
解决这个问题的具体方法是，首先在单元格F2中输入下面的公式：
=DATE（YEAR（C2），MONTH（C2）+E2，DAY（C2）-1）
然后将单元格E2的公式复制到单元格区域F3:F8，计算结果如图1-48所示。
这里，在单元格E2的公式中首先使用YEAR、MONTH、DAY函数把销售日期拆开，然后把信用期限数字加到月份数字上得到一个新的月份数字，最后利用DATE函数将三个数字组合成新一个的日期。图1-48 使用YEAR、MONTH、DAY和DATE函数计算应收账款到期日
4.WEEKDAY函数及其应用
WEEKDAY函数用于获取某日期为星期几。默认情况下，其值为1（星期天）到7（星期六）之间的整数。该函数的语法格式如下：
=WEEKDAY（serial_number，return_type）
=WEEKDAY（日期，[类型]）
参数Serial_number为日期序列号，可以是日期数据或对日期数据单元格的引用。
参数Return_type为确定返回值类型的数字，其含义如下所示：
例如，下面三个公式的含义是相同的，都表明2009年10月4日是星期日：
=WEEKDAY（"2009-10-4"，1）= 1
=WEEKDAY（"2009-10-4"，2）= 7
=WEEKDAY（"2009-10-4"，3）= 6
从我国的习惯来说，将参数Return_type设置为2是恰当的。
5.EDATE函数及其应用
EDATE函数的功能是获取指定日期往前或往后几个月的日期。对Excel 2003来说，该函数是分析工具库里的函数，但在Excel 2007中该函数可随时使用。函数的语法格式如下：
=EDATE（开始日期，几个月）
例如：
2009年10月12日之后3个月的日期：=EDATE（"2009-10-12"，3），为2010-1-12。
2009年10月12日之前4个月的日期：=EDATE（"2009-10-12"，-4），为2009-6-12。
在案例1-5中，也可以使用EDATE函数计算应收账款到期日，即可以在单元格F2中输入下面的公式：
=EDATE（C2，E2）-1
显然，这个公式更为简练。
将单元格F2复制到单元格区域F3:F8后，得到的计算结果如图1-49所示。图1-49 使用EDATE函数计算应收账款到期日
6.EOMONTH函数及其应用
EOMONTH函数的功能是获取指定日期往前或往后几个月的特定月份的月底日期。对Excel 003来说，该函数是分析工具库里的函数，但在Excel 2007中该函数可随时使用。函数的语法格式为：
=EOMONTH（开始日期，几个月）
例如：
2009年10月12日之后3个月的月末日期：= EOMONTH（"2009-10-12"，3），为2010-1-31。
2009年10月12日之前5个月的月末日期：= EOMONTH（"2009-10-12"，-5），为2009-5-31。
计算2009年2月共有多少天：=DAY（EOMONTH（"2009-2"，0）），运行结果为28天。
获取当月最后一天日期的公式为：=EOMONTH（TODAY（），0）
下面通过案例1-6说明使用EOMONTH函数计算应收账款到期日的方法。【案例1-6】仍以案例1-5中已知的应收账款数据资料为例，现在假定各笔应收账款到期日按照销售日期间隔信用期限对应的几个月后那个月的月底日期。要求：计算各笔应收账款的到期日。
在本案例中，使用EOMONTH函数来计算应收账款的到期日最为方便。具体方法是，在单元格F2中输入公式：
=EOMONTH（C2，E2）
然后，将单元格F2复制到单元格区域F3:F8，最终得到的计算结果如图1-50所示。图1-50 使用EOMONTH函数计算应收账款到期日
在本案例中，如果要使用YEAR、MONTH、DAY和DATE函数进行计算，那么计算公式是很复杂的，这种情况下需要利用DATE函数的特殊用法，单元格F2的计算公式应为：
=DATE（YEAR（C2），MONTH（C2）+E2+1，0）
7.WEEKNUM函数及其应用
WEEKNUM函数用于判断指定日期是该年的第几周。对Excel 2003来说，该函数是分析工具库里的函数，但在Excel 2007中该函数可随时使用。函数的语法格式为：
=WEEKNUM（日期，类别）
当类别省略或为1时，表示将星期日作为一周的起始日。
当类别为2时，表示将星期一作为一周的起始日。
例如：2010年3月5日是2010年的第10周，计算公式为：=WEEKNUM（"2010-3-5"，2）。
从我国的习惯来说，将参数Return_type设置为2是恰当的。
1.3.4 处理文本数据
在第1章和本章前面的有关案例中介绍了几个文本函数的使用方法。在财会日常工作中，常用的文本函数有LEN函数、LEFT函数、RIGHT函数、MID函数、TEXT函数、FIND函数等。下面进一步详细介绍常用的文本函数及其在财会工作中的应用案例。
1.LEN函数和LENB函数
LEN函数和LENB函数的功能都是用于获取字符串的长度。其中，LEN用于获取文本字符串中的字符数，LENB用于获取文本字符串中用于代表字符的字节数。这两个函数的语法格式如下：
=LEN（文本字符串）
=LENB（文本字符串）
例如：
=LEN（"100083北京市"） 结果为9。
=LENB（"100083北京市"） 结果为12（因为汉字为双字节字符，故“北京市”长度为6个字节）。
2.LEFT函数和LEFTB函数
LEFT函数和LEFTB函数的功能都是用于获取字符串左边指定个数的字符，不过LEFT是以字符串的字符数为单位，而LEFTB是以字符串的字节数为单位。这两个函数的语法格式如下：
=LEFT（文本字符串，[字符数]）
=LEFTB（文本字符串，[字节数]）
例如：
=LEFT（"100083北京市"，6） 结果为 "100083"。
=LEFTB（"北京100083"，6） 结果为 "北京10"。
3.RIGHT函数和RIGHTB函数
RIGHT函数和RIGHTB函数的功能都是用于获取字符串右边指定个数的字符，不过RIGHT是以字符串的字符数为单位，而RIGHTB是以字符串的字节数为单位。这两个函数的语法格式如下：
=RIGHT（文本字符串，[字符数]）
=RIGHTB（文本字符串，[字节数]）
例如：
=RIGHT（"100083北京市"，3） 结果为 "北京市"。
=RIGHTB（"北京100083"，8） 结果为 "京100083"。
4.MID函数和MIDB函数
MID函数和MIDB函数的功能都是用于获取字符串从指定位置开始指定个数的字符，不过MID是以字符串的字符数为单位，而MIDB是以字符串的字节数为单位。这两个函数的语法格式如下：
=MID（文本字符串，起始位置，字符数）
=MIDB（文本字符串，起始位置，字节数）
例如：
=MID（"100083北京市"，7，3） 结果为 "北京市"。
=MIDB（"北京100083"，3，4） 结果为 "京10"。
5.FIND函数和FINDB函数，SEARCH函数和SEARCHB函数
FIND函数和FINDB函数的功能都是用于在区分大小写的情况下，查找某字符在字符串中第一次出现的位置，其中FIND是以字符串的字符数为单位，而FINDB是以字符串的字节数为单位。这两个函数的语法格式如下：
=FIND（要找的字符串，文本字符串，[起始位置]）
=FINDB（要找的字符串，文本字符串，[起始位置]）
例如，公式“=FIND（"m"，"Miriam McGovern"）”的结果是6，因为小写m在字符串“Miriam McGovern”中第一次出现的位置是6。
又如，公式“=FINDB（"京"，"北京100083"）”的结果是3，因为按照字节数计算，字符“京”在字符串“北京100083”中第一次出现的位置是3（“京”前面的“北”占用了2个字符）。
需要注意的是，FIND函数与FINDB函数区分大小写并且不允许使用通配符。若不希望执行区分大小写的搜索或者要使用通配符，可以使用SEARCH函数和SEARCHB函数。
SEARCH函数和SEARCHB函数用于在不区分大小写的情况下，查找某字符在字符串中第一次出现的位置，其中SEARCH是以字符串的字符数为单位，而SEARCHB是以字符串的字节数为单位。这两个函数的语法格式如下：
=SEARCH（要找的字符串，文本字符串，[起始位置]）
=SEARCHB（要找的字符串，文本字符串，[起始位置]）
例如，公式“=SEARCH（"m"，"Miriam McGovern"）”的结果是1，因为字母m（不区分大小写）在字符串“Miriam McGovern”中第一次出现的位置是1。
又如，公式“=SEARCHB（"B"，"北京 Beijing 100083"）”的结果是6，因为在大写字母B第一次出现的位置是两个汉字“北京”和一个空格之后，两个汉字的字节数是4，一个空格的字节数是1，合起来是5个字节，因此大写字母B的位置是第6个字节。
6.文本函数综合应用案例
下面通过案例1-7说明运用文本函数分离会计科目编码和科目名称的具体方法。【案例1-7】某公司会计科目编码和名称的有关数据存放在如图1-51所示的工作表的A列。要求：将A列的会计科目编码和名称拆分成两列，以便于对会计信息进行日常核算和管理。图1-51 会计科目数据：科目编码长度不一
在本案例中，会计科目编码与科目名称紧密连接在一起，并且各科目编码的长度不完全一致，在这种情况下，无法直接利用LEFT函数将科目编码剥离出来。考虑到会计科目数据仅仅是由数字和汉字（如果有括号、冒号、逗号等符号，这些符号也是全角符号）组成，而每个汉字（也就是全角字符）占用2个字节，因此可以使用LENB函数和LEN函数对数据长度进行必要的计算，再利用LEFT函数和RIGHT函数将科目编码剥离出来。解决本案例问题的具体操作步骤如下。（1）在单元格B2中输入公式“=LEFT（A2，2*LEN（A2）-LENB（A2））”，得到科目编码数字文本。（2）在单元格C2中输入公式“=RIGHT（A2，LENB（A2）-LEN（A2））”，得到科目名称。（3）选取单元格区域B2:C2，将其向下填充复制直到第11行，即得到需要的结果，如图1-52所示。图1-52 科目编码与科目名称被分成两列
1.3.5 数据计数统计
在对数据进行计数统计时，使用最多的函数是COUNTA和COUNTIF函数。
COUNTA函数的功能是统计单元格区域内非空单元格的个数。在设置数据有效性和条件格式时，常常会用到这个函数。COUNTA函数的使用方法是：
=COUNTA（单元格区域）
COUNTIF函数的功能是统计数据区域内满足条件的单元格个数，例如利用它可以统计订单数、某部门人数、某类别的客户数等。不过，COUNTIF函数只能根据一个条件进行计数统计，如果有多个条件，就必须使用SUM函数构建数组公式或者使用SUMPRODUCT函数，或者使用Excel 2007的COUNTIFS函数。
COUNTIF函数的使用方法是：
=COUNTIF（单元格区域，条件值）
COUNTIFS函数的使用方法是：
=COUNTIFS（单元格区域1，条件值1，单元格区域2，条件值2，……）
值得注意的是，利用COUNTIFS进行多条件计数，只适用于有几个与条件（也就几个条件必须同时满足）情况下的多条件计数，而不能用于混合条件下的多条件计数。
下面通过案例1-8说明运用相关的函数对订单数量进行汇总统计的方法。【案例1-8】某公司的销售订单数据保存在名为“源数据”的工作表中，其中的每一行数据是一张订单记录，如图1-53所示。要求：（1）分别编制每种商品的订单数量和每个城市的订单数量统计分析表；（2）编制各城市的每种商品订单数量统计分析表。图1-53 已知的销售订单数据
对于第一个问题，可以使用COUNTIF函数来解决。
首先，将每列数据区域定义为第一行标题文字的名称，这样可以简化公式；其次，在单元格B2中输入公式“=COUNTIF（商品，A2）”，并将其向下复制到需要的行，即可得到每种商品的订单数；最后，在单元格E2输入公式“=COUNTIF（城市，D2）”，并将其向下复制到需要的行，即得到每个城市的订单数。公式运行的结果如图1-54所示。
对于第二个问题，可以使用数组公式来解决，或者在Excel 2007中使用COUNTIFS函数来解决。在使用数组公式的情况下，并不使用COUNTA函数或者COUNTIF函数，而是使用SUM函数进行多条件计数。如果觉得使用SUM函数创建数组公式比较麻烦，也可以使用SUMPRODUCT函数创建普通的公式。解决这个问题的具体方法是：
首先，设计如图1-55所示的统计分析表格；其次，在单元格B3中输入数组公式“=SUM（（城市=$A3）*（商品=B$2））”；最后，将该单元格中的公式向右和向下复制，即可得到每个城市中每种商品的订单数。公式的运行结果如图1-55所示。图1-54 统计每种商品和每个城市的订单数图1-55 统计各城市每种商品的订单数
如果使用SUMPRODUCT函数解决第二个问题，则使用的是一个普通的公式，单元格B3中的计算公式应为：
=SUMPRODUCT（（城市=$A3）*1，（商品=I$2）*1）
而在Excel 2007中，可以直接使用COUNTIFS函数来解决问题，此时单元格B3中的公式应为：
=COUNTIFS（城市，$A3，商品，B$2）
1.3.6 数据求和汇总
在实际工作中经常会遇到要对数据进行求和汇总的问题，有时要进行无条件的求和汇总，有时要在满足各种条件下进行求和汇总。Excel中有一些常用的求和汇总函数，可以很方便地用来解决这些问题。
1.常用的求和汇总函数
Excel中常用的求和汇总函数包括SUM函数、SUMIF函数、SUMPRODUCT函数等。在xcel 2007中还有SUMIFS函数。（1）SUM函数
SUM函数是的功能是用于计算某一单元格区域中所有数据之和。其语法格式为：
=SUM（数值1，数值2，……）
但需要强调说明的是，直接输入到参数表中的数字、逻辑值及数字的文本表达式将被计算。如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。如果参数为错误值或为不能转换成数字的文本，将会导致错误。（2）SUMIF函数
SUMIF函数的功能是根据指定条件对若干单元格求和。其语法格式如下：
=SUMIF（range，criteria，sum_range）
=SUMIF（条件判断区域，条件，求和区域）
这里，参数criteria确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、"32"、">32"或"apples"。
有关SUMIF函数的使用方法，前面有关的案例已经做了介绍。在下面的实际案例中，还将进一步介绍利用SUMIF函数简化计算公式的思路和方法。（3）SUMPRODUCT函数
SUMPRODUCT函数用于计算几组数组间对应元素乘积之和。其语法格式为：
= SUMPRODUCT（数组1，数组2，数组3，……）
需要注意的是，该函数的各个数组参数必须具有相同的维数，否则，函数将返回错误值#VALUE!。对于非数值型的数组元素将作为0处理。
例如，图1-56为不同产品的销售量、单价和折扣率，现要求计算销售总额。使用SUMPRODUCT函数可以一次性地计算出所有产品的实际销售总额，即在单元格B6中输入公式“=SUMPRODUCT（B2:F2，B3:F3，1-B4:F4）”，就可得到所需要的结果。图1-56 使用SUMPRODUCT函数计算销售总额（4）SUMIFS函数
SUMIFS函数是Excel 2007的新增函数，其功能是对某一区域内满足多重条件的单元格求和，其语法格式如下：
=SUMIFS（sum_range，criteria_range1，criteria1，criteria_range2，criteria2，…）
=SUMIFS（求和区域，条件判断区域1，条件1，条件判断区域2，条件2，……）
SUMIFS函数各个参数的使用方法与SUMIF函数是基本相同的。此外，SUMIFS函数的条件判断区域个数和条件个数最多为127个。
特别需要注意的是，SUMIFS函数只能用于多个与条件情况下的求和计算。如果要计算满足多个与条件和多个或条件组合情况下的求和计算，就无法使用SUMIFS函数了，而是必须联合使用前面介绍的SUM函数和IF函数，或者使用SUMPRODUCT函数，并利用加号（+）和乘号（*）来构建复杂的组合条件。
2.求和汇总问题实际案例
在实际工作中，求和汇总的情况是多种多样的，要跟据实际问题和实际表格结构，采用不同的函数和公式，如果是多条件求和，就需要使用数组公式。下面通过案例1-9和案例1-10说明运用求和类的函数解决对财务数据进行求和汇总问题的实际案例。【案例1-9】图1-57所示的是某公司一位财务人员设计的对各月销售额进行汇总分析的表格，在单元格区域C26:O26的每个单元格中都含有求和计算公式，其中C26单元格中的公式为：
=C4+C7+C10+C13+C16+C19+C22+C25
由于在日常工作中，经常需要在该表格中删除一些旧产品的数据或者插入一些新产品的数据，而在这种情况下，往往会导致求和公式会出现错误。例如，如果把产品C对应的第8、9、0行删除，则上述的C26单元格的公式就会变为下面的情形：
=C4+C7+#REF!+C10+C13+C16+C19+C22
这样的公式将会返回#REF!错误，无法得到计算结果，为了得到计算结果则需要重新修改公式，显然，原来在表格中建立的计算公式不仅麻烦而且不尽完美。
那么，应怎样对表格中的计算公式进行修改才能解决上述问题呢？图1-57 原始的表格：设计的计算公式效率不高
为了有效地解决上述问题，可以使用SUMIF函数重新建立计算公式。
首先将C26单元格的公式修改为：
=SUMIF（$B$2:$B$25，"小计"，C2:C25）
然后将单元格C26复制到单元格区域D26:O26，这样就可以得到修改后的更为科学合理的计算公式，即使在删除某种产品对应的几行数据之后，所设计的计算公式无须再做调整也能自动得到正确的计算结果。【案例1-10】在如图1-58所示的工作表中，单元格区域C120:F120中已设置了计算净利润的公式，其中单元格C120的计算公式为：
=C2+C7+C8+C9+C10+C11+C22+C23+C24+C70+C110+C116+C117+C118
显然，这个公式采用的是将总账科目金额逐个相加的方法来计算净利润，公式很长，也很不科学，不仅在输入公式时容易加错单元格，而且若因会计科目增加而插入新行，或者因需要减少会计科目而删除一些行，那么又得重新输入或修改公式，一不小心就容易出错。
针对这样的问题，应怎样对单元格区域C120:F120中的公式进行修改，使其变得更为科学合理呢？图1-58 原始的表格：设计的加总公式不科学
根据本案例的情况，使用数组公式或者使用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是一个数组公式，需要以数组公式的形式输入；公式2是一个普通公式。
1.3.7 数据查找与汇总
数据查询是日常财务管理工作中经常遇到的问题，是在Excel中执行得最频繁的操作，也是很复杂的操作。在使用函数进行数据查询时，往往很少只使用一个函数，而是多个函数联合使用。下面介绍几个常用的查找和引用函数及其在数据查询中的应用案例。
Excel提供了10多个查找和引用函数，这些函数都是非常有用的，其中应用得最多的有VLOOKUP函数、MATCH 函数、INDEX函数、CHOOSE函数、INDIRECT函数、OFFSET函数、ROW函数、COLUMN函数等。值得注意的是，一个数据查询问题，可能有很多种解决方法，至于采用什么方法，主要取决于读者的喜好。查找数据函数的使用原则是：简单和准确。下面结合实际案例介绍这些常用函数的使用方法和技巧。
1.MATCH函数
MATCH函数用于在指定方式下查找与指定数值匹配的数组中元素的相应位置，其使用方法如下：
=MATCH（lookup_value，lookup_array，match_type）
=MATCH（查找值，数据范围，对比规则）
在使用MATCH函数时，要特别注意参数match_type（对比规则）的设置，它必须是1、0或?1的任一个值。如果省略，表明默认值为1。
如果值为1，函数MATCH查找小于或等于lookup_value的最大数值，Lookup_array 必须按升序排列。
如果值为0，函数MATCH查找等于lookup_value的第一个数值，Lookup_array 可以按任何顺序排列。
如果值为?1，函数MATCH查找大于或等于lookup_value的最小数值，Lookup_array必须按降序排列。
此外，在使用MATCH函数查找文本值时，是不区分大小写字母的。且如果match_type为时，lookup_value可以包含通配符、星号（*）和问号（?）。星号可以匹配任何字符序列；问号可以匹配单个字符。
如果函数MATCH查找不成功，返回错误值#N/A。
在实际查询数据的过程中，MATCH函数常常与其他函数如INDEX函数、OFFSET函数等联合使用。
利用MATCH函数还可以对比分析两个表格中的数据，并标识出两个表格中的重复数据和不重复数据，下面通过案例1-11说明利用MATCH函数对比分析两个表格中的数据的方法。【案例1-11】如图1-59所示为两组示例数据，在“旧表”和“新表”两个工作表中的A列保存有员工姓名，它们的顺序不一样，两个工作表中的数据也会有重复和不重复的。要求：将两个工作表中都存在的数据以“YES”标识出来，将只在一个工作表中存在的数据以“NO”标识出来，并且将一个表中的某项数据在另一个表中的单元格地址也同时标识出来。图1-59 两个工作表的数据
解决本案例中的问题的具体操作步骤如下所述。（1）设计对比分析表格，如图1-60所示。图1-60 对比分析两个工作表的数据（2）激活工作表“旧表”，在单元格B2中输入下面的公式，并将其向下复制，判断旧表中哪些数据在新表中存在：
=IF（ISERROR（MATCH（A2，新表!$A$1:$A$15，0）），"NO"，"YES"）
在单元格C2中输入下面的公式，并将其向下复制，判断旧表中那些存在于新表中的数据在新表中的单元格位置：
=IF（ISERROR（MATCH（A2，新表!$A$1:$A$15，0）），""，ADDRESS（MATCH（A2，新表!$A$1:$A$15，0），1，4））（3）激活工作表“新表”，在单元格B2中输入下面的公式，并将其向下复制，判断新表中哪些数据在旧表中存在：
=IF（ISERROR（MATCH（A2，旧表!$A$1:$A$15，0）），"NO"，"YES"）
在单元格C2中输入下面的公式，并将其向下复制，判断新表中那些存在于旧表中的数据在旧表中的单元格位置：
=IF（ISERROR（MATCH（A2，旧表!$A$1:$A$15，0）），""，ADDRESS（MATCH（A2，旧表!$A$1:$A$15，0），1，4））
这样，就得到了两个表格的对比结果，如前面的图1-60所示。
说明：ADDRESS函数用于按照给定的行号和列标，建立文本类型的单元格地址。使用方法如下：
=ADDRESS（row_num，column_num，abs_num，a1，sheet_text）
=ADDRESS（行号，列标，引用类型，引用样式，工作表的名称）
例如，公式“=ADDRESS（2，8）”的结果是行号为2、列号为8处单元格的绝对地址$H$2。而公式“=ADDRESS（2，8，4）”的结果是行号为2、列号为8处单元格的相对地址H2。
需要注意ADDRESS函数的参数中引用类型参数abs_num的设置，1或省略时表示绝对引用；2时表示绝对行号，相对列标；3时表示相对行号，绝对列标；4时表示相对引用。
2.INDEX函数
INDEX函数用于涉及两个变量的查询，也就是取出列表或数组中的指定由行序号和列序号交叉处的值。INDEX函数有两种语法形式。
语法一，数组形式：
=INDEX（array，row_num，column_num）
=INDEX（列表或数组，行序号，列序号）
语法二，引用形式：
=INDEX（reference，row_num，column_num，area_num）
=INDEX（一个或多个单元格区域的引用，行序号，列序号，引用区域序号）
有关INDEX函数的参数含义请参阅Excel的联机帮助信息。
需要注意的是，只使用INDEX函数是很难查找到需要的数据的，除非事先已经知道了该数据所在的具体行和列。INDEX函数常常与MATCH函数一起使用，其功能是，首先用MATCH函数确定数据所在的行和所在的列，然后再利用INDEX将该行和列交叉处的数据取出。下面通过案例1-12使用查找类函数查找特定的商品名称和销售人员的方法。【案例1-12】某公司各销售人员销售各种不同商品的销售额数据如图1-61中的A至F列所示。要求：（1）找出每个销售人员的最大和最小销售额对应的商品名称；（2）找出各种商品中最大和最小销售额对应的销售人员姓名。图1-61 查找每个销售人员的最大和最小销售额对应的商品名称
对于第1个问题，可以联合使用MATCH函数、INDEX函数和MAX函数及MIN函数来解决。查找数据的基本思路是：首先使用MAX函数或MIN函数计算最大值或最小值，再利用MATCH函数确定这个最大值或最小值所在某行的位置（即列号），最后使用INDEX函数把该最大值或最小值对应的商品名称从工作表的第一行中查找出来。具体操作步骤如下。（1）在单元格G2中输入公式“=INDEX（$B$1:$F$8，1，MATCH（MAX（B2:F2），B2:F2，0））”。（2）在单元格H2中输入公式“=INDEX（$B$1:$F$8，1，MATCH（MIN（B2:F2），B2:F2，0））”。（3）选取单元格G2和H2，向下填充复制到单元格G8和H8，即得到每个销售人员的最大销售额的商品名称和最小销售额的商品名称。
为了解决第2个问题，首先可以设计如图1-62所示的表格，然后按以下的步骤操作。（1）在单元格B10中输入公式“=INDEX（$A$2:$A$8，MATCH（MAX（B2:B8），B2:B8，0），1）”。（2）在单元格B11中输入公式“=INDEX（$A$2:$A$8，MATCH（MIN（B2:B8），B2:B8，0），1） ”。（3）选取单元格区域B10:B11，将其复制到单元格区域C10:F11，即得到各种商品中最大和最小销售额对应的销售人员姓名。图1-62 查找每种商品中最大和最小销售额对应的销售人员姓名
3.VLOOKUP函数
VLOOKUP函数用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。其语法如下。
=VLOOKUP（lookup_value，table_array，col_index_num，[range_lookup]）
=VLOOKUP（查询值，数据表，第几列，[是否精确查询]）
VLOOKUP函数的各个参数含义简要说明如下。
参数lookup_value指定要查询的数据，该数据必须出现在数据区域的第一列（注意不一定是工作表的第一列）。
参数table_array指定从什么样的数据区域里查询数据，它必须包含待查数据所在的列。
参数col_index_num指定从数据区域里的哪列进行查询。这个列号是数据区域的相对位置，不是工作表的绝对列号。
这里要强调以下几点。
如果range_lookup为TRUE，则table_array的第一列的数值必须按升序排列，否则，函数VLOOKUP将不能给出正确的数值。如果range_lookup为FALSE，则table_array不必进行排序。
函数VLOOKUP查询不区分大小写。
如果参数range_lookup为TRUE或省略，则函数VLOOKUP进行近似查询，也就是说，
如果找不到精确匹配值，则返回小于lookup_value的最大数值。如果range_value为
FALSE，函数VLOOKUP将查找精确匹配值，如果找不到，则返回错误值#N/A!。
下面通过案例1-13说明使用VLOOKUP函数动态查询指定月份各种产品销售额的方法。【案例1-13】某公司5种产品6个月的销售额数据如图1-63种的单元格区域A1:F7所示。要求：设计一个可以动态查询指定月份各种产品销售额的模型。
解决本案例问题的具体操作步骤如下所述。（1）设计查询表格，如图1-63所示，其中在A10单元格输入指定的月份。（2）在单元格B10中输入公式“=VLOOKUP（$A$10，$A$2:$F$7，COLUMN（），FALSE）”。（3）选择将单元格B10，将其复制到单元格区域C10:F10，即可得到指定月份的各种产品销售额数据。图1-63 运用VLOOKUP函数动态查询指定月份的各种产品销售额
这里在公式中使用了COLUMN函数，因此单元格B10至F10的公式内容都是一样的，但其返回结果不同。COLUMN函数的功能是返回公式所在单元格的列号数字。例如，如果在单元格B10中输入公式“=COLUMN （）”，其结果是2；如果在单元格C10中输入公式“=COLUMN（）”，其结果是3。
与COLUMN函数对应的还有ROW函数，它的功能是返回公式所在单元格的行号数字。例如，如果在单元格B3中输入公式“=ROW（）”，其结果是3；如果在单元格G10输入公式=ROW（）”，其结果是10。
在建立公式的过程中，恰当灵活地使用COLUMN函数和ROW函数，可以快速输入和复制大量的计算公式。
在实际工作中，VLOOKUP函数的应用是非常广泛的，在后面的章节中，还会陆续介绍VLOOKUP函数的一些具体应用。
4.INDIRECT函数
INDIRECT函数的功能是返回由文本字符串指定的引用。使用方法如下：
=INDIRECT（ref_text，a1）
=INDIRECT（单元格引用字符串，引用样式）
函数中参数a1指定引用样式，如果a1为TRUE 或省略，则ref_text被解释为A1样式的引用，如果a1为FALSE，则ref_text被解释为R1C1样式的引用。
例如，假设A1单元格中的数据为字符串"B2"，而B2单元格中的数据为100，则公式“=INDIRECT（"A1"）”的结果会是100，因为公式“=INDIRECT（"A1"）”实质上就是“=B2”。
INDIRECT函数应用得最多的是在公式中构建数组常量。此外，利用INDIRECT函数进行跨表查询也是非常实用和方便的。下面通过案例1-14说明跨表动态查询某部门费用的方法。【案例1-14】某公司有若干个不同的部门，每个部门的费用数据分别保存在以部门名称命名的工作表中，如图1-64所示。由于该公司的部门很多，所以要查看某个部门的费用数据时若逐个工作表查找会很不方便。现需要设计一个查询系统，以便能够根据所选择的部门动态地显示该部门的所有费用。图1-64 某公司各部门的费用清单
解决本案例问题的具体步骤如下所述。（1）设计查询表格，如图1-65所示。图1-65 设计查询表格（2）在单元格B1中输入要查询的部门名称（可利用数据有效性选择输入）。（3）在单元格A4中输入下面的查询公式：
=IF（INDIRECT（$B$1&"!"&CHAR（64+COLUMN（））&ROW（）-2）=0，""，INDIRECT（$B$1&"!"& CHAR（64+COLUMN（））&ROW（）-2））（4）选择单元格A4，将其在A至D列中向右向下填充复制到适当的行数，即可得到查询结果。
在A4单元格输入的公式中，综合运用了查找引用函数INDIRECT和ROW以及COLUMN。注意这里使用了CHAR函数来获取某个字符所代表的ASCII码，例如CHAR（65）表示字母A，CHAR（66）表示字母B，依此类推。
在将A4单元格的公式向右复制时，表达式CHAR（64+COLUMN（））的运行结果会得到不同的字母A、B、C、D，而ROW（）-2会得到行号，因此CHAR（64+COLUMN（））&ROW（）-2）返回的是单元格地址文本字符串A1、B1、A2、B2等（注意不是单元格地址引用），表达式$B$1&"!"返回的是工作表名称加惊叹号的文本字符串如“办公室!”，“销售部!”等，因此，INDIRECT函数中的表达式$B$1&"!"&CHAR（64+COLUMN（））&ROW（）-2返回的结果是某个工作表的某个单元格地址的文本字符串，如“办公室!A1”，“销售部!A1”、“办公室!A2”，“销售部!A2”等，最后利用INDIRECT函数将这个文本字符串转换成真正的工作表地址引用，从而即可得到某个工作表中某个单元格的数据。
图1-66显示的是在单元格B1中选择输入“销售部”后的数据查询结果。图1-66 按指定部门动态查询数据
使用INDIRECT查询或汇总多个工作表数据时，各个工作表的先后顺序可以任意排列，返回的结果不会受此影响，这也是这种方法的一个比较突出的优点。
5.OFFSET函数
OFFSET函数是一个功能非常强大的查找和引用函数，它在动态定位单元格和单元格区域方面，有着巨大的优越性和方便性。尤其是利用OFFSET函数定义动态区域名称，可以绘制动态图表，制作动态的数据透视表，进行动态的求和汇总计算。
OFFSET函数的功能是以指定的引用为参照系，通过给定偏移量得到新的引用。OFFSET函数返回的引用可以为一个单元格或单元格区域。语法如下：
=OFFSET（reference，rows，cols，height，width）
=OFFSET（参照单元格，列位移量，行位移量，[高度]，[宽度]）
例如：公式“=OFFSET（C3，2，3，1，1）”将返回单元格F5中的值。这里，当前指定的引用为单元格C3，以此为参照系，向下偏移2行，为第5行；向右偏移3列，为F列；高度和宽度均为1，表示仅为一个单元格。
公式“=OFFSET（B1，，，10）”将返回单元格区域B1:B10。这里，以单元格B1为参照系，向下扩展到10行的高度，因此就得到了一个新的单元格区域B1:B10。
公式“=OFFSET（D10，?3，?2）”将返回单元格B7。这里，以单元格D10为参照系，向上偏移3行，向左偏移2行（为B列）。
下面通过案例1-15说明使用OFFSET函数动态查询某月销售额及累计销售额的方法。【案例1-15】某公司全年12个月各种商品的销售额数据记录清单如图1-67所示。要求：设计一个查询系统，其功能是在指定月份和商品后，可以将该商品在指定月份以前各月（包括指定月份）的销售额数据显示出来，同时计算出截至指定月份该商品的累计销售额。图1-67 同时显示查询数据明细和累计值的数据查询系统
本案例中查询各个月份的销售额数据的问题，可以使用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-67所示。
为了便于解决比较复杂的一些财务数据处理的问题，有些情况下，需要对某个单元格区域、某列或者某行定义一个动态的名称，以便在增加或减少数据后，该名称代表的数据区域能够自动调整。联合使用OFFSET函数和COUNTA函数可以解决这样的问题。下面通过案例1-16说明为动态单元格区域定义名称的方法。【案例1-16】某公司的销售数据存放在如图1-68所示的工作表的A至C列。要求：对A至C列的数据区域定义一个名为“Data”的动态名称，并且对A至C列的各列数据区域分别定义名为各列的列标题的动态名称。图1-68 为动态单元格区域定义名称
根据题意，本案例要定义的名为“Data”的动态名称对应的是这样一个单元格区域：以单元格A1为左上角单元格，数据区域的行数根据A列的非空单元格的个数来确定，数据区域的列数根据第1行的非空单元格个数来确定，这里假定该数据清单的A列和第一行是关键数据，在A列和第一行的数据区域内不允许有空单元格存在，数据区域外也不允许有其他数据存在。在这种情况下，可在“定义名称”对话框中使用下面的引用公式定义动态名称：
Data：=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-17说明使用ROUND函数处理计算误差的方法。【案例1-17】某工程公司预付账款及其他应收款的有关记录如图1-69所示，其中在J列的单元格中已设置了计算余额的公式，例如，J12单元格中的公式为“=J11+G12-H12”；在I列的单元格中已设置了根据J列单元格的数值判断借贷方向的公式，例如，I11单元格的公式为=IF（J11>0，"借"，IF（J11<0，"贷"，"平"））”，这个公式的含义是，这一行J列的单元格数字分别为正、负和0时，则I列对应的单元格将分别返回“借”、“贷”和“平”。但是从图1-69中可以看出，第14、17和19行J列的数字为0，I列对应的单元格返回的结果并不是“平”，而是“贷”。为何会出现这样的问题？怎样解决这类问题呢？图1-69 计算结果表面看起来是矛盾的
为了说明本案例中出现上述问题的原因，可以将单元格I14、I17和I19的数字以增加更多小数位数的格式显示，如图1-70所示，不难看出，这三个单元格的数字并不是零，而是非常小的负数！由此可见，尽管这张工作表中的计算公式很简单，并没有进行复杂的计算，但还是造成了计算误差，因而导致判断错误。图1-70 表面看起来是0 的数字实际上不为0
为了解决本案例中出现的这类问题，可以利用ROUND函数将余额四舍五入到两位小数，就会得到正确的计算结果，如图1-71所示。此时，在单元格J11中输入的计算公式为=ROUND（G11-H11，2）”，在单元格J12中输入的计算公式为“=ROUND（J11+G12-H12，2）”，J列其他单元格中的公式，依此类推。图1-71 正确的计算结果第2章 财务数据的筛选与分类汇总分析在日常的财务管理工作中，使用Excel的筛选和分类汇总功能分析各种财务数据可以大大提高工作效率。筛选包括自动筛选、高级筛选和自定义筛选几种情况，而为了利用分类汇总功能分析财务数据，首先需要对财务数据按照拟分类的字段进行排序。本章将介绍利用Excel的筛选、排序、分类汇总和分级显示功能处理财务数据的方法和技巧，希望能对读者提高日常财务管理工作的效率有所帮助。2.1 使用筛选工具分析财务数据
2.1.1 从普通日记账中自动筛选现金日记账
利用Excel的自动筛选功能可以很方便地从普通日记账中筛选出现金日记账，在此基础之上就可以随时反映企业的现金余额。下面通过案例2-1说明从普通日记账中自动筛选现金日记账的方法。【案例2-1】 某企业2009年2009年11月的普通日记账如图2-1的单元格区域A2:F38所示。现在要求根据该企业的普通日记账筛选出现金日记账。假定已知2009年10月31日的期末现金余额为26800元，要求进一步计算该企业2009年11月末的现金余额。图2-1 某企业的普通日记账
在如图2-1所示的数据表中，A4单元格的下边显示的是拆分条，其作用是将工作表拆分成两个部分，从而在查看下边的数据区域时，上边的数据不会溢出屏幕之外。在Excel 2003中设置这种拆分条的方式是执行“窗口”→“拆分”命令，取消拆分条时执行“窗口”→“取消拆分”命令。在Excel2007中设置拆分条的方式是在“视图”选项卡的“窗口”选项组中执行“拆分”命令，再次执行该命令时可取消拆分条。
利用自动筛选功能从普通日记账中筛选现金日记账并计算期末现金余额的具体步骤如下。（1）在数据清单中任选一个单元格。（2）在Excel 2003中，单击“数据”→“筛选”→“自动筛选”命令；在Excel 2007中，需要单击“数据”选项卡，在“排序和筛选”功能组中，单击“筛选”命令，则数据表每列的标题右边会出现一个自动筛选符号，如图2-2所示。（3）单击D2单元格右边的自动筛选符号，在展开的菜单中选择“现金”，则系统会自动筛选出总账科目是现金的所有记录，如图2-3所示。图2-2 建立自动筛选图2-3 自动筛选出来的现金日记账（4）将单元格区域A1:F37复制粘贴到一个新工作表中，并将单元格A1单元格的“普通日记账”改为“现金日记账”，如图2-4所示。图2-4 将筛选出的现金日记账复制到新工作表中（5）在新复制的数据表第3行的上方插入一个空行，输入已知的2009年10月末的现金余额，在G2单元格中输入列标题“现金余额”，分别在单元格E22和F22中单击自动求和按钮，对本列数据进行自动求和，然后在单元格G4中输入公式“=G3+E4-F4”，并将其复制填充到单元格区域G5:G21，最后在单元格G22中输入公式“=G21”，或输入公式“=G3+E22?F22”，得到期末现金余额，如图2-5所示。图2-5 包含现金余额数据的现金日记账
2.1.2 对有合并单元格标题的付款明细表建立自动筛选
当数据表的标题列中有合并单元格存在时，直接执行筛选命令一般无法得到所需要的筛选结果，而若先把合并单元格取消合并，然后对标题列规范整理之后再进行筛选虽然可以达到目的，但这样做会比较麻烦。实际上，在不对列标题中的合并单元格取消合并的情况下，经过适当设置同样也可以直接筛选出所需要的结果。下面通过案例2-2说明对有合并单元格标题的付款明细表建立自动筛选的方法。【案例2-2】某公司的付款明细表如图2-6所示，其中第2行和第3行的列标题中有合并单元格。要求：在不对标题中的合并单元格取消合并的情况下，筛选出该公司以承兑汇票方式付款的所有记录。图2-6 付款明细表
在如图2-6所示的工作表中，如果直接在数据表中任选一个非空单元格后执行自动筛选命令，那么自动筛选符号会出现在第2行而不是第3行的标题上，为了能够将承兑汇票付款的记录筛选出来，具体的操作步骤如下。（1）首先选择第3行，然后执行自动筛选命令，这样自动筛选符号会出现在第3行的标题上，如图2-7所示。图2-7 有合并单元格标题情况下建立的自动筛选（2）在D3单元格的右边单击自动筛选符号，在Excel 2003中，应在展开的列表中选择“非空白”选项，如图2-8所示；在Excel 2007中，应在展开的列表中取消勾选“空白”复选框，如图2-9所示。图2-8 在Excel 2003的筛选列表中选择“非空白”选项图2-9 在Excel 2007的筛选列表中取消勾选“空白”复选框（3）执行上述命令后，以承兑汇票方式付款的所有记录就被筛选出来了，如图2-10所示。图2-10 得到的筛选结果
2.1.3 利用自定义筛选方式筛选部分付款记录
建立自动筛选后，还可以根据需要进行自定义筛选。例如，仍以“案例2-2”中图2-7所示的数据为例，现在要筛选出付款总额大于50万元的所有记录，具体操作步骤如下。（1）单击合并单元格D2右侧的下拉箭头，在Excel 2003中，应在展开的列表中选择“自定义”筛选方式，如图2-11所示；在Excel 2007中，应在展开的列表中的“数字筛选”菜单中选择“自定义”筛选方式，如图2-12所示。图2-11 在Excel 2003中选择“自定义”筛选命令图2-12 在Excel 2007中选择“自定义”筛选命令（2）上述操作完成后，即可打开“自定义自动筛选方式”对话框，在其中设置所需要的筛选条件，Excel 2003和Excel 2007中“自定义自动筛选方式”对话框的设置情况分别如图2-13和图2-14所示。图2-13 Excel 2003中“自定义自动筛选方式”对话框图2-14 Excel 2007中“自定义自动筛选方式”对话框（3）单击“确定”按钮后，大于50万元的全部付款记录即被筛选出来，如图2-15所示。图2-15 筛选出的大于50万元的付款记录
2.1.4 利用高级筛选功能筛选销售数据
有时候，需要按照某种特殊的要求对数据进行筛选，例如要把所有大于平均值的数据筛选出来，把最近一个月的数据筛选出来，设置两个筛选条件下的数据筛选等。在这种情况下，可以使用高级筛选功能来解决问题。
在建立高级筛选之前，首先需要建立一个筛选条件区域。这些条件既可以是“与条件”，也可以是“或条件”，或者“与条件”与“或条件”的组合使用，还可以使用计算条件。这些条件的设置规则如下所述。
同一行的条件构成“与”关系条件。例如，如图2-16所示的条件可用于查找薪金在5 000至8 000元之间的纪录。
同一列的条件构成“或”关系条件。例如，如图2-17所示的条件可用于查找部门为销售部或办公室的记录。图2-16 同一行的条件图2-17 同一列的条件
不同列、同行的条件构成不同字段的“与”关系。例如，如图2-18所示的条件可用于查找男性且为经理的纪录。
不同列、不同行的条件构成不同字段的“或”关系条件。例如，如图2-19所示的条件可用于查找职称为高级工程师或职务为经理的纪录。图2-18 同行不同列的条件图2-19 不同列、不同行的条件
不同列、不同行的条件构成“与”关系和“或”关系的复杂条件。例如，如图2-20所示的条件可用于查找办公室的女性经理，或者销售部的男性高级工程师的纪录。图2-20 不同列、不同行的复杂条件
下面通过案例2-3说明利用高级筛选功能筛选销售数据的方法。【案例2-3】如图2-21所示的是某公司不同销售人员在不同城市销售不同商品的销售量和销售额的有关数据。要求：筛选出销售人员是“周德宇”、城市是“上海”或者“天津”、商品是“冰箱”或彩电的、并且销售额在50 000之100 000元之间的所有记录。
利用高级筛选功能对本案例中的销售数据清单进行筛选的具体步骤如下。（1）首先建立一个条件区域，如图2-21所示。图2-21 原始数据以及建立的条件区域（2）单击数据区域或数据库中的任一非空单元格，在Excel 2003中单击“数据”→“筛选”→“高级筛选”命令；在Excel 2007中应单击“数据”选项卡，然后在“排序和筛选”功能组中单击“高级”命令按钮，打开“高级筛选”对话框，如图2-22所示。（3）一般情况下，系统将自动选择列表区域（即数据区域），所以只需在“条件区域”输入框中输入条件区域（本例中为“$H$2:$L$4），如图2-23所示。图2-22 “高级筛选”对话框图2-23 设置高级筛选条件（4）高级筛选结果可以显示在数据清单的原有区域中，也可以显示在工作表的其他空白单元格区域，系统默认的方式是在数据区域的原有区域中显示结果。若需要在工作表的其他空白单元格区域显示结果，则应选中“将筛选结果复制到其他位置”单选按钮，并在“复制到”输入框中输入需要显示筛选结果的单元格（开头的一个单元格即可）。（5）单击“确定”按钮，即可得到高级筛选后的结果。如图2-24所示是在原有区域显示的筛选结果。图2-24 在原有区域显示的高级筛选结果
试读结束[说明：试读内容隐藏了图片]