Cuprins
Avem două tabele (de exemplu, versiunile vechi și noi ale listei de prețuri), pe care trebuie să le comparăm și să găsim rapid diferențele:
Este imediat clar că s-a adăugat ceva la noua listă de prețuri (curmale, usturoi…), ceva a dispărut (mure, zmeură…), prețurile s-au schimbat la unele mărfuri (smochine, pepeni…). Trebuie să găsiți și să afișați rapid toate aceste modificări.
Pentru orice sarcină în Excel, există aproape întotdeauna mai multe soluții (de obicei 4-5). Pentru problema noastră, pot fi folosite multe abordări diferite:
- funcţie VPR (CĂUTARE V) — căutați numele produselor din noua listă de prețuri în cea veche și afișați prețul vechi lângă cel nou, apoi observați diferențele
- îmbina două liste într-una și apoi construiește un tabel pivot pe baza acestuia, unde diferențele vor fi clar vizibile
- utilizați programul de completare Power Query pentru Excel
Să le luăm pe toate în ordine.
Metoda 1. Compararea tabelelor cu funcția VLOOKUP
Dacă nu sunteți complet familiarizat cu această caracteristică minunată, atunci mai întâi uitați-vă aici și citiți sau urmăriți un tutorial video despre ea - salvați-vă câțiva ani de viață.
De obicei, această funcție este folosită pentru a extrage date dintr-un tabel în altul prin potrivirea unui parametru comun. În acest caz, îl vom folosi pentru a împinge prețurile vechi în noul preț:
Acele produse, față de care s-a dovedit eroarea #N/A, nu sunt în vechea listă, adică au fost adăugate. Modificările de preț sunt, de asemenea, clar vizibile.
Pro-uri această metodă: simplă și clară, „clasic al genului”, după cum se spune. Funcționează în orice versiune de Excel.
Contra este și acolo. Pentru a căuta produse adăugate la noua listă de prețuri, va trebui să faceți aceeași procedură în direcția opusă, adică să ridicați prețuri noi la prețul vechi cu ajutorul VLOOKUP. Dacă mâine dimensiunile tabelelor se schimbă, atunci formulele vor trebui ajustate. Ei bine, și pe mese cu adevărat mari (> 100 de mii de rânduri), toată această fericire va încetini decent.
Metoda 2: Compararea tabelelor folosind un pivot
Să ne copiem tabelele unul sub altul, adăugând o coloană cu numele listei de prețuri, pentru ca mai târziu să înțelegeți din ce listă ce rând:
Acum, pe baza tabelului creat, vom crea un rezumat Insert – PivotTable (Inserați — Tabel Pivot). Să aruncăm un câmp Produs la zona liniilor, câmp Preț la zona coloanei și câmpul Цena in gama:
După cum puteți vedea, tabelul pivot va genera automat o listă generală a tuturor produselor din listele de prețuri vechi și noi (fără repetări!) și va sorta produsele în ordine alfabetică. Puteți vedea clar produsele adăugate (nu au prețul vechi), produsele eliminate (nu au prețul nou) și modificările de preț, dacă există.
Totalurile generale dintr-un astfel de tabel nu au sens și pot fi dezactivate în filă Constructor – Totaluri generale – Dezactivați pentru rânduri și coloane (Design - Totaluri mari).
Dacă prețurile se modifică (dar nu și cantitatea de mărfuri!), atunci este suficient să actualizați pur și simplu rezumatul creat făcând clic dreapta pe el - Reîmprospăta.
Pro-uri: Această abordare este cu un ordin de mărime mai rapidă cu tabele mari decât CĂUTARE V.
Contra: trebuie să copiați manual datele unul sub celălalt și să adăugați o coloană cu numele listei de prețuri. Dacă dimensiunile meselor se schimbă, atunci trebuie să faci totul din nou.
Metoda 3: Compararea tabelelor cu Power Query
Power Query este un program de completare gratuit pentru Microsoft Excel care vă permite să încărcați date în Excel din aproape orice sursă și apoi să transformați aceste date în orice mod dorit. În Excel 2016, acest program de completare este deja încorporat implicit în filă Date (Date), iar pentru Excel 2010-2013 trebuie să îl descărcați separat de pe site-ul Microsoft și să îl instalați - obțineți o filă nouă Interogare de alimentare.
Înainte de a încărca listele noastre de prețuri în Power Query, acestea trebuie mai întâi convertite în tabele inteligente. Pentru a face acest lucru, selectați intervalul cu date și apăsați combinația de pe tastatură Ctrl+T sau selectați fila de pe panglică Acasă – Formatați ca tabel (Acasă — Format ca tabel). Numele tabelelor create pot fi corectate pe filă Constructor (Voi părăsi standardul Tabelul 1 и Tabelul 2, care sunt obținute implicit).
Încărcați vechiul preț în Power Query folosind butonul Din masă/gamă (De la masă/gamă) din filă Date (Data) sau din filă Interogare de alimentare (în funcție de versiunea de Excel). După încărcare, vom reveni înapoi la Excel din Power Query cu comanda Închideți și încărcați - Închideți și încărcați în... (Închidere și încărcare — Închidere și încărcare la...):
… iar în fereastra care apare apoi selectați Doar creați o conexiune (Numai conexiune).
Repetați același lucru cu noua listă de prețuri.
Acum să creăm o a treia interogare care va combina și compara datele din cele două anterioare. Pentru a face acest lucru, selectați în Excel din filă Date – Obține date – Combină cereri – Combina (Date — Obțineți date — Îmbinați interogări — Îmbinați) sau apăsați butonul Combina (Combina) fila Interogare de alimentare.
În fereastra de alăturare, selectați tabelele noastre din listele derulante, selectați coloanele cu numele bunurilor din ele, iar în partea de jos, setați metoda de alăturare - Complet extern (Complet exterior):
După ce faceți clic pe OK ar trebui să apară un tabel cu trei coloane, unde în a treia coloană trebuie să extindeți conținutul tabelelor imbricate folosind săgeata dublă din antet:
Ca rezultat, obținem îmbinarea datelor din ambele tabele:
Desigur, este mai bine să redenumiți numele coloanelor din antet făcând dublu clic pe altele mai ușor de înțeles:
Și acum cel mai interesant. Accesați fila Adăugați coloana (Adăugați o coloană) și faceți clic pe buton Coloana condiționată (Coloană condiționată). Și apoi, în fereastra care se deschide, introduceți mai multe condiții de testare cu valorile lor de ieșire corespunzătoare:
Rămâne să dai clic pe OK și încărcați raportul rezultat în Excel folosind același buton închideți și descărcați (Închidere și încărcare) fila Acasă (Acasă):
Frumuseţe.
Mai mult, dacă în viitor apar modificări în listele de prețuri (se adaugă sau se șterg linii, se schimbă prețurile etc.), atunci va fi suficient doar să ne actualizăm solicitările cu o comandă rapidă de la tastatură Ctrl+Alt+F5 sau prin buton Reîmprospătați totul (Actualizează tot) fila Date (Data).
Pro-uri: Poate cel mai frumos și mai convenabil mod dintre toate. Funcționează inteligent cu mese mari. Nu necesită modificări manuale la redimensionarea tabelelor.
Contra: necesită instalarea suplimentului Power Query (în Excel 2010-2013) sau Excel 2016. Numele coloanelor din datele sursă nu trebuie modificate, altfel vom primi eroarea „Column such and such not found!” când încercați să actualizați interogarea.
- Cum să colectați date din toate fișierele Excel dintr-un folder dat folosind Power Query
- Cum să găsiți potriviri între două liste în Excel
- Îmbinarea a două liste fără duplicate