Compararea datelor Excel. Cum se compară două coloane în Excel pentru potriviri

Acest articol vă va dura aproximativ 10 minute să citiți. În următoarele 5 minute, puteți compara cu ușurință două coloane în Excel și puteți afla dacă există duplicate în ele, le puteți șterge sau le puteți evidenția cu culoare. Deci a sosit timpul!

Excel este o aplicație foarte puternică și foarte interesantă pentru crearea și manipularea unor cantități mari de date. Dacă aveți mai multe registre de lucru cu date (sau doar un tabel imens), atunci probabil că doriți să comparați 2 coloane, să găsiți valori duplicat și apoi să faceți o acțiune cu ele, de exemplu, să ștergeți, să evidențiați sau să ștergeți conținutul ... Coloanele pot fi în același tabel, pot fi adiacente sau nu adiacente, pot fi amplasate pe 2 foi diferite sau chiar în cărți diferite.

Imaginați-vă că avem 2 coloane cu nume de persoane - 5 nume într-o coloană Ași 3 nume într-o coloană B... Este necesar să comparați numele din aceste două coloane și să găsiți duplicate. După cum ați înțeles, acestea sunt date fictive, luate doar ca exemplu. În tabele reale, avem de-a face cu mii, dacă nu cu zeci de mii de înregistrări.

Opțiunea A: ambele coloane sunt pe aceeași foaie. De exemplu, coloana Ași coloană B.

Opțiunea B: Coloanele sunt amplasate pe diferite foi. De exemplu, coloana A pe foaie Foaia2și coloană A pe foaie Foaia 3.

Excel 2013, 2010 și 2007 au un instrument încorporat Eliminați duplicatul(Eliminați duplicatele), dar este neputincios într-o astfel de situație, deoarece nu poate compara datele din 2 coloane. Mai mult, poate elimina doar duplicatele. Nu există alte opțiuni precum evidențierea sau schimbarea culorilor. Și punctul!

Comparați 2 coloane în Excel și găsiți înregistrări duplicate folosind formule

Opțiunea A: ambele coloane sunt pe aceeași foaie

Cheie:În foile de calcul mari, copierea unei formule va fi mai rapidă dacă utilizați comenzile rapide de la tastatură. Selectați o celulă C1și apăsați Ctrl + C(pentru a copia formula în clipboard), apoi apăsați Ctrl + Shift + End(pentru a selecta toate celulele necompletate din coloana C) și apoi apăsați Ctrl + V(pentru a insera formula în toate celulele selectate).

Opțiunea B: cele două coloane sunt pe foi diferite (în cărți diferite)

Se procesează duplicatele găsite

Minunat, am găsit intrări în prima coloană care sunt prezente și în a doua coloană. Acum trebuie să facem ceva cu ei. Vizualizarea manuală a tuturor înregistrărilor duplicate într-un tabel este destul de ineficientă și consumă mult timp. Există modalități mai bune.

Afișați numai rândurile duplicate în coloana A

Dacă coloanele dvs. nu au anteturi, atunci trebuie să le adăugați. Pentru aceasta, plasați cursorul pe numărul care reprezintă prima linie și se va transforma într-o săgeată neagră, așa cum se arată în figura de mai jos:

Faceți clic dreapta și selectați Introduce(Introduce):

Dați nume de coloane, de exemplu, „ Nume" și " Duplicat?”Apoi deschideți fila Date(Date) și apăsați Filtru(Filtru):

Apoi faceți clic pe săgeata cenușie de lângă „ Duplicat?„Pentru a extinde meniul de filtrare; debifați toate articolele din această listă, cu excepția Duplicat, și apăsați Bine.

Atât, acum vedeți doar acele elemente ale coloanei DAR care sunt duplicate în coloană ÎN... Există doar două astfel de celule în tabelul nostru de antrenament, dar, după cum ați înțeles, în practică există multe altele.

Pentru a afișa din nou toate rândurile unei coloane DAR, faceți clic pe simbolul filtrului din coloană ÎN care acum arată ca o pâlnie cu o săgeată mică și selectați Selectează tot(Selectează tot). Alternativ, puteți face același lucru prin Feed făcând clic Date(Date)> Selectați și filtrați(Sortare și filtrare)> clar(Ștergeți) așa cum se arată în captura de ecran de mai jos:

Schimbați culoarea sau evidențiați duplicatele găsite

Dacă semnele „ Duplicat”Nu este suficient pentru scopurile dvs. și doriți să marcați celulele care se repetă cu o altă culoare a fontului, culoare de umplere sau în alt mod ...

În acest caz, filtrați duplicatele așa cum se arată mai sus, selectați toate celulele filtrate și faceți clic Ctrl + 1 pentru a deschide dialogul Celule de format(Formatul celulei). De exemplu, să schimbăm culoarea de umplere a celulelor din liniile duplicate în galben strălucitor. Desigur, puteți schimba culoarea de umplere cu instrumentul Completati(Culoare umplere) de pe filă Acasă(Acasă), dar avantajul casetei de dialog Celule de format(Format celulă) este că puteți configura toate opțiunile de formatare în același timp.

Acum, cu siguranță nu veți pierde o singură celulă cu duplicate:

Eliminarea valorilor duplicate din prima coloană

Filtrați tabelul astfel încât să fie afișate numai celulele cu valori duplicate și selectați acele celule.

Dacă cele 2 coloane pe care le comparați sunt pe foi diferite, adică în diferite tabele, faceți clic dreapta pe intervalul selectat și selectați Ștergeți rândul(Ștergeți linia):

Click pe Bine când Excel vă cere să confirmați că doriți cu adevărat să ștergeți întregul rând al foii de lucru și apoi să ștergeți filtrul. După cum puteți vedea, rămân doar rânduri cu valori unice rămase:

Dacă 2 coloane sunt pe aceeași foaie, aproape unul de altul (adiacent) sau nu aproape unul de celălalt (nu adiacent), atunci procesul de eliminare a duplicatelor va fi puțin mai dificil. Nu putem șterge întregul rând cu valori duplicat, deoarece acest lucru va șterge și celulele din a doua coloană. Deci, pentru a lăsa doar intrări unice în coloană DAR, fa asta:

După cum puteți vedea, eliminarea duplicatelor din două coloane din Excel folosind formule nu este atât de dificilă.

Destul de des, utilizatorii Excel se confruntă cu sarcina de a compara două tabele sau liste pentru a identifica diferențele sau elementele lipsă. Fiecare utilizator face față acestei sarcini în felul său, dar cel mai adesea se petrece un timp destul de mare pentru rezolvarea acestei probleme, deoarece nu toate abordările acestei probleme sunt raționale. În același timp, există mai mulți algoritmi dovediți pentru acțiuni care vă vor permite să comparați listele sau tablourile într-un timp destul de scurt, cu un efort minim. Să aruncăm o privire mai atentă asupra acestor opțiuni.

Există destul de multe modalități de a compara suprafețele de masă în Excel, dar toate pot fi împărțite în trei grupuri mari:

compararea listelor de pe aceeași foaie;

compararea tabelelor situate pe diferite foi;

compararea intervalelor de tabele din diferite fișiere.

Pe baza acestei clasificări sunt selectate, în primul rând, metode de comparație și se determină acțiuni și algoritmi specifici pentru realizarea sarcinii. De exemplu, atunci când faceți o comparație în registre de lucru diferite, trebuie să deschideți două fișiere Excel în același timp.

În plus, ar trebui spus că are sens să comparăm spațiile de tabel numai atunci când acestea au o structură similară.

Metoda 1: formula simplă

Cel mai simplu mod de a compara datele din două tabele este folosind o formulă simplă de egalitate. Dacă datele se potrivesc, atunci returnează indicatorul TRUE, iar dacă nu, atunci - FALSE. Puteți compara atât date numerice, cât și text. Dezavantajul acestei metode este că poate fi utilizat numai dacă datele din tabel sunt ordonate sau sortate la fel, sincronizate și au un număr egal de linii. Să vedem cum să folosim această metodă în practică folosind exemplul a două tabele plasate pe o singură foaie.

Deci, avem două tabele simple cu liste de angajați ai întreprinderii și salariile acestora. Este necesar să se compare listele angajaților și să se identifice neconcordanțele dintre coloanele în care sunt plasate numele.

Pentru aceasta avem nevoie de o coloană suplimentară pe foaie. Intrăm acolo într-un indicator «=» ... Apoi facem clic pe primul element care urmează să fie comparat în prima listă. Puneți din nou simbolul «=» de la tastatură. Apoi, faceți clic pe prima celulă a coloanei pe care o comparăm în al doilea tabel. Rezultatul este o expresie de următorul tip:

Deși, desigur, în fiecare caz specific coordonatele vor fi diferite, dar esența va rămâne aceeași.
Faceți clic pe tasta introduce pentru a obține rezultatele comparației. După cum puteți vedea, când ați comparat primele celule ale ambelor liste, programul a indicat indicatorul "ADEVĂRAT", ceea ce înseamnă potrivirea datelor.

Acum trebuie să efectuăm o operație similară cu restul celulelor ambelor tabele din coloanele pe care le comparăm. Dar puteți copia pur și simplu formula, ceea ce va economisi semnificativ timp. Acest factor este deosebit de important atunci când se compară listele cu un număr mare de șiruri.
Procedura de copiere este mai ușor de realizat cu un mâner de umplere. Plasați cursorul peste colțul din dreapta jos al celulei de unde am primit indicatorul "ADEVĂRAT"... În acest caz, ar trebui transformată într-o cruce neagră. Acesta este markerul de umplere. Apăsați butonul stâng al mouse-ului și trageți cursorul în jos până la numărul de linii din tablourile de tabel comparate.

După cum puteți vedea, acum coloana suplimentară afișează toate rezultatele comparării datelor în două coloane de tablouri. În cazul nostru, datele nu se potriveau într-o singură linie. La compararea acestora, formula a dat rezultatul "FALS"... Pentru toate celelalte linii, după cum puteți vedea, formula de comparație a dat indicatorul "ADEVĂRAT".

În plus, este posibil să se calculeze numărul de nepotriviri folosind o formulă specială. Pentru aceasta, selectați elementul foii unde va fi afișată. Apoi faceți clic pe pictogramă "Funcție de inserare".

La fereastră Vrăjitori de funcțiiîn grupul operatorilor "Matematic" evidențiați numele SUMPRODUCT... Faceți clic pe buton "BINE".

Fereastra argumentelor funcției este activată SUMPRODUCT, a cărei sarcină principală este să calculeze suma produselor din gama selectată. Dar această funcție poate fi utilizată și în scopurile noastre. Sintaxa sa este destul de simplă:
SUMPRODUCT (matrice1; matrice2; ...)

În total, adresele cu până la 255 de matrice pot fi utilizate ca argumente. Dar, în cazul nostru, vom folosi doar două tablouri, în plus, ca un singur argument.

Punem cursorul în câmp „Array1”și selectați pe foaie intervalul de date comparat în prima zonă. După aceea, în teren am pus semnul "nu este egal" (<> ) și selectați intervalul comparat al celei de-a doua regiuni. Apoi, înfășurăm expresia rezultată între paranteze, în fața căreia punem două semne «-» ... În cazul nostru, am obținut următoarea expresie:

- (A2: A7<>D2: D7)

Faceți clic pe buton "BINE".

Operatorul calculează și afișează rezultatul. După cum puteți vedea, în cazul nostru, rezultatul este egal cu numărul "unu" adică înseamnă că s-a găsit o nepotrivire în listele comparate. Dacă listele ar fi complet identice, atunci rezultatul ar fi egal cu numărul «0» .

În același mod, puteți compara datele în tabele care se află pe diferite foi. Dar, în acest caz, este de dorit ca liniile din ele să fie numerotate. În caz contrar, procedura de comparație este aproape exact aceeași cu cea descrisă mai sus, cu excepția faptului că atunci când introduceți o formulă, va trebui să comutați între foi. În cazul nostru, expresia va arăta astfel:

B2 = Sheet2! B2

Adică, după cum puteți vedea, înainte de coordonatele datelor care sunt situate pe alte foi, diferite de locul în care este afișat rezultatul comparației, sunt indicate numărul foii și un semn de exclamare.

Metoda 2: selectați grupuri de celule

Comparația poate fi făcută folosind instrumentul de selectare a grupului de celule. De asemenea, vă permite să comparați numai listele sincronizate și ordonate. În plus, în acest caz, listele ar trebui să fie amplasate una lângă cealaltă pe aceeași foaie.

Metoda 3: formatarea condiționată

Comparațiile pot fi făcute utilizând metoda de formatare condiționată. Ca și în metoda anterioară, zonele comparate trebuie să fie pe aceeași foaie de lucru Excel și să fie sincronizate între ele.

Există un alt mod de a utiliza formatarea condiționată pentru a îndeplini sarcina la îndemână. La fel ca opțiunile anterioare, necesită localizarea ambelor zone comparate pe aceeași foaie, dar spre deosebire de metodele descrise anterior, nu va fi necesară condiția sincronizării sau sortării datelor, ceea ce distinge în mod favorabil această opțiune de cele descrise anterior.

Dacă doriți, puteți, dimpotrivă, să colorați elementele nepotrivite și să lăsați acei indicatori care se potrivesc cu umplutura în aceeași culoare. În acest caz, algoritmul acțiunilor este practic același, dar în fereastra de setări pentru evidențierea valorilor duplicate în primul câmp în locul parametrului „Recurent” parametrul trebuie selectat "Unic"... După aceea, faceți clic pe buton "BINE".

Astfel, exact acei indicatori care nu coincid vor fi evidențiați.

Metoda 4: formula complexă

De asemenea, puteți compara date utilizând o formulă complexă bazată pe funcție COUNTIF... Cu acest instrument, puteți număra câte elemente din coloana selectată a celui de-al doilea tabel se repetă în primul.

Operator COUNTIF aparține grupului statistic de funcții. Sarcina sa este de a număra numărul de celule în care valorile satisfac o anumită condiție. Sintaxa pentru acest operator este după cum urmează:

COUNTIF (interval, criteriu)

Argument "Gamă" este adresa matricei în care sunt numărate valorile potrivite.

Argument "Criteriu" specifică o condiție de potrivire. În cazul nostru, va reprezenta coordonatele anumitor celule din prima zonă a tabelului.

Desigur, pentru a compara indicatorii tabulari, această expresie poate fi aplicată în forma sa existentă, dar există posibilitatea de a o îmbunătăți.

Să facem astfel încât valorile care se află în al doilea tabel, dar nu în primul, să fie afișate într-o listă separată.

În primul rând, să ne revizuim puțin formula. COUNTIF, și anume, îl vom face unul dintre argumentele operatorului DACĂ... Pentru aceasta, selectați prima celulă în care se află operatorul COUNTIF... În bara de formule, înaintea ei, adăugați expresia "DACĂ" fără ghilimele și deschideți parantezele. Apoi, pentru a ne ușura munca, selectați valoarea din bara de formule "DACĂ"și faceți clic pe pictogramă "Funcție de inserare".

Deschide fereastra argumentelor funcției DACĂ... După cum puteți vedea, primul câmp al ferestrei este deja umplut cu valoarea operatorului COUNTIF... Dar trebuie să adăugăm altceva la acest câmp. Setăm cursorul acolo și adăugăm la expresia deja existentă «=0» fără ghilimele.
După aceea, du-te la câmp „Valoare dacă este adevărat”... Aici vom folosi încă o funcție imbricată - LINIA... Intrăm cuvântul "LINIA" fără ghilimele, apoi deschideți parantezele și indicați coordonatele primei celule cu numele de familie în al doilea tabel, apoi închideți parantezele. Mai exact, în cazul nostru, în teren „Valoare dacă este adevărat” s-a dovedit următoarea expresie:

RÂND (D2)

Acum operatorul LINIA va comunica funcții DACĂ numărul liniei în care se află numele de familie specific și, în cazul în care este îndeplinită condiția specificată în primul câmp, funcția DACĂ va afișa acest număr către celulă. Faceți clic pe buton "BINE".

După cum puteți vedea, primul rezultat este afișat ca "FALS"... Aceasta înseamnă că valoarea nu îndeplinește condițiile operatorului DACĂ... Adică primul nume de familie este prezent în ambele liste.

Folosind marcatorul de umplere, copiați expresia operatorului în mod obișnuit DACĂîntreaga coloană. După cum puteți vedea, pentru două poziții care sunt prezente în al doilea tabel, dar nu și în prima, formula oferă numere de linie.

Trecem înapoi din zona mesei spre dreapta și umplem coloana cu numere în ordine, începând de la 1 ... Numărul de numere trebuie să se potrivească cu numărul de rânduri din al doilea tabel de comparație. De asemenea, puteți utiliza marcatorul de umplere pentru a accelera procesul de numerotare.

După aceea, selectați prima celulă din dreapta coloanei cu numere și faceți clic pe pictogramă "Funcție de inserare".

Se deschide Expertul de funcții... Accesați categoria "Statistic"și selectați numele "CEL MAI PUŢIN"... Faceți clic pe buton "BINE".

Funcţie CEL MAI PUŢIN, a cărei fereastră de argumente a fost deschisă, este destinată să afișeze cea mai mică valoare specificată de cont.
În câmp „Array” ar trebui să specificați coordonatele intervalului coloanei suplimentare „Număr de coincidențe” pe care le-am transformat anterior folosind funcția DACĂ... Facem toate legăturile absolute.

În câmp „K” indică cea mai mică valoare care trebuie afișată. Aici indicăm coordonatele primei celule a coloanei cu numerotare, pe care le-am adăugat recent. Lăsați adresa relativă. Faceți clic pe buton "BINE".

Operatorul scoate rezultatul - un număr 3 ... Aceasta este cea mai mică numerotare a rândurilor nepotrivite din tablourile de tabele. Folosind mânerul de umplere, copiați formula chiar în partea de jos.

Acum, cunoscând numerele de linie ale elementelor care nu se potrivesc, putem introduce în celulă și valorile lor folosind funcția INDEX... Selectați primul element de foaie care conține formula CEL MAI PUŢIN... După aceea, mergeți la bara de formule și înaintea numelui "CEL MAI PUŢIN" adaugă numele "INDEX" fără ghilimele, deschideți imediat paranteza și puneți un punct și virgulă ( ; ). Apoi selectați numele în bara de formule "INDEX"și faceți clic pe pictogramă "Funcție de inserare".

După aceea, se deschide o mică fereastră în care trebuie să determinați dacă funcția ar trebui să aibă un formular de referință INDEX sau concepute pentru a lucra cu tablouri. Avem nevoie de o a doua opțiune. Este instalat în mod implicit, deci în această fereastră, pur și simplu faceți clic pe buton "BINE".

Fereastra de argumente a funcției pornește INDEX... Acest operator este destinat să afișeze valoarea care se află într-o matrice specifică în linia specificată.
După cum puteți vedea, câmpul "Numărul liniei" deja umplut cu valori ale funcției CEL MAI PUŢIN... Diferența dintre numerotarea foii Excel și numerotarea internă a zonei tabelului trebuie scăzută din valoarea deja existentă acolo. După cum puteți vedea, avem doar un antet deasupra valorilor tabelului. Aceasta înseamnă că diferența este o linie. Prin urmare, adăugăm în câmp "Numărul liniei" sens "-unu" fără ghilimele.

În câmp „Array” specificați adresa intervalului de valori al celui de-al doilea tabel. În acest caz, facem absolute toate coordonatele, adică le punem un semn de dolar în fața lor, așa cum am descris deja.

Faceți clic pe buton "BINE".

După afișarea rezultatului pe ecran, extindeți funcția folosind markerul de umplere până la capătul coloanei în jos. După cum puteți vedea, ambele nume de familie care sunt prezente în al doilea tabel, dar care sunt absente în primul tabel, sunt afișate într-un interval separat.

Metoda 5: compararea matricilor din diferite cărți

Când comparați intervale în diferite registre de lucru, puteți utiliza metodele enumerate mai sus, cu excepția acelor opțiuni care necesită plasarea ambelor spații de tabelă pe aceeași foaie. Principala condiție pentru realizarea procedurii de comparație în acest caz este deschiderea ferestrelor ambelor fișiere în același timp. Pentru versiunile Excel 2013 și versiunile ulterioare, precum și pentru versiunile anterioare Excel 2007, nu există nicio problemă cu această condiție. Dar în Excel 2007 și Excel 2010, pentru a deschide ambele ferestre în același timp, este necesară o manipulare suplimentară. Cum se face acest lucru este descris într-o lecție separată.

După cum puteți vedea, există o serie de posibilități de a compara tabele între ele. Ce opțiune de utilizat depinde de locul exact în care sunt localizate datele tabulare unul față de celălalt (pe aceeași foaie, în cărți diferite, pe foi diferite), precum și de modul în care utilizatorul dorește ca această comparație să fie afișată pe ecran.

Spuneți că doriți să comparați versiunile unui registru de lucru, să analizați un registru de lucru pentru probleme sau neconcordanțe sau să vedeți legături între registre de lucru sau foi de lucru. Dacă pe computerul dvs. este instalat Microsoft Office 365 sau Office Professional Plus 2013, programul de completare Întrebare foaie de calcul este disponibil în Excel.

Puteți utiliza comenzile din fila Întrebați pentru a efectua toate aceste sarcini și multe altele. Fila Întrebați de pe panglica Excel are butoane pentru comenzile descrise mai jos.

Dacă nu vezi Întreba fila din panglica Excel, consultați Activarea foii de calcul Solicitați suplimentul.

Comparați două registre de lucru

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

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

The Comparați fișiere comanda folosește Microsoft Spreadsheet Compare pentru a compara cele două fișiere. În Windows 8, puteți începe Compararea foilor de calcul în afara Excel făcând clic pe Comparați foaia de calcul pe Aplicații ecran. În Windows 7, faceți clic pe Windows start butonul și apoi> Toate programele > Microsoft Office 2013 > Instrumente Office 2013 > Comparați foaia de calcul 2013.

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

Analizați un registru de lucru

The Analiza registrului 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 prezintă 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 de registru de lucru

Cărțile de lucru conectate la alte registre de lucru prin referințe de celule pot deveni confuze. Utilizați butonul pentru a crea o hartă interactivă și grafică a dependențelor registrului de lucru create de conexiunile (legăturile) dintre fișiere. Tipurile de linkuri 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 relației, puteți selecta elemente și puteți găsi mai multe informații despre ele și puteți trage liniile de conexiune pentru a schimba forma diagramei.

Această diagramă prezintă 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 ale registrului de lucru, oferindu-vă o imagine a originilor datelor pentru registrul de lucru.

Afișați legăturile cu foaia de lucru

Aveți o mulțime de foi de lucru care depind unele de altele? Utilizați butonul pentru a crea o hartă interactivă și grafică 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ă oferă o imagine mai clară a modului în care datele dvs. ar putea 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ăturile dintre foile de lucru din diferite registre de lucru. Când poziționați indicatorul peste un nod din diagramă, cum ar fi foaia de lucru numită „Vest” din diagramă, apare un balon care conține informații.

Afișați relațiile celulare

Pentru a obține o diagramă interactivă detaliată a tuturor legăturilor dintr-o celulă selectată către 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ă prezintă două niveluri de relații de celule pentru celula A10 pe Sheet5 în Book1.xlsx. Această celulă este dependentă de celula C6 din foaia 1 dintr-un alt registru de lucru, Book2.xlsx. Această celulă este un precedent pentru mai multe celule de pe alte foi de lucru din același fișier.

Pentru a afla mai multe despre vizualizarea relațiilor celulare, citiți Consultați linkurile dintre celule.

Curățați formatarea excesivă a celulei

Deschideți vreodată un registru de lucru și găsiți că se încarcă încet sau a devenit imens? Este posibil ca formatarea să fie aplicată rândurilor sau coloanelor de care nu știți. Utilizați Formatarea excesivă a celulei curată comanda pentru a elimina formatarea în exces și pentru a reduce 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 caracteristicile Anchetați pentru a analiza sau compara registrele de lucru protejate prin parolă, va trebui să adăugați parola registrului de lucru în lista de parole, astfel încât Ancheta să poată deschide copia salvată a registrului de lucru. Folosește Parole din registru de lucru comanda pe Întreba pentru a adăuga parole, care vor fi salvate pe computer. Aceste parole sunt criptate și accesibile doar de dvs.

Articolul oferă răspunsuri la următoarele întrebări:

Cum se compară două tabele în Excel?
Cum se compară foi de calcul complexe în Excel?
Cum se compară tabelele în Excel folosind funcția VLOOKUP ()?
Cum se generează identificatori de rând unici dacă unicitatea lor este inițial determinată de un set de valori din mai multe coloane?
Cum să înghețe valorile celulei în formule atunci când copiați formule?

Atunci când lucrează cu cantități mari de informații, utilizatorul se poate confrunta cu o astfel de sarcină, cum ar fi compararea a două surse tabulare de date. La stocarea datelor într-un singur sistem de contabilitate (de exemplu, sisteme bazate pe 1C Enterprise, sisteme care utilizează baze de date SQL), capacitățile integrate în sistem sau SGBD pot fi utilizate pentru a compara datele. De regulă, pentru aceasta este suficient să se implice un programator care va scrie o interogare în baza de date sau un mecanism de raportare software. Un utilizator cu experiență care are abilitatea de a scrie interogări 1C sau SQL poate gestiona, de asemenea, o interogare.

Problemele încep când trebuie să efectuați urgent o sarcină de comparare a datelor, iar angajarea unui programator și scrierea la timp a unei cereri sau a unui raport de program poate depăși termenele stabilite pentru rezolvarea sarcinii. O altă problemă la fel de frecventă este necesitatea de a compara informații din diferite surse. În acest caz, declarația problemei pentru programator va suna ca integrarea a două sisteme. Soluția unei astfel de sarcini va necesita o calificare superioară a programatorului și va dura, de asemenea, mai mult timp decât dezvoltarea într-un singur sistem.

Pentru a rezolva problemele indicate, tehnica ideală este să utilizați editorul de foi de calcul Microsoft Excel pentru a compara datele. Majoritatea sistemelor comune de management și de reglementare acceptă exportul în format Excel. Această sarcină va necesita doar o anumită calificare a utilizatorului pentru a lucra cu această suită office și nu va necesita abilități de programare.

Să luăm în considerare soluția la problema comparării tabelelor în Excel folosind un exemplu. Avem două tabele care conțin listele de apartamente. Surse de descărcare - 1C Enterprise (contabilitate construcții) și un tabel în Excel (contabilitate vânzări). Tabelele sunt situate în registrul de lucru Excel pe prima și respectiv a doua coli.

Sarcina noastră este să comparăm aceste liste după adresă. Primul tabel conține toate apartamentele din casă. Al doilea tabel conține doar apartamentele vândute și numele cumpărătorului. Scopul final este de a afișa numele cumpărătorului în primul tabel pentru fiecare apartament (pentru acele apartamente care au fost vândute). Sarcina este complicată de faptul că adresa apartamentului din fiecare tabel este construcțională și constă din mai multe câmpuri: 1) adresa clădirii (casa), 2) secțiunea (intrarea), 3) etajul, 4) numărul de pe podea (de exemplu, de la 1 la 4) ...

Pentru a compara două tabele Excel, trebuie să ne asigurăm că fiecare rând din ambele tabele este identificat de un câmp, nu de patru. Puteți obține un astfel de câmp concatenând valorile celor patru câmpuri de adresă cu funcția Concatenate (). Scopul funcției Concatenate () este de a combina mai multe valori de text într-o singură linie. Valorile din funcție sunt listate prin simbolul ";". Valorile pot fi adrese de celulă sau text arbitrar specificat între ghilimele.

Pasul 1. Să introducem o coloană goală „A” la începutul primului tabel și să scriem formula în celula acestei coloane opusă primei linii cu datele:
= CONCATENATE (B3; "-"; C3; "-"; D3; "-"; E3)
Pentru comoditatea percepției vizuale, între valorile celulelor care urmează să fie îmbinate, am setat simbolurile „-”.

Pasul 2. Copiați formula în următoarele celule din coloana A.

Pasul 4. Pentru a compara tabelele Excel după valori, utilizați funcția VLOOKUP (). Scopul funcției VLOOKUP () este de a găsi o valoare în coloana din stânga a unui tabel și de a returna valoarea unei celule situată în coloana specificată a aceluiași rând. Primul parametru este valoarea dorită. Al doilea parametru este tabelul în care se va căuta valoarea. Al treilea parametru este numărul coloanei din care valoarea va fi returnată în rândul găsit. Al patrulea parametru este tipul de căutare: false este o potrivire exactă, adevărat este o potrivire aproximativă. Deoarece informațiile de ieșire ar trebui plasate în primul tabel (în acest tabel trebuiau adăugate numele cumpărătorilor), atunci formula va fi scrisă în el. Să formăm o formulă în coloana liberă din dreapta tabelului opusă primei linii de date:
= VLOOKUP (A3; Sheet2! $ A $ 3: $ F $ 10; 6; FALSE)
Când copiați formule, Excel inteligent modifică automat adresa celulei. În cazul nostru, valoarea dorită pentru fiecare rând se va modifica: A3, A4 etc., iar adresa tabelului în care se efectuează căutarea trebuie să rămână neschimbată. Pentru a face acest lucru, fixați celulele din parametrul adresei tabelului cu simboluri „$”. În loc de „Sheet2! A3: F10” facem „Sheet2! $ A $ 3: $ F $ 10”.

După instalarea programului de completare, veți avea o nouă filă cu o comandă pentru a apela funcția. Când faceți clic pe comandă Compararea intervalelor apare o casetă de dialog pentru introducerea parametrilor.

Această macrocomandă vă permite să comparați tabele de orice dimensiune și cu orice număr de coloane. Compararea tabelelor se poate face una, două sau trei coloane în același timp.

Caseta de dialog este împărțită în două părți: stânga pentru primul tabel și dreapta pentru a doua.

Pentru a compara tabele, urmați acești pași:

Specificați intervalele de tabel.
Setați o casetă de selectare (casetă de selectare / casetă de selectare) sub gama selectată de tabele dacă tabelul include un antet (rând de antet).
Selectați coloanele tabelelor din stânga și din dreapta prin care se va efectua comparația (dacă intervalele tabelelor nu includ anteturi, coloanele vor fi numerotate).
Specificați tipul de comparație.
Selectați opțiunea pentru afișarea rezultatelor.

Tipul de comparație al tabelelor

Programul vă permite să selectați mai multe tipuri de comparație a tabelului:

Găsiți rânduri dintr-un tabel care nu sunt prezente într-un alt tabel

Când este selectat acest tip de comparație, programul caută rânduri dintr-un tabel care sunt absente în altul. Dacă comparați tabelele cu mai multe coloane, atunci rezultatul lucrării va fi rânduri în care există o diferență în cel puțin una dintre coloane.

Găsiți linii potrivite

Atunci când alegeți acest tip de comparație, programul găsește rânduri care se potrivesc în primul și al doilea tabel. Rândurile sunt considerate coincidente dacă valorile din coloanele de comparație selectate (1, 2, 3) ale unui tabel coincid complet cu valorile coloanelor celui de-al doilea tabel.

Un exemplu de lucru al programului în acest mod este prezentat în dreapta în imagine.

Potriviți tabelele pe baza celor selectate

În acest mod de comparație, opus fiecărui rând al primului tabel (selectat ca principal), datele din rândul corespunzător al celui de-al doilea tabel sunt copiate. Dacă nu există rânduri potrivite, rândul opus tabelului principal rămâne gol.

Compararea tabelelor pe patru sau mai multe coloane

Dacă nu aveți funcționalitatea programului și trebuie să mapați tabelele cu patru sau mai multe coloane, atunci puteți ieși din situație după cum urmează: