Excel高效应用培训-求和汇总函数与库存管理表格制作教程.pptx

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,#,案例,A,高效求和汇总,案例,B,库存管理系统,Excel,高效应用培训,库,案例,A,高效求和汇总,(Sumif,Sumproduct,函数的应用,),一、,Sumif,函数的应用,1.sumif,与,sum,的区别及用途,：,sum,函数是一个纯粹的求和函数（,无条件求和,），而,sumif,求和比,sum,多一个条件判断,(,有条件求和,),2.sumif,函数内的,3,个变量,：,在单元格内输入“,sumif()”,这个函数公式，能看到“,SUMIF,(range,criteria,sum_ range)”,这样的一个提示,它们分别的意思如下,:,range,为用于条件判断的单元格区域,criteria,为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本,sum_ range,需要求和的数据集合，这个变量内的单元格数值是真正用作求和计算的区域,库,3.sumif,函数的具体用法,:,目标：,求和,1,月份所有客户（或某个客户,112,月份的）产品总的需求情况,判断,：,判断这个数据表格哪部分信息是“条件判断区域,（,客户列,）,”，求和条件是什么（,所有客户或某个客户,），以及哪部分是求和区域数值（,112,月份,）,写入,sumif,公式,:,=SUMIF($B$2:$B$21,“,小计,”,C2:C21)(,具体演示如下图,),1.sumproduct(array1,array2,array3,.),的用途,：,在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和,注意：,array1,array2,array3,.,数组参数必须具有相同的维数，即 每个数组所包括的单元格个数相等,二、,Sumproduct,函数的应用,2.sumproduct,基本用法,：,3.sumproduct,多条件统计,在,B9,单元格输入公式：,=,sumproduct(B2:B7,C2:C7),即,B2*C2+B3*C3+B7*C7,乘积之和,问题：,如何在,AC,列内快速汇总求和出所有满足“,2011-7-22”,同时又满足条件“产品,A“,的销售额呢？而且,AC,列数据随时都可能会被更新，那么用其它方法做就显得繁琐，如果用,sumproduct,将显得方便简洁,解决：,在表格,F3,中输入,SUMPRODUCT,(,$A$2:$A$1000=$E3,),*,(,$B$2:$B$1000=F$2,),*,$C$2:$C$1000,),公式解释：,$A$2:$A$1000=$E3,指,如果,A2,到,A1000,单元格的内容对应等于,E3,的单元格，则得到,1,，否则返回,0,$B$2:$B$1000=F$2,指,如果,B2,到,B1000,单元格的内容对应等于,F2,的单元格，则得到,1,，否则返回,0,$C$2:$C$1000,指,如果前面,2,个条件都是,1,那么,C2C1000,对应值为真，否则返回,0,如上公式将返回：,65,0,0,0,库,案例,B,库存管理系统,(Vlookup,Sumif,条件格式的应用,),一、了解,Vlookup,函数,函数语法及参数解释,：,VLOOKUP,(lookup_value,table_array,col_index_num,range_lookup),Lookup_value,为需要在,数组,第一列中查找的数值，,可以为数值、引用或文本字符串,Table_array,为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表,Col_index_num,为,table_array,中待返回的匹配值的列序号,Range_lookup,为一逻辑值，指明函数,返回时是精确匹配还是近似匹配，如果为,TRUE,或省略或,1,表明是,近似匹配,；如果是,FALSE,或,0,表明是,精确匹配,。,这点区别很重要！,函数具体用法,：,F2,单元格,F2,中的值”,A0002“,就是要查找的数值,A2:C11,就是编码”,A0002“,这个值要去匹配查询的基本数据表格,指得是在数据表格中匹配到”,A0002“,的值后需要返回的表格第,2,列内对应值,0,指精确匹配”,A0002“,整个公式可以被解读为：编码为“,A0002”,的数值在基本数据表格中经精确匹配查找后最终返回对应在第,2,列表格中的数值为“,物料,1,”,公式演示,公式解释,库,二、建立库存管理系统,了解基本数据及确定目标,：,打开案例,8,，里面可以看到如下的,3,张表，分别为：,期初数,入库,出库,期初数：这张表主要是“物料编码”对应的“物料名称”以及初始的数据,入库：这张表是记录入库情况，其中,深颜色,表示是我们每天需要输入的数据,出库：这张表是记录出库情况，其中,深颜色,表示是我们每天需要输入的数据,通过上面,3,张基础数据表，我们应该确定这样一个目标：通过这,3,张表的数据更新情况，我们应建立一张可以即时反映出当前所有物料的一个“,库存情况,”的汇总表，而且每种物料当前库存情况因量的变化情况应突出显示不同的颜色,确立目标汇总表表格内容：,目标内容：在这张汇总表内的信息要求能反映出每种物料的：期初数、入库总数、出库总数、实际库存以及设定一个保险库存的数值情况（如右图）,2.,库存管理系统建立步骤：,1,）,为基础信息表写入查找引用函数,：在基础信息表（入库、出库）两张表内，为减少输入，我们设定只要输入“物料编码”，在物料名称栏就应显示物料编码对应的名称，所以在入库、出库的两张表内需要用到,vlookup,函数，因上面已经讲过用法了，这里就不重复了。,2,）,汇总各类物料信息,：用,sumif,函数汇总求和出各种物料的期初数、入库总数、出库总数，以及实际库存，这里需要用到,sumif,函数，因上面已经讲过用法了，这里也就不重复了。,3,）,建立条件格式,：完成上面,2,步后，汇总的所有数据就出来了，但是这样还不是很方便我们监控每种物料的库存情况，这里我们就要用到“,条件格式,”来帮助我们监控物料的库存量。,数据区域内“条件格式”建立的步骤：,a),选定要建立条件 格式的数据区域,：,b),选择“格式”,“,条件格式”,：,库,c),设定第一个条件的格式：,点选第一个条件下拉菜单,选择“公式”,：,在“公式”输入公式“,=$F2=$G2/2,”,：,“点击格式”,选择”图案“,在颜色筐中选”红色“,确定,这个条件的意思就是如果,“,实际库存,”,小于等于,”,保险库存“的一半,时，那么选定的区域”,A2:G2“,的背景色就显示,“,红色,”,d),设定第二个条件的格式：,选择”添加“,：,重复与第一个条件一样的步骤,：只是把条件设定范围放宽：当,“,实际库存,”小于等于”,保险库存,“，那么选定的区域”,A2:G2“,的背景色就显示“,黄色,”,e),继续选择 其它需要添加条件格式的数据，重复上面,c,d,两步，就可以完成整个汇总表的条件监控了，完成后的格式如下图,完成后的样式：,备注：其实在有数据表格内写条件格式并不是最好的办法，如果我们能在写数据和写表格内公式之前就先写好条件格式的话，我们就可以省去很多重复的劳动了，比如都没有数据，我们在写好,A2:G2,的条件格式时，然后把,A2:G2,选中的区域往下拉到,11,行就可以了，省去了设置,A3:G3,再然后,A4:G4,的重复工作了。,thanks!,库,