Múltiplas substituições sequenciais usando parâmetros em outras colunas

Question

Em uma planilha do Excel:

A coluna A é preenchida com valores de texto aleatórios (por exemplo, palavras / frases) que precisam ser atualizados.

E as colunas B e C contêm sequências que especificam as alterações que precisam ser feitas para a coluna A. Coluna B é preenchida cada célula com o texto antigo e coluna C com o novo texto, cada linha correspondente entre si, isto é, o texto em B1 precisa ser substituído pelo texto C1, B2 a ser alterado para o texto C2. Na coluna B, uma das coisas a substituir é um " .

Não há valores repetidos na coluna B. As células podem ser repetidas na coluna C, no entanto. As substituições devem obedecer à ordem em que foram escritas. E a coluna D é o resultado.

Cada célula da coluna A precisa passar por essa substituição de B1-C1, B2-C2, B3-C3, etc., até o final dos comandos de substituição nas colunas B-C. Portanto, antes de dar o resultado final, o texto da coluna A mudará várias vezes e só mostrará o resultado na coluna D depois de todas as alterações.

Exemplo:

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.

Portanto, a célula A1, que é Hello! , será alterada por B1-C1, portanto, será Hello& . Em seguida, alterará B2-C2 com as alterações feitas no resultado temporário Hello& , portanto, será Hello. . Então mudará B3-C3 e então fará B4-C4 e B5-C5, e nada acontecerá porque " não existe em Hello. .

Depois disso, o resultado D1 é Hello. .

Em seguida, ele fará A2 mudanças B1-C1 (nada a fazer), então aplicado a este fará B2-C2 (nada a fazer), então B3-C3 que resultará como How are you John? (ou seja, os caracteres de citação será removido), então fará B4-C4, que fará How are you John# e, em seguida, B5-C5, que não tem nada a ser feito aqui.

Então, o resultado D2 é How are you John# .

Da mesma forma, o valor A3 "Nice! thanks" terá a mudança de B1-C1 em primeiro lugar, tornando-se "Nice& thanks" , B2-C2 se tornando "Nice. thanks" e B3-C3 passando a Nice. thanks (novamente, as cotações serão ser removido), então nada a fazer em B4-C4 e então B5-C5 fará com que ele se torne finalmente Fine, THANKS em D3.

Resultados:

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

Qual seria a fórmula na coluna D para fazer várias substituições seguidas obedecendo à ordem de linha escrita nas colunas B e C, permitindo que a coluna B-C tivesse mais de 300 linhas? (Eu gostaria de uma solução puramente baseada em fórmulas, em vez de VBA, se possível).

    

Answer 1

Você pode fazer o que descreve / ilustra com

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

Se você colocar o acima na célula D1 e arrastar / preencher, o A1 será atualizado para A2 , A3 , etc. mas os parâmetros de string substitutos ( B$1 , C$1 , B$2 , C$2 , etc.) não mudará. Eu acredito que é óbvio que isso executa a substituição B1 → C1 em A1 , em seguida, executa a substituição B2 → C2 no resultado da primeira substituição, em seguida, executa a substituição B3 → C3 no resultado da segunda substituição, e assim por diante.

Isso é estranho para cinco substituições, e rapidamente se torna pesado para mais. Se você quiser fazer muitas substituições (digamos, até 312), use colunas auxiliares:

• Defina AA1 para

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

  Isso é equivalente a =SUBSTITUTE(A1, B1, C1) , mas obtém os valores de B1 e C1 dinamicamente tomando o número da coluna ( AA → 27) e subtraindo 26 (o número da coluna da coluna Z ), e usando o resultado (1) como o número da linha nas colunas B e C .

• Arraste / encha AA1 para AB1 , por isso, torna-se

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

  e altere B1 para AA1 , por isso, torna-se

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

  Isso é equivalente a =SUBSTITUTE(AA1, B2, C2) , que é equivalente a =SUBSTITUTE(SUBSTITUTE(A1, B1, C1), B2, C2) .

• Arraste / encha AB1 para LZ1 . Este será o resultado de A1 com todas as substituições de B1:C1 a B312:C312 aplicadas (porque L é a 12ª letra e 12 × 26 é 312).
• Defina D1 para =LZ1 , para refletir o resultado de todas as substituições.
• Arraste / abasteça D1 e AA1:LZ1 para baixo até os dados na coluna A .

Answer 2

Eu esperava uma maneira inteligente de fazer isso usando uma única fórmula de matriz, mas não consegui encontrar uma (eu adoraria estar errado). Acho que a única maneira de fazer isso com uma única fórmula por A linha de entrada é aninhar SUBSTITUTE funções, conforme resposta @ G-Man . Infelizmente, essa estratégia não funcionará para 300 substituições, já que o Excel tem um limitação que funções só podem aninhar 64 níveis de profundidade (ou 7 antes do Excel 2007).

Por isso, acho que você precisará de colunas auxiliares (que você sempre pode ocultar se ajudar na apresentação). O método abaixo usa fórmulas mais simples que as do G-Man. Ele usa mais algumas células auxiliares do que a dele: uma coluna por string de entrada × uma linha por substituição mais uma:

• Começando com a planilha apresentada de acordo com seu exemplo, insira uma linha em branco na parte superior da página. Selecione as células F1:H1 , digite a fórmula =TRANSPOSE(A2:A4) e pressione Ctrl + Deslocar + Enter , para que seja inserida como uma fórmula de matriz. Você deve ver as chaves ao redor da fórmula e as células devem ser preenchidas com as strings de entrada.

• Na célula F2 , insira a fórmula =SUBSTITUTE(F1,$B2,$C2) . Copie essa fórmula para baixo e para preencher todo o F2:H6 . Sua saída desejada aparecerá em F6:H6 .

• Para retornar sua saída à coluna D , selecione D2:D4 e insira a fórmula =TRANSPOSE(F6:H6) . Como antes, Ctrl + Deslocamento + Enter para funcionar como uma fórmula de matriz.

Isso deve escalar facilmente para mais strings de entrada (use mais colunas) ou substituições (use mais linhas); basta ajustar os intervalos de acordo. Se você não precisa realmente da entrada e saída em colunas, você pode facilmente fazer sem as fórmulas TRANSPOSE também; basta digitar sua entrada na linha superior para começar.

    

Answer 3

Embora eu saiba que você estava atrás de uma solução baseada em fórmulas, achei que uma versão do VBA seria útil como referência, pois não consegui encontrar nenhuma on-line:

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

Para o seu exemplo, você digitaria =MULTISUB(A1,$B$1:$B$5,$C$1:$C$5) na célula D1 e copiaria para D3 .

A função usa uma string e dois intervalos. Ele procura na cadeia os valores em cada célula no primeiro intervalo e os substitui pelos valores das células correspondentes no segundo intervalo. Se os dois intervalos não forem da mesma forma e tamanho, ele retornará #REF .