不走尋常路 按照背景色對單元格求和

俞木發

方法1：查找法

適合：單一背景色求和，要求原始數據不會再發生變化

如果工作表中標記的背景色只有一種，比如下面的工作表中，綠色標記表示質檢員01抽查的數量，現在需要統計這些數字（圖1）。

按下“Ctrl+F”快捷鍵打開“查找”對話窗口，依次點擊“格式→從單元格中選擇格式”，接著用鼠標點擊任意一個綠色單元格，點擊“查找全部”后在下方就可以將所有綠色單元格找到了。接著再按下“Ctrl+A”全選數據，在下方的狀態欄中就可以看到所有單元格的求和數據為651，按提示點擊即可復制。最后再將其粘貼到統計單元格中即可（圖2）。這個方法使用簡單，不足之處是如果原始數據發生了變化（如在圖1中增加了月份和質檢員01抽查的數量），那么就需要重新查找。

方法2：使用宏表函數

適合：任意背景色自動求和如果需要統計的是動態變化的數據（如條件格式標注的背景色數據），那么可以借助宏表函數將背景色轉換為具體的數值實現統計。比如下表中有多種背景色的單元格，現在需要對它們分別求和，并且要將其作為模板保存方便后續使用（圖3）。

1定義宏表函數

依次點擊菜單欄中的“公式→定義名稱→定義名稱”，在打開的窗口中定義一個名稱為“顏色”的公式，在“引用位置”處輸入“=GET.CELL（63，Sheet2！B2）”，即以B2單元格作為基準，使用宏表函數GET.CELL求出其背景色（即參數63）的數值，點擊“確定”保存退出（圖4）。

2使用宏表函數獲取背景色數值

返回工作表，定位到I2單元格并輸入公式“=顏色+RAND （ ） * 0 ”，即在引用上述定義公式的同時加上“R AND（ ）*0”的數值。由于宏表函數不會自動計算，這里使用R A N D 函數的隨機性來實現其自動重算，由于使用了“R AND（ ）*0 = 0”，這樣原始數值不會變化。接著將公式向右、向下填充到N2、N9單元格（填充區域要和原始數據一致），在I2：N9區域中就會自動顯示引用原始數據單元格的背景色數值了，如綠色為10，黃色為46（圖5）。

3轉換背景色顯示

這里的背景色使用數字來表示，為了方便后續的識別使用，還可以使用IFS函數來轉換。定位到O2單元格并輸入公式“=IFS（I2=10，"綠色"，I2=46，"黃色"，I2=0，""）”，接著將公式向右、向下填充到T2、T9單元格。這里使用IFS函數實現多條件的判斷，如果I2：N9區域單元格的值為10，那么就顯示為“綠色”，如果無填充（即數值= 0）就顯示為空。如果有更多的背景色數值，請參考上述的公式自行添加。完成設置后，原始數據中單元格的背景色就和I2：N9區域單元格顯示的背景色字符一一對應了（圖6）。

4對背景色單元格求和

選中I：T列數據區域，右擊并選擇“隱藏”，接著定位到B13單元格并輸入公式“=SUMIF（$O$2：$T$9，"綠色" ， $B$2：$G$9 ） ”，表示以“$O$2：$T$ 9”區域中顯示的“綠色”字符作為條件（它和原始數據綠色背景填充的單元格相對應），然后對“$ B$2：$G$ 9”區域中符合條件的數據求和（圖7）。如果需要對其他背景色單元格求和，那么只要將公式中的“綠色”替換為相應的背景色即可。

5保存為模板使用

選中B2：G9數據區域，依次點擊“開始→清除→全部清除”，然后將文件保存為“Excel啟用宏的工作簿”。這樣以后需要對背景色單元格求和時，只要打開需要統計數據的文檔，選擇需要統計的數據后復制，接著切換到上述保存的啟用宏工作簿，定位到B2單元格，依次點擊“開始→ 粘貼→ 保留源格式”，在B13、B14單元格中就可以自動完成數據的統計了（圖8）。

此外，由于在圖5所示的公式中使用了R A N D函數，這樣如果原始數據發生了變化，如圖8的G9單元格的數值變為2（假設此時條件格式的設置是單元格背景色變為黃色），那么只要在任意空白單元格中雙擊，B13、B14單元格的統計數值就會同步更新了。這樣對背景色單元格的統計是不是就簡單多了？