martes, 9 de junio de 2009

Sumaproducto saltando celdas en blanco

Descargar el fichero: sinblancos.xlsx

Queremos efectuar una operación típica con la función SUMAPRODUCTO pero saltando los elementos cuyo valor en la columna contigua esten en blanco. El supuesto práctico es el siguiente: Tenemos un proveedor habitual que nos suministra los productos A, B, C, D y E. Para conocer el importe total de la factura multiplicamos el precio unitario de cada producto por el número de productos que necesitamos adquirir. Surge un nuevo proveedor que nos puede suministrar todos los producto salvo uno de ellos, el producto D. Queremos comparar el importe de la factura de ambos proveedores para determinar el más económico. El problema es que del proveedor habitual debemos descontar de la factura el producto D, para que la comparación sea homogénea.

La función utilizada habitualmente es:

=SUMAPRODUCTO(matriz1;matriz2;matriz3; ...)

esta función multiplica los elementos de las diferentes matrices implicadas y luego los suma para obtener el total.

En nuestro caso, podemos calcular la factura completa del proveedor habitual de la siguiente forma:

=SUMAPRODUCTO(Precio;Cantidades)

previamente hemos nombrado rangos. El resultado obtenido será de 475.000 €.

Para el nuevo proveedor la fórmula utilizada será:

=SUMAPRODUCTO(Cantidades;Nuevo)

que supone un importe total de 375.000 €.

Ambas cantidades no son comparables, ya que no son homogéneas. Para el proveedor habitual estamos incluyendo todos los productos, pero para el nuevo proveedor no se incluye el producto D, ya que no lo suministra.

Para poder recalcular la factura total del proveedor habitual en términos homogéneos debemos eliminar el producto D. Esto se hace con una función matricial:

=+SUMA(Cantidades*Precio*(Nuevo<>0))

Recordar que esta función se valida con Control + Mayúsculas + Intro

La condición (Nuevo<>0) nos proporciona el siguiente Array:

{VERDADERO\VERDADERO\VERDADERO\FALSO\VERDADERO}

Recordar que en informática VERDADERO es un 1, y FALSO es un 0. Por tanto el vector es:

{1\1\1\0\1}

que al multiplicarse por los otros vectores consigue excluir el producto D.


7 comentarios:

  1. Esta muy bueno el ejemplo, solo cuando defines los rangos en el archivo excel adjunto sinblancos se hace referencia a la hoja 1, y en la presentacion se muestra hoja 4,

    ResponderEliminar
  2. SAludos el coste actual no deberia dar 475.00 que es la sumaproducto de las columnas C y D

    ResponderEliminar
  3. Hola Jose.

    1º Celebro que te guste el blog
    2º Ya he modificado la imagen y en los nombres de rango ya figura Hoja1.
    3º Sumaproducto de todos los valores efectivamente da un valor de 475.000. Pero en este caso lo que deseamos es poder comparar dos ofertas de proveedores, y para que sean comparables en ambas debemos tener los mismos artículos. Esto nos obliga a que para ambos proveedores se consideren los mismos artículos. Esto hace que para el proveedor antiguo el resultado sea 375.000 ya que el articulo D no se considera en el cálculo. De esta forma son comparables los dos presupuestos.

    Un saludo.

    Adolfo Aparicio

    ResponderEliminar
  4. muy agradecido por tu pagina
    me pondre en practica para aprender a fondo ...

    saludos de Chile

    ResponderEliminar
  5. hola Adolfo, me encanto tu blog... y resalto a la maxima potencia tu buena voluntad, y paciencia enseñando..
    gracias...
    mi pregunta es que este ejemplo se acopla a lo que necesitaba,
    necesitaba sumar con tres condiciones, si se cumple, entonces debe de sumar.. ahi es el problema.. que cuando mando a sumar con el ultimo rango.. aunque si cumple las condiciones me arroja 0... esta es mi formula:
    =SUMAPRODUCTO((volqueta=$C$12)*(mestrabajoorden=$D$9)*(TRABAJOorden=$H$9)*m3KmIf02)
    donde m3KmIf02 es la columna donde estan los datos que quiero sumar si solo se cumple esas condiciones.
    pero da el caso que me da resultado 0.. que crees que suceda..

    Saludos desde Ecuador.. felices fiestas.

    ResponderEliminar
  6. hola nuevamente yo, si puedes ayudarme te lo agradeceria mucho... sucede que ya me di cuenta en que falla la formula, sucede que el rango km3If02 es el resultado de unas formulas que complen ciertas condiciones, por lo tanto hay algunas que son error ocultadas para que no se vean, y solo se vizualicen las correctas, ahora si suprimo esos datos aparentemente vacios, funciona la formula, mientras tanto ... que puedo hacer.. ?

    ResponderEliminar
  7. Cordial saludo para todos las personas que acceden a este fabuloso blog.
    A Adolfo Aparicio mis agradecimientos porque he encontrado excelentes respuestas a inquietudes que he tenido, llevado de la mano; como algunos decimos "me le quito el sombrero".
    Como aporte y de acuerdo a lo que Adolfo nos ha adjuntado en los diferentes meses y años, creo que la respuesta para Hada Heavy la puede encontrar en el archivo adjuntado sobre "funciones matriciales" que se encuentra en el mes de noviembre de 2008, hoja del archivo "Errores"

    Un saludo

    avalderrama

    ResponderEliminar