您現在的位置: 萬盛學電腦網 >> 辦公軟件 >> Excel教程 >> excel綜合 >> 正文

Excel條件格式公式應用四例

作者:佚名    責任編輯:admin    更新時間:    2015-10-16 15:15:18

  我們知道,Excel“條件格式”功能可以根據單元格內容有選擇地自動應用格式,它為Excel增色不少的同時,還為我們帶來很多方便。如果讓“條件格式”和公式結合使用,則可以發揮更大的威力,下面提供幾個在“條件格式”中使用公式的應用實例,希望能給讀者朋友帶來一些啟發。

  一、 判別輸入是否正確

  在輸入如身份證等有固定位數的號碼,出現位數不正確的情形時,我們希望Excel能夠給出提示。雖然可以使用“數據有效性”設置實現,但是當輸入出錯時,Excel總會彈出一個提示的對話框,有朋友可能覺得這樣 “唐突”的提醒有點影響心情,那就讓“條件格式”來“溫和”的提醒吧。

  1、創建“條件格式”的公式

  假設我們通過“條件格式”,把符合位數(15位或18位)的號碼所在單元格的填充色設置為綠色,輸入完成后,通過查看單元格的填充色是否變為綠色,就可以知道輸入的正確性了。

  由于身份證號碼數據是屬于“文本”類型的,先選中需要存放身份證號碼的A2:A52單元格區域,將它們的數字格式設置為“文本”。然后在A2:A52單元格區域處于被選中的狀態下,選擇菜單“格式→條件格式”命令,打開“條件格式”對話框,單擊“條件 1”下方的下拉箭頭,在彈出的下拉列表中選擇“公式”(圖 1)。

Excel條件格式公式應用四例 三聯

  接著在其右邊的文本框中輸入公式“=OR(LEN(A2)=15,LEN(A2)=18)”,然后單擊“格式”按鈕,在打開的“單元格格式”對話框中選擇“圖案”選項卡,選擇綠色作為符合條件的單元格的填充色(圖 2)。

Excel條件格式公式應用四例(多圖)

  設置好后單擊“確定”按鈕,返回“條件格式”對話框,檢查無誤再次單擊“確定”就完成了條件格式的設置(圖 3)。

Excel條件格式公式應用四例(多圖)

  小提示:上面的操作,先選中了一個單元格范圍A2:A52,然后為這個單元格范圍設置條件格式的公式。在這種情況下,公式中應使用選擇范圍中左上單元格的引用,此例中為A2。公式輸入完成后,可以查看一下這個范圍中的其它單元格的條件格式公式,如A8單元格,為“=OR(LEN(A8)=15,LEN(A8)=18)”,這是由于上面的引用為相對應用,它會根據單元格的實際偏移量自動改變,從而得到適合其它單元格的公式。

  2、實現的具體效果

  現在來測試一下上面設置可以實現的效果,在A2:A52區域的單元格中輸入一些身份證號碼,當位數是18位或15位時,所在單元格的填充色自動變為“綠色”,而位數不對的身份證號碼,所在單元格的填充色不發生任何改變(圖 4),從是否變色我們就可以判斷輸入的正確性了。

Excel條件格式公式應用四例(多圖)(2)

  全部輸入并確認正確后,如果需要刪除單元格條件格式,則先選中A2:A52單元格區域,然后打開“條件格式”對話框,單擊如圖3中的“刪除”按鈕,在打開的“刪除條件格式”對話框中勾選“條件1”復選框,單擊確定即可(圖 5)。

Excel條件格式公式應用四例(多圖)(2)

    二、 找出銷售額的前三名

  如圖6中的B2:B12單元格中存放著銷售額數據,要找出其中的前三名,讓它們以藍色字體顯示。

  先選中B2:B12單元格,打開如圖1所示的對話框,輸入公式“=B2>LARGE($B$2:$B$12,4)”然后將符合條件的字體格式設置為藍色即可。實現的具體效果如下圖(圖 6)。

Excel條件格式公式應用四例(多圖)(3)

  說明:雖然可以對“銷售額”數據列排序找出前三名,但是,可能我們希望以日期為順序排列,這時“條件格式”就可以做到“兩全其美”了。

  三、 讓符合特殊條件的日期突出顯示

  有時 ,我們可能希望符合特殊條件的日期所在的單元格突出顯示,比如星期六或星期天。這時我們可以先選中日期所在的單元格,如圖6中的A2:A12,然后打開如圖1所示的單元格,輸入公式“=OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7)”,然后設置符合條件的單元格填充色為陰影即可。

  小提示:函數 WEEKDAY(serial_number,return_type)的功能為返回某日期為星期幾,Serial_number??表示一個順序的序列號,代表要查找的那一天的日期。當參數return_type為2時,函數返回數字 1(星期一)到數字 7(星期日)之間的整數。

  四、 讓工作表間隔固定行顯示陰影

  當單元格數據行較多,我們為了讓顯示效果更加醒目,可以讓工作表間隔固定行顯示陰影,效果如下圖所示(圖 7)。

Excel條件格式公式應用四例(多圖)(3)

  上面的效果是使用了公式“=MOD(ROW(),2)=0”,如果要間隔兩行顯示陰影則用公式“=MOD(ROW(),3)=0”,其余依次類推。

  小提示: 函數 MOD(number,divisor)返回兩數相除的余數,其中Number為被除數,Divisor為除數。函數ROW(reference)返回引用的行號。其中Reference??為需要得到其行號的單元格或單元格區域,如果省略 reference,則假定是對函數 ROW 所在單元格的引用。

  “條件格式”與公式結合還有非常多的應用,感興趣的朋友不妨再研究研究哦。

内蒙古十一选五跨度走势图