2018年5月15日 星期二

Excel-將資料表中相同地址的姓名集合在一起(OFFSET,SMALL,COLUMN)

參考下圖,在 Excel 中有一個資料表,如何將相同地址的姓名集合在一起?
Excel-將資料表中相同地址的姓名集合在一起(OFFSET,SMALL,COLUMN)

【公式設計與解析】
首先,選取儲存格A1:B27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、地址。
接著,輸入公式:
儲存格E2:{=IFERROR(OFFSET($A$1,SMALL(IF(地址=$D2,ROW(姓名),""),
COLUMN(A:A))-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格E2,貼至儲存格E2:K7。
(1) IF(地址=$D2,ROW(姓名),"")
在陣列公式中,判斷址址陣列中是否和儲存格D2(地址A)相同,若是,則傳回對應姓名儲存格的列號:若否,則傳回空字串("")。
(2) SMALL(第(1)式,COLUMN(A:A))
利用 SMALL 函數,由小至大取出對應的數值(列號)。當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→....。
(3) OFFSET($A$1,第(2)式-1,0)
將第(2)式的結果帶入 OFFSET 函數,取得對應的儲存格內容(姓名)。
(4) IFERROR(第(3)式,"")
若公式傳回錯誤訊息,則以 IFERROR 函數改顯示空字串(空白)。

沒有留言:

張貼留言

檢視其他文章

好康東東