Gå til hovedindhold

Excel-tip: Tæl/sum celler efter farve (baggrund, skrifttype, betinget formatering)

I daglige opgaver er farvemarkering en populær metode til hurtigt at skelne og fremhæve afgørende data. Men hvordan tæller eller summerer vi celledata baseret på specifik farve (fyldfarve, skriftfarve, betinget formatering)? Som standard tilbyder Excel ikke en direkte funktion til at tælle eller summere efter farve. Ikke desto mindre kan vi med nogle tricks og indirekte metoder stadig opnå dette. Denne artikel vil undersøge, hvordan man tæller eller summerer data efter farve.

Tæl og sum celler baseret på baggrundsfarve

Tæl og summer celler baseret på skriftfarve

Tæl og summer celler baseret på betinget formateringsfarve


Video: Tæl og summer celler baseret på farve


Tæl og sum celler baseret på baggrundsfarve

For eksempel, hvis du har en række data, hvor værdierne er fyldt med forskellige baggrundsfarver som vist på skærmbilledet nedenfor. For at tælle eller summere cellerne baseret på en bestemt farve, tilbyder Excel ikke en direkte funktion til at tælle eller summere celler baseret på deres baggrundsfarve. Men med en smule opfindsomhed og nogle praktiske teknikker kan du udføre denne opgave. Lad os undersøge nogle nyttige metoder i dette afsnit.


Tæl og summer celler efter baggrundsfarve med brugerdefineret funktion

Her vil vi vise dig, hvordan du opretter og bruger en sådan brugerdefineret funktion til at løse denne opgave i Excel. Gør venligst med følgende trin:

Trin 1: Åbn VBA-moduleditoren og kopier koden

  1. Presse Alt + F11 nøgler til at åbne Microsoft Visual Basic til applikationer vindue.
  2. Klik i det åbnede vindue indsatte > Moduler for at oprette et nyt tomt modul.
  3. Kopier og indsæt derefter nedenstående kode i det tomme modul.
    VBA-kode: Tæl og summer celler baseret på baggrundsfarve
    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
    

Trin 2: Opret formler til at tælle og summere celler efter baggrundsfarve

Når du har indsat ovenstående kode, skal du lukke modulvinduet og derefter anvende følgende formler:

  • Tæl celler baseret på den specifikke baggrundsfarve:
    Kopier eller skriv formlen nedenfor i din ønskede celle for resultatet. Træk derefter udfyldningshåndtaget nedad for at få andre resultater. Se skærmbillede:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Bemærk: I denne formel G2 er referencecellen med den specifikke baggrundsfarve, du vil matche; $B$2:$E$12 er det område, hvor du vil tælle antallet af celler i G2's farve; FALSK bruges til at tælle celler med matchende farve.
  • Sum celler baseret på den specifikke baggrundsfarve:
    Kopier eller skriv formlen nedenfor i din ønskede celle for resultatet. Træk derefter udfyldningshåndtaget nedad for at få andre resultater. Se skærmbillede:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Bemærk: I denne formel G2 er referencecellen med den specifikke baggrundsfarve, du vil matche; $B$2:$E$12 er det område, hvor du vil tælle antallet af celler i G2's farve; TRUE bruges til at summere celler med matchende farve.

Tæl og summer celler efter baggrundsfarve med en kraftfuld funktion

For dem, der ikke er bekendt med programmering, kan VBA virke ret kompleks. Her vil vi introducere et kraftfuldt værktøj - Kutool til Excel, dens Tæl efter farve funktionen giver dig mulighed for nemt at beregne (tælle, sum, gennemsnit osv.) baseret på baggrundsfarve med blot et par klik. Imponerende nok, Tæl efter farve funktion går ud over blot baggrundsfarver - den kan også differentiere og beregne baseret på skriftfarver og betinget formatering.

Efter download og installation af Kutools til Excel, skal du først vælge det dataområde, du vil tælle eller summere celler baseret på en bestemt baggrundsfarve. Derefter skal du navigere til Kutools Plus Og vælg Tæl efter farve.

I boksen Tæl efter farve dialogboks, angiv venligst handlingerne:

  1. Type Standardformatering fra Farvemetode rulleliste
  2. Angiv Baggrund fra Tælle type rullemenuen, og du kan få vist de statistiske resultater for hver baggrundsfarve i dialogboksen;
  3. Endelig skal du klikke på Generer rapport for at eksportere de beregnede resultater til en ny projektmappe.

Resultat:

Nu får du en ny projektmappe med statistikken. Se skærmbillede:

Tips:
  1. Tæl efter farve Funktionen understøtter også optælling og summering af celler baseret på standard skriftfarve, baggrund eller skriftfarve fra betinget formatering og en kombination af både fyld- og betinget formateringsfarver.
  2. Interesseret i denne funktion, tak klik for at downloade for at få en gratis prøveperiode i 30 dage.

Tæl og summer celler efter baggrundsfarve med Filter og SUBTOTAL funktion

Antag, at vi har en frugtsalgstabel som vist nedenfor, og vi vil tælle eller summere de farvede celler i beløb kolonne.

Trin 1: Anvend funktionen SUBTOTAL

Vælg tomme celler for at gå ind i SUBTOTAL-funktionen.

  • For at tælle alle celler med samme baggrundsfarve skal du indtaste formlen:
    =SUBTOTAL(102, F2:F16)
  • For at summere alle celler med samme baggrundsfarve skal du indtaste formlen;
    =SUBTOTAL(109, F2:F16)
  • Bemærk: i ovenstående formler, 102 repræsenterer at tælle numeriske værdier i en filtreret liste, mens skjulte celler ekskluderes; 109 repræsenterer for at opsummere værdier i en filtreret liste ekskl. skjulte celler; F2: F16 er det interval, over hvilket enten antallet eller summen vil blive beregnet.

Trin 2: Filtrer celler baseret på specifik farve

  1. Vælg overskriften på tabellen, og klik data > filtre. Se skærmbillede:
  2. Klik på knappen filtre ikon  i overskriftens celle beløb og klik på Filtrer efter farve og den angivne farve, du vil tælle efter successivt. Se skærmbillede:

Resultat:

Efter filtrering tæller SUBTOTAL-formlerne automatisk og summerer de farvede celler i beløb kolonne. Se skærmbillede:

Bemærk: Denne metode kræver, at de farvede celler, du vil tælle eller summe, er i samme kolonne.

Tæl og summer celler baseret på skriftfarve

Vil du tælle eller summere celler baseret på deres skriftfarve i Excel? Lad os sige, at du har dataene, ligesom i det givne skærmbillede, med celler, der indeholder tekster i rød, blå, orange og sort farve. Excel gør dette ikke nemt som standard. Men bare rolig! I dette afsnit viser vi dig nogle enkle tricks til at gøre netop det.


Tæl og summer celler baseret på skriftfarve med brugerdefineret funktion

For at tælle og summere celler med specifikke skriftfarver kan følgende brugerdefinerede funktion hjælpe dig med at løse denne opgave. Gør venligst med følgende trin:

Trin 1: Åbn VBA-moduleditoren og kopier koden

  1. Presse Alt + F11 nøgler til at åbne Microsoft Visual Basic til applikationer vindue.
  2. Klik i det åbnede vindue indsatte > Moduler for at oprette et nyt tomt modul.
  3. Kopier og indsæt derefter nedenstående kode i det tomme modul.
    VBA-kode: Tæl og summer celler baseret på skriftfarve
    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
    

Trin 2: Opret formler til at tælle og summere celler efter skriftfarve

Når du har indsat ovenstående kode, skal du lukke modulvinduet og derefter anvende følgende formler:

  • Tæl celler baseret på den specifikke skriftfarve:
    Kopier eller skriv formlen nedenfor i din ønskede celle for resultatet. Træk derefter udfyldningshåndtaget nedad for at få andre resultater. Se skærmbillede:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Bemærk: I denne formel G2 er referencecellen med den specifikke skriftfarve, du vil matche; $B$2:$E$12 er det område, hvor du vil tælle antallet af celler i G2s farve.
  • Sum celler baseret på den specifikke skriftfarve:
    Kopier eller skriv formlen nedenfor i din ønskede celle for resultatet. Træk derefter udfyldningshåndtaget nedad for at få andre resultater. Se skærmbillede:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Bemærk: I denne formel G2 er referencecellen med den specifikke skriftfarve, du vil matche; $B$2:$E$12 er det område, hvor du vil tælle antallet af celler i G2s farve.

Tæl og summer celler baseret på skriftfarve med en nem funktion

Ønsker du ubesværet at tælle eller summere celleværdier i Excel baseret på skriftfarve? Dyk ned i Kutools til Excel's Tæl efter farve funktion! Med dette smarte værktøj bliver det en leg at tælle og summere celler efter specifik skriftfarve. Opdag hvordan Kutools kan forvandle din Excel-oplevelse.

Efter download og installation af Kutools til Excel, skal du først vælge det dataområde, du vil tælle eller summere celler baseret på en bestemt skriftfarve. Klik derefter Kutools Plus > Tæl efter farve at åbne Tæl efter farve dialog boks.

I boksen Tæl efter farve dialogboks, angiv venligst handlingerne:

  1. Type Standardformatering fra Farvemetode rulleliste
  2. Angiv font fra Tælle type rullemenuen, og du kan få vist de statistiske resultater for hver skriftfarve i dialogboksen;
  3. Endelig skal du klikke på Generer rapport for at eksportere de beregnede resultater til en ny projektmappe.

Resultat:

Nu har du en ny projektmappe, der viser den detaljerede statistik baseret på skriftfarve. Se skærmbillede:


Tæl og summer celler baseret på betinget formateringsfarve

I Excel kan du almindeligvis bruge den betingede formatering til at anvende specifik farve på celler, der opfylder bestemte kriterier, hvilket gør datavisualisering intuitiv. Men hvad hvis du har brug for at tælle eller summere de specielt formaterede celler? Selvom Excel ikke tilbyder en direkte måde til dette, er her måder at manøvrere uden om denne begrænsning.


Tæl og summer betinget formaterede celler med VBA-kode

At tælle og summere betinget formaterede celler i Excel er ikke ligetil ved at bruge indbyggede funktioner. Du kan dog udføre denne opgave ved hjælp af VBA-kode. Lad os gennemgå, hvordan du kan bruge VBA til dette:

Trin 1: Åbn VBA-moduleditoren og kopier koden

  1. Presse Alt + F11 nøgler til at åbne Microsoft Visual Basic til applikationer vindue.
  2. Klik i det åbnede vindue indsatte > Moduler for at oprette et nyt tomt modul.
  3. Kopier og indsæt derefter nedenstående kode i det tomme modul.
    VBA-kode: Tæl og summer celler baseret på betinget formateringsfarve
    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
    

Trin 2: Udfør denne VBA-kode

  1. Når du har indsat koden, skal du trykke på F5 tasten for at køre denne kode, vises en promptboks, vælg venligst det dataområde, hvor du vil tælle og summere celler baseret på betinget formatering. Klik derefter OK, Se skærmbillede:
  2. I en anden promptboks skal du vælge en bestemt betinget formateringsfarve, som du vil tælle og summere, og klik OK knap, se skærmbillede:

Resultat:

Nu vil resultatet, som inkluderer både antallet og summen af ​​celler med den angivne betingede formateringsfarve, blive vist i den udfoldede boks. Se skærmbillede:


Tæl og summer betinget formaterede celler med en smart funktion

Hvis du leder efter andre hurtige og nemme metoder til at tælle og summere betinget formaterede celler, Kutools til Excel er din bedste løsning. Dens Tæl efter farve funktion kan løse denne opgave med blot et par klik. Dyk ned for at opdage effektiviteten og præcisionen Kutools kan bringe til din arbejdsgang.

Efter download og installation af Kutools til Excel, skal du først vælge det dataområde, du vil tælle eller summere celler baseret på en bestemt betinget formateringsfarve. Klik derefter Kutools Plus > Tæl efter farve at åbne Tæl efter farve dialog boks.

I boksen Tæl efter farve dialogboks, angiv venligst handlingerne:

  1. Type Betinget formatering fra Farvemetode rulleliste
  2. Angiv Baggrund fra Tælle type drop-down liste, og du kan få vist de statistiske resultater for hver conditionla formateringsfarve i dialogboksen;
  3. Endelig skal du klikke på Generer rapport for at eksportere de beregnede resultater til en ny projektmappe.

Resultat:

Nu har du en ny projektmappe, der viser den detaljerede statistik baseret på den betingede formateringsfarve. Se skærmbillede:


Relaterede artikler:

  • Hvis skriftfarven er rød, så returner en bestemt tekst
  • Hvordan kan du returnere en bestemt tekst, hvis skriftfarven er rød i en anden celle som vist nedenfor på skærmbilledet? I denne artikel vil jeg introducere nogle tricks til at udføre nogle handlinger baseret på den røde skrifttype i Excel.
  • Filtrer data efter flere farver
  • Normalt i Excel kan du hurtigt filtrere rækker med kun en farve, men har du nogensinde overvejet at filtrere rækker med flere farver på samme tid? Denne artikel vil jeg tale om hurtigt trick for dig at håndtere dette problem.
  • Tilføj farve til rullelisten
  • I Excel kan oprette en rulleliste hjælpe dig meget, og nogle gange skal du farvekode rullelistens værdier afhængigt af den tilsvarende valgte. For eksempel har jeg lavet en rulleliste over frugtnavnene, når jeg vælger Æble, skal cellen farves med rød automatisk, og når jeg vælger Orange, kan cellen farves med orange.
  • Farver alternative rækker til flettede celler
  • Det er meget nyttigt at formatere alternative rækker med en anden farve i store data, så vi kan scanne dataene, men nogle gange kan der være nogle flettede celler i dine data. For at fremhæve rækkerne skiftevis med en anden farve for de flettede celler som vist nedenfor skærmbillede, hvordan kunne du løse dette problem i Excel?