Análise de dados do Excel - validação de dados

A Validação de Dados é uma ferramenta muito útil e fácil de usar no Excel com a qual você pode definir validações de dados nos dados inseridos que são inseridos em sua Planilha.

Para qualquer célula da planilha, você pode

  • Exibe uma mensagem de entrada sobre o que precisa ser inserido.
  • Restrinja os valores inseridos.
  • Forneça uma lista de valores para escolher.
  • Exibe uma mensagem de erro e rejeita uma entrada de dados inválida.

Considere o seguinte rastreador de risco que pode ser usado para inserir e rastrear as informações de riscos identificados.

Neste rastreador, os dados inseridos nas colunas a seguir são validados com restrições de dados predefinidas e os dados inseridos são aceitos apenas quando atendem aos critérios de validação. Caso contrário, você receberá uma mensagem de erro.

  • Probability
  • Impact
  • Categoria de Risco
  • Fonte de Risco
  • Status

A coluna Exposição ao risco terá valores calculados e você não pode inserir nenhum dado. Até a colunaS. No. é definido para ter valores calculados que são ajustados mesmo se você excluir uma linha.

Agora, você aprenderá como configurar essa planilha.

Prepare a estrutura para a planilha

Para preparar a estrutura da planilha -

  • Comece com uma planilha em branco.
  • Coloque o cabeçalho na linha 2.
  • Coloque os cabeçalhos das colunas na linha 3.
  • Para os cabeçalhos de coluna Probabilidade, Impacto e Exposição ao Risco -
    • Clique com o botão direito na célula.
    • Clique em Formatar células no menu suspenso.
    • Na caixa de diálogo Formatar células, clique na guia Alinhamento.
    • Digite 90 em Orientação.
  • Mesclar e centralizar as células nas linhas 3, 4 e 5 para cada um dos cabeçalhos das colunas.
  • Bordas de formato para as células nas linhas 2 - 5.
  • Ajuste as larguras das linhas e colunas.

Sua planilha terá a seguinte aparência -

Definir valores válidos para categoria de risco

Nas células M5 - M13, insira os seguintes valores (M5 é o rumo e M6 - M13 são os valores)

Valores de Categoria
Usuários finais
Cliente
Gestão
Cronograma
Cronograma
Meio Ambiente
produtos
Projeto
  • Clique na primeira célula na coluna Categoria de risco (H6).
  • Clique na guia DADOS na faixa de opções.
  • Clique em Validação de dados no grupo Ferramentas de dados.
  • Selecione Validação de dados… na lista suspensa.

A caixa de diálogo Validação de dados é exibida.

  • Clique na guia Configurações.
  • Nos critérios de validação, no Allow: lista suspensa, selecione a opção List.
  • Selecione o intervalo M6: M13 na caixa Fonte: que aparece.
  • Marque as caixas Ignorar em branco e o menu suspenso Na célula que aparecem.

Definir mensagem de entrada para categoria de risco

  • Clique na guia Mensagem de entrada na caixa de diálogo Validação de dados.
  • Verifica a caixa Show input message quando a célula é selecionada.
  • Na caixa sob Título :, digite Categoria de risco:
  • Na caixa em Mensagem de entrada: Escolha a categoria do risco na lista.

Definir alerta de erro para categoria de risco

Para definir o alerta de erro -

  • Clique na guia Alerta de erro na caixa de diálogo Validação de dados.
  • Marque a caixa Mostrar alerta de erro após a inserção de dados inválidos.
  • Selecione Parar em Estilo: lista suspensa
  • Na caixa sob Título :, digite Entrada inválida:
  • Na caixa em Mensagem de erro: digite Escolha um valor na lista suspensa.
  • Clique OK.

Verifique a validação de dados para categoria de risco

Para a primeira célula selecionada na categoria de risco,

  • Os critérios de validação de dados estão definidos
  • A mensagem de entrada está definida
  • Alerta de erro definido

Agora, você pode verificar suas configurações.

Clique na célula para a qual você definiu os critérios de validação de dados. A mensagem de entrada é exibida. O botão suspenso aparece no lado direito da célula.

A mensagem de entrada é exibida corretamente.

  • Clique no botão suspenso no lado direito da célula. A lista suspensa aparece com os valores que podem ser selecionados.

  • Verifique os valores na lista suspensa com aqueles que são usados ​​para criar a lista suspensa.

Ambos os conjuntos de valores correspondem. Observe que se o número de valores for maior, você obterá uma barra de rolagem para baixo no lado direito da lista suspensa.

Selecione um valor na lista suspensa. Ele aparece na célula.

Você pode ver que a seleção de valores válidos está funcionando bem.

Finalmente, tente inserir uma entrada inválida e verifique o alerta de erro.

Digite Pessoas na célula e pressione Enter. A mensagem de erro que você definiu para a célula será exibida.

  • Verifique a mensagem de erro.
  • Você tem a opção de Tentar novamente ou Cancelar. Verifique ambas as opções.

Você definiu com sucesso a validação de dados para a célula.

Note - É muito importante verificar a ortografia e a gramática de suas mensagens.

Definir critérios válidos para a coluna de categoria de risco

Agora, você está pronto para aplicar os critérios de validação de dados a todas as células na coluna Categoria de risco.

Neste ponto, você precisa se lembrar de duas coisas -

  • Você precisa definir os critérios para o número máximo de células que podem ser usadas. Em nosso exemplo, pode variar de 10 a 100 com base em onde a planilha será usada.

  • Você não deve definir os critérios para intervalos indesejados de células ou para a coluna inteira. Isso aumentará desnecessariamente o tamanho do arquivo. Isso é chamado de formatação em excesso. Se você obtiver uma planilha de uma fonte externa, terá que remover o excesso de formatação, o que você aprenderá no capítulo Inquire neste tutorial.

Siga as etapas abaixo -

  • Defina os critérios de validação para 10 células em Categoria de risco.
  • Você pode fazer isso facilmente clicando no canto inferior direito da primeira célula.
  • Segure o símbolo + que aparece e puxe-o para baixo.

A validação de dados é definida para todas as células selecionadas.

Clique na última coluna selecionada e verifique.

A validação de dados para a coluna Categoria de risco está concluída.

Definir valores de validação para fonte de risco

Neste caso, temos apenas dois valores - Interno e Externo.

  • Clique na primeira célula na coluna Fonte de risco (I6)
  • Clique na guia DADOS na Faixa de Opções
  • Clique em Validação de Dados no grupo Ferramentas de Dados
  • Selecione Validação de dados… na lista suspensa.

A caixa de diálogo Validação de dados é exibida.

  • Clique na guia Configurações.
  • Em Critérios de validação, na lista suspensa Permitir :, selecione a opção Lista.
  • Digite Interno, Externo na caixa Fonte: que aparece.
  • Marque as caixas Ignorar em branco e o menu suspenso Na célula que aparecem.

Defina a mensagem de entrada para a origem do risco.

Definir alerta de erro para fonte de risco.

Para a primeira célula selecionada em Fonte de risco -

  • Os critérios de validação de dados estão definidos
  • A mensagem de entrada está definida
  • Alerta de erro definido

Agora, você pode verificar suas configurações.

Clique na célula para a qual você definiu os critérios de validação de dados. A mensagem de entrada é exibida. O botão suspenso aparece no lado direito da célula.

A mensagem de entrada é exibida corretamente.

  • Clique no botão de seta suspensa no lado direito da célula. Uma lista suspensa aparece com os valores que podem ser selecionados.

  • Verifique se os valores são iguais aos digitados - interno e externo.

Ambos os conjuntos de valores correspondem. Selecione um valor na lista suspensa. Ele aparece na célula.

Você pode ver que a seleção de valores válidos está funcionando bem. Finalmente, tente inserir uma entrada inválida e verifique o alerta de erro.

Digite Financeiro na célula e pressione Enter. A mensagem de erro que você definiu para a célula será exibida.

  • Verifique a mensagem de erro. Você definiu com sucesso a validação de dados para a célula.

  • Defina critérios válidos para a coluna Fonte de risco

  • Aplique os critérios de validação de dados às células I6 - I15 na coluna Fonte de risco (ou seja, mesmo intervalo da coluna Categoria de risco).

A validação de dados é definida para todas as células selecionadas. A validação de dados para a coluna Fonte de risco está concluída.

Definir valores de validação para status

  • Repita as mesmas etapas usadas para definir os valores de validação para a Origem do risco.

  • Defina os valores da lista como Aberto, Fechado.

  • Aplique os critérios de validação de dados às células K6 - K15 na coluna Status (ou seja, mesmo intervalo da coluna Categoria de risco).

A validação de dados é definida para todas as células selecionadas. A validação de dados para o status da coluna foi concluída.

Definir valores de validação para probabilidade

Os valores do Índice de probabilidade de risco estão na faixa de 1 a 5, sendo 1 baixo e 5 alto. O valor pode ser qualquer número inteiro entre 1 e 5, ambos inclusivos.

  • Clique na primeira célula na coluna Fonte de risco (I6).
  • Clique na guia DADOS na faixa de opções.
  • Clique em Validação de dados no grupo Ferramentas de dados.
  • Selecione Validação de dados… na lista suspensa.

A caixa de diálogo Validação de dados é exibida.

  • Clique na guia Configurações.
  • Em Critérios de validação, na lista suspensa Permitir :, selecione Número inteiro.
  • Selecione entre em Dados:
  • Digite 1 na caixa em Mínimo:
  • Digite 5 na caixa em Máximo:

Definir mensagem de entrada para probabilidade

Defina Alerta de erro para probabilidade e clique em OK.

Para a primeira célula selecionada em Probabilidade,

  • Os critérios de validação de dados estão definidos.
  • A mensagem de entrada está definida.
  • Alerta de erro definido.

Agora, você pode verificar suas configurações.

Clique na célula para a qual você definiu os critérios de validação de dados. A mensagem de entrada é exibida. Neste caso, não haverá um botão suspenso porque os valores de entrada são definidos para estar em um intervalo e não na lista.

A mensagem de entrada é exibida corretamente.

Insira um número inteiro entre 1 e 5 na célula. Ele aparece na célula.

A seleção de valores válidos está funcionando bem. Finalmente, tente inserir uma entrada inválida e verifique o alerta de erro.

Digite 6 na célula e pressione Enter. A mensagem de erro que você definiu para a célula será exibida.

Você definiu com sucesso a validação de dados para a célula.

  • Defina critérios válidos para a coluna de probabilidade.

  • Aplique os critérios de validação de dados às células E6 - E15 na coluna Probabilidade (ou seja, mesmo intervalo da coluna Categoria de risco).

A validação de dados é definida para todas as células selecionadas. A validação de dados para a coluna Probabilidade está concluída.

Definir valores de validação para impacto

Para definir os valores de validação para Impacto, repita as mesmas etapas que você usou para definir os valores de validação para probabilidade.

Aplique os critérios de validação de dados às células F6 - F15 na coluna Impacto (ou seja, mesmo intervalo da coluna Categoria de risco).

A validação de dados é definida para todas as células selecionadas. A validação de dados para a coluna Impacto está concluída.

Definir a exposição ao risco da coluna com valores calculados

A exposição ao risco é calculada como um produto da probabilidade do risco e do impacto do risco.

Exposição ao risco = probabilidade * impacto

Digite = E6 * F6 na célula G6 e pressione Enter.

0 será exibido na célula G6, pois E6 e F6 estão vazios.

Copie a fórmula nas células G6 - G15. 0 será mostrado nas células G6 - G15.

Como a coluna Exposição ao risco se destina a valores calculados, você não deve permitir a entrada de dados nessa coluna.

  • Selecione as células G6-G15

  • Clique com o botão direito e na lista suspensa que aparece, selecione Formatar células. A caixa de diálogo Formatar células é exibida.

  • Clique na guia Proteção.

  • Marque a opção Locked.

Isso é para garantir que a entrada de dados não seja permitida nessas células. No entanto, isso terá efeito apenas quando a planilha estiver protegida, o que você fará como a última etapa após a planilha estar pronta.

  • Clique OK.
  • Sombreie as células G6-G15 para indicar que são valores calculados.

Formatar valores de número de série

Você pode deixar que o usuário preencha a coluna S. No. No entanto, se você formatar os valores S. No., a planilha parece mais apresentável. Além disso, mostra para quantas linhas a planilha está formatada.

Digite = linha () - 5 na célula B6 e pressione Enter.

1 aparecerá na célula B6. Copie a fórmula nas células B6-B15. Valores 1-10 aparecem.

Sombreie as células B6-B15.

Embrulhar

Você está quase terminando seu projeto.

  • Oculte a coluna M que contém os valores da categoria de dados.
  • Bordas de formato para as células B6-K16.
  • Clique com o botão direito na guia da planilha.
  • Selecione Proteger planilha no menu.

A caixa de diálogo Proteger planilha é exibida.

  • Marque a opção Proteger planilha e conteúdo de células bloqueadas.
  • Digite uma senha em Senha para desproteger a planilha -
    • A senha diferencia maiúsculas de minúsculas
    • A folha protegida não pode ser recuperada se a senha for esquecida
    • É uma boa prática manter uma lista de nomes de planilhas e senhas em algum lugar
  • Em Permitir que todos os usuários desta planilha: marque a caixa Selecionar células desbloqueadas.

Você protegeu as células bloqueadas na coluna Exposição ao risco da entrada de dados e manteve o resto das células desbloqueadas editáveis. Clique OK.

o Confirm Password a caixa de diálogo aparece.

  • Digite a senha novamente.
  • Clique OK.

Sua planilha com o conjunto de Validação de Dados para células selecionadas está pronta para uso.