2011年6月12日 星期日

Excel-社團選社與分析(VLOOKUP,SUMPRODUCT)

在 Excel 中取得一個學校社團的基本資料表(如下圖),本例要根據學生選社的結果(如下下圖),來產生學生選社的結果報表,並進一步分析。

首先將社團基本資料表定義一個名稱:社團。

根據以上的兩個資料表,現在要來產生如下圖的選社結果。本次以VLOOKUP函數來做為查表的工具。

VLOOKUPhttp://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應該要尋找完全符合還是大約符合的值。

 

儲存格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。

1. 選取上圖的所有資料範圍。

2. 按一下 Ctrl+Shfit+F3 鍵。

3. 勾選「頂端列」,即以頂端列的欄位名稱當做該欄的名稱。

image

關於這個分析工作,主要是使用SUMPRODUCT函數即可達成。 

SUMPRODUCThttp://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,往下各列貼上。

沒有留言:

張貼留言

檢視其他文章

好康東東