Майк отслеживает значения широты и долготы на листе Excel. Поскольку это, по сути, точки на сетке, Майк хотел бы вычислить расстояние между любыми двумя заданными точками широты/долготы.
Если бы пары широты и долготы действительно были просто точками на сетке, то вычисление расстояние между ними было бы несложным. Проблема в том, что они на самом деле являются точками на сфере, а это означает, что вы не можете использовать вычисления с плоской сеткой для определения расстояния. Кроме того, существует множество способов вычисления расстояний: кратчайшее расстояние на поверхности, оптимальная траектория полета (“по прямой”), расстояние через землю, расстояние проезда и т. Д.
Очевидно, что это может быть сложный вопрос. В доступном месте я рассмотрю несколько способов определения расстояния большого круга (“по прямой”), а затем предоставлю некоторые ссылки для получения дополнительной информации о других типах вычислений.
Первое, что вам нужно выяснить, это как широта и долгота каждой точки будут представлены в Excel. Есть несколько способов его представления. Например, вы можете ввести градусы, минуты и секунды в отдельные ячейки. Или вы можете поместить их в одну ячейку как DD: MM: SS. Любой способ приемлем, но к ним нужно будет относиться по-разному в ваших формулах. Почему? Потому что, если вы введете широту и долготу как DD: MM: SS, тогда Excel преобразует их внутренне в значение времени, и вам просто нужно принять это преобразование во внимание.
Что вы собираетесь делать. В любом случае необходимо преобразовать широту и долготу в десятичное значение в радианах. Если у вас есть координаты в трех отдельных ячейках (градусы, минуты и секунды), вы можете использовать следующую формулу для преобразования в десятичное значение в радианах:
= RADIANS ((Degrees * 3600 + минуты * 60 + секунды)/3600)
В формуле используются именованные диапазоны для ваших градусов, минут и секунд. Он преобразует эти три значения в одно значение, представляющее общее количество градусов, а затем использует функцию РАДИАНЫ для преобразования его в радианы. Если вы начнете со значения 32 градусов, 48 минут и 0 секунд, формула будет выглядеть так:
= РАДИАНЫ ((32 * 3600 + 48 * 60 + 0)/3600 ) = РАДИАНЫ ((115200 + 2880 + 0)/3600) = РАДИАНЫ (118080/3600) = РАДИАНЫ (32,8) = 0,572467995
Если вы сохраняете свои координаты в формате ДД: ММ : SS в одной ячейке (в этом примере ячейка E12), тогда вы можете использовать следующую формулу для преобразования в десятичное значение в радианах:
= RADIANS ((DAY (E12) * 86400 + ЧАС (E12) * 3600 + МИНУТА (E12) * 60 + СЕКУНДА (E12))/3600)
Предположим, что ячейка E12 содержит 32:48:00, тогда формула будет выглядеть как это:
= РАДИАНЫ ((1 * 86400 + 8 * 3600 + 48 * 60 + 0)/3600) = РАДИАНЫ ((86400 + 28800 + 2880 + 0)/3600) = РАДИАНЫ ( 118080/3600) = РАДИАНЫ (32,8) = 0,572467995
Используя координаты в радианах, вы можете использовать тригонометрическую формулу для расчета расстояния по поверхности сферы.. Есть много таких формул, которые можно использовать; для наших целей будет достаточно следующей формулы:
= ACOS (SIN (Lat1) * SIN (Lat2) + COS (Lat1) * COS (Lat2) * COS (Lon2-Lon1)) * 180 /PI () * 60
В этой формуле каждая из координат широты (Lat1 и Lat2) и долготы (Lon1 и Lon2) должна быть десятичным значением в радианах, как уже обсуждалось. Формула возвращает значение в морских милях, к которому затем можно применять различные формулы, чтобы при желании преобразовать в другие единицы измерения.
Вы должны понимать, что значения, которые вы получаете, использование любой формулы, которая вычисляет расстояние на поверхности сферы, даст немного ошибочные результаты. Почему? Потому что Земля не идеальная сфера. Таким образом, расстояния следует считать приблизительными. Если вы хотите получить более точную информацию, вы можете использовать следующую формулу для определения ваших морских миль:
= ACOS (SIN (Lat1) * SIN (Lat2) + COS (Lat1) * COS (Lat2) * COS (Lon2-Lon1)) * 3443.89849
В этой формуле радиус сферы (180/PI () * 60 заменяется радиусом Земли (3443,89849 морских миль). , или 3437.746771). В любом случае ответ все равно следует считать приблизительным.
Как видите, формула для расчета расстояний довольно длинная. Возможно, вам будет проще разработать собственную пользовательскую функцию, которая будет выполнять вычисления за вас. Следующая функция принимает четыре значения (две пары широты и долготы в градусах), а затем возвращает результат в морских милях:
Функция CrowFlies (dlat1, dlon1, dlat2, dlon2) Pi = Application.Pi () earthradius = 3443,89849 ‘морских миль 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) Конечная функция
Если вы хотите увидеть более подробную информацию обсуждение широты и долготы, а также соответствующих математических расчетов, вы можете найти на этом сайте:
http://mathforum.org/library/drmath/sets/select/dm_lat_long. html
Имея математические знания за плечами, вы можете начать поиск различных формул, которые вы можете использовать. На этой веб-странице есть интересный пример VBA:
http://www.freevbcode.com/ShowCode.asp?ID=5532
Хороший общий целевое обсуждение также можно найти на сайте Чипа Пирсона, здесь:
http://www.cpearson.com/excel/LatLong.aspx
Источник: