Ao processar grandes lotes de dados, poucas funções do Excel são tão versáteis quanto a função SUBSTITUIR. Infelizmente, é tragicamente negligenciado em obséquio de soluções muito mais complicadas para o que geralmente são problemas simples.
A função SUBSTITUTE é uma maneira rápida e limpa de limpar dados, somar detalhes a relatórios e produzir comunicações de formulário simples. Dominar essa função e entender seu uso a torna uma instrumento insubstituível e fácil de usar para investigar grandes conjuntos de dados.
1. Criando comunicações de formulário com a função SUBSTITUIR do Excel
A função SUBSTITUIR do Excel facilita a troca rápida e fácil de marcadores pré-especificados em um pedaço de texto pré-formatado. Isso permite que os usuários criem comunicações de formulário facilmente, trocando as informações que mudam a cada iteração.
Você pode colocar um padrão em uma célula de uma planilha e inserir uma lista de destinatários em uma planilha dissemelhante. Em seguida, encadeie as funções SUBSTITUTE em outra célula para somar os detalhes de cada destinatário.
Observe que você pode aninhar mais de um SUBSTITUTE um dentro do outro:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Template!$A$1, "{ cardlastfour }", F2), "{ billingtotal }", E2), "{ cardtype }", D2), "{ enddate }", TEXT(C2, "mmm dd, yyyy")), "{ lname }", B2), "{ fname }", A2)
Se você tiver muitos campos alterados, poderá dividir a missiva inteira em parágrafos e alterá-los um de cada vez. Na última célula da risco, concatene todos esses parágrafos em uma única letra.
E-mails gerados maquinalmente são uma maneira fantástica de compreender uma lista de clientes de forma rápida e fácil, produzir correspondência de marketing personalizada ou gerar cartas de apresentação personalizadas.
Você pode dividir mensagens mais longas em parágrafos ou frases únicas para evitar fórmulas de SUBSTITUIÇÃO excessivamente complexas. Você pode até usar condicionais para personalização suplementar, porquê trocar diferentes listas de habilidades em uma missiva de apresentação com base no tipo de trabalho para o qual está se candidatando.
2. Usando a função SUBSTITUIR do Excel para gerar relatórios
A função SUBSTITUTE não é exclusivamente útil para inserir informações básicas em campos pré-formatados, mas também pode injetar cálculos complexos em relatórios pré-fabricados. A injeção de cálculos permite que você use o Excel para produzir um pintura que forneça informações visuais e forneça observações personalizadas com base nos dados.
A combinação de SUBSTITUTE para pré-gerar blocos de texto e condicionais porquê a função SE permite produzir relatórios mais detalhados.
No exemplo supra, você pode ver que dois parágrafos separados estão sendo criados usando algumas informações de um relatório. Uma função condicional pode mandar qual dos dois parágrafos é propício para exibir aos leitores do relatório.
O primeiro parágrafo é criado usando esta fórmula:
=IF(A2>B2, SUBSTITUTE(Templates!$A$3, "{ growthpercent }", (ROUND(B2 / A2, 2) * 100) - 100 & "%"), SUBSTITUTE(Templates!$A$4, "{ decreasepercent }", 100 - (ROUND(A2 / B2, 2)) * 100 & "%"))
O emparelhamento de várias funções SUBSTITUTE encadeadas com condicionais pode ajudá-lo a produzir painéis e relatórios mais perspicazes. Os cálculos podem até ser executados diretamente nas funções SUBSTITUIR para prometer que você apresente exclusivamente as informações mais relevantes aos seus leitores.
3. Limpeza de dados do Excel para armazenamento usando a função SUBSTITUIR do Excel
Quando se trata de armazenamento de dados de longo prazo, poucas considerações são tão cruciais quanto a sanitização. Impedir que dados inúteis poluam ou danifiquem um conjunto de dados é forçoso para evitar erros futuros que você não consegue entender.
Isso pode valer qualquer coisa, desde expelir caracteres ilegais de um conjunto de dados até prometer que palavras-chave reservadas sejam tratadas porquê texto em vez de comandos. Não importa o que você precise fazer para manter seus bancos de dados protegidos contra ameaças porquê injeção de SQL e seus dados utilizáveis, a função SUBSTITUIR do Excel pode ajudar.
Por exemplo, podemos preparar uma lista de respostas do usuário a uma pesquisa para inserção em um banco de dados SQL. Dados não validados podem sustar uma variedade de caracteres ilegais que podem ocasionar problemas mais adiante se não forem removidos:
=SUBSTITUTE(A2:A4, "'", ''")
A função supra representa uma lanço simplista, mas necessária, na preparação dos dados para armazenamento. Essa função examina cada resposta na pilastra B em procura de uma aspa simples. Se presente, o caractere tem uma barra invertida anexada antes. Juntar barras invertidas ajuda a prometer que os caracteres reservados não interfiram na consulta usada para inserir os dados no banco de dados.
Se o caractere ou string que você está procurando precisar ser removido completamente, a função SUBSTITUTE ainda é útil:
=SUBSTITUTE(B2:B20, "$", "")
Usar uma string vazia para o terceiro argumento removerá totalmente os caracteres especificados da célula. Com a função SUBSTITUIR, você pode remover um texto específico para remover completamente caracteres potencialmente nocivos de seus dados.
4. Garantindo a formatação adequada dos dados com a função SUBSTITUIR do Excel
Um problema potencial ao coletar dados é a possibilidade de receber informações malformadas. Quando os usuários inserem informações em um formulário de resposta livre, isso pode levar a informações de relatório incorretas. Dados imprecisos podem ser tão simples quanto um erro ortográfico geral ou tão complexos quanto uma equação com o formato inverídico.
A função SUBSTITUIR do Excel é a maneira perfeita de evitar que erros simples causem relatórios de dados incorretos. Ao investigar os resultados produzidos por uma nascente específica, a remoção de imprecisões conhecidas pode ajudar a produzir resultados mais precisos:
=SUBSTITUTE(A2, "CompanyName Inc.", "Company Name Inc.")
O exemplo supra mostra porquê uma função SUBSTITUTE pode remover um simples erro de digitação de uma lista de análises de produtos. A substituição de um erro ortográfico geral pode prometer que uma menção a uma marca ou empresa não passe despercebida ao examinar os dados posteriormente.
O uso da função SUBSTITUIR para substituir possíveis erros conhecidos pelas informações corretas pode detectar problemas em seu conjunto de dados antes que eles se tornem problemas, ajudando a evitar erros se desejar usar sua planilha para geração de perfis de dados no porvir.
Função SUBSTITUIR do Excel: simples, mas funcional
Embora a função SUBSTITUIR possa ser relativamente simples, é uma fórmula extremamente útil. Substituir texto em blocos pré-formatados, ajudar a gerar relatórios, limpar dados e remover possíveis informações de problemas de um conjunto de dados exclusivamente arranha a superfície.
Usar cálculos no SUBSTITUTE e combiná-los com fórmulas condicionais ajuda a expandir seus recursos. Ele pode estender painéis e relatórios de apresentação de dados visuais para fornecer análises complexas geradas maquinalmente.
A combinação dessa função com outras fórmulas e técnicas pode produzir um pintura que rivaliza com as ferramentas de banco de dados dedicadas. Portanto, saber e entender a função SUBSTITUIR do Excel é forçoso se você for um crítico de dados, engenheiro ou trabalhar com conjuntos de dados massivos.
