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

石曉玲 楊立功

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

關鍵詞：計算機二級;Excel;條件格式

中圖分類號：G642 ? ? ? ?文獻標識碼：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單元格條件格式的設置都是必考點，并且考察難度在逐漸提高1。實際上這個知識點在生活中也非常實用，正確設置后能夠極大提高數(shù)據(jù)的可視化程度，因此靈活掌握條件格式的設置是考試和實際應用的必然要求。

1 條件格式的概念

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

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

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

2 條件格式的功能和類型

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

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

2.1基于各類特征

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

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

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

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

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

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

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

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

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

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

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

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

4 小結

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

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

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

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

參考文獻：

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

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

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

[4] 張寧.玩轉Office輕松過二級[M].北京：清華大學出版社，2019.

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