电子表格在财务中的应用(WPS版) 课件 项目四 电子表格在进销存中的应用

项目四电子表格在进销存中的应用电子表格在进销存中的应用电子表格通过自动化数据处理和分析功能，极大地简化了进销存管理流程。例如，利用Excel的公式和函数，可以快速计算库存数量、成本和销售利润，减少人工计算的错误和时间成本。数据透视表功能可以轻松生成库存报告、销售报表和采购计划，帮助管理人员快速了解库存动态和销售情况，从而做出及时的决策。电子表格能够实时更新库存数据，确保库存信息的准确性。通过设置数据验证规则，可以避免输入错误，保证数据的完整性。同时，电子表格的图表功能可以直观地展示库存水平、销售趋势和采购需求，帮助管理人员更好地理解业务状况，优化库存管理和采购策略。具备爱岗敬业、诚实守信、精益求精、办事公正的意识与品质,拥有专业岗位的职业道德、职业能力及职业品质素质目标·熟练掌握电子表格SUMIFS、INDEX、MATCH、SUMPRODUCT函数的用法技能目标•了解电子表格在进销存业务方面的应用知识目标教学目标LEARNINGOBJECTIVES任务一采购管理任务二销售管理任务三库存管理1234项目提纲实战演练采购管理PURCHASINGMANAGEMENT一234任务要求操作流程成果展示1编制购进货物明细表①使用公式将“名称型号”数据补全。②使用公式计算“金额”。任务要求（1）双击“编制购进货物明细表（答题文件）.xlsx”，单击“购进货物明细表”工作表标签。（2）在E3单元格中输入公式“=C3&D3”按【Enter】键；在I3单元格输入公式“=G3*H3”按【Enter】键 ，将公式填充至其他区域。如图所示。操作流程成果展示123任务要求操作流程成果展示2采购统计分析①为”购进货物明细表”工作表中的E2:I53单元格区域以首行定义名称。②根据“购进货物明细表”工作表，使用SUMIF函数结合上述的定义名称计算出C3:C33区域数据。任务要求（1）双击“采购统计分析（答题文件）.xlsx”，单击“采购统计分析”工作表标签。（2）在“购进货物明细表”工作表中，选中E2:I53单元格区域，点击“公式”→“指定”，在弹出的“指定名称”对话框中，取消勾选“最左列”前的复选框，点击“确定”。如图所示。操作流程（3）在“采购统计分析”工作表中的C3单元格输入公式“=SUMIF(名称型号,B3,金额)”，按【Enter】键，向下填充公式。如图所示。操作流程成果展示销售管理SALESMANAGEMENT二1234知识链接任务要求操作流程成果展示1编制销售订单表1.INDEX函数INDEX函数返回表格或数组中的值，该值由行号和列号的索引确定。语法:=INDEX(数组,行序数,[列序数],[区域序数])参数:数组:单元格区域或数组常量。行序数:数组或引用中要返回值的行序号。如果忽略，列序数不为空。[列序数]:可选参数，数组或引用中要返回值的列序号。如果忽略，行序数不为空。[区域序数]:可选参数，对一个或多个单元格区域的引用。示例:假设有一个3x3的表格，A1:C3，想返回第二行第三列的值（即C2），可以使用:INDEX(A1:C3,2,3)。知识链接2.MATCH函数MATCH函数搜索指定项与指定数组的项进行匹配，并返回该项在数组中的相对位置。语法:MATCH(查找值,查找区域,[匹配类型])参数:查找值:要搜索的值。查找区域:要搜索的数组。[匹配类型]:可选参数，（0或省略:精确匹配，1:小于或等于查找值的最大值。-1:大于或等于查找值的最小值。）示例:假设有一个列表A1:A5，包含值1,2,3,4,5，想找到值3的位置，可以使用:MATCH(3,A1:A5,0)这将返回3，因为3在A1:A5中的位置是3。3.INDEX和MATCH组合使用当需要根据一个条件来查找并返回一个值时，可以将这两个函数组合使用。语法:INDEX(数组,MATCH(l查找值,查找数组,[匹配类型]),[返回值类型])参数:数组:你想从中返回值的数组。查找值:你想在数组中匹配的值。查找数组:你想搜索的数组。匹配类型:同MATCH函数的。返回值的类型:可选参数。示例:假设有一个员工名单在A1:A10，想根据B1中的员工姓名找到对应的员工ID在C1:C10中的位置，可以使用:INDEX(C1:C10,MATCH(B1,A1:A10,0))这将返回与B1中姓名匹配的员工的ID。这两个函数的组合使用非常灵活，可以解决许多复杂的数据查找和引用问题。知识链接①为“销售产品清单”工作表中B2:E410单元格区域定义名称为“销售产品清单”。②根据“远程支持服务促销价格表”使用INDEX函数和MATCH函数计算“单价”。③根据“销售产品清单”工作表，使用VLOOKUP函数结合任务①中定义的名称计算“数量”。④使用公式计算“金额”。任务要求（1）双击“编制销售订单表（答题文件）.xlsx”，单击“销售订单表”工作表标签。（2）选中“销售产品清单”工作表中B2:E410单元格区域，点击“公式”→“名称管理器”，在弹出的“名称管理器”对话框中，点击“新建”，在弹出的“新建名称”对话框中，名称文本框中输入“销售产品清单”，点击“确定”，回到“名称管理器”对话框中点击“关闭”。如图所示。操作流程（3）在“销售订单表”工作表的K3单元格输入公式“=INDEX($P$3:$S$7,MATCH(I3,$O$3:$O$7,0),MATCH(J3,$P$2:$S$2,0))”，按【Enter】键；在L3单元格输入公式“=VLOOKUP(H3,销售产品清单,4,0)”，按【Enter】键；在M3单元格输入公式“=K3*L3”，按【Enter】键；将公式向下填充至其他区域。如图所示。操作流程成果展示123任务要求操作流程成果展示2销售出库汇总表①使用VLOOKUP函数补充每个人对应的“提成比率(％)”和“速算扣除数”；②请使用公式计算“本月销售提成额”。注:本月销售提成额=月销售额×提成比率(％)—速算扣除数任务要求（1）双击“销售出库汇总表（答题文件）.xlsx”，单击“销售出库汇总表”工作表标签。（2）在E3单元格中输入公式“=VLOOKUP(D3,$I$2:$K$9,2,1)”，按【Enter】键；在F3单元格中输入公式“=VLOOKUP(D3,$I$2:$K$9,3,1)”，按【Enter】键；在G3单元格中输入公式“=D3*E3-F3”，按【Enter】键；将公式填充至其他区域。如图所示。操作流程成果展示库存管理INVENTORYMANAGEMENT三1234知识链接任务要求操作流程成果展示1编制库存清单SUMPRODUCT函数SUMPRODUCT的基本用法是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。在实际使用中，SUMPRODOCT函数可以实现多条件计数、多条件求和等特殊用法以代替COUNTIFS、SUMIFS等函数，而且用起来甚至比COUNTIFS、SUMIFS函数更加灵活，接下来我们将介绍SUMPRODUCT函数的基本用法和常见的特殊用法。1.SUMPRODUCT函数的基本用法功能:在给定的几组数组中，将数组间对应的数据相乘，并返回乘积之和。语法结构:SUMPRODUCT(数组1,...)参数介绍:数组1是1个数组，...是2到30个数组，SUMPRODUCT函数能对其相应元素进行相乘并求和。2.SUMPRODUCT函数多条件计数功能:多条件计数语法结构:SUMPRODUCT((条件1)*(条件2)*(条件3)*...(条件n))。此时SUMPRODUCT函数能统计同时满足条件1、条件2到条件n的记录的个数。3.SUMPRODUCT函数多条件求和功能:多条件求和。语法结构:SUMPRODUCT((条件1)*(条件2)*(条件3)*...(条件n)*某区域)。此时SUMPRODUCT函数将对同时满足条件1、条件2、条件3…条件n的记录的指定单元格区域进行求和。知识链接①根据“库存表”工作表，使用SUMPRODUCT函数计算“单价（元）”。②使用ROUND函数计算“金额（元）”，保留2位小数。任务要求（1）双击“编制库存清单（答题文件）.xlsx”，单击“库存清单”工作表标签。（2）在H3单元格输入公式“=SUMPRODUCT((库存表!$A$4:$A$28&库存表!$B$4:$B$28=E3)*库存表!$G$4:$G$28)”，按【Enter】键；在I3单元格输入公式“=ROUND(G3*H3,2)”，按【Enetr】键；将公式填充在其他区域。如图所示。操作流程成果展示234任务要求操作流程成果展示2设置库存预警 设置库存预警①使用公式计算结存数量和结存金额；②请根据产品结存数量使用条件格式设置库存预警提示；说明:1.如果产品结存数量大于30，请将整行数据单元格用标准黄色填充；2.如果产品结存数量小于10，请将整行数据单元格用标准红色填充；任务要求（1）双击打开“设置库存预警（答题文件）.xlsx”，单击“设置库存预警”工作表标签。（2）在L4单元格输入公式“=F4+H4-J4”,按【Enter】键；在L4单元格输入公式“=L4*E4”，按【Enter】键；向下填充公式。如图所示。操作流程（3）选中“A4:M20”单元格区域，点击“开始”→“条件格式”→“新建规则”，在弹出的“新建规则”对话框中点击“使用公式确定要设置格式的单元格”，在“只为满足以下条件的单元格设置格式”文本框中输入“=$L4>30”，点击“格式”，在弹出的“单元格格式”对话框中点击“图案”，点击颜色区域中最下面的“标准黄色”，点击“确定”，返回到“新建格式”对话框中点击“确定”。如图所示。用同样方法设置产品结存数量小于10条件格式预警，其中在“只为满足以下条件的单元格设置格式”文本框中输入“=$L4<10”,背景色选择“标准红色”。操作流程成果展示1234知识链接任务要求操作流程成果展示3产品成本分析1.SUMIFS函数SUMIFS函数用于计算满足一个或多个条件的单元格的总和。语法:SUMIFS(求和区域,区域1,条件1,...)参数:求和区域:需要求和的单元格区域。区域1:第一个条件的单元格区域。条件1:第一个条件，可以是数字、表达式或文本。...:可选参数，表示额外的条件区域和对应的条件。示例:假设有一个销售数据表，其中A列是销售员姓名，B列是销售区域，C列是销售额。想要计算“区域为华东”的所有销售员的总销售额，可以使用以下公式:=SUMIFS(C:C,B:B,"华东")如果还要计算“区域为华东”且“销售员为张三”的销售额总和，可以这样写公式:=SUMIFS(C:C,B:B,"华东",A:A,"张三")这将返回同时满足这两个条件的销售额总和。注意事项确保条件区域和求和区域的行数一致，否则函数将返回错误。条件可以是数字、表达式、单元格引用或文本字符串。文本字符串条件需要用双引号""括起来。条件表达式可以使用通配符，如*代表任意多个字符，?代表任意单个字符。知识链接①为E2:G26数据区域以首行定义名称；②用SUMIFS函数，结合上述的定义名称计算各产品的“直接材料”“直接人工”“制造费用”金额；③使用公式计算各产品的“生产成本”；④使用公式计算各产品的“直接材料”“直接人工”“制造费用”比重。任务要求（1）双击“产品成本分析（答题文件）.xlsx”，单击“生产成本明细表”工作表标签。（2）选中E2:G26单元格区域，点击“公式”→“指定”，在弹出的“指定名称”对话框中，取消勾选“最左列”，点击“确定”。如图所示。操作流程（3）在B32单元格中输入公式“=SUMIFS(金额,项目名称,A32,二级科目,$B$30)”，按【Enter】键；在D32单元格中输入公式“=SUMIFS(金额,项目名称,A32,二级科目,$D$30)”，按【Enter】键；在F32单元格中输入公式“=SUMIFS(金额,项目名称,A32,二级科目,$F$30)”，按【Enter】键；在H32单元格中输入公式“=B32+D32+F32”，按【Enter】键；在C32单元格中输入公式“=B32/H32”，按【Enter】键；在E32单元格中输入公式“=D32/H32”，按【Enter】键；在G32单元格中输入公式“=F32/H32”，按【Enter】键；将公式向下填充。如图所示。操作流程成果展示实战演练REAL-LIFEDRILL四1.根据任务要求，完成“期初余额试算平衡表”的处理。如图所示。任务描述:根据进销存业务登记的期初余额数据清单，进行试算平衡分析。①使用SUMIF函数计算H2:H5单元格区域的各项费用总额，使用公式计算H6:H7单元格区域的相关数据，在H1单元格使用IF函数判断期初试算平衡检查结果；②按照图表示例图片制作资产、负债、所有者权益、成本四项数据总额对比的二维簇状柱形图，图表标题、样式等各项内容要与图片示例相同。各项费用总额及检测结果标记规则如下:·资产总额=科目代码以1开头的期初借方余额总和-科目代码以1开头的期初贷方余额总和·负债总额=科目代码以2开头的期初贷方余额总和-科目代码以2开头的期初借方余额总和·所有者权益总额=科目代码以3开头的期初贷方余额总和-科目代码以3开头的期初借方余额总和·成本总额=科目代码以4开头的期初借方余额总和-科目代码以4开头的期初贷方余额总和·试算平衡检测结果标注规则:如果资产＋成本=负债＋所有者权益，显示“经试算已平衡！”，否则显示“试算不平衡，请查明原因！”。任务要求2.根据任务要求，完成“业绩奖金表”的处理。如图所示。任务描述:请你按照如下要求帮他统计3月的业绩奖金情况。本题所考核函数包括:AND、YEAR、VLOOKUP、TODAY、SUMPRODUCT、IF、MONTH根据需要可以单独使用也可嵌套使用，不可使用其他函数答题。①为“员工业绩管理表”工作表的A2:G13单元格区域添加名为“员工业绩”的定义名称。以下任务均在“业绩奖金表”工作表中完成。②为了保证数据的规范性，为“业绩奖金表”工作表中的员工姓名添加下拉列表，选项内容要从“员工业绩管理表”工作表中获取，方便用户选择，无需输入，如当前姓名有不规范的请修改成规范的数据。③使用函数在B1单元格依据A1单元格获取月份信息。④使用任务①中的定义名称，依据“员工业绩管理表”工作表中的数据使用函数获取各员工的“月度销售额”（匹配条件用数字表示）。⑤使用函数在D1单元格统计出本月的基本业绩奖金总额（基本业绩奖金=月度销售额*奖金比例）。⑥使用函数计算各员工截至到现在当前日期的工龄，只考虑自然年，其他因素不考虑。⑦使用函数计算员工“季度提成”，先判断各员工是否有季度提成（如果员工工龄满3年，且出勤率在95%或以上还完成了销售计划就可以有季度提成。），如果满足提成条件则季度提成为5000元，否则为0元。任务要求3.根据任务要求，完成“采购到货情况表”的处理。如图所示。任务描述:根据相关数据，按照如下要求完成相应分析。本题所考核函数为:ROUND,COUNTIF,COUNTIFS,VLOOKUP,DATEDIF,可以根据情况单独试用，也可以嵌套试用，不可使用其他函数答题。①使用函数补全“交货逾期天数”，“到期交货量”，“交货准时率”三列数据，其中“交货准时率”的计算结果，要使用函数进行保留2位小数；②使用公式计算“逾期未交货量”，“按时交货量率”两列数据，其中“按时交货量率”的计算结果，要使用函数进行保留2位小数；任务要求4.根据任务要求，完成“销售统计表