Несколько последовательных замен с использованием параметров в других столбцах

Question

На листе Excel:

Столбец А заполнен случайными текстовыми значениями (например, словами / предложениями), которые необходимо обновить.

И столбцы B и C содержат строки, которые определяют изменения, которые необходимо внести в столбец A. Столбец B заполняет каждую ячейку старым текстом, а столбец C - новым текстом, каждая строка соответствует друг другу, то есть текст в B1 необходимо заменить текстом C1, B2 заменить на текст C2. В столбце B одна из вещей, которую нужно заменить, - это " .

В столбце B нет повторяющихся значений. Однако в столбце C клетки могут повторяться. Замены должны соответствовать порядку, в котором они были написаны. И столбец D является результатом.

Каждая ячейка из столбца A должна проходить через эту замену из B1-C1, B2-C2, B3-C3 и т.д. До конца команд замещения в столбцах BC. Таким образом, перед тем, как дать окончательный результат, текст из столбца A будет меняться несколько раз, и только после всех изменений будет показывать результат в столбце D.

---

Пример:

Cell A1: Hello!
Cell A2: How are you "John"?
Cell A3: "Nice! thanks"

Cell B1: !                           Cell C1: &
Cell B2: &                           Cell C2: .
Cell B3: "                           Cell C3:    (empty)
Cell B4: ?                           Cell C4: #
Cell B5: Nice. thanks                Cell C5: Fine, THANKS.

---

Итак, Cell A1, который является Hello! , будет изменено B1-C1, поэтому будет Hello& . Затем изменится B2-C2 с изменениями, внесенными во временный результат Hello& будет Hello. ,  Затем изменится B3-C3 и затем выполнит B4-C4, а затем B5-C5, и ничего не произойдет, потому что " не существует в Hello. ..

После этого результат D1 будет Hello. ,

Затем он выполнит изменения A2 B1-C1 (ничего не делать), затем применится к этому будет делать B2-C2 (ничего не делать), затем B3-C3, что приведет к « How are you John? (т. е. символы кавычек будут удалены), затем произойдет B4-C4, который выдаст « How are you John# , а затем B5-C5, который здесь делать нечего.

Итак, результат D2 - How are you John# .

Точно так же, значение А3 "Nice! thanks" сначала изменится с B1-C1, превратившись в "Nice& thanks" , затем B2-C2 станет "Nice. thanks" , затем B3-C3 становится Nice. thanks (опять же, кавычки будут удалены), тогда ничего не делать в B4-C4, а затем B5-C5, наконец , превратится в Fine, THANKS в D3.

---

Результаты:

D1: Hello.
D2: How are you John#
D3: Fine, THANKS.

---

Какова будет формула в столбце D для выполнения этой множественной замены в строке, подчиняющейся порядку строк, записанному в столбцах B и C, что позволяет столбцу BC иметь более 300 строк? (Я хотел бы получить решение на основе формул, а не VBA, если это возможно.)

Answer 1

Я надеялся на умный способ сделать это, используя формулу с одним массивом, но не смог найти ее (я бы хотел оказаться ошибочным!) Я думаю, что единственный способ сделать это с одной формулой на строку ввода - это SUBSTITUTE вложенные функции в соответствии с ответом @ G-Man. К сожалению, эта стратегия не будет работать для 300 замен, поскольку в Excel есть ограничение , заключающееся в том, что функции могут вложить только 64 уровня (или 7 до Excel 2007).

Поэтому я думаю, что вам понадобятся вспомогательные столбцы (которые вы всегда можете скрыть, если это поможет с презентацией). Приведенный ниже метод использует более простые формулы, чем у G-Man. Он использует несколько вспомогательных ячеек, чем его: один столбец на входную строку × одна строка на замену плюс один:

• Начиная с листа, выложенного в соответствии с вашим примером, вставьте пустую строку в верхней части страницы. Выберите ячейки F1:H1 , введите формулу =TRANSPOSE(A2:A4) и нажмите Ctrl+Shift+Enter , чтобы она была введена как формула массива. Вы должны увидеть фигурные скобки вокруг формулы, а ячейки должны заполниться вашими входными строками.

• В ячейку F2 введите формулу =SUBSTITUTE(F1,$B2,$C2) . Скопируйте эту формулу поперек и вниз, чтобы заполнить все F2:H6 . Ваш желаемый результат появится в F6:H6 .

• Чтобы вернуть выходные данные в столбец D , выберите D2:D4 и введите формулу =TRANSPOSE(F6:H6) . Как и прежде, Ctrl+Shift+Enter, чтобы он работал как формула массива.

Это должно легко масштабироваться для большего количества входных строк (используйте больше столбцов) или замен (используйте больше строк); просто отрегулируйте диапазоны соответственно. Если вам на самом деле не нужен ввод и вывод в столбцах, вы легко можете обойтись и без формул TRANSPOSE ; просто введите свой ввод в верхнем ряду, чтобы начать с.

Answer 2

Хотя я знаю, что вам нужно решение на основе формул, я думал, что версия VBA будет полезна для справки, поскольку я не смог найти ее в Интернете:

Function MULTISUB(aString As String, oldVals As Range, newVals As Range) As Variant
    oldW = oldVals.Columns.Count
    oldH = oldVals.Rows.Count

    If (oldW = newVals.Columns.Count) And (oldH = newVals.Rows.Count) And (oldW = 1 Or oldH = 1) Then
        MULTISUB = aString
        For i = 1 To oldVals.Count
            MULTISUB = Replace(MULTISUB, oldVals.Cells(i), newVals.Cells(i))
        Next i
    Else
        MULTISUB = CVErr(xlErrRef)
    End If
End Function

Например, вы должны ввести =MULTISUB(A1,$B$1:$B$5,$C$1:$C$5) в ячейку D1 и скопировать ее в D3 .

Функция принимает строку и два диапазона. Он ищет в строке значения в каждой ячейке в первом диапазоне и заменяет их значениями соответствующих ячеек во втором диапазоне. Если два диапазона не имеют одинаковую форму и размер, возвращается #REF .

Answer 3

Вы можете делать то, что вы описываете / иллюстрируете с

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, B$1, C$1), B$2, C$2), B$3, C$3), B$4, C$4), B$5, C$5)

Если вы поместите вышеупомянутое в ячейку D1 и перетащите / заполните вниз, A1 обновится до A2 , A3 и т.д., Но заменит параметры строки (B$1 , C$1 , B$2 , C$2 и т.д.) не изменится.  Я считаю, что очевидно, что при этом выполняется замена B1 → C1 на A1 , затем выполняется замена B2 → C2 на результат первой замены, затем выполняется замена B3 → C3 на результат второй замены и так далее.

Это неудобно для пяти замен и быстро становится громоздким для большего количества.  Если вы хотите сделать много замен (скажем, до 312), используйте вспомогательные столбцы:

• Установите AA1 в

  =SUBSTITUTE(A1, INDEX($B:$B, COLUMN()-COLUMN($Z1)), INDEX($C:$C, COLUMN()-COLUMN($Z1)))
  

  Это эквивалентно =SUBSTITUTE(A1, B1, C1) , но динамически получает значения B1 и C1 , беря номер столбца (AA → 27) и вычитая 26 (номер столбца столбца Z), и используя результат (1) в качестве номера строки в столбцах B и C

• Перетащите / заполните AA1 до AB1 , чтобы он стал

  =SUBSTITUTE(B1, INDEX($B:$B, COLUMN()-COLUMN($Z1)), INDEX($C:$C, COLUMN()-COLUMN($Z1)))
  

  и измените B1 на AA1 , чтобы он стал

  =SUBSTITUTE(AA1, INDEX($B:$B, COLUMN()-COLUMN($Z1)), INDEX($C:$C, COLUMN()-COLUMN($Z1)))
  

  Это эквивалентно =SUBSTITUTE(AA1, B2, C2) , что эквивалентно =SUBSTITUTE(SUBSTITUTE(A1, B1, C1), B2, C2) .

• Перетащите / заполните AB1 до LZ1 .  Это будет результатом A1 со всеми заменами от B1:C1 до B312:C312 (потому что L - 12-я буква, а 12 × 26 - 312).
• Установите D1 в =LZ1 , чтобы отразить результат всех замен.
• Перетащите / заполните D1 и AA1:LZ1 вниз, пока у вас есть данные в столбце A