Fórmulas Personalizadas de Validação de Dados

Este tutorial demonstra como criar fórmulas personalizadas em Validação de dados no Excel e no Google Planilhas

Validação de Dados – “Deve Começar Com” – Excel

Podemos escrever uma fórmula personalizada para garantir que os dados em uma célula comecem com um determinado texto.

1. Destaque o intervalo necessário, por exemplo: B3:B8.
2. Na faixa de opções, selecione Dados > Ferramentas de dados > Validação de dados.

3. Selecione Personalizado na caixa suspensa Permitir e digite a seguinte fórmula:

=EXATO(ESQUERDA(B3;4);”FRU-“)

 

A fórmula usa 2 funções EXATO e ESQUERDA para determinar se os primeiros 4 caracteres inseridos na célula são idênticos a “FRU-”

4. Se quiser adicionar uma mensagem de entrada para o usuário, clique em Mensagem de entrada.

5. Certifique-se de que a caixa de seleção “Mostrar mensagem de entrada ao selecionar célula” esteja marcada e digite o título e a mensagem desejados.
6. Se quiser adicionar um alerta de erro, clique em Alerta de erro.

7. Você pode alterar o estilo de Parar para Aviso ou Informação, se necessário, e digitar o título e a mensagem de erro.
8. Clique em OK.
9. Se tiver usado a opção Mensagem de entrada, um comentário será exibido na tela informando o usuário sobre a regra.

10. Digite “FRI-124” na célula B3. Se você tiver selecionado Parar, a seguinte caixa de mensagem será exibida.

11. Se você selecionou Aviso, esta caixa de mensagem será exibida. Isso permite que você continue se determinar que os dados estão corretos.

12. Se você tiver selecionado Informações, essa caixa de mensagem será exibida.

13. Se você clicar em OK, poderá continuar com os dados incorretos inseridos na célula.
14. Clique em Cancelar  para sair da mensagem ou em OK para inserir o texto na célula.

Validação de Dados: Permitir Somente Maiúsculas no Excel

Podemos escrever uma fórmula personalizada para garantir que os dados em uma célula só permitam maiúsculas se o texto for inserido na célula.

1. Destaque o intervalo necessário, por exemplo: B3:B8.
2. Na faixa de opções, selecione Dados > Ferramentas de dados > Validação de dados.
3. Selecione Personalizado na caixa suspensa Permitir e digite a seguinte fórmula:

=EXATO(B3;MAIÚSCULA(B3))

A fórmula usa 2 funções EXATO e MAIÚSCULA para determinar se o texto inserido na célula está em letras maiúsculas. As células com uma mistura de número e texto são consideradas texto e os números são ignorados na regra.

4. Clique em OK.
5. Digite “fru-124” na célula B3.
6. Se você tiver usado a opção Alerta de Erro, seu aviso personalizado e a mensagem de erro serão exibidos. Se você não tiver usado essa opção, o aviso padrão será exibido.

7. Clique em Cancelar para sair da mensagem ou em Repetir para reinserir o texto correto na célula.
8. Digite “123456” na célula B3.
9. Isso será permitido, pois é um número e não um texto.

Em nosso próximo exemplo, garantiremos que somente texto em maiúsculas possa ser inserido na célula.

Validação de dados: Permitir Somente Texto em Maiúsculas no Excel

Podemos escrever uma fórmula personalizada em Validação de dados que pode ser usada para garantir que os dados em uma célula só permitam texto em maiúsculas

OBSERVAÇÃO: se você inserir informações em uma célula que começa com texto, mas contém números, o Excel considerará as informações como texto.

1. Destaque o intervalo necessário, por exemplo: B3:B8.
2. Na faixa de opções, selecione Dados > Ferramentas de dados > Validação de dados.
3. Selecione Personalizado na caixa suspensa Permitir e digite a seguinte fórmula:

=E(EXATO(B3;MAIÚSCULA(B3));ÉTEXTO(B3))

A fórmula usa 4 funções E, EXATO, MAIÚSCULA e ÉTEXTO para determinar se o texto inserido nas células está em maiúsculas E para determinar se a informação inserida é realmente um texto e não um número puro.

4. Clique em OK.
5. Digite “fru-124” na célula B3.
6. Se você tiver usado a opção Alerta de Erro, seu aviso personalizado e a mensagem de erro serão exibidos. Se você não tiver usado essa opção, o aviso padrão será exibido.

7. Clique em Cancelar para sair da mensagem ou em Repetir para reinserir o texto correto na célula.
8. Digite “123456” na célula B3.
9. Você receberá novamente a mensagem de erro.
10. Clique em Cancelar para sair da mensagem ou em Repetir para reinserir o texto correto na célula.

Validação de Dados para Evitar Espaços no Excel

Podemos escrever uma fórmula personalizada para garantir que nenhum espaço seja inserido nos dados inseridos em um intervalo de células.

1. Destaque o intervalo necessário, por exemplo: B3:B8.
2. Na faixa de opções, selecione Dados > Ferramentas de dados > Validação de dados.
3. Selecione Personalizado na caixa suspensa Permitir e digite a seguinte fórmula:

=B3=SUBSTITUIR(B3;” “;””)

A fórmula usa a função SUBSTITUIR para verificar se não há espaços.

4. Clique em OK.
5. Digite “FRU – 124” na célula B4.
6. Se você tiver usado a opção Alerta de erro, serão exibidos o aviso personalizado e a mensagem de erro. Se você não tiver usado essa opção, será exibido o aviso padrão.

7. Clique em Cancelar para sair da mensagem ou em Repetir para reinserir o texto correto na célula.

Validação de Dados para Evitar Duplicatas no Excel

Podemos escrever uma fórmula personalizada para evitar a inserção de informações duplicadas em um intervalo de células.

1. Destaque o intervalo necessário, por exemplo: B3:B8.
2. Na faixa de opções, selecione Dados > Ferramentas de dados > Validação de dados.
3. Selecione Personalizado na caixa suspensa Permitir e digite a seguinte fórmula:

=CONT.SE($B$3:$B$8;B3)<2

 

A fórmula usa a função CONT.SE e usa fixa o intervalo B3:B8 com $ ($B$3:$B$8) para garantir que essa seja a lista que a função CONT.SE examina quando verifica se há valores duplicados.

4. Clique em OK.
5. Digite “FRU-123” na célula D4.
6. Se você tiver usado a opção Alerta de Erro, seu aviso personalizado e a mensagem de erro serão exibidos. Se você não tiver usado essa opção, o aviso padrão será exibido.

7. Clique em Cancel (Cancelar) para sair da mensagem ou em Retry (Tentar novamente) para reinserir o texto correto na célula.

Validação de Dados Existentes em Lista do Excel

Podemos escrever uma fórmula personalizada para garantir que apenas um texto específico seja inserido em uma célula.

1. Destaque o intervalo necessário, por exemplo: D3:D8.
2. Na faixa de opções, selecione Dados > Ferramentas de dados > Validação de dados.
3. Selecione Personalizado na caixa suspensa Permitir e digite a seguinte fórmula:

=CONT.SE($F$6:$F$8;D3)>0

A fórmula usa a função CONT.SE e FIXA o intervalo F3:F8 com $ para garantir que essa seja a lista que a função CONT.SE examina quando verifica se o texto correto está sendo inserido.

4. Clique em OK.
5. Digite “Cacho” na célula D4.
6. Se você tiver usado a opção Alerta de Erro, seu aviso personalizado e a mensagem de erro serão exibidos. Se você não tiver usado essa opção, será exibido o aviso padrão.

7. Clique em Cancelar para sair da mensagem ou em Repetir para reinserir o texto correto na célula.

Validação de Dados não Existentes na Lista do Excel

Podemos usar uma fórmula personalizada para garantir que um texto específico não seja inserido em uma célula.

1. Destaque o intervalo necessário, por exemplo: C3:C8.
2. Na faixa de opções, selecione Dados > Ferramentas de dados > Validação de dados.
3. Selecione Personalizado na caixa suspensa Permitir e digite a seguinte fórmula:

=CONT.SE($F$6:$F$8;C3)=0

A fórmula usa a função CONT.SE e fixa o intervalo F3:F8 com $ para garantir que essa seja a lista que a função CONT.SE examina quando verifica se o texto correto está sendo inserido.

4. Clique em OK.
5. Digite “bife” na célula C4.
6. Se você tiver usado a opção Alerta de Erro, seu aviso personalizado e a mensagem de erro serão exibidos. Se você não tiver usado essa opção, aparecerá o aviso padrão.

 

7. Clique em Cancelar para sair da mensagem ou em Repetir para reinserir o texto correto na célula.

Validação de Dados: Somente Números são Inseridos no Excel

Podemos usar uma fórmula personalizada para garantir que apenas um número seja inserido em uma célula.

1. Destaque o intervalo necessário, por exemplo: F3:F8.
2. Na faixa de opções, selecione Dados > Ferramentas de dados > Validação de dados.
3. Selecione Personalizado na caixa suspensa Permitir e digite a seguinte fórmula:

=ÉNÚM(F3:F8)

A fórmula usa a função ÉNÚM para garantir que um número seja inserido nas células do intervalo.

4. Clique em OK.
5. Digite “nove” na célula F4.
6. Se você tiver usado a opção Alerta de Erro, serão exibidos o aviso personalizado e a mensagem de erro. Se você não tiver usado essa opção, será exibido o aviso padrão.

7. Clique em Cancelar para sair da mensagem ou em Repetir para reinserir o texto correto na célula.

Validação de Dados não Excedendo Valor no Excel

Podemos usar uma fórmula personalizada para garantir que os valores inseridos em uma célula não excedam um valor especificado.

1. Destaque o intervalo necessário, por exemplo: E3:E8.
2. Na faixa de opções, selecione Dados > Ferramentas de dados > Validação de dados.
3. Selecione Personalizado na caixa suspensa Permitir e digite a seguinte fórmula:

=E3<=$G$6

A fórmula fixa a célula $G$6 para garantir que esse seja o valor que a regra verifica quando os dados são inseridos em E3.

4. Clique em OK.
5. Digite “9” na célula E4.
6. Se você tiver usado a opção Alerta de Erro, seu aviso personalizado e a mensagem de erro serão exibidos. Se você não tiver usado essa opção, o aviso padrão será exibido.

7. Clique em Cancelar para sair da mensagem ou em Repetir para reinserir o texto correto na célula.

Validação de Dados com Total não Excedendo Valor no Excel

Podemos usar uma fórmula personalizada para garantir que os valores inseridos em um intervalo de células não excedam um valor total especificado para o intervalo

1. Destaque o intervalo necessário, por exemplo: F3:F8.
2. Na faixa de opções, selecione Dados > Ferramentas de dados > Validação de dados.
3. Selecione Personalizado na caixa suspensa Permitir e digite a seguinte fórmula:

=SOMA($F$3:$F$8)<=$H$6

A fórmula usa a função SOMA e fixa o intervalo $F$3:$F$8 para garantir que essa seja a lista que a função SOMA examina quando verifica se o total do intervalo não é maior do que o valor inserido em H6.

4. Clique em OK.
5. Digite “40” na célula F4.
6. Se você tiver usado a opção Error Alert (Alerta de Erro), seu aviso personalizado e a mensagem de erro serão exibidos. Se você não tiver usado essa opção, será exibido o aviso padrão.

 

7. Clique em Cancelar para sair da mensagem ou em Repetir para reinserir o texto correto na célula.

Validação de Dados Somente Datas de Dias Úteis no Excel

Podemos usar uma fórmula personalizada para garantir que somente os dias da semana (úteis) sejam inseridos quando as datas forem usadas no Excel.

1. Destaque o intervalo necessário, por exemplo: G3:G8.
2. Na faixa de opções, selecione Dados > Ferramentas de dados > Validação de dados.
3. Selecione Personalizado na caixa suspensa Permitir e digite a seguinte fórmula:

=DIA.DA.SEMANA(F3;2)<6

A função DIA.DA.SEMANA verifica se o dia contido na data não é um sábado ou um domingo.

4. Altere a data em G5 para mostrar um sábado (por exemplo,⁹ de maio de 2020).
5. Se você tiver usado a opção Alerta de Erro, sua mensagem personalizada de aviso e erro será exibida. Se você não tiver usado essa opção, o aviso padrão será exibido.

7. Clique em Cancelar ou Repetir para inserir uma data alternativa.

Validação de Dados Somente para Data Futura no Excel

Podemos criar uma fórmula personalizada para permitir que o usuário insira apenas uma data no futuro.

1. Destaque o intervalo necessário, por exemplo: G3:G8.
2. Na faixa de opções, selecione Dados > Ferramentas de dados > Validação de dados.
3. Selecione Personalizado na caixa suspensa Permitir e digite a seguinte fórmula:

=G3>HOJE()

A fórmula usa a função HOJE para verificar se a data inserida na célula é maior que a data de hoje.

4. Altere a data em G5 para ontem, ou qualquer data no passado.
5. Se você tiver usado a opção Alerta de erro, serão exibidos o aviso personalizado e a mensagem de erro. Se você não tiver usado essa opção, o aviso padrão será exibido.

6. Clique em Cancelar ou Repetir para inserir uma data alternativa.

A Validação de Dados Deve Começar Com no Planilhas Google

1. Destaque o intervalo necessário, por exemplo: B3:B8.
2. No menu, selecione Dados > Validação de dados.

3. Clique em “+ Adicionar regra”, na interface à direita.

O intervalo de células já estará preenchido

4. Selecione A fórmula personalizada é na lista suspensa Critérios.

5. Digite a fórmula.

=EXATO(ESQUERDA(B3;4);”FRU-“)

6. Selecione Mostrar aviso ou Rejeitar entrada se os dados forem inválidos.

7. Você pode digitar algum texto de ajuda de validação, se necessário.

8. Clique em Concluído.

9. Clique em B3 e digite FRI-123.

10. Se você tiver selecionado Mostrar um aviso, a seguinte mensagem será exibida.

12. Como alternativa, se tiver selecionado Rejeitar a entrada, você será impedido de inserir os dados e a seguinte mensagem será exibida na tela.

O restante dos exemplos de fórmulas personalizadas no Planilhas Google funciona exatamente da mesma maneira.