Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Последовательное сравнение массивов - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Последовательное сравнение массивов (Формулы/Formulas)
Последовательное сравнение массивов
Mizantrop4 Дата: Четверг, 20.03.2014, 16:15 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте,

Вообще не люблю просить о помощи, стараюсь все найти сам, но сегодня без вас, ребят, не получается.

Проблема в следующем - есть 2 массива данных (столбец1) и (столбец2)
Столбец один выглядит примерно следующим образом:
Москва
Санкт-Петербуг
Саратов
и.т.д

Второй столбец:
656002, Г.БАРНАУЛ, УЛ.СОВЕТСКАЯ, 8, тел.241606
656024, Г.БАРНАУЛ, ЗМЕИНОГОРСКИЙ ТРАКТ, 112, тел.688673
658837, АЛТАЙСКИЙ КРАЙ, Г.ЯРОВОЕ, УЛ.ГАГАРИНА, 5, тел.32386
658213, АЛТАЙСКИЙ КРАЙ, Г.РУБЦОВСК, УЛ. ГРОМОВА, 15, тел.34705
и.т.д

Если говоря вкратце, то там перечень всех городов России и перечень адресов.

Надо вытащить город из второго столбца.

Задача примерно следующая:
Берется поисковой запрос из города , к примеру, Москва и ищется во втором столбце , и все в следующей строке от мед. заведения пишет город.

P.S. Из за ограничение размеров не смог уместить весь список городов и адресов, но мне требуется только формула или макрос.
P.P.S. Формула будет продолжаться до 12000 мед.заведений
К сообщению приложен файл: 1537896.xlsx (45.4 Kb)


Сообщение отредактировал Mizantrop4 - Четверг, 20.03.2014, 16:16
 
Ответить
СообщениеЗдравствуйте,

Вообще не люблю просить о помощи, стараюсь все найти сам, но сегодня без вас, ребят, не получается.

Проблема в следующем - есть 2 массива данных (столбец1) и (столбец2)
Столбец один выглядит примерно следующим образом:
Москва
Санкт-Петербуг
Саратов
и.т.д

Второй столбец:
656002, Г.БАРНАУЛ, УЛ.СОВЕТСКАЯ, 8, тел.241606
656024, Г.БАРНАУЛ, ЗМЕИНОГОРСКИЙ ТРАКТ, 112, тел.688673
658837, АЛТАЙСКИЙ КРАЙ, Г.ЯРОВОЕ, УЛ.ГАГАРИНА, 5, тел.32386
658213, АЛТАЙСКИЙ КРАЙ, Г.РУБЦОВСК, УЛ. ГРОМОВА, 15, тел.34705
и.т.д

Если говоря вкратце, то там перечень всех городов России и перечень адресов.

Надо вытащить город из второго столбца.

Задача примерно следующая:
Берется поисковой запрос из города , к примеру, Москва и ищется во втором столбце , и все в следующей строке от мед. заведения пишет город.

P.S. Из за ограничение размеров не смог уместить весь список городов и адресов, но мне требуется только формула или макрос.
P.P.S. Формула будет продолжаться до 12000 мед.заведений
Автор - Mizantrop4
Дата добавления - 20.03.2014 в 16:15
_Boroda_ Дата: Четверг, 20.03.2014, 16:27 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Теоретически должна работать такая формула
Код
=ИНДЕКС($A$1:$A$72;МИН(ЕСЛИ(ЕЧИСЛО(ПОИСК($A$2:$A$72;B2));СТРОКА($A$2:$A$72))))

Но список городов должен быть ДЕЙСТВИТЕЛЬНО полный
И все равно будут ошибки
Можно еще так
Код
=ПСТР(B2;НАЙТИ(", Г.";B2)+4;ПОИСК(",";ПСТР(B2;НАЙТИ(", Г.";B2)+4;99))-1)

Но тоже будут ошибки

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТеоретически должна работать такая формула
Код
=ИНДЕКС($A$1:$A$72;МИН(ЕСЛИ(ЕЧИСЛО(ПОИСК($A$2:$A$72;B2));СТРОКА($A$2:$A$72))))

Но список городов должен быть ДЕЙСТВИТЕЛЬНО полный
И все равно будут ошибки
Можно еще так
Код
=ПСТР(B2;НАЙТИ(", Г.";B2)+4;ПОИСК(",";ПСТР(B2;НАЙТИ(", Г.";B2)+4;99))-1)

Но тоже будут ошибки
Автор - _Boroda_
Дата добавления - 20.03.2014 в 16:27
Mizantrop4 Дата: Четверг, 20.03.2014, 16:40 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Цитата _Boroda_, 20.03.2014 в 16:27, в сообщении № 2
Теоретически должна работать такая формула

=ИНДЕКС($A$1:$A$72;МИН(ЕСЛИ(ЕЧИСЛО(ПОИСК($A$2:$A$72;B2));СТРОКА($A$2:$A$72))))

Но список городов должен быть ДЕЙСТВИТЕЛЬНО полный
И все равно будут ошибки
Можно еще так

=ПСТР(B2;НАЙТИ(", Г.";B2)+4;ПОИСК(",";ПСТР(B2;НАЙТИ(", Г.";B2)+4;99))-1)

Но тоже будут ошибки


Для начала, спасибо большое за помощь.

Первая формула подставляет значения первого города
Получается примерно так
Москва - адрес г.Барнаул - (выводит) Москва

Вторая формула хороша, ошибки есть, но править реально. Только одно но , она очень придирчива к регистру, на некоторых городах "г.", а на некоторых "Г.".
 
Ответить
Сообщение
Цитата _Boroda_, 20.03.2014 в 16:27, в сообщении № 2
Теоретически должна работать такая формула

=ИНДЕКС($A$1:$A$72;МИН(ЕСЛИ(ЕЧИСЛО(ПОИСК($A$2:$A$72;B2));СТРОКА($A$2:$A$72))))

Но список городов должен быть ДЕЙСТВИТЕЛЬНО полный
И все равно будут ошибки
Можно еще так

=ПСТР(B2;НАЙТИ(", Г.";B2)+4;ПОИСК(",";ПСТР(B2;НАЙТИ(", Г.";B2)+4;99))-1)

Но тоже будут ошибки


Для начала, спасибо большое за помощь.

Первая формула подставляет значения первого города
Получается примерно так
Москва - адрес г.Барнаул - (выводит) Москва

Вторая формула хороша, ошибки есть, но править реально. Только одно но , она очень придирчива к регистру, на некоторых городах "г.", а на некоторых "Г.".
Автор - Mizantrop4
Дата добавления - 20.03.2014 в 16:40
_Boroda_ Дата: Четверг, 20.03.2014, 16:52 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата Mizantrop4, 20.03.2014 в 16:40, в сообщении № 3
Первая формула подставляет значения первого города

Это формула массива. Вводить ее нужно одновременным нажатием Контрл Шифт Ентер
Цитата Mizantrop4, 20.03.2014 в 16:40, в сообщении № 3
на некоторых городах "г.", а на некоторых "Г.".

Замените НАЙТИ на ПОИСК
Код
=ПСТР(B2;ПОИСК(", Г.";B2)+4;ПОИСК(",";ПСТР(B2;ПОИСК(", Г.";B2)+4;99))-1)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата Mizantrop4, 20.03.2014 в 16:40, в сообщении № 3
Первая формула подставляет значения первого города

Это формула массива. Вводить ее нужно одновременным нажатием Контрл Шифт Ентер
Цитата Mizantrop4, 20.03.2014 в 16:40, в сообщении № 3
на некоторых городах "г.", а на некоторых "Г.".

Замените НАЙТИ на ПОИСК
Код
=ПСТР(B2;ПОИСК(", Г.";B2)+4;ПОИСК(",";ПСТР(B2;ПОИСК(", Г.";B2)+4;99))-1)
Автор - _Boroda_
Дата добавления - 20.03.2014 в 16:52
AlexM Дата: Четверг, 20.03.2014, 18:01 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4511
Репутация: 1128 ±
Замечаний: 0% ±

Excel 2003
Длинная формула
Код
=ПРОПНАЧ(ПСТР(ЗАМЕНИТЬ(B2;ПОИСК(",";B2;ПРОСМОТР(0,1;1/ПОИСК(A$2:A$100;B2);ПОИСК(A$2:A$100;B2)));99;" ");ПРОСМОТР(0,1;1/ПОИСК(A$2:A$100;B2);ПОИСК(A$2:A$100;B2));99))
К сообщению приложен файл: 1537896_new.rar (34.2 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеДлинная формула
Код
=ПРОПНАЧ(ПСТР(ЗАМЕНИТЬ(B2;ПОИСК(",";B2;ПРОСМОТР(0,1;1/ПОИСК(A$2:A$100;B2);ПОИСК(A$2:A$100;B2)));99;" ");ПРОСМОТР(0,1;1/ПОИСК(A$2:A$100;B2);ПОИСК(A$2:A$100;B2));99))
Автор - AlexM
Дата добавления - 20.03.2014 в 18:01
AlexM Дата: Четверг, 20.03.2014, 18:26 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4511
Репутация: 1128 ±
Замечаний: 0% ±

Excel 2003
Пояснение к формуле из предыдущего сообщения
Фрагмент
Код
ПРОСМОТР(0,1;1/ПОИСК(Лист1!$A$2:$A$100;Лист1!$B2);ПОИСК(Лист1!$A$2:$A$100;Лист1!$B2))
определяет позицию в строке, с которой начинается название города.
Этому фрагменту можно присвоить имя, например PG
Тогда формула примет вид
Код
=ПРОПНАЧ(ПСТР(ЗАМЕНИТЬ(B2;ПОИСК(",";B2;PG);99;"");PG;99))

Фрагмент
Код
ЗАМЕНИТЬ(B2;ПОИСК(",";B2;PG);99;"")
Удаляет часть строки, которая начинается после названия города. Далее функцией ПСТР() выделяем название города.
К сообщению приложен файл: 1537896_new1.rar (34.3 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Четверг, 20.03.2014, 18:27
 
Ответить
СообщениеПояснение к формуле из предыдущего сообщения
Фрагмент
Код
ПРОСМОТР(0,1;1/ПОИСК(Лист1!$A$2:$A$100;Лист1!$B2);ПОИСК(Лист1!$A$2:$A$100;Лист1!$B2))
определяет позицию в строке, с которой начинается название города.
Этому фрагменту можно присвоить имя, например PG
Тогда формула примет вид
Код
=ПРОПНАЧ(ПСТР(ЗАМЕНИТЬ(B2;ПОИСК(",";B2;PG);99;"");PG;99))

Фрагмент
Код
ЗАМЕНИТЬ(B2;ПОИСК(",";B2;PG);99;"")
Удаляет часть строки, которая начинается после названия города. Далее функцией ПСТР() выделяем название города.
Автор - AlexM
Дата добавления - 20.03.2014 в 18:26
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Последовательное сравнение массивов (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!