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

案例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月份所有客户（或某个客户1~12月份的）产品总的需求情况判断：判断这个数据表格哪部分信息是“条件判断区域（客户列）”，求和条件是什么（所有客户或某个客户），以及哪部分是求和区域数值（1~12月份）写入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乘积之和问题：如何在A~C列内快速汇总求和出所有满足“2011-7-22”同时又满足条件“产品A“的销售额呢？而且A~C列数据随时都可能会被更新，那么用其它方法做就显得繁琐，如果用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那么C2~C1000对应值为真，否则返回0如上公式将返回：{65,0,0,0……}库管易——让仓库管理更容易，/案例B库存管理系统(Vlookup,Sumif,条件格式的应用)一、了解Vlookup函数1.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表明是精确匹配。这点区别很重要！2.Vlookup函数具体用法：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两步，就可以完成整个汇总表的条件监控了，完成后的格式如下图完成后的样式：备注：其实在有数据表格内写条件格式