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編程！

我們在表的基礎上（雖然公式填充不準，但是套用格式什麼的還是可以的撒），可以錄製一個宏，並改為工作表事件，當有新的流水的時候，公式從上到下再刷一遍模擬人工填充的動作。

這個大家可以自己去嘗試一下，不難，此處不做深入講解。

那麼這是在輸入，要是旁人突然想看某個產品的最新庫存怎麼辦？

• 流水的最下面一行輸入一個進出為0 的記錄查看

• 篩選這個產品，找到最後一條記錄

• 用公式函數返回某個產品對應最後一條既最新庫存值

針對第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

更全更新的圖書樣張及案例會陸續在以下分享放出，感興趣的可以訂閱提前了解，獲取知識快人一步！