仓库EXCEL公式使用技巧培训.doc

仓库EXCEL公式使用技巧培训文件除了求和公式之外，有两个公式对仓库的电子表格制作非常有帮助：一、VLOOKUP公式（查找与引用公式）1、零件图号无重复时当我们要在另一个表格中查找某个数值填入某个表格中时，用VLOOKUP公式会非常方便，不需要我们一个一个地进行比照。但是，用VLOOKUP公式有一个条件，那就是：这两个表格中有一列数据是相同的（比如说都有图号这列，或者都有供应商名称这一列），而且我们要查找与引用的那个表格中的这列数据是唯一的，没有重复的。VLOOKUP公式的一般格式如下：=VLOOKUP(用与比照的列，要引用的表格中从用于比照的列到要引用的数据所在的列，要引用的数据相对于用于比照的列的顺序号，FALSE)如：我们想把表二中的“月累计收入”导入到表一的相同零件图号的“本期到货数”中（经过分析，我们发现表二中的零件图号没有重复的，因此，我们可以直接用VLOOKUP公式）。那么，在上述VLOOKUP公式中的括号里第一个逗号前的“用于比照的列”就是表一的第D列；“要引用的表格中从用于比照的列到要引用的数据所在的列”就是从第D列到第K列；“要引用的数据相对于用于比照的列的顺序号”，应该是8，因为第D列是第1列，而第K列数过来就应当是第8列。“FALSE”是所有的VLOOKUP中都需要有。这个是表一这个是表二 那么，我们就可以在表一的L2单元格（即表示第L列第2行）中输入这个公式：=VLOOKUP(D：D，表一.XLSSheet1！$D：$K，8，FALSE) 实际上，上述公式并不需要我们全部手动输入，我们只需要先输入 =VLOOKUP（） ，然后把光标移到两个括号中间，然后再用鼠标点一下表一的D列的最上面，括号里就自动会出现D：D字样，如下图：=VLOOKUP（）里自动出现了D：D字样用鼠标点一下这里，然后我们会发现在L2表格的公式=VLOOKUP（）里自动出现了D：D字样然后，我们再手动输入一个逗号，然后公式里的“表一.XLSSheet1！$D：$K”也不用我们输入，我们只需要把表二打开，然后按住鼠标左键盘，从表二的第D列一直拖到第K列，这是我们也会发现表一的L2表格的公式的第一个逗号后面自动出现了“表一.XLSSheet1！$D：$K”，如下图：这里出现了“8C”字样公式栏用鼠标点一下这里，然后按住鼠标左键，一直从第D列往右拖到第K列，这是我们会发现在第K列的右上角出现了一个“8C”字样，这个就告诉我们第K列在从D列到K列这个区域里是处于第8列。同时，我们发现表格上头的公式栏的=VLOOKUP（）里在第一个逗号后自动出现了“表一.XLSSheet1！$D：$K”字样 然后，再在公式里手动输入一个逗号，再输入一个数字8，再输入一个逗号，再输入FALSE字样，然后再敲一下“回车键”，这样的话，表一的L2单元格里的公式就输入完毕，这时，我们点一下L2单元格，会发现在公式栏出现了这个公式：=VLOOKUP(D：D，表一.XLSSheet1！$D：$K，8，FALSE)然后，把公式往下复制（可以用拖的方法来复制），这时，我们会发现表二中的“月累计收入数”（第K列）中的数都导入到了表一的“本月到货数”中了。 但是，到此还没有完，还需要把表一的筛选都去掉，然后用鼠标点一下“本月到货数”（第D列）的列头，右键单击鼠标，点“复制”，然后同样在“本月到货数”（第D列）的列头右键单击鼠标，然后点“选择性粘贴”，这时会跳出一个对话框，然后在这个对话框中选中“数值”，然后再点确定。到此，操作就全部完成。为什么要这样复制粘贴一下呢？是因为表一的“本月到货数”中的数是通过公式引用的另一个表格（即表二）中的数，因此，如果我们把表二关掉后，如果表二的名称发生了改变或是移动了位置，这时我们再打开表一时，就会发现“本月到货数”中是一堆乱码，只有把公式通过“选择性粘贴”把用公式找来的数变成“数值”后，才可以避免这种情况。2、零件图号有重复时 前面说过，只有当表二的零件图号（即我们用于比照的数）是唯一的、没有重复的时候，才可以用VLOOKUP公式，但是，仓库中经常有这种情况：一个零件有两个厂家，即同一个零件图号在表二中可能出现两次，那么，是否也可以用VLOOKUP公式呢？答案是肯定的，只不过需要增加一列，把用于比照的列（在上面的例子中，用于比照的列即是零件图号列）中的数据变成唯一的、没有重复的就行。怎么做呢？这时，就需要在表一和表二中各增加一个空白列，然后在空白列中输入一个公式：=零件图号&供应商代码如下图：在E2这个单元格中，我们需要输入一个“=”（等号），然后点一下同一行的零件图号那个单元格（即D2），然后再手动输入一个“&”符号，然后再点一下同一行的供应商代码那个单元格（即I2）。这个就是我们增加的空白列这个是表一 当输完上面那个公式后，敲“回车键”，这时，E2表格里就出现了“D25-11090304502D6”字样，这个字样的前半截是零件图号，后半截是供应商代码。然后把公式复制到E列的其他单元格。表二的操作也完全同表一一样，需要新增一列，再输入公式，把零件图号与供应商代码组合在一起。零件图号与供应商代码组合在一起后的字样无论在表一中，还是在表二里，肯定都是唯一的，没有重复的。因此，我们就可以把这个组合后的字样所在的列（即上表一中的E列），作为使用VLOOKUP公式时的“用于比照的列”，VLOOKUP公式的操作完全同第1条“零件图号无重复时”的操作。示意图如下：此时，在VLOOKUP公式里，第一个逗号前的用于比照的列是新增那一列，即E列公式栏这个是表二此时，用鼠标拖的时候，应该是从第E列拖一直拖到第L列。 如果我们输完公式回车后，有时会发现有“#N/A”字样，则说明在表二中没有找到相关的数据。我们可以把这些“#N/A”筛选出来，然后在用“清除内容”把这些“#N/A”清除掉。 注意点：我们用VLOOKUP公式时，最需要注意的是，表二的表格中的比照列的数据一定是唯一的没有重复的！否则就会出错。比如说，一个零件有两个供应商时，我们就需要像上面第2条“零件有重复时”的操作那样，用“&”符号将零件图号与供应商代码组合在一起，但是，如果仓库里有退改账本时可能又行不通，因为正常物资账本与退改账本里可能都有同一个供应商供应的同一种零件。这时，怎么办呢？与将零件图号与供应商代码组合在一起的道理一样，我们只需要新增一列，将零件图号、供应商代码与账本号组合在一起即可。公式大致如下： =零件图号&供应商代码&账本不仅是到货数可以这样引用，“单价”“帐本”等等各类数都可以这样引用，不用一个一个的进行比照引用。而且，不仅仅是数字可以引用，文字（如供应商名称、零件名称等）也可以引用。比如我们可以将供应商代码作为比照的列，将一个表格的供应商名称引用到另一个表格中。二、SUMIF公式（条件求和公式）SUMIF公式是根据指定条件对若干单元格求和的