Kuidas võrrelda Excelis kahte veergu ja eemaldada duplikaate (esiletõstmine, värvimine, teisaldamine)

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ärviliselt esile tõsta. Niisiis, aeg on käes!

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 korduvaid väärtusi ja seejärel nendega midagi ette võtta, näiteks kustutada, tõsta esile või tühjendada sisu . Veerud võivad asuda samas tabelis, olla kõrvuti või mitte kõrvuti, paikneda kahel erineval lehel või isegi erinevates raamatutes.

Kujutage ette, et meil on 2 veergu inimeste nimedega – 5 nime veerus A ja 3 nime veerus B. Peate võrdlema nende kahe veeru nimesid ja leidma duplikaadid. Nagu te mõistate, on tegemist fiktiivsete andmetega, võttes ainult näiteks. Päris tabelites on meil tegemist tuhandete või isegi kümnete tuhandete plaatidega.

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

Valik B: Veerud on erinevatel lehtedel. Näiteks veerg A lehel Sheet2 ja veerg A lehel Sheet3.

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

Järgmisena näitan teile võimalikke viise Excelis kahe veeru võrdlemiseks, mis võimaldab teil dubleerivaid kirjeid leida ja eemaldada.

• Võrrelge kahte veergu ja otsige valemite abil duplikaate
  
  • Valik A: mõlemad veerud on samal lehel
  • Valik B: veerud on erinevatel lehtedel või erinevates töövihikutes
• Leitud duplikaatide töötlemine
  
  • Kuva veerus A ainult korduvad read
  • Muutke värvi või tõstke leitud duplikaadid esile
  • Eemaldage esimesest veerust duplikaadid

Võrrelge Excelis kahte veergu ja leidke valemite abil topeltkirjed

Valik A: mõlemad veerud on samal lehel

1. Esimesse tühja lahtrisse (meie näites on see lahter C1) kirjutame järgmise valemi:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   Meie valemis A1 see on esimese veeru esimene lahter, mida me võrdleme. 1 $ B$ и 10000 $ B$ need on teise veeru esimese ja viimase lahtri aadressid, millega me võrdleme. Pange tähele absoluutseid viiteid – veergude tähtedele ja ridade numbritele eelneb dollarimärk ($). Kasutan absoluutviiteid, et valemite kopeerimisel jääksid lahtriaadressid samaks.

   Kui soovite veerust duplikaate leida B, muutke viiteid nii, et valem näeks välja järgmine:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Selle asemel "Ainult"Ja"Duplikaat» Saate kirjutada oma sildid, näiteks «Ei leitud"Ja"avastatud"või jätke ainult"Duplikaatja sisestage teise väärtuse asemel tühik. Viimasel juhul jäävad tühjaks need lahtrid, mille kohta duplikaate ei leita, ja usun, et selline andmete esitus on edasiseks analüüsiks kõige mugavam.

2. Nüüd kopeerime oma valemi kõigisse veeru lahtritesse C, kuni alumise reani, mis sisaldab veerus olevaid andmeid A. Selleks viige hiirekursor lahtri alumisse paremasse nurka C1, osutub kursor musta ristiku kujul, nagu on näidatud alloleval pildil:Klõpsake ja hoidke all hiire vasakut nuppu ja lohistage raami ääris alla, tõstes esile kõik lahtrid, kuhu soovite valemi sisestada. Kui kõik vajalikud lahtrid on valitud, vabastage hiirenupp:

   

Vihje: Suurtes tabelites on valemi kopeerimine kiirem, kui kasutate kiirklahve. Tõstke esile 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 vajutage Ctrl + V (valemi sisestamiseks kõikidesse valitud lahtritesse).

3. Suurepärane, nüüd on kõik dubleerivad väärtused märgitud kui "Duplikaat":

Valik B: kaks veergu on erinevatel lehtedel (erinevates töövihikutes)

1. Töölehe esimese tühja veeru esimeses lahtris Sheet2 (meie puhul on see veerg B) sisestage järgmine valem:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Siin Sheet3 on selle lehe nimi, millel asub 2. veerg, ja $A $ 1: $ A $ 10000 on lahtri aadressid 1.-st viimaseni selles 2. veerus.

2. Kopeerige valem veeru kõikidesse lahtritesse B (sama mis variant A).
3. Saame sellise tulemuse:

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 duplikaatkirjete 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 päiseid, peate need lisama. Selleks asetage kursor numbrile, mis tähistab esimest rida ja see muutub mustaks nooleks, nagu on näidatud alloleval joonisel:

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

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

Pärast seda klõpsake väikesel hallil noolel "Kas dubleerida?« filtrimenüü avamiseks; tühjendage kõik selles loendis olevad üksused, v.a Duplikaatja vajutage OK.

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

Veeru kõigi ridade uuesti kuvamiseks А, klõpsake veerus filtri sümbolil В, mis näeb nüüd välja nagu väikese noolega lehter, ja valige Vali kõik (Vali kõik). Või saate seda teha lindi kaudu, klõpsates kuupäev (Andmed) > Valige ja filtreerige (Sordi ja filtreeri) > Puhasta valikud (Tühjenda), nagu on näidatud alloleval ekraanipildil:

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

Kui märkmed "Duplikaat” ei piisa teie eesmärkide jaoks ja soovite märkida duplikaatlahtrid erineva fondivärvi, täitevärvi või mõne muu meetodiga…

Sel juhul filtreerige duplikaadid nagu ülal näidatud, valige kõik filtreeritud lahtrid ja klõpsake Ctrl + 1dialoogi avamiseks Format Cells (lahtrivorming). Näitena muudame duplikaatidega ridade lahtrite täitevärvi erekollaseks. Loomulikult saab tööriistaga täitevärvi muuta Täitma (Täitevärv) vahekaart Avaleht (Kodu), kuid dialoogiboksi eelis Format Cells (Cell Format), kuna saate korraga konfigureerida kõiki vormindamisvalikuid.

Nüüd ei jää te kindlasti kahekordsete lahtrite vahele:

Duplikaatväärtuste eemaldamine esimesest veerust

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

Kui kaks võrreldavat veergu on erinevatel lehtedel, see tähendab, et erinevates tabelites paremklõpsake valitud vahemikku ja valige Kustuta rida (Eemalda rida):

press OKkui Excel palub teil kinnitada, et soovite tõesti kogu leherea kustutada, ja seejärel filtri tühjendada. Nagu näete, jäävad alles ainult unikaalsete väärtustega read:

Kui samal lehel on 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 eemaldada, kuna see eemaldab lahtrid ka teisest veerust. Nii et veergu jätta ainult kordumatud kirjed А, tee seda:

1. Filtreerige tabel, et kuvada ainult dubleerivad väärtused, ja valige need lahtrid. Paremklõpsake neid ja valige kontekstimenüüst Selge sisu (selge sisu).
2. Puhastage filter.
3. Valige veerus kõik lahtrid А, alustades lahtrist A1 kuni andmeid sisaldava põhjani.
4. kliki kuupäev (Andmed) ja vajutage Sorteeri A-st Z-ni (Sorteeri A-st Z-ni). Valige avanevas dialoogiboksis Jätkake praeguse valikuga (Sortige määratud valiku piires) ja klõpsake nuppu Must (Sorteerimine):
5. Kustutage veerg valemiga, te ei vaja seda enam, edaspidi on teil ainult kordumatud väärtused.
6. See on kõik, nüüd veerg А sisaldab ainult unikaalseid andmeid, mida veerus pole В:

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