2016年9月28日 星期三

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

網友問到:在 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。

沒有留言:

張貼留言

檢視其他文章

好康東東