用表格管理飲料店庫存：下拉選單、分組檢視與自動延伸的彙總

如果你有一份飲料店的庫存清單，三個人在改，盤點完不是狀態欄漏填，就是有人在中間塞一列，加總公式沒算到。這篇用表格功能把這份清單重做一次。

表格怎麼建立、為什麼會自動延伸，入門篇已經講過，這邊直接進實戰。

先看資料

庫存清單長這樣，A1:E9，已經轉換成表格，名稱是預設的「表_1」：

我想知道三件事：哪些品項要補貨、每個類別現在壓了多少庫存金額、新增品項之後這些數字會不會自己跟上。

每一欄先設好類型

這份清單我會這樣設，欄選單的「編輯欄類型」：

• 品項、類別：文字。
• 庫存、單價：數字。
• 狀態：下拉式選單，選項是「充足」「待補貨」「已叫貨」。

實際使用時，還可以在表格右邊再加欄：「預計到貨」設成日期，「已盤點」設成核取方塊，要記錄負責盤點的人就加「負責人」，設成智慧型方塊的人員。這篇的範例先不加這幾欄，後面的欄位位置才對得上。

這幾種類型的差別，我個人的用法是這樣：會被拿來篩選、分組的欄位（狀態）用下拉選單，只有勾或不勾的（已盤點）用核取方塊，填錯就會出問題的（預計到貨）用日期。欄類型不符的內容，依官方說明會顯示警告，所以有人把日期打成「下週三」，會被標出來。

如果是專案管理而不是庫存，「插入」>「預建表格」裡有專案管理的現成範本，可以先套用再改欄位。

算出每個品項的庫存金額

在表格最右邊加一欄：F1 輸入標題「金額」，確認表格範圍已經包含 F 欄，F2 輸入：

=C2*D2

F2 的公式往下填滿到 F9。這邊用一般的儲存格參照，不用表格參照。原因寫在進階篇：表格裡面不能用 Excel 那種「這一列」的 [@欄名] 寫法。

F2:F9 的金額依序是 10360、3120、760、1470、336、128、2050、558。

用篩選檢視看待補貨品項

點「資料」>「建立篩選器檢視畫面」，在「狀態」欄只留下「待補貨」；只想快速篩選的話，也可以用欄選單的「篩選欄」。

← 左右滑動看完整的圖，點圖可放大

這時會看到兩列：珍珠庫存 8、封口膜庫存 5。

篩選檢視一開始是暫時的，依官方說明，試算表重新整理後會消失。要留著就把檢視存起來，之後從「資料」選單選回來。我是建議存起來，每週盤點都會用到。

用分組檢視看每個類別

另一種是「分組依據檢視畫面」，點「資料」>「建立分組依據檢視畫面」，或用「類別」欄選單的「『分組依據』欄」。每一組可以選彙總方式，我把「庫存」選成加總，會得到：

分組檢視適合看，但沒辦法把結果拿去別的公式用，要拿來用的話就得靠公式彙總。

用 SUMIFS 做類別彙總

在表格外面，例如 H2，寫：

三個配料的金額是 760、1470、558，加起來是 2788。公式的寫法和一般範圍一樣，只是範圍換成「表_1 的某一欄」，語法可以回頭看 SUMIFS 的文章。

再多加一個條件，算「配料之中還待補貨的庫存」：

只有珍珠符合，結果是 8。想知道有幾個品項要補，就是 =COUNTIF(表_1[狀態], "待補貨")，結果是 2。

用 QUERY 一次列出所有類別

類別有很多的時候，一個一個寫 SUMIFS 很累，這時改用 QUERY，語法在 QUERY 基礎。資料來源放整個表格含標題列，寫法是 表_1[[#HEADERS],[#DATA]]：

結果會從 H2 往下展開，由大到小排：

我習慣加 order by，不然類別的順序要看 QUERY 怎麼排中文，不好預期。這裡用相對於輸入表格的 Col2、Col6，避免把實際工作表的 B、F 欄寫死。把整張表格移到其他欄後，字母版查詢回傳 #VALUE!，Col 版仍得到相同彙總；它仍依賴表格內第 2、6 欄的順序，調換欄位後要重查。

新增一列之後

在表格最後一列下面接著輸入：糖漿、配料、14、88、充足。表格會往下延伸，新那一列套用同樣的欄類型，「狀態」有下拉選單。

接著 SUMIFS 與 QUERY 不用改。糖漿的庫存金額是 14 × 88 = 1232，所以配料的金額加總變成 2788 + 1232 = 4020，配料的庫存加總變成 38 + 14 = 52。原有八列的金額公式都填好之後再新增糖漿，F10 會自動算出 1232，結構化參照與表格外陣列結果也擴為九筆。

這是原有八列都已填入金額公式時的結果。只在新欄第一格寫一條公式，不能因此假定其餘舊列都已補齊；新增後仍核對 F10，沒有算出 1232 時再從 F9 往下填滿。

哪些事還是要自己顧

• 彙總的公式我習慣放在表格外面，例如旁邊的 H 欄，不要放在表格正下方，免得要接著輸入新的一列時卡到。
• 條件式格式、圖表和樞紐分析表選範圍時，目前不能直接用表格參照，官方說明有寫，細節在進階篇。
• 這些公式裡的「表_1」，你的表格如果改過名字，要換成改過的名字。

這題的結果是 336 + 128 + 2050 = 2514。建議拿手邊一份常被亂改的清單，也這樣轉成表格試試。

我是喜特先生，Mr. Sheet，我們下個教學見！