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

Excel-在資料矩陣中篩選資料(SUBSTITUTE,OFFSET,INT,MOD)


【公式設計與解析】

(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的數值,計算資料位於第幾欄。

Excel-在資料矩陣中篩選資料(SUBSTITUTE,OFFSET,INT,MOD)

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

創作者介紹

學不完.教不停.用不盡

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