Clases Excel
Jueves, 27 Agosto 2015 04:43

Cómo sumar, contar o promediar celdas según su color de fondo o de texto (sin macros)

Valora este artículo
(5 votos)

Muchos usuarios de Excel han deseado alguna vez sumar, contar o promediar las celdas que tuvieran un determinado color de fondo o de texto. ¿Te ha pasado? Este tipo de situaciones se suele resolver con macros. Sin embargo hay formas mucho más rápidas y sencillas de lograrlo.

Sumar, contar o promediar celdas por color 1

 

Empecemos aceptando la realidad cruda: Ninguna función de Excel (al menos hasta hoy) tiene la capacidad de manejar colores como argumentos. No tenemos forma de decirle a Excel, por medio de una función, que nos sume todas las celdas con fondo celeste, o que nos promedie todos los números de color violeta.

 

De ahí que muchos usuarios recurran al uso de macros para resolver este tipo de situaciones. Sin embargo, en la mayoría de los casos, tal vez no se justifique el esfuerzo y el tiempo empleados en lidiar con una macro, ya que podemos conseguir lo mismo con una serie de pasos muy simples.

 

Sumar, contar o promediar celdas por color 2

 

 

Partamos de una lista de datos con diversos formatos (diferentes colores de celda y/o fuente), ya sea que se hayan aplicado de forma manual o por medio de formato condicional. Da igual, porque la solución que aplicaremos es idéntica en ambos casos. Los dos pasos básicos que daremos para conseguir nuestro objetivo son:

1) Filtrar las celdas que tengan el color de fondo o texto deseado.

2) Obtener la suma, el recuento o el promedio de dichas celdas.

 

 

Filtrar por color

Filtrar es establecer ciertas reglas para que solo se muestren los datos que coincidan con dichas reglas y que los demás se oculten momentáneamente. Excel nos da la posibilidad de filtrar los datos de un rango tanto por el contenido de las columnas, como por su color de celda o de fuente.

 

Pero vayamos por partes: ¿Cómo podemos aplicar un filtro a un rango, en primer lugar? Basta con que seleccionemos cualquier celda dentro del rango y presionemos Ctrl+Mayús+L (esto es: presionar la tecla Ctrl, sin soltarla presionar la tecla Mayús o Shift y, sin soltar ninguna de las anteriores, presionar la tecla L). Otra forma es hacer clic en el ícono de Ordenar y filtrar (en el grupo Modificar de la ficha Inicio) y luego seleccionar la opción Filtro.

 

Sumar, contar o promediar celdas por color 3

 

Sumar, contar o promediar celdas por color 4

 

 

Aparecerá un botoncito en el encabezamiento de cada columna de nuestro rango de datos. Eso significa que los filtros están disponibles para ser aplicados.

 

Si hacemos clic en uno de esos botones, se desplegarán todas las opciones que Excel nos ofrece para la filtrar los datos de la columna. La que nos interesa en este caso es Filtrar por color. Automáticamente Excel detecta todos los colores aplicados tanto a las fuentes como a los fondos de las celdas.

 

Nos alcanzará con cliquear sobre el color de los datos que queremos sumar, contar, promediar, etc. (Aunque pueda parecer obvio, no está de más aclarar que si no aplicamos ningún color de celda o de fuente a la columna, no aparecerán las opciones de filtrado por color. En el ejemplo solo se muestran colores de fondo porque no aplicamos ningún color de fuente al rango de datos.)

 

Sumar, contar o promediar celdas por color 5

 

 

Una vez elegido un color, Excel dejará visibles solamente las celdas de ese color y ocultará las demás.

 

Sumar, contar o promediar celdas por color 6

 

 

Con esto terminamos la primera parte de nuestro reto. Consideremos ahora cómo usar los datos que tenemos a la vista.

 

 

Seleccionar los datos filtrados

La solución más simple es solo seleccionar los datos que acabamos de filtrar. En la zona inferior izquierda de la ventana, sobre la barra de estado, Excel nos mostrará automáticamente la suma, el recuento y el promedio de las celdas visibles que fueron seleccionadas.

 

Sumar, contar o promediar celdas por color 7

 

 

Veamos ahora cómo conseguir una solución más permanente.

 

 

La función SUBTOTALES

La función SUBTOTALES nos permite efectuar los cálculos más básicos de Excel (sumar, contar, promediar, etc.). Su sintaxis es sencilla:

=SUBTOTALES(Tipo de cálculo; Rango)

 

El primer argumento es un número que identifica el tipo de cálculo a realizar. Los más usuales son:

 

Número Tipo de cálculo
109 SUMA
102 CONTAR
101 PROMEDIO

 

El segundo argumento es el rango de valores que deseamos sumar, contar, promediar, etc.

 

Sumar, contar o promediar celdas por color 8

 

 

La peculiaridad de la función SUBTOTALES que nos es útil en este caso es que solo toma en cuenta las celdas visibles de un rango filtrado.

 

Conviene aplicar la función SUBTOTALES sobre el rango de datos sin filtrar. Luego, cuando filtremos diferentes colores, la función se recalculará, adaptándose a los nuevos valores visibles.

 

Sumar, contar o promediar celdas por color 9

 

 

Por ejemplo, si deseamos sumar todas las celdas de la columna que tengan fondo verde, podemos aplicar el Filtro por color, tal como hemos analizado antes, y SUBTOTALES nos devolverá la suma de las celdas con fondo verde solamente. Para que la función devuelva la suma de las celdas con fondo de otro color, bastará con cambiar de filtro.

 

Sumar, contar o promediar celdas por color 10

 

Como te habrás dado cuenta, el proceso es bastante simple y lo mejor de todo es con solo modificar el filtro, la función SUBTOTALES recalculará los resultados automáticamente, sin necesidad de volverla a reescribir.

 

 

Descarga el libro de trabajo

Descarga el libro con en el ejemplo usado en el artículo para verlo en acción.

 

Descarga el libro de ejemplo

 

Visto 11245 veces Modificado por última vez en Sábado, 05 Marzo 2016 23:16

Deja un comentario

Los campos imprescindibles están marcados con asterisco (*). Tu dirección de correo nunca será publicada.

4 comentarios

  • Enlace al Comentario Clases Excel Sábado, 29 Octubre 2016 17:11 publicado por Clases Excel

    Hola, Martín.
    Para mostrar todas las filas ocultas puedes presionar el atajo de teclado CTRL+E. Entonces, con botón derecho sobre el número de alguna fila, elige la opción Mostrar.
    Saludos

  • Enlace al Comentario martin vazquez Viernes, 28 Octubre 2016 19:13 publicado por martin vazquez

    si una hoja de excel tiene varios renglones escondidos,como hago para tenerlos visibles otra vez.
    gracias

  • Enlace al Comentario Clases Excel Jueves, 21 Enero 2016 07:39 publicado por Clases Excel

    Hola, Victorio.

    Según lo que entendí, si tienes el total (con la función SUMA) y lo ya pagado, la diferencia sería la resta de ambos valores.

    Por ejemplo, si el total está en D1 y lo pagado en D2, lo pendiente sería:
    = D1 - D2

    ¿Te refieres a esto?
    Saludos

  • Enlace al Comentario victorio Miércoles, 20 Enero 2016 09:00 publicado por victorio

    Hola, me ha parecido muy interesante el articulo, pero necesitaria el poder ver el total de todos los datos y lo sumado.
    Me explico, me he preparado una hoja excell con los gastos mensuales, segun veo el cargo en el banco le cambio el color de la fuente, y lo que me gustaria es que automaticamente se calculara lo que tengo que pagar, esto con SUMA() sin problema, lo ya pagado, es a lo que le cambio el color con vuestra explicación, y calcular la diferencia, es decir lo pendiente de pago....

Suscríbete a nuestro Boletín gratuito

Recibe periódicamente en tu email los últimos artículos, tutoriales y otras novedades de Excel.


Tus datos son confidenciales, nunca serán compartidos con nadie.

Inicio    Contacto    Términos y condiciones    Mapa del sitio

Ir arriba

Desarrollado por www.espectradesign.com