Muitas vezes, os usuários do Excel têm uma tarefa de comparar duas tabelas ou listas para identificar diferenças nelas ou em itens ausentes. Cada usuário lida com esta tarefa à sua maneira, mas a maioria das vezes uma quantidade bastante grande de tempo é gasta na resolução da questão especificada, já que nem todas as abordagens para esse problema são racionais. Ao mesmo tempo, existem vários algoritmos de ações comprovadas que permitem comparar listas ou matrizes de tabela em tempo razoamente curto com um esforço mínimo considerável. Vamos considerar detalhes essas opções.
Da mesma forma, você pode comparar os dados nas tabelas que estão localizadas em folhas diferentes. Mas, neste caso, é desejável que as linhas neles estejam numeradas. O resto do procedimento de comparação é quase exatamente o mesmo que descrito acima, além do fato de que, ao fazer a fórmula, é necessário alternar entre as folhas. No nosso caso, a expressão terá a seguinte forma:
= B2 = List2! B2
Ou seja, como vemos, na frente das coordenadas de dados, que estão localizadas em outras folhas diferentes de onde o resultado de comparação é exibido, o número da folha e uma marca de exclamação são indicados.
Método 2: Seleção de grupos de células
A comparação pode ser feita usando a ferramenta para a separação de grupos de células. Com ele, você também pode comparar apenas listas sincronizadas e ordenadas. Além disso, neste caso, as listas devem estar localizadas próximas umas das outras em uma folha.
- Selecione matrizes comparadas. Vá para a guia "Home". Em seguida, clique no ícone "Localizar e selecionar", que está localizado na fita na barra de ferramentas de edição. Há uma lista na qual você deve selecionar a posição "Seleção do grupo de células ...".
Além disso, a seleção do grupo de células pode ser alcançada de outra maneira. Essa opção será especialmente útil para os usuários que possuem a versão do programa anterior ao Excel 2007, uma vez que o método através do botão "Localizar e selecionar" não suporta esses aplicativos. Selecione Arrays que desejem comparar e clique na tecla F5.
- Uma pequena janela de transição é ativada. Clique no botão "Destaque ..." no canto inferior esquerdo.
- Depois disso, quaisquer duas das opções acima que você tenha eleito, a janela de seleção de grupos celulares é lançada. Instale o interruptor para a posição "Destaque On Line". Clique no botão "OK".
- Como podemos ver, depois disso, os valores inconsistentes das linhas serão destacados com uma tonalidade diferente. Além disso, como pode ser julgado o conteúdo da linha de fórmula, o programa fará uma ativa das células nas linhas não coincididas indicadas.
Método 3: Formatação Condicional
Você pode fazer uma comparação aplicando o método de formatação condicional. Como no método anterior, as áreas comparadas devem estar em uma única folha de trabalho do Excel e ser sincronizadas entre si.
- Primeiro de tudo, escolhemos que região tabular será considerada a principal, e no que procurar a diferença. Por último vamos fazer na segunda mesa. Portanto, alocamos uma lista de trabalhadores nele. Movendo-se para a guia "Home", clique no botão "Formatação Condicional", que tem um local na fita no bloco "Estilos". Na lista suspensa, passe pelo item "Gerenciamento de Regras".
- A janela do Dispatcher Gerente de Regras é ativada. Clicamos no botão "Criar regra".
- Na janela em execução, selecionamos a posição "Use Formula". No campo "Format Cell", escreva uma fórmula contendo os endereços das primeiras células das faixas de colunas comparadas, separadas pelo sinal "não igual" (). Logo antes desta expressão desta vez vai suportar o sinal "=". Além disso, para todas as coordenadas de coluna nesta fórmula, você precisa aplicar um abordagem absoluta. Para fazer isso, alocamos a fórmula com o cursor e clicamos na tecla F4 três vezes. Como podemos ver, o cifrão apareceu perto de todos os endereços da coluna, o que significa transformar os links para absoluto. Para o nosso caso particular, a fórmula assumirá a seguinte forma:
= $ A2 $ D2
Nós registramos esta expressão no campo acima. Depois disso, clicamos no botão "Format ...".
- A janela "Formato de célula" é ativada. Nós vamos para a guia "Preencher". Aqui na lista de cores, pare a escolha na cor, que queremos pintar esses elementos onde os dados não corresponderão. Clique no botão "OK".
- Retornando à janela de regra de formatação, clique no botão "OK".
- Após a mudança automática para a janela "Gerenciador de regras", clique no botão "OK" e nele.
- Agora, na segunda tabela, os elementos que têm dados que são inconsistentes com os valores correspondentes da primeira área de tabela serão destacados na cor selecionada.
Há outra maneira de aplicar a formatação condicional para executar a tarefa. Como as opções anteriores, requer a localização de ambas as áreas comparadas em uma folha, mas em contraste com os métodos previamente descritos, a condição de sincronização ou os dados de classificação não serão obrigatórios, o que distingue essa opção anteriormente descrita.
- Nós produzimos a seleção de áreas que você precisa para comparar.
- Realizamos a transição para a guia chamada "Home". Clicamos no botão "Condicional Formating". Na lista ativada, selecione as regras da posição "para a alocação de células". No próximo menu, faça a posição "Repetir valores".
- Os valores duplicados são lançados. Se você for feito corretamente, nesta janela permanece apenas para clicar no botão "OK". Embora, se desejar, no campo apropriado desta janela, você pode escolher outra cor da seleção.
- Depois de produzirmos a ação especificada, todos os elementos repetitivos serão destacados na cor selecionada. Esses elementos que não coincidem permanecerão pintados em sua cor original (por padrão padrão). Assim, você pode ver imediatamente visualmente para ver qual a diferença entre matrizes.
Se você quiser, você pode, pelo contrário, pintar os elementos inconsistentes e os indicadores que correspondem, saem com o preenchimento da cor anterior. Ao mesmo tempo, o algoritmo de ação é quase o mesmo, mas nas configurações dos valores duplicados no primeiro campo, em vez do parâmetro "repetido", selecione o parâmetro "exclusivo". Depois disso, clique no botão "OK".
Assim, é precisamente esses indicadores que não coincidem.
Lição: Formatação condicional no Excel
Método 4: Fórmula abrangente
Além disso, compare os dados com a ajuda de uma fórmula complexa, cuja base é a função do medidor. Usando esta ferramenta, você pode calcular quanto cada elemento da coluna selecionada da segunda tabela é repetida no primeiro.
Os conselhos de operação referem-se ao grupo estatístico de funções. Sua tarefa é contar o número de células, os valores em que satisfazem a condição especificada. A sintaxe deste operador tem esse tipo:
= Cronogramas (intervalo; critério)
O argumento "intervalo" é o endereço da matriz, que calcula os valores coincidentes.
O argumento "Critérios" especifica a condição da coincidência. No nosso caso, serão as coordenadas das células específicas da primeira área tabular.
- Alocamos o primeiro elemento da coluna adicional em que o número de correspondências será calculado. Em seguida, clique no ícone "Inserir função".
- O assistente de funções começa. Vá para a categoria "estatística". Encontre na lista o nome "Countecles". Após sua seleção, clique no botão "OK".
- A janela Argumentos do operador começou a funcionar. À medida que vemos, os nomes dos campos nesta janela correspondem aos nomes dos argumentos.
Instale o cursor no campo "Range". Depois disso, segurando o botão esquerdo do mouse, aloque todos os valores da coluna com os nomes da segunda tabela. Como você pode ver, as coordenadas caem imediatamente no campo especificado. Mas para nossos propósitos, esse endereço deve ser feito por absoluto. Para fazer isso, destaque as coordenadas no campo e clique na tecla F4.
Como você pode ver, a referência assumiu a forma absoluta, que é caracterizada pela presença de sinais de dólar.
Em seguida, vá para o campo "Critério", instalando o cursor. Clique no primeiro elemento com os sobrenomes no primeiro intervalo tabular. Nesse caso, deixe o link relativo. Depois de ser exibido no campo, você pode clicar no botão "OK".
- O resultado é de saída para o elemento da folha. É igual ao número "1". Isso significa que na lista de nomes da segunda tabela, o nome "Grinev V. P.", que é o primeiro na lista da primeira matriz de tabela, ocorre uma vez.
- Agora precisamos criar uma expressão semelhante e para todos os outros elementos da primeira tabela. Para fazer isso, eu executarei copiando, usando o marcador de enchimento, como já fizemos antes. Colocamos o cursor na parte inferior direita do elemento de folha, que contém a função do medidor e depois de convertê-lo para o marcador de enchimento, clamp o botão esquerdo do mouse e puxe o cursor para baixo.
- Como podemos ver, o programa fez uma computação de coincidências, comparando cada célula da primeira tabela com dados, que estão localizados no intervalo de segunda tabela. Em quatro casos, o resultado foi "1" e em dois casos - "0". Ou seja, o programa não conseguiu encontrar os dois valores na segunda tabela que estão disponíveis na primeira matriz de tabela.
Claro, esta expressão para comparar os indicadores da tabela, é possível aplicar na forma existente, mas é possível melhorá-lo.
Vamos fazer os valores que existem na segunda tabela, mas não estão disponíveis no primeiro, foram exibidos como uma lista separada.
- Primeiro de tudo, recicamos nossa fórmula, mas faremos um dos argumentos do operador se. Para fazer isso, destacamos a primeira célula na qual o operador é o operador. Na fórmula string na frente dela, adicionamos a expressão "se" sem citações e abrir o suporte. Além disso, para que fosse mais fácil trabalhar, alocar as fórmulas na linha de fórmula e clique no ícone "Inserir função".
- A janela Argumentos de função é aberta se. Como você pode ver, a primeira janela da janela já foi preenchida com a valorização do Conselho do Operador. Mas precisamos adicionar outra coisa neste campo. Nós estabelecemos lá o cursor e a expressão já existente Adicionar "= 0" sem citações.
Depois disso, vá para o campo "Significado se a verdade". Aqui vamos usar outra linha de função aninhada. Digite a palavra "string" sem citações, em seguida, abra colchetes e indique as coordenadas da primeira célula com o sobrenome na segunda tabela, após o que fechamos os colchetes. Especificamente, no nosso caso no campo "Significado se verdadeiro", a seguinte expressão aconteceu:
Linha (d2)
Agora, a string do operador irá relatar funções se o número da linha em que o sobrenome específico estiver localizado, e no caso quando a condição especificada no primeiro campo será executada, a função se esse número será exibido. Clique no botão "OK".
- Como você pode ver, o primeiro resultado é exibido como "mentiras". Isso significa que o valor não satisfaz as condições do operador se. Ou seja, o primeiro sobrenome está presente em ambas as listas.
- Usando o marcador de enchimento, que já está familiarizado para copiar a expressão do operador se estiver em toda a coluna. Como vemos, em duas posições que estão presentes na segunda tabela, mas não no primeiro, a fórmula emite números de linhas.
- Nós recuamos da área da tabela para a direita e preenchemos a coluna numérica em ordem, a partir de 1. O número de números deve corresponder ao número de linhas na segunda tabela Competível. Para acelerar o procedimento de numeração, você também pode usar o marcador de preenchimento.
- Depois disso, destacamos a primeira célula à direita do alto-falante com os números e clique no ícone "Inserir função".
- Assistente abre. Vá para a categoria "estatística" e produza o nome do "menor" nome. Clique no botão "OK".
- A função é a menor, os argumentos dos argumentos foram divulgados, destina-se a retirar o menor valor especificado.
No campo "Array", você deve especificar as coordenadas do intervalo da coluna opcional "O número de correspondências", que anteriormente transformamos usando a função se. Nós fazemos todos os links absolutos.
No campo "K", é indicado que na conta o menor valor deve ser exibido. Aqui indicamos as coordenadas da primeira célula da coluna com a numeração, que recentemente adicionamos. Endereço Deixe parente. Clique no botão "OK".
- O operador exibe o resultado - Número 3. É precisamente o menor da numeração das linhas inconsistentes de matrizes de tabela. Usando o marcador de enchimento, copie a fórmula para o próprio nariz.
- Agora, conhecendo os números das linhas dos elementos incompreensíveis, podemos inserir na célula e seus valores usando a função de índice. Selecione o primeiro elemento da folha contendo a fórmula é o menor. Depois disso, vá para as fórmulas de linha e antes do nome do "menor" adicionar o nome "index" sem citações, abra imediatamente o suporte e coloque um ponto com uma vírgula (;). Em seguida, alocamos o nome do formulário "index" na linha e clique no ícone "Colar Função".
- Depois disso, uma pequena janela é aberta, na qual é necessário determinar se a visualização de referência deve ter uma função de índice ou projetada para trabalhar com matrizes. Precisamos de uma segunda opção. Ele é instalado por padrão, para que nesta janela simplesmente clique no botão "OK".
- A janela Argument está executando a função de índice. Este operador é projetado para gerar um valor localizado em uma matriz específica na linha especificada.
Como você pode ver, o campo "número da linha" já está preenchido com os valores de significado da menor função. De um valor existente, a diferença entre a numeração da folha do Excel e a numeração interna da região tabular. Como vemos, só temos um chapéu nos valores da tabela. Isso significa que a diferença é uma linha. Portanto, adicione "-1" sem citações no campo "número de linha".
No campo "Array", especifique o endereço do valor dos valores da segunda tabela. Ao mesmo tempo, todas as coordenadas fazem absoluta, isto é, colocamos o cifrão antes do método descrito por nós.
Clique no botão "OK".
- Após a retirada, o resultado na tela está alongando a função usando o marcador de preenchimento até o final da coluna para baixo. Como vemos, ambos sobrenomes presentes na segunda tabela, mas não estão disponíveis no primeiro, são removidos em um intervalo separado.
Método 5: Comparação de matrizes em livros diferentes
Ao comparar as faixas em livros diferentes, você pode usar os métodos acima, excluindo essas opções onde a colocação de ambas as áreas tabulares em uma folha é necessária. A principal condição para o procedimento de comparação neste caso é a abertura das janelas de ambos os arquivos ao mesmo tempo. Para versões do Excel 2013 e posterior, bem como para versões para o Excel 2007, não há problemas com a implementação dessa condição. Mas no Excel 2007 e no Excel 2010, a fim de abrir as duas janelas ao mesmo tempo, as manipulações adicionais são necessárias. Como fazê-lo diz em uma lição separada.
Lição: Como abrir o Excel em diferentes janelas
Como você pode ver, há uma série de possibilidades para comparar tabelas uns com os outros. Que tipo de opção de uso depende de onde os dados da tabela relativos entre si estão localizados (em uma folha, em diferentes livros, em diferentes folhas), bem como do usuário quer que essa comparação seja exibida na tela.