公式的应用解析课件

1、第3章 公式的应用3.1 公式的创建3.2 公式的编辑3.3 公式的引用3.4 使用名称3.5 数组公式及其应用*13.1 公式的创建公式是对工作表中的数值进行计算的等式。公式要以等号“=”开始，用于表示其后的字符为公式。紧随其后的是需要运算的元素，各元素之间用运算符分隔。*23.1 公式的创建3.1.1 公式的组成等号“=”变量常量运算符*33.1 公式的创建3.1.2 公式的运算符算术运算符：用于基本数学计算。+、-、*、/、%、比较运算符：用于比较两个数值的大小，公式返回值是一个逻辑值。 =、=、=、。文本运算符：& 将多个文本连接成组合文本（制作九九乘法表）。引用运算符：连续区域运算符

2、（：）、联合操作运算符（，）、取交集运算符（空格）。*43.1.2 公式的运算符1.冒号（:）：区域运算符，两个单元格之间用英文冒号连接，表示引用这两个单元格之间所有单元格的区域。例如区域A1:D3，表示以A1单元格和D3单元格为对角的区域， 如公式： =SUM(A1:D3)另外，“A:A”表示引用A列，“A:C”表示引用A列至C列，“1:1”表示引用第一行，“1:3”表示引用第1行至第3行，其余依此类推。*53.1.2 公式的运算符2.空格（ ）：交集运算符，生成对两个引用中共有的单元格的引用。 例如公式： =SUM(A1:D3 B1:C5)计算A1:D3和B1:C5 两个区域的交叉区域，即

3、B3:C3区域所有单元格数值的和。*63.1.2 公式的运算符3.逗号（,）： 联合运算符，该运算符将多个引用合并为一个引用。 例如公式: =SUM(A1:D3,B1:C5)计算A1:D3和 B1:C5两个区域中所有单元格数值的和。*73.1 公式的创建3.1.3 公式的创建直接在单元格输入直接在编辑栏中输入在输入公式时，不能输入空格，否则运算时会出现错误。*83.2 公式的编辑3.2.1 公式的修改在单元格修改双击要修改公式的单元格修改公式回车在编辑栏中修改*93.2 公式的编辑3.2.2 公式的移动和复制公式的移动选定要移动公式的单元格“剪切”命令“粘贴”命令公式移动后，单元格的引用不变。

4、即：计算的结果不变。*103.2 公式的编辑3.2.2 公式的移动和复制公式的移动移动前的公式引用情况移动后的公式引用情况*113.2 公式的编辑3.2.2 公式的移动和复制公式的复制选定要移动公式的单元格“复制”命令“选择性粘贴”命令“公式”公式复制后，单元格的引用发生变化。即：计算的结果一般会发生变化。*123.2 公式的编辑3.2.2 公式的移动和复制公式的复制复制前的公式引用情况复制后的公式引用情况*133.2 公式的编辑3.2.2 公式的移动和复制公式的复制只复制公式的计算结果复制含有公式的单元格在目标单元格点击右键在菜单中选择“选择型粘贴”命令粘贴“数值”*143.2 公式的编辑3

5、.2.2 公式的移动和复制公式的复制只复制公式的计算结果（1）（2）*153.2 公式的编辑3.2.2 公式的移动和复制公式的复制只复制公式的计算结果选择性粘贴前选择性粘贴后*163.3 公式的引用单元格引用：单元格的行、列标号在Excel中的引用就是标识工作表中单元格或表元区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表中不同部分的数据，或者在多个公式中使用同一个单元格的数据。*173.3 公式的引用3.3.1 公式的相对引用当把一个含有单元格地址的公式复制到一个新位置或一个公式填充一个单元格区域时，公式中单元格的地址会随之改变。公式所在单元格地址发生变化，则公式中引用

6、的单元格地址也随之发生变化。相对引用在公式中的使用最多。*183.3 公式的引用3.3.1 公式的相对引用当某部门销售量变化时，Excel会重新计算总量。*193.3 公式的引用3.3.2 公式的绝对引用将公式复制或填充到其他位置时，公式中单元格的地址固定不变，其中的数据还是原来单元格的数据。公式所在单元格地址发生变化，但是公式中引用的单元格地址固定不变。绝对引用要通过“冻结”单元格地址来实现。即：分别在行标号和列标号的前面添加一个“”。*203.3 公式的引用3.3.2 公式的绝对引用*213.3 公式的引用3.3.2 公式的绝对引用*223.3 公式的引用3.3.3 公式的混合引用混合引用

7、是介于相对引用和绝对引用之间的一种引用，也就是说引用单元格的行和列之一，一个是相对的，一个是绝对的。混合引用有两种：一种是行绝对、列相对；如E4，另一种是行相对，列绝对，如E4。对于混合引用，一般是先输入普通计算公式，然后再分析是行不变还是列不变，根据情况，添加“”符号。*233.3 公式的引用3.3.3 公式的混合引用*243.3 公式的引用3.3.3 公式的混合引用*253.3 公式的引用3.3.4 绝对交集在公式中，对单元格区域而不是单个单元格的引用按照单个单元格进行计算时，依靠公式所在的从属单元格与引用单元格之间的物理位置，返回交叉点值，称为“绝对交集”引用或“隐含交叉”引用。*263

8、.3 公式的引用3.3.5 三维引用单元格引用其他工作簿文档的单元格时需要在单元格名字前加上工作簿路径、工作簿的名称和工作表名称，并用单引号“”引起来，文件名本身（不包括路径名）需要用中括号括起来，另外还需要用感叹号！来连接单元格名称。引用格式：文件路径工作簿名工作表名！单元格地址注意：如果被引用的工作表名称含有汉字，则在使用三维引用时，需要使用单引号，将该工作表名称引用起来。*273.3 公式的引用三维引用汇总连续多工作表相同区域 当跨表引用多个相邻的工作表中相同的单元格区域进行汇总时，可以使用三维引用进行计算而无须逐个工作表对单元格区域进行引用，其表示方式为：按工作表排列顺序，使用冒号将起

9、始工作表和终止工作表名进行连接，作为跨表引用的工作表名。*283.4 使用名称名称是工作簿中某些项目的标示符，用户在工作过程中可以为单元格、常量、图标、公式或工作表建立一个名称。如果某个项目被定义了一个名称，就可以在公式或函数中通过该名称来应用它。*293.4 使用名称3.4.1根据选定内容快速创建名称Step1 选择创建名称的单元格区域，如B2:B5Step2 在鼠标右键中的“定义的名称”组中单击“根据所选内容创建”命令Step3 在弹出的“以选定区域创建名称”对话框中勾选“首行”复选框；Step4 单击“确定”按钮；Step5 返回工作表，选中单元格区域B2:B5时，名称框中会显示该区域的

10、名称。*303.4 使用名称3.4.2使用对话框新建名称除了可以直接在名称框中定义名称外，用户还可以使用“新建名称”对话框新建名称。Step1在“公式”选项卡的“定义的名称”组中单击“定义名称”下三角按钮；Step2在展开的下来列表框中单击“定义名称”命令；Step3 在弹出的“新建名称”对话框中的“名称”框中输入名称“借增”；Step4 单击“引用位置”框右侧的单元格引用按钮；*313.4 使用名称3.4.2使用对话框新建名称Step5 在工作表中选择要定义为该名称的单元格区域C2:C5；Step6 然后单击单元格引用按钮；Step7 返回“新建名称”对话框中，单击“确定”按钮；Step8

11、返回工作表中，选定单元格区域，名称框中将显示该区域的名称。*323.4 使用名称3.4.3使用名称框定义名称Step1 首先选定要定义为名称的单元格区域D2:D5；Step2 在名称框中输入名称“贷减”；在定义名称时，名称的命名规则如下：（1）名称的第一个字符必须是字母、汉字或下划线；（2）名称不能与单元格名称相同，名称中间不可以有空格；（3）名称长度不能超过255个字符，字母不区分大小写；（4）同一工作簿中定义的名称不能重复。*333.4 使用名称3.4.4 在公式中引用名称在单元格中输入公式起始符“=”在“定义的名称”功能组中单击“用于公式”下三角按钮；从弹出的下拉列表框中选择“期初余额”

12、名称；接着输入运算符“+”，再次单击“用于公式”下拉列表框，选择“借增”；输入运算符“-”，再次单击“用于公式”下拉列表框，选择“贷减”；按下“ctrl +enter”键。*343.4 使用名称3.4.5 管理名称使用名称管理器来管理已定义的名称，如编辑名称以及删除名称等操作。编辑名称在“定义的名称”组中单击“名称管理器”按钮；在“名称管理器”对话框中选择需要编辑的名称；单击“编辑”按钮；在“编辑名称”对话框中，可以重新输入名称；删除名称在“名称管理器”对话框中选中该名称；单击“删除”按钮； *353.5 数组公式及其应用之前的公式一次只能对一组数据进行计算。数组公式可以同时对多组数据进行计算

13、，并返回多个数据计算结果。在数组公式中使用的数据称为数组参数，并且运算符前后的数组参数必须是同维的。*363.5 数组公式及其应用3.5.1 数组公式的建立步骤如下：选择保存结果的单元格区域输入计算公式(在编辑栏)公式输入完成后，按Ctrl+Shift+Enter键如果公式输入完后只按Enter键，则返回的是一个结果。*37Excel会自动给公式加上“”以和普通公式区别开来，不用用户输入“”，但如是是想在公式里直接表示一个数组，就需要输入“”来把数组的元素括起来。*383.5 数组公式及其应用3.5.1 数组公式的建立用数组计算两个区域的乘积*393.5 数组公式及其应用3.5.1 数组公式的

14、建立用数组计算两个区域的乘积-选择结果区域*403.5 数组公式及其应用3.5.1 数组公式的建立用数组计算两个区域的乘积-输入计算公式*416.5 数组公式及其应用3.5.1 数组公式的建立用数组计算两个区域的乘积-快捷键出结果Enter键结果Ctrl+Shift+Enter键结果*423.5 数组公式及其应用3.5.1 数组公式的建立用数组计算多列数据的和-选择结果区域*433.5 数组公式及其应用3.5.1 数组公式的建立用数组计算多列数据的和-输入计算公式输入公式时，必须一列一列的选择，否则出错！*443.5 数组公式及其应用3.5.1 数组公式的建立用数组计算多列数据的和-快捷键出结

15、果*453.5 数组公式及其应用3.5.2 数组公式的扩展使用数组公式进行数据处理，所有的数组必须是同维的。如果数组参数或数据区域的维数不匹配，Excel会自动扩展该参数。*463.5 数组公式及其应用3.5.2 数组公式的扩展例如，假设单元格区域A1:A5的数据分别为1，2，3，4，5，现要将这五个数分别乘以5，把结果放在单元格区域B1:B5中。则可以在区域B1:B5中输入计算公式“= A1:A5*5”。显然，这个公式不平衡，左边五个参数，右边只有一个参数。如何相乘？*473.5 数组公式及其应用3.5.2 数组公式的扩展*48单列数组与单行数组的计算*49行数（或列数）相同的单列（或单行）数组与多行多列数组的计算（1）单列数组的行数与多行多列数组的行数相同时：*50（2）单行数组的列数与多行多列数组的列数相同时：*513.5 数组公式及其应用3.5.3 二维数组公式使用一维数组公式进行数据处理，都是局限于单行或单列的计算。实际应用中，往往涉及到多行或多列数据的处理，或需要在不同工作表中进行数据处理。二维数组公式的建立和一维数组公式的建立方法相同。*523.5 数组公式及其应用3.5.3 二维数组公式同一工作表中的数据计算*533.5 数组公式及其应用3.5.3