在Excel中找到最接近的一组坐标 [英] Find the nearest set of coordinates in Excel

问题描述

有两个表，每个表都有一个坐标对:经度和纬度，以十进制度表示(即37.23222，-121,3433).如何将表A与表B中最接近的坐标对匹配?

There are two tables, each with a coordinate pair: longitude and latitude, in decimal degrees (i.e, 37.23222, -121,3433). How do I match table A with its nearest coordinate pair in table B?

推荐答案

您可以尝试从G1填写此公式，如下所示:

You could try filling down this formula from G1 as shown below:

=LOOKUP(1,1/FREQUENCY(0,MMULT((B$1:C$10-E1:F1)^2,{1;1})),A$1:A$10)

要获得更精确的公式，并考虑到圆形距离，请尝试从H1填充:

For a more accurate formula that takes account of the circular distances try filling down from H1:

=LOOKUP(1,1/FREQUENCY(0,SIN((RADIANS(B$1:B$10-E1))/2)^2+SIN((RADIANS(C$1:C$10-F1))/2)^2*COS(RADIANS(B$1:B$10))*COS(RADIANS(E1))),A$1:A$10)

这篇关于在Excel中找到最接近的一组坐标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！