Как получить ссылку на результат формулы массива в списке?

Question

У меня есть два массива, каждый из которых содержит идентификаторы магазинов, координаты х и координаты у нас и конкурентов.

Я ищу для каждого магазина корпорации ABC, ее ближайшего конкурента в списке корпорации XYZ.

До сих пор мне удалось получить расстояние между каждым магазином ABC и ближайшим XYZ-компом с помощью формулы массива в столбце. H:

'{MIN((sqrt((Power(B3-$F$3:$F$6;2)+(power(C3-$G$3:$G$6;2))))))}

Я застрял в поиске для каждого ABCi имени XYZi (или ссылки на строку), соответствующего вычисленному значению в H.

Я два часа возился с Match() и Index() - как предлагалось в различных похожих вопросах по SE - и гуглил все, что мог придумать, но безрезультатно.

(Кажется, я вспоминаю, что для координат XY на сфероиде формула для расстояния между двумя точками на карте может отличаться от той, что используется здесь, но для моей реальной жизненной цели это достаточно хорошо.)

Answer 1

Я думаю, что формула массива может только усложнить дело. Фактический массив может показать все расстояния друг от друга =SQRT((E$2-$B4)^2+(E$3-$C4)^2) и затем:

= ВЫБРАТЬ (MATCH (MIN (Е4: Н4), $ Е4: $ H4,0), Е $ 1, Р $ 1, G $ 1, Н $ 1)

выбрать тот, который ближе всего в каждом случае:

Или, чтобы выделить минимум для каждой строки, вы можете использовать условное форматирование с формулой =E4=MIN($E4:$H4) .

Answer 2

Начиная с решения @pnuts, мы можем пропустить представление массива.

Чтобы получить минимальное расстояние между двумя точками на сетке между двумя координатами и множеством (формула массива ctrl + Shift + Enter):

'={MIN((sqrt((Power(B3-$F$3:$F$6;2)+(power(C3-$G$3:$G$6;2))))))}

Чтобы пропустить отображение массива, мы используем расчет расстояния (без MIN) в качестве второго аргумента для MATCH() . Он вернет позицию индекса значения в массиве.

Затем CHOOSE чтобы получить элемент k в списке E3:E6 который является источником минимального значения:

{CHOOSE(MATCH(MIN((sqrt((B3-$F$3:$F$6)^2+((C3-$G$3:$G$6)^2))))),
(sqrt(((B3-$F$3:$F$6)^2+((C3-$G$3:$G$6)^2)))),0),$E$3;$E$4;$E$5:$E$6)}

Так как это формула массива, она должна быть проверена с помощью Ctrl + Shift + Enter

Осталось только найти способ обойти ограничения 255 отдельных значений формулы CHOOSE для списка выше этого диапазона.

(Формула для получения расстояния между двумя координатами XY на сфероиде отличается от используемой здесь, но для небольшой 2/3 лат. Длинной. Область градусов достаточно близка.)