miércoles, 6 de febrero de 2013

Las posibilidades de SUMAPRODUCTO en Excel.

Hoy voy a hablar sobre algunas de las funciones más empleadas en nuestras hojas de Excel, las funciones SUMAR.SI y SUMAR.SI.CONJUNTO (de las que se han hablado largo y tendido en este blog); pero sobre todo de una función alternativa a estas, con multitud de ventajas, la función SUMAPRODUCTO.
En muchas ocasiones al elaborar nuestros cálculos nos hemos dado cuenta de las limitaciones de esas funciones SUMAR.SI o SUMAR.SI.CONJUNTO, que aunque muy potentes, nos restringen las operaciones al criterio Y, es decir, funcionan únicamente con sumas acumulativas de registros que cumplen condiciones simultáneas. Por ejemplo:
=SUMAR.SI.CONJUNTO(A1:A10;B1:B10;>100;C1:C10;"a")
que sumaría los importes del rango A1:A10 sólo en los casos en que la cantidad de B1:B10 correspondiente sea mayor a 100 y al mismo tiempo en C1:C10 contenga el texto "a".

Las posibilidades de SUMAPRODCUTO en Excel.


¿Pero, y qué podemos hacer si en lugar del criterio Y deseamos aplicar el criterio O?. Aquí es donde comienza la potencia de SUMAPRODUCTO.

Si buscamos en la ayuda de Excel esta función leeremos: SUMAPRODCUTO Multiplica los componentes correspondientes de las matrices suministradas y devuelve la suma de esos productos.; y efectivamente este sería el uso más habitual, sin embargo es mucho más interesante observar los argumentos de la función, en la Sintáxis:
SUMAPRODUCTO(matriz1; [matriz2]; [matriz3]; ...)

Lo especial de esta función es que permite trabajar u operar con Rangos o matrices!!!, lo que es poco frecuente, excepto con las funciones matriciales. Es, sobre este punto, sobre el que incidiré.
Esta forma parecida a la matricial de trabajar de nuestra función es la que ocupará nuestra entrada del día; ya que con ella podremos operar empleando ambos criterios lógicos Y y O (AND y OR); esto lo conseguiremos empleando los operadores * y +:
para el operador lógico Y usaremos *
para el operador lógico O usaremos +

En esencia conseguiremos que Excel evalúe las condiciones que le vamos a dar, empleando estos operadores, para que nos devuelva VERDADERO o FALSO (1 ó 0 en valor numérico respectivamente), todo ello sin emplear la forma matricial de ejecutar las funciones.


Veremos, como siempre, algunos ejemplos de este uso especial de SUMAPRODUCTO. Partiremos de la siguiente base de datos, al que hemos asignado ciertos Nombres definidos para trabajar de una forma más visual:
Comercial =Hoja2!$A$2:$A$14
Importe =Hoja2!$D$2:$D$14
Unidades =Hoja2!$C$2:$C$14
Zona =Hoja2!$B$2:$B$14

Las posibilidades de SUMAPRODCUTO en Excel.


Un primer ejemplo serviría para sustituir a las funciones SUMAR.SI o SUMAR.SI.CONJUNTO.
=SUMAR.SI.CONJUNTO(Importe;Comercial;"Pepe";Unidades;">60")
puede ser sustituido por
=SUMAPRODUCTO(Importe*(Comercial="Pepe")*(Unidades>60))

En ambos casos nos indica cuánto suman los importes para el comercial 'Pepe' cuando las Unidades vendidas superan las 60.
Notemos como las dos condiciones de SUMAR.SI.CONJUNTO son equivalentes al operador * (que implica el criterio lógico Y).


El anterior ejemplo no aporta mucha funcionalidad, pero asienta las bases para el siguiente ejemplo, en el cual combinaremos un operador lógico Y(AND) con un operador lógico O(OR), empleando * y +. Fijémosnos en la siguiente función:
=SUMAPRODUCTO(Importe*((Comercial="Pepe")+(Comercial="Luís"))*(Zona="Centro"))
con ella estamos obteniendo la suma de los Importes cuando se cumpla que el Comercial sea 'Pepe' o 'Luís' y además para cualquiera de ellos la Zona de trabajo sea 'Centro'.



La explicación de cómo funciona esta fórmula es idéntica a cómo lo hace una matricial, evalúa las condiciones, devolviendo 1 ó 0 en caso de cunplimiento o no. Sobre nuestro ejemplo en esta última operación, el primer registro es el Comercila 'Pepe', así que Comercial="Pepe" devolvería 1 (O VERDADERO), como obviamente no es comercial 'Luís', entonce Comercial="Luís" devolverá 0 (o FALSO), asi que
((Comercial="Pepe")+(Comercial="Luís")) = (1 + 0) = 1
Igualemente con Zona="Centro", para el primer registro es cierto, así que
(Zona="Centro") = 1
Ya podemos operar todo junto
Importe*((Comercial="Pepe")+(Comercial="Luís"))*(Zona="Centro") = 10.94 * (1 + 0 ) * 1 = 10.94
que acumulará sucesivamente con el resto de registros de nuestra tabla.


Y una última posibilidad de la función SUMAPRODUCTO nos lleva a emplearla para conteos, esto es, para sustituir a las funciones CONTAR.SI o CONTAR.SI.CONJUNTO.
La forma es prácticamente idéntica a las anteriores, basta eliminar el campo de suma que empleabamos; entonces si queremos conocer el número de registros que verifican para el comercial 'Pepe' que las Unidades vendidas superan las 60:
=CONTAR.SI.CONJUNTO(Comercial;"Pepe";Unidades;">60")
puede reemplazarse por
=SUMAPRODUCTO((Comercial="Pepe")*(Unidades>60))

sobre nuestro ejemplo el resultado en ambos casos es de 2, es decir, sólo dos registros cumplen simultáneamente las dos condiciones.

De igual forma podemos saber el número de veces que se cumplen el criterio combinado para el Comercial sea 'Pepe' o 'Luís' y además para cualquiera de ellos la Zona de trabajo sea 'Centro':
=SUMAPRODUCTO(((Comercial="Pepe")+(Comercial="Luís"))*(Zona="Centro"))


Y todo esto sin emplear fórmulas matriciales, sencillamente aplicando las mismas normas de éstas, a una función estándar de Excel.

22 comentarios:

  1. Excelente explicación. Me acaba de servir de mucho. Gracias!

    ResponderEliminar
  2. Excelente aportación. Me sirvió para sacar unos datos dentro de unos rangos! Gracias!

    Mario Alberto Ramos

    ResponderEliminar
  3. Como me bajo el archivo?

    ResponderEliminar
    Respuestas
    1. Hola, que tal...
      no hay posibilidad de descarga... sólo sigue las indicaciones del paso a paso.

      Un saludo

      Eliminar
  4. Cómo utilizo esta función para que me calcule por ejemplo la cantidad de productos con estado vendido que el comercial Pepe realizó durante el mes en curso?? Muchas gracias

    ResponderEliminar
  5. Mi tabla es:

    Fecha 10/10/2014
    Comercial: a b c d
    Producto: 1 2 3 4

    ResponderEliminar
  6. Me encantó esta explicación, pues venía utilizando la función SUMAR.SI y tenía que estar anidando sumar.si + sumar.si + sumar.si etc cuando necesitaba que me sumara más de un criterio.

    Ahora tengo otra duda, que sucede si luego quiero que el resultado me lo multiplique por otra columna, por ejemplo:
    Tengo la siguiente tabla:

    FRUTAS PRECIO UNIDADES
    LIMON 5 2
    PERA 4 3
    UVA 4 3
    MANZANA 5 2
    BANANO 6 6

    Entonces necesito que me sume los valores de la columna PRECIO cuando encuentre la fruta "LIMON" o la fruta "PERA" en la columna FRUTAS, es decir que el resultado me daría 9, pero que luego me lo multiplique por la columna UNIDADES, es decir, que me multiplique del criterio "LIMON", el resultado del precio por el resultado de las unidades, en este caso (5*2) y que luego me haga lo mismo con el criterio "PERA" y que finalmente me sume ambos resultados que en este caso sería (5*2)+(4*3) = 22
    Porque intenté hacerlo con una suma producto para cada columna y luego hacer la multiplicación pero lo que me hace es lo siguiente:
    (5+4)*(2*3).

    Te agradecería muchísimo si me puedes ayudar

    ResponderEliminar
    Respuestas
    1. Hola Luisa Fernanda,
      tendrías que realizar el producto completo por las dos columnas para cada condición, algo así:
      =SUMAPRODUCTO(((A2:A6="limon")*B2:B6*C2:C6)+((A2:A6="pera")*B2:B6*C2:C6))

      Saludos

      Eliminar
    2. Hola, muchas gracias por responder, te cuento que estuve ensayando varias formas con base a tu explicación y también encontré esta solución que me parece un poco más corta y me da el mismo resultado:

      SUMAPRODUCTO(B2:B6;C2:C6*((A2:A6="LIMON")+(A2:A6="PERA")))

      Mil gracias por todo!

      Eliminar
  7. Cordial saludo Isamel. Estoy utilizando la sumaproducto en una tabla de tal manera que me aplique la función solo sobre unos rangos de valores. Estaba utilizando así =SUMAPRODUCTO(('box culvert tipo 1'!B10:B84>=0)*('box culvert tipo 1'!B10:B84<1000)*('box culvert tipo 1'!L10:L84)*('box culvert tipo 1'!W10:W84)). Sin embargo lo que pretendo es que los limites se me referencien a una celda que contenga por ejemplo el valor de 0 y el valor de 1000. Como puedo hacerlo?

    ResponderEliminar
    Respuestas
    1. Hola Marcela,
      sería igual, pero cambiando el 0 y el 1000 por las referencias a las celdas en cuestión:
      =SUMAPRODUCTO(('box culvert tipo 1'!B10:B84>=A1)*('box culvert tipo 1'!B10:B84<A2)*('box culvert tipo 1'!L10:L84)*('box culvert tipo 1'!W10:W84))

      siendo A1 la celda con el valor 0 (o el que quieras)
      y A2 la celda con valor 1000 (u otro cualquiera)
      Slds

      Eliminar
    2. Ismael no funciona colocando la celda.

      Eliminar
    3. prueba ejecutando la fórmula matricialmente, i.e., presionando Ctrl+Mayusc+Enter en lugar de Enter para validar.
      Slds

      Eliminar
  8. La uso constantemente para mi trabajo, y nunca la había visto explicada de una forma tan clara. Muchas gracias por el aporte.

    ResponderEliminar
  9. Buenos días, utilizo esta fórmula desde hace años:
    =SUMAPRODUCTO((M5:M622="AB2")*(L5:L622="PASTOR")*(I5:I622<>"SI")*(G5:G622))
    y hoy me encuentro con que me da como resultado #¡VALOR!
    ¿Saben decirme qué pasa?
    Muchas gracias.

    ResponderEliminar
    Respuestas
    1. Buenos días,
      pues a priori no parece haya nada mal en esa fórmula, sumaría los datos de G:G cuando coincida con 'AB2', con 'PASTOR' y sea '<>SI'...
      podría ser que hubiera alguna celda con algún error en las columnas de condiciones.
      Prueba aplicando la función =SUMAR.SI.CONJUNTO(G5:G622;M5:M622;"AB2";L5:L622;"PASTOR";I5:I622;"<>SI")
      A ver qué pasa...
      Saludos

      Eliminar
    2. Hola:
      Efectivamente, había dos celdas combinadas.
      Muchas gracias por tu rápida respuesta, además me aportas una nueva manera de hacerlo.

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.