公式的应用解析课件.ppt

1、第第3 3章章 公式的应用公式的应用o3.1 3.1 公式的创建公式的创建o3.2 3.2 公式的编辑公式的编辑o3.3 3.3 公式的引用公式的引用o3.4 3.4 使用名称使用名称o3.5 3.5 数组公式及其应用数组公式及其应用6:206:201 13.1 3.1 公式的创建公式的创建o公式是对工作表中的数值进行计算的等式。公式是对工作表中的数值进行计算的等式。o公式要以等号公式要以等号“=”开始，用于表示其后的字符为公开始，用于表示其后的字符为公式。紧随其后的是需要运算的元素，各元素之间用式。紧随其后的是需要运算的元素，各元素之间用运算符分隔。运算符分隔。6:206:202 23.1

2、3.1 公式的创建公式的创建3.1.1 3.1.1 公式的组成公式的组成o等号等号“=”o变量变量o常量常量o运算符运算符6:206:203 33.1 3.1 公式的创建公式的创建3.1.2 3.1.2 公式的运算符公式的运算符o算术运算符：用于基本数学计算。算术运算符：用于基本数学计算。+、-、*、/、%、o比较运算符：用于比较两个数值的大小，公式比较运算符：用于比较两个数值的大小，公式返回值返回值是一个逻辑值是一个逻辑值。=、=、=、。o文本运算符：文本运算符：&将多个文本连接成组合文本（制作九将多个文本连接成组合文本（制作九九乘法表）。九乘法表）。o引用运算符：连续区域运算符（：）、联合

3、操作运算引用运算符：连续区域运算符（：）、联合操作运算符（，）、取交集运算符（空格）。符（，）、取交集运算符（空格）。6:206:204 43.1.2 3.1.2 公式的运算符公式的运算符o1.冒号（冒号（:）：区域运算符，两个单元格之间用英）：区域运算符，两个单元格之间用英文冒号连接，表示引用这两个单元格之间所有单元文冒号连接，表示引用这两个单元格之间所有单元格的区域。例如区域格的区域。例如区域A1:D3，表示以，表示以A1单元格和单元格和D3单元格为对角的区域，单元格为对角的区域，如公式：如公式：=SUM(A1:D3)o另外，另外，“A:A”表示引用表示引用A列列，“A:C”表示引用表示引

4、用A列至列至C列，列，“1:1”表示引用第一行表示引用第一行，“1:3”表示表示引用第引用第1行至第行至第3行，其余依此类推。行，其余依此类推。6:206:205 53.1.2 3.1.2 公式的运算符公式的运算符o2.空格（空格（）：交集运算符，）：交集运算符，生成对两个引用中共有的单元格生成对两个引用中共有的单元格的引用。的引用。例如公式：例如公式：=SUM(A1:D3 B1:C5)计算计算A1:D3和和B1:C5 两个区域的交叉区域，即两个区域的交叉区域，即B3:C3区域所有单元格数值的和。区域所有单元格数值的和。6:206:206 63.1.2 3.1.2 公式的运算符公式的运算符o3

5、.逗号（逗号（,）：）：联合运算符，该运算符将多个引用合并为一个联合运算符，该运算符将多个引用合并为一个引用。引用。例如公式例如公式:=SUM(A1:D3,B1:C5)计算计算A1:D3和和 B1:C5两个区域中所有单元格数值的和。两个区域中所有单元格数值的和。6:206:207 73.1 3.1 公式的创建公式的创建3.1.3 3.1.3 公式的创建公式的创建o直接在单元格输入直接在单元格输入o直接在编辑栏中输入直接在编辑栏中输入o在输入公式时，不能输入空格，否则运算时会出现在输入公式时，不能输入空格，否则运算时会出现错误。错误。6:206:208 83.2 3.2 公式的编辑公式的编辑3.

6、2.1 3.2.1 公式的修改公式的修改o在单元格修改在单元格修改n双击要修改公式的单元格双击要修改公式的单元格修改公式修改公式回车回车o在编辑栏中修改在编辑栏中修改6:206:209 93.2 3.2 公式的编辑公式的编辑3.2.2 3.2.2 公式的移动和复制公式的移动和复制公式的移动公式的移动o选定要移动公式的单元格选定要移动公式的单元格“剪切剪切”命令命令“粘贴粘贴”命令命令o公式移动后，单元格的引用不变。即：计算的结果不公式移动后，单元格的引用不变。即：计算的结果不变。变。6:206:2010103.2 3.2 公式的编辑公式的编辑3.2.2 3.2.2 公式的移动和复制公式的移动和

7、复制公式的移动公式的移动移动前的公式引用情况移动前的公式引用情况移动后的公式引用情况移动后的公式引用情况6:206:2011113.2 3.2 公式的编辑公式的编辑3.2.2 3.2.2 公式的移动和复制公式的移动和复制o公式的复制公式的复制o选定要移动公式的单元格选定要移动公式的单元格“复制复制”命令命令“选择选择性粘贴性粘贴”命令命令“公式公式”o公式复制后，单元格的引用发生变化。即：计算的公式复制后，单元格的引用发生变化。即：计算的结果一般会发生变化。结果一般会发生变化。6:206:2012123.2 3.2 公式的编辑公式的编辑3.2.2 3.2.2 公式的移动和复制公式的移动和复制o

8、公式的复制公式的复制复制前的公式引用情况复制前的公式引用情况复制后的公式引用情况复制后的公式引用情况6:206:2013133.2 3.2 公式的编辑公式的编辑3.2.2 3.2.2 公式的移动和复制公式的移动和复制o公式的复制公式的复制只复制公式的计算结果只复制公式的计算结果o复制含有公式的单元格复制含有公式的单元格在目标单元格点击右键在目标单元格点击右键在菜单中选择在菜单中选择“选择型粘贴选择型粘贴”命令命令粘贴粘贴“数值数值”6:206:2014143.2 3.2 公式的编辑公式的编辑3.2.2 3.2.2 公式的移动和复制公式的移动和复制o公式的复制公式的复制只复制公式的计算结果只复制

9、公式的计算结果（1 1）（2 2）6:206:2015153.2 3.2 公式的编辑公式的编辑3.2.2 3.2.2 公式的移动和复制公式的移动和复制o公式的复制公式的复制只复制公式的计算结果只复制公式的计算结果选择性粘贴前选择性粘贴前选择性粘贴后选择性粘贴后6:206:2016163.3 3.3 公式的引用公式的引用o单元格引用：单元格的行、列标号单元格引用：单元格的行、列标号o在在ExcelExcel中的引用就是标识工作表中单元格或表元区中的引用就是标识工作表中单元格或表元区域，并指明公式中所使用的数据的位置。域，并指明公式中所使用的数据的位置。o通过通过引用引用，可以在公式中使用工作表中

10、不同部分的数，可以在公式中使用工作表中不同部分的数据，或者在多个公式中使用同一个单元格的数据。据，或者在多个公式中使用同一个单元格的数据。6:206:2017173.3 3.3 公式的引用公式的引用o3.3.1 3.3.1 公式的相对引用公式的相对引用o当把一个含有单元格地址的公式复制到一个新位置或当把一个含有单元格地址的公式复制到一个新位置或一个公式填充一个单元格区域时，公式中一个公式填充一个单元格区域时，公式中单元格的地单元格的地址址会随之改变。会随之改变。o公式所在单元格地址发生变化，则公式中引用的单元公式所在单元格地址发生变化，则公式中引用的单元格地址也随之发生变化。格地址也随之发生变

11、化。o相对引用在公式中的使用最多。相对引用在公式中的使用最多。6:206:2018183.3 3.3 公式的引用公式的引用o3.3.1 3.3.1 公式的相对引用公式的相对引用当某部门销售量变化时，当某部门销售量变化时，ExcelExcel会重新计算总量。会重新计算总量。6:206:2019193.3 3.3 公式的引用公式的引用o3.3.2 3.3.2 公式的绝对引用公式的绝对引用o将公式复制或填充到其他位置时，公式中将公式复制或填充到其他位置时，公式中单元格的地单元格的地址固定不变址固定不变，其中的数据还是原来单元格的数据。，其中的数据还是原来单元格的数据。o公式所在单元格地址发生变化，但

12、是公式中引用的单公式所在单元格地址发生变化，但是公式中引用的单元格地址固定不变。元格地址固定不变。o绝对引用要通过绝对引用要通过“冻结冻结”单元格地址单元格地址来实现。即：分来实现。即：分别在行标号和列标号的前面添加一个别在行标号和列标号的前面添加一个“”。6:206:2020203.3 3.3 公式的引用公式的引用o3.3.2 3.3.2 公式的绝对引用公式的绝对引用6:206:2021213.3 3.3 公式的引用公式的引用o3.3.2 3.3.2 公式的绝对引用公式的绝对引用6:206:2022223.3 3.3 公式的引用公式的引用o3.3.3 3.3.3 公式的混合引用公式的混合引用

13、o混合引用是介于相对引用和绝对引用之间的一种引用，混合引用是介于相对引用和绝对引用之间的一种引用，也就是说引用单元格的行和列之一，一个是相对的，也就是说引用单元格的行和列之一，一个是相对的，一个是绝对的。一个是绝对的。o混合引用有两种：一种是行绝对、列相对；如混合引用有两种：一种是行绝对、列相对；如E E4 4，另一种是行相对，列绝对，如另一种是行相对，列绝对，如E4E4。o对于混合引用，一般是先输入普通计算公式，然后再对于混合引用，一般是先输入普通计算公式，然后再分析是行不变还是列不变，根据情况，添加分析是行不变还是列不变，根据情况，添加“”符符号。号。6:206:2023233.3 3.3

14、 公式的引用公式的引用o3.3.3 3.3.3 公式的混合引用公式的混合引用6:206:2024243.3 3.3 公式的引用公式的引用o3.3.3 3.3.3 公式的混合引用公式的混合引用6:206:2025253.3 3.3 公式的引用公式的引用o3.3.4 绝对交集绝对交集o在公式中，对单元格区域而不是单个单元格的引用在公式中，对单元格区域而不是单个单元格的引用按照单个单元格进行计算时，依靠公式所在的从属按照单个单元格进行计算时，依靠公式所在的从属单元格与引用单元格之间的物理位置，返回交叉点单元格与引用单元格之间的物理位置，返回交叉点值，称为值，称为“绝对交集绝对交集”引用或引用或“隐含

15、交叉隐含交叉”引用。引用。6:206:2026263.3 3.3 公式的引用公式的引用o3.3.5 3.3.5 三维引用单元格三维引用单元格o引用其他工作簿文档的单元格时需要在单元格名字引用其他工作簿文档的单元格时需要在单元格名字前加上工作簿路径前加上工作簿路径、工作簿的名称和工作表名称，、工作簿的名称和工作表名称，并用单引号并用单引号“”引起来，文件名本身引起来，文件名本身（不包括路（不包括路径名）径名）需要用需要用中括号中括号括起来，另外还需要用括起来，另外还需要用感叹号感叹号！来连接单元格名称。来连接单元格名称。o引用格式：引用格式：文件路径文件路径 工作簿名工作簿名 工作表名工作表名！

16、单！单元格地址元格地址注意：如果被引用的工作表名称含有汉字汉字，则在使用三维引用时，需要使用单引号单引号，将该工作表名称引用起来。6:206:2027273.3 3.3 公式的引用公式的引用o三维引用汇总连续多工作表相同区域三维引用汇总连续多工作表相同区域 当跨表引用多个相邻的工作表中相同的单元格区域进行汇总时，可以使用三维引用进行计算而无须逐个工作表对单元格区域进行引用，其表示方式为：按工作表排列顺序，使用冒号将起始工作表和终止工作表名进行连接，作为跨表引用的工作表名。6:206:2028283.4 使用名称使用名称o名称是工作簿中某些项目的标示符，用户在工作过名称是工作簿中某些项目的标示符

17、，用户在工作过程中可以为单元格、常量、图标、公式或工作表建程中可以为单元格、常量、图标、公式或工作表建立一个名称。如果某个项目被定义了一个名称，就立一个名称。如果某个项目被定义了一个名称，就可以在公式或函数中通过该名称来应用它。可以在公式或函数中通过该名称来应用它。6:206:2029293.4 使用名称使用名称o3.4.1根据选定内容快速创建名称根据选定内容快速创建名称nStep1 选择创建名称的单元格区域，如选择创建名称的单元格区域，如B2:B5nStep2 在鼠标右键中的在鼠标右键中的“定义的名称定义的名称”组中单组中单击击“根据所选内容创建根据所选内容创建”命令命令nStep3 在弹出

18、的在弹出的“以选定区域创建名称以选定区域创建名称”对话对话框中勾选框中勾选“首行首行”复选框；复选框；nStep4 单击单击“确定确定”按钮；按钮；nStep5 返回工作表，选中单元格区域返回工作表，选中单元格区域B2:B5时时，名称框中会显示该区域的名称。，名称框中会显示该区域的名称。6:206:2030303.4 使用名称使用名称o3.4.2使用对话框新建名称使用对话框新建名称n除了可以直接在名称框中定义名称外，用户还除了可以直接在名称框中定义名称外，用户还可以使用可以使用“新建名称新建名称”对话框新建名称。对话框新建名称。nStep1在在“公式公式”选项卡的选项卡的“定义的名称定义的名称

19、”组组中单击中单击“定义名称定义名称”下三角按钮；下三角按钮；nStep2在展开的下来列表框中单击在展开的下来列表框中单击“定义名称定义名称”命令；命令；nStep3 在弹出的在弹出的“新建名称新建名称”对话框中的对话框中的“名名称称”框中输入名称框中输入名称“借增借增”；nStep4 单击单击“引用位置引用位置”框右侧的单元格引用框右侧的单元格引用按钮；按钮；6:206:2031313.4 使用名称使用名称o3.4.2使用对话框新建名称使用对话框新建名称nStep5 在工作表中选择要定义为该名称的单元在工作表中选择要定义为该名称的单元格区域格区域C2:C5；nStep6 然后单击单元格引用按

20、钮；然后单击单元格引用按钮；nStep7 返回返回“新建名称新建名称”对话框中，单击对话框中，单击“确确定定”按钮；按钮；nStep8 返回工作表中，选定单元格区域，名称返回工作表中，选定单元格区域，名称框中将显示该区域的名称。框中将显示该区域的名称。6:206:2032323.4 使用名称使用名称o3.4.3使用名称框定义名称使用名称框定义名称nStep1 首先选定要定义为名称的单元格区域首先选定要定义为名称的单元格区域D2:D5；nStep2 在名称框中输入名称在名称框中输入名称“贷减贷减”；在定义名称时，名称的命名规则如下：在定义名称时，名称的命名规则如下：（1）名称的）名称的第一个字符

21、第一个字符必须是必须是字母、汉字或下划线字母、汉字或下划线；（2）名称不能与单元格名称相同，名称中间）名称不能与单元格名称相同，名称中间不可以有空格不可以有空格；（3）名称长度不能超过）名称长度不能超过255个字符，字母不区分大小写；个字符，字母不区分大小写；（4）同一工作簿中定义的名称不能重复。）同一工作簿中定义的名称不能重复。6:206:2033333.4 使用名称使用名称o3.4.4 在公式中引用名称在公式中引用名称n在单元格中输入公式起始符在单元格中输入公式起始符“=”n在在“定义的名称定义的名称”功能组中单击功能组中单击“用于公式用于公式”下三下三角按钮；角按钮；n从弹出的下拉列表框

22、中选择从弹出的下拉列表框中选择“期初余额期初余额”名称；名称；n接着输入运算符接着输入运算符“+”，再次单击，再次单击“用于公式用于公式”下拉下拉列表框，选择列表框，选择“借增借增”；n输入运算符输入运算符“-”，再次单击，再次单击“用于公式用于公式”下拉列表下拉列表框，选择框，选择“贷减贷减”；n按下按下“ctrl+enter”键。键。6:206:2034343.4 使用名称使用名称o3.4.5 管理名称管理名称o使用名称管理器来管理已定义的名称，如使用名称管理器来管理已定义的名称，如编辑名称编辑名称以及以及删除名称删除名称等操作。等操作。o编辑名称编辑名称n在在“定义的名称定义的名称”组中

23、单击组中单击“名称管理器名称管理器”按钮；按钮；n在在“名称管理器名称管理器”对话框中选择需要编辑的名称；对话框中选择需要编辑的名称；n单击单击“编辑编辑”按钮；按钮；n在在“编辑名称编辑名称”对话框中，可以重新输入名称；对话框中，可以重新输入名称；o删除名称删除名称n在在“名称管理器名称管理器”对话框中选中该名称；对话框中选中该名称；n单击单击“删除删除”按钮；按钮；6:206:2035353.5 3.5 数组公式及其应用数组公式及其应用p之前的公式一次只能对一组数据进行计算。之前的公式一次只能对一组数据进行计算。p数组公式可以同时对多组数据进行计算，并返回多个数组公式可以同时对多组数据进行

24、计算，并返回多个数据计算结果。数据计算结果。p在数组公式中使用的数据称为在数组公式中使用的数据称为数组参数数组参数，并且运算符并且运算符前后的数组参数必须是同维的前后的数组参数必须是同维的。6:206:2036363.5 3.5 数组公式及其应用数组公式及其应用3.5.1 3.5.1 数组公式的建立数组公式的建立o步骤如下：步骤如下：选择保存结果的单元格区域选择保存结果的单元格区域输入计算公式输入计算公式(在编辑栏在编辑栏)公式输入完成后，按公式输入完成后，按Ctrl+Shift+EnterCtrl+Shift+Enter键键p如果公式输入完后只按如果公式输入完后只按EnterEnter键，则

25、返回的是一个结键，则返回的是一个结果。果。6:206:203737oExcel会自动给公式加上会自动给公式加上“”以和普通公式区以和普通公式区别开来，不用用户输入别开来，不用用户输入“”，但如是是想在公，但如是是想在公式里直接表示一个数组，就需要输入式里直接表示一个数组，就需要输入“”来把来把数组的元素括起来。数组的元素括起来。6:206:2038383.5 3.5 数组公式及其应用数组公式及其应用3.5.1 3.5.1 数组公式的建立数组公式的建立o用数组计算两个区域的乘积用数组计算两个区域的乘积6:206:2039393.5 3.5 数组公式及其应用数组公式及其应用3.5.1 3.5.1

26、数组公式的建立数组公式的建立o用数组计算两个区域的乘积用数组计算两个区域的乘积-选择结果区域选择结果区域6:206:2040403.5 3.5 数组公式及其应用数组公式及其应用3.5.1 3.5.1 数组公式的建立数组公式的建立o用数组计算两个区域的乘积用数组计算两个区域的乘积-输入计算公式输入计算公式6:206:2041416.5 6.5 数组公式及其应用数组公式及其应用3.5.1 3.5.1 数组公式的建立数组公式的建立o用数组计算两个区域的乘积用数组计算两个区域的乘积-快捷键出结果快捷键出结果EnterEnter键结果键结果Ctrl+Shift+EnterCtrl+Shift+Enter

27、键结果键结果6:206:2042423.5 3.5 数组公式及其应用数组公式及其应用3.5.1 3.5.1 数组公式的建立数组公式的建立o用数组计算多列数据的和用数组计算多列数据的和-选择结果区域选择结果区域6:206:2043433.5 3.5 数组公式及其应用数组公式及其应用3.5.1 3.5.1 数组公式的建立数组公式的建立o用数组计算多列数据的和用数组计算多列数据的和-输入计算公式输入计算公式输入公式时，必须一列一列的选择，否则出错！输入公式时，必须一列一列的选择，否则出错！6:206:2044443.5 3.5 数组公式及其应用数组公式及其应用3.5.1 3.5.1 数组公式的建立数

28、组公式的建立o用数组计算多列数据的和用数组计算多列数据的和-快捷键出结果快捷键出结果6:206:2045453.5 3.5 数组公式及其应用数组公式及其应用3.5.2 3.5.2 数组公式的扩展数组公式的扩展o使用数组公式进行数据处理，所有的数组必须是同维使用数组公式进行数据处理，所有的数组必须是同维的。的。o如果数组参数或数据区域的维数不匹配，如果数组参数或数据区域的维数不匹配，ExcelExcel会自会自动扩展该参数。动扩展该参数。6:206:2046463.5 3.5 数组公式及其应用数组公式及其应用3.5.2 3.5.2 数组公式的扩展数组公式的扩展o例如，假设单元格区域例如，假设单元

29、格区域A1:A5A1:A5的数据分别为的数据分别为1 1，2 2，3 3，4 4，5 5，现要将这五个数分别乘以，现要将这五个数分别乘以5 5，把结果放在单元，把结果放在单元格区域格区域B1:B5B1:B5中。则可以在区域中。则可以在区域B1:B5B1:B5中输入计算公式中输入计算公式“=A1:A5=A1:A5*5 5”。显然，这个公式不平衡，左边五个。显然，这个公式不平衡，左边五个参数，右边只有一个参数。如何相乘？参数，右边只有一个参数。如何相乘？6:206:2047473.5 3.5 数组公式及其应用数组公式及其应用o3.5.2 3.5.2 数组公式的扩展数组公式的扩展6:206:2048

30、48o单列数组与单行数组的计算单列数组与单行数组的计算6:206:204949o行数（或列数）相同的单列（或单行）数组与多行行数（或列数）相同的单列（或单行）数组与多行多列数组的计算多列数组的计算n（1）单列数组的行数与多行多列数组的行数相）单列数组的行数与多行多列数组的行数相同时：同时：6:206:205050o（2）单行数组的列数与多行多列数组的列数相同）单行数组的列数与多行多列数组的列数相同时：时：6:206:2051513.5 3.5 数组公式及其应用数组公式及其应用3.5.3 3.5.3 二维数组公式二维数组公式o使用一维数组公式进行数据处理，都是局限于单行或使用一维数组公式进行数据

31、处理，都是局限于单行或单列的计算。单列的计算。o实际应用中，往往涉及到多行或多列数据的处理，或实际应用中，往往涉及到多行或多列数据的处理，或需要在不同工作表中进行数据处理。需要在不同工作表中进行数据处理。o二维数组公式的建立和一维数组公式的建立方法相同。二维数组公式的建立和一维数组公式的建立方法相同。6:206:2052523.5 3.5 数组公式及其应用数组公式及其应用3.5.3 3.5.3 二维数组公式二维数组公式o同一工作表中的数据计算同一工作表中的数据计算6:206:2053533.5 3.5 数组公式及其应用数组公式及其应用3.5.3 3.5.3 二维数组公式二维数组公式o同一工作表

32、中的数据计算同一工作表中的数据计算6:206:2054543.5 3.5 数组公式及其应用数组公式及其应用3.5.3 3.5.3 二维数组公式二维数组公式o同一工作表中的数据计算同一工作表中的数据计算6:206:2055553.5 3.5 数组公式及其应用数组公式及其应用3.5.3 3.5.3 二维数组公式二维数组公式o不同工作表间的数据计算不同工作表间的数据计算6:206:2056563.5 3.5 数组公式及其应用数组公式及其应用3.5.3 3.5.3 二维数组公式二维数组公式o不同工作表间的数据计算不同工作表间的数据计算6:206:2057573.5 3.5 数组公式及其应用数组公式及其应用3.5.3 3.5.3 二维数组公式二维数组公式o不同工作表间的数据计算不同工作表间的数据计算6:206:2058583.5 3.5 数组公式及其应用数组公式及其应用3.5.3 3.5.3 二维数组公式二维数组公式o不同工作表间的数据计算不同工作表间的数据计算6:206:2059593.5 3.5 数组公式及其应用数组公式及其应用3.5.3 3.5.3 二维数组公式二维数组公式o不同工作表间的数据计算不同工作表间的数据计算6:206:206060