domingo, abril 16, 2006

Función SUMAPRODUCTO en Excel (SUMPRODUCT) cuando los rangos tienen distinta orientación (función TRANSPONER).

En notas anteriores vimos una explicación básica de la función SUMAPRODUCTO y como utilizar SUMAPRODUCTO para contar condicional con varias condiciones.
El uso de SUMAPRODUCTO supone que se cumplen dos condiciones obligatorias:
1 – Los rangos son del mismo tamaño, es decir tienen la misma cantidad de miembros
2 – Los rangos tienen la misma orientación, filas o columnas.

A veces sucede que una de las matrices es un rango en una columna y otra matriz es un rango en una fila.

Para sobreponerse a este problema, sin tener que rehacer las hojas de cálculo, podemos utilizar la función TRANSPONER.

La definición de la función TRANSPONER en la ayuda de Excel es la siguiente:



Devuelve un rango de celdas vertical como un rango horizontal o viceversa.
TRANSPONER debe introducirse como una fórmula matricial en un rango que tenga el
mismo número de filas y columnas, respectivamente, que el número de columnas y
filas en una matriz. Utilice TRANSPONER para cambiar la orientación vertical y
horizontal de una matriz en una hoja de cálculo.

Es importante notar que se trata de una función matricial (array function).

Veamos el uso de
TRANSPONER con SUMAPRODUCTO con un ejemplo (apretar el link para decargar el cuaderno)

Supongamos que tenemos esta tabla en una hoja de cálculos



Aquí el cálculo del total del inventario es sencillo, ya que las dos matrices (cantidad y precios) están orientadas en el mismo sentido
=SUMAPRODUCTO(B4:B13,C4:C13)
Pero supongamos que la matriz de Precios esté en un rango en una fila (en nuestro ejemplo en el rango A17:K17). En este caso usamos la función TRANSPONER para convertir la matriz de precios de fila a columna. La fórmula es
={SUMAPRODUCTO(B4:B13,TRANSPONER(B17:K17))}
Debemos prestar atención a los símbolos "{" y "}" al principio y al final de la fórmula. Ya que TRANSPONER es una función matricial, SUMAPRODUCTO debe ser anotada como tal, es decir apretando Ctrl+Shift junto con Enter.
Ya que hemos convertido a SUMAPRODUCTO en una función matricial, podemos utilizar la función SUMA, en forma matricial, de la siguiente manera:
={SUMA((B4:B13)*TRANSPONER(B17:K17))}










Categorías: Funciones&Formulas_, Formulas Matriciales_


Technorati Tags: ,

15 comentarios:

  1. bien jorge si me sirve mucho, pero tengo una interrogante grande, tengo una tabla pero quiero sumar con dos condicionantes, por ejemplo quiero sumar una columna "Q:Q", pero q cumpla una condicion en "L:L", y a la vez cumpla otra condicion en "M:M", si cumple ambas condiciones sumar, he luchado mucho con SUMIF, SUMPRODUCT, y la verdad nose q mas hacer, gracias jorge .

    ResponderBorrar
  2. Hola Rudy, tengo una consulta necesito sumar una FILA C basado en 2 criterios de la fila B cuando B sea = a 'X' o 'Y', he intentado con sumar.si pero nada :( espero tu ayuda, gracias.

    Micky Rios (micronios@gmail.com)

    ResponderBorrar
  3. Hola Micky

    fíjate en el enlace de mi respuesta a Rudy (el que escribe el blog es Jorge, Rudy es un lector). Como está explicado en la nota, lo haces con SUMAPRODUCTO o con fórmulas matriciales.

    ResponderBorrar
  4. Hola Jorge!
    He empezado un trabajo nuevo y voy un poco loca con excel. Mi antecesor en el puesto tiene sus hojas llenas de SUMAPRODUCTO. Se que concatena varios criterios,pero para que se utiliza cuando lleva delante 2 guioncitos -- como por ejemplo la siguiente función: =SUMAPRODUCTO(--($A$27:$A$523=$C$11);--($B$27:$B$523=$D12);E$27:E$523).
    Te agradecería mucho la ayuda. Gracias.

    ResponderBorrar
  5. Hola Inna
    los dos guiones son en realidad dos signos menos. Al anteponerlos a una expresión los que hacemos es multiplicar la expresión por 1. Es decir que en lugar de escribir
    --($A$27:$A$523=$C$11) podríamos haber escrito 1*($A$27:$A$523=$C$11).
    La expresión ($A$27:$A$523=$C$11) genera una matriz de resultados FALSO o VERDADERO. Al multiplicarlo por 1 (con cualquiera de las dos técnicas) Excel convierte los valores VERDADERO y FALSO en 1 y cero respectivamente.
    De esta manera los valores de la matriz E$27:E$523 son multiplicados por 1 o por cero. Sólo los primeros son tomados en cuenta en SUMAPRODUCTO.
    Una observación: en tu fórmula es innecesario tanto el uso de "--" como la multiplicación por 1. Basta con reemplazar los separadores (;) por el operador "*", es decir, multipliar las expresiones entre si

    SUMAPRODUCTO(($A$27:$A$523=$C$11)*($B$27:$B$523=$D12)*E$27:E$523)

    ResponderBorrar
  6. Hola de nuevo Jorge! Gracias por contestar tan rápido. Hay una expresión de tu respuesta que no entiendo. Que quieres decir con:
    "Sólo los primeros son tomados en cuenta en SUMAPRODUCTO." .Gracias.

    ResponderBorrar
  7. Hola Inna
    como su nombre lo indica, SUMAPRODUCTO multiplica entre si los miembros de las matrices y suma los resultados. En los casos en que uno de los miembros es FALSO, Excel lo interpreta como cero. Al multiplicar los elementos correspondientes de las otra matrices por cero, todo el resultado es cera y eso elemento no es sumado. Espero haberme explicado.

    ResponderBorrar
  8. Esta muy claro, te habías explicado bien, me había liado yo sola.Gracias.

    ResponderBorrar
  9. Disculpas por mi falta de conocimientos, pero el Excel me sobrepasa. Yo solo algo me acuerdo del Lotus 123....

    Andaba buscando una solucion a un problema y los encontre en internet. que me pasa ?

    Tengo 24 Hojas que representan meses de gastos, cuyos totales deben resumirse en un planilla por separado, esta planilla es Resumen.

    En cada mes tengo los totales ordenados de esta manera

    Enero 2008
    Gtos 1 Gtos 2 Gtos 3
    100 300 300

    Esos totales mes, debo llevarlos
    a un hoja resumen que los muestra de lo siguiente manera

    Enero 2008
    Gtos 1
    Gtos 2
    Gtos 3

    Cuando creo la primera formula, en la planilla Resumen en Gtos 1 no puedo dejar fija la fila. a
    Al copiar la formula para abajo, no me trae los valores de Gtos 2 y Gtos 3

    Es transponer la funcion que debo usar ?

    Les agradeceria mucho una ayuda, estoy metido en un lio y no puedo salir, ya que tengo que pasar los totales de 24 meses y 48 gastos diferentes y hacerlo todo manualmente seria casi impracticable para mi. Soy bastante corto de vista.

    gracias a todos !!

    ResponderBorrar
  10. Hola Os

    lo que tienes que hacer es mandarme tu consulta al mail que figura en el blog (jorgedun@gmail.com)

    ResponderBorrar
  11. La separación en sumaproducto es ; no ,

    ResponderBorrar
  12. Como se ha explicado en este blog repetidas veces: el separador de argumentos en las funciones de Excel puede ser tanto la coma (,) como el punto y coma (;). Depende de las definiciones regionales del Windows..
    No tiene ninguna relación con esta u otra función.

    ResponderBorrar
  13. Hola,
    Me podría ayudar a comprender esta fórmula. Estoy viendo un ejercicio en donde se crea una tabla de posiciones de equipos de futbol. En colunma Z estan los puntos obtenidos y en la columna Y está el dato del gol diferencia. Los puntos obtenidos se obtienen usando la fórmula =jerarquia. pero para desempatar aquellos equipos que tienen los mismos puntos, se esta usando esta formula que he adjuntado


    =SUMAPRODUCTO(($Z$4:$Z$7=Z4)*(Y4<$Y$4:$Y$7))

    agradezco la colaboración

    ResponderBorrar