6 formas de contar celdas de colores en Microsoft Excel [Guía ilustrada]

Probablemente haya usado códigos de colores en sus datos de Excel o los haya visto en un libro de trabajo que tenía que usar.

¡Es una forma popular de visualizar sus datos!

Si bien las celdas de colores son una excelente manera de resaltar datos para captar rápidamente la atención de alguien, no son una excelente manera de almacenar datos.

Desafortunadamente, muchos usuarios colorearán una celda para indicar algún valor en lugar de crear otro punto de datos con el valor.

Por ejemplo, podrían colorear una celda de verde para indicar que un elemento está aprobado en lugar de crear otro punto de datos con el texto Aprobado .

Esto causa muchos problemas cuando realmente necesita averiguar cuántos elementos se aprobaron. Excel no ofrece una forma integrada de contar celdas coloreadas.

En esta publicación, le mostraré 6 formas de encontrar y contar las celdas coloreadas en sus datos.

Use el comando Buscar y seleccionar para contar celdas coloreadas

Excel tiene una gran característica que le permite encontrar celdas según el formato. ¡Esto también incluye las celdas coloreadas!

Puede encontrar todas las celdas de un determinado color y luego contarlas.

Vaya a la pestaña Inicio ➜ haga clic en el comando Buscar y seleccionar ➜ luego elija Buscar entre las opciones.

También hay un gran atajo de teclado para esto. Presione Ctrl + F para abrir el menú Buscar y reemplazar .

Haga clic en la pequeña flecha hacia abajo en el botón Formato y seleccione Elegir formato de celda .

Al hacer clic en la parte principal del botón Formato , se abrirá el menú Buscar formato, donde puede seleccionar cualquier combinación de formato para buscar.

Esto es perfecto si sabe exactamente qué color está buscando, pero más a menudo será mejor que establezca el formato con el ejemplo. ¡El formateo puede ser sutilmente diferente y esto puede hacer que no encuentre los datos correctos!

Cuando hace clic en la flecha pequeña dentro del botón Formato , revelará más opciones, incluida la capacidad de establecer el formato seleccionando una celda.

Una vez que haya seleccionado el formato, haga clic en el botón Buscar todo.

La parte inferior del cuadro de diálogo Buscar y reemplazar mostrará todas las celdas que se encontraron que coinciden con el formato y en la parte inferior izquierda encontrará el recuento.

Presione Ctrl + A para seleccionar todas las celdas y luego presione el botón Cerrar y luego puede cambiar el color de todas estas celdas o cambiar cualquier otro formato.

Si solo desea devolver celdas en una columna o rango determinado, esto es posible. Seleccione el rango en la hoja antes de presionar el botón Buscar todo para limitar la búsqueda a la selección.

ventajas

• Fácil de usar.
• Puede usar esto para buscar otros tipos de formato y no solo color de relleno.
• Puede usar esto para buscar un rango seleccionado, la hoja completa o el libro de trabajo completo.

Contras

• Esta solución no es dinámica y deberá repetirse cada vez que desee obtener el recuento.

Use filtros y la función de subtotal para contar celdas coloreadas

Este método se basará en el hecho de que puede filtrar según el color de la celda.

Primer paso, deberá agregar filtros a sus datos.

Seleccione sus datos y vaya a la pestaña Datos , luego elija el comando Filtrar .

Esto agregará un ícono de ordenar y filtrar a cada encabezado de columna de sus datos y esto le permitirá filtrar sus datos de muchas maneras diferentes.

También hay un práctico atajo de teclado para agregar o eliminar filtros de sus datos. Seleccione sus datos y presione Ctrl + Shift + L en su teclado.

Otra opción para agregar filtros es convertir sus datos en una tabla de Excel. Escribí una publicación sobre las tablas de Excel y las excelentes características que vienen con ellas.

Puede convertir sus datos en una tabla con cualquiera de estos dos métodos.

• Seleccione una celda dentro de sus datos ➜ vaya a la pestaña Insertar ➜ haga clic en el comando Tabla.
• Seleccione una celda dentro de sus datos ➜ presione Ctrl + T en su teclado.

Su tabla debe venir con filtros por defecto. De lo contrario, vaya a la pestaña Tabla y marque la opción Botón de filtro en la sección Opciones de estilo de tabla .

= SUBTOTAL ( 3, Orders[Order ID] )

Ahora puede agregar la fórmula SUBTOTAL anterior para contar las celdas no vacías donde ID de pedido es la columna que contiene las celdas coloreadas que le gustaría contar.

El primer argumento de la función SUBTOTAL le dice que devuelva un conteo mientras que el segundo argumento le dice qué contar.

El truco especial aquí es que la función SUBTOTAL solo contará las celdas visibles, por lo que actualizará el recuento en función de los datos que se filtran.

¡Esto significa que puede filtrar en las celdas coloreadas y obtendrá un recuento de esas celdas coloreadas!

Ahora puede filtrar sus datos por color.

1. Haga clic en el conmutador de clasificación y filtrado de la columna que contiene las celdas coloreadas.
2. Seleccione Filtrar por color en las opciones del menú.
3. Elija el color que desea filtrar.

Ahora el resultado SUBTOTAL se actualizará y podrá encontrar rápidamente el recuento de las celdas coloreadas.

Si ajusta los colores, agregue o elimine datos en la tabla. Deberá volver a aplicar los filtros ya que no se actualizan dinámicamente.

Vaya a la pestaña Datos y haga clic en el botón Volver a aplicar en la sección Ordenar y filtrar .

ventajas

• Fácil de usar.

Contras

• Requiere filtrado manual de datos.
• Los filtros no se actualizan y deberá volver a aplicarlos cuando cambie sus datos.
• Dado que el recuento se basa en el filtrado, el resultado puede ser diferente para cada usuario al colaborar en el libro de trabajo.

Use la función GET.CELL Macro4 para contar celdas coloreadas

Excel tiene una función para obtener el color de relleno de una celda, pero es una función heredada de Macro 4.

Estos son anteriores a VBA y eran el lenguaje de secuencias de comandos basado en fórmulas de Excel.

Si bien se consideran obsoletos, aún es posible usarlos dentro del administrador de nombres.

Hay una función GET.CELLS Macro4 que devolverá un código de color basado en el color de relleno de la celda.

Puede crear un rango con nombre relativo que use esto yendo a la pestaña Fórmulas y haciendo clic en Definir nombre .

Esto abrirá el menú Nuevo nombre y podrá definir la referencia.

Asigne a su nombre definido un Nombre como ColorCode . Así es como se referirá a él en el libro de trabajo.

= GET.CELL ( 38, Orders[@[Order ID]] )

Agregue la fórmula anterior en la sección Se refiere a. Para esta fórmula, sus datos deberán estar en una tabla llamada Pedidos con una columna llamada ID de pedido , pero puede cambiarlos para que se ajusten a sus datos.

Esta fórmula siempre hará referencia a la celda ID de pedido en la fila actual a la que hace referencia.

= GET.CELL ( 38, $B3 )

Si sus datos no están dentro de una tabla, entonces podría usar la fórmula anterior, donde B es la columna que contiene el color de relleno que desea contar. Esto utiliza una columna fija y una referencia de fila relativa para referirse siempre a la columna B de la fila actual.

= ColorCode

Con el nombre definido, ahora puede crear otra columna usando la fórmula anterior en sus datos para calcular el código de color para cada fila.

El resultado será un valor entero basado en el color de relleno de la celda en la columna ID de pedido .

= COUNTIFS ( Orders[ColorCode], B14 )

Ahora puede contar el número o las celdas coloreadas usando la fórmula anterior CONTAR.SI.

Esta fórmula contará las celdas en la columna ColorCode si tienen un código coincidente. En este ejemplo, cuenta los 10 valores que corresponden al color verde.

ventajas

• Puede calcular el color de relleno para cada fila de datos y se actualizará dinámicamente a medida que cambie los datos o el color de relleno de los datos.

Contras

• Este método utiliza las funciones heredadas de Macro4 y es posible que Microsoft no las siga admitiendo.
• Más difícil de implementar.
• Deberá guardar su libro de trabajo en el formato habilitado para macros xlsm.
• No puede mover la columna a la que se hace referencia si está utilizando la notación de columna dentro del rango con nombre.
• No puede cambiar el nombre de su columna si está usando la notación de tabla dentro del rango con nombre.
• Debe crear una columna adicional y usar una función COUNTIFS para obtener el recuento.

Use una función LAMBDA para contar celdas coloreadas

Esto usará la misma función GET.CELL Macro4 que el método anterior, pero puede crear una función LAMBDA personalizada para usarla dentro del libro de trabajo.

La función LAMBDA es una función especial que le permite crear funciones personalizadas a través del administrador de nombres.

Esta es una función nueva, por lo que generalmente no está disponible y debe estar en el programa Microsoft 365 Office Insiders al momento de escribir esta publicación.

Vaya a la pestaña Fórmulas y haga clic en Definir nombre para abrir el menú Nuevo nombre.

= LAMBDA ( cell, GET.CELL ( 38, cell ) )

Asigne al nombre definido un nombre como GETCOLORCODE y agregue la fórmula anterior en la sección Se refiere a .

Esto creará una nueva función GETCOLORCODE que puede usar dentro del libro de trabajo. Tomará un argumento llamado celda y devolverá el código de color de relleno de la celda.

= GETCOLORCODE ( [@[Order ID]] )

Ahora todo lo que tiene que hacer es crear una columna para calcular el código de color para cada fila usando la fórmula anterior.

= COUNTIFS ( Orders[ColorCode], B14 )

Nuevamente, puede contar el número o las celdas coloreadas usando la fórmula COUNTIFS anterior.

ventajas

• Puede crear una función que calcule el código de color para una celda determinada.
• Le permite hacer referencia directamente a una celda para obtener el código de color.

Contras

• Este método usa la función LAMBDA en Excel para M365 y actualmente no está disponible de forma general.
• Más difícil de implementar.
• Este método utiliza las funciones heredadas de Macro4 que pueden no ser compatibles en el futuro.
• Deberá guardar su libro de trabajo en el formato habilitado para macros xlsm.
• Requiere crear una columna adicional y usar una función COUNTIFS para obtener el conteo.

Use VBA para contar celdas coloreadas

Function COLORCOUNT(CountRange As Range, FillCell As Range)Dim FillColor As IntegerDim Count As IntegerFillColor = FillCell.Interior.ColorIndexFor Each c In CountRange    If c.Interior.ColorIndex = FillColor Then        Count = Count + 1    End IfNext cCOLORCOUNT = CountEnd Function

ventajas

• Puede crear una función que cuente las celdas coloreadas en un rango.
• Los resultados se actualizarán a medida que edite sus datos o cambie el color de relleno.
• Opción fácil de usar para usar una vez que está configurado.

Contras

• Utiliza VBA, que requiere que el archivo se guarde en formato xlsm.
• Menos fácil de usar para configurar.

Use scripts de Office para contar celdas coloreadas

Office Scripts es el nuevo método para automatizar tareas en Excel.

Pero solo está disponible para Excel en línea y solo con un plan empresarial. Si tiene un plan empresarial, ¡entonces está listo para usar este método!

Primero, deberá configurar dos celdas con nombre a las que se referirá el código.

Seleccione cualquier celda y escriba un nombre como ColorCount en el cuadro de nombre y presione Entrar . Esto creará un rango con nombre al que se puede hacer referencia en el código.

Esto significa que podemos mover la celda y el código se referirá a su nueva ubicación.

También deberá crear un rango con nombre de Color para que la entrada del color cuente.

Ahora puede crear un nuevo script de oficina. Vaya a la pestaña Automatizar en Excel en línea y haga clic en el comando Nuevo script .

function main(workbook: ExcelScript.Workbook) {let selectedSheet = workbook.getWorksheet("Sheet1");let myID = selectedSheet.getRange("Orders[Order ID]");let myIDCount = selectedSheet.getRange("Orders[Order ID]").getCellCount();let myColorCode = selectedSheet.getRange("Color").getFormat().getFill().getColor();let counter = 0;for (let i = 0; i  myIDCount; i++) {if (myID.getCell(i, 0).getFormat().getFill().getColor() == myColorCode ) {counter = counter + 1;} }selectedSheet.getRange("ColorCount").setValue(counter);}

Esto abrirá el Editor de código y podrá pegar el código anterior y guardar el script.

Presione el botón Ejecutar y el código se ejecutará y luego completará el rango con nombre ColorCount con el recuento de las celdas coloreadas que se encuentran en la columna ID de pedido .

ventajas

• Este es el método más nuevo y se admitirá en el futuro.
• El script se puede ejecutar desde Power Automate para una solución sin clic.

Contras

• Este método es difícil de configurar.
• Esto requiere una licencia empresarial y necesita usar Excel en línea.

Conclusiones

Con suerte, Microsoft algún día creará una función estándar de Excel que pueda devolver propiedades de una celda, como su color de relleno.

Hasta que eso suceda, al menos tiene algunas opciones disponibles que le permiten encontrar el recuento de celdas coloreadas sin contarlas manualmente.

¿Tiene un método preferido que no aparece aquí? ¡Házmelo saber en los comentarios!