為什么會有這種需求？

我們知道Excel是個平面二維的,有時候我們展示記錄多維度信息時,僅僅使用橫縱坐標(biāo)軸是不夠的,

舉例說明,我們需要了解一個商品在不同時間段的銷售情況,那如果我對銷售數(shù)據(jù)進(jìn)行分級,看分別屬于不同的檔次,那么我們會發(fā)現(xiàn)在表格設(shè)計的時候就不大好弄了。

有人說,我們可以用備注啊,是滴,但是備注里面的信息不好參與運算,處理起來不大方便,這時候就有一種方法了,比如添加顏色背景以示區(qū)分(顏色管理在日常報表中的運用,以后會單獨開一章節(jié)來講),這樣就相當(dāng)于增加了第三維,豐富我們報表的承載能力.

困難點?

好了，用了顏色了，報表好看了，信息量也大了， 但是問題來了，現(xiàn)有的函數(shù)按條件統(tǒng)計，并沒有加入顏色這個條件，那我要統(tǒng)計某些顏色的單元格時，該如何處理呢？相信很多人平時會碰到這么個問題，今天咱們就來探討一下。

解決方案:

方法一、篩選

Excel的高版本增加了按顏色篩選這么一個功能，但是有個局限是僅能對單列進(jìn)行處理。

假設(shè)我們只需要處理單列：

1.1 按顏色進(jìn)行篩選

1.2 用subtotal函數(shù)進(jìn)行統(tǒng)計

之所以用subtotal是因為subtotal函數(shù)有2個參數(shù)，第一個參數(shù)可以區(qū)分可見單元格和不可見單元格的匯總。

我們篩選后，剩下的就是有顏色的，subtotal進(jìn)行計算就可以得出正確的結(jié)論，假設(shè)黃色單元格的求和，109就是相當(dāng)于sum函數(shù)，加了100就是表示對可見單元格統(tǒng)計，如果參數(shù)為9就是統(tǒng)計全部

不清楚的可以下去加強了解一下這個函數(shù)，此處不做深入解釋

非篩選結(jié)果

篩選后結(jié)果

方法二、查找

可以處理多列

很多人平時用查找功能可能很少注意到也能按照顏色查找

2.1 ctrl+F，調(diào)出查找對話框

找到對應(yīng)的格式，點OK

2.2 查找所有

將下面查找到的全部展開后，1）選擇第一個按住shift鍵，選擇最后一個；2）ctrl+A也行。這樣你就會發(fā)現(xiàn)所有滿足條件的即有顏色的單元格都選中了

統(tǒng)計結(jié)果，1）可以直接查看下面的狀態(tài)欄的統(tǒng)計數(shù)據(jù)，手動記錄；2）保持查找所有的這個狀態(tài)，將其區(qū)域定義名稱，如abc，旁邊寫個函數(shù)=sum（abc）進(jìn)行計算。

定義名稱

方法三、宏表函數(shù)

這里要用到宏表函數(shù)get.cell。

宏表函數(shù)作為函數(shù)的一種特殊存在是特定歷史背景下的產(chǎn)物，最初出發(fā)點為了兼容考慮的。高版本也能用，只是沒那么直接。我們需要配合名稱來用。

3.1 定義名稱

選擇B4單元格，定義名稱假設(shè)為color，輸入公式=GET.CELL(63，？？)，其中63表示獲取單元格顏色參數(shù)，大家可以度娘詳細(xì)了解這個函數(shù)的知識。

第二個參數(shù)注意相對引用和絕對引用。

3.2 計算出每個單元格的顏色值

在旁邊空白單元格輸入=color，求得單元格對應(yīng)的顏色

3.3 sumif函數(shù)求和

在這個案例中，既能處理多列也能處理單列，Excel很聰明的自動識別對應(yīng)的位置進(jìn)行求和

方法四、UDF

以上3種辦法在固定的數(shù)據(jù)里面操作比較簡單直接，但是在應(yīng)對經(jīng)常性的重復(fù)性的或者修改比較多的情況下就顯得步驟比較繁瑣，不是很智能。這時候怎么能少得了強大的VBA編程呢？

UDF，即user-defined-function，自定義函數(shù)。

4.1 文件另存為.xlsm格式

因為涉及到VBA代碼，高版本的需要換個存儲格式，否則代碼無法保存，03版就不需要了，可以直接編輯

4.2 插入代碼

alt+F11，打開VBE編輯器，插入模塊

復(fù)制粘貼如下代碼即可

1. Option Explicit

2. Function SumColorCells(referCell As Range, sumCell As Range)

3. Dim cell As Range

4. Dim s

5. s = 0

6. Application.Volatile '易失性函數(shù)

7. For Each cell In sumCell

8. If Len(cell) <> 0 And cell.Interior.ColorIndex = referCell.Interior.ColorIndex Then

9. s = s + cell.Value

10. End If

11. Next

12. SumColorCells = s

13. End Function

此處定義了一個SumColorCells函數(shù)，其中

第一個參數(shù)表示參考顏色的單元格

第二個參數(shù)表示求和區(qū)域

4.3 返回單元格按參數(shù)設(shè)置公式即可

按照定義輸入函數(shù)即可，有時候函數(shù)不會自動重算，我們可以改進(jìn)一下，比如后面+now（）*0等

以后想要計算有顏色的隨便什么顏色的可以直接套用這個自定義函數(shù)啦，像正常內(nèi)置函數(shù)一樣使用。主要注意的是：

• 每次打開工作簿記得開啟宏

• 只有植入代碼的工作簿才能使用這個UDF

• 數(shù)據(jù)計算量大的時候可能會影響電腦運行效率，我們可以去掉易失性或者改為手動重算

總結(jié)

幾種方式優(yōu)劣對比，相信總有一種方式適合你：

------------------------------------------------------------

【喜歡就轉(zhuǎn)發(fā)訂閱吧，也可關(guān)注微信公眾號Excel-365，新浪博客：http://blog.sina.com.cn/excel365】