上财 EXCEL在财务管理中的应用培训.ppt

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