學不完．教不停．用不盡

網友問到：在 Excel 的工作表中有一個各個商家進貨數量表(如下圖左)，如何透過下拉式清單選取商家後，可以自動建立該商家的進貨資料(如下圖右)？

在下圖中共有六個商家，對於不同商品有不同的進貨的數量，要列出有進貨的商品清單，該如何處理？

【公式設計與解析】

1. 定義名稱

選取儲存格C2:H23，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：忠店, 孝店, 仁店, 愛店, 信店, 義店。

2. 建立下拉式清單

在儲存格K1中設定資料驗證：

儲存格內允許：清單；來源：=$C$2:$H$2。

3. 輸入公式

列出「品名」：

儲存格K2：{=OFFSET($A$1,SMALL(IF(INDIRECT($K$1)<>0,ROW(INDIRECT
($K$1)),999),ROW(1:1))-1,0)}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動產生「｛｝」。

(1) INDIRECT($K$1)

將儲存格K1中的內容("仁店")透過 INDIRECT 函數轉換為儲存格範圍(已在步驟 1 定義好「仁店」的儲存格範圍)。

(2) IF(INDIRECT($K$1)<>0,ROW(INDIRECT($K$1)),999)

在陣列公式中，判斷儲存格K1內容(仁店)的儲存格範圍內容是否為空白(=0)，若是則傳回其列號陣列，若否則傳回『999』(這只是一個很大的數)。

(3) SMALL(第(2)式,ROW(1:1))

利用 SMALL 函數找出第(3)式傳回的列號陣列中的最小者。當公式向下複製時，ROW(1:1)=1→ROW(2:2)=3→ROW(3:3)=3→...。可以依序找出第1, 2, 3, ... 較小者。

(4) OFFSET($A$1,第(3)式-1,0)

根據第(4)式傳回的列號利用 OFFSET 函數查詢對應的儲存格內容。

同理：

列出「售價」：

儲存格L2：{=OFFSET($B$1,SMALL(IF(INDIRECT($K$1)<>0,ROW(INDIRECT
($K$1)),999),ROW(1:1))-1,0)}

列出「數量」：

儲存格M2：{=OFFSET(INDIRECT($K$1),SMALL(IF(INDIRECT($K$1)<>0,ROW
(INDIRECT($K$1)),999),ROW(1:1))-3,0)}

複製儲存格K2:M2，貼至儲存格K2:M23。