說實(shí)話，小窩是第一次做顏色求和，因?yàn)槲規(guī)缀醵加脳l件格式標(biāo)識(shí)數(shù)據(jù)，顏色求和就是偽需求。但是問了身邊朋友，以及看了一些學(xué)員的提問，原來真存在按顏色求和的。在此，整理了4種方法。

顏色求和實(shí)際是個(gè)偽命題！

不信？

那就往下看！

1.直接用SUM或者SUMIF求和

分別求綠色與粉色單元格之和。

綠色單元格之和：

=SUM((B3:G9>500)*B3:G9)

粉色單元格之和：

=SUM((B3:G9<250)*B3:G9)

“對嗎？”

你肯定有疑惑：感覺“顏色”條件都沒有使用就完成了求和，這結(jié)果對嗎？

結(jié)果是否對，繼續(xù)看就知道了。

2.查找法求和

來到Sheet2中，同樣分別求綠色和粉色單元格之和。

步驟：

（1）按CTRL+F打開“查找和替換”對話框

（2）單擊“選項(xiàng)”—“格式”—“從單元格格式”，然后吸取綠色單元格。

（3）單擊“查找全部”。

（4）按CTRL+A全選，然后點(diǎn)“關(guān)閉”。

（5）在名稱框中輸入“綠色”。

（6）同樣的操作選中粉色單元格，在名稱框中輸入“粉色”。

（7）輸入公式=SUM(綠色)或=SUM(粉色)完成求和。

用到顏色條件了，并且求出來的和與前方是一樣的！

回到Sheet1中。

請用查找法做顏色求和。

請一定試試??！

試了后你會(huì)發(fā)現(xiàn)無法用查找顏色的方法求和，或者說其結(jié)果是錯(cuò)誤的。

咋回事呢？

我們在表中用顏色標(biāo)識(shí)不同的數(shù)據(jù)都是基于具體規(guī)則進(jìn)行的，譬如所有大于500的填充綠色，小于250的填充粉色。Excel的條件格式可以幫我們自動(dòng)完成標(biāo)識(shí)。

下圖就是Sheet1中的條件格式。

它包含兩條規(guī)則：<250填充粉色，>500填充綠色。

知道了顏色出現(xiàn)的規(guī)則，那么顏色求和也就是按條件規(guī)則求和而已，與具體的顏色無關(guān)。

如此處，綠色之和=SUM((B3:G9>500)*B3:G9),粉色之和=SUM((B3:G9<250)*B3:G9)。

用條件格式顯示出來的單元格填色并不等于單元格實(shí)質(zhì)填充了顏色。因此，你無法用查找顏色的方式來求和；無法用下面將要介紹的宏表函數(shù)，以及更牛的VBA自定義函數(shù)完成顏色求和。

查找法、宏表函數(shù)法、VBA自定義格式法，它們都要利用具體填色信息，只能求——

逐個(gè)手動(dòng)填色的數(shù)字的和！

顏色標(biāo)識(shí)數(shù)字，肯定用條件格式；

用條件格式，就無法通過識(shí)別顏色來求和；

能按顏色求和的都是手動(dòng)填色的，

可誰會(huì)自己手欠找麻煩呢？

因此，

按顏色求和就是偽命題！

或許你說，“我就是手動(dòng)標(biāo)色的 —— 啊，不，是那個(gè)安排做事的人隨手標(biāo)的，然后要求我求和”。

太壞了！

看來還得做顏色求和。下面是其他的方法。

3.宏表函數(shù)法

到Sheet3。提供兩種宏表函數(shù)法：一個(gè)是公式簡單的，但有輔助列（行）；一個(gè)是不用輔助列（行）的，但是公式復(fù)雜。

1）簡單公式

步驟：

（1）單擊“公式”—“定義名稱”，輸入名稱“color”（名稱須是唯一的，不能與已有名稱相同）。引用位置處輸入公式“=get.cell(63,sheet3!b3)”。

Get.cell()是宏表函數(shù)，用于獲取單元格的某類信息。具體信息類型由數(shù)字指定，數(shù)字范圍1~66。其中，63代表單元格背景顏色。

（2）在B11輸入公式“=color”并右拉下拉獲取單元格的顏色值。

可以看到當(dāng)前綠色顏色值36，粉色顏色值40。

（3）寫公式完成顏色求和。

輸入公式“=SUMIF($B$11:$G$17,A19,$B$3:$G$9)”并下拉即可。

能去掉輔助行或列嗎？

可以！只不過定義名稱中的公式就復(fù)雜了。

2）復(fù)雜公式

步驟：

（1）重新定義名稱。

定義名稱，新創(chuàng)建一個(gè)名稱“color_2”,然后在引用位置輸入如下公式：

=SUM((GET.CELL(63,INDIRECT("r"&ROW(Sheet3!$B$3:$G$9)&"c"&COLUMN(Sheet3!$B$3:$G$9),0))=GET.CELL(63,Sheet3!A19))*Sheet3!$B$3:$G$9)

（2）在B19處輸入公式“=color_2”下拉即可。

公式說明：

①INDIRECT("r"&ROW(Sheet3!$B$3:$G$9)&"c"&COLUMN(Sheet3!$B$3:$G$9),0)，用INDIRECT分別引用B3:G9中的每個(gè)單元格。之所以要分別引用，而不是直接寫成GET.CELL(63, Sheet3!$B$3:$G$9),是因?yàn)?span>GET.CELL函數(shù)不支持?jǐn)?shù)據(jù)區(qū)域。

②GET.CELL(63, ①)得到每個(gè)單元格的顏色值。

余下的部分不說你也明白。

4.“很牛很牛”的自定義函數(shù)法

到Sheet4。

在B13中輸入公式“=SumColor($B$3:$G$9,A13)”下拉即可。

非常簡單，很靈活，可以在當(dāng)前文件的任何表格中使用。

SUMCOLOR是自定義函數(shù)，第一參數(shù)選擇要求和的區(qū)域，第二參數(shù)選擇顏色條件單元格。

這個(gè)自定義函數(shù)怎么來的呢？

按ALT+F11打開VBA編輯器。

（1）單擊“插入”—“模塊”命令。

（2）在插入的模塊中輸入如下代碼（可以復(fù)制此處代碼進(jìn)行粘貼。能實(shí)現(xiàn)顏色求和功能的代碼有多種，下方只是相對簡單的一種。）

Function SumColor(sum_range As Range, ref_rang As Range)

Dim x As Range

For Each x In sum_range

If x.Interior.ColorIndex = ref_rang.Interior.ColorIndex Then

SumColor = Application.Sum(x) + SumColor

End If

Next x

End Function

（3）返回工作表即可用函數(shù)SUMCOLOR進(jìn)行求和了。

附上代碼解析：

注意：使用了宏表函數(shù)，以及VBA自定義函數(shù)后，文件需要保存為支持宏的xlsm格式。

小結(jié)

1.如果是利用條件格式賦予單元格顏色的，（只能）直接用規(guī)則進(jìn)行條件求和，與顏色無關(guān)。

2.如果真是手動(dòng)為單元格填充顏色的，那查找法、宏表函數(shù)法、自定義函數(shù)法都可以。

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育Excel精品好課任你選擇！

掃下方二維碼關(guān)注公眾號，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

用SUM函數(shù)條件求和比SUMIF還方便

SUMIF函數(shù)用法集

條件格式效果錯(cuò)誤的原因

INDIRECT函數(shù)的R1C1樣式用法

版權(quán)申明：

本文作者小窩；部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。