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

domingo, abril 16, 2006

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: ,

16 comments:

rudy garcia,  11 julio, 2007 03:43  

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 .

Jorge L. Dunkelman 11 julio, 2007 09:06  

Hola Rudy,

como contar y sumar con varias condiciones lo explico en la nota sobre Sumar y contar con varias condiciones en Excel:SUMAR.SI, CONTAR.SI y SUMAPRODUCTO

Anónimo,  12 mayo, 2008 19:04  

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)

Jorge L. Dunkelman 12 mayo, 2008 23:03  

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.

Inma,  05 septiembre, 2008 00:36  

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.

Jorge L. Dunkelman 05 septiembre, 2008 08:38  

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)

Inma,  05 septiembre, 2008 17:31  

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.

Jorge L. Dunkelman 05 septiembre, 2008 18:32  

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.

Inma,  16 septiembre, 2008 21:32  

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

Os 21 septiembre, 2008 17:06  

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 !!

Jorge L. Dunkelman 21 septiembre, 2008 20:13  

Hola Os

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

Anónimo,  10 octubre, 2013 21:40  

La separación en sumaproducto es ; no ,

Jorge Dunkelman 11 octubre, 2013 08: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.

Miguel 23 enero, 2014 19:47  

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

Juan Isaza 06 diciembre, 2016 16:16  

Genial, muchas gracias.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP