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

Excel - Come vlookup per restituire valori multipli?

Sto cercando di usare Excel per cercare e restituire valori di riferimento multipli per una data chiave. VLookup fa qualcosa di molto simile a quello di cui ho bisogno - ma restituisce solo una singola corrispondenza.

Suppongo che comporterà il ritorno dell'array e metodi di gestione, anche se non ho mai avuto a che fare con questi prima d'ora. Alcuni Googling iniziano ad appoggiarsi all'if([lookuparray]=[valore],row[lookuparray]) come parte di una soluzione - anche se non riesco a fargli restituire una singola corrispondenza…

Per esempio, se ho questi dati di riferimento: 

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

sto cercando di ottenere i valori di ritorno multipli sulla destra. (virgola separata, se possibile) 

Red Adam, Bob, Carl
Green Adam, Bob
Blue Adam
Yellow Bob
``` &001 


(ho già il valore chiave a sinistra - non c'è bisogno di estrarre questi valori) 


Si apprezza qualsiasi aiuto su come affrontare la gestione dei valori multipli in questo contesto. Grazie.
Fonte

Risposte (4)

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

Supponendo che si voglia un approccio basato su una formula come detto (non usando VLOOKUP, ma sempre una formula), ecco come ho esposto i dati:

Ho poi usato la seguente formula nella cella 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))
``` &001 


Questa è una formula di matrice, quindi dopo averla copiata e incollata nella cella si deve premere `Ctrl+Shift+Enter`. Poi l'ho semplicemente trascinata a destra e in basso. 


Se non c'è nessun valore(i) rimanente(i) dà l'errore `#NUM!`, ho dato un esempio per il giallo nell'esempio di immagine caricata. 


Penso che un approccio VBA/Macro sarebbe una soluzione migliore se si hanno tonnellate di righe.
Fonte
5
5
5
2013-01-15 13:35:44 +0000
    1. Scambiare le colonne in modo che i colori siano nella colonna A e i nomi nella colonna B, e poi ordinare sul colore. 2. Formula in C2 (copiarla in basso nella colonna): =IF(A2<>A1,B2,C1 & “, ” & B2)
    1. Formula in D2 (copiarla in basso nella colonna): =A2<>A3

  3. Formula in D2 (copiarla in basso nella colonna): =A2<>A3

  4. Formula in D2 (copiarla in basso nella colonna): =A2<>A3

  5. Filtro per “VERO” sulla colonna D per ottenere i risultati desiderati. Vedere sotto:

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

Ecco la soluzione VBA per voi. In primo luogo, ecco i risultati:

&004

Ed ecco il codice: 

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

Se si vuole un approccio basato su una formula, allora è molto più semplice ottenere i risultati in celle separate, quindi supponiamo che la prima tabella sia A2:B8 e che i colori siano elencati di nuovo in D2:D5. Provate questa formula in 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))),"")

confermato con CTRL+SHIFT+ENTER e copiato attraverso e giù. Quando le partite si esauriscono si ottengono degli spazi vuoti.

Formula assume Excel 2007 o successivo - se la versione precedente si può usare COUNTIF invece di IFERROR, cioè

=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))))
Fonte