Excel实战案例（函数）——还在为实时库存、余额计算伤脑筋？来来来，一贴解决你的问题 ...

书籍简介：围绕实战案例讲述Excel办公技巧，全部案例皆来自淘宝office办公定制化服务5钻店，多年的积累，涵盖各行各业各类需求，Excel为主打，穿插讲述PPT、Word以及职场经验等，整个书籍聚焦于解决实际问题，尽请期待！

问题：

经常会有人问到计算实时库存（采购、物流、仓储或者生管岗）或者实时帐户余额（财会、金融类岗）的问题。

这个应该是实际工作生活中很常见的一个问题，了解实时数据的变化，做到心中有数，才能更好的做计划，采取相应措施，安排生产活动。

但是现实中，我们很多时候并没有完善的ERP等信息系统等数据支撑来支持我们获取此类信息，更多的人在用Excel处理一些不算非常庞大的数据，获取此类信息变得没有那么直观。

有些人不堪“繁琐”，找人开发VBA编程做小的进销存类的软件或者Excel二次开发，能够解决这些问题。

但是更多的人却没有此等资源或者无力承担此成本去搞专门开发，一是见识有限，不知道还能这么做，二是无处报销，自己贴钱去解决工作上的难题的人还是比较少的(经济发达沿海地区的情况相对好些)，可能更多的人选择自己尝试着去学习，最后其实这个学习成本也不低，很多人最后不了了之，还是用着最传统最笨的办法在处理日常事务，今天我们就好好来探讨一下这个问题。

现金流

首先，需要指出的是，此类数据的源数据记录方式一律建议采用数据库的形式，即最简单的一个抬头下面是流水清单的方式。

最见不得的是每天一个sheet，每个产品一个小汇总，每天一个大汇总之类的记录方式，每每碰到这种求救的人，真是心情复杂，既同情又愤恨——同情的是他平日处理是多么耗时耗力，愤恨的是他没有从源头了解数据存储处理原则自己为自己每天累死累活却不讨好的工作挖坑埋雷，即使花钱找人处理，处理的人估计也是头大得很。数据稍微规整的还好，还能编程进行批量化处理；不规整的想哭的心都有了，推倒重来只能是体力活，一点技巧可言都没有。

我在多个场合多篇文章里面有反复提到这么一个问题，我们一定要了解其中的原理，合理规范的数据存储形式不仅有利于统一设置格式，公式，还能利用强大的数据透视表工具，对于编程来说也会容易得多。好了，在此不再赘述。

那么我们常见的登记形式是这样的：

有人习惯将进、出作为两个字段来登记

分字段

当然也可以用另外一种形式，将这两个字段进行合并处理，因为属性其实是相同的，用+、-来区分进出。

合并字段

我们注意到一个现象，很多人为了计算方便将相同的产品放在一起，有新的录入的时候就在底下插入一行，这么做是因为底气不足，放在一起，他们好计算处理，那实时库存就=进-出就可以了。

大多数人在这个情况下是能够处理的，因为相当直观了，简单的四则运算就可以实现。但是存在不便的地方就是输入的时候要找到相同的料号插入，如果料号很多量大，插入很麻烦，这个不符合我们实际的生产活动。比如超市收银，肯定是客户都是随机的，买的东西也是随机的，录入的产品信息也是随机的，是真正的流水，那就要按照时间先后顺序来发生一笔记一笔。

窍门

问题来了，很多产品裹在一起，有些人开始傻傻分不清楚了，有的人稍微清白点知道能够用一些函数进行约束，进行条件求和。但是另外一个痛点来了，如何实时库存啊？

有的人说这个简单啊，我把总进的减去总出的不就行了？

是滴，但是你没发现你的所有相同料号的实时库存是一样的么？

产品型号写一起的时候还比较单纯，首先单独为第一个设置一个公式，第二个就在第一个的基础上+进-出的就得了，下拉填充，第三个就在第二个的基础上，以此类推……

但是如果混在一起记流水就行不通了，那么到底怎么办？

其实这里一个小窍门就可以了，就是相对引用和绝对引用。

很多人问他知道函数不？我估计几乎所有人都会说知道，问$的作用，大部分人也都知道，但是实际运用，还是很多人搞不清楚。

讲2种方法：

1. Sumproduct

=sumproduct(($C2:C2=C2)*$E2:E2)

公式的意思很简单，在满足产品名的条件下计算他的库存的和。

sumproduct

我们注意只要锁定第一个单元格，向下填充就行了，这样数据引用范围会从第一数据行到实时录入的行，即体现了实时数据更新。

尤其注意第一公式的设置，很多人喜欢第一个单独设置，第二个在第一个的基础上再设置，这样就丧失了灵活性，其实完全可以$C2：C2的形式，sum计算也是这样的，虽然只是一个单元格，但是也可以写成连续区域的格式，这样避免了第二行单独写公式的麻烦，扩展性和灵活性要好得多。

2.Sumif

同样的道理，很多人搞不懂sumproduct函数里面与和或的设置关系，那么sumif就更适合你，多条件的话可以用sumifs，结果一样。

sumif

高手更喜欢用sumproduct，更灵活方便，功能更强大。

这样，我们在实时输入的时候，就能看到实时库存的变化了。

注：以上例子没有考虑期初库存，如果有对照表的话，可以用vlookup引用过来加在前面进行运算。

如果设计安全库存，配合条件格式进行提醒的话，那么就更好了，我们可以实时掌控库存变化，进行良好的管控。

我们在记流水的时候会发现一个问题，我们实时库存的公式需要时不时的往下拉填充，有懒癌晚期的同学说，我不想拉公式怎么办？能不能自动啊？

首先，我会想到用表的功能，表具有自动延展性，能够自动扩充格式和公式。

但是实际过程中却发现，表在扩展这类公式时结果不对（见倒数第二行）导致后面的结果不对，不知道是不是微软的bug，一直没找到这个问题的官方答案。

但是对于一般的四则运算和简单的公式，表还是能够如实的正确的填充的，这是需要特别注意的一个地方，我已经多次发现并印证了这点。

公式自动填充错误

那么就没有其他办法了么？有啊，VBA编程！

我们在表的基础上（虽然公式填充不准，但是套用格式什么的还是可以的撒），可以录制一个宏，并改为工作表事件，当有新的流水的时候，公式从上到下再刷一遍模拟人工填充的动作。

这个大家可以自己去尝试一下，不难，此处不做深入讲解。

库存查询

那么这是在输入，要是旁人突然想看某个产品的最新库存怎么办？

1. 流水的最下面一行输入一个进出为0 的记录查看

2. 筛选这个产品，找到最后一条记录

3. 用公式函数返回某个产品对应最后一条既最新库存值

针对第3条，我们讲2个方法，他们得到的结果是一样的：

１.Index法

=INDEX(F:F,MAX(($C$1:$C$1000=K2)*ROW($1:$1000)))，公式以ctrl+shift+enter结束

公式很好理解，满足条件的最大的行号返回其对应的值就行了

index

2. Lookup法

更强大，公式更简单，但同时一般人比较难以理解

=LOOKUP(1,0/(C:C=K2),F:F)

这个是很解决此类问题的标准公式，很多人不是很理解这个函数，大家用得更多的是vlookup和hlookup。

lookup大法好

它的原理是找=1的对应区域，没有的话，返回比1小的，0/？要么为0，要么为错误值#DIV/0!，而错误值是不参与运算的，最后只返回满足条件的最后一行的值。

此函数很深奥， 大神们都运用得天花乱坠的，一般人需要花点时间去消化，此处点到为止，不做重点讲解。

当然还有第3种方法，就是直接计算啦，这个大家都知道了，把前面计算实时库存的公式改一下(注意此处如果C,E选择整列的话最终结果会报错)：

=sumproduct((C2:C1000=C2)*E2:E1000)

我们查询的时候可以简单点，设置下拉列表，公式提前设置好，选取你想查看的型号就行了，能看到最新的库存。

下拉填充

如此这般，就实现了录入端和查询端的实时库存信息获取。

只要几个简单的公式，就能解决貌似很高大上的实时库存/余额查询，是不是很有成就感呢？赶紧在你的工作中用起来吧！

美女办公

此案例中的涉及到的知识点本身不难，但是要求基本功扎实，视野宽泛，遇到问题能从不同的角度来解决，这就要求我们要一步一个脚印的打好基础。关注我，跟着我一起学习吧！

END

更全更新的图书样张及案例会陆续在以下分享放出，感兴趣的可以订阅提前了解，获取知识快人一步！