Estoy buscando sacar y mostrar las múltiples edades de las personas asociadas con un único número de referencia.
A continuación, recortada, se muestra la configuración de la hoja. La hoja tiene aproximadamente 2500 filas:
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
Para cada uno App Ref
, quiero extraer todas las edades asociadas:
| App ref | Age 1 | Age 2 | Age 3 |
|---------|-------|-------|-------|
| 1 | 34 | 34 | 30 |
| 830 | 63 | 32 | 65 |
| 846 | 75 | 72 | - |
Respuesta1
- Copie la columna A en una columna no utilizada donde comenzarán sus datos de destino y usarán datos, elimine duplicados para generar un conjunto de datos únicos.Referencia de la aplicaciónnúmeros. En mi imagen de muestra a continuación, utilicé la columna H.
Coloque esta fórmula en la celda de la fila 1 inmediatamente a la derecha de su únicoReferencia de la aplicaciónnúmeros y arrástrelo hacia la derecha. En mi muestra, comencé en I1.
=TEXT(COLUMN(A:A), "\A\g\e 0")
Pon esta fórmula debajoEdad 1y arrastre hacia la derecha y hacia abajo. En mi ejemplo, comencé en 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))), "")
Si elReferencia de la aplicaciónLa columna está ordenada de forma ascendente, entonces puede reducir el cálculo limitando las filas de búsqueda.
=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))), "")
Respuesta2
Puedes hacer esto fácilmente conPower Query
un complemento gratuito de Microsoft en Excel 2010+; incluido como Get & Transform
en Excel 2016+
Excepto ingresar la fórmula de la columna personalizada, todo se puede hacer desde la GUI y, si los datos originales cambian, la consulta se actualiza fácilmente.
- Seleccione una celda en su tabla original
Data--> Get & Transform --> from Table/Range
- Agrupar por
App ref
operación == Todas las filas
- Agregar columna personalizada (para cambiar la
Age
columna a unaList
- Fórmula
=Table.Column([Merged],"Age")
- Fórmula
- Haga clic derecho en la flecha doble de esta nueva columna Personalizada y elija Extraer los valores utilizándolos
comma
como separador. - Divida la columna usando
comma
como separador. - Cambie el nombre de las nuevas columnas a
Age n
etc. - Cerrar y cargar los resultados
Código 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"