Kahe veeru vahelisi duplikaatkirjeid saate otsida ja esile tõsta, kasutades Google'i arvutustabelite tingimusliku vormindamise funktsiooni.
Töötades Google'i arvutustabelites suurte andmehulkidega, puutute tõenäoliselt kokku probleemiga, kus peate tegelema paljude dubleerivate väärtustega. Kuigi mõned duplikaadid sisestatakse tahtlikult, samas kui teised on vead. See kehtib eriti siis, kui teete meeskonnaga samal lehel koostööd.
Andmete analüüsimisel Google'i arvutustabelites võib duplikaatide välja filtreerimine olla hädavajalik ja mugav. Kuigi Google'i arvutustabelid ei toeta lehtedelt duplikaatide leidmiseks, pakub see mitmeid viise lahtrites duplikaatide võrdlemiseks, tuvastamiseks ja eemaldamiseks.
Mõnikord soovite võrrelda veeru iga väärtust teise veeruga ja leida, kas selles on duplikaate ja vastupidi. Google'i arvutustabelites saate tingimusvormingu funktsiooni abil hõlpsasti leida kahe veeru vahelisi duplikaate. Selles artiklis näitame teile, kuidas võrrelda kahte Google'i arvutustabelite veergu ja leida nende vahel duplikaate.
Tingimusliku vormingu abil otsige kahe veeru vahelt topeltkirjeid
Tingimuslik vormindamine on Google'i arvutustabelite funktsioon, mis võimaldab kasutajal teatud tingimustel lahtrile või lahtrivahemikule rakendada kindlaid vorminguid, nagu fondi värv, ikoonid ja andmeribad.
Seda tingimusvormingut saate kasutada kahe veeru vahel duplikaatkirjete esiletõstmiseks, täites lahtrid värviga või muutes teksti värvi. Peate võrdlema veeru iga väärtust teise veeruga ja leidma, kas mõni väärtus kordub. Selle toimimiseks peate rakendama tingimusvormingu igale veerule eraldi. Selleks järgige neid samme.
Avage arvutustabel, mille kohta soovite Google'i arvutustabelites duplikaate kontrollida. Esmalt valige esimene veerg (A), mida veeruga B kontrollida. Saate kogu veeru esile tõsta, klõpsates selle kohal oleval veeru tähel.
Seejärel klõpsake menüüribal menüüd "Vorming" ja valige "Tingimuslik vormindamine".
Google'i lehtede paremal küljel avaneb menüü Tingimuslik vormindamine. Saate kinnitada, et lahtrivahemik on see, mille valisite valiku „Rakenda vahemikule” all. Kui soovite vahemikku muuta, klõpsake vahemiku ikoonil ja valige mõni muu vahemik.
Seejärel klõpsake jaotises „Vormingu reeglid” rippmenüüd ja valige suvand „Kohandatud valem on”.
Nüüd peate sisestama kohandatud valemi väljale Väärtus või valem.
Kui valisite terve veeru (B:B), sisestage järgmine COUNTIF valem jaotises Vormingureeglid väljale Väärtus või valem:
=countif($B:$B,$A2)>0või
Kui valisite veerus lahtrite vahemiku (näiteks sada lahtrit, A2:A30), kasutage järgmist valemit:
=LOENDIF($B$2:$B$30, $A2)>0Valemi sisestamisel asendage kindlasti kõik valemis oleva tähe "B" eksemplarid esiletõstetud veeru tähega. Lisame lahtriviite ette märgi $, et muuta need absoluutseks vahemikuks, nii et see ei muuda valemi rakendamist.
Jaotises Vormindamisstiil saate valida dubleerivate üksuste esiletõstmiseks vormingustiili. Vaikimisi kasutab see rohelist täitevärvi.
Saate valida ühe eelseadistatud vormingustiilidest, klõpsates suvandite Vormindamisstiil all nuppu Vaikimisi ja seejärel valides ühe eelseadistustest.
Või võite duplikaatide esiletõstmiseks kasutada mõnda seitsmest vormindamistööriistast (paks, kaldkiri, allajoonitud, läbikriipsutatud, tekstivärv, täitevärv) jaotises Vormindamislaad.
Siin valime duplikaatlahtrite täitevärvi, klõpsates ikooni Täitmise värv ja valides kollase värvi.
Kui olete vorminduse valinud, klõpsake lahtrite esiletõstmiseks nuppu "Valmis".
Funktsioon COUNTIF loendab, mitu korda iga lahtri väärtus veerus A kuvatakse veerus B. Seega, kui üksus ilmub veerus B kasvõi üks kord, tagastab valem TRUE. Seejärel tõstetakse see üksus esile veerus A vastavalt teie valitud vormingule.
See ei tõsta esile duplikaate, vaid tõstab esile üksused, millel on veerus B duplikaadid. See tähendab, et igal kollaselt esiletõstetud üksusel on veerus B duplikaadid.
Nüüd peame veerule B rakendama tingimusvormingu, kasutades sama valemit. Selleks valige teine veerg (B2:B30), minge menüüsse "Format" ja valige "Tingimuslik vormindamine".
Teise võimalusena klõpsake paanil „Tingimusliku vormingu reeglid” nuppu „Lisa veel üks reegel”.
Järgmisena kinnitage vahemik (B2:B30) kastis „Rakenda vahemikule”.
Seejärel määrake suvand "Vorminda lahtrid, kui..." väärtusele "Kohandatud valem on" ja sisestage valemikasti järgmine valem:
=LOENDIF($A$2:$A$30, $B2)>0Siin kasutame esimeses argumendis veergu A vahemikku ($A$2:$A$30) ja teises argumendis '$B2'. See valem kontrollib lahtri väärtust veerus B iga lahtriga veerus A. Kui leitakse vaste (dublikaat), tõstab tingimusvormingus selle üksuse veerus B
Seejärel määrake vormingu suvandites Vormindamisstiil ja klõpsake nuppu Valmis. Siin valime veeru B oranži värvi.
See tõstab esile veeru B üksused, millel on veerus A duplikaadid. Nüüd olete leidnud ja esile tõstnud kahe veeru vahel duplikaadid.
Tõenäoliselt märkasite, kuigi veerus A on „Arcelia” duplikaat, pole seda esile tõstetud. Põhjus on selles, et duplikaadi väärtus on ainult ühes veerus (A), mitte veergude vahel. Seetõttu pole seda esile tõstetud.
Tõstke esile Duplikaadid kahe sama rea veeru vahel
Samuti saate tingimusvormingu abil esile tõsta read, millel on samad väärtused (duplikaadid) kahe veeru vahel. Tingimusliku vormingu reegel saab kontrollida iga rida ja tõstab esile read, mille mõlemas veerus on vastavad andmed. Seda saate teha järgmiselt.
Esmalt valige mõlemad veerud, mida soovite võrrelda, seejärel minge menüüsse "Vorming" ja valige "Tingimuslik vormindamine".
Kinnitage paanil Tingimusliku vormingu reeglid vahemikku „Rakenda vahemikule” ja valige rippmenüüst „Valemi lahtrid, kui...” valik „Kohandatud valem on”.
Seejärel sisestage allolev valem väljale Väärtus või valem:
=$A2=$B2See valem võrdleb kahte veergu ridade kaupa ja tõstab esile read, millel on identsed väärtused (duplikaadid). Nagu näete, on siia sisestatud valem ainult valitud vahemiku esimese rea jaoks, kuid tingimusliku vormindamise funktsioon rakendab valemit automaatselt kõikidele valitud vahemiku ridadele.
Seejärel määrake vorming suvandites Vormindamisstiil ja klõpsake nuppu Valmis.
Nagu näete, tõstetakse esile ainult need read, millel on kahe veeru vahel vastavad andmed (duplikaadid), ja kõiki teisi duplikaate ignoreeritakse.
Tõstke esile topeltlahtrid mitmes veerus
Suuremate ja paljude veergudega arvutustabelitega töötades võiksite ühe või kahe veeru asemel esile tõsta kõik mitmes veerus kuvatavad duplikaadid. Duplikaadi mitmes veerus esiletõstmiseks saate siiski kasutada tingimusvormingut.
Esiteks valige ühe või kahe veeru asemel kõigi veergude ja ridade vahemik, millest soovite duplikaate otsida. Saate valida terveid veerge, hoides all klahvi Ctrl ja klõpsates seejärel iga veeru ülaosas olevat tähte. Teise võimalusena võite klõpsata oma vahemiku esimesel ja viimasel lahtril, hoides samal ajal all tõstuklahvi, et valida korraga mitu veergu.
Näites valime A2:C30.
Seejärel klõpsake menüüs suvandit Vorminda ja valige Tingimuslik vormindamine.
Määrake Tingimusliku vormingu reeglites Vormingu reeglid väärtusele „Kohandatud valem on” ja seejärel sisestage väljale „Väärtus või valem” järgmine valem:
=countif($A$2:$C$30,A2)>Lisame lahtriviite ette märgi "$", et muuta need absoluutseteks veergudeks, nii et see ei muuda valemi rakendamist. Valemi saate sisestada ka ilma märkideta "$", see töötab mõlemal viisil.
Seejärel valige suvandite „Vormindamisstiil” abil vorming, milles soovite duplikaatlahtrid esile tõsta. Siin valime täitevärvi "Kollane". Pärast seda klõpsake nuppu "Valmis".
See tõstab esile duplikaadid kõigis valitud veergudes, nagu allpool näidatud.
Pärast tingimusvormingu rakendamist saate tingimusvormingu reeglit igal ajal muuta või kustutada.
Kui soovite muuta praegust tingimusvormingu reeglit, valige mis tahes tingimusliku vorminguga lahter, minge menüüsse "Vorminda" ja valige "Tingimusvorming".
See avab paremal paani „Tingimuslikud vormingureeglid” koos praegusele valikule rakendatud vormingureeglite loendiga. Kui hõljutate kursorit reegli kohal, kuvatakse teile kustutamisnupp, reegli eemaldamiseks klõpsake kustutamisnupul. Või kui soovite praegu kuvatavat reeglit muuta, klõpsake reeglil endal.
Kui soovite praegusele reeglile lisada mõne muu tingimusliku vormingu, klõpsake nuppu Lisa veel üks reegel.
Loendage kahe veeru vahelisi duplikaate
Mõnikord soovite lugeda, mitu korda ühes veerus olev väärtus teises veerus kordub. Seda saab hõlpsasti teha sama funktsiooni COUNTIF abil.
Et leida, mitu korda veerus A olev väärtus veerus B eksisteerib, sisestage järgmise veeru lahtrisse järgmine valem:
=COUNTIF($B$2:$B$30,$A2)Sisestage see valem lahtrisse C2. See valem loendab, mitu korda on lahtris A2 olev väärtus veerus (B2:B30) olemas, ja tagastab arvu lahtris C2.
Valemi tippimisel ja sisestusklahvi vajutamisel kuvatakse automaatse täitmise funktsioon. Valemi automaatseks täitmiseks ülejäänud lahtrites (C3:C30) klõpsake 'Lick mark'.
Kui automaatse täitmise funktsiooni ei kuvata, klõpsake lahtri C2 paremas alanurgas sinist ruutu ja lohistage see alla, et kopeerida lahtris C2 olev valem lahtritesse C3:C30.
Veerg „Võrdlus 1” (C) näitab nüüd, mitu korda iga vastav väärtus veerus A veerus B ilmub. Näiteks ei leidu veerus B väärtust A2 või „Franklyn”, seega Funktsioon COUNTIF tagastab väärtuse 0. Ja väärtus "Loreta" (A5) leitakse veerus B kaks korda, seega tagastab see "2".
Nüüd peame veeru B korduvate arvude leidmiseks kordama samu samme. Selleks sisestage veeru D lahtrisse D2 järgmine valem (võrdlus 2):
=COUNTIF($A$2:$A$30, $B2)Selles valemis asendage vahemik '$B$2:$B$30' kuni '$A$2:$A$30' ja '$B2' väärtusega '$A2'. Funktsioon loendab, mitu korda on lahtris B2 olev väärtus veerus A olemas (A2:A30) ja tagastab loenduse lahtris D2.
Seejärel täitke valem automaatselt ülejäänud lahtritega (D3:D30) veerus D. Nüüd näitab 'Võrdlus 2', mitu korda iga vastav väärtus veerus B ilmub veerus A. Näiteks , leitakse B2 ehk Starki väärtus veerus A kaks korda, seega tagastab funktsioon COUNTIF väärtuse 2.
Märge: Kui soovite loendada kõigi veergude või mitme veeru duplikaate, peate lihtsalt muutma funktsiooni COUNTIF esimese argumendi vahemiku ühe veeru asemel mitme veeru vastu. Näiteks muutke vahemikku A2:A30 väärtuseks A2:B30, mis loendab kõik duplikaadid kahes veerus, mitte ühe veerus.
see on kõik.