Väärtuste tabelite 1c võrdlemine 8. Kuidas võrrelda Excelis vasteid kahte veergu

Selle artikli lugemiseks kulub umbes 10 minutit. Järgmise 5 minuti jooksul saate Excelis hõlpsasti võrrelda kahte veergu ja uurida, kas neis on duplikaate, need kustutada või värviga esile tõsta. Niisiis, aeg on kätte jõudnud!

Excel on väga võimas ja tõeliselt lahe rakendus suurte andmemahtude loomiseks ja töötlemiseks. Kui teil on mitu andmetega töövihikut (või ainult üks suur tabel), siis soovite tõenäoliselt võrrelda kahte veergu, leida korduvad väärtused ja seejärel nendega midagi ette võtta, näiteks kustutada, esile tõsta või kustutada sisu . Veerud võivad olla samas tabelis, kõrvuti või mitte kõrvuti, paikneda 2 erineval lehel või isegi erinevates töövihikutes.

Kujutage ette, et meil on 2 veergu inimeste nimedega – 5 nime veerus A ja 3 nime veerus B. Peate nendes kahes veerus olevaid nimesid võrdlema ja leidma duplikaadid. Nagu te mõistate, on tegemist fiktiivsete andmetega, mis on võetud ainult illustratiivsetel eesmärkidel. Päris tabelites on meil tegemist tuhandete või isegi kümnete tuhandete kirjetega.

Valik A: mõlemad veerud on samal lehel. Näiteks veerg A ja veerg B.

Variant B: Veerud asuvad erinevatel lehtedel. Näiteks veerg A lehel Leht2 ja veerg A lehel Leht3.

Excel 2013, 2010 ja 2007 on sisseehitatud tööriistaga Eemalda duplikaat(Eemalda duplikaadid), kuid see on selles olukorras jõuetu, kuna ei saa võrrelda andmeid kahes veerus. Lisaks saab see eemaldada ainult duplikaate. Muid võimalusi, näiteks esiletõstmist või värvide muutmist, pole. Ja punkt!

Võrrelge Excelis kahte veergu ja leidke valemite abil topeltkirjed

Valik A: mõlemad veerud on samal lehel

Vihje: Suurtes tabelites on valemi kopeerimine kiirem, kui kasutate kiirklahve. Valige lahter C1 ja vajutage Ctrl+C(valemi lõikelauale kopeerimiseks), seejärel klõpsake Ctrl + Tõstuklahv + Lõpp(et valida veerus C kõik mittetühjad lahtrid) ja lõpuks klõpsake Ctrl+V(valemi kleepimiseks kõikidesse valitud lahtritesse).

Valik B: kaks veergu on erinevatel lehtedel (erinevates raamatutes)

Leitud duplikaatide töötlemine

Suurepärane, leidsime esimesest veerust kirjed, mis on olemas ka teises veerus. Nüüd peame nendega midagi ette võtma. Tabeli kõigi dubleerivate kirjete käsitsi läbimine on üsna ebaefektiivne ja võtab liiga palju aega. On paremaid viise.

Kuva veerus A ainult korduvad read

Kui teie veergudel pole pealkirju, peate need lisama. Selleks asetage kursor esimest rida tähistavale numbrile ja see muutub mustaks nooleks, nagu on näidatud alloleval joonisel:

Paremklõpsake ja valige kontekstimenüüst Sisesta(Sisesta):

Andke veergudele nimed, näiteks " Nimi"Ja" Kas dubleerida?” Seejärel avage vahekaart Andmed(Andmed) ja vajutage Filter(Filter):

Pärast seda klõpsake väikesel hallil noolel " Kas dubleerida?“ filtrimenüü laiendamiseks; tühjendage kõik selles loendis olevad üksused, v.a Duplikaat ja vajutage Okei.

See on kõik, nüüd näete ainult neid veeru elemente A, mis on veerus dubleeritud IN. Meie treeningtabelis on ainult kaks sellist lahtrit, kuid nagu teate, on praktikas neid palju rohkem.

Veeru kõigi ridade uuesti kuvamiseks A, klõpsake veerus filtri sümbolil IN, mis näeb nüüd välja nagu lehter, millel on väike nool ja valige Vali kõik(Vali kõik). Või saate seda teha lindi kaudu, klõpsates Andmed(Andmed) > Valige ja filtreerige(Sordi ja filtreeri) > Selge(Tühjenda), nagu on näidatud alloleval ekraanipildil:

Muutke värvi või tõstke leitud duplikaadid esile

Kui märgid " Duplikaat” ei ole teie eesmärkide jaoks piisav ja soovite märkida duplikaatlahtrid erineva fondivärvi, täitevärvi või muul viisil...

Sel juhul filtreerige duplikaadid nagu ülal näidatud, valige kõik filtreeritud lahtrid ja klõpsake Ctrl+1 dialoogiboksi avamiseks Lahtrite vormindamine(Cell Format). Näitena muudame duplikaatidega ridade lahtrite täitevärvi erekollaseks. Loomulikult saate tööriista abil täitevärvi muuta Täida(Täitevärv) vahekaart Kodu(Kodu), kuid dialoogiboksi eelis Lahtrite vormindamine(Format Cells) on see, et saate korraga konfigureerida kõiki vormindamissuvandeid.

Nüüd ei jää te kindlasti kahekordsetest lahtritest ilma:

Duplikaatväärtuste eemaldamine esimesest veerust

Filtreerige tabel, et kuvada ainult topeltväärtustega lahtrid, ja valige need lahtrid.

Kui kaks võrreldavat veergu on erinevatel lehtedel, st erinevates tabelites paremklõpsake valitud vahemikku ja valige kontekstimenüüst Kustuta rida(Kustuta rida):

Klõpsake Okei kui Excel palub teil kinnitada, et soovite tõesti kogu töölehe rea kustutada, ja seejärel filtri tühjendada. Nagu näete, jäävad alles ainult unikaalsete väärtustega read:

Kui ühel lehel asuvad 2 veergu, üksteise lähedal (kõrvuti) või mitte üksteise lähedal (mitte kõrvuti), siis on duplikaatide eemaldamise protsess veidi keerulisem. Me ei saa kogu topeltväärtustega rida kustutada, kuna see kustutaks lahtrid ka teisest veerust. Seega, et veerus hoida ainult kordumatuid kirjeid A, tehke järgmist:

Nagu näete, pole duplikaatide eemaldamine kahest Exceli veerust valemite abil nii keeruline.

Mõnikord on vaja võrrelda kahte MS Exceli faili. See võib olla teatud kaupade hinnaerinevuste leidmine või näidustuste muutmine, vahet pole, peaasi, et teatud lahknevused on vaja leida.

Poleks vale mainida, et kui MS Exceli failis on paar kirjet, siis pole mõtet automatiseerida. Kui failis on mitusada või isegi tuhandeid kirjeid, siis ei saa ilma arvuti arvutusvõimsuse abita hakkama.

Simuleerime olukorda, kus kahes failis on sama rida ridu ja lahknevust tuleb otsida konkreetsest veerust või mitmest veerust. Selline olukord on võimalik näiteks siis, kui on vaja võrrelda kauba hindu kahe hinnakirja järgi või võrrelda sportlaste mõõtu enne ja pärast treeninghooaega, kuigi sellise automatiseerimise jaoks peab neid olema palju.

Töönäitena võtame faili fiktiivsete osalejate sooritustega: 100 meetri jooks, 3000 meetri jooks ja jõutõmbed. Esimene fail on mõõtmine hooaja alguses ja teine on hooaja lõpp.

Esimene viis probleemi lahendamiseks. Lahenduseks on ainult MS Exceli valemite kasutamine.

Kuna kirjed on paigutatud vertikaalselt (kõige loogilisem paigutus), siis on vaja funktsiooni kasutada. Kui kasutate kirjete horisontaalset paigutust, peate kasutama funktsiooni.

100 meetri jooksu tulemuslikkuse võrdlemiseks on valem järgmine:
=IF(VLOOKUP($B2,Sheet2!$B$2:$F$13,3,TRUE)<>D2;D2-VLOOKUP($B2;Sheet2!$B$2:$F$13,3,TRUE);"Ei ole vahet")
Kui erinevust pole, kuvatakse teade, et erinevust pole, kui erinevus on, lahutatakse hooaja lõpu väärtus hooaja alguse väärtusest.

3000 meetri jooksu valem on järgmine:
=IF(VLOOKUP($B2,Sheet2!$B$2:$F$13,4,TRUE)<>E2;"Erinevus on";"Erinevust pole")
Kui lõpp- ja algväärtus ei ole võrdsed, kuvatakse vastav teade. Tõmbamise valem võib olla sarnane mis tahes eelnevaga, seda pole mõtet täiendavalt anda. Lõplik fail leitud lahknevustega on näidatud allpool.

Väike täpsustus. Valemite lugemise hõlbustamiseks teisaldati kahe faili andmed ühte (erinevatele lehtedele), kuid seda poleks saanud teha.

Video, milles võrreldakse kahte MS Exceli faili kasutades ja funktsioone.

Teine viis probleemi lahendamiseks. Lahendus MS Accessi abil.

Selle probleemi saab lahendada, kui impordite esmalt MS Exceli failid Accessi. Mis puudutab välisandmete importimise meetodit, siis erinevate väljade leidmisel pole vahet (selleks sobib iga esitatud valik).

Viimane on ühendus Exceli ja Accessi failide vahel, nii et Exceli failides olevate andmete muutmisel leitakse MS Accessis päringu käivitamisel lahknevused automaatselt.

Järgmine samm pärast importimist on tabelite vaheliste seoste loomine. Ühendusväljana valige kordumatu väli "Kauba nr".
Kolmas samm on päringu koostaja abil lihtsa valikupäringu loomine.

Esimeses veerus märgime, millised kirjed tuleb kuvada, ja teises - millistel tingimustel kirjeid kuvatakse. Loomulikult on teise ja kolmanda välja toimingud sarnased.

Video, milles võrreldakse MS-faile Exceliga, kasutades MS Accessi.

Teostatud manipulatsioonide tulemusena kuvatakse kõik rekordid, väljal erinevad andmed: "100 meetri jooksmine." MS Accessi fail on esitatud allpool (kahjuks ei luba SkyDrive Exceli failina manustada)

Need kaks meetodit on olemas MS Exceli tabelites lahknevuste leidmiseks. Igal neist on nii eelised kui ka puudused. Ilmselgelt pole see kahe Exceli faili võrdluste ammendav loend. Ootame teie ettepanekuid kommentaaridesse.

Meil on kaks tellimuste tabelit kopeeritud ühele tööle. Excelis on vaja võrrelda kahe tabeli andmeid ja kontrollida, millised positsioonid on esimeses tabelis, aga mitte teises. Iga lahtri väärtust pole mõtet käsitsi võrrelda.

Võrrelge Excelis vastete leidmiseks kahte veergu

Kuidas võrrelda Excelis kahe veeru väärtusi? Selle probleemi lahendamiseks soovitame kasutada tingimusvormingut, mis tõstab kiiresti esile ainult ühes veerus olevad üksused. Tööleht tabelitega:

Esimene samm on nimetada mõlemad tabelid. Nii on lihtsam mõista, milliseid lahtrivahemikke võrreldakse:

Valige tööriist VALEMID – Määratletud nimed – Määra nimi.
Sisestage ilmuvas aknas väljale „Nimi:” väärtus – Tabel_1.
Kasutage hiire vasakut nuppu, et klõpsata sisestusväljal "Range:" ja valida vahemik: A2:A15. Ja klõpsake nuppu OK.

Teise loendi jaoks tehke samad toimingud, andke sellele ainult nimi - Tabel_2. Ja määrake vastavalt vahemik C2:C15.

Abistav nõuanne! Vahemikunimesid saab kiiremini määrata, kasutades nimede välja. See asub valemiribast vasakul. Valige lihtsalt lahtrite vahemikud ja sisestage nimeväljale vahemiku sobiv nimi ja vajutage sisestusklahvi.

Nüüd kasutame tingimusvormingut kahe loendi võrdlemiseks Excelis. Peame saama järgmise tulemuse:

Üksused, mis on tabelis_1, kuid mitte tabelis_2, kuvatakse roheliselt. Samal ajal tõstetakse sinisega esile positsioonid, mis on tabelis_2, kuid mitte tabelis_1.

Exceli kahe veeru andmete võrdlemise põhimõte

Veeru lahtrite vormindamise tingimuste määratlemisel kasutasime funktsiooni COUNTIF. Selles näites kontrollib see funktsioon, mitu korda kuvatakse teise argumendi väärtus (näiteks A2) esimese argumendi loendis (näiteks Tabel_2). Kui kordade arv = 0, tagastab valem TRUE. Sel juhul määratakse lahtrile tingimusvormingusuvandites määratud kohandatud vorming.

Teise argumendi link on suhteline, mis tähendab, et kõiki valitud vahemiku lahtreid kontrollitakse ükshaaval (näiteks A2:A15). Näiteks võrrelda kahte hinnakirja Excelis kasvõi erinevatel lehtedel. Teine valem töötab sarnaselt. Sama põhimõtet saab rakendada erinevate sarnaste ülesannete puhul.

Võib-olla seisavad kõik, kes Excelis andmetega töötavad, silmitsi küsimusega, kuidas võrrelda Exceli kahte veergu sarnasuste ja erinevuste osas. Selleks on mitu võimalust. Vaatame igaüks neist lähemalt.

Kuidas võrrelda Exceli kahte veergu ridade kaupa

Kahe andmeveeru võrdlemisel on sageli vaja võrrelda iga üksiku rea andmeid vastete või erinevuste leidmiseks. Sellise analüüsi saame teha funktsiooni abil. Vaatame allolevate näidete abil, kuidas see toimib.

Näide 1: kuidas võrrelda kahte veergu sama rea vastete ja erinevuste jaoks

Exceli kahe veeru iga rea andmete võrdlemiseks kirjutame lihtsa valemi. Valem tuleks sisestada külgneva veeru igale reale tabeli kõrvale, milles peamised andmed asuvad. Olles loonud valemi tabeli esimese rea jaoks, saame selle venitada/kopeerida ülejäänud ridadele.

Selleks, et kontrollida, kas sama rea kaks veergu sisaldavad samu andmeid, vajame valemit:

=IF(A2=B2; “Sobivus”; “”)

Valem, mis määrab ühe rea kahe veeru andmete erinevused, näeb välja järgmine:

=IF(A2<>B2; "Ei sobi"; ")

Saame sobitada vaste ja erinevuse testi kahe veeru vahel ühele reale ühes valemis:

=IF(A2=B2; "Sobib"; "Ei sobi")

=IF(A2<>B2; "Ei sobi"; "Kokku sobima")

Näidisarvutuse tulemus võib välja näha selline:

Sama rea kahe veeru andmete võrdlemiseks tõstutundlikul viisil kasutage valemit:

=IF(MATCH(A2,B2), "Sobimine", "Unikaalne"

Kuidas võrrelda mitut veergu vastete leidmiseks ühes Exceli reas

Excel võimaldab võrrelda andmeid sama rea mitmes veerus, kasutades järgmisi kriteeriume.

Otsige tabeli kõigist veergudest samade väärtustega read;
Otsige tabeli kahest veerust samade väärtustega read;

Näide 1. Kuidas leida ühest reast vasteid tabeli mitmes veerus

Kujutagem ette, et meie tabel koosneb mitmest andmeveerust. Meie ülesanne on leida ridu, mille väärtused kattuvad kõigis veergudes. Excel ja funktsioonid aitavad meid selles. Sobivuse määramise valem on järgmine:

=IF(JA(A2=B2,A2=C2); " langeb kokku"; " ")

Kui meie tabelis on palju veerge, on funktsiooni lihtsam kasutada koos:

=IF(COUNTIF($A2:$C2,$A2)=3;"Sobivus";" ")

Valemis tähistab "5" tabeli veergude arvu, mille jaoks me valemi koostasime. Kui teie tabelis on rohkem või vähem veerge, peaks see väärtus olema võrdne veergude arvuga.

Näide 2: kuidas leida sama rea vasteid tabeli mis tahes kahes veerus

Kujutagem ette, et meie ülesandeks on tuvastada mitmes veerus andmetega tabelist need read, kus andmed on samad või korduvad vähemalt kahes veerus. Funktsioonid ja aitavad meid selles. Kirjutame kolmest andmeverust koosneva tabeli valem:

=IF(VÕI(A2=B2,B2=C2,A2=C2);"Sobib";" ")

Juhtudel, kui meie tabelis on liiga palju veerge, on meie valem funktsiooniga väga suur, kuna selle parameetrites peame määrama tabeli iga veeru sobituskriteeriumid. Lihtsam viis on sel juhul kasutada .

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; "Unikaalne string"; "Pole kordumatu string"

=IF(COUNTIF($B:$B,$A5)=0; "Veerus B pole vasteid"; "Veerus B on vasteid"

See valem kontrollib väärtusi veerus B, et näha, kas need vastavad veeru A lahtritele.

Kui teie tabel koosneb kindlast arvust ridadest, saate määrata valemis selge vahemiku (näiteks $ B2: $ B10). See kiirendab valemit.

Kuidas võrrelda kahte Exceli veergu vastete leidmiseks ja neid värviliselt esile tõsta

Kui otsime Excelis vasteid kahe veeru vahel, peame võib-olla visualiseerima leitud vasted või andmete erinevused, kasutades näiteks värvide esiletõstmist. Lihtsaim viis värvide vastete ja erinevuste esiletõstmiseks on kasutada Excelis tingimusvormingut. Vaatame allolevate näidete abil, kuidas seda teha.

Värviliste vastete otsimine ja esiletõstmine Excelis mitmes veerus

Juhtudel, kui peame leidma vasteid mitmest veerust, vajame selleks:

Valige veerud andmetega, milles soovite vasteid arvutada;
Tööriistariba vahekaardil "Kodu" klõpsake menüükäsku "Tingimuslik vormindamine" -> "Lahte esiletõstmise reeglid" -> "Korduvad väärtused";
Hüpikdialoogiboksis valige vasakpoolses ripploendis "Kordumine" ja parempoolses ripploendis valige, mis värviga korduvad väärtused esile tõstetakse. Klõpsake nuppu "OK":
Pärast seda tõstetakse vasted valitud veerus esile:

Otsige ja tõstke Excelis esile sobivad read

Kahes või enamas veerus olevate andmetega sobivate lahtrite leidmine ja andmetega tervete ridade vastete otsimine on erinevad mõisted. Pange tähele kahte allolevat tabelit:

Ülaltoodud tabelid sisaldavad samu andmeid. Nende erinevus seisneb selles, et vasakpoolses näites otsisime sobivaid lahtreid ja paremalt terveid korduvaid ridu andmetega.

Vaatame, kuidas tabelist sobivaid ridu leida:

Andmetega tabelist paremale loome abiveeru, milles iga andmetega rea vastas paneme valemi, mis ühendab kõik tabelirea väärtused ühte lahtrisse:

=A2&B2&C2&D2

Abiveerus näete kombineeritud tabeliandmeid:

Nüüd tehke tabelis sobivate ridade määramiseks järgmised toimingud.

Valige abiveerus andmetega ala (meie näites on see lahtrite vahemik E2:E15 );
Tööriistariba vahekaardil "Kodu" klõpsake menüükäsku "Tingimuslik vormindamine" -> "Lahte esiletõstmise reeglid" -> "Korduvad väärtused";
Hüpikdialoogiboksis valige vasakpoolses ripploendis "Kordumine" ja parempoolses ripploendis valige, mis värviga korduvad väärtused esile tõstetakse. Klõpsake nuppu "OK":
Pärast seda tõstetakse valitud veerus esile topeltread:

Sageli on ülesandeks võrrelda kahte elementide loendit. Selle käsitsi tegemine on liiga tüütu ning samuti ei saa välistada vigade võimalust. Excel teeb selle toimingu lihtsaks. See näpunäide kirjeldab meetodit, mis kasutab tingimusvormingut.

Joonisel fig. Joonisel 164.1 on näide kahest mitmeveerulisest nimeloendist. Tingimusliku vormingu kasutamine võib muuta loendite erinevused ilmseks. Need loendi näited sisaldavad teksti, kuid kõnealune meetod töötab ka arvandmetega.

Esimene loend on A2:B31, seda vahemikku nimetatakse Vananimekiri. Teine loend on D2:E31, vahemikku nimetatakse Uus nimekiri. Vahemikud nimetati käsu abil Valemid Määratletud nimed Määrake nimi. Vahemikuid pole vaja nimetada, kuid see teeb nendega töötamise lihtsamaks.

Alustame tingimusvormingu lisamisega vanasse loendisse.

Valige vahemikus lahtrid Vananimekiri.
Valige.
Aknas Loo vormindusreegel valige üksus nimega Kasutage valemit
Sisestage see valem aknaväljale (joonis 164.2): =COUNTIF(New List;A2)=0.
Klõpsake nuppu Vorming ja määrake vorming, mida rakendatakse, kui tingimus on tõene. Parim on valida erinevad täitevärvid.
Klõpsake nuppu OK.

Vahemikus olevad rakud Uus nimekiri kasutage sarnast tingimusvormingu valemit.

Valige vahemikus lahtrid Uus nimekiri.
Valige Avaleht Tingimusvorming Looge reegel dialoogiboksi avamiseks Loo vormindusreegel.
Aknas Loo reegel vormindamine valige üksus Kasutage valemit vormindatavate lahtrite määratlemiseks.
Sisestage aknaväljale järgmine valem: =COUNTIF(Vanaloend;D2)=0 .
Klõpsake nuppu Vorming ja määrake vormingu, mida rakendatakse, kui tingimus on tõene (erinev täitevärv).
Klõpsake nuppu OK.

Selle tulemusena tõstetakse esile nimed, mis on vanas loendis, kuid mitte uues (joonis 164.3). Lisaks on uues loendis esile tõstetud ka nimed, mida vanas loendis pole, kuid erinevat värvi. Mõlemas loendis olevaid nimesid ei tõsteta esile.

Mõlemad tingimusvormingu valemid kasutavad funktsiooni COUNTIF. See arvutab, mitu korda teatud väärtus vahemikus kuvatakse. Kui valem tagastab 0, tähendab see, et üksus pole vahemikus. Nii võtab tingimusvormingu võimust ja lahtri taustavärv muutub.