Excel在财务中的应用 第3版 课件 项目六 Excel在存货管理中的应用

项目六Excel在存货管理中的应用项目一

任务一采购与成品入库业务处理任务二销售与车间领料业务处理任务三库存管理任务四存货明细账任务五制定薪金等级知识目标和能力目标知识目标：1.掌握进销存的业务处理流程。2.了解进销存管理中数据之间的关系。能力目标：1.学会使用Excel设计进销存管理系统。2.掌握工作表数据之间的操作。工作任务分解及操作1.进货流程：采购员接到缺货信息后，分析缺货信息是否合理，再将订单下达给供应商；材料送达后，实物入库，根据入库单登记库存账。2.销售流程：接收客户订单，签约销售合同，向客户发货并收款；每笔销售业务发生时都要及时更新库存。3.库存管理流程：材料采购入库、产品完工入库、领料退货等均是涉及库存变化的业务，均需在进出时及时记录并处理。o1采购与成品入库业务处理任务一采购与成品入库业务处理1.建立基础信息表名称引用位置范围供应商名称=OFFSET(基础信息表!$A$2,0,0,COUNTA(基础信息表!$A:$A)-1)工作簿客户名称=OFFSET(基础信息表!$B$2,0,0,COUNTA(基础信息表!$B:$B)-1)工作簿原材料=OFFSET(基础信息表!$D$2,0,0,COUNTA(基础信息表!$D:$D)-1)工作簿库存商品=OFFSET(基础信息表!$I$2,0,0,COUNTA(基础信息表!$I:$I)-1)工作簿定义名称为了方便输入并防止输入错误，可以进行“数据验证”设置。数据验证2.编制“采购业务表”

单元格E4，输入“=VLOOKUP(D4,基础信息表!$D:$G,2,0)”，则E列将返回存货对应的单位。

单元格F4，输入“=VLOOKUP(D4,基础信息表!$D:$G,3,0)”，则F列将返回存货对应的类别。

单元格G4，输入“=VLOOKUP(D4,基础信息表!$D:$G,4,0)”，则G列将返回存货对应的规格型号。完成采购业务明细表应付总额：N4=L4+M4其中：存货成本：L4=I4*J4+K4分析采购数据1）按供货商查看知识技能一：分类汇总1.显示或隐藏明细数据（1）运用任务窗格的加减号和级别号知识技能一：分类汇总1.显示或隐藏明细数据（1）运用任务窗格的加减号和级别号知识技能一：分类汇总1.显示或隐藏明细数据（2）运用功能区选项卡中的按钮知识技能一：分类汇总2.编辑或清除分类汇总

一个分类汇总虽然可以对多列进行计算，但同时只能按一个字段汇总并执行一种计算。知识技能一：分类汇总

在【汇总方式】下除了常规求和外，还可以求平均值、最大/最小值等；现以“计数”为例。知识技能一：分类汇总2.编辑或清除分类汇总

如果不再需要此分类汇总，可将其删除。

需要说明的是，分类汇总一旦执行全部删除，Excel无法执行“撤销键入”操作，也就意味着无法回到分类汇总前的某项操作，实际工作中请注意数据的保存和备份。知识技能二：SUBTOTAL函数

通常，使用“分类汇总”命令创建的列表，可自动生成SUBTOTAL函数。当然，也可直接使用SUBTOTAL函数完成相关数据的汇总。

语法格式：SUBTOTAL(function_num,ref1,[ref2],...])。

function_num：可以指定1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，用于指定使用何种函数在列表中进行分类汇总计算。

ref：要进行分类汇总计算的命名区域或引用。function_num（包含隐藏值）function_num（忽略隐藏值）对应函数1101AVERAGE（算数平均值）2102COUNT（计算包含数字的单元格个数）3103COUNTA（计算不为空单元格的个数）4104MAX（一组值中的最大值）5105MIN（一组值中的最小值）6106PRODUCT（乘积）7107STDEV（估计基于样本的标准偏差）8108STDEVP（整个样本总体的标准偏差）9109SUM（求和）10110VAR(基于给定样本的方差)11111VARP(基于整个样本总体的方差)知识技能二：SUBTOTAL函数关于隐藏值的说明

实际运用SUBTOTAL函数时，并不需要死记硬背每个数字具体对应什么函数，当单元格输入“=SUBTOTAL(”后，Excel会给出参数供选择关于隐藏值的说明2）按存货名称查看分析采购数据先手动将主分类字段进行排序，Excel才会按此分类执行汇总。2）按存货名称查看分析采购数据知识技能三：排序

排序，是指按照指定的顺序将数据重新排列组织，是数据整理的一种重要手段。通常，数据排序要求每列中的数据类型相同，而且不允许有空行或空列，也不能有合并的单元格。

1.排序操作（1）单列排序与多列排序（2）按颜色排序（3）自定义排序

1.排序操作2.SORT函数含义：SORT函数可对某个区域范围或数组的内容进行排序。语法格式为：SORT(array,[sort_index],[sort_order],[by_col])其中：array是要排序的区域或数组；sort_index表示排序字段在所在array中的数字；sort_order表示所需排序顺序的数字，默认1表示升序，-1表示降序；by_col表示所需排序方向的逻辑值，默认FALSE是按行排序，TRUE是按列排序。

3.SORTBY函数含义：如果想要对网格中的数据排序，最好用SORTBY函数。SORTBY函数是基于某范围或数组中的值对一些列范围或数组的内容进行排序。语法格式为：SORTBY(array,by_array1,[sort_order1])其中：array是要排序的区域或数组；by_array1是要进行排序的范围或数组的依据；sort_order表示所需排序顺序的数字，默认1表示升序，-1表示降序；因为可以实现多字段排序，所以可根据需求添加by_array2和其对应的sort_order2。3.编制“成品入库表”单元格E4，输入公式“=VLOOKUP(D4,基础信息表!$I:$L,2,FALSE)”，则E列将返回存货对应的单位。单元格F4，输入公式“=VLOOKUP(D4,基础信息表!$I:$L,3,FALSE)”，则F列将返回存货对应的类别。单元格G4，输入公式“=VLOOKUP(D4,基础信息表!$I:$L,4,FALSE)”

则G列将返回存货对应的规格型号。3.编制“成品入库表”

手工输入H列“结转数量”和I列“结转单价”后，选择单元格J4，输入公式“=H4*I4”，即“结转金额=结转数量*结转单价”o2销售与车间领料业务处理

单元格F4，输入“=IF(D4="材料",VLOOKUP(E4,基础信息表!$D:$G,2,FALSE),IF(D4="商品",VLOOKUP(E4,基础信息表!$I:$L,2,FALSE),“请检查存货名称输入是否正确”))”，返回存货对应的单位。同理返回G列存货对应的类别、H列存货对应的规格型号。1.编制“销售业务表”任务二销售与车间领料业务处理用IF函数区分不同的销售业务

销售业务，若实现主营业务收入，减少的是库存商品，若实现其他业务收入，减少的是原材料，故要增加“摘要”来区分不同的属性。1.编制“销售业务表”2.编制”车间领料表”同“采购业务表”，设置E列、F列和G列相关公式选择J4单元格，输入“=H4*I4”知识技能三：多条件求和间的区别o3库存管理任务三库存管理1.编制“库存管理表”

材料是采购入库的，数据来源为“采购业务表”；产成品是车间生产完工后结转入库的，数据来源为“成品入库表”。

材料的减少可能是车间领料，也可能是出售多余材料，所以数据来源包含“销售业务表”和“车间领料表”；产品的减少一般来说是因为销售实现了主营业务收入，所以数据来源为“销售业务表”。2.编辑相关单元格公式（1）原材料的“入库数量”和“入库金额”（2）产成品的“入库数量”和“入库金额”（3）原材料的“出库数量”和“出库金额”（4）产成品的“出库数量”和“出库金额”（5）计算“期末数量”和“期末金额”3.录入业务，美化单元格格式设置“预警”列【条件格式】

选择单元格N4，输入公式“=IF(K4<=M4,"*","")”3.录入业务，美化单元格格式设置“预警”列【条件格式】

条件格式可以根据特定条件对数据进行格式标识，以更加直观地获取特定问题的视觉提示，常被应用在单元格、表格和数据透视表中。“条件格式”的菜单栏由3块内容构成：1．仅对部分单元格设置格式2.使用数据条/色阶/图标集设置所有单元格格式3.新建/管理/清除规则

知识技能四：条件格式1．仅对部分单元格设置格式知识技能四：条件格式2．使用数据条/色阶/图标集设置所有单元格格式知识技能四：条件格式3．新建/管理/清除规则知识技能四：条件格式

增设“安全”列，执行【新建规则】，选中【使用公式确定要设置格式的单元格】，在“为符合此公式的值设置格式”中输入“=$K1>$M1”，调整【填充】为绿色。知识技能四：条件格式条件格式规则的优先级

列表中较高处规则的优先级高于较低处的规则；因为默认情况下，新规则总是添加到列表的顶部，所以具有较高的优先级。4.利用数据透视表进行分析4.利用数据透视表进行分析完成数据透视图o4存货明细账任务四存货明细账1.编制“进销总记录表”

分别从“采购业务表”“成品入库表”“销售业务表”“车间领料表”“库存管理表”中复制相关信息。关于“选择性粘贴”的说明1.编制“进销总记录表”知识技能五：数据的舍入1.ROUND/ROUNDUP/ROUNDDOWN函数ROUND函数用于对数值进行四舍五入；ROUNDUP函数用于向上舍入（远离零）；ROUNDDOWN函数则是向下舍入（朝向零）。三者的语法格式均为：(number,num_digits)其中，number是要四舍五入的数字，num_digits表示四舍五入的位数，即计算精度。知识技能五：数据的舍入2.INT函数/TRUNC函数INT函数只有一位参数(number)，表示将数字向下舍入到最接近的整数；当number是负数时只会朝着远离零的方向将数字舍入。TRUNC函数表示将数字的小数部分截去，以返回整数。语法格式为TRUNC(number,[num_digits])，不同于ROUND系列函数，num_digits是一个可选项，参数省略时，默认值为零。知识技能五：数据的舍入第一列num_digits为-1，第二列为0，第三列为1。ROUNDDOWN和TRUNC函数的结果是一样的，唯一的区别是公式中参数的写法格式存在细微差别，因为TRUNC函数的num_digits是可选项，而在ROUNDDOWN函数中为必选项，即便省略，也要保留参数间的逗号。知识技能五：数据的舍入3.CEILING函数/FLOOR函数

CEILING函数用于向上舍入（远离零的方向）最接近指定参数的倍数；FLOOR函数用于（朝向零的方向）向下舍入最接近指定参数的倍数。

语法格式均为(number,significance)，其中，number是待舍入的数字，significance是基数。CEILING(2.5,2)=4FLOOR(-2.5,-2)=-4知识技能五：数据的舍入4.ODD函数/EVEN函数

ODD函数用于将正数向上舍入到最接近的奇数，将负数向下舍入到最接近的奇数；

EVEN函数用于将正数向上舍入到最接近的偶数，将负数向下舍入到最