Métodos de comparación de táboas en Microsoft Excel

A miúdo, os usuarios de Excel teñen a tarefa de comparar dúas táboas ou listas para identificar diferenzas ou elementos que faltan nelas. Cada usuario trata esta tarefa ao seu xeito, pero a maioría das veces dedícase a resolver este problema, xa que non todas as aproximacións a este problema son racionais. Ao mesmo tempo, hai varios algoritmos de acción probados que lle permitirán comparar listas ou matrices de táboas nun tempo bastante curto cun esforzo mínimo. Vexamos con máis detalle estas opcións.

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

Métodos de comparación

Hai moitos xeitos de comparar os espazos de táboas en Excel, pero todos poden dividirse en tres grandes grupos:

Con base nesta clasificación, en primeiro lugar selecciónanse métodos de comparación, así como tamén se determinan accións e algoritmos específicos para a tarefa. Por exemplo, ao comparar en diferentes libros, necesitas abrir simultaneamente dous ficheiros Excel.

Ademais, hai que dicir que comparar as áreas da táboa só ten sentido cando teñen unha estrutura similar.

Método 1: fórmula sinxela

O xeito máis sinxelo de comparar datos en dúas táboas é empregar unha fórmula de igualdade sinxela. Se os datos coinciden, dá o indicador TRUE e, se non, FALSE. Podes comparar datos numéricos e de texto. A desvantaxe deste método é que só se pode usar se os datos da táboa están ordenados ou ordenados do mesmo xeito, sincronizados e teñen o mesmo número de liñas. Vexamos como empregar este método na práctica cun exemplo de dúas táboas colocadas nunha folla.

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

1. Para iso, necesitamos unha columna adicional na folla. Entramos alí un cartel "=". Despois facemos clic no primeiro elemento que desexa comparar na primeira lista. Poñemos de novo o símbolo "=" dende o teclado. A continuación, fai clic na primeira cela da columna que estamos comparando na segunda táboa. O resultado é unha 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 Introduzapara obter resultados de comparación. Como podes ver, ao comparar as primeiras celas de ambas listas, o programa indicou un indicador "TRUE", que significa coincidencia de datos.



3. Agora necesitamos realizar unha operación similar coas outras celas de ambas as táboas das columnas que estamos comparando. Pero simplemente pode 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 realízase máis facilmente usando o marcador de recheo. Pasamos pola esquina inferior dereita da cela, onde obtivemos o indicador "TRUE". Ao mesmo tempo, debería transformarse nunha cruz negra. Este é o marcador de recheo. Prememos o botón esquerdo do rato e arrastramos o cursor cara a abaixo no número de liñas nas matrices de táboas comparadas.



4. Como podes ver, agora nunha columna adicional móstranse todos os resultados da comparación de datos en dúas columnas de matrices de táboas. No noso caso, os datos dunha soa liña non coinciden. Ao comparalos, a fórmula produciu o resultado FALSA. Para o resto de liñas, como vemos, a fórmula de comparación produciu un indicador "TRUE".



5. Ademais, é posible calcular o número de discrepancias empregando unha fórmula especial. Para iso, selecciona o elemento da folla onde se mostrará. Despois faga clic na icona "Función de inserción".



6. Na xanela Asistentes de funcións nun grupo de operadores "Matemático" selecciona o nome RESUMO. Fai clic no botón "Está ben".



7. A xanela do argumento da función está activada. RESUMOcuxa tarefa principal é calcular a suma dos produtos da gama seleccionada. Pero esta función pode usala para os nosos propósitos. A sintaxe é bastante sinxela:

   = SUMPRODUCT (array1; array2; ...)

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

   Pon o cursor no campo "Array1" e selecciona na folla o rango de datos comparado na primeira área. Despois, coloque un cartel no campo non igual () e selecciona o rango comparado da segunda rexión. A continuación, envolve a expresión resultante entre parénteses ante a que poñemos dous caracteres "-". No noso caso, esta expresión resultou:

   - (A2: A7D2: D7)

   Fai clic no botón "Está ben".



8. O operador calcula e mostra o resultado. Como podes ver, no noso caso, o resultado é igual ao número "1"isto é, significa que se atopou un desaxuste nas listas comparadas. 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 sexan numeradas. Se non, o procedemento de comparación é case exactamente o mesmo que descrito anteriormente, salvo o feito de que cando introduce a fórmula hai que cambiar entre follas. No noso caso, a expresión quedará así:

= B2 = Folla2! B2

É dicir, como vemos, antes das coordenadas dos datos, que se atopan noutras follas, agás onde se amosa o resultado da comparación, indícanse o número de folla e un signo de exclamación.

Método 2: selecciona grupos de células

A comparación pódese facer usando a ferramenta de selección de grupos celulares. Tamén se pode usar para comparar só listas sincronizadas e ordenadas. Ademais, neste caso, as listas deberían situarse unha á outra da mesma folla.

1. Seleccionamos as matrices comparadas. Vai á lapela "Casa". A continuación, fai clic na icona Busca e resaltasituado na cinta da caixa de ferramentas "Edición". Ábrese unha lista na que seleccionar unha posición "Seleccionando un grupo de celas ...".

   

   Ademais, podemos chegar á xanela desexada para seleccionar un grupo de celas doutro xeito. Esta opción será especialmente útil para aqueles usuarios que instalaron unha versión do programa anterior a Excel 2007, desde o método a través do botón Busca e resalta estas aplicacións non son compatibles. Seleccionamos as matrices que queremos comparar e prememos a tecla F5.

2. Está activada unha pequena xanela de transición. Fai clic no botón "Selecciona ..." na súa esquina inferior esquerda.



3. Despois diso, calquera das dúas opcións anteriores que escolla, ábrese a xanela para seleccionar grupos de celas. Axuste o interruptor á posición "Seleccionar liña por liña". Fai clic no botón "Está ben".



4. Como podes ver, despois disto resaltarán os distintos valores das liñas cunha tonalidade diferente. Ademais, como se pode avaliar dos contidos da barra de fórmulas, o programa fará activa unha das celas situadas nas liñas non concordadas especificadas.

Método 3: formato condicional

Podes comparar usando o método de formato condicional. Do mesmo xeito que no método anterior, as áreas comparadas deberían estar na mesma folla de traballo de Excel e sincronizarse entre si.

1. Primeiro de todo, escollemos que área de táboa consideraremos a principal e en que buscar diferenzas. Fagamos o último na segunda táboa. Polo tanto, seleccionamos a lista de traballadores situados nela. Ao pasar á lapela "Casa"fai clic no botón Formato condicionalque está situado na cinta do bloque Estilos. Na lista despregable, vai a Xestión de normas.



2. A xanela do xestor de regras está activada. Fai clic no botón que hai Crear regra.



3. Na xanela que se inicia, seleccione a posición Use Fórmula. No campo "Formatar celas" escriba unha fórmula que conteña as direccións das primeiras celas dos intervalos das columnas comparadas, separadas por un signo "non igual" () Esta vez só se enfrontará esta expresión. "=". Ademais, o enderezo absoluto debe aplicarse a todas as coordenadas da columna desta fórmula. Para iso, selecciona a fórmula co cursor e prema a tecla tres veces F4. Como podes ver, apareceu un sinal en dólares preto de todas as direccións da columna, o que significa converter 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 diso, faga clic no botón "Formatear ...".



4. A xanela está activada Formato das celas. Vai á lapela "Enche". Aquí na lista de cores detemos a elección da cor coa que queremos colorear aqueles elementos nos que os datos non coinciden. Fai clic no botón "Está ben".



5. Volvendo á xanela para crear unha regra de formato, faga clic no botón "Está ben".



6. Despois de pasar automaticamente á xanela Xestor de normas fai clic no botón "Está ben" e nela.



7. Na segunda táboa destacarase na cor seleccionada elementos que teñan datos que non coincidan cos valores correspondentes á primeira área da táboa.

Hai outro xeito de aplicar o formato condicional á 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, non será obrigatoria a condición para sincronizar ou clasificar datos, o que distingue esta opción da descrita anteriormente.

1. Seleccionamos as áreas a comparar.



2. Vai á pestana chamada "Casa". Fai clic no botón Formato condicional. Na lista activada, seleccione a posición Regras de selección de celas. No seguinte menú facemos unha elección da posición Valores duplicados.



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



4. Despois de realizar a acción especificada, todos os elementos que se repiten resaltaranse na cor seleccionada. Aqueles elementos que non coincidan permanecerán pintados na súa cor orixinal (branco por defecto). Así, podes ver de inmediato cal é a diferenza entre as matrices.

Se o desexa, pode, pola contra, colorear os elementos non coincidentes e os indicadores que coinciden, deixan o recheo coa mesma cor. O algoritmo de accións é case o mesmo, pero na xanela de configuracións para resaltar os valores duplicados no primeiro campo en vez do parámetro Duplicar debería seleccionar "Único". Despois diso, faga clic no botón "Está ben".

Así, precisamente resaltaranse aqueles indicadores que non coincidan.

Lección: Formatación condicional en Excel

Método 4: fórmula complexa

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

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

= COUNTIF (rango; criterio)

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

Argumento "Criterio" establece unha condición de partido. No noso caso, serán as coordenadas de celas específicas da primeira táboa.

1. Seleccionamos o primeiro elemento da columna adicional no que se contará o número de coincidencias. A continuación, fai clic na icona "Función de inserción".



2. Posta en marcha Asistentes de funcións. Vai á categoría "Estatística". Busca o nome na lista "COUNTIF". Despois de seleccionalo, faga clic no botón "Está ben".



3. O lanzamento da xanela do argumento do operador CONTA. Como podes ver, os nomes dos campos nesta xanela corresponden aos nomes dos argumentos.

   Estableza o cursor no campo "Rango". 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 entran inmediatamente no campo especificado. Pero para os nosos propósitos, este enderezo debería ser absoluto. Para iso, selecciona estas coordenadas no campo e prema a tecla F4.

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

   Despois vai ao campo "Criterio"poñendo alí o cursor. Facemos clic no primeiro elemento con apelidos no primeiro intervalo de táboas. Neste caso, deixe o enlace relativo. Despois de que apareza no campo, pode facer clic no botón "Está ben".



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



5. Agora necesitamos crear unha expresión similar para todos os outros elementos da primeira táboa. Para iso, copiaremos usando o marcador de recheo, como xa fixemos antes. Coloque o cursor na parte inferior dereita do elemento da folla que contén a función CONTAe despois de convertelo nun marcador de recheo, manteña o botón esquerdo do rato e arrastre o cursor cara abaixo.



6. Como podes ver, o programa calculou as coincidencias comparando cada cela da primeira táboa cos datos situados no segundo intervalo de táboas. En catro casos, o resultado saíu "1"e en dous casos - "0". É dicir, o programa non atopou na segunda táboa dous valores que están na primeira matriz.

Por suposto, esta expresión, para comparar indicadores tabulares, pode usarse na súa forma existente, pero hai unha oportunidade para melloralo.

Asegurámonos de que aqueles valores que se atopan na segunda táboa, pero que non estean na primeira, se mostren nunha lista separada.

1. En primeiro lugar, reproblemaremos lixeiramente a nosa fórmula CONTAé dicir, facémolo un dos argumentos do operador SE. Para iso, selecciona a primeira cela na que se atopa o operador CONTA. Na liña de fórmulas anteriores a ela, engade a expresión SE sen comiñas e abra o soporte. A continuación, para que nos sexa máis sinxelo o traballo, seleccionamos o valor na barra de fórmulas SE e fai clic na icona "Función de inserción".



2. Ábrese a xanela de argumentos de función SE. Como podes ver, o primeiro campo da xanela xa está cuberto co valor do operador CONTA. Pero hai que engadir algo máis a este campo. Fixamos o cursor alí e engadimos á expresión existente "=0" sen comiñas.

   Despois diso, vai ao campo "Significando se é certo". Aquí usaremos outra función anidada - LIÑA. Introduce a palabra LIÑA sen comiñas, logo abra os parénteses e indique as coordenadas da primeira cela co apelido da segunda táboa e logo pecha os parénteses. Concretamente, no noso caso, no campo "Significando se é certo" A seguinte expresión resultou:

   LIÑA (D2)

   Agora o operador LIÑA informará das funcións SE o número da liña na que se atopa un apelido particular e no caso de que se cumpra a condición especificada no primeiro campo, a función SE amosará este número na cela. Fai clic no botón "Está ben".



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



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



5. Partimos da área da táboa á dereita e enchemos 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 a comparar. Para acelerar o proceso de numeración, tamén pode empregar o marcador de recheo.



6. Despois diso, seleccione a primeira cela á dereita da columna con números e faga clic na icona "Función de inserción".



7. Abre Asistente de características. Vai á categoría "Estatística" e faga unha escolla do nome "MÁIS". Fai clic no botón "Está ben".



8. Función MELLORcuxa xanela de argumentos se abriu, pretende mostrar o menor valor especificado na conta.

   No campo Array especifique as coordenadas do rango da columna adicional "Número de partidos"que antes convertiamos usando a función SE. Facemos todas as ligazóns absolutas.

   No campo "K" indica que conta hai que amosar o valor máis baixo. Aquí indicamos as coordenadas da primeira cela da columna con numeración, que recentemente engadimos. Deixamos a dirección relativa. Fai clic no botón "Está ben".



9. O operador mostra o resultado: un número 3. É a máis pequena da numeración de filas incomparables de matrices. Usando o marcador de recheo, copia a fórmula ao fondo.



10. Agora, coñecendo os números de liña dos elementos non coincidentes, podemos inserir na cela os seus valores mediante a función ÍNDICE. Seleccione o primeiro elemento da folla que contén a fórmula MELLOR. Despois diso, vaia á liña de fórmulas e antes do nome "MÁIS" engade o nome ÍNDICE sen comiñas, abra de inmediato o soporte e pon un punto e coma (;) A continuación, seleccione o nome na liña de fórmulas ÍNDICE e fai clic na icona "Función de inserción".



11. Despois diso, ábrese unha pequena xanela na que se debe determinar a vista de referencia debería ter unha función ÍNDICE ou deseñado para traballar con matrices. Necesitamos a segunda opción. Está instalado de xeito predeterminado, polo que nesta xanela só tes que facer clic no botón "Está ben".



12. Iníciase a xanela do argumento da función ÍNDICE. Este operador está destinado a producir un valor que está situado nunha matriz específica na cadea especificada.

    Como podes ver, o campo Número de liña xa está cheo de valores de función MELLOR. Do valor que xa existe hai que restar a diferenza entre a numeración da folla de Excel e a numeración interna da área da táboa. Como podes ver, só temos un encabezado sobre os valores da táboa. Isto significa que a diferenza é dunha liña. Polo tanto, engadimos no campo Número de liña valor "-1" sen comiñas.

    No campo Array especifique o enderezo do rango de valores da segunda táboa. Ao mesmo tempo, facemos todas as coordenadas absolutas, é dicir, poñemos diante deles o signo do dólar do xeito que antes describimos.

    Fai clic no botón "Está ben".



13. Despois de mostrar o resultado na pantalla, ampliamos a función usando o marcador de recheo ata a parte inferior da columna. Como podes ver, os dous apelidos que están presentes na segunda táboa, pero non están na primeira, móstranse nun rango separado.

Método 5: comparar as matrices en diferentes libros

Ao comparar intervalos en diferentes libros, pode empregar os métodos anteriores, excepto para aquelas opcións nas que desexa colocar as dúas áreas da táboa nunha folla. A principal condición para o procedemento de comparación neste caso é abrir as ventás de ambos os ficheiros simultaneamente. Para as versións de Excel 2013 e posteriores, así como para versións anteriores a Excel 2007, non hai problemas con esta condición. Pero en Excel 2007 e Excel 2010, para abrir ambas as ventás ao mesmo tempo, son necesarias manipulacións adicionais. Como facelo descríbese nunha lección separada.

Lección: Como abrir Excel en diferentes fiestras

Como podes ver, hai varias posibilidades para comparar táboas entre si. Que opción empregar depende de onde se atopen exactamente os datos tabulares uns dos outros (nunha folla, en libros diferentes, en diferentes follas), así como de como o usuario quere que se compare esta pantalla na pantalla.