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

Excel - Comment rechercher pour retourner des valeurs multiples ?

Je cherche à utiliser Excel pour rechercher et retourner des valeurs de référence multiples pour une clé donnée. VLookup fait quelque chose de très similaire à ce dont j'ai besoin - mais ne renvoie qu'une seule correspondance.

Je suppose que cela impliquera des méthodes de retour et de manipulation de tableaux, bien que je n'aie jamais traité ces questions auparavant. Certaines recherches sur Google commencent à s'appuyer sur le if([lookuparray]=[valeur],row[lookuparray]) comme partie d'une solution - bien que je ne puisse pas obtenir qu'il renvoie une seule correspondance…

Par exemple, si j'ai ces données de référence : 

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

j'essaie d'obtenir les multiples valeurs de retour sur la droite. (séparées par des virgules, si possible) 

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

(j'ai déjà la valeur clé à gauche - pas besoin d'extraire ces valeurs)

Toute aide sur la manière d'aborder le traitement des valeurs multiples dans ce contexte est appréciée. Merci.
Source

Réponses (4)

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

En supposant que vous voulez une approche par formule comme indiqué (sans utiliser VLOOKUP, mais toujours une formule), voici comment j'ai présenté les données :

J'ai ensuite utilisé la formule suivante dans la cellule 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))

Il s'agit d'une formule de tableau, donc après l'avoir copiée et collée dans la cellule, vous devez appuyer sur Ctrl+Shift+Enter. Je l'ai ensuite simplement déplacée vers la droite et vers le bas.

S'il ne reste aucune valeur, cela donne l'erreur #NUM!, j'ai donné un exemple pour le jaune dans l'exemple d'image téléchargée.

Je pense qu'une approche VBA/Macro serait une meilleure solution si vous avez des tonnes de lignes.
Source
5
5
5
2013-01-15 13:35:44 +0000

  1. Échangez les colonnes de sorte que les couleurs soient dans la colonne A et les noms dans la colonne B, puis triez sur la couleur.

  2. Formule en C2 (à copier dans la colonne) : =IF(A2<>A1,B2,C1 & “, ” & B2)

  3. Formule en D2 (à recopier dans la colonne) : =A2<>A3

  4. Filtrez pour “VRAI” sur la colonne D pour obtenir les résultats souhaités. Voir ci-dessous :

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

Voici la solution VBA pour vous. Tout d'abord, voici à quoi ressemblent les résultats :

Et voici le code : 

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

Si vous souhaitez une approche par formule, il est beaucoup plus simple d'obtenir les résultats dans des cellules séparées. Supposons donc que votre premier tableau soit A2:B8 et que les couleurs soient à nouveau répertoriées dans D2:D5. Essayez cette formule dans 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))),"")

confirmée par CTRL+SHIFT+ENTER et copiée de part et d'autre. Lorsque les correspondances sont épuisées, vous obtenez des blancs. La formule

suppose Excel 2007 ou une version ultérieure - si une version antérieure, vous pouvez utiliser COUNTIF au lieu de IFERROR, c'est-à-dire

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