Como contar las celdas de colores en Excel

 

No sería estupendo si hubiera una función que pudiera contar las celdas de colores en Excel?

Desafortunadamente, no hay ninguna función incorporada para hacerlo.

PERUT ..

Se puede hacer fácilmente.

Como se cuentan las celdas de colores en Excel

En este tutorial, te mostraré tres maneras de contar las celdas de colores en Excel (con y sin VBA):

  1. Utilizando la función SUBTOTAL y Filter
  2. Utilizando la función GET.CELL
  3. Utilización de una función personalizada creada con VBA

# 1 Cuenta las celdas de colores mediante el filtro y SUBTOTAL

Para contar las celdas de colores en Excel, siga los dos pasos siguientes:

  • Filtrar las celdas de colores
  • Utilice la función SUBTOTAL para contar las celdas de colores que sean visibles (después de filtrar).

Supongamos que tiene un conjunto de datos como se muestra a continuación:

Contad las celdas de colores a Excel: conjunto de datos

Hay dos colores de fondo que se utilizan en este conjunto de datos (verde y naranja).

Estos son los pasos para contar las celdas de colores a Excel:

  1. A cualquier celda que hay debajo del conjunto de datos, utilice la siguiente fórmula: = SUBTOTAL (102, E1: E20)
    Uso de la función Subtotal para contar las celdas de colores en Excel
  2. Seleccione las cabeceras.
  3. Vaya a Datos -> Ordena y filtra -> Filtro. Esto aplicará un filtro a todas las cabeceras.
  4. Haga clic en cualquiera de los desplegables del filtro.
  5. Vaya a “Filtrar por color” y seleccione el color. Al conjunto de datos anterior, ya que se utilizan dos colores para resaltar las celdas, el filtro muestra dos colores para filtrar estas celdas.Uso del filtro para contar las celdas de colores en Excel

Luego que filtre las celdas, notará que el valor de la función SUBTOTAL cambia y sólo devuelve el número de celdas visibles después del filtrado.Cuenta de celdas con un color de fondo: subtotal recuento de celdas filtrado

¿Cómo funciona?

La función SUBTOTAL utiliza 102 como primer argumento, que se utiliza para contar las celdas visibles (las filas ocultas no se cuentan) en el intervalo especificado.

Si no se filtran los datos, vuelve 19, pero si se filtra, sólo devuelve el recuento de las celdas visibles.

 

# 2 Cuenta las celdas de colores mediante la función GET.CELL

GET.CELL es una función Macro4 que se ha mantenido por motivos de compatibilidad.

No funciona si se utiliza como funciones habituales en la hoja de trabajo.

Sin embargo, funciona en intervalos llamados Excel.

Véase también: Más información sobre la función GET.CELL.

Estos son los tres pasos para utilizar GET.CELL para contar las celdas de colores a Excel:

  • Cree un rango nombrado mediante la función GET.CELL
  • Utilice el intervalo llamado para obtener el código de color de una columna
  • Uso del número de color para contar el número de celdas de colores (por color)

Vamos a bucear profundamente y veremos qué hacer en cada uno de los tres pasos mencionados.

Creación de un rango con nombre

  • Vaya a Fórmulas -> Define el nombre.Contad las celdas de colores a Excel: nombre definido
  • En el cuadro de diálogo Nombre nuevo, escriba:
    • Nombre: GetColor
    • Alcance: libro de trabajo
    • Se refiere a: = GET.CELL (38, Hoja1! $ A2)
      En la fórmula anterior, he utilizado Sheet1! $ A2 como segundo argumento. Debe utilizar la referencia de la columna donde tiene las celdas con el color de fondo. Contad las celdas de colores a Excel: GetColor

Obtener el código de colores para cada celda

En la celda adyacente a los datos, utilice 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, de manera que todas las celdas con el mismo color de fondo obtienen el mismo número.Contad las celdas de colores a Excel: código de colores

Contad las celdas de colores mediante el código de colores

Si sigue el proceso anterior, tendríais una columna con números correspondientes al color de fondo.

Para obtener el recuento de un color específico:

  • En algún lugar de debajo del conjunto de datos, eche el mismo color de fondo a una celda que desee contar. Asegúrese de que lo haga en la misma columna que utilizó para crear el intervalo con nombre. Por ejemplo, he utilizado la columna A y, por tanto, sólo utilizaré las celdas de la columna ‘A’.
  • En la celda adyacente, utilice la siguiente fórmula:

= COUNTIF ($ F $ 2: $ F $ 20, GetColor)

Esta fórmula proporciona el recuento de todas las celdas con el color de fondo especificado.Cuenta de celdas de colores a Excel: recuento de colores sin VBA

¿Cómo funciona?

La función COUNTIF utiliza el intervalo llamado (GetColor) como criterio. El intervalo llamado de la fórmula hace referencia a la celda adyacente de la izquierda (en la columna A) y devuelve el código de color de esta celda. Por lo tanto, este número de código de color es el criterio.

La función COUNTIF utiliza el intervalo ($ F $ 2: $ F $ 18) que contiene los números de código de color de todas las celdas y devuelve el recuento en función del número de criterios.

 

# 3 Cuenta de colores mediante VBA (mediante la creación de una función personalizada)

A los dos métodos anteriores, habéis aprendido a contar las celdas de colores sin utilizar VBA.

Pero, si está bien con el uso de VBA, este es el más fácil de los tres métodos.

Con VBA, crearíamos una función personalizada que funcionaría como una función COUNTIF y devolvería el recuento de celdas con el color de fondo específico.

He aquí el código:

'Code created by Sumit Bansal from https://trumpexcel.com
Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function

Para crear esta función personalizada:

  • Con su libro de trabajo activo, pulse Alt + F11 (o haga clic derecho en la pestaña de la hoja de trabajo y seleccione Ver el código). Esto abriría el editor VB.
  • En el panel izquierdo, debajo del libro de trabajo donde está trabajando, haga clic con el botón derecho en cualquier de las hojas de trabajo y seleccione Insertar -> Módulo. Esto insertaría un nuevo módulo. Copia y pega el código en la ventana del código del módulo.Como se cuentan las celdas de colores a Excel: inserte el módulo
  • Haga doble clic en el nombre del módulo (por defecto el nombre del módulo a Modulo1) y pegue el código en la ventana del código.Función personalizada de código VBA para contar las celdas de colores en Excel
  • Cierre el editor VB.
  • Esto es! Ahora tiene una función personalizada en la hoja de trabajo llamada GetColorCount.

Para utilizar esta función, simplemente utilícela como cualquier función excel normal.

Sintaxis: = GetColorCount (CountRange, CountColor)

  • CountRange: el intervalo en el que desea contar las celdas con el color de fondo especificado.
  • CountColor: el color para el que desea contar las celdas.

Para utilizar esta fórmula, utilice el mismo color de fondo (que desea contar) en una celda y utilice la fórmula. El argumento CountColor sería la misma celda donde introduzca la fórmula (como se muestra a continuación):

Contad las celdas de colores a Excel: fórmula personalizada

Nota: Como hay un código en el libro, guárdelo con una extensión .xls o .xlsm.

 

Conoce alguna otra manera de contar celdas de colores en Excel?

Si es así, compártelo conmigo dejando un comentario.

 

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *