2011年11月7日 星期一

Excel-摘要分組名單(VLOOKUP+陣列)

在 Excel 中取得一個學生的分組名單(如下圖),現在要依組別摘要班級和姓名的資料(如下下圖),該如何處理?

摘要後的組別資料:

【準備工作】

先將資料範圍定義名稱:

1. 選取資料範圍(本例為儲存格A1:D193),按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」選項。定義四個名稱:序號、班級、姓名、組別。

2. 選取資料圍(本例為儲存格A1:D193),選取[公式/已定義名稱]中的「名稱管理員」,定義這個範圍的名稱為:資料。

 

【設定公式】

儲存格F2:{=VLOOKUP(SMALL(IF(組別=F$1,序號),ROW(1:1)),資料,2,FALSE)}

儲存格G2:{=VLOOKUP(SMALL(IF(組別=F$1,序號),ROW(1:1)),資料,3,FALSE)}

以上二式為陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。

IF(組別=F$1,序號):根據組別中,如果符合儲存格F1中的名稱,則找出對應的序號。

SMALL(IF(組別=F$1,序號),ROW(1:1)):找出由小到大的序號。

藉由 VLOOKUP 函數找出符合資料的第1個、第2個、第3個、…。

複製儲存格F2:G2,貼至儲存格F2:M2。複製儲存格F2:M2,往下各列貼上。

 

【設定不顯示錯誤訊息

因為往下複製公式時,會因為VLOOKUP函數查詢對應資料時,會產生「#NUM!」的錯誤訊息(參考下圖,找不到對應資料時產生訊息)。該如何消除錯誤訊息(參考下下圖)?

修正之前的公式:

儲存格F2:{=IFERROR(VLOOKUP(SMALL(IF(組別=F$1,序號),ROW(1:1)),資料,2,FALSE),"")}

儲存格G2:{=IFERROR(VLOOKUP(SMALL(IF(組別=F$1,序號),ROW(1:1)),資料,3,FALSE),"")}

利用 IFERROR 函數將發生錯誤訊息時,設定為顯示空白。

1 則留言:

  1. 請問為何我複製儲存格的公式之後,只抓的到第一位人員的姓名,是因為組別合併儲存格的關係嗎?

    回覆刪除

好康東東