excel在财务管理中的运用 第3章 EXCEL的运算与数据处理

Excel在财务管理中的应用,第3章EXCEL的运算与数据处理,东北财经大学出版社,学习目标3.1EXCEL中的公式运用3.2函数的使用3.3EXCEL的数据管理和分析3.4Excel图表本章小结主要概念和观念,目录,学习目标,通过本章的学习，要求学生要掌握利用公式进行数据计算的方法，掌握Excel2003的工作表、图表和数据库三大功能的操作方法和技巧等，熟悉常用函数的功能与使用方法，学会创建直观、形象的统计图表，能够利用数据库管理功能对数据进行分析和统计管理。,3.1EXCEL中的公式运用,运用公式可方便地对工作表、工作簿中的数据进行统计和分析。公式是由运算符和参与计算的运算数组成的表达式。运算数可是常量、单元格、数据区域及函数等，其中，单元格、数据区域既可以是同一工作表、工作簿的，也可以是不同工作表、工作簿的。输入公式必须以“=”开始，然后是公式的表达式。,3.1.1公式中的运算符算术运行符:加号(+)、减号(-)、乘号(*)、除号(/)、百分比(%)、幂()比较运算符:等号(=)、大于号()、小于号(=)、小于等于号()文本连接符:Book2Sheet2!$C$3,3.1.3公式的输入、修改、移动与复制输入:公式可直接在单元格中或在编辑栏中输入在公式中如需使用函数，可单击编辑栏左端框旁的向下箭头，从弹出的常用函数列表中选定所需要的函数。如果常用函数列表中没有所需要的函数，可单击“其他函数”项，屏幕弹出“插入函数”对话框，再从中选择所需要的函数。修改:编辑栏修改或单元格修改移动:单元格引用不会改变复制:相对引用;根据需要设置为绝对引用或混合引用.,=（B4+25）/SUM（D5:F5）,例如：有期末成绩表，总评成绩中平时成绩占30%，期末成绩占70%。,3.1EXCEL中的公式运用,3.1.4数组公式1）输入数组公式输入数组公式与输入单值公式方法基本相同。首先单击待输入公式的单元格，如要求给出多个结果需单击待输入公式的单元格区域；然后输入公式，系统将自动为公式加上大括号；最后按Ctrl+Shift+Enter组合键结束操作，计算结果即显示在选定的单元格内。2）数组公式的编辑,3.1.5公式中返回的错误信息,3.2.3嵌套函数的使用在某些情况下，用户可能需要将某函数作为另一函数的参数使用。这就是函数的嵌套。当嵌套函数作为参数使用时，它返回的数值类型必须与参数使用的数值类型相同。否则，Excel将显示#VALUE!错误值。公式中最多可以包含七级嵌套函数。当函数B作为函数A的参数时，函数B称为第二级函数。,说明：函数总是返回一个处理结果；参数之间用逗号隔开；既使无参数，圆括号也不能省略。,3.2函数的使用,3.2.1函数的基本语法,函数名(参数1，参数2，),函数的主体，代表函数功能,函数运行时参数，使用逗号(,)分隔，表示与上一参数种类相同的参数可以有多个。不同类型的函数要求给定不同类型的参数.有的函数无参数,括号是必须的,=,1）手工输入函数按公式输入的方法输入。在编辑栏中输入一个等号(=)，然后直接输入函数及参数。注意：在公式和函数中所有标点符号均为半角英文标点,3.2.2函数的输入,2）使用“插入函数”,(1)函数是一种简单的表达式，是Excel自带的一些已经定义好的公式可以直接在单元格中输入,但要以等号(=)开头.,(2)函数可以出现在表达式中。,(3)利用函数向导輸入函数：,选定要输入函数的单元格；执行“插入”“函数”命令，或者单击工具栏上的“粘贴函数()”图标，找开“粘贴函数”对话框；从“函数分类”列表框中选择要输入的函数的类别，在“函数名”列表框中选择所需要的函数，从弹出的对话框中输入函数的参数；单击“确定”按钮。,使用函数向导输入：,函数格式,函数功能及参数说明,参数说明,引用的数据,计算结果,3.2函数的使用,3.2.4常用函数介绍(1)算术函数sum、sumif、ABS、exp、log、sqrt、int、round、mod等(2)统计函数count、countif、average、Max、Min、Rank等(3)日期函数now、month、today、year等(4)逻辑函数and、or、not、if、false、true等,SUM函数详解,作用:,语法:,SUM(number1,number2,),计算给定参数中数值的总和,参数说明：,(1)单一数值型数据，例如：=SUM(50,35,65),等同于公式=(50+35+65),(2)包含数值型数据单元格地址,例如：,=SUM(A1,B2,C3),等同于公式=A1+B2+C3,(3)单元格区域地址引用,例如:=SUM(A1:F5),表示对从A1单元格到F5单元格中所有数值型数据进行求和。,也可以为多个单元格区域的引用，用逗号分割。例如：=SUM(A1:F5,E3:G8)。,SUMIF函数详解,语法：,作用：,参数说明：,SUMIF(,),求满足条件的单元格中的数值之和,指用于条件判断的单元格区域。,指确定求和的条件，其形式可以为数字、表达式或文本。例如，可以表示为32、“32”、“32”、“apples”注意：双引号（”)对于字符数据是必须的,指需要求和的实际单元格。只有当中的相应单元格满足条件时，才对中的单元格求和。如果省略,则直接对中的单元格求和。,SUMIF函数详解,例如：要求计算所有男生语文成绩的总和:,COUNT函数详解,作用:,语法:,COUNT(value1,value2,),统计参数列表中数值型数据的个数,参数说明：,(1)单一数值型数据，例如：COUNT(50,35,65),(2)包含数值型数据单元格地址,例如：COUNT(A1,B2,C3),(3)单元格区域地址引用,例如:=COUNT(A1:F5),表示统计从A1单元格到F5单元格中所有数值型数据的个数。,也可以为多个单元格区域的引用，用逗号分割。例如：=COUNT（A1:F5,E3:G8)。,COUNTIF函数详解,作用:,语法:,参数说明：,COUNTIF(,),统计出指定范围内符合条件的单元格个数,指统计的单元格区域范围。可以包含任何数据,确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，可以表示为32、“32”、“32”、“apples“、”王”等。,COUNTIF函数详解,=COUNTIF(E3:E9,”=70”)-COUNTIF(E3:E9,”=80”),=E13/COUNT(E3:E9),AVERAGE函数详解,作用:,语法:,AVERAGE(number1,number2,),计算给定参数中数值数据的算数平均值,参数说明：,(1)单一数值型数据，例如：=AVERAGE(50,35,65),等同于公式=(50+35+65)/3,(2)包含数值型数据单元格地址,例如：,AVERAGE(A1,B2,C3),等同于公式=(A1+B2+C3)/3,(3)单元格区域地址引用,例如:=AVERAGE(A1:F5),表示对从A1单元格到F5单元格中所有数值型数据进行求和。,也可以为多个单元格区域的引用，用逗号分割。例如：=AVERAGE(A1:F5,E3:G8)。,MAX、MIN函数详解,作用:,语法:,参数说明：,MAX(number1,number2,)MIN(number1,number2,),统计出给定参数中数值数据的最大值和最小值,等同与AVERAGE函数,RANK函数详解,作用:,语法:,参数说明：,RANK(,),返回指定数字在一列数字中的排位,指定一个特定的数字(或单元格）；,指一组数值（或单元格区域)；,指排位方式，如果为“0”或忽略，降序；非零值，升序,例如：在总分后面添加一列，给出每位学生的总分名次。（按降序）,=RANK(H3,H$3:H$9),日期时间函数详解,Excel日期的内部表示,Excel将日期存储为一系列连续的序列数，时间存储为小数。通过将单元格的格式设置为“常规”格式，可以查看以系列值显示的日期和以小数值显示的时间。,Excel支持两种日期系统：1900年和1904年日期系统。默认的日期系统是1900年日期系统。如果要更改日期系统，请执行“工具”-“选项”命令，通过“重新计算”标签设置,注意：当输入的年份为2位数字时,Excel将如下解释年份：00到29之间，解释为2000到2029年；30到99之间，解释为1930到1999年。,日期时间函数详解,Excel日期的内部表示,下表格显示了每个日期系统中的第一天和最后一天的日期，及其对应的序列数。,日期时间函数详解,TODAY()返回当前机器的日期序列数。,NOW()返回当前机器的日期与时间序列数。,DATE(,)，根据,产生日期序列数,DAY()返回指定日期是当月的“几”号。,YEAR()返回指日期的年份。,MONTH()返回指定日期的月份。,WEEKDAY(，)返回指定日期是星期几。,日期时间函数详解,应用举例:,计算年龄,与当前日期（动态日期）比较：1、=(today()-B3)/3652、=year(today()year(B3),与某个单元格内的日期比较：1、=($B$10-B3)/3652、=year($B$10)year(B3),固定日期单元格B10,IF函数详解,作用:,语法:,参数说明：,IF(Logical_test,Logaical_if_ture,Logaical_if_false),根据条件是否成立返回特定的值,Logical_test判断条件,返回值为TURE或FALSE,Logaical_if_ture如果条件成立,则返回当前值。如果忽略则返回TURE,Logaical_if_false如果条件不成立,则返回当前值。如果忽略则返回FALSE,IF函数详解,例一：在成绩表后添加一“是否补考”列，如果物理成绩不及格则填入“补考”,课堂练习：IF函数可以嵌套7层。如果成绩表中包含数学，语文，物理成绩。怎么完成以上要求,IF函数详解,例二：有学生基本信息表，包括“出生日期”列和“说明”列，如果学生的出生月份和当前月相等，则在说明栏里填入“祝你生日快乐”,3.3.1数据记录单的使用1）使用记录单输入数据2）使用记录单修改数据3）使用记录单删除数据4）使用记录单查找数据,3.3EXCEL的数据管理和分析,3.3.2数据的排序1）数据排序的规则默认排序Excel在默认排序时是根据单元格中的数据进行排序的，在按升序排序时，Excel使用如下顺序：1.数值从最小的负数到最大的正数排序2.文本和数字的文本按从09，az，AZ的顺序排列3.逻辑值False排在True之前4.所有错误值的优先级相同5.空格排在最后,2）数据排序方法与步骤使用工具栏排序按钮：选定要进行排序的数据区域；单击“常用”工具栏上的“降序()”按钮（或“升序()”按钮）。,3）自定义排序使用排序命令:选取需排序的单元格区域；执行“数据”“排序”命令；在“排序”对话框中选择排序的依据和排序的方式；最后单击“确定”按钮。,3.3EXCEL的数据管理和分析,3.3.3数据的筛选1）自动筛选选择工作表中数据的列标题；单击“数据”“筛选”“自动筛选”；根据所要筛选的数据内容，单击相应列标题的“筛选条件”按钮，打开“筛选条件”列表框，单击所需选项；若需进一步筛选数据，可重复第步的操作。,“筛选条件”列表中项目的含义,（1）自动筛选选择工作表中数据的列标题；单击“数据”“筛选”“自动筛选”；根据所要筛选的数据内容，单击相应列标题的“筛选条件”按钮，打开“筛选条件”列表框，单击所需选项；若需进一步筛选数据，可重复第步的操作。,2）高级筛选在工作表的任一空白区域，输入筛选条件；,注意：同行条件是“与”的关系，不同行的条件是“或”的关系.,例1：语文大于80而且数学大于70。,例2：语文大于80或数学大于70,（2）高级筛选单击“数据”“筛选”“高级筛选”，打开“高级筛选”对话框；单击选择“将筛选结果复制到其他位置”选项；在“数据区域”文本框中单击，然后拖动鼠标选择将被筛选的数据单元格区域；在“条件区域”文本框中单击，然后拖动鼠标选择条件单元格区域；在“复制到”文本框中单击，然后单击欲放置筛选结果区域的起始单元格；单击“确定”。,3.3.4数据的分类汇总1）分类汇总的建立选择分类汇总数据所在区域；对所选区域的数据，按分类要求进行排序；单击“数据”“分类汇总”，打开“分类汇总”对话框；按需要进行选择后，单击“确定”。,2）分类汇总的清除（2）删除分类汇总选择分类汇总数据所在的区域；单击“数据”“分类汇总”，打开“分类汇总”对话框；单击“全部删除”。,3.3EXCEL的数据管理和分析,什么是数据透视表？数据透视表是用来从Excel数据列表、关系数据库文件或OLAP多维数据集中的特殊字段中总结信息的分析工具。它是一种交互式报表，可以快速分类汇总、比较大量的数据，并可以随时选择其中页、行和列中的不同元素，以达到快速查看源数据的不同统计结果，同时还可以随意显示和打印出你所感兴趣区域的明细数据。数据透视表有机的综合了数据排序、筛选、分类汇总等数据分析的优点，可方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。一张“数据透视表”仅靠鼠标移动字段位置，即可变换出各种类型的报表。同时，数据透视表也是解决函数公式速度瓶颈的手段之一。因此，该工具是最常用、功能最全的Excel数据分析工具之一。,数据透视表有什么用？数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式动态表格，能帮助用户分析、组织数据。例如，计算平均数、标准差，建立列联表、计算百分比、建立新的数据子集等。建好数据透视表后，可以对数据透视表重新安排，以便从不同的角度查看数据。数据透视表的名字来源于它具有“透视”表格的能力，从大量看似无关的数据中寻找背后的联系，从而将纷繁的数据转化为有价值的信息，以供研究和决策所用。总之，合理运用数据透视表进行计算与分析，能使许多复杂的问题简单化并且极大的提高工作效率,3.3.5数据透视表的使用1）建立数据透视表2）数据的透视分析3）数据更新4)显示数据项的明细数据3.3.6数据的审核与跟踪分析1）跟踪引用单元格（引用）2）跟踪从属单元格（被引用）3）数据的有效性4）圈释无效数据,3.4Excel图表,3.4.1图表的建立方法一：F11自动插入一张图表（自动显示简单柱形图表）方法二：工具栏图表方法三：插入图表(表格向导）1）柱形图的制作2）饼图的制作,3.4.2图表的编辑1）设置坐标、标题、图例等的格式2）改变图表尺寸3）移动图表4）复制图表5）删除图表6）添加数据标志,图表操作,图例,数据标记,类型轴(X),刻度线,值轴（Y）,图表相关名词术语,图表操作,1.创建图表选定要包含在统计图中的单元格数据。执行“插入”“图表”命令，或单击工具栏中的“图表向导”图标()从“图表向导(图表类型)”对话框中选择合适的图表类型，并单击“下一步”按钮。如果数据源不合可以在打开的对话框中修改。一般，简单地单击“下一步”按钮。在打开的对话框中设置图表的标题、坐标轴、网线、图例等。一般，简单地单击“下一步”按钮最后指定图表插入位置，并单击“完成”按钮。,图表操作,1.创建图表选定要包含在统计图中的单元格数据。执行“插入”“图表”命令，或单击工具栏中的“图表向导”图标()从“图表向导(图表类型)”对话框中选择合适的图表类型，并单击“下一步”按钮。如果数据源不合可以在打开的对话框中修改。一般，简单地单击“下一步”按钮。在打开的对话框中设置图表的标题、坐标轴、网线、图例等。一般，简单地单击“下一步”按钮最后指定图表插入位置，并单击“完成”按钮。,图表操作,1.创建图表选定要包含在统计图中的单元格数据。执行“插入”“图表”命令，或单击工具栏中的“图表向导”图标()从“图表向导(图表类型)”对话框中选择合适的图表类型，并单击“下一步”按钮。如果数据源不合可以在打开的对话框中修改。一般，简单地单击“下一步”按钮。在打开的对话框中设置图表的标题、坐标轴、网线、图例等。一般，简单地单击“下一步”按钮最后指定图表插入位置，并单击“完成”按钮。,图表操作,1.创建图表选定要包含在统计图中的单元格数据。执行“插入”“图表”命令，或单击工具栏中的“图表向导”图标()从“图表向导(图表类型)”对话框中选择合适的图表类型，并单击“下一步”按钮。如果数据源不合可以在打开的对话框中修改。一般，简单地单击“下一步”按钮。在打开的对话框中设置图表的标题、坐标轴、网线、图例等。一般，简单地单击“下一步”按钮最后指定图表插入位置，并单击“完成”按钮。,图表操作,2.图表的移动和大小的调整(1)移动图表单击要移动的图表（此时在选中的图表的四周将出现八个黑色的控点），用鼠标拖动它到一个新的位置，再松开鼠标即可。(2)改变图表的大小先选中要改变大小的图表，然后将鼠标指针移至图表的四周的任一控点上，当指针变成双箭头时，拖动鼠标直至图表变成满意的大小后松开鼠标。,图表操作,3.图表格式的编辑(1)修改图表的类型选中欲更改类型的图表执行“图表”“图表类型”命令在打开的对话框中选择一种合适的类型后单击“确定”按钮。,图表操作,3.图表格式的编辑(2)插入数据标志单击要添加数据标志的图表执行“图表”“图表选项”命令切换到“数据标志”标签选择需要的数据标志种类，单击“确定”按钮,数据标志,图表操作,3.图表格式的编辑(3)添加图表标题单击要添加标题的图表“图表”“图表选项”命令，切换到“标题”标签输入图表标题和X轴、Y轴、Z轴标后，单击“确定”按钮。,、图表操作,3.图表格式的编辑(4)改变图表的文字、颜色和图案双击需要修改的标题或者在选中标题后单击“格式”菜单中的“坐标轴标题”命令（打开“坐标轴标题格式”对话框）。,通过“字体”标签设置坐标轴标题文本格式，通过“图案”标签设置