Форум программистов, компьютерный форум, киберфорум
VBA
Войти
Регистрация
Восстановить пароль
 
Рейтинг 4.52/21: Рейтинг темы: голосов - 21, средняя оценка - 4.52
0 / 0 / 0
Регистрация: 11.08.2017
Сообщений: 10
1

Усовершенствованный VLOOKUP на VBA

11.08.2017, 17:14. Показов 3847. Ответов 10

Добрый день. Пробовал использовать функцию VLOOKUP, но она возвращает только последнее совпадение в списке.
А мне надо, так одинаковых совпадений множество, чтобы оно возвращало значение всегда из соответствующего рядка.

Прикладываю фотки.
Усовершенствованный VLOOKUP на VBA


Усовершенствованный VLOOKUP на VBA


На второй картинке в 4 столбце значения, которые ищу, на первой картинке таблица, где осуществляется поиск и в 8 столбце значения которые должны возвращаться всегда из соответсвующего рядка.

Есть какие-то идеи?
__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь
0
Лучшие ответы (1)
Programming
Эксперт
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
11.08.2017, 17:14
Ответы с готовыми решениями:

Vlookup (ВПР) на VBA
Здавствуйте, Написал аналог функции vlookup (ВПР) на VBA. Основная задача была сделать нечто, что...

Есть ли в VBA оператор, который работает как VLOOKUP ну или Index и Match?
Всем здрасти! Простенький вопросик, есть ли в VBA оператор, который работает как VLOOKUP ну или...

Vlookup - 2,3,4 и т.д. найденнные значения
Доброе утро, помогите с написанием... и/или с идей как осуществить следующее: есть лист1, на...

VLookup с параметрами через переменные из значений функции
Не могу никак заставить работать VLookup с данными указанными в параметрах функции. Function...

10
6861 / 2795 / 530
Регистрация: 19.10.2012
Сообщений: 8,530
11.08.2017, 17:20 2
1. Не путайте людей - ВПР() возвращает не последнее, а по первому.
2. Подождём экстрасенса, который расскажет про "соответствующего рядка"
Если без экстрасенсов - ничего не понятно что нужно. А ВПРов всяких много уже понаписано...
0
0 / 0 / 0
Регистрация: 11.08.2017
Сообщений: 10
11.08.2017, 17:29  [ТС] 3
Я просто уже двое суток ищу, и накого решения не нашел.
Ну вот смотрите, к примеру так. На первой картинке программа натыкается на 194 РЯД и видит там в 4 столбце - 26054112 и теперь ищет это значение в таблице на второй картинке и натыкается на 2598 ряд, где в первом столбце видит тоже значение, после чего возвращает - -10629425,25. Далее идет на след. ряд на первой картинке (то есть 195) и видит опять - 26054112, тоже самое находит на 2599 ряду на второй картинке и возвращает - -1549761,31 и так далее.
0
6861 / 2795 / 530
Регистрация: 19.10.2012
Сообщений: 8,530
11.08.2017, 17:42 4
Есть такая ВПР() на VBA - ищите в сети ВПР2()

Добавлено через 1 минуту
Или вот вариант:
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
Function VLOOKUP2(Table As Variant, SearchColumnNum As Long, SearchValue As Variant, _
                N As Long, ResultColumnNum As Long)
    Dim i As Long, iCount As Long
    If TypeName(Table) = "Range" Then Table = Intersect(Table.Parent.UsedRange, Table).Value
    VLOOKUP2 = ""
    For i = 1 To UBound(Table)
        If Table(i, 1) = SearchValue Then iCount = iCount + 1
        If iCount = N Then
            VLOOKUP2 = Table(i, ResultColumnNum)
            Exit For
        End If
    Next i
End Function
Может даже такое и нашли бы...

Добавлено через 4 минуты
Да, параметр N - это какое по порядку нужно находить. Тут нужно по факту/месту придумать как это число подставлять, можно например использовать СЧЕТЕСЛИ().
0
0 / 0 / 0
Регистрация: 11.08.2017
Сообщений: 10
11.08.2017, 17:54  [ТС] 5
Нашел и попробовал уже. Спасибо.
Но или я чего-то не понимаю, или это не совсем то.
Так как тут 4-м аргументом задается номер вхождения, то есть я могу написать 1,2,3,или 4, а мне надо чтобы оно возвращало правильные значения для всех вхождений автоматически.

Добавлено через 2 минуты
понял. спасибо!
0
6861 / 2795 / 530
Регистрация: 19.10.2012
Сообщений: 8,530
11.08.2017, 17:55 6
Я там выше написал как делают. Удфке нужно указать какое вхождение возвращать, сама она высчитать не может, ей не хватает информации.
0
0 / 0 / 0
Регистрация: 11.08.2017
Сообщений: 10
11.08.2017, 18:33  [ТС] 7
попробовал, кажется работает. но пришел к тому, что из-за структуры репорта и из-за множества условий, которые надо потом добавить, это не всоем рабочий вариант

не знаете, есть ли еще какой-то способ реализовать это сугубо через код?


У меня кстати имеется вот такой код, но работает он не совсем правильно, а так как показано на картинках, что я прикрепил ранее. Может его можно как-то поменять, чтобы он работал так как мне надо?

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Sub MultipleSearch()
Dim aNum(), aTabl(), aRes()
Dim i As Long, k As Long, n As Long
    With Worksheets("Лист1") ' лист с таблицей для поиска'
        i = .Cells(.Rows.Count, "A").End(xlUp).Row
        aTabl = .Range("A1:G" & i).Value ' таблица в массив'
        ' в А - счет, в G - остаток'
    End With
 
    With Worksheets("Лист2") ' лист с таблицей (результат)'
        i = .Cells(.Rows.Count, "D").End(xlUp).Row ' искомые счета в столбце D'
        aNum = .Range("D1:D" & i).Value ' искомые счета в массив'
 
        ' здесь 3 - максимальное количество возможных значений для одного искомого'
        ReDim aRes(1 To i + 3, 1 To 1) ' задаем размерность массива'
        aRes(1, 1) = .Range("K1").Value ' заголовок столбца К'
    End With
 
    ' допускаем, что строка 1 - "шапка", ее в обработку не берем'
    For i = 2 To UBound(aNum) ' по искомым данным'
        n = i ' строка для записи найденного'
 
        For k = 2 To UBound(aTabl) ' по строкам таблицы'
            If aTabl(k, 1) <> Empty Then ' если не пусто'
                If aNum(i, 1) = aTabl(k, 1) Then
                    aRes(n, 1) = aTabl(k, 7): n = n + 1 ' запись найденного'
                End If
            End If
        Next k
    Next i
 
    ' выгрузка результата на лист в столбец К'
    Worksheets("Лист2").Range("K1").Resize(UBound(aRes), 1).Value = aRes
End Sub
0
6861 / 2795 / 530
Регистрация: 19.10.2012
Сообщений: 8,530
11.08.2017, 21:16 8
Сделайте файл-пример (с макросом внутри), и покажите какой нужен результат - тогда можно что-то думать.
0
0 / 0 / 0
Регистрация: 11.08.2017
Сообщений: 10
13.08.2017, 23:46  [ТС] 9
Добрый вечер. Прикладываю файл с тем макросом что имеется. Тот макрос, который нужно изменить называется MultipleSearch. Я в нем закомментировал весь блок который для вас не важен, чтобы он вас не сбивал с толку.

Во всем столбце J на листе Accounting output результаты от этого макроса, кроме J41:J43. Эти ячейки я отформатировал и задал туда правильный результат.

Макро берет номер счета с листа Accounting output, столбец D, сверяет его с листом KBSWG столбец A, и если они совпадают возвращает значение с листа KSWG столбец H.

Правда файл пришлось сильно почистить так как можно заливать только до 100кб, и макро отправляю отдельно, так как файлы с макросами тоже нельзя добавлять.

Буду очень признателен за помощь. Спасибо!
Вложения
Тип файла: xlsx example.xlsx (98.0 Кб, 7 просмотров)
Тип файла: txt MultipleSearch.txt (4.8 Кб, 5 просмотров)
0
6861 / 2795 / 530
Регистрация: 19.10.2012
Сообщений: 8,530
14.08.2017, 09:54 10
Лучший ответ Сообщение было отмечено vladron как решение

Решение

Там ведь ещё есть и валюта, и вроде как массив для неё создали, а использовать забыли...
Добавьте тут ещё одну проверку, вот так:
Visual Basic
1
2
3
4
5
                        If aNum(i, 1) = aTabl(k, 1) Then
                         If aCcyMV(i, 1) = aTabl(k, 2) Then
                            aRes(N - 3, 1) = aTabl(k, 8): N = N + 1 'zapis nalezenych hodnot
                        End If
                         End If
Добавлено через 2 минуты
P.S. А файлы с макросами можно архивировать! Правдя я архивы на работе не могу качать...

Добавлено через 52 минуты
Но можно сделать более быстрый код на словаре - собрать в словарь для каждого сочетания номер|валюта суммы (если например несколько таких строк), затем выгрузить всё в первую такую строку.
Или иначе - собрать в словаре коллекцию значений для каждого сочетания, и так и выгружать, а в финале анализировать/сообщать всё ли сошлось - т.е. нет ли остатка в словаре, и всем ли строкам нашлось что выгрузить.
Но чтоб так делать - нужно видеть всю задачу, со всеми мелочами. И есть ли вообще в системе словарь.
0
0 / 0 / 0
Регистрация: 11.08.2017
Сообщений: 10
14.08.2017, 12:10  [ТС] 11
Добрый день. Это я еще неделю назад собирался добавить условие с валютой, но подумал, что это не решит мою проблему, и оставил это на потом. Думал что оно все равно будет выгружать все значения для каждого сочетания, а оно вот как!!! Решение оказалось таким простым!!!

Если будут еще какие-то небольшие вопросы по этому заданию, я сюда напишу

Спасибо вам большое за помощь! Теперь все работает так как надо!
0
IT_Exp
Эксперт
87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
14.08.2017, 12:10

Ввести формулу VLOOKUP в первую отфильтрованную строчку
Здравствуйте, возник такой вопрос. Весь интернет облазил, ничего не смог найти. Пишу макрос для...

Макрос для сравнения данных в двух столбцах, вместо vlookup
Подскажите, пожалуйста, как написать макрос, который бы можно было использовать для сравнения...

Команда Vlookup
Всем добрый день! У меня есть массив данных, вставленный из файла эксель. С помощью операции...

Усовершенствованный UPS
Появилась идея немного усовершенствовать UPS. То есть заменить стандартный аккумулятор (который уже...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
11
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin
Copyright ©2000 - 2022, CyberForum.ru