Cum se pot găsi potrivirile de date în Excel

Printre numeroasele funcții ale programului Microsoft Excel, există una care vă permite să comparați două liste de date, să identificați potrivirile dintre aceste liste și, de asemenea, să identificați elementele care se găsesc numai în una dintre liste. Acest lucru este foarte util pentru compararea înregistrărilor financiare sau verificarea dacă un nume specific este într-o bază de date. Puteți utiliza funcția MATCH pentru a identifica și a marca înregistrările potrivite și necorespunzătoare sau pentru a utiliza funcția COUNTIF pentru a stabili un format condiționat. Următorii pași vă vor arăta cum să utilizați aceste funcții pentru a găsi potriviri între date.

conținut

Pași
Metoda 1identificați înregistrările prin intermediul funcției coincidir
Metoda 2condiționarea formatării prin funcția countif
Sfaturi

pași

Metoda 1
Identificați înregistrările prin intermediul funcției COINCIDIR

Imaginea intitulată Date de potrivire în Excel Pasul 1

Copiați listele de date într-o singură foaie de calcul. Excel poate lucra cu mai multe foi de calcul într-o singură carte sau cu mai multe cărți, dar veți vedea că este mai ușor să comparați listele dacă copiați toate informațiile într-o singură foaie de calcul.

Imaginea intitulată Date de potrivire în Excel Pasul 2

Furnizați un identificator unic pentru fiecare element din listă. Dacă cele două liste nu au o metodă comună de identificare, va trebui probabil să adăugați o coloană suplimentară în fiecare listă de date care identifică acel element în Excel. În acest fel puteți determina dacă elementul dintr-o listă dată este legat de un element al celeilalte liste. Caracteristicile acestui identificator vor depinde de tipul de date pe care îl veți compara. Veți avea nevoie de un identificator pentru fiecare listă de coloane.

Dacă aveți date financiare asociate cu o anumită perioadă, de exemplu, înregistrări fiscale, identificatorul ar putea fi descrierea activului, data la care activul a fost achiziționat sau ambele. În unele cazuri, intrările pot fi identificate cu un cod numeric. Cu toate acestea, dacă nu utilizați același sistem pentru ambele liste, acest identificator poate crea potriviri în care nu există sau ignoră potrivirile care există într-adevăr.

În unele cazuri, puteți prelua elemente dintr-o listă și le puteți combina cu elementele dintr-o altă listă pentru a crea un identificator, de exemplu, descrierea unui activ fizic și anul în care a fost achiziționat. Pentru a crea un astfel de identificator, este necesar să concatenăm (adăugați, combinăm) datele a două sau mai multe celule folosind simbolul ampersand (&). Pentru a combina descrierea unui element în celula F3 cu o dată în celula G3, separată de un spațiu, va trebui să introduceți formula `= F3& " "&G3 ". Dacă doriți să includeți numai anul în identificator (deoarece o listă utilizează date complete și cealaltă folosește numai ani), va trebui să includeți funcția YEAR introducând `= F3&" "&YEAR (G3) "în celula E3 (nu includeți citatele singulare - în acest caz ele sunt folosite doar pentru a indica un exemplu).

Odată ce ați creat formula, o puteți copia în toate celelalte celule din coloana de identificare. Pentru a face acest lucru, selectați celula în care este formulată și trageți cutia mică de umplere la celelalte celule din coloana în care doriți să copiați formula. Când eliberați butonul mouse-ului, fiecare celulă pe care ați tras caseta va fi completat automat cu formula și va avea referințele celulelor actualizate cu celulele care corespund acelui rând.

Imaginea intitulată Date de potrivire în Excel Pasul 3

Standardizați datele când este posibil. În timp ce mintea recunoaște asta "Inc" și "Societate cu răspundere limitată" ele înseamnă același lucru, Excel nu va ști dacă nu reformați una dintre cele două expresii. De asemenea, s-ar putea crede că valorile 11950 $ si $ 11,999.95 sunt destul de aproape de a considera că există o potrivire, dar Excel nu va vedea în acest fel, dacă nu-l spun.

Puteți avea grijă de abrevieri, cum ar fi de exemplu "co" pentru "companie" sau "org" pentru "organizație" utilizând funcția LEFT pentru a trunchia caracterele rămase. Dar există și alte abrevieri care nu pot fi trunchiate, cum ar fi "S.R.L." pentru "Societate cu Răspundere Limitată". În acest caz, trebuie să stabiliți un stil de introducere a datelor și să utilizați un program care caută și corectează formatele incorecte.

În lanțurile numerice, poate fi aplicată și funcția STÂNGA. De exemplu, dacă aveți coduri zip cu format "ZIP + 4" care includ un sufix după numărul și codurile poștale simple, utilizați STÂNGA pentru a recunoaște și a găsi meciul numai în prima parte a codului. Pentru ca Excel să recunoască valori numerice apropiate dar nu egale, puteți utiliza funcția ROUND. Cu această funcție puteți face ca valorile apropiate să fie rotunjite la același număr, astfel încât atunci când le comparați, există o coincidență între ele.

Spațiile care s-au terminat, de exemplu dacă există două spații între cuvinte în loc de unul, pot fi eliminate prin intermediul funcției CUT.

Imaginea intitulată Date de potrivire în Excel Pasul 4

Creați coloane pentru formula de comparație. Așa cum a trebuit să creați coloane pentru identificatorii listelor, acum va trebui să creați coloane pentru formula care va face comparația. Veți avea nevoie de o coloană pentru fiecare listă.

Etichetați aceste coloane cu un titlu care spune "Lipsesc înregistrare?" sau ceva similar.

Imaginea intitulată Date de potrivire în Excel Pasul 5

Introduceți formula de comparare în fiecare celulă. Pentru a face formula de comparație trebuie să utilizați funcția COINCID imbricată în cadrul unei alte funcții Excel numită ESNOD.

Formula ar trebui să pară similară "= ESNOD (MATCH (G3- $ L $ 3: $ L $ 14-FALSE))", unde o celulă este luată din coloana de identificatori din prima listă și comparată cu fiecare dintre identificatorii celei de-a doua liste pentru a vedea dacă există o potrivire cu oricare dintre ele. Dacă nu există nici o potrivire, înseamnă că lipsește înregistrarea și cuvântul "TRUE" va apărea în celulă. Dacă există o potrivire, înseamnă că înregistrarea este și cuvântul va apărea "FALS" (Când veți scrie formula, nu includeți citatele extremelor).

Puteți copia formula în celulele rămase ale coloanei în același mod în care ați copiat formula identificatorilor. În acest caz, se modifică numai referința celulei pentru identificator. Semnele greutăților din fața referinței rândului și coloanei primei și ultimei celule din lista de identificatori din a doua celulă sunt folosite pentru a transforma referințele în "referințe absolute".

Puteți copia formula de comparare a primei liste în prima celulă din coloana celei de-a doua liste. În acest caz, va trebui să editați referințele de înlocuire a celulelor "G3" prin referința primei celule de identificatori din a doua listă și "$ L $ 3: $ L $ 14" de către prima și ultima celulă de identificatori ai celei de-a doua liste (lasă semnele de greutăți și cele două puncte așa cum sunt). Acum puteți copia această formulă editată la restul celulelor din coloana de comparare a celei de-a doua liste.

Imaginea intitulată Date de potrivire în Excel Pasul 6

Sortați listele astfel încât să fie mai ușor să vedeți datele care nu se potrivesc. Dacă listele sunt mari, este mai bine să le ordonați, punând împreună toate datele care nu se potrivesc. Instrucțiunile pentru subactivitățile explicate mai jos servesc la transformarea formulelor în valori și, astfel, la evitarea erorilor atunci când se recalculează. Dacă listele sunt lungi, acest lucru va reduce și timpul de recalculare.

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

selecta "copie" din meniu "ediție" (Excel 2003) sau în grup "clipboard" a panglicii (Excel 2007 sau 2010).

selecta "Specială blocată" din meniu "ediție" (Excel 2003) sau în butonul derulant "pastă" a grupului "clipboard" (Excel 2007 sau 2010) "inițiere" în panglică.

selecta "valorile" în listă "Lipiți ca" din caseta de dialog "Specială blocată". Apoi faceți clic pe "accepta" pentru a închide caseta.

selecta "comandă" din meniu "date" (Excel 2003) sau în grup "Sortați și filtrați" din fila "date" în panglică (Excel 2007 sau 2010).

selecta "Rândul rândului" în listă "Intervalul meu de date a fost" din caseta de dialog "Sortați după". selecta "Lipsesc înregistrare?" (sau numele pe care l-ați ales drept antet pentru coloana de comparație) și faceți clic pe "accepta".

Repetați acești pași cu cealaltă listă.

Imaginea intitulată Date de meci în Excel Pasul 7

Comparați vizual articolele nepotrivite pentru a vedea de ce nu se potrivesc. După cum sa explicat mai sus, Excel este proiectat să caute potriviri exacte în date, cu excepția cazului în care îl setați să caute aproximări. "nici o coincidență" se poate datora unei greșeli simple de transpunere a literelor sau cifrelor. De asemenea, poate fi ceva care necesită o verificare independentă, cum ar fi verificarea datelor pentru a vedea dacă activele din listă ar trebui raportate în primul rând.

Metoda 2
Condiționarea formatării prin funcția COUNTIF

Imaginea intitulată Date de potrivire în Excel Pasul 8

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

Imaginea intitulată Date de potrivire în Excel Pasul 9

Decideți care listă doriți să evidențiați înregistrările care se potrivesc sau nu. Dacă doriți să evidențiați înregistrări numai într-o listă, este mai bine să evidențiați înregistrările unice din acea listă, adică cele care nu se potrivesc cu înregistrările din altă listă. Dacă doriți să evidențiați înregistrări ale celor două liste, este mai bine să evidențiați acelea care coincid cu celelalte liste. Pentru scopurile acestui exemplu, se va presupune că prima listă ocupă celulele G3 până la G14 și a doua ocupă celulele L3 până la L14.

Imaginea intitulată Date de potrivire în Excel Pasul 10

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 din cele două liste, va trebui să selectați listele unul câte unul și să aplicați formula de comparație (indicată în pasul următor) în fiecare dintre acestea.

Imaginea intitulată Date de potrivire în Excel Pasul 11

Aplicați formula de comparație corespunzătoare. Pentru a face acest lucru, va trebui să accesați masa "Formatul condiționat" din versiunea dvs. de Excel. În Excel 2003, deschideți-l selectat "Formatul condiționat" din meniu "format". În Excel 2007 și 2010, faceți clic pe buton "Formatul condiționat" a grupului "stiluri" din fila "inițiere" în panglică. selecta "formulă" ca tip regulat și introduceți formula în câmp "Editați o descriere a regulilor".

Dacă doriți să evidențiați înregistrările unice din prima listă, formula ar fi "= COUNTIF ($ L $ 3: $ L $ 14-G3 = 0)" cu intervalul de celule din a doua listă exprimat în valori absolute și referința la prima celulă din prima listă ca valoare relativă (nu includeți citatele din formula).

Dacă doriți să evidențiați înregistrările unice din a doua listă, formula ar fi "= COUNTIF ($ G $ 3: $ G $ 14-L3 = 0)" cu intervalul de celule din prima listă exprimat în valori absolute și referința la prima celulă din a doua listă ca valoare relativă (nu includeți citate în formula).

Dacă doriți să evidențiați înregistrările unei liste care se află în altă listă, veți avea nevoie de două formule, una pentru prima listă și una pentru al doilea. 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)". După cum sa explicat mai sus, trebuie să selectați prima listă pentru a aplica formula și apoi să selectați a doua pentru a aplica formula.

Aplicați formatul pe care doriți să evidențiați înregistrările pe care doriți să le marcați. Faceți clic pe "accepta" pentru a închide caseta de dialog.

sfaturi

În loc să utilizați o referință de celule cu funcția COUNTIF a metodei de formatare condițională, puteți introduce o valoare. Această valoare va fi căutată în liste și toate instanțele găsite vor fi evidențiate.
Pentru a simplifica formulele de comparare, puteți crea, de exemplu, un nume pentru fiecare listă "List1" și "Lista2". Apoi, când scrieți formula, numele listelor pot fi folosite pentru a înlocui intervalele de celule absolute ale exemplelor anterioare.

Distribuiți pe rețelele sociale:

înrudit