Cum se compara datele din Excel

Una dintre multele caracteristici ale Microsoft Excel este abilitatea de a compara două liste de date, identificarea meciurilor în liste și elementele care sunt prezente numai în unul dintre cele două. Acest lucru poate fi util pentru compararea înregistrărilor financiare sau verificarea dacă un nume specific este într-o bază de date. Puteți utiliza funcția COMPARE pentru a găsi și marca date cu sau fără potriviri, altfel utilizați formatarea condiționată cu funcția CONTASE. Următorii pași vă vor explica modul de utilizare a ambelor metode pentru a compara datele dvs.

conținut

Paşi
Metoda 1
Metoda 2
Sfaturi

paşi

Metoda 1

Identificați elementele egale cu funcția COMPARE

Copiați liste de date într-o singură foaie de calcul. Excel poate lucra pe mai multe foi de lucru în cadrul unuia sau mai multor seturi de coli, dar este mai ușor să compare două liste dacă sursele de date se află pe aceeași pagină.

Atribuiți un identificator unic pentru fiecare element al listelor. Dacă cele două liste nu au o metodă de identificare, este posibil să fie necesar să adăugați o coloană ambelor elemente care identifică elementele astfel încât Excel să le poată compara. Alegeți sistemul de identificare pe baza tipului de date pe care doriți să îl comparați. Trebuie să o utilizați pentru ambele liste.

Pentru datele financiare asociate cu o anumită perioadă de timp, de exemplu pentru declarația fiscală, puteți utiliza descrierea activelor, data la care activul a fost achiziționat sau ambele. În unele cazuri, puteți identifica elementele cu un code- Cu toate acestea, în cazul în care nu a folosit același sistem pentru ambele liste, această identificare poate crea meciuri false sau nu detectează elemente prezente în ambele liste.

În unele cazuri, puteți lua elemente dintr-o listă și le puteți combina cu cele ale celeilalte pentru a crea un sistem de identificare, cum ar fi descrierea unui activ fizic și anul cumpărării. Pentru a face acest lucru, concatenați (suma sau combinați) datele a două sau mai multe celule folosind &. Pentru a combina descrierea unui obiect în celula F3 cu data din celula G3, separate de un spațiu, utilizați formula = F3&" "&G3 într-o altă celulă din același rând, de exemplu E3. Dacă preferați să includeți numai anul în identificator (deoarece o listă utilizează date complete, iar cealaltă doar anii), puteți utiliza funcția YEAR tastând = F3&" "&ANUL (G3) în celula E3.

Odată ce formula este creată, puteți să o copiați în toate celelalte celule din coloana de identificare, selectând celula cu formula și glisând cursorul de umplere peste celelalte. Când butonul mouse-ului este eliberat, formula va fi copiată la toate celulele pe care le-ați selectat, cu referințele corecte bazate pe rând.

Când aveți șansa, urmați același standard de date. Deși este ușor pentru noi să înțelegem acest lucru "corporație" și "S.p.A." acestea sunt același lucru, Excel nu este capabil să facă același lucru dacă nu scrieți cei doi termeni în același mod. Din același motiv, ați putea crede că 11.950 și 11.999.95 sunt valori destul de similare pentru a fi considerate egale, dar nu vor fi pentru Excel dacă nu o exprimați în mod explicit.

Puteți gestiona unele abrevieri, cum ar fi "Soc" pentru "companie" și "Int" pentru "internațional" folosind funcția LEFT pentru a trunchia alte caractere. Este mai bine să gestionați celelalte abrevieri, cum ar fi "spa" pentru "corporație" stabilirea unui stil unic pentru formatarea datelor, apoi scrierea unui program pentru a căuta și a corecta erorile.

Pentru șiruri de numere care în unele cazuri au un sufix și nu, puteți utiliza din nou funcția LEFT pentru a recunoaște și compara numai primele cifre. Pentru a sorta valorile numerice similare, dar nu identice, puteți utiliza funcția ROUND pentru a aproxima numerele la cel mai apropiat număr întreg.

Puteți elimina spații suplimentare, cum ar fi un spațiu dublu între cuvinte, utilizând funcția CANCEL.

Creați coloane pentru formula de comparație. Așa cum a trebuit să le creați pentru identificatorii de listă, trebuie să faceți același lucru pentru formula care se ocupă de comparație. Adăugați o coloană după listă.

Puteți da titlul "Lipsește?" la coloane.

Introduceți formula de comparație în toate celulele. Veți folosi funcția COMPARISON în acel VAL.NON.DISP.

Formula are formatul = ISNA (MECI (G3, $ L $ 3: $ L $ 14, FALSE)), în care o celulă din coloana de identificare a primei liste este comparată cu toată coloana de identificare a doua listă , căutând meciuri. Dacă nu există nici o potrivire, elementul nu este prezent și cuvântul va apărea "TRUE" în celulă. Dacă este prezent, cuvântul va apărea "FALS".

Puteți copia formula în celulele rămase așa cum ați făcut pentru identificare. În acest caz, numai referința celulei de identificare se va schimba, deoarece semnul dolarului înainte de prima și ultima celulă din coloana de identificare a celei de-a doua liste le face referințe absolute.

Puteți copia formula de comparație pentru prima listă din prima celulă a coloanei pentru a doua listă. În acest moment, va fi suficient să schimbați referințele, pentru a le înlocui "G3" cu prima celulă din coloana de identificare a celei de-a doua liste e "$ L $ 3: $ L $ pe 14" cu prima și ultima celulă din coloana de identificare din prima listă (nu modificați simbolurile în dolari și colon.

Dacă este necesar, sortați listele pentru a afișa mai ușor valori de neegalat. Dacă listele conțin o mulțime de date, le puteți sorta astfel încât toate elementele de neegalat să fie grupate împreună. Instrucțiunile din pasul următor convertesc formulele în valori pentru a evita erorile de recalculare și, dacă listele sunt mari, reduceți timpii.

Glisați mouse-ul peste toate celulele pentru a le selecta.

Selectați Copiere din meniul Editare din Excel 2003 sau din grupul Clipboard din secțiunea Ribbon Home din Excel 2007 sau 2010.

Selectați Paste special din meniul Modificați din Excel 2003 sau din butonul Lipire din grupul Clipboard din secțiunea Ribbon Home din Excel 2007 sau 2010.

selecta "valorile" din lista de lipire ca în fereastra Paste specială. Faceți clic pe OK pentru a închide fereastra.

Selectați Sortare din meniul Date din Excel 2003 sau din grupul Sortare și filtrare din secțiunea Ribbon Data din Excel 2007 sau 2010.

selecta "Header line" din "Intervalul meu de date a fost" în fereastra Sortare după, selectați "Lipsește?" (sau numele atribuit coloanei de comparație), apoi faceți clic pe OK.

Repetați aceiași pași pentru cealaltă listă.

Comparați articolele de neegalat vizual pentru a afla de ce nu apar pe ambele liste. Așa cum am menționat mai devreme, Excel este proiectat să caute potriviri exacte dacă nu ordinele de aproximare a căutării. Nepotrivire ar putea fi pur și simplu din cauza unei tipografii sau a unui motiv independent, cum ar fi un bun cadou pe o listă, dar care să nu fie declarat în cealaltă.

Metoda 2

Condiționarea formatării cu CONTASE

Copiați listele de date într-o singură foaie de calcul.

Decideți în ce listă să evidențiați elemente cu sau fără corespondență. Dacă doriți să o faceți într-o singură listă, probabil că doriți să găsiți înregistrările numai în acea listă. Dacă doriți să evidențiați intrările din cele două liste, căutați cele care apar în ambele. Pentru exemplul nostru, vom considera că prima listă merge de la celula G3 la G14, iar cea de-a doua de la L3 la L14.

Selectați elementele din listă în care doriți să evidențiați elemente unice sau potrivite. Dacă doriți să evidențiați elementele din cele două liste, trebuie să le selectați una câte una și să aplicați formula de comparare fiecăruia (descrisă în pasul următor).

Aplicați formula de comparație adecvată. Pentru a face acest lucru, trebuie să deschideți fereastra de formatare condiționată a versiunii dvs. Excel. În Excel 2003, puteți face acest lucru selectând Formatare condiționată din meniul Format, în timp ce în Excel 2007 și 2010, puteți face clic pe butonul Condiționare Formatare din grupul Stiluri din secțiunea Acasă a panglicii. Selectați tipul de regulă, cum ar fi "formulă" și introduceți formula în câmpul Editați descrierea regulilor.

Dacă doriți să evidențiați elementele prezente doar în prima listă, formula este = COUNTIF ($ L $ 3: $ L $ 14, G3 = 0), cu gama de celule din a doua listă introdusă ca valoare absolută, în timp ce referința la prima celulă din prima listă este relativă.

Dacă doriți să evidențiați elementele prezente numai în a doua listă, formula este = COUNTIF ($ G $ 3: $ G 14 $, L3 = 0), cu gama de celule din prima listă introdus ca valoare absolută, în timp ce referința la prima celulă din a doua listă este relativă.

Dacă doriți să evidențiați elementele care apar în ambele liste, aveți nevoie de două formule, una pentru prima listă și una pentru a doua. Prima este = CONTINUE ($ L $ 3: $ L $ 14, G3>0), în timp ce al doilea este CONTINUAT ($ G $ 3: $ G $ 14, L3>0). Așa cum am menționat mai devreme, trebuie să selectați prima listă pentru a aplica formula sa, apoi faceți același lucru cu al doilea.

Aplicați formatul care vă permite să evidențiați celulele care vă interesează. Faceți clic pe OK pentru a închide fereastra.

Sfaturi

În loc să utilizați o referință de celule cu metoda de formatare condițională CONTASE, puteți introduce o valoare pentru a căuta și raporta una sau mai multe liste de fiecare dată când apare.
Pentru a simplifica formulele de comparare, puteți atribui nume listelor, cum ar fi "List1" și "Lista2". În acest fel, puteți înlocui numele în intervale de celule absolute în formule.

Distribuiți pe rețelele sociale:

înrudit