Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Определение ближайшего города, Имея список городов и расстояние между ними
 
Имея список городов и расстояние между ними, определить по каждому из городов в списке ближайший к нему город. Возможно объяснил не совсем понятно.
Прикладываю файл, в котором есть таблица с городами и расстояниями между ними.
Города расположены в столбце A и те же города транспонированы в строку 1. Сама таблица заполнена расстояниями между каждым из городов (в километрах). Так как список в столбце и в строке один и тот же, то по диагонали таблицы идут нули.

Ниже есть вторая небольшая табличка со списком городов. нужно на против каждого указать ближайший к нему.
Есть один нюанс. Города повторяются. Нужно прописать формулу таким образом. что бы при определении ближайшего города, не учитывалось раастояние между одинаковыми городами. То есть например при определении ближайшего города к Барнаулу, формула не подтягивала Барнаул.

 
 
Umar_06, а какой выбирать бранаул если их 4? и для других городов так же
 
С формулами - не помощник, а вот вариант решения на Power Query могу предложить.
 
массивная
=INDEX($1:$1;MOD(MIN(IF(($B$2:$AD$25=MIN(IF(($B$1:$AD$1<>A29)*($A$2:$A$25=A29);$B$2:$AD$25)))*($A$2:$A$25=A29);ROW($A$2:$A$25)+COLUMN($B$1:$AD$1)%%));1)/1%%)

Если не Пух, то Пых :-)
Изменено: БМВ - 12 Сен 2018 10:15:01
 
Цитата
a.i.mershik написал:
а какой выбирать бранаул если их 4?
Я не заморачивался, и выбирал верхний.
 
a.i.mershik, города в списке повторяются, потому что в одном городе есть несколько точек продаж (назовем их так). Я их удалил. Пусть будет первый город из списка.
 
PooHkrd, я так понимаю это надстройка, которую можно скачать?
А какую функцию выбрали в ней?
 
У вас какой Excel?
 
PooHkrd у меня 2013
 
В таком случае вам нужно сначала установить надстройку, ознакомиться хотя бы с интерфейсом и после этого вопрос
Цитата
Umar_06 написал:
А какую функцию выбрали в ней?
должен отпасть сам собой, Там не одна функция была выбрана, несколько разных, расписывать каждую на форуме - этио ж никакого времени не хватит. Тем паче когда для этого есть справочники.
Изменено: PooHkrd - 12 Сен 2018 11:41:53
 
БМВ, я воспользовался вашей формулой, но почему то у вас она корректно работает, а у меня подтягивает те же города.
К сожалению файл вложить не получается, так как слишком много весит.
Таблица точно такая же как и в примере, только намного больше. Соответственно подогнал формулу под большую таблицу.

=ИНДЕКС($1:$1;ОСТАТ(МИН(ЕСЛИ(($B$2:$AFO$847=МИН(ЕСЛИ(($B$1:$AFO$1<>A851)*($A$2:$A$847=A851);$B$2:$AFO$847)))*($A$2:$A$847=A851);СТРОКА($A$2:$A$847)+СТОЛБЕЦ($B$1:$AFO$1)%%));1)/1%%)

Что еще нужно поменять в формуле?
 
Umar_06, Странно, вроде формульный вариант дает идентичный вариант, но вы продолжаете мучаться с Power Query. Ни против этого решения ни против самой надстройки ничего не имею, но ...
 
БМВ, день добрый , а можно коротко о %% в вашей формуле!)
 
a.i.mershik,  %-деление на 100, %% аналогично делению на 10000 номер столбца переносим в дробную часть  Можно конечно было и одним % обойтись, так как в примере столбцов менее 100, но это так по привычке. Соответственно после того как выделили дробную часть через остаток от деления на 1, возвращаем путем делений на 1%% что равносильно делению на 1/10000 ну или умножению на 10000. По другому записать получится просто чуть длиннее *10^-4
 
БМВ, спасибо.
 
БМВ, я надстройкой еще не пользовался.
Дублирую вопрос по вашей формуле. Наверное не заметили его.

воспользовался вашей формулой, но почему то у вас она корректно работает, а у меня подтягивает те же города.
К сожалению файл вложить не получается, так как слишком много весит.
Таблица точно такая же как и в примере, только намного больше. Соответственно подогнал формулу под большую таблицу.

=ИНДЕКС($1:$1;ОСТАТ(МИН(ЕСЛИ(($B$2:$AFO$847=МИН(ЕСЛИ(($B$1:$AFO$1<>A851)*($A$2:$A$847=A851);$B$2:$AFO$847)))*($A$2:$A$847=A851);СТРОКА($A$2:$A$847)+СТОЛБЕЦ($B$1:$AFO$1)%%));1)/1%%)

Что еще нужно поменять в формуле?
 
Цитата
БМВ написал:
массивная
это не к тому что большая, а к тому что это формула массива и вводится не просто ENTER, а CTRL+SHIFT+ENTER

Однако данных очень много, то пересчет может быть заметным по времени.
 
То же вариант, формула массива.
=ИНДЕКС(B$1:AD$1;ПОИСКПОЗ(МИН(ЕСЛИ($B$1:$AD$1<>A29;ИНДЕКС($B$2:$AD$25;ПОИСКПОЗ(A29;$A$2:$A$25;0);)));ИНДЕКС($B$2:$AD$25;ПОИСКПОЗ(A29;$A$2:$A$25;0););0))
 
AleksSid,  да, так  можно и даже быстрее, я честно признаться не смотрел на то, что ближайший город будет в первой строке группы.
 
AleksSid никак не могу понять, почему эта формула не хочет работать с большой таблицей.
По структуре таблица идентична той, что в примере, просто намного больше.
Соответственно я просто подставляю свои данные в формулу, но у меня формула начинает тянуть минимальные расстояния нули.
Как у вас получилось в вашем случае предусмотреть. что бы к городам из списка не тянулись те же города?
Например, у вас в формуле к Барнаулу подтянулся Новоалтайск, хотя в той строке и есть меньшие значения (другие точки барнаула)

Моя формула выглядит так:

=ИНДЕКС(B$1:AFO$1;ПОИСКПОЗ(МИН(ЕСЛИ($B$1:$AFO$1<>A851;ИНДЕКС($B$2:$AFO$847;ПОИСКПОЗ(A851;$A$2:$A$847;0);)));ИНДЕКС($B$2:$AFO$847;ПОИСКПОЗ(A851;$A$2:$A$847;0););0))
 
Цитата
БМВ написал:
формула массива и вводится не просто ENTER, а CTRL+SHIFT+ENTERUmar_06 написал:
Цитата
что бы к городам из списка не тянулись те же города?
Отвечает эта часть формулы.
МИН(ЕСЛИ($B$1:$AFO$1<>A851
Обратите внимание на ";" после ПОИСКПОЗ(), а также на размер диапазонов.
Изменено: AleksSid - 14 Сен 2018 13:47:36
Страницы: 1
Читают тему (гостей: 1)
Наверх