#excel #vlookup #vba
Вопрос:
Я нашел этот VBA, который способен возвращать все соответствующие значения в одну ячейку, используя один критерий соответствия:
Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long)
'Update 20150310
Dim rng As Range
Dim xResult As String
xResult = ""
For Each rng In pWorkRng
If rng = pValue Then
xResult = xResult amp; " " amp; rng.Offset(0, pIndex - 1)
End If
Next
MYVLOOKUP = xResult
End Function
Но мне нужно, чтобы этот VLOOKUP возвращал значения по сравнению с несколькими критериями соответствия.
Есть идеи, как это можно улучшить?
Спасибо. Обновление ниже:
Мне нужна формула для возврата значений в одной ячейке, где совпадают A1-1A и A. 0002. Результат должен быть 8 3
Комментарии:
1. Можете ли вы привести нам несколько примеров // как выглядят ваши данные?
Ответ №1:
Вот несколько иной подход.
Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
Dim d As Long
Dim c As Long
Dim arr2()
Dim t As Long, y As Long
t = -1
y = -1
If TypeName(arr) = "Range" Then
arr2 = arr.Value
Else
arr2 = arr
End If
On Error Resume Next
t = UBound(arr2, 2)
y = UBound(arr2, 1)
On Error GoTo 0
If t >= 0 And y >= 0 Then
For c = LBound(arr2, 1) To UBound(arr2, 1)
For d = LBound(arr2, 1) To UBound(arr2, 2)
If arr2(c, d) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN amp; arr2(c, d) amp; delim
End If
Next d
Next c
Else
For c = LBound(arr2) To UBound(arr2)
If arr2(c) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN amp; arr2(c) amp; delim
End If
Next c
End If
TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function
Это позволяет вам определить разделитель, например, вы можете иметь ,
или просто пробел, или что угодно, что вы хотите поместить между возвращаемыми значениями.
Второй критерий спрашивает, хотите ли вы вернуть пустое место для любого пустого места.
В третий вы бы поместили форму массива IF (), которая использует критерии, которые вы хотите отфильтровать возвращаемые значения.
Поэтому в вашем случае вы бы использовали это в форме массива:
=TEXTJOIN(" ",TRUE,IF((A2:A7="A")*(B2:B7=2),C2:C7,""))
В " "
нем говорится, что нам нужно пространство между значениями.
Это TRUE
означает, что мы пропускаем любые пробелы, это важно, так как мы отправляем пробелы, когда значения не оправданы фильтром.
IF((A2:A7="A")*(B2:B7=2),C2:C7,"")
циклически проходит по столбцам и возвращает значения, когда оба логических теста ВЕРНЫ, если нет, он возвращает пустое значение.
Формула бытия и массива должна быть подтверждена с помощью Ctrl-Shift-Enter при выходе из режима редактирования вместо Ввода. Если все сделано правильно, то Excel поместит {}
формулу вокруг.
Если вы хотите вернуть полный столбец, вы можете просто использовать:
=TEXTJOIN(" ",TRUE,C2:C7)
В обычной форме, и он вернется 8 3 3 9 2 3
в одну ячейку.
записка
Если у вас есть Office 365, Excel TEXTJOIN
-это формула, существующая изначально, которая вводится, как указано выше, в обоих случаях.
В Office 365 также есть ФИЛЬТР, и мы можем использовать:
=TEXTJOIN(" ",TRUE,FILTER(C2:C7,(A2:A7="A")*(B2:B7=2),""))
Комментарии:
1. Будет ли иметь значение, если столбцы будут перемешаны по порядку? Поскольку текущий порядок стоит A B C, что, если это будет C A B ?
2. @Martin ни на йоту не меняйте диапазоны, чтобы они соответствовали. И убедитесь, что вы вводите его в виде массива с помощью Ctrl-Shift-Enter вместо ввода.
3. Какой вариант занимает больше времени для расчета? Используя VBA или формулу на себе? Какой из них вы бы предложили, так как я с нетерпением жду работы примерно с 15-20 тысячами строк.
4. Если у вас есть Office 365 excel, используйте только формулу. Если вы этого не сделаете, я бы использовал, как я показал выше. Не используйте ссылки на полные столбцы, но ограничивайте ссылки фактическим набором данных.