Tabelvergelykingmetodes in Microsoft Excel

Die gebruikers van Excel word dikwels gekonfronteer met die vergelyking van twee tabelle of lyste om verskille of elemente wat daarin ontbreek te identifiseer. Elke gebruiker hanteer hierdie taak op sy eie manier, maar meestal word 'n redelike hoeveelheid tyd spandeer aan die oplossing van hierdie probleem, omdat nie alle benaderings tot hierdie probleem rasioneel is nie. Terselfdertyd is daar verskeie beproefde aksie-algoritmes waarmee u lyste of tabelreeks in 'n redelike kort tydjie met minimale inspanning kan vergelyk. Kom ons kyk na hierdie opsies van naderby.

Kyk ook: Vergelyking van twee dokumente in MS Word

Vergelykingsmetodes

Daar is 'n hele paar maniere om tafelruimtes in Excel te vergelyk, maar hulle kan almal in drie groot groepe verdeel word:

Op grond van hierdie indeling word eerstens vergelykingsmetodes gekies, asook spesifieke handelinge en algoritmes word vir die taak bepaal. Byvoorbeeld, as u in verskillende boeke vergelyk, moet u twee Excel-lêers gelyktydig oopmaak.

Daarbenewens moet gesê word dat dit slegs sinvol is om tafelareas te vergelyk as hulle 'n soortgelyke struktuur het.

Metode 1: eenvoudige formule

Die maklikste manier om data in twee tabelle te vergelyk, is om 'n eenvoudige formule vir gelykheid te gebruik. As die data ooreenstem, gee dit die WARE aanwyser, en indien nie, dan ONWAAR. U kan beide numeriese en teksdata met mekaar vergelyk. Die nadeel van hierdie metode is dat dit slegs gebruik kan word as die gegewens in die tabel op dieselfde manier georden of gesorteer is, gesinkroniseer en dieselfde aantal reëls het. Kom ons kyk hoe u hierdie metode prakties kan gebruik met die voorbeeld van twee tabelle wat op een vel geplaas is.

Ons het dus twee eenvoudige tabelle met lyste van werknemers en hul salarisse. Dit is noodsaaklik om die lyste van werknemers te vergelyk en teenstrydighede tussen die kolomme waarin die name geplaas is, te identifiseer.

1. Om dit te kan doen, benodig ons 'n addisionele kolom op die blad. Ons gaan daar 'n bord in "=". Klik dan op die eerste item wat u in die eerste lys wil vergelyk. Ons plaas die simbool weer "=" vanaf die sleutelbord. Klik dan op die eerste sel van die kolom wat ons in die tweede tabel vergelyk. Die resultaat is 'n uitdrukking van die volgende tipe:

   = A2 = D2

   

   Alhoewel, natuurlik, in elk geval, sal die koördinate anders wees, maar die kern bly dieselfde.

2. Klik op die knoppie Tikom vergelykingsresultate te kry. Soos u kan sien, het die program, wanneer u die eerste selle van beide lyste vergelyk, 'n aanwyser aangedui "WAAR", wat ooreenstem met data.



3. Nou moet ons 'n soortgelyke operasie uitvoer met die ander selle van beide tabelle in die kolomme wat ons vergelyk. Maar u kan eenvoudig die formule kopieer, wat tyd aansienlik bespaar. Hierdie faktor is veral belangrik as u lyste met 'n groot aantal reëls vergelyk.

   Die kopieprosedure word maklik uitgevoer met behulp van die vulmerker. Ons hou oor die onderste regterkantste hoek van die sel, waar ons die aanwyser kry "WAAR". Terselfdertyd moet dit in 'n swart kruis omskep word. Dit is die vulmerker. Ons druk op die linkermuisknoppie en sleep die pyltjie neer op die aantal reëls in die vergelykende tabelreeks.



4. Soos u kan sien, word al die resultate van die datavergelyking in twee kolomme tabelarray's in 'n addisionele kolom getoon. In ons geval stem die data op slegs een reël nie ooreen nie. As u dit vergelyk, het die formule die resultaat opgelewer "ONWAAR". Soos ons sien, het die vergelykingsformule 'n aanwyser vir alle ander reëls "WAAR".



5. Daarbenewens is dit moontlik om die aantal teenstrydighede met behulp van 'n spesiale formule te bereken. Om dit te doen, kies die element van die blad waar dit vertoon sal word. Klik dan op die ikoon "Voeg funksie in".



6. In die venster Funksie towenaars in 'n groep operateurs "Wiskundige" kies die naam SUMPRODUCT. Klik op die knoppie "OK".



7. Die funksie-argumentvenster is geaktiveer. SUMPRODUCTwaarvan die belangrikste taak is om die som van die produkte van die geselekteerde reeks te bereken. Maar hierdie funksie kan vir ons doeleindes gebruik word. Die sintaksis is redelik eenvoudig:

   = SUMPRODUCT (skikking1; skikking2; ...)

   In totaal kan adresse tot 255 skikkings as argumente gebruik word. Maar in ons geval gebruik ons ​​slegs twee skikkings, boonop as een argument.

   Sit die wyser in die veld "Array1" en kies op die blad die vergelykende datareeks in die eerste gebied. Sit daarna 'n bord in die veld nie gelyk nie () en kies die vergelykende reeks van die tweede streek. Dan moet u die resulterende uitdrukking in hakies pak, waarna ons twee karakters plaas "-". In ons geval het hierdie uitdrukking geblyk:

   - (A2: A7D2: D7)

   Klik op die knoppie "OK".



8. Die operateur bereken en vertoon die resultaat. Soos u kan sien, is die resultaat in ons geval gelyk aan die getal "1", dit wil sê, dit beteken dat daar nie een ooreenstemming in die vergelykende lyste gevind is nie. As die lyste heeltemal identies was, sou die resultaat gelyk wees aan die getal "0".

Op dieselfde manier kan u data vergelyk in tabelle wat op verskillende velle geleë is. Maar in hierdie geval is dit wenslik dat die lyne daarin genommer is. Andersins is die vergelykingsprosedure byna presies dieselfde as hierbo beskryf, behalwe vir die feit dat wanneer u die formule invoer, u tussen die velle moet wissel. In ons geval sal die uitdrukking só lyk:

= B2 = Blad2! B2

Dit is, soos ons sien, voordat die koördinate van die gegewens wat op ander velle geleë is, anders as waar die resultaat van die vergelyking vertoon word, die velnommer en 'n uitroepteken aangedui word.

Metode 2: sel selgroepe te selekteer

Vergelyking kan met behulp van die selgroep seleksie-instrument gemaak word. Dit kan ook gebruik word om slegs sinchroniseerde en geordende lyste met mekaar te vergelyk. Daarbenewens moet die lyste in hierdie geval langs mekaar op dieselfde vel geplaas word.

1. Ons kies die vergelykende skikkings. Gaan na die oortjie "Home". Klik op die ikoon Soek en beklemtoonop die lint in die gereedskapskas geleë "Editing". 'N Lys word oopgemaak om 'n posisie te kies "Kies 'n groep selle ...".

   

   Daarbenewens kan ons na die gewenste venster gaan om 'n groep selle op 'n ander manier te kies. Hierdie opsie is veral nuttig vir gebruikers wat 'n weergawe van die program vroeër as Excel 2007 geïnstalleer het, aangesien die metode met die knoppie gedoen is Soek en beklemtoon hierdie toepassings ondersteun nie. Ons kies die skikkings wat ons wil vergelyk en druk op die toets F5.

2. 'N Klein oorgangsvenster word geaktiveer. Klik op die knoppie "Kies ..." in sy linker onderste hoek.



3. Daarna, watter een van die twee van die bogenoemde opsies wat u kies, word die venster vir seleksie van groepe selle geloods. Stel die skakelaar in posisie "Kies reël vir reël". Klik op die knoppie "OK".



4. Soos u kan sien, sal die waardeversameling van die lyne hierna met 'n ander kleur uitgelig word. Boonop, soos beoordeel kan word na aanleiding van die inhoud van die formulebalk, maak die program een ​​van die selle wat in die gespesifiseerde verkeerde reëls geleë is, aktief.

Metode 3: voorwaardelike formatering

U kan vergelyk met behulp van die voorwaardelike formateringsmetode. Soos in die vorige metode, moet die vergelykende gebiede op dieselfde Excel-werkblad wees en met mekaar gesinkroniseer word.

1. In die eerste plek kies ons watter tafelarea ons die belangrikste sal oorweeg, en om na verskille te soek. Laat ons die laaste in die tweede tabel doen. Daarom kies ons die lys werkers wat daarin geleë is. Deur na die oortjie te skuif "Home"Klik op die knoppie Voorwaardelike formateringwat op die band in die blok geleë is "Styles". Gaan na die keuselys Reëlbestuur.



2. Die reëlbestuurdervenster is geaktiveer. Klik op die knoppie daarin Skep reël.



3. Kies die posisie in die venster wat begin Gebruik Formule. In die veld "Formateer selle" skryf 'n formule met die adresse van die eerste selle van die reekse van die vergelykende kolomme, geskei deur 'n "nie gelykstaande" teken (). Slegs hierdie uitdrukking sal hierdie keer in die gesig staar. "=". Daarbenewens moet absolute adressering toegepas word op alle kolomkoördinate in hierdie formule. Om dit te doen, kies die formule met die muisaanwyser en druk drie keer op die toets F4. Soos u kan sien, het 'n dollarteken naby alle kolomadresse verskyn, wat beteken dat u skakels in absolute adresse moet maak. Vir ons spesifieke geval sal die formule die volgende vorm aanneem:

   = $ A2 $ D2

   Ons skryf hierdie uitdrukking in die bostaande veld. Klik daarna op die knoppie "Formaat ...".



4. Venster is geaktiveer Selformaat. Gaan na die oortjie "Vulsel". Hier in die lys van kleure stop ons die keuse van die kleur waarmee ons daardie elemente wil inkleur waar die data nie ooreenstem nie. Klik op die knoppie "OK".



5. Klik terug op die knoppie om terug te keer na die venster vir die skep van 'n formatteringsreël "OK".



6. Nadat u outomaties na die venster beweeg het Reëls Bestuurder Klik op die knoppie "OK" en daarin.



7. In die tweede tabel word elemente met data wat nie ooreenstem met die ooreenstemmende waardes van die eerste tabelarea, in die gekose kleur uitgelig.

Daar is 'n ander manier om voorwaardelike formatering op die taak toe te pas. Soos die vorige opsies, vereis dit die ligging van albei vergelykende gebiede op dieselfde blad, maar anders as die metodes wat voorheen beskryf is, is die voorwaarde vir die gesinchroniseer of sorteer van data nie verpligtend nie, wat hierdie opsie onderskei van die wat vroeër beskryf is.

1. Ons kies die gebiede wat vergelyk moet word.



2. Gaan na die oortjie wat genoem word "Home". Klik op die knoppie Voorwaardelike formatering. Kies die posisie in die geaktiveerde lys Seleksie-reëls. In die volgende menu kies ons die posisie Dupliseer waardes.



3. Die venster vir die instelling van die keuse van duplikaatwaardes begin. As u alles korrek gedoen het, dan bly dit in hierdie venster slegs om op die knoppie te klik "OK". Alhoewel, in die ooreenstemmende veld van hierdie venster, indien gewenst, u 'n ander kleur van die hoogtepunt kan kies.



4. Nadat ons die gespesifiseerde aksie uitgevoer het, word alle herhalende elemente in die gekose kleur uitgelig. Die elemente wat nie ooreenstem nie, sal in die oorspronklike kleur geverf word (standaard wit). U kan dus onmiddellik visueel sien wat die verskil tussen skikkings is.

As u wil, kan u, inteendeel, die elemente wat nie ooreenstem nie, inkleur en die aanwysers wat daarby pas, met dieselfde kleur laat. In hierdie geval is die algoritme van aksies byna dieselfde, maar in die instellingsvenster vir die uitlig van duplikaatwaardes in die eerste veld in plaas van die parameter "Dubbele" moet kies "Unieke". Klik daarna op die knoppie "OK".

Dus, juis die aanwysers wat nie saamval nie, sal uitgelig word.

Les: Voorwaardelike formatering in Excel

Metode 4: komplekse formule

U kan ook data met behulp van 'n komplekse formule vergelyk op grond van die funksie COUNTIF. Met behulp van hierdie instrument kan u bereken hoeveel elke element uit die geselekteerde kolom van die tweede tabel in die eerste herhaal word.

operateur COUNTIF verwys na 'n statistiese groep funksies. Die taak is om die aantal selle te tel waarvan die waardes aan 'n gegewe voorwaarde voldoen. Die sintaksis van hierdie operateur is soos volg:

= COUNTIF (reeks; maatstaf)

argument "Range" stel die adres voor van die skikking waarin die ooreenstemmende waardes bereken word.

argument "Criterion" stel 'n wedstrydvoorwaarde. In ons geval sal dit die koördinate van spesifieke selle in die eerste tabelarea wees.

1. Ons kies die eerste element van die bykomende kolom waarin die aantal wedstryde getel sal word. Klik op die ikoon "Voeg funksie in".



2. Begin Funksie towenaars. Gaan na die kategorie "Statistiek". Soek die naam in die lys "COUNTIF". Nadat u dit gekies het, klik op die knoppie "OK".



3. Operator Argument Venster begin COUNTIF. Soos u kan sien, stem die name van die velde in hierdie venster ooreen met die name van die argumente.

   Stel die wyser in die veld "Range". Hou daarna die linkermuisknop vas en kies al die waardes van die kolom met die name van die tweede tabel. Soos u kan sien, val die koördinate onmiddellik in die gespesifiseerde veld. Maar vir ons doeleindes moet hierdie adres absoluut gemaak word. Om dit te kan doen, selekteer u hierdie koördinate in die veld en druk op die toets F4.

   Soos u kan sien, het die skakel 'n absolute vorm aangeneem, wat gekenmerk word deur die teenwoordigheid van dollartekens.

   Gaan dan veld toe "Criterion"deur die wyser daar te stel. Ons klik op die eerste element met van in die eerste tabelreeks. Laat in hierdie geval die skakel relatief. Nadat dit in die veld vertoon is, kan u op die knoppie klik "OK".



4. Die resultaat word in die bladelement vertoon. Dit is gelyk aan die getal "1". Dit beteken dat die familienaam in die lys name van die tweede tabel verskyn "Grinev V.P.", wat die eerste is in die lys van die eerste tabelreeks, kom een ​​keer voor.



5. Nou moet ons 'n soortgelyke uitdrukking vir alle ander elemente van die eerste tabel skep. Om dit te doen, sal ons kopieer met die vulmerker, soos ons al voorheen gedoen het. Plaas die wyser regs onder in die bladelement wat die funksie bevat COUNTIF, en nadat u dit na 'n vulmerker omgeskakel het, hou u die linkermuisknop ingedruk en sleep die muis na onder.



6. Soos u kan sien, het die program die toeval bereken deur elke sel van die eerste tabel met data in die tweede tabelreeks te vergelyk. In vier gevalle kom die resultaat uit "1", en in twee gevalle - "0". Dit wil sê, die program kon nie in die tweede tabel twee waardes vind wat in die eerste tabelreeks is nie.

Natuurlik kan hierdie uitdrukking, om tabelle-aanwysers te vergelyk, in die bestaande vorm gebruik word, maar daar is 'n geleentheid om dit te verbeter.

Ons sorg dat die waardes wat in die tweede tabel voorkom, maar nie in die eerste tabel is nie, in 'n aparte lys vertoon word.

1. In die eerste plek sal ons ons formule effens verwerk COUNTIFnaamlik, ons maak dit een van die argumente van die operateur INDIEN. Om dit te doen, kies die eerste sel waarin die operateur geleë is COUNTIF. Voeg die uitdrukking in die formulereël daarvoor "AS" sonder aanhalings en maak die hakie oop. Volgende, om die werk makliker te maak, kies die waarde in die formulebalk "AS" en klik op die ikoon "Voeg funksie in".



2. Die funksie-argumente-venster word oopgemaak INDIEN. Soos u kan sien, is die eerste veld van die venster reeds gevul met die waarde van die operateur COUNTIF. Maar ons moet iets anders by hierdie veld voeg. Ons stel die muis daar en voeg by die bestaande uitdrukking "=0" sonder aanhalings.

   Gaan daarna veld toe "Betekenis as dit waar is". Hier sal ons 'n ander geneste funksie gebruik - STRING. Tik die woord in "LINE" sonder aanhalings, maak dan die hakies oop en dui die koördinate van die eerste sel met die familienaam in die tweede tabel aan, en maak dan die hakies toe. Spesifiek, in ons geval, in die veld "Betekenis as dit waar is" Die volgende uitdrukking blyk:

   LYN (D2)

   Nou die operateur STRING sal funksies rapporteer INDIEN die nommer van die reël waarin 'n spesifieke naam geleë is, en indien die voorwaarde wat in die eerste veld gespesifiseer is, is die funksie INDIEN sal hierdie nommer in die sel vertoon. Klik op die knoppie "OK".



3. Soos u kan sien, word die eerste resultaat vertoon as "ONWAAR". Dit beteken dat die waarde nie aan die operateur se voorwaardes voldoen nie. INDIEN. Die eerste van is in albei lyste aanwesig.



4. Ons gebruik die vulmerk om die gebruikeruitdrukking op die gewone manier te kopieer INDIEN op die hele kolom. Soos u kan sien, vir die twee posisies wat in die tweede tabel aanwesig is, maar nie in die eerste nie, gee die formule lynnommers.



5. Ons vertrek vanaf die tafelregterkant en vul die kolom met syfers in volgorde, vanaf 1. Die aantal getalle moet ooreenstem met die aantal rye in die tweede tabel wat vergelyk moet word. Om die nommeringsproses te bespoedig, kan u ook die vulmerker gebruik.



6. Kies daarna die eerste sel aan die regterkant van die kolom met syfers en klik op die ikoon "Voeg funksie in".



7. Open Funksie-towenaar. Gaan na die kategorie "Statistiek" en maak 'n keuse van die naam "Klein". Klik op die knoppie "OK".



8. funksie KLEINwie se argumentvenster oopgemaak is, is bedoel om die kleinste waarde wat in die rekening gespesifiseer is, te vertoon.

   In die veld "Array" spesifiseer die koördinate van die omvang van die addisionele kolom "Aantal wedstryde"wat ons voorheen met die funksie omgeskakel het INDIEN. Ons maak alle skakels absoluut.

   In die veld "K" dui aan watter rekening die laagste waarde vertoon moet word. Hier word die koördinate van die eerste sel van die kolom met nommering aangedui, wat ons onlangs bygevoeg het. Ons laat die adres relatief. Klik op die knoppie "OK".



9. Die operateur vertoon die resultaat - 'n nommer 3. Dit is dit die kleinste van die nommering van ongelyke rye tafelarray's. Kopieer die formule met die vulmerk na onder.



10. As ons die lynnommers van die elemente wat nie ooreenstem nie, ken, kan ons hul waardes met behulp van die funksie in die sel invoeg INDEKS. Kies die eerste element van die blad wat die formule bevat KLEIN. Gaan daarna na die reël formules en voor die naam "Klein" voeg die naam by "Index" sonder aanhalings, maak die hakie onmiddellik oop en sit 'n semikolon (;). Kies dan die naam in die formule-lyn "Index" en klik op die ikoon "Voeg funksie in".



11. Daarna word 'n klein venster oopgemaak waarin u moet bepaal of die verwysingsaansig 'n funksie moet hê INDEKS of ontwerp om met skikkings te werk. Ons het die tweede opsie nodig. Dit is standaard geïnstalleer, dus klik in hierdie venster net op die knoppie "OK".



12. Die funksie-argumentvenster begin INDEKS. Hierdie operateur is bedoel om 'n waarde uit te voer wat in 'n spesifieke skikking in die gespesifiseerde string geleë is.

    Soos u kan sien, is die veld Lyn nommer reeds gevul met funksiewaardes KLEIN. Van die waarde wat daar reeds bestaan, moet die verskil tussen die nommering van die Excel-blad en die interne nommering van die tabelarea afgetrek word. Soos u kan sien, het ons slegs 'n kop bo die tabelwaardes. Dit beteken dat die verskil een lyn is. Daarom voeg ons in die veld Lyn nommer betekenis "-1" sonder aanhalings.

    In die veld "Array" spesifiseer die adres van die waardeversameling van die tweede tabel. Terselfdertyd maak ons ​​alle koördinate absoluut, dit wil sê, ons plaas die dollar-teken voor hulle op die manier wat ons voorheen beskryf het.

    Klik op die knoppie "OK".



13. Nadat ons die resultaat op die skerm vertoon het, brei ons die funksie uit met die vulmerker aan die onderkant van die kolom. Soos u kan sien, word albei vanne wat in die tweede tabel voorkom, maar nie in die eerste nie, in 'n aparte reeks vertoon.

Metode 5: vergelyk skikkings in verskillende boeke

As u reekse in verskillende boeke vergelyk, kan u die bogenoemde metodes gebruik, behalwe vir die opsies waar u beide tafelareas op een vel wil plaas. Die belangrikste voorwaarde vir die vergelykingsprosedure in hierdie geval is om die vensters van albei lêers gelyktydig oop te maak. Vir weergawes van Excel 2013 en later, sowel as vir weergawes voor Excel 2007, is daar geen probleme met hierdie toestand nie. Maar in Excel 2007 en Excel 2010, is beide manipulasies nodig om albei vensters terselfdertyd oop te maak. Hoe u dit doen, word in 'n aparte les beskryf.

Les: Hoe om Excel in verskillende vensters oop te maak

Soos u kan sien, is daar 'n aantal moontlikhede om tabelle onder mekaar te vergelyk. Watter opsie om te gebruik hang af van die presiese tabelvorm relatief tot mekaar (op een vel, in verskillende boeke, op verskillende velle), en ook hoe presies die gebruiker wil hê dat hierdie vergelyking op die skerm vertoon moet word.