2013年1月18日 星期五

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

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

 

【準備工作】

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

 

【輸入公式】

儲存格D2:

{=IFERROR(INDEX(Ref,SMALL(IF(Name=D$1,ROW(Ref)-1,FALSE),ROW(1:1)),),"")}

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

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

IF(Name=D$1,ROW(Ref)-1,FALSE):判斷儲存格D1和名稱Name中相符者,找出其對應的Ref 之所在列(例如:ROW(3:3)=3)。

SMALL(IF(Name=D$1,ROW(Ref)-1,FALSE),ROW(1:1)):找出上述 Ref 的最小值。

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

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

接著使用 INDEX 函數,以查表方式求得在 Ref 陣列中的內容。

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

 

【比較】

在另一篇文章中:http://isvincent.blogspot.tw/2013/01/excel_14.html

Ref 是純數字,本例為文字,試著比較其間不同的公式變化。

沒有留言:

張貼留言

好康東東