2014年4月21日 星期一

Excel-查詢同欄中相同內容的資料(OFFSET,陣列公式)

有網友問到:在一個資料清單中,如果同一個內容出現多次,如何下公式全部找出來?因為其使用 VLOOKUP 函數,每次都只是列出相同內容的第一筆,有沒有其他方法可以使用呢?(參考下圖)

【準備工作】

選取儲存格A1:A9,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號。

 

假設:要搜尋的內容置於儲存格A11中,要把所有相同內容的儲存格依序列出。

【輸入公式】

儲存格A13:{=IFERROR(OFFSET($A$1,SMALL(IF(編號=$A$11,ROW(編號),FALSE),ROW(1:1))-1,COLUMN(A:A)-1),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

IF(編號=$A$11,ROW(編號),FALSE):找出編號陣列中和儲存格A11相同內容的儲存格陣列。本例可得陣列:{2,3,4,5,Fasle,False,False,False,False}

SMALL(IF(編號=$A$11,ROW(編號),FALSE),ROW(1:1)):因為 ROW(1:1)=1,所以結果為2。往下複製時,ROW(1:1)→ROW(2:2)=2,會傳回3,依此類推。

利用 OFFSET 函數以相對位址取得以儲存格A2為起始的相對儲存格,即為所求。

再使用 IFERROR 函數,將查不到資料所傳回的錯誤訊息 #NUM!,以空白顯示。

複製儲存格A13,貼至儲存格A13:D20。

沒有留言:

張貼留言

檢視其他文章

好康東東