Excel - как vlookup возвращает несколько значений?

Я хочу использовать Excel для поиска и возврата нескольких ссылочных значений для данного ключа. ВПР делает что-то очень похожее на то, что мне нужно, - но возвращает только один матч.

Я предполагаю, что это будет включать в себя методы возврата и обработки массивов, хотя я с ними раньше не сталкивался. Некоторые погуглить начинается, чтобы опереться на если([массив]=[значение],ряд[массив]) как часть решения - хотя я не могу получить его, чтобы вернуться на один матч...

для пример, если у меня есть такие справочные данные:

Adam    Red
Adam    Green
Adam    Blue
Bob     Red
Bob     Yellow
Bob     Green
Carl    Red

Я пытаюсь получить несколько возвращаемых значений справа. (По возможности через запятую)

Red      Adam, Bob, Carl
Green    Adam, Bob
Blue     Adam
Yellow   Bob

(у меня уже есть значение ключа слева - не нужно вытаскивать эти значения)

любая помощь о том, как подойти к обработке нескольких значений в этом контексте является apprecited. Благодаря.

16
задан overflew
05.12.2022 15:30 Количество просмотров материала 2633
Распечатать страницу

4 ответа

предполагая, что вы хотите подход формулы, как указано (не используя VLOOKUP, но все же формулу), вот как я выложил данные:

Data Layout

затем я использовал следующую формулу в ячейке C12:

=INDEX($C:$C, SMALL(IF($B12=$B:$B, ROW($B:$B)-MIN(ROW($B:$B))+1, ""), 1))

это формула массива, так что после копирования и вставки в ячейку вы должны нажать Ctrl+Shift+Enter. Тогда я просто перетащил его вправо и вниз.

Если нет никакого значения (ОВ) оставшиеся дает #NUM! ошибка, Я дал пример для желтого цвета в загруженном примере изображения.

Я думаю, что подход VBA / Macro был бы лучшим решением, если у вас есть тонны строк.

12
отвечен Dan 2022-12-06 23:18

  1. поменять местами столбцы так, чтобы цвета были в столбце A, а имена-в столбце B, а затем отсортировать по цвету.

  2. формула в C2 (скопируйте ее в столбец): = IF (A2<>A1,B2, C1&", " & B2)

  3. формула в D2 (скопируйте ее в столбец): =A2<>A3

  4. фильтр "TRUE" в столбце D для получения желаемых результатов. Смотрите ниже:

enter image description here

4
отвечен F106dart 2022-12-07 01:35

Если вы хотите формульный подход, то гораздо проще получить результаты в отдельных ячейках, поэтому давайте предположим, что ваша первая таблица A2:B8, а цвета снова перечислены в D2:D5. Попробуйте эту формулу в Е2

=IFERROR(INDEX($A:$A,SMALL(IF($B:$B=$D2,ROW($B:$B)-ROW($B)+1),COLUMNS($E2:E2))),"")

подтвержден CTRL+SHIFT+ENTER и копируется поперек и вниз. Когда спички заканчиваются, вы получаете заготовки.

формула предполагает Excel 2007 или более поздней версии-если более ранняя версия, вы можете использовать COUNTIF вместо IFERROR, то есть

=IF(COLUMNS($E2:E2)>COUNTIF($B:$B,$D2),"",INDEX($A:$A,SMALL(IF($B:$B=$D2,ROW($B:$B)-ROW($B)+1),COLUMNS($E2:E2))))

3
отвечен barry houdini 2022-12-07 03:52

вот решение VBA для вас. Во-первых, вот как выглядят результаты:

Screenshot

и вот код:

Option Explicit
Function LookupCSVResults(lookupValue As Variant, lookupRange As Range, resultsRange As Range) As String

    Dim s As String 'Results placeholder
    Dim sTmp As String  'Cell value placeholder
    Dim r As Long   'Row
    Dim c As Long   'Column
    Const strDelimiter = "|||"  'Makes InStr more robust

    s = strDelimiter
    For r = 1 To lookupRange.Rows.Count
        For c = 1 To lookupRange.Columns.Count
            If lookupRange.Cells(r, c).Value = lookupValue Then
                'I know it's weird to use offset but it works even if the two ranges
                'are of different sizes and it's the same way that SUMIF works
                sTmp = resultsRange.Offset(r - 1, c - 1).Cells(1, 1).Value
                If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then
                    s = s & sTmp & strDelimiter
                End If
            End If
        Next
    Next

    'Now make it look like CSV
    s = Replace(s, strDelimiter, ",")
    If Left(s, 1) = "," Then s = Mid(s, 2)
    If Right(s, 1) = "," Then s = Left(s, Len(s) - 1)

    LookupCSVResults = s 'Return the function

End Function
3
отвечен Engineer Toast 2022-12-07 06:09

Постоянная ссылка на данную страницу: [ Скопировать ссылку | Сгенерировать QR-код ]

Ваш ответ

Опубликуйте как Гость или авторизуйтесь

Имя
Мы используем cookie. Это позволяет нам анализировать взаимодействие посетителей с сайтом и делать его лучше. Продолжая пользоваться сайтом, вы соглашаетесь с использованием файлов cookie. Политика конфиденциальности
Согласен
Вверх