Básicamente, la función SUMAPRODUCTO multiplica los elementos de varias matrices “por filas” y suma los resultados. Esta definición no dice mucho, pero es algo relativamente sencillo que nos puede servir para realizar complejas sumas condicionales si usamos un poco la imaginación y en un sólo paso. Lo veremos con algunos ejemplos.
Supongamos que tenemos el siguiente listado de ventas de productos:
¿Cómo calculamos el total de ventas?
Vamos a utilizar la función suma producto para realizarlo en sólo un paso. Nos situamos en una celda vacía y escribimos la siguiente fórmula:
=SUMAPRODUCTO(D2:D13*E2:E13)
La fórmula multiplica fila a fila la cantidad por el precio unitario y finalmente suma todos los resultados. Obtenemos el total de las ventas (8.285.255)
¿Cómo calculamos el total de ventas del mes 2?
Con la función de suma producto es sencillo. Partiendo de la fórmula usada en el ejemplo anterior, vamos a añadir la matriz de los meses (C2:C13) al producto de matrices, pero con algunos cambios. Tenemos que conseguir una matriz que cuando sea una fila del mes 2 multiplique por uno y cuando no sea del mes 2 multiplique por cero. Es decir, tenemos que crear una fórmula que convierta la columna del mes en una columna de ceros y unos como la siguiente.
Con la función SUMAPRODUCTO podemos añadir comparaciones/validaciones que nos devuelven un valor binario para cada fila que se procesa. La fórmula para calcular las ventas del mes 2 sería la siguiente:
=SUMAPRODUCTO((C2:C13=2)*D2:D13*E2:E13)Dónde el resultado de C2:C13=2 es una matriz que tiene unos para las filas del mes 2 y el resto ceros. Finalmente, el resultado de la función SUMAPRODUCTO la suma de los productos dónde la matriz “C2:C13=2” no es cero.
Un paso más, la tabla dinámica
Después del ejemplo anterior, ahora vamos a obtener las ventas del Producto A en PYME en el mes 1. Es decir, Producto = “Producto A”; Sector=”PYME”; Mes = 1. La fórmula será la siguiente:
=SUMAPRODUCTO((A2:A13=“Producto A”)*(B2:B13=”PYME”)*(C2:C13=1)*D2:D13*E2:E13)Esto no sirve de mucho, usemos una fórmula más reutilizable. Vamos a poner los valores “Producto A”, “PYME” y “1” en otras celdas y hacemos referencia a ellos en la fórmula.
Celda A15 = 1
Celda A18 = “Producto A”
Celda B17 = “PYME”
Celda B18 = “=SUMAPRODUCTO((A2:A13=A18)*(B2:B13=B17)*(C2:C13=A15)*D2:D13*E2:E13)”
Vamos cambiar la formula un poco. Fijamos las referencias a las matrices y el número del mes, para el producto sólo fijamos la columna y finalmente para el sector fijamos sólo la fila. El resultado es la siguiente fórmula:
=SUMAPRODUCTO(($A$2:$A$13=$A18)*($B$2:$B$13=B$17)*($C$2:$C$13=$A$15)*$D$2:$D$13*$E$2:$E$13)Ahora ya podemos crear una tabla con más datos de productos y sectores en función del mes, tal y como se muestra en la siguiente imagen y únicamente tenemos que “arrastrar” la fórmula que hemos creado.
¿Os suena esto?, seguramente sí. Es muy parecido a una tabla dinámica de Excel, pero hemos tardado bastante más en construirla y no tiene el potencial de las tablas dinámicas. Personalmente, siempre utilizo una dinámica… ¿pero? ¿Entonces para que nos sirve la función SUMAPRODUCTO?
NOTA: No es cierto que una tabla dinámica siempre sea más rápida. En este ejemplo, hay que añadir una columna adicional a nuestro listado de ventas (contendrá el resultado de multiplicar las cantidades por los precios unitarios) para obtener con una tabla dinámica el mismo resultado.Sacando partido a la función SUMAPRODUCTO
Cuando queremos obtener resultados complejos una tabla dinámica resulta insuficiente sin realizar cálculos previos o añadir columnas adicionales. Por ejemplo, ¿Cómo obtener el total de ventas de aquellos productos de los que hemos vendido entre 10 y 30 unidades?
Utilizando la fórmula SUMAPRODUCTO creada en el ejemplo anterior sustituimos la matriz de cantidades ($D$2:$D$13), por el siguiente producto de matrices: ($D$2:$D$13<30)*($D$2:$D$13>10)
La formula final es:
=SUMAPRODUCTO(($A$2:$A$13=$A18)*($B$2:$B$13=B$17)*($C$2:$C$13=$A$15)*($D$2:$D$13<30)*($D$2:$D$13>10)*$E$2:$E$13)Si usamos la tabla que hemos creado en el ejemplo anterior y “arrastramos” la nueva fórmula nos queda la tabla como aparece en el siguiente ejemplo.
Con este último ejemplo hemos visto la verdadera potencia de la función SUMAPRODUCTO, a partir de este punto la imaginación al poder para realizar sumas condicionales de forma sencilla y rápida.
NOTA: Un ultimo consejo, si las matrices que usamos son muy grandes los cálculos suelen ser lentos, en estos casos es mejor usar tablas dinámicas generando columnas calculadas.
No hay comentarios:
Publicar un comentario