學不完．教不停．用不盡

觀察下圖左，有網友想要在 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。