LOOKUP-Referenz mit mehreren Ergebniswerten

Question

Ich möchte die verschiedenen Altersgruppen von Personen, die mit einer einzigen Referenznummer verknüpft sind, herausfiltern und anzeigen.

Der folgende Ausschnitt zeigt das eingerichtete Blatt. Das Blatt hat ungefähr 2500 Zeilen:

App ref,Relationship,Age,Date of Birth,Gender,Marital Status
1,A1,34,12-Apr-85,F,SINGLE
1,A1,34,12-Apr-85,F,SINGLE
1,PT,30,12-Oct-89,M,SINGLE
830,A1,63,10-Nov-56,F,MARRIED
830,SN,32,11-Nov-87,M,SINGLE
830,HU,65,24-Sep-54,M,MARRIED
846,A1,75,11-Aug-44,M,MARRIED
846,A2,72,16-Nov-47,F,MARRIED

Für jedes App Refmöchte ich alle zugehörigen Altersangaben herausziehen:

| App ref | Age 1 | Age 2 | Age 3 |
|---------|-------|-------|-------|
| 1       |  34   |  34   |  30   |
| 830     |  63   |  32   |  65   |
| 846     |  75   |  72   |  -    |

Answer 1

1. Kopieren Sie die Spalte A in eine unbenutzte Spalte, in der Ihre Zieldaten beginnen, und verwenden Sie Daten, Duplikate entfernen, um einen Satz eindeutigerApp-ReferenzZahlen. In meinem Beispielbild unten habe ich Spalte H verwendet.

2. Tragen Sie diese Formel in die Zelle Zeile 1 direkt rechts neben Ihrem eindeutigenApp-ReferenzZahlen und ziehen Sie sie nach rechts. In meinem Beispiel habe ich bei I1 begonnen.

   =TEXT(COLUMN(A:A), "\A\g\e 0")
   

3. Setzen Sie diese Formel unterAlter 1und ziehen Sie nach rechts und unten. In meinem Beispiel habe ich bei I2 begonnen.

   =IFERROR(INDEX($C:$C, AGGREGATE(15, 7, ROW($C$2:INDEX($C:$C, MATCH(1E+99, $C:$C)))/($A$2:INDEX($A:$A, MATCH(1E+99, $C:$C))=$H2), COLUMN(A:A))), "")
   

Wenn dasApp-ReferenzWenn die Spalte aufsteigend sortiert ist, können Sie die Berechnung reduzieren, indem Sie die Nachschlagezeilen begrenzen.

=IFERROR(INDEX($C:$C, AGGREGATE(15, 7, ROW(INDEX($A:$A, MATCH($H2, $A:$A, 0)):INDEX($A:$A, MATCH($H2, $A:$A))), COLUMN(A:A))), "")

Answer 2

Dies geht ganz einfach mitPower Query ein kostenloses Add-In von Microsoft in Excel 2010+; enthalten ab Get & TransformExcel 2016+

Mit Ausnahme der Eingabe der benutzerdefinierten Spaltenformel kann alles über die GUI erledigt werden, und wenn sich Ihre Originaldaten ändern, lässt sich die Abfrage problemlos aktualisieren.

• Wählen Sie eine Zelle in Ihrer Originaltabelle aus
• Data--> Get & Transform --> from Table/Range
• Gruppiere nachApp ref Operation == Alle Zeilen

• Benutzerdefinierte Spalte hinzufügen (um die AgeSpalte in eineList
  • Formel=Table.Column([Merged],"Age")
• Klicken Sie mit der rechten Maustaste auf den Doppelpfeil dieser neuen benutzerdefinierten Spalte und wählen Sie „Werte extrahieren mit“ commaals Trennzeichen.
• Teilen Sie die Spalte mit „. commaals Trennzeichen“.
• Benennen Sie die neuen Spalten um in Age netc.
• Schließen und Ergebnisse laden

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"App ref", Int64.Type}, {"Relationship", type text}, {"Age", Int64.Type}, {"Date of Birth", type datetime}, {"Gender", type text}, {"Marital Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"App ref"}, {{"Merged", each _, type table [App ref=number, Relationship=text, Age=number, Date of Birth=datetime, Gender=text, Marital Status=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Merged],"Age")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom.1", "Age 1"}, {"Custom.2", "Age 2"}, {"Custom.3", "Age 3"}})
in
    #"Renamed Columns"