Funciones de BúsquedaAvanzado

Calcula el franqueo por tramo de peso y zona con INDICE y dos COINCIDIR

La tarifa de un transportista se lee en dos sentidos a la vez: hacia abajo hasta el tramo de peso en el que cae un paquete, y a lo ancho hasta su zona de entrega exacta. INDICE con dos COINCIDIR hace ambas cosas, siempre que a cada COINCIDIR se le diga qué tipo de coincidencia está buscando.

Tarea:

Preparas y envías los pedidos de una tienda online de artículos para el hogar. La tarifa del transportista (E1:H6) indica un precio para cada tramo de peso en el lateral y cada zona de entrega en la parte superior; un tramo va desde su cifra de "Weight from" hasta justo antes de la siguiente, y el tramo de 20 kg cubre cualquier paquete de 20 kg o más. En D2, busca el franqueo de cada paquete usando su peso de la columna B y su zona de la columna C, y luego rellena hacia abajo hasta la fila 7.

Hoja de Cálculo Interactiva

Pistas

+5 XPbonus sin pistas

Resuélvelo por tu cuenta para conservar el bonus. Cada pista te acerca un paso más a la fórmula.

Los datos de este ejercicio

Esta es la cuadrícula con la que empiezas. Las referencias de celda de la tarea — B6, C2 — apuntan a los números de fila y letras de columna de abajo.

7 filas × 8 columnas6 celdas que rellenas
ABCDEFGH
1ParcelWeight (kg)ZonePostageWeight from (kg)Zone 1Zone 2Zone 3
2ORD-33010.8Zone 204.55.957.8
3ORD-33021Zone 116.958.411.25
4ORD-33034.99Zone 3511.414.119.6
5ORD-330412.3Zone 21018.7523.332.45
6ORD-330520Zone 32029.937.1551.8
7ORD-33067.5Zone 1
Qué enseña este ejercicioPuede contener la respuesta

Los dos COINCIDIR hacen trabajos distintos, y la fórmula solo funciona porque a cada uno se le dice cuál. La columna de pesos guarda el inicio de cada tramo, no todos los pesos posibles, así que COINCIDIR(B2;$E$2:$E$6;1) recorre la lista ordenada hacia abajo y se detiene en la última cifra que no supera al paquete — 0,8 kg se detiene en 0, 12,3 kg se detiene en 10. Por eso ORD-3303, con 4,99 kg, sigue pagando la tarifa de 1 kg: no ha llegado a 5, y quedarse un pelo por debajo del límite de un tramo sale un tramo entero más barato. ORD-3302 y ORD-3305 caen justo en un límite, y «menor o igual que» los coloca en el tramo que empieza ahí, que es lo que la tarifa quiere decir con "Weight from". La zona es el caso contrario: "Zone 2" es una etiqueta, no un punto en una escala, así que COINCIDIR(C2;$F$1:$H$1;0) exige un acierto exacto — una coincidencia aproximada sobre texto caería sin avisar en una zona vecina en lugar de señalar una errata. INDICE toma entonces el número de fila del tramo y el número de columna de la zona y devuelve el único precio donde se cruzan. El tipo de coincidencia 1 da por hecho que los pesos van de menor a mayor; si alguna vez se pegara la tarifa del transportista de más pesado a más ligero, devolvería precios equivocados sin ningún error, así que el orden de la columna E forma parte de que la fórmula sea correcta.