用戶73940654277
大家好,我是頭條號Excel自學成才
對於單元格按顏色求和有4種方法來解決,1、查找法,2、篩選法,3、宏表函數法,4、VBA代碼法,我們一一來進行介紹
1、使用查找法
我們按CTRL+F調出查找窗口
將格式調整為黃色,進行查找
按CTRL+A選中所有查找出來的結果
在公式編輯欄左邊輸入黃色,然後按CTRL+回車,定義了一個名稱管理器
最後我們在任意的一個單元格中輸入公式=SUM(黃色)便可得到最終的結果,操作的動圖如下所示:
2、使用篩選法
我們對數據列進行篩選,選擇按單元格的顏色進行篩選,選擇黃色單元格,然後我們輸入對可見單元格計算的公式=substotal(9,a:a),便可以對這些數據進行求和,操作的動圖如下:
3、使用宏表函數法
我們選中數據列,公式選項卡下,創建一個名稱管理器,然後創建名稱為顏色,公式為=get.cell(63,$A1)
然後在B列輸入公式=顏色,所有A列的顏色代碼就被顯示出來,黃色的代碼是6
最後我們使用=SUMIFS(a:a,b:b,6)得到最終的結果
4、使用VBA的方法
我們按ALT+F11,新建一個模塊,在代碼區內輸入代碼,然後調用自定義的hs函數,對單元格的區域進行求和即可
其中插入的代碼是:
Function hs(a As Range)
Dim t
For Each x In a
If x.Interior.Color = 65535 Then
t = x.Value + t
End If
Next x
hs = t
Application.Calculate
End Function
以上就是對對單元格顏色求和的4個小技巧。
頭條號:Excel自學成才,主頁有更多精彩內容
Excel自學成才
今天教大家如何按單元格對應的填充色來求和。
案例:
下表是學生成績表,每個顏色代表了不同的分數區間,現在需要對每種顏色的分數求和,如何實現?
解決方案:
1. 按 Ctrl+F 查找工作表,在彈出的對話框中點擊“格式”按鈕
2. 在彈出的對話框中點擊“從單元格選擇格式”
3. 此時鼠標會變成吸管形狀,點擊任意一個黃色的單元格 --> 回到對話框點擊“查找全部”
4. 此時可以看到所有黃色的單元格都已經找到
5. 全選找到的黃色單元格 --> 關閉查找對話框
6. 選擇菜單欄的“公式”-->“名稱管理器”--> 在彈出的對話框中點擊“新建”
7. 在新彈出的對話框,在名稱欄中輸入“黃色”-->“確定”
8. 現在回到工作表,在需要對黃色單元格求和的區域輸入以下公式即可:=sum(黃色)
9. 依照上述步驟依次對其他顏色求和
Excel學習世界
介紹兩個簡單、高效的方法。
1. 按“Ctr+F”鍵,彈出查找對話框,單擊“選項”按鈕,出現更多選項。
然後,從“格式”菜單選擇“從單元格選擇格式”,此時鼠標變成一個吸管樣式,單擊任意一個黃色單元格即可。
然後,點擊“查找全部”,選擇全部查找結果(Ctrl+A)。
在選擇的單元格上右鍵“定義名稱”,輸入一個自定義的名稱,比如“HUANG”。
最後,輸入公式“=SUM(HUANG)”即可顯示所有黃色單元格的數據之和。
2. 在任意一個黃色單元格上右鍵“篩選”、“按所選單元格篩選”。然後選中所有刪選出來的收據,在Excel任務欄就會顯示所有黃色單元格數據的平均值、計數、求和。
上述兩種方法你都學會了麼?歡迎留言談論!
光速Excel
按照題主的要求,可以採用按照背景顏色進行求和,我將以下圖為例計算結果。
查找顏色
第一步,查找到背景色相同的顏色,長按【Ctrl+F】,選擇【查找】—【高級】—【格式】—【背景色】,選擇對應的背景色。
定義名稱
第二步,定義所選背景色的名稱,選擇【公式】—【定義名稱】,新建你需要的名稱並確定。
顏色求和
最後一步,根據名稱進行求和,選擇【公式】—【自動求和】,在求和公式中將數據區域修改為剛才新建的背景顏色回車確定。
按照顏色求和步驟介紹完畢,希望對題主有幫助。
另外各位有PPT模板需求,私信回覆PPT就能GET對應下載鏈接。
永中軟件
題主,你好。做一名高校信息技術專業教師,我將按解決原理和解決辦法兩方面回答你的問題。希望能對你有所有幫助。喜歡,請點擊右上角,關注 嚴sir課堂。
解決原理
題主最終目的是求和。
那麼求和的前提條件是什麼呢?選中要進行求和的單元格區域。
選中哪些單元格區域呢?題主的例子中是標成黃色背景的單元格。
如何選中這些單元格區域呢?這是本問當中的難點問題,在數量少的時候,還可以用選取不連續單元格的方法來進行選擇,數量多的時候,就會非常麻煩。
怎麼辦呢?我從實用的角度出發,提供兩種方法:
其一,排序法(將單元格背景色相同的排成連續單元格);
其二,查找取名法。(先查找出需要的單元格區域,然後給單元格區域取名稱)
解決辦法
第一種方法:排序法
此種方法適用於較簡單情況,如題主所示例子。
1.選出求和單元格區域:選中 姓名和數量 兩列,調出 自定義排序 對話框,按數量 單元格顏色 排序,將有黃色背景的單元格放在頂端。設置如下圖所示。
2.求和:對放在一起的黃色背景單元格區域求和。
第二種方法:查找取名法
優點:這種方法適合於各種複雜的情況
缺點:如果背景單元格有調整,則修改相對複雜。
1.選出求各單元格區域:
A.調出查找對話框(快捷鍵:CTRL+F)
B.點擊 格式 後面的 小三角按鈕 ,下拉菜單中選擇 從單元格選擇格式
C.利用帶有吸管的光標,選中 1個 帶有顏色背景的單元格,再單擊 查找全部 按鈕,則會在下方得到所找到的區域。
D.利用鼠標拖放的方式將下方找到的區域全部選中(利用CRTL+A更快)
2.將所選單元格區域命名(因為前面找到的區域不能直接應用)
A.點擊公式菜單,選擇 定義名稱 命令
B.彈出對話框中,將命稱為yellow(注意:這個名稱是自己取的)
C.關閉掉查找對話框
3.運用名稱進行求和
當然除了上述兩種方法之外,還有一些其它做法。只是上面兩種方法相對簡單,理解較為容易。
如果覺得有所幫助,請點擊右上角 關注 嚴sir課堂,我們將一直關注於信息技術的應用,為大家的學習、工作和生活帶來更多的收穫。
EXCEL進階課堂
方法1:定義名稱,是否固定,簡單的顏色求和
一圖千言
方法2:宏表函數Get.cell(通過獲取顏色代碼再加工處理)
一圖勝千言
GET.CELL(63,單元格地址)-獲取該單元格中的顏色代碼!
獲取了顏色代碼後,我們輔助SUMIF/COUNTIF等進行求和計數都可以
方法3:VBA法(更加靈活自由)
一圖千言
Option Explicit
'日期:2019年8月11日
Function sumByCol(sum_rng As Range, color_rng As Range)
Dim rng As Range, temp_sum
Application.Volatile
For Each rng In Intersect(sum_rng.Parent.UsedRange, sum_rng)
If rng.Interior.ColorIndex = _
color_rng(1).Interior.ColorIndex Then
temp_sum = temp_sum + rng.Value
End If
Next
sumByCol = temp_sum
End Function
通過VBA自定義函數,我們可以更加自由,不用再添加輔助列,可以直接調用,跟普通函數一樣使用。
方法4:結合VBA事件,點擊後再固定位置顯示合計,更加方便
事件代碼,複製使用
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Interior.ColorIndex <> -4142 Then
Range("E1").Resize(1, 2).Clear
Range("E1").Interior.ColorIndex = Target.Interior.ColorIndex
Range("F1") = sumByCol(Intersect(Target.EntireRow, Target.Parent.UsedRange), Target)
End If
End Sub
Excel辦公實戰
①啟動Excel,打開表格數據,我們看到有一些黃色的單元格,按下鍵盤上的Ctrl+F鍵,打開查找對話框,單擊選項按鈕。
②單擊選項按鈕之後,會有更多的功能可供選擇,點擊格式按鈕。
③彈出單元格格式界面,我們在填充選項卡中,選擇要求和的顏色,確定。
④這時查找內容就是黃色單元格了,點擊查找全部選項。
⑤查找完成,點擊查找結果的第一項,按住Shift鍵,在點擊最後一項,這樣可以將所有的查找結果選中,關閉界面。
⑥然後點擊菜單欄--公式--定義名稱,彈出新建名稱對話框,名稱我們輸入XXX,確定。
⑦這樣,選中的單元格被取名為了黃色單元格,我們直接在指定單元格輸入: =SUM(XXX)。
⑧回車,得到結果,這裡的技巧是用黃色單元格中文名稱代替單元格,這樣就不必一一選擇了。
深海魚愛生活
1)按顏色定義名稱後用SUM求和;
2)VBA;
3)創建宏表函數(GET.CELL)再用SUMIF求和;
前面兩種方法很多答主都講過了,就不再贅述了,以下主要介紹第三種方法:創建宏表函數(GET.CELL)再用SUMIF求和。
001 新建宏表函數
1)選擇【公式】選項卡的【名稱管理器】;
2)點擊【新建】,名字可以自行決定;
3)在【引用位置】中輸入公式=GET.CELL(63,Sheet1!G3)+NOW()*0
語法:GET.CELL(信息類型,引用單元格或區域),第一個參數63表示返回單元格的填充顏色。(若需要對單元格的首字符的字體顏色編號,可將【63】改為【24】)NOW()是為了保證宏表函數及時更新。數據更新後按【F9】即可。
如圖所示,本列中,我們創建創建名COLOR的名稱:
002 創建輔助列
公式寫好了,我們還需要利用輔助列得到每個單元格顏色的編號。
PS:上圖中的B13結果不是0,那是因為顏色填充的是【白色】,並不是無顏色。
因為輔助列是在需要求和的數據的右側,上一步寫公式的時候是直接用【←】選取單元格。輔助列所在單元格一定要與引用單元格一一對應。
003 SUMIF求和
在上一步中我們已經知道需要求和的顏色的編號了,最後就只需要利用SUMIF函數對黃色進行求和。在目標單元格輸入公式:=SUMIF(B2:B17,COLOR,A2:A17)
當然,第二個參數求和條件也可以直接輸入【6】,因為在上一步中我們已經知道需要求和的顏色對應的編號是【6】。
「精進Excel」系頭條簽約作者,關注我,如果任意點開三篇文章,沒有你想要的知識,算我耍流氓!
精進Excel
工作表中,我們常常會給單元格填充不同的背景色或字體顏色,便於凸顯和區分不同的數據。
那,如何按顏色求和呢?
使用名稱管理器,管理顏色區域
Excel中的名稱管理器,可以使用名稱管理那些不連續的區域。這樣便可以在公式中直接使用名稱引用。
如:我們將所有的散落在工作表中的黃色背景單元格定義為名稱"黃色背景",然後就可以直接sum(黃色背景)來對所有的黃色區域求和。
具體步驟如下:
查找<ctrl>+F並全選<ctrl>+A所有的黃色區域
/<ctrl>/<ctrl>
將這些區域直接定義為名稱"黃色背景"
Excel便會自動生成"黃色背景"所對應的單元格地址
直接使用sum(黃色背景)求和
完整動畫操作如下
使用篩選+subtotal函數,動態按顏色求和
Subtotal函數是Excel中比較特殊的存在,TA身兼數職,可以計數、統計、求和、平均。。。
=SUBTOTAL( 功能參數, 區域1, [區域2], ...])
使用Subtotal配合篩選一起使用,便能動態按顏色求和
完整動畫演示如下:
按顏色排序
這裡,我再多分享一個小技巧,那就是按顏色排序。
不同的顏色,往往意味著不同的緊急度。
按顏色排序,你就可以對數據的緊急度一目瞭然,知道那些緊急,那些不緊急了。
希望這些技巧,可以幫到你!
Excel技巧精選
大家好,我是【Excel與財務】小編,歡迎關注我的頭條號,每天都會分享Excel實用技巧。Excel如何按顏色求和?看了前面大咖們的回答都非常專業,也非常詳細,今天我介紹一個簡的方法來實現顏色求和:
一、在目標單元格中輸入”=SUBTOTAL(109,B3:B9)“
二、選擇求和單元格區域,點擊鼠標右鍵,在彈出的菜單中依次選擇【篩選】-【按所選單元格的顏色篩選】。
三、目標單元格中顯示的數值就是黃色單元格數據的和。
希望回答對你有所幫助,如有不明白的私信【Excel與財務】,如果覺得有用,別忘記點贊喲!