2013年1月14日 星期一

Excel-將相同資料集合在同一欄(陣列公式)

有網友問到:在 Excel 中有一個資料表(如下圖),其中有 Ref 和 Name 兩個欄位,如何將相同 Name 的資料集合在同一欄,並且列出 Ref,該如何處理?

【準備工作】

選取儲存格A1:B6,按一下 Ctrl+Shift+F3 鍵,定義名稱:Ref、Name。

 

【輸入公式】

儲存格D2:{=IFERROR(SMALL(IF(Name=D$1,Ref,FALSE),ROW(1:1)),"")}

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

複製儲存格D2,貼至儲存格D2:H16。

IF(Name=D$1,Ref,FALSE):判斷儲存格D1和名稱Name中相符者,找出其對應的Ref,其形成的陣列為 { Fasle,Fasle,Fasle,Fasle,Fasle,Fasle,7,Fasle, Fasle, Fasle, Fasle, Fasle, Fasle, 14,Fasle, Fasle, Fasle, Fasle, 19,Fasle, Fasle, Fasle, Fasle, Fasle }

SMALL(IF(Name=D$1,Ref,FALSE),ROW(1:1)):找出上述 Ref 的最小值(本例為 7 ),其中Fasle 會被視為 0。

當公式往下複製時,ROW(1:1)=1 會變為 ROW(2:2)=2 → ROW(3:3)=3 → …。

因此透過 SMALL 函數可以找出最小值(7)、第二小值(14)、第三小值(19)、…。

因為這個公式會被複製到儲存格D2:H16,所以使用 IFERROR 函數可以將因為找不到對應值產生的錯誤以空白顯示。

2 則留言:

  1. 您好, 謝謝您的回覆.
    再想請教, 如ref不是數字, 是文字, 以上公式可行嗎?

    回覆刪除
    回覆
    1. 請再參考 http://isvincent.blogspot.tw/2013/01/excel_18.html 的說明。

      刪除

檢視其他文章

好康東東