Страницы: 1
RSS
ВПР или аналогичные формулы для поиска по части слова
 
Есть таблица в ней по определённой методике присваиваются значения.
Основа этой методики ВПР, который ищет определенную связку, и если находит, то ставит соответствующее значение.

Вопрос возможно ли сделать так чтобы формула искала не точное совпадение а частичное.

Основная сложность в том что, например при использовании ВПР, у меня искомое значение как бы фиксированное.


В общем словами сложно описать.

Приложил файл, в нём постарался максимально подробно описать ситуацию.
 
Почитайте тут, может сможете переделать под свои нужды:
Найти в ячейке любое слово из списка
Изменено: Msi2102 - 10.01.2025 18:28:28
 
Вариант
Код
=ВПР(C23&D23&"*"&ЛЕВБ(F23;ПОИСК(" ";F23)-1)&"*"&G23;R:S;2;ЛОЖЬ)
или так
=ВПР(C23&D23&"*"&ЛЕВБ(F23;5)&"*"&G23;R:S;2;)
Изменено: gling - 10.01.2025 18:49:48
 
Цитата
написал:
Вариант
Код=ВПР(C23&D23&"*"&ЛЕВБ(F23;ПОИСК(" ";F23)-1)&"*"&G23;R:S;2;ЛОЖЬ)
или так
=ВПР(C23&D23&"*"&ЛЕВБ(F23;5)&"*"&G23;R:S;2;)

Да вариант интересный я пытался что-то подобное придумать, но есть проблема в том что в колонке "содержание" текст может быть абсолютно любой.
Там нет никакой структуры, просто так вышло что я когда пример писал везде прописал аналогично.
А так то слово "Экологический" может быть в любом месте.

Что то типо "это уплата взноса в счёт экологического сбора" т.е. один раз так прописали в другой раз по другому.

А вот кстате в ячейке "L30" как раз не сработало

Но за идею спасибо.
 
Цитата
написал:
Почитайте тут, может сможете переделать под свои нужды: Найти в ячейке любое слово из списка
Выглядит так как будто это то что нужно, но покачто у меня не получается повторить эффект.
Буду стараться.

Но если вдруг у кого-то ещё есть идеи, я только за.
 
Цитата
написал:
слово "Экологический" может быть в любом месте
Тогда попробуйте формулу массива
Код
=ЕСЛИОШИБКА(ИНДЕКС($S$23:$S$25;ПОИСКПОЗ(1;--ЕЧИСЛО(ПОИСК($P$23:$P$25;F23));)*($N$23:$N$25=C23)*($O$23:$O$25=D23));"")
 
Цитата
написал:
Тогда попробуйте формулу массива
Попробовал разные варианты формула массива не очень удобна в использовании и она получается какбы игнарирует счета которые указаны во вспом таблице, только по слову ищет.

Поэкспериментировав с подсказкой из второго поста получилась такая формула
Код
=ПРОСМОТР(2;1/ПОИСК($R$23:$R$25;C23&D23&F23&G23);$S$23:$S$25)
И в целом она вроде бы работает почти идеально, учитывает счета учета и ищет по слову.
Причем есть особенность если во вспом таблице у слова *Экологический* убрать звездочки то поиск перестаёт работать.
Впринципи со *звездочками* это не проблема, это даже лучше.
Но проблема в диапазонах как только в диапазоне появляется пустая ячейка всё рушится
тоесть вот так формулу уже не укажешь =ПРОСМОТР(2;1/ПОИСК(R:R;C23&D23&F23&G23);S:S)
Ну или даже если брать фиксированный диапазон но в него попадает пустая ячейка то всё рушится.
Может быть кто нить подскажет дальнейшее направление? как победить эту ситуацию.


Ещё попытался сделать такой вариант
Код
=ВПР(C23&D23&"*"&ЕСЛИ(ЕЧИСЛО(ПРОСМОТР(1;ПОИСК($P$23:$P$25;F23)));$P$23:$P$25;"нету")&"*"&G23;R:S;2;ЛОЖЬ)

Логика выстраивалась так что если в ячейке F23 (содержание) в тексте "экологический сбор за 2025 год" содержится слово из диапазона P:P "экологический"
то вместо текста "экологический сбор за 2025 год" подставляется текст "экологический"
Но я так понимаю в таком виде формула не понимает какой текст подставлять
И опятьже наверно с пустыми ячейками косяки, но это не точно.

В общем может быть второй вариант когото натолкнёт на размышления.
Файл примера тот же что и в первом сообщении.
 
Цитата
написал:
она получается какбы игнарирует счета которые указаны во вспом таблице, только по слову ищет.
Интересно, а что тогда проверяется этой частью в формулы?
Код
($N$23:$N$25=C23)*($O$23:$O$25=D23) 

Ведь $N$23:$N$25 - Счет Дт; $O$23:$O$25 - Счет Кт. Возможно вы не внимательно проанализировали формулу, а если проанализировали, тогда покажите в примере в котором формула не правильно сработала. Или покажите в моём файле-примере, где не правильный результат?

Изменено: gling - 13.01.2025 10:06:22
 
Цитата
написал:
Возможно вы не внимательно проанализировали формулу, а если проанализировали, тогда покажите в примере в котором формула не правильно сработала. Или покажите в моём файле-примере, где не правильный результат?
Пример который вы прикладывали в сообщении №6 там ячейка L33
В ней он получается находит некорректно результат и игнорирует счета учета. Ну вернее не игнорирует, формула то у вас логичная, просто видимо там алгоритм экселя както по другому действует.

Для большего понимания ситуации поменяйте в вспомогательной таблице все счета например на 22 (тоесть 44.02 заменить на 22 во всех 3 строках)
И формула все равно будет находить значения. (а не должна)


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



Мне бы вот всётаки както разобраться с пустыми ячейками, которые я в сообщении выше описал.
Вот этот вариант формулы + добавление ** к слову мне прям нравится (только пустые ячейки создают проблему)
=ПРОСМОТР(2;1/ПОИСК($R$23:$R$25;C23&D23&F23&G23);$S$23:$S$25)
 
Цитата
написал:
формула все равно будет находить значения. (а не должна)
Да, это при нулевом значении ИНДЕКС выдает первое значение массива.
Формулу немного подправил, может кому пригодится  с массивным вводом
Код
=ЕСЛИОШИБКА(ИНДЕКС($S$23:$S$25;ПОИСКПОЗ(1;--ЕЧИСЛО(ПОИСК($P$23:$P$25;F23))*($N$23:$N$25=C23)*($O$23:$O$25=D23);));"")
А по вашему вопросу прописал формулу без массивного ввода, со звездочками как вам нравится. Чуток длинновата правда, но попробуйте.
Код
=ЕСЛИ(СУММПРОИЗВ(СЧЁТЕСЛИМН(C23;$N$23:$N$33;D23;$O$23:$O$33;F23;$P$23:$P$33));ИНДЕКС($S:$S;СУММПРОИЗВ(СЧЁТЕСЛИМН(C23;$N$23:$N$33;D23;$O$23:$O$33;F23;$P$23:$P$33)*СТРОКА($P$23:$P$33)));"")
Изменено: gling - 13.01.2025 21:26:40
 
Цитата
написал:
А по вашему вопросу прописал формулу без массивного ввода, со звездочками как вам нравится. Чуток длинновата правда, но попробуйте.
Да немного запутанно вышло но вообще поидее вроде работает, похоже то что нужно.
Теперь постараюсь понять эту логику и адаптировать под себя.

Единственная потенциальная уязвимость (но вероятность что она случится я думаю достаточно мала)
Это если например встречается 2 слова в одном предложении,
приложил файл в нём выделил красным.

Но я думаю это не такая уж критичная история, врядле у меня такие совпадения будут.
 
А так не подойдет? Но результат один из совпадений.
Код
=ЕСЛИ(СУММПРОИЗВ(СЧЁТЕСЛИМН(C23;$N$23:$N$33;D23;$O$23:$O$33;F23;"*"&$P$23:$P$33&"*"));ПРОСМОТР(;-ПОИСК($P$23:$P$25;F23);$S$23:$S$25);"")
Изменено: gling - 14.01.2025 00:17:48
 
Цитата
написал:
А так не подойдет? Но результат один из совпадений.
Ну тут получается таже проблема что и в моих формулах.
Если вот тут расширить диапазон на пустые ячейки
ПРОСМОТР(;-ПОИСК($P$23:$P$25;F23);$S$23:$S$25);"")

То формула перестаёт работать.

А у меня в основном файле в диапазоне к сожалению присутствуют пустые ячейки
 
Zoxma,  проще UDF:
Код
Function VPRpoCasti(s1, d1Arr, s2, d2Arr, s3, d3Arr, Result) As String
Dim i&, a, b, c

a = d1Arr.Value
b = d2Arr.Value
c = d3Arr.Value

For i = 1 To UBound(a)
    If a(i, 1) = s1 Then
        If b(i, 1) = s2 Then
            If s3 Like "*" & c(i, 1) & "*" Then
                VPRpoCasti = Result(i, 1)
                'Exit For ' включить если нужно первое совпадение!
            End If
        End If
    End If
Next

End Function


Вот это вбить в красную:
Код
=VPRpoCasti(C28;$N$23:$N$33;D28;$O$23:$O$33;F28;$P$23:$P$33;$S$23:$S$33)
 
Цитата
написал:
проще UDF:
Решение шикарное но к сожалению в этом случае макросы использовать не могу, для коллег это ругательное слово.
Хотя всё таки подумать на этот счет, можно но тоже крайний случай.

Ещё возвращаясь назад к формуле
Код
=ПРОСМОТР(2;1/ПОИСК($R$23:$R$25;C23&D23&F23&G23);$S$23:$S$25)
Посетила меня идея может быть вот этот диапазон $R$23:$R$25 загнать в диспетчер имен попутно расширив его в $R$23:$R$33
И в диспетчере имён прописать какую нибудь формулу которая из диапазона исключит пустые ячейки.
(так сказать преобразует его в новый диапазон но уже без пустых)

Если быть точным некоторые ячейки не только пустые но и с результатом формулы =""

Правда тогда скорее всего нужен будет второй диапазон (так сказать "диапазон результатов" аналогичный диапазону без пустых ячеек) ($S$23:$S$33)
 
Знаете всё таки если долго мучатся то что нибудь получится.

Экспериментальным путём вывелась формула.
Как она действует хрен его знает, но вроде на файле примера работает, надо канеш проверять более подробно.
Код
=ПРОСМОТР(1;ПОИСК(СМЕЩ($R$23;0;0;СЧЁТЗ($R$23:$R$34);1);C23&D23&F23&G23);$S$23:$S$34)
Но минус этой формулы что пустые ячейки то убираются, а вот ячейки ="" , рушат формулу


НО опять же хрен пойми как способом наугад, я сделал вот "ЭТО"
Код
=ПРОСМОТР(1;ПОИСК(СМЕЩ($R$23;0;0;СЧЁТЕСЛИ($R$23:$R$34;">=*");1);C23&D23&F23&G23);$S$23:$S$34)
И эта формула вроде как адекватно работает и на пустые ячейки и на =""

По факту решение практически идеальное, одна мелочь осталась
если для текста "экологический сбор за 2025 год"
во вспом таблице создано 2 правила
первое со словом *экологический сбор*
второе со словом *сбор*
То формула выберет второе, по той причине что оно последнее.

Собственно к чему я веду, былобы просто невероятно шикардосно если бы формула выбирала то значение в котором больше символов совпадает.
Тоесть не последнее а то которое написано более подробно

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

Вообще сам себе удивляюсь как это может работать, непонятно.




PS/ похоже всё таки поторопился я =(   Если во вспом таблице пропустить 1 строку и заполнить, перескочив (то есть в приложенном примере, сразу заполнить строку N28 то формула рушится =(, обидно.
Изменено: Zoxma - 14.01.2025 21:47:11
 
Отсортируйте список по длине критерия - и будет выбирать как задумаете.
И мой вариант UDF тоже берёт первое или последнее, выгоднее на больших диапазонах брать первое.
 
Zoxma, если вы хотите сгруппировать затраты внутри одной статьи, почему бы изначально не использовать Субконто Дт2, оно у вас пустует. И не придется выдумывать костыли.
 
Цитата
написал:
Zoxma , если вы хотите сгруппировать затраты внутри одной статьи, почему бы изначально не использовать Субконто Дт2, оно у вас пустует. И не придется выдумывать костыли.


Не совсем понятно что именно вы предлагаете.Вы предлагаете какую то формулу поместить в колонку Субконто Дт2 ?
Или вы предлагаете чтобы в 1С при создании документов указывали нужную мне группу затрат?

Если первое то расскажите подробнее вашу идею
Если второе то это отпадает, слишком много людей задействовано+ там "свои проблемы"

Или вы вообще предлагаете просто от руки заполнять список?


А по теме вот этой формулы
=ПРОСМОТР(2;1/ПОИСК($R$23:$R$25;C23&D23&F23&G23);$S$23:$S$25)

Рабочий файл у меня от примера немножко отличается (кол-вом колонок во вспом таблице)

И решил всётаки попробовать сделать доп столбец и в нём формулой, если ячейка пустая, то вместо пустого прописать "какой-нибудь левый текст" который точно не встретится в таблице.

И сразу распространить это на нужный мне диапазон (условно на 200 строк)

Таким образом пустых ячеек у меня не будет и проблема решится, правда на скорости может отразится, но надеюсь не критично.
Страницы: 1
Наверх