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
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ă cărți 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 se schimbă linie cu linie. Diferențele dintre celule sunt afișate într-un aspect ușor de citit al grilei, astfel:
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\u003e 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.
Analizează un registru de lucru
Analiza registrului de lucru comanda creează un raport interactiv care afișează 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ă grafică interactivă 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 de relații, 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, afișează 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 \u200b\u200bacelaș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 linkurilor dintr-o celulă selectată către celule 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 celulare 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 Vedeți linkurile dintre celule.
Curățați formatul excesiv al 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 a reduce considerabil 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 filă pentru a adăuga parole, care vor fi salvate pe computer. Aceste parole sunt criptate și accesibile numai de dvs.
Vă va dura aproximativ 10 minute să citiți acest articol. Î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 contigue sau nu contigue, 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 vă puteți imagina, 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 Foaia 2 ș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 duplicat folosind formule
Opțiunea A: ambele coloane sunt pe aceeași foaie
Prompt: În tabelele 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 care nu sunt goale în 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. Este destul de ineficient și consumator de timp să vizualizați manual toate înregistrările duplicate dintr-un tabel. 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 a face acest lucru, 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 din meniul contextual Introduce (Introduce):
Dați nume coloanelor, 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 O.K.
Atât, acum vedeți doar acele elemente ale coloanei Acare 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 A, faceți clic pe simbolul filtrului din coloană ÎNcare 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)\u003e Selectați și filtrați (Sortare și filtrare)\u003e 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 + 1pentru 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ă) înseamnă că toate opțiunile de formatare pot fi configurate în același timp.
Acum, cu siguranță nu veți rata 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 în meniul contextual Ștergeți rândul (Ștergeți linia):
Clic O.Kcâ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ă A, fa asta:
După cum puteți vedea, eliminarea duplicatelor din două coloane din Excel folosind formule nu este atât de dificilă.
Întrebarea utilizatorului
Salut!
Am o singură sarcină și, pentru a treia zi, mi-am aruncat creierul - nu știu cum să o finalizez. Există 2 tabele (aproximativ 500-600 de rânduri fiecare), trebuie să luați o coloană cu numele produsului dintr-un tabel și să o comparați cu numele produsului din altul, iar dacă produsele se potrivesc, copiați și înlocuiți valoarea din tabelul 2 în tabelul 1. Explicați în mod confuz , dar cred că veți înțelege problema din fotografie ( aproximativ : fotografie tăiată prin cenzură, informații personale).
Multumesc anticipat. Andrey, Moscova.
Bună ziua tuturor!
Ceea ce ați descris se referă la sarcini destul de populare, care sunt relativ ușor și rapid de rezolvat folosind Excel. Este suficient să introduceți cele două tabele în program și să utilizați funcția VLOOKUP. Despre munca ei de mai jos ...
Un exemplu de lucru cu funcția VLOOKUP
De exemplu, am luat două tablete 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 nicio valoare și scriem formula:
\u003d VLOOKUP (A2, $ E $ 1: $ F $ 7,2, FALS)
A2 - valoarea din prima coloană a primului tabel (ceea ce vom căuta în prima coloană a celui de-al doilea tabel);
$ E $ 1: $ F $ 7 - al doilea tabel complet selectat (în care dorim să găsim și să copiem ceva). Acordați atenție pictogramei „$” - este necesar ca celulele celui de-al doilea tabel selectat să nu se schimbe atunci când copiați formula;
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 2 sau 3);
MINCINTE - căutăm o potrivire exactă (altfel va fi înlocuit 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 stabili și prețul pentru alte nume de produse, trebuie doar să 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 substituite. În general, este clar că tabelele 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, poate dura mult timp pentru a compara tabelele. În aceste cazuri, merită luate în considerare fie alte formule, fie soluții complet diferite (fiecare caz este individual).
Atât, noroc!
În fiecare lună, angajatul de resurse umane primește o listă de angajați împreună cu salariile acestora. Copiază lista într-o nouă foaie a registrului de lucru Excel. Sarcina este următoarea: comparați salariile angajaților, care s-au schimbat față de luna precedentă. Pentru a face acest lucru, trebuie să efectuați o comparație a datelor în Excel pe diferite foi. Să folosim formatarea condiționată. Astfel, nu numai că vom găsi automat toate diferențele în valorile celulelor, dar le vom evidenția și cu culoare.
Comparați două foi în Excel
O firmă poate avea mai mult de o sută de angajați, dintre care unii pleacă, alții își găsesc locuri de muncă, alții pleacă în concediu sau concediu medical etc. Ca urmare, poate fi dificil să comparați datele salariale. De exemplu, numele angajaților vor fi în mod constant într-o ordine diferită. Cum se face compararea a două tabele Excel pe foi diferite?
Formatarea condiționată ne va ajuta să rezolvăm această sarcină dificilă. De exemplu, să luăm datele pentru februarie și martie, așa cum se arată în figură:
Pentru a găsi modificări pe fișele salariale:
După introducerea tuturor condițiilor de formatare, Excel a evidențiat automat acei angajați ale căror salarii s-au modificat față de luna precedentă.
Principiul comparării a două game de date în Excel pe foi diferite:
Într-o anumită stare, funcția MATCH este esențială. Primul său argument conține o pereche de valori care ar trebui găsite pe foaia sursă a lunii următoare, adică „martie”. Un interval scanat este definit ca combinând valorile intervalelor specificate de nume în perechi. Astfel, comparația șirului se realizează pe două motive - numele de familie și salariul. Pentru potrivirile găsite, se returnează un număr, ceea ce este în esență adevărat pentru Excel. Prin urmare, ar trebui să utilizați funcția \u003d NOT (), care vă permite să înlocuiți valoarea TRUE cu FALSE. În caz contrar, formatarea va fi aplicată celulelor ale căror valori se potrivesc. Pentru fiecare pereche de valori care nu a fost găsită (adică o nepotrivire) și B2 și $ C2 din gama Nume și salariu, MATCH returnează o eroare. Valoarea erorii nu este o valoare booleană. Prin urmare, folosim funcția IFERROR, care va atribui o valoare logică pentru fiecare eroare - ADEVĂRAT. Acest lucru promovează alocarea unui nou format numai celulelor fără a se potrivi cu valorile salariale în raport cu luna următoare, martie.
Poate că toți cei care lucrează cu date în Excel se confruntă cu întrebarea cum să comparați două coloane în Excel pentru coincidențe și diferențe. Există mai multe modalități de a face acest lucru. Să aruncăm o privire mai atentă la fiecare dintre ele.
Cum se compară două coloane în rând Excel 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 pe 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, îl 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:
\u003d IF (A2 \u003d B2, „Potrivire”, „”)
Formula care determină diferențele dintre datele a două coloane dintr-un rând va arăta astfel:
\u003d IF (A2<>B2; "Nu se potriveste"; "")
Putem încadra verificarea pentru potriviri și diferențe între două coloane într-un rând într-o singură formulă:
\u003d IF (A2 \u003d B2, „Potrivire”, „Nu se potrivește”)
\u003d IF (A2<>B2; "Nu se potriveste"; "Meci")
Un exemplu de rezultat al calculului ar putea arăta astfel:
Pentru a compara în mod sensibil datele din două coloane ale aceluiași rând, folosiți formula:
\u003d IF (MATCH (A2, B2), „Match”, „Unique”)
Cum se compară mai multe coloane pentru potriviri într-un singur rând Excel
Excel are capacitatea de a compara date în mai multe coloane dintr-un 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 într-un singur 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 de a găsi rânduri în care valorile sunt aceleași în toate coloanele. Funcțiile Excel și. Formula pentru determinarea meciurilor va fi următoarea:
\u003d IF (ȘI (A2 \u003d B2; A2 \u003d C2); „Potrivire”; „„)
Dacă tabelul nostru are multe coloane, atunci va fi mai ușor să folosiți funcția în combinație cu:
\u003d IF (COUNTIF ($ A2: $ C2; $ A2) \u003d 3; „Potriviri”; „„)
În formulă, „5” este numărul de coloane din tabelul 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 într-un rând în oricare două coloane ale unui tabel
Imaginați-vă că sarcina noastră este de a identifica dintr-un tabel cu date în mai multe coloane acele rânduri în care datele coincid 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:
\u003d IF (SAU (A2 \u003d B2; B2 \u003d C2; A2 \u003d 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 de a utiliza o funcție.
\u003d IF (COUNTIF (B2: D2; A2) + COUNTIF (C2: D2; B2) + (C2 \u003d D2) \u003d 0; "Șir unic"; "Șir nu unic")
\u003d IF (COUNTIF ($ B: $ B; $ A5) \u003d 0; „Nu există 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 dvs. constă 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 se compară două coloane în Excel pentru potriviri și se evidențiază cu culoare
Când căutăm potriviri între două coloane în Excel, este posibil să fie nevoie să vizualizăm potrivirile sau diferențele găsite în 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 potriviri în mai multe coloane din Excel
În cazurile în care trebuie să găsim potriviri în mai multe coloane, atunci pentru aceasta avem nevoie de:
- Selectați coloane cu date în care doriți să calculați potrivirile;
- În fila „Acasă” din Bara de instrumente, faceți clic pe elementul de meniu „Formatare condiționată” -\u003e „Reguli de selecție a celulei” -\u003e „Valori duplicate”;
- În caseta de dialog pop-up, selectați elementul „Duplicate” în lista verticală stângă, în lista verticală dreaptă, selectați ce culoare vor fi evidențiate valorile duplicate. Faceți clic pe butonul „OK”:
- După aceea, coloana selectată va fi evidențiată cu culoarea potrivită:
Găsiți și evidențiați rânduri potrivite în Excel
Găsirea celulelor potrivite cu date în două sau mai multe coloane și găsirea potrivirilor pentru rânduri întregi cu date sunt concepte diferite. Rețineț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 potrivite, iar în dreapta, am găsit linii întregi de date repetate.
Să vedem cum să găsim rânduri potrivite într-un tabel:
- În dreapta tabelului cu date, creați o coloană auxiliară, în care, opus fiecărui rând cu date, punem o formulă care combină toate valorile rândului tabelului într-o singură celulă:
\u003d A2 și B2 și C2 și D2
În coloana auxiliară, veți vedea datele combinate ale tabelului:
Acum, pentru a determina rândurile potrivite din tabel, urmați acești pași:
- Selectați o zonă cu date în coloana auxiliară (în exemplul nostru, aceasta este o gamă de celule E2: E15 );
- În fila „Acasă” din Bara de instrumente, faceți clic pe elementul de meniu „Formatare condiționată” -\u003e „Reguli de selecție a celulei” -\u003e „Valori duplicate”;
- În caseta de dialog pop-up, selectați „Duplicare” în lista verticală stângă, în lista verticală dreaptă, 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ă:
