¿Sabías que puedes llevar la gestión de tus hojas de cálculo a un nuevo nivel con funciones personalizadas en Google Sheets? Si te has encontrado alguna vez con la necesidad de contar celdas coloreadas en tus tablas, este artículo es para ti. Hoy te presentamos dos funciones innovadoras: CELLCOLOR y VALUESBYCOLORALL. Estas herramientas no solo facilitarán el análisis de tus datos, sino que también te permitirán darle un toque visual a tus informes. Descubre cómo implementar estas funciones personalizadas y transforma la manera en que trabajas con tus hojas de cálculo. ¡Sigue leyendo y lleva tus habilidades de Google Sheets al siguiente nivel!
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:
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?>
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:
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.
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:
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’.
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.
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
xw5sGFfajOeiFhMdcAEtyaljTjE/copy?usp=sharing» rel=»noopener nofollow»>Función por color – funciones personalizadas – ejemplos (haga una copia de la hoja de cálculo)
Jaime fermin: ¡Exacto! A mí me pasó algo similar, estaba en medio de un proyecto y me volví loco tratando de contar celdas coloreadas a mano. Cuando encontré estas funciones, fue como descubrir el agua tibia. Ahora todo fluye muchísimo mejor, ¡se las recomiendo a todos!
Abdelhalim: ¡Totalmente de acuerdo, Fericseq! A mí también me salvó la vida esta función porque antes pasaba horas tratando de contar celdas coloreadas y me frustraba un montón. Desde que la probé, he visto un gran cambio en mi productividad. ¡Es un must para cualquiera que trabaje con datos!
Fericseq: ¡Me encanta este tipo de funciones en Google Sheets! La verdad es que el otro día me di cuenta de que gastar tiempo contando celdas coloreadas manualmente era un dolor de cabeza, así que esta solución me vino de perlas. Ahora puedo organizar mis datos mucho más rápido y fácil. ¡Gracias por compartirlo!