顏色求和實(shí)際是個(gè)偽命題,但還是告訴你4種方法!
?
作者:小窩來(lái)源:部落窩教育發(fā)布時(shí)間:2023-08-16 21:04:55點(diǎn)擊:758
說(shuō)實(shí)話(huà),小窩是第一次做顏色求和,因?yàn)槲規(guī)缀醵加脳l件格式標(biāo)識(shí)數(shù)據(jù),顏色求和就是偽需求。但是問(wèn)了身邊朋友,以及看了一些學(xué)員的提問(wèn),原來(lái)真存在按顏色求和的。在此,整理了4種方法。
顏色求和實(shí)際是個(gè)偽命題!
不信?
那就往下看!
1.直接用SUM或者SUMIF求和
分別求綠色與粉色單元格之和。
綠色單元格之和:
=SUM((B3:G9>500)*B3:G9)
粉色單元格之和:
=SUM((B3:G9<250)*B3:G9)
“對(duì)嗎?”
你肯定有疑惑:感覺(jué)“顏色”條件都沒(méi)有使用就完成了求和,這結(jié)果對(duì)嗎?
結(jié)果是否對(duì),繼續(xù)看就知道了。
2.查找法求和
來(lái)到Sheet2中,同樣分別求綠色和粉色單元格之和。
步驟:
(1)按CTRL+F打開(kāi)“查找和替換”對(duì)話(huà)框
(2)單擊“選項(xiàng)”—“格式”—“從單元格格式”,然后吸取綠色單元格。
(3)單擊“查找全部”。
(4)按CTRL+A全選,然后點(diǎn)“關(guān)閉”。
(5)在名稱(chēng)框中輸入“綠色”。
(6)同樣的操作選中粉色單元格,在名稱(chēng)框中輸入“粉色”。
(7)輸入公式=SUM(綠色)或=SUM(粉色)完成求和。
用到顏色條件了,并且求出來(lái)的和與前方是一樣的!
回到Sheet1中。
請(qǐng)用查找法做顏色求和。
請(qǐng)一定試試!!
試了后你會(huì)發(fā)現(xiàn)無(wú)法用查找顏色的方法求和,或者說(shuō)其結(jié)果是錯(cuò)誤的。
咋回事呢?
我們?cè)诒碇杏妙伾珮?biāo)識(shí)不同的數(shù)據(jù)都是基于具體規(guī)則進(jìn)行的,譬如所有大于500的填充綠色,小于250的填充粉色。Excel的條件格式可以幫我們自動(dòng)完成標(biāo)識(shí)。
下圖就是Sheet1中的條件格式。
它包含兩條規(guī)則:<250填充粉色,>500填充綠色。
知道了顏色出現(xiàn)的規(guī)則,那么顏色求和也就是按條件規(guī)則求和而已,與具體的顏色無(wú)關(guān)。
如此處,綠色之和=SUM((B3:G9>500)*B3:G9),粉色之和=SUM((B3:G9<250)*B3:G9)。
用條件格式顯示出來(lái)的單元格填色并不等于單元格實(shí)質(zhì)填充了顏色。因此,你無(wú)法用查找顏色的方式來(lái)求和;無(wú)法用下面將要介紹的宏表函數(shù),以及更牛的VBA自定義函數(shù)完成顏色求和。
查找法、宏表函數(shù)法、VBA自定義格式法,它們都要利用具體填色信息,只能求——
逐個(gè)手動(dòng)填色的數(shù)字的和!
顏色標(biāo)識(shí)數(shù)字,肯定用條件格式;
用條件格式,就無(wú)法通過(guò)識(shí)別顏色來(lái)求和;
能按顏色求和的都是手動(dòng)填色的,
可誰(shuí)會(huì)自己手欠找麻煩呢?
因此,
按顏色求和就是偽命題!
或許你說(shuō),“我就是手動(dòng)標(biāo)色的 —— 啊,不,是那個(gè)安排做事的人隨手標(biāo)的,然后要求我求和”。
太壞了!
看來(lái)還得做顏色求和。下面是其他的方法。
3.宏表函數(shù)法
到Sheet3。提供兩種宏表函數(shù)法:一個(gè)是公式簡(jiǎn)單的,但有輔助列(行);一個(gè)是不用輔助列(行)的,但是公式復(fù)雜。
1)簡(jiǎn)單公式
步驟:
(1)單擊“公式”—“定義名稱(chēng)”,輸入名稱(chēng)“color”(名稱(chēng)須是唯一的,不能與已有名稱(chēng)相同)。引用位置處輸入公式“=get.cell(63,sheet3!b3)”。
Get.cell()是宏表函數(shù),用于獲取單元格的某類(lèi)信息。具體信息類(lèi)型由數(shù)字指定,數(shù)字范圍1~66。其中,63代表單元格背景顏色。
(2)在B11輸入公式“=color”并右拉下拉獲取單元格的顏色值。
可以看到當(dāng)前綠色顏色值36,粉色顏色值40。
(3)寫(xiě)公式完成顏色求和。
輸入公式“=SUMIF($B$11:$G$17,A19,$B$3:$G$9)”并下拉即可。
能去掉輔助行或列嗎?
可以!只不過(guò)定義名稱(chēng)中的公式就復(fù)雜了。
2)復(fù)雜公式
步驟:
(1)重新定義名稱(chēng)。
定義名稱(chēng),新創(chuàng)建一個(gè)名稱(chēng)“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”下拉即可。
公式說(shuō)明:
①INDIRECT("r"&ROW(Sheet3!$B$3:$G$9)&"c"&COLUMN(Sheet3!$B$3:$G$9),0),用INDIRECT分別引用B3:G9中的每個(gè)單元格。之所以要分別引用,而不是直接寫(xiě)成GET.CELL(63, Sheet3!$B$3:$G$9),是因?yàn)?span>GET.CELL函數(shù)不支持?jǐn)?shù)據(jù)區(qū)域。
②GET.CELL(63, ①)得到每個(gè)單元格的顏色值。
余下的部分不說(shuō)你也明白。
4.“很牛很?!钡淖远x函數(shù)法
到Sheet4。
在B13中輸入公式“=SumColor($B$3:$G$9,A13)”下拉即可。
非常簡(jiǎn)單,很靈活,可以在當(dāng)前文件的任何表格中使用。
SUMCOLOR是自定義函數(shù),第一參數(shù)選擇要求和的區(qū)域,第二參數(shù)選擇顏色條件單元格。
這個(gè)自定義函數(shù)怎么來(lái)的呢?
按ALT+F11打開(kāi)VBA編輯器。
(1)單擊“插入”—“模塊”命令。
(2)在插入的模塊中輸入如下代碼(可以復(fù)制此處代碼進(jìn)行粘貼。能實(shí)現(xiàn)顏色求和功能的代碼有多種,下方只是相對(duì)簡(jiǎ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)行條件求和,與顏色無(wú)關(guān)。
2.如果真是手動(dòng)為單元格填充顏色的,那查找法、宏表函數(shù)法、自定義函數(shù)法都可以。
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育Excel精品好課任你選擇!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
版權(quán)申明:
本文作者小窩;部落窩教育享有稿件專(zhuān)有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車(chē)一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱(chēng)完成目錄制作
- 延遲退休落地,快速查詢(xún)你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂(yōu) !
- 如何用LET+LAMBDA直接寫(xiě)遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)