《办公软件案例教程 Office 2007版 教学课件 ppt 作者 赖利君 第4篇 物流篇》由会员分享，可在线阅读，更多相关《办公软件案例教程 Office 2007版 教学课件 ppt 作者 赖利君 第4篇 物流篇（33页珍藏版）》请在金锄头文库上搜索。
1、,第4篇 物流篇, 学习目标,1利用Excel创建公司的库存统计表，灵活设置各部分的格式。 2自定义数据格式。 3通过数据有效性的设置来控制录入符合规定的数据。 4学会合并多表数据，得到汇总结果。 5利用Excel制作公司产品进销存管理表、对产品销售和成本进行分析。 6在Excel中利用自动筛选和高级筛选实现显示满足条件的数据行。 7利用分类汇总来分类统计某些字段的汇总函数值。 8灵活地构造和使用图表来满足各种需要的数据结果的显示要求。 9能灵活使用条件格式突出显示数据结果。,主要案例,案例15 公司库存表的规范设计 案例16 制作产品进销存管理表 案例17 制作产品销售与成本分析,案例15
2、公司库存表的规范设计,案例分析,本案例通过制作“公司库存管理表”来介绍Excel软件在库存管理方面的应用，效果分别如图4.1、图4.2、图4.3、图4.4、图4.5和图4.6所示。,解决方案,（1）创建工作簿，重命名工作表。 启动Excel 2007，新建一个空白工作簿。 将创建的工作簿以“公司库存管理表”为名保存到“E:公司文档物流部”文件夹中。 将“公司库存管理表”工作簿中的Sheet1工作表重命名为“产品明细表”。 （2）创建“产品明细表”。 选中“产品明细表”工作表。 输入如图4.7所示的产品明细数据。,解决方案,（3）创建“第一仓库入库”表。 将Sheet2工作表重命名为“第一仓库入
3、库”。 在“第一仓库入库”工作表中创建表格框架，如图4.9所示。 输入“编号”。 a选中编号所在列A列，打开“单元格格式”菜单，选择【设置单元格格式】选项，打开“单元格格式”对话框。 b切换到“数字”选项卡，在左侧“分类”列表中选择“自定义”；在右侧类型中，自己输入自定义格式，如图4.10所示。 c选中A4单元格，输入为“1”后，单元格中显示的是“NO-1-0001” 。 d使用填充句柄自动填充其余的编号。,解决方案, 参照图4.12输入“日期”和“产品编号”数据。 输入“产品类别”数据。使用“VLOOKUP”函数后从“产品明细表”中查找相应的“产品类别”数据。 用同样的方式，参照图4.15设
4、置参数，输入“产品型号”数据。 输入入库“数量”数据。为保证输入的数量值均为正整数、不会出现其他数据，需要对这列进行数据有效性设置，设置完成后，进行数量数据的输入。 （4）创建“第二仓库入库”表。 将Sheet3工作表重命名为“第二仓库入库”。 参照创建“第一仓库入库”表的方法创建如图4.2所示的“第二仓库入库”表。,解决方案,（5）创建“第一仓库出库”表。 在“第二仓库入库”表之后插入一张新的工作表，并将新工作表重命名为“第一仓库出库”。 参照创建“第一仓库入库”表的方法创建如图4.3所示的“第一仓库出库”表。 （6）创建“第二仓库出库”表。 在“第一仓库出库”表之后插入一张新的工作表，并将
5、新工作表重命名为“第二仓库出库”。 参照创建“第一仓库入库”表的方法创建如图4.4所示的“第二仓库出库”表。 （7）创建“入库汇总表”。采用“合并计算”来完成汇总出所有仓库中各种产品的入库数据。 在“第二仓库入库”表之后插入一张新的工作表，并将新工作表重命名为“入库汇总表”。,解决方案, 选中A1单元格，将合并计算的结果从这个单元格开始填列。 打开 “合并计算”对话框。 在“函数”下拉列表框中选择合并的方式“求和”。 添加第一引用位置区域。 添加第二引用位置区域。 选中“标签位置”中的【首行】和【最左列】选项，单击【确定】按钮，完成合并计算，得到如图4.25所示的结果。,解决方案, 调整表格。
6、将合并后不需要的“产品类别”和“产品型号”列删除，将“产品编号”标题添上，在适当调整列宽后得到最终效果，如图4.5所示。 （8）创建“出库汇总表”。 采用创建“入库汇总表”的方法，在“第二仓库出库”表之后插入一张新的工作表，并将新工作表重命名为“出库汇总表”，汇总出所有仓库中各种产品的出库数据。结果如图4.6所示。,拓展案例,材料采购明细表，如图4.26所示。,拓展训练,制作一份公司出货明细单，效果图如图4.27所示。在这个明细表中，会涉及数据有效性设置、自定义序列来构造下拉列表进行数据输入、自动筛选数据、冻结网格线等操作。,案例小结,本案例通过制作“公司库存管理表”，主要介绍了工作簿的创建、
7、工作表重命名，自动填充、有效性设置、使用VLOOKUP函数导入数据。在此基础上，使用“合并计算”对多个仓库的出、入库数据进行汇总统计。,案例16 制作产品进销存管理表,案例分析,在一个经营性企业中，物流部门的基本业务流程就是产品的进销存管理过程，产品的进货、销售和库存的各个环节直接影响到企业的发展。 对企业的进销存实行信息化管理，不仅可以实现数据之间的共享，保证数据的正确性，还可以实现对数据的全面汇总和分析，促进企业的快速发展。本案例通过制作“产品进销存管理表”来介绍Excel软件在进销存管理方面的应用。产品进销存管理表的设计效果图如 图4.37。,解决方案,（1）创建工作簿。 启动Excel
8、 2007，新建一个空白工作簿。 将创建的工作簿以“产品进销存汇总表”为名保存在“E:公司文档物流部”文件夹中。 （2）复制工作表。 打开“公司库存管理表”工作簿。 选中“产品明细表”、“入库汇总表”和“出库汇总表”工作表。 打开“移动或复制工作表”对话框。 从“工作簿”的下拉列表中选择“产品进销存汇总表”工作簿，在“下列选定工作表之前”中选择“Sheet1”工作表，再选中“建立副本”选项。,解决方案, 单击【确定】按钮，将选定的工作表“产品明细表”、“入库汇总表”和“出库汇总表”复制到“产品进销存汇总表”工作簿中。 （3）创建“进销存汇总表”工作表框架。 将“Sheet1”工作表重命名为“进
9、销存汇总表”。 建立如图4.41所示的“进销存汇总表”框架。 （4）输入和计算“进销存汇总表”工作表中的数据。 计算“期初库存额”。这里，期初库存额 = 期初库存量 进价。 导入“本月入库量”。这里，本月入库量为本月入库汇总表中的数量。,解决方案, 计算“本月入库额”。这里，本月入库额 = 本月入库量进价。 导入“本月销售量”。这里，本月销售量为本月出库汇总表中的数量。 计算“本月销售额”。这里，本月销售额 = 本月销售量售价。 计算“期末库存量”。这里，期末库存量 = 期初库存量+本月入库量-本月销售量。 计算“期末库存额”。这里，期末库存额 = 期末库存量进价。,解决方案,（5）格式化“进
10、销存汇总表”。 将表格标题进行合并及居中，字体设置为宋体、18磅、加粗，设置行高为30。 将表格标题字段设置为加粗、居中，并将字体设置为白色，添加“橄榄色，强调文字颜色3，深色25%”的底纹。 为表格添加内细外粗的边框。 将“单位”、“期初库存量”、“本月入库量”、“本月销售量”和“期末库存量”的数据设置为居中对齐。 将“期初库存额”、“本月入库额”、“本月销售额”和“期末库存额”的数据设置“会计专用”格式，且无“货币符号”和“小数位数”。 （6）对“期末库存量”设置条件格式。 为了更方便地了解库存信息，我们可以为相应的期末库存量设置条件格式，根据不同库存量等级设置不同的底纹颜色。如，期末库存
11、量低于10以黄色显示、库存量高于50用橙色显示，正常时显示为绿色。,拓展案例,公司产品生产成本预算表设计，如图4.51和图4.52所示。,拓展训练,设计一份科源有限公司的生产预算表，如图4.53所示。,案例小结,本案例通过制作“产品进销存管理表”，主要介绍了工作簿的创建、工作簿之间工作表的复制、工作表重命名，使用VLOOKUP函数导入数据，工作表间数据的引用以及公式的使用。在此基础上，利用“条件格式”对表中的数据进行分层次显示，以方便合理地入库管理。,案例17 制作产品销售与成本分析,案例分析,在企业的经营管理过程中，成本的管理和控制成为关注的焦点。科学分析企业的各项成本构成及影响利润的关键要
12、素，了解公司的成本构架和盈利情况，有利于把握正确的决策方向，实现有效的成本控制。 物流管理部门在产品进销存的管理过程中，通过分析产品的存货量、平均采购价格以及存货占用资金，可对产品的销售和成本进行分析，从而为产品的库存管理提供决策支持。本案例通过制作“产品销售与成本分析”来介绍Excel软件在成本控制方面的应用。效果如图4.70、图4.71和图4.72所示。,解决方案,（1）创建工作簿。 启动Excel 2007，新建一个空白工作簿。 将新建的工作簿以“产品销售与成本分析”为名保存在“E:公司文档物流部”文件夹中。 （2）复制工作表。 打开“产品进销存管理表”工作簿。 选中“进销存汇总表”工作
13、表。 打开“移动或复制工作表”对话框。 从“工作簿”的下拉列表中选择“产品销售与成本分析”工作簿，在“下列选定工作表之前”中选择“Sheet1”工作表，再选中“建立副本”选项。 单击【确定】按钮，将选定的工作表“进销存汇总表”复制到“产品销售与成本分析”工作簿中。,解决方案,（3）创建“产品销售与成本分析”工作表框架。 将“Sheet1”工作表重命名为“销售与成本分析”。 建立如图4.74所示的“销售与成本分析”表框架。 （4）计算“存货数量”。这里，存货数量 = 入库数量-销售数量。 （5）计算“加权平均采购价格”。这里，我们设定“加权平均采购价格 = 入库金额/入库数量”。 （6）计算“存
14、货占用资金”。这里，存货占用资金 = 存货数量*加权平均采购价格。 （7）计算“销售成本”。这里，销售成本 = 销售数量*加权平均采购价格。,解决方案,（8）导入“销售收入”数据。 这里，销售收入 = 销售金额。使用“VLOOKUP”函数，查找“销售金额”数据。 （9）计算“销售毛利”。这里，销售毛利 = 销售收入-销售成本。 （10）计算“销售成本率”。这里，销售成本率 = 销售成本/销售收入。 （11）格式化“产品销售与成本分析”表。 设置表格标题格式。将表格标题进行合并及居中，字体设置为宋体、20磅、加粗，行高35。,解决方案, 将表格标题字段的字设置为加粗、居中，添加“蓝色 强调文字颜
15、色1，深色25%”底纹，并将字体颜色设置为“白色”，并设置行高为20。 为表格添加内细外粗的边框。 将“加权平均采购价格”、“存货占用资金”、“销售成本”、“销售收入”和“销售毛利”的数据设置“会计专用”格式，且无“货币符号”和“小数位数”。 将“销售成本率”数据设置为百分比格式，1位小数。 （12）复制“产品销售与成本分析”工作表。将“产品销售与成本分析”工作表复制两份，并分别重命名为“销售毛利分析”和“占用资金少或销售成本低的产品”。,解决方案,（13）通过分类汇总分析各类产品的销售与成本情况。 选中“销售毛利分析”工作表。 按“产品类别”对各项数据进行汇总计算。 单击按钮 ，仅显示汇总数
16、据。 （14）利用分类汇总结果制作各类产品的销售毛利分析图 （15）筛选出“占用资金少或销售成本低”产品。使用高级筛选，从数据表中筛选出没有存货占用资金或销售成本率在80%的产品。,拓展案例,按图4.83所示，制作公司5月份的材料成本对比表，对比本期单位消耗材料与上期单位消耗材料的变化，以及上年单位成本乘以本期产量与本期单位成本乘以本期产量的变化，以真实地反映总成本水平的变化。,拓展训练,设计一份公司材料采购分析表格，如图4.84所示。,案例小结,本案例通过制作“产品销售与成本分析”，主要介绍了工作簿的创建、工作簿之间工作表的复制、工作表重命名，使用VLOOKUP函数导入数据，工作表间数据的引用以及公式的使用。在此基础上，利用分类汇总、图表、高级筛选等方法，分别从不同的侧重点对产品的销售与成
