Muchas veces, cuando trabajamos con hojas de cálculo en Google Sheets, nos encontramos con la necesidad de realizar cálculos o análisis basados en celdas coloreadas. Sin embargo, esta tarea puede resultar complicada y consumir mucho tiempo si no conocemos las herramientas adecuadas. En este artículo, exploraremos dos funciones personalizadas de Google Sheets que te permitirán contar celdas coloreadas de forma rápida y sencilla: CELLCOLOR y VALUESBYCOLORALL. Descubre cómo utilizar estas funciones y optimiza tu trabajo con hojas de cálculo en Google Sheets. ¡No te lo pierdas!
Este tutorial presenta 2 nuevas funciones de nuestro complemento Función por color para Google Sheets: CELLCOLOR y VALUESBYCOLORALL. Úselos para sumar y contar celdas no solo por sus colores sino también por el contenido común. Se incluyen fórmulas SUMIFS y COUNTIFS listas para usar;)
Si trabaja mucho con celdas de colores en Google Sheets, probablemente haya probado nuestro Complemento Función por color. Lo que no sabes es que ahora cuenta con 2 funciones más que amplían aún más tus operaciones con celdas de colores: COLOR DE CELDA y VALORES POR COLORTODOS. En este tutorial, le presentaré ambas funciones y le proporcionaré algunas fórmulas ya preparadas.
Suma y cuenta celdas coloreadas con Función por Color
Antes de sumergirnos en nuestras 2 nuevas funciones personalizadas, me gustaría describir brevemente nuestras Función por color complemento en caso de que no esté familiarizado con él.
Este complemento para Google Sheets verifica la fuente y/o los colores de relleno en las celdas seleccionadas y:
- suma números con un tono común
- cuenta celdas coloreadas e incluso espacios en blanco
- encuentra los valores promedio/min/max entre las celdas resaltadas
- y más
Hay 13 funciones en total para calcular tus celdas coloreadas.
Así es como funciona:
- Usted selecciona el rango a procesar.
- Seleccione la fuente y/o los tonos de relleno que desee considerar y elija la función según su tarea.
- Elija calcular registros en cada fila/columna o en el rango completo.
- Seleccione las celdas donde desea ver el resultado.
- Golpear Función de inserción.
Por ejemplo, aquí, en cada fila, sumo todos los elementos que están «en camino», con un fondo azul:
=SUM(VALUESBYCOLOR("light cornflower blue 3", "", B2:E2))
Consejo. Hay un tutorial detallado para el complemento disponible aquí y una publicación de blog con ejemplos aquí.
Como puede ver, el complemento utiliza la función SUMA estándar junto con una función especial dentro: VALUESBYCOLOR.
Función VALORES POR COLOR
VALUESBYCOLOR es nuestra función personalizada.
Nota. No lo encontrarás en hojas de cálculo sin el complemento.
Devuelve aquellas celdas que corresponden a los colores que seleccionas en el complemento:
=VALUESBYCOLOR("light cornflower blue 3", "", B2:E2)
¿Ver? Obtiene solo aquellos registros para cada artículo suministrado desde arriba que están coloreados según mi configuración. Y estos números se calculan mediante una de esas funciones estándar que seleccioné en la herramienta: SUMA.
Muy bien, ¿eh? 😉
Bueno, hubo algo que el complemento pasó por alto. Esta fórmula no se puede usar en SUMIFS y CONTAR.SI, por lo que aún no se pueden contar según múltiples condiciones como el tono común y el contenido de las celdas al mismo tiempo. ¡Y nos han preguntado mucho sobre eso!
¡Me alegra decirles que lo hemos hecho posible con la última actualización (octubre de 2021)! Ahora Function by Color contiene 2 funciones personalizadas más que te ayudarán con eso 🙂
Funciones extra de Función por Color
2 nuevas funciones que implementamos se llaman VALUESBYCOLORALL y CELLCOLOR. Veamos qué argumentos requieren y cómo puedes usarlos con tus datos.
Nota. Dado que las funciones son personalizadas, son parte de nuestra Función por color Añadir. Necesitas tener instalado el complemento. De lo contrario, no podrá utilizar las funciones y se perderá el resultado que devuelven.
Consejo. Mira este vídeo o continúa leyendo. O haga ambas cosas para comprender mejor 😉 Incluso hay una hoja de cálculo de práctica disponible al final de la publicación del blog 😉
VALORES POR COLORTODOS
Esta función personalizada requiere 3 argumentos:
VALUESBYCOLORALL(color_relleno, color_fuente, rango)
- color de relleno — Código RGB o nombre de color (según la paleta de colores de Google Sheets) para un color de fondo.
Consejo. Aunque el argumento es obligatorio, puedes hacer que la función ignore totalmente el color de relleno ingresando solo un par de comillas dobles: «»
- color de fuente — Código RGB o nombre de color (según la paleta de colores de Google Sheets) para un color de texto.
Consejo. El argumento también es obligatorio, pero también requiere un par de comillas dobles «» cuando es necesario ignorar el color de fuente.
- rango — Aquí no hay nada especial, solo un rango de celdas que deseas procesar.
¿Has notado que VALUESBYCOLORALL se puede confundir fácilmente con la función VALUESBYCOLOR utilizada por el complemento? Ten cuidado porque hay una gran diferencia. Eche un vistazo a esta captura de pantalla:
Las fórmulas están escritas en B2 y C2, pero puedes ver cómo se ven en B8 y C8 correspondientemente:
=VALUESBYCOLOR("light green 3", "", A2:A7)
y
=VALUESBYCOLORALL("light green 3", "", A2:A7)
Consejo. Los nombres de los colores se toman de la paleta de Google Sheets:
¡Estas dos funciones tienen los mismos argumentos e incluso sus nombres son muy similares!
Sin embargo, devuelven diferentes conjuntos de datos:
- VALUESBYCOLOR devuelve la lista solo de aquellos registros que aparecen con un color de relleno verde en la columna A. El resultado de esta fórmula toma solo 3 celdas: B2:B4.
- VALUESBYCOLORALL, a su vez, devuelve el rango del mismo tamaño que el original (6 celdas) – C2:C7. Pero las celdas de este rango contienen registros sólo si la celda correspondiente en la columna A tiene el color de relleno requerido. Otras celdas permanecen vacías.
Aunque esto pueda parecerle lo mismo, marca una gran diferencia en combinación con otras funciones. Y esto es exactamente lo que le permite comprobar los colores junto con el contenido de las celdas con funciones como CONTAR.SI o SUMIFS.
COLOR DE CELDA
La siguiente función es bastante sencilla: verifica los colores de las celdas y devuelve una lista de nombres de colores o códigos RGB (usted elige) utilizados en cada celda. Incluso se llama igual: CELLCOLOR.
Es posible que no necesites esos nombres de colores directamente, pero puedes usarlos en otras funcionespor ejemplo, como condición.
Esta función también necesita 3 argumentos:
CELLCOLOR(rango, fuente_color, nombre_color)
- rango — aquellas celdas en las que desea comprobar los colores.
- color_fuente — le dice a la función dónde mirar:
- usa la palabra «llenar» entre comillas dobles para comprobar los colores de fondo
- «fuente» — para colores de texto
- «ambos» — tanto para colores de relleno como de texto
- nombre_color – su forma de saber qué tipo de nombre devolver:
- VERDADERO le muestra los nombres que ve en una paleta de Hojas de cálculo de Google, por ejemplo rojo o azul oscuro 1
- FALSO obtiene códigos RGB de los colores, por ejemplo #ff0000 o #3d85c6
Por ejemplo, la siguiente fórmula devuelve la lista de colores de relleno y fuente utilizados en cada celda de A2:A7:
=CELLCOLOR(A2:A7, "both", TRUE)
Entonces, ¿cómo se pueden usar estas funciones con IF, SUMIFS, COUNTIFS? ¿Cómo se configuran los criterios de búsqueda en función de los colores?
Sumar y contar celdas por color y contenido: ejemplos de fórmulas
Intentemos usar VALUESBYCOLORALL y CELLCOLOR en algunos casos simples.
SI color, entonces…
Aquí tengo una breve lista de estudiantes que aprobaron 3 pruebas:
Quiero marcar la fila con APROBADO en la columna E solo si todas las celdas de una fila son verdes (estudiantes que aprobaron todos los exámenes). Usaré nuestro CELLCOLOR en la función IF para verificar los colores y devolver la cadena requerida:
=IF(COUNTIF(CELLCOLOR(B2:D2,"fill",TRUE),"light green 3")=3,"PASS","")
Esto es lo que hace:
- CELLCOLOR(B2:D2,»relleno»,VERDADERO) devuelve todos los colores de relleno utilizados en una fila.
- CONTAR.SI(CELLCOLOR(B2:D2,»relleno»,TRUE),»verde claro 3″)=3 toma esos colores y comprueba si ‘verde claro 3’ (que uso en mis celdas) aparece exactamente 3 veces seguidas.
- Si es así, SI devuelve ‘PASS’; de lo contrario, la celda permanece vacía.
CONTAR.SI: cuenta por colores y valores con 1 fórmula
CONTAR.SI es otra función que finalmente puede contar según múltiples criterios incluso si uno de ellos es el color.
Supongamos que existen registros de ganancias por turno y por empleado:
Usando nuestras dos funciones personalizadas dentro de CONTAR.SI, puedo contar cuántas veces cada empleado implementó el plan de ventas (celdas verdes).
Ejemplo 1. CONTAR.SI + CELLCOLOR
Enumeraré a todos los gerentes al lado de la tabla con datos e ingresaré una fórmula separada para cada empleado. Empezaré con CELLCOLOR:
=COUNTIFS($A$2:$A$10,E2,CELLCOLOR($C$2:$C$10,"fill",TRUE),"light green 3")
- Lo primero que verifica la fórmula es la columna A: si hay ‘Leela’ (un nombre de E2), toma en cuenta el registro.
- Lo segundo que necesito verificar es si las celdas de la columna C son de color verde claro 3.
Consejo. Verifique el color de la celda usando la paleta de Google Sheets:
Dado que COUNTIFS en sí no puede simplemente captar el color, utilizo nuestro CELLCOLOR como rango para la condición.
Recordar, CELLCOLOR devuelve una lista de colores utilizados en cada celda. Cuando lo incrusto en CONTAR.SI, este último escanea esa lista en busca de todas las apariciones de ‘verde claro 3’. Esto, en combinación con un nombre de la columna E, da el resultado requerido. Pan comido 🙂
Ejemplo 2. CONTAR.SI + VALORES PORCOLORTODOS
Lo mismo sucede si eliges VALUESBYCOLORALL en su lugar. Ingréselo como un rango para la segunda condición:
=COUNTIFS($A$2:$A$10,E2,VALUESBYCOLORALL("light green 3","",$C$2:C$10),"<>")
¿Recuerdas lo que VALUESBYCOLORALL devuelve? Una lista de valores donde todas las celdas que cumplen con sus requisitos de color contienen registros. Todas las demás celdas permanecen vacías.
Entonces, cuando VALUESBYCOLORALL se pone en COUNTIFS, la fórmula cuenta solo aquellas celdas que no están vacías: «<>» (o, en otras palabras, corresponden al color requerido).
SUMIFS: suma celdas por colores y valores con 1 fórmula
La historia con SUMIFS es como con CONTAR.SI:
- Tome una de nuestras funciones personalizadas: COLOR DE CELDA o VALORES POR COLORTODOS.
- Póngalo como una gama que debería probarse en cuanto a colores.
- Ingrese la condición dependiendo de la función que seleccionó: el nombre del color para CELLCOLOR y «no vacío» («<>«) para VALUESBYCOLORALL.
Nota. SUMIFS no toma nada más que un simple rango como primer argumento: rango suma. Si intenta insertar una de nuestras funciones personalizadas allí, la fórmula simplemente no funcionará. Así que tenga esto en cuenta y asegúrese de ingresar CELLCOLOR y VALUESBYCOLORALL como criterio en cambio.
Aquí hay un par de ejemplos.
Ejemplo 1. SUMIFS + CELLCOLOR
Mira esta fórmula:
=SUMIFS($C$2:$C$10,A$2:A$10,E2,CELLCOLOR($C$2:$C$10,"fill",TRUE),"light green 3")
- CELLCOLOR obtiene todos los colores de relleno de C2:C10 y SUMIFS comprueba si alguno de ellos es ‘verde claro 3’.
- SUMIFS también escanea A2:A10 en busca de un nombre de E2 – leila.
- Una vez cumplidas ambas condiciones, se suma al total el importe de C2:C10.
Ejemplo 2. SUMIFS + VALORESBYCOLORALL
Lo mismo ocurre con VALUESBYCOLORALL:
=SUMIFS($C$2:$C$10,$A$2:$A$10,E2,VALUESBYCOLORALL("light green 3","",$C$2:$C$10),"<>")
- VALUESBYCOLORALL devuelve el rango donde solo las celdas del color de relleno requerido contienen valores. SUMIFS tiene en cuenta todas las celdas que no están vacías.
- SUMIFS también escanea A2:A10 en busca de ‘Leela’ desde E2.
- Una vez cumplidas ambas condiciones, se está totalizando el monto correspondiente de C2:C10.
Espero que este tutorial explique cómo funcionan las funciones y sugiera posibles formas de usarlas. Si aún tienes dificultades para aplicarlos a tu caso, encuéntrame en la sección de comentarios 😉
Hoja de cálculo para practicar
Función por color – funciones personalizadas – ejemplos (haga una copia de la hoja de cálculo)
Usted también podría estar interesado en
Funciones personalizadas de Google Sheets para contar celdas coloreadas: CELLCOLOR & VALUESBYCOLORALL
¿Qué son las funciones CELLCOLOR y VALUESBYCOLORALL?
Las funciones CELLCOLOR y VALUESBYCOLORALL son funciones personalizadas creadas para aquellos usuarios de Google Sheets que deseen contar el número de celdas coloreadas en una hoja de cálculo. Estas funciones facilitan el proceso de recopilación y análisis de datos resaltados visualmente.
CELLCOLOR – Contar celdas coloreadas individualmente
La función CELLCOLOR permite realizar el recuento de celdas coloreadas de manera individual. Su sintaxis básica es la siguiente: =CELLCOLOR(rango_celdas, color). Aquí, rango_celdas representa el rango de celdas en el que deseamos buscar y color es el código hexadecimal del color que estamos buscando. La función devolverá el número de celdas coloreadas con el color especificado.
VALUESBYCOLORALL – Contar celdas de diferentes colores simultáneamente
La función VALUESBYCOLORALL permite contar el número total de celdas coloreadas que coincidan con varios colores simultáneamente. Su sintaxis es la siguiente: =VALUESBYCOLORALL(rango_celdas, colores). Aquí, rango_celdas representa el rango de celdas en el que deseamos buscar, y colores es una matriz de códigos hexadecimales de colores separados por comas. La función devolverá el número total de celdas coloreadas con los colores especificados.
¿Cómo utilizar estas funciones en Google Sheets?
- Abre Google Sheets y ve a la hoja de cálculo en la que deseas contar las celdas coloreadas.
- En la celda donde deseas ver el resultado del recuento, escribe la función CELLCOLOR o VALUESBYCOLORALL según tus necesidades.
- Completa los parámetros requeridos. Para CELLCOLOR, ingresa el rango de celdas y el código hexadecimal del color. Para VALUESBYCOLORALL, ingresa el rango de celdas y la lista separada por comas de los códigos hexadecimales de los colores que deseas contar.
- Presiona Enter y el resultado del recuento se mostrará en la celda.
Conclusión
Las funciones CELLCOLOR y VALUESBYCOLORALL son herramientas poderosas que facilitan el recuento de celdas coloreadas en Google Sheets. Ya sea que necesites realizar un análisis de datos visual o simplemente quieras llevar un seguimiento de las celdas resaltadas, estas funciones personalizadas te brindarán una solución eficiente y precisa.
Referencias Externas
- Documentación oficial de Google Sheets: https://support.google.com/docs/answer/3093281?hl=es
- Tutoriales de Google Sheets: https://www.youtube.com/watch?v=ebhy3gykgEI&t=124s