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

Buscar valores en un rango de datos

Hace poco, recibíamos una consulta de un usuario en nuestra página de facebook, en la que nos preguntaba como localizar determinados valores en un rango de datos. Concretamente, lo necesitaba para determinar la tarifa aplicable por un servicio de transporte o mensajería, en función del peso del envío.

Vamos a ver como resolver tan interesante cuestión. Para ello, como siempre, ilustraremos la explicación con un sencillo ejemplo. Supongamos que tenemos la siguiente tarifa de transportes, para las localidades que se indican en la tabla adjunta:


Supongamos ahora, que queremos saber cuanto nos costaría enviar un paquete que pesa 70 kgs. a Sevilla. Visualmente, lo tenemos fácil, pues en la tabla tenemos que localizar la fila correspondiente a los 70 kgs. y la columna correspondiente a Sevilla. Eso nos dará como resultado, que el precio de ese transporte es de 94,00 euros, dólares, pesos, o cualquier otra unidad monetaria con la que trabajemos. Pero claro, queremos obtener ese dato, de forma automática, simplemente informando en algún sitio, del destino de la mercancía, y del peso.

Para ello, montaremos esta plantilla de Excel, donde a través de dos listas de validación, podremos seleccionar el destino y el peso del producto, y una vez seleccionados estos datos, debajo nos aparecerá el importe del envío.


Para calcular el precio, todo es muy sencillo, con esta fórmula:


=SUMAPRODUCTO((B6:B15=D21)*(C5:F5=D20)*(C6:F15))

Esta fórmula, lo que viene a hacer es buscar en el rango de celdas comprendido entre B6 y B15, el dato informado en la lista de validación de D21, es decir, el peso del paquete que enviaremos. Seguidamente, nos busca en el rango de C5 a F5, la localidad informada en la lista desplegable de la celda D20. Y por último, con esos dos datos, lo que hace la fórmula es localizar el importe en el que confluyen ambos datos (peso y localidad), dentro del rango comprendido entre C6 y F15. Como veis, no es nada complicado.

También obtendremos el mismo resultado, si utilizamos las funciones INDICE y COINCIDIR, en lugar de SUMAPRODUCTO, de la siguiente forma:

=INDICE(C6:F15;COINCIDIR(D21;B6:B15;0);COINCIDIR(D20;C5:F5;0))

En este caso, la fórmula la leeríamos más o menos así:

Busca en la matriz de datos que hay entre C6 y F15, la fila cuyo número es el resultado de aplicar la función COINCIDIR(D21;B6:B15;0), y cuya columna es el resultado de aplicar la función COINCIDIR(D20;C5:F5;0).

COINCIDIR(D21;B6:B15;0) nos indica que posición ocupa en valor de la celda D21, dentro del rango de datos comprendido entre B6 y B15. Una lectura similar se podría hacer de COINCIDIR(D20;C5:F5;0), pues nos indica la posición que ocupa el dato de D20, dentro del rango de datos comprendido entre C5 y F5.

Esto puede parecer algo complicadillo, pero si lo imprimís y lo leéis de nuevo, teniendo la hoja de cálculo de Excel delante (la que podéis descargar al final de este artículo), os será mucho más fácil de “digerir”.

Esta segunda opción donde utilizamos las funciones INDICE y COINCIDIR, nos sería especialmente de utilidad, en el caso de tener datos de texto en la matriz, en lugar de importes, pues la función SUMAPRODUCTO nos daría error, al no tener datos numéricos.

Para hacerlo más elegante, en el ejemplo que podrás descargarte al final de este artículo, hemos puesto un formato condicional a las celdas, para que nos coloree de azul la celda con la tarifa, tal y como se muestra en el siguiente ejemplo:


A este tipo de localización de datos dentro de un rango de datos (o una matriz de datos), lo vamos a llamar localizar datos en una matriz, con datos de entrada exactos. Es decir, introduciremos los datos exactos de la localidad y sobretodo el peso del producto a enviar, para localizar el valor en la tabla. De hecho ya habíamos visto alguna que otra utilidad para localizar datos dentro de una tabla, utilizando algunos ejemplos de funciones personalizadas de bases de datos por aquí, y más funciones personalizadas de bases de datos, por este otro lado.

Llegados a este punto, nos encontramos con que la realidad del día a día es bien distinta, pues ese tipo de tarifas no suelen proporcionárnoslo así los transportistas de paquetería. ¿Verdad que cuando enviamos un producto, la tarifa de transportista nos informa de un peso por tramos?. Es decir, el transportista nos cobra en función de si el peso está dentro de un rango (o baremo) de pesos. Por eso, la tabla anterior no nos vale, y el más claro ejemplo es el siguiente: ¿cuánto nos costaría enviar 45,46 kgs. a Sevilla?. Pues siendo estrictos, a la vista de esa tabla anterior, no lo sabemos, pues no aparece ese peso en la tarifa.

Para solucionar esto, vamos a imaginar que la tarifa del transportista es esta otra:


En este caso, ya vemos que la cosa es mucho más realista, pues el transportista, nos va a ofrecer un precio, para un rango determinado de pesos, a cada localidad. En el ejemplo, podemos ver como la empresa de transportes nos va a cobrar 93 euros, dólares, pesos, o cualquier otra unidad monetaria con la que trabajemos, si llevamos entre 60,01 kgs. y 70 kgs., a Madrid. Es decir, si solicitamos que la empresa de paquetería nos lleve un paquete a Madrid, que pesa 62 kgs., nos cobrará lo mismo que si nos lleva 68 kgs.

A este tipo de localización de datos dentro de un rango de datos (o una matriz de datos), lo vamos a llamar localizar datos en una matriz, con datos de entrada dentro de un rango de datos.

Para localizar la tarifa correspondiente a un envío de determinado peso a una localidad, montaremos una tabla como esta, donde la localidad será una lista de validación, y el peso lo introduciremos manualmente:


Para calcular el precio, solo nos quedará aplicar esta fórmula:

=DESREF(C5;COINCIDIR(D21;B6:B15);COINCIDIR(D20;D5:G5))

¿Cómo se lee esta formulita?. Pues de una forma no muy complicada. Las funciones COINCIDIR ya las hemos explicado unos párrafos más arriba, y nos indican tanto la fila donde está el peso seleccionado, y la columna donde está la localidad elegida. La función DESREF, simplemente nos desplaza tantas filas y columnas como nos indican las funciones COINCIDIR, partiendo desde la celda C5.

Como veis, tenemos una forma bastante sencilla para localizar valores en una tabla, tanto, si se trata de valores exactos, como si se trata de valores dentro de un rango.

Desde aquí podéis descargar el fichero de Excel, con todo lo que hemos visto en este artículo.



28 comentarios:

David dijo...

Antes que nada, ¡muchisimas gracias por la información compartida! Personalmente, no tenia conocimiento de las posibilidades de las macros, y ahora ya me estoy armando un pequeño "Task Manager" bastante completito ;) (y el objetivo final en un PIM... mmm...)
Ahora la consulta:
Tengo un rango de datos definido por con un nombre (supongamos que se llama "Mi_Rango"), y por ejemplo, el rango va de B3 a F8.

Con esto, si yo ejecuto en una macro lo siguiente:

variable = ("Mi_Rango").Row

me devuelve el valor de la fila de la primer celda que define el rango (en este caso sería "3").Ahora bien, mi consulta es ¿puedo conseguir de manera similar el valor de la fila de la celda inferior derecha? (o sea, la que sería de valor "8")

PD: Todo esto es porque quiero que determinadas macros se ejecuten en determinadas celdas y quiero limitarlo con coordenadas... Seguramente existe alguna manera mas simple, pero recien comienzo con esto... Saludos

Javier Marco dijo...

Así a bote pronto, la primera forma que se me ocurre, aunque seguro que hay más, es esta:

Sub ejemplo()
variable = Range("Mi_Rango").Row + Range("Mi_Rango").Rows.Count - 1
MsgBox (variable)
End Sub

Saludos.

David dijo...

Mucha gracias Javier, es justo lo que necesitaba. Y ahora me siento tentado a consultarte sobre una curiosidad que me surgió al obtener los datos de columnas de esta manera:

Sub traigo_columna()

columna= ActiveCell.Column
MsgBox (columna)

End Sub


En este caso, devuelve el valor numérico de la columna, y lo que querría saber si es posible traer este dato en su correspondiente valor en "letras" (o sea, "F" en lugar de "6",por ejemplo)

Saludos,

Javier Marco dijo...

Esta es una forma de hacerlo (explicada paso a paso):

Sub traigo_columna()
'fichamos la coordenada
coordenada = ActiveCell.Address
'eliminamos el primer caracter ($)
coordenada = Right(coordenada, Len(coordenada) - 1)
'separamos los elementos por el caracter $
elementos = Split(coordenada, "$")
'mostramos como queda todo
MsgBox (coordenada)
'ahora solo el dato que nos interesa (el primer elemento)
MsgBox (elementos(0))
'ahora el segundo
MsgBox (elementos(1))
End Sub

Saludos.

David dijo...

Muchisimas gracias Javier, y prometo no molestarte por un rato...
Gracias nuevamente y saludos.

Anónimo dijo...

muy bueno este sitio por lo que hevisto todo esta de ataque para aprender

posible dijo...

hola e visto sus hojas de calculo y tengo una pero por mas que trato de hacer las formulas no se me da me pueden ayudar gracias o diganme como enviar mi hoja de exel para que me ayuden

Gamelos2 dijo...

Que tal Javier antes que nada permite enviarte un cordial saludo, también darte las gracias por tu blog ya que es una herramienta de gran ayuda para las personas que no sabemos mucho de códigos.
El motivo de este mensaje es para solicitar tu ayuda, estoy tratando de hacer una plantilla donde extraigo datos que están contenidos en una hoja. El problema que me esta dando es cuando hago la búsqueda, si me presenta los datos Pero cuando le pido una nueva búsqueda me dice que el documento no existe, voy y reviso la tabla y la información esta ahí. Me podrías ayudar para mejorar mi búsqueda o si hay alguna otra forma de hacerlo.
Te lo agradezco por anticipado saludos.
Te anexo mi código.
Sub Buscar()
Sheets("Nota").Select
Application.ScreenUpdating = False
If Range("B3").Value <> 0 ””Then End
ActiveSheet.Unprotect
Range("R15:AR25").ClearContents
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Range("Q1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
x = 0
Range("R101:BC120").ClearContents
Range("B100:B100").Select
Selection.Copy
Range("B15:B18").Select
ActiveSheet.Paste
fac = Range("B3").Value
Sheets("Notas1").Select
ActiveSheet.Unprotect
Range("A2").Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
If ActiveCell.Value = fac Then
x = 1
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Select
Sheets("Nota").Select
Range("R150").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Notas1").Select
'Selection.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop
'ActiveSheet.Protect
Sheets("Nota").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
If x = 0 Then MsgBox Prompt:="El numero de Nota no Existe.", Buttons:=vbOKOnly, Title:="GILBERTO"
Range("B3").Select
ActiveSheet.Protect
End Sub
Atte.
Gilberto(gamelos2)

Milena Garcia dijo...

AYUDA PARA FORMULA EN EXCEL

Deseo saber una fórmula que me permita hallar un valor buscado con dos criterios y mostrar como resultado un tercer valor, es decir tengo la siguiente matriz de información

CC.....ref.......Nombre
33333..01........Juana
22221..02........Andres
55555..01........Lucas
33333..02........Pedro
77777..03.:......Bancolombia

Lo que deseo es que al buscar ejemplo la CC (33333) en la matriz anterior, me ubique luego la referencia 02 y me de como resultado (Pedro). Lo he intentado con la opción buscarv pero solo me ubica el primer valor registrado en este caso la 01 (Juana).

Agradezco su valiosa colaboración con sus respuestas pues estoy hecha un lio y no se que funcion utilizar para este caso

Anónimo dijo...

Hola Espero que laguien me pueda ayudar con una formula
tengo dos rangos 102-106
por otro lado tengo una tabla con dos columnas A y B
Columna A tiene los rangos desde el 001.... al 200.
Columna B tiene datos que necesito,

con que formula puedo hacerle para que me devuelva los datos que existen enla columna B entre los rangos 102 al 106

Javier Marco dijo...

Creo que lo que necesitas es utilizar funciones de bases de datos. Concretamente, y a falta de más información, creo que necesitas utilizar la función BDSUMA.

Pásate por este artículo donde se explica el funcionamiento de la función BDCONTAR, y que es muy similar al de BDSUMA. En la ayuda de Excel (F1), puedes obtener más información, y ejemplos de esta función.

Anónimo dijo...

Buenas, He estado viendo tu pagina y es excelenta muy explicativa y con todos sus detalles muy fácil de seguir mil gracias por ello. Sabes, lo que no he podido ver es que si existe la posibilidad de imprimir un informe con datos determinados ubicados dentro de la hoja de calculculo o dentro del libro (EJ: el Cliente NN, hoja1 tiene las siguientes facturas vencidas, hoja 2 con el médio de pago, hoja 3)si pudieras agregar algo similar a modo de cultura general te lo agradeceria, espero haberme explicado de modo adecuado. Gracias. Saludos Lorean

Javier Marco dijo...

Lo más cercano que tengo son estas dos aplicaciones:

Controlar vencimientos de facturas y recibos, con descuento comercial.

Controlar vencimientos de facturas y recibos.

En ambos casos, hay un detalle de importes totales vencidos y no vencidos por cliente, tal y como podrás ver en la hoja "Previsión de cobros". En la hoja de facturas, podrás ver de color azul celeste aquellas que están vencidas.

Saludos.

Anónimo dijo...

Hola david buen dia, me intereso tu articulo y a mi me surgio una duda como puedo hacer este metodo pero buscando fechas por ejemplo la fecha actual, en vez de otro dato te lo agradeceria mucho.

Saludos Gerar

Javier Marco dijo...

Te contesta Javier, no David :-)

Prueba este macro, que lo que hace es situarse en la celda que contiene la fecha de hoy (fecha del sistema, o fecha del PC):

Sub ir_al_dia_de_hoy()
'Si ay errores, que continúe
On Error Resume Next
'pasamos la fecha de hoy a una variable
fecha = Date
'nos situamos en la celda con la fecha de hoy
Cells.Find(What:=fecha).Activate
End Sub

Saludos.

Anónimo dijo...

Hola Javier buen dia, antes que nada gracias por crear este sitio a muchos nos ayuda a entender mejor este programa sobre a nosotros los principiantes jejeje buen; mira la duda que tengo es esta y espero me puedas ayudar, tengo una macro que lo que hace es crear un email por medio de outlook, que lo que hace al posicionarme en la celda que quiera me crea ese correo de la persona a la que esta configurada la macro, mi duda es que quiero hacer un solo correo buscando por medio de la fecha actual poniendo so lo algunos datos dentro del mensaje; osea que busque los datos en la columna fecha diagnostico y nose solo me junte los registro de la fecha actual y con ellos me cree el correo no con todos los datos solo con algunos campos que serian 3 responsable, folio y tag, el destinatario no importa ese puede quedar vacio. espero me puedas ayudar te dejo en link de mi archivo y gracias.

Saludos. Fernando E.

archivo

http://www.4shared.com/file/wDknEJkS/correo.html

Anónimo dijo...

BUENAS TARDES, OYE ME PODRIAS AYUDAR PARA CREAR UNA FORMULA PARA BUSCAR UN ELEMENTO EN BASE A DOS CONDICIONANTES???, TENGO QUE BUSCAR EN UN ESTADO DE CUENTA BANCARIO UN IMPORTE EN BASE A FECHA

MrWolf dijo...

Buenas Tardes, una consulta. Tengo un excel que va conectado a una base de datos que se va actualizando diariamente, por lo que me va agregando una nueva celda con valores y su fecha respectiva. El punto es que me gustaría saber cómo puedo hacer para que cada día una formula que tengo, por ejemplo (promedio de todos los números), tome el último valor entregado por la base de datos. Espero me puedan ayudar, muchas gracias!

vitorique dijo...

Una pequeña observación:
La última fórmula (DESREF( ...)) no funciona con los nombres en desorden; pero si se modifica como sigue: =DESREF(C5,COINCIDIR(D21,C6:C15)+1,COINCIDIR(D20,D5:G5,0)), trabaja en cualquier caso.
Espero que les sea útil a los lectores.
Gracias y hasta pronto.

Javier Marco dijo...

Gracias por el aporte Vitorique. En la mayoría de los casos, lo normal será tenerlo todo ordenado, porque visualmente es incluso más sencillo de localizar un dato, como en el caso de una tarifa de mensajería (como es el caso del ejemplo). Incluso podemos ayudarnos visualmente a localizar el dato, comprobando que lo que nos dice Excel es cierto.

En desorden, es todo más farragoso y agotador :-)

ing. carlos peñaranda dijo...

Hola a todos
primero me gustaria felicitar al creador de este blog esta excelente
segundo me gustaria que me solucionaran una duda

estoy utilizando BUSCAR() para que me encuentre un numero pero el me arroja el numero menor al resultado y necesito que me de el mayor Ejemplo: multiplico 22*1,25= 27,5 y el rango donde el tiene que buscar es 20 y 30 el de da como resultado es el 20 y necesito que me de 30.

Fernando dijo...

Hola, qué tal? Aprovecho para preguntarte lo siguiente, espero que me puedas ayudar por favor. Tengo filas con los números de cheques y luego columnas por cada día del año. Cada cheque tiene una cruz en una columna según su fecha de emisión, lo que necesito es una formula que me devuelva la fecha de emisión de cada cheque (osea el título de cada columna)
Desde ya muchas gracias!

JuanVen dijo...

Buen día Javier, estoy seguro me puedes ayudar. digamos que tengo la siguiente tabla en el rango A1:C4, conformada por tres columnas: Nombre, Edad y Altura. Los datos son: Javier, 30 años, 1,7 mts; Juan, 15 años, 1,5 mts; y Pepe, 25 años, 1,6 mts. Es una tabla sencilla inventada. El tema es que hago una macro que me ordene esa tabla y hasta ahí todo bien. Pero cuando agrego un cuarto elemento, digamos, José, 20 años, 1,8 mts. entonces no lo toma porque deja fijo el rango anterior y no sé cómo hacer para que automáticamente identifique un rango con un dato nuevo (o unos datos nuevos).

Aquí la macro que estoy usando, muy bàsica por cierto:

Range("A1").Select
Selection.CurrentRegion.Select
ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Hoja1").Sort
.SetRange Range("A2:C4")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Como ves si tengo datos nuevos, siempre va a tomar el rango "A2:C4" y no sé cómo hacer para que cambie este rango de manera dinámica.

Gracias por tu respuesta.

Javier Marco dijo...

Tienes exceso de código. Simplemente con el CurrentRegion, que abarca todas las celdas contíguas, debería bastar. Prueba este código:

Sub ejemplo()
Application.ScreenUpdating = False
Range("A2").Select
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Range("A2").Select
Application.ScreenUpdating = True
End Sub

Saludos.

JuanVen dijo...

Javier, no esperaba que me respondieras tan rápido. En verdad muchas gracias, pero voy a seguir molestándote.

La macro de ordenación que sugieres funciona bien al agregarle nuevos valores, ¡Fino!... pero un detalle es que el encabezado también me lo ordena considerándolo una fila de datos más. Como viste antes la tabla ejemplo tiene como encabezdos: Nombre, Edad y Altura que siempre deben quedarse fijos en la primera fila.

Algo más que estoy seguro puedes resolver, digamos que en otra celda fuera de la región le escribo el encabezado por el cual quiero la ordenación (así como los criterios en Filtros Avanzados), por ejemplo esta vez lo quiero ordenado por "Altura" (celda C1). Agrego esto porque a los datos les doy usos distintos y dentro de otras macros, así que requieren ordanamientos distintos.

Espero realmente no molestarte aunque veo que para ti es un placer ayudar a tus seguidores en cuyo blog encontramos soluciones realmente maravillosas y útiles.

Saludos,

Javier Marco dijo...

Para el primer caso, para que no te ordene encabezados, cambia esta línea:

Selection.CurrentRegion.Select

Por esta otra, en la que le estamos diciendo que baje una fila:

Selection.CurrentRegion.Offset(1, 0).Select

Para la segunda cuestión, coloca el criterio de ordenación en una celda que no esté contígua al rango de datos a ordenar, y prueba este ejemplo:

Sub ejemplo()
'ocultamos el procedimiento
Application.ScreenUpdating = False
'si hay errores, que continúe
'On Error Resume Next
'seleccionamos la celda A2
Range("A2").Select
'seleccionamos todo el rango contínuo
Selection.CurrentRegion.Offset(1, 0).Select
'ordenamos por el criterio seleccionado en la celda G1
'que no debe estar contígua pues la seleccionaría en la línea anterior
dato = Range("G1")
'buscamos ese dato, para localizar en qué celda está
'para lo cual supondremos que los encabezados están en la fila 1
'y llegan hasta la columna 4 (columna D)
celda = Range("A1:D1").Find(dato, LookAt:=xlWhole).Address
'ordenamos según ese criterio
Selection.Sort Key1:=Range(celda), Order1:=xlAscending
'nos situamos en A2
Range("A2").Select
'mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Si te fijas, he supuesto que la celda donde pones el criterio es G1, y los encabezados para ordenar están desde A1 a D1.

Saludos.

JuanVen dijo...

¡Funcionó a la perfección! usted es una lumbrera de la red.

De nuevo ¡Gracias!

Anónimo dijo...

hola me gustaria preguntar que posibilidad hay de programar un buscar y a la hora de repetir la busqueda me la sume y me compare la exixtencia que se encuentra en otra columna