Поиск по текстовому значению

Дата: Среда, 14.05.2014, 16:10 | Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 160

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

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

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

К сообщению приложен файл: -2.xlsx (28.3 Kb)

Сообщение отредактировал hatter - Среда, 14.05.2014, 16:11

Дата: Среда, 14.05.2014, 17:45 | Сообщение № 2

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 160

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

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

Попробовала так:

Код

=ЕСЛИ(ЕОШИБКА(ВПР(ПСТР(A661;1;6);'Клиенты - адреса'!$C$2:$C$283;1;0));0;ПРАВСИМВ(ИНДЕКС('Клиенты - адреса'!$C$2:$C$283;ПОИСКПОЗ(ПСТР(A661;1;6);'Клиенты - адреса'!$C$2:$C$283));6))

но не выходит((

Дата: Среда, 14.05.2014, 22:30 | Сообщение № 3

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

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

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

Excel 2007,2010,2013

как-то так

К сообщению приложен файл: -2-1.xlsx (35.7 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Дата: Четверг, 15.05.2014, 10:15 | Сообщение № 4

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 160

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

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

Немного задача поменялась...Помогите, пожалуйста, подтянуть с листа "Справочник кодов" код города, на отдельный столбец листа Клиенты-адреса.

Дата: Четверг, 15.05.2014, 12:02 | Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 160

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

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

Пытаюсь поменять формулу под новые условия,но не выходит((

Дата: Четверг, 15.05.2014, 19:43 | Сообщение № 6

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

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

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

Excel 2007,2010,2013

какой-то монстр у меня получился

К сообщению приложен файл: -2-2.xlsx (36.5 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Дата: Четверг, 15.05.2014, 20:55 | Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 160

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

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

Крутая формула

Спасибо

только для Львова ( М.ЛЬВIВ) почему-то не возвращает код((

Дата: Четверг, 15.05.2014, 21:55 | Сообщение № 8

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

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

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

Excel 2007,2010,2013

Это все потому, что украинская І

Код

СИМВОЛ(178)

совсем не то же самое, что и английская I

Код

СИМВОЛ(73)

, хоть и выглядят совершенно одинаково. И еще там где в названиях стоит апостроф, там обязательно должен быть апостроф '

Код

СИМВОЛ(39)

, а не одинарная кавычка ’

Код

СИМВОЛ(146)

И еще, если вдруг не знаете, украинские буквы І и і вводятся с клавиатуры комбинациями Alt+0178 и Alt+0179 соответственно, буквы Ї и і - Alt+0175 и Alt+0191, буквы Ґ и ґ Alt+0165 Alt+0180, буквы Є и є Alt+0170 и Alt+0186, апостроф можно ввести комбинацией Alt+0039 если не хочется переключаться на английскую раскладку. Буквы І, і, Ї, ї, Ґ, ґ, Є, є вводятся только с русской раскладки набором соответствующего кода на цифровой клавиатуре при зажатом Alt

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Четверг, 15.05.2014, 21:57

Дата: Пятница, 16.05.2014, 00:16 | Сообщение № 9

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

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

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

Excel 2007,2010,2013

переписал формулу, исправил в ней небольшую ошибку, добавил возможность поиска не только города (М.), но и поселка (СЕЛ.)

Код

=ЕСЛИ(ИЛИ(ЕСЛИОШИБКА(НАЙТИ(ПРОПИСН(ЛЕВСИМВ({"М.";"СЕЛ."}&ИНДЕКС('Справочник кодов'!$A$1:$A$500;СУММ(ЕСЛИ(ЕСЛИОШИБКА(НАЙТИ({"М.";"СЕЛ."}&ПРОПИСН(ЛЕВСИМВ('Справочник кодов'!$A$1:$A$500;НАЙТИ(" ";'Справочник кодов'!$A$1:$A$500)-1));$C2);0)>0;СТРОКА('Справочник кодов'!$A$1:$A$500))));НАЙТИ(" "; {"М.";"СЕЛ."}&ИНДЕКС('Справочник кодов'!$A$1:$A$500;СУММ(ЕСЛИ(ЕСЛИОШИБКА(НАЙТИ({"М.";"СЕЛ."}&ПРОПИСН(ЛЕВСИМВ('Справочник кодов'!$A$1:$A$500;НАЙТИ(" ";'Справочник кодов'!$A$1:$A$500)-1));$C2);0)>0;СТРОКА('Справочник кодов'!$A$1:$A$500)))))-1));$C2);0)>0);ПРАВСИМВ({"М.";"СЕЛ."}&ИНДЕКС('Справочник кодов'!$A$1:$A$500;СУММ(ЕСЛИ(ЕСЛИОШИБКА(НАЙТИ({"М.";"СЕЛ."}&ПРОПИСН(ЛЕВСИМВ('Справочник кодов'!$A$1:$A$500;НАЙТИ(" ";'Справочник кодов'!$A$1:$A$500)-1));$C2);0)>0;СТРОКА('Справочник кодов'!$A$1:$A$500))));ДЛСТР({"М.";"СЕЛ."}&ИНДЕКС('Справочник кодов'!$A$1:$A$500;СУММ(ЕСЛИ(ЕСЛИОШИБКА(НАЙТИ({"М.";"СЕЛ."}&ПРОПИСН(ЛЕВСИМВ('Справочник кодов'!$A$1:$A$500;НАЙТИ(" ";'Справочник кодов'!$A$1:$A$500)-1));$C2);0)>0;СТРОКА('Справочник кодов'!$A$1:$A$500)))))+1-МИН(ЕСЛИОШИБКА(НАЙТИ({0;1;2;3;4;5;6;7;8;9};{"М.";"СЕЛ."}&ИНДЕКС('Справочник кодов'!$A$1:$A$500;СУММ(ЕСЛИ(ЕСЛИОШИБКА(НАЙТИ({"М.";"СЕЛ."}&ПРОПИСН(ЛЕВСИМВ('Справочник кодов'!$A$1:$A$500;НАЙТИ(" ";'Справочник кодов'!$A$1:$A$500)-1));$C2);0)>0;СТРОКА('Справочник кодов'!$A$1:$A$500)))));"")));"")

если нужно будет добавить какие-нибудь еще сокращения ,то в массив

Код

{"М.";"СЕЛ."}

нужно будет дописать нужное сокращение, например

Код

{"М.";"СЕЛ.";"GGG"}

и заменить этот массив в формуле
[p.s.]этой формулой только людей пугать

Код

если нужно будет добавить какие-нибудь еще сокращения ,то в массив

Код

{"М.";"СЕЛ."}

нужно будет дописать нужное сокращение, например

Код

{"М.";"СЕЛ.";"GGG"}

и заменить этот массив в формуле
[p.s.]этой формулой только людей пугать

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщениепереписал формулу, исправил в ней небольшую ошибку, добавил возможность поиска не только города (М.), но и поселка (СЕЛ.)

Код

если нужно будет добавить какие-нибудь еще сокращения ,то в массив

Код

{"М.";"СЕЛ."}

нужно будет дописать нужное сокращение, например

Код

{"М.";"СЕЛ.";"GGG"}

и заменить этот массив в формуле
[p.s.]этой формулой только людей пугать

Автор - krosav4ig
Дата добавления - 16.05.2014 в 00:16

gling

Дата: Пятница, 16.05.2014, 00:30 | Сообщение № 10

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2525

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

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

2010

Заменить 'Справочник кодов'!$A$1:$A$500 на именованный диапазон типа (Коды), и будет красиво выглядеть.

ЯД-41001506838083

Дата: Пятница, 16.05.2014, 10:32 | Сообщение № 11

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 160

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

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

Цитата krosav4ig, 16.05.2014 в 00:16, в сообщении № 10

P.S.этой формулой только людей пугать

Меня эта формула только восхищает!Она прекрасна!
А подскажите насчет этой строки: АДРЕС М.ХМЕЛЬНИЦЬКИЙ ПРОВ. СТЕЛЬМАХА БУД. 5 ТЕЛЕФОН 727515
по ней телефон не возвращается, хотя вроде все буквы правильные...
В справочнике кодов он: Хмельницький– 0382, 03822

Сообщение отредактировал hatter - Пятница, 16.05.2014, 10:35

Дата: Пятница, 16.05.2014, 21:09 | Сообщение № 12

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

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

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

Excel 2007,2010,2013

а у меня формула нормально все ищет

(-2-3.xlsx)

может у вас ХМЕЛЬНИЦЬКИЙ в справочнике кодов на строке>500...
тогда смотрите -2-4.xlsm, в нем обратите внимание на udf, новые имена и условное форматирование на листе с адресами (красными выделяются те ячейки, которые содержат "неправильные" буквы и символы). И в качестве бонуса в файле несколько небольших макросов.

К сообщению приложен файл: -2-3.xlsx (37.3 Kb) · -2-4.xlsm (49.6 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Суббота, 17.05.2014, 17:08