延續前三篇文章：
http://isvincent.blogspot.com/2011/07/excel-countif.html
http://isvincent.blogspot.com/2011/07/excel-indirect.html
http://isvincent.blogspot.com/2011/07/excel.html
這次，要根據研習者的基本資料，進行各種資料的統計。(參考下圖)
先選取儲存格J1:N30，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」。定義了：姓名、單位、職務、性別、時數等名稱，並將儲存格J1:N30命名為：資料。
(1) 求各單位參與人數及時數
儲存格Q2：=SUMPRODUCT(--(單位=P2))
公式中的「--」乃是要將「單位=P2」的 True/False 陣列，轉換為 1/0 的陣列。
儲存格R2：=SUMIF(單位,P2,時數)
複製儲存格Q2:R2，貼至儲存Q2:R8。
(2) 求各單位不同職務的參與人數
儲存格Q11：=SUMPRODUCT(--(單位=$P11),--(職務=Q$10))
複製儲存格Q11，貼至儲存格Q11:S17。
(3) 求各單位不同職務的男女生參與人數及參與時數
儲存格Q20：=SUMPRODUCT(--(職務=$P20),--(性別=Q$19))
複製儲存格Q20，貼至儲存格Q20:R22。
儲存格S20：=SUMIF(職務,P20,時數)
複製儲存格S20，貼至儲存格S20:S22。
(4) 以清單選取人名查詢個人基本資料(使用INDEX函數)
選取儲存格P24，設定資料驗證：「儲存格內允許：清單；來源：=姓名」
儲存格P26：=INDEX(資料,MATCH($P$24,姓名,0)+1,COLUMN(B:B))
複製儲存格P26，貼至儲存格P26:S26。
利用INDEX函數來進行查詢工作：
MATCH($P$24,姓名,0)+1：查詢儲存格P24中的姓名位於姓名欄中的第幾列。
COLUMN(B:B)：COLUMN(B:B)=2，表示要查詢第2欄，向右複製後，COLUMN(B:B)=2→COLUMN(C:C)=3→COLUMN(D:D)=4→COLUMN(E:E)=5，可以查詢各欄位。
(5) 以清單選取人名查詢個人基本資料(使用VLOOKUP函數)
儲存格P30：=VLOOKUP($P$28,資料,COLUMN(B:B))：
複製儲存格P30，貼至儲存格P30:S30。
利用VLOOKUP函數來進行查詢工作：
COLUMN(B:B)：COLUMN(B:B)=2，表示要查詢第2欄，向右複製後，COLUMN(B:B)=2→COLUMN(C:C)=3→COLUMN(D:D)=4→COLUMN(E:E)=5，可以查詢各欄位。
詳細函數說明，請參閱微軟網站：
SUMPRODUCT：http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx
| SUMPRODUCT：傳回各陣列中所有對應元素乘積的總和。 |
| 語法：SUMPRODUCT(array1, [array2], [array3], ...) array1：要求對應元素乘積和的第一個陣列引數。 array2, array3,...：要求對應元素乘積和的第 2 個到第 255 個陣列引數。 註：各陣列必須有相同的維度(相同的列數，相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。 |
INDEX：http://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx
| INDEX：傳回表格或範圍內的某個值或值的參照。 |
| 語法：INDEX(array, row_num, [column_num]) Array：儲存格範圍或陣列常數。 Row_num：選取陣列中傳回值的列。 Column_num：選取陣列中傳回值的欄。 |
MATCH：http://office.microsoft.com/zh-tw/excel-help/HP010342679.aspx
| MATCH：搜尋某儲存格範圍內的指定項目，然後再傳回該項目在範圍內的相對位置。 |
| 語法：MATCH(lookup_value, lookup_array, [match_type]) lookup_value：在 lookup_array 中尋找比對的值。 lookup_array：要搜尋的儲存格範圍。 match_type：這是一個數字，其值有三種可能：(預設值為 1) 1 或省略：找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列。 0：找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。 -1：找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序。 |
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應該要尋找完全符合還是大約符合的值。 |
留言列表