Fórmulas personalizadas de validação de dados

Este tutorial demonstrará como criar fórmulas personalizadas na validação de dados no Excel e no Planilhas Google

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 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 DEIXOU para determinar se os primeiros 4 caracteres inseridos na célula são idênticos a “FRU-”

4. Se você deseja adicionar uma mensagem de entrada para o seu usuário, clique em Mensagem de entrada.

5. Certifique-se de que a caixa de seleção “Mostrar mensagem de entrada quando a célula for selecionada”Está marcada e, em seguida, digite o título e a mensagem de que você deseja.
6. Se você deseja adicionar um alerta de erro, clique em Alerta de Erro.

7. Você pode alterar o estilo de Pare para qualquer um Aviso ou Em formação se você precisar, digite seu título e a mensagem de erro.
8. Clique OK.
9. Se você usou o Entrada Opção de mensagem, aparecerá um comentário na tela informando o usuário da regra.

10. Digite “FRI-124” na célula B3. Se você selecionou Pare, a seguinte caixa de mensagem aparecerá.

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

12. Se você selecionou Em formação, então esta caixa de mensagem aparecerá.

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

A validação de dados permite apenas letras maiúsculas no Excel

Podemos escrever uma fórmula personalizada para garantir que os dados em uma célula só permitam letras 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, SUPERIOR (B3))

A fórmula usa 2 funções EXATO e SUPERIOR para determinar se o texto inserido na célula está em 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 OK.
5. Digite “fru-124” na célula B3.
6. Se você usou a opção Alerta de erro, seu aviso personalizado e mensagem de erro serão exibidos. Se você não usou esta opção, o aviso padrão aparecerá.

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

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

A validação de dados permite apenas letras maiúsculas no Excel

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

NOTA: se você inserir informações em uma célula que começa com texto, mas contém números, o Excel considerará o texto das informações.

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, SUPERIOR (B3)), ISTEXTO (B3))

A fórmula usa 4 funções E, EXATO, SUPERIOR e TEXTO para determinar se o texto inserido nas células está em maiúsculas E para determinar se as informações inseridas são realmente texto e não um número puro.

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

7. Clique em Cancelar para sair da mensagem ou em Tentar novamente para inserir 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 Tentar novamente para inserir o texto correto na célula.

Validação de dados impede 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 = SUBSTITUTAR (B3, ””, “”)

A fórmula usa a função SUBSTITUTE para verificar se não existem espaços.

4. Clique OK.
5. Digite “FRU - 124” na célula B4.
6. Se você usou a opção Alerta de erro, seu aviso personalizado e mensagem de erro serão exibidos. Se você não usou esta opção, o aviso padrão aparecerá.

7. Clique em Cancelar para sair da mensagem ou em Tentar novamente para inserir o texto correto na célula.

Validação de dados evita duplicatas no Excel

Podemos escrever uma fórmula personalizada para nos impedir de inserir 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 Cliente na caixa suspensa Permitir e digite a seguinte fórmula:

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

A fórmula usa o CONT.SE função e usos ABSOLUTOS no intervalo B3: B8 para garantir que esta é a lista que o CONT.SE A função analisa quando verifica se há algum valor duplicado.

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

7. Clique em Cancelar para sair da mensagem ou em Tentar novamente para inserir o texto correto na célula.

Validação de dados existe em lista no 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 o CONT.SE função e usos ABSOLUTOS no intervalo F3: F8 para garantir que esta é a lista que o CONT.SE A função analisa quando verifica se o texto correto está sendo inserido.

4. Clique OK.
5. Digite “Single” na célula D4.
6. Se você usou a opção Alerta de erro, seu aviso personalizado e mensagem de erro serão exibidos. Se você não usou esta opção, o aviso padrão aparecerá.

7. Clique em Cancelar para sair da mensagem ou em Tentar novamente para inserir o texto correto na célula.

A validação de dados não existe 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 Cliente na caixa suspensa Permitir e digite a seguinte fórmula:

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

A fórmula usa o CONT.SE função e usos ABSOLUTOS no intervalo F3: F8 para garantir que esta é a lista que o CONT.SE A função analisa quando verifica se o texto correto está sendo inserido.

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

7. Clique em Cancelar para sair da mensagem ou em Tentar novamente para inserir o texto correto na célula.

Validação de dados apenas 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: E3: E8.
2. Na faixa de opções, selecione Dados> Ferramentas de dados> Validação de dados.
3. Selecione Cliente na caixa suspensa Permitir e digite a seguinte fórmula:

= ISNUMBER (F3: F8)

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

4. Clique OK.
5. Digite “nove” na célula F4.
6. Se você usou a opção Alerta de erro, seu aviso personalizado e mensagem de erro serão exibidos. Se você não usou esta opção, o aviso padrão aparecerá.

7. Clique em Cancelar para sair da mensagem ou em Tentar novamente para inserir o texto correto na célula.

A validação de dados não excede o 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 Cliente na caixa suspensa Permitir e digite a seguinte fórmula:

= E3 <= $ G $ 6

A fórmula e ABSOLUTO na faixa G6 para garantir que este seja o valor que a regra verifica quando os dados são inseridos no E3.

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

7. Clique em Cancelar para sair da mensagem ou em Tentar novamente para inserir o texto correto na célula.

A validação de dados não excede o total 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 Cliente na caixa suspensa Permitir e digite a seguinte fórmula:

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

A fórmula usa o SOMA função e usos ABSOLUTOS no intervalo F3: F8 para garantir que esta é a lista que o SOMA A função verifica quando verifica se o total do intervalo não é maior do que o valor inserido em H6.

4. Clique OK.
5. Digite “40” na célula F4.
6. Se você usou a opção Alerta de erro, seu aviso personalizado e mensagem de erro serão exibidos. Se você não usou esta opção, o aviso padrão aparecerá.

7. Clique em Cancelar para sair da mensagem ou em Tentar novamente para inserir o texto correto na célula.

Datas da semana de validação de dados somente no Excel.

Podemos usar uma fórmula personalizada para garantir que apenas os dias da semana 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 Cliente na caixa suspensa Permitir e digite a seguinte fórmula:

= WEEKDAY (F3,2) <6

A função de dia da semana verifica se o dia contido na data não é um sábado ou um domingo.

4. Altere a data no G5 para mostrar um sábado (por exemplo, 9^º Maio de 2022).
5. Se você usou a opção Alerta de erro, seu aviso personalizado e mensagem de erro serão exibidos. Se você não usou esta opção, o aviso padrão aparecerá.

7. Clique em Cancelar ou Tentar novamente para inserir uma data alternativa.

Data futura de validação de dados apenas em Excel

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

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 Cliente na caixa suspensa Permitir e digite a seguinte fórmula:

= G3> HOJE ()

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

4. Altere a data no G5 para ontem.
5. Se você usou a opção Alerta de erro, seu aviso personalizado e mensagem de erro serão exibidos. Se você não usou esta opção, o aviso padrão aparecerá.

6. Clique em Cancelar ou Tentar novamente 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. O intervalo de células já estará preenchido.

4. Selecione Fórmula Personalizada é da lista suspensa Critérios.

5. Digite a fórmula.

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

6. Selecione um 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 Salvar.

9. Clique em B3 para ver o texto de ajuda da Validação

10. Tipo FRI-123
11. Se você selecionou Mostrar aviso, a seguinte mensagem aparecerá.

12. Alternativamente, se você selecionou Rejeitar entrada em dados inválidos, você será impedido de inserir os dados e a seguinte mensagem aparecerá na tela.

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