Găsirea discrepanțelor în Excel – 5 tehnici simple explicate

Când lucrați cu seturi de date mari în Microsoft Excel, întâlnirea discrepanțelor în date este inevitabil. Identificarea și remedierea acestor inconsecvențe este esențială pentru asigurarea unor raportări și analize corecte.

Veștile bune? Excel vine cu o varietate de instrumente și tehnici pentru a vă ajuta să găsiți și să rezolvați aceste probleme. În acest articol, veți afla despre unele dintre cele mai eficiente metode. Aceasta include filtrarea, formatarea condiționată și utilizarea formulelor avansate.

Încorporând aceste tehnici în fluxul de lucru zilnic, veți deveni mai eficient în gestionarea și analiza datelor dvs. În cele din urmă, duce la o mai bună luare a deciziilor și la rezultate îmbunătățite pentru proiectele dvs.

Cuprins

• Înțelegerea discrepanțelor în Excel
• 5 moduri de a găsi discrepanțe în Excel
• #1 - Găsirea discrepanțelor prin revizuirea manuală în Excel
• #2 – Folosirea filtrului în Excel pentru a detecta și a remedia datele inconsecvente
• 3# – Evidențierea discrepanțelor cu formatarea condiționată
• #4 - Găsirea discrepanțelor cu funcțiile Excel avansate
  • Funcțiile IF și IS
  • CĂUTARE V, CĂUTARE ȘI CĂUTARE XL
  • MECI
• #5 – Utilizarea programelor de completare Excel pentru detectarea discrepanțelor
• Cum puteți crea reguli de validare a datelor pentru a preveni discrepanțe?
• Cuvântul nostru final

Înțelegerea discrepanțelor în Excel

Discrepanțele în rânduri și coloane pot rezulta din erori de introducere a datelor, intrări duplicate sau nereguli în formatarea datelor.

Aruncă o privire la următoarele date. Veți observa că Genul lui Walter White și Wayne Cena este scris greșit – Mael în loc de Masculin. Într-adevăr, este simplu să observați și să remediați erorile din acest scenariu. Dar nu este cazul foilor de calcul care conțin mii de rânduri și coloane.

Pentru a găsi și gestiona aceste discrepanțe, puteți utiliza următoarele caracteristici Excel :

• Filtre: aplicați filtre pentru anumite coloane pentru a vedea valori unice și pentru a identifica rapid datele inconsecvente.
• Formatare condiționată: utilizați regulile de formatare pentru a evidenția celulele care îndeplinesc anumite criterii sau date nepotrivite între coloane.
• Funcții Excel avansate: Utilizați funcții precum MATCH și VLOOKUP pentru a compara seturile de date și pentru a identifica discrepanțe între ele.

Înțelegând cum să utilizați aceste caracteristici în Excel, puteți identifica și rectifica în mod eficient discrepanțele din seturile dvs. de date.

5 moduri de a găsi discrepanțe în Excel

Există 5 moduri principale prin care ne place să abordăm discrepanțele în Excel:

1. Revizuire manuală
2. Filtrare
3. Formatarea condițională
4. Funcții Excel avansate
5. Suplimente Excel

Să trecem prin fiecare dintre ele, astfel încât să puteți găsi cel mai bun mod care vi se potrivește.

#1 - Găsirea discrepanțelor prin revizuirea manuală în Excel

Când lucrați cu Excel , o revizuire manuală poate fi o metodă utilă pentru identificarea discrepanțelor în datele dvs. Acest proces necesită o examinare atentă a seturilor de date pentru a identifica eventualele inconsecvențe sau erori.

Iată câțiva pași pe care îi puteți urma pentru a efectua o revizuire manuală:

• Începeți prin a înțelege clar seturile de date cu care lucrați. Familiarizați-vă cu rândurile, coloanele și formatarea.
• Derulați prin date și acordați o atenție deosebită informațiilor prezentate în fiecare celulă. Puteți folosi instrumente precum evidențierea celulelor cu culoarea de umplere, formatarea textului sau formatarea condiționată pentru a face datele mai ușor de citit.
• Comparați un rând cu altul sau o coloană cu alta. Căutați informații nepotrivite, intrări duplicate etc. care pot cauza inexactități în foile dvs. Excel.
• Pe măsură ce identificați discrepanțe, creați o listă sau utilizați comentarii pentru a le documenta. Mai târziu, acestea pot servi drept referințe importante pentru corectarea problemelor.

Atunci când examinați manual datele pentru discrepanțele dintr-o foaie de calcul Excel, răbdarea este cheia. Acest proces necesită timp. Dar este un bun punct de plecare pentru a identifica potențialele erori.

#2 – Folosirea filtrului în Excel pentru a detecta și a remedia datele inconsecvente

Una dintre cele mai ușoare modalități de a detecta date inconsistente este utilizarea filtrului Excel .

Cu Filtru, vom izola datele incorecte din coloana Sex din următorul set de date:

1. Selectați Sort & Filter din fila Acasă .

2. Alegeți Filtru – asigurați-vă că cursorul se află în intervalul de date.

3. Faceți clic pe săgeata din lista verticală de pe coloana cu date inconsecvente.

4. Acum, deselectați toate datele potrivite. În cazul nostru, este bărbat . Apoi, faceți clic pe OK pentru a salva modificările.

5. Foaia de lucru Excel va afișa acum doar rânduri cu valori incorecte. Pentru a le remedia, faceți clic pe Găsiți și selectați în fila Acasă .

6. Faceţi clic pe Înlocuire când apare lista verticală.

7. Introduceți valoarea incorectă în Găsiți ce și cea corectă în Înlocuiți cu . Apoi, faceți clic pe Înlocuiți tot .

8. Va apărea o casetă de dialog care confirmă modificările. Faceți clic pe OK pentru a continua.

3# – Evidențierea discrepanțelor cu formatarea condiționată

O altă modalitate de a identifica diferențele de coloane și rânduri este utilizarea formatării condiționate.

Puteți accesa reguli precum Evidențiați celule, Sus/Jos, Bare de date, Scale de culori și Seturi de fier. În plus, puteți crea o nouă regulă și puteți gestiona sau șterge regulile anterioare.

Vom folosi un exemplu de tabel pentru a vă arăta puterea formatării condiționate:

1. Selectați tabelul sau rândurile și coloanele preferate. Alegem Coloana A (Companie) și Coloana B (Țara). Apoi, selectați Formatare condiționată din fila Acasă .

2. Selectați un tip de regulă și apoi regula. Alegem regulile de evidențiere a celulelor și valorile duplicate...

3. Acum, trebuie să selectăm dacă să evidențiem valorile duplicate sau unice și culoarea de umplere și fontul pentru a formata celulele. După ce ați terminat, faceți clic pe OK .

4. Toate valorile duplicate sunt acum evidențiate cu Light Red Fill with Dark Red Text.

#4 - Găsirea discrepanțelor cu funcțiile Excel avansate

Funcțiile IF și IS

Pentru a compara celulele și a identifica diferențele, puteți utiliza funcțiile IF și IS.

De exemplu, am folosit formula =IF(A2=B2,"MATCH","NOT MATCHED")pentru a compara celulele A2 și B2. Și am tras formula pentru a o aplica altor celule. Deci, celulele identice returnează MATCH, altfel NOT MATCHED .

Puteți combina IF cu funcții IS, cum ar fi ISNUMBER sau ISTEXT pentru a verifica anumite tipuri de date. De exemplu, formula =IF(ISNUMBER(B2), "Number", "NaN")va returna Număr în coloana c dacă B2 este numeric și NaN în caz contrar.

CĂUTARE V, CĂUTARE ȘI CĂUTARE XL

Funcțiile LOOKUP (verticală) și HLOOKUP (orizontală) sunt utile atunci când se compară valori în coloane sau rânduri diferite ale două tabele. Pentru a face acest lucru, utilizați următoarea formulă: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) sau =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Înlocuiți searchup_value cu valoarea pe care doriți să o găsiți în celălalt tabel, table_array cu intervalul de celule din al doilea tabel, col_index_num sau row_index_num cu numărul de index al coloanei sau rândului din care doriți să returnați o valoare și [range_lookup] cu FALSE pentru potriviri exacte sau TRUE pentru o potrivire aproximativă.

În următorul tutorial, folosim funcția CĂUTARE V din coloana E pentru a compara numele angajaților din coloana A și coloana D pentru a-și scoate salariile din coloana B dacă se găsește o potrivire.

XLOOKUP este o versiune reîmprospătată a VLOOKUP și HLOOKUP disponibilă pe Excel 2021 și Excel 365. Formula pentru XLOOKUP este: =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

Înlocuiți lookup_value cu valoarea pe care doriți să o găsiți în celălalt tabel, lookup_array cu intervalul de celule de căutat în al doilea tabel, return_array cu matricea din care doriți să returnați o valoare și [if_nout_found] cu o valoare text dacă nu se potrivește se gasesc valori.

Iată o mostră rapidă de XLOOKUP cu aceleași tabele:

MECI

Funcția MATCH poate fi folosită și pentru a compara două liste pentru discrepanțe.

Funcția MATCH, =MATCH(lookup_value, lookup_array, [match_type]), caută un articol specificat într-un interval și returnează poziția relativă a articolului în acel interval.

Aici, găsim poziția pepenilor verzi în matricea A2:A8 folosind MATCH. Folosim match_type ca 0 pentru a găsi prima valoare care este exact egală cu lookup_value .

#5 – Utilizarea programelor de completare Excel pentru detectarea discrepanțelor

Excel oferă diverse suplimente și instrumente încorporate care vă pot ajuta să detectați și să analizați discrepanțe mai eficient. În această secțiune, vom discuta câteva dintre aceste suplimente utile.

Mai întâi, luați în considerare utilizarea programului de completare Spreadsheet Inquire . Acest lucru vă poate ajuta să comparați două registre de lucru și să evidențiați diferențele celulă cu celulă. Pentru a activa programul de completare Spreadsheet Inquire:

1. Faceți clic pe fila Fișier .

2. Selectați Opțiuni , apoi faceți clic pe categoria Add-in-uri .

3. În caseta Gestionare , selectați Add-in-uri COM și faceți clic pe Go .

4. Bifați caseta Spreadsheet Inquire și faceți clic pe OK .

5. Odată activat, navigați la fila Întrebare pentru a utiliza comanda Compara fișiere .

Un alt instrument util pentru detectarea discrepanțelor este Analysis ToolPak . Acest program de completare oferă funcții statistice avansate și instrumente de analiză a datelor, care pot fi utile atunci când se analizează discrepanțe.

Pentru a activa pachetul de instrumente de analiză, urmați aceiași pași ca și activarea programului de completare Spreadsheet Inquire. Dar selectați Add-ins Excel în caseta Gestionare și bifați caseta Analysis ToolPak .

În rezumat, Excel oferă diverse programe de completare pentru a vă ajuta să detectați și să gestionați discrepanțe în datele dvs. Vă puteți îmbunătăți acuratețea datelor familiarizându-vă cu aceste instrumente și formule.

Cum puteți crea reguli de validare a datelor pentru a preveni discrepanțe?

Pentru a crea reguli de validare a datelor în Excel care pot ajuta la prevenirea discrepanțelor, urmați acești pași:

1. Selectați celulele pe care doriți să le restricționați. Aceasta poate fi o singură celulă, un interval de celule sau o coloană întreagă.

2. Accesați fila Date din bara de instrumente și faceți clic pe butonul Validare date (marcat cu două casete orizontale, o bifă verde și un cerc încrucișat cu roșu).

3. În fereastra de validare a datelor, asigurați-vă că vă aflați în fila Setări . Aici veți avea ocazia să vă definiți criteriile de validare în funcție de nevoile dvs.

Există diferite tipuri de criterii de validare disponibile în Excel, dintre care unele includ:

• Număr întreg – permite numai numere întregi dintr-un interval specificat.
• Decimală – permite numai numere zecimale dintr-un interval specificat.
• Listă – restricționează intrarea la o listă predefinită de valori acceptabile.
• Data – necesită date dintr-un anumit interval de date.
• Timp – acceptă numai orele dintr-un anumit interval de timp.
• Lungimea textului – impune constrângeri privind lungimea textului introdus.
• Personalizat – vă permite să creați reguli de validare personalizate folosind formule Excel.

După selectarea criteriilor de validare adecvate, specificați parametrii după cum este necesar. De exemplu, dacă ați ales Număr întreg, va trebui să setați Minimul și Maximul cu un validator de interval de date , cum ar fi între, egal cu, mai mic decât etc.

Pe lângă criteriile de validare, puteți configura și mesaje de eroare personalizate și sugestii de introducere pentru a ajuta utilizatorii să înțeleagă și să respecte regulile dvs. de validare. Pentru a face acest lucru, comutați la filele Mesaj de intrare și Alertă de eroare pentru a introduce mesajele dorite.

Prin implementarea regulilor de validare a datelor, reduceți semnificativ probabilitatea inconsecvenței datelor și îmbunătățiți acuratețea generală a foilor de lucru Excel.

Cuvântul nostru final

În acest articol, ați învățat diferite metode pentru a găsi și a rezolva discrepanțe în datele dvs. Excel. Să recapitulăm acele strategii cheie:

• Utilizarea listelor derulante Filtre în coloane pentru a identifica valori unice și date inconsecvente.
• Folosirea formatării condiționate în celulele selectate pentru a evidenția discrepanțe folosind o mulțime de șabloane de reguli.
• Aplicarea de funcții Excel avansate precum MATCH , VLOOKUP , XLOOKUP , etc. pentru a verifica datele în două liste sau tabele.
• Utilizarea programelor de completare Excel pentru a detecta discrepanțe.
• Configurarea regulilor de validare a datelor pentru a preveni erorile în date

Cu aceste tehnici în setul dvs. de abilități Excel, datele dvs. vor fi mai precise și mai de încredere.

Nu uitați să practicați aceste metode în mod regulat pentru a deveni mai eficient în detectarea discrepanțelor în foile de calcul.

Continuați să vă îmbunătățiți abilitățile înscriindu-vă la și continuați să explorați noi caracteristici și funcționalități pentru a deveni și mai pricepuți în Excel.