學不完．教不停．用不盡

在 Excel 中有一個運動會報名表的某一種格式(參考下圖)，其中 F 代表男生、M 代表女生。每一種項目男生和女生分別最多二人報名(0、1、2人皆可)，如何根據這個資料表來產生摘要表？(參考下下圖)

下圖是要產生的摘要表，為了說明方便，特別建立了一個輔助欄位(H欄)。表中項目和性別是固定不變的內容。

【準備工作】

選取儲存格A1:H26，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，建立名稱：班級、性別、姓名、號碼布、跳高、跳遠、鉛球、鐵餅。

【公式設計與解析】

先來看男生部分：

儲存格H31：｛=SMALL(IF(INDIRECT(A31)=C31,ROW(INDIRECT(A31))-1,
FALSE),COUNTIF($A$31:A31,A31))｝

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上｛｝。

INDIRECT(A31)：將儲存格A31中的文字藉由 INDIRECT 函數轉換為儲存格位址(文字所代表的)，透過巧妙的設計，結合二個資料來源。

IF(INDIRECT(A31)=C31,ROW(INDIRECT(A31))-1,FALSE)：判斷儲存格A31所指儲存格範圍(已定義名稱)是否和儲存格C31相同，若是，則傳回儲存格A31所指儲存格範圍的列號；若否，則傳回 FALSE。本例傳回：{FALSE;FALSE;FALSE;FALSE;5;6FALSE;…}。

COUNTIF($A$31:A31,A31)：計算從儲存格A31至目前的儲存格範圍中，和儲存格A31相同者有幾個。

根據以上二式，藉由 SAMLL 函數取出第1、第2個較小的數(本例第1個取出5、第2個取出6、…)

儲存格B31：=IFERROR(OFFSET($A$1,H31,,,),"")

儲存格D31：=IFERROR(OFFSET($C$1,H31,,,),"")

儲存格E31：=IFERROR(OFFSET($D$1,H31,,,),"")

透過 OFFSET 函數找到對應的內容，並使用 IFERROR 函數將傳回錯誤訊息的儲存格顯示為空白。

同理，女生部分：

儲存格H41：｛=SMALL(IF(INDIRECT(A41)=C41,ROW(INDIRECT(A41))-1,
FALSE),COUNTIF($A$31:I31,A41))｝

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上｛｝。

儲存格B41：=IFERROR(OFFSET($A$1,H41,,,),"")

儲存格D41：=IFERROR(OFFSET($C$1,H41,,,),"")

儲存格E41：=IFERROR(OFFSET($D$1,H41,,,),"")