Como posso calcular a distância entre coordenadas em várias linhas correspondentes no Excel?

Question

Eu tenho uma folha com 20.000 linhas. Cada linha tem um ID de objeto, que pode ocorrer várias vezes em toda a planilha. Cada linha também contém coordenadas, pois são pontos do mundo real. Cada objeto pode ocorrer várias vezes na planilha porque uma linha representa um objeto encontrado em uma imagem. E 1 imagem = 1 dia, daí o atributo de data.

Eu preciso consultar a coluna com os IDs dos objetos, que eu sei fazer usando INDEX: MATCH. Mas preciso então calcular, para cada objeto, a distância entre coordenadas sucessivas. Eu calculo a distância usando esta fórmula: d = (sqrt ((x2-x1) ^ 2 + (y2-y1) ^ 2)). O problema que estou tendo é como criar uma fórmula do Excel para calcular entre linhas correspondentes sucessivas.

No exemplo, destaquei as linhas com ID de objeto 30. Elas ocorrem 7 vezes. Eu precisaria calcular a distância entre entradas sucessivas, 30 (1) vs. 30 (2), 30 (2) vs. 30 (3), etc., portanto, seis cálculos de distância. Eu preciso fazer isso para cada ID de objeto (excluindo IDs de objeto que ocorrem uma única vez)

    

Answer 1

Assumi um layout de dados mínimo - as linhas contêm ID, coordenada X, e coordenada Y nas colunas A , B e C , respectivamente. Na Coluna D , use MATCH para encontrar a próxima linha com o mesmo ID. Em Colunas E e F , use INDEX para localizar as coordenadas X e Y para a próxima ocorrência do mesmo ID. Na coluna G , calcule a distância entre este ponto e o próximo.

• D2 → =MATCH(A2, A3:A$9, 0)+ROW()
• E2 → =INDEX(B:B, $D2, 1)
• F2 → =INDEX(C:C, $D2, 1)
• G2 → =IF(ISERROR(D2), "", SQRT((E2-B2)^2+(F2-C2)^2))

(substituindo o 9 na fórmula de D2 com seu último número de linha de dados válido) e arraste / preencha.

Os valores da distância funcionam como inteiros porque eu escolhi os dois primeiros trios pitagóricos (3-4-5 e 5-12-13) para os vetores inter-ponto.