為什么會(huì)有這種需求？

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

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

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

困難點(diǎn)?

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

解決方案:

方法一、篩選

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

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

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

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

之所以用subtotal是因?yàn)閟ubtotal函數(shù)有2個(gè)參數(shù)，第一個(gè)參數(shù)可以區(qū)分可見(jiàn)單元格和不可見(jiàn)單元格的匯總。

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

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

非篩選結(jié)果

篩選后結(jié)果

方法二、查找

可以處理多列

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

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

找到對(duì)應(yīng)的格式，點(diǎn)OK

2.2 查找所有

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

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

定義名稱(chēng)

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

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

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

3.1 定義名稱(chēng)

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

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

3.2 計(jì)算出每個(gè)單元格的顏色值

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

3.3 sumif函數(shù)求和

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

方法四、UDF

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

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

4.1 文件另存為.xlsm格式

因?yàn)樯婕暗絍BA代碼，高版本的需要換個(gè)存儲(chǔ)格式，否則代碼無(wú)法保存，03版就不需要了，可以直接編輯

4.2 插入代碼

alt+F11，打開(kāi)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

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

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

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

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

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

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

• 每次打開(kāi)工作簿記得開(kāi)啟宏

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

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

總結(jié)

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

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

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