2013年2月18日 星期一

Excel-依組別將項目集合在一起(INDEX,SMALL,COUNTIF)

在 Excel 的資料表中中有一個組別的名冊(如下圖左),有人想要依組別將項目集合在一起(如下圖右),該如何處理?

【準備工作】

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

【輸入公式】

儲存格E2:{=IFERROR(INDEX(姓名,SMALL(IF(組別=E$1,序號,),ROW(1:1)+COUNTIF(組別,"<>"&E$1)),),"")}

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

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

 

【公式原理】

IF(組別=E$1,序號,):找出組別名稱和儲存格E1相同的序號陣列。(結果為:符合者得到其序號值,不符合者得到 0。)

COUNTIF(組別,"<>"&E$1):找出組別名稱和儲存格E1不同的的個數。

SMALL(IF(組別=E$1,序號,),ROW(1:1)+COUNTIF(組別,"<>"&E$1)):找出組別名稱和儲存格E1相同的序號陣列中,排除 0 以外的最小值。(因為會有 COUNTIF(組別,"<>"&E$1) 個結果為 0)

INDEX(姓名,SMALL(IF(組別=E$1,序號,),ROW(1:1)+COUNTIF(組別,"<>"&E$1)),):根據上式的序號值,利用 INDEX 函數在姓名陣列中查表找出對應的姓名。

因為上式如果查不到資料,會顯示錯誤訊息(#NUM!),所以利用 IFERROR 函數,將錯誤訊息顯示為空字串。

 

【補充資料】

相關之詳細函數說明,請參閱微軟網站:

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

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

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

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

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

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

沒有留言:

張貼留言

好康東東