Hoppa till huvudinnehåll

Excel-tips: Räkna/summa celler efter färg (bakgrund, teckensnitt, villkorlig formatering)

I dagliga uppgifter är färgmärkning en populär metod för att snabbt särskilja och lyfta fram avgörande data. Men hur räknar eller summerar vi celldata baserat på specifik färg (fyllningsfärg, teckensnittsfärg, villkorlig formatering)? Som standard erbjuder Excel inte en direkt funktion för att räkna eller summera efter färg. Ändå, med några knep och indirekta metoder, kan vi fortfarande uppnå detta. Den här artikeln kommer att utforska hur man räknar eller summerar data efter färg.

Räkna och summera celler baserat på bakgrundsfärg

Räkna och summera celler baserat på teckensnittsfärg

Räkna och summera celler baserat på villkorlig formateringsfärg


Video: Räkna och summera celler baserat på färg


Räkna och summera celler baserat på bakgrundsfärg

Till exempel, om du har ett dataintervall där värdena är fyllda med olika bakgrundsfärger som visas i skärmdumpen nedan. För att räkna eller summera cellerna baserat på en specifik färg, erbjuder Excel inte en direkt funktion för att räkna eller summera celler baserat på deras bakgrundsfärg. Men med lite uppfinningsrikedom och några praktiska tekniker kan du utföra denna uppgift. Låt oss utforska några användbara metoder i det här avsnittet.


Räkna och summera celler efter bakgrundsfärg med användardefinierad funktion

Här kommer vi att visa dig hur du skapar och använder en sådan användardefinierad funktion för att lösa denna uppgift i Excel. Vänligen gör med följande steg:

Steg 1: Öppna VBA-modulredigeraren och kopiera koden

  1. Presse Alt + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.
  2. Klicka på i det öppnade fönstret Insert > Modulerna för att skapa en ny tom modul.
  3. Kopiera och klistra sedan in koden nedan i den tomma modulen.
    VBA-kod: Räkna och summera celler baserat på bakgrundsfärg
    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
    'Updateby Extendoffice
        Dim rCell As Range
        Dim lCol As Long
        Dim vResult As Double
        lCol = rColor.Interior.ColorIndex
        vResult = 0
        If SUM Then
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + rCell.Value
                End If
            Next rCell
        Else
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + 1
                End If
            Next rCell
        End If
        ColorFunction = vResult
    End Function
    

Steg 2: Skapa formler för att räkna och summera celler efter bakgrundsfärg

Efter att ha klistrat in ovanstående kod, stäng modulfönstret och använd sedan följande formler:

  • Räkna celler baserat på den specifika bakgrundsfärgen:
    Kopiera eller skriv in formeln nedan i önskad cell för resultatet. Dra sedan fyllningshandtaget nedåt för att få andra resultat. Se skärmdump:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Anmärkningar: I denna formel, G2 är referenscellen med den specifika bakgrundsfärgen du vill matcha; $B$2:$E$12 är intervallet där du vill räkna antalet celler i G2:s färg; FALSK används för att räkna celler med matchande färg.
  • Summa celler baserat på den specifika bakgrundsfärgen:
    Kopiera eller skriv in formeln nedan i önskad cell för resultatet. Dra sedan fyllningshandtaget nedåt för att få andra resultat. Se skärmdump:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Anmärkningar: I denna formel, G2 är referenscellen med den specifika bakgrundsfärgen du vill matcha; $B$2:$E$12 är intervallet där du vill räkna antalet celler i G2:s färg; SANN används för att summera celler med matchande färg.

Räkna och summera celler efter bakgrundsfärg med en kraftfull funktion

För dem som inte är bekanta med programmering kan VBA verka ganska komplicerat. Här kommer vi att introducera ett kraftfullt verktyg - Kutool för Excel, dess Räkna efter färg funktionen låter dig enkelt beräkna (räkning, summa, medelvärde, etc.) baserat på bakgrundsfärg med bara några klick. Imponerande nog, Räkna efter färg Funktionen går längre än bara bakgrundsfärger – den kan också differentiera och beräkna baserat på teckensnittsfärger och villkorlig formatering.

Efter ladda ner och installera Kutools för Excel, välj först det dataintervall som du vill räkna eller summera celler baserat på en specifik bakgrundsfärg. Nästa, navigera till Kutools Plus Och välj Räkna efter färg.

I Räkna efter färg dialogrutan, vänligen ange operationerna:

  1. Välja Standardformatering från Färgmetod rullgardinsmeny;
  2. Ange Bakgrund från Räkna typ rullgardinsmenyn, och du kan förhandsgranska de statistiska resultaten för varje bakgrundsfärg i dialogrutan;
  3. Äntligen klickar du på Generera rapport för att exportera de beräknade resultaten till en ny arbetsbok.

Resultat:

Nu får du en ny arbetsbok med statistiken. Se skärmdump:

tips:
  1. Smakämnen Räkna efter färg Funktionen stöder också räkning och summering av celler baserat på standardtypsnittsfärg, bakgrund eller teckensnittsfärg från villkorlig formatering och en kombination av både fyllnings- och villkorsformateringsfärger.
  2. Intresserad av den här funktionen, tack klicka för att ladda ner för att få en gratis provperiod i 30 dagar.

Räkna och summera celler efter bakgrundsfärg med funktionen Filter och SUBTOTAL

Om vi ​​antar att vi har en fruktförsäljningstabell enligt skärmbilden nedan, och vi räknar eller summerar de färgade cellerna i Antal kolonn.

Steg 1: Använd funktionen SUBTOTAL

Välj tomma celler för att gå in i SUBTOTAL-funktionen.

  • För att räkna alla celler med samma bakgrundsfärg, skriv in formeln:
    =SUBTOTAL(102, F2:F16)
  • För att summera alla celler med samma bakgrundsfärg, ange formeln;
    =SUBTOTAL(109, F2:F16)
  • Anmärkningar: i formlerna ovan, 102 representerar att räkna numeriska värden i en filtrerad lista samtidigt som dolda celler utesluts; 109 representerar för att summera värden i en filtrerad lista exklusive dolda celler; F2: F16 är det intervall inom vilket antingen räkningen eller summan kommer att beräknas.

Steg 2: Filtrera celler baserat på specifik färg

  1. Välj rubriken i tabellen och klicka Data > Filter. Se skärmdump:
  2. Klicka på Filter ikon  i rubrikcellen för Antal och klicka Sortera efter färg och den angivna färgen kommer du att räkna med i följd. Se skärmdump:

Resultat:

Efter filtrering räknar och summerar SUBTOTAL-formlerna automatiskt de färgade cellerna i Antal kolumn. Se skärmdump:

Anmärkningar: Denna metod kräver att de färgade cellerna du räknar eller summerar finns i samma kolumn.

Räkna och summera celler baserat på teckensnittsfärg

Vill du räkna eller summera celler baserat på deras teckensnittsfärg i Excel? Låt oss säga att du har data, som i den givna skärmdumpen, med celler som innehåller texter i röd, blå, orange och svart färg. Excel gör detta inte lätt som standard. Men oroa dig inte! I det här avsnittet kommer vi att visa dig några enkla knep för att göra just det.


Räkna och summera celler baserat på teckensnittsfärg med användardefinierad funktion

För att räkna och summera celler med specifika teckensnittsfärger kan följande användardefinierade funktion hjälpa dig att lösa denna uppgift. Vänligen gör med följande steg:

Steg 1: Öppna VBA-modulredigeraren och kopiera koden

  1. Presse Alt + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.
  2. Klicka på i det öppnade fönstret Insert > Modulerna för att skapa en ny tom modul.
  3. Kopiera och klistra sedan in koden nedan i den tomma modulen.
    VBA-kod: Räkna och summera celler baserat på teckensnittsfärg
    Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
    'Updateby Extendoffice
        Application.Volatile
        Dim rng As Range
        Dim xTotal As Double
        Dim xCount As Double
        xTotal = 0
        xCount = 0
        For Each rng In pRange1
            If rng.Font.Color = pRange2.Font.Color Then
                If UCase(FunctionType) = "SUM" Then
                    xTotal = xTotal + rng.Value
                ElseIf UCase(FunctionType) = "COUNT" Then
                    xCount = xCount + 1
                End If
            End If
        Next
        If UCase(FunctionType) = "SUM" Then
            ProcessByFontColor = xTotal
        ElseIf UCase(FunctionType) = "COUNT" Then
            ProcessByFontColor = xCount
        Else
            ProcessByFontColor = CVErr(xlErrValue)
        End If
    End Function
    

Steg 2: Skapa formler för att räkna och summera celler efter teckensnittsfärg

Efter att ha klistrat in ovanstående kod, stäng modulfönstret och använd sedan följande formler:

  • Räkna celler baserat på den specifika teckensnittsfärgen:
    Kopiera eller skriv in formeln nedan i önskad cell för resultatet. Dra sedan fyllningshandtaget nedåt för att få andra resultat. Se skärmdump:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Anmärkningar: I denna formel, G2 är referenscellen med den specifika teckensnittsfärgen du vill matcha; $B$2:$E$12 är intervallet där du vill räkna antalet celler i G2:s färg.
  • Summa celler baserat på den specifika teckensnittsfärgen:
    Kopiera eller skriv in formeln nedan i önskad cell för resultatet. Dra sedan fyllningshandtaget nedåt för att få andra resultat. Se skärmdump:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Anmärkningar: I denna formel, G2 är referenscellen med den specifika teckensnittsfärgen du vill matcha; $B$2:$E$12 är intervallet där du vill räkna antalet celler i G2:s färg.

Räkna och summera celler baserat på teckensnittsfärg med en enkel funktion

Vill du enkelt räkna eller summera cellvärden i Excel baserat på teckensnittsfärg? Dyka i Kutools för Excel's Räkna efter färg funktion! Med detta smarta verktyg blir det enkelt att räkna och summera celler efter specifik teckenfärg. Upptäck hur Kutools kan förvandla din Excel-upplevelse.

Efter ladda ner och installera Kutools för Excel, välj först det dataintervall som du vill räkna eller summera celler baserat på en specifik teckensnittsfärg. Klicka sedan Kutools Plus > Räkna efter färg att öppna Räkna efter färg dialog ruta.

I Räkna efter färg dialogrutan, vänligen ange operationerna:

  1. Välja Standardformatering från Färgmetod rullgardinsmeny;
  2. Ange Font från Räkna typ rullgardinsmenyn, och du kan förhandsgranska de statistiska resultaten för varje teckensnittsfärg i dialogrutan;
  3. Äntligen klickar du på Generera rapport för att exportera de beräknade resultaten till en ny arbetsbok.

Resultat:

Nu har du en ny arbetsbok som visar detaljerad statistik baserat på teckensnittsfärg. Se skärmdump:

tips: Intresserad av den här funktionen, tack klicka för att ladda ner för att få en gratis provperiod i 30 dagar.

Räkna och summera celler baserat på villkorlig formateringsfärg

I Excel kan du vanligtvis använda villkorlig formatering för att tillämpa specifik färg på celler som uppfyller vissa kriterier, vilket gör datavisualiseringen intuitiv. Men vad händer om du behöver räkna eller summera de speciellt formaterade cellerna? Även om Excel inte erbjuder ett direkt sätt för detta, finns här sätt att manövrera runt denna begränsning.


Räkna och summera villkorligt formaterade celler med VBA-kod

Att räkna och summera villkorligt formaterade celler i Excel är inte enkelt med inbyggda funktioner. Du kan dock utföra denna uppgift med VBA-kod. Låt oss gå igenom hur du kan använda VBA för detta:

Steg 1: Öppna VBA-modulredigeraren och kopiera koden

  1. Presse Alt + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.
  2. Klicka på i det öppnade fönstret Insert > Modulerna för att skapa en ny tom modul.
  3. Kopiera och klistra sedan in koden nedan i den tomma modulen.
    VBA-kod: Räkna och summera celler baserat på villkorlig formateringsfärg
    Sub SumCountByConditionalFormat()
    'Updateby Extendoffice
        Dim sampleColor As Range
        Dim selectedRange As Range
        Dim cell As Range
        Dim countByColor As Long
        Dim sumByColor As Double
        Dim refColor As Long
        Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                 "Kutools for Excel", _
                                                 Type:=8)
        If selectedRange Is Nothing Then Exit Sub
        Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                               "Kutools for Excel", _
                                               Type:=8)
        If Not sampleColor Is Nothing Then
            refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
            For Each cell In selectedRange
                If cell.DisplayFormat.Interior.color = refColor Then
                    countByColor = countByColor + 1
                    sumByColor = sumByColor + cell.Value
                End If
            Next cell
            MsgBox "Count: " & countByColor & vbCrLf & _
                   "Sum: " & sumByColor, _
                   vbInformation, "Results based on Conditional Format Color"
        End If
    End Sub
    

Steg 2: Kör denna VBA-kod

  1. När du har klistrat in koden trycker du på F5 för att köra den här koden, kommer en uppmaningsruta att visas, välj det dataintervall där du vill räkna och summera celler baserat på villkorlig formatering. Klicka sedan OK, Se skärmdump:
  2. I en annan uppmaningsruta väljer du en specifik villkorlig formateringsfärg som du vill räkna och summera och klickar OK knapp, se skärmdump:

Resultat:

Nu kommer resultatet, som inkluderar både antalet och summan av celler med den angivna villkorliga formateringsfärgen, att visas i den utskjutna rutan. Se skärmdump:


Räkna och summera villkorligt formaterade celler med en smart funktion

Om du letar efter andra snabba och enkla metoder för att räkna och summera villkorligt formaterade celler, Kutools för Excel är din bästa lösning. Dess Räkna efter färg funktionen kan lösa denna uppgift med bara några klick. Dyk in för att upptäcka effektiviteten och precisionen Kutools kan tillföra ditt arbetsflöde.

Efter ladda ner och installera Kutools för Excel, välj först det dataintervall som du vill räkna eller summera celler baserat på en specifik villkorlig formateringsfärg. Klicka sedan Kutools Plus > Räkna efter färg att öppna Räkna efter färg dialog ruta.

I Räkna efter färg dialogrutan, vänligen ange operationerna:

  1. Välja Villkorlig formatering från Färgmetod rullgardinsmeny;
  2. Ange Bakgrund från Räkna typ rullgardinsmenyn och du kan förhandsgranska de statistiska resultaten för varje villkorsformateringsfärg i dialogrutan;
  3. Äntligen klickar du på Generera rapport för att exportera de beräknade resultaten till en ny arbetsbok.

Resultat:

Nu har du en ny arbetsbok som visar den detaljerade statistiken baserad på den villkorliga formateringsfärgen. Se skärmdump:

tips: Intresserad av den här funktionen, tack klicka för att ladda ner för att få en gratis provperiod i 30 dagar.

Relaterade artiklar:

  • Om teckensnittsfärgen är röd, returnera en specifik text
  • Hur skulle du kunna returnera en specifik text om teckensnittsfärgen är röd i en annan cell enligt skärmbilden nedan? I den här artikeln kommer jag att introducera några knep för att göra vissa operationer baserat på den röda teckensnittstexten i Excel.
  • Filtrera data efter flera färger
  • Normalt i Excel kan du snabbt filtrera rader med bara en färg, men har du någonsin funderat på att filtrera rader med flera färger samtidigt? Denna artikel kommer jag att prata om ett snabbt trick för dig att hantera detta problem.
  • Lägg till färg i listrutan
  • I Excel kan skapa en rullgardinslista hjälpa dig mycket, och ibland måste du färgkoda rullgardinslistans värden beroende på motsvarande valda. Till exempel har jag skapat en rullgardinslista med fruktnamnen, när jag väljer Apple behöver jag att cellen färgas med rött automatiskt, och när jag väljer Orange kan cellen färgas med orange.
  • Färg alternativa rader för sammanslagna celler
  • Det är mycket användbart att formatera alternativa rader med en annan färg i stora data för att vi ska kunna skanna data, men ibland kan det finnas några sammanslagna celler i din data. För att markera raderna växelvis med en annan färg för de sammanslagna cellerna enligt skärmbilden nedan, hur kunde du lösa det här problemet i Excel?