☰

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つのテーブルすべてで同じ列」、名前を決定するには、パブリックまたはプライベートの定数変数を使用することをお勧めします。列の名前を変更する場合は、1 か所を変更するだけで済みます。

VBA で構造化されたテーブルを扱うのは面倒な作業です。方法によって長所と短所が異なります。短所が最も少ない方法は、Range("Table01").ListObjectListObject テーブルを使用することです。これは、単一のワークシートのテーブルだけでなく、ワークブックのテーブルを扱う場合に特に当てはまります。

各テーブルの列をセルごとにループしないでください。各テーブルの列をバリアント配列に読み込み、その配列をループします。これにより、処理が高速化され、計算量が少なくなります。

これは、2つのワークシートの3つのテーブルから3番目のワークシートの4番目のテーブルに一意の値のリストを取得するコードです。このワークブックコードシート。あなたの利益のために多くのコメントが付けられています。

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) 数式をお勧めします。

ここに画像の説明を入力してください

使い方：

  • 3 つのテーブルを作成し、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 や配列/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)),
                                   "")
                 )
)

3 つのテーブルから異なる値を返す非 CSE および非 VBA 式

この数式は、まず、COUNTIFソース値のリスト {A、B、B、C} を、個別のリストにすでに存在する値に基づいて 1 と 0 のリストに変換します。「A」と「B」の値が個別のリストにすでに存在するが、「C」の値は存在しない場合、この例では、{A、B、B、C} が {1、1、1、0} に変換されます。

次に、MATCHバイナリ配列を使用して最初の「0」値を検索します。これは、個別のリストにまだ含まれていない値を持つソース リスト セルの位置です。つまり、上記の「C」値の位置は 4 です。

3 番目に、 を使用して、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])`
  • 各ノードがカンマ区切りの項目の1つであるXMLを作成します。
  • FILTERXMLノードを配列として返すために使用します。
  • 関数を使用してUNIQUE、一意のエントリのみを返します。

ここに画像の説明を入力してください

関連情報