2012年3月7日 星期三

Excel-依組別自動填入姓名

在 Excel 中有一個組別、姓名的基本資料表(參考下圖左),如何根據這個資料表自動將姓名填入各個組別中?(參考下圖左)

【準備工作】

選取儲存格A1:B28,按一下 Ctrl+Shift+F3 鍵,定義名稱:組別、姓名。

 

【輸入公式】

(1) 基本

儲存格E2:{=SMALL(IF(組別=$D2,ROW(組別),FALSE),COLUMN(A:A))}

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

複製儲存格E2,貼至儲存格E2:J7。

IF(組別=$D2,ROW(組別),FALSE):找出組別中合於儲存格D2的列號陣列。

SMALL(IF(組別=$D2,ROW(組別),FALSE),COLUMN(A:A)):找出第1個、第2個、…合於儲存格D2組別的列號。COLUMN(A:A)=1,往右複製後會變成COLUMN(B:B)=2、COLUMN(C:C)=3、…。

此公式可能產生錯誤訊息。

(2) 進一步

儲存格E10:{=IFERROR(INDEX(姓名,SMALL(IF(組別=$D10,ROW(組別),FALSE),COLUMN(A:A))-1),"")}

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

複製儲存格E10,貼至儲存格E10:J15。

根據(1)的說明,透過 INDEX 函數,將上述列號代入以查表方式求取姓名。(將列號-1的目的,是因為資料中的第一列是標題,不是資料。)

藉由 IFERROR 函數,將錯誤訊息以空字串顯示。

 

【補充說明】

相關函數說明,可以參考微軟網站:

INDEXhttp://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

INDEX:傳回表格或範圍內的某個值或值的參照。

語法:INDEX(array, row_num, [column_num])

Array:儲存格範圍或陣列常數。

Row_num:選取陣列中傳回值的列。

Column_num:選取陣列中傳回值的欄。

 

沒有留言:

張貼留言

檢視其他文章

好康東東