☰

Excel에서 3개 테이블의 동일한 열에 있는 고유 값의 동적 목록을 어떻게 얻을 수 있습니까?

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
Excel에서 3개 테이블의 동일한 열에 있는 고유 값의 동적 목록을 어떻게 얻을 수 있습니까?

동일한 Excel 통합 문서에 3개의 테이블이 있는데 모두 동일한 열 머리글을 사용합니다.

4번째 테이블에 열을 갖고 싶습니다.동적으로3개의 테이블 전체에서 동일한 열에 있는 모든 고유 값으로 업데이트합니다(예: Table01[MyCol], Table02[MyCol], Table03[MyCol]).

VBA 솔루션은 괜찮습니다. 어디서부터 시작해야할지 모르겠습니다.

답변1

VBA 솔루션은 괜찮습니다. 어디서부터 시작해야할지 모르겠습니다.

당신이 뭔가를 원한다면'동적으로 업데이트할 수 있는', 아마도 '이벤트'를 기반으로 한 솔루션을 찾고 있을 것입니다. 다른 소스에서 테이블을 업데이트하거나 워크시트를 변경하면 이벤트가 트리거됩니다.

테이블이 모두 동일한 워크시트에 있는 경우 워크시트 이벤트 트리거를 사용하여 작업을 피할 수 있습니다. 서로 다른 워크시트에 있는 경우 통합 문서 이벤트 트리거가 필요합니다.

용어를 들을 때마다'독특한 가치'그리고'VBA'같은 단락에서 저는 즉시 VBA 스크립팅 사전에 대해 생각하기 시작했습니다. 사전의열쇠고유합니다(선택적으로 대소문자를 구분할지 여부).

문제의 열은 다음과 같으므로'3개 테이블 모두에서 동일한 열', 이름을 결정하려면 공개 또는 비공개 상수 변수를 사용해야 합니다. 열 이름을 변경하려면 한 곳에서만 변경하면 됩니다.

VBA에서 구조화된 테이블을 처리하는 것은 어려울 수 있습니다. 다양한 방법에는 서로 다른 장단점이 있습니다. Range("Table01").ListObject특히 단일 워크시트의 테이블이 아닌 통합 문서의 테이블을 처리할 때 단점이 가장 적은 ListObject 테이블을 사용하는 방법을 찾았습니다 .

각 테이블의 열을 셀별로 반복하지 마세요. 각 테이블의 열을 변형 배열로 읽고 배열을 반복합니다. 더 빠르고 계산 집약도가 낮습니다.

다음은 두 워크시트의 세 테이블에서 세 번째 워크시트의 네 번째 테이블로 고유한 값 목록을 파생시키는 일부 코드입니다. 그것은에 속한다이워크북코드 시트. 귀하의 이익을 위해 많은 의견을 제시했습니다.

Option Explicit

'declare the common column's name available to all sub procedures within this code sheet
Private Const col As String = "col2"

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    'the Intersect function sees if you have changed anything within the tables
    'you cannot Intersect across worksheets so you need to know what worksheet contains which table
    'in this case, Table01 and Table02 are on Sheet1, Table03 is on Sheet2
    'note the use of the LCase function
    Select Case LCase(Sh.Name)
        Case "sheet1"
            'did the change event (add/update/remove) occur on Sheet1.Table01 or Sheet1.Table02
            If Not Intersect(Target, Range("Table01").ListObject.ListColumns(col).DataBodyRange, _
                                     Range("Table02").ListObject.ListColumns(col).DataBodyRange) Is Nothing Then
                'set error control
                On Error GoTo byebye
                'disable events so this doesn't run on top of itself when Table04 is updated
                Application.EnableEvents = False
                'run the Table04 update procedure
                UpDate_Table04
            End If
        Case "sheet2"
            'did the change event (add/update/remove) occur on Sheet2.Table03
            If Not Intersect(Target, Range("Table03").ListObject.ListColumns(col).DataBodyRange) Is Nothing Then
                'see above
                On Error GoTo byebye
                'see above
                Application.EnableEvents = False
                'see above
                UpDate_Table04
            End If
        Case Else
            'do nothing (placeholder for other considerations)
    End Select

'error control 'catcher'
byebye:
    'reenable event handling for future operations
    Application.EnableEvents = True

End Sub

Private Sub UpDate_Table04()

    'declare variables
    Dim t As Long, v As Long, tbls As Variant, vals As Variant
    'declare a reusable variable for the dictionary
    Static d As Object

    'assign the source table names to a variant array
    tbls = Array("Table01", "Table02", "Table03")

    'determine if the dictionary has been created
    'if so remove all previous entries; if not create one and make it non-case-sensitive
    If d Is Nothing Then
        'dictionary does not exist; create one and make it non-case-sensitive
        Set d = CreateObject("scripting.dictionary")
        d.CompareMode = vbTextCompare
    Else
        'dictionary exists; remove all previous entries
        d.RemoveAll
    End If

    'loop through the three source tables
    For t = LBound(tbls) To UBound(tbls)

        'retrieve the values from this table's common column
        vals = Application.Transpose(Range(tbls(t)).ListObject.ListColumns(col).DataBodyRange.Value2)

        'loop through the values and create create unique dictionary keys using the faster Add/Overwrite method
        'this method does not require checking for identical previous additions
        For v = LBound(vals) To UBound(vals)
            'Add/Overwrite method
            d(vals(v)) = vbNullString
        Next v

    Next t

    'work with the destination table
    'this reference method does not require worksheet reference within ThisWorkbook
    With Range("Table04").ListObject
        'make room/remove rows on the destination table
        .Resize .HeaderRowRange.Cells(1).Resize(d.Count + 1, .ListColumns.Count)
        'put the dictionary keys into the destination table
        .ListColumns(col).DataBodyRange = Application.Transpose(d.keys)
    End With


End Sub

이미 조사했지만 답변을 찾을 수 없는 질문에 기꺼이 답변해 드리겠습니다.

답변2

VBA 코드 대신 배열(CSE) 수식을 제안하고 싶습니다. 다른 열에 고유한 목록이 생성됩니다.

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

작동 방식:

  • 세 개의 테이블을 만들고 이름을 NameTBL, NameTBL1& 로 지정합니다 NameTBL2.

  • 셀의 수식 J36:

    {=IFERROR(IFERROR (IFERROR(INDEX(NameTBL[City1],MATCH(0, COUNTIF($J$35:J35, NameTBL[City1])+(NameTBL[City1]=""), 0)), INDEX(NameTBL1[City2], MATCH(0, COUNTIF($J$35:J35, NameTBL1[City2])+(NameTBL1[City2]=""), 0))), INDEX(NameTBL2[City3], MATCH(0, COUNTIF($J$35:J35, NameTBL2[City3])+(NameTBL2[City3]=""), 0))), "")}

  • 수식 마무리Ctrl+Shift+Enter& 채워 넣으세요(추가 행이 몇 개 남을 때까지).

  • 표 1에 새로운 데이터가 추가되었습니다.

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

  • 표 3에 새로운 데이터가 추가되었습니다.

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

주의

  • Excel에서는 새 데이터를 얻는 즉시 관련 테이블을 자동으로 업데이트하므로 의 수식에는 Column J새 값이 동적으로 포함됩니다.

  • column J고유한 값을 테이블로 변환할 수 있습니다 .

답변3

내가 찾은 좋은 출발점은https://www.ablebits.com/office-addins-blog/2016/04/21/get-list-unique-values-excel/, 여기에는 대소문자를 구분하고 숫자와 공백을 무시하는 변형이 포함됩니다.

VBA나 Array/CSE 수식보다는 이 정규 수식을 사용하는 것이 더 좋습니다. 여기에는 바로 위의 셀에 대한 상대 참조가 포함되어 있습니다. 예를 들어 셀 D5의 수식은 아래 표시된 것처럼 D1이 아니라 TableHeader에서 D4까지의 범위를 나타냅니다.

=IFERROR( INDEX( Table1[MyCol], MATCH(0, INDEX( COUNTIF(TableDistinct[[#Headers],[DistinctVals]]:D1,Table1[MyCol]),0,0),0)),
                 IFERROR( INDEX( Table2[MyCol], MATCH(0, INDEX( COUNTIF(TableDistinct[[#Headers],[DistinctVals]]:D1,Table2[MyCol]),0,0),0)),
                          IFERROR( INDEX( Table3[MyCol], MATCH(0,INDEX(COUNTIF(TableDistinct[[#Headers],[DistinctVals]]:D1,Table3[MyCol]),0,0),0)),
                                   "")
                 )
)

세 테이블에서 고유한 값을 반환하는 비 CSE 및 비 VBA 수식

이 공식은 먼저 COUNTIF소스 값 {A,B,B,C} 목록을 고유 목록에서 이미 발견된 값을 기반으로 1과 0 목록으로 변환하는 데 사용됩니다. "A" 및 "B" 값이 이미 고유 목록에 있지만 "C"는 그렇지 않은 경우 이 예에서는 {A,B,B,C}를 {1,1,1,0}로 변환합니다.

둘째, MATCH이진 배열을 사용하여 첫 번째 "0" 값을 찾습니다. 이는 고유 목록에 아직 포함되지 않은 값이 있는 소스 목록 셀의 위치입니다. 즉, 위의 "C" 값은 위치 4입니다.

INDEX셋째, 에서 찾은 위치와 관련된 값을 가져오는 데 사용됩니다 MATCH. 이 예에서는 "C"를 반환합니다.

마지막으로, 일치하는 항목이 더 이상 발견되지 않으면 (빈 셀 값)을 IFERROR반환하는 데 사용됩니다 ."""

답변4

사용 중인 Excel 버전에 동적 배열 기능이 있는 경우 해당 UNIQUE기능을 사용하여 다음을 사용할 수 있습니다.

=UNIQUE(FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN(",",TRUE,Table01[MyCol],Table02[MyCol],Table03[MyCol]),",","</s><s>")& "</s></t>","//s"))

알고리즘

  • TEXTJOIN쉼표로 구분된 목록을 만드는 기능을 사용하여 모든 텍스트를 함께 결합하세요.(또는 문자열에 쉼표가 포함된 경우 다른 구분 기호를 사용하세요)

     TEXTJOIN(",",TRUE,Table01[MyCol],Table02[MyCol],Table03[MyCol])`
  • 각 노드가 쉼표로 구분된 항목 중 하나인 XML을 만듭니다.
  • FILTERXML노드를 배열로 반환하는 데 사용합니다 .
  • UNIQUE고유한 항목만 반환하려면 이 함수를 사용하세요 .

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

관련 정보