Autor:
John Pratt
Data De Criação:
12 Lang L: none (month-010) 2021
Data De Atualização:
3 Poderia 2024
Contente
Outras SeçõesEste 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 para isso.
Passos
Método 1 de 2: usando a fórmula CountIf
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.
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.
Clique no Fórmulas guia 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.
Clique Definir o Nome na barra de ferramentas. Você pode encontrar esta opção no meio da faixa "Fórmulas". Isso abrirá uma nova janela pop-up e permitirá que você atribua um nome à sua lista.
Tipo Lista1 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".
Clique Está bem na janela pop-up. Isso confirmará sua ação e nomeará sua lista.
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.
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.
Clique no Casa guia na barra de ferramentas. Esta é a primeira guia no canto superior esquerdo da barra de ferramentas. Ele abrirá suas ferramentas básicas de planilha na barra de ferramentas.
Clique 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.
Clique 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.
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.
- Em janelas, você o encontrará na parte inferior da lista de regras, na caixa "Selecionar um tipo de regra".
- Em Mac, selecione Clássico na lista suspensa "Estilo" na parte superior da janela pop-up. Em seguida, encontre essa opção no segundo menu suspenso abaixo do menu Estilo.
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.
Tipo = countif (Lista2, 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.
- Substituir A1 na fórmula com o número da primeira célula da 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 = contagemif (Lista2, D5) = 1.
- Se você deu um nome diferente para sua segunda lista, certifique-se de substituir Lista2 na fórmula com o nome real de sua própria lista.
- Alternativamente, altere a fórmula para = countif (Lista2, A1) = 0 se você quiser marcar as células que não aparecem na segunda lista.
Tipo = countif (Lista1, B1) = 1 na barra de fórmulas (opcional). Se você quiser 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.
- Substituir Lista1 com o nome de sua primeira lista, e B1 com a primeira célula da sua segunda lista.
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.
- Em 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".
- Em Mac, selecione um formato predefinido no menu suspenso "Formatar com" na parte inferior. Você também pode selecionar formato personalizado aqui para selecionar manualmente um preenchimento de fundo e estilos de fonte.
Clique Está bem 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 na 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
Abra sua planilha do Excel. Encontre o arquivo Excel com as listas que deseja comparar e clique duas vezes no nome do arquivo ou ícone para abrir a planilha no Microsoft Excel.
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 de 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.
Tipo = 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.
Selecione o primeiro item da segunda lista. Sem fechar o parêntese da fórmula, clique no primeiro item da segunda lista. Isso irá inserir a primeira célula da sua segunda lista na fórmula.
Digite um , 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.
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.
Digite um , vírgula na fórmula. Isso bloqueará o intervalo de comparação em sua fórmula.
Tipo 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ê quiser que sua fórmula retorne o valor da coluna ao lado de sua primeira lista, digite 2 aqui.
Digite um , vírgula na fórmula. Isso bloqueará o número do índice da coluna (1) na fórmula VLookup.
Tipo FALSO 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.
- Ao invés de FALSO você pode usar 0, é exatamente o mesmo.
- Como alternativa, você pode digitar VERDADEIRO ou 1 se você deseja pesquisar uma correspondência aproximada.
Tipo ) 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).
pressione ↵ Entre ou ⏎ Retorno no 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ê está pesquisando "John" na primeira lista e agora vê "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.
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.
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 de 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."
