Cookies hjelper oss å levere våre tjenester. Ved å bruke våre tjenester, samtykker du til vår bruk av informasjonskapsler.
Tips: Andre språk er Google-oversatt. Du kan besøke English versjon av denne lenken.
Logg inn
x
or
x
x
Registrere
x

or

Hvordan telle og summe celler basert på bakgrunnsfarge i Excel?

Anta at du har en rekke celler med forskjellige bakgrunnsfarger, for eksempel rød, grønn, blå og så videre, men nå må du telle hvor mange celler i dette området har en bestemt bakgrunnsfarge og summer de fargede cellene med samme bestemte farge . I Excel er det ingen direkte formel for å beregne Sum og Count av fargeceller, her vil jeg introdusere deg noen måter å løse dette problemet på.

Telle og Sum-celler basert på bestemt fyllfarge etter Filter og SUBTOTAL

Teller og Sum-celler basert på bestemt fyllfarge ved GET.CELL-funksjon

Telle og summe celler basert på bestemt fyllfarge med brukerdefinert funksjon

Count og Sum-celler basert på bestemt fyllfarge (eller betinget formateringskvalitet) med Kutools for Excel

Ett klikk for å telle, sum og gjennomsnittlig fargede celler i Excel

Med den utmerkede Count by Color funksjon av Kutools for Excel, kan du raskt telle, summe og gjennomsnittlige celler med spesifisert fyllfarge eller skriftfarge med bare ett klikk i Excel. Dessuten vil denne funksjonen også finne ut maks og min verdier av celler ved fyllfarge eller skriftfarge. Full Feature Free Trial 60-dag!
annonse teller etter farge 2


Count og Sum farget celler etter Filter og SUBTOTAL

Anta at vi har et fruktsalgstabell som vist under skjermbilde, og vi vil telle eller summere de fargede cellene i kolonnen Beløp. I denne situasjonen kan vi filtrere beløpskolonnen etter farge, og deretter telle eller summere filtrerte fargede celler med SUBTOTAL-funksjonen enkelt i Excel.

1. Velg tomme celler for å gå inn i SUBTOTAL-funksjonen.

  1. For å telle alle celler med samme bakgrunnsfarge, vennligst skriv inn formelen = SUBTOTAL (102, E2: E20);
  2. For å oppsummere alle celler med samme bakgrunnsfarge, vennligst skriv inn formelen = SUBTOTAL (109, E2: E20);


Merknader: I begge formlene er E2: E20 beløpet som inneholder de fargede cellene, og du kan endre dem etter behov.

2. Velg topptekst i tabellen, og klikk Data > filtre. Se skjermbilde:

3. Klikk på Filter-ikonet i toppteksten i kolonnen Mengde, og klikk Filtrer etter farge og den angitte farge du vil telle etter suksessivt. Se skjermbilde:

Etter filtrering, teller begge SUBTOTAL formler og summerer alle filtrerte fargeceller i beløpskolonnen automatisk. Se skjermbilde:

Merknader: Denne metoden krever de fargede cellene du vil telle eller summen er i samme kolonne.


Teller eller Sum farget celler ved hjelp av GET.CELL-funksjonen

I denne metoden vil vi opprette et navngitt område med GET.CELL-funksjonen, få fargekoden til celler, og deretter telle eller summere av fargekoden enkelt i Excel. Vennligst gjør følgende:

1. Klikk Formler > Definer navn. Se skjermbilde:

2. I dialogboksen Nytt navn gjør du som vist under skjermbilde:
(1) Skriv inn et navn i Navn-boksen;
(2) Skriv inn formelen = GET.CELL (38, Sheet4! $ E2) i boksen Refererer til (note: i formelen, 38 betyr returnere cellekoden, og Sheet4! $ E2 er den første cellen i kolonnen Mengde unntatt kolonneoverskriften som du må endre basert på tabelldataene dine.)
(3) Klikk på OK knapp.

3. Legg nå til en ny farge kolonne rett til det opprinnelige tabellen. Neste skriv inn formelen = NumColor , og dra AutoFill-håndtaket for å bruke formelen til andre celler i kolonnen Farge. Se skjermbilde:
Merknader: I formelen, NumColor er det navngitte området vi angav i de første 2-trinnene. Du må endre den til det angitte navnet du angir.

Nå kommer fargekoden til hver celle i kolonnen Mengde tilbake i fargekolonnen. Se skjermbilde:

4. Kopier og oppgi fyllfarge i et tomt område i det aktive regnearket, og skriv formler ved siden av det som vist skjermbilde:
A. For å telle celler etter farge, vennligst skriv inn formelen = ANTALL.HVIS ($ F $ 2: $ F $ 20, NumColor);
B. For å summere celler etter farge, skriv inn formelen = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Merknader: I begge formler, $ F $ 2: $ F $ 20 er kolonnen Farge, NumColor er det angitte navngitte området, $ E $ 2: $ E $ 20 er beløpspolonnen, og du kan endre dem etter behov.

Nå vil du se at cellene i beløpskolonnen er talt og summen av deres fyllfarger.


Telle og summe celler basert på bestemt fyllfarge med brukerdefinert funksjon


Hvis de fargede cellene sprer seg i et område som vist under skjermbilde, kan begge metodene ikke telle eller summere de fargede cellene. Her vil denne metoden introdusere en VBA for å løse problemet.

1. Hold nede ALT + F11 nøkler, og den åpner Microsoft Visual Basic for Applications vinduet.

2. Klikk innfelt > Modules, og lim inn følgende kode i modulvinduet.

VBA: Telle- og sumceller basert på bakgrunnsfarge:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3. Lagre koden og bruk følgende formel:
A. Telle de fargede cellene: = Colorfunction (A, B: C, FALSE)
B. Sum de fargede cellene: = Colorfunction (A, B: C, TRUE)

Merk: I ovennevnte formler, A er cellen med den spesielle bakgrunnsfargen du vil beregne tellingen og summen, og B: C er celleområdet hvor du vil beregne tellingen og summen.

4. Ta følgende skjermbilde for eksempel, skriv inn formelen= Colorfunction (A1, A1: D11, FALSE) å telle de gule cellene. Og bruk formelen = Colorfunction (A1, A1: D11, TRUE) å summere de gule cellene. Se skjermbilde:

5. Hvis du vil telle og summere andre fargede celler, må du gjenta trinn 4. Da får du følgende resultater:


Telle og Sum-celler basert på bestemt fyllfarge med Kutools for Excel

Med den ovenfor definerte brukerdefinerte funksjonen må du skrive inn formel 1 for en, hvis det er mange forskjellige farger, vil denne metoden være kjedelig og tidkrevende. Men hvis du har det Kutools for Excel'S Count by Color verktøy, kan du raskt generere en rapport av de fargede cellene. Du kan ikke bare telle og summere de fargede cellene, men kan også få gjennomsnittlig, maks og minverdier av det fargede området.

1. Velg området du vil bruke, og klikk Kutools More > Count by Color, se skjermbilde:

2. Og i Count by Color dialogboksen, vennligst gjør som vist skjermbilde:
(1) Velg Standardformatering fra Fargemetode nedtrekksliste;
(2) Velg Bakgrunn fra Count type nedtrekksliste.
(3) Klikk på Generer rapport-knappen.

Kutools for Excel - Inkluderer mer enn 300 praktiske Excel-verktøy. Full funksjon gratis prøveperiode 60-dag, ingen kredittkort kreves! Få det nå!

Merknader: For å telle og summere fargede celler ved spesifikke betingede formateringsfarger, velg Betinget formatering fra Fargemetode rullegardinlisten i dialogboksen ovenfor, eller velg Standard og betinget formatering fra rullegardinlisten for å telle alle celler fylt av den angitte fargen.

Nå får du en ny arbeidsbok med statistikken. Se skjermbilde:


Relatert artikkel:

Hvordan telle / summe celler basert på fontfarger i Excel?


Demo: Count og sumceller basert på bakgrunn, betinget formatering farge:

I denne videoen Kutools tab og Kutools More fanen er lagt til av Kutools for Excel. Hvis du trenger det, vennligst klikk her å ha en gratis prøveperiode på 60-dagen uten begrensning!



Anbefalte produktivitetsverktøy

Office-fanen

gull star1 Ta med praktiske faner til Excel og annen Office-programvare, akkurat som Chrome, Firefox og ny Internet Explorer.

Kutools for Excel

gull star1 Fantastisk! Øk produktiviteten din i 5 minutter. Trenger ikke noen spesielle ferdigheter, lagre to timer hver dag!

gull star1 300 Nye funksjoner for Excel, gjør Excel veldig enkelt og kraftig:

  • Slett Cell / Rows / Columns uten å miste data.
  • Kombinere og konsolidere flere ark og arbeidsbøker.
  • Sammenlign Ranges, Kopier flere ranger, Konverter tekst til dato, Enhet og Valutaomregning.
  • Telling av farger, personsøkende subtotaler, avansert sortering og superfilter,
  • Mer Velg / Sett inn / Slett / Tekst / Format / Link / Kommentar / Arbeidsbøker / Regneark Verktøy ...

Skjermbilde av Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mori · 2 days ago
    I copied and paste but calculation result is "0" why?? I am using Office 2016.
  • To post as a guest, your comment is unpublished.
    Troy Havard · 29 days ago
    I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
    • To post as a guest, your comment is unpublished.
      Jku · 13 days ago
      #NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
    • To post as a guest, your comment is unpublished.
      Troy Havard · 29 days ago
      I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
  • To post as a guest, your comment is unpublished.
    Karolin · 2 months ago
    works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
    • To post as a guest, your comment is unpublished.
      Arnier Hamz · 1 months ago
      Same here, i tried Ctrl+Alt+F9 or Ctrl+Shift+Alt+F9 to recalculate all formula and it works
  • To post as a guest, your comment is unpublished.
    Aaron · 3 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 5 months ago
    I was experiencing issues when attempting to use this function as-is to count/sum the lighter (pastel?) colors, at least in Excel 2016. Some debugging led me to realize that the range of "ColorIndex" values is quite limited, and the function above fails to distinguish between light yellow and light orange, reading both as ColorIndex = 19.

    Using the "Color" attribute, instead (as in "lCol = rColor.Interior.Color" and the two instances of "If rCell.Interior.Color = lCol Then", per the sample code above), resolved the issue.