Contar celdas en función del color de la celda en Google Sheets

Cuanto más uso Google Sheets, más me doy cuenta de lo potente que es.

Y al mismo tiempo, hay algunas cosas que me gustaría que fueran parte incorporada de las hojas de Google.

Una de esas funciones que me gustaría que tuviera Google Sheets es la de contar el número de celdas en función del color de fondo.

Por ejemplo, supongamos que tengo un conjunto de datos como el que se muestra a continuación y quiero saber rápidamente el número total de celdas que tienen el color de fondo verde y amarillo.

>Pero como esta funcionalidad no forma ya parte de Google Sheets, puede conseguirlo utilizando un script personalizado de Google Apps para crear su propia función en Google Sheets.

En este tutorial, le mostraré cómo puede crear fácilmente una función que le permita contar las celdas en función del color de fondo.

¡Así que empecemos!

Contar celdas en función del color de fondo

Supongamos que tiene un conjunto de datos como el que se muestra a continuación, y quiere averiguar rápidamente el número total de celdas que tienen el color de fondo verde y amarillo.

>El primer paso sería contar con el Script de Google Apps para crear una función personalizada que pueda hacer esto en Google Sheets.

He intentado buscar un script de este tipo en Internet y finalmente he encontrado uno en este blog.

A continuación se muestra el script de Google apps para la función personalizada que permitiría contar las celdas en función del color de la celda de fondo

función countColoredCells(countRange,colorRef) {  var activeRange = SpreadsheetApp.getActiveRange();  var activeSheet = activeRange.getSheet();  var fórmula = activeRange.getFormula();  var rangoA1Notación = formula.match(((.*)N,).pop();  var range = activeSheet.getRange(rangeA1Notation);  var bg = range.getBackgrounds();  var valores = range.getValues();  var celdaColorA1Notación = formula.match(,(.*)N).pop();  var celdaColor = activeSheet.getRange(colorCellA1Notation);  var color = colorCell.getBackground();  var count = 0;  for(var i=0;i<bg.length;i++)   for(var j=0;j<bg[0].length;j++)    if( bg[i][j] = color )     count=count+1;  devuelve count; };

Estos son los pasos que debe seguir para poner este script en su documento de Google sheets:

  1. En su documento de Google Sheets, haga clic en Herramientas
  2. En las opciones que aparecen, haga clic en Editor de scripts. Esto abrirá el Editor de Script de Apps
  3. >Por defecto, estará en la ventana de código Code.gs. Elimine todo lo que ya esté ahí, y copie y pegue el código anterior

    >Haga clic en el icono Guardar de la barra de herramientas

    >Cierre la ventana de Apps Script.

Al realizar los pasos anteriores, he añadido el código de script a Google Sheets, de modo que ahora puedo utilizar mis fórmulas personalizadas recién creadas dentro de la hoja de trabajo (igual que las fórmulas normales).

Nota: En caso de que ya tenga algún otro código en la ventana Code.gs y no quiera estropearlo, lo mejor es insertar un nuevo archivo Script y pegar el código allí. Para ello, haga clic en el icono del signo más junto a Archivos y luego seleccione Script. Esto insertará un nuevo archivo de script (déle un nombre) y copie-pegue el código anterior en este nuevo archivo de script.

Ahora que tenemos el código en su lugar, veamos cómo utilizar la fórmula personalizada para contar las celdas con un color de fondo específico.

En nuestro ejemplo, necesito contar todos los nombres que están en color verde.

Para ello, introduzca la siguiente fórmula en la celda en la que desee el recuento de las celdas de color:

=contarceldascoloreadas(A2:A11,C1)

>En la fórmula anterior, he utilizado la celda C1 como aquella de la que la fórmula debe recoger el color de fondo.También puede utilizar cualquier celda dentro del rango de datos para obtener el color y luego contar en base a él.

Al igual que la fórmula para contar las celdas de color verde, la siguiente fórmula cuenta todas las celdas de color amarillo:

=contarceldascoloreadas(A2:A11,D1)

¿Cómo funciona esta fórmula?

Déjeme explicar rápidamente lo que ocurre aquí.

Esta fórmula personalizada que hemos creado toma 2 argumentos:

  • el rango de celdas que tienen las celdas de color que necesitamos contar
  • la celda que tiene el color de fondo que necesitamos contar

En nuestro ejemplo, he tomado como segundo argumento la celda C1, ya que también tiene el mismo color que quiero contar. Si lo desea, puede elegir cualquier celda del rango y utilizarla en lugar de crear una nueva.

Por ejemplo, puede elegir la celda A3 en lugar de la C1 como segundo argumento, ya que también tiene el mismo color.

Cuando utiliza esta fórmula, recorre todas las celdas del rango y comprueba el color de fondo de todas las celdas. Si el color de fondo de una celda coincide con el del segundo argumento, entonces esa es, contada de otra manera no es.

Una vez que la fórmula ha recorrido todas las celdas del rango, simplemente devuelve el número total de celdas que ha encontrado que tienen el mismo color de fondo.

Nota: Puede utilizar la misma fórmula para contar celdas con diferentes colores de fondo. Por ejemplo, si en el mismo conjunto de datos tengo verde y rojo, puedo utilizar esta fórmula en dos celdas diferentes: una que se refiera a la celda verde y cuente el número total de celdas verdes, y otra que se refiera a la celda roja cuente el número total de celdas rojas.

Cómo hacer que la fórmula se actualice automáticamente al cambiar

Aunque la fórmula funciona perfectamente y cuenta el número de células con un color de fondo específico, hay un pequeño inconveniente.

No se actualiza automáticamente cuando se realiza un cambio. Por ejemplo, si elimino el color de una de las celdas o añado manualmente color a una de las celdas, la fórmula no se actualiza automáticamente para darme el resultado correcto.

Incluso si voy a la celda que tiene la fórmula, entro en el modo de edición y pulso intro, seguiría sin recalcularse.

El motivo es que no lo considera como un cambio que implique un recálculo. Incluso probé a refrescar el libro de trabajo y tampoco funcionó (quizás por algún problema de caché)

Desgraciadamente, no hay una forma automática de hacerlo, pero hay un método manual realmente sencillo para hacerlo.

Así que aquí está la solución - vaya a cualquiera de las celdas del rango, entre en el modo de edición (pulse F2 o haga doble clic en la celda), y añada un carácter de espacio al final del contenido de la celda, y pulse intro. En cuanto haga esto, verá que la fórmula se recalcula y le da el resultado correcto.

Ahora puede volver a la celda donde ha realizado este cambio añadiendo un carácter de espacio y eliminarlo.

Aunque esto es un inconveniente, por ahora es la única forma que se me ha ocurrido para asegurarnos de que obtenemos el recuento correcto del número de celdas coloreadas utilizando esta fórmula personalizada.

Si está trabajando con un conjunto de datos grande, esta fórmula puede tardar unos segundos o incluso unos minutos en calcular el número total de celdas con un color de fondo específico. Es posible que durante ese tiempo aparezca un texto "Cargando" en la celda (como se muestra a continuación).

>Así es como puede crear y utilizar su propia función personalizada para contar las ventas en función del color de fondo en Google Sheets.

Espero que haya encontrado útil este tutorial.

1 comentario en “Contar celdas en función del color de la celda en Google Sheets”

Deja un comentario