公式与函数基础PPT讲稿.ppt

公式与函数基础第1页，共30页，编辑于2022年，星期五 2.1 公式 在Excel中，公式是对工作表中数据进行计算的等式，以等号（=）开始。例如，=10+6*2 是一个比较简单的公式。第2页，共30页，编辑于2022年，星期五 2 2.1.1.1 1 公式的组成公式的组成 公式通常由运算符和参与运算的操作数组成。公式通常由运算符和参与运算的操作数组成。操作数可以是常量、单元格引用、函数等。操作数可以是常量、单元格引用、函数等。常量：直接输入到公式中的数字或者文本，是不用计算常量：直接输入到公式中的数字或者文本，是不用计算的值。的值。单元格引用：引用某一单元格或单元格区域中的单元格引用：引用某一单元格或单元格区域中的数据，可以是当前工作表的单元格、同一工作簿中其数据，可以是当前工作表的单元格、同一工作簿中其他工作表中的单元格、其他工作簿中工作表中的单元他工作表中的单元格、其他工作簿中工作表中的单元格。格。工作表函数：包括函数及它们的参数。工作表函数：包括函数及它们的参数。运算符：是连接公式中的基本元素并完成特定计算的符运算符：是连接公式中的基本元素并完成特定计算的符号，例如号，例如“+”、“/”等。不同的运算符完成不同的运算。等。不同的运算符完成不同的运算。第3页，共30页，编辑于2022年，星期五 2 2.1.1.2 2 公式的输入和编辑公式的输入和编辑 ExcelExcel公式必须以等号（公式必须以等号（=）开始。）开始。1 1输入公式输入公式 在在ExcelExcel中输入公式时首先应选中要输入公式中输入公式时首先应选中要输入公式的单元格，然后在其中输入的单元格，然后在其中输入“=”，接着根据需，接着根据需要输入表达式，最后按回车键对输入的内容进要输入表达式，最后按回车键对输入的内容进行确定。行确定。例如在单元格例如在单元格D3D3中输入公式中输入公式“=10+5*2=10+5*2”，输入完成按下回车键，在该单元格中即可显示，输入完成按下回车键，在该单元格中即可显示该公式的运算结果。该公式的运算结果。第4页，共30页，编辑于2022年，星期五第5页，共30页，编辑于2022年，星期五2 2显示公式显示公式 输入公式后，系统将自动计算其结果并在单元格中输入公式后，系统将自动计算其结果并在单元格中显示出来。如果需要将公式显示在单元格中，可以通过显示出来。如果需要将公式显示在单元格中，可以通过下列方法下列方法:(1)(1)单击单击“公式公式”选项卡选项卡“公式审核公式审核”组中组中“显示公式显示公式”按钮。按钮。（2 2）单击）单击“OfficeOffice”按钮，单击按钮，单击“ExcelExcel选项选项”按按钮，在弹出的对话框中选择钮，在弹出的对话框中选择“高级高级”选项卡，在选项卡，在“此此工作表的显示选项工作表的显示选项”栏中启用栏中启用“在单元格中显示公式在单元格中显示公式而非计算结果而非计算结果”复选框。复选框。第6页，共30页，编辑于2022年，星期五3 3修改公式修改公式 在计算的过程中若发现某公式有错误，或者在计算的过程中若发现某公式有错误，或者发现情况发生改变，就需要对公式进行修改。具发现情况发生改变，就需要对公式进行修改。具体的操作步骤如下：体的操作步骤如下：（1 1）选定包含要修改公式的单元格，这时在编辑栏）选定包含要修改公式的单元格，这时在编辑栏中将显示出该公式；中将显示出该公式；（2 2）在编辑栏中对公式进行修改；）在编辑栏中对公式进行修改；（3 3）修改完毕按回车键即可。）修改完毕按回车键即可。修改公式时也可在含有公式的单元格上双击，修改公式时也可在含有公式的单元格上双击，然后直接在单元格区域中对公式进行修改。然后直接在单元格区域中对公式进行修改。第7页，共30页，编辑于2022年，星期五4 4移动公式移动公式 如果需要移动公式到其他的单元格中，那如果需要移动公式到其他的单元格中，那么具体的操作步骤如下。么具体的操作步骤如下。（1 1）选定包含公式的单元格，这时单元格的周）选定包含公式的单元格，这时单元格的周围会出现一个黑色的边框。围会出现一个黑色的边框。（2 2）要移动该单元格中的公式可将鼠标放在单元）要移动该单元格中的公式可将鼠标放在单元格边框上，当鼠标指针变为四向箭头时按下鼠标格边框上，当鼠标指针变为四向箭头时按下鼠标左键，拖动鼠标指针到目标单元格。左键，拖动鼠标指针到目标单元格。（3 3）释放鼠标左键，公式移动完毕。）释放鼠标左键，公式移动完毕。移动公式后，公式中的单元格引用不会发生移动公式后，公式中的单元格引用不会发生变化。变化。第8页，共30页，编辑于2022年，星期五5 5复制公式复制公式 在在Excel Excel 中，可以将已经编辑好的公式复制到其他中，可以将已经编辑好的公式复制到其他单元格中。复制公式时，单元格引用将会根据所用引用单元格中。复制公式时，单元格引用将会根据所用引用类型而变化。类型而变化。复制公式可使用复制公式可使用“开始开始”选项卡中的选项卡中的“复制复制”和和“粘贴粘贴”按钮来复制公式。按钮来复制公式。复制公式也可以使用复制公式也可以使用“填充柄填充柄”，相当于批量复，相当于批量复制公式，制公式，根据复制的需要，有时在复制内容时不需要复制单根据复制的需要，有时在复制内容时不需要复制单元格的格式，或只想复制公式。这时可以使用元格的格式，或只想复制公式。这时可以使用【选择性选择性粘贴粘贴】命令来完成复制操作。命令来完成复制操作。第9页，共30页，编辑于2022年，星期五 2.1.3 2.1.3 公式中的运算符公式中的运算符 Excel 2007Excel 2007公式中，运算符有算术运算符、比较运公式中，运算符有算术运算符、比较运算符、文本连接运算符和引用运算符四种类型。算符、文本连接运算符和引用运算符四种类型。1 1、算术运算符、算术运算符 算术运算符含义示例+加号2+1-减号2-1*乘号3*5/除号9/2%百分号50%乘幂号32第10页，共30页，编辑于2022年，星期五 2.1.3 2.1.3 公式中的运算符公式中的运算符 2 2、比较运算符、比较运算符 该类运算符能够比较两个或者多个数字、文本串、该类运算符能够比较两个或者多个数字、文本串、单元格内容、函数结果的大小关系，比较的结果为逻单元格内容、函数结果的大小关系，比较的结果为逻辑值：辑值：TRUETRUE或者或者FALSEFALSE。比较运算符含义示例=等于A1=B1大于A1B1小于A1=大于等于A1=B1=小于等于A1=B1不等于A1B1第11页，共30页，编辑于2022年，星期五 2.1.3 2.1.3 公式中的运算符公式中的运算符 3 3、文本连接运算符、文本连接运算符 文本连接运算符用文本连接运算符用“&”表示，用于将两个文本连接起表示，用于将两个文本连接起来合并成一个文本。例如，公式来合并成一个文本。例如，公式“中国中国”&“北京北京”的结的结果就是果就是“中国北京中国北京”。第12页，共30页，编辑于2022年，星期五 2.1.3 2.1.3 公式中的运算符公式中的运算符 4 4、引用运算符、引用运算符 引用运算符可以把两个单元格或者区域结合起来引用运算符可以把两个单元格或者区域结合起来生成一个联合引用。生成一个联合引用。引用运算符含义示例:（冒号）区域运算符，生成对两个引用之间所有单元格的引用。A5:A8,（逗号）联合运算符，将多个引用合并为一个引用SUM(A5:A10,B5:B10)（引用A5:A10和B5:B10两个单元区域）（空格）交集运算符，产生对两个引用共有的单元格的引用SUM(A1:F1 B1:B3)(引用A1:F1和 B1:B3两个单元格区域相交的B1单元格)第13页，共30页，编辑于2022年，星期五 2.1.3 2.1.3 公式中的运算符公式中的运算符 如果在一个公式中包含了多个运算符，就要按照如果在一个公式中包含了多个运算符，就要按照一定的顺序进行计算。公式的计算顺序与运算符的优一定的顺序进行计算。公式的计算顺序与运算符的优先级有关。先级有关。运算符（优先级从高到低）说明：区域运算符，联合运算符空格交集运算符-负号%百分比乘幂*和/乘法和除法+和-加法和减法&文本连接符=，=，=，比较运算符第14页，共30页，编辑于2022年，星期五 2.1.2.1.4 4 单元格引用单元格引用 在使用公式和函数进行计算时，往往需要引用单元格中在使用公式和函数进行计算时，往往需要引用单元格中的数据。通过引用可以在公式中使用同一个工作表中不同部的数据。通过引用可以在公式中使用同一个工作表中不同部分的数据，或者在多个公式中使用同一单元格或区域的数值，分的数据，或者在多个公式中使用同一单元格或区域的数值，还可以引用相同工作簿中不同工作表上的单元格和其他工作还可以引用相同工作簿中不同工作表上的单元格和其他工作簿中的单元格数据。簿中的单元格数据。第15页，共30页，编辑于2022年，星期五 1 1A1A1引用样式和引用样式和R1C1R1C1引用样式引用样式 默认情况下默认情况下ExcelExcel使用使用A1A1引用样式，就是采用列的字母标识和行的数字标引用样式，就是采用列的字母标识和行的数字标识。若想引用单元格区域，则需要输入区域左上角的单元格引用标识，后面识。若想引用单元格区域，则需要输入区域左上角的单元格引用标识，后面跟一个冒号，接着输入区域右下角的单元格引用标识。例如，跟一个冒号，接着输入区域右下角的单元格引用标识。例如，B3:E6B3:E6引用的引用的是从是从B3B3到到E6E6所在区域的所有单元格。所在区域的所有单元格。在在ExcelExcel中有时也使用中有时也使用R1C1R1C1引用样式。引用样式。R1C1R1C1引用是使用引用是使用“R”“R”加行数字加行数字和和“C”“C”加列数字来确定单元格的位置。例如，加列数字来确定单元格的位置。例如，R3C5R3C5引用的是第引用的是第3 3行和第行和第5 5列交叉处的单元格；列交叉处的单元格；R2C5R2C5引用的是指针所在单元格的下面第引用的是指针所在单元格的下面第3 3行右面行右面第第6 6列交叉处的单元格；列交叉处的单元格；R3C4:R5C8R3C4:R5C8引用的是第引用的是第3 3行行4 4列到第列到第5 5行行8 8列之间的所列之间的所有单元格。有单元格。在在R1C1R1C1引用样式中，引用样式中，中表示的是数据的相对位置，即指针所在位置为当前中表示的是数据的相对位置，即指针所在位置为当前激活单元格位置，而引用的是移动括号内的数字后的单元格或者区域。当前激活单元格位置，而引用的是移动括号内的数字后的单元格或者区域。当前位置向下向右用正数表示，向上向左用负数表示。位置向下向右用正数表示，向上向左用负数表示。系统默认使用的是系统默认使用的是A1A1引用样式。因此在工作表中要使用引用样式。因此在工作表中要使用R1C1R1C1引用样引用样式则需要重新进行一些设置。式则需要重新进行一些设置。第16页，共30页，编辑于2022年，星期五 第17页，共30页，编辑于2022年，星期五 2 2相对引用相对引用 相对引用是指引用单元格的相对地址，即被相对引用是指引用单元格的相对地址，即被引用的单元格与公式所在的单元格之间的位置是引用的单元格与公式所在的单元格之间的位置是相对的。如果公式所在的单元格的位置发生了变相对的。如果公式所在的单元格的位置发生了变化，那么引用的单元格的位置也会相应地发生变化，那么引用的单元格的位置也会相应地发生变化。所以当带有相对引用的公式被复制到其他单化。所以当带有相对引用的公式被复制到其他单元格时，公式内引用的单元格将变成与目标单元元格时，公式内引用的单元格将变成与目标单元格相对位置上的单元格。格相对位置上的单元格。第18页，共30页，编辑于2022年，星期五 3 3绝对引用绝对引用 绝对引用是指被引用的单元格与公式所在的单元绝对引用是指被引用的单元格与公式所在的单元格之间的位置关系是绝对的，即不管公式被复制到什格之间的位置关系是绝对的，即不管公式被复制到什么位置，公式中所引用的还是原来单元格区域的数据。么位置，公式中所引用的还是原来单元格区域的数据。在某些操作中如果不希望调整引用位置，则可使用绝在某些操作中如果不希望调整引用位置，则可使用绝对引用。对引用。A1A1样式的单元格绝对引用是在单元格的行和列样式的单元格绝对引用是在单元格的行和列前加上前加上“$”“$”符号，例如符号，例如“$B$5”“$B$5”表示表示B B列列5 5行交叉处的行交叉处的单元格；可以通过按单元格；可以通过按“F4F4”键进行切换。键进行切换。R1C1R1C1样式样式中单元格的绝对引用是直接在中单元格的绝对引用是直接在“R”“R”的后面加行号，的后面加行号，在在“C”“C”的后面加列号，如的后面加列号，如R3C4R3C4则表示第则表示第3 3行第行第4 4列交叉列交叉处的单元格。处的单元格。第19页，共30页，编辑于2022年，星期五 4 4混合引用混合引用 混合引用就是在单元格引用中既有绝对引用又有相混合引用就是在单元格引用中既有绝对引用又有相对引用，例如对引用，例如“$A1”“$A1”、“A$1”“A$1”。列绝对、行相对混合引用采用列绝对、行相对混合引用采用“$A1”“$A1”、“$B1”“$B1”等形式。等形式。公式的计算结果不随公式所在单元格的列位置变化而改变，公式的计算结果不随公式所在单元格的列位置变化而改变，而随公式所在单元格的行位置变化而改变。而随公式所在单元格的行位置变化而改变。列相对、行绝对混合引用采用列相对、行绝对混合引用采用A$1A$1、B$1B$1等形式。公等形式。公式的计算结果不随公式所在单元格的行位置变化而改变，式的计算结果不随公式所在单元格的行位置变化而改变，而随公式所在单元格的列位置变化而改变。而随公式所在单元格的列位置变化而改变。在某些情况下，复制公式时只需改变行或者只需改变在某些情况下，复制公式时只需改变行或者只需改变列，这时就需要使用混合引用。列，这时就需要使用混合引用。第20页，共30页，编辑于2022年，星期五 5 5引用其他工作表或工作簿中的单元格引用其他工作表或工作簿中的单元格 在单元格引用中，不仅仅是引用当前工作表中的单在单元格引用中，不仅仅是引用当前工作表中的单元格，有时需要引用同一工作簿中其他工作表中的单元元格，有时需要引用同一工作簿中其他工作表中的单元格。这就需要使用叹号格。这就需要使用叹号“！”来实现，格式为：来实现，格式为：=工作表名称！单元格地址工作表名称！单元格地址 即在单元格地址前加上工作表名称和叹号。即在单元格地址前加上工作表名称和叹号。如果要引用不同工作簿中某一工作表的单元格，则需要如果要引用不同工作簿中某一工作表的单元格，则需要使用如下格式：使用如下格式：=工作簿名称工作簿名称 工作表名称！单元格地址工作表名称！单元格地址即用即用将工作簿名称括起来。将工作簿名称括起来。第21页，共30页，编辑于2022年，星期五 2.1.5 2.1.5 公式的错误和审核公式的错误和审核 1 1、Excel Excel常见错误常见错误 错误值产生的原因#!公式计算的结果太长，单元格容纳不下#DIV/O除数为零。当公式被空单元格除时也会出现这个错误#N/A公式中无可用的数值或者缺少函数参数#NAME?公式中引用了一个无法识别的名称。当删除一个公式正在使用的名称或者在使用文本时有不相称的引用，也会返回这种错误#NULL!使用了不正确的区域运算或者不正确的单元格引用#NUM!在需要数字参数的函数中使用了不能接受的参数，或者公式的计算结果的数字太大 或太小而无法表示#RFF!公式中引用了一个无效的单元格。如果单元格从工作表中被删除就会出现这个错误#VALUE!公式中含有一个错误类型的参数或者操作数第22页，共30页，编辑于2022年，星期五 2.1.5 2.1.5 公式的错误和审核公式的错误和审核 2 2、使用错误检查使用错误检查 ExcelExcel使用特定的规则来检查公式中的错误。这些规则虽然不能使用特定的规则来检查公式中的错误。这些规则虽然不能保证工作表中没有错误，但对发现错误却非常有帮助。错误检查规则保证工作表中没有错误，但对发现错误却非常有帮助。错误检查规则可以单独打开或关闭。可以单独打开或关闭。在在ExcelExcel中可以使用两种方法检查错误：一是像使用拼写检查器中可以使用两种方法检查错误：一是像使用拼写检查器那样一次检查一个错误，二是检查当前工作表中的所有错误。一旦那样一次检查一个错误，二是检查当前工作表中的所有错误。一旦发现错误，在单元格的左上角会显示一个三角。这两种方法检查到发现错误，在单元格的左上角会显示一个三角。这两种方法检查到错误后都会显示相同的选项。错误后都会显示相同的选项。当单击包含错误的单元格时，在单元格旁边就会出现一个错误提当单击包含错误的单元格时，在单元格旁边就会出现一个错误提示按钮示按钮 ，单击该按钮会打开一个菜单，显示错误检查的相关命令。使用，单击该按钮会打开一个菜单，显示错误检查的相关命令。使用这些命令可以查看错误的信息、相关帮助、显示计算步骤、忽略错误、转这些命令可以查看错误的信息、相关帮助、显示计算步骤、忽略错误、转到编辑栏中编辑公式，以及设置错误检查选项等。到编辑栏中编辑公式，以及设置错误检查选项等。第23页，共30页，编辑于2022年，星期五 2.1.5 2.1.5 公式的错误和审核公式的错误和审核 3 3处理循环引用处理循环引用 如果公式引用自己所在的单元格，则不论是直接引用还如果公式引用自己所在的单元格，则不论是直接引用还是间接引用，都称为是间接引用，都称为“循环引用循环引用”。例如在。例如在A1A1单元格中输入单元格中输入公式公式“=A1+2=A1+2”，这就是一个循环引用。则完成公式输入后，这就是一个循环引用。则完成公式输入后按回车键确认时，按回车键确认时，ExcelExcel会弹出一个警告对话框，提示产会弹出一个警告对话框，提示产生了循环引用。生了循环引用。第24页，共30页，编辑于2022年，星期五 2.1.5 2.1.5 公式的错误和审核公式的错误和审核 4 4审核公式审核公式 使用使用ExcelExcel中提供的多种公式审核功能，可以追踪引用中提供的多种公式审核功能，可以追踪引用单元格和从属单元格，可以使用监视窗口监视公式及其结单元格和从属单元格，可以使用监视窗口监视公式及其结果。果。（1 1）追踪引用单元格）追踪引用单元格 （2 2）追踪从属单元格）追踪从属单元格 （3 3）使用监视窗口）使用监视窗口第25页，共30页，编辑于2022年，星期五 2.1.6 2.1.6 使用数组公式使用数组公式 前面介绍的公式都是执行单个计算并且返回单个结果的情况，而数前面介绍的公式都是执行单个计算并且返回单个结果的情况，而数组公式可以执行多个计算并且返回多个结果。组公式可以执行多个计算并且返回多个结果。数组公式作用于两组或多数组公式作用于两组或多组被称为数组参数的数值，每组数值参数必须具有相同数目的行和列。组被称为数组参数的数值，每组数值参数必须具有相同数目的行和列。(ctrl+shift+enter)(ctrl+shift+enter)第26页，共30页，编辑于2022年，星期五 2.2 2.2 函数函数 函数是函数是ExcelExcel提供的内部工具，是一些预定义的公式。提供的内部工具，是一些预定义的公式。2.2.1 2.2.1 函数简介函数简介 函数的一般结构是：函数的一般结构是：函数名（参数函数名（参数1 1，参数，参数2 2，）其中，函数名是函数的名称，每个函数名是惟一标识一个函其中，函数名是函数的名称，每个函数名是惟一标识一个函数的。参数就是函数的输入值，用来计算所需的数据。参数可以数的。参数就是函数的输入值，用来计算所需的数据。参数可以是常量、单元格引用、数组、逻辑值或者是其他的函数。是常量、单元格引用、数组、逻辑值或者是其他的函数。按照参数的数量和使用区分，函数可以分为无参数型和有参数按照参数的数量和使用区分，函数可以分为无参数型和有参数型。无参数型如返回当前日期和时间的型。无参数型如返回当前日期和时间的NOWNOW（）函数，不需要参数。（）函数，不需要参数。大多数函数至少有一个参数，有的甚至有八九个之多。这些参数又可大多数函数至少有一个参数，有的甚至有八九个之多。这些参数又可以分为必要参数和可选参数。以分为必要参数和可选参数。函数要求的参数必须出现在括号内，否则会产生错误信息。可函数要求的参数必须出现在括号内，否则会产生错误信息。可选参数则依据公式的需要而定。选参数则依据公式的需要而定。第27页，共30页，编辑于2022年，星期五 2.2.2 2.2.2 函数的输入函数的输入 要在工作表中使用函数必须先输入函数。函要在工作表中使用函数必须先输入函数。函数的输入有两种常用的方法。数的输入有两种常用的方法。1 1、手工输入、手工输入 2 2、使用函数向导输入、使用函数向导输入第28页，共30页，编辑于2022年，星期五 2.3 2.3 使用名称使用名称 1 1、定义名称常量、定义名称常量 在引用一个常量时，通常会想到将这个常量输入到在引用一个常量时，通常会想到将这个常量输入到某一单元格中，然后在公式中引用这个单元格。但在某一单元格中，然后在公式中引用这个单元格。但在ExcelExcel中可以给一个并没有出现在单元格中的条目命名。中可以给一个并没有出现在单元格中的条目命名。例如，如果在工作表的公式中要用到一个贷款利率，例如，如果在工作表的公式中要用到一个贷款利率，又不希望使用单元格引用的方式来使用贷款利率的具体数又不希望使用单元格引用的方式来使用贷款利率的具体数值，则可以定义一个常量名称。值，则可以定义一个常量名称。第29页，共30页，编辑于2022年，星期五 2.3 2.3 使用名称使用名称 2 2、定义公式名称、定义公式名称 在在ExcelExcel中可以为公式创建名称，其方法与为常量定中可以为公式创建名称，其方法与为常量定义名称类似。所命名的公式也不会出现在工作表中，但义名称类似。所命名的公式也不会出现在工作表中，但可以使用可以使用“名称管理器名称管理器”查看与编辑。查看与编辑。例如，可以创建一个名为例如，可以创建一个名为“总销售额总销售额”的命名公式，的命名公式，通过每月的销售额来计算一年总的预销售额，而每月通过每月的销售额来计算一年总的预销售额，而每月的销售额被输入到的销售额被输入到A2A2至至A13A13单元格区域中，则名称单元格区域中，则名称“总销总销售额售额”可以使用公式可以使用公式“=SUM(A2:A13)=SUM(A2:A13)”。第30页，共30页，编辑于2022年，星期五