Métodos para comparar táboas en Microsoft Excel

Moitas veces, os usuarios de Excel están confrontados coa tarefa de comparar dúas táboas ou listas para identificar diferenzas ou elementos en falta neles. Cada usuario xestiona esta tarefa ao seu xeito, pero a maioría das veces gasta un tempo bastante grande para resolver este problema, xa que non todos os enfoques deste problema son racionais. Ao mesmo tempo, existen varios algoritmos de acción comprobados que lle permitirán comparar listas ou matrices de táboas nun tempo bastante curto cun esforzo mínimo. Vexamos máis de cerca estas opcións.

Vexa tamén: Comparación de dous documentos en MS Word

Métodos de comparación

Hai moitas maneiras de comparar espazos de táboa en Excel, pero todos poden ser divididos en tres grandes grupos:

Baséase nesta clasificación que, en primeiro lugar, selecciónanse métodos de comparación e determinan as accións e algoritmos específicos para a tarefa. Por exemplo, ao facer comparacións en diferentes libros, ten que abrir simultaneamente dous ficheiros de Excel.

Ademais, hai que dicir que a comparación de espazos de táboa só ten sentido cando teñen unha estrutura similar.

Método 1: fórmula sinxela

O xeito máis doado de comparar datos en dúas táboas é usar unha fórmula de igualdade simple. Se os datos coinciden, entón dá o valor TRUE e, se non, entón - FALSE. É posible comparar, tanto datos numéricos como texto. A desvantaxe deste método é que pode usarse só se os datos da táboa son ordenados ou ordenados do mesmo xeito, sincronizados e teñen un número igual de liñas. Vexamos como usar este método na práctica no exemplo de dúas táboas colocadas nunha folla.

Polo tanto, temos dúas táboas sinxelas con listas de empregados e os seus salarios. É necesario comparar as listas de empregados e identificar inconsistencias entre as columnas nas que se colocan os nomes.

1. Para iso necesitamos unha columna extra na folla. Introduza o sinal alí "=". A continuación, faga clic no primeiro elemento que se comparará na primeira lista. De novo poñemos o símbolo "=" desde o teclado. A continuación, faga clic na primeira cela da columna, que comparamos na segunda táboa. A expresión é do seguinte tipo:

   = A2 = D2

   

   Aínda que, por suposto, en cada caso as coordenadas serán diferentes, pero a esencia seguirá sendo a mesma.

2. Fai clic no botón Intropara obter resultados de comparación. Como podes ver, ao comparar as primeiras celas de ambas listas, o programa indicou un indicador "TRUE"o que significa coincidencia de datos.



3. Agora necesitamos realizar unha operación similar coas celas restantes de ambas táboas nas columnas que comparamos. Pero pode simplemente copiar a fórmula, o que aforrará significativamente tempo. Este factor é especialmente importante cando se comparan listas cun gran número de liñas.

   O procedemento de copia é máis fácil de realizar usando o mango de enchemento. Poñemos o cursor no canto inferior dereito da cela, onde obtivemos o indicador "TRUE". Ao mesmo tempo, debería converterse nunha cruz negra. Este é o marcador de recheo. Fai clic co botón esquerdo do rato e arrastra o cursor cara a abaixo polo número de liñas dos arrays de táboas comparados.



4. Como vemos, agora na columna adicional móstranse todos os resultados da comparación de datos en dúas columnas de matrices tabulares. No noso caso, os datos non coinciden só nunha liña. Cando se compara, a fórmula deu o resultado "FALSO". Para todas as demais liñas, como podes ver, a fórmula de comparación deu o indicador "TRUE".



5. Ademais, é posible calcular o número de discrepancias utilizando unha fórmula especial. Para iso, selecciona o elemento da folla, onde se mostrará. A continuación, faga clic na icona "Inserir función".



6. Na xanela Mestría de funcións nun grupo de operadores "Matemática" seleccione o nome SUMPRODUCTO. Fai clic no botón "OK".



7. Activa a xanela de argumento de función. SUMPRODUCTOcuxa tarefa principal é calcular a suma dos produtos do intervalo seleccionado. Pero esta función pode usarse para os nosos propósitos. A súa sintaxe é bastante sinxela:

   = SUMPRODUCT (matriz1; matriz2; ...)

   En total, pode usar enderezos de ata 255 matrices como argumentos. Pero no noso caso usaremos só dúas matrices, ademais, como un único argumento.

   Coloca o cursor no campo "Massive1" e selecciona o intervalo de datos comparado na primeira área da folla. Despois poñemos unha marca no campo. "non igual" () e seleccione o intervalo comparado da segunda rexión. A continuación, envolve a expresión resultante con parénteses, ante a que poñemos dous caracteres "-". No noso caso, temos a seguinte expresión:

   - (A2: A7D2: D7)

   Fai clic no botón "OK".



8. O operador calcula e mostra o resultado. Como vemos, no noso caso o resultado é igual ao número "1"isto é, significa que nas listas comparadas atopouse un desajuste. Se as listas fosen completamente idénticas, o resultado sería igual ao número "0".

Do mesmo xeito, pode comparar datos en táboas situadas en diferentes follas. Pero neste caso é desexable que as liñas nelas estean numeradas. O resto do procedemento de comparación é case exactamente o mesmo que o descrito anteriormente, agás o feito de que cando fas unha fórmula, tes que cambiar entre as follas. No noso caso, a expresión terá a seguinte forma:

= B2 = Sheet2! B2

É dicir, como vemos, antes de que as coordenadas dos datos, que se atopan noutras follas, sexan diferentes do lugar onde se mostra o resultado da comparación, indícanse o número da folla e o signo de exclamación.

Método 2: Selecciona grupos de celas

Pódese facer unha comparación mediante a ferramenta de selección de grupos de celas. Con el, tamén pode comparar só listas sincronizadas e ordenadas. Ademais, neste caso, as listas deberían situarse unha ao lado da outra na mesma folla.

1. Seleccione as matrices comparadas. Vaia á pestana "Fogar". A continuación, fai clic na icona "Buscar e resaltar"que se atopa na cinta do bloque de ferramentas Edición. Ábrese unha lista na que debería seleccionar unha posición. "Seleccionando un grupo de celas ...".

   

   Ademais, na ventá desexada de selección dun grupo de celas pódese acceder doutro xeito. Esta opción será especialmente útil para os usuarios que instalaron a versión do programa antes que Excel 2007, porque o método a través do botón "Buscar e resaltar" Estas aplicacións non soportan. Seleccione as matrices que desexamos comparar e prema a tecla F5.

2. Activa unha pequena ventá de transición. Fai clic no botón "Resaltar ..." no seu canto inferior esquerdo.



3. Despois diso, calquera das dúas opcións anteriores que escolla, lanzarase unha ventá para seleccionar grupos de celas. Sitúe o interruptor na posición "Seleccionar por liña". Fai clic no botón "OK".



4. Como podes ver, despois diso, os valores non coincidentes das filas resaltaranse cun ton diferente. Ademais, como se pode xulgar a partir dos contidos da liña da fórmula, o programa fará que unha das celas estea activa nas liñas especificadas sen igual.

Método 3: formatado condicional

Pode facer unha comparación empregando o método de formato condicional. Como no método anterior, as áreas comparadas deberían estar na mesma folla de cálculo de Excel e sincronizarse entre si.

1. Primeiro de todo, eliximos que espazo de táboa consideraremos o principal e cal buscará as diferenzas. O último que faremos na segunda táboa. Polo tanto, selecciona a lista de empregados localizados nela. Pasando á pestana "Fogar", fai clic no botón "Formato condicional"que se atopa na cinta do bloque "Estilos". Na lista despregable, continúa "Xestión de regras".



2. A ventá do xestor de regras está activada. Pulsamos nel no botón "Crear unha regra".



3. Na ventá de lanzamento, escolla a posición "Usa a fórmula". No campo "Formatar celas" escriba a fórmula que contén os enderezos das primeiras celas dos intervalos das columnas comparadas, separadas polo signo "non igual" (). Só esta expresión terá un sinal esta vez. "=". Ademais, o enderezamento absoluto debería aplicarse a todas as coordenadas de columna desta fórmula. Para iso, selecciona a fórmula co cursor e fai clic tres veces na tecla F4. Como podes ver, apareceu un sinal de dólar preto de todas as direccións das columnas, o que significa converter os enlaces en absolutos. Para o noso caso particular, a fórmula terá a seguinte forma:

   = $ A2 $ D2

   Escribimos esta expresión no campo anterior. Despois faga clic no botón "Formato ...".



4. Xanela activada "Formatar celas". Vaia á pestana "Encher". Aquí na lista de cores detemos a elección da cor coa que queremos colorear aqueles elementos onde os datos non coincidirán. Pulsamos o botón "OK".



5. Volvendo á ventá para crear unha regra de formato, faga clic no botón. "OK".



6. Despois de pasar automaticamente á ventá Xestor de regras faga clic no botón "OK" e nel.



7. Agora na segunda táboa, os elementos que teñan datos que non coinciden cos valores correspondentes da primeira área de táboa serán resaltados na cor seleccionada.

Hai outro xeito de usar o formato condicional para realizar a tarefa. Do mesmo xeito que as opcións anteriores, require a localización de ambas áreas comparadas na mesma folla, pero a diferenza dos métodos descritos anteriormente, a condición para sincronizar ou ordenar os datos non será necesaria, o que distingue esta opción dos descritos anteriormente.

1. Fai unha selección de áreas que hai que comparar.



2. Realiza unha transición cara a pestana chamada "Fogar". Fai clic no botón. "Formato condicional". Na lista activada, seleccione a posición "Regras para a selección de celas". No seguinte menú facemos unha selección de posición. "Valores duplicados".



3. Iníciase a ventá para configurar a selección de valores duplicados. Se fixeches todo correctamente, entón nesta fiestra só queda premer no botón. "OK". Aínda que, se o desexa, pode seleccionar unha cor de selección diferente no campo correspondente desta ventá.



4. Despois de realizar a acción especificada, todos os elementos duplicados serán resaltados na cor seleccionada. Os elementos que non coinciden seguirán coloreados na súa cor orixinal (por defecto, branco). Así, pode ver de inmediato cal é a diferenza entre matrices.

Se o desexa, pode, pola contra, pintar elementos non coincidentes, e os indicadores que coincidan poden quedar co mesmo recheo de cores. Neste caso, o algoritmo de accións é case o mesmo, pero na ventá de configuración para resaltar os valores duplicados no primeiro campo en vez do parámetro "Duplicar" seleccione a opción "Único". Despois, faga clic no botón "OK".

Así, destacaranse os indicadores que non coinciden.

Lección: formatado condicional en Excel

Método 4: fórmula complexa

Tamén pode comparar datos usando unha fórmula complexa, que está baseada na función COUNTES. Usando esta ferramenta, pode calcular canto se repite cada primeiro elemento da columna seleccionada na segunda táboa.

Operador COUNTES refírese a un grupo de funcións estatísticas. A súa tarefa é contar o número de celas cuxos valores satisfán unha condición dada. A sintaxe deste operador é a seguinte:

= CONTADOR (rango; criterio)

Argumento "Intervalo" é a dirección da matriz na que se calculan os valores correspondentes.

Argumento "Criterio" establece a condición de coincidencia. No noso caso, serán as coordenadas de celas específicas do primeiro espazo de táboas.

1. Selecciona o primeiro elemento da columna adicional na que se calculará o número de coincidencias. A continuación, fai clic na icona "Inserir función".



2. O lanzamento ocorre Mestría de funcións. Ir á categoría "Estatística". Busca na lista o nome "COUNTES". Despois de seleccionalo, fai clic no botón. "OK".



3. Iníciase a ventá de argumento do operador. COUNTES. Como podes ver, os nomes dos campos desta ventá corresponden aos nomes dos argumentos.

   Sitúe o cursor no campo "Intervalo". Despois diso, mantendo o botón esquerdo do rato, selecciona todos os valores da columna cos nomes da segunda táboa. Como podes ver, as coordenadas caen inmediatamente no campo especificado. Pero para os nosos propósitos, este enderezo debería facerse absoluto. Para facelo, selecciona as coordenadas no campo e fai clic na tecla F4.

   Como podes ver, a ligazón tomou unha forma absoluta, que se caracteriza pola presenza de signos de dólar.

   Despois vaia ao campo "Criterio"colocando o cursor alí. Facemos clic no primeiro elemento con apelidos no primeiro rango de táboas. Neste caso, deixe a ligazón relativa. Despois de que se amose no campo, pode premer no botón "OK".



4. O resultado móstrase no elemento de folla de cálculo. É igual ao número "1". Isto significa que na lista de nomes da segunda táboa o apelido "Grinev V.P."que é o primeiro na lista da primeira matriz de táboa, ocorre unha vez.



5. Agora necesitamos crear unha expresión similar para todos os outros elementos da primeira táboa. Para facelo, cópiaa usando o marcador de recheo, como fixemos antes. Coloca o cursor na parte inferior dereita do elemento de folla que contén a función COUNTESe despois de convertela no marcador de enchemento, manteña premido o botón esquerdo do rato e arrastre o cursor cara a abaixo.



6. Como podes ver, o programa realizou un cálculo de coincidencias comparando cada cela da primeira táboa cos datos que se atopan no segundo rango de táboas. En catro casos saíu o resultado "1", e en dous casos - "0". É dicir, o programa non puido atopar na segunda táboa os dous valores que están na primeira matriz de táboas.

Por suposto, esta expresión para comparar os indicadores de táboa pódese aplicar no formulario existente, pero hai unha oportunidade para mellorala.

Fagamos que os valores dispoñibles na segunda táboa, pero que estean ausentes no primeiro, aparecen nunha lista separada.

1. Primeiro de todo, reformularemos a nosa fórmula COUNTES, é dicir, convérteno nun dos argumentos do operador SI. Para iso, selecciona a primeira cela na que se atopa o operador COUNTES. Na barra de fórmulas antes del engadimos a expresión "SI" sen comiñas e abre o soporte. A continuación, para que podamos traballar máis fácilmente, seleccionamos o valor na barra de fórmulas. "SI" e fai clic na icona "Inserir función".



2. Ábrese a ventá de argumento de función. SI. Como podes ver, o primeiro campo da xanela xa está cheo co valor do operador. COUNTES. Pero necesitamos engadir algo máis neste campo. Poñemos o cursor alí e engadimos á expresión xa existente "=0" sen comiñas.

   Despois vaia ao campo "Valor se é certo". Aquí usaremos outra función anidado - LINE. Introduza a palabra "LIÑA" sen comiñas, a continuación, abra os parénteses e especifique as coordenadas da primeira cela co apelido na segunda táboa, despois peche os parénteses. En concreto, no noso caso no campo "Valor se é certo" obtivo a seguinte expresión:

   LINE (D2)

   Agora o operador LINE informará de funcións SI o número de liña no que se atopa o apelido específico, e no caso de que se cumpra a condición especificada no primeiro campo, a función SI enviará este número á cela. Pulsamos o botón "OK".



3. Como podes ver, móstrase o primeiro resultado como "FALSO". Isto significa que o valor non satisfai as condicións do operador. SI. É dicir, o primeiro apelido está presente en ambas listas.



4. Usando o marcador de recheo, de xeito habitual copiamos a expresión do operador SI en toda a columna. Como podes ver, en dúas posicións presentes na segunda táboa, pero non na primeira, a fórmula dá números de liña.



5. Retire do espazo de tabela á dereita e enche a columna con números en orde, a partir de 1. O número de números debe coincidir co número de filas da segunda táboa comparada. Para acelerar o procedemento de numeración, tamén pode usar o marcador de recheo.



6. Despois disto, seleccione a primeira cela á dereita da columna con números e faga clic na icona "Inserir función".



7. Abre Asistente de funcións. Ir á categoría "Estatística" e facer unha selección de nomes "O NOME". Fai clic no botón "OK".



8. Función O menos, a fiestra de argumentos cuxa apertura foi deseñada para mostrar o valor máis baixo especificado pola conta.

   No campo "Matriz" especifique as coordenadas do rango da columna adicional "Número de coincidencias"que anteriormente converteuse utilizando a función SI. Facemos todas as ligazóns absolutas.

   No campo "K" indique en que conta debe mostrarse o valor máis baixo. Aquí indicamos as coordenadas da primeira cela da columna con numeración, que engadimos recentemente. O enderezo é relativo. Fai clic no botón "OK".



9. O operador mostra o resultado: o número 3. Esta é a numeración máis pequena das filas non coincidentes das matrices de táboas. Utilizando o marcador de recheo, copie a fórmula cara abaixo.



10. Agora, sabendo os números de liña dos elementos que non coinciden, podemos inserir na cela e os seus valores usando a función ÍNDICE. Seleccione o primeiro elemento da folla que contén a fórmula O menos. Despois vaia á liña da fórmula e antes do nome "O NOME" engadir nome ÍNDICE sen comiñas, abra inmediatamente a paréntese e coloque un punto e coma (;). A continuación, seleccione o nome na barra de fórmulas. ÍNDICE e fai clic na icona "Inserir función".



11. Despois, ábrese unha pequena ventá na que precisa determinar se a referencia debe ter unha función ÍNDICE ou deseñado para traballar con matrices. Necesitamos a segunda opción. Está definido por defecto, polo que nesta fiestra simplemente fai clic no botón. "OK".



12. Iníciase a fiestra do argumento da función. ÍNDICE. Esta instrución está deseñada para mostrar o valor situado nunha matriz específica na liña especificada.

    Como podes ver, o campo "Número de liña" xa está chea de valores de función O menos. Do valor que xa existe, resta a diferenza entre a numeración da folla de Excel e a numeración interna da área de táboa. Como podes ver, por encima dos valores da táboa só temos un tope. Isto significa que a diferenza é unha liña. Polo tanto engadimos no campo "Número de liña" significado "-1" sen comiñas.

    No campo "Matriz" especifique o enderezo do rango de valores da segunda táboa. Ao mesmo tempo, facemos todas as coordenadas absolutas, é dicir, poñemos un sinal de dólar diante deles como o describimos anteriormente.

    Pulsamos o botón "OK".



13. Despois de sacar o resultado á pantalla, estiraremos a función empregando o marcador de recheo ata o final da columna cara a abaixo. Como podes ver, os dous apelidos presentes na segunda táboa, pero non no primeiro, móstranse nun intervalo separado.

Método 5: Comparación de matrices en diferentes libros

Ao comparar intervalos en diferentes libros, pode utilizar os métodos listados anteriormente, excluíndo as opcións que requiren a colocación de ambos os espazos de táboa nunha folla. A condición principal para levar a cabo o procedemento de comparación neste caso é abrir simultaneamente as xanelas dos dous ficheiros. Non hai problemas para versións de Excel 2013 e posteriores, así como para versións anteriores a Excel 2007. Pero en Excel 2007 e Excel 2010, para abrir as dúas xanelas ao mesmo tempo, son necesarias manipulacións adicionais. A forma de facelo descríbese nunha lección separada.

Lección: como abrir Excel en diferentes xanelas

Como podes ver, hai unha serie de posibilidades para comparar táboas entre si. Que opción usar depende exactamente de onde están situados os datos tabulares entre si (nunha folla, en diferentes libros, en distintas follas) e tamén como exactamente o usuario quere que esta comparación se amose na pantalla.