Excel在财务管理中的应用（第五版）第03章Excel的运算与数据处理

1、Excel在财务管理中的应用（第5版）03CHAPTER Three 第3章Excel的运算与数据处理【学习目标】通过本章的学习，学生应该掌握利用公式进行数据计算的方法，掌握Excel 2016的工作表、图表和数据库三大功能模块的操作方法与技巧，熟悉常用函数的功能与使用方法，学会创建直观、形象的统计图表，能够利用数据库管理功能对数据进行分析和统计管理。Excel中的公式运用3.1点击添加文本点击添加文本点击添加文本点击添加文本3.1.1公式中的运算符DCBA2）比较运算符比较运算符用于比较两个数值的大小，并返回逻辑值True（真）或False（假）。3）文本运算符文本运算符用于连接文本（字符串

2、），主要表示为“&”。4）引用运算符引用运算符用于对单元格区域进行合并计算，常见的引用运算符有冒号（：）、逗号（，）和空格。1）算术运算符算术运算符主要用于完成基本的数学运算，如加（+）、减（-）、乘（*）、除（/）、幂（）。点击添加文本点击添加文本点击添加文本点击添加文本3.1.1公式中的运算符表3-1运算符的优先级运算符优先级引用运算符1算术运算符2文本运算符3比较运算符4点击添加文本点击添加文本点击添加文本点击添加文本3.1.2单元格的引用DCBA2）绝对引用如“$A$1”3）半相对引用（半绝对引用）如A$14）三维引用三维引用是指在一个工作簿中从不同的工作表引用单元格地址，或从不同的工

3、作簿中引用单元格地址。“=B5+Book2Sheet2!$C$3”。1）相对引用“=A2+A3”点击添加文本点击添加文本点击添加文本点击添加文本3.1.3公式的输入、修改、移动与复制公式的输入方法很简单，具体步骤如下：1选择要输入公式的单元格；2键入等号（=）；3键入公式；4按【Enter】键或选用编辑栏左边的“”按钮结束。点击添加文本点击添加文本点击添加文本点击添加文本 3.1.4数组公式的使用图3-1“16月份主营业务利润”图示1）输入数组公式【例3-1】如图3-1所示，如果要计算16月份的主营业务利润，可以采用数组公式计算。其方法是：（1）选中单元格区域B5：G5。（2）输入公式“=B2

4、：G2-B3：G3-B4：G4”。（3）同时按下【Ctrl】+【Shift】+【Enter】组合键，即可得到各月的主营业务利润。点击添加文本点击添加文本点击添加文本点击添加文本 3.1.4数组公式的使用2）编辑数组公式如果发现输入的数组公式错误，可按下列步骤进行修改：04030201修改完毕后，按【Ctrl】+【Shift】+【Enter】组合键。编辑数组公式的内容。单击公式编辑栏，此时编辑栏被激活，大括号“ ”在数组公式中消失。（1）单击数组区域中的任意一个单元 点击添加文本点击添加文本点击添加文本点击添加文本3.1.5公式中返回的错误信息1）输入数组公式图3-2错误符号提示及错误提示选项点

5、击添加文本点击添加文本点击添加文本点击添加文本3.1.5公式中返回的错误信息表3-2公式中的出错返回值及其含义 出错返回值含义 #DIV/0!公式中的除数为0或公式使用了空单元格时，显示此错误值 #N/A!某个单元格的数值不可用于公式时，显示此错误值 #NAME?不能识别公式中的文本时，显示此错误值 #NUM!公式中包含无效数值时，显示此错误值 #NULL!公式中使用了两个不相交的区域交集时，显示此错误值 #RFF!公式中引用了无效的单元格时，显示此错误值 #VALUE!公式中所包含的单元格是不同的数据类型时，显示此错误值 #!公式产生的结果太长，单元格容纳不下或单元格的日期、时间公式产生了一

6、个负值时，显示此错误值点击添加文本点击添加文本点击添加文本点击添加文本3.1.6给单元格或单元格区域定义名称1）命名规则（1）名称中不能含有空格，并且不区分大小写。（2）名称的长度不能超过255个字符。（3）名称中不能使用除下划线、点号和反斜线（）、问号（？）以外的其他符号。（4）名称的第一个字符必须是字母、汉字或下划线，但不能以纯数字命名或以数字开头。不能使用单元格地址。（5）因为字母R、C在R1C1引用样式中表示工作表的行、列，所以除了R、C、r、c，其他单个字母均可作为名称。点击添加文本点击添加文本点击添加文本点击添加文本3.1.6给单元格或单元格区域定义名称2）命名方法 （1）使用菜单

7、命令。（2）使用【定义名称】对话框。 图3-3【定义名称】/【新建名称】对话框点击添加文本点击添加文本点击添加文本点击添加文本3.1.6给单元格或单元格区域定义名称2）命名方法 （1）使用菜单命令。（2）使用【定义名称】对话框。 图3-4将单元格区域B2：G2命名为“销售量”点击添加文本点击添加文本点击添加文本点击添加文本3.1.6给单元格或单元格区域定义名称3）名称的使用图3-5单元格名称的使用函数的使用3.2点击添加文本点击添加文本点击添加文本点击添加文本表3-3Excel中函数的种类及功能种类功能简介数据库函数分析列表或数据库中的数据日期与时间函数在公式中分析和处理日期值和时间值信息函数

8、检测及返回单元格的个数、单元格内的数据类型等财务函数进行一般的财务计算逻辑函数进行逻辑判断或者进行复合检验统计函数对数据组及数据区域进行统计分析查找和引用函数在数据清单中查找特定数据或者查找一个单元格的引用文本函数处理文本中的字符串或单元格的直接引用数学和三角函数独立地完成数学运算工程函数解决工程方面的计算问题用户自定义函数在工作表函数无法满足需要时，用户可以自定义创建函数点击添加文本点击添加文本点击添加文本点击添加文本3.2.1函数的基本语法 =函数名（参数1，参数2，参数n）点击添加文本点击添加文本点击添加文本点击添加文本3.2.2函数的输入 1）手工输入函数 2）使用【插入函数】功能输入

9、函数手工输入函数的方法很简单，与输入公式类似，或者说函数的输入是公式输入的一种特例，在输入函数时可以不区分大小写。点击添加文本点击添加文本点击添加文本点击添加文本3.2.3嵌套函数的使用 图3-7函数IF（）对话框点击添加文本点击添加文本点击添加文本点击添加文本 3.2.4常用函数介绍 1）函数SUM（）、SUMIF（）和SUMPRODUCT（）（3）乘积求和函数SUMPRODUCT（）。功能：在给定的几个数组中，将数组间对应的元素相乘，并返回乘积之和，属于数学函数。 （1）无条件求和函数SUM（）。功能：求30个以内参数的和，属于数学函数。公式：=SUM（number1，number2，nu

10、mberN） （2）条件求和函数SUMIF（）。功能：根据指定条件对若干单元格求和，属于数学函数，在进行分类汇总时是很有用的。公式：=SUMIF（range，criteria，sum_range）ACB点击添加文本点击添加文本点击添加文本点击添加文本 3.2.4常用函数介绍图3-8M公司销售人员的销售业绩1）函数SUM（）、SUMIF（）和SUMPRODUCT（）【例3-2】M公司销售人员的销售业绩如图3-8所示，若要计算每个销售人员的销售额，可以使用函数SUMIF（），公式为“=SUMIF（B3：B16，刘正，C3：C16）”。函数SUMIF（）的对话框如图3-9所示。点击添加文本点击添加文

11、本点击添加文本点击添加文本 3.2.4常用函数介绍图3-9函数SUMIF（）的对话框1）函数SUM（）、SUMIF（）和SUMPRODUCT（）【例3-2】M公司销售人员的销售业绩如图3-8所示，若要计算每个销售人员的销售额，可以使用函数SUMIF（），公式为“=SUMIF（B3：B16，刘正，C3：C16）”。函数SUMIF（）的对话框如图3-9所示。点击添加文本点击添加文本点击添加文本点击添加文本 3.2.4常用函数介绍 2）平均数函数AVERAGE（）例如，A1：A5命名为“基础数据”，其中的数值分别为7、8、18、15和2，那么输入“=AVERAGE（A1：A5）”，得出答案为“10”

12、。功能：计算给定参数的算术平均值，属于统计函数。公式：=AVERAGE（number1，number2，numberN）AB点击添加文本点击添加文本点击添加文本点击添加文本 3.2.4常用函数介绍 3）四舍五入函数ROUND（）功能：返回某个数字按指定位数四舍五入后的数字，属于数学函数。公式：=ROUND（number，num_digits）AB点击添加文本点击添加文本点击添加文本点击添加文本 3.2.4常用函数介绍 4）乘幂函数POWER（）和平方根函数SQRT（）1 2（1）乘幂函数POWER（）。功能：对数字进行乘幂运算，属于数学函数。公式：=POWER（number，power）（2）

13、平方根函数SQRT（）。功能：返回数值的平方根，属于数学函数。公式：=SQRT（number） 点击添加文本点击添加文本点击添加文本点击添加文本 3.2.4常用函数介绍 5）最小值函数MIN（）和最大值函数MAX（）0201功能：函数MIN（）的功能是给定参数表中的最小值，函数MAX（）的功能是给定参数表中的最大值，它们都属于统计函数。公式：=MIN（参数1，参数2，参数N） =MAX（参数1，参数2，参数N） 点击添加文本点击添加文本点击添加文本点击添加文本 3.2.4常用函数介绍 6）无条件计数函数COUNT（）和条件计数函数COUNTIF（）1 2（1）无条件计数函数COUNT（）。功能

14、：计算给定区域内包含数值的单元格以及参数列表中的数值的个数，属于统计函数。公式：=COUNT（value1，value2，valueN）（2）条件计数函数COUNTIF（）。功能：计算给定区域内满足特定条件的单元格的数目，属于统计函数。公式：=COUNTIF（range，criteria）点击添加文本点击添加文本点击添加文本点击添加文本 3.2.4常用函数介绍 7）条件函数IF（）0201功能：执行真假值判断，也称条件函数，它根据参数条件的真假，返回不同的结果，属于逻辑函数。在实践中，经常使用函数IF（）对数值和公式进行条件检测。公式：=IF（logical_test，value_if_tru

15、e，value_if_false） 点击添加文本点击添加文本点击添加文本点击添加文本 3.2.4常用函数介绍图3-10“工资计算表”图示7）条件函数IF（）【例3-3】在图3-10中，完成工资计算表的填制。其中，奖金按部门发放，标准为厂部500元、车间400元、销售部300元。岗位津贴根据所在部门按基础工资的一定比例发放，其发放比例为厂部6%、车间5%、销售部4%。住房公积金按基础工资的6%缴存。点击添加文本点击添加文本点击添加文本点击添加文本 3.2.4常用函数介绍7）条件函数IF（）奖金的计算方法为：在G4单元格中输入“=IF（E4=厂部，500，IF（E4=车间，400，300）”，然后

16、向下复制即可。岗位津贴的计算方法为：在H4单元格中输入“=IF（E4=厂部，F4*6%，IF（E4=车间，F4*5%，F4*4%）”，然后向下复制即可。住房公积金的计算方法为：在K4单元格中输入“=F4*6%”，然后向下复制即可。计税工资的计算方法为：在J4单元格中输入“=I4-K4”，然后向下复制即可。个人所得税的计算方法为：在L4单元格中输入“=IF（J40，0，IF（J41500，J4*3%，IF（J4300000，C3/B31%），10%，5%）”，将其向下填充复制到D4：D7单元格中。（2）选取单元格区域E3：E7，输入公式“=B3：B7*D3：D7”，按【Ctrl】+【Shift】

17、+【Enter】组合键。这样各销售人员的奖金额就计算出来了，如图3-11所示。Excel的数据管理和分析3.3点击添加文本点击添加文本点击添加文本点击添加文本3.3.1数据的排序 单列排序1多列排序2图3-13Excel中的排序和筛选命令 自定义排序3点击添加文本点击添加文本点击添加文本点击添加文本3.3.1数据的排序 单列排序1多列排序2图3-14Excel中的排序和筛选命令 自定义排序3点击添加文本点击添加文本点击添加文本点击添加文本3.3.1数据的排序 单列排序1多列排序2图3-15多列排序示例 自定义排序3点击添加文本点击添加文本点击添加文本点击添加文本3.3.1数据的排序 单列排序1

18、多列排序2图3-16多列排序结果 自定义排序3点击添加文本点击添加文本点击添加文本点击添加文本3.3.1数据的排序 单列排序1多列排序2图3-17自定义排序 自定义排序3点击添加文本点击添加文本点击添加文本点击添加文本 3.3.2数据的筛选 02011）自动筛选2）高级筛选 点击添加文本点击添加文本点击添加文本点击添加文本 3.3.2数据的筛选 1）自动筛选图3-18自动筛选结果点击添加文本点击添加文本点击添加文本点击添加文本 3.3.2数据的筛选 1）自动筛选图3-19【自动筛选前10个】对话框点击添加文本点击添加文本点击添加文本点击添加文本 3.3.2数据的筛选 1）自动筛选图3-20【自

19、定义自动筛选方式】对话框点击添加文本点击添加文本点击添加文本点击添加文本 3.3.2数据的筛选 2）高级筛选图3-21建立条件区域点击添加文本点击添加文本点击添加文本点击添加文本 3.3.2数据的筛选 2）高级筛选图3-22【高级筛选】对话框点击添加文本点击添加文本点击添加文本点击添加文本3.3.3数据的分类汇总 02011）分类汇总的建立2）分类汇总的清除 图3-23【分类汇总】对话框点击添加文本点击添加文本点击添加文本点击添加文本 3.3.4数据透视表与数据透视图图3-25数据透视表字段列表1）数据透视表 （1）创建数据透视表【例3-8】下面以工作簿中，某公司某品牌计算机设备全年销量统计表

20、为例，介绍建立数据透视表的详细步骤，如图3-25所示。点击添加文本点击添加文本点击添加文本点击添加文本 3.3.4数据透视表与数据透视图图3-26【创建数据透视表】对话框（1）创建数据透视表【例3-8】下面以工作簿中，某公司某品牌计算机设备全年销量统计表为例，介绍建立数据透视表的详细步骤，如图3-25所示。点击添加文本点击添加文本点击添加文本点击添加文本 3.3.4数据透视表与数据透视图图3-27空白数据透视表并打开【数据透视表字段列表】窗口（1）创建数据透视表【例3-8】下面以工作簿中，某公司某品牌计算机设备全年销量统计表为例，介绍建立数据透视表的详细步骤，如图3-25所示。点击添加文本点击

21、添加文本点击添加文本点击添加文本 3.3.4数据透视表与数据透视图图3-28创建的数据透视表（1）创建数据透视表【例3-8】下面以工作簿中，某公司某品牌计算机设备全年销量统计表为例，介绍建立数据透视表的详细步骤，如图3-25所示。点击添加文本点击添加文本点击添加文本点击添加文本 3.3.4数据透视表与数据透视图图3-29数据透视表结构（2）数据透视表的组成及相关术语。点击添加文本点击添加文本点击添加文本点击添加文本 3.3.4数据透视表与数据透视图图3-30数据透视表字段列表（2）数据透视表的组成及相关术语。点击添加文本点击添加文本点击添加文本点击添加文本 3.3.4数据透视表与数据透视图（3

22、）编辑数据透视表。在创建好数据透视表后，用户还可以根据需要添加和设置数据透视表中的字段，以达到从不同角度使用数据透视表分析数据的目的。点击添加文本点击添加文本点击添加文本点击添加文本 3.3.4数据透视表与数据透视图 0201（1）基于工作表数据创建数据透视图。（2）选择外部数据源创建数据透视图。 2）创建数据透视图点击添加文本点击添加文本点击添加文本点击添加文本 3.3.4数据透视表与数据透视图图3-31【创建数据透视图】对话框 2）创建数据透视图【例3-9】下面以“某公司差旅报销管理-数据透视图“工作簿中的内容为例，介绍基于工作表数据创建数据透视图的操作步骤。 点击添加文本点击添加文本点击添加文本点击添加文本 3.3.4数据透视表与数据透视图图3-32【数据透视