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

Excel - Jak vlookupować, aby zwrócić wiele wartości?

Szukam użyć Excela, aby wyszukać i zwrócić wiele wartości referencyjnych dla danego klucza. VLookup robi coś bardzo podobnego do tego, czego potrzebuję - ale zwraca tylko jedno dopasowanie.

Zakładam, że będzie to wymagało zwrócenia tablicy i metod obsługi, choć nie zajmowałem się nimi wcześniej. Niektóre Googlowanie zaczyna opierać się na if([lookuparray]=[value],row[lookuparray]) jako części rozwiązania - choć nie mogę uzyskać zwrotu pojedynczego meczu…

Na przykład, jeśli mam te dane referencyjne: 

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

staram się uzyskać wiele wartości zwrotnych po prawej stronie. (przecinek oddzielony, jeśli to możliwe) 

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

(mam już kluczową wartość po lewej - nie ma potrzeby wyciągania tych wartości)

Wszelka pomoc dotycząca sposobu podejścia do obsługi wielu wartości w tym kontekście jest mile widziana. Dziękuję.
Źródło

Odpowiedzi (4)

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

Zakładając, że chcesz podejście do formuły, jak podano (nie przy użyciu VLOOKUP, ale nadal formuła), tutaj jest, jak układałem dane:

Następnie użyłem następującej formuły w komórce 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))

Jest to formuła macierzowa, więc po skopiowaniu i wklejeniu go do komórki musisz trafić Ctrl+Shift+Enter. Następnie po prostu przeciągnąłem ją w prawo i na dół.

Jeśli nie ma już wartości(y), to daje to błąd #NUM!, podałem przykład dla żółtego w przykładzie załadowanego obrazu.

Myślę, że podejście VBA/Macro byłoby lepszym rozwiązaniem, jeśli masz mnóstwo wierszy.
Źródło
5
5
5
2013-01-15 13:35:44 +0000

  1. Zamień kolumny tak, aby kolory znajdowały się w kolumnie A, a nazwy w kolumnie B, a następnie posortuj na kolor.

  2. Formuła w C2 (skopiuj ją w dół kolumny): =IF(A2<<>A1,B2,C1 & “, ” & B2)

  3. Wzór w D2 (skopiuj go w dół kolumny): =A2<<A3

  4. Filtr dla “TRUE” w kolumnie D, aby uzyskać pożądane wyniki. Zobacz poniżej:

Źródło
4
4
4
2015-02-23 13:45:45 +0000

Oto rozwiązanie VBA dla Ciebie. Po pierwsze, oto jak wyglądają wyniki:

A oto kod: 

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
Źródło
3
3
3
2013-01-14 21:59:35 +0000

Jeśli chcesz skorzystać z podejścia opartego na formułach, o wiele łatwiej jest uzyskać wyniki w osobnych komórkach, więc załóżmy, że twoja pierwsza tabela to A2:B8, a kolory są wymienione ponownie w D2:D5. Wypróbuj tę formułę w 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))),"")

potwierdzoną z CTRL+SHIFT+ENTER i skopiowaną w poprzek i w dół. Gdy skończą się mecze otrzymujesz puste miejsca.

Formuła zakłada Excel 2007 lub nowszy - jeśli wcześniejsza wersja, możesz użyć COUNTIF zamiast IFERROR, czyli

=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))))
Źródło