Майк отслеживает значения широты и долготы на листе Excel. Поскольку это, по сути, точки на сетке, Майк хотел бы вычислить расстояние между любыми двумя заданными точками широты / долготы.

Если бы пары широты и долготы были просто точками на сетке, то вычислить расстояние между ними было бы легко. Проблема в том, что они действительно являются точками на сфере, а это означает, что вы не можете использовать вычисления с плоской сеткой для определения расстояния. Кроме того, существует множество способов расчета расстояний: кратчайшее наземное расстояние, оптимальная траектория полета («по прямой»), расстояние через землю, расстояние проезда и т. Д.

Очевидно, это может быть сложный вопрос. В доступном месте я рассмотрю несколько способов определения расстояния большого круга («по прямой»), а затем предоставлю некоторые ссылки для получения дополнительной информации о других типах вычислений.

Первое, что вам нужно выяснить, это то, как широта и долгота каждой точки будут представлены в Excel. Есть несколько способов его представления. Например, вы можете ввести градусы, минуты и секунды в отдельные ячейки. Или вы можете поместить их в одну ячейку как DD: MM: SS. Любой способ приемлем, но к ним нужно будет относиться по-разному в ваших формулах. Зачем? Потому что, если вы введете широту и долготу как DD: MM: SS, тогда Excel преобразует их внутренне во значение времени, и вам просто нужно принять это преобразование во внимание.

Что вам нужно сделать, что бы ни случилось, это преобразовать вашу широту и долготу в десятичное значение в радианах. Если у вас есть координаты в трех отдельных ячейках (градусы, минуты и секунды), вы можете использовать следующую формулу для преобразования в десятичное значение в радианах:

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

В формуле используются именованные диапазоны для градусов, минут и секунд. Он преобразует эти три значения в одно значение, представляющее общее количество градусов, а затем использует функцию РАДИАНЫ для преобразования его в радианы.

Если вы начнете со значения 32 градусов, 48 минут и 0 секунд, формула будет выглядеть так:

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

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

=RADIANS(118080/3600)

=RADIANS(32.8)

=0.572467995

Если вы сохраняете свои координаты в формате DD: MM: SS в одной ячейке (в этом примере, ячейка E12), вы можете использовать следующую формулу для преобразования в десятичное значение в радианах:

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

Предположим, что ячейка E12 содержит 32:48:00, тогда формула будет выглядеть так:

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

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

=RADIANS(118080/3600)

=RADIANS(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 или 3437,746771) радиусом Земли (3443,89849 морских миль). В любом случае ответ все равно следует считать приблизительным.

Как видите, формула для расчета расстояний довольно длинная. Возможно, вам будет проще разработать собственную пользовательскую функцию, которая будет выполнять вычисления за вас. Следующая функция принимает четыре значения (две пары широты и долготы в градусах), а затем возвращает результат в морских милях:

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

Если вы хотите увидеть более подробное обсуждение широты и долготы, а также математических расчетов, вы можете найти хорошую подборку статей на этом сайте:

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

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (3275) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздние версии) здесь:

link: / excelribbon-Calculating_the_Distance_between_Points [Расчет расстояния между точками].