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 efectua toate aceste sarcini și multe altele. Fila Întrebare din panglica Excel are butoane pentru comenzile descrise mai jos.
Dacă nu vedeți Î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 start 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 de 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” din 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. Utilizaț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”, care î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. Folosește 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.
Trebuie să comparați două fișiere Microsoft Excel? Iată două moduri ușoare de a face acest lucru.
Există multe motive pentru care ați putea dori să luați un document Excel și să-l comparați cu altul. Aceasta poate fi o sarcină consumatoare de timp
este nevoie de multă concentrare, dar există modalități de a-ți ușura viața.
Trebuie să aruncați o privire atentă cu mâna sau doriți ca Excel să facă ceva greu
în numele dvs., iată două moduri simple de a compara mai multe foi.
Cum se compară fișierele Excel
Excel permite utilizatorilor să afișeze două versiuni ale unui document simultan pentru a le distinge rapid:
- Mai întâi, deschideți registrele de lucru pe care doriți să le comparați.
- Schimba cu Vizualizare> Fereastră> Vedere laterală.
Comparați fișierele Excel după ochi
Pentru a începe, deschideți Excel și toate registrele de lucru pe care doriți să le comparați. Putem folosi aceeași tehnică pentru a compara foile din același document
sau fișiere complet diferite.
Dacă se primesc mai multe foi din aceeași carte, aceasta trebuie separată în prealabil. Pentru a face acest lucru, mergeți la Vizualizare> Fereastră> Fereastră nouă.
Acest lucru nu va împărți definitiv foile individuale, ci doar va deschide o nouă instanță a documentului dvs.
Acest meniu va lista toate tabelele care sunt deschise în prezent. Dacă aveți doar două deschise, acestea vor fi selectate automat.
Faceți alegerea și faceți clic Bun... Veți vedea ambele tabele care apar pe ecran.
Dacă este mai convenabil, puteți folosi Aranjați totul butonul pentru a comuta între configurația verticală și orizontală.
O opțiune importantă de care trebuie să fii conștient este Defilare sincronă comutarea.
Activarea acestei opțiuni asigură că atunci când o fereastră este derulată, cealaltă se va muta în sincronizare. Acest lucru este important dacă lucrați cu o masă mare.
și vrei să continui să verifici unul împotriva celuilalt. Dacă dintr-un motiv oarecare cele două foi nu sunt aliniate, trebuie doar să apăsați Resetați poziția ferestrei.
Comparați fișierele Excel folosind formatarea condiționată
În multe cazuri, cel mai bun mod de a compara două foi de calcul ar putea fi pur și simplu să le afișați pe ecran în același timp. Cu toate acestea, este posibil să automatizezi oarecum procesul.
Utilizarea formatării condiționate
Putem verifica Excel pentru discrepanțe între cele două foi. Acest lucru vă poate economisi mult timp dacă tot ce trebuie să găsiți este diferențele dintre o versiune și cealaltă.
Pentru această metodă, trebuie să ne asigurăm că cele două foi cu care lucrăm fac parte din același registru de lucru. Pentru a face acest lucru, faceți clic dreapta pe numele foii pe care doriți să o transferați și selectați Mutați sau copiați.
Aici puteți utiliza meniul drop-down pentru a decide în ce document va fi inserat.
Selectați toate celulele care sunt completate în foaia de lucru unde doriți să fie evidențiate diferențele. O modalitate rapidă de a face acest lucru este să faceți clic pe celula din colțul din stânga sus și apoi să utilizați comanda rapidă
Ctrl + Shift + End.
Schimba cu Acasă> Stiluri> Formatare condiționată> Regulă nouă.
Selectați Utilizați o formulă pentru a determina ce celule să formatațiși introduceți următoarele:
A1sheet_name! A1
Nu uitați să puneți „sheet_name” pentru orice alt nume de foaie. Această formulă efectuează validarea numai atunci când o celulă de pe o foaie nu se potrivește exact cu celula corespunzătoare din altă foaie și semnalează fiecare instanță.
Puteți vedea rezultatele mai sus. Toate celulele care conțin modificări au fost evidențiate cu roșu, ceea ce face rapidă și ușoară compararea celor două foi.
Lasă Excel să facă treaba grea
Tehnica de mai sus demonstrează o modalitate prin care puteți lăsa Excel să facă ceva grele. Chiar dacă acordați o atenție deosebită, sunt șanse să pierdeți o schimbare dacă faceți această sarcină manual. Cu formatarea condiționată, puteți fi sigur că nimic nu se strecoară în rețea
Excel este bun la lucrul repetitiv și detaliat. Odată ce ați obținut capacitățile sale, puteți economisi puțin timp și efort folosind tehnici precum formatarea condiționată și puțină ingeniozitate.
Aveți vreun sfat despre compararea documentelor în Excel? Sau aveți nevoie de ajutor cu procesele din acest ghid? Oricum, de ce să nu te alăture conversației în secțiunea de comentarii de mai jos?
Întrebarea utilizatorului
Salut!
Am o sarcină și pentru a treia zi m-am încurcat - 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 o coloană cu numele unui produs dintr-un tabel și să o comparați cu numele unui produs din altul, iar dacă produsele se potrivesc, copiați și înlocuiți valoarea din tabelul 2 în tabelul 1. Explicată în mod confuz, dar cred că veți înțelege problema din fotografie ( aproximativ : fotografie tăiată de cenzură, încă informații personale).
Vă mulțumesc anticipat. Andrei, Moscova.
Bună ziua tuturor!
Ceea ce ați descris se referă la sarcini destul de populare care sunt relativ simple și rapid de rezolvat folosind Excel. Este suficient să introduceți cele două tabele în program și să utilizați funcția CĂUTARE V. Despre munca ei mai jos...
Un exemplu de lucru cu funcția VLOOKUP
Ca exemplu, am luat două farfurii mici, acestea sunt prezentate î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 se completează 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 pe prima placă. Se pare că sarcina este simplă...
Cum să o facă...
Punem indicatorul mouse-ului în celulă B2- adică în prima celulă a coloanei, unde nu avem valoare și scriem formula:
= CĂUTARE V (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- al doilea tabel complet selectat (în care vrem să găsim și să copiem ceva). Acordați atenție pictogramei „$” - este necesar pentru ca celulele celui de-al doilea tabel selectat să nu se schimbe atunci când copiați formula;
2 - numărul coloanei din care vom copia valoarea (rețineți că cel de-al doilea tabel nostru 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);
MINCIUT- cautam o potrivire exacta (in caz contrar se va inlocui primul similar, ceea ce evident nu ne convine).
De fapt, puteți ajusta formula gata făcută pentru a se potrivi nevoilor 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 introdus în modul automat. Totul merge!
Pentru a seta prețul pentru alte nume de produse, trageți (copiați) formula în alte celule. Vezi exemplul de mai jos.
După aceea, după cum puteți vedea, primele coloane ale tabelelor vor fi comparate: din rândurile în care se potrivesc valorile celulei, datele necesare vor fi copiate și înlocuite. În general, este clar că mesele pot fi mult mai mari!
Notă: 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 mult timp. În aceste cazuri, merită luate în considerare fie alte formule, fie soluții complet diferite (fiecare caz este individual).
Asta e tot, succes!
După instalarea suplimentului, veți avea o filă nouă 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 pe 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 al doilea.
Pentru a compara tabele, urmați acești pași:
- Specificați intervalele de tabel.
- Setați o casetă de selectare (căsuță de selectare / casetă de selectare) sub intervalul selectat de tabele dacă tabelul include un antet (rând de antet).
- Selectați coloanele din tabelele din stânga și din dreapta prin care va fi efectuată comparația (dacă intervalele de tabele nu includ anteturi, coloanele vor fi numerotate).
- Specificați tipul de comparație.
- Selectați opțiunea pentru afișarea rezultatelor.
Tip de comparație de tabele
Programul vă permite să selectați mai multe tipuri de comparații de tabel:
Găsiți rânduri dintr-un tabel care lipsesc într-un alt tabel
Când este selectat acest tip de comparație, programul caută rânduri dintr-un tabel care lipsesc în altul. Dacă comparați tabelele pe mai multe coloane, atunci rezultatul muncii vor fi rânduri în care există o diferență în cel puțin una dintre coloane.
Găsiți linii care se potrivesc
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 funcționare a programului în acest mod este afișat în partea dreaptă a imaginii.
Harta tabele pe baza selectate
Î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 pe patru sau mai multe coloane
Dacă nu aveți funcționalitatea programului și aveți nevoie să potriviți tabelele în patru sau mai multe coloane, atunci puteți ieși din situație după cum urmează:
- Creați o coloană goală în tabele.
- Pe coloanele noi folosind formula = CUPLAREîmbinați coloanele pe 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ă potriviți o coloană.
Poate că toți cei care lucrează cu date în Excel se confruntă cu întrebarea cum să compare două coloane în Excel pentru coincidențe și diferențe. Există mai multe moduri de a face acest lucru. Să aruncăm o privire mai atentă la fiecare dintre ele.
Cum se compară două coloane în Excel rând cu rând
Când comparați două coloane cu date, este adesea necesar să comparați datele din fiecare rând separat pentru coincidențe sau diferențe. Putem face această analiză folosind funcția. Să vedem cum funcționează în exemplele de mai jos.
Exemplul 1. Cum se compară două coloane pentru potriviri și diferențe într-un rând
Pentru a compara datele din fiecare rând de două coloane din Excel, vom scrie o formulă simplă. Formula trebuie inserată în fiecare rând din coloana alăturată, lângă tabelul în care se află datele de bază. După ce am creat o formulă pentru primul rând al tabelului, o putem întinde / copia în restul rândurilor.
Pentru a verifica dacă două coloane din același rând conțin aceleași date, avem nevoie de formula:
= DACA (A2 = B2, „Potrivire”, „”)
Formula care determină diferențele dintre datele a două coloane dintr-un rând va arăta astfel:
= DACA (A2<>B2; "Nu se potriveste"; "")
Putem potrivi verificarea pentru potriviri și diferențe între două coloane pe o singură linie într-o singură formulă:
= DACA (A2 = B2, „Potrivit”, „Nu se potrivește”)
= DACA (A2<>B2; "Nu se potriveste"; „Potriviți”)
Un exemplu de rezultat de calcul ar putea arăta astfel:
Pentru a compara datele din două coloane ale aceluiași rând, ținând cont de majuscule și minuscule, utilizați formula:
= DACĂ (POTIRE (A2, B2), „Potrivire”, „Unic”)
Cum să comparați mai multe coloane pentru potriviri într-un singur rând Excel
Excel are capacitatea de a compara datele din mai multe coloane ale aceluiași rând, conform următoarelor criterii:
- Găsiți rânduri cu aceleași valori în toate coloanele tabelului;
- Găsiți rânduri cu aceleași valori în oricare două coloane ale tabelului;
Exemplul 1. Cum să găsiți potriviri pe un rând în mai multe coloane ale unui tabel
Să ne imaginăm că tabelul nostru este format din mai multe coloane de date. Sarcina noastră este să găsim rânduri în care valorile sunt aceleași în toate coloanele. Funcțiile Excel și. Formula de determinare a potrivirilor va fi următoarea:
= DACĂ (ȘI (A2 = B2; A2 = C2); „Potrivire”; „“)
Dacă tabelul nostru are o mulțime de coloane, atunci va fi mai ușor să utilizați funcția în combinație cu:
= DACĂ (COUNTIF ($ A2: $ C2; $ A2) = 3; „Potrivire”; „“)
În formulă, „5” este numărul de coloane din tabel pentru care am creat formula. Dacă tabelul dvs. are mai multe sau mai puține coloane, atunci această valoare ar trebui să fie egală cu numărul de coloane.
Exemplul 2. Cum să găsiți potriviri pe un rând în oricare două coloane ale unui tabel
Imaginați-vă că sarcina noastră este să identificăm dintr-un tabel cu date în mai multe coloane acele rânduri în care datele sunt aceleași sau se repetă în cel puțin două coloane. Funcțiile și ne vor ajuta în acest sens. Să scriem o formulă pentru un tabel format din trei coloane de date:
= DACA (SAU (A2 = B2; B2 = C2; A2 = C2); „Potrivire”; „“)
În cazurile în care există prea multe coloane în tabelul nostru, formula noastră cu o funcție va fi foarte mare, deoarece în parametrii săi trebuie să specificăm criteriile de potrivire între fiecare coloană a tabelului. O modalitate mai ușoară, în acest caz, este să utilizați o funcție.
= DACĂ (COUNTIF (B2: D2; A2) + COUNTIF (C2: D2; B2) + (C2 = D2) = 0; „Șir unic”; „Șir nu unic”)
= DACĂ (COUNTIF ($ B: $ B; $ A5) = 0; „Fără potriviri în coloana B”; „Există potriviri în coloana B”)
Această formulă verifică valorile din coloana B pentru a vedea dacă se potrivesc cu datele din celulele din coloana A.
Dacă tabelul este format dintr-un număr fix de rânduri, puteți specifica un interval clar în formulă (de exemplu, $ B2: $ B10). Acest lucru va accelera formula.
Cum să compari două coloane în Excel pentru potriviri și să evidențiezi cu culoare
Când căutăm potriviri între două coloane în Excel, este posibil să fie nevoie să vizualizăm potrivirile găsite sau diferențele de date, de exemplu, folosind evidențierea culorilor. Cel mai simplu mod de a evidenția coincidențele și diferențele cu culoarea este să utilizați „Formatarea condiționată” în Excel. Să vedem cum să facem acest lucru în exemplele de mai jos.
Găsiți și evidențiați potrivirile în mai multe coloane în Excel
În cazurile în care trebuie să găsim potriviri în mai multe coloane, atunci pentru aceasta avem nevoie de:
- Selectați coloanele cu date în care doriți să calculați potrivirile;
- În fila „Acasă” din Bara de instrumente, faceți clic pe elementul de meniu „Formatizare condiționată” -> „Reguli de selecție a celulelor” -> „Valori duplicate”;
- În caseta de dialog pop-up, selectați elementul „Duplicat” din lista verticală din stânga, în lista verticală din dreapta, selectați ce culoare vor fi evidențiate valorile duplicate. Faceți clic pe butonul „OK”:
- După aceea, în coloana selectată, culoarea potrivită va fi evidențiată:
Găsiți și evidențiați rândurile care se potrivesc în Excel
Căutarea celulelor care se potrivesc cu date în două sau mai multe coloane și găsirea potrivirilor pentru rânduri întregi cu date sunt concepte diferite. Observați cele două tabele de mai jos:
Tabelele de mai sus conțin aceleași date. Diferența lor este că în exemplul din stânga am căutat celule care se potrivesc, iar în dreapta am găsit linii întregi de date care se repetă.
Să vedem cum să găsim rânduri potrivite într-un tabel:
- În dreapta tabelului cu date, creați o coloană auxiliară, în care, vizavi de fiecare rând cu date, punem o formulă care combină toate valorile rândului tabelului într-o singură celulă:
= A2 & B2 & C2 & D2
În coloana auxiliară, veți vedea datele combinate din tabel:
Acum, pentru a determina rândurile care se potrivesc din tabel, urmați acești pași:
- Selectați o zonă cu date în coloana auxiliară (în exemplul nostru, acesta este un interval de celule E2: E15 );
- În fila „Acasă” din Bara de instrumente, faceți clic pe elementul de meniu „Formatizare condiționată” -> „Reguli de selecție a celulelor” -> „Valori duplicate”;
- În caseta de dialog pop-up, selectați „Duplicate” în lista derulantă din stânga, în lista verticală din dreapta, selectați ce culoare vor fi evidențiate valorile duplicate. Faceți clic pe butonul „OK”:
- După aceea, liniile duplicate vor fi evidențiate în coloana selectată:
