Puteți găsi și evidenția intrările duplicate între două coloane folosind funcția Formatare condiționată din Foi de calcul Google.
În timp ce lucrați în Foi de calcul Google cu seturi mari de date, probabil că vă confruntați cu o problemă în care trebuie să faceți față cu multe valori duplicat. În timp ce unele intrări duplicat sunt plasate intenționat, în timp ce altele sunt greșeli. Acest lucru este valabil mai ales atunci când colaborați pe aceeași foaie cu o echipă.
Când vine vorba de analiza datelor din Foi de calcul Google, posibilitatea de a filtra duplicatele poate fi esențială și convenabilă. Deși Google Sheets nu are suport nativ pentru găsirea duplicatelor în foi, oferă mai multe modalități de a compara, identifica și elimina datele duplicate din celule.
Uneori, doriți să comparați fiecare valoare dintr-o coloană cu o altă coloană și să aflați dacă există duplicate în ea și invers. În Foi de calcul Google, puteți găsi cu ușurință duplicate între două coloane cu ajutorul funcției de formatare condiționată. În acest articol, vă vom arăta cum să comparați două coloane în Foi de calcul Google și să găsiți duplicate între ele.
Găsiți intrări duplicate între două coloane folosind formatarea condiționată
Formatarea condiționată este o funcție din Foi de calcul Google care permite utilizatorului să aplice formatări specifice, cum ar fi culoarea fontului, pictogramele și barele de date unei celule sau unui interval de celule, în funcție de anumite condiții.
Puteți folosi această formatare condiționată pentru a evidenția intrările duplicate între două coloane, fie prin umplerea celulelor cu culoare, fie prin schimbarea culorii textului. Trebuie să comparați fiecare valoare dintr-o coloană cu o altă coloană și să aflați dacă vreo valoare se repetă. Pentru ca acest lucru să funcționeze, trebuie să aplicați formatare condiționată fiecărei coloane separat. Urmați acești pași pentru a face asta:
Deschideți foaia de calcul pe care doriți să o verificați pentru duplicate în Foi de calcul Google. Mai întâi, selectați prima coloană (A) pentru a verifica cu coloana B. Puteți evidenția întreaga coloană făcând clic pe litera coloanei de deasupra acesteia.
Apoi, faceți clic pe meniul „Format” din bara de meniu și selectați „Formatare condiționată”.
Meniul Formatare condiționată se deschide în partea dreaptă a foilor Google. Puteți confirma că intervalul de celule este ceea ce ați selectat în opțiunea „Aplicați la interval”. Dacă doriți să modificați intervalul, faceți clic pe „pictograma interval” și alegeți un alt interval.
Apoi, faceți clic pe meniul drop-down de sub „Reguli de format” și selectați opțiunea „Formula personalizată este”.
Acum, trebuie să introduceți o formulă personalizată în caseta „Valoare sau formulă”.
Dacă ați selectat o coloană întreagă (B:B), introduceți următoarea formulă COUNTIF în caseta „Valoare sau formulă” din Reguli de format:
=countif($B:$B,$A2)>0Sau,
Dacă ați selectat un interval de celule într-o coloană (să zicem o sută de celule, A2:A30), utilizați această formulă:
=COUNTIF($B$2:$B$30, $A2)>0Când introduceți formula, asigurați-vă că înlocuiți toate aparițiile literei „B” din formulă cu litera coloanei pe care ați evidențiat-o. Adăugăm semnul „$” înaintea referințelor de celule pentru a le face un interval absolut, astfel încât să nu se schimbe, aplicăm formula.
În secțiunea Stil de formatare, puteți alege stilul de formatare pentru evidențierea elementelor duplicate. În mod implicit, va folosi culoarea de umplere verde.
Puteți alege unul dintre stilurile de formatare prestabilite făcând clic pe „Implicit” din opțiunile „Stil de formatare”, apoi selectând una dintre presetări.
Sau puteți utiliza oricare dintre cele șapte instrumente de formatare (Aldin, Cursiv, Subliniat, Barat, Culoare text, Culoare umplere) din secțiunea „Stil de formatare” pentru a evidenția duplicatele.
Aici, alegem o culoare de umplere pentru celulele duplicate făcând clic pe pictograma „Culoare de umplere” și selectând culoarea „galben”.
După ce ați selectat formatarea, faceți clic pe „Terminat” pentru a evidenția celulele.
Funcția COUNTIF numără de câte ori apare fiecare valoare a celulei din „Coloana A” în „Coloana B”. Deci, dacă un element apare chiar și o dată în coloana B, formula returnează TRUE. Apoi acel element va fi evidențiat în „Coloana A” în funcție de formatarea pe care ați ales-o.
Aceasta nu evidențiază duplicatele, ci mai degrabă evidențiază elementele care au duplicate în coloana B. Aceasta înseamnă că fiecare element evidențiat galben are duplicate în coloana B.
Acum, trebuie să aplicăm formatarea condiționată la coloana B folosind aceeași formulă. Pentru a face asta, selectați a doua coloană (B2:B30), accesați meniul „Format” și selectați „Formatare condiționată”.
Ca alternativă, faceți clic pe butonul „Adăugați o altă regulă” din panoul „Reguli de format condiționat”.
Apoi, confirmați intervalul (B2:B30) în caseta „Aplicați la interval”.
Apoi, setați opțiunea „Format celule dacă...” la „Formula personalizată este” și introduceți formula de mai jos în caseta de formule:
=COUNTIF($A$2:$A$30, $B2)>0Aici, folosim coloana A interval ($A$2:$A$30) în primul argument și „$B2” în al doilea argument. Această formulă va verifica valoarea celulei din „coloana B” cu fiecare celulă din coloana A. Dacă se găsește o potrivire (duplicată), atunci formatarea condiționată va înălța acel element în „coloana B”.
Apoi, specificați formatarea în opțiunile „Stil de formatare” și faceți clic pe „Terminat”. Aici, alegem culoarea portocalie pentru coloana B.
Aceasta va evidenția elementele din coloana B care au duplicate în coloana A. Acum, ați găsit și evidențiat elementele duplicat între două coloane.
Probabil ați observat, deși există un duplicat pentru „Arcelia” în coloana A, acesta nu este evidențiat. Se datorează faptului că valoarea duplicat este doar într-o coloană (A), nu între coloane. Prin urmare, nu este evidențiată.
Evidențiați duplicatele între două coloane din același rând
De asemenea, puteți evidenția rândurile care au aceleași valori (duplicate) între două coloane folosind formatarea condiționată. Regula de formatare condiționată poate verifica fiecare rând și evidențiază rândurile care au date care se potrivesc în ambele coloane. Iată cum faci asta:
Mai întâi, selectați ambele coloane pe care doriți să le comparați, apoi accesați meniul „Format” și selectați „Formatare condiționată”.
În panoul Reguli de format condiționat, confirmați intervalul în caseta „Aplicați la interval” și alegeți „Formula personalizată este” din meniul derulant „Celele de formulă dacă...”.
Apoi, introduceți formula de mai jos în caseta „Valoare sau formulă”:
=$A2=$B2Această formulă va compara cele două coloane rând cu rând și va evidenția rândurile care au valori identice (duplicate). După cum puteți vedea, formula introdusă aici este doar pentru primul rând al intervalului selectat, dar formula va fi aplicată automat tuturor rândurilor din intervalul selectat prin caracteristica de formatare condiționată.
Apoi, specificați formatarea din opțiunile „Stil de formatare” și faceți clic pe „Terminat”.
După cum puteți vedea, numai rândurile care au date care se potrivesc (duplicate) între două coloane vor fi evidențiate și toate celelalte duplicate vor fi ignorate.
Evidențiați Celulele duplicate în mai multe coloane
Când lucrați cu foi de calcul mai mari, cu mai multe coloane, vă recomandăm să evidențiați toate duplicatele care apar pe mai multe coloane în loc de doar una sau două coloane. Puteți folosi în continuare formatarea condiționată pentru a evidenția duplicatul în mai multe coloane.
Mai întâi, selectați intervalul tuturor coloanelor și rândurilor pe care doriți să le căutați duplicate în loc de doar una sau două coloane. Puteți selecta coloane întregi ținând apăsată tasta Ctrl, apoi făcând clic pe litera din partea de sus a fiecărei coloane. Alternativ, puteți, de asemenea, să faceți clic pe prima și ultima celulă din intervalul dvs., ținând apăsată și tasta Shift pentru a selecta mai multe coloane simultan.
În exemplu, selectăm A2:C30.
Apoi, faceți clic pe opțiunea „Format” din meniu și selectați „Formatare condiționată”.
În Regulile de format condiționat, setați regulile de format la „Formula personalizată este”, apoi introduceți următoarea formulă în caseta „Valoare sau formulă”:
=countif($A$2:$C$30,A2)>Adăugăm semnul „$” înaintea referințelor de celule pentru a le face coloane absolute, astfel încât să nu se schimbe, aplicăm formula. De asemenea, puteți introduce formula fără semnele „$”, funcționează în orice mod.
Apoi, alegeți formatarea în care doriți să evidențiați celulele duplicate folosind opțiunile „Stil de formatare”. Aici, alegem culoarea de umplere „Galben”. După aceea, faceți clic pe „Terminat”.
Aceasta va evidenția duplicatele în toate coloanele pe care le-ați selectat, așa cum se arată mai jos.
După aplicarea formatării condiționate, puteți edita sau șterge regula de formatare condiționată oricând doriți.
Dacă doriți să editați regula actuală de formatare condiționată, selectați orice celulă cu formatare condiționată, accesați „Format” din meniu și selectați „Formatare condiționată”.
Aceasta va deschide panoul „Reguli de format condiționat” din dreapta, cu o listă de reguli de format aplicate selecției curente. Când treceți mouse-ul peste regulă, acesta vă va afișa butonul de ștergere, faceți clic pe butonul de ștergere pentru a elimina regula. Sau, dacă doriți să editați regula care se afișează în prezent, faceți clic pe regula în sine.
Dacă doriți să adăugați o altă formatare condiționată peste regula curentă, faceți clic pe butonul „Adăugați o altă regulă”.
Numărați duplicatele între două coloane
Uneori, doriți să numărați de câte ori se repetă o valoare dintr-o coloană într-o altă coloană. Se poate face cu ușurință folosind aceeași funcție COUNTIF.
Pentru a afla de câte ori există o valoare în coloana A în coloana B, introduceți următoarea formulă într-o celulă dintr-o altă coloană:
=COUNTIF($B$2:$B$30,$A2)Introduceți această formulă în celula C2. Această formulă numără de câte ori există valoarea din celula A2 în coloana (B2:B30) și returnează numărul din celula C2.
Când tastați formula și apăsați Enter, va apărea funcția de completare automată, faceți clic pe „Marcul de bifare” pentru a completa această formulă în restul celulelor (C3:C30).
Dacă funcția de completare automată nu apare, faceți clic pe pătratul albastru din colțul din dreapta jos al celulei C2 și trageți-l în jos pentru a copia formula din celula C2 în celulele C3: C30.
Coloana „Comparație 1” (C) vă va afișa acum de câte ori fiecare valoare corespunzătoare din coloana A apare în coloana B. De exemplu, valoarea lui A2 sau „Franklyn” nu se găsește în coloana B, deci, Funcția COUNTIF returnează „0”. Și valoarea „Loreta” (A5) se găsește de două ori în coloana B, prin urmare, returnează „2”.
Acum, trebuie să repetăm aceiași pași pentru a găsi numărul duplicat al coloanei B. Pentru a face asta, introduceți următoarea formulă în celula D2 din coloana D (Comparația 2):
=COUNTIF($A$2:$A$30,$B2)În această formulă, înlocuiți intervalul de la „$B$2:$B$30” la „$A$2:$A$30” și „$B2” la „$A2”. Funcția numără de câte ori există valoarea din celula B2 în coloana A (A2:A30) și returnează numărul în celula D2.
Apoi, completați automat formula pentru restul celulelor (D3:D30) din coloana D. Acum, „Comparația 2” vă va arăta de câte ori fiecare valoare corespunzătoare din coloana B apare în coloana A. De exemplu , valoarea lui B2 sau „Stark” se găsește de două ori în coloana A, deci, funcția COUNTIF returnează „2”.
Notă: Dacă doriți să numărați duplicatele pe toate coloanele sau pe mai multe coloane, trebuie doar să schimbați intervalul din primul argument al funcției COUNTIF în mai multe coloane în loc de o singură coloană. De exemplu, modificați intervalul de la A2:A30 la A2:B30, care va număra toate duplicatele în două coloane în loc de doar una.
Asta e.