Un program pentru compararea conținutului unui fișier Excel. Cum se compară două coloane în Excel pentru potriviri

Adesea sarcina este de a compara două liste de elemente. A face acest lucru manual este prea obositor și, de asemenea, nu poate fi exclusă posibilitatea apariției unor erori. Excel face această operație ușoară. Acest sfat descrie o metodă care utilizează formatarea condiționată.

În fig. Figura 164.1 prezintă un exemplu de două liste de nume cu mai multe coloane. Folosirea formatării condiționate poate face evidente diferențele dintre liste. Aceste exemple de listă conțin text, dar metoda în cauză funcționează și cu date numerice.

Prima listă este A2:B31, acest interval este numit Lista veche. A doua listă este D2:E31, intervalul este numit NewList. Intervalele au fost denumite folosind comanda Formule Nume definite Atribuiți un nume. Nu este necesar să denumești intervalele, dar face lucrul cu ele mai ușor.

Să începem prin a adăuga formatare condiționată la vechea listă.

1. Selectați celule dintr-un interval Lista veche.
2. Selecta.
3. În fereastră Creați o regulă de formatare selectați elementul numit Utilizați formula
4. Introduceți această formulă în câmpul ferestrei (Fig. 164.2): =COUNTIF(NewList;A2)=0.
5. Faceți clic pe butonul Formatși specificați formatarea care va fi aplicată atunci când condiția este adevărată. Cel mai bine este să alegeți diferite culori de umplere.
6. Faceți clic pe OK.

Celulele din gamă NewList utilizați o formulă de formatare condiționată similară.

1. Selectați celule dintr-un interval NewList.
2. Selecta Acasă Formatare condiționată Creați o regulă pentru a deschide o casetă de dialog Creați o regulă de formatare.
3. În fereastră Creați o regulă formatarea elementului selectat Utilizați formula pentru a defini celulele de formatat.
4. Introduceți această formulă în câmpul ferestrei: =COUNTIF(OldList;D2)=0 .
5. Faceți clic pe butonul Formatși setați formatarea care urmează să fie aplicată atunci când condiția este adevărată (culoare de umplere diferită).
6. Faceți clic pe OK.

Ca urmare, vor fi evidențiate numele care se află în lista veche, dar nu și în cea nouă (Fig. 164.3). În plus, sunt evidențiate și nume din noua listă care nu sunt în vechea listă, dar într-o culoare diferită. Numele care apar în ambele liste nu sunt evidențiate.

Ambele formule de formatare condiționată folosesc funcția COUNTIF. Acesta calculează de câte ori apare o anumită valoare într-un interval. Dacă formula returnează 0, înseamnă că elementul nu se află în interval. În acest fel, formatarea condiționată preia și culoarea de fundal a celulei se schimbă.

Uneori doriți să vizualizați numai acele înregistrări dintr-un tabel Access care se potrivesc cu înregistrările dintr-un alt tabel care conțin câmpuri cu date care se potrivesc. De exemplu, este posibil să doriți să examinați evidențele angajaților care au procesat cel puțin o comandă pentru a determina care dintre ei merită o promovare. Sau poate fi necesar să revizuiți informațiile de contact pentru clienții care locuiesc în același oraș cu angajatul pentru a aranja o întâlnire față în față.

Dacă trebuie să comparați două tabele Access și să găsiți date care se potrivesc, există două opțiuni.

Creați o interogare care unește câmpuri din fiecare tabel care conțin date care se potrivesc, folosind o relație existentă sau o îmbinare pe care ați creat-o pentru interogare. Această metodă este optimă în ceea ce privește viteza de returnare a rezultatelor interogării, dar nu permite combinarea câmpurilor cu date de diferite tipuri.

Creați o interogare de comparare a câmpurilor care utilizează un câmp ca condiție pentru altul. Această metodă durează de obicei mai mult, deoarece o îmbinare elimină rânduri din rezultatele interogării înainte ca tabelele de bază să fie citite, în timp ce condițiile sunt aplicate rezultatelor interogării după ce aceste tabele sunt citite. Dar câmpul poate fi folosit ca o condiție pentru compararea câmpurilor cu date de diferite tipuri, ceea ce nu se poate face atunci când se utilizează uniuni.

Acest articol discută compararea a două tabele pentru a identifica datele care se potrivesc și oferă date eșantion care pot fi utilizate în procedurile de eșantion.

În acest articol

Compararea a două tabele folosind îmbinări

Pentru a compara două tabele folosind îmbinări, trebuie să creați o interogare de selectare care să includă ambele tabele. Dacă nu există încă o relație între tabele pe baza câmpurilor care conțin datele necesare, trebuie să creați îmbinări pe acestea. Puteți crea câte unități doriți, dar fiecare pereche de câmpuri îmbinate trebuie să conțină același tip de date sau compatibil.

Să presupunem că lucrezi la o universitate și vrei să știi cum au afectat notele studenților modificările recente aduse curriculum-ului de matematică. În special, sunteți interesat de notele acelor elevi a căror materie principală este matematica. Aveți deja un tabel care conține date despre materiile principale și un tabel care conține date despre studenții care le studiază. Datele despre note sunt stocate în tabelul „Elevi”, iar datele despre materiile majore sunt stocate în tabelul „Specializări”. Pentru a vedea cum s-au schimbat notele la matematică de la modificările recente ale curriculumului, va trebui să vă uitați la intrările din tabelul Studenți care corespund intrărilor din tabelul Majors.

Pregătirea probelor de date

În acest exemplu, creați o interogare care determină modul în care modificările recente aduse curriculum-ului de matematică au afectat notele elevilor la cursul de matematică. Utilizați cele două tabele de mai jos: Specializări și Studenți. Adăugați-le în baza de date.

Access oferă mai multe moduri de a adăuga aceste tabele la o bază de date exemplu. Puteți introduce datele manual, copiați fiecare tabel într-un program de foi de calcul (cum ar fi Microsoft Office Excel 2007) și apoi importați foile în Access sau puteți lipi datele într-un editor de text, cum ar fi Notepad și apoi importați datele din fișierele text rezultate.

Instrucțiunile pas cu pas din această secțiune explică cum să introduceți manual datele într-o foaie de lucru goală și cum să copiați exemple de tabele în Excel și apoi să le importați în Access.

Specializări

Elevii

Dacă intenționați să introduceți date exemplu într-o foaie de calcul, puteți.

Introducerea manuală a datelor exemplu

Dacă nu sunteți interesat să creați o foaie de lucru bazată pe date eșantion, săriți peste secțiunea următoare (Crearea foilor de lucru cu date mostre).

Crearea mostrelor de fișe de date

Crearea tabelelor bazei de date din foi de calcul

Comparați exemple de tabele și găsiți înregistrări care se potrivesc folosind îmbinări

Acum sunteți gata să comparați tabelele Studenți și Specializări. Deoarece relațiile dintre cele două tabele nu sunt definite, trebuie să creați îmbinări pe câmpurile corespunzătoare din interogare. Tabelele conțin mai multe câmpuri și va trebui să creați o unire pentru fiecare pereche de câmpuri comune: ID student, An și Curriculum (în tabelul Studenți) și Major (în tabelul Specializări). În acest caz, ne interesează doar matematica, așa că putem limita rezultatele interogării folosind o condiție de câmp.

Pe fila Creare faceți clic pe butonul Generator de interogări.

În caseta de dialog Adăugarea unui tabel Faceți dublu clic pe tabelul care conține înregistrările dorite ( Elevii), apoi faceți dublu clic pe tabelul cu care îl comparați ( Specializări).

Închideți caseta de dialog Adăugarea unui tabel.

Trageți câmpul Cod student de la masă Eleviiîn câmp Cod student mesele Specializări. În formularul de interogare va apărea o linie între cele două tabele, indicând faptul că a fost creată o îmbinare. Faceți dublu clic pe linie pentru a deschide caseta de dialog Opțiuni de îmbinare.

Observați cele trei opțiuni din caseta de dialog Opțiuni de îmbinare. În mod implicit, opțiunea 1 este selectată. În unele cazuri, trebuie să adăugați rânduri suplimentare din același tabel la parametrii de unire. Deoarece doriți să găsiți doar date care se potrivesc, lăsați valoarea de unire la 1. Închideți caseta de dialog Opțiuni de îmbinare prin apasarea butonului Anula.

Trebuie să creați încă două îmbinări. Pentru a face acest lucru, trageți câmpul An de la masă Eleviiîn câmp An mesele Specializări iar apoi câmpul Programă de la masă Eleviiîn câmp Specializare mesele Specializări.

În tabel Elevii faceți dublu clic pe stea ( * ) pentru a adăuga toate câmpurile din tabel la formularul de solicitare.

Nota: Elevi.*.

În tabel Specializări dublu clic pe câmp Specializare pentru a-l adăuga la formular.

Spectacol coloană Specializare.

În linie Condiție de selecție coloană Specializare intra MATEMATICĂ.

Pe fila Constructorîn grup Rezultate faceți clic pe butonul Executa.

Compararea a două tabele folosind un câmp ca condiție

Uneori trebuie să comparați tabele pe baza câmpurilor care au aceleași valori, dar tipuri de date diferite. De exemplu, un câmp dintr-un tabel poate avea un tip de date numerice, dar trebuie să-l comparați cu un câmp dintr-un alt tabel care are un tip de date text. Câmpurile care conțin date similare de diferite tipuri pot apărea atunci când salvați numere ca text (de exemplu, când importați date dintr-un alt program). Deoarece nu este posibil să creați îmbinări între câmpuri cu diferite tipuri de date, va trebui să utilizați o altă metodă. Pentru a compara două câmpuri cu tipuri diferite de date, puteți utiliza un câmp ca condiție pentru celălalt.

Să presupunem că lucrezi la o universitate și vrei să știi cum au afectat notele studenților modificările recente aduse curriculum-ului de matematică. În special, sunteți interesat de notele acelor elevi a căror materie principală este matematica. Aveți deja tabelele „Specializări” și „Studenți”. Datele despre note sunt stocate în tabelul „Elevi”, iar datele despre materiile majore sunt stocate în tabelul „Specializări”. Pentru a vedea cum s-au schimbat notele la matematică, trebuie să vă uitați la intrările din tabelul Studenți care corespund intrărilor din tabelul Specializări. Cu toate acestea, unul dintre câmpurile pe care doriți să-l utilizați pentru a compara tabele are un tip de date diferit de câmpul cu care este asociat.

Pentru a compara două tabele folosind un câmp ca condiție, trebuie să creați o interogare de selectare care să includă ambele tabele. Includeți în interogarea dvs. câmpurile pe care doriți să le afișați, precum și câmpul corespunzător câmpului care va fi folosit ca condiție. Apoi creați o condiție pentru a compara tabelele. Puteți crea câte condiții este necesar pentru a compara câmpurile.

Pentru a ilustra această metodă, vom folosi , dar în câmpul Student ID din tabelul Specializări, vom schimba tipul de date din numeric în text. Deoarece nu putem crea o uniune a două câmpuri cu tipuri diferite de date, va trebui să comparăm cele două câmpuri Student ID folosind un câmp ca condiție pentru celălalt.

Modificarea tipului de date în câmpul ID student din tabelul Specializări

Deschideți baza de date în care ați salvat exemplele de tabele.

ÎN zonele de navigație faceți clic dreapta pe tabelul Specializări și selectați Constructor.

Tabelul Specializări se deschide în modul proiectare.

În coloană Tip de date schimbare pentru câmp Cod student tip de date Număr pe Text.

Închideți tabelul „Specializări”. Faceți clic pe butonul Da când vi se solicită salvarea modificărilor.

Comparați exemple de tabele și găsiți înregistrări care se potrivesc folosind o condiție de câmp

Următoarele arată cum să comparați două câmpuri ID student folosind un câmp din tabelul Studenți ca o condiție pentru un câmp din tabelul Specializări. Folosind un cuvânt cheie Ca puteți compara două câmpuri chiar dacă acestea conțin tipuri diferite de date.

Pe fila Creaîn grup Alte faceți clic pe butonul Generator de interogări.

În caseta de dialog Adăugarea unui tabel dublu clic pe tabel Elevii iar apoi masa Specializări.

Închideți caseta de dialog Adăugarea unui tabel.

Trageți câmpul An de la masă Eleviiîn câmp An mesele Specializări iar apoi câmpul Programă de la masă Eleviiîn câmp Specializare mesele Specializări. Aceste câmpuri conțin același tip de date, așa că puteți utiliza uniuni pentru a le compara. Pentru a compara câmpuri de același tip de date, se recomandă utilizarea uniunilor.

Faceți dublu clic pe stea ( * ) în tabel Elevii pentru a adăuga toate câmpurile tabelului la formularul de solicitare.

Nota: Când utilizați un asterisc pentru a adăuga toate câmpurile dintr-un formular, apare o singură coloană. Acest nume de coloană include numele tabelului urmat de un punct (.) și un asterisc (*). În acest exemplu, coloana este numită Elevi.*.

În tabel Specializări dublu clic pe câmp Cod student pentru a-l adăuga la formular.

În formularul de solicitare, debifați caseta din rând Spectacol coloană Cod student. În linie Condiție de selecție coloană Cod student intra Ca [Students].[Cod student].

În tabel Specializări dublu clic pe câmp Specializare pentru a-l adăuga la formular.

În formularul de solicitare, debifați caseta din rând Spectacol coloană Specializare. În linie Condiție de selecție intra MATEMATICĂ.

Pe fila Constructorîn grup Rezultate faceți clic pe butonul Executa.

Solicitarea este executată și sunt afișate notele la matematică doar ale acelor elevi a căror specializare este această materie.

Să presupunem că doriți să comparați versiunile unui registru de lucru, să analizați un registru de lucru pentru probleme sau inconsecvențe sau să vedeți legături între registrele de lucru sau foile de lucru. Dacă pe computer este instalat Microsoft Office 365 sau Office Professional Plus 2013, programul de completare Spreadsheet Inquire este disponibil în Excel.

Puteți folosi comenzile din fila Întrebare pentru a face toate aceste sarcini și multe altele. Fila Întrebare din panglica Excel are butoane pentru comenzile descrise mai jos.

Dacă nu vezi Întreba fila din panglica Excel, consultați Activarea programului de completare Spreadsheet Inquire .

Comparați două registre de lucru

The Comparați fișiere comanda vă permite să vedeți diferențele, celulă cu celulă, dintre două registre de lucru. Trebuie să aveți două registre de lucru deschise în Excel pentru a rula această comandă.

Rezultatele sunt codificate cu culori în funcție de tipul de conținut, cum ar fi valorile introduse, formulele, intervalele denumite și formatele. Există chiar și o fereastră care poate afișa modificările codului VBA linie cu linie. Diferențele dintre celule sunt afișate într-un aspect al grilei ușor de citit, ca acesta:

The Comparați fișiere comanda utilizează Microsoft Spreadsheet Compare pentru a compara cele două fișiere. În Windows 8, puteți porni Compararea foii de calcul în afara Excel făcând clic Comparare foaie de calcul pe Aplicații ecran. În Windows 7, faceți clic pe Windows Început butonul și apoi > Toate Programele > Microsoft Office 2013 > Instrumente Office 2013 > Comparație cu foi de calcul 2013.

Pentru a afla mai multe despre Compararea și compararea fișierelor cu foi de calcul, citiți Comparați două versiuni ale unui registru de lucru.

Analizați un registru de lucru

The Analiza caietului de lucru comanda creează un raport interactiv care arată informații detaliate despre registrul de lucru și structura acestuia, formule, celule, intervale și avertismente. Imaginea de aici arată un registru de lucru foarte simplu care conține două formule și conexiuni de date la o bază de date Access și un fișier text.

Afișați link-uri la cărți de lucru

Registrele de lucru conectate la alte registre de lucru prin referințe la celule pot deveni confuze. Utilizați pentru a crea o hartă grafică interactivă a dependențelor caietului de lucru create de conexiuni (legături) între fișiere. Tipurile de legături din diagramă pot include alte registre de lucru, baze de date Access, fișiere text, pagini HTML, baze de date SQL Server și alte surse de date. În diagrama de relații, puteți selecta elemente și găsi mai multe informații despre ele și puteți trage linii de conexiune pentru a schimba forma diagramei.

Această diagramă arată registrul de lucru curent din stânga și conexiunile dintre acesta și alte registre de lucru și surse de date. De asemenea, arată niveluri suplimentare de conexiuni la registrul de lucru, oferindu-vă o imagine a originilor datelor pentru registrul de lucru.

Afișați link-urile la foile de lucru

Aveți o mulțime de fișe de lucru care depind una de cealaltă? Utilizați pentru a crea o hartă grafică interactivă a conexiunilor (legăturilor) între foile de lucru, atât în ​​același registru de lucru, cât și în alte registre de lucru. Acest lucru vă ajută să vă oferiți o imagine mai clară a modului în care datele dvs. pot depinde de celulele din alte locuri.

Această diagramă arată relațiile dintre foile de lucru din patru registre de lucru diferite, cu dependențe între foile de lucru din același registru de lucru, precum și legături între foile de lucru din registre de lucru diferite. Când poziționați indicatorul peste un nod din diagramă, cum ar fi foaia de lucru numită „Vest” în diagramă, apare un balon care conține informații.

Afișați relațiile celulare

Pentru a obține o diagramă detaliată, interactivă a tuturor legăturilor de la o celulă selectată la celulele din alte foi de lucru sau chiar din alte registre de lucru, utilizați Relația celulară instrument. Aceste relații cu alte celule pot exista în formule sau referințe la intervale denumite. Diagrama poate traversa foi de lucru și registre de lucru.

Această diagramă arată două niveluri de relații de celule pentru celula A10 din Sheet5 din Book1.xlsx. Această celulă depinde de celula C6 din foaia 1 dintr-un alt registru de lucru, Book2.xlsx. Această celulă este un precedent pentru mai multe celule din alte foi de lucru din același fișier.

Pentru a afla mai multe despre vizualizarea relațiilor dintre celule, citiți Vezi legăturile dintre celule.

Curățați formatarea celulelor în exces

Ați deschis vreodată un registru de lucru și găsiți că se încarcă lent sau a devenit uriaș? Este posibil să aibă formatare aplicată rândurilor sau coloanelor de care nu știți Curățați formatarea celulelor în exces comandă pentru a elimina formatarea în exces și a reduce foarte mult dimensiunea fișierului. Acest lucru vă ajută să evitați „balonarea foii de calcul”, ceea ce îmbunătățește viteza Excel.

Gestionați parolele

Dacă utilizați funcțiile Inquire pentru a analiza sau compara registrele de lucru care sunt protejate cu parolă, va trebui să adăugați parola registrului de lucru la lista de parole, astfel încât Inquire să poată deschide copia salvată a registrului de lucru. Utilizați Parolele registrului de lucru comanda pe Întreba pentru a adăuga parole, care vor fi salvate pe computer. Aceste parole sunt criptate și accesibile numai de dvs.

În fiecare lună, angajatul HR primește o listă cu angajații împreună cu salariile acestora. Copiază lista într-o foaie nouă din registrul de lucru Excel. Sarcina este următoarea: comparați salariile angajaților, care s-au modificat față de luna precedentă. Pentru a face acest lucru, trebuie să comparați datele în Excel pe diferite foi. Să folosim formatarea condiționată. În acest fel, nu numai că vom găsi automat toate diferențele dintre valorile celulelor, dar și le vom evidenția cu culoare.

Compararea a două foi în Excel

O companie poate avea mai mult de o sută de angajați, dintre care unii renunță, alții obțin locuri de muncă, alții pleacă în concediu sau concediu medical etc. Ca urmare, poate fi dificil să comparați datele salariale. De exemplu, numele de familie ale angajaților vor fi întotdeauna în secvențe diferite. Cum se compară două tabele Excel pe foi diferite?

Formatarea condiționată ne va ajuta să rezolvăm această problemă dificilă. De exemplu, să luăm date pentru februarie și martie, așa cum se arată în figură:

Pentru a găsi modificările pe borderourile de salariu:

După ce a introdus toate condițiile de formatare, Excel a evidențiat automat cu culoare acei angajați ale căror salarii s-au modificat față de luna precedentă.

Principiul comparării a două intervale de date în Excel pe foi diferite:

În anumite condiții, funcția MATCH este esențială. Primul său argument conține o pereche de valori care ar trebui găsite în foaia sursă a lunii următoare, adică „Martie”. Un interval de navigare este definit ca o pereche de valori ale intervalului definite prin nume. În acest fel, șirurile sunt comparate pe baza a două caracteristici: numele de familie și salariul. Pentru potrivirile găsite, este returnat un număr, ceea ce este în esență adevărat pentru Excel. Prin urmare, ar trebui să utilizați funcția =NOT(), care vă permite să înlocuiți valoarea TRUE cu FALSE. În caz contrar, formatarea va fi aplicată celulelor ale căror valori se potrivesc. Pentru fiecare pereche de valori care nu este găsită (adică o nepotrivire) &B2&$C2 în intervalul LastName&Salary, funcția MATCH returnează o eroare. Valoarea de eroare nu este o valoare booleană. Prin urmare, folosim funcția IFERROR, care va atribui o valoare logică pentru fiecare eroare - TRUE. Acest lucru facilitează alocarea unui nou format numai pentru celulele fără valori salariale potrivite în raport cu luna următoare - martie.

Dacă alți utilizatori au permisiunea de a vă edita registrul de lucru, atunci după ce îl deschideți este posibil să aveți întrebări: „Cine l-a schimbat și ce s-a schimbat exact?” Microsoft Spreadsheet Compare vă poate ajuta să răspundeți la aceste întrebări prin găsirea modificărilor și evidențierea acestora.

Important: Instrumentul de comparare a foilor de calcul este disponibil numai cu Office Professional Plus 2013 și Office 365 ProPlus.

Rezultatele comparației sunt afișate într-un tabel format din două părți. Cartea din partea stângă corespunde fișierului specificat în câmpul „Comparare”, iar cartea din partea dreaptă corespunde fișierului specificat în câmpul „Către”. Detaliile apar în zona de sub cele două părți ale tabelului. Modificările sunt evidențiate în culori diferite în funcție de tipul lor.

Interpretarea rezultatelor

Alte moduri de a lucra cu rezultatele comparației

Dacă doriți să salvați rezultatele sau să le analizați într-o altă aplicație, exportați-le într-un fișier Excel sau copiați și lipiți-le într-un alt program, cum ar fi Microsoft Word. De asemenea, puteți obține o reprezentare mai precisă a fiecărei foi de lucru, arătând formatarea celulelor mai aproape de ceea ce vedeți în Excel.

Puteți exporta rezultatele într-un fișier Excel care este mai ușor de citit. Selecta Acasă > Export rezultate(Acasă > Exportați rezultate).

Pentru a copia rezultatele și a le lipi într-un alt program, selectați Acasă > Copiați rezultatele în Clipboard(Acasă > Copiați rezultatele în clipboard).

Pentru a afișa formatarea celulei dintr-un registru de lucru, selectați Acasă > Afișați culorile caietului de lucru(Acasă > Afișați culorile cărții).

Alte motive pentru a compara cărți

Să presupunem că organizația dvs. așteaptă un audit. Trebuie să urmăriți datele din cărțile importante care arată schimbări lună de lună și an de an. Acest lucru vă va ajuta să găsiți și să remediați erorile înainte ca recenzenții să ajungă la ele.

Puteți utiliza instrumentul de comparare a foilor de calcul nu numai pentru a compara conținutul foilor, ci și pentru a găsi diferențe în codul Visual Basic pentru aplicații (VBA). Rezultatele sunt afișate într-o fereastră, astfel încât diferențele pot fi vizualizate una lângă alta.