Поиск по текстовому значению
hatter
Дата: Среда, 14.05.2014, 16:10 |
Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 160
Репутация:
0
±
Замечаний:
0% ±
Здравствуйте, помогите придумать формулу: у меня есть файл, на листе "клиенты-адреса "есть третий столбец "Данные" и второй лист "справочник кодов" с перечнем названий населенных пунктов, мне необходимо на этот лист рядом с каждым населенным пунктом проставить рядом в столбце номер телефона с первого листа со столбца "Данные".
Здравствуйте, помогите придумать формулу: у меня есть файл, на листе "клиенты-адреса "есть третий столбец "Данные" и второй лист "справочник кодов" с перечнем названий населенных пунктов, мне необходимо на этот лист рядом с каждым населенным пунктом проставить рядом в столбце номер телефона с первого листа со столбца "Данные". hatter
К сообщению приложен файл:
-2.xlsx
(28.3 Kb)
Сообщение отредактировал hatter - Среда, 14.05.2014, 16:11
Ответить
Сообщение Здравствуйте, помогите придумать формулу: у меня есть файл, на листе "клиенты-адреса "есть третий столбец "Данные" и второй лист "справочник кодов" с перечнем названий населенных пунктов, мне необходимо на этот лист рядом с каждым населенным пунктом проставить рядом в столбце номер телефона с первого листа со столбца "Данные". Автор - hatter Дата добавления - 14.05.2014 в 16:10
hatter
Дата: Среда, 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))
но не выходит((
Попробовала так: Код
=ЕСЛИ(ЕОШИБКА(ВПР(ПСТР(A661;1;6);'Клиенты - адреса'!$C$2:$C$283;1;0));0;ПРАВСИМВ(ИНДЕКС('Клиенты - адреса'!$C$2:$C$283;ПОИСКПОЗ(ПСТР(A661;1;6);'Клиенты - адреса'!$C$2:$C$283));6))
но не выходит(( hatter
Ответить
Сообщение Попробовала так: Код
=ЕСЛИ(ЕОШИБКА(ВПР(ПСТР(A661;1;6);'Клиенты - адреса'!$C$2:$C$283;1;0));0;ПРАВСИМВ(ИНДЕКС('Клиенты - адреса'!$C$2:$C$283;ПОИСКПОЗ(ПСТР(A661;1;6);'Клиенты - адреса'!$C$2:$C$283));6))
но не выходит(( Автор - hatter Дата добавления - 14.05.2014 в 17:45
krosav4ig
Дата: Среда, 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
Ответить
Сообщение как-то так Автор - krosav4ig Дата добавления - 14.05.2014 в 22:30
hatter
Дата: Четверг, 15.05.2014, 10:15 |
Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 160
Репутация:
0
±
Замечаний:
0% ±
Немного задача поменялась...Помогите, пожалуйста, подтянуть с листа "Справочник кодов" код города, на отдельный столбец листа Клиенты-адреса.
Немного задача поменялась...Помогите, пожалуйста, подтянуть с листа "Справочник кодов" код города, на отдельный столбец листа Клиенты-адреса. hatter
Ответить
Сообщение Немного задача поменялась...Помогите, пожалуйста, подтянуть с листа "Справочник кодов" код города, на отдельный столбец листа Клиенты-адреса. Автор - hatter Дата добавления - 15.05.2014 в 10:15
hatter
Дата: Четверг, 15.05.2014, 12:02 |
Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 160
Репутация:
0
±
Замечаний:
0% ±
Пытаюсь поменять формулу под новые условия,но не выходит((
Пытаюсь поменять формулу под новые условия,но не выходит(( hatter
Ответить
Сообщение Пытаюсь поменять формулу под новые условия,но не выходит(( Автор - hatter Дата добавления - 15.05.2014 в 12:02
krosav4ig
Дата: Четверг, 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
Ответить
Сообщение какой-то монстр у меня получился Автор - krosav4ig Дата добавления - 15.05.2014 в 19:43
hatter
Дата: Четверг, 15.05.2014, 20:55 |
Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 160
Репутация:
0
±
Замечаний:
0% ±
Крутая формула Спасибо только для Львова ( М.ЛЬВIВ) почему-то не возвращает код((
Крутая формула Спасибо только для Львова ( М.ЛЬВIВ) почему-то не возвращает код(( hatter
Ответить
Сообщение Крутая формула Спасибо только для Львова ( М.ЛЬВIВ) почему-то не возвращает код(( Автор - hatter Дата добавления - 15.05.2014 в 20:55
krosav4ig
Дата: Четверг, 15.05.2014, 21:55 |
Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация:
989
±
Замечаний:
0% ±
Excel 2007,2010,2013
Это все потому, что украинская І совсем не то же самое, что и английская I , хоть и выглядят совершенно одинаково. И еще там где в названиях стоит апостроф, там обязательно должен быть апостроф ' , а не одинарная кавычка ’ И еще, если вдруг не знаете, украинские буквы І и і вводятся с клавиатуры комбинациями Alt+0178 и Alt+0179 соответственно, буквы Ї и і - Alt+0175 и Alt+0191, буквы Ґ и ґ Alt+0165 Alt+0180, буквы Є и є Alt+0170 и Alt+0186, апостроф можно ввести комбинацией Alt+0039 если не хочется переключаться на английскую раскладку. Буквы І, і, Ї, ї, Ґ, ґ, Є, є вводятся только с русской раскладки набором соответствующего кода на цифровой клавиатуре при зажатом Alt
Это все потому, что украинская І совсем не то же самое, что и английская I , хоть и выглядят совершенно одинаково. И еще там где в названиях стоит апостроф, там обязательно должен быть апостроф ' , а не одинарная кавычка ’ И еще, если вдруг не знаете, украинские буквы І и і вводятся с клавиатуры комбинациями Alt+0178 и Alt+0179 соответственно, буквы Ї и і - Alt+0175 и Alt+0191, буквы Ґ и ґ Alt+0165 Alt+0180, буквы Є и є Alt+0170 и Alt+0186, апостроф можно ввести комбинацией Alt+0039 если не хочется переключаться на английскую раскладку. Буквы І, і, Ї, ї, Ґ, ґ, Є, є вводятся только с русской раскладки набором соответствующего кода на цифровой клавиатуре при зажатом Alt krosav4ig
email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
Сообщение отредактировал krosav4ig - Четверг, 15.05.2014, 21:57
Ответить
Сообщение Это все потому, что украинская І совсем не то же самое, что и английская I , хоть и выглядят совершенно одинаково. И еще там где в названиях стоит апостроф, там обязательно должен быть апостроф ' , а не одинарная кавычка ’ И еще, если вдруг не знаете, украинские буквы І и і вводятся с клавиатуры комбинациями Alt+0178 и Alt+0179 соответственно, буквы Ї и і - Alt+0175 и Alt+0191, буквы Ґ и ґ Alt+0165 Alt+0180, буквы Є и є Alt+0170 и Alt+0186, апостроф можно ввести комбинацией Alt+0039 если не хочется переключаться на английскую раскладку. Буквы І, і, Ї, ї, Ґ, ґ, Є, є вводятся только с русской раскладки набором соответствующего кода на цифровой клавиатуре при зажатом Alt Автор - krosav4ig Дата добавления - 15.05.2014 в 21:55
krosav4ig
Дата: Пятница, 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)))));"")));"")
если нужно будет добавить какие-нибудь еще сокращения ,то в массив нужно будет дописать нужное сокращение, например и заменить этот массив в формуле [p.s.]этой формулой только людей пугать
переписал формулу, исправил в ней небольшую ошибку, добавил возможность поиска не только города (М.), но и поселка (СЕЛ.) Код
=ЕСЛИ(ИЛИ(ЕСЛИОШИБКА(НАЙТИ(ПРОПИСН(ЛЕВСИМВ({"М.";"СЕЛ."}&ИНДЕКС('Справочник кодов'!$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)))));"")));"")
если нужно будет добавить какие-нибудь еще сокращения ,то в массив нужно будет дописать нужное сокращение, например и заменить этот массив в формуле [p.s.]этой формулой только людей пугать krosav4ig
email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
Ответить
Сообщение переписал формулу, исправил в ней небольшую ошибку, добавил возможность поиска не только города (М.), но и поселка (СЕЛ.) Код
=ЕСЛИ(ИЛИ(ЕСЛИОШИБКА(НАЙТИ(ПРОПИСН(ЛЕВСИМВ({"М.";"СЕЛ."}&ИНДЕКС('Справочник кодов'!$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)))));"")));"")
если нужно будет добавить какие-нибудь еще сокращения ,то в массив нужно будет дописать нужное сокращение, например и заменить этот массив в формуле [p.s.]этой формулой только людей пугать Автор - krosav4ig Дата добавления - 16.05.2014 в 00:16
gling
Дата: Пятница, 16.05.2014, 00:30 |
Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2525
Репутация:
678
±
Замечаний:
0% ±
2010
Заменить 'Справочник кодов'!$A$1:$A$500 на именованный диапазон типа (Коды), и будет красиво выглядеть.
Заменить 'Справочник кодов'!$A$1:$A$500 на именованный диапазон типа (Коды), и будет красиво выглядеть. gling
ЯД-41001506838083
Ответить
Сообщение Заменить 'Справочник кодов'!$A$1:$A$500 на именованный диапазон типа (Коды), и будет красиво выглядеть. Автор - gling Дата добавления - 16.05.2014 в 00:30
hatter
Дата: Пятница, 16.05.2014, 10:32 |
Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 160
Репутация:
0
±
Замечаний:
0% ±
P.S.этой формулой только людей пугать
Меня эта формула только восхищает!Она прекрасна! А подскажите насчет этой строки: АДРЕС М.ХМЕЛЬНИЦЬКИЙ ПРОВ. СТЕЛЬМАХА БУД. 5 ТЕЛЕФОН 727515 по ней телефон не возвращается, хотя вроде все буквы правильные... В справочнике кодов он: Хмельницький– 0382, 03822
P.S.этой формулой только людей пугать
Меня эта формула только восхищает!Она прекрасна! А подскажите насчет этой строки: АДРЕС М.ХМЕЛЬНИЦЬКИЙ ПРОВ. СТЕЛЬМАХА БУД. 5 ТЕЛЕФОН 727515 по ней телефон не возвращается, хотя вроде все буквы правильные... В справочнике кодов он: Хмельницький– 0382, 03822hatter
Сообщение отредактировал hatter - Пятница, 16.05.2014, 10:35
Ответить
Сообщение P.S.этой формулой только людей пугать
Меня эта формула только восхищает!Она прекрасна! А подскажите насчет этой строки: АДРЕС М.ХМЕЛЬНИЦЬКИЙ ПРОВ. СТЕЛЬМАХА БУД. 5 ТЕЛЕФОН 727515 по ней телефон не возвращается, хотя вроде все буквы правильные... В справочнике кодов он: Хмельницький– 0382, 03822Автор - hatter Дата добавления - 16.05.2014 в 10:32
krosav4ig
Дата: Пятница, 16.05.2014, 21:09 |
Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 2347
Репутация:
989
±
Замечаний:
0% ±
Excel 2007,2010,2013
а у меня формула нормально все ищет (-2-3.xlsx ) может у вас ХМЕЛЬНИЦЬКИЙ в справочнике кодов на строке>500... тогда смотрите -2-4.xlsm , в нем обратите внимание на udf, новые имена и условное форматирование на листе с адресами (красными выделяются те ячейки, которые содержат "неправильные" буквы и символы). И в качестве бонуса в файле несколько небольших макросов.
а у меня формула нормально все ищет (-2-3.xlsx ) может у вас ХМЕЛЬНИЦЬКИЙ в справочнике кодов на строке>500... тогда смотрите -2-4.xlsm , в нем обратите внимание на udf, новые имена и условное форматирование на листе с адресами (красными выделяются те ячейки, которые содержат "неправильные" буквы и символы). И в качестве бонуса в файле несколько небольших макросов. krosav4ig
email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
Сообщение отредактировал krosav4ig - Суббота, 17.05.2014, 17:08
Ответить
Сообщение а у меня формула нормально все ищет (-2-3.xlsx ) может у вас ХМЕЛЬНИЦЬКИЙ в справочнике кодов на строке>500... тогда смотрите -2-4.xlsm , в нем обратите внимание на udf, новые имена и условное форматирование на листе с адресами (красными выделяются те ячейки, которые содержат "неправильные" буквы и символы). И в качестве бонуса в файле несколько небольших макросов. Автор - krosav4ig Дата добавления - 16.05.2014 в 21:09