贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

在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,並設定符合條件時,顯示和底色相同的文字色彩(當文字色彩和底色一樣時,將看不到文字。),這樣就可以把第一個以外的文字隱藏了。

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • 1373309441@qq.com
  • 請問...
    1.
    儲存格G2:=SMALL($B$2:$B$24,ROW(1:1))
    觀察因為班級全部由數字組成,所以利用SMALL函數,將其由小到大取出。

    如果是文字,可以怎樣做?? 謝謝

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼