Страницы: 1
RSS
Получить все найденные значения ВПР по частичному совпадению
 
Всем привет :)
Столкнулся с проблемой... Есть несколько столбцов, из них на данный момент интересует всего 2, во втором столбце (В) есть куча данных в данных есть цифры которые повторяются (цифры в середине или около того). Необходимо чтобы при вводе этих цифр заполнялись ячейки данными из столбца (А). пример во вложении сделал... к сожалению сам пока не понял как это сделать. через ВПР не получается так как он дает только первое попавшееся значение. В инете не нашел решения этой проблемы...
 
UDF:
Код
=VLOOKUP22(A:B;2;"*"&$E$1&"*";СТРОКА()-1;1)

и протянуть вниз насколько хватит совести :)
Код:
Код
Function VLOOKUP22(Table As Variant, SearchColumnNum As Long, SearchValue As Variant, _
                 n As Long, ResultColumnNum As Long)    ' как впр2/VLOOKUP2, но сравнение по Like
    Dim i As Long, iCount As Long
    If TypeName(Table) = "Range" Then Table = Intersect(Table.Parent.UsedRange, Table).Value
    VLOOKUP22 = ""
    For i = 1 To UBound(Table)
        If Table(i, SearchColumnNum) Like SearchValue Then iCount = iCount + 1
        If iCount = n Then
            VLOOKUP22 = Table(i, ResultColumnNum)
            Exit For
        End If
    Next i
End Function
Изменено: Hugo - 31.08.2020 17:56:45
 
<#2>
Hugo, я так понимаю первое это код в строчку, а второе это макрос???
можно немного расшифровки??? я еще макросы не изучал...
хотя в моем возрасте стыдно об этом говорить :)
Заранее благодарен
Изменено: Александр Миронов - 01.09.2020 10:04:56
 
Первое - это формула в ячейке.
Второе - это код пользовательской функции, у меня он в надстройке.
Что это и как - есть в приёмах сайта https://www.planetaexcel.ru/techniques/3/59/
 
Hugo,спасибо огромное, сейчас буду разбираться и пробовать
 
В файле выглядит вот так: http://prntscr.com/u92zls
Отметил что это работает и в xlsx, если код в надстройке.
 
Hugo, прописал макрос, формулу вставил, но выдает ошибку...
Нашел еще одну формулу на просторах инета и тоже ничего не получается(((
Код
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3));»»)} 

Может что-то я не так делаю и прописываю?
 
Александр Миронов, Доброго дня. ещё один вариант

Формулу массива нужно распространять на диапазон той же размерности, что и исходный список. Вышеуказанная формула массива будет возвращать несколько значений, поэтому перед вводом формулы нужно выделить сразу весь диапазон, т.е. список у вас в A2:A30 а выделяете С2:С30 (ну или куда вы там будите вставлять) , ввести формулу в Строке формул и нажать CRTL+SHIFT+ENTER

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$30;НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИОШИБКА(ПОИСК($E$1;$B$2:$B$30);0)>0;СТРОКА($B$2:$B$30)-СТРОКА($B$1);99999999);СТРОКА(ДВССЫЛ("B1:B"&ЧСТРОК($B$2:$B$30)))));"")
 
Цитата
Александр Миронов написал:
Может что-то я не так делаю и прописываю?
1. Не в том столбце ищите.
2. Не то по порядку совпадение выводите.
3. Устал удалять дубли модулей, что думали чем больше копий, тем лучше будет работать? :) Хотя хуже от этого не стало, только себя запутаете.
Работает вот так:
Код
=VLOOKUP22(Sheet1!B:D;3;"*"&$C$2&"*";СТРОКА()-2;1)
Изменено: Hugo - 01.09.2020 10:14:04
 
Hugo, странно... Все равно не получается.
1.Shet1!B:D - поиск в массиве,
2."*"&$C$2&"*" - часть значения которое ищем в массиве
3. СТРОКА()-2 - дополнительный счетчик
4. 1 - истина или ложь
При вводе формулы выводит в ячейку #ИМЯ?
 
Wild.Godlike, да, эту формулу тоже пробовал, первое значение находит нормально, но остальные значения или ноль или пусто
 
Цитата
Александр Миронов написал:
4. 1 - истина или ложь
- а имя аргумента ни на что не намекает? Или Вы не умеете пользоваться мастером формул? А ведь полезно.
И #ИМЯ? может не будет выводить, если настраивать мастером. Ибо разделители аргументов...
Причём в Вашем файле у меня формула в J3 работает и без коррекций, правда логично выводит не с первого значения.
Только я не имею информации что именно нужно выводить :)
И да, для первого столбца находит только одно значение, потому при СТРОКА()-1 ничего и не показывало.
Изменено: Hugo - 01.09.2020 11:22:30
 
Александр Миронов, вы файл смотрели файл который вложен?
Смотрите вложение) куда ещё проще объяснить хз)
Изменено: Wild.Godlike - 01.09.2020 11:31:16
 
Wild.Godlike, Да, спасибо большое! разобрался с массивом, получилось.
Теперь буду разбираться как удалить лишнее и как автоматически массив дублировать на другие данные

Hugo, J3 выдало по формуле Wild.Godlike, пробую вывести значения по искомому значению O2, но ничего не выдает, в мастере менял наименование О2, но тогда значение все равно не выдает. Ну и мастером я еще не умею пользоваться).
Если честно то экселем начал пользоваться пол года назад и до этого справлялся видеоуроками, но в данном случае не получилось справиться.
И спасибо Всем что помогаете.
 
Александр Миронов,  в этом последнем файле =VLOOKUP22(Sheet1!B:D;3;"*"&$C$2&"*";СТРОКА()-2;1) вполне работает, результат аналогичен той стандартной, если дать соотв.значение.
А дубли думаю проще будет удалить в UDF, добавить в код коллекцию или словарь...
P.S. Вот чуть изменил код.
И это... когда/если будете постить свою прежнюю версию - сами поудаляйте лишние модули, ибо задолбало :)
Изменено: Hugo - 01.09.2020 12:12:47
 
Hugo, огромное спасибо! разобрался с формулой и почему не работало ;)
Нашел как удалять лишние модели и все заработало.
В итоге формула получилась <=VLOOKUP22(Sheet1!$B:$D;3;"*"&C$2&"*";СТРОКА()-2;1)>.
Про то что проще добавить формулу  это я с Вами не соглашусь. проще для Вас а не для меня на данном этапе.
Воспользуюсь в итоге по стандартной схеме - через данные и удалить дубли.
Еще раз спасибо огромное за помощь!
Изменено: Александр Миронов - 01.09.2020 12:15:33
 
Так последняя версия VLOOKUP23() уже сама дубли не выводит. Правда я там нигде в описании это не указал, нужно просто запомнить :)
А так в принципе можно добавить аргумент БезПовторов как в других аналогичных, но это нужно код чуть усложнять.
 
Я ,как понимаю, просто должен сменить VLOOKUP22 на VLOOKUP23 и все будет ок?
 
Да. С заменой/добавлением кода. Ну файл ведь есть в #15
 
Hugo, спасибо большое, очень помогли и научили новому!!!
Теперь разбираюсь почему VLOOKUP22 на VLOOKUP23 работают через раз, но это думаю как то связано либо с ПО, либо с моими кривыми руками :)

Еще вопрос: Если вставлять данные из двух столбцов, то что надо изменить?
В формуле просто изменение одной буквы столбца на другое не идет.
Я так понимаю что это надо прописать в макросе?
 
А можно же было в одном сообщении написать, да?
 
Цитата
Александр Миронов написал:
Еще вопрос: Если вставлять данные из двух столбцов, то что надо изменить?
- ничего, вставляйте :)
Только не пойму - как этот вопрос относится к этой UDF?
Вообще можно совместить две функции в одной ячейке, но в 23 это не будет согласовываться с удалением дубликатов.
Если точно объясните задачу - есть шанс получить точный ответ.
 
Hugo,На примере столбца С: формула берет данные с Sheet1 столбец B, но не учитывает Sheet1 столбец Е (мое упущение, потеряется часть данных).
Т.е. в столбце С под шифром 1184391 должно быть 2 значения, одно значение учитывается (из столбца В вкладка Sheet1).
 
Вот именно ПОД шифром? Вообще конечно всё можно сделать, но... какое-то странное невиданное желание... Точно нужно как в примере?
Или вот так достаточно?
Изменено: Hugo - 01.09.2020 16:49:18
 
Hugo, очень хорошо сделал, но к сожалению когда буду с еще 3-мя файлами совмещать, то не смогу разделить эти значения, поэтому мне проще как в моем примере.
Мне и так уже стыдно.... Вы очень выручаете.
 
Почему не сможете разделить? Да тот же текст по столбцам по результату, или строковыми функциями...
Не вполне понимаю что хотите - если выводить результат как в примере в две разные строки, то можно написать одну массивную UDF, но думаю Вам будет трудно ею пользоваться.
Может лучше сразу выводить в два рядом стоящих столбца? Вот брать этот последний результат и сразу выводить - в одну ячейку первую часть, рядом вторую. Чтоб код не переписывать.
Или вот так, как в примере - сделал для двух значений. Обязательно нужен столбец с нумерацией, функция СТРОКА() не работает...
Изменено: Hugo - 01.09.2020 18:06:13
Страницы: 1
Наверх