在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版新增函數的使用

Excel-利用SORT和SORTBY函數進行排序

Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

Excel-亂數重排座位

Excel-資料排序的好幫手-SORT,SORTBY函數(2021版以上)

學不完.教不停.用不盡文章列表

arrow
arrow
    全站熱搜

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