excel使用公式与函数

第10章使用公式与函数利用Excel的公式和函数可以对表格中的数据进行各种计算和处理操作，从而提高我们在制作复杂表格时的工作效率及计算准确率。本章就来介绍公式和函数的使用方法。本章内容提要使用公式单元格的引用审核公式使用函数10.1使用公式公式是工作表中用于对单元格数据进行计算的表达式，利用公式可对同一工作表的各单元格、同一工作簿中不同工作表的单元格，以及不同工作簿的工作表中单元格的数值进行加、减、乘、除、乘方等各种运算。要输入公式必需先输入“=”，然后再在后面输入表达式，否则Excel会将输入的内容作为文本型数据处理。表达式由运算符和参与运算的操作数组成。运算符可以是算术运算符、比较运算符、文本运算符和引用运算符；操作数可以是常量、单元格地址和函数等。10.1.1认识公式的运算符运算符用来连接要运算的数据对象，并说明进行哪种运算。Excel中的运算符包括如下4种类型。算术运算符：用于完成基本的数学运算。如“+（加）”、“-（减）”、“*（乘）”、“/（除）”、“%（百分比）”和“^（乘幂）”都属于算术运算符。比较运算符：用于比较两个数值并产生逻辑值，结果为真时，其逻辑值为TRUEE，如图10-1所示，否则为FALS。“=（相等）”、“（大于）”、“（小于）”、“=（大于等于）”、“=（小于等于）”和“（不等于）”均为比较运算符。文本运算符：使用文本运算符“＆”（与号）可将两个或多个文本值串起来产生一个连续的文本值。引用运算符：使用引用运算符可以将单元格区域合并计算。10.1.2运算符的优先级如果在一个公式中出现多种运算符，Excel会按运算符的优先级逐个进行计算。若要更改运算的顺序，可以将公式中要先进行运算的部分用括号括起来。10.1.3输入和编辑公式在Excel中输入公式的方法与输入文本类似。单击要输入公式的单元格，然后输入公式，按【Enter】键确认编辑公式与编辑数据的方法相同。双击含有公式的单元格进入编辑状态，将光标定位在需要修改的位置，按【Backspace】键删除错误内容，再输入正确内容。然后按【Enter】键即可。也可以在选中单元格后在编辑栏中进行修改操作。默认情况下，单元格中只显示公式计算结果，用户只能在编辑栏中查看公式。为了方便查看，可设置在单元格中显示公式。10.1.4复制公式复制公式可以快速为其他单元格输入公式，从而提高工作效率。其操作方法与复制数据的方法相似。复制含有公式的单元格利用“选择性粘贴”对话框复制公式利用填充柄复制公式10.2单元格的引用通过单元格的引用，可以在一个公式中使用工作表不同部分的数据，或者在多个公式中使用一个单元格中的数据，还可以引用同一个工作簿中不同工作表中的单元格，甚至还可以引用不同工作簿中的数据。10.2.1引用单元格和单元格区域在Excel中，每个单元格都有一个独一无二的地址，该地址由单元格所在的列标和行号组成，用户可以通过该地址引用该单元格10.2.2相对引用、绝对引用和混合引用1．相对引用相对引用指的是单元格的相对地址，其引用形式为直接用列标和行号表示单元格。当将公式复制或填充到其他单元格时，系统会根据当前单元格的地址自动改变公式中引用的单元格的行号和列标。复制公式时，相对引用改变2．绝对引用绝对引用是指总是引用指定位置的单元格。如果公式所在的单元格位置发生改变，使用绝对引用的单元格不变。使用绝对引用时，在列标和行号前加一个“$”符号。复制公式时，绝对引用不变3．混合引用混合引用是指对单元格的引用既存在相对引用，又存在绝对引用。混合引用的方式有两种：一种为列相对引用，行绝对引用，如C$4，此时公式中引用的行号是不变的，而列号会随公式所在位置的变化而变化；另一种为列绝对引用，行相对引用，如$C4，此时公式中引用的列号不变，而行号会随公式所在位置的变化而变化。编辑公式时，输入单元格地址后，按【F4】键可在绝对引用、相对引用和混合引用之间切换。10.2.3引用不同工作表或工作簿中的单元格如果要引用同一工作簿中不同工作表中的单元格，可在公式中输入“工作表名称！单元格地址”，例如，Sheet2！C3，也可在输入公式时切换到相应工作表，然后单击所需单元格。引用Sheet1工作表中的B15单元格如果要引用不同工作簿中的单元格，可直接在公式中输入“[工作簿名称]工作表名称！单元格地址”，例如，[Book2]Sheet3!D2，也可以在输入公式时切换到相应工作簿，然后单击相应工作表中要引用的单元格。如果被引用的单元格数据发生改变，那么引用了该单元格的工作簿再次打开时，Excel会打开一提示对话框，提示引用的数据发生改变，单击“更新”按钮，系统自动更新公式的计算结果。10.3审核公式在使用公式的过程中，有时可能会因人为疏忽，或是表达式的设置错误，导致计算结果发生错误。Excel提供的审核功能可以帮助用户找出并修正公式中的错误。10.3.1公式错误代码“####”：输入到单元格中的数值或公式计算结果太长，单元格容纳不下。增加单元格宽度可以解决这个问题。另外，日期运算结果为负值也会出现这种情况，此时可以改变单元格的格式，比如改为文本格式。“#DIV/0!”：除数引用了零值单元格或空单元格。“#N/A”：公式中没有可用数值，或缺少函数参数。“#NAME?”：公式中引用了无法识别的名称，或删除了公式正在使用的名称。例如，函数的名称拼写错误，使用了没有被定义的区域或单元格名称，引用文本时没有加引号等。“#NULL!”：使用了不正确的区域运算符或引用的单元格区域的交集为空。例如，输入公式“=A1:B4C1:D4”，因为这两个单元格区域交集为空，所以回车后返回值为“#NULL!”。“#NUM!”：公式产生的结果数字太大或太小，Excel无法表示出来，例如，输入公式“=10^309”，由于运算结果太大，公式返回错误；或在需要数字参数的函数中使用了无法接受的参数，例如，在输入开平方的公式(SQRT)时，引用了负值的单元格或直接使用了负值。“#RFF”：公式引用的单元格被删除，并且系统无法自动调整，或链接的数据不可用。“#VALUE”：当公式需要数字或逻辑值时，却输入了文本；为需要单个值（而不是区域）的运算符或函数提供了区域引用；输入数组公式后，没有按【Ctrl＋Shift＋Enter】组合键确认。10.3.2使用“公式审核”工具栏使用“公式审核”工具栏可以非常方便地对单元格中的公式进行错误检查。选择“视图”“工具栏”“公式审核”菜单，显示“公式审核”工具栏。显示工作表中出错的单元格内容及错误产生原因追踪出错单元格所引用的单元格并进行修改追踪从属单元格追踪引用单元格10.4使用函数Excel2003提供了大量的内置函数，利用这些函数可以很容易地完成各种复杂的数据处理，如数值统计、逻辑判断等。10.4.1函数的分类Excel中函数按其功能可分为以下几类。财务函数：可以进行常见的财务计算和财务分析，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值等。日期和时间函数：可以在公式中分析和处理日期值和时间值。数学和三角函数：进行各种数学计算。统计函数：对工作表中的数据进行统计、分析。查找和引用函数：查找特定的数据或引用公式中的特定信息。数据库函数：对数据表中的数据进行分类、查找和计算等。文本函数：用于处理公式中的文本字符串。逻辑函数：可以进行真假值判断，或者进行复合检验。信息函数：判定单元格或公式中的数据类型。10.4.2了解函数格式函数是预先定义好的表达式，它必须包含在公式中。每个函数都由函数名和参数组成，基本形式为：=函数名（参数1，参数2，……），如下所示。一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名后是用圆括号括起来的参数，各参数之间用逗号分隔。参数可以是数字、文本、数组和单元格引用，也可以是常量、公式或其他函数。10.4.3使用函数的方法Excel提供了多种输入函数的方法，用户可以在单元格中直接输入函数，也可以使用函数向导输入函数。1．直接输入函数如果用户对函数的使用非常熟悉，可直接在单元格或编辑栏中输入。方法是：单击要输入函数的单元格，然后依次输入等号、函数名、左括号、具体参数和右括号，最后单击编辑栏中的“输入”按钮或按【Enter】键，此时在输入函数的单元格中将显示运算结果。2．使用函数向导输入函数如果对Excel函数不是很熟悉，可以用Excel函数向导来输入函数进行计算。在工作表中选择要进行计算的单元格区域松开鼠标左键，自动返回“函数参数”对话框10.4.4常用函数介绍1．COUNT函数COUNT函数的功能是统计参数列表中含有数值数据的单元格个数。其语法格式是COUNT(value1,value2,…)。其中参数“value1,value2,…”为包含或引用各种类型数据的参数（1到30个），但只有含数值的单元格才被统计。利用COUNT函数计算考试人数2．COUNTIF函数COUNTIF函数的功能是统计某个单元格区域中符合指定条件的单元格数目。其语法格式为COUNTIF(range,criteria)。其中“range”为要统计单元格个数的单元格区域；“criteria”为指定的条件表达式。利用COUNTIF函数统计600分以的人数3．MAX和MIN函数这两个函数的功能分别是返回参数列表中的最大值和最小值。其语法格式是MAX(number1,number2,…)和MIN(number1,number2,…)。其中参数“number1,number2,…”可以是数字、空白单元格、逻辑值或数字的文本表达式（1到30个）。如果没有有效参数，MAX函数和MIN函数的返回值都为“0”。利用MAX函数求总分最高分4．RANK函数RANK函数的功能是返回一个数字在数字列表中的排位。其语法格式为RANK(number,ref,order)。其中“number”为需要排序的数值；“ref”为要排序的单元格区域；“order”指明排序方式，如果为“0”或者缺省，则按降序排名，即数值越大，排名结果数值越小；如果不为“0”值，则按升序排名，即数值越大，排名结果数值越大。5．IF函数IF函数的功能是执行真假值判断，根据逻辑计算的真假值返回不同结果。其语法格式为：IF（logical_test,value_if_true,value_if_false），其中“logical_test”表示要选取的条件；“value_if_true”表示条件为真时返回的值；“value_if_false”表示条件为假时返回的值。按F4键将其转换为绝对引用利用RANK函数将成绩按总分大小进行排名假设将平均分划分为2个等级，大于等于100的为“A”，否则为“B”利用IF函数按平均分划分等级6．TODAY函数TODAY函数返回当前日期的序列号。该函数不需要参数，输入公式“=TODAY()”，确认后即显示系统的当前日期。如果系统日期发生了改变，按【F9】键即可更新数据。7．PMT函数PMT函数即年金函数。它的功能是基于固定利率及等额分期付款方式，返回贷款的每期付款额。其语法格式是：PMT(Rate,Nper,Pv,Fv,Type)，其中Rate为贷款利率；Nper为该项贷款的付款总期数；Pv为现值，或一系列未来付款的当前值的累积和，也称为本金；Fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略Fv，则假设其值为零，也就是一笔贷款的未来值为零；Type为数字0或1，用以指定各期的付款时间是在期初还是期末。1代表期初，不输入或输入0代表期末。利用PMT函数计算出月还款额8．数学与三角函数SIN函数：返回给定角度的正弦值。其语法格式为SIN(number)，其中“number”为需要求正弦的角度，以弧度表示。如果参数的单位是度，需将其乘以PI()/180将其转换为弧度或使用RADIANS函数将其转换为弧度。例如，计算30度的正弦值，可输入公式“=SIN(30*PI()/180)”或“=SIN(RADIANS(30))。COS函数：返回给定角