Obtém endereços de célula do Excel dos valores mais próximos no mesmo intervalo

Question

Eu tenho uma planilha grande (> 15000 linhas) que contém nomes de empresas, locais, números de produção e latitude / longitude dos locais. Tabela de exemplo

Eu estou tentando comparar cada coordenada de latitude e longitude com todas as outras para encontrar aquelas que estão dentro de 0,01 graus. Vamos chamar essas coleções de localização como "áreas". Então eu quero obter o total de Sold / Day e Flared / Day para cada uma dessas áreas. Eu tenho atacado o problema tentando primeiro obter os endereços de célula de todos os locais lat / long que estão dentro do requisito de 0,01 grau da linha existente usando uma fórmula como esta:

=CELL("address",INDEX(AB:AB,MATCH(MIN(ABS(AB:AB-[@Lat])),ABS(AB:AB-[@Lat]),0)))

Ou uma fórmula muito mais complexa:

=ABS(RANK([@Lat],AB:AB)-MATCH(MIN(LARGE(AB:AB,ROW(INDIRECT("1:"&COUNT(AB:AB)-2)))-LARGE(AB:AB,ROW(INDIRECT("1:"&COUNT(AB:AB)-2))+2)),LARGE(AB:AB,ROW(INDIRECT("1:"&COUNT(AB:AB)-2)))-LARGE(AB:AB,ROW(INDIRECT("1:"&COUNT(AB:AB)-2))+2),0)-1)<=1

Onde AB é a coluna Lat.

Mas eles retornam # N / A ou 0. Talvez eu precise usar um script VBA para percorrer as comparações lat / long. No entanto, não estou muito familiarizado com o Excel VBA. Copiei uma parte menor dos dados (< 2000 linhas) para outra folha para facilitar o teste das fórmulas de matriz.

    

Answer 1

Você pode criar uma tabela dinâmica selecionando seus dados e indo para Inserir - > Tabelas - > Tabela Dinâmica. Se você selecionar apenas um dos seus dados, o Excel provavelmente adivinhará o intervalo de todos os seus dados.

Se você quiser, por exemplo, que todas as suas empresas atinjam um requisito de 0,01 grau, arraste e solte os campos Long e Company na área Linhas. A ordem é importante, deve ser primeiro o campo Longo e depois o campo Empresa. Então, tudo que você precisa fazer é agrupar a longitude pela exigência de 0,01 grau.

Existem algumas maneiras de fazer isso. Você pode ir para Dados - > Esboço - > Agrupar ou você pode ir para Ferramentas de Tabela Dinâmica - > Analise - > Grupo - > Group Selection, ou você pode clicar com o botão direito em um único item (dos dados Long) e escolher Group no menu de atalho. De qualquer forma, a caixa Agrupamento aparece e o Excel coloca automaticamente os valores Starting At e Ending At com base nos seus valores. Tudo que você precisa fazer é definir o By em 0.01. O Excel agrupa automaticamente todas as empresas com base em seus valores de longitude, agrupados pela exigência de 0,01.