在Excel中輸入一個資料表,如下圖左半部,班級是不規則順序輸入的。如果要自動轉換成右半部,變成依班級順序排列,而且只有班級名稱的第一筆才顯示,其餘班級名稱不顯示。
1.
儲存格G2:=SMALL($B$2:$B$24,ROW(1:1))
觀察因為班級全部由數字組成,所以利用SMALL函數,將其由小到大取出。
2.
儲存格H2:{=OFFSET($A$1,SMALL(IF($B$2:$B$24=G2,ROW(INDIRECT("$A$2:$A$24"))-1),COUNTIF($G$2:G2,G2)),,)}
這是陣列公式,輸入請按Ctrl+Shift+Enter鍵。目的是將SMALL函數所對應的班級來找出對應的編號。
其中「ROW(INDIRECT("$A$2:$A$24"))-1」,在陣列公式中會產生{1,2,3,…,23}的陣列內容。
其中「COUNTIF($G$2:G2,G2))」,找出某個班級名稱在「相同的」班級名稱上位於第幾個位置。
其中「SMALL(IF($B$2:$B$24=G2,ROW(INDIRECT("$A$2:$A$24"))-1),COUNTIF($G$2:G2,G2))」,在陣列公式中為找出指定的班級在第幾列。
透過「OFFSET($A$1,第幾列,,)」來找出編號號。
3.
儲存格I2:=VLOOKUP($H2,$A$1:$E$24,COLUMN(C2),FALSE)
複製儲存格I2到儲存格I2:K2。
使用VLOOKUP函數來根據編號找到姓名、檢定和級別欄位內容。
4.
然後將儲存格G2:K2複製到儲存格G2:K24。
5.
如何只顯示第一次出現的班級名稱,其餘則不顯示呢?可以利用設定格式化條件的方式來處理。
選取儲存格G3,設定公式的內容為:=COUNTIF($G$2:$G3,$G3)>1,並設定符合條件時,顯示和底色相同的文字色彩(當文字色彩和底色一樣時,將看不到文字。),這樣就可以把第一個以外的文字隱藏了。
【延伸學習】
Excel-2021版新增函數進行篩選、查詢、排序之綜合練習
留言列表