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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск части текста в ячейке из массива данных - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск части текста в ячейке из массива данных (Формулы/Formulas)
Поиск части текста в ячейке из массива данных
fyx Дата: Пятница, 06.05.2016, 21:59 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Добрый день коллеги.
Уже целый день сижу в гугле, не могу найти решение проблемы.
Есть два столбца данных.
Нужно найти в них совпадения, причем необходимо задавать количество символов совпадений.
Например:

5-НОК 50МГ. №50 ТАБ. П/О /ЛЕК/

должно ровняться именно

5-НОК N50 таб.

А еще сложнее найти совпадение

911-БАДЯГА ГЕЛЬ Д/ТЕЛА ОТ СИНЯКОВ И УШИБОВ 100МЛ.

и

БАДЯГА 911 от синяков и ушибов 100мл

То есть поиск нужно осуществлять по последовательности:
Сперва искать совпадение четырех слов в разной последовательности в столбцах, потом трех, двух одного.
И если не нашел, то искать уже часть слова, но опять же по 8 символам совпадения, по 7,6,5 ...
И в столбец "с" вставлять значение найденного для проверки.
К сообщению приложен файл: 7053441.xls(46Kb)


Сообщение отредактировал fyx - Пятница, 06.05.2016, 22:02
 
Ответить
СообщениеДобрый день коллеги.
Уже целый день сижу в гугле, не могу найти решение проблемы.
Есть два столбца данных.
Нужно найти в них совпадения, причем необходимо задавать количество символов совпадений.
Например:

5-НОК 50МГ. №50 ТАБ. П/О /ЛЕК/

должно ровняться именно

5-НОК N50 таб.

А еще сложнее найти совпадение

911-БАДЯГА ГЕЛЬ Д/ТЕЛА ОТ СИНЯКОВ И УШИБОВ 100МЛ.

и

БАДЯГА 911 от синяков и ушибов 100мл

То есть поиск нужно осуществлять по последовательности:
Сперва искать совпадение четырех слов в разной последовательности в столбцах, потом трех, двух одного.
И если не нашел, то искать уже часть слова, но опять же по 8 символам совпадения, по 7,6,5 ...
И в столбец "с" вставлять значение найденного для проверки.

Автор - fyx
Дата добавления - 06.05.2016 в 21:59
krosav4ig Дата: Суббота, 07.05.2016, 03:44 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1355
Репутация: 547 ±
Замечаний: 0% ±

Excel 2007, 2013
fyx, здравствуйте, нагородил монструозную массивную формулу, так пойдет?
Код
=ЕСЛИОШИБКА(ВПР(;ЕСЛИ({1;0};МУМНОЖ(Ч(ЕСЛИОШИБКА(ПОИСК(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(" "&A$1:A$88;" ";ПОВТОР(" ";999));СТОЛБЕЦ(СМЕЩ(A$1;;;;МАКС(ДЛСТР(0&A$1:A$88)-ДЛСТР(ПОДСТАВИТЬ(A$1:A$88;" ";)))))*999;999));ПОДСТАВИТЬ(" "&B1;"№";"N"));)>1);СТРОКА(СМЕЩ(A$1;;;МАКС(ДЛСТР(0&A$1:A$88)-ДЛСТР(ПОДСТАВИТЬ(A$1:A$88;" ";)))))^0)-ДЛСТР(0&A$1:A$88)+ДЛСТР(ПОДСТАВИТЬ(A$1:A$88;" ";));A$1:A$88);2;);"")
добавил сокращенный за счет имен вариант
К сообщению приложен файл: 7053441.xlsx(19Kb)


(_)Õvõ(_)

Сообщение отредактировал krosav4ig - Суббота, 07.05.2016, 03:48
 
Ответить
Сообщениеfyx, здравствуйте, нагородил монструозную массивную формулу, так пойдет?
Код
=ЕСЛИОШИБКА(ВПР(;ЕСЛИ({1;0};МУМНОЖ(Ч(ЕСЛИОШИБКА(ПОИСК(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(" "&A$1:A$88;" ";ПОВТОР(" ";999));СТОЛБЕЦ(СМЕЩ(A$1;;;;МАКС(ДЛСТР(0&A$1:A$88)-ДЛСТР(ПОДСТАВИТЬ(A$1:A$88;" ";)))))*999;999));ПОДСТАВИТЬ(" "&B1;"№";"N"));)>1);СТРОКА(СМЕЩ(A$1;;;МАКС(ДЛСТР(0&A$1:A$88)-ДЛСТР(ПОДСТАВИТЬ(A$1:A$88;" ";)))))^0)-ДЛСТР(0&A$1:A$88)+ДЛСТР(ПОДСТАВИТЬ(A$1:A$88;" ";));A$1:A$88);2;);"")
добавил сокращенный за счет имен вариант

Автор - krosav4ig
Дата добавления - 07.05.2016 в 03:44
fyx Дата: Суббота, 07.05.2016, 10:47 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
добавил сокращенный за счет имен вариант


Спасибо огромное, то что было нужно.
 
Ответить
Сообщение
добавил сокращенный за счет имен вариант


Спасибо огромное, то что было нужно.

Автор - fyx
Дата добавления - 07.05.2016 в 10:47
fyx Дата: Суббота, 07.05.2016, 10:59 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
здравствуйте, нагородил монструозную массивную формулу, так пойдет?


При увеличении длины столбца (то есть количество записей в столбце) с A$1:A$88 на A$1:A$9988
перестает подставлять. В чем может быть проблема?
 
Ответить
Сообщение
здравствуйте, нагородил монструозную массивную формулу, так пойдет?


При увеличении длины столбца (то есть количество записей в столбце) с A$1:A$88 на A$1:A$9988
перестает подставлять. В чем может быть проблема?

Автор - fyx
Дата добавления - 07.05.2016 в 10:59
китин Дата: Суббота, 07.05.2016, 11:21 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3450
Репутация: 549 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
В чем может быть проблема?

все считает у меня. про массивную не забыли? ввод Ctrl Shift Enter
К сообщению приложен файл: _7053441.xlsx(19Kb)


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщение
В чем может быть проблема?

все считает у меня. про массивную не забыли? ввод Ctrl Shift Enter

Автор - китин
Дата добавления - 07.05.2016 в 11:21
fyx Дата: Суббота, 07.05.2016, 21:31 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
все считает у меня. про массивную не забыли? ввод Ctrl Shift Enter


Теперь заработало.
Наверное и в прям что то забыл.
 
Ответить
Сообщение
все считает у меня. про массивную не забыли? ввод Ctrl Shift Enter


Теперь заработало.
Наверное и в прям что то забыл.

Автор - fyx
Дата добавления - 07.05.2016 в 21:31
krosav4ig Дата: Суббота, 07.05.2016, 22:33 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1355
Репутация: 547 ±
Замечаний: 0% ±

Excel 2007, 2013
fyx, посмотрите еще вот такой вариант
Код
=ЕСЛИОШИБКА(ВПР(;ЕСЛИ({1;0};МУМНОЖ(Ч(ЕСЛИОШИБКА(ПОИСК(dd;ПОДСТАВИТЬ(" "&B1;"№";"N"));)>1);cc^0)-bb;aa);2;);"")

имена aa,bb,cc,dd смотрите в диспетчере имен
Код
aa    =мои!$A$1:ИНДЕКС(мои!$A:$A;СЧЁТЗ(мои!$A:$A))
Код
bb    =ДЛСТР(0&aa)-ДЛСТР(ПОДСТАВИТЬ(aa;" ";))
Код
cc    =СТРОКА(мои!$A$1:ИНДЕКС(мои!$A:$A;МАКС(bb)))
Код
dd    =СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(" "&aa;" ";ПОВТОР(" ";999));ТРАНСП(cc)*999;999))
К сообщению приложен файл: _7053441_.xlsx(19Kb)


(_)Õvõ(_)

Сообщение отредактировал krosav4ig - Суббота, 07.05.2016, 22:33
 
Ответить
Сообщениеfyx, посмотрите еще вот такой вариант
Код
=ЕСЛИОШИБКА(ВПР(;ЕСЛИ({1;0};МУМНОЖ(Ч(ЕСЛИОШИБКА(ПОИСК(dd;ПОДСТАВИТЬ(" "&B1;"№";"N"));)>1);cc^0)-bb;aa);2;);"")

имена aa,bb,cc,dd смотрите в диспетчере имен
Код
aa    =мои!$A$1:ИНДЕКС(мои!$A:$A;СЧЁТЗ(мои!$A:$A))
Код
bb    =ДЛСТР(0&aa)-ДЛСТР(ПОДСТАВИТЬ(aa;" ";))
Код
cc    =СТРОКА(мои!$A$1:ИНДЕКС(мои!$A:$A;МАКС(bb)))
Код
dd    =СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(" "&aa;" ";ПОВТОР(" ";999));ТРАНСП(cc)*999;999))

Автор - krosav4ig
Дата добавления - 07.05.2016 в 22:33
fyx Дата: Пятница, 13.05.2016, 12:58 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Все таки вопрос не до конца закрылся.
При сравнении

ЭРГОФЕРОН №20 ТАБ. Д/РАССАСЫВ.

и

ЭРГОФЕРОН таб. №20

оба решения не дают совпадений.

Хотя совпадение есть.
 
Ответить
СообщениеВсе таки вопрос не до конца закрылся.
При сравнении

ЭРГОФЕРОН №20 ТАБ. Д/РАССАСЫВ.

и

ЭРГОФЕРОН таб. №20

оба решения не дают совпадений.

Хотя совпадение есть.

Автор - fyx
Дата добавления - 13.05.2016 в 12:58
krosav4ig Дата: Среда, 18.05.2016, 23:39 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1355
Репутация: 547 ±
Замечаний: 0% ±

Excel 2007, 2013
fyx, добавил еще 1 имя в диспетчер имен
Код
ee    =ПОДСТАВИТЬ(" "&мои!B1;"№";ЕСЛИ(ЕОШ(ПОИСК("№";aa));"N";"№"))

и формула в столбце C
Код
=ЕСЛИОШИБКА(ВПР(;ЕСЛИ({1;0};МУМНОЖ(Ч(ЕСЛИОШИБКА(ПОИСК(dd;ee);)>1);cc^0)-bb;aa);2;);"")

общая формула без использования имен выглядит так %) :
Код
=ЕСЛИОШИБКА(ВПР(;ЕСЛИ({1;0};МУМНОЖ(Ч(ЕСЛИОШИБКА(ПОИСК(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(" "&$A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A));" ";ПОВТОР(" ";999));ТРАНСП(СТРОКА($A$1:ИНДЕКС($A:$A;МАКС(ДЛСТР(0&$A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A)))+ДЛСТР(ПОДСТАВИТЬ($A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A));" ";))))))*999;999));ПОДСТАВИТЬ(" "&B1;"№";ЕСЛИ(ЕОШ(ПОИСК("№";$A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A))));"N";"№")));)>1);СТРОКА($A$1:ИНДЕКС($A:$A;МАКС(ДЛСТР(0&$A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A)))+ДЛСТР(ПОДСТАВИТЬ($A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A));" ";)))))^0)-ДЛСТР(0&$A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A)))+ДЛСТР(ПОДСТАВИТЬ($A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A));" ";));$A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A)));2;);"")
К сообщению приложен файл: _7053441-1-.xlsx(19Kb)


(_)Õvõ(_)

Сообщение отредактировал krosav4ig - Среда, 18.05.2016, 23:40
 
Ответить
Сообщениеfyx, добавил еще 1 имя в диспетчер имен
Код
ee    =ПОДСТАВИТЬ(" "&мои!B1;"№";ЕСЛИ(ЕОШ(ПОИСК("№";aa));"N";"№"))

и формула в столбце C
Код
=ЕСЛИОШИБКА(ВПР(;ЕСЛИ({1;0};МУМНОЖ(Ч(ЕСЛИОШИБКА(ПОИСК(dd;ee);)>1);cc^0)-bb;aa);2;);"")

общая формула без использования имен выглядит так %) :
Код
=ЕСЛИОШИБКА(ВПР(;ЕСЛИ({1;0};МУМНОЖ(Ч(ЕСЛИОШИБКА(ПОИСК(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(" "&$A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A));" ";ПОВТОР(" ";999));ТРАНСП(СТРОКА($A$1:ИНДЕКС($A:$A;МАКС(ДЛСТР(0&$A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A)))+ДЛСТР(ПОДСТАВИТЬ($A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A));" ";))))))*999;999));ПОДСТАВИТЬ(" "&B1;"№";ЕСЛИ(ЕОШ(ПОИСК("№";$A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A))));"N";"№")));)>1);СТРОКА($A$1:ИНДЕКС($A:$A;МАКС(ДЛСТР(0&$A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A)))+ДЛСТР(ПОДСТАВИТЬ($A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A));" ";)))))^0)-ДЛСТР(0&$A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A)))+ДЛСТР(ПОДСТАВИТЬ($A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A));" ";));$A$1:ИНДЕКС($A:$A;СЧЁТЗ($A:$A)));2;);"")

Автор - krosav4ig
Дата добавления - 18.05.2016 в 23:39
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск части текста в ячейке из массива данных (Формулы/Formulas)
Страница 1 из 11
Поиск:

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