Este wikiHow ensina como usar a formatação condicional para comparar todas as células em duas listas separadas em uma planilha do Excel e marcar as células que aparecem em ambas as listas. Este recurso está disponível apenas na versão desktop do Excel, e o aplicativo móvel não oferece suporte a ele.
Passos
Método 1 de 2: usando a fórmula CountIf
Etapa 1. Abra a planilha do Excel que deseja editar
Encontre o arquivo de planilha com as listas que deseja comparar e clique duas vezes no arquivo para abri-lo no Microsoft Excel.
Etapa 2. Selecione sua primeira lista
Clique na primeira célula da primeira lista e arraste o mouse até a última célula da lista para selecionar todo o intervalo de dados.
Etapa 3. Clique na guia Fórmulas na faixa da barra de ferramentas
Você pode encontrar essa guia acima da barra de ferramentas na parte superior da planilha. Isso abrirá suas ferramentas de fórmula na faixa da barra de ferramentas.
Etapa 4. Clique em Definir nome na barra de ferramentas
Você pode encontrar esta opção no meio da faixa de opções "Fórmulas". Isso abrirá uma nova janela pop-up e permitirá que você atribua um nome à sua lista.
Etapa 5. Digite List1 no campo Nome
Clique no campo de texto na parte superior da janela pop-up e insira um nome de lista aqui.
- Posteriormente, você pode usar esse nome para inserir sua lista na fórmula de comparação.
- Como alternativa, você pode dar à sua lista um nome diferente aqui. Por exemplo, se esta for uma lista de locais, você pode chamá-la de "locais1" ou "localLista".
Etapa 6. Clique em OK na janela pop-up
Isso irá confirmar sua ação e nomear sua lista.
Etapa 7. Nomeie sua segunda lista como Lista2
Siga as mesmas etapas da primeira lista e dê um nome à sua segunda lista. Isso permitirá que você use rapidamente esta segunda lista em sua fórmula de comparação posteriormente.
Você pode dar à lista o nome que desejar. Lembre-se ou anote o nome que você deu a cada uma de suas listas aqui
Etapa 8. Selecione sua primeira lista
Clique na primeira célula da primeira lista e arraste para baixo para selecionar todo o intervalo de dados.
Certifique-se de que sua primeira lista esteja selecionada antes de iniciar a configuração da formatação condicional
Etapa 9. Clique na guia Home na barra de ferramentas
Esta é a primeira guia no canto superior esquerdo da faixa de opções da barra de ferramentas. Ele abrirá suas ferramentas básicas de planilha na barra de ferramentas.
Etapa 10. Clique em Formatação condicional na barra de ferramentas
Esta opção se parece com um pequeno ícone de planilha com algumas células destacadas em vermelho e azul. Ele abrirá um menu suspenso com todas as opções de formatação.
Etapa 11. Clique em Nova Regra no menu suspenso
Ele abrirá uma nova janela pop-up e permitirá que você configure manualmente uma nova regra de formatação para o intervalo selecionado.
Etapa 12. Selecione a opção "Usar uma fórmula para determinar quais células formatar"
Esta opção permitirá que você digite manualmente uma fórmula de formatação para comparar suas duas listas.
- Sobre janelas, você o encontrará na parte inferior da lista de regras, na caixa "Selecionar um tipo de regra".
- Sobre Mac, selecione Clássico no menu suspenso "Estilo" na parte superior da janela pop-up. Em seguida, encontre essa opção no segundo menu suspenso abaixo do menu Estilo.
Etapa 13. Clique no campo da fórmula na janela pop-up
Você pode inserir qualquer fórmula válida do Excel aqui para configurar uma regra de formatação condicional.
Etapa 14. Digite = countif (List2, A1) = 1 na barra de fórmulas
Esta fórmula examinará suas duas listas e marcará todas as células da primeira lista que também aparecem na segunda lista.
- Substitua A1 na fórmula pelo número da primeira célula de sua primeira lista.
- Por exemplo, se a primeira célula de sua primeira lista for a célula D5, sua fórmula será semelhante a = countif (Lista2, D5) = 1.
- Se você deu um nome diferente para sua segunda lista, certifique-se de substituir Lista2 na fórmula pelo nome real de sua própria lista.
- Como alternativa, altere a fórmula para = countif (List2, A1) = 0 se você quiser marcar as células que não aparecem na segunda lista.
Etapa 15. Digite = countif (List1, B1) = 1 na barra de fórmulas (opcional)
Se você deseja encontrar e marcar as células em seu segundo lista que também aparece na primeira lista, use esta fórmula em vez da primeira.
Substitua List1 pelo nome de sua primeira lista e B1 pela primeira célula de sua segunda lista
Etapa 16. Selecione um formato personalizado para marcar as células (opcional)
Você pode selecionar uma cor de preenchimento de plano de fundo personalizada e diferentes estilos de fonte para marcar as células que sua fórmula encontra.
- Sobre janelas, Clique no Formato botão na parte inferior direita da janela pop-up. Você pode selecionar uma cor de fundo na guia "Preenchimento" e estilos de fonte na guia "Fonte".
- Sobre Mac, selecione um formato predefinido na lista suspensa "Formatar com" na parte inferior. Você também pode selecionar formato personalizado aqui para selecionar manualmente um preenchimento de plano de fundo e estilos de fonte.
Etapa 17. Clique em OK na janela pop-up
Isso confirmará e aplicará sua fórmula de comparação. Todas as células da primeira lista que também aparecem na segunda lista serão marcadas com a cor e a fonte selecionadas.
- Por exemplo, se você selecionar um preenchimento vermelho claro com texto vermelho escuro, todas as células recorrentes ficarão com essa cor em sua primeira lista.
- Se você usar a segunda fórmula acima, a formatação condicional marcará as células recorrentes em sua segunda lista em vez da primeira.
Método 2 de 2: usando a fórmula VLookup
Etapa 1. Abra sua planilha do Excel
Encontre o arquivo Excel com as listas que deseja comparar e clique duas vezes no nome ou ícone do arquivo para abrir a planilha no Microsoft Excel.
Etapa 2. Clique na célula vazia ao lado do primeiro item da segunda lista
Encontre sua segunda lista na planilha e clique na célula vazia ao lado do primeiro item da lista no topo.
- Você pode inserir sua fórmula VLookup aqui.
- Como alternativa, você pode selecionar qualquer célula vazia em sua planilha. Esta célula apenas tornará mais conveniente para você ver sua comparação ao lado de sua segunda lista.
Etapa 3. Digite = vlookup (na célula vazia
A fórmula VLookup permitirá que você compare todos os itens em duas listas separadas e veja se um valor é uma repetição ou um novo valor.
Não feche os parênteses da fórmula até que sua fórmula esteja completa
Etapa 4. Selecione o primeiro item da segunda lista
Sem fechar os parênteses da fórmula, clique no primeiro item da segunda lista. Isso irá inserir a primeira célula da sua segunda lista na fórmula.
Etapa 5. Digite a, vírgula na fórmula
Depois de selecionar a primeira célula da segunda lista, digite uma vírgula na fórmula. Você poderá selecionar seu intervalo de comparação a seguir.
Etapa 6. Mantenha pressionado e selecione sua primeira lista inteira
Isso irá inserir o intervalo de células de sua primeira lista na segunda parte da fórmula VLookup.
Isso permitirá que você pesquise na primeira lista o item selecionado de sua segunda lista (o primeiro item no topo da segunda lista) e retorne se for uma repetição ou um novo valor
Etapa 7. Digite a, vírgula na fórmula
Isso bloqueará o intervalo de comparação em sua fórmula.
Etapa 8. Digite 1 na fórmula após a vírgula
Este número representa o número do índice da coluna. Ele solicitará que a fórmula VLookup pesquise a coluna da lista real em vez de uma coluna diferente ao lado dela.
Se você deseja que sua fórmula retorne o valor da coluna ao lado de sua primeira lista, digite 2 aqui
Etapa 9. Digite a, vírgula na fórmula
Isso bloqueará o número do índice da coluna (1) na fórmula VLookup.
Etapa 10. Digite FALSE na fórmula
Isso pesquisará na lista uma correspondência exata do item de pesquisa selecionado (o primeiro item no topo da segunda lista) em vez de correspondências aproximadas.
- Em vez de FALSE, você pode usar 0, é exatamente o mesmo.
- Como alternativa, você pode digitar VERDADEIRO ou 1 se quiser pesquisar uma correspondência aproximada.
Passo 11. Digite) no final para fechar a fórmula
Agora você pode executar sua fórmula e ver se o item de pesquisa selecionado em sua segunda lista é uma repetição ou um novo valor.
Por exemplo, se sua segunda lista começa em B1, e sua primeira lista vai das células A1 a A5, sua fórmula será semelhante a = vlookup (B1, $ A $ 1: $ A $ 5, 1, falso).
Etapa 12. Pressione ↵ Enter ou ⏎ Retorne em seu teclado.
Isso executará a fórmula e procurará em sua primeira lista o primeiro item de sua segunda lista.
- Se este for um valor de repetição, você verá o mesmo valor impresso novamente na célula da fórmula.
- Se este for um novo valor, você verá " #N / D"impresso aqui.
- Por exemplo, se você estiver procurando "John" na primeira lista e agora vir "John" na célula da fórmula, é um valor de repetição que aparece em ambas as listas. Se você vir "# N / A", é um novo valor na segunda lista.
Etapa 13. Selecione sua célula de fórmula
Depois de executar sua fórmula e ver os resultados do primeiro item da lista, clique na célula da fórmula para selecioná-la.
Etapa 14. Clique e arraste para baixo o ponto verde na parte inferior direita da célula
Isso expandirá sua célula de fórmula ao longo da lista e aplicará a fórmula a todos os itens de sua segunda lista.
- Dessa forma, você pode comparar cada item da segunda lista com a primeira lista inteira.
- Isso pesquisará sua primeira lista para cada item em sua segunda lista individualmente e mostrará o resultado próximo a cada célula separadamente.
- Se você quiser ver um marcador diferente para novos valores em vez de "# N / A", use esta fórmula: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, falso), "Novo valor"). Isso imprimirá "Novo valor" para novos valores em vez de "# N / A."
