簡單的excel庫存表（試過很多種方法）

2023-10-20 01:41:09

財務對帳、進出庫盤點，一直是 Excel 領域裡出現頻率頗高的需求場景。

對於此類問題的解決方案，網上也是層出不窮，各有利弊。

在實際工作中，這類需求的數據量往往非常龐大，動輒大幾千行，如果用公式不僅慢，而且還挺麻煩，要考慮到兩張表的行數可能不一致，類目不一一對應，甚至排序也是混亂的。於是網上有出來很多教程，教大家怎麼查錯。

算了，那麼麻煩，不如用我今天教的 Power Query 來實現吧，滑鼠點擊下就設置好了，接下去不管數據怎麼變，只要刷新就能更新結果。

下圖 1、2 是前後兩次庫存檔點數據，需要計算每項物品的進出庫數。

1. 選中「1月」工作表中數據表的任意單元格 --> 選擇菜單欄的「數據」-->「從表格」

2. 在彈出的對話框中點擊「確定」

表格已上傳至 Power Query。

3. 選中「1月盤點數」列 --> 選擇菜單欄的「轉換」-->「逆透視列」

4. 選擇菜單欄的「主頁」-->「關閉並上載」-->「關閉並上載至...」

5. 在彈出的對話框中點擊「僅創建連接」 --> 點擊「確定」

6. 用同樣的方法將「2月」的數據表上傳至 Power Query：選中數據表的任意單元格 --> 選擇菜單欄的「數據」-->「從表格」--> 在彈出的對話框中點擊「確定」

7. 選中「2月盤點數」列 --> 選擇菜單欄的「轉換」-->「逆透視列」

8. 選擇菜單欄的「主頁」-->「追加查詢」

9. 在彈出的對話框中，在「要追加的表」下拉菜單中選擇「表1」--> 點擊「確定」

10. 選中「屬性」列 --> 選擇菜單欄的「轉換」-->「透視列」

11. 在彈出的對話框中進行如下設置 --> 點擊「確定」：

由於 2 月新增了部分品類，所以 1 月沒有的品類數據為 null。null 值不能參與計算，我們就需要把它們全部替換為 0。

12. 選中「1月盤點數」列 --> 選擇菜單欄的「轉換」-->「替換值」

13. 在彈出的對話框中進行如下設置 --> 點擊「確定」：

14. 選擇菜單欄的「添加列」-->「自定義列」

15. 在彈出的對話框中的「自定義公式」區域輸入以下公式 --> 點擊「確定」

公式中的參數可以從右側的「可用列」區域選擇並插入。

16. 將「1月盤點數」列拖動到第 2 列

17. 將最後一列的列名修改為「2月進出庫」

18. 選擇「2月進出庫」右邊的篩選箭頭 --> 在彈出的菜單中取消勾選 0 --> 點擊「確定」

0 即表示這個品類無進出庫變化，就不需要列出了。

根據實際情況，如果不需要保留 1、2 月的盤點數據，也可以將這兩列刪除。

19. 選擇菜單欄的「主頁」-->「關閉並上載」-->「關閉並上載」

Power Query 中的表格就上傳到了 Excel 中的一個新工作表中。如果盤點數據有任何變動，只要刷新下方這張表，就能實時更新結果。

很多同學會覺得 Excel 單個案例講解有些碎片化，初學者未必能完全理解和掌握。不少同學都希望有一套完整的圖文教學，從最基礎的概念開始，一步步由簡入繁、從入門到精通，系統化地講解 Excel 的各個知識點。

現在終於有了，以下專欄，從最基礎的操作和概念講起，用生動、有趣的案例帶大家逐一掌握 Excel 的操作技巧、快捷鍵大全、函數公式、數據透視表、圖表、列印技巧等……學完全本，你也能成為 Excel 高手。