从Excel中的一组GPS坐标中查找下一个关闭机场

问题描述

我在此处我正在尝试在excel中执行计算.

I have a set of airport coordinates as defined here which I'm attempting to perform calculations on in excel.

我想为任何给定的机场坐标找到下一个最近的机场.

I want to find the next closest airport for any given airports' coordinates.

我能得到的最接近的公式是:

The closest I've been able to get is the use of the formula :

=INDEX($A$1:$A$20,MATCH(MIN(SQRT(($B$1:$B$20-E1)^2+($C$1:$C$20-F1)^2)),SQRT(($B$1:$B$20-E1)^2+($C$1:$C$20-F1)^2),0),1)

如此页面上显示的.

但是，此公式将提供最接近命名的位置(即机场)到给定的一组坐标.

However, this formula will provide the closest named location (i.e the airport) to a given set of coordinates.

如何找到最近的下一个机场?

推荐答案

希望这会有所帮助，在D2中使用shif + ctrl + enter复制以下公式:

Hope this will help, copy the below formula in D2 with shif+ctrl+enter:

=INDEX($A$2:$A$19,MATCH(MIN(IF($B$2:$B$19&$C$2:$C$19=B2&C2,99999,ACOS(COS(RADIANS(90-$B$2:$B$19))*COS(RADIANS(90-B2))+SIN(RADIANS(90-$B$2:$B$19))*SIN(RADIANS(90-B2))*COS(RADIANS($C$2:$C$19-C2)))*6371)),IF($B$2:$B$19&$C$2:$C$19=B2&C2,99999,ACOS(COS(RADIANS(90-$B$2:$B$19))*COS(RADIANS(90-B2))+SIN(RADIANS(90-$B$2:$B$19))*SIN(RADIANS(90-B2))*COS(RADIANS($C$2:$C$19-C2)))*6371),0),1)

这篇关于从Excel中的一组GPS坐标中查找下一个关闭机场的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！