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

¿Cómo contar y sumar celdas según el color de fondo en Excel?

Supongamos que tiene un rango de celdas con diferentes colores de fondo, como rojo, verde, azul, etc., pero ahora necesita contar cuántas celdas en ese rango tienen un cierto color de fondo y sumar las celdas coloreadas con el mismo color determinado. . En Excel, no existe una fórmula directa para calcular la suma y el recuento de celdas de color, aquí le presentaré algunas formas de resolver este problema.


Contar y sumar celdas coloreadas por filtro y SUBTOTAL

Supongamos que tenemos una tabla de ventas de frutas como se muestra a continuación, y contaremos o sumaremos las celdas de colores en la columna Cantidad. En esta situación, podemos filtrar la columna Cantidad por color y luego contar o sumar celdas de colores filtradas mediante la función SUBTOTAL fácilmente en Excel.

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

  1. Para contar todas las celdas con el mismo color de fondo, ingrese la fórmula = SUBTOTAL (102, E2: E20);
  2. Para sumar todas las celdas con el mismo color de fondo, ingrese la fórmula = SUBTOTAL (109, E2: E20);


Nota:: En ambas fórmulas, E2: E20 es la columna Cantidad que contiene las celdas coloreadas, y puede cambiarlas cuando lo necesite.

2. Seleccione el encabezado de la tabla y haga clic en Datos > Filtrar. Ver captura de pantalla:

3. Haga clic en el icono de filtro  en la celda del encabezado de la columna Importe y haga clic en Filtrar por color y el color especificado por el que contará sucesivamente. Ver captura de pantalla:

Después de filtrar, ambas fórmulas SUBTOTALES cuentan y suman todas las celdas de color filtradas en la columna Cantidad automáticamente. Ver captura de pantalla:

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

Un clic para contar, sumar y promediar celdas de colores en Excel

Con la excelente Contar por color característica de Kutools for Excel, puede contar, sumar y promediar celdas rápidamente por el color de relleno especificado o el color de fuente con solo un clic en Excel. Además, esta función también encontrará los valores máximo y mínimo de las celdas por el color de relleno o el color de la fuente. ¡Prueba gratuita de todas las funciones de 30 días!
recuento de anuncios por color 2

Kutools for Excel - Incluye más de 300 útiles herramientas para Excel. Prueba gratuita de todas las funciones durante 30 días, ¡no se requiere tarjeta de crédito! Consíguelo ahora

Contar o sumar celdas de colores mediante la función GET.CELL

En este método, crearemos un rango con nombre con la función GET.CELL, obtendremos el código de color de las celdas y luego contaremos o sumaremos por el código de color fácilmente en Excel. Haz lo siguiente:

1. Hacer clic Fórmulas > Definir nombre. Ver captura de pantalla:

2. En el cuadro de diálogo Nuevo nombre, haga lo que se muestra a continuación:
(1) Escriba un nombre en el cuadro Nombre;
(2) Ingrese la fórmula = OBTENER.CELDA (38, Hoja4! $ E2) en el cuadro Se refiere a (nota: en la fórmula, 38 significa devolver el código de celda, y Hoja4! $ E2 es la primera celda de la columna Cantidad, excepto el encabezado de la columna, que debe cambiar según los datos de su tabla).
(3) Haga clic en el OK

3. Ahora agregue una nueva columna Color directamente a la tabla original. Luego escriba la fórmula = NumColor y arrastre el controlador de Autocompletar para aplicar la fórmula a otras celdas en la columna Color. Ver captura de pantalla:
Nota:: En la fórmula, NúmColor es el rango con nombre que especificamos en los primeros 2 pasos. Debe cambiarlo al nombre especificado que estableció.

Ahora el código de color de cada celda en la columna Cantidad regresa en la Columna de color. Ver captura de pantalla:

4. Copie y enumere el color de relleno en un rango en blanco en la hoja de trabajo activa, y escriba fórmulas junto a él como se muestra a continuación:
A. Para contar las celdas por color, ingrese la fórmula = CONTAR.SI ($ F $ 2: $ F $ 20, NumColor);
B. Para sumar celdas por color, ingrese la fórmula = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Nota:: En ambas fórmulas, $ F $ 2: $ F $ 20 es la columna Color, NúmColor es el rango con nombre especificado, $ E $ 2: $ E $ 20 es la Columna de cantidad y puede cambiarla cuando lo necesite.

Ahora verá que las celdas en la columna Cantidad se cuentan y suman por sus colores de relleno.


Cuente y sume celdas según el color de relleno específico con la función definida por el usuario

Suponiendo que las celdas coloreadas se dispersen en un rango como se muestra en la captura de pantalla siguiente, ambos métodos anteriores no pueden contar ni sumar las celdas coloreadas. Aquí, este método introducirá un VBA para resolver el problema.

1. Mantenga pulsado el ALT + F11 llaves, y abre el Microsoft Visual Basic para aplicaciones ventana.

2. Hacer clic recuadro > Móduloy pegue el siguiente código en la ventana del módulo.

VBA: cuenta y suma celdas según el color 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. Luego guarde el código y aplique la siguiente fórmula:
A. Cuente las celdas coloreadas: = función de color (A, B: C, FALSO)
B. Suma las celdas coloreadas: = función de color (A, B: C, VERDADERO)

Nota: En las fórmulas anteriores, A es la celda con el color de fondo particular que desea calcular el recuento y la suma, y ANTES DE CRISTO es el rango de celdas donde desea calcular el recuento y la suma.

4. Tome la siguiente captura de pantalla, por ejemplo, ingrese la fórmula= función de color (A1, A1: D11, FALSO) para contar las celdas amarillas. Y usa la fórmula = función de color (A1, A1: D11, TRUE) para sumar las celdas amarillas. Ver captura de pantalla:

5. Si desea contar y sumar otras celdas de colores, repita el paso 4. Luego obtendrá los siguientes resultados:


Contar y sumar celdas según el color de relleno específico con las funciones de Kutools

Kutools for Excel también admite algunas funciones útiles para ayudar a los usuarios de Excel a realizar cálculos especiales, dice contar por color de fondo de celda, suma por color de fuente, etc.

Kutools for Excel - Incluye más de 300 útiles herramientas para Excel. Prueba gratuita de todas las funciones durante 30 días, ¡no se requiere tarjeta de crédito! Prueba gratis ahora!

1. Seleccione la celda en blanco donde coloca los resultados del conteo y haga clic en Kutools > Funciones de Kutools > Estadística y Matemáticas > CONTAR POR COLOR DE LA CELDA. Ver captura de pantalla:

2. En el cuadro de diálogo Argumentos de función, especifique el rango en el que contará las celdas de color en el Referencia cuadro, elija la celda que se llena con el color de fondo especificado en el Color_index_nr caja, y haga clic en OK botón. Ver captura de pantalla:

Notas
(1) También puede escribir la función Kutools especificada = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2)  en la celda en blanco o en la barra de fórmulas directamente para obtener los resultados del recuento;
(2) Click Kutools > Funciones de Kutools > Estadística y Matemáticas > SUMBYCELLCOLOR o tipo = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) en la celda en blanco directamente para sumar celdas según el color de fondo especificado.
Aplique la CONTAR POR COLOR DE LA CELDA y SUMBYCELLCOLOR funciones para cada color de fondo por separado, y obtendrá los resultados como se muestra a continuación:

Funciones de Kutools contienen una serie de funciones integradas para ayudar a los usuarios de Excel a calcular fácilmente, incluyendo Recuento / suma / promedio de celdas visibles, Contar / Sumar por color de celda, Contar / Sumar por color de fuente, Contar personajes, Contar por fuente en negrita, etc. ¡Tenga una prueba gratis!


Contar y sumar celdas según el color de relleno específico con Kutools para Excel

Con la función definida por el usuario anterior, debe ingresar la fórmula uno por uno, si hay muchos colores diferentes, este método será tedioso y llevará mucho tiempo. Pero si tienes Kutools for Excel, Contar por color utilidad, puede generar rápidamente un informe de las celdas coloreadas. No solo puede contar y sumar las celdas coloreadas, sino que también puede obtener los valores promedio, máximo y mínimo del rango de colores.

Kutools for Excel - Incluye más de 300 útiles herramientas para Excel. Prueba gratuita de todas las funciones durante 30 días, ¡no se requiere tarjeta de crédito! Prueba gratis ahora!

1. Seleccione el rango que desea usar y haga clic en Kutools Más > Contar por color, ver captura de pantalla:

2. Y en la Contar por color cuadro de diálogo, haga lo que se muestra a continuación:
(1) Seleccionar Formato estándar from the Método de color la lista desplegable;
(2) Seleccionar Antecedentes from the Tipo de recuento la lista desplegable.
(3) Haga clic en el botón Generar informe.

Nota:: Para contar y sumar celdas coloreadas por color de formato condicional específico, seleccione Formato condicional from the Método de color lista desplegable en el cuadro de diálogo anterior, o seleccione Formato estándar y condicional de la lista desplegable para contar todas las celdas rellenas con el color especificado.

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

La Contar por color La función calcula (recuento, suma, promedio, máximo, etc.) celdas por color de fondo o color de fuente. ¡Tenga una prueba gratis!


Artículo relacionado:


Demostración: cuente y sume celdas según el fondo, color de formato condicional:


Kutools for Excel incluye más de 300 útiles herramientas para Excel, gratis para probar sin limitación en 30 días. Descarga y prueba gratis ahora!

Las mejores herramientas de productividad de oficina

Kutools para Excel resuelve la mayoría de sus problemas y aumenta su productividad en un 80%

  • Reutilizar: Inserte rápidamente fórmulas complejas, gráficos y cualquier cosa que hayas usado antes; Cifrar celdas con contraseña; Crear lista de distribución y enviar correos electrónicos ...
  • Barra de súper fórmula (edite fácilmente varias líneas de texto y fórmulas); Diseño de lectura (leer y editar fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas sin perder datos; Contenido de celdas divididas; Combinar filas / columnas duplicadas... Prevenir celdas duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Varias celdas sin cambiar la referencia de la fórmula; Crear referencias automáticamente a varias hojas; Insertar viñetas, Casillas de verificación y más ...
  • Extraer texto, Agregar texto, Eliminar por posición, Quitar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtros a otras hojas); Orden avanzado por mes / semana / día, frecuencia y más; Filtro especial en negrita, cursiva ...
  • Combinar libros y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes de xls, xlsx y PDF...
  • Más de 300 potentes funciones. Compatible con Office / Excel 2007-2019 y 365. Compatible con todos los idiomas. Fácil implementación en su empresa u organización. Características completas Prueba gratuita de 30 días. Garantía de devolución de dinero de 60 días.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
officetab parte inferior
Comentarios (234)
Aún no hay calificaciones. ¡Sé el primero en calificar!
Este comentario fue minimizado por el moderador en el sitio
Herramienta muy útil, muchas gracias
Este comentario fue minimizado por el moderador en el sitio
Después de insertar la función, cuando cambiamos el valor, la suma no se actualiza automáticamente, cualquier sugerencia
Este comentario fue minimizado por el moderador en el sitio
Alt-Ctrl-F9 volverá a calcular
Este comentario fue minimizado por el moderador en el sitio
Puede agregar y actualizar el botón si alguien más está usando el archivo y no conocen el Alt-Ctrl-F9. simplemente agregue esta línea de código a la macro "Application.CalculateFull". es lo mismo que Alt-Ctrl-F9 recién programado en un botón.
Este comentario fue minimizado por el moderador en el sitio
Agregue una segunda línea directamente debajo de la primera que dice Application.volitile hace que se recalcule después de que se actualice algo
Este comentario fue minimizado por el moderador en el sitio
Traté de poner Aplicación.Volatile debajo de la primera línea y no pasa nada :(
Este comentario fue minimizado por el moderador en el sitio
[quote] Agregue una segunda línea directamente debajo de la primera que dice Application.volitile hace que se recalcule después de que se actualice algopor 12345678998765432154[/quote] Application.Volatile es la función correcta, no Application.volitile
Este comentario fue minimizado por el moderador en el sitio
No veo ninguna línea que diga application.volatile. ¿Donde lo encuentro?
Este comentario fue minimizado por el moderador en el sitio
Hice doble clic en la celda con la fórmula ColorFunction y luego presioné Enter. Se actualizó.
Este comentario fue minimizado por el moderador en el sitio
Muy útil. Gracias
Este comentario fue minimizado por el moderador en el sitio
También probé la fórmula. Funciona en primera fila y no con segunda y tercera. ????
Este comentario fue minimizado por el moderador en el sitio
Estoy de acuerdo con Jarod. Las primeras dos líneas de mi función dicen: Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Application.Volatile El problema es que solo Alt-Ctrl-F9 volverá a calcular. Estamos buscando una solución más automatizada. ¿Ideas?
Este comentario fue minimizado por el moderador en el sitio
Pruébelo así: 1. Asegúrese de tener la función guardada como se describe 2. Permítame usar un ejemplo * Tengo cientos de filas de datos * Uso las columnas A a AB con más datos * siempre que tengo un problema con mi data, lo resalto en amarillo * Uso esta fórmula genial para 'contar' el número de resaltes en cada fila 3. Cómo contar el número de mis resaltes por fila a. figura el rango de celdasque podría tener aspectos destacados que desea contar (o sumar) * para mí, quiero CONTAR la cantidad de mis aspectos destacados marcados en cada fila (mi rango) b. elija una celda donde informará el conteo (o la suma) * para mí, lo coloqué en el extremo derecho de mis datos... en la columna AE c. inserte la siguiente fórmula en la celda que eligió en el elemento b (arriba) = función de color (AE3, A3: AB3, FALSO) * para mí, coloqué esta fórmula en la celda AE3 (el final de mi fila) Y Y resalté lo mismo celda de fórmula Amarilla d. Luego copié esta fórmula para todas mis filas de datos 4. Observé a. no se hicieron conteos. (malo) 5 ASUNTO CLAVE AQUÍ: a. Presioné CTL+ ALT+F9. PRESTO!!! funciona.! Espero que esta broma sea de alguna ayuda. Saludos haz esto: ABCD 1 2 3 4
Este comentario fue minimizado por el moderador en el sitio
¡¡¡Muy bien, muchas gracias!!!
Este comentario fue minimizado por el moderador en el sitio
Artículo muy útil, muchas gracias.
Este comentario fue minimizado por el moderador en el sitio
¡¡¡Maravilloso!!! ¡¡¡Muchas gracias!!!
Este comentario fue minimizado por el moderador en el sitio
Increíble, muchas gracias
Este comentario fue minimizado por el moderador en el sitio
Esta es una gran herramienta, gracias por compartir! Tengo una pregunta: noté que la fórmula macro no se actualiza cuando cambias el color de una celda de un color a otro. ¿Hay alguna manera de hacer que se actualice automáticamente cuando cambien los colores de las celdas? Si hago doble clic en la celda con la fórmula de suma y presiono Intro, se actualiza, pero tengo una hoja con muchas de estas celdas y no quiero tener que actualizarla manualmente cada vez.
Este comentario fue minimizado por el moderador en el sitio
Gracias por su respuesta, lo siento, en la situación actual, el código VBA no puede resolver la pregunta que señaló, debe actualizarlos manualmente.
Este comentario fue minimizado por el moderador en el sitio
También descubrí que usar el pintor de formato para actualizar el color de la celda hará que las fórmulas funcionen como se esperaba. Todavía no es perfecto.
Este comentario fue minimizado por el moderador en el sitio
hola, he probado. y la hoja de trabajo solo necesita un "Enter" sin importar en qué celda. Entonces, después de colorear su celda, simplemente escriba una nota en las celdas coloreadas y "ingresar"
Este comentario fue minimizado por el moderador en el sitio
intente CTL+ALT+f9 actualizará todas las celdas con fórmulas de adición a la vez. No del todo automático, pero al menos mejor que tener que hacer clic en cada celda con fórmula de suma individualmente.
Este comentario fue minimizado por el moderador en el sitio
esto no me hizo nada :(
Este comentario fue minimizado por el moderador en el sitio
hola me sale error error de cumplimiento, se acerca un error ambiguo.
Este comentario fue minimizado por el moderador en el sitio
Apliqué la fórmula pero la celda decía ¿NOMBRES? lo que necesito arreglar
Este comentario fue minimizado por el moderador en el sitio
Hola RAMON, tal vez no copiaste el código anterior en el Módulo. Primero debe copiar el código en el Módulo y guardarlo, luego aplicar las fórmulas. Puedes probarlo. Si no te ayuda, por favor házmelo saber.
Este comentario fue minimizado por el moderador en el sitio
Hola, funcionó la primera vez, pero ahora cuando tengo que agregar una celda a la suma... Probé introduciendo nuevamente el código, guardando, escribiendo la fórmula nuevamente... dice "¿nombre?"
Este comentario fue minimizado por el moderador en el sitio
Tengo el mismo problema: funcionó muy bien al principio, pero obtuve el error NOMBRE después de agregar otra celda. Intenté eliminar el módulo y agregarlo nuevamente, volver a ingresar a la función ... sin alegría.
Este comentario fue minimizado por el moderador en el sitio
Es posible que deba volver a habilitar Marcos. Debería parpadear en la parte superior de la pantalla según la versión de Excel. :)
Este comentario fue minimizado por el moderador en el sitio
Skyyang, si te envío un documento por correo electrónico, ¿puedes ayudarme? Miguel
Este comentario fue minimizado por el moderador en el sitio
¡Muchas gracias! Esto es genial, gracias por la ayuda!
Este comentario fue minimizado por el moderador en el sitio
Muchas gracias, he estado buscando todo el día un resultado exitoso y ahora tenemos uno :)
Este comentario fue minimizado por el moderador en el sitio
Gracias. Funcionó para mí como se describe. Sin embargo, después de volver a abrir el archivo la próxima vez, todas las celdas con esta fórmula mostraban un error. Tuve que volver a copiar la codificación de nuevo. ¿Hay algo que este olvidando? Tendré que compartir el archivo con otras personas y no podrán "arreglar" el problema. Gracias Ker.
Este comentario fue minimizado por el moderador en el sitio
Debe guardarlo como libro de macros de Excel
Este comentario fue minimizado por el moderador en el sitio
bien hecho ... pero ... ¿cómo funciona en los colores de celda de formato condicional?
Este comentario fue minimizado por el moderador en el sitio
Esto es genial, gracias!
Este comentario fue minimizado por el moderador en el sitio
funcionó bien hecho!!!
Este comentario fue minimizado por el moderador en el sitio
Estimado señor, de hecho, es un código maravilloso, pero me encontré con el problema de que seguirá calculando la celda incluso cuando cambiemos el color de la celda.
Este comentario fue minimizado por el moderador en el sitio
Gracias señor, este es un trabajo maravilloso y me ayuda mucho.
Este comentario fue minimizado por el moderador en el sitio
Tengo un archivo de Excel con muchas celdas de colores. ¿Hay alguna forma de convertir estos colores en datos? Entonces, ¿una celda roja tendrá "rojo" como datos, una celda de color azul tendrá "azul" y así sucesivamente? Jaime
Este comentario fue minimizado por el moderador en el sitio
Artículo oportuno y adecuado como dar en el blanco que estoy buscando :) gracias
Este comentario fue minimizado por el moderador en el sitio
Parece que no puedo hacer que esto funcione. ¿Solo funciona para celdas que han sido coloreadas manualmente? Necesito contar las celdas que se han coloreado a través del formato condicional, pero no está jugando a la pelota en este momento.
Este comentario fue minimizado por el moderador en el sitio
Tengo el mismo problema que Ian, estoy tratando de sumar números según las reglas de color establecidas por el formato condicional, pero esto no parece detectarlo. ¿Alguna sugerencia?
Este comentario fue minimizado por el moderador en el sitio
Intentaremos mejorarlo en las próximas versiones. :-)
Este comentario fue minimizado por el moderador en el sitio
Lancé un script de Powershell que actúa como una especie de solución alternativa: " #setup Excel $excelApp = New-Object -comobject Excel.Application $excelApp.Quit() $excelApp.Visible = $True $workbook = $excelApp.Workbooks .Open("H:\Desktop\test.xlsx")#CAMBIE ESTO A SU DIRECCIÓN DE ARCHIVO DE EXCEL. $hoja de trabajo = $libro de trabajo.Hojas de trabajo.Item("Hoja1")#CAMBIE ESTO SI SU HOJA NO SE LLAMA "Hoja1" #variables estáticas $fila = 1 $columna = 1#CAMBIA ESTA VARIABLE A CUALQUIER COLUMNA QUE ESTÉS BUSCANDO $totalRow = $worksheet.UsedRange.Rows.Count do{ $currentCell = $worksheet.cells.item($row, $column ) if($currentCell.text -eq "BUSCAR_ESTO") { $worksheet.cells.item($row,$column).Interior.ColorIndex = 44#CAMBIE ESTE NÚMERO PARA CAMBIAR EL COLOR DE LA NUEVA CELDA "$fila, $columna = EN BLANCO. Coloreando" } $fila++ $fila } while($fila -lt $totalRow) "Script completado". "GUARDAR..." $excelApp.Save $excelApp.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject ($excelApp) " Esta secuencia de comandos buscará a través de su hoja de cálculo y si encuentra una celda que contiene "BUSCAR_PARA_ESTO" (probablemente debería cambiar esa parte en la secuencia de comandos), entonces cambiará esa celda a cualquier número de color que esté en la secuencia de comandos (actualmente establecido en 44, un color naranja/tostado de aspecto extraño). Una vez que la secuencia de comandos haya terminado, intentará decirle a Excel que desea guardar, pero Excel no es un programa muy confiable, por lo que le pedirá que haga clic en guardar. Una vez que hayas hecho eso, se cerrará. Luego, vuelva a abrir el archivo y haga todo lo que se enumera en esta página, asegurándose de que el color que mi script está buscando sea el mismo color que el script que se encuentra en esta página. ¡Espero que esto ayude!
Este comentario fue minimizado por el moderador en el sitio
¡Leyenda! gracias funciono de maravilla
No hay comentarios publicados aquí todavía
Cargar más
Deje sus comentarios
Publicar como invitado
×
Califica esta publicación:
0  Personajes.
Ubicaciones sugeridas