Получить адреса ячеек Excel из ближайших значений в том же диапазоне

Question

У меня есть большая электронная таблица (> 15000 строк), которая содержит названия компаний, местоположения, производственные номера и широту / долготу местоположений. Пример таблицы

Я пытаюсь сравнить каждую координату широты и долготы со всеми остальными, чтобы найти те, которые находятся в пределах 0,01 градуса. Давайте назовем эти коллекции местоположений "областями". Затем я хочу получить общее количество проданных / день и всплеск / день для каждой из этих областей. Я атаковал этот вопрос, пытаясь сначала получить адреса ячеек всех длинных / длинных местоположений, которые находятся в пределах 0,01 градуса для существующей строки, используя формулу, подобную этой:

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

Или гораздо более сложная формула:

=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

Где AB - столбец лат.

Но они возвращают либо # N/A, либо 0. Возможно, мне нужно использовать скрипт VBA для итерации сравнений lat/long. Тем не менее, я не очень знаком с Excel VBA. Я скопировал меньшую часть данных (<2000 строк) на другой лист, чтобы облегчить тестирование формул массива.

Answer 1

Вы можете создать сводную таблицу, выбрав свои данные и выбрав Вставить -> Таблицы -> Сводная таблица. Если вы выберете только одну из ваших данных, Excel, вероятно, определит правильный диапазон всех ваших данных.

Например, если вы хотите, чтобы все ваши компании соответствовали требованиям 0,01 градуса, перетащите поля Long и Company в область Rows. Порядок важен, это должно быть сначала поле Long, а затем поле Company. Затем все, что вам нужно сделать, это сгруппировать долготу по требованию 0,01 градуса.

Есть несколько способов сделать это. Вы можете перейти к «Данные» -> «Структура» -> «Группа», или вы можете перейти к «Сводные таблицы» -> «Анализ» -> «Группа» -> «Выбор группы», или вы можете щелкнуть правой кнопкой мыши по одному элементу (из длинных данных) и выбрать «Группу» из ярлыка. меню. В любом случае, появляется поле Группировка, и Excel автоматически устанавливает значения Starting At и Ending At на основе ваших значений. Все, что вам нужно сделать, установить By на 0,01. Excel автоматически группирует все компании на основе их значений долготы, сгруппированных по требованию 0,01.