☰

여러 결과 값이 있는 LOOKUP 참조

tag-icon • tag-icon microsoft-excel lookup
여러 결과 값이 있는 LOOKUP 참조

단일 참조 번호와 관련된 여러 연령층의 사람들을 뽑아서 표시하려고 합니다.

아래에 잘라낸 시트 설정이 표시됩니다. 시트에는 대략 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   |  -    |

답변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))), "")

답변2

당신은 이것을 쉽게 할 수 있습니다Power Query Excel 2010+에는 Microsoft의 무료 추가 기능이 있습니다. Get & TransformExcel 2016+에 포함됨

사용자 정의 열 수식 입력을 제외한 모든 작업은 GUI에서 수행할 수 있으며, 원본 데이터가 변경되면 쿼리가 쉽게 새로 고쳐집니다.

  • 원본 테이블에서 셀을 선택하세요.
  • Data--> Get & Transform --> from Table/Range
  • 그룹화 기준App ref 작업 == 모든 행

여기에 이미지 설명을 입력하세요

  • 사용자 정의 열 추가(열 Age을List
    • 공식=Table.Column([Merged],"Age")
  • comma이 새로운 사용자 정의 열의 이중 화살표를 마우스 오른쪽 버튼으로 클릭하고 구분 기호로 사용하여 값 추출을 선택합니다.
  • comma구분 기호로 사용하여 열을 분할합니다 .
  • 새 열의 이름을 Age n등 으로 바꿉니다.
  • 결과를 닫고 로드합니다.

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"

여기에 이미지 설명을 입력하세요

여기에 이미지 설명을 입력하세요

관련 정보