|
為什么會(huì)有這種需求？ 我們知道Excel是個(gè)平面二維的,有時(shí)候我們展示記錄多維度信息時(shí),僅僅使用橫縱坐標(biāo)軸是不夠的, 舉例說(shuō)明,我們需要了解一個(gè)商品在不同時(shí)間段的銷售情況,那如果我對(duì)銷售數(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ú)開一章節(jié)來(lái)講),這樣就相當(dāng)于增加了第三維,豐富我們報(bào)表的承載能力. 困難點(diǎn)? 好了，用了顏色了，報(bào)表好看了，信息量也大了， 但是問題來(lái)了，現(xiàn)有的函數(shù)按條件統(tǒng)計(jì)，并沒有加入顏色這個(gè)條件，那我要統(tǒng)計(jì)某些顏色的單元格時(shí)，該如何處理呢？相信很多人平時(shí)會(huì)碰到這么個(gè)問題，今天咱們就來(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ū)分可見單元格和不可見單元格的匯總。 我們篩選后，剩下的就是有顏色的，subtotal進(jìn)行計(jì)算就可以得出正確的結(jié)論，假設(shè)黃色單元格的求和，109就是相當(dāng)于sum函數(shù)，加了100就是表示對(duì)可見單元格統(tǒng)計(jì)，如果參數(shù)為9就是統(tǒng)計(jì)全部 不清楚的可以下去加強(qiáng)了解一下這個(gè)函數(shù)，此處不做深入解釋 非篩選結(jié)果 篩選后結(jié)果 方法二、查找 可以處理多列 很多人平時(shí)用查找功能可能很少注意到也能按照顏色查找 2.1 ctrl+F，調(diào)出查找對(duì)話框 找到對(duì)應(yīng)的格式，點(diǎn)OK 2.2 查找所有 將下面查找到的全部展開后，1）選擇第一個(gè)按住shift鍵，選擇最后一個(gè)；2）ctrl+A也行。這樣你就會(huì)發(fā)現(xiàn)所有滿足條件的即有顏色的單元格都選中了 統(tǒng)計(jì)結(jié)果，1）可以直接查看下面的狀態(tài)欄的統(tǒng)計(jì)數(shù)據(jù)，手動(dòng)記錄；2）保持查找所有的這個(gè)狀態(tài)，將其區(qū)域定義名稱，如abc，旁邊寫個(gè)函數(shù)=sum（abc）進(jìn)行計(jì)算。 定義名稱 方法三、宏表函數(shù) 這里要用到宏表函數(shù)get.cell。 宏表函數(shù)作為函數(shù)的一種特殊存在是特定歷史背景下的產(chǎn)物，最初出發(fā)點(diǎn)為了兼容考慮的。高版本也能用，只是沒那么直接。我們需要配合名稱來(lái)用。 3.1 定義名稱 選擇B4單元格，定義名稱假設(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，打開VBE編輯器，插入模塊
復(fù)制粘貼如下代碼即可
此處定義了一個(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ù)一樣使用。主要注意的是：
總結(jié) 幾種方式優(yōu)劣對(duì)比，相信總有一種方式適合你：
------------------------------------------------------------ 【喜歡就轉(zhuǎn)發(fā)訂閱吧，也可關(guān)注微信公眾號(hào)Excel-365，新浪博客：http://blog.sina.com.cn/excel365】 |
|
|
