Moitas veces, os usuarios de Excel teñen unha tarefa de comparar dúas táboas ou listas para identificar diferenzas nelas ou perder elementos. Cada usuario enfróntase con esta tarefa ao seu xeito, pero a maioría das veces é unha gran cantidade de tempo que se gasta na resolución da pregunta especificada, xa que non todos os enfoques a este problema son racionais. Ao mesmo tempo, hai varios algoritmos de accións comprobadas que permitirán comparar listas ou matrices de mesa en pouco tempo con mínimo esforzo considerable. Consideremos detalles estas opcións.
Do mesmo xeito, pode comparar os datos nas táboas que están situadas en diferentes follas. Pero neste caso, é desexable que as liñas neles estean numeradas. O resto do procedemento de comparación é case exactamente o mesmo que o descrito anteriormente, ademais do feito de que ao facer a fórmula, é necesario cambiar de follas. No noso caso, a expresión terá a seguinte forma:
= B2 = LISTA2! B2
É dicir, como vemos, fronte ás coordenadas de datos, que están situadas noutras follas distintas do que se amosa o resultado de comparación, indícase o número de folla e unha marca de exclamación.
Método 2: Selección de grupos de células
A comparación pode realizarse usando a ferramenta para a separación de grupos de células. Con el, tamén podes comparar só listas sincronizadas e ordenadas. Ademais, neste caso, as listas deben situarse ao lado do outro nunha folla.
- Seleccione Arrays comparados. Vaia á pestana "Home". A continuación, faga clic na icona "Buscar e seleccionar", que está situado na cinta da barra de ferramentas de edición. Hai unha lista na que ten que seleccionar a posición "Selección do grupo de celas ...".
Ademais, pódese alcanzar a selección do grupo de células doutra forma. Esta opción será especialmente útil para aqueles usuarios que teñan a versión do programa anterior que Excel 2007, xa que o método a través do botón "Buscar e seleccionar" non admite estas aplicacións. Seleccione matrices que desexen comparar e prema na tecla F5.
- Activa unha pequena xanela de transición. Fai clic no botón "Resaltar ..." na súa esquina inferior esquerda.
- Despois diso, calquera que sexan dúas das opcións anteriores que teña elixido, póñase en marcha a xanela de selección de grupos de células. Instala o interruptor á posición "Resaltar en liña". Fai clic no botón "Aceptar".
- Como podemos ver, despois diso, os valores inconsistentes das liñas serán resaltadas cun tinte diferente. Ademais, como se pode xulgar dos contidos da liña de fórmulas, o programa fará unha activa das células nas liñas non coincidentes indicadas.
Método 3: Formato condicional
Pode facer unha comparación aplicando o método de formato condicional. Como no método anterior, as áreas comparativas deben estar nunha única folla de traballo de Excel e sincronizarse entre si.
- Primeiro de todo, eliximos que rexión tabular será considerada a principal e, en que buscar a diferenza. Íticamente imos facer na segunda mesa. Polo tanto, reservamos unha lista de traballadores nel. Movéndose á pestana "Home", fai clic no botón "Formato condicional", que ten unha localización na cinta no bloque "Estilos". Na lista despregábel, percorre o elemento "Xestión de regras".
- A xanela Dispatcher do xestor de regras está activada. Faga clic sobre el no botón "Crear regra".
- Na xanela de execución, seleccionamos a posición "Usar fórmula". No campo "Format Cell", escriba unha fórmula que contén as direccións das primeiras celas das gamas de columnas comparadas, separadas polo signo "non igual" (). Pouco antes desta expresión esta vez estará o sinal "=". Ademais, a todas as coordenadas da columna nesta fórmula, cómpre aplicar un enderezo absoluto. Para iso, destinamos a fórmula co cursor e prema na tecla F4 tres veces. Como podemos ver, o sinal de dólar apareceu preto de todos os enderezos das columnas, o que significa transformar as ligazóns a absoluto. Para o noso caso particular, a fórmula tomará o seguinte formulario:
= $ A2 $ D2
Gravamos esta expresión no campo anterior. Despois diso, facemos clic no botón "Formato ...".
- A xanela "Formato de móbil" está activada. Imos á pestana "encher". Aquí na lista de cores paran a elección da cor, que queremos pintar aqueles elementos onde os datos non coincidirán. Fai clic no botón "Aceptar".
- Volvendo á xanela de regra de formato, faga clic no botón "Aceptar".
- Despois de moverse automático á xanela "Xerente de regras", faga clic no botón "Aceptar" e nel.
- Agora na segunda táboa, os elementos que teñen datos que son inconsistentes cos valores correspondentes da primeira táboa estarán resaltados na cor seleccionada.
Hai outra forma de aplicar o formato condicional para realizar a tarefa. Como as opcións anteriores, isto require a situación de ambas as áreas de comparación sobre unha folla, pero en contraste cos métodos anteriormente descritos, o estado de sincronización ou clasificación de datos non ha ser obrigatorio, o que distingue esta opción dende anteriormente descrito.
- Nós producimos a selección de áreas que cómpre comparar.
- Realizamos a transición á pestana chamada "Home". Facemos clic no botón "Formato condicional". Na lista activada, seleccione a posición "Regras para a asignación de celas". No próximo menú, faga a posición de "valores repetitivos".
- Lanzáronse os valores duplicados. Se está todo feito correctamente, entón nesta xanela segue sendo só facer clic no botón "Aceptar". Aínda que, se o desexa, no campo apropiado desta xanela, pode escoller outra cor da selección.
- Despois de producir a acción especificada, todos os elementos repetitivos serán resaltados na cor seleccionada. Eses elementos que non coinciden permanecerán pintados na súa cor orixinal (por defecto branco). Así, pode ver inmediatamente visualmente para ver cal é a diferenza entre as arrays.
Se o desexa, pode, pola contra, pintar os elementos inconsistentes e os indicadores que coinciden, saír co recheo da cor anterior. Ao mesmo tempo, o algoritmo de acción é case o mesmo, pero na configuración dos valores duplicados no primeiro campo, no canto do parámetro "repetido", selecciona o parámetro "único". Despois, faga clic no botón "Aceptar".
Así, é precisamente aqueles indicadores que non coinciden.
Lección: Formato condicional en Excel
Método 4: fórmula integral
Tamén compara datos coa axuda dunha fórmula complexa, cuxa función é a función do contador. Usando esta ferramenta, pode calcular o que cada elemento da columna seleccionada da segunda táboa repítese no primeiro.
Os consellos de operador refírense ao grupo estatístico de funcións. A súa tarefa é contar o número de células, os valores nos que satisfán a condición especificada. A sintaxe deste operador ten este tipo:
= Horarios (intervalo; criterio)
O argumento "rango" é o enderezo da matriz, que calcula os valores coincidentes.
O argumento "Criterios" especifica a condición da coincidencia. No noso caso, será as coordenadas das células específicas da primeira área tabular.
- Restauramos o primeiro elemento da columna adicional na que se calculará o número de partidos. A continuación, faga clic na icona "Inserir función".
- Comeza o asistente de funcións. Ir á categoría "estatística". Atopa na lista o nome "Countifes". Despois da súa selección, faga clic no botón "Aceptar".
- A xanela de argumentos do operador comezou a funcionar. Como vemos, os nomes dos campos nesta xanela corresponden aos nomes dos argumentos.
Instala o cursor no campo "intervalo". Despois diso, mantendo o botón esquerdo do rato, reservar 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 debe ser feito por absoluto. Para facelo, resalte as coordenadas no campo e faga clic na tecla F4.
Como podes ver, a referencia tomou a forma absoluta, que se caracteriza pola presenza de signos de dólar.
A continuación, vai ao campo "Criterion" instalando o cursor. Fai clic no primeiro elemento cos apelidos no primeiro rango tabular. Neste caso, deixe a ligazón relativa. Despois de que se amose no campo, pode facer clic no botón "Aceptar".
- O resultado é a saída ao elemento da folla. É igual ao número "1". Isto significa que na lista de nomes da segunda táboa, o nome "Grinev V. P.", que é o primeiro na lista da primeira matriz de mesa, ocorre unha vez.
- Agora necesitamos crear unha expresión similar e para todos os outros elementos da primeira mesa. Para iso, vou realizar copiando, usando o marcador de recheo, xa que xa fixemos antes. Poñemos o cursor á parte inferior dereita do elemento LEAF, que contén a función do medidor e despois de convertelo ao marcador de recheo, abrazar o botón esquerdo do rato e tirar o cursor cara a abaixo.
- Como podemos ver, o programa fixo unha computación de coincidencias, comparando cada cela da primeira táboa con datos, que están localizados no rango da segunda táboa. En catro casos, o resultado foi "1", e en dous casos - "0". É dicir, o programa non puido atopar os dous valores na segunda táboa que están dispoñibles na primeira matriz de mesa.
Por suposto, esta expresión para comparar os indicadores de táboas, é posible aplicar no formulario existente, pero é posible melloralo.
Faremos que os valores que existen na segunda mesa, pero non están dispoñibles no primeiro, mostráronse como unha lista separada.
- Primeiro de todo, reciclamos a nosa fórmula, pero imos facelo un dos argumentos do operador se. Para iso, destacamos a primeira cela na que o operador é o operador. Na cadea de fórmula diante dela, engadimos a expresión "se" sen citas e abre o soporte. Ademais, para que fose máis doado para nós traballar, reservar as fórmulas na Fórmula Row e facer clic na icona "Inserir función".
- A xanela de argumentos de función ábrese se. Como podes ver, a primeira fiestra da xanela xa foi cuberta coa valoración do Consello do Operador. Pero necesitamos engadir outra cousa neste campo. Definimos que o cursor ea expresión xa existente engaden "= 0" sen comiñas.
Despois diso, vai ao campo "significado se a verdade". Aquí imos usar outra liña de función aniñada. Introduza a palabra "cadea" sen comiñas, a continuación, abren corchetes e indique as coordenadas da primeira cela co apelido na segunda táboa, despois de que pechamos corchetes. En concreto, no noso caso no campo "significado se é verdadeiro", ocorreu a seguinte expresión:
Fila (D2)
Agora a cadea de operador informará funcións se o número de liña no que se atopa o apelido específico e, no caso de que se realice a condición especificada no primeiro campo, a función se se mostrará este número. Fai clic no botón "Aceptar".
- Como podes ver, o primeiro resultado móstrase como "mentiras". Isto significa que o valor non satisfai as condicións do operador se. É dicir, o primeiro apelido está presente en ambas listas.
- Usando o marcador de recheo, que xa está familiarizado para copiar a expresión do operador se está en toda a columna. Como vemos, en dúas posicións que están presentes na segunda táboa, pero non no primeiro, a fórmula emite os números de filas.
- Retratamos da área da táboa á dereita e enche a columna de números en orde, a partir de 1. O número de números debe coincidir co número de filas na segunda táboa complementaria. Para acelerar o procedemento de numeración, tamén pode usar o marcador de recheo.
- Despois diso, destacamos a primeira célula á dereita do altofalante cos números e faga clic na icona "Inserir función".
- Abre o asistente. Ir á categoría "Estatística" e producir o nome do nome "máis pequeno". Fai clic no botón "Aceptar".
- A función é a máis pequena, os argumentos dos argumentos dos cales foron divulgados, preténdese retirar o valor máis pequeno especificado.
No campo "Array", debes especificar as coordenadas do rango da columna opcional "O número de partidos", que previamente transformamos usando a función se. Facemos todas as conexións absolutas.
No campo "K", indícase que na conta debe mostrarse o menor valor. Aquí indicamos as coordenadas da primeira cela da columna coa numeración, que recentemente engadimos. Enderezo Deixar relativo. Fai clic no botón "Aceptar".
- O operador mostra o resultado - número 3. É precisamente o máis pequeno da numeración das liñas inconsistentes de matrices de mesa. Usando o recheo, copie a fórmula ao nariz en si.
- Agora, coñecer os números das filas dos elementos incomprensivos, podemos inserir na cela e os seus valores usando a función de índice. Seleccione o primeiro elemento da folla que conteña a fórmula é a máis pequena. Despois diso, vai ás fórmulas de liña e antes do nome do "máis pequeno" engade o nome "Índice" sen comiñas, inmediatamente abre o soporte e coloque un punto cunha coma (;). A continuación, reservamos o nome da fórmula "Índice" na liña e faga clic na icona "Función de pasta".
- Despois diso, ábrese unha pequena fiestra, na que é necesario determinar se a vista de referencia debe ter unha función de índice ou deseñada para traballar con arrays. Necesitamos unha segunda opción. Está instalado por defecto, de xeito que nesta xanela simplemente faga clic no botón "Aceptar".
- A xanela do argumento está a executar a función de índice. Este operador está deseñado para producir un valor que está situado nunha matriz específica na liña especificada.
Como podes ver, o campo "Número de fila" xa está cheo de valores de significado da función máis pequena. Desde un valor existente alí, a diferenza entre a numeración da folla de Excel e a numeración interna da rexión tabular. Como vemos, só temos un sombreiro sobre os valores da mesa. Isto significa que a diferenza é unha liña. Polo tanto, engade "-1" sen citas no campo "Número de fila".
No campo "Array", especifique a dirección do valor dos valores da segunda táboa. Ao mesmo tempo, todas as coordenadas fan absoluta, é dicir, poñemos o sinal de dólar antes do método descrito por nós.
Fai clic no botón "Aceptar".
- Despois de retirarse, o resultado na pantalla está estendendo a función usando o marcador de recheo ata o final da columna cara a abaixo. Como vemos, ambos apelidos que están presentes na segunda mesa, pero non están dispoñibles no primeiro, elimínanse nun rango separado.
Método 5: Comparación de matrices en diferentes libros
Ao comparar as rangos en libros diferentes, pode usar os métodos anteriores, excluíndo as opcións onde se require a colocación de ambas áreas tabulares nunha soa folla. A principal condición para o procedemento de comparación neste caso é a apertura das fiestras de ambos ficheiros ao mesmo tempo. Para as versións de Excel 2013 e posterior, así como para versións a Excel 2007, non hai problemas coa implementación desta condición. Pero en Excel 2007 e Excel 2010, a fin de abrir ambas fiestras ao mesmo tempo, son necesarias manipulacións adicionais. Como facelo conta nunha lección separada.
Lección: Como abrir Excel en diferentes fiestras
Como podes ver, hai unha serie de posibilidades para comparar táboas entre si. Que tipo de opción para usar depende de onde se localice os datos da táboa en relación ao outro (nunha folla, en diferentes libros, en diferentes follas), así como do usuario que desexe que se comparen na pantalla.