最近又要為校內同仁上 Excel 的研習課程，本篇為課程範例。參考先前範例：
Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)
本篇要介紹由一個學生升學資料(模擬)，每一個學生的資料分成四列呈現，有數百名學生待處理。而要將姓名中間的一個字遮蔽，並且將多列資料轉換為多欄，再統計國立和非國立的人數。
(1) 遮蔽姓名中間的一個字
為了學生的個人隱私及個資法的限制，常會遇到要隱蔽部分文字內容。
儲存格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) 將多列資料轉換為多欄
儲存格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) 統計國立和非國立的人數
選取儲存格E1:H580(本例的資料範圍)，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：班級、姓名、學校、科系。
觀察資料中如果是國立學校，其校名最前方都冠有『國立』字樣。
儲存格K2：=SUMPRODUCT((班級=J2)*(LEFT(學校,2)="國立"))
儲存格L2：=SUMPRODUCT(1*(班級=J2))-K2
複製儲存格K2:L2，往下各列貼上。
留言列表