Devolver un valor en Excel si se encuentra en un intervalo especificado - parte 1

Hola, tuve una consulta recientemente de un lector que me preguntaba cómo se podía hacer en Excel para devolver un valor, si el valor especificado se encontraba en un intervalo. 

Veamos cómo hacerlo con un ejemplo

Así que tenemos una tabla de intereses, en la cual queremos asignar a valores de hasta 500, un interés del 1%, a valores entre 501 y 1000 un interés del 0.75%, a valores entre 1001 y 2000 un interés del 0.5% y por último a valores mayores de 2000 un interés del 0.25%.

De tal manera que si introducimos un valor en una celda, queremos que nos calcule el interés a aplicar de acuerdo a esta tabla.

Si es 600, sería 0.75%, y si es 1800 sería 0.50%.

Y cómo haríamos esto?



Pues hay 3 maneras de hacerlo:



1. La más sencilla es utilizando la función BUSCARV

BUSCARV es una función que busca un valor específico en la primer columna de una matriz de tabla y devuelve, en la misma fila, un valor de otra columna de dicha matriz de tabla.

La sintaxis es 

BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;[ordenado])

- valor_buscado (obligatorio): el valor o la referencia que se quiere buscar en un rango

- matriz_buscar_en (obligatorio): El rango de celdas o tabla que contiene la columna de valores y la columna de resultados.

- valor_regresado (obligatorio): Número de columna (dentro del rango) que contiene los resultados.


- ordenado (opcional): Indica si devolverá una aproximación. Si se omite se tomará como VERDADERO, y devolverá una aproximación en caso de no encontrar en valor buscado.


Es este último parametro el que nos va ayudar con nuestro problema. Para ello usaremos la opción VERDADERO o su omisión que es lo mismo.

------------------------------------------------------------------------------------------------------------------------

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.

------------------------------------------------------------------------------------------------------------------------

Imaginemos que nuestra tabla está en el rango A1:C5

Y nuestro valos a evaluar está en la celda A9.

Nuestra fórmula para obtener el interés sería:

=BUSCARV(A9,$A$1:$C$5,3,VERDADERO) o =BUSCARV(A9,$A$1:$C$5,3)

Esta fórmula devolvería:

Valor	Interés
350	1.00%
980	0.75%
1425	0.50%
3500	0.25%

Importante: Para que este método funcione la tabla ha de estar ordenana!

2. La segunda manera es con la combinación INDICE + COINCIDIR


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.

Por otra parte, COINCIDIR busca un valor dentro de una matriz y regresa la posición relativa del valor dentro de esa matriz.

Así que cuando las 2 están combinadas tenemos

 =INDICE(columna que contiene el dato que ti quieres,  COINCIDIR(datos que estás buscando, en qué columna está ese dato,0))


Recomiendo también leer el siguiente enlace para entender mejor el alcance de la combinación entre INDICE + CONCIDIR:

http://elsabiodeexcel.blogspot.com.mt/2015/03/buscarv-izquierda-indice-coincidir.html

COINCIDIR(valor_buscado,matriz_buscada, [tipo_de_coincidencia])

• valor_buscado (obligatorio): Valor que se usa para encontrar el valor deseado en la matriz, y puede ser un número, texto, valor lógico o una referencia a uno de ellos.
• matriz_buscada (obligatorio): Rango de celdas en el que se realiza la búsqueda.
• tipo_de_coincidencia (opcional): Puede ser el número -1, 0 o 1. El argumento tipo_de_coincidencia especifica cómo Excel hace coincidir el valor_buscado con los valores de matriz_buscada. El valor predeterminado de este argumento es 1.

TIPO_DE_COINCIDENCIA	COMPORTAMIENTO
1 u omitido	COINCIDIR encuentra el mayor valor que es menor o igual que el valor_buscado. Los valores del argumento matriz_buscada se deben colocar en orden ascendente, por ejemplo: ...-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO.
0	COINCIDIR encuentra el primer valor que es exactamente igual que elvalor_buscado. Los valores del argumento matriz_buscada pueden estar en cualquier orden.
-1	COINCIDIR encuentra el menor valor que es mayor o igual que el valor_buscado. Los valores del argumento matriz_buscada se deben colocar en orden descendente, por ejemplo: VERDADERO, FALSO, Z-A, ...2, 1, 0, -1, -2, ..., etc.

Como se acaba de ver, se puede utilizar el tipo de coincidencia 1 para nuestro objetivo.

Teniendo en cuenta esto la fórmula a utilizar podría ser:

=INDICE($C$2:$C$5,COINCIDIR(A9,$A$2:$A$5,1))

Lo importante aquí es la parte de COINCIDIR(A9,$A$2:$A$5,1) imaginemos que el valor en A9 es 1850, así pues sería COINCIDIR(1850,(0,501,1001,2001),1)  y 

1001 es el valor más grande que es menor que o igual al valor buscado. Como 1001 es el tercer valor de la matriz, la función devuelve 3.

Importante: Para que este método funcione la tabla ha de estar ordenana!

3. La tercera y última no necesitaría que la tabla estuviese ordenada, pero eso lo dejo para la siguiente entrada

*********************************************************************************************

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)

El Sabio de Excel recibirá una comisión por cada compra del libro que se realice usando el link de a continuación. Esta comisión ayuda (y mucho) a seguir manteniendo esta web. 

****************************************************************************

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.

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.

Consultoría Excel / Cursos de Excel

Subscríbete y recibe todos las entradas por email

Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.

O sígueme por las redes sociales

Facebook, Twitter, Youtube y Google+