当前位置 > 首頁 > 技術熱點 > Excel中的條件格式這麽用,數據分析更顯專業化

Excel中的條件格式這麽用,數據分析更顯專業化

来源:中国数据分析行业网 | 时间:2019-09-05 | 作者:数据委

條件格式就是根據預先設置的條件,對滿足不同條件的單元格應用指定的格式。

 

在Excel中,不僅內置有5種條件格式規則,還可以自定義條件格式規則,讓滿足條件的數據呈現成百上千種變化,並且當單元格中的數據發生變化時,會自動評估並應用指定的格式。

 

Excel內置的條件格式規則

Excel提供了許多內置的格式規則可供選擇使用。對這些內置的規則,不需要做過多的設置,幾乎拿來就可以使用,減少了手動設置的麻煩。

 

1、突出顯示滿足條件的單元格

在對數據進行統計分析時,如果要突出顯示表格中的一些數據,如大于某個值、小于某個值、等于某個值、介于某個值之間、包含某個文本等,可以使用條件格式中的突出顯示單元格規則。

 

例如,對員工總成績在420~450的數據進行查看,具體操作步驟如下:

 

Step01? 选择“总成绩”列数据区域,单击【开始】选项卡下【样式】组中的【条件格式】按钮,在弹出的下拉列表中选择【突出显示单元格规则】选项,在弹出的子列表中选择【介于】选项。

 

Step02 打开【介于】对话框,在其中对条件格式进行设置,单击【确定】按钮。此时,介于420~450的数据将以黄色底纹和深黄色文本显示。

 

2、突出顯示最大值最小值

當需要突出顯示靠前或靠後及高于或低于平均值的單元格時,可以使用條件格式中的最前/最後規則來實現。該規則的使用方法與突出顯示單元格規則的方法基本相同,如下圖所示爲突出顯示前6項的效果。

 

在使用突出顯示單元格規則和最前/最後規則時,如果單元格中有重複的數值,那麽突出顯示的項數可能會與設置的項數有所增加,如下圖所示。

 

Excel內置的自定義條件格式規則

如果Excel內置的條件格式規則不能满足需要,则可以使用自定义规则和显示效果的方式来创建需要的条件格式。例如,对员工培训考核成绩进行分析时,需要将各项考核成绩为“>=90”的数值使用“小红旗”标注出来,使用内置的图标集条件格式是不能完成的,需要自定义格式规则,具体操作步骤如下。

 

Step01 选择需要设置条件格式的数据区域,在【条件格式】下拉列表中选择【新建规则】选项,打开【新建格式规则】对话框,将【选择规则类型】设置为【基于各自值设置所有单元格的格式】,【格式样式】设置为【图标集】,然后设置图标的样式,单击【确定】按钮,如下图所示。

 

Step02 返回工作表中,即可看到考核成绩为“>=90”的单元格数值前均标有“小红旗”,效果如下图所示。

 

用公式自定義條件格式規則

【使用公式確定要設置格式的單元格】規則類型是通過設置公式來確定條件的,靈活應用該規則,可以擴展條件格式的應用範圍,使其滿足各類數據的分析需要。

 

1、突出顯示重複出現的數據

在人力資源管理過程中,爲了及時查看輸入的數據是否重複時,可以通過函數公式和條件格式的結合來突出顯示輸入的重複數據,這樣可以方便查看,並且確認重複的數據是否需要更改。

 

例如,用公式定義規則,突出顯示重複輸入的電話號碼,具體操作步驟如下。

 

Step01 选择 J2:J16 单元格区域,单击【条件格式】按钮,在弹出的下拉列表中选择【新建规则】选项,如下图所示。

 

Step02 打开【新建格式规则】对话框,在【选择规则类型】列表框中选择【使用公式确定要设置格式的单元格】选项。

 

在【編輯規則說明】參數框中輸入公式“=COUNTIF(J$2:J2,J2)>1”,單擊【格式】按鈕,如下圖所示。

 

Step03 打开【设置单元格格式】对话框,在其中对条件格式的单元格格式进行设置,单击【确定】按钮,如下图所示。

 

Step04 在设置条件格式的单元格区域中输入员工的联系电话,当输入的联系电话重复时,将会以设置的单元格格式突出显示,效果如下图所示。

 

2、突出顯示周末日期

在制作考勤表、加班統計表時,經常需要突出顯示雙休日。在Excel中,使用公式定義條件格式,也能輕松智能地實現。例如,下圖所示爲使用公式定義的條件格式,突出顯示了考勤表中的雙休日。

 

3、合同到期提醒

勞動合同管理是人力資源部門很重要的一項工作,它直接關系著員工的利益。因此,HR在管理過程中,當勞動合同要到期時,需要及時續簽或處理,但員工的勞動合同到期並不是同一時間,差不多每個月都有合同到期的可能,爲了避免工作中出現纰漏,HR可以通過條件格式設置合同到期提醒。

 

例如,下圖使用公式“=AND($J2>TODAY(),$J2-TODAY()<7)”對A2:K26單元格區域中滿足條件的單元格設置格式突出顯示,該公式中設置了兩個條件對J2單元格中的日期進行判斷:第一個條件是大于系統當前日期;第二個條件是和系統當前日期的間隔小于7。

 

音符動態簡約分割線

這些知識點,你都了解了嗎?