1、2018/7/20,Excel在财务管理中的应用培训,上海财经大学,2,2018/7/20,2,Excel在财务管理中的应用,一、EXCEL的自定义及其灵活使用二、EXCEL的数据分类汇总,3,2018/7/20,3,一、EXCEL的自定义及其灵活使用知识要点,工作表的插入、重命名、隐藏数据的复制粘贴、选择性粘贴部分数据的隐藏数据内容删除、单元格删除、行列删除等数据的快速求和文本函数，对文本类型数据的处理,4,2018/7/20,4,一、EXCEL的自定义及其灵活使用知识要点,数据的排序Excel的自动汇总选定可见单元格按钮，以及对汇总数据的作用查找与替换、单元格与数值格式设置百分比数据的计算
2、图形绘制柱形图绘制，柱形图上添加折线饼图绘制微调器窗体的制作窗体对折线的控制,5,2018/7/20,5,一、EXCEL的自定义及其灵活使用知识要点,Excel数据的打印整张工作表的打印选中数据的打印特殊格式的打印（带网格线的打印、带行号和列标的打印）打印的预览Excel数据复制粘贴到Word文档粘贴、选择性粘贴粘贴链接复制图片，粘贴图片屏幕的复制当前窗口的复制,6,2018/7/20,6,二、EXCEL的数据分类汇总,利用数据透视表的汇总功能汇总销售数据数据透视表结构的灵活性(添加字段、删除字段、修改字段、字段的旋转、字段求和与统计次数）数据透视图的灵活性(在图中添加字段、删除字段、修改字段
3、、字段的旋转等）导入外部数据并汇总利用数据透视表生成时间序列，预测销售收入统计不同规模销量的发生次数及频率分布计算占同列数据总和的百分比，分析客户贡献Excel数据清单功能*,7,2018/7/20,7,培训特点,从具体问题出发，围绕例题，使学院不仅了解Excel的函数与菜单功能，而且能够掌握运用所学函数、功能解决财务管理实践中所面临的问题的能力。,8,2018/7/20,8,上课要求,请尽量跟上进度如有任何问题，可以示意老师两个人坐在相邻位置中间休息20分钟,2018/7/20,EXCEL的自定义及其灵活使用,Excel在财务管理中的应用培训,10,成教院培训, 2018/7/20,10,主
4、要知识要点,工作表的插入、重命名、隐藏数据的复制粘贴、选择性粘贴部分数据的隐藏数据内容删除、单元格删除、行列删除等数据的快速求和文本函数的使用，对文本类型数据的处理,11,成教院培训, 2018/7/20,11,主要知识要点,数据的排序Excel的自动汇总选定可见单元格按钮，以及对汇总数据的作用查找与替换、单元格与数值格式设置百分比数据的计算图形绘制柱形图绘制，柱形图上添加折线微调器窗体的制作窗体对折线的控制,12,成教院培训, 2018/7/20,12,主要知识要点,Excel数据的打印整张工作表的打印选中数据的打印特殊格式的打印（带网格线的打印、带行号和列标的打印）打印的预览,13,成教院
5、培训, 2018/7/20,13,主要知识要点,Excel数据复制粘贴到Word文档粘贴、选择性粘贴粘贴链接复制图片，粘贴图片屏幕的复制当前窗口的复制,14,成教院培训, 2018/7/20,14,例题:上海财经大学招生各省学生百分比分析,从因特网上获得数据在Excel中加以处理将处理结果组织成Word文档,15,成教院培训, 2018/7/20,15,例题:上海财经大学招生各省学生百分比分析,通过网上查询资料，制作一份2009年上海财经大学上海生源招生比例的分析报告,16,成教院培训, 2018/7/20,16,网上查找2009年招生数据,从财大校园网查找我校2009年招生计划各省市招生计划
6、上海招生计划财大主页-招生信息-本科生招生-招生计划将招生数据复制粘贴到Excel文档,17,成教院培训, 2018/7/20,17,工作表的重命名和插入新工作表,将保存各省和上海市数据的工作表重新命名插入新的工作表,18,成教院培训, 2018/7/20,18,改造各省市招生计划数数据列表格式,调整各省市招生计划数数据列表格式文本函数的应用统一上海生源和外地生源的“专业名称”利用left和Find函数将外地招生计划中专业名称的“（文）”或“（理）”去掉,19,成教院培训, 2018/7/20,19,利用分类汇总功能获得文理科总人数,将数据复制到一张“各省招生计划总人数分类汇总”工作表按照专业
7、名称进行排序按照专业名称对外地生人数进行汇总设定分类汇总的浏览级别为第二层，选中数据区，单击选定可见单元格按钮（选择菜单：视图工具栏自定义类别编辑选定可见单元格，拖到工具栏），单击复制，粘贴到“上海生源百分比”工作表,20,成教院培训, 2018/7/20,20,利用分类汇总功能获得文理科总人数,复制“上海招生计划”表中的数据至“上海生源百分比”工作表按照“专业名称”进行排序。计算外地生源和上海生源的百分比。绘制图形,21,成教院培训, 2018/7/20,21,2009年上海财经大学各专业上海生源比例图形,22,成教院培训, 2018/7/20,22,制作带参考比例下限的图形,设定L2为上海
8、生源比例参考线在I3：I35填加一列代表上海生源比例参考线的数据。选中图表，添加新系列，将I3:I35加为新系列选中新系列，右击鼠标，选择图表类型，改为折线图。,23,成教院培训, 2018/7/20,23,制作带参考比例下限的图形,制做微调项（视图-工具栏-窗体），链接单元格指定为M。L2:=M2/100制作可调面板,24,成教院培训, 2018/7/20,24,Excel数据的打印,Excel数据的打印整张工作表的打印选中数据的打印特殊格式的打印（带网格线的打印、带行号和列标的打印）打印的预览,25,成教院培训, 2018/7/20,25,编写word 文档,初步编写word 文档插入图片
9、插入页眉页脚插入题注,26,成教院培训, 2018/7/20,26,复制粘贴数据到Word文档、图片,复制Excel区域或对象：选中区域或对象复制，粘贴（或者粘贴链接）到word文档截取屏幕图片：print screen, alt-print screen, 粘贴到word文档（如果需要可以先利用画图软件进行裁剪）复制Excel图片：按住ctrl，点“编辑复制图片如打印效果”，粘贴到word文档,27,成教院培训, 2018/7/20,27,插入题注,插入，题注引用时，插入，交叉引用,28,成教院培训, 2018/7/20,28,建立样式体系与模板,在编写文档时，规定了页眉页脚、页面设置，并且
10、新建和修改了很多样式，这些样式成为一个体系，我们可以把这些保存在一个模板文件里，在以后可以使用。,29,成教院培训, 2018/7/20,29,作业,网上寻找数据与工作有关的待分析数据例如产品价格比较分析股票价格波动比较分析市场占有率变化比较分析等利用Excel整理数据、绘制图形编写Word文档,二、EXCEL数据分类汇总分析,主要知识要点,一、利用数据透视表的汇总功能汇总销售数据二、数据透视表结构的灵活性(添加字段、删除字段、修改字段、字段的旋转、字段求和与统计次数）三、数据透视图的灵活性(在图中添加字段、删除字段、修改字段、字段的旋转等）四、导入外部数据并汇总五、利用数据透视表生成时间序列
11、，预测销售收入六、统计不同规模销量的发生次数及频率分布七、计算占同列数据总和的百分比，分析客户贡献八、 Excel数据清单功能*,数据的分类汇总分析,企业需要从保存业务活动数据的数据库中获得满足特定条件的数据数据往往是具体的、零散的通过查询工具Microsoft Query可以获得这些数据汇总的数据发现生产和经营活动的规律、未来的发展趋势等 数据的分类汇总分析,数据分类汇总分析意义和作用,获得销售额分类统计值,数据分类汇总分析意义和作用,获得各类销售额排行榜,数据分类汇总分析意义和作用,获得各类销售额的时间序列,数据分类汇总分析意义和作用,获得各经济量之间的相关性,数据分类汇总分析意义和作用,
12、获得各种产品需求量的频率分布,一、数据透视表的基本功能,数据分类汇总的方法数据透视表数据清单功能*DSUM函数和控件*,一、数据透视表的汇总功能,汇总字段 ：待汇总的变量或字段。销售额、销售数量、工作时间等。分类字段 ：对数据进行汇总时，以某个变量或字段的不同值为参考来对汇总字段进行汇总。参考不同产品类别 (分类字段) 汇总销售额（汇总字段）参考不同的生产人员姓名（分类字段）汇总工作时间 （汇总字段）,一、数据透视表的汇总功能,数据透视表分类汇总的方法对当前工作簿的数据进行汇总先将数据导入Excel成为数据清单，利用数据透视表汇总对数据清单进行汇总直接导入,并汇总利用query从数据库查询导入
13、，并汇总利用数据透视表直接从数据库中查询、并汇总数据,一、数据透视表的汇总功能,【例3-02】利用当前工作簿ABC公司1995年的销售数据，使用Excel数据透视表功能制作如图所示的分类汇总表。,一、数据透视表的汇总功能,数据透视表分类汇总的方法对当前工作簿的数据进行汇总先将数据导入Excel成为数据清单，利用数据透视表汇总对数据清单进行汇总直接导入,并汇总利用query数据库查询导入，并汇总利用数据透视表直接从数据库中查询、并汇总数据,一、数据透视表的汇总功能,数据清单数据清单是包含相关数据的一系列工作表数据行数据清单的首行为字段名，首行下的各行是各个记录，数据清单中不能出现空行直接导入数据
14、，并汇总Excel菜单，数据导入外部数据导入数据,一、数据透视表的汇总功能,【例3-03】利用ABC公司1995年的销售数据，利用Excel导入数据功能和数据透视表功能，制作如图所示的分类汇总表。导入外部数据Excel菜单，数据导入外部数据导入数据利用数据透视表汇总数据,一、数据透视表的汇总功能,一、数据透视表的汇总功能,二、数据透视表结构的灵活性,改变数据透视表的行列结构利用页域对数据进行分页汇总新增、删除和修改行域、列域或页域字段（分类字段）新增、删除和修改汇总字段改变汇总字段的汇总方式同一个字段既作分类字段又作汇总字段,二、数据透视表结构的灵活性,改变分类字段值的位置对分类字段的值加以合
15、并隐藏行域、列域的字段值展开汇总值，显示详细数据,三、数据透视图的灵活性,变换分类字段的位置将行、列分类字段换成页分类字段新增、删除分类字段新增、删除汇总字段改变被汇总方式显示分类字段的部分值,四、利用Query导入数据并汇总,五、利用数据透视表生成时间序列,时间序列:按照一定时间间隔汇总的数据序列【例3-05】利用数据透视表，对Northwind Trader公司的销售数据，按月汇总种各产品销售额,并绘制图表,对未来的销售情况加以预测。,五、利用数据透视表生成时间序列,五、利用数据透视表生成时间序列,六、统计不同规模销量的发生次数以及频率分布,【例3-6】利用数据透视表，对Northwind
16、公司的销售数据按照不同产品、不同时间段，统计不同规模销售数量的发生次数，并计算其频率分布。,六、统计不同规模销量的发生次数以及频率分布,六、统计不同规模销量的发生次数以及频率分布,七、计算占同列数据总和的百分比,【例3-7】Northwind Trader公司根据客户总销售额将客户划分为不同级别，20000元以上为黄金客户，10000元20000元为重要客户， 10000以下为普通客户。按照客户的不同级别汇总各级客户的总销售额、销售额占总销售额的百分比，以及该级客户销售次数占总销售次数的比重，如图3- 69所示，并绘制数据透视图。,七、计算占同列数据总和的百分比,八、 Excel数据清单功能*
17、,数据清单的基本功能排序筛选分类总计,八、 Excel数据清单功能*,利用Excel数据清单处理功能进行分类汇总的一般步骤：获得数据清单；设定筛选条件；筛选数据，将筛选结果复制到工作表其他位置；对经过筛选的数据按分类字段进行排序；对排序后的数据进行分类汇总。,八、 Excel数据清单功能*,【例3-01】利用Excel的数据清单功能，将ABC公司的销售数据按照图3-7的形式，汇总出1995年不同省份、不同类别商品的净销售额总计值。,九、D函数和模拟运算表*,D函数模拟运算表生成时间序列统计不同规模销量的发生次数以及概率分布,D函数*,D函数：对数据清单或数据库中的数据进行分析的 12个数据库内
18、建函数。常用的D函数包括：DSUM()DAVERAGE()DCOUNT()DCOUNTA(),D函数*,D函数名称(数据清单,统计字段,条件范围) 数据清单：包含相关数据的工作表中的一系列数据行统计字段：汇总字段。可以使用字段名、字段名所在的单元格、或者字段在数据清单中所在列的序号。条件范围：规定函数的匹配条件的一个工作表区域。D函数只对满足该条件范围所规定条件的记录进行汇总。,D函数*,【例3-8】利用ABC公司的销售数据，应用DSUM()函数，生成一个与图3-7相同的数据分类汇总表，列出ABC公司不同省份不同类别净销售额总计值。,D函数*,模拟运算表*,模拟运算表用于计算一系列自变量给定值
19、对应的函数值。自动计算函数值。一维模拟运算表，计算一个自变量的一系列给定值的一个或若干个函数的函数值。二维模拟运算表，计算两个自变量的系列给定值的一个函数的函数值。,模拟运算表*,【例3-9】在例3-8的基础上，利用DSUM()函数和模拟运算表计算ABC公司不同省份不同类别净销售额总计值。,模拟运算表*,模拟运算表*,补充部分：数据合并,营业1部销售数据,营业2部销售数据,营业3部销售数据,3个营业部数据的合并,二、EXCEL数据分类汇总分析,主要知识要点,一、利用数据透视表的汇总功能汇总销售数据二、数据透视表结构的灵活性(添加字段、删除字段、修改字段、字段的旋转、字段求和与统计次数）三、数据
20、透视图的灵活性(在图中添加字段、删除字段、修改字段、字段的旋转等）四、导入外部数据并汇总五、利用数据透视表生成时间序列，预测销售收入六、统计不同规模销量的发生次数及频率分布七、计算占同列数据总和的百分比，分析客户贡献八、 Excel数据清单功能*,数据的分类汇总分析,企业需要从保存业务活动数据的数据库中获得满足特定条件的数据数据往往是具体的、零散的通过查询工具Microsoft Query可以获得这些数据汇总的数据发现生产和经营活动的规律、未来的发展趋势等 数据的分类汇总分析,数据分类汇总分析意义和作用,获得销售额分类统计值,数据分类汇总分析意义和作用,获得各类销售额排行榜,数据分类汇总分析意
21、义和作用,获得各类销售额的时间序列,数据分类汇总分析意义和作用,获得各经济量之间的相关性,数据分类汇总分析意义和作用,获得各种产品需求量的频率分布,一、数据透视表的基本功能,数据分类汇总的方法数据透视表数据清单功能*DSUM函数和控件*,一、数据透视表的汇总功能,汇总字段 ：待汇总的变量或字段。销售额、销售数量、工作时间等。分类字段 ：对数据进行汇总时，以某个变量或字段的不同值为参考来对汇总字段进行汇总。参考不同产品类别 (分类字段) 汇总销售额（汇总字段）参考不同的生产人员姓名（分类字段）汇总工作时间 （汇总字段）,一、数据透视表的汇总功能,数据透视表分类汇总的方法对当前工作簿的数据进行汇总
22、先将数据导入Excel成为数据清单，利用数据透视表汇总对数据清单进行汇总直接导入,并汇总利用query从数据库查询导入，并汇总利用数据透视表直接从数据库中查询、并汇总数据,一、数据透视表的汇总功能,【例3-02】利用当前工作簿ABC公司1995年的销售数据，使用Excel数据透视表功能制作如图所示的分类汇总表。,一、数据透视表的汇总功能,数据透视表分类汇总的方法对当前工作簿的数据进行汇总先将数据导入Excel成为数据清单，利用数据透视表汇总对数据清单进行汇总直接导入,并汇总利用query数据库查询导入，并汇总利用数据透视表直接从数据库中查询、并汇总数据,一、数据透视表的汇总功能,数据清单数据清
23、单是包含相关数据的一系列工作表数据行数据清单的首行为字段名，首行下的各行是各个记录，数据清单中不能出现空行直接导入数据，并汇总Excel菜单，数据导入外部数据导入数据,一、数据透视表的汇总功能,【例3-03】利用ABC公司1995年的销售数据，利用Excel导入数据功能和数据透视表功能，制作如图所示的分类汇总表。导入外部数据Excel菜单，数据导入外部数据导入数据利用数据透视表汇总数据,一、数据透视表的汇总功能,一、数据透视表的汇总功能,二、数据透视表结构的灵活性,改变数据透视表的行列结构利用页域对数据进行分页汇总新增、删除和修改行域、列域或页域字段（分类字段）新增、删除和修改汇总字段改变汇总
24、字段的汇总方式同一个字段既作分类字段又作汇总字段,二、数据透视表结构的灵活性,改变分类字段值的位置对分类字段的值加以合并隐藏行域、列域的字段值展开汇总值，显示详细数据,三、数据透视图的灵活性,变换分类字段的位置将行、列分类字段换成页分类字段新增、删除分类字段新增、删除汇总字段改变被汇总方式显示分类字段的部分值,数据透视表的基本功能,数据透视表分类汇总的方法对当前工作簿的数据进行汇总先将数据导入Excel成为数据清单，利用数据透视表汇总对数据清单进行汇总直接导入,并汇总利用query数据库查询导入，并汇总利用数据透视表直接从数据库中查询、并汇总数据,一、数据透视表的基本功能,利用query数据库
25、查询导入，并汇总建立ODBC数据源通过Excel导入外部数据功能，启动QUERY软件利用QUERY软件查询所需数据将数据返回Excel利用数据透视表进行汇总,四、利用Query导入数据并汇总,ODBC数据源Microsoft ODBC体系结构,四、利用Query导入数据并汇总,建立ODBC数据源一个ODBC数据源包括下面三方面的内容：数据源名字系统将根据该名字建立一个扩展名为.DSN的文件，以存放数据库连接信息。驱动程序名称选择一个具体的数据库驱动程序，该驱动程序应与数据源所要引用的数据库的类型相匹配。引用Access数据库，选择Microsoft Access驱动程序引用Foxpro数据库，
26、选择Microsoft Visual FoxPro 驱动程序数据库连接信息给出所要连接的数据库的文件名或文件夹。,四、利用Query导入数据并汇总,例3-04，利用Northwind sales data.xls中的数据制作如下的数据透视表,四、利用Query导入数据并汇总,Excel菜单选择“数据导入外部数据新建数据库查询新数据源确定,四、利用Query导入数据并汇总,起名为nw01驱动程序为Microsoft Excel Driver,四、利用Query导入数据并汇总,单击“连接选择工作簿”选中d:第1讲 数据分类汇总分析northwind sales data.xls文件,四、利用Que
27、ry导入数据并汇总,单击“确定确定确定”选择数据源nw01,单击“确定”,四、利用Query导入数据并汇总,选择产品和订单明细表中如下字段,四、利用Query导入数据并汇总,选择“下一步下一步”“完成”,四、利用Query导入数据并汇总,进入Query查询数据,四、利用Query导入数据并汇总,进入Query查询数据，建立两个表之间的联系,四、利用Query导入数据并汇总,选择“文件”，“将数据返回Microsoft Offixce Excel”,四、利用Query导入数据并汇总,数据返回Excel成为数据清单,四、利用Query导入数据并汇总,制作数据透视表,五、利用数据透视表生成时间序列,
28、时间序列:按照一定时间间隔汇总的数据序列【例3-05】利用数据透视表，对Northwind Trader公司的销售数据，按月汇总种各产品销售额,并绘制图表,对未来的销售情况加以预测。,五、利用数据透视表生成时间序列,五、利用数据透视表生成时间序列,六、统计不同规模销量的发生次数以及频率分布,【例3-6】利用数据透视表，对Northwind公司的销售数据按照不同产品、不同时间段，统计不同规模销售数量的发生次数，并计算其频率分布。,六、统计不同规模销量的发生次数以及频率分布,六、统计不同规模销量的发生次数以及频率分布,七、计算占同列数据总和的百分比,【例3-7】Northwind Trader公司
29、根据客户总销售额将客户划分为不同级别，20000元以上为黄金客户，10000元20000元为重要客户， 10000以下为普通客户。按照客户的不同级别汇总各级客户的总销售额、销售额占总销售额的百分比，以及该级客户销售次数占总销售次数的比重，如图3- 69所示，并绘制数据透视图。,七、计算占同列数据总和的百分比,八、 Excel数据清单功能*,数据清单的基本功能排序筛选分类总计,八、 Excel数据清单功能*,利用Excel数据清单处理功能进行分类汇总的一般步骤：获得数据清单；设定筛选条件；筛选数据，将筛选结果复制到工作表其他位置；对经过筛选的数据按分类字段进行排序；对排序后的数据进行分类汇总。,
30、八、 Excel数据清单功能*,【例3-01】利用Excel的数据清单功能，将ABC公司的销售数据按照图3-7的形式，汇总出1995年不同省份、不同类别商品的净销售额总计值。,九、D函数和模拟运算表*,D函数模拟运算表生成时间序列统计不同规模销量的发生次数以及概率分布,D函数*,D函数：对数据清单或数据库中的数据进行分析的 12个数据库内建函数。常用的D函数包括：DSUM()DAVERAGE()DCOUNT()DCOUNTA(),D函数*,D函数名称(数据清单,统计字段,条件范围) 数据清单：包含相关数据的工作表中的一系列数据行统计字段：汇总字段。可以使用字段名、字段名所在的单元格、或者字段在
31、数据清单中所在列的序号。条件范围：规定函数的匹配条件的一个工作表区域。D函数只对满足该条件范围所规定条件的记录进行汇总。,D函数*,【例3-8】利用ABC公司的销售数据，应用DSUM()函数，生成一个与图3-7相同的数据分类汇总表，列出ABC公司不同省份不同类别净销售额总计值。,D函数*,模拟运算表*,模拟运算表用于计算一系列自变量给定值对应的函数值。自动计算函数值。一维模拟运算表，计算一个自变量的一系列给定值的一个或若干个函数的函数值。二维模拟运算表，计算两个自变量的系列给定值的一个函数的函数值。,模拟运算表*,【例3-9】在例3-8的基础上，利用DSUM()函数和模拟运算表计算ABC公司不同省份不同类别净销售额总计值。,模拟运算表*,模拟运算表*,补充部分：数据合并,营业1部销售数据,营业2部销售数据,营业3部销售数据,3个营业部数据的合并,
