Hojas de cálculo en Excel - página principal

Medias ponderadas

Hoy os presento un artículo, que aunque no es estrictamente algo que podamos considerar como puramente de Excel, creo que le va a servir a muchos usuarios que utilizan esta popular hoja de cálculo. Se trata de obtener lo que se denomina media ponderada.

La Real Academia Española (RAE), define la media ponderada como el "resultado de multiplicar cada uno de los números de un conjunto por un valor particular llamado su peso, sumar las cantidades así obtenidas, y dividir esa suma por la suma de todos los pesos".

¿Está claro, verdad?. Jajajaja. Probablemente leído así a bote pronto, esa definición lo que hace es confundir más que aclarar, pero si lo vemos con un ejemplo, nos daremos cuenta que es algo bastante sencillo de entender.

Vamos a suponer que hoy día 26-01-2011, efectuamos dos inversiones, una que nos rinde el 7% de interés nominal anual, y cuyo vencimiento es el 31-12-2011, y otra que rinde el 1% de interés nominal anual, y cuyo vencimiento es dentro de 5 días, es decir, el 31-01-2011. En ambos casos, supondremos que los intereses nos los retribuyen al vencimiento de cada inversión.

Si pasáramos esta información a nuestra hoja de cálculo de Excel, nos quedaría algo así:


Si alguien nos preguntara, a qué tipo de interés medio nos retribuyen la inversión, podríamos contestarle dos cosas:

a) Al 4,00% de interés medio, pues esa cifra resulta de hacer la meda de tipos de interés: (7%+1%)/2
b) Nos faltan datos para poder dar una respuesta.

Si hemos elegido al respuesta a), sin duda estaremos cometiendo un grave error, porque entre otras cosas, las dos inversiones no tienen el mismo vencimiento. Piensa por un momento que la inversión 1 vence dentro de 339 días, es decir, todavía queda mucho, mientras que la inversión 2 está prácticamente finalizada, por lo que debemos valorar esta circunstancia, para calcular el tipo medio de interés de nuestras inversiones, pues en caso de no hacerlo, estaremos ponderando más de la cuenta el tipo de interés de la inversión 2 (la que está a punto de finalizar). Llegados a este punto, podríamos reformular nuestro esquema en Excel, de la siguiente forma:


Si nos volvieran a preguntar cual es el tipo de interés medio al que retribuyen nuestras inversiones, la respuesta que podríamos dar, sería una de estas:

a) Al 6,91% de interés medio, pues esa cifra resulta de multiplicar el 7% por los días que quedan hasta el vencimiento de esta primera inversión, es decir 339 días, multiplicando nuevamente el 1% por los días que quedan hasta el vencimiento de esta segunda inversión, es decir, 5 días, y dividiendo todo ello entre la suma de 339 y 5. Es decir, ese tipo medio del 6,91% lo obtendríamos de la siguiente fórmula:


b) Nos faltan datos para poder dar una respuesta.

Como en el caso anterior, si hubiésemos dado como respuesta la a), estaríamos cometiendo un error, pues nos falta el último dato, el importe de cada una de las inversiones (la inversión 1, y la inversión 2), y que es condición necesaria para poder determinar el tipo de interés medio al cual nos retribuyen esas inversiones. En ningún momento hemos dicho que ambas inversiones sean del mismo importe, por eso presuponer ese dato sería un error.

Vamos a suponer que la inversión 1 es de 10.000 euros, y que la inversión 2 es de 5.000 euros. Reformulemos por tanto nuestro esquema en Excel, para tenerlo todo más claro, de un vistazo:


Si ahora nos hicieran la preguntita de marras, nuestra respuesta sería que el tipo de interés medio resultante de estas inversiones, es del 6,96%, pues lo obtenemos de resolver la siguiente fórmula:


En realidad, lo que estamos haciendo, es ponderar (sopesar, o medir de forma proporcional) cada tipo de interés, con respecto al importe de cada una de las inversiones, y con respecto a la fecha de cada uno de los vencimientos. En términos matemáticos, lo que hemos hecho no es más que ponderar la media, o como reza el título de este artículo, calcular la media ponderada.

Si volvemos a mirar esta última fórmula donde obtenemos ese 6,96% de interés medio (ponderado), y releemos nuevamente la definición que da la RAE sobre la media ponderada, estoy seguro que ahora tendremos las cosas mucho más claras, y habremos entendido esta definición.

Como colofón de este artículo, vamos a suponer ahora que tenemos 20 inversiones, en lugar de 2, como era el caso que contemplábamos en el ejemplo. En ese caso, en lugar de hacer una fórmula donde el numerador y el denominador van a ser larguísimos, podemos solucionarlo muy fácilmente con el uso de la función SUMAPRODUCTO. Vamos a verlo con un ejemplo, donde podemos contemplar en la imagen, la fórmula que aparece en la barra de fórmulas:


Podemos construir dos columnas auxiliares, pare ver que efectivamente, la función SUMAPRODUCTO nos arroja el mismo resultado, que una fórmula tradicional:


En realidad, la fórmula SUMAPRODUCTO, lo que va a hacer es multiplicar cada elemento de las matrices que le indiquemos, sumando cada una de las líneas, de tal forma que si por ejemplo ponemos esto:


=SUMAPRODUCTO(C5:C24;E5:E24;F5:F24)

...le estaremos diciendo que haga esto:

=(C5*E5*F5)+(C6*E6*F6)+(C7*E7*F7)+(C8*E8*F8)+(C9*E9*F9)+(C10*E10*F10)+(C11*E11*F11)+(C12*E12*F12)+(C13*E13*F13)+(C14*E14*F14)+(C15*E15*F15)+(C16*E16*F16)+(C17*E17*F17)+(C18*E18*F18)+(C19*E19*F19)+(C20*E20*F20)+(C21*E21*F21)+(C22*E22*F22)+(C23*E23*F23)+(C24*E24*F24)

Es decir, tanto la función SUMAPRODUCTO que hemos puesto, y esta otra fórmula tradicional tan larga que acabamos de poner, hacen exactamente lo mismo.

Espero que os haya sido útil este artículo, y que lo podáis aplicar alguna vez en vuestro trabajo. Como siempre, desde aquí podéis descargar el fichero de Excel, con el ejemplo final que hemos visto en este artículo.



10 comentarios:

Anónimo dijo...

Buenas tardes.
Magnífico tu blog.

Yo tengo un macroproyecto en la mente para hacer una aplicación en excel, access y VB, una especie de estación de análisis de bolsa (quiero descargar cotizaciones, noticias, realizar análisis...)
Probablemente lo abandonaré a medias, como me ha sucedido en otros proyectos personales, pero lo cierto es que lo he comenzado a diseñar con ilusión.
Ahí es donde entra mi consulta. Aunque no soy especialista en excel, tengo un buen blog (el tuyo) y un buen libro para guiarme en muchos de los puntos de mi diseño, pero hay algo que he notado en falta. Sé descargar tablas, pero si quiero descargar noticias necesitaría un código para importar un RSS o un XML de noticias de otras páginas a mi excel.
¿Crees que sería viable? Seguro que después de tantas fórmulas bancarias estás deseando darte un respiro con un reto interesante. Además, será un tema que muchos de tus visitantes encontrarán muy útil.

Muchas gracias de antemano.
Juanma.

Javier Marco dijo...

¿Viable?. Yo diría que sí, pues si se puede generar un xml desde excel, se podría leer también un xml.

A ver si un día tengo ganas e inspiración, pero la verdad es que estoy a tope de trabajo últimamente.

Jorge dijo...

Hola,

No sé si ha salido en alguno de tus post lo que yo necesito.

Tengo una hoja excel que se debe alimentar de los datos de varias celdas de otras hojas de Excel. Esas hojas van teniendo nombres distintos (normalmente cambian con la fecha) pero las celdas que necesito está siempre en la misma posición en todas las hojas. Vamos, que necesito de una serie de informes mensuales coger los datos finales para meterlos en la hoja final que es un resumen y no sé como puedo hacerlo. Hay que recorrer todas las hojas Excel e ir cogiendo los datos de cada una de ellas, pero no tengo muy claro como.

¿Me puedes echar una mano?

Gracias

Jorge

Anónimo dijo...

Me ha sido de gran ayuda.Gracias

Ezequiel dijo...

Hola, ante todo muchas gracias por las miles de ayudas que das a la gente. Te quería hacer una consulta, quizás sabés. Tenés idea de alguna aplicación o programa (gratis) que me permita utilizar excel (o similar) online en tiempo real? Tengo una empresa de venta de artículos de electrónica por internet en la cual anotamos los movimientos de caja y stock en distintas computadoras y queremos que se actualice en el momento y no pisarnos entre nosotros. Estamos utilizando google docs, pero como el sistemita que yo cree utilizó muchas llamadas a otras hojas, me limita esa cantidad y tenemos que estar backupeando la info aprox. cada 4 días. Por otra parte utilizamos office 2003 en red, pero se actualiza como mínimo cada 5 min o cuando se graba en las computadoras. Escuché algunos comentarios que con office 2010 se podía laburar en tiempo real, pero no quiero hacer semejante cambio si no tengo esa certeza. También, si no es molestia, preguntarte si alguno de esos programas se pueden utilizar macros. Espero que sea haya entendido. Sino con mucho gusto te vuelvo a escribir con mas detalle. Saludos, Ezequiel

Javier Marco dijo...

Pues la verdad es que te iba a comentar lo de google docs, pero debido a las limitaciones que comentas, pues queda descartado.

Sinceramente, aparte de docs, no creo que haya nada fiable, porque cualquier cosa que encuentres online, puede durar 2 días, o 2 siglos. No me fiaría yo de una hoja de cálculo online, a no ser que estuviera respaldada por una gran compañía (como en el caso de google docs).

Es por ello, que creo que la mejor opción es la de compartir el libro de excel (desde Excel 95 en adelante se pueden compartir, para ser editados por diferentes usuarios), para evitar pisaros los cambios.

Aquí te dejo un enlace para que veas como compartir libros: http://support.microsoft.com/kb/269860

Y aquí, otro enlace de interés: http://office.microsoft.com/es-es/excel-help/utilizar-un-libro-compartido-para-trabajar-en-colaboracion-HP010096833.aspx

Saludos.

manu dijo...

Hola, por mi nuevo cargo en el trabajo necesito crearme tablas excel para tener datos de produccion a mano, la pregunta es que he creado un libro donde recojo los datos y ahora quiero transportarlos a otro libro, pero solo ciertas celdas que se iran actualizando diariamente,la cuetion es "se puede de una manera facil cambiar solo un dato y que todas las celdas que yo seleccione solo cambie ese dato"

Javier Marco dijo...

Hola Manu.

Aquí te pongo un ejemplo, que lo que hace es obtener la información de la delda A1, de un libro y una hoja de cálculo en concreto:

='[Nombre del libro.xls]Nombre de la hoja'!A1

Solo tienes que cambiar el nombre del libro, y el de la hoja, para que te funcione. Si trabajas con versiones posteriores a Excel 2003, la extensión cámbiala por xlsx

Un saludo.

manu dijo...

Gracias por contestar tan rapido.
como puedes ver lo que me explicas ya lo tengo realizado.
=[Porgramacion.xls]Ref.501001!$L$4
=[Porgramacion.xls]Ref.501001!$L$42
=[Porgramacion.xls]Ref.501001!$L$76
=[Porgramacion.xls]Ref.501001!$L$113
El libro de donde extraigo los datos esta realizado por todos los dias del año y cada Ref. es una pestaña, en todas las pestañas coinciden las celdas donde se recogen los datos,de ahi recojo datos por semanas y por meses.
En el nuevo libro solo quiero coger el acumulado mesunal.
En el nuevo libro tengo los doce meses y cada Ref.hasta ahora iva enlazando las celdas de cada mes una a una. la pregunta seria ¿ se puede en excel seleccionar todas las casillas correspondientes y solo cambiar la referencia y que lo asumieran sin cambiar nada mas?

Pd. Esta es mi primera tabla y tu blog me ayuda bastante.

Un saludo y gracias
Manu

Javier Marco dijo...

Te invito a que plantees tu pregunta en http://www.forosdelweb.com/f90/ (previo registro en el foro), y pegues pantallazos para que sea más sencillo resolver tu problema.

Lo comento, porque entablaríamos una conversación en el blog, con preguntas y respuestas, que exceden la finalidad de este blog.

Espero que no te moleste. Un saludo.