Saltar al contenido principal

Consejos de Excel: cuente/suma celdas por color (fondo, fuente, formato condicional)

En las tareas diarias, el marcado de colores es un método popular para distinguir y resaltar rápidamente datos cruciales. Pero, ¿cómo contamos o sumamos los datos de las celdas según un color específico (color de relleno, color de fuente, formato condicional)? De forma predeterminada, Excel no ofrece una función directa para contar o sumar por color. Sin embargo, con algunos trucos y métodos indirectos todavía podemos conseguirlo. Este artículo explorará cómo contar o sumar datos por color.

Contar y sumar celdas según el color de fondo

Contar y sumar celdas según el color de fuente

Cuente y sume celdas según el color de formato condicional


Vídeo: contar y sumar celdas según el color


Contar y sumar celdas según el color de fondo

Por ejemplo, si tiene un rango de datos donde los valores se rellenan con diferentes colores de fondo, como se muestra en la captura de pantalla siguiente. Para contar o sumar las celdas según un color específico, Excel no ofrece una función directa para contar o sumar celdas según su color de fondo. Sin embargo, con un poco de ingenio y algunas técnicas útiles, puedes realizar esta tarea. Exploremos algunos métodos útiles en esta sección.


Cuente y sume celdas por color de fondo con la función definida por el usuario

Aquí, le mostraremos cómo crear y utilizar una función definida por el usuario para resolver esta tarea en Excel. Por favor hazlo con los siguientes pasos:

Paso 1: abre el editor de módulos de VBA y copia el código

  1. Prensa Alt + F11 teclas para abrir el Microsoft Visual Basic para aplicaciones ventana.
  2. En la ventana abierta, haga clic en recuadro > Módulo para crear un nuevo módulo en blanco.
  3. Luego, copie y pegue el siguiente código en el módulo en blanco.
    Código VBA: cuente y sume celdas según el color de fondo
    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
    'Updateby Extendoffice
        Dim rCell As Range
        Dim lCol As Long
        Dim vResult As Double
        lCol = rColor.Interior.ColorIndex
        vResult = 0
        If SUM Then
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + rCell.Value
                End If
            Next rCell
        Else
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + 1
                End If
            Next rCell
        End If
        ColorFunction = vResult
    End Function
    

Paso 2: crea fórmulas para contar y sumar celdas por color de fondo

Después de pegar el código anterior, cierre la ventana del módulo y luego aplique las siguientes fórmulas:

  • Cuente las celdas según el color de fondo específico:
    Copie o escriba la fórmula proporcionada a continuación en la celda deseada para obtener el resultado. Luego, arrastre el controlador de relleno hacia abajo para obtener otros resultados. Ver captura de pantalla:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Note: En esta fórmula, G2 es la celda de referencia con el color de fondo específico que desea hacer coincidir; $B$2:$E$12 es el rango donde desea contar el número de celdas del color de G2; FALSO se utiliza para contar celdas con colores coincidentes.
  • Suma de celdas según el color de fondo específico:
    Copie o escriba la fórmula proporcionada a continuación en la celda deseada para obtener el resultado. Luego, arrastre el controlador de relleno hacia abajo para obtener otros resultados. Ver captura de pantalla:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Note: En esta fórmula, G2 es la celda de referencia con el color de fondo específico que desea hacer coincidir; $B$2:$E$12 es el rango donde desea contar el número de celdas del color de G2; VERDADERO se utiliza para sumar celdas con colores coincidentes.

Cuente y sume celdas por color de fondo con una potente función

Para quienes no están familiarizados con la programación, VBA puede parecer bastante complejo. Aquí, presentaremos una herramienta poderosa: Kutool para Excel, su Contar por color La función le permite calcular fácilmente (recuento, suma, promedio, etc.) según el color de fondo con solo unos pocos clics. Impresionantemente, Contar por color La función va más allá de los simples colores de fondo: también puede diferenciar y calcular en función de los colores de fuente y el formato condicional.

Después descargando e instalando Kutools para Excel, primero, seleccione el rango de datos que desea contar o sumar celdas según un color de fondo específico. A continuación, navegue hasta Kutools Más y elige Contar por color.

En la Contar por color cuadro de diálogo, especifique las operaciones:

  1. Seleccione Formato estándar del desplegable Método de color la lista desplegable;
  2. Especificar Antecedentes del desplegable Tipo de recuento lista desplegable y podrá obtener una vista previa de los resultados estadísticos para cada color de fondo en el cuadro de diálogo;
  3. Por último, haz clic Generar informe para exportar los resultados calculados a un nuevo libro de trabajo.

Resultado:

Ahora obtendrá un nuevo libro de trabajo con las estadísticas. Ver captura de pantalla:

Tips:
  1. El Contar por color La función también admite contar y sumar celdas según el color de fuente estándar, el color de fondo o de fuente del formato condicional, y una combinación de colores de relleno y de formato condicional.
  2. Interesado en esta característica, por favor haga clic para descargar para obtener una prueba gratuita durante 30 días.

Cuente y sume celdas por color de fondo con la función Filtro y SUBTOTAL

Supongamos que tenemos una tabla de ventas de frutas como la que se muestra a continuación, y contaremos o sumaremos las celdas coloreadas en la Cantidad columna.

Paso 1: Aplicar la función SUBTOTAL

Seleccione celdas en blanco para ingresar a la función SUBTOTAL.

  • Para contar todas las celdas con el mismo color de fondo, ingrese la fórmula:
    =SUBTOTAL(102, F2:F16)
  • Para sumar todas las celdas con el mismo color de fondo, ingrese la fórmula;
    =SUBTOTAL(109, F2:F16)
  • Note: en las fórmulas anteriores, 102 representa contar valores numéricos en una lista filtrada excluyendo celdas ocultas; 109 representa resumir valores en una lista filtrada excluyendo celdas ocultas; F2: F16 es el rango sobre el cual se calculará el recuento o la suma.

Paso 2: filtrar celdas según un color específico

  1. Seleccione el encabezado de la tabla y haga clic Datos > Filtrar. Ver captura de pantalla:
  2. Haga clic en el Filtrar ícono  en la celda del encabezado del Cantidad columna y haga clic en Filtrar por color y el color especificado por el que contará sucesivamente. Ver captura de pantalla:

Resultado:

Después del filtrado, las fórmulas SUBTOTALES cuentan y suman automáticamente las celdas coloreadas en el Cantidad columna. Ver captura de pantalla:

Note: Este método requiere que las celdas de colores que contará o sumará estén en la misma columna.

Contar y sumar celdas según el color de fuente

¿Quiere contar o sumar celdas según su color de fuente en Excel? Digamos que tienes los datos, como en la captura de pantalla dada, con celdas que contienen textos en color rojo, azul, naranja y negro. Excel no hace esto fácil por defecto. ¡Pero no te preocupes! En esta sección, le mostraremos algunos trucos sencillos para lograr precisamente eso.


Cuente y sume celdas según el color de fuente con la función definida por el usuario

Para contar y sumar celdas con colores de fuente específicos, la siguiente función definida por el usuario puede ayudarle a resolver esta tarea. Por favor hazlo con los siguientes pasos:

Paso 1: abre el editor de módulos de VBA y copia el código

  1. Prensa Alt + F11 teclas para abrir el Microsoft Visual Basic para aplicaciones ventana.
  2. En la ventana abierta, haga clic en recuadro > Módulo para crear un nuevo módulo en blanco.
  3. Luego, copie y pegue el siguiente código en el módulo en blanco.
    Código VBA: cuente y sume celdas según el color de fuente
    Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
    'Updateby Extendoffice
        Application.Volatile
        Dim rng As Range
        Dim xTotal As Double
        Dim xCount As Double
        xTotal = 0
        xCount = 0
        For Each rng In pRange1
            If rng.Font.Color = pRange2.Font.Color Then
                If UCase(FunctionType) = "SUM" Then
                    xTotal = xTotal + rng.Value
                ElseIf UCase(FunctionType) = "COUNT" Then
                    xCount = xCount + 1
                End If
            End If
        Next
        If UCase(FunctionType) = "SUM" Then
            ProcessByFontColor = xTotal
        ElseIf UCase(FunctionType) = "COUNT" Then
            ProcessByFontColor = xCount
        Else
            ProcessByFontColor = CVErr(xlErrValue)
        End If
    End Function
    

Paso 2: crea fórmulas para contar y sumar celdas por color de fuente

Después de pegar el código anterior, cierre la ventana del módulo y luego aplique las siguientes fórmulas:

  • Cuente las celdas según el color de fuente específico:
    Copie o escriba la fórmula proporcionada a continuación en la celda deseada para obtener el resultado. Luego, arrastre el controlador de relleno hacia abajo para obtener otros resultados. Ver captura de pantalla:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Note: En esta fórmula, G2 es la celda de referencia con el color de fuente específico que desea hacer coincidir; $B$2:$E$12 es el rango donde desea contar el número de celdas del color de G2.
  • Suma de celdas según el color de fuente específico:
    Copie o escriba la fórmula proporcionada a continuación en la celda deseada para obtener el resultado. Luego, arrastre el controlador de relleno hacia abajo para obtener otros resultados. Ver captura de pantalla:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Note: En esta fórmula, G2 es la celda de referencia con el color de fuente específico que desea hacer coincidir; $B$2:$E$12 es el rango donde desea contar el número de celdas del color de G2.

Cuente y sume celdas según el color de fuente con una función sencilla

¿Quiere contar o sumar valores de celda en Excel sin esfuerzo según el color de fuente? Sumergirse en Kutools for Excel's Contar por color ¡característica! Con esta herramienta inteligente, contar y sumar celdas por color de fuente específico se vuelve muy sencillo. Descubre como Kutools puede transformar su experiencia con Excel.

Después descargando e instalando Kutools para Excel, primero, seleccione el rango de datos que desea contar o sumar celdas según un color de fuente específico. Luego, haga clic Kutools Más > Contar por color para abrir el Contar por color caja de diálogo.

En la Contar por color cuadro de diálogo, especifique las operaciones:

  1. Seleccione Formato estándar del desplegable Método de color la lista desplegable;
  2. Especificar Fuente del desplegable Tipo de recuento lista desplegable y podrá obtener una vista previa de los resultados estadísticos para cada color de fuente en el cuadro de diálogo;
  3. Por último, haz clic Generar informe para exportar los resultados calculados a un nuevo libro de trabajo.

Resultado:

Ahora tiene un nuevo libro de trabajo que muestra estadísticas detalladas según el color de fuente. Ver captura de pantalla:

Tips: Interesado en esta característica, por favor haga clic para descargar para obtener una prueba gratuita durante 30 días.

Cuente y sume celdas según el color de formato condicional

En Excel, normalmente puedes usar el formato condicional para aplicar un color específico a las celdas que cumplen ciertos criterios, lo que hace que la visualización de datos sea intuitiva. Pero, ¿qué pasa si necesitas contar o sumar esas celdas con formato especial? Si bien Excel no ofrece una forma directa de hacerlo, aquí hay formas de sortear esta limitación.


Cuente y sume celdas formateadas condicionalmente con código VBA

Contar y sumar celdas con formato condicional en Excel no es sencillo utilizando funciones integradas. Sin embargo, puede realizar esta tarea utilizando código VBA. Repasemos cómo puedes usar VBA para esto:

Paso 1: abre el editor de módulos de VBA y copia el código

  1. Prensa Alt + F11 teclas para abrir el Microsoft Visual Basic para aplicaciones ventana.
  2. En la ventana abierta, haga clic en recuadro > Módulo para crear un nuevo módulo en blanco.
  3. Luego, copie y pegue el siguiente código en el módulo en blanco.
    Código VBA: cuente y sume celdas según el color de formato condicional
    Sub SumCountByConditionalFormat()
    'Updateby Extendoffice
        Dim sampleColor As Range
        Dim selectedRange As Range
        Dim cell As Range
        Dim countByColor As Long
        Dim sumByColor As Double
        Dim refColor As Long
        Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                 "Kutools for Excel", _
                                                 Type:=8)
        If selectedRange Is Nothing Then Exit Sub
        Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                               "Kutools for Excel", _
                                               Type:=8)
        If Not sampleColor Is Nothing Then
            refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
            For Each cell In selectedRange
                If cell.DisplayFormat.Interior.color = refColor Then
                    countByColor = countByColor + 1
                    sumByColor = sumByColor + cell.Value
                End If
            Next cell
            MsgBox "Count: " & countByColor & vbCrLf & _
                   "Sum: " & sumByColor, _
                   vbInformation, "Results based on Conditional Format Color"
        End If
    End Sub
    

Paso 2: Ejecute este código VBA

  1. Después de pegar el código, presione F5 para ejecutar este código, aparecerá un cuadro emergente, seleccione el rango de datos donde desea contar y sumar las celdas según el formato condicional. Luego, haga clic OK, Ver captura de pantalla:
  2. En otro cuadro emergente, seleccione un color de formato condicional específico que desee contar y sumar, y haga clic en OK botón, ver captura de pantalla:

Resultado:

Ahora, el resultado, que incluye tanto el recuento como la suma de las celdas con el color de formato condicional especificado, se mostrará en el cuadro emergente. Ver captura de pantalla:


Cuente y sume celdas con formato condicional con una función inteligente

Si está buscando otros métodos rápidos y sencillos para contar y sumar celdas con formato condicional, Kutools for Excel es su solución ideal. Es Contar por color La función puede resolver esta tarea con solo unos pocos clics. Sumérgete para descubrir la eficiencia y precisión que Kutools puede aportar a tu flujo de trabajo.

Después descargando e instalando Kutools para Excel, primero, seleccione el rango de datos que desea contar o sumar celdas según un color de formato condicional específico. Luego, haga clic Kutools Más > Contar por color para abrir el Contar por color caja de diálogo.

En la Contar por color cuadro de diálogo, especifique las operaciones:

  1. Seleccione Formato condicional del desplegable Método de color la lista desplegable;
  2. Especificar Antecedentes del desplegable Tipo de recuento lista desplegable y podrá obtener una vista previa de los resultados estadísticos para cada color de formato de condición en el cuadro de diálogo;
  3. Por último, haz clic Generar informe para exportar los resultados calculados a un nuevo libro de trabajo.

Resultado:

Ahora tiene un nuevo libro de trabajo que muestra estadísticas detalladas basadas en el color de formato condicional. Ver captura de pantalla:

Tips: Interesado en esta característica, por favor haga clic para descargar para obtener una prueba gratuita durante 30 días.

Artículos relacionados:

  • Si el color de fuente es rojo, devuelve un texto específico.
  • ¿Cómo podría devolver un texto específico si el color de la fuente es rojo en otra celda como se muestra a continuación? En este artículo, presentaré algunos trucos para realizar algunas operaciones basadas en el texto de fuente roja en Excel.
  • Filtrar datos por varios colores
  • Normalmente, en Excel, puede filtrar filas rápidamente con un solo color, pero, ¿alguna vez ha considerado filtrar filas con varios colores al mismo tiempo? En este artículo, hablaré sobre un truco rápido para que puedas lidiar con este problema.
  • Agregar color a la lista desplegable
  • En Excel, crear una lista desplegable puede ayudarle mucho y, a veces, es necesario codificar por colores los valores de la lista desplegable según lo correspondiente seleccionado. Por ejemplo, he creado una lista desplegable de nombres de frutas, cuando selecciono Apple, necesito que la celda se coloree de rojo automáticamente, y cuando elijo Naranja, la celda se puede colorear de naranja.
  • Colorea filas alternativas para celdas fusionadas
  • Es muy útil dar formato a filas alternas con un color diferente en datos grandes para que podamos escanear los datos, pero, a veces, puede haber algunas celdas combinadas en sus datos. Para resaltar las filas alternativamente con un color diferente para las celdas combinadas como se muestra a continuación, ¿cómo podría resolver este problema en Excel?
Comments (237)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
嗨~版主好,我用了VBA 模塊,但是完全沒有動靜,沒有出現顏色儲存格的統計數量,Count欄一片空白~~請問是為什麼呢?跟office版本有關嗎?謝謝
This comment was minimized by the moderator on the site
嗨,MINA,
文章中的VBA代碼,微軟office版本基本上都可以適用,我這代碼可以正常使用。 如果你那邊還用不了,可以上傳你的文件,我們可以幫忙看看哪裡的問題,謝謝!
This comment was minimized by the moderator on the site
I am using =IF(D272>F272,D272-F272,if(F272>D272,F272-D272,"")) formula for subtraction, and I want it will coloured also??
This comment was minimized by the moderator on the site
Hi namrata,
Do you want to fill color for the result of your formula?
So, if the result is D272-F272, you want it, say, red; If the result is F272-D272, you want it, say, green; If blank, blank?
Amanda
This comment was minimized by the moderator on the site
I ran into problems when trying to run the function. Macro errors telling me: No RETURN() or HALT() function found on macro sheet. perhaps somebody could assist here. ThanksPaul
This comment was minimized by the moderator on the site
Hi, How to make a diagram based on the colors in the table? For example, I want to count all the red, green and yellow colors in the cells in a table and make a diagram. How to do this? Please
This comment was minimized by the moderator on the site
Is there a way to count different color backgrounds from conditional formatting? The current code as of 7/14/2020 counts them all as default yellow or not at all.
This comment was minimized by the moderator on the site
Hi Dusty,
You can try the Count by Color feature of Kutools for Excel. This feature will help you quickly calculate (count, sum, average, etc.) cells by cell background color or font color, no matter they are formatted by conditional formatting or solidly format.
This comment was minimized by the moderator on the site
Did anyone find a solution to auto-refresh? I have to manually refresh for it to update. Otherwise, it works great!
This comment was minimized by the moderator on the site
Hi Dennis,
By default, formulas are calculated in Excel until you are turning off the Automatic Formula Calculation. You can enable it by clicking Formulas > Calculation Options > Automatic.
This comment was minimized by the moderator on the site
Anyone have tips on a max by color VBA?
This comment was minimized by the moderator on the site
Hi Natasha,VBA is good but hard to apply. But below methods may solve your work easily too.
Method 1: Use Find & Replace feature to select and statistic the color cells(1) Press Ctrl + H keys to open the Find and Replace dialog, and then enable the Find tab.
(2) In the dialog, click Options to show advanced find options.
(3) Then click Format > Choose Format From Cell, and select one of the specified color cells.
(4) Click Find All. Now all cells with the same fill color are found out and listed at the bottom of the dialog.
(5) Select one of found cells, and press Ctrl + A to select all found cells, so that these cells are selected in the worksheet.
(6) Now you can get the count, average, sum, min, max, etc. of these cells in the task bar.
Note: If a certain statistic result cannot be found one the taskbar, you can right click the task bar, and then tick the specified item to show it.

Method 2: Kutools for Excel
Kutools for Excel supports 30-day free trial. Therefore, you can download it and try its Count by color feature to solve your problem with several clicks only.
This comment was minimized by the moderator on the site
awesome fix! count by color over an entire sheet was just what i was looking for and your VBA code was tighter than others that i have looked at. Works like a charm. Thank you, and again, well done.
This comment was minimized by the moderator on the site
I copied and paste but calculation result is "0" why?? I am using Office 2016.
This comment was minimized by the moderator on the site
I try the same you this command =COUNTBYCELLCOLOR is counting only fill color but is not count by condition formating. Please help to improve code thx.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
#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
This comment was minimized by the moderator on the site
I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations