Com comparar dues llistes a Excel

Excel és un programa eficient de processament de dades. I un dels mètodes d'anàlisi de la informació és la comparació de dues llistes. Si compareu correctament dues llistes a Excel, organitzar aquest procés serà molt fàcil. N'hi ha prou amb seguir alguns dels punts que es tractaran avui. La implementació pràctica d'aquest mètode depèn totalment de les necessitats de la persona o organització en un moment concret. Per tant, s'han de considerar diversos casos possibles.

Comparació de dues llistes a Excel

Per descomptat, podeu comparar dues llistes manualment. Però trigarà molt de temps. Excel té el seu propi conjunt d'eines intel·ligents que us permetrà comparar dades no només ràpidament, sinó també obtenir informació que no és tan fàcil d'obtenir amb els vostres ulls. Suposem que tenim dues columnes amb les coordenades A i B. Alguns valors es repeteixen en elles.

Formulació del problema

Per tant, hem de comparar aquestes columnes. El procediment per comparar dos documents és el següent:

1. Si les cel·les úniques de cadascuna d'aquestes llistes són les mateixes i el nombre total de cel·les úniques és el mateix i les cel·les són les mateixes, aquestes llistes es poden considerar iguals. L'ordre en què s'apilen els valors d'aquesta llista no importa tant.
2. Podem parlar de coincidència parcial de llistes si els valors únics són els mateixos, però el nombre de repeticions és diferent. Per tant, aquestes llistes poden contenir un nombre diferent d'elements.
3. El fet que les dues llistes no coincideixin s'indica amb un conjunt diferent de valors únics.

Totes aquestes tres condicions simultàniament són les condicions del nostre problema.

La solució del problema

Generem dos rangs dinàmics per facilitar la comparació de llistes. Cadascun d'ells correspondrà a cadascuna de les llistes.

Per comparar dues llistes, feu el següent:

1. En una columna separada, creem una llista de valors únics que són específics per a ambdues llistes. Per a això fem servir la fórmula: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). La fórmula en si s'ha d'escriure com una fórmula matricial.
2. Determinem quantes vegades es produeix cada valor únic a la matriu de dades. Aquestes són les fórmules per fer-ho: =COMPTARSI(Llista1,D5) i =COMPTARSI(Llista2,D5).
3. Si tant el nombre de repeticions com el nombre de valors únics són els mateixos en totes les llistes que s'inclouen en aquests rangs, aleshores la funció retorna el valor 0. Això indica que la coincidència és XNUMX%. En aquest cas, els encapçalaments d'aquestes llistes adquiriran un fons verd.
4. Si tot el contingut únic es troba a les dues llistes, es retornarà per fórmules =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) el valor serà zero. Si E1 no conté zero, però aquest valor es troba a les cel·les E2 i F2, en aquest cas els intervals es reconeixeran com a coincidents, però només parcialment. En aquest cas, els encapçalaments de les llistes corresponents es tornaran taronges.
5. I si una de les fórmules descrites anteriorment retorna un valor diferent de zero, les llistes no coincidiran completament.

Aquesta és la resposta a la pregunta de com analitzar columnes per a coincidències mitjançant fórmules. Com podeu veure, amb l'ús de funcions, podeu implementar gairebé qualsevol tasca que, a primera vista, no estigui relacionada amb les matemàtiques.

Exemple de prova

A la nostra versió de la taula, hi ha tres tipus de llistes de cada tipus descrits anteriorment. Té concordança parcial i completament, així com no coincidència.

Per comparar dades, utilitzem l'interval A5:B19, en el qual inserim alternativament aquests parells de llistes. Sobre quin serà el resultat de la comparació, entendrem pel color de les llistes originals. Si són completament diferents, serà un fons vermell. Si una part de les dades és la mateixa, llavors el groc. En cas d'identitat completa, els encapçalaments corresponents seran de color verd. Com fer un color segons quin sigui el resultat? Això requereix un format condicional.

Trobar diferències en dues llistes de dues maneres

Descriurem dos mètodes més per trobar diferències, depenent de si les llistes són sincròniques o no.

Opció 1. Llistes síncrones

Aquesta és una opció fàcil. Suposem que tenim aquestes llistes.

Per determinar quantes vegades els valors no van convergir, podeu utilitzar la fórmula: =SUMAPRODUCTE(—(A2:A20<>B2:B20)). Si aconseguim 0 com a resultat, això vol dir que les dues llistes són iguals.

Opció 2: Llistes barrejades

Si les llistes no són idèntiques en l'ordre dels objectes que contenen, heu d'aplicar una característica com ara el format condicional i acolorir els valors duplicats. O utilitzeu la funció COUNTIF, mitjançant el qual determinem quantes vegades apareix un element d'una llista a la segona.

Com comparar 2 columnes fila per fila

Quan comparem dues columnes, sovint hem de comparar informació que es troba en files diferents. Per fer-ho, l'operador ens ajudarà SI. Fem una ullada a com funciona a la pràctica. Per fer-ho, presentem diverses situacions il·lustratives.

Exemple. Com comparar 2 columnes per a coincidències i diferències en una fila

Per analitzar si els valors que estan a la mateixa fila però diferents columnes són iguals, escrivim la funció IF. La fórmula s'insereix a cada fila situada a la columna auxiliar on es mostraran els resultats del processament de dades. Però no és gens necessari prescriure-ho a cada fila, només cal copiar-lo a les cel·les restants d'aquesta columna o utilitzar el marcador d'autocompletar.

Hauríem d'escriure aquesta fórmula per entendre si els valors de les dues columnes són iguals o no: =SI(A2=B2, “Coincidència”, “”). La lògica d'aquesta funció és molt senzilla: compara els valors de les cel·les A2 i B2, i si són iguals, mostra el valor "Coincideix". Si les dades són diferents, no retorna cap valor. També podeu comprovar les cel·les per veure si hi ha una coincidència entre elles. En aquest cas, la fórmula utilitzada és: =SI(A2<>B2, “No coincideix”, “”). El principi és el mateix, primer es realitza la comprovació. Si resulta que les cel·les compleixen el criteri, es mostra el valor "No coincideix".

També és possible utilitzar la fórmula següent al camp de la fórmula per mostrar tant "Coincideix" si els valors són els mateixos, com "No coincideix" si són diferents: =SI(A2=B2; "Coincideix", "No coincideix"). També podeu utilitzar l'operador de desigualtat en lloc de l'operador d'igualtat. Només l'ordre dels valors que es mostraran en aquest cas serà lleugerament diferent: =SI(A2<>B2, “No coincideix”, “Coincideix”). Després d'utilitzar la primera versió de la fórmula, el resultat serà el següent.

Aquesta variació de la fórmula no distingeix entre majúscules i minúscules. Per tant, si els valors d'una columna difereixen dels altres només perquè s'escriuen en majúscules, el programa no notarà aquesta diferència. Per fer que la comparació distingeix entre majúscules i minúscules, heu d'utilitzar la funció als criteris EXACT. La resta d'arguments es deixen sense canvis: =SI(EXACTE(A2,B2), “Coincidència”, “Únic”).

Com comparar diverses columnes per a coincidències en una fila

És possible analitzar els valors de les llistes segons tot un conjunt de criteris:

1. Trobeu aquelles files que tinguin els mateixos valors a tot arreu.
2. Cerqueu aquelles files on hi ha coincidències en només dues llistes.

Vegem alguns exemples de com procedir en cadascun d'aquests casos.

Exemple. Com trobar coincidències en una fila en diverses columnes d'una taula

Suposem que tenim una sèrie de columnes que contenen la informació que necessitem. Ens trobem davant de la tasca de determinar aquelles files en què els valors són els mateixos. Per fer-ho, heu d'utilitzar la fórmula següent: =SI(AND(A2=B2,A2=C2), “concorda”, ” “).

Si hi ha massa columnes a la taula, només cal que les feu servir juntament amb la funció IF operador COUNTIF: =SI(COMPTARSI($A2:$C2,$A2)=3;”coincidència”;” “). El nombre utilitzat en aquesta fórmula indica el nombre de columnes que cal comprovar. Si és diferent, heu d'escriure tant com sigui cert per a la vostra situació.

Exemple. Com trobar coincidències en una fila en 2 columnes qualsevol d'una taula

Suposem que hem de comprovar si els valors d'una fila coincideixen en dues columnes amb els de la taula. Per fer-ho, heu d'utilitzar la funció com a condició OR, on escriu alternativament la igualtat de cadascuna de les columnes amb l'altra. Aquí teniu un exemple.

Utilitzem aquesta fórmula: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Pot haver-hi una situació en què hi hagi moltes columnes a la taula. En aquest cas, la fórmula serà enorme i pot trigar molt de temps seleccionar totes les combinacions necessàries. Per resoldre aquest problema, cal utilitzar la funció COUNTIF: =SI(CONTARSI(B2:D2,A2)+CONTARSI(C2:D2,B2)+(C2=D2)=0; “Cadena única”; “Cadena no única”)

Veiem que en total tenim dues funcions COUNTIF. Amb la primera, determinem alternativament quantes columnes tenen una semblança amb A2, i amb la segona, comprovem el nombre de semblances amb el valor de B2. Si, com a resultat del càlcul mitjançant aquesta fórmula, obtenim un valor zero, això indica que totes les files d'aquesta columna són úniques, si hi ha més, hi ha similituds. Per tant, si com a resultat de calcular per dues fórmules i sumar els resultats finals obtenim un valor zero, es retorna el valor de text "Cadena única", si aquest nombre és més gran, s'escriu que aquesta cadena no és única.

Com comparar 2 columnes a Excel per a coincidències

Ara prenguem un exemple. Suposem que tenim una taula amb dues columnes. Cal comprovar si coincideixen. Per fer-ho, cal aplicar la fórmula, on també s'utilitzarà la funció IF, i l'operador COUNTIF: =SI(COMPTARSI($B:$B,$A5)=0, “No hi ha coincidències a la columna B”, “Hi ha coincidències a la columna B”)

No cal més acció. Després de calcular el resultat mitjançant aquesta fórmula, obtenim si el valor del tercer argument de la funció IF partits. Si no n'hi ha, llavors el contingut del segon argument.

Com comparar 2 columnes a Excel per obtenir coincidències i ressaltar amb color

Per facilitar la identificació visual de columnes coincidents, podeu ressaltar-les amb un color. Per fer-ho, heu d'utilitzar la funció "Format condicional". Vegem a la pràctica.

Cercar i ressaltar coincidències per color en diverses columnes

Per determinar les coincidències i destacar-les, primer heu de seleccionar l'interval de dades en què es realitzarà la comprovació i, a continuació, obrir l'element "Format condicionat" a la pestanya "Inici". Allà, seleccioneu "Valors duplicats" com a regla de selecció de cel·les.

Després d'això, apareixerà un nou quadre de diàleg, en el qual a la llista emergent de l'esquerra trobem l'opció "Repetició", i a la llista de la dreta seleccionem el color que s'utilitzarà per a la selecció. Després de fer clic al botó "D'acord", es seleccionarà el fons de totes les cel·les amb semblances. A continuació, només cal comparar les columnes a ull.

Cercar i destacar línies coincidents

La tècnica per comprovar si les cordes coincideixen és lleugerament diferent. Primer, hem de crear una columna addicional i allà utilitzarem els valors combinats amb l'operador &. Per fer-ho, heu d'escriure una fórmula de la forma: =A2&B2&C2&D2.

Seleccionem la columna que s'ha creat i conté els valors combinats. A continuació, realitzem la mateixa seqüència d'accions que es descriu anteriorment per a les columnes. Les línies duplicades es ressaltaran amb el color que especifiqueu.

Veiem que no hi ha res de difícil buscar repeticions. Excel conté totes les eines necessàries per a això. És important només practicar abans de posar en pràctica tots aquests coneixements.