Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Поиск ячейки содержащей текст, часть которого отвечает условию поиска.
 
#1
05.11.2011 21:35:21
Добрый вечер! Битые сутки не могу решить казалось бы не сложную, задачку, но всё мысли заходят в тупик. Прошу помощи гуру.  
ОСОБЕННОСТИ: Задачу необходимо решить только с использованием формул.  
ИСХОДНЫЕ ДАННЫЕ: есть таблица 5 строк, 5 столбов. В каждой ячейке могут быть один из след.типов данных: текст, число, дата, просто пусто. Одна (И ТОЛЬКО ОДНА - иначе быть не может) ячейка содержит текст в котором встречается "ИНН" и "КПП". А вот её расположение в таблице - может меняться.  
Пример содержимого: "Администрация МО города Москвы ИНН 0123456789 КПП 123456789"  
ЗАДАЧА: проверить всю таблицу 5*5 на предмет ячейки содержащей текст "ИНН" и "КПП" и в случае её обнаружения отобразить текст из этой ячейки в ячейке (к примеру) R77.
 
 
 
#2
05.11.2011 21:40:30
где файл?
 
 
 
#3
05.11.2011 21:44:56
Опять студенты лодыри...
Я сам - дурнее всякого примера! ...
 
 
 
#4
05.11.2011 21:57:57
формула массива, вводится нажатием ctrl+shift+enter:  
=ИНДЕКС(A1:E5;МАКС(ЕЧИСЛО(ПОИСК("ИНН";A1:E5))*ЕЧИСЛО(ПОИСК("КПП";A1:E5))*СТРОКА(A1:E5));МАКС(ЕЧИСЛО(ПОИСК("ИНН";A1:E5))*ЕЧИСЛО(ПОИСК("КПП";A1:E5))*СТОЛБЕЦ(A1:E5)))
 
 
 
#5
05.11.2011 22:04:11
{quote}{login=Himtree}{date=05.11.2011 09:35}{thema=Поиск ячейки содержащей текст, часть которого отвечает условию поиска.}{post}Прошу помощи гуру.  
{/post}{/quote}  
 
МСН: <<формула массива...>>  
 
Ну наконец-то!  
Хоть один гуру сознался что он гуру! :-)))
 
 
 
#6
05.11.2011 22:08:12
UDF:  
 
Function inn(rng As Range) As String  
Dim r As Range  
For Each r In rng  
   If InStr(r.Text, "ÈÍÍ") > 0 And InStr(r.Text, "ÊÏÏ") > 0 Then inn = r.Text: Exit Function  
Next r  
End Function
 
 
 
#7
05.11.2011 22:09:35
Криво получилось:  
Function inn(rng As Range) As String  
Dim r As Range  
For Each r In rng  
   If InStr(r.Text, "ИНН") > 0 And InStr(r.Text, "КПП") > 0 Then inn = r.Text: Exit Function  
Next r  
End Function
 
 
 
#8
05.11.2011 22:19:30
Что за факультет?
 
 
 
#9
05.11.2011 22:39:06
The_Prist, я так понял найти ячейку с текстом, и "отобразить ее в R77" (т.е. по адресу)
Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина - самый громкий звук

https://github.com/nervgh
 
 
 
#10
05.11.2011 22:40:01
Я так понял, что в R77 результат :-)
 
 
 
#11
05.11.2011 22:46:30
Если результат будет не в R77 - незачёт!
 
 
 
#12
05.11.2011 23:45:28
Не хотел я пугать вас своим файлом, ну раз просите, вот. В нём три листа.  
1) Карточка результата (здесь должны отображаться все необходимые данные, в пригодном для восприятия виде)  
2) Обработка (промежуточный лист, для упорядочивания данных)  
3) Данные (сюда каждый раз будут вставляться новые данные, скопированные с сайта)  
На первом листе, в красной ячейке должна появится надпись:  
"ИНН 2311038642 КПП 231101001 Администрация Прикубанского внутригородского округа города Краснодара  
javascript:"    
Её можно подцепить как с третьего так и со второго листа, но как ?  
ТОЛЬКО ФОРМУЛОЙ, никаких макросов.
 
 
 
#13
05.11.2011 23:46:04
файл вот
 
 
 
#14
05.11.2011 23:50:22
{quote}{login=The_Prist}{date=05.11.2011 10:31}Фигня какая-то. Если надо найти ячейку, содержащую ИНН и КПП, то: найдя в ячейке "Администрация МО города Москвы ИНН 0123456789 КПП 123456789", отобразить содержимое этой ячейки как "R77" вряд ли получиться. Ведь там уже "Администрация МО города Москвы ИНН 0123456789 КПП 123456789"  
Вопрос: откуда брать "R77"?{/post}{/quote}  
Наверное криво я изъяснился. Прикрепил файл, с конкретным примером. В красной ячейке должно отобразиться название организации с ИНН и КПП.
 
 
 
#15
05.11.2011 23:55:14
{quote}{login=MCH}{date=05.11.2011 09:57}{thema=}{post}формула массива, вводится нажатием ctrl+shift+enter:  
=ИНДЕКС(A1:E5;МАКС(ЕЧИСЛО(ПОИСК("ИНН";A1:E5))*ЕЧИСЛО(ПОИСК("КПП";A1:E5))*СТРОКА(A1:E5));МАКС(ЕЧИСЛО(ПОИСК("ИНН";A1:E5))*ЕЧИСЛО(ПОИСК("КПП";A1:E5))*СТОЛБЕЦ(A1:E5))){/post}{/quote}  
Блин, а работает =) сейчас буду разбирать по этапам и вкуривать как именно она работает и стабильность вычисления.  
Спасибо!
 
 
 
#16
06.11.2011 00:09:06
А зачем Вам 5 столбцов на 5 строк, когда нужно просматривать ячейки B1:B6, и формула значительно проще получается:  
=ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК("КПП";B1:B6))/ЕЧИСЛО(ПОИСК("ИНН";B1:B6));B1:B6)  
массивный ввод не требуется
 
 
 
#17
06.11.2011 00:49:07
{quote}{login=MCH}{date=06.11.2011 12:09}{thema=}{post}А зачем Вам 5 столбцов на 5 строк, когда нужно просматривать ячейки B1:B6, и формула значительно проще получается:  
=ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК("КПП";B1:B6))/ЕЧИСЛО(ПОИСК("ИНН";B1:B6));B1:B6)  
массивный ввод не требуется{/post}{/quote}  
Да, спасибо, как вариант! Просто рассматривал два варианта:  
1) вытащить эту строку со второго листа - тогда ваша последняя формула отлично подходит.  
2) вытаскивать эту строку сразу с третьего листа (скопированных данны) - и в этом случае ваша последняя формула может дать сбой, так как нужная ячейка может оказаться не во втором (B) столюце, а в третьем (С).
 
 
 
#18
06.11.2011 01:19:47
{quote}{login=MCH}{date=06.11.2011 12:09}{thema=}{post}А зачем Вам 5 столбцов на 5 строк, когда нужно просматривать ячейки B1:B6, и формула значительно проще получается:  
=ПРОСМОТР(2;1/ЕЧИСЛО(ПОИСК("КПП";B1:B6))/ЕЧИСЛО(ПОИСК("ИНН";B1:B6));B1:B6)  
массивный ввод не требуется{/post}{/quote}  
эх, поспешил...  
Дело в том что я работаю в бесплатном OO ver.3.2.1 а вот в нём данная формула (при том что отлично работает в MS 2007) вываливается в ошибку: #DIV/0!
 
 
 
#19
06.11.2011 01:34:12
попробуйте так:  
=ИНДЕКС(B1:B6;ПОИСКПОЗ("*ИНН*КПП*";B1:B6;0))
 
 
 
#20
06.11.2011 01:38:25
или;  
=ВПР("*ИНН*КПП*";B1:B6;1;0)
 
 
 
#21
06.11.2011 10:10:59
{quote}{login=Himtree}{date=06.11.2011 01:19}{thema=Re: }{post}{quote}{login=MCH}{date=06.11.2011 12:09}{thema=}{post}{/post}{/quote}Дело в том что я работаю в бесплатном OO ver.3.2.1 {/post}{/quote}Тогда Вам прямая дорога на форум по этому продукту.
 
 
 
#22
06.11.2011 13:13:36
{quote}{login=MCH}{date=06.11.2011 01:34}{thema=}{post}попробуйте так:  
=ИНДЕКС(B1:B6;ПОИСКПОЗ("*ИНН*КПП*";B1:B6;0)){/post}{/quote}  
 
{quote}{login=MCH}{date=06.11.2011 01:34}{thema=}{post}или;  
=ВПР("*ИНН*КПП*";B1:B6;1;0){/post}{/quote}  
 
спасибо но опять в MS 2007 работает легко, а в OO нефунциклирует! Эх =(
 
 
 
#23
06.11.2011 13:27:49
Himtree, Вам же Юрий М подсказал выход от 06.11.2011, 10:10. Или просто нравится людям голову морочить?
Я сам - дурнее всякого примера! ...
 
 
 
#24
06.11.2011 21:35:10
{quote}{login=KukLP}{date=06.11.2011 01:27}{thema=}{post}Himtree, Вам же Юрий М подсказал выход от 06.11.2011, 10:10. Или просто нравится людям голову морочить?{/post}{/quote}  
Простите, все кому заморочил голову.  
Большая часть функций (по крайне мере все простые) из MS 2007 идентичны и поддерживаются и в OO. Надеялся что мою задачку можно решить на элементарных функциях MS, которые легко ретранслировать в OO.    
По видимому ошибался! =(
 
 
 
Страницы: 1
Читают тему
Наверх