2016年1月15日 星期五

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

下圖右是一個 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。

沒有留言:

張貼留言

好康東東