http://elsabiodeexcel.blogspot.com.mt/2016/05/devolver-valor-excel-intervalo-rango.html
En esa entrada vimos 2 maneras de hacerlo, una utilizanda la función BUSCARV y la otra utilizando la combinación de funciones INDICE + COINCIDIR.
Los 2 métodos son muy buenos, pero requieren de un requisito indispensable.
Este requisito es que la tabla donde se indican los intervalos ha de estar ordenada.
Si esta tabla no estuviese ordenada no valdría ninguno de los 2 métodos aprendidos en la entrada anterior.
Así que hoy vamos a ver un tercer método para rectificar ésto, y es que con este método la tabla no ha de estar ordenada.
------------------------------------------------------------------------------------------------------------------------
RECUERDA QUE:
Tú puedes colaborar para seguir manteniendo este blog de una manera gratuita haciendo varios clics en los anuncios de la página. Como referencia, 2 clics por artículo. Muchas gracias por querer colaborar.
------------------------------------------------------------------------------------------------------------------------
Tú puedes colaborar para seguir manteniendo este blog de una manera gratuita haciendo varios clics en los anuncios de la página. Como referencia, 2 clics por artículo. Muchas gracias por querer colaborar.
------------------------------------------------------------------------------------------------------------------------
INDICE es una función que devuelve el valor de un elemento de una tabla o matriz seleccionado por los índices de número de fila y de columna.
La función SUMAPRODUCTO es una de esas funciones en Excel que merece la pena entender bien porque puede facilitar mucho las cosas.
Sintaxis y uso:
Vayamos primero a ver su sintaxis:
SUMAPRODUCTO(matriz1, [matriz2], …)
matriz1 (Obligatorio): Primera matriz que se desea multiplicar y después sumar.
matriz2 (Opcional): Segunda matriz que se desea multiplicar y después sumar.
En realidad, esta función son dos funciones en una ya que nos permite realizar la multiplicación de los valores pertenecientes a las matrices proporcionadas para obtener su producto y posteriormente realiza la suma de todos esos productos.
Conviene mencionar que las matrices que se proporcionan como argumentos deben tener las mismas dimensiones o de lo contrario la función SUMAPRODUCTO devolverá el error #¡VALOR!.
Por ejemplo, digamos que tenemos las siguientes 2 matrices:
Sumaproducto
Matriz1: A1:A3 = {2,10,-1}, matriz de 3 filas por 1 columna (3x1)
Matriz 2: B1:B3 = {4,1.5,3}, matriz de 3 filas por 1 columna (3x1)
Ahora vemos qué hace esta función.
=SUMAPRODUCTO(A1:A3,B1:B3) = {2*4+10*1.5+(-1)*3}= 20
Asi que lo que ha hecho es multiplicar A1 por B1, A2 por B2 y A3 por B3 y luego a sumado esos resultados.
Bueno pues una vez entendido como funciona la función INDICE y la función SUMAPRODUCTO veamos cuál sería la fórmula a emplear:
=INDICE($C$2:$C$5, SUMAPRODUCTO(--(A9<=$B$2:$B$5), --(A9>=$A$2:$A$5), FILA($A$2:$A$5)-1))
Veamos cómo funciona esta función.
Primero veamos que nuestra tabla de intervalos es la siguiente:
y nuestra celda a evaluar es A9.
Lo que se quiere ver es si la celda A9 cae en unos de los intervalos de las columnas A y B, se ha de devolver el interés adecuado de la columna C.
Veamos pues por partes cómo funcionan los argumentos de la función SUMAPRODUCTO.
Empezamos con la parte (A9<=$B$2:$B$5)
Esta parte en si es una fórmula de matriz.
Si se introduce esta fórmula en una celda, y se presiona CTRL + SHIFT + ENTER, se activa la fórmula de matriz.
Si la celda A9 es, por ejemplo, 350, tendremos lo siguiente:
(350<={500,1000,2000,10000})
y por supuesto 350 es menor o igual que todos eso valores con lo que esta fórmula matricial o de matriz devolverá lo siguiente:
={VERDADERO;VERDADERO;VERDADERO;VERDADERO}, una vez pulsados F2 + F9.
Si delante de esa fórmula introducimos --, ésto convertirá los VERDADEROS en 1s y los FALSOS en 0s. Por lo que:
--(A9<=$B$2:$B$5) = {1;1;1;1}
Veamos ahora la segunda parte: =(A9>=$A$2:$A$5)
Al poner los valores tenemos =(350>={0,501,1001,2001}) que produce el siguiente resultado. {VERDADERO;FALSO;FALSO;FALSO}, y una vez poniendo el -- delante tenemos que =--(A9>=$A$2:$A$5)={1;0;0;0}
Por último tenemos la parte de =FILA($A$2:$A$5)-1 que devuelve la matriz {1,2,3,4}. El -1 se coloca al haber encabezados.
Así que tenemos:
=SUMAPRODUCTO(--(A9<=$B$2:$B$5), --(A9>=$A$2:$A$5), FILA($A$2:$A$5)-1)
se convierte en
=SUMAPRODUCTO({1;1;1;1}, {1;0;0;0}, {1,2,3,4}) = (1x1x1) + (1x0x2) + (1x0x3) + (1x0x4) = 1
Ahora traemos la función entera:
que se convierte en:
=INDICE({0.01;0.0075;0.005;0.0025}, 1) = 0.01 = 1%
Bueno pues ya hemos visto la tercera manera, y sin lugar a dudas más difícil de poder hacerlo.
Un saludo
Oscar
Formato: PDF Libre de DRM, Descarga INMEDIATA.
Datos Técnicos: 476 Páginas + Compendio de Archivos
Precio: $ 32 95 USD (Dólares Americanos)
Bueno pues ya hemos visto la tercera manera, y sin lugar a dudas más difícil de poder hacerlo.
Un saludo
Oscar
*********************************************************************************************
El Sabio de Excel les quiere recomendar un nuevo ebook que les explicará todo lo que necesitan saber sobre Power Pivots.
EL ADN DE POWER PIVOT
Guía paso a paso sobre Power Pivot, construcción de modelo de datos y el lenguaje DAX, con orientación a inteligencia de negocios.
Formato: PDF Libre de DRM, Descarga INMEDIATA. Datos Técnicos: 476 Páginas + Compendio de Archivos Precio: $ 32 95 USD (Dólares Americanos)
****************************************************************************
Te ha gustado el artículo?
No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:
1. Puedes invitarme a un café pinchando en la siguiente imagen.
2. Puedes hacer clic en los anuncios mostrados en la página
3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.
No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:
1. Puedes invitarme a un café pinchando en la siguiente imagen.
2. Puedes hacer clic en los anuncios mostrados en la página
3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.
Quieres aprender más en Excel o tienes algún problema que necesita solución?
Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.
No hay comentarios:
Publicar un comentario