WPS表格條件格式應用舉例二則

　　提到WPS表格的“條件格式”，如果用戶在“條件格式”中巧用“單元格的混合引用”，將會達到意想不到的效果。

　　下面就通過幾個經典的實例，帶領大家進入這個神奇的世界。

　　首先向用戶簡單介紹一下單元格引用的概念，在WPS表格中，對單元格引用分為以下3類(4種引用)：

　　·相對引用：A1、D10等，當公式復制后，自動按行、列產生相對引用。

　　·絕對引用：$B$1、$F$5等，在行列號前置“$”號，保證公式復制后不會改變。

　　·混合引用：$A2、B$1等，行相對、列絕對引用(如$A2)，當公式向左右方向進行復制時，A列固定保持不變;行絕對、列相對引用(如：B$1)，當公式向上下方向進行復制時，第1行固定保持不變。

　　固定的隔行底紋

　　文章開始提到了“使用條件格式設置隔行底紋”的用法，但常規的隔行顯示設置方法往往在用戶進行條件篩選后就會失效，如圖 1所示，那如何才能使間隔底紋不受篩選的影響呢?

　　圖1 篩選后受影響的間隔底紋效果

　　具體設置如下：

　　步驟1　單擊A2單元格，并拖動鼠標選擇A2:G11區域;

　　步驟2　選擇“格式|條件格式”菜單項，在彈出的對話框中設置條件格式公式如下：

　　條件格式公式為：=MOD (SUBTOTAL (3, $A$1:$A2), 2)

　　公式解析：該技巧主要利用SUBTOTAL函數支持對篩選條件下的數據計數功能來動態實現取得數據個數，再使用MOD函數進行計算余數，從而使滿足條件的數據賦予格式。其中：SUBTOTAL函數參數“$A$1:$A2”中使用了A1的絕對引用和A2的混合引用。

　　步驟3　點擊“格式”按鈕，并設置相應的單元格格式如圖 2所示：

　　圖2 隔行底紋的條件格式設置

　　步驟4　點擊“確定”按鈕保存設置，并重新進行“篩選”設置，如篩選“語文成績為60分(含)以上的學員”，最終格式顯示效果如圖 3所示。

　　圖3 固定的隔行底紋顯示效果

　　提示：由于條件格式公式中使用了SUBTOTAL函數，所以不管數據是否在篩選狀態下都能夠固定地顯示隔行底紋。

　　自動標記重復數據

　　很多時候，用戶由于誤操作等原因，導致在數據表中輸入了重復數據，如輸入了重復的員工姓名、相同的產品編號等，如果能夠在用戶輸入的時候進行“顯式”的提醒，將會盡可能地避免用戶的錯誤輸入。所示的數據表中已經錄入了部分學員的成績，下面的方法將介紹使用“條件格式”來對用戶輸入了重復的學員姓名進行提醒。

　　圖4 學員期末考試成績表

　　具體的設置方法如下：

　　步驟1　首先將光標定位A2單元格，選擇A2:G9單元格區域;

　　步驟2　選擇“格式|條件格式”菜單項，在彈出的對話框中設置條件格式公式如下：

　　條件格式公式：=COUNTIF($A$1:$A2,$A2)>1

　　思路解析：公式主要利用了COUNTIF函數結合單元格的混合引用來實現動態計數，當用戶輸入重復的學員時，公式計算值必定大于1，因此滿足條件賦予指定格式。

　　步驟3　點擊“格式”按鈕，并設置相應的單元格格式如圖 5 所示：

　　圖5 重復數據標記的條件格式設置

　　步驟4　點擊“確定”按鈕保存設置，當用戶輸入重復的學員時，系統會以特殊格式顯示出來，顯示效果如圖 6 所示。

　　圖6 重復數據標記結果

　　小結：

　　1、理解并掌握單元格的“混合引用”，有利于對WPS表格的公式進行復制和運用。

　　2、利用SUBTOTAL函數結合混合引用，可以實現“固定的隔行底紋”的顯示。

　　3、利用COUNTIF函數結合混合引用，用戶可以有目的地防止輸入重復數據。

　　用戶理解并掌握條件格式后，結合實際的工作進行合理地運用。如“查找當前數據在指定的數據表中是否存在?”這類應用都可以利用WPS表格的“條件格式”來實現的。

　　適用范圍:

　　適用于WPS Office 2005及以上版本。