2013-01-14 21:43:56 +0000 2013-01-14 21:43:56 +0000
20
20

Excel - как просмотреть, чтобы вернуть несколько значений?

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

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

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

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

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

Любая помощь в подходе к работе с несколькими значениями в этом контексте приветствуется. Спасибо.

Ответы (4)

14
14
14
2013-01-14 22:17:56 +0000

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

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

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

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

Если не осталось никаких значений, это дает ошибку #NUM!, я привел пример для желтого цвета в примере загруженного изображения.

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

5
5
5
2013-01-15 13:35:44 +0000
  1. Поменяйте местами столбцы так, чтобы цвета были в столбце A, а имена - в столбце B, а затем отсортируйте по цвету.

  2. Формула в C2 (скопируйте ее в колонку): =IF(A2<>A1,B2,C1 & “, ” & B2)

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

  4. Фильтр “TRUE” в колонке D для получения желаемых результатов. См. ниже:

4
4
4
2015-02-23 13:45:45 +0000

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

И вот код:

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
3
3
2013-01-14 21:59:35 +0000

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

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

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

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

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

Похожие вопросы

6
13
9
10
2