As cookies axúdanos a entregar os nosos servizos. Ao usar os nosos servizos, acepta o noso uso das cookies.
Consello: outros idiomas están traducidos a Google. Podes visitar o English versión desta ligazón.
Iniciar sesión
x
or
x
x
Register
x

or

Como contar e sumar celas en función da cor de fondo en Excel?

Supoñendo que ten un rango de celas con diferentes cores de fondo, como vermello, verde, azul e así por diante, pero agora cómpre contar cantas celas nese rango teñen certa cor de fondo e suma as celas de cores coa mesma cor determinada . En Excel, non hai unha fórmula directa para calcular Sum e Count de células de cor, aquí vou vos presentar algunhas formas de resolver este problema.

Contar e sumprar celas baseadas na cor do recheo específico por filtro e SUBTOTAL

Contar e sumprar celas baseadas na cor específica de recheo pola función GET.CELL

Contar e sumar as celas en función da cor específica do recheo coa función definida polo usuario

Contar e sumprar celas baseadas na cor específica de recheo (ou cor de formato condicional) con Kutools para Excel

Un clic para contar, suma e media de celas de cores en Excel

Co excelente Conta por cor característica de Kutools para Excel, podes contar, sumar e medir rápidamente as celas mediante a cor ou a cor da fonte especificadas con só un clic en Excel. Ademais, esta característica tamén descubrirá os valores máximo e mínimo das celas mediante a cor do recheo ou a cor da fonte. Proba gratuíta de características completas 60-day!
conteo de anuncios por cor 2


Contar e sumar as células de cores por filtro e SUBTOTAL

Supoñendo que temos unha táboa de vendas de froitas segundo a imaxe que se mostra a continuación, e contaremos ou sumaremos as celas coloreadas na columna Importe. Nesta situación, podemos filtrar a cor da cantidade por cor, e logo contar ou sumar as celas de cores filtradas mediante a función SUBTOTAL facilmente en Excel.

1. Seleccione as celas en branco para entrar na función SUBTOTAL.

  1. Para contar todas as celas coa mesma cor de fondo, introduza a fórmula = SUBTOTAL (102, E2: E20);
  2. Para sumar todas as celas coa mesma cor de fondo, introduza a fórmula = SUBTOTAL (109, E2: E20);


Nota: Nas dúas fórmulas E2: E20 é a columna Amount que contén as celas de cores, e pode cambialas segundo o necesites.

2. Seleccione o encabezado da táboa e faga clic Data > filtro. Ver captura de pantalla:

3. Fai clic na icona de filtro na cabeceira da cela da cantidade e faga clic Filtrar por color e a cor especificada contarase sucesivamente. Ver captura de pantalla:

Despois do filtrado, ambas as fórmulas SUBTOTAL contan e suman automaticamente todas as celas de cores filtradas na columna Amount. Ver captura de pantalla:

Nota: Este método require que as celas de cores que conteñas ou a suma estean na mesma columna.


Contar ou Suma celas de cores por función GET.CELL

Neste método, crearemos un intervalo con nome coa función GET.CELL, obteremos o código de cores das celas e, a continuación, contaremos ou sumaremos o código de cores con facilidade en Excel. Faga o seguinte:

1. Prema Fórmulas > Definir nome. Ver captura de pantalla:

2. No diálogo Novo nome, fai a seguinte imaxe mostrada:
(1) Escriba un nome no cadro Nome;
(2) Introduza a fórmula = GET.CELL (38, Sheet4! $ E2) na caixa de referencia a (nota: na fórmula, 38 significa devolver o código da cela e Sheet4! $ E2 é a primeira cela da columna Importe excepto o título da columna que precisa cambiar segundo os datos da táboa.)
(3) Faga clic na OK botón.

3. Agora engada unha nova columna de cor á dereita na táboa orixinal. A continuación, escriba a fórmula = NumColor , e arrastre o identificador de filtro automático para aplicar a fórmula a outras celas da columna Cor. Ver captura de pantalla:
Nota: Na fórmula, NumColor é o rango nomeado que especificamos nos primeiros pasos de 2. Debe cambialo ao nome especificado que configurou.

Agora o código de cores de cada cela da columna Amount retorna na Coluna de cores. Ver captura de pantalla:

4. Copia e enumera a cor do recheo nun intervalo en branco na folla de cálculo activa e escribe fórmulas ao carón como se mostra na seguinte imaxe:
A. Para contar celas por cor, ingrese a fórmula = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. Para completar as celas por cor, introduza a fórmula = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Nota: En ambas as fórmulas, $ F $ 2: $ F $ 20 é a columna Cor, NumColor é o intervalo especificado especificado, $ E $ 2: $ E $ 20 é a columna de cantidade, e podes cambialos como necesites.

Agora verá que as celas da columna Importe contáronse e súmense coas cores de recheo.


Contar e sumar as celas en función da cor específica do recheo coa función definida polo usuario


Supoñendo que as celas de cores se espallan nun rango segundo a imaxe mostrada, os dous métodos anteriores non poden contar nin sumar as celas de cores. Aquí, este método introducirá un VBA para resolver o problema.

1. Manteña o botón ALT + F11 chaves e abre o Microsoft Visual Basic para aplicacións fiestra.

2. Prema Inserir > Módulos, e pegue o seguinte código na xanela do módulo.

VBA: celas Count e Suns baseadas na cor de fondo:

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ón, garda o código e aplica a seguinte fórmula:
A. Contar as celas de cores: = función de cor (A, B: C, FALSE)
B. Suma as células coloreadas: = función de cor (A, B: C, TRUE)

Nota: nas fórmulas anteriores, A é a cela coa cor de fondo en particular que quere calcular o reconto e suma, e B: C é o intervalo de celas onde desexa calcular a conta e suma.

4. Tome a seguinte captura de pantalla por exemplo, insira a fórmula= función de cor (A1, A1: D11, FALSE) para contar as celas amarelas. E usa a fórmula = función de cor (A1, A1: D11, VERDADEIRO) para sumar as celas amarelas. Ver captura de pantalla:

5. Se desexa contar e engadir outras celas de cores, repita o paso 4. Entón obterás os seguintes resultados:


Contar e sumar celas baseadas na cor do recheo específico con Kutools para Excel

Coa anterior Función definida polo usuario, cómpre introducir a fórmula unha por unha, se hai moitas cores diferentes, este método será tedioso e lento. Pero se tes Kutools para Excel'S Conta por cor utilidade, pode xerar rapidamente un informe das celas coloreadas. Non só pode contar e sumar as celas de cores, senón que tamén pode obter os valores medios, máximos e mínimos do intervalo de cores.

1. Selecciona o intervalo que queres usar e fai clic en Kutools Máis > Conta por cor, ver captura de pantalla:

2. E no Conta por cor caixa de diálogo, faga como a continuación mostra a imaxe:
(1) Seleccione Formato estándar a partir do Método de cor lista despregábel;
(2) Seleccione Fondo a partir do Tipo de conta lista despregábel.
(3) Faga clic no botón Generar informe.

Kutools para Excel - Inclúe máis ferramentas 300 útiles a man. Proceso gratuíto de característica completa 60-día, sen necesidade de tarxeta de crédito. Comece xa!

Nota: Para contar e engadir celas de cores por cor de formato condicional específico, seleccione Formato condicional a partir do Método de cor lista despregábel no diálogo anterior ou seleccione Formato estándar e condicional desde a lista despregábel para contar todas as celas cubertas pola cor especificada.

Agora obterás un novo caderno coas estatísticas. Ver captura de pantalla:


Artigo relacionado:

Como contar / sumar celas en función das cores da fonte en Excel?


Demo: conteñen e suman celas baseadas en fondo, cor de formato condicional:

Neste video, o Kutools guía e Kutools Máis A pestaña son engadidas por Kutools para Excel. Se o necesitas, faga clic aquí para ter unha proba gratuíta 60-día sen limitación!



Ferramentas de produtividade recomendadas

Tabulación de Office

estrela de ouro1 Traia páxinas útiles para Excel e outro software de Office, como Chrome, Firefox e Internet Explorer novo.

Kutools para Excel

estrela de ouro1 Sorprendente! Aumenta tu productividad en 5 minutos. Non necesitas habilidades especiais, garda dúas horas todos os días.

estrela de ouro1 300 Novidades para Excel, Excel moi fácil e poderoso:

  • Combina celas / filas / columnas sen perder datos.
  • Combina e consolida varias follas e libros de traballo.
  • Comparar intervalos, copiar varios intervalos, converter texto a data, conversión de unidade e moeda.
  • Conta por cores, subtotales de paginación, clasificación avanzada e filtro super,
  • Máis Seleccionar / Inserir / Eliminar / Texto / Formato / Ligazón / Comentario / Libros / Follas de ferramentas ...

Pantalla de Kutools para 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 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 · 28 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 · 12 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 · 28 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.