Contents [show]
Excel is 'n doeltreffende dataverwerkingsprogram. En een van die metodes van inligtingsontleding is die vergelyking van twee lyste. As jy twee lyste in Excel korrek vergelyk, sal dit baie maklik wees om hierdie proses te organiseer. Dit is genoeg om net 'n paar van die punte te volg wat vandag bespreek sal word. Die praktiese implementering van hierdie metode hang geheel en al af van die behoeftes van die persoon of organisasie op 'n bepaalde oomblik. Daarom moet verskeie moontlike gevalle oorweeg word.
Vergelyk twee lyste in Excel
Natuurlik kan jy twee lyste met die hand vergelyk. Maar dit sal lank neem. Excel het sy eie intelligente gereedskapstel wat jou sal toelaat om data nie net vinnig te vergelyk nie, maar ook om inligting te kry wat nie so maklik is om met jou oë te kry nie. Gestel ons het twee kolomme met koördinate A en B. Sommige waardes word daarin herhaal.
Formulering van die probleem
Ons moet dus hierdie kolomme vergelyk. Die prosedure om twee dokumente te vergelyk is soos volg:
- As die unieke selle van elk van hierdie lyste dieselfde is, en die totale aantal unieke selle is dieselfde, en die selle is dieselfde, dan kan hierdie lyste as dieselfde beskou word. Die volgorde waarin die waardes in hierdie lys gestapel word, maak nie soveel saak nie.
- Ons kan praat oor gedeeltelike toeval van lyste as die unieke waardes self dieselfde is, maar die aantal herhalings verskil. Daarom kan sulke lyste 'n ander aantal elemente bevat.
- Die feit dat die twee lyste nie ooreenstem nie, word deur 'n verskillende stel unieke waardes aangedui.
Al hierdie drie toestande is gelyktydig die voorwaardes van ons probleem.
Die oplossing van die probleem
Kom ons genereer twee dinamiese reekse om dit makliker te maak om lyste te vergelyk. Elkeen van hulle sal ooreenstem met elk van die lyste. 
Om twee lyste te vergelyk, doen die volgende:
- In 'n aparte kolom skep ons 'n lys unieke waardes wat spesifiek vir beide lyste is. Hiervoor gebruik ons die formule: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Die formule self moet as 'n skikkingsformule geskryf word.
- Kom ons bepaal hoeveel keer elke unieke waarde in die dataskikking voorkom. Hier is die formules om dit te doen: =COUNTIF(Lys1,D5) en =COUNTI(Lys2,D5).
- As beide die aantal herhalings en die aantal unieke waardes dieselfde is in alle lyste wat by hierdie reekse ingesluit is, gee die funksie die waarde 0 terug. Dit dui aan dat die passing XNUMX% is. In hierdie geval sal die opskrifte van hierdie lyste 'n groen agtergrond kry.
- As alle unieke inhoud in beide lyste is, word dan deur formules teruggestuur =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) die waarde sal nul wees. As E1 nie nul bevat nie, maar so 'n waarde is vervat in selle E2 en F2, dan sal die reekse in hierdie geval as ooreenstemmend erken word, maar slegs gedeeltelik. In hierdie geval sal die opskrifte van die ooreenstemmende lyste oranje word.
- En as een van die formules hierbo beskryf 'n nie-nul waarde gee, sal die lyste heeltemal nie ooreenstem nie.
Dit is die antwoord op die vraag hoe om kolomme vir passings te ontleed deur formules te gebruik. Soos u kan sien, kan u met die gebruik van funksies byna enige taak implementeer wat met die eerste oogopslag nie met wiskunde verband hou nie.
Voorbeeld Toetsing
In ons weergawe van die tabel is daar drie tipes lyste van elke tipe hierbo beskryf. Dit het gedeeltelik en heeltemal bypassende, sowel as nie-ooreenstemmende.
Om data te vergelyk, gebruik ons die reeks A5:B19, waarin ons hierdie pare lyste afwisselend invoeg. Oor wat die resultaat van die vergelyking sal wees, sal ons verstaan deur die kleur van die oorspronklike lyste. As hulle heeltemal anders is, sal dit 'n rooi agtergrond wees. As 'n deel van die data dieselfde is, dan geel. In die geval van volledige identiteit sal die ooreenstemmende opskrifte groen wees. Hoe om 'n kleur te maak, afhangende van wat die resultaat is? Dit vereis voorwaardelike formatering.
Vind verskille in twee lyste op twee maniere
Kom ons beskryf nog twee metodes om verskille te vind, afhangende van of die lyste sinchronies is of nie.
Opsie 1. Sinchroniese lyste
Dit is 'n maklike opsie. Gestel ons het sulke lyste.
Om te bepaal hoeveel keer die waardes nie konvergeer het nie, kan jy die formule gebruik: =SOMPRODUK(—(A2:A20<>B2:B20)). As ons 0 as gevolg daarvan gekry het, beteken dit dat die twee lyste dieselfde is.
Opsie 2: Geskommelde lyste
As die lyste nie identies is in die volgorde van die voorwerpe wat hulle bevat nie, moet jy 'n kenmerk soos voorwaardelike formatering toepas en duplikaatwaardes inkleur. Of gebruik die funksie COUNTIF, waarmee ons bepaal hoeveel keer 'n element uit een lys in die tweede voorkom.
Hoe om 2 kolomme ry vir ry te vergelyk
Wanneer ons twee kolomme vergelyk, moet ons dikwels inligting wat in verskillende rye is vergelyk. Om dit te doen, sal die operateur ons help INDIEN. Kom ons kyk hoe dit in die praktyk werk. Om dit te doen, bied ons verskeie illustratiewe situasies aan.
Voorbeeld. Hoe om 2 kolomme vir passings en verskille in een ry te vergelyk
Om te ontleed of die waardes wat in dieselfde ry maar verskillende kolomme is dieselfde is, skryf ons die funksie IF. Die formule word ingevoeg in elke ry wat in die hulpkolom geplaas word waar die resultate van dataverwerking vertoon sal word. Maar dit is glad nie nodig om dit in elke ry voor te skryf nie, kopieer dit net in die oorblywende selle van hierdie kolom of gebruik die outovoltooimerker.
Ons moet so 'n formule neerskryf om te verstaan of die waardes in beide kolomme dieselfde is of nie: =IF(A2=B2, “Pas”, “”). Die logika van hierdie funksie is baie eenvoudig: dit vergelyk die waardes in selle A2 en B2, en as hulle dieselfde is, vertoon dit die waarde "Sameval". As die data anders is, gee dit geen waarde terug nie. Jy kan ook die selle nagaan om te sien of daar 'n ooreenkoms tussen hulle is. In hierdie geval is die formule wat gebruik word: =IF(A2<>B2, “Moenie pas nie”, “”). Die beginsel is dieselfde, eers word die kontrole uitgevoer. As dit blyk dat die selle aan die kriterium voldoen, word die waarde "Stem nie ooreen nie" vertoon.
Dit is ook moontlik om die volgende formule in die formuleveld te gebruik om beide "Pas" te vertoon as die waardes dieselfde is, en "Moenie pas nie" as hulle verskil: =IF(A2=B2; “Pas”, “Pas nie”). Jy kan ook die ongelykheidsoperateur gebruik in plaas van die gelykheidsoperateur. Slegs die volgorde van die waardes wat in hierdie geval vertoon sal word, sal effens anders wees: =IF(A2<>B2, “Steen nie ooreen nie”, “Sameval”). Nadat u die eerste weergawe van die formule gebruik het, sal die resultaat soos volg wees.
Hierdie variasie van die formule is hoofletter-onsensitief. Daarom, as die waardes in een kolom slegs van ander verskil deurdat hulle in hoofletters geskryf is, sal die program nie hierdie verskil opmerk nie. Om die vergelyking hooflettergevoelig te maak, moet jy die funksie in die kriteria gebruik PRESIES. Die res van die argumente word onveranderd gelaat: =IF(EXACT(A2,B2), "Pas", "Uniek").
Hoe om veelvuldige kolomme vir passings in een ry te vergelyk
Dit is moontlik om die waardes in die lyste te ontleed volgens 'n hele stel kriteria:
- Soek daardie rye wat oral dieselfde waardes het.
- Soek daardie rye waar daar passings is in net twee lyste.
Kom ons kyk na 'n paar voorbeelde van hoe om te werk te gaan in elk van hierdie gevalle.
Voorbeeld. Hoe om passings in een ry in verskeie kolomme van 'n tabel te vind
Gestel ons het 'n reeks kolomme wat die inligting bevat wat ons benodig. Ons word gekonfronteer met die taak om daardie rye te bepaal waarin die waardes dieselfde is. Om dit te doen, moet jy die volgende formule gebruik: =IF(EN(A2=B2,A2=C2), “pas”, ” “).
As daar te veel kolomme in die tabel is, moet jy dit net saam met die funksie gebruik IF operateur COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;"pas";" "). Die getal wat in hierdie formule gebruik word, dui die aantal kolomme aan om na te gaan. As dit verskil, moet jy soveel skryf as wat vir jou situasie waar is.
Voorbeeld. Hoe om passings in een ry in enige 2 kolomme van 'n tabel te vind
Kom ons sê ons moet kyk of die waardes in een ry ooreenstem in twee kolomme van dié in die tabel. Om dit te doen, moet jy die funksie as 'n voorwaarde gebruik OR, waar afwisselend die gelykheid van elk van die kolomme aan die ander skryf. Hier is 'n voorbeeld.
Ons gebruik hierdie formule: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Daar kan 'n situasie wees wanneer daar baie kolomme in die tabel is. In hierdie geval sal die formule groot wees, en dit kan baie tyd neem om al die nodige kombinasies te kies. Om hierdie probleem op te los, moet jy die funksie gebruik COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Unieke string”; “Nie unieke string”)
Ons sien dat ons in totaal twee funksies het COUNTIF. Met die eerste een bepaal ons afwisselend hoeveel kolomme 'n ooreenkoms met A2 het, en met die tweede een kontroleer ons die aantal ooreenkomste met die waarde van B2. As ons, as gevolg van berekening deur hierdie formule, 'n nulwaarde kry, dui dit aan dat alle rye in hierdie kolom uniek is, indien meer, is daar ooreenkomste. Daarom, as ons 'n nulwaarde kry as gevolg van berekening deur twee formules en die byvoeging van die finale resultate, dan word die tekswaarde "Unieke string" teruggestuur, as hierdie getal groter is, word geskryf dat hierdie string nie uniek is nie.
Hoe om 2 kolomme in Excel te vergelyk vir passings
Kom ons neem nou 'n voorbeeld. Kom ons sê ons het 'n tabel met twee kolomme. Jy moet kyk of hulle ooreenstem. Om dit te doen, moet jy die formule toepas, waar die funksie ook gebruik sal word IF, en die operateur COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, "Geen passings in kolom B", "Daar is passings in kolom B")
Geen verdere optrede is nodig nie. Nadat ons die resultaat deur hierdie formule bereken het, kry ons as die waarde van die derde argument van die funksie IF wedstryde. As daar geen is nie, dan is die inhoud van die tweede argument.
Hoe om 2 kolomme in Excel te vergelyk vir passings en uitlig met kleur
Om dit makliker te maak om visueel ooreenstemmende kolomme te identifiseer, kan jy hulle met 'n kleur uitlig. Om dit te doen, moet jy die funksie "Voorwaardelike formatering" gebruik. Kom ons kyk in die praktyk.
Soek en beklemtoon passings volgens kleur in verskeie kolomme
Om die passings te bepaal en uit te lig, moet jy eers die datareeks kies waarin die kontrole uitgevoer sal word, en dan die "Voorwaardelike formatering"-item op die "Tuis"-oortjie oopmaak. Kies daar "Duplikaatwaardes" as die seleksiereël.
Daarna sal 'n nuwe dialoogkassie verskyn, waarin ons in die linker pop-up lys die opsie "Herhaal" vind, en in die regte lys kies ons die kleur wat vir die keuse gebruik sal word. Nadat ons op die "OK"-knoppie geklik het, sal die agtergrond van alle selle met ooreenkomste gekies word. Vergelyk dan net die kolomme per oog.
Soek en merk bypassende lyne uit
Die tegniek om te kyk of snare pas, is effens anders. Eerstens moet ons 'n bykomende kolom skep, en daar sal ons die gekombineerde waardes gebruik deur die &-operateur te gebruik. Om dit te doen, moet jy 'n formule van die vorm skryf: =A2&B2&C2&D2.
Ons kies die kolom wat geskep is en bevat die gekombineerde waardes. Vervolgens voer ons dieselfde volgorde van aksies uit wat hierbo vir die kolomme beskryf is. Duplikaatlyne sal uitgelig word in die kleur wat jy spesifiseer.
Ons sien dat daar niks moeilik is om na herhalings te soek nie. Excel bevat al die nodige gereedskap hiervoor. Dit is belangrik om net te oefen voordat al hierdie kennis in die praktyk toegepas word.