Metodes vir die vergelyking van tabelle in Microsoft Excel

Heel dikwels word Excel-gebruikers gekonfronteer met die taak om twee tabelle of lyste te vergelyk om verskille of ontbrekende elemente in hulle te identifiseer. Elke gebruiker hanteer hierdie taak op sy eie manier, maar meestal word 'n baie groot hoeveelheid tyd bestee aan die oplossing van hierdie probleem, aangesien nie alle benaderings tot hierdie probleem rasioneel is nie. Terselfdertyd is daar verskeie bewese aksie algoritmes wat u in staat sal stel om lyste of tabelraamwerke in 'n redelike kort tyd met minimale inspanning te vergelyk. Kom ons kyk noukeuriger na hierdie opsies.

Sien ook: Vergelyking van twee dokumente in MS Word

Vergelyking Metodes

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

Op grond van hierdie klassifikasie word eerstens vergelykingsmetodes gekies, en spesifieke aksies en algoritmes vir die uitvoering van die taak word bepaal. Byvoorbeeld, wanneer vergelykings in verskillende boeke gemaak word, moet jy twee Excel-lêers gelyktydig oopmaak.

Daarbenewens moet gesê word dat die vergelyking van tafelspasies slegs sin maak wanneer hulle 'n soortgelyke struktuur het.

Metode 1: eenvoudige formule

Die maklikste manier om data in twee tabelle te vergelyk, is om 'n eenvoudige gelykheidsformule te gebruik. As die data ooreenstem, dan gee dit die WAAR waarde, en indien nie, dan - ONWAAR. Dit is moontlik om te vergelyk, beide numeriese data en teks. Die nadeel van hierdie metode is dat dit slegs gebruik kan word indien die data in die tabel op dieselfde manier bestel of gesorteer word, gesinkroniseer en 'n gelyke aantal lyne het. Kom ons kyk hoe om hierdie metode in die praktyk te gebruik op die voorbeeld van twee tabelle wat op een blad geplaas is.

Dus, ons het twee eenvoudige tafels met lyste van werknemers en hul salarisse. Dit is nodig om die lyste van werknemers te vergelyk en teenstrydighede tussen die kolomme waarin die name geplaas word, te identifiseer.

1. Hiervoor benodig ons 'n ekstra kolom op die vel. Tik die bord daar in "=". Klik dan op die eerste item wat in die eerste lys vergelyk moet word. Weereens sit ons die simbool "=" van die sleutelbord. Klik dan op die eerste sel van die kolom, wat ons vergelyk, in die tweede tabel. Die uitdrukking is van die volgende tipe:

   = A2 = D2

   

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

2. Klik op die knoppie Tikvergelykingsuitslae te kry. Soos u kan sien, het die program 'n aanduider aangedui met die vergelyking van die eerste selle van albei lyste "WAAR"wat beteken dat die data ooreenstem.



3. Nou moet ons 'n soortgelyke operasie met die oorblywende selle van albei tabelle in die kolomme wat ons vergelyk, uitvoer. Maar jy kan eenvoudig die formule kopieer, wat aansienlik tyd sal bespaar. Hierdie faktor is veral belangrik wanneer lyste met 'n groot aantal lyne vergelyk word.

   Die kopieerprosedure is maklikste om uit te voer met behulp van die vulhantering. Ons plaas die wyser in die onderste regterhoek van die sel waar ons die aanwyser het "WAAR". Terselfdertyd moet dit omgeskakel word na 'n swart kruis. Dit is die vul merker. Klik die linker muisknop en sleep die wyser af deur die aantal lyne in die vergelyke tabelraamwerke.



4. Soos ons sien, word nou al in die bykomende kolom alle resultate van data vergelyking in twee kolomme van tabelraamwerke vertoon. In ons geval pas die data nie net in een reël nie. In vergelyking het die formule die resultaat opgelewer "ONWAAR". Vir al die ander lyne, soos u kan sien, het die vergelyking formule die aanwyser opgelewer "WAAR".



5. Daarbenewens is dit moontlik om die aantal verskille 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 Meesters in 'n groep operateurs "Wiskundige" kies die naam SUMPRODUCT. Klik op die knoppie "OK".



7. Die funksie argument venster is geaktiveer. SUMPRODUCTwie se hooftaak is om die som van die produkte van die gekose reeks te bereken. Maar hierdie funksie kan vir ons doeleindes gebruik word. Die sintaksis is redelik eenvoudig:

   = SUMPRODUCT (array1; array2; ...)

   In totaal kan jy adresse van tot 255 skikkings as argumente gebruik. Maar in ons geval sal ons net twee skikkings gebruik as een argument.

   Plaas die wyser in die veld "Array1" en kies die vergelyking van die data in die eerste area op die vel. Daarna sit ons 'n punt in die veld. "nie gelyk nie" () en kies die vergelyking van die tweede streek. Draai dan die volgende uitdrukking met hakies in, voor wat ons twee karakters plaas "-". In ons geval kry ons die volgende uitdrukking:

   - (A2: A7D2: D7)

   Klik op die knoppie "OK".



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

Op dieselfde manier kan jy data vergelyk in tabelle wat op verskillende velle geleë is. Maar in hierdie geval is dit wenslik dat die lyne in hulle getel word. Die res van die vergelyking is amper presies dieselfde as hierbo beskryf, behalwe dat jy 'n formule moet maak om tussen die lakens te wissel. In ons geval sal die uitdrukking die volgende vorm hê:

= B2 = Blad2! B2

Dit is, soos ons sien, voor die koördinate van die data wat op ander velle voorkom, anders as waar die uitslag van die vergelyking vertoon word, word die nommer van die vel en die uitroepteken aangedui.

Metode 2: Kies groepe van selle

Vergelyking kan gemaak word met behulp van die selgroep seleksie instrument. Daarmee kan jy net gesinkroniseerde en geordende lyste vergelyk. Daarbenewens moet die lyste in hierdie geval langs mekaar op dieselfde blad geleë wees.

1. Kies die vergelykende skikkings. Gaan na die oortjie "Home". Klik dan op die ikoon "Vind en verhelder"wat op die band in die blok gereedskap geleë is "Editing". 'N Lys word oop waarin u 'n posisie moet kies. "Kies 'n groep selle ...".

   

   Daarbenewens kan in die gewenste venster van seleksie van 'n groep selle op 'n ander manier verkry word. Hierdie opsie sal veral nuttig wees vir die gebruikers wat die weergawe van die program vroeër as Excel 2007 geïnstalleer het, omdat die metode deur die knoppie "Vind en verhelder" Hierdie aansoeke ondersteun nie. Kies die skikkings wat ons wil vergelyk, en druk die sleutel F5.

2. 'N Klein oorgangsvenster word geaktiveer. Klik op die knoppie "Hoogte ..." in sy onderste linkerhoek.



3. Daarna word die venster vir die seleksie van groepe selle wat van die bogenoemde twee opsies gekies word. Stel die skakelaar in posisie "Kies per ry". Klik op die knoppie "OK".



4. Soos u kan sien, sal die wanpassende waardes van die rye hierna met 'n ander kleur uitgelig word. Daarbenewens kan die program, soos beoordeel word uit die inhoud van die formule reël, een van die selle aktief maak in die gespesifiseerde ongeëwenaarde lyne.

Metode 3: Voorwaardelike formatering

U kan 'n vergelyking maak deur die voorwaardelike formatteringsmetode te gebruik. Soos in die vorige metode, moet die vergelyking areas op dieselfde Excel werkblad wees en met mekaar gesinkroniseer word.

1. In die eerste plek kies ons watter tabelruimte ons die hoof sal oorweeg en watter verskille moet soek. Laastens sal ons in die tweede tabel doen. Kies dus die lys van werknemers wat daarin geleë is. Skuif na die oortjie "Home", klik op die knoppie "Voorwaardelike formatering"wat op die band in die blok geleë is "Styles". In die vervolg keuselys, gaan voort "Reëlbestuur".



2. Die reël bestuurder venster is geaktiveer. Ons druk dit op die knoppie "Skep 'n reël".



3. Maak in die launch venster 'n keuse van posisie "Gebruik formule". In die veld "Formateer selle" skryf die formule wat die adresse van die eerste selle van die bereik van die vergelyking kolomme bevat, geskei deur die "nie gelyke" teken (). Slegs hierdie uitdrukking sal hierdie keer 'n teken hê. "=". Daarbenewens moet absolute adressering toegepas word op alle kolomkoördinate in hierdie formule. Om dit te doen, kies die formule met die wyser en klik drie keer op die sleutel F4. Soos u kan sien, het 'n dollarteken naby al die kolomadresse verskyn, wat beteken dat skakels in absolute een verander word. Vir ons spesifieke geval sal die formule die volgende vorm inneem:

   = $ A2 $ D2

   Ons skryf hierdie uitdrukking in bogenoemde veld. Klik daarna op die knoppie "Formateer ...".



4. Geaktiveerde venster "Formateer selle". Gaan na die oortjie "Vulsel". Hier in die lys van kleure stop ons die keuse op die kleur waarmee ons die elemente wil kleur waar die data nie ooreenstem nie. Ons druk die knoppie "OK".



5. As jy terugkom na die venster om 'n formatteringsreël te skep, klik op die knoppie. "OK".



6. Na outomaties beweeg na die venster Reglement Bestuurder Klik op die knoppie "OK" en daarin.



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

Daar is nog 'n manier om voorwaardelike formatering te gebruik om die taak te bereik. Soos die vorige opsies vereis dit die ligging van albei vergelyke areas op dieselfde vel, maar in teenstelling met die voorheen beskryf metodes, sal die voorwaarde vir die sinkronisering of sortering van die data nie nodig wees nie, wat hierdie opsie onderskei van die voorheen beskryf.

1. Maak 'n verskeidenheid areas wat vergelyk moet word.



2. Voer 'n oorgang na die genaamd "Home". Klik op die knoppie. "Voorwaardelike formatering". In die geaktiveerde lys, kies die posisie "Reëls vir sel seleksie". In die volgende spyskaart maak ons ​​'n keuse van posisie. "Duplikaatwaardes".



3. Die venster vir die kies van duplikaatwaardes word geloods. As jy alles korrek gedoen het, dan bly dit net in hierdie venster om op die knoppie te klik. "OK". Alhoewel, as jy wil, kan jy 'n ander seleksie kleur kies in die ooreenstemmende veld van hierdie venster.



4. Nadat ons die gespesifiseerde optrede uitvoer, sal alle duplikaatelemente in die gekose kleur uitgelig word. Die elemente wat nie ooreenstem nie, bly kleur in hul oorspronklike kleur (wit by verstek). So kan jy dadelik visueel sien wat die verskil tussen skikkings is.

As jy wil, kan jy terselfdertyd nie-toevallige elemente verf, en die aanwysers wat ooreenstem, kan met dieselfde kleurvul gelaat word. In hierdie geval is die algoritme van aksies amper dieselfde, maar in die instellingsvenster om duidelike waardes in die eerste veld te verlig in plaas van "Dubbele" kies opsie "Unieke". Klik daarna op die knoppie "OK".

Dit sal dus die aanwysers wat nie ooreenstem nie, uitgelig word.

Les: Voorwaardelike formatering in Excel

Metode 4: komplekse formule

U kan ook data vergelyk deur gebruik te maak van 'n komplekse formule wat gebaseer is op die funksie COUNTIF. Met behulp van hierdie instrument kan jy bereken hoeveel elke element uit die geselekteerde kolom in die tweede tabel herhaal in die eerste.

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

= COUNTERS (reeks; maatstaf)

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

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

1. Kies die eerste element van die addisionele kolom waarin die aantal vuurhoutjies bereken sal word. Klik dan op die ikoon "Voeg funksie in".



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



3. Die operateur argument venster word geloods. 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". Daarna, hou die linker muisknop in, 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 doen, kies die koördinate in die veld en klik op die sleutel F4.

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

   Gaan dan na die veld "Criterion"deur die wyser daar te stel. Ons kliek op die eerste element met die achternaam in die eerste tabel reeks. In hierdie geval, verlaat die relatiewe skakel. Nadat dit in die veld vertoon word, kan jy op die knoppie klik "OK".



4. Die resultaat word in die vel element vertoon. Dit is gelyk aan die nommer "1". Dit beteken dat die naam in die lys van name van die tweede tabel "Grinev V.P."wat die eerste in die lys van die eerste tabel skikking is, kom een ​​keer voor.



5. Nou moet ons 'n soortgelyke uitdrukking vir alle ander elemente van die eerste tabel skep. Om dit te doen, kopieer dit met behulp van die vul merker, soos ons voorheen gedoen het. Plaas die wyser in die regter onderkant van die vel element wat die funksie bevat COUNTIF, en na die omskakeling na die vul merker, hou die linkermuisknop in en sleep die wyser af.



6. Soos u kan sien, het die program 'n berekening van wedstryde gemaak deur elke sel van die eerste tabel te vergelyk met data wat in die tweede tabelreeks voorkom. In vier gevalle het die uitslag uitgekom "1", en in twee gevalle - "0". Dit wil sê, die program kon nie in die tweede tabel die twee waardes in die eerste tabel skikking vind nie.

Uiteraard kan hierdie uitdrukking om die tabelaanwysers te vergelyk, in die bestaande vorm toegepas word, maar daar is 'n geleentheid om dit te verbeter.

Kom ons maak dat die waardes wat in die tweede tabel beskikbaar is, maar nie in die eerste afwesig is nie, in 'n aparte lys vertoon word.

1. Eerste van alles, laat ons ons formule herwerk COUNTIF, maak dit een van die argumente van die operateur INDIEN. Om dit te doen, kies die eerste sel waarin die operateur geleë is COUNTIF. In die formulebalk voor dit voeg ons die uitdrukking by "AS" sonder aanhalingstekens en maak die hakie oop. Om dit vir ons makliker te laat werk, kies ons die waarde in die formulebalk. "AS" en klik op die ikoon "Voeg funksie in".



2. Die funksie argument venster verskyn. INDIEN. Soos u kan sien, is die eerste veld van die venster reeds gevul met die waarde van die operateur. COUNTIF. Maar ons moet nog iets op hierdie gebied byvoeg. Ons stel die wyser daar en ons voeg by die reeds bestaande uitdrukking "=0" sonder aanhalingstekens.

   Daarna gaan jy na die veld "Waarde as waar". Hier sal ons 'n ander geneste funksie gebruik - STRING. Tik die woord in "LINE" sonder aanhalingstekens, maak dan die hakies oop en spesifiseer die koördinate van die eerste sel met die laaste naam in die tweede tabel en sluit dan die hakies. Spesifiek, in ons geval in die veld "Waarde as waar" het die volgende uitdrukking:

   LYN (D2)

   Nou die operateur STRING sal funksies rapporteer INDIEN die lynnommer waarin die spesifieke familienaam geleë is, en in die geval wanneer die toestand in die eerste veld gespesifiseer is, word die funksie INDIEN sal hierdie nommer na die sel uitstuur. Ons druk die knoppie "OK".



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



4. Met behulp van die vul merker, op die gewone manier, kopieer ons die uitdrukking van die operateur INDIEN op die hele kolom. Soos u kan sien, vir twee posisies wat in die tweede tabel voorkom, maar nie in die eerste nie, gee die formule lynnommers.



5. Trek uit die tabelruimte na regs en vul die kolom met getalle in volgorde, vanaf 1. Die aantal getalle moet ooreenstem met die aantal rye in die tweede vergelykingstabel. Om die nommersprosedure te bespoedig, kan u ook die vulpen gebruik.



6. Daarna kies die eerste sel aan die regterkant van die kolom met nommers en klik op die ikoon "Voeg funksie in".



7. open Funksie Wizard. Gaan na kategorie "Statistiek" en maak 'n keuse van name "DIE NAAM". Klik op die knoppie "OK".



8. funksie DIE MINSTE, waarvan die argument venster is oopgemaak, is ontwerp om die laagste waarde wat deur die rekening gespesifiseer is, te vertoon.

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

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



9. Die operateur vertoon die resultaat - die nommer 3. Dit is die kleinste nommer van die mismatchende rye tafelraamwerke. Gebruik die vul merker, kopieer die formule aan die onderkant.



10. Deur die lynnommers van die nie-ooreenstemmende elemente te ken, kan ons die sel en hul waardes in die funksie gebruik INDEKS. Kies die eerste element van die vel wat die formule bevat DIE MINSTE. Daarna gaan jy na die formule lyn en voor die naam "DIE NAAM" voeg naam by "Index" sonder aanhalingstekens, maak die hakie dadelik oop en sit 'n semikolon (;). Kies dan die naam in die formulebalk. "Index" en klik op die ikoon "Voeg funksie in".



11. Daarna word 'n klein venster oop waarin u moet bepaal of die verwysing 'n funksie moet hê INDEKS of ontwerp om met skikkings te werk. Ons benodig die tweede opsie. Dit is standaard ingestel, so in hierdie venster kliek eenvoudig op die knoppie. "OK".



12. Die funksie argument venster begin. INDEKS. Hierdie stelling is ontwerp om die waarde wat in 'n spesifieke skikking in die gespesifiseerde lyn is, te vertoon.

    Soos u kan sien, die veld "Lynnommer" reeds gevul met funksie waardes DIE MINSTE. Vanuit die waarde wat daar reeds bestaan, trek die verskil tussen die nommering van die Excel-blad en die interne nommering van die tabelarea af. Soos u kan sien, bo die tabelwaardes, het ons net 'n pet. Dit beteken dat die verskil een reël is. Daarom voeg ons in die veld by "Lynnommer" wat beteken "-1" sonder aanhalingstekens.

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

    Ons druk die knoppie "OK".



13. Nadat u die resultaat na die skerm uitgeput het, strek ons ​​die funksie met die vuller na die einde 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 jy die ranglys in verskillende boeke vergelyk, kan jy die bogenoemde metodes gebruik, behalwe die opsies wat die plasing van beide tafels op een vel vereis. Die belangrikste voorwaarde vir die uitvoer van die vergelyking proses in hierdie geval is die opening van die vensters van beide lêers op dieselfde tyd. Daar is geen probleme vir weergawes van Excel 2013 en later, sowel as vir weergawes voor Excel 2007. Maar in Excel 2007 en Excel 2010, om beide vensters gelyktydig oop te maak, word addisionele manipulasies benodig. Hoe om dit te 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 met mekaar te vergelyk. Watter opsie om te gebruik, hang af van presies waar die tabel data relatief tot mekaar geleë is (op een vel, in verskillende boeke, op verskillende velle), en ook op hoe presies die gebruiker wil hê dat hierdie vergelyking op die skerm vertoon word.

Kyk na die video: Our Miss Brooks: Another Day, Dress Induction Notice School TV Hats for Mother's Day (Mei 2024).