[Fórmulas Matriciais] Validar célula para não aceitar texto com acentuação

Hoje vi uma pergunta interessante de uma pessoa num fórum, onde o usuário gostaria de validar um intervalo para não permitir a digitação de textos com acentuação.

Vou demostrar aqui uma maneira de fazer essa validação através de uma validação personalizada utilizando uma fórmula matricial.

Quando crio uma regra personalizada que utilizam fórmulas um pouco mais complexa, normalmente desenvolvo a fórmula na planilha para depois copiá-la no campo de validação.

Neste método vamos precisar criar uma sequência de caracteres que contenham todos os caracteres que desejamos excluir na validação da célula, ou seja, os caracteres que não desejamos que o usuário digite. A sequência de caracteres que vamos validar será:

àáâãäåçèéêëìíîïðñòóôõöùúûüýÿšž

A fórmula matricial de teste lógico para realizar esta validação, tomando como base o texto inserido na célula A2, será:

=SOMA(SEERRO(LOCALIZAR(EXT.TEXTO(“àáâãäåçèéêëìíîïðñòóôõöùúûüýÿšž”;LIN(INDIRETO(“1:”&NÚM.CARACT(“àáâãäåçèéêëìíîïðñòóôõöùúûüýÿšž”)));1);A2);0))=0

Note que a cadeia de caractere só tem letras minúsculas. Isso porque estamos aplicando a função LOCALIZAR que não é Case Sensitive, ou seja, não diferencia letras maiúsculas de minúsculas, e por isso não é necessário incluir letras maiúsculas acentuadas na cadeia de caractere.

Se desejar uma validação de caracteres específicos, levando em conta a diferenciação de letras maiúsculas e minúsculas, crie a sequência de caracteres com as letras desejadas e substitua a função LOCALIZAR por PROCURAR, pois esta função possui os mesmos argumentos, com a diferença que é Case Sensitive, ou seja, distinguirá letras maiúsculas de minúsculas.

Explicação da fórmula:

Vamos supor que na célula A2 tenha a palavra “ExcelManíacos”, vamos analisar passo a passo a avaliação dessa fórmula matricial, sendo que o processamento vai ocorrendo de dentro pra fora da fórmula:


1º) =SOMA(SEERRO(LOCALIZAR(EXT.TEXTO(“àáâãäåçèéêëìíîïðñòóôõöùúûüýÿšž”;LIN(INDIRETO(“1:”&NÚM.CARACT(“àáâãäåçèéêëìíîïðñòóôõöùúûüýÿšž”)));1);A2);0))=0:

O processamento da função NÚM.CARACT retornar a quantidade de caracteres dentro do argumento, que no caso deste modelo são 30 caracteres

Fórmula avaliada: =SOMA(SEERRO(LOCALIZAR(EXT.TEXTO(“àáâãäåçèéêëìíîïðñòóôõöùúûüýÿšž”;LIN(INDIRETO(“1:”&30));1);A2);0))=0


2º) =SOMA(SEERRO(LOCALIZAR(EXT.TEXTO(“áàâãéèêíìîóòôõúùûüñý”;LIN(INDIRETO(“1:”&30));1);A2);0))=0

O ampersand (&) tem a função de concatenar duas cadeiras de caracteres, ou seja, formará um novo texto unindo dois textos ou números. Neste caso juntando “1:” com 30, resultando no novo texto “1:30”

Fórmula avaliada: =SOMA(SEERRO(LOCALIZAR(EXT.TEXTO(“áàâãéèêíìîóòôõúùûüñý”;LIN(INDIRETO(“1:30”));1);A2);0))=0


3º) =SOMA(SEERRO(LOCALIZAR(EXT.TEXTO(“áàâãéèêíìîóòôõúùûüñý”;LIN(INDIRETO(“1:30”));1);A2);0))=0

A função INDIRETO retornará a referencia indicada por um elemento de texto, neste caso, retornará a referência à planilha correspondente ao intervalo de linhas de 1 até 30. Esta é uma técnica comum de se utilizar em fórmulas matriciais, pois é uma maneira de criar uma matriz de 1 ate um n-ésimo elemento desejado. Aqui neste modelo desejamos criar uma matriz de 1 até a quantidade de letras da cadeia de caracteres acentuados que definimos, no caso 30. No próximo passo entenderemos melhor.

Fórmula avaliada: =SOMA(SEERRO(LOCALIZAR(EXT.TEXTO(“áàâãéèêíìîóòôõúùûüñý”;LIN(1:30);1);A2);0))=0


4º) =SOMA(SEERRO(LOCALIZAR(EXT.TEXTO(“áàâãéèêíìîóòôõúùûüñý”;LIN(1:30);1);A2);0))=0

A função LIN retorna o número da linha da referência indicada. Neste modelo, como estamos dando a referência 1:30, ela retornará uma matriz de 1 até 30, que é representada entre chaves: {1;2;3;4;5;6;…;27;28;29;30}

Fórmula avaliada: =SOMA(SEERRO(LOCALIZAR(EXT.TEXTO(“àáâãäåçèéêëìíîïðñòóôõöùúûüýÿšž”;{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30};1);A2);0))=0


5º) =SOMA(SEERRO(LOCALIZAR(EXT.TEXTO(“àáâãäåçèéêëìíîïðñòóôõöùúûüýÿšž”;{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30};1);A2);0))=0

Primeiro, precisamos saber o que a função EXT.TEXTO faz: “Retorna os caracteres do meio de uma cadeia de texto, tendo o comprimento e a posição especificados.” O 1º argumento é o texto onde vamos extrair os caracteres, no 2º definimos a posição da esquerda para a direita, que é a partir de onde queremos extrair os caracteres e no 3º argumento dizemos quantos caracteres vamos extrair a partir da posição definida.

Aqui entra um segundo processamento matricial. Observe que a função EXT.TEXTO será processada 30 vezes neste cálculo, sendo uma vez para cada elemento do matriz de 1 a 30. Como a matriz está no 2º argumento da função, que é o argumento que define a posição e o 3º argumento está constante com 1, a matriz de resposta será cada caractere da cadeia de textos das caracteres com acento:

{“à”;”á”;”â”;”ã”;”ä”;”å”;”ç”;”è”;”é”;”ê”;”ë”;”ì”;”í”;”î”;”ï”;”ð”;”ñ”;”ò”;”ó”;”ô”;”õ”;”ö”;”ù”;”ú”;”û”;”ü”;”ý”;”ÿ”;”š”;”ž”}

Fórmula avaliada: =SOMA(SEERRO(LOCALIZAR
({“à”;”á”;”â”;”ã”;”ä”;”å”;”ç”;”è”;”é”;”ê”;”ë”;”ì”;”í”;”î”;”ï”;”ð”;”ñ”;”ò”;”ó”;”ô”;”õ”;”ö”;”ù”;”ú”;”û”;”ü”;”ý”;”ÿ”;”š”;”ž”};A2);0))=0


6º) =SOMA(SEERRO(LOCALIZAR
({“à”;”á”;”â”;”ã”;”ä”;”å”;”ç”;”è”;”é”;”ê”;”ë”;”ì”;”í”;”î”;”ï”;”ð”;”ñ”;”ò”;”ó”;”ô”;”õ”;”ö”;”ù”;”ú”;”û”;”ü”;”ý”;”ÿ”;”š”;”ž”};A2);0))=0

A função LOCALIZAR localiza uma cadeia de texto em uma segunda cadeia de texto e retornam o número da posição inicial da primeira cadeia de texto do primeiro caractere da segunda cadeia de texto. Em outras palavras, localiza um texto dentro de outro texto, se achar ela retorna a posição onde inicia o texto procurado, se não achar retorna o erro #VALOR! .

Assim, o que ocorrerá é um terceiro processamento matricial, que será a procura de cada caractere com acento dentro do texto digitado na célula A2, que como relatamos no começo será “ExcelManíacos”.

Aqui uma nova matriz com 30 elementos será retornada e somente no 13º elemento (“í”) é que não resultará em erro, pois na palavra “ExcelManíacos” há o “í” no 9º caractere. Assim a matriz resposta será:

{#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;
#VALOR!;#VALOR!;#VALOR!;9;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;
#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!}

Fórmula avaliada ficará: =SOMA(SEERRO({#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;
#VALOR!;#VALOR!;#VALOR!;9;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;
#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;
#VALOR!;#VALOR!};0))=0


7º) =SOMA(SEERRO({#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;
#VALOR!;#VALOR!;#VALOR!;9;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;
#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;#VALOR!;
#VALOR!;#VALOR!};0))=0

A função SERRO servirá para tratar os erros #VALOS! gerados na matriz, trocando-os por 0.

Fórmula avaliada: =SOMA({0;0;0;0;0;0;0;0;0;0;0;0;9;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0})=0


8º) =SOMA({0;0;0;0;0;0;0;0;0;0;0;0;9;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0})=0

Neste penúltimo passo, a função soma somará os resultados da matriz, resultando em 9.

Fórmula avaliada:=9=0


9º) =9=0

Por fim, chegamos ao teste lógico de “nove é igual a zero”, que retorna FALSO.

Fórmula avaliada:=FALSO


Recapitulando, esta é uma fórmula que utilizaremos para a validação de dados de entrada da célula, então veja que sempre que o texto digitado na célula A2 contiver algum caractere da sequência “àáâãäåçèéêëìíîïðñòóôõöùúûüýÿšž” a fórmula até o passo 8º resultará um valor maior que zero, e do contrário, quando não conter nenhum caractere da sequencia, a fórmula retornará zero.

Criando a regra de validação:

Agora que já sabemos qual fórmula utilizar, basta selecionarmos o intervalo desejado de A2 à A20, começando a seleção em A2 para que a célula ativa seja A2, e ir na opção de validação de dados e copiar a fórmula desenvolvida para o campo da fórmula:

validacao-sem-acentuacao-01

  • Note que a referência a célula A2 está relativa (sem nenhum $), portanto a mesma regra será aplicada relativamente a cada célula no intervalo selecionado (A2:A20).
  • Uma curiosidade é que não é preciso inserir a fórmula com CTRL+SHIFT+ENTER na fórmula de validação, mesmo que a fórmula seja matricial. O Excel entende e processa corretamente a fórmula neste caso. O mesmo ocorre quando inserimos fórmulas matriciais no Gerenciador de Nomes.

Defina uma mensagem de Alerta de Erro:

validacao-sem-acentuacao-02

Pronto! A sua regra de validação para não permitir digitação de acentos está pronto, sem necessidade de recorrer a outras ferramentas de desenvolvedor.

Baixe o modelo aqui: arquivo-modelo-validacao-somente-sem-acentuacao

5 comentários em “[Fórmulas Matriciais] Validar célula para não aceitar texto com acentuação

  1. Prezado a fórmula é muito boa e de fato valida os dados, impedido que se coloque acento nas palavras, mas estou encontrando um problema no excel 2007, pois ao fechar e abrir novamente o arquivo, a planilha para de validar os dados, sendo necessário refazer o processo (apesar da fórmula continuar salda na validação).

    Curtir

  2. Prezados, esse é, de fato, um problema que acomete todos que compartilham o uso de uma mesma planilha. Tenho uma base de dados e preciso ficar sempre retirando os acentos para que as demais fórmulas funcionem. Achei maravilhoso essa possibilidade de impedir a acentuação, mas ao tentar aplicar o excel retorna a mensagem: “Um intervalo nomeado especificado por você não pode ser encontrado”. Fiz conforme o orientado, mesmo assim deu problema. Podem me ajudar?

    Obrigada.

    Curtir

    1. sgc, também tive esse problema. Resolvi eliminando as aspas (“) dos termos “EXT.TEXTO” e “NUM.CARACT”, ou seja, o intervalo de caracteres proibidos (àáâãäåçèéêëìíîïðñòóôõöùúûüýÿšž) não deve estar entre aspas.

      Curtir

      1. Mesmo sem as aspas não consegui utilizar para validação. Como disse ela funciona somente quando é colocada inicialmente. Após fechar e abrir novamente o arquivo a fórmula para de validar. Tem mais alguma dica do que pode ser feito?

        Curtir

  3. Muito bom o trabalho, bem didático. Parabéns ao ExcelManíacos! …

    Lendo aqui, também tive esse problema com as aspas quando copiei a fórmula desse link diretamente para a planilha, pois a fórmula não funcionava. Depois de muito tempo é que descobri que as aspas copiadas eram de um caractere diferente do utilizado pelo meu sistema operacional. Foi só trocar as aspas e deu certo. No arquivo modelo, disponibilizado nesse link para baixar – as aspas estão de acordo, a fórmula funciona perfeitamente.

    Quanto à ativação da fórmula com CTRL+SHIFT+ENTER, na planilha é necessário (uma deficiência do Excel), mas na validação da célula de entrada realmente não há necessidade.

    No Excel para smartphone ANDROID, a validação da célula funciona (parcialmente, pois não impede que o dado seja armazenado na célula), mas para usar a fórmula diretamente na planilha do Android é complicado, pois é necessário descobrir como configurar o teclado para ativar a fórmula com CTRL+SHIFT+ENTER.

    Abraço.

    Curtir

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Gravatar
Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s