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

《EXCEL函数与公式专题讲座课件.ppt》由会员分享，可在线阅读，更多相关《EXCEL函数与公式专题讲座课件.ppt（42页珍藏版）》请在三一办公上搜索。

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

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

3、”、“（不等于）”均为比较运算符。,认识公式的运算符 运算符用来连接要运算的,文本运算符：使用文本运算符“”（与号）可将两个或多个文本值串起来产生一个连续的文本值。,引用运算符：使用引用运算符可以将单元格区域合并计算。,文本运算符：使用文本运算符“”（与号）可将两个,运算符的优先级,如果在一个公式中出现多种运算符，Excel会按运算符的优先级逐个进行计算。,若要更改运算的顺序，可以将公式中要先进行运算的部分用括号括起来。,运算符的优先级 如果在一个公式中,输入和编辑公式,在Excel中输入公式的方法与输入文本类似。,单击要输入公式的单元格，然后输入公式，按【Enter】键确认,编辑公式与编辑数

4、据的方法相同。双击含有公式的单元格进入编辑状态，将光标定位在需要修改的位置，按【Backspace】键删除错误内容，再输入正确内容。然后按【Enter】键即可。也可以在选中单元格后在编辑栏中进行修改操作。,输入和编辑公式 在Excel中输入公式的方法与输,默认情况下，单元格中只显示公式计算结果，用户只能在编辑栏中查看公式。为了方便查看，可设置在单元格中显示公式。,默认情况下，单元格中只显示公式计算结果，用户,复制公式,复制公式可以快速为其他单元格输入公式，从而提高工作效率。其操作方法与复制数据的方法相似。,复制含有公式的单元格,利用“选择性粘贴”对话框复制公式,复制公式 复制公式可以快速为其他

5、单元,利用填充柄复制公式,利用填充柄复制公式,单元格的引用,通过单元格的引用，可以在一个公式中使用工作表不同部分的数据，或者在多个公式中使用一个单元格中的数据，还可以引用同一个工作簿中不同工作表中的单元格，甚至还可以引用不同工作簿中的数据。,引用单元格和单元格区域,在Excel中，每个单元格都有一个独一无二的地址，该地址由单元格所在的列标和行号组成，用户可以通过该地址引用该单元格,单元格的引用 通过单元格的引用，可以在一个公式中使用工作,相对引用、绝对引用和混合引用,1相对引用,相对引用指的是单元格的相对地址，其引用形式为直接用列标和行号表示单元格。当将公式复制或填充到其他单元格时，系统会根据

6、当前单元格的地址自动改变公式中引用的单元格的行号和列标。,复制公式时，相对引用改变,相对引用、绝对引用和混合引用 1相对引用,2绝对引用,绝对引用是指总是引用指定位置的单元格。如果公式所在的单元格位置发生改变，使用绝对引用的单元格不变。使用绝对引用时，在列标和行号前加一个“$”符号。,复制公式时，绝对引用不变,2绝对引用 绝对引用是指总是引用指定位置的单,3混合引用,混合引用是指对单元格的引用既存在相对引用，又存在绝对引用。混合引用的方式有两种：一种为列相对引用，行绝对引用，如C$4，此时公式中引用的行号是不变的，而列号会随公式所在位置的变化而变化；另一种为列绝对引用，行相对引用，如$C4，此

7、时公式中引用的列号不变，而行号会随公式所在位置的变化而变化。,编辑公式时，输入单元格地址后，按【F4】键可在绝对引用、相对引用和混合引用之间切换。,3混合引用 混合引用是指对单元格的引用既存在,引用不同工作表或工作簿中的单元格,如果要引用同一工作簿中不同工作表中的单元格，可在公式中输入“工作表名称！单元格地址”，例如，Sheet2！C3，也可在输入公式时切换到相应工作表，然后单击所需单元格。,引用Sheet1工作表中的B15单元格,引用不同工作表或工作簿中的单元格 如果要,如果要引用不同工作簿中的单元格，可直接在公式中输入“工作簿名称工作表名称！单元格地址”，例如，Book2Sheet3!D2

8、，也可以在输入公式时切换到相应工作簿，然后单击相应工作表中要引用的单元格。,如果被引用的单元格数据发生改变，那么引用了该单元格的工作簿再次打开时，Excel会打开一提示对话框，提示引用的数据发生改变，单击“更新”按钮，系统自动更新公式的计算结果。,如果要引用不同工作簿中的单元格，可直接在公式,审核公式,在使用公式的过程中，有时可能会因人为疏忽，或是表达式的设置错误，导致计算结果发生错误。Excel提供的审核功能可以帮助用户找出并修正公式中的错误。,公式错误代码,“#”：输入到单元格中的数值或公式计算结果太长，单元格容纳不下。增加单元格宽度可以解决这个问题。另外，日期运算结果为负值也会出现这种情

9、况，此时可以改变单元格的格式，比如改为文本格式。 “#DIV/0!”：除数引用了零值单元格或空单元格。 “#N/A”：公式中没有可用数值，或缺少函数参数。,审核公式 在使用公式的过程中，有时可能会因人为,“#NAME?”：公式中引用了无法识别的名称，或删除了公式正在使用的名称。例如，函数的名称拼写错误，使用了没有被定义的区域或单元格名称，引用文本时没有加引号等。 “#NULL!”：使用了不正确的区域运算符或引用的单元格区域的交集为空。例如，输入公式“=A1:B4 C1:D4”，因为这两个单元格区域交集为空，所以回车后返回值为“#NULL!”。 “#NUM!”：公式产生的结果数字太大或太小，Ex

10、cel无法表示出来，例如，输入公式“=10309”，由于运算结果太大，公式返回错误；或在需要数字参数的函数中使用了无法接受的参数，例如，在输入开平方的公式(SQRT)时，引用了负值的单元格或直接使用了负值。 “#RFF”：公式引用的单元格被删除，并且系统无法自动调整，或链接的数据不可用。 “#VALUE”：当公式需要数字或逻辑值时，却输入了文本；为需要单个值（而不是区域）的运算符或函数提供了区域引用；输入数组公式后，没有按【CtrlShiftEnter】组合键确认。,“#NAME?”：公式中引用了无法识别的名称，或删除了公式,使用“公式审核”工具栏,使用“公式审核”工具栏可以非常方便地对单元格

11、中的公式进行错误检查。选择“视图”“工具栏”“公式审核”菜单，显示“公式审核”工具栏。,使用“公式审核”工具栏 使用“公式审核”工,显示工作表中出错的单元格内容及错误产生原因,追踪出错单元格所引用的单元格并进行修改,显示工作表中出错的单元格内容及错误产生原因 追踪出,追踪从属单元格,追踪引用单元格,追踪从属单元格 追踪引用单元格,使用函数,Excel 2003提供了大量的内置函数，利用这些函数可以很容易地完成各种复杂的数据处理，如数值统计、逻辑判断等。,10.4.1 函数的分类,Excel中函数按其功能可分为以下几类。 财务函数：可以进行常见的财务计算和财务分析，如确定贷款的支付额、投资的未来

12、值或净现值，以及债券或息票的价值等。 日期和时间函数：可以在公式中分析和处理日期值和时间值。 数学和三角函数：进行各种数学计算。,使用函数 Excel 2003提供了大量,统计函数：对工作表中的数据进行统计、分析。 查找和引用函数：查找特定的数据或引用公式中的特定信息。 数据库函数：对数据表中的数据进行分类、查找和计算等。 文本函数：用于处理公式中的文本字符串。 逻辑函数：可以进行真假值判断，或者进行复合检验。 信息函数：判定单元格或公式中的数据类型。,统计函数：对工作表中的数据进行统计、分析。,了解函数格式,函数是预先定义好的表达式，它必须包含在公式中。每个函数都由函数名和参数组成，基本形式

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

14、输入函数的单元格中将显示运算结果。,使用函数的方法 Excel提供了多种输,2使用函数向导输入函数,如果对Excel函数不是很熟悉，可以用Excel函数向导来输入函数进行计算。,2使用函数向导输入函数 如果对Excel函数,在工作表中选择要进行计算的单元格区域,松开鼠标左键，自动返回“函数参数”对话框,在工作表中选择要进行计算的单元格区域松开鼠标左键，自动,常用函数介绍,1COUNT函数,COUNT函数的功能是统计参数列表中含有数值数据的单元格个数。其语法格式是COUNT(value1,value2,)。其中参数“value1,value2,”为包含或引用各种类型数据的参数（1到30个），但只

15、有含数值的单元格才被统计。,利用COUNT函数计算考试人数,常用函数介绍 1COUNT函数 CO,2COUNTIF函数,COUNTIF函数的功能是统计某个单元格区域中符合指定条件的单元格数目。其语法格式为COUNTIF(range,criteria)。其中“range”为要统计单元格个数的单元格区域；“criteria”为指定的条件表达式。,2COUNTIF函数 COUNTIF函数的功,利用COUNTIF函数统计600分以的人数,利用COUNTIF函数统计600分以的人数,3MAX和MIN函数,这两个函数的功能分别是返回参数列表中的最大值和最小值。其语法格式是MAX(number1,numbe

16、r2,)和MIN(number1,number2,)。其中参数“number1,number2,”可以是数字、空白单元格、逻辑值或数字的文本表达式（1到30个）。如果没有有效参数，MAX函数和MIN函数的返回值都为“0”。,利用MAX函数求总分最高分,3MAX和MIN函数 这两个函数的功能分别是,4RANK函数,RANK函数的功能是返回一个数字在数字列表中的排位。其语法格式为RANK(number,ref,order)。其中“number”为需要排序的数值；“ref”为要排序的单元格区域；“order”指明排序方式，如果为“0”或者缺省，则按降序排名，即数值越大，排名结果数值越小；如果不为“0

17、”值，则按升序排名，即数值越大，排名结果数值越大。,4RANK函数 RANK函数的功能是返回一个,5IF函数,IF函数的功能是执行真假值判断，根据逻辑计算的真假值返回不同结果。其语法格式为：IF（logical_test,value_if_true,value_if_false），其中“logical_test”表示要选取的条件；“value_if_true”表示条件为真时返回的值；“value_if_false”表示条件为假时返回的值。,5IF函数 IF函数的功能是执行真假值判断，,按F4键将其转换为绝对引用,利用RANK函数将成绩按总分大小进行排名,按F4键将其转换为绝对引用 利用RANK

18、函数将成绩按总分,假设将平均分划分为2个等级，大于等于100的为“A”，否则为“B”,利用IF函数按平均分划分等级,假设将平均分划分为2个等级，大于等于100的为“A”，否则为,6TODAY函数,TODAY函数返回当前日期的序列号。该函数不需要参数，输入公式“=TODAY()”，确认后即显示系统的当前日期。如果系统日期发生了改变，按【F9】键即可更新数据。,6TODAY函数 TODAY函数返回当前日期,7PMT函数,PMT函数即年金函数。它的功能是基于固定利率及等额分期付款方式，返回贷款的每期付款额。其语法格式是：PMT(Rate, Nper, Pv, Fv, Type)，其中Rate为贷款利

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

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

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

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