Encontrando discrepâncias no Excel – 5 técnicas fáceis explicadas

Ao trabalhar com grandes conjuntos de dados no Microsoft Excel, é inevitável encontrar discrepâncias nos dados. Identificar e corrigir essas inconsistências é fundamental para garantir relatórios e análises precisos.

As boas notícias? O Excel vem com uma variedade de ferramentas e técnicas para ajudá-lo a encontrar e resolver esses problemas. Neste artigo, você aprenderá sobre alguns dos métodos mais eficazes. Isso inclui filtragem, formatação condicional e uso de fórmulas avançadas.

Ao incorporar essas técnicas em seu fluxo de trabalho diário, você se tornará mais eficiente no gerenciamento e na análise de seus dados. Em última análise, levando a uma melhor tomada de decisão e melhores resultados para seus projetos.

Índice

• Compreendendo discrepâncias no Excel
• 5 maneiras de encontrar discrepâncias no Excel
• #1 – Encontrando discrepâncias por meio de revisão manual no Excel
• #2 – Usando Filtro no Excel para Detectar e Corrigir Dados Inconsistentes
• 3# – Destacando discrepâncias com formatação condicional
• #4 – Encontrar discrepâncias com funções avançadas do Excel
  • Funções SE e IS
  • VLOOKUP, HLOOKUP & XLOOKUP
  • CORRESPONDER
• #5 – Usando suplementos do Excel para detecção de discrepância
• Como você pode criar regras de validação de dados para evitar discrepâncias?
• Nossa palavra final

Compreendendo discrepâncias no Excel

Discrepâncias em linhas e colunas podem resultar de erros humanos de entrada, entradas duplicadas ou irregularidades na formatação de dados.

Observe os dados a seguir. Você notará que o gênero de Walter White e Wayne Cena está escrito incorretamente – Mael em vez de Masculino. Na verdade, é fácil perceber e corrigir os erros nesse cenário. Mas esse não é o caso de planilhas contendo milhares de linhas e colunas.

Para localizar e gerenciar essas discrepâncias, você pode usar os seguintes recursos do Excel :

• Filtros: aplique filtros a colunas específicas para visualizar valores exclusivos e identificar rapidamente dados inconsistentes.
• Formatação condicional: use regras de formatação para destacar células que atendem a critérios específicos ou dados incompatíveis entre colunas.
• Funções avançadas do Excel: utilize funções como MATCH e VLOOKUP para comparar conjuntos de dados e identificar discrepâncias entre eles.

Ao entender como usar esses recursos no Excel, você pode identificar e corrigir com eficiência as discrepâncias em seus conjuntos de dados.

5 maneiras de encontrar discrepâncias no Excel

Existem 5 maneiras principais que gostamos de lidar com discrepâncias no Excel:

1. revisão manual
2. Filtragem
3. Formatação condicional
4. Funções Avançadas do Excel
5. Suplementos do Excel

Vamos percorrer cada um deles para que você possa descobrir a melhor maneira que mais lhe convier.

#1 – Encontrando discrepâncias por meio de revisão manual no Excel

Ao trabalhar com o Excel , uma revisão manual pode ser um método útil para identificar discrepâncias em seus dados. Esse processo requer um exame cuidadoso dos conjuntos de dados para detectar quaisquer inconsistências ou erros.

Aqui estão algumas etapas que você pode seguir para realizar uma revisão manual:

• Comece entendendo claramente os conjuntos de dados com os quais você está trabalhando. Familiarize-se com as linhas, colunas e formatação.
• Percorra os dados e preste muita atenção às informações apresentadas em cada célula. Você pode usar ferramentas como destaque de célula com cor de preenchimento, formatação de texto ou formatação condicional para facilitar a leitura dos dados.
• Compare uma linha com outra ou uma coluna com outra. Procure informações incompatíveis, entradas duplicadas etc. que possam causar imprecisões em suas planilhas do Excel.
• Ao identificar discrepâncias, crie uma lista ou use comentários para documentá-las. Mais tarde, estes podem servir como referências importantes para corrigir os problemas.

Ao revisar manualmente os dados em busca de discrepâncias em uma planilha do Excel, paciência é a chave. Este processo é demorado. Mas é um bom ponto de partida para identificar possíveis erros.

#2 – Usando Filtro no Excel para Detectar e Corrigir Dados Inconsistentes

Uma das maneiras mais fáceis de detectar dados inconsistentes é usando o filtro do Excel .

Com Filter, isolaremos os dados incorretos na coluna Gender no seguinte conjunto de dados:

1. Selecione Classificar e filtrar na guia Início .

2. Escolha Filtro – certifique-se de que seu cursor esteja no intervalo dos dados.

3. Clique na seta da lista suspensa na coluna com dados inconsistentes.

4. Agora, desmarque todos os dados correspondentes corretos. No nosso caso, é Male . Em seguida, clique em OK para salvar as alterações.

5. Sua planilha do Excel agora mostrará apenas linhas com valores incorretos. Para corrigi-los, clique em Localizar e selecionar na guia Início .

6. Clique em Substituir quando a lista suspensa aparecer.

7. Digite o valor incorreto em Localizar e o correto em Substituir por . Em seguida, clique em Substituir tudo .

8. Aparecerá uma caixa de diálogo confirmando as alterações. Clique em OK para continuar.

3# – Destacando discrepâncias com formatação condicional

Outra maneira de identificar diferenças de coluna e linha é usando a formatação condicional.

Você pode acessar regras como realçar células, superior/inferior, barras de dados, escalas de cores e conjuntos de ferro. Além disso, você pode criar uma nova regra e gerenciar ou limpar regras anteriores.

Usaremos uma tabela de exemplo para mostrar o poder da formatação condicional:

1. Selecione sua tabela ou linhas e colunas preferidas. Estamos escolhendo a Coluna A (Empresa) e a Coluna B (País). Em seguida, selecione Formatação Condicional na guia Início .

2. Selecione um tipo de regra e a regra. Estamos escolhendo regras de célula de destaque e valores duplicados…

3. Agora, temos que selecionar se deseja realçar valores duplicados ou exclusivos e a cor de preenchimento e fonte para formatar as células. Feito isso, clique em OK .

4. Todos os valores duplicados agora são realçados com preenchimento vermelho claro com texto vermelho escuro.

#4 – Encontrar discrepâncias com funções avançadas do Excel

Funções SE e IS

Para comparar células e identificar diferenças, você pode utilizar as funções IF e IS.

Por exemplo, usamos a fórmula =IF(A2=B2,"MATCH","NOT MATCHED")para comparar as células A2 e B2. E arrastamos a fórmula para aplicá-la a outras células. Portanto, células idênticas estão retornando MATCH, caso contrário , NOT MATCHED .

Você pode combinar IF com funções IS como ISNUMBER ou ISTEXT para verificar tipos de dados específicos. Por exemplo, a fórmula =IF(ISNUMBER(B2), "Number", "NaN")retornará Número na coluna c se B2 for numérico e NaN caso contrário.

VLOOKUP, HLOOKUP & XLOOKUP

As funções VLOOKUP (Vertical) e HLOOKUP (Horizontal) são úteis ao comparar valores em diferentes colunas ou linhas de duas tabelas. Para fazer isso, use a seguinte fórmula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) ou =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Substitua lookup_value pelo valor que deseja encontrar na outra tabela, table_array pelo intervalo de células na segunda tabela, col_index_num ou row_index_num pelo número de índice da coluna ou linha da qual deseja retornar um valor e [range_lookup] por FALSE para correspondências exatas ou TRUE para uma correspondência aproximada.

No tutorial a seguir, estamos usando a função VLOOKUP na coluna E para comparar os nomes dos funcionários na coluna A e na coluna D para retirar seus salários da coluna B se uma correspondência for encontrada.

XLOOKUP é uma versão atualizada de VLOOKUP e HLOOKUP disponível no Excel 2021 e Excel 365. A fórmula XLOOKUP é: =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

Substitua lookup_value pelo valor que deseja encontrar na outra tabela, lookup_array pelo intervalo de células a pesquisar na segunda tabela, return_array pelo array do qual deseja retornar um valor e [if_nout_found] por um valor de texto se não houver correspondência valores são encontrados.

Aqui está uma amostra rápida de XLOOKUP com as mesmas tabelas:

CORRESPONDER

A função CORRESP também pode ser usada para comparar duas listas quanto a discrepâncias.

A função CORRESP, =MATCH(lookup_value, lookup_array, [match_type]), procura um item especificado em um intervalo e retorna a posição relativa do item nesse intervalo.

Aqui, estamos encontrando a posição de melancias na matriz A2:A8 usando MATCH. Estamos usando match_type como 0 para encontrar o primeiro valor que é exatamente igual ao lookup_value .

#5 – Usando suplementos do Excel para detecção de discrepância

O Excel oferece vários suplementos e ferramentas integradas que podem ajudá-lo a detectar e analisar discrepâncias com mais eficiência. Nesta seção, discutiremos alguns desses suplementos úteis.

Primeiro, considere usar o suplemento Consulta de Planilha . Isso pode ajudá-lo a comparar duas pastas de trabalho e destacar as diferenças célula por célula. Para habilitar o suplemento Consulta de Planilha:

1. Clique na guia Arquivo .

2. Selecione Opções e clique na categoria Suplementos .

3. Na caixa Gerenciar , selecione Suplementos COM e clique em Ir .

4. Marque a caixa Consulta de Planilha e clique em OK .

5. Uma vez ativado, navegue até a guia Inquire para usar o comando Compare Files .

Outra ferramenta útil para detecção de discrepância é o Analysis ToolPak . Este suplemento fornece funções estatísticas avançadas e ferramentas de análise de dados, que podem ser úteis ao analisar discrepâncias.

Para habilitar o Analysis ToolPak, siga as mesmas etapas para habilitar o suplemento Spreadsheet Inquire. Mas selecione Suplementos do Excel na caixa Gerenciar e marque a caixa Ferramentas de análise .

Em resumo, o Excel oferece vários suplementos para ajudá-lo a detectar e lidar com discrepâncias em seus dados. Você pode melhorar a precisão de seus dados familiarizando-se com essas ferramentas e fórmulas.

Como você pode criar regras de validação de dados para evitar discrepâncias?

Para criar regras de validação de dados no Excel que possam ajudar a evitar discrepâncias, siga estas etapas:

1. Selecione as células que deseja restringir. Isso pode ser uma única célula, um intervalo de células ou uma coluna inteira.

2. Vá para a guia Dados na barra de ferramentas e clique no botão Validação de dados (marcado por duas caixas horizontais, uma marca de seleção verde e um círculo cruzado vermelho).

3. Na janela Validação de dados, verifique se você está na guia Configurações . Aqui, você terá a oportunidade de definir seus critérios de validação com base em suas necessidades.

Existem vários tipos de critérios de validação disponíveis no Excel, alguns dos quais incluem:

• Número inteiro – permite apenas números inteiros dentro de um intervalo especificado.
• Decimal – permite apenas números decimais dentro de um intervalo especificado.
• Lista – restringe a entrada a uma lista predefinida de valores aceitáveis.
• Data – requer datas dentro de um intervalo de datas específico.
• Hora – aceita apenas horas dentro de um intervalo de tempo específico.
• Comprimento do texto – impõe restrições ao comprimento do texto inserido.
• Personalizado – permite criar regras de validação personalizadas usando fórmulas do Excel.

Depois de selecionar os critérios de validação apropriados, especifique os parâmetros conforme necessário. Por exemplo, se você escolher Número Inteiro, deverá definir o Mínimo e o Máximo com um validador de intervalo de dados como entre, igual a, menor que, etc.

Além dos critérios de validação, você também pode configurar mensagens de erro personalizadas e dicas de entrada para ajudar os usuários a entender e aderir às suas regras de validação. Para fazer isso, mude para as guias Mensagem de entrada e Alerta de erro para inserir as mensagens desejadas.

Ao implementar regras de validação de dados, você reduz significativamente a probabilidade de inconsistências de dados e melhora a precisão geral de suas planilhas do Excel.

Nossa palavra final

Neste artigo, você aprendeu vários métodos para encontrar e resolver discrepâncias em seus dados do Excel. Vamos recapitular essas estratégias-chave:

• Usando menus suspensos de filtro em colunas para identificar valores exclusivos e dados inconsistentes.
• Empregando formatação condicional em células selecionadas para destacar discrepâncias usando vários modelos de regras.
• Aplicando funções avançadas do Excel como MATCH , VLOOKUP , XLOOKUP , etc. funções para verificar dados cruzados em duas listas ou tabelas.
• Usando suplementos do Excel para detectar discrepâncias.
• Configurando regras de validação de dados para evitar erros nos dados

Com essas técnicas em seu conjunto de habilidades do Excel, seus dados serão mais precisos e confiáveis.

Lembre-se de praticar esses métodos regularmente para se tornar mais eficiente na detecção de discrepâncias em suas planilhas.

Continue aprimorando suas habilidades inscrevendo-se em nosso e continue explorando novos recursos e funcionalidades para se tornar ainda mais proficiente no Excel.