2015年1月15日 星期四

Excel-多條件查表應用(陣列應用,INT,ADDRESS,INDIRECT,SMALL)

觀察下圖左,有網友想要在 Excel 中具有型式、尺寸、變化等構成的資料表中,再由不同長度來對照查詢到「時間」,就像下圖右這樣。

因為要用以比對的條件比較多個,所以沒有可以直接運用的單一公式。再觀察其資料表中長度部分是間隔 1000 的規則,而不是亂數分佈,還好,這樣就可以設計公式來查表。不過得用到「陣列公式」的做法。

【準備工作】

假設資料範圍是儲存格A2:E72,選取儲存格A2:C72,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:型式、尺寸、變化。定義名稱有助於縮短公式,並且增加公式理解。

 

【公式設計】

儲存格k3:{=INDIRECT(ADDRESS(SMALL(IF((型式=G3)*(尺寸=H3)*(變化=I3),
(ROW(型式)),FALSE),1)+INT((J3-1)/1000),5))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。

為了解說方便,將上圖的K欄再細分為二欄(參考下圖)。

(1) 儲存格K3:{=SMALL(IF((型式=G3)*(尺寸=H3)*(變化=I3),(ROW(型式)),FALSE),1)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。

(型式=G3)*(尺寸=H3)*(變化=I3):在 IF 函數中的條件使用「*」,相對於執行邏輯 AND 運算,也就說三個條件均需符合者,結果才會傳回 TRUE

ROW(型式):在陣列公式中代表 ROW(2:72),即陣列內容為{ 2, 3, 4, …, 72 }。

IF((型式=G3)*(尺寸=H3)*(變化=I3),(ROW(型式)),FALSE):在陣列公中會傳回符合三個條件的所有列號。以本例來看,會傳回 { 3, 4, 5, 6 } 陣列。

最後透過 SMALL 函數取得上式中傳回陣列的最小值,本例結果為:3。

 

(2) 儲存格L3:=INDIRECT(ADDRESS(K3+INT((J3-1)/1000),5))

INT((J3-1)/1000):計算儲存格J3中的內容屬於第幾個1000,即 0~1000 傳回 0、1001~2000 傳回 1、2001~3000 傳回 2、…。本例傳回 1。

K3+INT((J3-1)/1000):傳回符合長度所對照的時間在第幾列,本例傳回 4(=3+1)。

使用 ADDRESS 函數取得在E欄(=5)中的第 K3+INT((J3-1)/1000) 列(=4)所對應的位址,本例會傳回:$E$4。

最後使用 INDIRECT 函數,將儲存格位址:$E$4,轉換為儲存格內容:1.50。

沒有留言:

張貼留言

檢視其他文章

好康東東