Tips: Andra språk är Google-översatta. Du kan besöka English version av den här länken.
Logga in
x
or
x
x
Registrera
x

or

Hur räknar man och summerar celler baserat på bakgrundsfärg i Excel?

Anta att du har en rad celler med olika bakgrundsfärger, till exempel rött, grönt, blått och så vidare men nu måste du räkna hur många celler i det området som har en viss bakgrundsfärg och summera de färgade cellerna med samma viss färg . I Excel finns det ingen direkt formel för att beräkna Sum och Count av färgceller, här kommer jag att presentera dig några sätt att lösa detta problem.

Räkna och summera celler baserat på specifik fyllfärg med Filter och SUBTOTAL

Räkna och summera celler baserat på specifik fyllfärg med GET.CELL-funktionen

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

Räkna och summa celler baserat på specifik fyllfärg med Kutools funktioner

Räkna och summa celler baserat på specifik fyllfärg (eller villkorlig formateringsfärg) med Kutools for Excel

Ett klick för att räkna, summa och genomsnittliga färgade celler i Excel

Med det utmärkta Räkna efter färg egenskap av Kutools för Excel, kan du snabbt räkna, summa och genomsnittliga celler med angiven fyllfärg eller teckensnittsfärg med bara ett klick i Excel. Dessutom kommer den här funktionen också att ta reda på max- och minvärdena för cellerna genom fyllfärgen eller teckensnittsfärgen. Fullständig funktion Gratis test 60-dag!
Annonsräkning enligt färg 2


Räkna och summera färgade celler med Filter och SUBTOTAL

Antag att vi har ett fruktförsäljningsbord enligt nedan visad skärmdump, och vi räknar eller summerar de färgade cellerna i kolumnen Mängd. I den här situationen kan vi filtrera kolumnbeloppet efter färg och sedan räkna eller summera filtrerade färgade celler med SUBTOTAL-funktionen enkelt i Excel.

1. Välj tomma celler för att gå till SUBTOTAL-funktionen.

  1. För att räkna alla celler med samma bakgrundsfärg, ange formeln = SUBTOTAL (102, E2: E20);
  2. För att summera alla celler med samma bakgrundsfärg, ange formeln = SUBTOTAL (109, E2: E20);


Anmärkningar: I båda formlerna är E2: E20 kolumängden innehållande de färgade cellerna, och du kan ändra dem efter behov.

2. Välj tabellens rubrik och klicka på Data > filter. Se skärmdump:

3. Klicka på Filter-ikonen i rubrikkolumnen i kolumnen Mängd och klicka Sortera efter färg och den angivna färgen du räknar med successivt. Se skärmdump:

Efter filtrering räknas båda SUBTOTAL-formlerna automatiskt och summerar alla filtrerade färgceller i kolumnen Amount. Se skärmdump:

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


Räkna eller summera färgade celler med GET.CELL-funktionen

I den här metoden kommer vi att skapa ett namnområde med funktionen GET.CELL, få färgkod för celler, och sedan räkna eller summera med färgkoden enkelt i Excel. Gör så här:

1. Klicka Formler > Ange namn. Se skärmdump:

2. I dialogrutan Nytt namn gör du som visas nedan:
(1) Skriv ett namn i rutan Namn;
(2) Ange formeln = GET.CELL (38, Sheet4! $ E2) i rutan Refers to (Notera: i formeln 38 medel returnerar cellkoden, och Sheet4! $ E2 är den första cellen i kolumnen Mängd utom kolumnrubriken som du behöver byta utifrån din tabelldata.)
(3) Klicka på OK knapp.

3. Lägg nu till en ny färgkolumn direkt till originalbordet. Skriv sedan nästa formel = NumColor , och dra AutoFill-handtaget för att använda formeln till andra celler i kolumnen Färg. Se skärmdump:
Anmärkningar: I formeln NumColor är namnet som vi angav i de första 2-stegen. Du måste ändra det till det angivna namnet du anger.

Nu återkommer färgkoden för varje cell i kolumnen Mängd i Färgkolumnen. Se skärmdump:

4. Kopiera och lista fyllnadsfärgen i ett tomt område i det aktiva kalkylbladet och skriv formulär bredvid det enligt bilden nedan:
A. För att räkna celler efter färg, ange formeln = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. För att summera celler efter färg, ange formeln = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Anmärkningar: I båda formlerna $ F $ 2: $ F $ 20 är kolumnen Färg, NumColor är det angivna namnet, $ E $ 2: $ E $ 20 är mängden kolumn, och du kan ändra dem som du behöver.

Nu ser du cellerna i kolumnen Mängd räknas och summeras av deras fyllfärger.


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


Om man antar att de färgade cellerna sprids i ett intervall som visas nedan, kan båda ovanstående metoderna inte räkna eller summa de färgade cellerna. Här kommer denna metod att introducera en VBA för att lösa problemet.

1. Håll ner ALT + F11 nycklar, och det öppnar Microsoft Visual Basic för applikationer fönster.

2. Klicka Infoga > Modulerna, och klistra in följande kod i modulfönstret.

VBA: Räkna och summa celler baserade på bakgrundsfärg:

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. Spara sedan koden och använd följande formel:
A. Räkna de färgade cellerna: = Colorfunction (A, B: C, FALSE)
B. Summa de färgade cellerna: = Colorfunction (A, B: C, SANT)

Anmärkning: I ovanstående formler, A är cellen med den speciella bakgrundsfärgen du vill beräkna räkna och summa, och FÖRE KRISTUS är cellområdet där du vill beräkna räkna och summa.

4. Ta följande skärmdump till exempel, ange formeln= Colorfunction (A1, A1: D11, FALSE) att räkna de gula cellerna. Och använd formeln = Colorfunction (A1, A1: D11, SANT) att summera de gula cellerna. Se skärmdump:

5. Om du vill räkna och summera andra färgade celler, upprepa steg 4. Då får du följande resultat:


Räkna och summa celler baserat på specifik fyllfärg med Kutools funktioner

Kutools for Excel stöder också några användbara funktioner för att hjälpa Excel-användare att göra speciella beräkningar, säger räkna med cellbakgrundsfärg, summa efter typsnitt färg etc.

Kutools för Excel - Inkluderar mer än 300 praktiska Excel-verktyg. Fullständig funktion gratis provperiod 60-dag, inget kreditkort krävs! Hämta den nu!

1. Välj den tomma cellen som du placerar räknarresultaten och klicka på Kutools > Kutools funktioner > Statistisk & Matematik > COUNTBYCELLCOLOR. Se skärmdump:

2. I dialogrutan Funktionsargument anger du det intervall du räknar in färgade celler inuti i Hänvisning rutan, välj den cell som fylls med den angivna bakgrundsfärgen i Color_index_nr rutan och klicka på OK knapp. Se skärmdump:

Anmärkningar:
(1) Du kan också skriva in den angivna Kutools-funktionen = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) i tomcellen eller formulärfältet direkt för att få räknaresultaten;
(2) Klicka Kutools > Kutools funktioner > Statistisk & Matematik > SUMBYCELLCOLOR eller typ = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) i den tomma cellen direkt till summan celler baserat på den angivna bakgrundsfärgen.
Applicera COUNTBYCELLCOLOR och SUMBYCELLCOLOR Funktionerna för varje bakgrundsfärg separat, och du får resultatet enligt nedan:

Kutools funktioner innehåller ett antal inbyggda funktioner för att hjälpa Excel-användare att beräkna enkelt, inklusive Räkna / Summa / Genomsnittliga Synliga celler, Räkna / summera med cellfärg, Räkna / Summa med teckenfärg, Räkna tecken, Räkna med typsnitt fetstilEtc. Ha en gratis prov!


Räkna och summera celler baserat på specifik fyllfärg med Kutools for Excel

Med ovanstående Användardefinierad funktion måste du ange formel 1 i taget, om det finns många olika färger, kommer den här metoden att vara långsamt och tidskrävande. Men om du har det Kutools för ExcelÄr Räkna efter färg nytta, kan du snabbt generera en rapport av de färgade cellerna. Du kan inte bara räkna och summera de färgade cellerna, men kan också få de genomsnittliga, maximala och minsta värdena för det färgade området.

Kutools för Excel - Inkluderar mer än 300 praktiska Excel-verktyg. Fullständig funktion gratis provperiod 60-dag, inget kreditkort krävs! Hämta den nu!

1. Välj det intervall som du vill använda och klicka på Kutools More > Räkna efter färg, se skärmdump:

2. Och i Räkna efter färg dialogrutan, vänligen gör som visas nedan:
(1) Välj Standardformatering från Färgmetod listrutan
(2) Välj Bakgrund från Räkningstyp listrutan.
(3) Klicka på knappen Generera rapport.

Anmärkningar: För att räkna och summera färgade celler med specifik villkorlig formateringsfärg, välj Villkorlig formatering från Färgmetod rullgardinsmenyn i ovanstående dialogruta, eller välj Standard och villkorlig formatering från rullgardinsmenyn för att räkna alla celler fyllda med den angivna färgen.

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

De Räkna efter färg funktionen beräknar (räkna, summa, medelvärde, max, etc.) celler med bakgrundsfärg eller teckensnittsfärg. Ha en gratis prov!


Relaterad artikel:

Hur räknar / summerar celler baserat på teckensnittsfärgerna i Excel?


Demo: Räkna och summa celler baserat på bakgrund, villkorlig formateringsfärg:

I den här videon Kutools fliken och Kutools More fliken läggs till av Kutools för Excel. Om det behövs klickar du på här att ha en 60-dagars gratis försök utan begränsning!



Rekommenderade produktivitetsverktyg

Fliken Office

guld star1 Hämta praktiska flikar till Excel och annan Office-programvara, precis som Chrome, Firefox och ny Internet Explorer.

Kutools för Excel

guld star1 Fantastiskt! Öka din produktivitet i 5 minuter. Behöver inte några speciella färdigheter, spara två timmar varje dag!

guld star1 300 Nya funktioner för Excel, gör Excel mycket enkelt och kraftfullt:

  • Sammanfoga cell / rader / kolumner utan att förlora data.
  • Kombinera och konsolidera flera ark och arbetsböcker.
  • Jämför rader, kopiera flera rader, konvertera text till datum, enhet och valutaomvandling.
  • Räkna med färger, personsökande subtotaler, avancerad sortering och superfilter,
  • Mer Välj / Infoga / Radera / Text / Format / Länk / Kommentar / Arbetsböcker / Arbetsblad Verktyg ...

Skärmdump av Kutools för 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 · 1 months 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 · 2 months 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 · 1 months 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 · 2 months 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 · 3 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 · 2 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 · 4 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 6 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.