Tutorialul arată cum să comparați șiruri de text în Excel pentru a nu ține cont de majuscule și minuscule și pentru potrivire exactă. Veți învăța o serie de formule pentru a compara două celule în funcție de valorile lor, de lungimea șirului sau de numărul de apariții ale unui anumit caracter, precum și cum să comparați mai multe celule.
Când folosiți Excel pentru analiza datelor, acuratețea este cea mai vitală preocupare. Informațiile incorecte conduc la termene limită ratate, tendințe greșit judecate, decizii greșite și venituri pierdute.
În timp ce formulele Excel sunt întotdeauna perfect adevărate, rezultatele lor pot fi greșite deoarece unele date eronate au pătruns în sistem. În acest caz, singurul remediu este de a verifica datele pentru acuratețe. Nu este mare lucru să compari manual două celule, dar este aproape imposibil să identifici diferențele dintre sute și mii de șiruri de text.
Acest tutorial vă va învăța cum să automatizați sarcina plictisitoare și predispusă la erori de comparare a celulelor și ce formule este cel mai bine să folosiți în fiecare caz în parte.
- Cum se compară 2 celule în Excel
- Cum se compară mai multe celule
- Comparați un interval de celule cu o celulă de probă
- Comparați celulele după lungimea șirului de caractere
- Comparați celulele după lungimea șirului de caractere
- Comparați șiruri de caractere în funcție de aparițiile unui anumit caracter
Cum se compară două celule în Excel
Există două moduri diferite de a compara șiruri de caractere în Excel, în funcție de faptul că se caută case-o comparație sensibilă sau insensibilă la majuscule și minuscule.
Formula insensibilă la majuscule și minuscule pentru a compara 2 celule
Pentru a compara două celule în Excel ignorând majusculele, utilizați o formulă simplă ca aceasta:
=A1=B1
Unde A1 și B1 sunt celulele pe care le comparați. Rezultatul formulei sunt valorile booleene TRUE și FALSE.
Dacă doriți să afișați propriile texte pentru potriviri și diferențe, încorporați declarația de mai sus în testul logic al funcției IF. De exemplu:
=IF(A1=B1, "Equal", "Not equal")
După cum vedeți în captura de ecran de mai jos, ambele formule compară la fel de bine șiruri de text, date și numere:
Formulă sensibilă la majuscule pentru a compara șiruri de caractere în Excel
În unele situații, poate fi important nu numai să comparați textul din două celule, ci și să comparați cazul caracterelor. Compararea textului în funcție de majuscule și minuscule se poate face cu ajutorul funcției Excel EXACT:
Unde text1 și text2 sunt cele două celule pe care le comparați.
Să presupunem că șirurile de caractere se află în celulele A2 și B2, formula este următoarea:
=EXACT(A2, B2)
Ca rezultat, obțineți TRUE pentru că șirurile de text se potrivesc exact, inclusiv cazul fiecărui caracter, FALSE în caz contrar.
Dacă doriți ca funcția EXACT să livreze și alte rezultate, încorporați-o într-o formulă IF și introduceți propriul text pentru argumentele value_if_true și value_if_false:
=IF(EXACT(A2 ,B2), "Exactly equal", "Not equal")
Captura de ecran următoare prezintă rezultatele comparării șirurilor de caractere sensibile la majuscule și minuscule în Excel:
Cum se compară mai multe celule în Excel
Pentru a compara mai mult de 2 celule pe un rând, utilizați formulele discutate în exemplele de mai sus în combinație cu operatorul AND. Detaliile complete sunt prezentate mai jos.
Formulă insensibilă la majuscule pentru a compara mai mult de 2 celule
În funcție de modul în care doriți să afișați rezultatele, utilizați una dintre următoarele formule:
=AND(A2=B2, A2=C2)
sau
=IF(AND(A2=B2, A2=C2), "Equal", "Not equal")
Formula AND returnează TRUE dacă toate celulele conțin aceeași valoare, FALSE dacă vreo valoare este diferită. Formula IF produce etichetele pe care le introduceți în ea, „Equal” și „Not equal” în acest exemplu.
După cum se demonstrează în captura de ecran de mai jos, formula funcționează perfect cu orice tip de date – text, date și valori numerice:
Formulă sensibilă la majuscule pentru a compara textul din mai multe celule
Pentru a compara mai multe șiruri de caractere între ele pentru a vedea dacă se potrivesc exact, utilizați următoarele formule:
=AND(EXACT(A2,B2), EXACT(A2, C2))
Sau
=IF(AND(EXACT(A2,B2), EXACT(A2, C2)),"Exactly equal", "Not equal")
Ca și în exemplul anterior, prima formulă livrează valorile ADEVĂRAT și FALS, în timp ce cea de-a doua afișează textele proprii pentru potriviri și diferențe:
Compararea unui interval de celule cu o celulă de probă
Exemplele următoare arată cum puteți verifica dacă toate celulele dintr-un anumit interval conțin același text ca într-o celulă de probă.
Formulă insensibilă la majuscule pentru a compara celulele cu un text eșantion
Dacă nu contează cu adevărat majusculele caracterelor, puteți utiliza următoarea formulă pentru a compara celulele cu un eșantion:
În testul logic al funcției IF, comparați două numere:
- Numărul total de celule dintr-un interval specificat (numărul de rânduri înmulțit cu numărul de coloane) și
- Numărul de celule care conțin aceeași valoare ca în celula eșantion (returnat de funcția COUNTIF).
Să presupunem că textul de probă se află în C2, iar șirurile de comparat se află în intervalul A2:B6, formula este următoarea:
=ROWS(A2:B6)*COLUMNS(A2:B6)=COUNTIF(A2:B6,C2)
Pentru ca rezultatele să fie mai ușor de utilizat, de ex. afișați ceva de genul „Toate se potrivesc” și „Nu toate se potrivesc” în loc de TRUE și FALSE, utilizați funcția IF, așa cum am făcut în exemplele anterioare:
=IF(ROWS(A2:B6)*COLUMNS(A2:B6)=COUNTIF(A2:B6,C2),"All match", "Not all match")
După cum se arată în captura de ecran de mai sus, formula face față perfect unui interval de șiruri de text, dar poate fi utilizată și pentru a compara numere și date.
Formula de comparare a șirurilor de caractere cu un text de probă
În cazul în care cazul caracterelor face diferența, puteți compara șirurile de caractere cu textul de probă folosind următoarele formule de matrice.
Cu intervalul sursă care rezidă în A2:B6 și textul de probă în C2, formula ia următoarea formă:
=IF(ROWS(A2:B6)*COLUMNS(A2:B6)=SUM(--EXACT(C2, A2:B6)), "All match", "Not all match")
În mod diferit de formulele obișnuite din Excel, formulele array se completează prin apăsarea Ctrl + Shift + Enter. Dacă este introdusă corect, Excel înglobează formula array în {între paranteze curbe}, așa cum se arată în captura de ecran:
Cum se compară două celule în funcție de lungimea șirului de caractere
Cîteodată, este posibil să doriți să verificați dacă șirurile de text din fiecare rînd conțin un număr egal de caractere. Formula pentru această sarcină este foarte simplă. Mai întâi, obțineți lungimea șirului de caractere din două celule folosind funcția LEN, apoi comparați numerele.
Supunând că șirurile de comparat se află în celulele A2 și B2, folosiți oricare dintre următoarele formule:
=LEN(A2)=LEN(B2)
Sau
=IF(LEN(A2)=LEN(B2), "Equal", "Not equal")
După cum știți deja, prima formulă returnează valorile booleene TRUE sau FALSE, în timp ce a doua formulă emite propriile rezultate:
Așa cum se demonstrează în captura de ecran de mai sus, formulele funcționează atât pentru șiruri de text, cât și pentru numere.
Comparați două celule în funcție de aparițiile unui anumit caracter
Acesta este ultimul exemplu din tutorialul nostru de comparare a șirurilor de caractere în Excel și prezintă o soluție pentru o sarcină destul de specifică. Să presupunem că aveți 2 coloane de șiruri de text care conțin un caracter important pentru dumneavoastră. Scopul dvs. este să verificați dacă două celule din fiecare rând conțin același număr de apariții ale unui anumit caracter.
Pentru a face lucrurile mai clare, luați în considerare următorul exemplu. Să spunem că aveți două liste de comenzi expediate (coloana B) și primite (coloana C). Fiecare rând conține comenzi pentru un anumit articol, al cărui identificator unic este inclus în toate ID-urile comenzilor și este listat în același rând din coloana A (vă rugăm să consultați captura de ecran de mai jos). Vreți să vă asigurați că fiecare rând conține un număr egal de articole expediate și primite cu acel ID specific.
Pentru a rezolva această problemă, scrieți o formulă cu următoarea logică.
- În primul rând, înlocuiți identificatorul unic cu nimic folosind funcția SUBSTITUTE:
SUBSTITUTE(A1, character_to_count,"") - Apoi, calculați de câte ori apare identificatorul unic în fiecare celulă. Pentru aceasta, obțineți lungimea șirului fără identificatorul unic și scădeți-o din lungimea totală a șirului. Această parte se scrie pentru celula 1 și celula 2 în mod individual, de exemplu:
LEN(cell 1) - LEN(SUBSTITUTE(cell 1, character_to_count, ""))
șiLEN(cell 2) - LEN(SUBSTITUTE(cell 2, character_to_count, "")) - În cele din urmă, comparați aceste 2 numere prin plasarea semnului de egalitate (=) între părțile de mai sus.
LEN(cell 2) – LEN(SUBSTITUTE(cell 2, character_to_count, „”))
În exemplul nostru, identificatorul unic este în A2, iar șirurile de comparat sunt în celulele B2 și C2. Așadar, formula completă este următoarea:
=LEN(B2)-LEN(SUBSTITUTE(B2,$A2,""))=LEN(C2)-LEN(SUBSTITUTE(C2,$A2,""))
Formula returnează TRUE dacă celulele B2 și C2 conțin un număr egal de apariții ale caracterului din A2, iar în caz contrar FALSE. Pentru ca rezultatele să fie mai semnificative pentru utilizatorii dumneavoastră, puteți încorpora formula în funcția IF:
=IF(LEN(B2)-LEN(SUBSTITUTE(B2, $A2,""))=LEN(C2)-LEN(SUBSTITUTE(C2, $A2,"")), "Equal", "Not equal")
Cum puteți vedea în captura de ecran de mai sus, formula funcționează perfect în ciuda câtorva complicații suplimentare:
- Caracterul care trebuie numărat (identificatorul unic) poate apărea oriunde într-un șir de text.
- Șirurile de caractere conțin un număr variabil de caractere și diferiți separatori, cum ar fi punct și virgulă, virgulă sau spațiu.
Acesta este modul în care se compară șirurile de caractere în Excel. Pentru a vedea mai îndeaproape formulele discutate în acest tutorial, vă invităm să descărcați Excel Compare Strings Worksheet. Vă mulțumesc pentru lectură și sper să ne revedem săptămâna viitoare pe blogul nostru.
Te-ar putea interesa și
- Comparați două coloane în Excel pentru potriviri și diferențe
- Cum se compară două foi de calcul Excel pentru diferențe
- Cum se utilizează operatorul logic pentru a compara numere
- 4 moduri de a face un vlookup sensibil la majuscule și minuscule în Excel
.