Una dintre numeroasele capacități ale Microsoft Excel este capacitatea de a compara două liste de date, identificarea potrivirilor dintre liste și identificarea elementelor care se găsesc într-o singură listă. Acest lucru este util atunci când se compară înregistrările financiare sau se verifică dacă un anumit nume se află într-o bază de date. Puteți utiliza funcția MATCH pentru a identifica și marca înregistrările potrivite sau care nu se potrivesc sau puteți utiliza formatarea condiționării cu funcția COUNTIF. Următorii pași vă arată cum să utilizați fiecare pentru a se potrivi cu datele dvs.
Pași
Metoda 1 din 2: Identificarea înregistrărilor cu funcția MATCH
Pasul 1. Copiați listele de date într-o singură foaie de lucru
Excel poate funcționa cu mai multe foi de lucru într-un singur registru de lucru sau cu mai multe registre de lucru, dar veți găsi compararea listelor mai ușor dacă le copiați informațiile pe o singură foaie de lucru.
Pasul 2. Oferiți fiecărui articol din listă un identificator unic
Dacă cele două liste nu împărtășesc un mod comun de a le identifica, poate fi necesar să adăugați o coloană suplimentară la fiecare listă de date care identifică acel element în Excel, astfel încât să poată vedea dacă un element dintr-o listă dată este legat de un element în cealaltă listă. Natura acestui identificator va depinde de tipul de date pe care încercați să le potriviți. Veți avea nevoie de un identificator pentru fiecare listă de coloane.
- Pentru datele financiare asociate cu o perioadă dată, cum ar fi înregistrările fiscale, aceasta ar putea fi descrierea unui activ, data achiziționării activului sau ambele. În unele cazuri, o intrare poate fi identificată cu un număr de cod; totuși, dacă același sistem nu este utilizat pentru ambele liste, acest identificator poate crea potriviri acolo unde nu există sau ignora potriviri care ar trebui făcute.
- În unele cazuri, puteți prelua articole dintr-o listă și le puteți combina cu articole dintr-o altă listă pentru a crea un identificator, cum ar fi o descriere fizică a activului și anul în care a fost achiziționat. Pentru a crea un astfel de identificator, concatenați (adăugați, combinați) date din două sau mai multe celule folosind ampersand (&). Pentru a combina o descriere a articolului din celula F3 cu o dată din celula G3, separată printr-un spațiu, veți introduce formula „= F3 &„”& G3 'într-o altă celulă din acel rând, cum ar fi E3. Dacă doriți să includeți doar anul în identificator (deoarece o listă folosește date complete și cealaltă folosește doar ani), ați include funcția YEAR introducând '= F3 & "" & YEAR (G3)' în celula E3. (Nu includeți ghilimelele unice; acestea sunt acolo doar pentru a indica exemplul.)
- După ce ați creat formula, o puteți copia în toate celelalte celule ale coloanei de identificare selectând celula cu formula și glisând mânerul de umplere peste celelalte celule ale coloanei în care doriți să copiați formula. Când eliberați butonul mouse-ului, fiecare celulă pe care ați tras-o va fi populată cu formula, cu referințele de celulă ajustate la celulele corespunzătoare din același rând.
Pasul 3. Standardizați datele acolo unde este posibil
În timp ce mintea recunoaște că „Inc.” și „Încorporat” înseamnă același lucru, Excel nu face decât dacă îl formatați din nou un cuvânt sau altul. În mod similar, puteți considera valori precum 11, 950 și 11, 999,95 dolari ca fiind suficient de apropiate pentru a se potrivi, dar Excel nu o va face decât dacă vi-l spuneți.
- Puteți face față unor abrevieri, cum ar fi „Co” pentru „Companie” și „Inc” pentru „Incorporat utilizând funcția șir STÂNGA pentru a trunchia caracterele suplimentare. Alte abrevieri, cum ar fi„ Assn”pentru„ Asociație”, pot fi cel mai bine tratate prin stabilirea unui ghid de stil de introducere a datelor și apoi scrierea unui program pentru a căuta și a corecta formate necorespunzătoare.
- Pentru șiruri de numere, cum ar fi codurile poștale în care unele intrări includ sufixul ZIP + 4 și altele nu, puteți utiliza din nou funcția șir stânga pentru a recunoaște și a potrivi doar codurile poștale primare. Pentru ca Excel să recunoască valorile numerice apropiate, dar care nu sunt aceleași, puteți utiliza funcția ROUND pentru a rotunji valorile închise la același număr și a le potrivi.
- Spațiile suplimentare, cum ar fi tastarea a două spații între cuvinte în loc de unul, pot fi eliminate prin utilizarea funcției TRIM.
Pasul 4. Creați coloane pentru formula de comparație
Așa cum a trebuit să creați coloane pentru identificatorii listei, va trebui să creați coloane pentru formula care face comparația pentru dvs. Veți avea nevoie de o coloană pentru fiecare listă.
Veți dori să etichetați aceste coloane cu ceva de genul „Lipsești?”
Pasul 5. Introduceți formula de comparație în fiecare celulă
Pentru formula de comparație, veți utiliza funcția MATCH imbricată în altă funcție Excel, ISNA.
- Formula ia forma „= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))”, unde o celulă din coloana de identificare a primei liste este comparată cu fiecare dintre identificatorii din a doua listă cu vezi dacă se potrivește cu una dintre ele. Dacă nu se potrivește, lipsește o înregistrare, iar cuvântul „ADEVĂRAT” va fi afișat în acea celulă. Dacă se potrivește, înregistrarea este prezentă și va fi afișat cuvântul „FALS”. (La introducerea formulei, nu includeți ghilimele anexate.)
- Puteți copia formula în celulele rămase ale coloanei în același mod în care ați copiat formula de identificare a celulei. În acest caz, doar referința celulei pentru celula identificatoare se modifică, deoarece punerea semnelor de dolar în fața rândului și a referințelor coloanei pentru prima și ultima celulă din lista celui de-al doilea identificator de celulă le face referințe absolute.
- Puteți copia formula de comparație pentru prima listă în prima celulă a coloanei pentru a doua listă. Apoi va trebui să editați referințele celulei astfel încât „G3” să fie înlocuit cu referința pentru prima celulă de identificare din a doua listă și „$ L $ 3: $ L $ 14” să fie înlocuit cu prima și ultima celulă de identificare a a doua listă. (Lăsați semnele dolarului și punctele în pace.) Puteți copia apoi această formulă editată în celulele rămase în rândul de comparație din a doua listă.
Pasul 6. Sortați listele pentru a vedea mai ușor valorile care nu se potrivesc, dacă este necesar
Dacă listele dvs. sunt mari, poate fi necesar să le sortați pentru a pune împreună toate valorile care nu se potrivesc. Instrucțiunile din subsecțiunile de mai jos vor converti formulele în valori pentru a evita erorile de recalculare și, dacă listele dvs. sunt mari, va evita un timp de recalculare lung.
- Trageți mouse-ul peste toate celulele dintr-o listă pentru a-l selecta.
- Selectați Copiere din meniul Editare în Excel 2003 sau din grupul Clipboard al panglicii Acasă în Excel 2007 sau 2010.
- Selectați Lipire specială din meniul Editare din Excel 2003 sau din butonul drop-down Lipire din grupul Clipboard din panglica de pornire Excel 2007 sau 2010.
- Selectați „Valori” din lista Lipire ca din caseta de dialog Lipire specială. Faceți clic pe OK pentru a închide dialogul.
- Selectați Sortare din meniul Date în Excel 2003 sau grupul Sortare și filtrare a panglicii Date în Excel 2007 sau 2010.
- Selectați „Rând antet” din lista „Intervalul meu de date are” în dialogul Sortare după, selectați „Lipsești?” (sau numele pe care l-ați dat antetul coloanei de comparație) și faceți clic pe OK.
- Repetați acești pași pentru cealaltă listă.
Pasul 7. Comparați vizual articolele care nu se potrivesc pentru a vedea de ce nu se potrivesc
Așa cum s-a menționat anterior, Excel este conceput pentru a căuta potriviri exacte ale datelor, cu excepția cazului în care îl configurați pentru a căuta unele aproximative. Non-potrivirea dvs. ar putea fi la fel de simplă ca o transpunere accidentală de litere sau cifre. Ar putea fi, de asemenea, ceva care necesită o verificare independentă, cum ar fi verificarea dacă activele listate trebuie raportate în primul rând.
Metoda 2 din 2: Formatarea condiționată cu COUNTIF
Pasul 1. Copiați listele de date într-o singură foaie de lucru
Pasul 2. Decideți în ce listă doriți să evidențiați înregistrările care se potrivesc sau care nu se potrivesc
Dacă doriți să evidențiați înregistrările într-o singură listă, probabil că veți dori să evidențiați înregistrările unice pentru acea listă; adică înregistrări care nu se potrivesc cu înregistrările din cealaltă listă. Dacă doriți să evidențiați înregistrările din ambele liste, veți dori să evidențiați înregistrările care se potrivesc reciproc. În scopul acestui exemplu, vom presupune că prima listă ocupă celulele G3 până la G14 și a doua listă ocupă celulele L3 până la L14.
Pasul 3. Selectați elementele din lista în care doriți să evidențiați elemente unice sau potrivite
Dacă doriți să evidențiați elementele potrivite în ambele liste, va trebui să selectați listele pe rând și să aplicați formula de comparație (descrisă în pasul următor) fiecărei liste.
Pasul 4. Aplicați formula de comparație adecvată
Pentru a face acest lucru, va trebui să accesați dialogul Formatare condiționată din versiunea dvs. de Excel. În Excel 2003, faceți acest lucru selectând Formatare condițională din meniul Format, în timp ce în Excel 2007 și 2010, faceți clic pe butonul Formatare condiționată din grupul Stiluri al panglicii Acasă. Selectați tipul de regulă ca „Formula” și introduceți formula în câmpul Editați descrierea regulii.
- Dacă doriți să evidențiați înregistrările unice pentru prima listă, formula ar fi „= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)”, cu intervalul de celule din a doua listă redat ca valori absolute și referința la prima celulă a primei liste ca valoare relativă. (Nu introduceți ghilimele închise.)
- Dacă doriți să evidențiați înregistrările unice pentru a doua listă, formula ar fi „= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)”, cu intervalul de celule din prima listă redat ca valori absolute și referința la prima celulă a celei de-a doua liste ca valoare relativă. (Nu introduceți ghilimele închise.)
- Dacă doriți să evidențiați înregistrările din fiecare listă care se găsesc în cealaltă listă, veți avea nevoie de două formule, una pentru prima listă și una pentru a doua. Formula pentru prima listă este „= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)”, în timp ce formula pentru a doua listă este COUNTIF ($ G $ 3: $ G $ 14, L3> 0)”. menționat anterior, selectați prima listă pentru a-i aplica formula și apoi selectați a doua listă pentru a-i aplica formula.
- Aplicați orice formatare doriți pentru a evidenția înregistrările marcate. Faceți clic pe OK pentru a închide dialogul.
Video - Prin utilizarea acestui serviciu, unele informații pot fi partajate cu YouTube
sfaturi
- În loc să utilizați o referință de celulă cu metoda de formatare condițională COUNTIF, puteți introduce o valoare care să fie căutată și să marcați una sau mai multe liste pentru instanțele acelei valori.
- Pentru a simplifica formularele de comparație, puteți crea nume pentru lista dvs., cum ar fi „List1” și „List2”. Apoi, atunci când scrieți formulele, aceste nume de liste pot înlocui intervalele de celule absolute utilizate în exemplele de mai sus.
