2016年10月29日 星期六

Excel-用公式篩選符合條件者(OFFSET,ROW,陣列公式)

網友問到:在 Excel 的資料清單中,如何用公式篩選符合條件者?
參考下圖左,是一個『日期、編號、評語』的清單,現在要根據一個『編號』值,篩選出符合該編號的資料內容(日期和評語),該如何處理?
Excel-用公式篩選符合條件者(OFFSET,ROW,陣列公式)
【公式設計與解析】
選取儲存格B1:B26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號。
儲存格E3:
{=OFFSET($A$1,SMALL(IF(編號=$F$1,ROW(編號),999),ROW(1:1))-1,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格EE3,貼至儲存格E3:E14。
(1) IF(編號=$F$1,ROW(編號),999)
在陣列公式中判斷若是編號範圍的儲存格內容和儲存格F1相同者,傳回其列號陣列,若不是,則傳回「999」(這只是很大的一個數)。
(2) SMALL(IF(編號=$F$1,ROW(編號),999),ROW(1:1))
利用 SMALL 函數依序取出列號陣列中的第1, 2, 3, ... 較小值。當公式向下複製時,其中 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
(3) OFFSET($A$1,第(2)式-1,0)
將列號代入 OFFSET 函數,即可查詢到對應的儲存格內容。

同理,儲存格F3:
{=OFFSET($A$1,SMALL(IF(編號=$F$1,ROW(編號),999),ROW(2:2))-1,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格F3,貼至儲存格F3:F14。

沒有留言:

張貼留言

好康東東