Excel 2007條件格式進階應用兩則

　　在Excel應用條件格式可以讓符合特定條件的單元格數據以醒目的方式突出顯示出來，便於我們對工作表數據進行更好地分析。通常我們只要選中相應的單元格，然後設置條件格式，就可以達到目的。但是如果我們需要設置格式的單元格是根據別的單元格條件來設置呢?或者我們要設置的條件比較復雜呢?

　　圖1：單元格

　　以圖1所示的工作表為例。工作表中A2:J1032為某市一次大型拉練考試的成績表。各學科成績分別在C至G列，總分成績在H列，而學校名稱和專業名稱分別在I列和J列。

　　一、突出顯示指定學校的學生成績

　　現在成績表已經根據英語學科按從高到低降序排序。為了更方便比較，我們希望能把“化工學校”的學生所在的行用不同的顏色區別出來。在不改變現有排序、也不做篩選的情況下，用條件格式是最方便的了。

　　選中A2單元格，點擊功能區“開始”選項卡“樣式”功能組中的“條件格式”按鈕，在彈出的菜單中點擊“新建規則”命令，打開“新建格式規則”對話框。在“選擇規則類型”列表中點擊“使用公式確定要設置格式的單元格”項目，然後在下方的“為符合此公式的值設置格式”輸入框中輸入公式“=$I2="化工學校"”，然後再點擊下方的“格式”按鈕，在打開的對話框中設置單元格的填充顏色，如圖2所示。點擊確定關閉對話框。

　　圖2：新建格式

　　再選中A2單元格，然後點擊功能區“開始”選項卡“剪貼板”功能組中的“格式刷”工具，把A2:J1032的所有單元格依次“刷”一遍，復制A2單元格的格式至所有單元格，呵呵，看到了吧?“刷”到之處，凡是“化工學校”的學生所在行都填充了我們所指定的顏色。很容易吧?

　　用這種辦法，我們還可以設置別的什麼條件來突出顯示某一部分學生成績。比如，我們可以將公式改成“=$H2>=600”來突出顯示那些總分超過600分的學生。

　　二、突出顯示一定比例的學生

　　分析學生成績時經常要看一下總分或某學科名列前茅的前20%的學生成績分布，那麼我們就會希望能把這些學生的相應成績突出顯示出來。這項工作如果針對某學科用RANK函數排出名次，再進行排序，再計算出前20%的學生人數，然後再對符合條件的成績填充顏色，也不是不能完成。但是這肯定太麻煩。更何況有多個學科、總分等成績，都這麼來一遍的話，那頭都會大了。還有呢，肯定會有名次相同的情況的，這在確定前20%的人數時也是需要小心對待的。

　　但如果我們把這個工作交給條件格式來做的話，那問題就簡單多了。

　　比如我們要對總分列(H2:H1032)前20%的學生成績填充顏色。而我們又是在Excel 2007中完成這項工作的話，那麼我們就選中H2:H1032單元格區域，然後點擊功能區中的“條件格式”按鈕，然後在彈出的菜單中點擊“項目選取規則→值最大的10%項”命令，打開“10%最小的值”對話框。這對話框的名稱有點亂，呵呵，不知道是不是Excel 2007的Bug?不管它了，如圖3所示，在對話框左側的調節框中將比例值調整為“20%”，然後我們可以在右側的下拉列表中選擇“自定義格式”，然後在打開的對話框中為單元格指定格式。確定後就可以立刻將前20%的高分學生成績突出顯示出來了。

　　圖3：比例值設置

　　如果需要其它學科也同樣突出前20%的高分學生，那麼也只需要用“格式刷”將此格式“刷”到其它學科成績上去即可。

　　如果說我們使用是Excel 2007以前的版本，那也不要緊。選中H2:H1032單元格區域後，點擊菜單命令“格式→條件格式”，打開“條件格式”對話框。如圖4所示，在左側的下拉列表中選擇“公式”，然後在右側的輸入框中輸入公式“=LARGE(H$2:H$1032,INT(COUNT(H$2:H$1032)*20%))<=H2”，再點擊“格式”按鈕，設置好單元格格式，確定後就可以了。

　　圖4：設置條件格式

　　上面的公式看起來復雜，其實說白了也很簡單，首先中間的INT(COUNT(H$2:H$1032)*20%)部分是對整個數據區域求出20%的數據個數並且向下取整數。然後我們用LARGE函數求到了在此區域中前20%中最小的一個數據，拿這個數值與當前的單元格數據作比較。只要它小於當前單元格數據，那麼該單元格當然是名列前20%的喽。

        .