Mike rastrea los valores de latitud y longitud en una hoja de cálculo de Excel. Como estos son esencialmente puntos en una cuadrícula, a Mike le gustaría calcular la distancia entre dos puntos de latitud / longitud dados.

Si los pares de latitud y longitud fueran realmente solo puntos en una cuadrícula, entonces sería fácil calcular la distancia entre ellos. El problema es que en realidad son puntos en una esfera, lo que significa que no puede usar cálculos de cuadrícula plana para determinar la distancia. Además, hay muchas formas de calcular distancias: distancia de superficie más corta, ruta de vuelo óptima («en línea recta»), distancia a través de la tierra, distancia de conducción, etc.

Obviamente, esta podría ser una pregunta complicada. En el espacio disponible, examinaré un par de formas de determinar la distancia del gran círculo («en línea recta») y luego proporcionaré algunas referencias para obtener información adicional sobre los otros tipos de cálculos.

Lo primero que debe averiguar es cómo se representarán la latitud y la longitud de cada punto en Excel. Hay varias formas de representarlo. Por ejemplo, puede ingresar los grados, minutos y segundos en celdas individuales. O puede tenerlos en una sola celda como DD: MM: SS. De cualquier manera es aceptable, pero deberán tratarse de manera diferente a sus fórmulas. ¿Por qué? Porque si ingresa latitud y longitud como DD: MM: SS, Excel los convertirá internamente en un valor de tiempo, y solo debe tener en cuenta esa conversión.

Lo que tendrá que hacer, pase lo que pase, es convertir su latitud y longitud en un valor decimal en radianes. Si tiene una coordenada en tres celdas separadas (grados, minutos y segundos), entonces puede usar la siguiente fórmula para hacer la conversión a un valor decimal en radianes:

=RADIANS((Degrees3600+Minutes60+Seconds)/3600)

La fórmula utiliza rangos con nombre para sus grados, minutos y segundos. Convierte esos tres valores en un valor único que representa el total de grados y luego usa la función RADIANS para convertir esto a radianes.

Si comienza con un valor de 32 grados, 48 ​​minutos y 0 segundos, la fórmula termina luciendo así:

=RADIANS((323600+4860+0)/3600)

=RADIANS((115200+2880+0)/3600)

=RADIANS(118080/3600)

=RADIANS(32.8)

=0.572467995

Si está almacenando sus coordenadas en el formato de DD: MM: SS en una sola celda (en este ejemplo, celda E12), entonces puede utilizar la siguiente fórmula para convertir a un valor decimal en radianes:

=RADIANS((DAY(E12)86400+HOUR(E12)3600+MINUTE(E12)*60+SECOND(E12))/3600)

Suponiendo que la celda E12 contiene 32:48:00, entonces la fórmula termina luciendo así:

=RADIANS((186400+83600+48*60+0)/3600)

=RADIANS((86400+28800+2880+0)/3600)

=RADIANS(118080/3600)

=RADIANS(32.8)

=0.572467995

Con sus coordenadas en radianes, puede usar una fórmula trigonométrica para calcular la distancia a lo largo de la superficie de una esfera. Hay muchas fórmulas de este tipo que podrían utilizarse; la siguiente fórmula será suficiente para nuestros propósitos:

=ACOS(SIN(Lat1)SIN(Lat2)+COS(Lat1)COS(Lat2)COS(Lon2-Lon1))180/PI()*60

En esta fórmula, cada una de las coordenadas de latitud (Lat1 y Lat2) y longitud (Lon1 y Lon2) debe ser un valor decimal, en radianes, como ya se ha explicado. La fórmula devuelve un valor en millas náuticas, a las que luego puede aplicar varias fórmulas para convertir a otras unidades de medida, según lo desee.

Debe darse cuenta de que los valores que obtenga mediante el uso de cualquier fórmula que calcule la distancia en la superficie de una esfera darán resultados ligeramente erróneos. ¿Por qué? Porque la Tierra no es una esfera perfecta. Por lo tanto, las distancias solo deben considerarse aproximadas. Si quieres ser un poco más preciso, puedes usar la siguiente fórmula para determinar tus millas náuticas:

=ACOS(SIN(Lat1)SIN(Lat2)+COS(Lat1)COS(Lat2)COS(Lon2-Lon1))3443.89849

Esta fórmula sustituye el radio de la tierra (3443.89849 millas náuticas) por el radio de una esfera (180 / PI () * 60, o 3437.746771). De cualquier manera, la respuesta debe considerarse aproximada.

Como puedes ver, la fórmula para calcular distancias es bastante larga. Puede que le resulte más fácil desarrollar su propia función definida por el usuario que hará el cálculo por usted. La siguiente función toma cuatro valores (los dos pares de latitudes y longitudes, en grados) y luego devuelve un resultado en millas náuticas:

Function CrowFlies(dlat1, dlon1, dlat2, dlon2)

Pi = Application.Pi()

earthradius = 3443.89849  'nautical miles

lat1 = dlat1  Pi / 180     lat2 = dlat2  Pi / 180     lon1 = dlon1  Pi / 180     lon2 = dlon2  Pi / 180

cosX = Sin(lat1)  Sin(lat2) + Cos(lat1) _        Cos(lat2) * Cos(lon1 - lon2)

CrowFlies = earthradius * Application.Acos(cosX)

End Function

Si desea ver una discusión más profunda sobre latitudes y longitudes, y las matemáticas involucradas, puede encontrar una buena selección de artículos en este sitio:

http://mathforum.org/library/drmath/sets/select/dm_lat_long.html

Con las matemáticas en su haber, entonces puede comenzar a buscar varias fórmulas que puede usar. Hay uno interesante en VBA en esta página web:

http://www.freevbcode.com/ShowCode.asp?ID=5532

También se puede encontrar una buena discusión de propósito general en el sitio de Chip Pearson, aquí:

http://www.cpearson.com/excel/LatLong.aspx

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (3275) se aplica a Microsoft Excel 97, 2000, 2002 y 2003. Puede encontrar una versión de este consejo para la interfaz de cinta de Excel (Excel 2007 y posterior) aquí:

link: / excelribbon-Calculating_the_Distance_between_Points [Calculando la distancia entre puntos].