2015年12月30日 星期三

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

有網友想要根據 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)

沒有留言:

張貼留言

檢視其他文章

好康東東