Введение

Привет, переполнение стека. Я надеюсь получить ваш опыт по проблеме, с которой я иногда сталкиваюсь в Microsoft Excel. Пожалуйста, ознакомьтесь с контекстом и информацией, которые я представил вместе с моим вопросом ниже.

Пожалуйста, дайте мне знать, могу ли я уточнить, что я ищу, и / или могу ли я предоставить какие-либо дополнительные сведения.

Спасибо, как всегда! Это сообщество всегда является таким большим преимуществом.

Фон / контекст

Преобразование таблиц так, чтобы они содержали значения, хранящиеся в другой таблице

Мне часто приходится преобразовывать значения исходной таблицы (назовем их значениями «A») в соответствующее значение, хранящееся в другой таблице (значения «B»).

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

Файлы, содержащие несколько значений в отдельных ячейках

В последнее время мне приходится преобразовывать несколько значений «A» (разделенных точками с запятой), хранящихся вместе в отдельных ячейках. Примечание: в сценариях «реального мира» обычно имеется около 200-300 строк и около 80 уникальных значений «A», поэтому обновление вручную не очень практично. Я попытался привести общую иллюстрацию ниже.

Иллюстрация

Для иллюстрации предположим, что каждое из отдельных значений в таблице 1 должно быть преобразовано в значения в соответствии с переходом, хранящимся в таблице 2.

Таблица 1: Исходные значения «A»

Таблица 2: Переход: от значений «A» к значениям «B»

Таблица 3: идеальный конечный результат (значения A, преобразованные в значения B)

Анализ

Учитывая, что значения «A» хранятся вместе в таблице 1, такие методы, как поиск / замена или VLOOKUP в Excel, не сразу доступны для извлечения соответствующих значений «B».

У меня есть обходной путь, при котором я использую Excel «Text to Columns» для разделения строк таблицы 1 на отдельные ячейки, а затем использую VLOOKUP для извлечения значения «B», а затем использую функцию конкатенации, чтобы снова соединить строки вместе, но это хлопотно и подверженоиз-за человеческой ошибки.

Я повсюду искал ответы на эту тему, и мне еще не повезло найти кого-то, кто работает над той же проблемой.

Вопрос

Могу ли я каким-то образом автоматизировать весь или часть этого обременительного процесса с помощью чего-то вроде VBA для автоматической замены всех значений «A» в таблице 1 на соответствующие значения «B»? Я открыт для любых / всех потенциальных решений. Прямо сейчас я даже не уверен, с чего начать, или вообще разрешима ли моя проблема для начала.

Еще раз спасибо!

Вы определенно могли бы сделать что-то подобное с помощью VBA. Хотя, если вы еще не программист, я бы порекомендовал вам создать электронную таблицу с 4 вкладками:

1. На первой вкладке есть исходные выходные данные (для справки и проверки)
2. На второй вкладке снова вставьте данные и используйте текст для столбцов
3. На третьей вкладке уже настроены vlookups
4. На четвертой вкладке поместите свою таблицу vlookup

Теперь это становится двухэтапным процессом: вставьте данные, затем добавьте текст в столбцы. Надеюсь, это уменьшит человеческую ошибку

Опасности, на которые следует обратить внимание:

1. Если у вас тысячи строк данных, вычисления могут быть слишком обременительными для вашего ноутбука. В этом случае установите вычисления вручную (см. Здесь https://www.ablebits.com/office-addins-blog/2017/06/29/excel-calculations-automatic-manual-iterative / ). Вам нужно будет нажать «Рассчитать сейчас», когда данные будут готовы.
2. Текст в столбцах может перемещать ссылки на ячейки, что приводит к ошибкам #REF на вкладке формулы. Если это произойдет, то выполните текст в столбцах на первой вкладке и скопируйте во вторую

Замените несколько значений ячеек в таблицах с помощью словаря

 Option Explicit

Sub replaceValues()
    
    ' Define constants.
    Const dstName As String = "Sheet1"
    Const dstTbl As String = "Table1"
    Const dstCol As String = "A Values"
    Const Delimiter As String = "; "
    Const srcName As String = "Sheet2"
    Const srcTbl As String = "Table2"
    Const srcKey As String = "A Value"
    Const srcVal As String = "B Value"
    
    ' Define workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    ' Write values from Source Column Ranges to Source Arrays.
    Dim sKey As Variant ' Source Key Array
    Dim sVal As Variant ' Source Value Array
    With wb.Worksheets(srcName).ListObjects(srcTbl)
        sKey = .ListColumns(srcKey).DataBodyRange.Value
        sVal = .ListColumns(srcVal).DataBodyRange.Value
    End With
    
    ' Write values from Source Arrays to Unique Dictionary.
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim i As Long ' Arrays Rows Counter
    For i = 1 To UBound(sKey)
        dict(sKey(i, 1)) = sVal(i, 1)
    Next i
    Erase sKey
    Erase sVal
    
    ' Define Destination Column Range.
    Dim rng As Range
    With wb.Worksheets(dstName).ListObjects(dstTbl)
        Set rng = .ListColumns(dstCol).DataBodyRange
    End With
    
    ' Write values from Destination Column Range to Data Array.
    Dim Data As Variant: Data = rng.Value
    
    ' Declare additional variables.
    Dim cSplit() As String ' Current Split Array
    Dim n As Long ' Current Split Array Element Counter
    Dim cString As String ' Current String (in Split Array)
    
    ' Modify (replace) values in Data Array (using Unique Dictionary).
    For i = 1 To UBound(Data, 1)
        cSplit = Split(Data(i, 1), Delimiter)
        For n = 0 To UBound(cSplit)
            cString = cSplit(n)
            If dict.Exists(cString) Then
                cSplit(n) = dict(cString)
            End If
        Next n
        Data(i, 1) = Join(cSplit, Delimiter)
    Next i
    
    ' Write values from Data Array to Destination Column Range.
    rng.Value = Data
   
End Sub