Ir al contenido principal

sumaproducto: otra función útil y poco intuitiva de excel

Utilicé esta función en mi anterior entrada, para multiplicar dos rangos (matrices), lo que originó inmediatamente la consulta de un amigo, dado que no estaba al tanto de la existencia de ella. Y luego de pensarlo un poco y analizar el contenido de muchos mensajes o correos electrónicos que recibo, observo que la gran mayoría del usuario promedio de Excel no la utiliza. Y existen un par de razones: hay otras formas de trabajo (prescindiendo de SUMAPRODUCTO), no existen muchos ejemplos y es una función matricial. Esto último es igual a decir: complicada, poco documentada y tabú. Intentaré brindar un par de ejemplos bien sencillos sobre esta función que nos ayudará a realizar cálculos complejos y nos servirá de "atajo" en mas de una ocasión, ya que sabiéndola utilizar evitaremos el uso de varias "funciones anidadas" o "rebuscadas".
=SUMAPRODUCTO(Matriz1; Matriz2; Matriz3....)

La sintaxis no es complicada: cada argumento de la función es una matriz o rango de datos. Cabe aclarar que todas las matrices pasadas como parámetro deberán tener el mismo tamaña, es decir, cantidad de elementos.
Veamos el principal y mas básico uso de SUMAPRODUCTO(): la suma de los productos de dos matrices.
La siguiente imagen aclarará convenientemente el panorama:
en C2 podemos observar el resultado de multiplicar cada elemento de ambas matrices y, posteriormente, la suma de todos ellos.

A mano: (2 * 20) + (3 * 30) + (4 * 40) + (5 * 50) = 40 + 90 + 160 + 250 = 540
Ya podemos observar a simple vista el trabajo que nos ahorró esta función. Es matricial (va almacenando resultados parciales), aunque no se ingresa con el "ctrol + alt + enter" ni aparecen las típicas llaves ( { } ) de toda función matricial. Si a al ejemplo anterior le agregamos un par de rangos, nos será mas que evidente el gran trabajo manual que nos evita:
y así hasta el infinito... y mas allá. estamos a estas alturas convencidos que realizar este cálculo por otra vía  hubiese sido un trabajo mucho mas engorroso.

Pero, por lo menos a mí, esto me deja "sabor a poco". Y en realidad es así, por que esta función da para mucho mas; es lo que trataré de explicar en las próximas líneas.
ex
Un trabajo realmente bueno que se puede llevar a cabo es contar o sumar con varias condiciones. Si bien Excel posee funciones específicas para esta tarea (CONTAR.SI o SUMAR.SI), carecen de un punto vital: solo aceptan una condición. Con SUMAPRODUCTO() podemos crear contadores o sumas con varias condiciones, de forma sencilla, con solo conocer un poco su lógica.
He mencionado en varias oportunidades que los valores VERDADERO y FALSO son tratados como números: VERDADERO=1 y FALSO=0. Les paso una imagen para refrescar la memoria:
si multiplico números (columna A) por Verdaderos o Falsos (columna B) obtendré el mismo resultado que multiplicar por 1 o 0 (columna C) respectivamente.

Lo anteriormente mencionado, en combinación con SUMAPRODUCTO(), será la herramienta que nos permitirá la creación de sumas y cuentas condicionales. Supongamos la siguiente tabla:
tres columnas archiconocidas y usadas: vendedor, zona e importe. muy básico, pero efectivo

Vamos a contar las ventas de Juan en la zona norte:
=SUMAPRODUCTO((A2:A12="juan")*(B2:B12="norte"))
efectivamente, Juan realizó dos ventas en la zona norte.

Para explicar mejor, diría: dentro de SUMAPRODUCTO() estoy multiplicando dos matrices: la columna de los nombres y la columna de las zonas. Como esta función es matricial, va analizando elemento x elemento. Si encuentra algún valor VERDADERO ("juan" o "norte") ese VERDADERO se convierte en 1; entonces "almacena" ese 1 y siguen con el próximo elemento. De esta forma halló solo dos registros en toda la tabla cuyos datos Vendedor y Zona son coincidentes, almacenó dos veces 1 ... y la suma de dos 1 es = 2.
Vean la fila dos de la tabla: Juan y Norte se encuentran juntos.... listo: VERDADERO.
Vean la fila diez de la tabla: Juan y Norte se encuentran junsto.....listo: VERDADERO.
O sea: dos VERDADEROS es igual a  1 + 1, que es el 2 que obtenemos como resultados final.

Calculamos hasta aquí la cantidad de ventas, en base a dos condiciones. En la celda de abajo expondremos el total de esas ventas:
=SUMAPRODUCTO((A2:A12="juan")*(B2:B12="norte")*(C2:C12))
ahá, así de fácil: sólo agregué la matriz (rango) que deberá multiplicar cada vez que "juan" y "norte" se encuentren, es decir: cuando ambos datos sean VERDADEROS, SUMAPRODUCTO() multiplicará por 1 el valor de la Columna C (importe), almacenará ese resultado, y al finalizar el recorrido de la tabla los sumará

A estas alturas se encontrarán evaluando lo poderosa y útil que es esta función. Y podemos agregar tantas condiciones como necesitemos, con la sola condición de estar planteándole a Excel algo lógico.
Otro ejemplo muy práctico: ¿que usuario de Excel no se desveló pensando en la forma de sumar las ventas correspondientes a un mes determinado? Y luego de mucho batallar llegamos a diseñar complejas funciones anidadas, haciendo también uso de celdas o columnas auxiliares... bueno, se terminó la historia. Con SUMAPRODUCTO() es sencillo lograrlo:
=SUMAPRODUCTO((MES(A2:A7)=1)*(B2:B7))
exacto, el total de ventas de enero asciende a 30.

Explicación: en la primer parte de la función (MES(A2:A7)=1) le digo a Excel que vaya analizando si el MES de las celdas del rango A2:A7 es  igual a 1. Y en la segunda parte (B2:B7) le estoy indicando los valores que deberá multiplicar por 1 (verdadero) cuando la anterior condición se cumpla.
En la imagen que sigue les aclaro la lógica:
no caben dudas que SUMAPRODUCTO() nos ahorra mucho trabajo

Es mas, podemos ir un poco mas lejos: de ser necesario, se nos hace factible sumar por trimestres. Aqui la cuestión se empieza a complicar un poco, dado que anidaremos otras funciones dentro de SUMAPRODUCTO()... pero a no tenerle miedo, la lógica es la misma:
=SUMAPRODUCTO((MES(A2:A11)>=1)*(MES(A2:A11)<=3)*(B2:B11))

Aclarando:
primer parte: devolverá VERDADERO solo si el mes de la celda es mayor o igual a 1 (enero)
segunda parte: devolverá VERDADERO solo si el mes de la celda es menor o igual a 3 (marzo)
tercera parte: suma todas las coincidencias encontradas, cuyos meses estén comprendidos entre enero y marzo, es decir, el primer trimestre del año.

Y hay mas, pero lo dejaré para futuras entradas, en donde seguiremos viendo la forma de ahorrar y encarar trabajos complejos mediante esta increíble y poco conocida función. No sigo ahora por dos motivos: permitir que practiquen con lo visto hasta el momento y no hacer tan larga la entrada.
Les dejo el link a la descarga del archivo.

Comentarios

  1. Hola mi nombre es Omar Maldonado quisiera saber si se puede vincular solo el formato de alguna celda a otra?

    Gracias / Saludos

    ResponderEliminar
  2. hola omar, un gusto después de tanto tiempo.
    estimo que haces referencia, por ejemplo a que si el valor de una celda cumple con la condición, ese formato se aplique a toda la fila ¿es eso?
    ¿estás necesitando que si, por ejemplo, en A1 hay un determinado valor se coloree A1 y otras celdas? ¿entendí bien?
    te consulto para armar un ejemplo que te sirva. quedo al aguardo de tus noticias. en todo caso, pasame un archivo con un pequeño ejemplo.
    un abrazo

    ResponderEliminar
  3. Hola Damian, necesito ayuda con esta formula de SUMAPRODUCTO, necesito que me haga una suma segun dos criterios, una es el mes desde una fecha y otro un codigo como texto (ej. 110-01) es como una resumen se puede decir de ventas por mes y por codigo de producto
    Marco Maldonado

    ResponderEliminar
  4. Marcos: suponiendo que en A1:A5 tienes las fechas, en B1:B5 los códigos y en C1:C5 los importes, esta función anda perfecto:
    {=SUMA(SI(MES(A1:A5)=2;SI(B1:B5="100-02";C1:C5)))}

    dentro de SUMA() anido un SI() que posee dos condicionales: si el mes es igual a 2 y el código igual a 100-02, entonces que se aplique la SUMA de los importes expuestos en C1:C5.
    ¿se entiende la lógica? funciona bien. es matricial (mirá los corchetes al iniciar y finalilzar) así que no olvides presionar "ctrol + mayuscula + enter" al ingresar la funcion.

    ResponderEliminar

Publicar un comentario

Entradas populares de este blog

funciones: convertir numeros a letras con excel

En realidad hace tiempo que vienen consultándome sobre esto... quizás cinco o seis años . Debe ser una de las funciones mas buscadas de Excel, por lejos: la posibilidad de escribir: 1.534,63 y que en una celda aparezca magicamente "un mil quinientos treinta y cuatro c/15/100". Aquí les dejo una solución, basada en funciones , sin utilizar macros, la cual preparé exclusivamente para este Blog. A pesar de los millones de usuarios que requieren esta herramienta, no viene incorporada en Excel, debemos armarla nosotros mismos .  Luego de la imagen irá la explicación de como llegué a lograr esto, dado que es un proceso medianamente complejo , que utiliza tres o cuatro funciones básicas y requiere de varios pasos. así quedará nuestra planilla y siempre devolverá en letras el valor que ingresemos en A1 [+/-] Ver el resto / Ocultar Desde ya aclaro: esta no es " la forma " de hacerlo, simplemente es una mas, evitando el uso de macros. Sobre esta base, comencemos: 1) El núme

macros: como enviar mails desde Excel (vba)

Un gran amigo "on-line", Johan Moreno, de Colombia , me hizo llegar la inquietud de cómo enviar mails desde Excel . Si bien contaba con algunas líneas de código sobre el particular, decidí retomar mi ejemplo y adaptarlo mejor a las circunstancias. Hace unos minutos termino de remitirle un correo con la solución a Johan... desde Excel y con un archivo adjunto, mismo que transcribo a continuación para ayudar a todos con esta tarea. Veamos primero las dos formas principales de enviar mails: mediante Outlook o nuestro Web Mail ( yahoo, gmail, hotmail, etc, etc ) No soy usuario de Outlook en lo absoluto: no confío en los agujeros de seguridad que continuamente aparecen y, por otro lado, t eniendo a mi alcance una herramienta tan poderosa (y gratuita) como Gmail, la cual ahora también permite sincronizar los mensaje con la Pc para verlos offline (deben activarlo en "google labs") ... no creo que me haga usuario ni hoy ni mañana. Igualmente mas adelante daré una solució

buscarv con varios resultados

Es de las preguntas mas recurrentes que he tenido: ¿se puede lograr que la función BUSCARV() devuelva varios resultados? Si recordamos el uso de esta función, sabremos que la misma buscar en valor en el rango especificado, retornando un solo resultado. Si en la tabla tenermos varios registros iguales BUSCARV() solo nos devolverá el primero de ellos, omitiendo el resto. Entonces ¿se puede? Bueno, aplicando otra técnica sencilla que se me ocurrió al intentar solucionar el planteamiento de un lector... si, puedo hacerlo.... pero sin usar BUSCARV(). la idea es que coloquemos en E1 el código del producto a buscar y a partir de E2 nos devuelva todas las coincidencias de la tabla. [+/-] Ver el resto / Ocultar Vamos a necesitar una columna "auxiliar" para llevar a buen término este proyecto. Con macros el tema sería mas sencillo, pero aquí la cuestión es resolverlo con las funciones de Excel, sin VBA. Paso a paso: Como primer medida chequeamos que valores de la columna A coincide co