Les galetes ens ajuden a lliurar els nostres serveis. En utilitzar els nostres serveis, accepteu el nostre ús de cookies.
Consell: altres idiomes es tradueixen en Google. Pots visitar el English versió d'aquest enllaç.
Iniciar Sessió
x
or
x
x
Registre
x

or

Com comptar i sumar cèl · lules basades en el color de fons a Excel?

Suposant que teniu un rang de cel·les amb diferents colors de fons, com ara el vermell, el verd, el blau, etc., però ara heu de comptar quantes cel·les d'aquest rang tenen un cert color de fons i sumar les cel·les de colors amb el mateix color . En Excel, no hi ha cap fórmula directa per calcular la suma i el recompte de les cel·les de color, aquí us presentaré algunes maneres de resoldre aquest problema.

Càlculs de comptatge i suma basats en un color de farciment específic per filtre i SUBTOTAL

Càlculs de comptatge i suma basats en el color de farciment específic per la funció GET.CELL

Càlculs de recompte i suma basats en un color de farciment específic amb la funció definida per l'usuari

Càlculs de comptatge i suma basats en un color de farciment específic (o color de format condicional) amb Kutools for Excel

Un clic per explicar, sumar i les cel·les de colors mitjanes d'Excel

Amb l'excel·lent Compte per color característica de Kutools for Excel, podeu comptar ràpidament, sumar i les cel·les mitjanes mitjançant el color o el color de la font especificat amb només un clic a Excel. A més, aquesta característica també descobrirà els valors màxim i mínim de les cel·les pel color del farciment o el color de la font. Prova gratuïta de característiques completes 60-day!
recompte d'anuncis per color 2


Compte i suma cel·les de colors per filtre i SUBTOTAL

Suposem que tenim una taula de vendes de fruites, com es mostra a continuació, i explicarem o sumarem les cel·les de colors de la columna Amount. En aquesta situació, podem filtrar la columna Amount per color i, a continuació, comptar o sumar cèl·lules de colors filtrades per la funció SUBTOTAL fàcilment en Excel.

1. Seleccioneu cel·les en blanc per introduir la funció SUBTOTAL.

  1. Per explicar totes les cel·les amb el mateix color de fons, introduïu la fórmula = SUBTOTAL (102, E2: E20);
  2. Per sumar totes les cel·les amb el mateix color de fons, introduïu la fórmula = SUBTOTAL (109, E2: E20);


Nota: En ambdues fórmules, E2: E20 és la columna Amount que conté les cel·les de colors, i podeu canviar-les segons necessiteu.

2. Seleccioneu la capçalera de la taula i feu clic a dades > filtre. Mostra la captura de pantalla:

3. Feu clic a la icona Filtre a la cel·la de capçalera de la columna Quantitat i feu clic a Filtrar per color i el color especificat que comptaràs successivament. Veure captura de pantalla:

Després del filtratge, les dues fórmules SUBTOTAL compten i es recullen totes les cel·les de color filtrades a la columna Amount automàticament. Veure captura de pantalla:

Nota: Aquest mètode requereix que les cel·les de colors que contareu o sumaran a la mateixa columna.


Compte o suma les cel·les de colors mitjançant la funció GET.CELL

En aquest mètode, crearem un rang amb nom amb la funció GET.CELL, obtindreu el codi de color de les cel·les i, a continuació, comptareu o sumeu-vos amb el codi de color fàcilment a Excel. Feu el següent:

1. Clic Fórmules > Defineix el nom. Mostra la captura de pantalla:

2. Al quadre de diàleg Nom nou, feu el següent quadre de pantalla que es mostra:
(1) Escriviu un nom al quadre Nom;
(2) Introduïu la fórmula = GET.CELL (38, Sheet4! $ E2) a la caixa de referències (nota: a la fórmula, 38 vol dir tornar el codi de la cel·la, i Sheet4! $ EXTR és la primera cel·la de la columna Amount, excepte el encapçalament de la columna que heu de canviar segons les dades de la vostra taula.
(3) Feu clic al botó OK botó.

3. Ara afegiu una nova columna de color a la dreta de la taula original. A continuació, escriviu la fórmula = NumColor , i arrossegueu l'identificador d'emplenament automàtic per aplicar la fórmula a altres cel·les de la columna Color. Veure captura de pantalla:
Nota: A la fórmula, NumColor és l'interval anomenat que vam especificar en els primers passos 2. Heu de canviar-lo al nom especificat que heu definit.

Ara el codi de color de cada cel·la de la columna Amount torna a la columna de colors. Veure captura de pantalla:

4. Copieu i enumereu el color del farciment en un interval en blanc al full de treball actiu i escriviu fórmules al costat d'ell tal com es mostra a la captura de pantalla següent:
A. Per explicar les cel·les per color, introduïu la fórmula = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Per resumir les cel·les per color, introduïu la fórmula = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Nota: En ambdues fórmules, $ F $ 2: $ F $ 20 és la columna Color, NumColor és l'interval especificat especificat, $ E $ 2: $ E $ 20 és la columna de quantitat, i podeu canviar-los com necessiteu.

Ara veureu que les cel·les de la columna Amount es compten i s'hi afegeixen els colors de farciment.


Càlculs de recompte i suma basats en un color de farciment específic amb la funció definida per l'usuari


Si suposem que les cel·les de colors es dispersen en un rang a continuació de la captura de pantalla que es mostra, els dos mètodes anteriors no poden comptar o sumar les cel·les de colors. Aquí, aquest mètode introduirà un VBA per resoldre el problema.

1. Mantingueu premut el botó ALT + F11 tecles i obre el Microsoft Visual Basic per a aplicacions finestra.

2. Clic Insereix > Mòduls, i enganxeu el següent codi a la finestra del mòdul.

VBA: Càlculs de recompte i suma basats en el color de fons:

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. A continuació, deseu el codi i apliqueu la següent fórmula:
A. Compteu les cel·les de colors: = funció de color (A, B: C, FALSE)
B. Suma les cèl·lules de colors: = funció de color (A, B: C, TRUE)

Nota: a les fórmules anteriors, A és la cel·la amb el color de fons en particular que voleu calcular el recompte i la suma, i B: C és l'interval de cel·les on voleu calcular el recompte i la suma.

4. Feu la captura de pantalla següent, per exemple, introduïu la fórmula= funció de color (A1, A1: D11, FALSE) per explicar les cèl · lules grogues. I utilitzeu la fórmula = funció de color (A1, A1: D11, TRUE) per sumar les cel·les grogues. Mostra la captura de pantalla:

5. Si voleu comptar i afegir altres cel·les de colors, torneu a repetir el pas 4. A continuació, obtindreu els resultats següents:


Càlculs de comptatge i suma basats en un color de farciment específic amb Kutools for Excel

Amb la Funció definida per l'usuari anterior, heu d'introduir la fórmula un per un, si hi ha molts colors diferents, aquest mètode serà tediós i requereix molt de temps. Però si ho teniu Kutools for Excel'S Compte per color Utilitat, podeu generar ràpidament un informe de les cel·les de colors. No només podeu comptar i sumar les cel·les de colors, sinó que també podeu obtenir els valors mitjana, màxima i mínima del rang de colors.

1. Seleccioneu l'interval que vulgueu utilitzar i feu clic a Kutools més > Compte per color, veure captura de pantalla:

2. I a la Compte per color quadre de diàleg, feu el següent quadre de pantalla que es mostra:
(1) Seleccioneu Format estàndard des Mètode de color llista desplegable;
(2) Seleccioneu Fons des Tipus de recompte llista desplegable.
(3) Feu clic al botó Generar informe.

Kutools for Excel - Inclou més eines 300 pràctiques d'Excel. Prova gratuïta de prova completa 60-dia, no cal una targeta de crèdit. Aconsegueix ara!

Nota: Per comptar i sumar cèl·lules de colors mitjançant un color de format condicional específic, seleccioneu Formató condicional des Mètode de color llista desplegable a dalt del diàleg o seleccioneu Format estàndard i condicional des de la llista desplegable per explicar totes les cel·les omplertes pel color especificat.

Ara obtindreu un nou llibre amb les estadístiques. Veure captura de pantalla:


Article relacionat:

Com comptar / sumar les cel·les segons els colors de la font a l'Excel?


Demostració: comptar i sumar cel·les en funció del fons, color de format condicional:

En aquest vídeo, el Kutools fitxa i el Kutools més la pestanya és afegida per Kutools for Excel. Si ho necessiteu, feu clic a aquí tenir una prova gratuïta 60-dia sense limitacions!



Eines de productivitat recomanades

Pestanya d'Office

estrella d'or1 Porteu les pestanyes pràctiques a l'Excel i a un altre programari d'Office, igual que Chrome, Firefox i el nou Internet Explorer.

Kutools for Excel

estrella d'or1 Increïble! Incrementeu la productivitat en 5 minuts. No necessites cap habilitat especial, estalvieu dues hores cada dia.

estrella d'or1 300 Noves característiques per a Excel, Excel molt fàcil i potent:

  • Combina cel·les / files / columnes sense perdre dades.
  • Combina i consolida diverses fulles i llibres.
  • Comparar intervals, copiar diversos rangs, convertir text a data, unitat i conversió de divises.
  • Compte per colors, subtotals de paginació, classificació avançada i filtre súper,
  • Més Seleccioneu / Insereix / Suprimeix / Text / Format / Enllaç / Comentari / Llibres / Eines de full de càlcul ...

Tret de pantalla de Kutools per a 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 · 6 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 · 1 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 · 17 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 · 1 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 · 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.