用活條件格式 用Excel制作星級系統

平淡

公司每個月都對會員消費積分進行統計，根據不同的積分給予不同的星級服務。常規的方法是使用圖表的方式進行統計，為了可以更直觀地表現出積分和星級的對應關系，現在需要在消費積分的后面加上對應的星級標記，這樣通過標記就可以非常方便地知道會員的星級了（圖1）。

可以看到如圖示例中，星級標記是要和不同的積分對應的。通過對原始數據進行分析可以知道，每個分級標準主要是30、31、61、101、201、301這幾個數字，只要大于這里的臨界數字，則需要添加上對應的星級標記（最多5個星級）。因此設計的思路是，將原始積分分解并和分級標準對比，如果大于該數值則分配一個星級標記。我們只要插入5列的輔助列，然后將分解的積分使用IF函數和分級標準比對，最后使用條件格式分配星級符號即可。

首先按示例的提示在Excel中A、B列輸入對應的數據，接著插入C、D、E、F、G列作為存放分級圖標的輔助列，并在C1、D1、E1、F1、G1輸入分級的標準數值31、61、101、201、301（圖2）。

選中C 2并在其中插入公式“= I F （ C $ 1<=$B2，1，0）”。公式的意思是使用IF函數，將C1的值（即分級標準的數值）和B2的值（會員的是實際積分）進行比較，如果C1<=B2，那么就在C2顯示為1，否則顯示為0。然后將公式依次向右、向下填充到C2→G6區域，可以看到積分分解后會在C→D列分別顯示為1和0這些數據，其中1代表一顆星（表示超過分級基準值），0則為無（圖3）。

完成上述操作后，上面輔助列數據中只有1和0，因此接下來就是將“1”代替為一顆星，0則隱藏顯示，這樣即可完成星級的設置。選中C2→G6區域數據，點擊“開始→條件格式→新建規則”，在打開的窗口中，規則類型選擇“基于各自值設置所有單元格的格式”，格式樣式選擇“圖標集”，圖標樣式選擇“自定義”，勾選其后的“僅顯示圖標”，圖標選擇五角星，當值是>=1時，類型選擇“數字”，其他選項設置為“無單元格圖標”（圖4）。

這樣點擊“確定”后返回Excel窗口，可以看到輔助列的單元格中會根據積分數值自動填充上對應的星級標記，達不到分級標準的區域則顯示為空白（圖5）。

添加上星級標記后，我們還要對星級增加文字說明。文字說明同樣可以使用I F嵌套函數進行添加。定位到H 2，然后輸入公式“= I F（ B2<61，"一星級客戶"，IF（B2<101，"二星級客戶"， I F（ B2< 2 01，"三星級客戶"，IF（B2<301，"四星級客戶"，"五星級客戶"））））”。這里使用四級嵌套對會員積分進行判斷，符合某一分級標準則顯示對應的“X星級客戶”。這里同樣要先根據分級標準進行嵌套，按照不同的積分標準對應顯示不同星級的文字，注意文字說明需要使用半角雙引號進行標注。接著將公式下拉，可以看到函數會自動為不同星級的客戶添加上文字說明（圖6）。

接下來對星級標記進行美化操作，按提示選擇C2→G6列，將其列寬設置為和星級圖標大小一致，點擊“開始→套用表格格式”，套用一種深色且無豎線的樣式即可。應用樣式后，由于表列的存在會導致數據錯格排列，按提示進行調整對齊即可。這樣，通過客戶積分以星級數目就可以非常直觀地進行展示了（圖7）。

因為這里填充的是公式，星標可以根據客戶積分的不同動態地進行增減，比如更改（或者增加）客戶積分，那么星級標記也會同步進行變化。當然也可以將其保存為模板備用，如果分級標準變化，那么只要更改輔助列的數字即可。因為是公式動態引用，我們還可以直接復制其中的某列（需要包含公式）到其他位置，然后制作一個帶查詢功能的模塊。比如復制任意一列到11行，然后在上方單元格輸入需要查詢的信息和星級說明，這樣只要用戶輸入查詢的姓名和積分，星級和判斷文字會自動顯示（圖8）。