Excel如何按顏色求和：整理了4 種方法，你會用哪些？

編按：說實話，小窩是第一次做顏色求和，因爲我幾乎都用條件格式標識數據，顏色求和就是僞需求。但是問了身邊朋友，以及看了一些學員的提問，原來真存在按顏色求和的。在此，整理了4種方法。

顏色求和實際是個僞命題！

不信？

那就往下看！

1、直接用SUM或者SUMIF求和

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

綠色單元格之和：

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

粉色單元格之和：

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

“對嗎？”

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

結果是否對，繼續看就知道了。

2、查找法求和

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

步驟：

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

（2）單擊“選項”—“格式”—“從單元格格式”，然後吸取綠色單元格。

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

（4）按CTRL+A全選，然後點“關閉”。

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

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

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

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

回到Sheet1中。

請用查找法做顏色求和。

請一定試試！！

試了後你會發現無法用查找顏色的方法求和，或者說其結果是錯誤的。

咋回事呢？

我們在表中用顏色標識不同的數據都是基於具體規則進行的，譬如所有大於500的填充綠色，小於250的填充粉色。Excel的條件格式可以幫我們自動完成標識。

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

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

知道了顏色出現的規則，那麼顏色求和也就是按條件規則求和而已，與具體的顏色無關。

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

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

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

逐個手動填色的數字的和！

顏色標識數字，肯定用條件格式；

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

能按顏色求和的都是手動填色的，

可誰會自己手欠找麻煩呢？

因此，

按顏色求和就是僞命題！

或許你說，“我就是手動標色的 —— 啊，不，是那個安排做事的人隨手標的，然後要求我求和”。

太壞了！

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

3、宏表函數法

到Sheet3。提供兩種宏表函數法：一個是公式簡單的，但有輔助列（行）；一個是不用輔助列（行）的，但是公式複雜。

1）簡單公式

步驟：

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

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

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

可以看到當前綠色顏色值36，粉色顏色值40。

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

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

能去掉輔助行或列嗎？

可以！只不過定義名稱中的公式就複雜了。

2）複雜公式

步驟：

（1）重新定義名稱。

定義名稱，新創建一個名稱“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中的每個單元格。之所以要分別引用，而不是直接寫成GET.CELL(63, Sheet3!$B$3:$G$9),是因爲GET.CELL函數不支持數據區域。

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

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

4、“很牛很牛”的自定義函數法

到Sheet4。

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

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

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

這個自定義函數怎麼來的呢？

按ALT+F11打開VBA編輯器。

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

（2）在插入的模塊中輸入如下代碼（可以複製此處代碼進行粘貼。能實現顏色求和功能的代碼有多種，下方只是相對簡單的一種。）

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）返回工作表即可用函數SUMCOLOR進行求和了。

附上代碼解析：

注意：使用了宏表函數，以及VBA自定義函數後，文件需要保存爲支持宏的xlsm格式。

小結

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

2.如果真是手動爲單元格填充顏色的，那查找法、宏表函數法、自定義函數法都可以。

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

學習交流請加微信hclhclsc進羣領取資料

用SUM函數條件求和比SUMIF還方便

SUMIF函數用法集

條件格式效果錯誤的原因

INDIRECT函數的R1C1樣式用法

版權申明：

本文作者小窩；部落窩教育享有稿件專有使用權。若需轉載請聯繫部落窩教育。