Note: The other languages of the website are Google-translated. Back to English
English English

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

Om du antar att du har ett antal celler med olika bakgrundsfärger, som rött, grönt, blått och så vidare, men nu måste du räkna hur många celler i det intervallet 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 summan och antalet färgceller, här presenterar jag några sätt att lösa detta problem.


Räkna och summa färgade celler efter Filter och SUBTOTAL

Antag att vi har en fruktförsäljningstabell som visas nedan, och vi räknar eller summerar de färgade cellerna i kolumnen Mängd. I den här situationen kan vi filtrera kolumnen Mängd 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å in i SUBTOTAL-funktionen.

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


Anmärkningar: I båda formlerna är E2: E20 kolumnen Mängd som innehåller de färgade cellerna, och du kan ändra dem efter behov.

2. Välj rubriken i tabellen och klicka Data > Filter. Se skärmdump:

3. Klicka på filterikonen  i rubrikcellen i kolumnen Belopp och klicka Sortera efter färg och den angivna färgen kommer du att räkna med i följd. Se skärmdump:

Efter filtrering räknar och summerar båda SUBTOTALA formler automatiskt alla filtrerade färgceller i kolumnen Mängd. Se skärmdump:

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

Ett klick för att räkna, summera 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, summera och genomsnittliga celler efter angiven fyllningsfä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 celler efter fyllningsfärg eller teckensnittsfärg. Fullversion gratis provperiod 30 dagar!
annonsantal efter färg 2

Kutools för Excel - Innehåller mer än 300 praktiska verktyg för Excel. Fullversion gratis provperiod 30 dagar, inget kreditkort krävs! Hämta den nu

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

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

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

2. I dialogrutan Nytt namn gör du som nedanstående skärmdump:
(1) Skriv ett namn i rutan Namn;
(2) Ange formeln = GET.CELL (38, Sheet4! $ E2) i hänvisar till rutan (Notera: i formeln, 38 betyder returnera cellkoden, och Ark4! $ E2 är den första cellen i kolumnen Belopp förutom kolumnrubriken som du behöver ändra baserat på dina tabelldata.)
(3) Klicka på OK knapp.

3. Lägg nu till en ny färgkolumn direkt till originaltabellen. Skriv sedan formeln = NumColor och dra handtaget AutoFyll för att tillämpa formeln på andra celler i kolumnen Färg. Se skärmdump:
Anmärkningar: I formeln, NumColor är det namngivna intervallet som vi specificerade i de två första stegen. Du måste ändra det till det angivna namnet du ställer in.

Nu återgår färgkoden för varje cell i kolumnen Belopp i färgkolumnen. Se skärmdump:

4. Kopiera och lista fyllningsfärgen i ett tomt intervall i det aktiva kalkylbladet och skriv formler bredvid det som visas 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 namngivna intervallet, $ E $ 2: $ E $ 20 är beloppskolumnen och du kan ändra dem efter behov.

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


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

Om vi ​​antar att de färgade cellerna sprids i ett intervall som visas nedan, kan båda metoderna ovan inte räkna eller summera de färgade cellerna. Här kommer den här metoden att introducera en VBA för att lösa problemet.

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

2. Klicka Insert > Modulernaoch klistra in följande kod i modulfönstret.

VBA: Räkna och summera celler baserat 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: = färgfunktion (A, B: C, FALSE)
B. Summa de färgade cellerna: = färgfunktion (A, B: C, SANT)

Obs: I ovanstående formler, A är cellen med den specifika bakgrundsfärg som du vill beräkna antalet och summan, och FÖRE KRISTUS är det cellintervall där du vill beräkna antalet och summan.

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

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


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

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

Kutools för Excel - Innehåller mer än 300 praktiska verktyg för Excel. Fullversion gratis provperiod 30 dagar, inget kreditkort krävs! Gratis testversion nu!

1. Markera den tomma cellen som du placerar räkningsresultaten och klicka på Kutools > Kutools-funktioner > Statistik och matematik > COUNTBYCELLFÄRG. Se skärmdump:

2. I dialogrutan Funktionsargument, ange det intervall du kommer att räkna färgade celler inom i Hänvisning välj cellen som fylls med den angivna bakgrundsfärgen i Färg_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 den tomma cellen eller formelfältet direkt för att få räkningsresultaten;
(2) Klicka Kutools > Kutools-funktioner > Statistik och matematik > SUMBYCELFÄRG eller typ = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) i den tomma cellen direkt för att summera celler baserat på den angivna bakgrundsfärgen.
Applicera COUNTBYCELLFÄRG och SUMBYCELFÄRG funktioner för varje bakgrundsfärg separat, och du får resultaten enligt nedanstående skärmdump:

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 / Summa efter cellfärg, Räkna / summa efter teckensnittsfärg, Räkna tecken, Räkna efter fetstilEtc. Ha en gratis testversion!


Räkna och summa celler baserat på specifik fyllningsfärg med Kutools för Excel

Med ovanstående användardefinierad funktion måste du ange formeln en efter en, om det finns många olika färger kommer den här metoden att vara tråkig och tidskrävande. Men om du har Kutools för ExcelÄr Räkna efter färg verktyg kan du snabbt skapa en rapport över de färgade cellerna. Du kan inte bara räkna och summera de färgade cellerna utan också få medelvärden, max och min för det färgade området.

Kutools för Excel - Innehåller mer än 300 praktiska verktyg för Excel. Fullversion gratis provperiod 30 dagar, inget kreditkort krävs! Gratis testversion nu!

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

2. Och i Räkna efter färg dialogrutan, gör som nedanstående skärmdump:
(1) Välj Standardformatering från Färgmetod rullgardinsmeny;
(2) Välj Bakgrund från Räkna typ listrutan.
(3) Klicka på knappen Skapa rapport.

Anmärkningar: För att räkna och summera färgade celler efter specifik villkorad formateringsfärg, välj Villkorlig formatering från Färgmetod rullgardinsmenyn i dialogrutan ovan, eller välj Standard och villkorlig formatering från listrutan 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:

Du har nu möjlighet Räkna efter färg funktionen beräknar (räkna, summa, genomsnitt, max, etc.) celler efter bakgrundsfärg eller teckensnittsfärg. Ha en gratis testversion!


Relaterad artikel:


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


Kutools för Excel innehåller mer än 300 praktiska verktyg för Excel, gratis att prova utan begränsning på 30 dagar. Ladda ner och gratis testversion nu!

De bästa Office-produktivitetsverktygen

Kutools för Excel löser de flesta av dina problem och ökar din produktivitet med 80%

  • återanvändning: Sätt snabbt i komplexa formler, diagram och allt som du har använt tidigare; Kryptera celler med lösenord; Skapa e-postlista och skicka e-post ...
  • Super Formula Bar (enkelt redigera flera rader med text och formel); Läslayout (enkelt läsa och redigera ett stort antal celler); Klistra in i filtrerat intervall...
  • Sammanfoga celler / rader / kolumner utan att förlora data; Delat cellinnehåll; Kombinera duplicerade rader / kolumner... Förhindra duplicerade celler; Jämför intervall...
  • Välj Duplicera eller Unikt Rader; Välj tomma rader (alla celler är tomma); Super Find och Fuzzy Find i många arbetsböcker; Slumpmässigt val ...
  • Exakt kopia Flera celler utan att ändra formelreferens; Skapa referenser automatiskt till flera ark; Sätt in kulor, Kryssrutor och mer ...
  • Extrahera text, Lägg till text, ta bort efter position, Ta bort mellanslag; Skapa och skriva ut personsökningstalsatser; Konvertera mellan celler innehåll och kommentarer...
  • Superfilter (spara och tillämpa filterscheman på andra ark); Avancerad sortering efter månad / vecka / dag, frekvens och mer; Specialfilter av fet, kursiv ...
  • Kombinera arbetsböcker och arbetsblad; Sammanfoga tabeller baserat på nyckelkolumner; Dela data i flera ark; Batchkonvertera xls, xlsx och PDF...
  • Mer än 300 kraftfulla funktioner. Stöder Office / Excel 2007-2019 och 365. Stöder alla språk. Enkel distribution i ditt företag eller organisation. Fullständiga funktioner 30-dagars gratis provperiod. 60-dagars pengarna tillbaka-garanti.
kte-flik 201905

Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50% och minskar hundratals musklick åt dig varje dag!
officetab botten
Sortera kommentarer efter
Kommentarer (235)
Inga betyg än. Bli först med att betygsätta!
Denna kommentar minimerades av moderatoren på webbplatsen
Mycket användbart verktyg, tack så mycket
Denna kommentar minimerades av moderatoren på webbplatsen
Efter att ha infogat funktionen , när vi ändrar värdet summan inte automatiskt uppdaterad , något förslag
Denna kommentar minimerades av moderatoren på webbplatsen
Alt-Ctrl-F9 kommer att räkna om
Denna kommentar minimerades av moderatoren på webbplatsen
Du kan lägga till och uppdatera knappen om någon annan använder filen och de inte känner till Alt-Ctrl-F9. lägg bara till denna kodrad till makrot "Application.CalculateFull". det är samma sak som Alt-Ctrl-F9 precis programmerad till en knapp.
Denna kommentar minimerades av moderatoren på webbplatsen
En que parte agrego la linea para crear el boton
Denna kommentar minimerades av moderatoren på webbplatsen
Lägg till en andra rad direkt under den första som säger Application.volitile det gör att den beräknas om efter att något har uppdaterats
Denna kommentar minimerades av moderatoren på webbplatsen
Jag försökte sätta Application.Volatile under första raden och inget händer :(
Denna kommentar minimerades av moderatoren på webbplatsen
[quote]Lägg till en andra rad direkt under den första som säger Application.volitile det gör att den beräknas om efter att något har uppdateratsgenom 12345678998765432154[/quote] Application.Volatile är den korrekta funktionen inte Application.volitile
Denna kommentar minimerades av moderatoren på webbplatsen
Jag ser ingen rad som säger application.volatile. Var hittar jag den?
Denna kommentar minimerades av moderatoren på webbplatsen
Jag dubbelklickade på cellen med ColorFunction-formeln och tryckte sedan på Enter. Den uppdaterades.
Denna kommentar minimerades av moderatoren på webbplatsen
Mycket användbart. Tack
Denna kommentar minimerades av moderatoren på webbplatsen
Jag provade också formeln. Fungerar i första raden och inte med andra och tredje. ????
Denna kommentar minimerades av moderatoren på webbplatsen
Jag håller med Jarod. De första två raderna i min funktion säger: Funktion ColorFunction(rColor As Range, rrange As Range, Optional SUM As Boolean) Application.Volatile Problemet är att bara en Alt-Ctrl-F9 kommer att räkna om. Vi letar efter en mer automatiserad lösning. Idéer?
Denna kommentar minimerades av moderatoren på webbplatsen
Prova så här: 1. vara säker på att du har funktionen sparad enligt beskrivningen 2. tillåt mig att använda ett exempel * Jag har hundratals rader med data * Jag använder kolumnerna A till AB med mer data * när jag har problem med min data, jag markerar den i gult * Jag använder den här coola formeln för att 'räkna' antalet höjdpunkter på varje rad 3. Hur man räknar antalet mina höjdpunkter per rad a. figur cellintervalletsom kan ha höjdpunkter som du vill räkna (eller summera) * för mig, jag vill RÄKNA antalet av mina flaggade höjdpunkter på varje rad (mitt intervall) b. välj en cell där du ska rapportera räkningen (eller summan) * för mig, jag placerade den längst till höger på mina data...i kolumn AE c. infoga följande formel i cellen du valde i punkt b (ovan) =colorfunction(AE3,A3:AB3,FALSE) * för mig placerade jag den här formeln i cell AE3 (slutet på min rad) OCH OCH jag markerade samma formelcell Gul d. Jag kopierade sedan ner den här formeln för alla mina datarader 4. Jag observerade en. inga räkningar gjordes. (dåligt) 5 NYCKELFRÅGOR HÄR: a. Jag tryckte på CTL+ ALT + F9 . PRESTO!!! det fungerar.! hoppas detta skämt är till någon hjälp. Skål gör så här: ABCD 1 2 3 4
Denna kommentar minimerades av moderatoren på webbplatsen
Mycket bra, tack så mycket!!!
Denna kommentar minimerades av moderatoren på webbplatsen
Mycket användbar artikel, tack så mycket
Denna kommentar minimerades av moderatoren på webbplatsen
Underbar!!! Tack så mycket!!!
Denna kommentar minimerades av moderatoren på webbplatsen
Underbart, tack så mycket
Denna kommentar minimerades av moderatoren på webbplatsen
Det här är ett bra verktyg, tack för att du delar med dig! Jag har en fråga: Jag märkte att makroformeln inte uppdaterar sig själv när du ändrar en cellfärg från en färg till en annan. Finns det något sätt att få den att uppdateras automatiskt när cellfärgerna ändras? Om jag dubbelklickar på cellen med tilläggsformeln och trycker på enter uppdateras den, men jag har ett ark med många av dessa celler och vill inte behöva uppdatera det manuellt varje gång.
Denna kommentar minimerades av moderatoren på webbplatsen
Tack för ditt svar, jag är ledsen, under den nuvarande situationen kan VBA-koden inte lösa frågan som du påpekade, du måste uppdatera dem manuellt.
Denna kommentar minimerades av moderatoren på webbplatsen
Jag har också funnit att om du använder formatmålaren för att uppdatera cellfärgen kommer formlerna att fungera som förväntat. Fortfarande inte perfekt.
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, jag har försökt. och kalkylbladet behöver bara en "Enter" Oavsett vilken cell. Så, efter att ha färglagt din cell, skriv bara ett memo på de färgade cellerna och "enter"
Denna kommentar minimerades av moderatoren på webbplatsen
prova CTL+ALT+f9 det kommer att uppdatera alla celler med additionsformler på en gång. Inte helt automatiskt men åtminstone bättre än att behöva klicka på varje cell med tilläggsformel individuellt.
Denna kommentar minimerades av moderatoren på webbplatsen
detta gjorde inget för mig :(
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, får fel. complie-fel, ett tvetydigt fel kommer.
Denna kommentar minimerades av moderatoren på webbplatsen
Jag tillämpade formeln men cellen sa NAMN? vad jag behöver fixa
Denna kommentar minimerades av moderatoren på webbplatsen
Hej RAMON, du kanske inte kopierade ovanstående kod till modulen. Du måste först kopiera koden till modulen och spara den och sedan tillämpa formlerna. Du kan försöka. Om det inte hjälper dig, vänligen meddela mig.
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, Det fungerade första gången, men nu, när jag måste lägga till en cell till summan... Jag försökte införa koden igen, spara, skriva formeln igen... det står "namn?"
Denna kommentar minimerades av moderatoren på webbplatsen
Jag har samma problem -- fungerade bra till en början, men får NAME-felet efter att ha lagt till en annan cell. Har försökt ta bort modul och lägga till igen, gå in i funktionen igen...ingen glädje.
Denna kommentar minimerades av moderatoren på webbplatsen
Du kanske måste aktivera Marcos igen. Bör blinka upp längst upp på skärmen beroende på version av excel. :)
Denna kommentar minimerades av moderatoren på webbplatsen
Skyyang, om jag mailar dig ett dokument kan du hjälpa mig med det? Mikrofon
Denna kommentar minimerades av moderatoren på webbplatsen
Tack så mycket! Det här är geni, tack för hjälpen!
Denna kommentar minimerades av moderatoren på webbplatsen
Tack SÅ mycket, jag har letat hela dagen efter ett lyckat resultat och vi har nu ett :)
Denna kommentar minimerades av moderatoren på webbplatsen
Tack. Det fungerade för mig enligt beskrivningen. Men efter att jag öppnade filen igen nästa gång visade alla celler med denna formel fel. Jag var tvungen att kopiera om kodningen igen. Är det något jag saknar? Jag kommer att behöva dela filen med andra och de kommer inte att kunna "fixa" problemet. Tack, Ker.
Denna kommentar minimerades av moderatoren på webbplatsen
Du måste spara den som Excel Macro-arbetsbok
Denna kommentar minimerades av moderatoren på webbplatsen
bra gjort..men..hur fungerar det i cellfärger med villkorlig formatering ???
Denna kommentar minimerades av moderatoren på webbplatsen
Det här är fantastiskt, tack!
Denna kommentar minimerades av moderatoren på webbplatsen
det fungerade bra gjort!!!
Denna kommentar minimerades av moderatoren på webbplatsen
kära herr, i själva verket är det underbar kod men jag stod inför ett problem att den kommer att fortsätta beräkna cellen även när vi ändrar cellfärgen
Denna kommentar minimerades av moderatoren på webbplatsen
Tack Sir, det här är ett underbart arbete och hjälper mig mycket.
Denna kommentar minimerades av moderatoren på webbplatsen
Jag har en Excel-fil med många färgade celler. Finns det något sätt att konvertera dessa färger till data? Så en röd cell kommer att ha ''röd'' i sig som data, en blå färgad cell kommer att ha ''blå'' i sig och så vidare? James
Denna kommentar minimerades av moderatoren på webbplatsen
Tidig och träffande artikel som att träffa målet som jag letar efter:) tack
Denna kommentar minimerades av moderatoren på webbplatsen
Jag verkar inte få det här att fungera. Fungerar det bara för celler som har färgats manuellt? Jag behöver räkna celler som har färgats via villkorlig formatering, men det spelar inte boll för tillfället.
Denna kommentar minimerades av moderatoren på webbplatsen
Jag har samma problem som Ian, jag försöker summera siffror baserat på färgreglerna som ställts in av villkorlig formatering, men det här verkar inte fånga det. Några förslag?
Denna kommentar minimerades av moderatoren på webbplatsen
Vi kommer att försöka förbättra den i de kommande versionerna. :-)
Denna kommentar minimerades av moderatoren på webbplatsen
Jag kastade ett Powershell-skript som fungerar som en sorts lösning: " #setup Excel $excelApp = New-Object -comobject Excel.Application $excelApp.Quit() $excelApp.Visible = $True $workbook = $excelApp.Workbooks .Open("H:\Desktop\test.xlsx")#ÄNDRA DETTA TILL DIN EXCEL-FILADRESS. $worksheet = $workbook.Worksheets.Item("Sheet1")#ÄNDRA DETTA OM DU ARNET INTE HETAS "Sheet1" #statiska variabler $row = 1 $column = 1#ÄNDRA DENNA VARIABLEN TILL VILKEN KOLUMN DU SÖKER $totalRow = $worksheet.UsedRange.Rows.Count do{ $currentCell = $worksheet.cells.item($row, $column ) if($currentCell.text -eq "SEARCH_FOR_THIS") { $worksheet.cells.item($row,$column).Interior.ColorIndex = 44#ÄNDRA DETTA NUMMER FÖR ATT ÄNDRA DEN NYA CELL-FÄRGEN "$row, $column = TOMT. Färgläggning" } $row++ $row } while($row -lt $totalRow) "Skript färdigt." "SPARAR..." $excelApp.Save $excelApp.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject ($excelApp) " Det här skriptet kommer att söka igenom ditt kalkylblad och om det hittar en cell som innehåller "SEARCH_FOR_THIS" (du bör förmodligen ändra den delen i skriptet) så kommer det att vända den cellen till vilken färg som finns i skriptet (för närvarande inställd på 44, en konstig orange/brun färg). När skriptet har avslutats kommer det att försöka tala om för Excel att det vill spara, men Excel är inte ett särskilt tillförlitligt program så kommer att be dig att klicka på spara. När du har gjort det kommer det att stängas. Öppna sedan filen säkerhetskopiera och gör allt som är listat på den här sidan, och se till att färgen som mitt skript letar efter har samma färg som skriptet på den här sidan det letar efter. Hoppas det här hjälper!
Denna kommentar minimerades av moderatoren på webbplatsen
Legend! Tack fungerade en charm
Det finns inga kommentarer här ännu
Ladda fler
Lämna dina kommentarer
Postar som gäst
×
Betygsätt detta inlägg:
0   Tecken
Föreslagna platser