在 Excel 中取得一個學校社團的基本資料表(如下圖)，本例要根據學生選社的結果(如下下圖)，來產生學生選社的結果報表，並進一步分析。
首先將社團基本資料表定義一個名稱：社團。
根據以上的兩個資料表，現在要來產生如下圖的選社結果。本次以VLOOKUP函數來做為查表的工具。
VLOOKUP：http://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx (參考微軟網站的說明)
| VLOOKUP：用來搜尋儲存格範圍的第一欄，然後從範圍同一列的任何儲存格傳回一個值。 |
| 語法：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) lookup_value：在表格或範圍的第一欄中搜尋的值。 table_array：包含資料的儲存格範圍。可以使用範圍的參照，也可以使用範圍名稱。 col_index_num：table_array 引數中必須傳回相符值的欄號。 range_lookup：這是一個邏輯值，用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。 |
儲存格F2：=VLOOKUP(E2,社團,2,TRUE)
儲存格G2：=VLOOKUP(E2,社團,3,TRUE)
儲存格H2：=IF(VLOOKUP(E2,社團,4,TRUE)=0,"",VLOOKUP(E2,社團,4,TRUE))
因為VLOOKUP查詢後的結果如果是一個空的儲存格，則會傳回「0」。因此，必須將0轉為空字串。
複製儲存格F2:H2，往下貼至各個儲存格。
特別說明：在使用VLOOKUP函數時，因為 range_lookup 設為 FALSE，則 VLOOKUP 只會尋找完全符合的值。如果 table_array 第一欄中有兩個以上的值與 lookup_value 相符，將會使用第一個找到的值。如果找不到完全符合的值，則傳回 #N/A 錯誤值。
接著，要來分析選社人數中，一二年級各佔多少人，及男女生的人數分別為多少，還要檢查人數超過社團規定人數多少人。(參考下圖)
在設計公式前，先為選社結果的每一欄定義一個名稱：班級、
1. 選取上圖的所有資料範圍。
2. 按一下 Ctrl+Shfit+F3 鍵。
3. 勾選「頂端列」，即以頂端列的欄位名稱當做該欄的名稱。
關於這個分析工作，主要是使用SUMPRODUCT函數即可達成。
SUMPRODUCT：http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx (參考微軟網站的說明)
| SUMPRODUCT：傳回各陣列中所有對應元素乘積的總和。 |
| 語法：SUMPRODUCT(array1, [array2], [array3], ...) array1：要求對應元素乘積和的第一個陣列引數。 array2, array3,...：要求對應元素乘積和的第 2 個到第 255 個陣列引數。 註：各陣列必須有相同的維度(相同的列數，相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。 |
儲存格C2：=SUMPRODUCT(--(社團代碼=選社分析!A2))
公式中的「--」，乃是要將 True/False 的陣列轉換成 1/0 的陣列。
儲存格H2：=SUMPRODUCT((社團代碼=A2)*(LEFT(班級)="1"))
利用LEFT函數取出班級的第1個字(1為一年級)，並將公式中的兩個邏輯判斷相乘，即可將True/False 的陣列轉換成 1/0 的陣列。
儲存格I2：=SUMPRODUCT((社團代碼=A2)*(LEFT(班級)="2"))
儲存格J2：==SUMPRODUCT((社團代碼=A2)*(姓別="男"))
儲存格K2：=SUMPRODUCT((社團代碼=A2)*(姓別="女"))
儲存格L2：=IF(G2>E2,"超過"&G2-E2&"人","")
公式中利用「&」運算子，可以將一些文字(以「""」含括)和運算式連結。
複製儲存格C2:L2，往下各列貼上。
留言列表