COMPARACIó DE FULLS DE CàLCUL EXCEL

Sovint, els usuaris d’Excel s'enfronten a la tasca de comparar dues taules o llistes per identificar les diferències o els elements que falten. Cada usuari fa front a aquesta tasca a la seva manera, però sovint es dedica una quantitat de temps bastant gran per resoldre aquest problema, ja que no tots els enfocaments d'aquest problema són racionals. Al mateix temps, hi ha diversos algorismes d’acció comprovats que us permetran comparar llistes o matrius de taules en un temps bastant curt amb un esforç mínim. Fem una ullada a aquestes opcions.

Vegeu també: Comparació de dos documents en MS Word

Mètodes de comparació

Hi ha moltes maneres de comparar espais de taula en Excel, però tots es poden dividir en tres grans grups:

comparació de llistes que es troben en el mateix full;

comparació de taules situades en diferents fulls;

comparació dels rangs de taula en diferents fitxers.

Sobre la base d’aquesta classificació s’escullen, en primer lloc, mètodes de comparació i es determinen accions i algorismes específics per realitzar la tasca. Per exemple, quan feu comparacions en diferents llibres, cal obrir simultàniament dos fitxers Excel.

A més, cal dir que la comparació d’espais de taula només té sentit quan tenen una estructura similar.

Mètode 1: fórmula senzilla

La manera més fàcil de comparar dades en dues taules és utilitzar una fórmula d’igualtat simple. Si les dades coincideixen, donarà el valor TRUE i, si no, llavors - FALSE. És possible comparar, tant les dades numèriques com el text. El desavantatge d’aquest mètode és que només es pot utilitzar si les dades de la taula s’ordenen o s'ordenen de la mateixa manera, sincronitzades i tenen un nombre igual de línies. Vegem com utilitzar aquest mètode en la pràctica a l’exemple de dues taules situades en un full.

Per tant, tenim dues taules senzilles amb llistes d’empleats i els seus salaris. És necessari comparar les llistes d'empleats i identificar les inconsistències entre les columnes en què es col·loquen els noms.

Per això necessitem una columna addicional al full. Introduïu el signe allà "=". A continuació, feu clic al primer element que cal comparar a la primera llista. De nou posem el símbol "=" des del teclat. A continuació, feu clic a la primera cel·la de la columna, que es compara, a la segona taula. L’expressió és del següent tipus:
= A2 = D2
Tot i que, per descomptat, en cada cas les coordenades seran diferents, però l'essència seguirà sent la mateixa.
Feu clic al botó Introduïuper obtenir resultats de comparació. Com podeu veure, en comparar les primeres cel·les d’ambdues llistes, el programa indicava un indicador "TRUE"el que significa coincidència de dades.

Ara hem de fer una operació similar amb les cel·les restants de les dues taules a les columnes que comparem. Però simplement podeu copiar la fórmula, que permetrà estalviar significativament temps. Aquest factor és especialment important quan es comparen llistes amb un gran nombre de línies.
El procediment de còpia és més fàcil de fer amb el mànec de farciment. Posem el cursor a la cantonada inferior dreta de la cel·la, on vam obtenir l’indicador "TRUE". Al mateix temps, s'hauria de convertir en una creu negra. Aquest és el marcador d’ompliment. Feu clic al botó esquerre del ratolí i arrossegueu el cursor cap avall pel nombre de línies de les matrius de taula comparades.

Com veiem, ara a la columna addicional es mostren tots els resultats de la comparació de dades en dues columnes de matrius tabulars. En el nostre cas, les dades no coincideixen només en una línia. En comparació, la fórmula va donar el resultat "FALS". Per a totes les altres línies, com podeu veure, la fórmula de comparació va donar l’indicador "TRUE".

A més, és possible calcular el nombre de discrepàncies mitjançant una fórmula especial. Per fer-ho, seleccioneu l'element del full, on es mostrarà. A continuació, feu clic a la icona "Insereix la funció".

A la finestra Màsters de funcions en un grup d’operadors "Matemàtica" seleccioneu el nom SUMPRODUCT. Feu clic al botó "D'acord".

S'activa la finestra d’argument de funció. SUMPRODUCTLa tasca principal és calcular la suma dels productes del rang seleccionat. Però aquesta funció es pot utilitzar per als nostres propòsits. La seva sintaxi és bastant simple:
= SUMPRODUCT (array1; array2; ...)
En total, podeu utilitzar adreces de fins a 255 matrius com a arguments. Però, en el nostre cas, només utilitzarem dues matrius, com a argument.
Poseu el cursor al camp "Massive1" i seleccioneu l'interval de dades comparades a la primera àrea del full. Després vam posar una marca al camp. "no igual" () i seleccioneu l’interval de comparació de la segona regió. A continuació, envoliu l’expressió resultant amb claudàtors, abans de la qual posem dos personatges "-". En el nostre cas, tenim l’expressió següent:
- (A2: A7D2: D7)
Feu clic al botó "D'acord".

L’operador calcula i mostra el resultat. Com veiem, en el nostre cas el resultat és igual al nombre "1", és a dir, significa que en les llistes comparades es va trobar un desajustament. Si les llistes fossin completament idèntiques, el resultat seria igual al nombre "0".

De la mateixa manera, podeu comparar dades de taules que es troben en diferents fulls. Però en aquest cas és desitjable que les línies en elles estiguin numerades. La resta del procediment de comparació és gairebé exactament el mateix que el descrit anteriorment, excepte el fet que quan feu una fórmula, heu de canviar entre els fulls. En el nostre cas, l’expressió tindrà el següent formulari:

= B2 = Full2! B2

És a dir, com veiem, abans que les coordenades de les dades, que es troben en altres fulls, siguin diferents del lloc on es mostra el resultat de la comparació, s'indiquen el número del full i el signe d’exclamació.

Mètode 2: seleccioneu grups de cel·les

La comparació es pot fer mitjançant l’eina de selecció del grup de cel·les. Amb ell, també podeu comparar només llistes sincronitzades i ordenades. A més, en aquest cas, les llistes s'han de situar al costat de l’altra al mateix full.

Seleccioneu les matrius comparades. Aneu a la pestanya "Inici". A continuació, feu clic a la icona "Cerca i ressalta"que es troba a la cinta del bloc d'eines Edició. S'obrirà una llista en la qual haureu de seleccionar una posició. "Seleccionant un grup de cel·les ...".
A més, a la finestra desitjada de selecció d’un grup de cel·les es pot accedir d’una altra manera. Aquesta opció serà especialment útil per a aquells usuaris que hagin instal·lat la versió del programa abans que Excel 2007, perquè el mètode a través del botó "Cerca i ressalta" Aquestes aplicacions no són compatibles. Seleccioneu les matrius que volem comparar i premeu la tecla F5.
S'ha activat una petita finestra de transició. Feu clic al botó "Ressalta ..." a la cantonada inferior esquerra.

Després d'això, qualsevol de les dues opcions anteriors que trieu, es llança una finestra per seleccionar grups de cel·les. Poseu el commutador en posició "Selecciona per fila". Feu clic al botó "D'acord".

Com podeu veure, després d'això, els valors no coincidents de les files es ressaltaran amb un to diferent. A més, com es pot jutjar a partir del contingut de la línia de fórmula, el programa farà que una de les cel·les estigui activa a les línies sense igual especificades.

Mètode 3: formatació condicional

Podeu fer una comparació mitjançant el mètode de formatació condicional. Com en el mètode anterior, les àrees comparades haurien d'estar en el mateix full de càlcul d'Excel i ser sincronitzades entre elles.

Primer de tot, escollim quin espai de taula considerem el principal i quin serà el de buscar diferències. Finalment ho farem a la segona taula. Per tant, seleccioneu la llista d'empleats ubicats en ella. Anar a la pestanya "Inici", feu clic al botó "Format condicional"que es troba a la cinta del bloc "Estils". A la llista desplegable, continua "Gestió de la regla".

S'activa la finestra del gestor de regles. Pressionem el botó "Crea una regla".

A la finestra de llançament, trieu la posició "Utilitza la fórmula". Al camp "Format de cel·les" escriviu la fórmula que conté les adreces de les primeres cel·les dels rangs de les columnes comparades, separades pel signe "no igual" (). Aquesta expressió només tindrà un signe aquesta vegada. "=". A més, s'ha d’aplicar l’adreçament absolut a totes les coordenades de columna d’aquesta fórmula. Per fer-ho, seleccioneu la fórmula amb el cursor i feu clic tres vegades a la tecla F4. Com podeu veure, un signe de dòlar va aparèixer a prop de totes les adreces de les columnes, el que significa convertir els enllaços en absoluts. Per al nostre cas particular, la fórmula tindrà la forma següent:
= $ A2 $ D2
Escrivim aquesta expressió en el camp anterior. Després fes clic al botó "Format ...".

Finestra activada "Format de cel·les". Aneu a la pestanya "Omplir". Aquí, a la llista de colors, deixem de triar el color amb el qual volem acolorir aquells elements on les dades no coincidiran. Premeu el botó "D'acord".

Tornant a la finestra per crear una regla de format, feu clic al botó. "D'acord".

Després de moure automàticament a la finestra Gestor de regles feu clic al botó "D'acord" i en ella.

Ara a la segona taula, els elements que tenen dades que no coincideixen amb els valors corresponents de la primera àrea de taula es ressaltaran en el color seleccionat.

Hi ha una altra manera d’utilitzar el format condicional per aconseguir la tasca. Igual que les opcions anteriors, requereix la ubicació de les dues àrees comparades en el mateix full, però a diferència dels mètodes descrits anteriorment, la condició per sincronitzar o ordenar les dades no serà necessària, cosa que distingeix aquesta opció de les descrites anteriorment.

Feu una selecció d’àrees que s’hauran de comparar.

Realitzeu una transició a la pestanya anomenada "Inici". Feu clic al botó. "Format condicional". A la llista activada, seleccioneu la posició "Regles per a la selecció de cel·les". Al següent menú fem una elecció de la posició. "Valors duplicats".

Es posa en marxa la finestra per configurar la selecció de valors duplicats. Si ho heu fet tot correctament, només es pot fer clic en aquest botó en aquesta finestra. "D'acord". Tot i que, si voleu, podeu seleccionar un color de selecció diferent al camp corresponent d'aquesta finestra.

Després de realitzar l’acció especificada, tots els elements duplicats es ressaltaran en el color seleccionat. Aquells elements que no coincideixen romandran acolorits pel seu color original (per defecte, blanc). Per tant, podeu veure de forma immediata quina és la diferència entre les matrius.

Si ho desitgeu, podeu, per contra, pintar elements que no coincideixin i que es deixin aquells indicadors que coincideixin amb el mateix color. En aquest cas, l'algorisme de les accions és gairebé el mateix, però a la finestra de configuració per ressaltar els valors duplicats al primer camp en lloc del paràmetre "Duplica" seleccioneu l'opció "Únic". Després, feu clic al botó "D'acord".

Per tant, es destacaran els indicadors que no coincideixen.

Lliçó: Format condicional a Excel

Mètode 4: fórmula complexa

També podeu comparar dades utilitzant una fórmula complexa, que es basa en la funció COUNTES. Mitjançant aquesta eina, podeu calcular quant es repeteix cada primer element de la columna seleccionada de la segona taula.

Operador COUNTES fa referència a un grup estadístic de funcions. La seva tasca és comptar el nombre de cèl·lules els valors dels quals compleixen una determinada condició. La sintaxi d’aquest operador és la següent:

= COMPTES (rang, criteri)

Argument "Interval" és l'adreça de la matriu en la qual es calculen els valors coincidents.

Argument "Criteri" estableix la condició de concordança. En el nostre cas, seran les coordenades de cèl·lules específiques del primer espai de taula.

Seleccioneu el primer element de la columna addicional en què es calcularà el nombre de coincidències. A continuació, feu clic a la icona "Insereix la funció".

S'inicia el llançament Màsters de funcions. Vés a la categoria "Estadística". Cerqueu a la llista el nom "COUNTES". Després de seleccionar-lo, feu clic al botó. "D'acord".

Es llança la finestra d’argument d’operador. COUNTES. Com podeu veure, els noms dels camps d’aquesta finestra corresponen als noms dels arguments.
Situeu el cursor al camp "Interval". Després d'això, mantenint el botó esquerre del ratolí, seleccioneu tots els valors de la columna amb els noms de la segona taula. Com podeu veure, les coordenades cauen immediatament al camp especificat. Però per als nostres propòsits, aquesta adreça s'hauria de fer absoluta. Per fer-ho, seleccioneu les coordenades al camp i feu clic a la tecla F4.
Com podeu veure, l’enllaç ha tingut una forma absoluta, que es caracteritza per la presència de signes de dòlars.
Després, aneu al camp "Criteri"establint el cursor allà. Feu clic al primer element amb cognoms a la primera àrea de taula. En aquest cas, deixeu l’enllaç relatiu. Després de mostrar-se al camp, podeu fer clic al botó "D'acord".

El resultat es mostra a l’element del full. És igual al nombre "1". Això significa que en la llista de noms de la segona taula el cognom "Grinev V.P."que és la primera de la llista de la primera matriu de taula, es produeix una vegada.

Ara hem de crear una expressió similar per a tots els altres elements de la primera taula. Per fer-ho, copieu-lo amb el marcador de farciment, tal com hem fet abans. Poseu el cursor a la part inferior dreta de l’element de full que conté la funció COUNTES, i després de convertir-lo en el marcador d’ompliment, manteniu premut el botó esquerre del ratolí i arrossegueu el cursor cap avall.

Com podeu veure, el programa va fer un càlcul de coincidències comparant cada cel·la de la primera taula amb les dades que es troben a la segona gamma de taules. En quatre casos, va sortir el resultat "1", i en dos casos - "0". És a dir, el programa no va poder trobar a la segona taula els dos valors que es troben a la primera matriu de taules.

Per descomptat, aquesta expressió per tal de comparar els indicadors de taula es pot aplicar en el formulari existent, però hi ha una oportunitat per millorar-la.

Anem a fer que els valors disponibles a la segona taula, però absents en el primer, es mostrin en una llista separada.

Primer de tot, redissenyem la nostra fórmula COUNTES, és a dir, que sigui un dels arguments de l’operador SI. Per fer-ho, seleccioneu la primera cel·la en què es troba l’operador COUNTES. A la barra de fórmules abans d’aquesta afegim l’expressió "SI" sense pressupostos i obriu el claudàtor. A continuació, per facilitar-ne la feina, seleccionem el valor a la barra de fórmules. "SI" i feu clic a la icona "Insereix la funció".

S'obrirà la finestra d’argument de funció. SI. Com podeu veure, el primer camp de la finestra ja està ple del valor de l’operador. COUNTES. Però hem d’afegir alguna cosa més en aquest camp. Posem el cursor allà i afegim a l’expressió ja existent "=0" sense pressupostos.
Després, aneu al camp "Valor si és cert". Aquí utilitzarem una altra funció niada - LÍNIA. Introduïu la paraula "LÍNIA" sense cometes, obriu els parèntesis i especifiqueu les coordenades de la primera cel·la amb el cognom a la segona taula, i tanqueu els parèntesis. Concretament, en el nostre cas, en el camp "Valor si és cert" va obtenir la següent expressió:
LÍNIA (D2)
Ara l’operador LÍNIA informarà de funcions SI el número de línia en què es troba el cognom, i en el cas que es compleixi la condició especificada al primer camp, la funció SI mostrarà aquest número a la cel·la. Premeu el botó "D'acord".

Com podeu veure, el primer resultat es mostra com "FALS". Això significa que el valor no compleix les condicions de l’operador. SI. És a dir, el primer cognom està present a les dues llistes.

Utilitzant el marcador d’ompliment, de la manera habitual copiem l’expressió de l’operador SI a tota la columna. Com podeu veure, en dues posicions presents a la segona taula, però no a la primera, la fórmula dóna números de línia.

Retirar-se de l’espai de taula a la dreta i omplir la columna amb els números en ordre, a partir de 1. El nombre de números ha de coincidir amb el nombre de files de la segona taula comparada. Per accelerar el procediment de numeració, també podeu utilitzar el marcador d’ompliment.

Després, seleccioneu la primera cel·la a la dreta de la columna amb números i feu clic a la icona "Insereix la funció".

Obre Auxiliar de funcions. Vés a la categoria "Estadística" i fer una selecció de noms "EL NOM". Feu clic al botó "D'acord".

Funció EL MÍNIM, la finestra de arguments del qual s’ha obert, està dissenyada per mostrar el valor més baix especificat pel compte.
Al camp "Matriu" especifiqueu les coordenades del rang de la columna addicional "Nombre de partits"que anteriorment hem convertit utilitzant la funció SI. Fem tots els enllaços absoluts.
Al camp "K" indica quin compte hauria de mostrar el valor més baix. Aquí indiquem les coordenades de la primera cel·la de la columna amb numeració, que hem afegit recentment. L’adreça es deixa relativa. Feu clic al botó "D'acord".

L’operador mostra el resultat: el número 3. Aquesta és la numeració més petita de files no coincidents de les matrius de taules. Utilitzant el marcador d’ompliment, copieu la fórmula a la part inferior.

Ara, coneixent els números de línia dels elements no coincidents, podem inserir-nos a la cel·la i els seus valors utilitzant la funció ÍNDEX. Seleccioneu el primer element del full que conté la fórmula EL MÍNIM. Després passem a la línia de fórmula i abans del nom "EL NOM" afegir el nom ÍNDEX sense pressupostos, obriu immediatament el claudàtor i poseu un punt i coma (;). A continuació, seleccioneu el nom a la barra de fórmules. ÍNDEX i feu clic a la icona "Insereix la funció".

Després d'això, s'obrirà una petita finestra en la qual haureu de determinar si la referència ha de tenir una funció ÍNDEX o dissenyat per treballar amb matrius. Necessitem la segona opció. Es defineix per defecte, de manera que en aquesta finestra simplement feu clic al botó. "D'acord".

S'inicia la finestra d'argument de la funció. ÍNDEX. Aquesta declaració està dissenyada per mostrar el valor que es troba en una matriu específica a la línia especificada.
Com podeu veure, el camp "Número de línia" ja omplert amb valors de funció EL MÍNIM. A partir del valor que ja existeix, resti la diferència entre la numeració del full d’Excel i la numeració interna de l’àrea de la taula. Com podeu veure, per sobre dels valors de la taula només tenim una tapa. Això significa que la diferència és una línia. Per tant, afegim al camp "Número de línia" significat "-1" sense pressupostos.
Al camp "Matriu" especifiqueu l’adreça de l’interval de valors de la segona taula. Al mateix temps, fem totes les coordenades absolutes, és a dir, posem un signe de dòlar davant d'ells de la manera descrita anteriorment.
Premeu el botó "D'acord".

Després de sortir el resultat a la pantalla, estirarem la funció utilitzant el marcador de farciment fins al final de la columna cap avall. Com podeu veure, els dos cognoms presents a la segona taula, però no en el primer, es mostren en un rang separat.

Mètode 5: Comparació de matrius en diferents llibres

En comparar els rangs en diferents llibres, podeu utilitzar els mètodes esmentats anteriorment, excloent les opcions que requereixen la col·locació dels dos espais de taula en un full. La condició principal per dur a terme el procediment de comparació en aquest cas és obrir les finestres dels dos fitxers alhora. No hi ha problemes per a les versions d’Excel 2013 i posteriors, així com per a versions anteriors a Excel 2007. Però a Excel 2007 i Excel 2010, per obrir les dues finestres al mateix temps, es necessiten manipulacions addicionals. Com es fa això es descriu en una lliçó separada.

Lliçó: com obrir Excel en diferents finestres

Com podeu veure, hi ha diverses possibilitats de comparar taules entre elles. Quina opció d’utilitzar depèn exactament d’on es troben les dades tabulars entre elles (en un full, en diferents llibres, en diferents fulls) i també sobre com l’usuari vol que aquesta comparació es mostri a la pantalla.