2015年9月17日 星期四

Excel-藉由定義名稱轉換表格來摘要資料(陣列公式,OFFSET,INDIRECT)

在 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,,,),"")

沒有留言:

張貼留言

檢視其他文章

好康東東