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

俞木發(fā)

方法1：查找法

適合：單一背景色求和，要求原始數(shù)據(jù)不會(huì)再發(fā)生變化

如果工作表中標(biāo)記的背景色只有一種，比如下面的工作表中，綠色標(biāo)記表示質(zhì)檢員01抽查的數(shù)量，現(xiàn)在需要統(tǒng)計(jì)這些數(shù)字（圖1）。

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

方法2：使用宏表函數(shù)

適合：任意背景色自動(dòng)求和如果需要統(tǒng)計(jì)的是動(dòng)態(tài)變化的數(shù)據(jù)（如條件格式標(biāo)注的背景色數(shù)據(jù)），那么可以借助宏表函數(shù)將背景色轉(zhuǎn)換為具體的數(shù)值實(shí)現(xiàn)統(tǒng)計(jì)。比如下表中有多種背景色的單元格，現(xiàn)在需要對它們分別求和，并且要將其作為模板保存方便后續(xù)使用（圖3）。

1定義宏表函數(shù)

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

2使用宏表函數(shù)獲取背景色數(shù)值

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

3轉(zhuǎn)換背景色顯示

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

4對背景色單元格求和

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

5保存為模板使用

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

此外，由于在圖5所示的公式中使用了R A N D函數(shù)，這樣如果原始數(shù)據(jù)發(fā)生了變化，如圖8的G9單元格的數(shù)值變?yōu)?（假設(shè)此時(shí)條件格式的設(shè)置是單元格背景色變?yōu)辄S色），那么只要在任意空白單元格中雙擊，B13、B14單元格的統(tǒng)計(jì)數(shù)值就會(huì)同步更新了。這樣對背景色單元格的統(tǒng)計(jì)是不是就簡單多了？