En Excel, ¿cómo puedo obtener una lista dinámica de los distintos valores de la misma columna en 3 tablas?

Question

Tengo 3 tablas en el mismo libro de Excel y todas usan los mismos encabezados de columna.

Quiero tener una columna en una cuarta tabla que puedadinamicamenteactualice con todos los valores distintos que se encuentran en la misma columna en las 3 tablas (por ejemplo, Tabla01[MyCol], Table02[MyCol], Table03[MyCol]).

Una solución VBA está bien; Simplemente no sé por dónde empezar.

Answer 1

Una solución VBA está bien; Simplemente no sé por dónde empezar.

si quieres algo'que puede actualizarse dinámicamente', probablemente esté buscando una solución basada en un "evento". Hay eventos desencadenados por actualizaciones de otras fuentes en las tablas, así como por cualquier cambio en cualquier hoja de trabajo.

Si todas sus tablas están en la misma hoja de trabajo, debería poder salirse con la suya con un activador de evento en la hoja de trabajo; si están en hojas de trabajo diferentes, entonces necesita un activador de evento del libro de trabajo.

Cada vez que escucho los términos'valores distintos'y'VBA'En el mismo párrafo, inmediatamente empiezo a pensar en un diccionario de secuencias de comandos VBA. el diccionariollavesson únicos (opcionalmente distinguen entre mayúsculas y minúsculas o no).

Dado que la columna en cuestión es'la misma columna en las 3 tablas', probablemente deberías usar una variable constante pública o privada para determinar su nombre. Si el nombre de las columnas alguna vez cambiara, solo necesita cambiarlo en un lugar.

Tratar con tablas estructuradas en VBA puede resultar complicado. Los diferentes métodos tienen diferentes pros y contras. He descubierto que el método con el menor número de desventajas es Range("Table01").ListObjectel de una tabla ListObject, especialmente cuando se trata de tablas de un libro de trabajo y no solo de una sola hoja de trabajo.

No recorra la columna de cada tabla celda por celda. Lea la columna de cada tabla en una matriz variante y recorra la matriz. Es más rápido y requiere menos cálculos.

Aquí hay un código que deriva una lista única de valores de tres tablas en dos hojas de trabajo a una cuarta tabla en una tercera hoja de trabajo. pertenece en elEste libro de trabajohoja de código. Muy comentado para su beneficio.

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

Estoy dispuesto a responder cualquier pregunta para la que ya hayas investigado y no puedas encontrar respuesta.

Answer 2

En lugar del código VBA, me gustaría sugerir una fórmula de matriz (CSE) que creará una lista única en otra columna.

Cómo funciona:

• Cree tres tablas y asígneles el nombre NameTBL, NameTBL1& NameTBL2.

• Fórmula en la celda 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))), "")}
  

• Termine la fórmula conCtrl+Mayús+EntrarY rellénelo (hasta algunas filas adicionales).

---

• Se han agregado nuevos datos a la Tabla 1

---

• Nuevos datos agregados en la Tabla 3.

NÓTESE BIEN

• Dado que Excel actualiza automáticamente las tablas relacionadas tan pronto como obtiene nuevos datos, la fórmula Column Jincluye dinámicamente un nuevo valor.

• Puede convertir un valor único en column Juna tabla.

Answer 3

Un buen punto de partida que encontré es enhttps://www.ablebits.com/office-addins-blog/2016/04/21/get-list-unique-values-excel/, que incluye variaciones para distinguir entre mayúsculas y minúsculas e ignorar números y espacios en blanco.

En lugar de VBA o una fórmula Array/CSE, prefiero usar esta fórmula normal. Tenga en cuenta que contiene una referencia relativa a la celda directamente arriba; por ejemplo, la fórmula en la celda D5 se refiere al rango desde TableHeader hasta D4, en lugar de D1 como se muestra a continuación.

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

Esta fórmula primero se utiliza COUNTIFpara convertir la lista de valores de origen {A,B,B,C} en una lista de unos y ceros basada en los valores que ya se encuentran en la lista distinta. Si los valores "A" y "B" ya están en la lista distinta pero "C" no, en este ejemplo convertiría {A,B,B,C} en {1,1,1,0}

En segundo lugar, utiliza MATCHla matriz binaria para encontrar el primer valor "0", que es la posición de una celda de la lista de origen con un valor que aún no está contenido en la lista distinta. Es decir, la posición 4 para el valor "C" anterior.

En tercer lugar, se utiliza INDEXpara obtener el valor asociado con la posición encontrada por MATCH, en nuestro ejemplo devuelve "C".

Por último, suele IFERRORdevolver """(un valor de celda en blanco) cuando no se han encontrado más coincidencias.

Answer 4

Si su versión de Excel tiene la función Matrices dinámicas, con la UNIQUEfunción puede usar:

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

algoritmo

• Une todo el texto usando TEXTJOINla función para crear una lista separada por comas(o use algún otro delimitador si se incluyen comas en sus cadenas)

   TEXTJOIN(",",TRUE,Table01[MyCol],Table02[MyCol],Table03[MyCol])`
  

• Cree un XML en el que cada nodo sea uno de los elementos separados por comas
• Úselo FILTERXMLpara devolver los nodos como una matriz.
• Utilice la UNIQUEfunción para devolver solo las entradas únicas.