Cómo crear una tabla de distancias entre ciudades en Excel. A partir de un listado de ciudades, una única fórmula te permitirá presentar la información en forma de tabla de distancias. Y por supuesto, esta técnica funciona si quieres presentar cruces entre productos, personas, duraciones.
Tabla de distancias de Excel: Distancia entre ciudades
Este ejemplo muestra las distancias entre diferentes ciudades de Europa (fuente)
Tenga en cuenta que no es necesario que el mismo par de ciudades esté presente en ambas direcciones. La fórmula de este artículo se ajusta en ambos casos: Ciudad A - Ciudad B y Ciudad B - Ciudad A.
Paso 1: extraiga nombres de ciudades únicos
Primero extraeremos todas las ciudades para crear una lista única. Esto se hace fácilmente con el Función ÚNICA de Excel 365.
Paso 2: transponer la columna
Luego use la función TRANSPONER para convertir el resultado de vertical a horizontal. El símbolo # indica una referencia extendida, lo que significa que TRANSPOSE "leerá" todas las celdas generadas por UNIQUE.
Paso 3: cree la fórmula matricial
Ahora crearemos la fórmula de Excel para transformar una lista de ciudades en una tabla de distancias. Compara las ciudades en la tabla fuente y calcula los resultados.
- ((tbl_Dist[Ciudad1]=F$1)*(tbl_Dist[Ciudad2]=$E2))
- Comprueba si Ciudad1 en tbl_Dist corresponde a F$1 y Ciudad2 a $E2.
- Crea tablas VERDADERO/FALSO. El producto da 1 si ambas son VERDADERAS.
- Uso de las celdas F$1 y $E2 referencias mixtas para permitir que la fórmula se copie más tarde
- ((tbl_Dist[Ciudad1]=$E2)*(tbl_Dist[Ciudad2]=F$1))
- Verifique el orden inverso: Ciudad1 es $E2, Ciudad2 es F$1.
- Garantiza que se encuentre la distancia independientemente del orden.
- ((tbl_Dist[Ville1]=F$1)*(tbl_Dist[Ville2]=$E2))+((tbl_Dist[Ville1]=$E2)*(tbl_Dist[Ville2]=F$1))
- Combina los resultados con +, dando 1 si alguna de las condiciones es VERDADERA.
- EQUIVALENTE(1,…, 0)
- Localiza la línea que contiene las ciudades correspondientes (F$1 y $E2).
- Identifique el par de ciudades encontrando el 1 principal.
- ÍNDICE(tbl_Dist[Distancia(km)],…)
- Devuelve la distancia desde tbl_Dist[Distance(km)] hasta la posición MATCH.
- SI ERROR(…, “-”)
- Muestra un guión ("-") si no se encuentra ninguna coincidencia.
Copie esta fórmula en cada celda de la matriz de distancias.
- Aplicar una formato condicional cromático para distancias cortas o largas.
