基于計算機二級考點-Excel設(shè)置條件格式的探究

石曉玲 楊立功

摘要：在Excel中進(jìn)行各種類型的單元格條件設(shè)置是計算機等級考試的要點，但這個知識點一直是困擾著考生的難點，也是教師教學(xué)中的重點。本文嘗試就此做探討，提出條件格式設(shè)置的種類和方法，并給出操作中的要點和注意事項。

關(guān)鍵詞：計算機二級;Excel;條件格式

中圖分類號：G642 ? ? ? ?文獻(xiàn)標(biāo)識碼：A

文章編號：1009-3044（2021）32-0160-03

Research on Setting Conditional Formatting in Excel Cell of NCRE Level 2

SHI Xiao-ling，YANG Li-gong

（Taizhou Polytechnic College， TaiZhou 225300， China）

Absrtact： How to set Conditional Formatting in Excel Cells is the emphasis of NCRE，but it has been beset our students， the teacher also think this is the difficulty of teaching. This paper has done a preliminary study about it， and put forward the key points and precautions in it.

Key words： NCRE level 2; Excel; conditional format

無論是在全國或各地組織的計算機二級office考試中，對于Excel單元格條件格式的設(shè)置都是必考點，并且考察難度在逐漸提高1。實際上這個知識點在生活中也非常實用，正確設(shè)置后能夠極大提高數(shù)據(jù)的可視化程度，因此靈活掌握條件格式的設(shè)置是考試和實際應(yīng)用的必然要求。

1 條件格式的概念

所謂條件格式是指，預(yù)先設(shè)置某種單元格格式或圖形效果，并在指定的條件被滿足時自動應(yīng)用于目標(biāo)單元格。并且這種格式設(shè)置是動態(tài)的，即當(dāng)單元格中的數(shù)值發(fā)生變化時，對應(yīng)的格式也會自動改變。

在實際生產(chǎn)生活中，這樣的設(shè)置需求是比較多的，典型的應(yīng)用場景如：標(biāo)識出輸入錯誤工號的重復(fù)值;在成績匯總表中用紅色字體標(biāo)識出不及格的成績;使用黃色底紋突出顯示下個月過生日的員工;使用色階或數(shù)據(jù)條表示各城市降水量的差別等。有了條件格式這個利器使得數(shù)據(jù)差異一目了然，表現(xiàn)力直觀性大大提高。

基于如此廣泛實用的功能，在全國計算機等級考試中，設(shè)置單元格條件格式也是考試的重點。在目前題庫34套題中，有21道題涉及該知識點的考核，其中，既有簡單地使用內(nèi)置功能進(jìn)行條件格式設(shè)置，也有復(fù)雜的使用函數(shù)公式的規(guī)則設(shè)置，甚至還出現(xiàn)了多條件規(guī)則設(shè)置2。

2 條件格式的功能和類型

目前二級考試采用的版本Excel2016中，能夠進(jìn)行多種條件格式規(guī)則設(shè)置，如表 1所示：

前兩類條件格式的設(shè)置簡單，效果直觀。在二級考試樣題及實際應(yīng)用舉例如下：

2.1基于各類特征

例1.第30套“為各省市選票抽樣率”工作表的數(shù)據(jù)區(qū)域，以三種不同的字體顏色和單元格底紋在“抽樣率”列分別標(biāo)記出最高值、最低值和高于平均抽樣率的單元格。設(shè)置方法和效果如圖 1所示。

例2.工會需要在每個月底提交下個月生日員工清單給禮品店，請通過條件格式動態(tài)標(biāo)記下個月的生日數(shù)據(jù)3，如圖 2所示：

2.2內(nèi)置的單元格圖形效果樣式

采用數(shù)據(jù)條、色階、圖標(biāo)集能夠極大提高數(shù)據(jù)的可讀性，表達(dá)數(shù)據(jù)特征，在實際應(yīng)用時效果顯著，也是考核的內(nèi)容之一。第25套Excel試題，計算全年合計降水量，對其應(yīng)用實心數(shù)據(jù)條條件格式，并不顯示數(shù)據(jù)本身。效果及設(shè)置方法如圖 3所示。

2.3在條件格式中使用公式和函數(shù)

通過公式或函數(shù)確定較為復(fù)雜的條件，可以為特定的單元格設(shè)置格式。比如“為非空且偶數(shù)行的單元格填充紅色底紋，并添加上下邊框”，如何確定非空偶數(shù)行這兩個條件，使用軟件內(nèi)置的設(shè)置條件不能達(dá)到目標(biāo)，必須通過函數(shù)和公式確定條件4。這部分內(nèi)容需要函數(shù)知識的支撐，是考生感覺較困難之處，但同時是當(dāng)前考試重點和方向。

具體操作方法是：選擇數(shù)據(jù)區(qū)域即B4：H26單元格區(qū)域，在“新建格式規(guī)則”對話框中選擇“使用公式確定要設(shè)置格式的單元格”，在設(shè)置格式框中輸入“=AND（B4<>""，MOD（ROW（），2）=0）”，單擊“格式”按鈕，并在打開的單元格格式對話框中設(shè)置底紋和框線。

說明：在對話框中錄入公式時，注意必須先輸入“=”，使用“<>”””判斷單元格不為空，綜合使用MOD函數(shù)、ROW（）判斷當(dāng)前單元格為偶數(shù)行，具體方法如圖 4所示。

3 多個條件格式規(guī)則

對于同一個單元格，可以設(shè)置多條件規(guī)則。如果各個規(guī)則互不沖突，將同時作用在當(dāng)前單元格。如同時為單元設(shè)置字體、底紋和邊框;如果規(guī)則沖突，將之應(yīng)用優(yōu)先級高的規(guī)則，優(yōu)先級低的規(guī)則將被覆蓋，不能顯示。

例，為表格中的數(shù)據(jù)添加條件格式：將年齡最小的10個人設(shè)置紅色文本黃色底紋，將年消費金額最低的5位顧客所在的整行記錄的文本顏色設(shè)置為加粗傾斜綠色（注意：如果該顧客屬于年齡最小的10位顧客，則年齡數(shù)值應(yīng)保持為紅色文本黃色底紋）。

分別設(shè)置年齡最小的單元格條件格式和消費最低的5位顧客行的條件格式，如圖 5所示。在“條件格式規(guī)則管理器”對話框中，將列出所有規(guī)則，越排在上面的規(guī)則優(yōu)先級別越高。如需調(diào)整，可選中該規(guī)則，單擊對話框右上角的和按鈕調(diào)整在列表中的先后順序，即可調(diào)整規(guī)則的優(yōu)先級別。

4 小結(jié)

條件格式的設(shè)置，可以快速對特定單元格進(jìn)行必要的標(biāo)識，使數(shù)據(jù)更加直觀易讀，表現(xiàn)力得到大大提高。在設(shè)置條件格式時還需要注意幾點：

①對同一單元格設(shè)置多種條件格式時，應(yīng)根據(jù)需要調(diào)整各個規(guī)則的優(yōu)先級別，確保最終顯示結(jié)果符合要求;

②當(dāng)存在多個條件格式規(guī)則時，優(yōu)先級高的規(guī)則先執(zhí)行，次一級規(guī)則后執(zhí)行，直至所有規(guī)則執(zhí)行完畢。另外，還可以借助“如果為真則停止”規(guī)則，強制終止規(guī)則的繼續(xù)執(zhí)行，實現(xiàn)對數(shù)據(jù)的集中的、有條件的篩選;

③條件格式的優(yōu)先級別高于單元格格式，即兩者格式?jīng)_突時，顯示條件格式的設(shè)置結(jié)果。如果刪除條件格式的設(shè)置內(nèi)容，單元格格式仍將保留。

參考文獻(xiàn)：

[1] 教育部考試中心.全國計算機等級考試二級教程MS Office高級應(yīng)用[M] .北京：高等教育出版社，2015.

[2] 未來教育教學(xué)與研究中心;全國計算機等級考試上機考試題庫二級MS Office高級應(yīng)用[M]. 成都：電子科技大學(xué)出版社，2018.

[3] ExcelHome. Excel2013應(yīng)用大全[M].北京：人民郵電出版社，2015.

[4] 張寧.玩轉(zhuǎn)Office輕松過二級[M].北京：清華大學(xué)出版社，2019.

【通聯(lián)編輯：王力】