最近又要為校內同仁上 Excel 的研習課程,本篇為課程範例。參考先前範例:

Excel-SUMPRODUCT函數範例與說明(研習範例)

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

本篇要介紹由一個學生升學資料(模擬),每一個學生的資料分成四列呈現,有數百名學生待處理。而要將姓名中間的一個字遮蔽,並且將多列資料轉換為多欄,再統計國立和非國立的人數。

 

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

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(1) 人氣()