學不完．教不停．用不盡

下圖右是一個 Excel 的資料表，是一個矩陣式的資料清單，要如何篩選出同類型的資料？例如：選取『戊』開頭的資料內容。

 

【公式設計與解析】

(1)

儲存格A2：{=SMALL(IF((SUBSTITUTE($D$1:$H$10,$B$1,"")<>$D$1:$H$10),
(ROW($D$1:$H$10)-1)*5+COLUMN($D$1:$H$10),999),ROW(1:1))-3}

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

SUBSTITUTE($D$1:$H$10,$B$1,"")<>$D$1:$H$10：利用 SUBSTITUTE 函數判斷在儲存格陣列中以儲存格B1文字置換為空白，其結果如果和原儲存格陣列內容不相同者，表示該儲存格內容含有儲存格B1的文字。

(ROW($D$1:$H$10)-1)*5+COLUMN($D$1:$H$10)：將資料矩陣的位置轉換一個數值。

參數 999，是隨意假設一個較大的數值。

在陣列公式中以 SMALL 函數配合 ROW 函數，依序取出第 1, 2, 3, 4, ...較小的數值。

 

(2)

儲存格B2：=OFFSET($D$1,INT((A2-1)/5),MOD(A2-1,5))

INT((A2-1)/5)：藉由儲存格A2的數值，計算資料位於第幾列。

MOD(A2-1,5)：藉由儲存格A2的數值，計算資料位於第幾欄。

複製儲存格A2:B2，貼至儲存格A2:B19。