2015年11月24日 星期二

Excel-資料重組(多列轉多欄)與統計(SUMPRODUCT,ROW,INT,MOD)

最近又要為校內同仁上 Excel 的研習課程,本篇為課程範例。參考先前範例:
本篇要介紹由一個學生升學資料(模擬),每一個學生的資料分成四列呈現,有數百名學生待處理。而要將姓名中間的一個字遮蔽,並且將多列資料轉換為多欄,再統計國立和非國立的人數。

(1) 遮蔽姓名中間的一個字
Excel-資料重組(多列轉多欄)與統計(SUMPRODUCT,ROW,INT,MOD)
為了學生的個人隱私及個資法的限制,常會遇到要隱蔽部分文字內容。
儲存格C3:=LEFT(A3,1)&"○"&RIGHT(A3,1)
LEFT(A3,1):用以取出儲存格A3最左邊的 1 個字。
RIGHT(A3,1):用以取出儲存格A3最右邊的 1 個字。
『&』運算子用以串接字串。
儲存格C2:=A2;儲存格C4:=A4;儲存格C5:=A5。
複製儲存格C2:C5,往下各列貼上。

(2) 將多列資料轉換為多欄
Excel-資料重組(多列轉多欄)與統計(SUMPRODUCT,ROW,INT,MOD)
儲存格E2:=OFFSET($C$1,4*ROW(1:1)-3,0,1,1)
4*ROW(1:1)-3:ROW(1:1)=1,當公式向下複製時會產生ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。本例會傳回:1。
上式代入 OFFSET 函數可以傳回儲存格C1相對下移 1 列的儲存格內容。
儲存格F2:=OFFSET($C$1,4*ROW(1:1)-2,0,1,1)
可以傳回儲存格C1相對下移 2 列的儲存格內容。
儲存格G2:=OFFSET($C$1,4*ROW(1:1)-1,0,1,1)
可以傳回儲存格C1相對下移 3 列的儲存格內容。
儲存格H2:=OFFSET($C$1,4*ROW(1:1),0,1,1)
可以傳回儲存格C1相對下移 4 列的儲存格內容。
複製儲存格E2:H2,往下各列貼上。

(3) 統計國立和非國立的人數
Excel-資料重組(多列轉多欄)與統計(SUMPRODUCT,ROW,INT,MOD)
選取儲存格E1:H580(本例的資料範圍),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:班級、姓名、學校、科系。
觀察資料中如果是國立學校,其校名最前方都冠有『國立』字樣。
儲存格K2:=SUMPRODUCT((班級=J2)*(LEFT(學校,2)="國立"))
儲存格L2:=SUMPRODUCT(1*(班級=J2))-K2
複製儲存格K2:L2,往下各列貼上。

沒有留言:

張貼留言

好康東東