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

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

Excel-由清單挑選列出多組資料中的其中一組(OFFSET,INDIRECT,陣列公式)


【公式設計與解析】

1. 定義名稱

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

Excel-由清單挑選列出多組資料中的其中一組(OFFSET,INDIRECT,陣列公式)

2. 建立下拉式清單

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

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

Excel-由清單挑選列出多組資料中的其中一組(OFFSET,INDIRECT,陣列公式)

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。

創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()