Compararea foilor în excel pentru meciuri. Compararea datelor în Excel pe diferite foi. Cum se compară fișierele Excel

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 Versiuni Office ProPlus 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, de ex. 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 celulelor 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 de cod 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.

Uneori este nevoie să comparați două fișiere MS Excel. Aceasta poate fi găsirea discrepanțelor de preț pentru anumite articole sau modificarea oricăror indicații, nu contează, principalul lucru este că este necesar să găsiți anumite discrepanțe.

Nu ar fi greșit să menționăm că, dacă există câteva înregistrări în fișierul MS Excel, atunci nu are rost să recurgem la automatizare. Dacă fișierul conține câteva sute sau chiar mii de înregistrări, atunci este imposibil să faci fără ajutorul puterii de calcul a unui computer.

Să simulăm o situație în care două fișiere au același număr de linii, iar discrepanța trebuie căutată într-o anumită coloană sau în mai multe coloane. Această situație este posibilă, de exemplu, dacă trebuie să comparați prețul mărfurilor în funcție de două liste de prețuri sau să comparați măsurătorile sportivilor înainte și după sezonul de antrenament, deși pentru o astfel de automatizare trebuie să existe o mulțime de ele.

Ca exemplu de lucru, să luăm un fișier cu performanța participanților fictivi: alergare de 100 de metri, alergare de 3000 de metri și tracțiuni. Primul fișier este o măsurătoare la începutul sezonului, iar al doilea este sfârșitul sezonului.

Prima modalitate de a rezolva problema. Soluția este utilizarea doar formulelor MS Excel.

Deoarece înregistrările sunt aranjate vertical (aranjamentul cel mai logic), este necesar să folosiți funcția. Dacă utilizați plasarea orizontală a înregistrărilor, va trebui să utilizați funcția.

Pentru a compara performanța de alergare la 100 de metri, formula arată ca după cum urmează:
=DACĂ(CĂUTAREV($B2,Foaie2!$B$2:$F$13,3,ADEVĂRAT)<>D2;D2-VLOOKUP($B2;Sheet2!$B$2:$F$13,3,TRUE);„Fără diferență”)
Dacă nu există nicio diferență, este afișat un mesaj că nu există nicio diferență dacă este prezentă, atunci valoarea de la sfârșitul sezonului este scăzută din valoarea de la sfârșitul sezonului.

Formula pentru alergarea de 3000 de metri este următoarea:
=DACĂ(CĂUTARE V($B2,Foaie2!$B$2:$F$13,4,ADEVĂRAT)<>E2;„Există o diferență”;„Nu există nicio diferență”)
Dacă finită şi valorile initiale nu sunt egale, este afișat un mesaj corespunzător. Formula pentru trageri poate fi similară cu oricare dintre cele anterioare, nu are rost să o dați suplimentar. Fișierul final cu discrepanțele găsite este afișat mai jos.

O mica precizare. Pentru a face formulele mai ușor de citit, datele din cele două fișiere au fost mutate într-unul singur (pe foi diferite), dar acest lucru nu s-ar fi putut face.

Video care compară două fișiere MS Excel folosind și funcții.

A doua modalitate de a rezolva problema. Soluție folosind MS Access.

Această problemă poate fi rezolvată dacă importați mai întâi fișiere MS Excel în Access. În ceea ce privește metoda de importare a datelor externe în sine, nu există nicio diferență în găsirea diferitelor câmpuri (oricare dintre opțiunile prezentate va fi potrivită).

Acesta din urmă reprezintă legătura Fișiere Excelși Access, astfel încât atunci când modificați datele din fișierele Excel, discrepanțele vor fi găsite automat atunci când executați o interogare în MS Access.

Următorul pas după import este crearea de relații între tabele. Ca câmp de legătură, selectați câmpul unic „Număr articol”.
Al treilea pas este crearea simpla cerere pentru a selecta folosind designerul de interogări.

În prima coloană indicăm ce înregistrări trebuie afișate, iar în a doua - în ce condiții vor fi afișate înregistrările. Desigur, pentru al doilea și al treilea câmp acțiunile vor fi similare.

Video care compară fișierele MS cu Excel folosind MS Access.

Ca urmare a manipulărilor efectuate, sunt afișate toate înregistrările, cu date diferite în câmp: „Alergare 100 de metri”. Fișierul MS Access este prezentat mai jos (din păcate, SkyDrive nu permite încorporarea ca fișier Excel)

Aceste două metode există pentru a găsi discrepanțe în tabelele MS Excel. Fiecare are atât avantaje, cât și dezavantaje. Evident, aceasta nu este o listă exhaustivă de comparații între cele două fișiere Excel. Așteptăm sugestiile dumneavoastră în comentarii.

S-ar părea sarcină simplă- compararea tabelelor. Mai precis, comparând două coloane de tabel pentru potriviri sau diferențe. Este logic să sugerăm că Excel este o soluție ideală la problemă, dar, din păcate, este simplă comparație gratuită Nu am găsit niciun tabel în Excel, cu excepția, poate, a primitivului „line1=line2”. În realitate, o anumită prelucrare a șirurilor este necesară înainte de comparare, deoarece acestea pot conține spații suplimentare, semne de punctuație și așa mai departe. Drept urmare, s-a decis să se scrie un utilitar care compară două fișiere text linie cu linie și cu procesare linie la alegerea utilizatorului...

Procesarea șirurilor în mod specific prin fișiere a fost aleasă ca universală. Nu contează care este sursa de date, dacă este doar o listă sau Foaie de calcul Excel. De obicei, totul poate fi copiat într-un fișier text. Deci, să trecem direct la program.

Descărcați și despachetați programul. În versiunea sa originală, conține trei fișiere „Compare.exe” - programul în sine. „Lista 1.txt” și „Lista 2.txt” sunt goale fișiere text. Aici trebuie să introduceți liniile pentru comparație. Hai sa lansam:

În mod implicit, după părerea mea, sunt setări optime comparatii. Fereastra cu un exemplu despre cum va funcționa compararea tabelelor a fost creată doar pentru a ajusta setările la sarcinile dvs. și înțelegere comună ce se întâmplă. Nu compara datele reale din exemplu, deoarece... Aceste ferestre nu pot conține mai mult de 32 KB de text, restul este întrerupt fără avertisment. Este posibil să obțineți un rezultat eronat! Programul are un indiciu și când treceți cu mouse-ul în fereastră se afișează scurtă descriere setări sau element.

După ce ați jucat cu exemplul de comparație, copiați datele pentru comparare în fișierele „List 1.txt” și „List 2.txt” și cu setările selectate anterior, faceți clic pe butonul „Procesează fișiere”. În timp ce fișierele sunt procesate, butonul va afișa mesajul „Procesare în curs” în roșu, așteptați până când acest proces este finalizat. Când ați terminat, uitați-vă la locul unde ați lansat programul în folderul cu programul, în funcție de setări apar fișierele indicate în exemplul de comparație; Cu fiecare nouă comparație sau deschidere/închidere a programului, toate fișierele cu excepția „List 1.txt” și „List 2.txt” sunt șterse.

Și puțin despre viteza de comparație. Majoritatea problemelor din viața reală pot fi rezolvate aproape instantaneu. Ei bine, testele mele sunt așa (tip procesor Intel Core pentru mufa LGA 775 cu o frecvență de 2 GHz):

Comparând 2 liste de 1MB fiecare (25 de caractere pe rând și 39 de mii de rânduri în fiecare listă), pentru comparație, evident că trebuie să compari fiecare rând din prima listă cu toate rândurile celeilalte. În total, obținem 1,521 miliarde de comparații de șiruri. Timpul de execuție este de aproximativ 20 de secunde. Consumul de memorie este mai mic de 10 MB.

Comparație a 2 liste de 10 MB fiecare (25 de caractere pe rând și 390 de mii de rânduri în fiecare listă). În total, obținem 152,1 miliarde de comparații de șiruri. Timpul de finalizare este de aproximativ o oră. În acest caz, programul preia aproximativ 200 MB RAM. Deși astfel de dimensiuni sunt deja teritoriul bazelor de date. În acest program, am folosit deja toate modalitățile rezonabile de a crește viteza.

Algoritm de operare și parametri de comparație

Algoritmul de operare este astfel încât, indiferent de setări, toate caracterele sunt eliminate din șiruri, cu excepția literelor latine și rusești, a cifrelor și a punctelor și a virgulelor. Desigur, toate spațiile suplimentare dintre cuvinte și spațiile de la marginile liniei sunt eliminate.

Găsiți șiruri potriviteŞi Găsiți șiruri diferite - Totul este clar aici, vor fi căutate linii care se potrivesc sau diferite. Potrivirile vor fi scrise în fișierul „Matches.txt”. Când căutați șiruri de caractere diferite, acestea vor fi scrise în două fișiere „Dispotriviri 1.txt” și „Nepotriviri 2.txt” pentru Listele 1 și, respectiv, 2. Tot în zona de exemplu de comparație, în loc de o fereastră, apar două.

ATENŢIE! La compararea listelor pentru o potrivire, există o particularitate, deoarece aceleași linii sunt prezente în ambele liste, rezultatul include linii din lista 1. Puneți text mai bine formatat în lista 1 atunci când comparați pentru potriviri.

Remediați erorile de aspect al tastaturii- până la Comutator Punto Cu siguranță este departe aici. Aceasta se referă la erori la tastarea literelor similare într-un aspect greșit (C, H, P etc.). De exemplu, rusă „s” și latină. Sunt pe aceeași tastă și dacă cuvântul începe cu „c”, atunci puteți introduce prima literă Aspect în limba engleză, apoi treceți la rusă sau invers. Algoritmul de înlocuire este de așa natură încât, dacă într-un cuvânt există mai multe litere rusești decât cele englezești, atunci cele engleze sunt înlocuite cu cele rusești și invers.

Corectează Yo(yo) la E(e)- pur și simplu toate „e” sunt înlocuite cu „e”.

Comparați fără diferențiere între majuscule și minuscule- toate literele devin majuscule.

Comparați prin șiruri unice- Dacă parametrul este activat, atunci listele sunt mai întâi verificate pentru șiruri de caractere potrivite. Dacă un șir este repetat, de exemplu, de 5 ori, atunci o instanță a acestui șir rămâne în listă pentru comparație, iar a patra este trimisă în lista „repetări”. Există repetări diferite pentru fiecare listă de cuvinte.

Fără acest parametru, șirurile sunt comparate ca în perechi. De exemplu, la compararea șirurilor de potrivire, dacă există 2 șiruri identice în prima listă și încă 3 șiruri similare în a doua listă, atunci rezultatul va fi doar două șiruri, deoarece Linia a 3-a nu a găsit o pereche cu care să se potrivească. Dacă pentru acest exemplu comutăm comparațiile la șiruri care nu se potrivesc, atunci rezultatul care nu se potrivește va include o linie din lista 2-a, deoarece nu se potriveste cu nimic.

Folosind o comparație unică de șiruri, puteți găsi șiruri duplicate într-o listă. Pentru a face acest lucru, puteți, de exemplu, să completați doar fișierul „Lista 1.txt” cu linii și să comparați cu dosar gol„List 2.txt” și apoi în fișierul „RepeatSp1.txt” vor apărea liniile duplicate din lista 1.

Se repetă numai pentru rândurile din rezultat- funcționează numai împreună cu compararea pe șiruri unice. Fără acest parametru, toate liniile duplicate sunt incluse în listele repetate. Dacă este activat, atunci doar liniile prezente în rezultat vor fi repetate. Numărul de linii incluse în repetări este similar și egal cu numărul de repetări din lista inițială minus 1.

Eliminați punctele și virguleleŞi Eliminați toate spațiile- sunt pur și simplu șterse și atât.

După instalarea suplimentului, veți avea filă nouă cu o comandă de apelare a funcției. Când faceți clic pe comandă Compararea intervalului 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. Comparațiile de tabel pot fi făcute pe una, două sau trei coloane simultan.

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

Pentru a compara tabele, trebuie să faceți următoarele:

  • Specificați intervalele de tabel.
  • Plasați o casetă de selectare (marca de bifare/pasăre) sub intervalul selectat de tabele dacă tabelul include un antet (linia de titlu).
  • Selectați coloanele din tabelele din stânga și din dreapta pentru comparație (dacă intervalele de tabel nu includ titluri, coloanele vor fi numerotate).
  • Specificați tipul de comparație.
  • Selectați o opțiune pentru afișarea rezultatelor.

Tip de comparație de tabel

Programul vă permite să selectați mai multe tipuri de comparații de tabele:

Găsiți rânduri dintr-un tabel care lipsesc dintr-un alt tabel

La alegere de acest tip Programul de comparare caută rânduri dintr-un tabel care lipsesc în altul. Dacă potriviți tabele pe mai multe coloane, rezultatul va fi rânduri care au o diferență în cel puțin una dintre coloane.

Găsiți șiruri potrivite

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

Un exemplu de program în acest mod prezentată în dreapta în imagine.

Potriviți tabele în funcție de selecție

În acest mod de comparare, vizavi de fiecare rând din primul tabel (selectat ca principal), sunt copiate datele rândului de potrivire al celui de-al doilea tabel. Dacă nu există rânduri care se potrivesc, rândul de lângă tabelul principal rămâne gol.

Compararea tabelelor cu patru sau mai multe coloane

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

  • Creați o coloană goală în tabelele dvs.
  • În coloane noi folosind formula = CONECTAȚI combinați coloanele cu care doriți să le comparați.

În acest fel, veți ajunge cu 1 coloană care conține valorile mai multor coloane. Ei bine, știi deja cum să compari o coloană.

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

  1. 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.
  2. 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 complet tabel este tabelul de pe foaie 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:

  1. 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);
  2. Folosind = IFERROR(INDEX(Lista, MATCH(SMALL(COUNTIF(Lista, "<"&Список); СТРОКА()-СТРОКА($B$4)); СЧЁТЕСЛИ(Список; "<"&Список); 0));"") , unde este Listă