Excel函数入门.docx

第 1 章函数应用基础在本章中，将首先讲解关于 Excel 函数的基础知识。主要包括如何使用公式和函数， 以及在使用公式和函数过程中，需要注意的内容。本章是本书后面章节的基础，希望用户 能够通过本章的知识，能够对公式和函数的内容有个整体的了解。1.1公式基础公式和函数是 Excel 处理数据的重要工具，当用户在使用 Excel 处理和分析问题的时 候，当涉及到各种运算时，一般会需要用到公式和函数。本小节首先讲解关于公式和函数 的基本内容，对其使用过程中的问题加以分析。1.1.1什么是公式公式是用户自行定义的计算过程，在 Excel 中以输入“”开始，来输入公式（或者 函数），如图 1.1 所示。图 1.1 开始输入公式从上图中可以看出，当用户在单元格中开始输入“=”以后，Excel 就会准备接收后面 输入的公式或者函数。在标题栏的左侧会显示“SUM”函数名称，提示用户输入函数或者 公式。在输入公式时，用户除了输入“=”之外，还需要输入一些运算符号，进行运算。 在实际应用中，四则运算是最常见的运算符号，也就是说，+、和。用户在输入等号之后，使用上面的运算符号，开始数值运算。在单元格 A5 中输入公式 “=A1+A2+A3”，表示计算 A1A3 中单元格数值的和，如图 1.2 所示。图 1.2 输入公式在公式中，用户除了可以引用单元格的数值之外，还可以直接输入具体的数值。例如， 需要在 Excel 中计算“890653”的数值，可以在单元格中直接输入“=890*653”，得到的 结果如图 1.3 所示。图 1.3 直接计算数值运算结果说明：在公式中，当用户输入单元格作为参数的时候，实际上是使用对单元格的引用。引用是 Excel中十分重要的概念，在后面的章节中将详细讲解。1.1.2切换公式从小节中的例子可以看出，公式的功能十分强大，几乎可以完成常见的各种运算。但 是，这同样带来一个问题：当公式中的参数比较多的时候，将很难跟踪公式的运算过程。 当用户需要查看公式的具体运算时，可以双击包含公式的单元格，如图 1.4 所示。图 1.4 显示公式的表达式笔者在这里提供一个快捷方式，按下“Ctrl”“”快捷键，可以显示单元格中的 公式表达式，如图 1.5 所示。图 1.5 使用快捷方式从上图中可以看出，当用户按下“Ctrl”“”快捷键，就可以在单元格中显示具 体的公式。再次按下“Ctrl”“”快捷键，就可以恢复原来的计算结果。1.1.3公式的引用方式在前面小节中，用户已经接触过引用的概念。在 Excel 中，用户可以在公式或者函数 中，引用本工作表中的数据，也可以引用不同工作表上或者其他工作薄中的数据。在 EXCEL 中有多种引用的方式，一般可以分为 A1 引用方式和 R1C1 引用方式，也可以分为相对引 用和绝对引用方式。在默认的情况下，EXCEL 中的引用方式是 A1 引用方式。在这种引用中，用字母和数 字标示单元格。如果引用的是单元格区域，那么可以使用区域的左上角的单元格的标示符、 冒号和区域的右下角的单元格的标示符来共同标识。表 1.1 中列出了常用的几种引用格式 和对应的引用区域。表.1.1A1样式引用对照表引用格式对应的区域F2第 F 列和第 2 行交叉处的单元格D1:D3第 D 列中 13 行的单元格D2:G2第 2 行中 D 列到 G 列的单元格H:H第 H 列全部单元格E:H第 E 列到 H 列全部单元格3:3第 3 行的全部单元格2:10第 2 行到 10 行的全部单元格B2:F7第 B 列 2 行到 F 列 7 行的单元格EXCEL 的默认引用方式是 A1 方式，如果用户有需要采用 R1C1 样式引用，可以通过 选项的设置修改引用方式。选择“Office 按钮”|“Excel 选项”命令，打开“Excel 选项” 对话框，选择“公式”选项卡，然后在“使用公式”选框下面的“R1C1 引用样式”，如 图 1.6 所示。图 1.6 修改引用方式在 R1C1 引用样式中，R 代表行数字，C 代表列数字，用行和列数字共同指示单元格 的位置。1.1.4相对引用和绝对引用在 EXCEL 中，默认情况下公式使用的是相对引用，“相对”是指公式计算的单元格和 引用数据的单元格中的相对位置。当用户在 EXCEL 中复制公式时，复制的结果也会采用 相对引用方式，如图 1.7 所示。图 1.7 使用相对引用从上面的结果中，用户可以看出，当用户复制 A5 中的单元格到 B5 单元格时，复制 的是相对引用，A5 中的“=A1+A2+A3”会转换为“=B1+B2+B3”。当用户在使用 EXCEL 时，有时可能不希望使用相对引用，而需要使用绝对引用，也就是说，公式处理的是单元 格的精确地址。在 A1 样式中，使用绝对引用的方法是在行号和列标前面加上“$”符号， R1C1 引用样式中，直接在 R 和 C 后面接上行号和列号就可，如图 1.8 所示。图 1.8 使用绝对引用当用户使用绝对引用后，用户复制单元格中的公式时，Excel 会复制绝对引用，结果 如图 1.9 所示。图 1.9 复制绝对引用1.1.5公式和数值根据前面的介绍，公式和数值是不同的类型。在 Excel 中，有时只需要引用公式的结 果，而不用了解或者分析公式的具体表达式。这个时候，用户需要将公式的结果当作数值 来处理，而不能当作有公式的单元格来处理。一般来讲，用户可以直接使用选择性粘贴的方法来复制数值。以前面小节的例子来说 明，复制单元格 A5 中的数值，然后选择单元格 B5。单击鼠标右键，在弹出的快捷菜单中 选择“选择性粘贴”选项。打开“选择性粘贴”对话框，然后选中“数值”选项，如图 1.10 所示。图 1.10 选择粘贴数值单击对话框中的“确定”按钮，关闭对话框，查看粘贴的结果，如图 1.11 所示。图 1.11 查看粘贴结果从上面的结果中可以看出，单元格 B5 中只有相应的数据，没有复制对应的公式。1.1.6删除公式当用户在输入或者编辑公式的过程中出现错误的时候，需要及时的删除公式或者公式 的选项。删除的方法十分简单，直接选中公式对象，然后单击“Delete”键，就可删除。 或者，用户可以在编辑栏中编辑或者删除公式，如图 1.12 所示。图 1.12 在编辑栏中删除公式1.2使用公式审核工具在用户使用或者输入公式的时候，有时错误并不明显。也就是说，只能判断结果不正 确，但是并不知道具体错误在什么地方。有过编程经验的用户也许了解，只知道程序运行 的最后结果错误，但是不知道具体是哪行代码出问题。这个时候需要使用到公式审核工具。 本小节将详细讲解如何使用公式审核工具。1.2.1追踪引用单元格使用公式错误中，引用单元格错误是一种重要的类型。也就是说，公式的单元格引用 错误。例如，求解的单元格 A2 和 A3 的和，公式中却引用了单元格 B2。在本小姐中，继 续使用上节例题，简单介绍工具审核工具的使用。（1）选中出错的单元格，打开“公式”选项卡，单击“工具审核工具栏”中的“追 踪引用单元格”按钮，如图 1.13 所示。图 1.13 公式审核（2）单击“追踪引用单元格”按钮后，用箭头指示单元格引用，如图 1.14 所示。图 1.14 追踪引用单元格1.2.2追踪从属单元格前面小节已经详细讲解了引用单元格的方法，在本小节中，将讲解一个方向的检测方 法。在 Excel 中，用户还可以追踪从属单元格。也就说，分析该单元格受哪个数值或者单 元格的影响。本小节依然沿用前面小节的例子，分析从属单元格。（1）选择开始追踪的单元格 A3，然后在“公式”选项卡中，单击“公式审核”工具 栏中的“追踪从属单元格”按钮，如图 1.15 所示。图 1.15 追踪从属单元格（2）单击“追踪从属单元格”按钮，工作表中用带箭头指出受到计算值影响的单元 格，如图 1.16 所示。图 1.16 追踪到从属单元格1.2.3移去箭头当用户使用“追踪引用单元格”功能和“追踪从属单元格”功能追踪到目标单元格时， 可以在“公式”选项卡中，通过单击“公式审核”工具栏的“移去箭头”按钮，把箭头去 掉，如图 1.17 所示。图 1.17 移去箭头1.2.4错误检查当用户在使用公式的时候，如果出现了错误提示，而用户又不了解具体的错误情况， 则可以使用错误检查的方法。在本小节中，将使用简单的例子说明如何进行错误检查。（1）打开“公式”选项卡，单击“公式审核”工具栏中 的下三角按钮，在打开的 列表中选择“错误检查”，如图 1.18 所示。图 1.18 错误检查（2）在打开的“错误检查”对话框中将显示出错的单元格，以及出错原因，单击“下 一个”按钮，查看下一个出错的地方，如图 1.19 所示。图 1.19 查看出错原因当用户不了解具体的公式计算流程，可以单击“显示计算步骤”按钮，查看具体的错 误计算步骤，如图 1.20 所示。图 1.20 显示计算步骤如果对该错误不了解该类型的错误情况，可以单击“关于此错误的帮助”按钮，查看 对应的错误信息，如图 1.21 所示。图 1.21 关于错误的帮助信息1.3函数基础前面小节中，用户已经了解到关于 Excel 中公式的基本知识和方法。在本小节中，将 详细讲解关于 Excel 中函数中的使用方法和注意事项1.3.1什么是函数在数学理论中，函数表示一种关系，表示一个集合里的每一个元素对应到另一个集合 里的唯一元素。其中，体现这种关系的核心就是函数表达式。而在 Excel 中，函数是预先 定义的、执行计算或分析等数据处理任务的特殊公式。从 Excel 的知识框架中，Excel 函数 就是微软为用户开发的 VBA 程序代码库。以常用的求和函数 SUM 为例，其函数表达式是“SUM(number1，number2，)”。 对于该表达式的具体分析如下：“SUM”是函数名称，一个函数有且只有一个名称，表示函数的功能和用途。函数名称后紧跟小括号，括号内的数据是用逗号隔开的参与计算的各个参数。 参数是函数中最重要的部分，它确定函数的运算对象和顺序等。 在本书中，主要讲解的是 Excel 的内置工作表函数，也就是说，任何安装了 Excel 2007的用户，在任何工作表中，都使用这些函数。这些函数用户可以在 Excel 中查看对应的说明，并且可以在“公式”选项卡中查看对应的函数类别和名称，如图 1.22 所示。图 1.22 查看 Excel 的工作表函数1.3.2公式与函数的关系函数与公式既有区别又互相联系。函数属于公式，是预先定义计算过程的公式，计算 方法和内容已确定，用户只能通过改变函数参数的取值更改函数的计算结果；公式可以由 用户来自定义计算过程和计算方向，也可以更改公式的元素。函数的优点在于其能够准确进行复杂的计算，且能够保证准确度。用户在使用公式时， 会因种种原因导致计算准确度不足，不够系统。正由于上面的原因，在实际应用中，经常 将公式和函数相互搭配使用，取长补短。1.3.3函数的种类本书主要讲解 Excel 的内置工作表函数。在 Excel 2007 中，提供了 300 多个内置函数， 用户可以根据需要，调用满足要求的函数。本书将函数划分为 11 大类，后面章节将详细 介绍各类各个函数的含义，以及使用方法。财务函数：用于财务计算，例如，计算存款未来值、应还利息、收益率等。日期和时间函数：用于处理日期和时间值的函数。数学和三角函数：对表格中的数据进行求和、求平均值等各种运算。统计函数：用于对大量数据的统计分析。查找与引用函数：根据给定的条件查找特定的数值或单元格。数据库函数：对数据库中的数据进行计算、分析等。文本函数：用于对字符串进行替换、提取等操作。逻辑函数：用于判断真假值，还可以进行多条件检验。信息函数：用于判定单元格中的数据类型等。工程函数：主要用于工程计算中。多维数据集函数：用于联机分析处理（OLAP）数据库，是 Excel 2007 的新增函数 类型。1.3.4函数的帮助用户在使用 Excel 2007 的过程中，如果不了解某个函数的功能，以及其使用方法，可 以使用“函数帮助”来学习该函数，进而了解该函数的含义、参数类型，以及使用方法。 我们以学习财务函数中的“AVERAGEIF”函数为例，简单介绍使用函数帮助的过程。（1）单击“公式”选项卡中的“插入函数”按钮，在打开的“插入函数”对话框中， 单击“选择类别”列表框右边的下三角按钮，在打开的列表中选择“统计”，再在“选择 函数”列表框中选择“AVERAGEIF”函数，然后单击左下方的“有关该函数的帮助”， 如图 1.23 所示。（2）单击“有关该函数的帮助”超链接，就可以显示 Excel 帮助，如图 1.24 所示。图 1.23 使用函数帮助图 1.24 显示 Ecxel 帮助1.4输入函数在一般情况下，用户可以用两种方法来输入函数：一种是在编辑栏或单元格中输入；另一种是使用“插入函数”向导输入。其中，使用“插入函数”向导，可以让用户加深对 函数的理解。而在单元格中直接输入函数，则比较快捷。本节中将详细讲解两种方法。1.4.1直接输入函数如果用户对函数比较熟悉，就可以选择直接输入函数的方法。当用户输入函数时，必 须以“=”开头，然后输入函数名称