Поиск значения из одного столбца в другом (формулы)

Дата: Понедельник, 20.02.2017, 00:13 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Доброго времени суток!

Очень нужна помощь. Есть две таблицы с адресами. Необходимо найти улицу из таблицы 1 в таблице 2 и подставить код из таблицы 2 в таблицу 1.
Подскажите формулу для решения такой задачи.
Буду признательна за помощь.
Спасибо.

К сообщению приложен файл: _1.xls (37.5 Kb)

Сообщение отредактировал LesGor - Понедельник, 20.02.2017, 00:14

Дата: Понедельник, 20.02.2017, 00:29 | Сообщение № 2

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

Репутация: 6479 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Всё скорее всего не найдет, но многое

Код

=ЕСЛИОШИБКА(ИНДЕКС(I$3:I$75;ПОИСКПОЗ("*"&B3&"*"&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(C3;",";" ");".";" "));" ";"*")&"*";H$3:H$75;));"")

И Вам нужно не только по улице, но и по дому - Белгород, ул. Попова есть два кода

К сообщению приложен файл: 6646464_1.xls (48.0 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

enzo

Дата: Понедельник, 20.02.2017, 11:56 | Сообщение № 3

Группа: Пользователи

Ранг: Участник

Сообщений: 74

Репутация: 1 ±

Замечаний: 0% ±

Excel 2010

Это получается поиск по частичному совпадению ? А как он понимает то ? Тут фишка в "*" ?

Дата: Понедельник, 20.02.2017, 12:03 | Сообщение № 4

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

Репутация: 6479 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Цитата enzo, 20.02.2017 в 11:56, в сообщении № 3 ()

Тут фишка в "*" ?

Ага.
* - любое количество (включая нулевое) любых символов
? - один любой символ

Цитата enzo, 20.02.2017 в 11:56, в сообщении № 3 ()

Это получается поиск по частичному совпадению ?

Нууу, не совсем. Чтобы совсем, то вот здесь 10 листов http://www.excelworld.ru/forum/3-717-1

Дата: Понедельник, 20.02.2017, 23:33 | Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Спасибо за формулу

Очень облегчило работу со списками)))
Но к сожалению, да, если встречаются одинаковые улицы, действительно не ищет(( а можно как-то формуле учитывать и номер дома?
буду признательна или за помощь или подсказку))))

Дата: Понедельник, 20.02.2017, 23:51 | Сообщение № 6

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

Репутация: 6479 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Цитата LesGor, 20.02.2017 в 23:33, в сообщении № 5 ()

если встречаются одинаковые улицы, действительно не ищет(( а можно как-то формуле учитывать и номер дома?

А у меня в формуле учитывается номер дома

Дата: Вторник, 21.02.2017, 20:36 | Сообщение № 7

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

извините, я просто как ни старалась разобраться в формуле, всё равно для меня как темный лес(((

Дата: Среда, 22.02.2017, 10:59 | Сообщение № 8

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

Репутация: 6479 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Давайте поясню.

1. ПОДСТАВИТЬ(C3;",";" ") - в значении из ячейки С3 меняем все запятые на пробелы

2. ПОДСТАВИТЬ(п.1;".";" ") - в полученном в п.1 меняем все точки на пробелы

3. СЖПРОБЕЛЫ(п.2) - в полученном в п.2 убираем все пробелы спереду и сзаду и преобразуем все двойные-тройные-... пробелы в одинарные (из " ввв ааа " получится "ввв ааа")

4. ПОДСТАВИТЬ(п.3;" ";"*") - в полученном в п.3 меняем все пробелы на звездочки

5. "*"&B3&"*"&п.4&"*" - склеиваем звездочку, значение из ячейки В3, звездочку, полученное в п.4, звездочку

6. ПОИСКПОЗ(п.5;H$3:H$75;) - ищем номер вхождения полученного в п.5 в массиве Н3:Н75. Для ПОИСКПОЗа звездочка - это, как я уже писал ранее, любое количество (включая нулевое) любых символов. Это сделано для игнорирования при поиске всяческих лишних символов, типа "ул" или "кв", а также знаков препинания

7. ИНДЕКС(I$3:I$75;п.6) - вынимаем из диапазона I3:I75 то по порядку значение, которое получили в п.6

8. ЕСЛИОШИБКА(п.7;"") - если что-то пошло не так, то пустая строка

Дата: Вторник, 28.02.2017, 22:12 | Сообщение № 9

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

всё как по полочкам разложили и теперь стало намного понятнее)) спасибо)))