Как в Excel получить динамический список различных значений из одного столбца в трех таблицах?

Question

У меня есть три таблицы в одной книге Excel, и все они используют одинаковые заголовки столбцов.

Я хочу иметь столбец в четвертой таблице, который можетдинамическиобновить, используя все отдельные значения, которые находятся в одном и том же столбце во всех 3 таблицах (например, Table01[MyCol], Table02[MyCol], Table03[MyCol]).

Решение на VBA вполне приемлемо, просто не знаю, с чего начать.

Answer 1

Решение на VBA вполне приемлемо, просто не знаю, с чего начать.

Если вы хотите что-то«который может динамически обновляться», вы, вероятно, ищете решение, основанное на 'событии'. Существуют события, вызванные обновлениями из других источников в таблицах, а также любым изменением в любом рабочем листе.

Если все ваши таблицы находятся на одном листе, вам вполне хватит триггера событий листа; если они находятся на разных листах, вам понадобится триггер событий книги.

Каждый раз, когда я слышу термины«отдельные ценности»и'ВБА'в том же абзаце я сразу начинаю думать о словаре сценариев VBA. Словарьключиявляются уникальными (по желанию с учетом регистра или без него).

Поскольку рассматриваемый столбец«один и тот же столбец во всех трех таблицах», вам, вероятно, следует использовать публичную или частную константную переменную для определения ее имени. Если имя столбцов когда-либо должно было измениться, вам нужно было изменить его только в одном месте.

Работа со структурированными таблицами в 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

Я готов ответить на любые вопросы, которые вы уже исследовали, но не нашли ответа.

Answer 2

Вместо кода VBA я хотел бы предложить формулу Array(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таблицу.

Answer 3

Хорошей отправной точкой я считаюhttps://www.ablebits.com/office-addins-blog/2016/04/21/get-list-unique-values-excel/, который включает в себя варианты с учетом регистра и игнорированием цифр и пробелов.

Вместо формулы VBA или Array/CSE я бы предпочел использовать эту обычную формулу. Обратите внимание, что она содержит относительную ссылку на ячейку непосредственно выше, например, формула в ячейке D5 ссылается на диапазон от TableHeader до D4, а не на D1, как показано ниже.

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

Эта формула сначала использует COUNTIFдля преобразования списка исходных значений {A,B,B,C} в список 1 и 0 на основе значений, уже найденных в списке уникальных значений. Если значения "A" и "B" уже есть в списке уникальных значений, а "C" нет, то в этом примере она преобразует {A,B,B,C} в {1,1,1,0}

Во-вторых, он использует MATCHдвоичный массив для поиска первого значения "0", которое является позицией ячейки исходного списка со значением, еще не содержащимся в отдельном списке. То есть, позиция 4 для значения "C" выше.

В-третьих, он используется INDEXдля получения значения, связанного с позицией, найденной с помощью MATCH, в нашем примере он возвращает «C».

Наконец, он используется IFERRORдля возврата """(значения пустой ячейки), когда больше не найдено совпадений.

Answer 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функцию для возврата только уникальных записей.