¿Cómo contar o sumar celdas según el color de la celda en una hoja de Google?
En el trabajo diario con hojas de cálculo, es posible que te encuentres en situaciones donde necesites contar o sumar los valores de las celdas según el color de fondo específico de dichas celdas, como se muestra en la captura de pantalla a continuación. Por ejemplo, podrías querer contar o totalizar solo aquellas celdas resaltadas en un color particular para analizar rápidamente los datos por categoría o estado. Esta guía abordará cómo lograr esto no solo en Google Sheets, que carece de soporte integrado para este tipo de cálculos basados en color, sino también en Microsoft Excel, que proporciona varios enfoques diferentes, desde funciones integradas hasta utilidades avanzadas.
Comprender cómo manejar análisis de datos basados en colores puede hacer tu trabajo más eficiente, especialmente cuando los colores se utilizan para marcar estados, prioridades o categorías. También discutiremos diferentes soluciones, compararemos sus escenarios de uso y proporcionaremos consejos prácticos de operación, así como recordatorios de errores para asegurar que tus tareas se realicen sin problemas.
- Contar valores de celdas según el color de la celda con script en una hoja de Google
- Sumar valores de celdas según el color de la celda con script en una hoja de Google
- Contar o sumar valores de celdas según el color de la celda con Kutools para Excel en Microsoft Excel
Contar valores de celdas según el color de la celda con script en una hoja de Google
Google Sheets no ofrece una opción directa para contar celdas según el color de fondo. Sin embargo, puedes lograrlo con un script personalizado de Apps Script. Este script actúa como una función definida por el usuario, permitiéndote referenciarla como una fórmula. Aquí está cómo configurar y usar el script:
1. Haz clic en Herramientas > Editor de scripts para acceder al entorno de scripting. Ver captura de pantalla:
2. En la ventana del proyecto, selecciona Archivo > Nuevo > Archivo de script para abrir un nuevo módulo de código, como se muestra:
3. Cuando se te solicite, introduce un nombre para tu nuevo código de script y confirma. Dale un nombre significativo al script para ayudarte a identificar su propósito más adelante.
4. Haz clic en Aceptar, luego copia y pega el siguiente código para reemplazar cualquier código de ejemplo en el módulo. Asegúrate de pegarlo exactamente como se proporciona.
function countColoredCells(countRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};
5. Guarda este archivo de script, regresa a tu hoja y utiliza la nueva función como cualquier fórmula de Google Sheets. Ingresa: =countcoloredcells(A1:E11,A1) en una celda en blanco para contar las celdas en el rango A1:E11 que coincidan con el color de A1. Presiona Enter para obtener el resultado. Si se te solicita permiso, autoriza el script para ejecutarlo en tu hoja.
Nota: A1:E11 es tu rango de datos; A1 es la celda de referencia del color a contar. Asegúrate de que las celdas de referencia tengan exactamente el mismo color y evita las celdas combinadas para mayor fiabilidad.
6. Para contar otros colores, repite la fórmula con una celda de referencia de color diferente según sea necesario. Si tu rango cambia, ajusta el rango en la fórmula en consecuencia.
Si recibes un error o un resultado inesperado, verifica nuevamente que el script se haya guardado y que hayas utilizado la referencia de color correcta. Las funciones basadas en Apps Script solo recalculan si la función o sus argumentos cambian —si más tarde vuelves a colorear tus celdas, vuelve a ingresar la fórmula o presiona Enter nuevamente para actualizar.
Sumar valores de celdas según el color de la celda con script en una hoja de Google
Sumar valores de celdas según un color de celda definido en Google Sheets requiere un enfoque similar utilizando un Apps Script. Esto es especialmente útil para hojas financieras, registros de estado o cualquier escenario donde los colores representen categorías con datos numéricos subyacentes.
1. En Google Sheets, abre el Editor de scripts a través de Herramientas > Editor de scripts. Una vez en la ventana del proyecto, selecciona Archivo > Nuevo > Archivo de script para agregar un nuevo módulo de código. Asigna un nombre único en el mensaje emergente para ayudar a seguir su propósito, como "SumColoredCells". Confirma para crear el módulo.
2. Haz clic en Aceptar, y en la nueva ventana del módulo de código, reemplaza cualquier código predeterminado pegando el script proporcionado para sumar celdas coloreadas. Asegúrate cuidadosamente de que todo el código esté copiado, ya que los caracteres faltantes pueden resultar en errores de sintaxis.
function sumColoredCells(sumRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var sumValues = activeSht.getRange(countRangeAddress).getValues();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var totalValue = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
if ((typeof sumValues[i][k]) == 'number')
totalValue = totalValue + (sumValues[i][k]);
return totalValue;
};
3. Después de guardar el script, regresa a tu hoja e ingresa la fórmula =sumcoloredcells(A1:E11,A1) en una celda en blanco, luego presiona Enter. Esta fórmula suma los valores en A1:E11 donde el color de fondo coincide con A1. Al usar esta función, asegúrate de que todas las celdas objetivo de la suma sean numéricas; los valores no numéricos serán ignorados.
Nota: A1:E11 representa tu rango de datos, y A1 proporciona la referencia de color. La fórmula solo sumará los valores numéricos visibles —asegúrate de que las celdas combinadas o los errores dentro del rango no afecten tus totales.
4. Puedes replicar el proceso anterior para sumar valores para diferentes categorías de color cambiando la celda de referencia de color en tu fórmula. Si tus datos se actualizan o cambias los colores de fondo, recuerda actualizar la fórmula para obtener resultados actualizados.
Si la suma devuelve cero o un valor de error, verifica que el rango contenga números y que la coincidencia de color sea exacta. Además, el recálculo no es automático si solo cambia el color de la celda —edita la celda de la fórmula para forzar una actualización.
Contar o sumar valores de celdas según el color de la celda con Kutools para Excel en Microsoft Excel
Al trabajar en Microsoft Excel, contar o sumar celdas por color es un requisito frecuente, especialmente dentro de informes de gestión de proyectos, inventario o control de calidad. Kutools para Excel ofrece una utilidad dedicada Contar por Color, que te permite obtener conteos y totales por color de fondo o fuente directamente —esto es particularmente útil para rangos de datos más grandes y cuando necesitas resultados rápidos y repetibles.
Una vez instalado Kutools para Excel, procede con estos pasos:
1. Resalta el rango donde deseas contar o sumar por color, luego haz clic en Kutools Plus > Contar por Color. Consulta la captura de pantalla a continuación para obtener orientación:
2. Aparecerá el cuadro de diálogo Contar por Color. Establece Formato estándar bajo Método de color, y elige Fondo para Tipo de conteo. Revisa cuidadosamente la vista previa y las opciones:
3. Haz clic en Generar informe para crear una nueva hoja de cálculo que contenga un desglose de conteos y sumas para cada color presente en tu rango. Este informe incluye tanto el número como la suma de las celdas coloreadas, lo que permite una fácil referencia o análisis adicional.
Nota: Esta característica también puede calcular valores basados en formato condicional o color de fuente. Usa reglas de formato condicional para análisis dinámicos; de lo contrario, la herramienta funciona mejor para rellenos de color estáticos. Cualquier cambio en los colores de las celdas de origen requerirá volver a ejecutar la utilidad Contar por Color para obtener resultados actualizados. Si experimentas problemas, confirma que Kutools está activo y actualizado.
¡Haz clic aquí para descargar y probar gratis Kutools para Excel ahora!
Las mejores herramientas de productividad para Office
Mejora tu dominio de Excel con Kutools para Excel y experimenta una eficiencia sin precedentes. Kutools para Excel ofrece más de300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haz clic aquí para obtener la función que más necesitas...
Office Tab incorpora la interfaz de pestañas en Office y facilita mucho tu trabajo
- Habilita la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abre y crea varios documentos en nuevas pestañas de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumenta tu productividad en un50% y reduce cientos de clics de ratón cada día!
Todos los complementos de Kutools. Un solo instalador
El paquete Kutools for Office agrupa complementos para Excel, Word, Outlook y PowerPoint junto con Office Tab Pro, ideal para equipos que trabajan en varias aplicaciones de Office.
- Suite todo en uno: complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un solo instalador, una licencia: configuración en minutos (compatible con MSI)
- Mejor juntos: productividad optimizada en todas las aplicaciones de Office
- Prueba completa de30 días: sin registro ni tarjeta de crédito
- La mejor relación calidad-precio: ahorra en comparación con la compra individual de complementos