日常费用与统计预测分析.ppt

日常费用与统计预测分析 赵江英 河北邢台农业学校 授课要求 1 点评上周作业情况 设计数据编码与名称 1新建一个工作簿，命名为日常费用与统计预测分析 2建工作表---费用系统编码设置 （1）以下内容填入 费用系统编码设置 （2）完成对部门名称、部门编码、费用类别、类别编码、部门主管列的名称定义，可定义为bm、 bm-code 、fy、 fy-code 、zg 创建第三季度费用预测表 1建新工作表-第三季度费用预测表 2录入表格列标题 3对部门名称列、部门主管列、费用类别列设置下拉序列 选定区域-数据有效性设置 在月份列设置数据有效性- 创建第三季度费用预测表 3部门编码列可以从费用编码设置工作表中取数 4类别编码列也是从费用编码设置工作表中取数 5根据要求填入金额列 创建第三季度实际费用表 1将第三季度费用预测表复制到新工作表 命名为第三季度实际费用表 2将“金额”中的数据改为实际发生额即可 分类汇总分析第三季度实际费用 1汇总第三季度各部门实际费用简单分类汇总用于对数据清单中的某一列进行排序 以“部门名称”分类汇总字段，按“金额”汇总 （1）默认情况下是以拼音字母的顺序进行排序，也可自定义顺序进行排序，如按“销售部、客户部、行政部、策划部、生产部”顺序进行排序 工具-选项自定义-序列 利用数据透视图表分析第三季度实际费用 1选定数据区域中任意单元格。 数据-数据透视表和数据透视图 利用数据透视图表分析第三季度实际费用 第三季度费用差异分析 1新建一个工作表，命名为第三季度费用差异分析 2 Index（）与match（）函数嵌套 Index（）函数 查找与引用函数 Index（）与match（）函数嵌套 match（）函数 查找与应用函数 MATCH()返回匹配值的位置而不是匹配值本身 MATCH(查找值，查找区域，查找模式) Index（）与match（）函数嵌套 第三季度费用差异分析 3表格内容输入 第三季度费用差异分析 第三季度费用差异分析 第三季度预测和实际费用差异分析 INDEX() 返回指定行列交叉处引用的单元 INDEX(要检索的范围，指定返回处的行序号，制定返回初的列序号， 返回该区域中行和列的交叉域) MATCH()返回匹配值的位置而不是匹配值本身 MATCH(查找值，查找区域，查找模式) 第三季度费用差异分析 第三季度费用差异分析 第三季度费用差异分析 第三季度费用差异分析 为数据区域设置 条件格式（为醒 目，设置了底纹 为黄色） 第三季度费用差异分析 第三季度费用差异分析 第三季度费用差异分析 第三季度费用差异分析 第三季度费用差异分析 第三季度费用差异分析 第三季度费用差异分析 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 费用系统编码设置 创建预测费用表 创建实际费用表 汇总各类别费用 汇总各部门费用 实际费用分析 差异图表分析 计算差异额与差异率 下季度预测费用 里面的内容均是自己填上的 注意引用的区域 最好简单易懂 绝对引用 打回车即可 或输入英文半角逗点 / 然后单击数据区域中的任意单元格，数据----排序 单击选项 嵌套分类汇总 分类字段：费用类别 汇总方式：求和 选定汇总项：金额 将该项去掉 注意 （ 2）分类汇总 注意选项 点开布局注意要将分类汇总去掉 注意重新命名工作表名 自己手工输入 数据有效性设置 所以，一定在定义名称时，定义易记的 Index（）与match（）函数嵌套重点内容 INDEX() 返回指定行列交叉处单元格的值 有三个参数 INDEX(要查找的区域，查找的特定值的行序号，查找的特定值的列序号) 自行输入 1.数据录入之前，先在第三季度实际费用工作表中 插入一列，部门编码费用编码月份 =C2F2D2 2.在第三季度实际费用工作表里A2到A70区域里， 行政部办公费7月（JS0015210017）,第三季度 实际费用工作表里G2到G70区域里所对应的值 =SUMIF(第三季度实际费用!$A$2:$A$70,$D$2C7$G$6,第三季度实际费用!$G$2:$G$70) 只是费用类别发生变化 =SUMIF(第三季度实际费用!$A$2:$A$70,$D$2$C$7G6,第三季度实际费用!$G$2:$G$70) 只是月份发生变化 / 注意单元格格式 1.在第三季度费用预测表里插入一列，部门编码费用编码 =SUMIF(第三季度费用预测!$A$2:$A$70,$D$2C7,第三季度费用预测!$G$2:$G$70) =IF(OR(M7=0,M16=0),无,M7/$M$16) If（）和