用活條件格式 用Excel制作星級(jí)系統(tǒng)

平淡

公司每個(gè)月都對(duì)會(huì)員消費(fèi)積分進(jìn)行統(tǒng)計(jì)，根據(jù)不同的積分給予不同的星級(jí)服務(wù)。常規(guī)的方法是使用圖表的方式進(jìn)行統(tǒng)計(jì)，為了可以更直觀地表現(xiàn)出積分和星級(jí)的對(duì)應(yīng)關(guān)系，現(xiàn)在需要在消費(fèi)積分的后面加上對(duì)應(yīng)的星級(jí)標(biāo)記，這樣通過標(biāo)記就可以非常方便地知道會(huì)員的星級(jí)了（圖1）。

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

首先按示例的提示在Excel中A、B列輸入對(duì)應(yīng)的數(shù)據(jù)，接著插入C、D、E、F、G列作為存放分級(jí)圖標(biāo)的輔助列，并在C1、D1、E1、F1、G1輸入分級(jí)的標(biāo)準(zhǔn)數(shù)值31、61、101、201、301（圖2）。

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

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

這樣點(diǎn)擊“確定”后返回Excel窗口，可以看到輔助列的單元格中會(huì)根據(jù)積分?jǐn)?shù)值自動(dòng)填充上對(duì)應(yīng)的星級(jí)標(biāo)記，達(dá)不到分級(jí)標(biāo)準(zhǔn)的區(qū)域則顯示為空白（圖5）。

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

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

因?yàn)檫@里填充的是公式，星標(biāo)可以根據(jù)客戶積分的不同動(dòng)態(tài)地進(jìn)行增減，比如更改（或者增加）客戶積分，那么星級(jí)標(biāo)記也會(huì)同步進(jìn)行變化。當(dāng)然也可以將其保存為模板備用，如果分級(jí)標(biāo)準(zhǔn)變化，那么只要更改輔助列的數(shù)字即可。因?yàn)槭枪絼?dòng)態(tài)引用，我們還可以直接復(fù)制其中的某列（需要包含公式）到其他位置，然后制作一個(gè)帶查詢功能的模塊。比如復(fù)制任意一列到11行，然后在上方單元格輸入需要查詢的信息和星級(jí)說明，這樣只要用戶輸入查詢的姓名和積分，星級(jí)和判斷文字會(huì)自動(dòng)顯示（圖8）。