Formula pentru compararea datelor în excel. Cum se compară două coloane în Excel - Metode de comparare a datelor Excel

Acasă / Routere

Să comparăm două tabele cu aproape aceeași structură. Tabelele diferă în valorile rândurilor individuale; unele nume de rând apar într-un tabel, dar este posibil să nu fie în altul.

Lasă-l pe cearșafuri ianuarieŞi februarie Există două tabele cu cifra de afaceri pentru perioada pentru conturile corespunzătoare.

După cum se poate observa din figuri, tabelele diferă:

Prezența (absența) liniilor (nume de cont). De exemplu, într-un tabel pe o foaie ianuarie nu există număr 26 (vezi fișierul exemplu), iar în tabelul de pe foaie februarie contul 10 și subconturile acestuia lipsesc.
Diferite valori în linii. De exemplu, conform contului 57, cifra de afaceri pentru ianuarie și februarie nu se potrivește.

Dacă structurile tabelelor sunt aproximativ aceleași (majoritatea numelor de cont (rânduri) sunt aceleași, numărul și numele coloanelor sunt aceleași), atunci puteți compara cele două tabele. Să facem o comparație în două moduri: unul este mai ușor de implementat, celălalt este mai vizual.

O opțiune simplă pentru compararea a 2 tabele

Mai întâi, să determinăm ce rânduri (nume de cont) sunt prezente într-un tabel, dar nu în altul. Apoi, în tabelul în care lipsesc mai puține rânduri (cel mai complet tabel), vom afișa un raport de comparație reprezentând diferența pe coloană (diferența de cifra de afaceri pentru ianuarie și februarie).

Principalul dezavantaj al acestei abordări este că raportul de comparare a tabelului nu include rânduri care lipsesc din cel mai complet tabel. De exemplu, în cazul pe care îl luăm în considerare, cel mai mult masă plină este un tabel pe o fișă de lucru Ianuarie,în care lipsește contul 26 din tabelul din februarie.

Pentru a determina care dintre cele două tabele este cel mai complet, trebuie să răspundeți la 2 întrebări: Ce conturi din tabelul din februarie lipsesc în tabelul din ianuarie? și Ce conturi din tabelul din ianuarie lipsesc din tabelul din ianuarie?

Acest lucru se poate face folosind formule (vezi coloana E): = IF(END(CĂUTAREV(A7, ianuarie! $A$7:$A$81,1,0));"Nu","Da")și = IF(END(CĂUTAREV(A7,februarie!7$A$:77$A$,1,0));"Nu","Da")

Vom compara cifra de afaceri a conturilor folosind formulele: = DACĂ(Sfârșit(CĂUTAREV($A7,februarie!$A$7:$C77,2,0)),0,CĂUTAREV($A7,februarie!$A$7:$C77,2,0))-B7și = DACĂ(Sfârșit(CĂUTAREV($A7,februarie!$A$7:$C77,3,0)),0,CĂUTAREV($A7,februarie!$A$7:$C77,3,0))-C7

Dacă nu există un rând corespunzător, funcția VLOOKUP() returnează eroarea #N/A, care este procesată de o combinație a funcțiilor END() și IF(), înlocuind eroarea cu 0 (dacă rândul lipsește) sau cu o valoare din coloana corespunzătoare.

Puteți folosi acest lucru pentru a evidenția discrepanțe (de exemplu, cu roșu).

O opțiune mai vizuală pentru compararea a 2 tabele (dar mai complexă)

Prin analogie cu problema rezolvată în articol, puteți genera o listă de nume de cont, inclusiv TOATE numele de cont din ambele tabele (fără repetări). Apoi afișați diferența după coloană.

Pentru a face acest lucru aveți nevoie de:

Folosind = IFEROARE(IFEROARE(INDEX(Ianuarie, MATCH(0,COUNTIF(A$4:$A4,Ianuarie),0)), INDEX(Februarie,MATCH(0,COUNTIF(A$4:$A4,Februarie),0))) ;"") creați o listă de conturi din ambele tabele din coloana A (fără repetări);
Folosind = IFERROR(INDEX(Lista, MATCH(SMALL(COUNTIF(Lista, "<"&Список); СТРОКА()-СТРОКА($B$4)); СЧЁТЕСЛИ(Список; "<"&Список); 0));"") , unde Listă este o listă de conturi din ambele tabele (coloana A), conturi obținute la etapa anterioară;
Folosind formula = DACĂ(Sfârșit(CĂUTAREV($B5, ianuarie!$A$7:$C$81,2,0)),0,CUTAREV($B5,ianuarie!$A$7:$C$81,2,0)) - DACĂ( UND(CĂUTAREV($B5,februarie!7$A$:77$C$,2,0)),0,CĂUTARE V($B5,februarie!77$A$:77,2,0$C$)) compararea cifrei de afaceri a contului;
Folosiți culoarea pentru a evidenția discrepanțe, precum și pentru a evidenția conturile găsite doar într-un tabel (de exemplu, în figura de mai sus, conturile conținute doar în tabelul din ianuarie sunt evidențiate cu albastru, iar conturile doar din tabelul din februarie sunt evidențiate cu galben).

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ă.

Selectați celule dintr-un interval Lista veche.
Selecta.
În fereastră Creați o regulă de formatare selectați elementul numit Utilizați formula
Introduceți această formulă în câmpul ferestrei (Fig. 164.2): =COUNTIF(NewList;A2)=0.
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.
Faceți clic pe OK.

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

Selectați celule dintr-un interval NewList.
Selecta Acasă Formatare condiționată Creați o regulă pentru a deschide o casetă de dialog Creați o regulă de formatare.
În fereastră Creați o regulă formatarea elementului selectat Utilizați formula pentru a defini celulele de formatat.
Introduceți această formulă în câmpul ferestrei: =COUNTIF(OldList;D2)=0 .
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ă).
Faceți clic pe OK.

Ca urmare, vor fi evidențiate numele care se află în lista veche, dar nu î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ă.

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

Există destul de multe moduri de a compara zonele de tabel în Excel, dar toate pot fi împărțite în trei grupuri mari:

compararea listelor situate pe aceeași foaie;

compararea tabelelor situate pe diferite foi;

compararea intervalelor de tabel în diferite fișiere.

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

În plus, trebuie spus că are sens să compari zonele de tabel doar atunci când au o structură similară.

Metoda 1: formulă simplă

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

Deci, avem două tabele simple cu liste cu angajații întreprinderii și salariile acestora. Este necesar să se compare listele de angajați și să se identifice neconcordanțe între coloanele în care se află numele.

Pentru a face acest lucru, avem nevoie de o coloană suplimentară pe foaie. Scrieți un semn acolo «=» . Apoi faceți clic pe prenumele pe care doriți să îl comparați în prima listă. Punem 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 tipul următor:

Deși, desigur, în fiecare caz specific coordonatele vor diferi, dar esența va rămâne aceeași.
Faceți clic pe cheie Intră pentru a obține rezultate de comparație. După cum puteți vedea, la compararea primelor celule din ambele liste, programul a indicat indicatorul "ADEVĂRAT", ceea ce înseamnă că datele se potrivesc.

Acum trebuie să efectuăm o operație similară cu celulele rămase din ambele tabele din coloanele pe care le comparăm. Dar puteți copia pur și simplu formula, ceea ce va economisi timp semnificativ. Acest factor este deosebit de important atunci când se compară liste cu un număr mare de linii.
Procedura de copiere se realizează cel mai ușor folosind marcatorul de umplere. Mutăm cursorul în colțul din dreapta jos al celulei unde am primit indicatorul "ADEVĂRAT". În același timp, ar trebui să se transforme într-o cruce neagră. Acesta este marcatorul de umplere. Faceți clic pe butonul stâng al mouse-ului și trageți cursorul în jos cu numărul de linii din tablourile comparate.

După cum puteți vedea, acum coloana suplimentară afișează toate rezultatele comparării datelor în două coloane de matrice tabelare. În cazul nostru, datele dintr-un singur rând nu s-au potrivit. La compararea lor, formula a dat rezultatul "MINCIUNĂ". Pentru toate celelalte linii, după cum vedem, formula de comparație a dat indicatorul "ADEVĂRAT".

În plus, este posibil să se calculeze numărul de discrepanțe folosind o formulă specială. Pentru a face acest lucru, selectați elementul foii în care va fi afișat. Apoi faceți clic pe pictogramă „Inserare funcție”.

În fereastră Vrăjitorii de funcțiiîn grupul de operatori "Matematic" evidențiați numele SUMPRODUS. Faceți clic pe butonul "BINE".

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

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

Plasați cursorul în câmp „Matrice1”și selectați intervalul de date care trebuie comparat în prima zonă de pe foaie. După aceea, puneți un semn în câmp "nu este egal" (<> ) și selectați intervalul comparat al celei de-a doua zone. În continuare, înfășurăm expresia rezultată între paranteze, înaintea căreia punem două semne «-» . În cazul nostru, avem următoarea expresie:

--(A2:A7<>D2:D7)

Faceți clic pe butonul "BINE".

Operatorul efectuează calculul și afișează rezultatul. După cum puteți vedea, în cazul nostru rezultatul este egal cu numărul "1", adică asta înseamnă că a fost 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 din tabele care se află pe foi diferite. Dar în acest caz, este de dorit ca liniile din ele să fie numerotate. În caz contrar, procedura de comparare este aproape exact aceeași cu cea descrisă mai sus, cu excepția faptului că la introducerea formulei va trebui să comutați între foi. În cazul nostru, expresia va arăta astfel:

B2=Foaie2!B2

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

Metoda 2: Selectarea grupurilor de celule

Comparațiile pot fi făcute folosind instrumentul de selecție a grupului de celule. De asemenea, poate compara numai liste sincronizate și ordonate. În plus, în acest caz listele trebuie să fie amplasate una lângă alta pe aceeași foaie.

Metoda 3: Formatarea condiționată

Puteți face o comparație folosind metoda de formatare condiționată. Ca și în metoda anterioară, zonele care sunt comparate trebuie să fie pe aceeași foaie de lucru Excel și să fie sincronizate între ele.

Există o altă modalitate de a utiliza formatarea condiționată pentru a îndeplini sarcina. Ca și opțiunile anterioare, necesită amplasarea ambelor zone comparate pe aceeași foaie, dar spre deosebire de metodele descrise anterior, condiția de sincronizare sau sortare a datelor nu va fi obligatorie, ceea ce distinge această opțiune de cele descrise anterior.

Dacă doriți, puteți, dimpotrivă, să colorați elementele care nu se potrivesc și să lăsați indicatorii care se potrivesc cu aceeași culoare de umplere. În acest caz, algoritmul acțiunilor este aproape același, dar în fereastra de setări, valorile repetate sunt evidențiate în primul câmp în loc de parametru "Recurente" ar trebui să selectați opțiunea "Unic". După aceea, faceți clic pe butonul "BINE".

Astfel, exact acei indicatori care nu se potrivesc vor fi evidentiati.

Metoda 4: formula complexă

De asemenea, puteți compara datele folosind o formulă complexă bazată pe funcție COUNTIF. Folosind acest instrument, puteți calcula câte fiecare element din coloana selectată a celui de-al doilea tabel este repetat în primul.

Operator COUNTIF aparține grupului statistic de funcții. Sarcina sa este de a număra numărul de celule ale căror valori satisfac o anumită condiție. Sintaxa acestui operator este următoarea:

COUNTIF(interval, criteriu)

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

Argument "Criteriu" specifică condiția de potrivire. În cazul nostru, va reprezenta coordonatele celulelor specifice din prima zonă a tabelului.

Desigur, această expresie poate fi folosită în forma sa existentă pentru a compara indicatorii tabelare, dar este posibil să o îmbunătățim.

Să ne asigurăm că acele valori care se află în al doilea tabel, dar nu în primul, sunt afișate într-o listă separată.

În primul rând, să ne reelaborăm puțin formula COUNTIF, și anume să-l facem unul dintre argumentele operatorului DACĂ. Pentru a face acest lucru, selectați prima celulă în care se află operatorul COUNTIF. În bara de formule, adăugați expresia în fața acesteia "DACĂ" fără ghilimele și deschideți paranteza. Apoi, pentru a ne ușura lucrul, evidențiați valoarea în bara de formule "DACĂ"și faceți clic pe pictogramă „Inserare funcție”.

Se deschide fereastra cu argumente ale funcției DACĂ. După cum puteți vedea, primul câmp al ferestrei este deja completat cu valoarea operatorului COUNTIF. Dar trebuie să adăugăm altceva la acest domeniu. Plasați cursorul acolo și adăugați la expresia existentă «=0» fără ghilimele.
După aceasta trecem pe teren „Valoare dacă este adevărat”. Aici vom folosi o altă funcție imbricată - LINIA. Introduceți cuvântul "LINIA" fara ghilimele, apoi deschidem parantezele si indicam coordonatele primei celule cu numele de familie in al doilea tabel, dupa care inchidem parantezele. Mai exact în cazul nostru în domeniu „Valoare dacă este adevărat” s-a obtinut urmatoarea expresie:

RÂND(D2)

Acum operatorul LINIA va raporta funcții DACĂ numărul rândului în care se află un anumit nume de familie, iar în cazul în care condiția specificată în primul câmp este îndeplinită, funcția DACĂ va scoate acest număr în celulă. Faceți clic pe butonul "BINE".

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

Folosind marcatorul de umplere, copiem expresia operatorului în mod obișnuit DACĂ pentru întreaga coloană. După cum puteți vedea, pentru două poziții care sunt prezente în al doilea tabel, dar nu în primul, formula produce numere de rând.

Ne întoarcem din zona tabelului în 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 comparat. De asemenea, puteți utiliza un marcator de umplere pentru a accelera procesul de numerotare.

După aceasta, selectați prima celulă din dreapta coloanei cu numere și faceți clic pe pictogramă „Inserare funcție”.

Se deschide Expertul de funcții. Mergi la categorie "Statistic"și selectați un nume "CEL MAI PUŢIN". Faceți clic pe butonul "BINE".

Funcţie CEL MAI PUŢIN, a cărei fereastră de argument a fost deschisă, are scopul de a afișa cea mai mică valoare specificată de cont.
În câmp „Matrice” ar trebui să specificați coordonatele intervalului coloanei suplimentare „Numărul de meciuri”, pe care l-am convertit anterior folosind funcția DACĂ. Facem toate linkurile absolute.

În câmp "K" indică cea mai mică valoare care trebuie scoasă. Aici indicăm coordonatele primei celule a coloanei numerotate pe care am adăugat-o recent. Lăsăm adresa relativă. Faceți clic pe butonul "BINE".

Operatorul emite rezultatul - un număr 3 . Este cea mai mică dintre numerotarea rândurilor care nu se potrivesc de matrice de tabel. Folosind marcatorul de umplere, copiați formula în partea de jos.

Acum, cunoscând numerele de rând ale elementelor care nu se potrivesc, putem introduce valorile acestora în celulă folosind funcția INDEX. Selectați primul element al foii care conține formula CEL MAI PUŢIN. După aceea, accesați bara de formule și înainte de nume "CEL MAI PUŢIN" adauga numele "INDEX" fără ghilimele, deschideți imediat paranteza și puneți punct și virgulă ( ; ). Apoi selectați numele în bara de formule "INDEX"și faceți clic pe pictogramă „Inserare funcție”.

După aceasta, se deschide o mică fereastră în care trebuie să determinați ce tip de referință ar trebui să aibă funcția INDEX sau concepute pentru a funcționa cu matrice. Avem nevoie de o a doua variantă. Este instalat implicit, așa că în această fereastră facem pur și simplu clic pe butonul "BINE".

Se deschide fereastra cu argumente ale funcției INDEX. Acest operator este conceput pentru a afișa o valoare care se află într-o anumită matrice într-o linie specificată.
După cum vedem, câmpul „Număr de linie” deja umplute cu valori ale funcției CEL MAI PUŢIN. Din valoarea care există deja acolo, ar trebui să scădeți diferența dintre numerotarea foii Excel și numerotarea internă a zonei tabelului. După cum puteți vedea, avem doar un antet deasupra valorilor din tabel. Aceasta înseamnă că diferența este de o linie. Prin urmare, adăugăm în teren „Număr de linie” sens „-1” fără ghilimele.

În câmp „Matrice” indicați adresa intervalului de valori din cel de-al doilea tabel. În același timp, facem absolute toate coordonatele, adică punem un semn dolar în fața lor în modul descris anterior.

Faceți clic pe butonul "BINE".

După afișarea rezultatului pe ecran, utilizați marcatorul de umplere pentru a trage funcția în jos până la sfârșitul coloanei. După cum puteți vedea, ambele nume de familie, care sunt prezente în al doilea tabel, dar nu în primul, sunt afișate într-un interval separat.

Metoda 5: Compararea matricelor în diferite registre de lucru

Când comparați intervalele din diferite registre de lucru, puteți utiliza metodele enumerate mai sus, excluzând acele opțiuni în care trebuie să plasați ambele zone de tabel pe aceeași foaie. Condiția principală pentru efectuarea procedurii de comparare în acest caz este deschiderea ferestrelor ambelor fișiere în același timp. Pentru versiunile de Excel 2013 și ulterioare, precum și pentru versiunile anterioare Excel 2007, nu există probleme cu îndeplinirea acestei condiții. Dar în Excel 2007 și Excel 2010, pentru a deschide ambele ferestre în același timp, sunt necesare manipulări suplimentare. 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 tabelele între ele. Ce opțiune de utilizat depinde de locul exact în care sunt situate datele tabelare unele față de altele (pe aceeași foaie, în registre de lucru diferite, pe foi diferite), precum și de modul în care utilizatorul dorește ca această comparație să fie afișată pe ecran.

Întrebare de la un utilizator

Buna ziua!

Am o sarcină și pentru a treia zi acum mi-am zguduit mintea - nu știu cum să o duc la bun sfârșit. Există 2 tabele (aproximativ 500-600 de rânduri în fiecare), trebuie să luați coloana cu numele produsului dintr-un tabel și să o comparați cu numele produsului din celălalt, iar dacă produsele se potrivesc, copiați și lipiți valoarea din tabelul 2 în tabelul 1. Explicat confuz, dar cred că veți înțelege sarcina din fotografie ( aproximativ : fotografia a fost tăiată de cenzură, este încă informații personale).

Vă mulțumesc anticipat. Andrei, Moscova.

O zi buna tuturor!

Ceea ce ați descris se referă la sarcini destul de populare care sunt relativ ușor și rapid de rezolvat folosind Excel. Tot ce trebuie să faceți este să puneți cele două tabele în program și să utilizați funcția CĂUTARE V. Mai multe despre munca ei mai jos...

Un exemplu de lucru cu funcția VLOOKUP

Ca exemplu, am luat două semne mici, acestea sunt afișate în captura de ecran de mai jos. În primul tabel (coloane A, B- produs și preț) nu există date pe coloană B; în a doua, sunt completate ambele coloane (produs și preț). Acum trebuie să verificați primele coloane din ambele tabele și automat, dacă se găsește o potrivire, copiați prețul în primul tabel. Pare o sarcină simplă...

Cum se face...

Plasați indicatorul mouse-ului într-o celulă B2- adică în prima celulă a coloanei unde nu avem valoare și scriem formula:

=CĂUTAREV(A2,$E$1:$F$7,2,FALSE)

A2- valoarea din prima coloană a primului tabel (ce vom căuta în prima coloană a celui de-al doilea tabel);

$E$1:$F$7- un al doilea tabel complet selectat (în care vrem să găsim și să copiem ceva). Acordați atenție semnului „$” - este necesar ca atunci când copiați formula, celulele celui de-al doilea tabel selectat să nu se schimbe;

2 - numărul coloanei din care copiem valoarea (rețineți că al doilea tabel selectat are doar 2 coloane. Dacă ar avea 3 coloane, atunci valoarea ar putea fi copiată din coloana a 2-a sau a 3-a);

MINCIUNĂ- cautam o potrivire exacta (in caz contrar se va inlocui primul asemanator care evident nu ne convine).

De fapt, puteți ajusta formula finită la nevoile dvs. modificând-o ușor. Rezultatul formulei este prezentat în imaginea de mai jos: prețul a fost găsit în al doilea tabel și a intrat în modul automat. Totul funcționează!

Pentru a seta prețul pentru alte nume de produse, pur și simplu extindeți (copiați) formula în alte celule. Exemplu de mai jos.

După care, după cum puteți vedea, primele coloane ale tabelelor vor fi comparate: din rândurile în care valorile celulelor coincid, datele necesare vor fi copiate și înlocuite. În general, este clar că mesele pot fi mult mai mari!

Nota: Trebuie să spun că funcția VLOOKUP este destul de solicitantă pentru resursele computerului. În unele cazuri, cu un document excesiv de mare, compararea tabelelor poate dura destul de mult. În aceste cazuri, merită să luați în considerare fie alte formule, fie soluții complet diferite (fiecare caz este individual).

Asta-i tot, succes!

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 foi 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.