JASoft.org

El blog de José Manuel Alarcón Aguín. Programación web y mucho más...

MENÚ - JASoft: JM Alarcón

Cómo localizar valores según rangos en Excel

Esto se sale bastante de la temática habitual de este blog, pero como se trata de algo muy interesante y que le puede servir a mucha gente, lo voy a incluir.

La situación es algo así... Disponemos de una serie de celdas en Excel que nos marcan unos rangos numéricos (o de fechas, que viene a ser lo mismo) con un valor asociado que queremos utilizar según el tramo. Por ejemplo, una tabla de descuentos de producto según el importe de ventas como esta:

BuscarEnRangos_ejemplo

Como vemos en la parte superior tenemos una tabla de descuentos que va por tramos. Así, por ejemplo, si el cliente gasta 3.512€ le corresponderá un descuento del 5% (por estar en el segundo tramo), por lo que al introducir esta cantidad en la parte de abajo, se calculará automáticamente y le daremos el descuento y el precio final.

Parece sencillo ¿verdad?

Yo también lo creía, y supuse que habría alguna manera fácil y directa (alguna función de Excel) para poder conseguirlo en medio minuto. Me equivoqué :-S

Veamos cómo lograrlo...

En este momento podría dar simplemente la fórmula necesaria (que ya adelanto que es larga), pero es muy complicada de entender. Así que es mucho mejor ir por partes y ver uno a uno los conceptos que necesitamos conocer antes de llegar a la fórmula final.

Comencemos...

1.- Averiguar el número de fila en el que está una celda

Bien, lo primero que necesitaremos es saber cómo se averigua en Excel el número de fila de una celda dada. Es decir, una fórmula que si le pregunto por la celda B10 me diga que es la fila 10. Esta función es ROW (o FILA en español, yo uso Excel en inglés, pero es lo mismo). Se puede aplicar a rangos de celdas, devolviendo un rango (o matriz) de números de fila.

2.- Condicionales de múltiples celdas a la vez

Otra cosa que no todo el mundo sabe pero es muy útil, es que en Excel puedes hacer comparaciones de varias celdas a la vez en lugar de ir de una a una. De esta forma podemos obtener matrices de resultados de comparaciones dentro de fórmulas.

Por ejemplo si escribo dentro de una fórmula:

A2:A7<5

obtengo una matriz de TRUE o FALSE por cada celda del rango que he especificado (de la A2 a la A7) según si es menor que 5 o no lo es.

Esto, como veremos enseguida, es muy interesante para lo que nos proponemos.

3.- Convertir booleanos en enteros

Para lo que nosotros necesitamos, mejor que un booleano (verdadero o falso) necesitaremos tener ceros o unos como resultado de las comparaciones de matrices de celdas. Así que para convertirlo en un 0 (falso) o un 1 (verdadero), aunque tenemos varias formas, lo más rápido y directo es usar una doble negación, así:

--(A2:A7<5)

De este modo, el FALSO se convierte en un 0 (al negarlo lo convertimos en un número), y el VERDADERO, que por defecto se convierte en un -1, al negarlo dos veces, se queda como un +1 (dos veces menos es más, como sabemos de álgebra de la escuela).

4.- Multiplicar y sumar matrices de un solo golpe

Esta función, he de confesar que no la conocía en absoluto. Ni me sonaba, Me resultó de gran ayuda la página Chandoo.org, especializada en Excel, que lo explica de maravilla.

La función SUMPRODUCT (SUMAPRODUCTO en español) como su propio nombre indica, lo que hace es multiplicar los elementos correspondientes de las matrices que le pasemos (hasta 255 matrices diferentes, del mismo tamaño), y sumar dichos productos.

Por ejemplo, si tenemos las matrices de antes: {1, 2, 3} y {4, 5, 6}, lo que hace la función es devolvernos el resultado de esto:

1*4 + 2*5 + 3*6

Es decir, devuelve 32.

¡Buff! De momento la cosa pinta muy complicada. ¿Qué tiene que ver todo esto con lo que necesitamos?

Un poco de paciencia, ya falta muy poco :-)

5.- Obtener el valor de una celda a partir de su ubicación puesta en un texto

El último ingrediente que necesitamos es poder obtener el valor de una celda si tenemos su nombre en una cadena de texto. Por ejemplo “C5” o “$C$5” o cualquier otra forma de expresar la ubicación de una celda.

Para obtener el valor de una celda si tenemos su ubicación en texto usaremos la función INDIRECT (INDIRECTO en español). Se le pasa la celda y devuelve el valor, muy sencillo:

INDIRECT(“C5”)

devuelve en nuestro ejemplo el 15%.

La fórmula que usaremos ¡Por fin!

Vale. Ya hemos visto cada una de las piezas del puzle. Ahora ¿cómo las combinamos para obtener lo que necesitamos?

La fórmula utilizada en la celda C10 de la tabla del principio, y que nos devolverá el descuento necesario es la siguiente:

BuscarEnRangos_formula

¡Bufff! Es algo “durilla” ¿verdad? Párate a pensarla a la luz de todo lo que hemos visto en los puntos anteriores...

Lo explico:

  1. El fragmento --(A2:A7<=C9) lo que hace es devolver un 1 o un 0 por cada celda de la primera fila (los rangos inferiores) según si ésta es inferior o igual a la cantidad comprada (celda C9). En el ejemplo de la figura devolvería {1,1,0,0,0,0}. Es decir, tenemos unos por cada rango inferior que nos encaje con la cantidad buscada.
  2. Del mismo modo exactamente, el fragmento --(B2:B7>=C9) devolverá un 1 o un 0 si cada celda de la segunda columna es superior o igual a la cantidad comprada, es decir, en este caso: {0,1,1,1,1,1}. O sea, tenemos un 1 por cada tramo superior que nos encaje, en su posición concreta.
  3. El fragmento ROW(A2:A7) lo que nos devuelve es el número de fila de las filas en las que están los rangos con los que queremos trabajar, es decir, en este caso: {2,3,4,5,6,7}.
  4. Bien, ahora viene lo bueno. Al aplicar SUMPRODUCT sobre las tres matrices anteriores, como la primera solo tiene 1 en los tramos inferiores que encajen, y la segunda tiene 1 en todos los tramos superiores que encajen, al multiplicarlas ambas (como 0 por lo que sea es 0) sólo nos quedará un 1 en el tramo en que coinciden ambas, o sea, ¡el que nos interesa!. En este caso {0,1,0,0,0,0}. Como además la tercera matriz tiene las posiciones de cada fila, al multiplicarla por la anterior, nos deja ceros en todas las posiciones menos en la de la fila que estamos buscando: {0,3,0,0,0,0}. ¡Justo lo que queríamos! Ya sabemos en qué tramo está la cifra de ventas.
  5. Ahora ya sólo queda obtener el valor de la tercera columna de esa fila con INDIRECT concatenándola a “C” (que es la columna), y multiplicarla por la cantidad comprada (celda C9) para obtener directamente el descuento. Es directo.
  6. Le he añadido un condicional para que si no hay nada escrito en la cantidad comprada (o está vacía) que no use la fórmula y deje la celda en blanco.

Aquí te dejo la Excel de ejemplo para que puedas descargarla y probarla.

¿A qué no era tan sencillo? :-)

¡Espero que te sea útil!

José Manuel Alarcón José Manuel Alarcón
Fundador de campusMVP.es, el proyecto de referencia en formación on-line para programadores en lengua española. Autor de varios libros y cientos de artículos. Galardonado como MVP de Microsoft desde 2004. Gallego de Vigo, amante de la ciencia y la tecnología, la música y la lectura. Ayudando a la gente en Internet desde 1996.
Descarga GRATIS mi último libro (no técnico): "Tres Monos, Diez Minutos".
Banner

Comentarios (31) -

je je je
porque se que eres mvp...
que si no te propondría visual basic para aplicaciones
esto está en el límite...

Una solución más "simple" y más flexible es usar la siguiente fórmula en C)


= C9 * INDICE(C2:C7; COINCIDIR(C9; A2:A7; 1))


La explicación de esta fórmula es sencilla. La parte de COINCIDIR(C9; A2:A7; 1) localiza el valor indicado (el de C9) en la tabla Volumen de compras (A2:A7). El parámetro final =1 indica que debe buscar el primer valor de la tabla que se superior al valor de comparación. Si se inserta esta parte de la ecuación completa en, por ejemplo la celda E10 nos permitirá ver qué fila se selecciona.
Una vez hallada qué fila es la adecuada, la función INDICE selecciona el valor de descuento (C2:C7) apropiado. Y basta multiplicarlo por el valor deseado para obtener el valor final.
Un par de detalles adicionales. Es imprescindible que la lista A2:A7 esté ordenada en orden creciente de valores. La columna B2:B7 es innecesaria. La lista A2:A7 marca cada franja de valores, ya que el tratamiento mediante COINCIDIR(valor; lista ; 1) hace la función "buscar valor que es superior a [valor de la lista].
Y una curiosidad: se puede eliminar un valor de la lista A2:A7 y el sistema sigue funcionando correctamente y adecuado a las nuevas franjas de valores.
Espero que este truco os sirva de ayuda en problemas similares.

Spain José M. Alarcón

¡Gracias por la aportación Javier!

Siempre hay varias formas de hacer las cosas, y la verdad es que Excel es mucho más complejo de lo que parece a primera vista...

Saludos!

Mexico Francisco HC

Hola Javier

Me ha sido super útil tu aportación.
Solo quiero comentar que no me funcionaba la formula y creo que es porque ha faltado indicar que al cerrar la fórmula debemos de ingresarla de forma matricial (CTRL+SHIFT-ENTER) para que regrese el resultado.

Muchas gracias

Hola javier:
No funciona tu codigo, algo debiste haber hecho mal.

Estimado a mi me funcionó bien tu formula pero necesito comparar 2 numeros con 2 rangos distintos y hallar el valor que representa ambos valores

Ni te lo imaginas José Manuel, lo complejo que es.

Una de las ventajas de este enfoque es que puedes tener un elevado número de franjas de comparación, sin necesidad de complicar la ecuación, y con unas fórmulas fáciles de usar y entender.

Saludos. Muchísimas gracias por el aporte. Me ha servido de mucho, de verdad, aunque te propongo algo mas simple: =BUSCARV(C9;A1:C7;3;1)*C9

Spain José Manuel Alarcón

Hola,

Yo creo que esa fórmula no sirve porque BUSCARV busca un valor concreto en la primera columna de un rango de celdas, y nosotros queremos buscar un valor que está comprendido entre los valores de dos celdas, algo que no es lo mismo. ¿no es así?

Saludos.

Saludos José Manuel.

Es el último dígito de la función buscarv el que determina si el valor a buscar es "concreto" o "aproximado". (0 y 1 respectivamente.). Al terminar en 1, determina que a todas las compras comprendidas entre 1000€ y 3000€ del ejemplo le corresponde el 0% de descuento, y lo mismo con el resto de las cantidades. Ya lo he probado y no falla metas la cantidad que metas. Únicamente cambié la cantidad inicial de 1000 a cero, pero pq me pareció más lógico.

Agradecerte una vez más el tiempo que dedicas a mantener este sitio y el que compartas tus conocimientos incluída la posibilidad de bajar los archivos para practicar. La doble resta para el tema de los booleanos y la función sumaproducto no los conocia.

Un saludo de nuevo.

Excelente aporte, increiblemente funciona la funcion buscarv, tal como lo describes, he batallado por mucho tiempo con esta formula para lograr esto y nunca habia podido lograrlo aunque el aporte de José Manuel me sirvio para aprender cosas que no sabia, esa abstraccion esta por encima de mis capacidades mortales. Igualmente gracias por los aportes.
Saludos

Saludos Adrian. Yo también llegué aquí buscando una solución. No comprendía la explicación dada, pero como funcionaba, me valía. Me bajé el ejemplo -de nuevo agradecer al webmaster esa posibilidad- y me puse a trastear la formula a ver si sacaba algo mas en claro. Al tiempo las piezas encajaron y me salio lo del buscarv. Con el último argumento de la función en 1, busca el dato hasta que, o bien se pasa -como en este ejercicio- o no lo encuentra. En ambos casos devuelve siempre el resultado de la fila inmediatamente superior. Un saludo

Hola a todos:
Tambien se puede resolver de la siguiente manera.
=SUMAPRODUCTO((C9>=A2:A7)*(C9<=B2:B7)*C2:C7)*C9
Saludos

Chile Ricardo I.

Debo decir que este análisis y solución me ha traído una de las mejores noticias para completar un par de tablas de conversión. Por fuerza debía hacer yo a mano el ejercicio de convertir los valores de referencia y los buscados para hacer funcionar "buscarv", pero con esto todo se me simplifica. No he intentado la sugerencia Javier, pero experimentaré más tarde.
Muchísimas gracias José Manuel por tu generosidad al publicar esto.
Saludos desde el sur

Argentina Alejandro

Qué tal José, te pido este favor. Busco algo muy similar a la fórmula recién explicada, hice una gran esfuerzo en adaptarla pero no me sale. Yo tengo un listado de intervalos, a cada intervalo le corresponde una calificación, al cargar en una celda un valor, la fórmula tiene que evaluar a qué intervalo corresponde este valor, y en base a eso arrojar en otra celda la calificación correspondiente al intervalo en que cayo el valor.
Por ejemplo
Min         Max          Calificación
0              10              1
11            20              2
21            30              3

Si cargo 15 en una celda, debe arrojar en otra 2.
Muchas gracias por el blog.
Saludos.

Hola, espero no sea muy tarde para contestar, o si lo es, al menos que le sirva a alguien más que llegue a este sitio en busca de respuesta a algo similar.
Alejandro, a veces tenemos la respuesta enfrente de nosotros y no nos damos cuenta. Con la fórmula que utiliza la función BUSCARV (posteada por elcano7, crédito para él), y modificándola un poco, obtienes el resultado que estás buscando.
Aquí la fórmula, colócala en la celda (C6): =BUSCARV(C5,A2:C4,3,1).
La fórmula supone que tus encabezados están en A1:C3, que los rangos están en A2:C4, la puntuación obtenida está en la celda C5 (donde cargas el valor 15) y la finalmente la fórmula para obtener la calificación está en la celda C6, dando como resultado 2

También se puede hacer con la función "SI" con varias condiciones, pero encuentro que la mejor solución fue la =BUSCARV.

Cordial saludo. Viendo tu fórmula me ha surgido una duda de un cálculo manual que hacemos en Agility (Un deporte canino) para hacer ciertos cálculos, y que implica identificar el tiempo del mejor perro. Voy a mostrarlo primero

Perro   Total   Tiempo
Ion        0         35
Nix        5         22
Firulais 0         30
Rex       10       31

A primera vista, el mejor tiempo es el de Nix (22 segundos), pero en Agility el mejor perro es el que menos puntaje de faltas acumula, por lo que los mejores perros son Firulais e Ion, pero como el tiempo de Firulais es 30 segundos, y el de Ion es 35 segundos, el mejor perro es Firulais y el mejor tiempo es 30. He intentado varias combinaciones pero no encuentro un modo de obtener ese dato. Si no es mucha molestia ¿Tienes alguna idea?

Gracias

Spain José Manuel Alarcón

Lo siento, no soy un experto en Excel, precisamente por eso puse este post, sobre todo para acordarme yo de cómo lo había hecho. Para este tipo de cosas más complejas yo uso una base de datos y consultas SQL, algo mucho más flexible y potente que una hoja de cálculo. En Excel quizá lo mejor para algo así sea usar algún filtro de columnas y listo.

Quizá alguno de los que ha comentado con soluciones alternativas y que controlan mucho más que yo del tema pueda aportar una solución.

Saludos.

Estimado:
Quizas alguien mas pueda ayudarte, pero deberias explicar mejor lo que necesitas.
Saludos

Muchas Gracias Javier, Jose Manuel, me resulto excelente para calcular un precio por servicio notarial según valor de inmueble u otra transferencia, lo único que hice fue agregar por criterio una división para que me de el valor de la celda resultado.

Valor inmueble: "E3"  Precio Servicio Notarial:  =(E3*INDICE(E6:E57,COINCIDIR(E3,A6:A57,1)))/E3

Rango
1 - 10000             150.00
10001 - 20000    170.00
20000 - 30000    190.00
30000 - 50000    210.00
......

Mexico luis balcazar

MUCHAS GRACIAS A TODOS POR SUS COLABORACIONES. HE ECHO TODAS Y TODAS RESULTAN BIEN. AUNQUE LA MAS PRACTICA ES LA DE "BUSCARV"

Excelente, creo que con eso puedo resolver un problema ya que de esta forma encuentra la fila, mi problema es el siguiente y no se como resolverlo.

Min                  Max               Modo        Región
4491000000  4491009999  CPP           AGS
4961000000  4961000127  FIJO    AGS
4961000128  4961000999  FIJO           AGS
4951001000  4951001299  FIJO           AGS
4951002000  4951002299  FIJO           AGS
4951000000  4951000499  FIJO          AGS
4651009000  4651009999  MPP  AGS
4651000000  4651008999  CPP          AGS
6461000000  6461009999  CPP          BC
6161000000  6161001999  FIJO          BC
6161002000  6161009999  FIJO          BC
6861002000  6861009999  CPP          BC
6861001000  6861001999  CPP          BC

En la F1 ingreso un valor (4491000001) que está dentro del primer rango
Necesito que en la F2 me regrese el texto de modo (fila c)
y en la F3 me de el texto de región (fila d)

Le he dado vueltas por semanas y no logro tener el valor

by Jose M. Alarcon

Como digo, no soy experto en Excel, pero si no he entendido mal lo que preguntas, con la función BUSCARV es directo hacer esto...

Allan Paz

No, con buscarv no me queda porque lo que tiene que hacer es buscar el valor exacto, lo que necesito es que busque dentro del valor, es decir, ingreso un número, tiene que buscar si ese número está dentro de (ejemplo que busque 123) dentro de la fila A que inicia en 100 y b que termina en 200.
Si 123 está dentro de esos rangos que me regrese el valor de la columna C
pero con buscarv busca valores exactos no???

by Jose M. Alarcon

Tienes razón, no lo entendí bien. Pues entonces te remito al contenido del post porque va exactamente de esto...

Muchas gracias, cual es el post?

Hola:
Me encuentro con un problema. Tengo una tabla en la que introduzco notas de mis alumnos, pero en un punto determinado, hay cuatro asignaturas de las que mis alumnos solo pueden escoger una. En mi tabla tengo las cuatro asignaturas y le asigno la nota de cada evaluación a la asignatura que cada alumno cursa. Hay alumnos que cursan la Asign.-1, otros la Asign.-2, otros las Asign-3 y otros la Asign.-4. Es decir, cada alumno tiene un rango de cuatro casillas de las cuales solo una casilla tiene nota y las otras tres están en blanco.
Quisiera una fórmula que recogiera la nota de esa celda (cada alumno tiene la nota en una celda diferente, la correspondiente a su asignatura) y la depositara en otra celda en la que hago las medias. ¿Qué fórmula puedo usar que contemple esas cuatro celdas y recoja solo la nota de la única celda con número?
Muchas gracias
Juan

Esta muy enredado, no?

que tal si lo resumimos a =C9*BUSCARV(C9,A2:C7,3) e igual trae el mismo resultado

buenas tardes,

mire me encuentro que quiero hacer una tabla que me calcule las tarifas de un hotel, en diferentes periodos, es decir

del 20/8 al 31/8 : 50 eur
del 01/9 al 02/9 : 30 eur

si estoy del 31/08 al 03/09 me debería calcular 80 eur, 50 el 31/8 y 30 el 1/9.

pero no lo consigo, creo que la formula tiene  que ver con la funcion BUSCARV pero lo pongo y me da siempre error "Nombre"## tengo excel 2010 ????

he estado utilizando por ejemplo argumentos como: =BUSCARV(B2,C2:E7,3) pero no lo consigo

ruego una ayuda

gracias
alejandro

Buenas tardes, tengo un problema con unas formulas, deseo saber una formula que me busque los nombres del departamento, provincia, distrito, segun el codigo de los mismos en la tabla de codigos de ubigeo departamento , con respecto al nombre del departamento encontre la formula con buscar, pero para el nombre de la provincia aun no lo he logrado ya que creo que tendria que coincidir dependiendo el departamento para que recien consulte el codigo de provincia. ojala me hallan podido entender.

Cod.  Departam.    Cod  Provincia.  Cod. Distrito
01                    01                    01  
01                    01                    02  
01                    01                    03  
01                    02                    01  
01                    02                    02  
01                    02                    03  
01                    02                    04  
02                    01                    01  
02                    01                    02  
02                    02                    01  
02                    02                    02  


códigos de ubigeos departamentos

Cod.  Departam.    Cod  Provincia.  Cod. Distrito
01  Amazonas  01  Chachapoyas  01  Chachapoyas
01  Amazonas  01  Chachapoyas  02  Asunción
01  Amazonas  01  Chachapoyas  03  Balsas
01  Amazonas  02  Bagua          01  Bagua
01  Amazonas  02  Bagua          02  Aramango
01  Amazonas  02  Bagua          03  Copallin
01  Amazonas  02  Bagua          04  El Parco
02  Ancash          01  Huaraz          01  Huaraz
02  Ancash          01  Huaraz          02  Cochabamba
02  Ancash          01  Huaraz          03  Colcabamba
02  Ancash          01  Huaraz          04  Huanchay
02  Ancash          02  Aija                  01  Aija
02  Ancash          02  Aija                  02  Coris
02  Ancash          02  Aija                  03  Huacllan
02  Ancash          02  Aija                  04  La Merced
02  Ancash          02  Aija                  05  Succha
02  Ancash          03  Antonio Raymondi  01  Llamellin
02  Ancash          03  Antonio Raymondi  02  Aczo
02  Ancash          03  Antonio Raymondi  03  Chaccho

esperando sus comentarios y agradeciendo de antemano

No se aceptan más comentarios