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.