贊助廠商

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

搜尋本部落格文章資料

有網友想要根據 Excel 資料表中的一個含有色彩的樣式清單,挑選指定色彩的儲存格。參考下圖,挑選所有『紅色』的各種樣式。該如何處理?

image

【準備工作】

選取B欄中有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:樣式。

 

【公式設計與解析】

儲存格D3:{=IFERROR(OFFSET($A$1,LARGE(IF((LEFT(樣式,2)=E$1),
ROW(樣式),FALSE),ROW(1:1))-1,),"")}

儲存格E3:{=IFERROR(OFFSET($B$1,LARGE(IF((LEFT(樣式,2)=E$1),
ROW(樣式),FALSE),ROW(1:1))-1,),"")}

以上二式是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

LEFT(樣式,2)=E$1:利用 LEFT 函數篩選所有樣式儲存格中的前二碼(代表色彩),再和儲存格E1來比對。

IF((LEFT(樣式,2)=E$1),ROW(樣式),FALSE):比對結果若相符,則傳回『列號』;若不相符,則傳回『FALSE』。

LARGE(IF((LEFT(樣式,2)=E$1),ROW(樣式),FALSE),ROW(1:1)):利用 LARGE 函數依序找出較大列號第 1, 2, 3, ... 個。

將上式代入 OFFSET 函數求得相對於儲存格B1的儲存格,即為所求。

最後利用 IFERROR 函數將傳回的錯誤訊息顯示為空白。

複製儲存格D3:E3,往下各列貼上。

注意:該公式的結果會依編號的相反順序呈現。

 

【延伸練習】

1. 依色彩文字內容顯示不同色彩

如果想要將儲存格文字色彩依儲存格內容標示的色彩而變,該如何處理?

選取「樣式」儲存格範圍,設定格式化的條件。

Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)

本例設定七個相同的規則類型:使用公式來決定要格式化哪些儲存格。

分別設定不同規則:

(1) 規則:=FIND($E$1,$B2);格式:儲存格填滿較深藍色

如果儲存格E1中指定哪種色,則該色彩所在的儲存格即可變為較深藍色的底色。

Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)

(2) 規則:=LEFT($B2,2)="紫色";格式:文字色彩為『紫色』。

觀察『樣式』中的前二碼均為色彩名稱。

Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)

(3) 規則:=LEFT($B2,2)="橙色";格式:文字色彩為『橙色』。

(4) 規則:=LEFT($B2,2)="黑色";格式:文字色彩為『黑色』。

(5) 規則:=LEFT($B2,2)="綠色";格式:文字色彩為『綠色』。

(6) 規則:=LEFT($B2,2)="藍色";格式:文字色彩為『藍色』。

(7) 規則:=LEFT($B2,2)="紅色";格式:文字色彩為『紅色』。

依照此規則,即可依樣式中的色彩,文字自動變化為該色彩。

Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)

 

2. 篩選相同色彩

如果你啟用「自動篩選」,則可以在B欄中使用『依色彩篩選』:

Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)

利用篩選功能即可手動篩選相同色彩的儲存格:

Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)

 

3.使用下拉式清單挑選色彩

如果要在儲存格E1中使用下拉式清單來挑選一個色彩,可以透過「資料驗證」工具來處理。先選取儲存格E1,再選取[資料/資料工具]功能表中的「資料驗證」。

Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)

在[資料驗證]對話框中,設定:

儲存格內允許:清單

來源:橙色,灰色,紅色,紫色,黑色,綠色

Excel-挑選樣式清單中相同色彩的儲存格(OFFSET,ROW,LARGE)

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

學不完.教不停.用不盡

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


留言列表 (4)

發表留言
  • 婷婷
  • 非常感謝大師你的指點迷津,讓我受益良多^^

  • Ciel Shen
  • 老師你好,想請教下拉式選單的問題。
    表格的標題為某公司OO部門三節獎金印領清冊,希望在00部門的欄位設計成下拉式選單後自動帶出部門員工資料,例如姓名、職稱等,不知道該如何設定下拉式選單?
    已有建立全公司的員工名冊。
  • 訪客
  • 請問關於 =FIND($E$1,$B2) 設定較深的底色,
    因為儲存格只能會顯示最上位階的格式化條件,即便寫入相同的指令,都會被
    =LEFT($B2,2)="橙色"給蓋過去,
    B欄位都無法像老師一樣顯示出較深色的底色,該怎麼設定呢?
  • 悄悄話

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼