Поиск части текста в ячейке из массива данных
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 ... И в столбец "с" вставлять значение найденного для проверки.
Добрый день коллеги. Уже целый день сижу в гугле, не могу найти решение проблемы. Есть два столбца данных. Нужно найти в них совпадения, причем необходимо задавать количество символов совпадений. Например: 5-НОК 50МГ. №50 ТАБ. П/О /ЛЕК/ должно ровняться именно 5-НОК N50 таб. А еще сложнее найти совпадение 911-БАДЯГА ГЕЛЬ Д/ТЕЛА ОТ СИНЯКОВ И УШИБОВ 100МЛ. и БАДЯГА 911 от синяков и ушибов 100мл То есть поиск нужно осуществлять по последовательности: Сперва искать совпадение четырех слов в разной последовательности в столбцах, потом трех, двух одного. И если не нашел, то искать уже часть слова, но опять же по 8 символам совпадения, по 7,6,5 ... И в столбец "с" вставлять значение найденного для проверки. fyx
Сообщение отредактировал 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
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация:
989
±
Замечаний:
0% ±
Excel 2007,2010,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;);"")
добавил сокращенный за счет имен вариант
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
email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
Сообщение отредактировал 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
Ответить
Сообщение добавил сокращенный за счет имен вариант
Спасибо огромное, то что было нужно.Автор - 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
Ответить
Сообщение здравствуйте, нагородил монструозную массивную формулу, так пойдет?
При увеличении длины столбца (то есть количество записей в столбце) с A$1:A$88 на A$1:A$9988 перестает подставлять. В чем может быть проблема?Автор - fyx Дата добавления - 07.05.2016 в 10:59
китин
Дата: Суббота, 07.05.2016, 11:21 |
Сообщение № 5
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация:
1073
±
Замечаний:
0% ±
Excel 2007;2010;2016
В чем может быть проблема?
все считает у меня. про массивную не забыли? ввод Ctrl Shift Enter
В чем может быть проблема?
все считает у меня. про массивную не забыли? ввод Ctrl Shift Enterкитин
Не судите очень строго:я пытаюсь научиться ЯД 41001877306852
Ответить
Сообщение В чем может быть проблема?
все считает у меня. про массивную не забыли? ввод 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
Ответить
Сообщение все считает у меня. про массивную не забыли? ввод Ctrl Shift Enter
Теперь заработало. Наверное и в прям что то забыл.Автор - fyx Дата добавления - 07.05.2016 в 21:31
krosav4ig
Дата: Суббота, 07.05.2016, 22:33 |
Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация:
989
±
Замечаний:
0% ±
Excel 2007,2010,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))
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
email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
Сообщение отредактировал 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
Ответить
Сообщение Все таки вопрос не до конца закрылся. При сравнении ЭРГОФЕРОН №20 ТАБ. Д/РАССАСЫВ. и ЭРГОФЕРОН таб. №20 оба решения не дают совпадений. Хотя совпадение есть. Автор - fyx Дата добавления - 13.05.2016 в 12:58
krosav4ig
Дата: Среда, 18.05.2016, 23:39 |
Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация:
989
±
Замечаний:
0% ±
Excel 2007,2010,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;);"")
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
email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
Сообщение отредактировал 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