Ver video - Cómo contar celdas de colores en Excel
¿No sería genial si hubiera una función que pudiera contar celdas de colores en Excel?
Lamentablemente, no hay ninguna función incorporada para hacer esto.
PERO…
Se puede hacer fácilmente.
Cómo contar celdas de colores en Excel
En este tutorial, le mostraré tres formas de contar celdas de colores en Excel (con y sin VBA):
- Uso de la función Filtro y SUBTOTAL
- Usando la función GET.CELL
- Usando una función personalizada creada usando VBA
# 1 Cuente las celdas coloreadas usando filtro y SUBTOTAL
Para contar las celdas coloreadas en Excel, debe seguir los dos pasos siguientes:
- Filtrar celdas coloreadas
- Utilice la función SUBTOTAL para contar las celdas coloreadas que son visibles (después de filtrar).
Suponga que tiene un conjunto de datos como se muestra a continuación:
En este conjunto de datos se utilizan dos colores de fondo (verde y naranja).
Estos son los pasos para contar las celdas de colores en Excel:
- En cualquier celda debajo del conjunto de datos, use la siguiente fórmula: = SUBTOTAL (102, E1: E20)
- Seleccione los encabezados.
- Vaya a Datos -> Ordenar y filtrar -> Filtro. Esto aplicará un filtro a todos los encabezados.
- Haga clic en cualquiera de los menús desplegables de filtros.
- Vaya a "Filtrar por color" y seleccione el color. En el conjunto de datos anterior, dado que se utilizan dos colores para resaltar las celdas, el filtro muestra dos colores para filtrar estas celdas.
Tan pronto como filtre las celdas, notará que el valor en la función SUBTOTAL cambia y devuelve solo el número de celdas que son visibles después del filtrado.
¿Como funciona esto?
La función SUBTOTAL usa 102 como primer argumento, que se usa para contar las celdas visibles (las filas ocultas no se cuentan) en el rango especificado.
Si los datos no se filtran, devuelve 19, pero si se filtran, solo devuelve el recuento de las celdas visibles.
Pruébelo usted mismo … Descargue el archivo de ejemplo
# 2 Contar celdas coloreadas usando la función GET.CELL
GET.CELL es una función de Macro4 que se ha mantenido por razones de compatibilidad.
No funciona si se usa como funciones regulares en la hoja de trabajo.
Sin embargo, funciona en rangos con nombre de Excel.
Ver también: Conozca más sobre la función GET.CELL.
Estos son los tres pasos para usar GET.CELL para contar celdas coloreadas en Excel:
- Cree un rango con nombre usando la función GET.CELL
- Utilice el rango con nombre para obtener el código de color en una columna
- Uso del número de color para contar el número de celdas coloreadas (por color)
Profundicemos y veamos qué hacer en cada uno de los tres pasos mencionados.
Crear un rango con nombre
- Vaya a Fórmulas -> Definir nombre.
- En el cuadro de diálogo Nuevo nombre, ingrese:
- Nombre: GetColor
- Alcance: Libro de trabajo
- Se refiere a: = GET.CELL (38, Sheet1! $ A2)
En la fórmula anterior, he usado Hoja1! $ A2 como segundo argumento. Debe usar la referencia de la columna donde tiene las celdas con el color de fondo.
Obtener el código de color para cada celda
En la celda adyacente a los datos, use la fórmula = GetColor
Esta fórmula devolvería 0 si NO hay color de fondo en una celda y devolvería un número específico si hay un color de fondo.
Este número es específico de un color, por lo que todas las celdas con el mismo color de fondo obtienen el mismo número.
Cuente las celdas coloreadas usando el código de color
Si sigue el proceso anterior, tendrá una columna con números correspondientes al color de fondo.
Para obtener el recuento de un color específico:
- En algún lugar debajo del conjunto de datos, asigne el mismo color de fondo a una celda que desee contar. Asegúrese de hacer esto en la misma columna que utilizó para crear el rango con nombre. Por ejemplo, usé la Columna A y, por lo tanto, solo usaré las celdas de la columna "A".
- En la celda adyacente, use la siguiente fórmula:
= CONTAR.SI ($ F $ 2: $ F $ 20, GetColor)
Esta fórmula le dará el recuento de todas las celdas con el color de fondo especificado.
¿Como funciona?
La función CONTAR.SI usa el rango con nombre (GetColor) como criterio. El rango con nombre en la fórmula se refiere a la celda adyacente a la izquierda (en la columna A) y devuelve el código de color para esa celda. Por lo tanto, este número de código de color es el criterio.
La función CONTAR.SI usa el rango ($ F $ 2: $ F $ 18) que contiene los números de código de color de todas las celdas y devuelve el recuento según el número de criterio.
Pruébelo usted mismo … Descargue el archivo de ejemplo
# 3 Cuente coloreado usando VBA (creando una función personalizada)
En los dos métodos anteriores, aprendió a contar celdas coloreadas sin usar VBA.
Pero, si está de acuerdo con el uso de VBA, este es el más fácil de los tres métodos.
Usando VBA, crearíamos una función personalizada, que funcionaría como una función CONTAR.SI y devolvería el recuento de celdas con el color de fondo específico.
Aquí está el código:
'Código creado por Sumit Bansal de https://trumpexcel.com Función GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Establecer rCell = CountRange para cada rCell en CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount Fin Función
Para crear esta función personalizada:
- Con su libro de trabajo activo, presione Alt + F11 (o haga clic con el botón derecho en la pestaña de la hoja de trabajo y seleccione Ver código). Esto abriría el Editor de VB.
- En el panel izquierdo, debajo del libro de trabajo en el que está trabajando, haga clic con el botón derecho en cualquiera de las hojas de trabajo y seleccione Insertar -> Módulo. Esto insertaría un nuevo módulo. Copie y pegue el código en la ventana de código del módulo.
- Haga doble clic en el nombre del módulo (por defecto el nombre del módulo en Module1) y pegue el código en la ventana de código.
- Cierre el editor de VB.
- ¡Eso es! Ahora tiene una función personalizada en la hoja de trabajo llamada GetColorCount.
Para usar esta función, simplemente úsela como cualquier función normal de Excel.
Sintaxis: = GetColorCount (CountRange, CountColor)
- CountRange: el rango en el que desea contar las celdas con el color de fondo especificado.
- CountColor: el color para el que desea contar las celdas.
Para usar esta fórmula, use el mismo color de fondo (que desea contar) en una celda y use la fórmula. El argumento CountColor sería la misma celda donde está ingresando la fórmula (como se muestra a continuación):
Nota: Dado que hay un código en el libro de trabajo, guárdelo con una extensión .xls o .xlsm.
Pruébelo usted mismo … Descargue el archivo de ejemplo
¿Conoce alguna otra forma de contar celdas de colores en Excel?
Si es así, compártelo conmigo dejando un comentario.
