這些Excel條件格式和資料有效性套路,你必須會
突出顯示重復資料
1、選中A2:A11儲存格區域,新建格式規則
2、使用公式為:
=COUNTIF(A$2:A2,A2)>1
3、設置突出顯示的格式
提示:
COUNTIF函數第一參數使用A$2:A2,表示從A2儲存格開始至公式所在行的A列。在這個動態擴展的範圍中,統計有多少個儲存格與公式所在行的內容相同。
突出顯示每種商品的最低價格
1、選中B4:F13儲存格區域,新建格式規則
2、使用公式為:
=B4=MIN($B4:$F4)
3、設置突出顯示的格式
提示:
1、MIN($B4:$F4)部分計算出公式所在行的最小值。
2、然後判斷B4(作用儲存格),是否等於公式所在行的最小值。
3、在條件格式中,針對作用儲存格的設置,將被作用到所選區域的每一個儲存格。
突出顯示已完成合同
1、選中A4:F13儲存格區域,新建格式規則
2、使用公式為:
=$F4="是"
3、設置突出顯示的格式
提示:
本例需要特別注意引用方式,因為每一列都是依據F列的判斷結果來顯示條件格式,所以要使用列絕對引用。
突出顯示週末日期
1、選中A3:F7儲存格區域,新建格式規則
2、使用公式為:
=WEEKDAY(A$3,2)>5
3、設置突出顯示的格式
提示:
1、WEEKDAY函數返回某日期為星期幾。第二參數使用2,表示以1~7表示星期一到星期日。
2、對第三行中的日期進行判斷後,如果數值大於5,即表示該日期為週六或是周日。
合同到期提醒
1、選中A4:D7儲存格區域,新建格式規則
2、使用公式為:
=AND($D4>TODAY(),$D4-TODAY()<7)
3、設置突出顯示的格式
提示:
使用兩個條件對D4儲存格中的日期進行判斷,第一個條件是大於系統當前日期,第二個條件是和系統當前日期的間隔小於7。
員工生日一周內提醒
1、選中A4:D7儲存格區域,新建格式規則
2、使用公式為:
=DATEDIF($D4,NOW()+7,"yd")<=7
3、設置突出顯示的格式
提示:
1、DATEDIF函數用於計算兩個日期之間的間隔。
2、第三參數使用yd,表示計算兩個日期忽略年的間隔天數。
注意:
由於DATEDIF函數的第三參數在使用“YD”時有特殊的計算規則,因此當結束日期是3月份時,計算結果可能會出現一天的誤差。
資料有效性不僅能夠對儲存格的輸入資料進行條件限制,還可以在儲存格中創建下拉清單功能表方便使用者選擇輸入。
普通青年這樣用:
步驟簡要說明:
選中區域,設置資料驗證,允許條件選擇序列,輸入要在下拉式功能表中顯示的內容:
男,女
注意不同選項要使用半形的逗號隔開。
文藝青年這樣用:
步驟簡要說明:
選中資料區域,設置資料驗證,在【輸入資訊】選項卡下輸入提示內容。
強迫症青年這樣用:
步驟簡要說明:
選中資料區域,設置資料驗證,自訂公式為:
=COUNTA(E$3:E3)=ROW(A1)
注意:公式中的E3是選中資料區域的首個儲存格。
走你青年這樣用:
步驟簡要說明:
選中資料區域,設置資料驗證,自訂公式為:
=COUNTIF(E:E,E3)=1
注意:公式中的E3是實際選中資料區域的首個儲存格。
牛B青年這樣用:
步驟簡要說明:
任意儲存格(如H3)輸入公式=NOW()
選中資料區域,設置資料驗證,序列來源為:
=H3
注意:要預先設置所選資料區域的數位格式為:H:MM:SS
圖文製作:祝洪忠
普通青年這樣用:
步驟簡要說明:
選中區域,設置資料驗證,允許條件選擇序列,輸入要在下拉式功能表中顯示的內容:
男,女
注意不同選項要使用半形的逗號隔開。
文藝青年這樣用:
步驟簡要說明:
選中資料區域,設置資料驗證,在【輸入資訊】選項卡下輸入提示內容。
強迫症青年這樣用:
步驟簡要說明:
選中資料區域,設置資料驗證,自訂公式為:
=COUNTA(E$3:E3)=ROW(A1)
注意:公式中的E3是選中資料區域的首個儲存格。
走你青年這樣用:
步驟簡要說明:
選中資料區域,設置資料驗證,自訂公式為:
=COUNTIF(E:E,E3)=1
注意:公式中的E3是實際選中資料區域的首個儲存格。
牛B青年這樣用:
步驟簡要說明:
任意儲存格(如H3)輸入公式=NOW()
選中資料區域,設置資料驗證,序列來源為:
=H3
注意:要預先設置所選資料區域的數位格式為:H:MM:SS
圖文製作:祝洪忠