EXCEL函数与公式专题讲座课件.ppt

1、专题讲座专题讲座 使用公式与函数使用公式与函数 利用Excel的公式和函数可以对表格中的数据进行各种计算和处理操作，从而提高我们在制作复杂表格时的工作效率及计算准确率。下面就来介绍公式和函数的使用方法。内容提要内容提要n使用公式n单元格的引用n审核公式n使用函数 使用公式使用公式 公式是工作表中用于对单元格数据进行计算的表达式，利用公式可对同一工作表的各单元格、同一工作簿中不同工作表的单元格，以及不同工作簿的工作表中单元格的数值进行加、减、乘、除、乘方等各种运算。要输入公式必需先输入“=”，然后再在后面输入表达式，否则Excel会将输入的内容作为文本型数据处理。表达式由运算符和参与运算的操作数

2、组成。运算符可以是算术运算符、比较运算符、文本运算符和引用运算符；操作数可以是常量、单元格地址和函数等。认识公式的运算符认识公式的运算符 运算符用来连接要运算的数据对象，并说明进行哪种运算。Excel中的运算符包括如下4种类型。n算术运算符：算术运算符：用于完成基本的数学运算。如“+（加）”、“-（减）”、“*（乘）”、“/（除）”、“%（百分比）”和“（乘幂）”都属于算术运算符。n比较运算符：比较运算符：用于比较两个数值并产生逻辑值，结果为真时，其逻辑值为TRUEE，如图10-1所示，否则为FALS。“=（相等）”、“（大于）”、“=（大于等于）”、“=（小于等于）”和“（不等于）”均为比较

3、运算符。n文本运算符：文本运算符：使用文本运算符“”（与号）可将两个或多个文本值串起来产生一个连续的文本值。n引用运算符：引用运算符：使用引用运算符可以将单元格区域合并计算。运算符的优先级运算符的优先级 如果在一个公式中出现多种运算符，Excel会按运算符的优先级逐个进行计算。若要更改运算的顺序，可以将公式中要先进行运算的部分用括号括起来。输入和编辑公式输入和编辑公式 在Excel中输入公式的方法与输入文本类似。单击要输入公式的单元格，然后输入公式，按【Enter】键确认 编辑公式与编辑数据的方法相同。双击含有公式的单元格进入编辑状态，将光标定位在需要修改的位置，按【Backspace】键删除

4、错误内容，再输入正确内容。然后按【Enter】键即可。也可以在选中单元格后在编辑栏中进行修改操作。默认情况下，单元格中只显示公式计算结果，用户只能在编辑栏中查看公式。为了方便查看，可设置在单元格中显示公式。复制公式复制公式 复制公式可以快速为其他单元格输入公式，从而提高工作效率。其操作方法与复制数据的方法相似。复制含有公式的单元格利用“选择性粘贴”对话框复制公式 利用填充柄复制公式 单元格的引用单元格的引用 通过单元格的引用，可以在一个公式中使用工作表不同部分的数据，或者在多个公式中使用一个单元格中的数据，还可以引用同一个工作簿中不同工作表中的单元格，甚至还可以引用不同工作簿中的数据。引用单元

5、格和单元格区域引用单元格和单元格区域 在Excel中，每个单元格都有一个独一无二的地址，该地址由单元格所在的列标和行号组成，用户可以通过该地址引用该单元格 相对引用、绝对引用和混合引用相对引用、绝对引用和混合引用 1相对引用相对引用 相对引用指的是单元格的相对地址，其引用形式为直接用列标和行号表示单元格。当将公式复制或填充到其他单元格时，系统会根据当前单元格的地址自动改变公式中引用的单元格的行号和列标。复制公式时，相对引用改变 2绝对引用绝对引用 绝对引用是指总是引用指定位置的单元格。如果公式所在的单元格位置发生改变，使用绝对引用的单元格不变。使用绝对引用时，在列标和行号前加一个“$”符号。复

6、制公式时，绝对引用不变 3混合引用混合引用 混合引用是指对单元格的引用既存在相对引用，又存在绝对引用。混合引用的方式有两种：一种为列相对引用，行绝对引用，如C$4，此时公式中引用的行号是不变的，而列号会随公式所在位置的变化而变化；另一种为列绝对引用，行相对引用，如$C4，此时公式中引用的列号不变，而行号会随公式所在位置的变化而变化。编辑公式时，输入单元格地址后，按【F4】键可在绝对引用、相对引用和混合引用之间切换。引用不同工作表或工作簿中的单元格引用不同工作表或工作簿中的单元格 如果要引用同一工作簿中不同工作表中的单元格，可在公式中输入“工作表名称！单元格地址工作表名称！单元格地址”，例如，S

7、heet2！C3，也可在输入公式时切换到相应工作表，然后单击所需单元格。引用Sheet1工作表中的B15单元格 如果要引用不同工作簿中的单元格，可直接在公式中输入“工作簿名称工作簿名称工作表名称！单元格地址工作表名称！单元格地址”，例如，Book2Sheet3!D2，也可以在输入公式时切换到相应工作簿，然后单击相应工作表中要引用的单元格。如果被引用的单元格数据发生改变，那么引用了该单元格的工作簿再次打开时，Excel会打开一提示对话框，提示引用的数据发生改变，单击“更新”按钮，系统自动更新公式的计算结果。审核公式审核公式 在使用公式的过程中，有时可能会因人为疏忽，或是表达式的设置错误，导致计算

8、结果发生错误。Excel提供的审核功能可以帮助用户找出并修正公式中的错误。公式错误代码公式错误代码 n“#”：输入到单元格中的数值或公式计算结果太长，单元格容纳不下。增加单元格宽度可以解决这个问题。另外，日期运算结果为负值也会出现这种情况，此时可以改变单元格的格式，比如改为文本格式。n“#DIV/0!”：除数引用了零值单元格或空单元格。n“#N/A”：公式中没有可用数值，或缺少函数参数。n“#NAME?”：公式中引用了无法识别的名称，或删除了公式正在使用的名称。例如，函数的名称拼写错误，使用了没有被定义的区域或单元格名称，引用文本时没有加引号等。n“#NULL!”：使用了不正确的区域运算符或引

9、用的单元格区域的交集为空。例如，输入公式“=A1:B4 C1:D4”，因为这两个单元格区域交集为空，所以回车后返回值为“#NULL!”。n“#NUM!”：公式产生的结果数字太大或太小，Excel无法表示出来，例如，输入公式“=10309”，由于运算结果太大，公式返回错误；或在需要数字参数的函数中使用了无法接受的参数，例如，在输入开平方的公式(SQRT)时，引用了负值的单元格或直接使用了负值。n“#RFF”：公式引用的单元格被删除，并且系统无法自动调整，或链接的数据不可用。n“#VALUE”：当公式需要数字或逻辑值时，却输入了文本；为需要单个值（而不是区域）的运算符或函数提供了区域引用；输入数组

10、公式后，没有按【CtrlShiftEnter】组合键确认。使用使用“公式审核公式审核”工具栏工具栏 使用“公式审核”工具栏可以非常方便地对单元格中的公式进行错误检查。选择“视图”“工具栏”“公式审核”菜单，显示“公式审核”工具栏。显示工作表中出错的单元格内容及错误产生原因 追踪出错单元格所引用的单元格并进行修改 追踪从属单元格 追踪引用单元格 使用函数使用函数 Excel 2003提供了大量的内置函数，利用这些函数可以很容易地完成各种复杂的数据处理，如数值统计、逻辑判断等。10.4.1 函数的分类函数的分类 Excel中函数按其功能可分为以下几类。n 财务函数：财务函数：可以进行常见的财务计算

11、和财务分析，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值等。n 日期和时间函数：日期和时间函数：可以在公式中分析和处理日期值和时间值。n 数学和三角函数：数学和三角函数：进行各种数学计算。统计函数：统计函数：对工作表中的数据进行统计、分析。n 查找和引用函数：查找和引用函数：查找特定的数据或引用公式中的特定信息。n 数据库函数：数据库函数：对数据表中的数据进行分类、查找和计算等。n 文本函数：文本函数：用于处理公式中的文本字符串。n 逻辑函数：逻辑函数：可以进行真假值判断，或者进行复合检验。n 信息函数：信息函数：判定单元格或公式中的数据类型。了解函数格式了解函数格式 函数是

12、预先定义好的表达式，它必须包含在公式中。每个函数都由函数名和参数组成，基本形式为：=函数名（参数1，参数2，），如下所示。一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名后是用圆括号括起来的参数，各参数之间用逗号分隔。参数可以是数字、文本、数组和单元格引用，也可以是常量、公式或其他函数。使用函数的方法使用函数的方法 Excel提供了多种输入函数的方法，用户可以在单元格中直接输入函数，也可以使用函数向导输入函数。1直接输入函数直接输入函数 如果用户对函数的使用非常熟悉，可直接在单元格或编辑栏中输入。方法是：单击要输入函数的单元格，然后依次输入等号、函数名、左括号、具体参数和右括号，最

13、后单击编辑栏中的“输入”按钮 或按【Enter】键，此时在输入函数的单元格中将显示运算结果。2使用函数向导输入函数使用函数向导输入函数 如果对Excel函数不是很熟悉，可以用Excel函数向导来输入函数进行计算。在工作表中选择要进行计算的单元格区域松开鼠标左键，自动返回“函数参数”对话框 常用函数介绍常用函数介绍 1COUNT函数函数 COUNT函数的功能是统计参数列表中含有数值数据的单元格个数。其语法格式是COUNT(value1,value2,)。其中参数“value1,value2,”为包含或引用各种类型数据的参数（1到30个），但只有含数值的单元格才被统计。利用COUNT函数计算考试人

14、数 2COUNTIF函数函数 COUNTIF函数的功能是统计某个单元格区域中符合指定条件的单元格数目。其语法格式为COUNTIF(range,criteria)。其中“range”为要统计单元格个数的单元格区域；“criteria”为指定的条件表达式。利用COUNTIF函数统计600分以的人数 3MAX和和MIN函函数数 这两个函数的功能分别是返回参数列表中的最大值和最小值。其语法格式是MAX(number1,number2,)和MIN(number1,number2,)。其中参数“number1,number2,”可以是数字、空白单元格、逻辑值或数字的文本表达式（1到30个）。如果没有有效参

15、数，MAX函数和MIN函数的返回值都为“0”。利用MAX函数求总分最高分 4RANK函数函数 RANK函数的功能是返回一个数字在数字列表中的排位。其语法格式为RANK(number,ref,order)。其中“number”为需要排序的数值；“ref”为要排序的单元格区域；“order”指明排序方式，如果为“0”或者缺省，则按降序排名，即数值越大，排名结果数值越小；如果不为“0”值，则按升序排名，即数值越大，排名结果数值越大。5IF函数函数 IF函数的功能是执行真假值判断，根据逻辑计算的真假值返回不同结果。其语法格式为：IF（logical_test,value_if_true,value_i

16、f_false），其中“logical_test”表示要选取的条件；“value_if_true”表示条件为真时返回的值；“value_if_false”表示条件为假时返回的值。按F4键将其转换为绝对引用 利用RANK函数将成绩按总分大小进行排名 假设将平均分划分为2个等级，大于等于100的为“A”，否则为“B”利用IF函数按平均分划分等级 6TODAY函数函数 TODAY函数返回当前日期的序列号。该函数不需要参数，输入公式“=TODAY()”，确认后即显示系统的当前日期。如果系统日期发生了改变，按【F9】键即可更新数据。7PMT函数函数 PMT函数即年金函数。它的功能是基于固定利率及等额分期

17、付款方式，返回贷款的每期付款额。其语法格式是：PMT(Rate,Nper,Pv,Fv,Type)，其中Rate为贷款利率；Nper为该项贷款的付款总期数；Pv为现值，或一系列未来付款的当前值的累积和，也称为本金；Fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略Fv，则假设其值为零，也就是一笔贷款的未来值为零；Type为数字0或1，用以指定各期的付款时间是在期初还是期末。1代表期初，不输入或输入0代表期末。利用PMT函数计算出月还款额 8数学与三角函数数学与三角函数 n SIN函数：函数：返回给定角度的正弦值。其语法格式为SIN(number)，其中“number”为需要求正弦的角度

18、，以弧度表示。如果参数的单位是度，需将其乘以PI()/180将其转换为弧度或使用RADIANS函数将其转换为弧度。例如，计算30度的正弦值，可输入公式“=SIN(30*PI()/180)”或“=SIN(RADIANS(30)。n COS函数：函数：返回给定角度的余弦值。其语法格式为COS(number)，其中“number”为需要求余弦的角度，以弧度表示。如果参数的单位是度，需将其乘以PI()/180将其转换为弧度或使用RADIANS函数将其转换成弧度。n ASIN函数：函数：返回参数的反正弦值。其语法格式为ASIN(number)，其中“number”为角度的正弦值，必须介于-1到1之间。返

19、回的角度值将以弧度表示，若要用度表示，需将结果再乘以180/PI()或用DEGREES函数表示。例如，若要以弧度表示-0.5的反正弦值则为“=ASIN(-0.5)”。若要以度表示-0.5的反正弦值则为“=ASIN(-0.5)*180/PI()”或“=DEGREES(ASIN(-0.5)”。n ACOS函数：函数：返回数字的反余弦值。其语法格式为ACOS(number)，其中“number”为角度的余弦值，必须介于-1到1之间。如果要用度表示反余弦值，需将结果再乘以180/PI()或用DEGREES函数表示。n LN、LOG和和LOG10函数：函数：其中LN函数返回一个数的自然对数；LOG函数按

20、所指定的底数，返回一个数的对数；LOG10函数返回以10为底的对数。其其语法格式分别为LN(number)、LOG(number,base)和LOG10(number)。n POWER函数：函数：返回给定数字的乘幂。其语法格式为POWER（number,power），其中“number”为底数；“power”为指数。综合实例综合实例计算计算“图书销售统计表图书销售统计表”中的数中的数据据 本例通过计算“图书销售统计表”中的数据，来练习公式和函数的使用方法。首先利用自动求和按钮计算每个销售员的总销量和图书总销量，然后利用函数计算销量的最大和最小值，系列图书的平均销量和销量排行，最后统计各系列图书的发奖人数。制作思路制作思路操作步骤操作步骤具体操作步骤参见视频10-1。小结小结 学完本内容，应重点掌握以下知识。掌握在单元格中输入公式及利用填充柄复制公式的方法。掌握相对引用、绝对引用和混合引用的概念，以便在创建公式时正确设置单元格引用。掌握使用“公式审核”工具栏检验公式正确性的方法。掌握使用直接输入法和“插入函数”向导输入函数的方法。