2011年7月2日 星期六

Excel-研習人員統計報表(SUMPRODUCT,INDEX,MATCH,VLOOKUP)

延續前三篇文章:

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_numtable_array 引數中必須傳回相符值的欄號。

range_lookup:這是一個邏輯值,用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。

沒有留言:

張貼留言

檢視其他文章

好康東東