Страницы: 1
RSS
Поиск текста по неполному соответствию
 
Добрый день, помогите пожалуйста научить формулу в столбце D примера, искать так же, как ищет формула в столбце C.
Только нужно учесть, что размер полного перечня 17407 строк. И быстродействие является важным фактором. А также формула должна работать в Excel 2007+
 
Чем не нравится формула столбца С?
(Правильнее спросить: что Вы хотите получить?)
 
Формула нравится, но в том количестве строк, которое указано, она ищет очень долго. К тому же по многу раз повторяет найденное значение до следующего совпадения. А требуется найти все значения в столбце А, которые соответствуют условиям поиска. Без многократных дубликатов.
 
В С4 и протянуть по строкам:
=ЕСЛИ(ЕЧИСЛО(ПОИСК($C$3;A4));МАКС($C$3:C3)+1)
Вспомогательная формула, которая отмечает строки с вхождением слова в текст.

Можно изменить и показывать не номер строки, а слово. Вторая вспомогательная будет нумеровать  строки с уникальными записями. Основная - ВПР , которая соберет уникальные в один диапазон. Таких решеий на форуме много, надоедает писать одно и то же.
 
=ЕСЛИ(ЕЧИСЛО(ПОИСК($C$3;A4));ЛЕВБ(ПСТР(A4;ПОИСК($C$3;A4);50);ПОИСК(" ";ПСТР(A4;ПОИСК($C$3;A4);50)&" ")-1))
Вот такая конструкция извлечет слова с вхождением указанного слова. Из этого диапазона извлечь уникальные (см. в Приемах)
 
Благодарю Вас за помощь, но мне пренципиально, чтобы под ячейкой поиска формировался именно список с полным наименованием текста всех найденных в столбце А ячеек, которые отвечают критериям поиска С3.
 
одной формулой можно, но это будет формула массива, которая на 17000 строк подвесит файл.
Вы же хотите облегчения? у Вас доп. столбцы закончились? А в столбец С запишете формулу с ВПР

Цитата
список с полным наименованием текста
Это что значит? Что такое "полное наименование текста"?
 
Я неправильно выразился.
Подячейкой поиска должен быть список ячеек со значениями соответствующими критериям поиска. При этом их может быть как 40, так и 2.

Например: если в С 3 ввести "магистрали", то должны появиться только 3 значения соответствующие строкам 25,26,27 столбца А
 
Немного измененая доп. формула из сообщения №4:
=ЕСЛИ(ЕЧИСЛО(ПОИСК($C$3;A4));СТРОКА(A1))
В столбец C:
=НАИМЕНЬШИЙ(диапазон_с_допформулой;СТРОКА(A1))
 
Принцип именно такой. Но вместо цифр 22,23,24 мне нужно именно полное наименование ячеек.
Я из-за этого и ушел от аналогичной формулы к впр и подставить.  
 
Цитата
полное наименование ячеек.
Адрес ячеек? Зачем? Или Вы о значении ячеек? См. сообщение №4
 
Да. Именно о значении ячеек.
Читаю сообщение 4 и неоченьпонимаю, что нужно сделать. Извините пожалуйста.  
 
Цитата
неоченьпонимаю
Наверное, "очень не понимаю" :)

Одна формула показана в сообщении №4, вторая:
=ЕСЛИ(СТРОКА(A1)>$C$2;"";ИНДЕКС($A$4:$A$31;ПОИСКПОЗ(СТРОКА(A1);$B$4:$B$31;)))
См. файл
 
Спасибо большое за помощь vikttur.
У вас очень хороший сайт. И такое грамотное и оперативное решение вопроса только подтверждает профессионализм людей его создающих.
 
Уважаемый vikttur, еще раз хочу поблагодарить вас за помощь. Однако у меня по прежнему есть вопросы. Формула МАКС работает от 1 до 255 чисел и в случае если задать "=МАКС(K4:K17407)" в ячейке С2 значение "0".
Пробую заменить "=МАКС(K4:K17407)" на "=НАИБОЛЬШИЙ(K4:K17407;1)", но он так же не хочет работать дальше значения "K4:K150". Не поделитесь опытом в чем может быть причина.

В случае с Вашим файлом К=В
Изменено: Семён - 18.06.2016 10:07:25
 
Не обвиняйте функцию, она хорошая :)
Покажите
 
По-моему, проще макросом
Код
Sub tt()
    Dim arr(), i As Long, col As New Collection, s As String
    s = Range("C3").Value
    s = Right(s, Len(s) - 1) 'эта строка нужна, потому что у первый символ "'"
    arr = Range("A4:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    For i = 1 To UBound(arr)
        If arr(i, 1) Like "*" & s & "*" Then col.Add arr(i, 1)
    Next
    If col.Count = o Then Exit Sub
        ReDim arr(1 To col.Count, 1 To 1)
    For i = 1 To col.Count
        arr(i, 1) = col(i)
    Next
    Range("C4:C" & WorksheetFunction.Max(Cells(Rows.Count, 3).End(xlUp).Row, 4)).ClearContents
    Range("C4").Resize(col.Count, 1).Value = arr
End Sub

 
Здравствуйте vikttur. Летом вы хорошо помогли мне с формулой поиска. Но вот засада, пока был в отпуске, коллега влез в табличку и с его слов поменял только лишь область поиска (в файле лист "ОКПД2"), но теперь функция не хочет правильно искать соответствия значению ячейки поиска. Не поможете разобраться в чем дело?
 
ПОИСК($D$1;ОКПД2!B3) - где происходит поиск?
 
На листе ОКПД2 в столбце В.  
 
Где Вы столбец увидели? Я в ссылке вижу только одну ячейку В3
 
Семён,
у формул Excel такая особенность, что поиск происходит не в том диапазоне, о котором Вы думаете (как бы сильно Вы о нем не думали), а в том, который Вы написали в формуле
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
А должно быть В4, верно vikttur?
Разобрался! Спасибо на наставление на путь.
Я пока только учусь.
 
Ігор Гончаренко, вы весьма остроумны. Так держать!  ;)
 
vikttur, если позволите, ещё один вопрос.
В том же файле, на листе поиска, в столбце С ведется поиск кода соответствующего значению находящемуся справа. Так вот, ищет хорошо, но в случае если значения в столбце D дублируются, формула подставляет одинаковый код (так называемый наименьший), хотя в листе ОКПД2, \ данному наименованию соответствует два отличающихся кода.
Подскажите пожалуйста, как поправить формулу, что бы она подставляла разные значения в столбце С даже если в столбце D значения одинаковы.
Для наглядности в ячейке поиска можно ввести "солома".
Страницы: 1
Наверх