具有多個結果值的 LOOKUP 參考

Question

我希望提取並顯示與單一參考號碼相關的多個年齡的人。

下面的截圖顯示了設定的工作表。該工作表大約有 2500 行：

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

對於每個App Ref，我想提取所有相關的年齡：

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

Answer 1

1. 將 A 列複製到目標資料將在其中啟動的未使用列，並使用「資料」、「刪除重複項」來產生一組唯一的應用參考數字。在下面的範例圖片中，我使用了 H 列。

2. 將此公式放在第 1 行儲存格中，緊鄰您唯一的右側應用參考數字並將其向右拖曳。在我的範例中，我從 I1 開始。

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

3. 把這個公式放在下面1 歲並向右和向下拖曳。在我的範例中，我從 I2 開始。

   =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))), "")
   

如果應用參考列按升序排序，那麼您可以透過限制查找行來減少計算。

=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

您可以輕鬆地做到這一點Power Query Microsoft 在 Excel 2010+ 中提供的免費外掛程式；包含Get & Transform在 Excel 2016+ 中

除了輸入自訂列公式之外，一切都可以透過 GUI 完成，如果原始資料發生變化，查詢也可以輕鬆刷新。

• 選取原始表格中的一個儲存格
• Data--> Get & Transform --> from Table/Range
• 透過...分組App ref 操作 == 所有行

• 新增自訂列（將該Age列變更為List
  • 公式=Table.Column([Merged],"Age")
• comma右鍵單擊此新自訂列的雙箭頭，然後選擇使用作為分隔符號提取值
• comma使用分隔符號拆分列。
• 將新列重新命名為Age netc
• 關閉並加載結果

M程式碼

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"