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

广西快三淘宝百科:Excel中的條件格式這么用,數據分析更顯專業化

广西快三专家推荐号 www.otukai.com.cn 來源:中國數據分析行業網 | 時間: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。

 

音符動態簡約分割線

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