Trucos de Excel

Descargar archivo de ejemplo.

En el tutorial anterior, vimos el uso de la función de Excel SUMAPRODUCTO para calcular promedios ponderados.

Este es el uso más básico de la función.  Pero esta función tiene un enorme potencial para realizar distintos tipos de cálculos.

A partir del comentario de Emanuel, desarrollé este tutorial se relaciona con contar y realizar sumas donde aplican varios filtros.

Si bien Excel tiene funciones como CONTAR.SI y SUMAR.SI, la ventaja de SUMAPRODUCTO es que nos permite realizar estos cálculos cuando tenemos varios filtros simultáneamente.

En este tutorial vamos a trabajar con la tabla de la derecha donde utilizaremos esta función para:

• Calcular la cantidad de proyectos de un mes específico (hay muchas formas de hacer esto, pero en este post haremos todo solo con SUMAPRODUCTO).
• Para un proyecto específico, cuántos se realizaron en un mes específico (Cuenta con DOS filtros: proyecto y mes).
• Suma de la cantidad de horas de un proyecto en un mes determinado (Suma con DOS filtros).

Este es el tipo de operaciones que si no te la explican, jamás ocurriría que se pueden hacer porque no es intuitivo.

Por esto, antes de entrar a la solución específica, voy a dar un par de ejemplos más sencillos.

Verdadero y Falso son números!!!

Lo primero que debemos saber es que los valores lógicos VERDADERO y FALSO (ver funciones lógicas en Excel), pueden actuar como números en Excel.

VERDADERO =1

FALSO = 0

Esto es, VERDADERO + 1 = 2.  FALSO + 1=1.  o VERDADERO*5 =5; FALSO*5 =0.

También:

VERDADERO*VERDADERO=1; VERDADERO*FALSO y FALSO*FALSO =0.

¿Para qué sirve esto?  Porque cuando utilizamos condiciones (filtros), el resultado es un valor lógico VERDADERO o FALSO (cumple o no cumple la condición).

Ejemplos básicos de SUMAPRODUCTO

Tomando siempre el ejemplo de la tabla que tenemos al inicio de este post.  Si introducimos (en una celda cualquiera) la siguiente función:

=SUMAPRODUCTO(1*(A2=”ene”))

Paso a paso, Excel realiza los siguientes pasos:

=SUMAPRODUCTO(1*(“ene”=”ene”)).  Esto es, reemplaza la referencia A2 por su valor real.

=SUMAPRODUCTO(1*(VERDADERO)) Porque efectivamente, A2 tiene el valor “ene”

=SUMAPRODUCTO(1*1)

=1.  Esto nos indica, que hay UN valor que cumple exactamente con esta condición.  Es decir, está contando.

Si en nuestra fórmula reemplazamos “ene” por “feb” tendríamos.

=SUMAPRODUCTO(1*(A2=”feb”))

=SUMAPRODUCTO(1*(“ene”=”feb”))

=SUMAPRODUCTO(1*FALSO)

=SUMAPRODUCTO(1*0)

=0

Ahora veamos un ejemplo sumando.  Evaluaremos:

=SUMAPRODUCTO(C2*(A2=”ene”))

• =SUMAPRODUCTO(15*(A2=”ene”))
• =SUMAPRODUCTO(15*(“ene”=”ene”))
• =SUMAPRODUCTO(15*VERDADERO)
• =SUMAPRODUCTO(15*1)
• =15

Solución al ejercicio

1. Contar la cantidad de proyectos en un mes

Esta es la parte más fácil de todas:

=SUMAPRODUCTO(1*(A2:A10=F2))

Esto es, la función tomará cada celda entre A2 y A10 y la comparará con F2 (en este caso, el mes, “ene”).

Esta comparación generará una serie de resultados VERDADERO-FALSO.

Luego multiplicará estos resultados por 1.  La suma de todos estos 1 y 0, equivale a contar la cantidad de datos que cumplen con la condición.

Esta función es totalmente equivalente a usar:

• =CONTAR.SI(A2:A10;F2)

2. Para un proyecto específico, contar la cantidad de ocurrencias durante un mes

Esto ya es un poco más complicado.

Pero con lo que hemos aprendido hasta ahora, podemos solucionarlo fácilmente aplicando la función:

• =SUMAPRODUCTO((A2:A10=F6)*(B2:B10=F7))

Donde, en la parte de la izquierda se realizan los cálculos relacionados con la comparación del mes mientras en la izquierda la equivalente al producto.

Como estamos sumando multiplicaciones de VERDADEROS y FALSOS, cuando haya doble coincidencia, tendremos VERDADERO*VERDADERO =1 y 0 en caso contrario.  Por lo tanto, tendremos la suma de UNOS (1), cada vez que se cumplan ambas condiciones.

El resultado es equivalente a contar, donde el requisito es que se cumplan dos condiciones.

Si tuviéramos una tercera condición, el proceso a realizar es el mismo!  Solo basta agregar el rango y el valor al cual comparar.

3. Suma de la cantidad de horas de un proyecto en un mes determinado (Suma con DOS filtros)

Para SUMAR los datos que cumplen ambas condiciones, no nos sirve solamente con sumar los valores VERDADEROS sino que necesitamos además los valores propios a sumar.

Esto lo conseguimos así:

• =SUMAPRODUCTO((A2:A10=F11)*(B2:B10=F12)*C2:C10)

Tal como hicimos en el punto anterior, pero agregando el rango con los datos a sumar (C2:C10).

Cuando se cumplen ambas condiciones del filtro, estaremos multiplicando VERDADERO (=1) por un valor en la columna C.

Cuando no se cumpla una o ninguna de las condiciones, se sumará la multiplicación de FALSO por el valor de la columna C.  Es decir, sumará CERO.

De esta forma, solo sumarán los valores para los cuales se cumplan solamente AMBAS condiciones.

———————————————————————————————————————————————-

¿Te gustó este truco o tenés alguna recomendación u otra forma de hacerlo?  No te olvides de dejar tu comentario y compartir esta nota!

Buscá más trucos en: trucosdeexcel.com
Seguime por Twitter, usuario “trucosdeexcel”.
Mandá tus sugerencias o preguntas a: excelman@trucosdeexcel.com

11 comentarios para “SUMAPRODUCTO #2 – Contar y sumas con múltiples filtros”