No Excel, como posso obter uma lista dinâmica dos valores distintos da mesma coluna em 3 tabelas?

Question

Tenho três tabelas na mesma pasta de trabalho do Excel, todas usando os mesmos cabeçalhos de coluna.

Quero ter uma coluna em uma quarta tabela que possadinamicamenteatualize com todos os valores distintos encontrados na mesma coluna em todas as 3 tabelas (por exemplo, Table01[MyCol], Table02[MyCol], Table03[MyCol]).

Uma solução VBA é adequada; Só não sei por onde começar.

Answer 1

Uma solução VBA é adequada; Só não sei por onde começar.

Se você quer alguma coisa'que pode atualizar dinamicamente', você provavelmente está procurando uma solução baseada em um 'evento'. Existem eventos acionados por atualizações de outras fontes em tabelas, bem como por qualquer alteração em qualquer planilha.

Se todas as suas tabelas estiverem na mesma planilha, você poderá usar um gatilho de evento de planilha; se eles estiverem em planilhas diferentes, você precisará de um gatilho de evento de pasta de trabalho.

Toda vez que ouço os termos'valores distintos'e'VBA'no mesmo parágrafo, começo imediatamente a pensar em um Dicionário de Scripting VBA. O dicionáriochavessão exclusivos (opcionalmente diferenciam maiúsculas de minúsculas ou não).

Como a coluna em questão é'a mesma coluna em todas as 3 tabelas', você provavelmente deve usar uma variável constante pública ou privada para determinar seu nome. Se o nome das colunas mudar, você só precisará alterá-lo em um lugar.

Lidar com tabelas estruturadas em VBA pode ser uma tarefa difícil. Métodos diferentes têm prós e contras diferentes. Descobri que o método com o menor número de contras é Range("Table01").ListObjectuma tabela ListObject, principalmente ao lidar com tabelas de uma pasta de trabalho e não apenas com tabelas de uma única planilha.

Não percorra cada coluna da tabela, célula por célula. Leia a coluna de cada tabela em uma matriz variante e percorra a matriz. É mais rápido e menos intensivo em cálculos.

Aqui está um código que deriva uma lista exclusiva de valores de três tabelas em duas planilhas em uma quarta tabela em uma terceira planilha. Pertence aoEsta pasta de trabalhofolha de código. Fortemente comentado para seu benefício.

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

Estou disposto a responder a quaisquer perguntas que você já tenha pesquisado e não consiga encontrar uma resposta.

Answer 2

Em vez do código VBA, gostaria de sugerir uma fórmula Array (CSE), que criará uma lista exclusiva em outra coluna.

Como funciona:

• Crie três tabelas e nomeie-as como NameTBL, NameTBL1& NameTBL2.

• Fórmula na célula 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))), "")}
  

• Finalize a fórmula comCtrl+Shift+Entere preencha (até algumas linhas extras).

---

• Novos dados foram adicionados à Tabela 1

---

• Novos dados adicionados na Tabela 3.

Observação

• Como o Excel atualiza automaticamente as tabelas relacionadas assim que obtém novos dados, a fórmula Column Jinclui dinamicamente o novo valor.

• Você pode converter um valor único em column Juma tabela.

Answer 3

Um bom ponto de partida que encontrei está emhttps://www.ablebits.com/office-addins-blog/2016/04/21/get-list-unique-values-excel/, que inclui variações por diferenciar maiúsculas de minúsculas e ignorar números e espaços em branco.

Em vez de VBA ou uma fórmula Array/CSE, prefiro usar esta fórmula regular. Observe que ele contém uma referência relativa à célula diretamente acima, por exemplo, a fórmula na célula D5 refere-se ao intervalo de TableHeader a D4, em vez de D1, conforme mostrado abaixo.

=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 é usada primeiro COUNTIFpara converter a lista de valores de origem {A,B,B,C} em uma lista de 1 e 0 com base nos valores já encontrados na lista distinta. Se os valores "A" e "B" já estiverem na lista distinta, mas "C" não, então neste exemplo ele converteria {A,B,B,C} em {1,1,1,0}

Em segundo lugar, ele usa MATCHa matriz binária para encontrar o primeiro valor "0", que é a posição de uma célula da lista de origem com um valor ainda não contido na lista distinta. Ou seja, posição 4 para o valor “C” acima.

Terceiro, ele usa INDEXpara obter o valor associado à posição encontrada por MATCH, em nosso exemplo ele retorna “C”.

Por último, ele IFERRORretorna """(um valor de célula em branco) quando nenhuma outra correspondência for encontrada.

Answer 4

Se a sua versão do Excel possui o recurso Dynamic Arrays, com a UNIQUEfunção você pode usar:

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

Algoritmo

• Junte todo o texto usando TEXTJOINa função para criar uma lista separada por vírgulas(ou use algum outro delimitador se vírgulas estiverem incluídas em suas strings)

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

• Crie um XML com cada nó sendo um dos itens separados por vírgula
• Use FILTERXMLpara retornar os nós como uma matriz.
• Use a UNIQUEfunção para retornar apenas as entradas exclusivas.