Поиск ячейки в массиве данных по части текста

[Himtree]

Вот такая вот незадача, или задачка =)
Есть массив смешанных (цифры, текст) данных. 5 столбцов 5 строк.
Нужно найти ячейку которая содержит слово "срок" и отобразить ВСЮ информацию из этой ячейки в другой ячейке за пределами этого массива.
P.S. Слово "срок" в ячейке может быть в начале предложения, может в середине, может быть в другой форме: "срока", "сроки", нужно что бы функция находила это слово внезависимости от его местоположения в тексте.
ПРИМЕЧАНИЕ: использование макросов не возможно, решение задачи должно быть построено чисто на формулах.

**упрощение задачи**
если вышеописанную задачу решить невозможно, то можно упростить: Указав функции массив из 1-го столбца (кол-во ячеек сохраняется = 5) то есть мы заранее знаем что предложение содержащее слово "срок" будет находится в первом столбце массива данных.

**усложнение задачи**
предполагаем что в массиве из 1 слобца и 5 строк есть две ячейки, содержащие предложения со словом "срок". Нужно найти и отобразить информацию только из второй ячейки.

[JohnSUN]

Добро пожаловать на форум.
Решение, конечно, есть.

[вложение удалено Администратором]

[Himtree]

Добро пожаловать на форум.
Решение, конечно, есть.

Спасибо!
И отдельное спасибо за старания в написании решения, однако:
1) Во-перывых немного не тот результат. Мне нужно отобразить не кол-во найденных ячеек, а только какую-либо определённую (первую или последнюю, либо единственную) ячейку, содержащую слово "срок"
Во-вторых нужно получить результат только одной формулой, без получения промежуточных данных.
2) Вопрос именно по вашему решению: что делает вот эта часть функции "INDEX(B4:F8;MAX(G13:G17);MAX(B18:F18))" ?
Теоретически конечно функция никогда не дойдёт до этого сегмента, тогда какой в нём смысл? Но если всё таки в ячейке С20 поставить "-1" то вся функция вывалится в ошибку "502".

[JohnSUN]

А зачем в С20 ставить -1? Она специально защищена, чтобы туда ничего не ставить. Чтобы в диапазоне B4:F8 (5x5) найти единственную ячейку с текстом, достаточно вписать в ячейку С10 часть этого текста. Если такая ячейка будет единственной, как раз этот INDEX() и вернет ее значение. Например, слово "строк" встречается во всей той мешанине только один раз. Поэтому и результатом будет значение ячейки E4, в которой это слово встречается.

Ну, извини, что ответил не на тот вопрос, что ты хотел задать.

[Himtree]

Например, слово "строк" встречается во всей той мешанине только один раз. Поэтому и результатом будет значение ячейки E4, в которой это слово встречается.

Да, верно! Сорри результат именно тот что нужен. Однако это работает только в случае когда слово встречается лишь один раз. =(
А нужна именно универсальная формула, в одну ячейку.

[JohnSUN]

Без проблем! Только уточни какую из, допустим, трех выбирать?
"Первую встречную"? Смотри картинку: если просматриваем столбец за столбцом, то первой будет B6, с текстом "P.S. Слово "срок" в ячейке может быть в начале предложения"
А если смотреть строка за строкой, то C5 - "содержит слово "срок" "

И насчет затолкать все этапы вычислений в одну формулу... Прикидывал и так, и эдак - не получается. Слишком много исходных данных.
Так что или какой-то вспомогательный диапазон, пусть даже на отдельном - скрытом - листе, или все-таки макрос...

[вложение удалено Администратором]

[JohnSUN]

Ладно, допустим, что действительно смотреть нужно по столбцам. В смысле, просмотреть первый столбец сверху донизу и перейти к следующему, пока не найдем нужное слово или не закончатся ячейки.
Тогда так (см. вложение)

[вложение удалено Администратором]

[neft]

JohnSUN, ай как нехорошо выпендриваться.

[JohnSUN]

Ну, не "выпендриваться", а "прикалываться"... Хотел бы выпендриться - защитил бы книгу хитрым паролем.  Или начал бы рассказывать, что "в Base это делается в пол пинка без единой формулы" 

Перечитал первое сообщение... Там где про **усложнение задачи**. Знаешь, Himtree, плохая идея - выбирать последнее найденное значение. Так первое нашли и прекратили перебор... А в случае с последним - заставлять машину каждый раз перебирать всё?

[Smaigas]

Тогда так (см. вложение)

Ой, а я ничего не понял. А можно поподробней про функцию ищимухтар?

[JohnSUN]

Можно, конечно, можно!
Это не функция, это имя ячейки H4 на скрытом листе search. Все вычисления по поиску текста делаются там.

Последовательность вычислений:

• Подготовка данных:
  
  • Диапазон ячеек, в которых находятся строки для поиска, заносятся в именованный диапазон с именем inpData. Это делается так - выделяется диапазон с ячейками, клавишами Ctrl+F3 или из меню Вставка-Названия-Определить... открывается окно со списком уже имеющихся имен и добавляется новое имя. Если нужное имя в списке уже есть, то для него просто переопределяется адрес в строчке Назначено.
  • 
    Ячейке, в которую будут вводится данные для поиска (часть слова) точно так же назначается имя findWhat

• 
  Разбор данных:
  • 
    На вспомогательном листе (я назвал его search, но имя может быть любым) двумя формулами вычисляется размер области inpData. То есть формулами =ROWS(inpData) и =COLUMNS(inpData) вычисляются количество строк и колонок.
    
  • 
    Одна колонка (я это сделал в колонке A, но и это не обязательно) заполняется формулами вида =IF(A1<(<количество строк>-1;A1+1;0). Эта формула просто создаст целые числа от 0 до <количество строк-1> и опять 0 до <количество строк-1> и еще раз от 0 до <количество строк-1>... Сколько ячеек заполним формулой - столько раз этот "цикл" и повторится. Фокус в том, что пишем числа не от 1 до <количество строк>, а именно от 0. Дело в том, что для поиска окончательного результата будет использоваться функция OFFSET. Она может искать смещение относительно одной ячейки. Но чтобы указать саму эту "базовую" ячейку нужно задать смещение в 0 колонок и 0 строк. Если бы использовали функцию INDEX, то номера можно было бы задавать в привычном виде, от 1 до <количество строк>. Но для этой функции нужно указывать весь диапазон. Это не совсем удобно, если когда-нибудь захотим увеличить или уменьшить диапазон поиска.
    
  • 
    В колонке B формула =IF(B1 < (<количество колонок>-1);IF(A2=0;B1+1;B1);0) заполнит числами от 0 до <количество колонок-1> каждую группу строк из первой колонки. Словами описывать сложно - проще посмотреть на значения этих колонок в книге.
    
  • 
    В колонке C выпишем значения ячеек исходного диапазона. То есть, сделаем именно то, что было описано в "упрощение задачи" в первом сообщении. Только возмем не одну первую колонку, а все. И выстроим их одну под другой в один столбик.
    
• 
  Вычисления:
  • 
    Формула в столбце D =NOT(ISERROR(SEARCH(".*"&findWhat;C2))) просто возвращает TRUE или FALSE, "подходит" или "не подходит" значение в ячейке findWhat к тексту в соответствующей ячейке колонки С. Чтобы эта функция работала правильно, нужно обязательно включить возможность использования регулярных выражений в формулах книги - Сервис–Параметры-Calc-Вычисления-Разрешить регулярные выражения в формулах. Это нужно сделать один раз, настройка сохранится вместе с книгой.
    
  • 
    В отдельной ячейке (у меня это H3) с помощью функции =MATCH(1;D2:D201) ищем первое попавшееся значение TRUE (единица) в колонке D
    
  • 
    А в ячейке H4 проверяем, найдено ли хоть какое-то значение. Дело в том, что если во всем столбце D не окажется ни одного TRUE , MATCH() вернет самую последнюю ячейку с FALSE. Если строка найдена, то функцией OFFSET(C2;H3-1;0;1;1) возвращаем нужный текст, иначе возвращаем строку "Нет такой буквы в этом слове!"
    
• 
  Оформление результата:
  
  • Присваиваем ячейке search.H4 имя, чтобы замаскировать от пользователя использование вспомогательного листа. По приколу "ищиМухтар". Можно было дать любое допустимое имя: "фас", "Штирлиц", "результатПоиска", "кочерга" - всё зависит от фантазии и чувства юмора.
    
  • Именно это значение указываем в ячейке результата Лист1.F1.
    
  • Скрываем вспомогательный лист Формат - Лист - Скрыть
    

Разумеется, можно было бы упростить некоторые формулы за счет использования дополнительных именованных диапазонов. Или отказаться от некоторых ячеек, просто записав нужную формулу в именованном диапазоне вместо адреса ячейки. Можно было бы через условное форматирование подсвечивать найденную ячейку(-и) в диапазоне inpData...
Но просто для примера решения задачи это уже было бы лишним, правда?

[Himtree]

JohnSUN, ОГРОМНЕЙШЕЕ СПАСИБО за труды!!!!!! А теперь по полочкам:
1) Крайне тяжело со временем, именно поэтому так долго не заходил сюда и поэтому во всех деталях вашего решения не разобрался, но в целом суть понял. Дело в том что у меня и так есть два рабочих листа, первый - куда вставляются данные скопированные с сайта, второй - лист обработки (что то типа вашего листа SEARCH), поэтому добавлять ещё один промежуточный лист с обработкой ну совсем бы уж не хотелось!
Однозначно спасибо за это:

Скрываем вспомогательный лист Формат - Лист - Скрыть

Я конечно не гуру, но считал что куда продвинутее обычного пользователя Excel (в нашем случае OO) - однако ж нет. Таких простых вещей не знал =((( Теперь это очень кстати, буду пользоваться, ещё раз огромное спасибо!!!
А можно с одного вашего вспомогательного листа SEARCH выдёргивать разные поисковые слова. Т.е. массив ячеек для поиска будет всегда один A1:E70 но на листе РЕЗУЛЬТАТОВ нужно будет в колонку отобразить результаты поиска по различным условиям (15-20). т.е.
найти слово "срок"
найти слово "дата"
найти слово "порог"
и т.д. только отображать не именно ту ячейку в которой первым найдено это слово, а ячейку находящююся в этой же строке но в правостоящем столбце?
P.S. JohnSUN, напиши номер телефона, я не рокфеллер, но пару копеек с ЗП на счёт закину. Всегда хочется отблагодарить людей за их добрые дела)

[Himtree]

Тогда так (см. вложение)

Ой, а я ничего не понял. А можно поподробней про функцию ищимухтар?

Приятно, что кому то возможно тоже будет полезным решение поднятого мной вопроса)

[JohnSUN]

А можно с одного вашего вспомогательного листа SEARCH выдёргивать разные поисковые слова. Т.е. массив ячеек для поиска будет всегда один A1:E70 но на листе РЕЗУЛЬТАТОВ нужно будет в колонку отобразить результаты поиска по различным условиям (15-20). т.е.
найти слово "срок"
найти слово "дата"
найти слово "порог"
и т.д. только отображать не именно ту ячейку в которой первым найдено это слово, а ячейку находящююся в этой же строке но в правостоящем столбце?

Можно, всё можно. Просто один поисковый столбец размножается вправо, да и всё... До чуть больше чем тысячу слов можно искать. Но почему все-таки не макросом? Откуда этот запрет?
Опять для тестовых данных лень было заполнять A1:E70 всеми этими "срок"-"дата"-"порог". Взял просто таблицу курсов... Но суть там должна быть понятна: в колонку A на листе результат пишем искомые слова и получаем и саму ячейку, и следующую, и предыдущую... В общем, сделать можно все что угодно.

[вложение удалено Администратором]