Diferite metode de potrivire a datelor în Excel
Există diverse metode pentru a potrivi datele în Excel, dacă vrem să potrivim datele din aceeași coloană, să presupunem că dorim să verificăm duplicitatea, putem folosi formatarea condiționată din fila acasă sau altceva dacă vrem să potrivim datele în două sau mai multe coloane diferite putem folosi funcții condiționale, cum ar fi funcția if.
- Metoda # 1 - Utilizarea funcției Vlookup
- Metoda # 2 - Utilizarea funcției Index + Match
- Metoda # 3 - Creați-vă propria valoare de căutare
Acum, să discutăm în detaliu fiecare dintre metode
Puteți descărca acest șablon Excel Match Data aici - Șablon Match Data Excel# 1 - Potrivirea datelor folosind funcția VLOOKUP
VLOOKUP nu este folosit doar pentru a obține informațiile solicitate din tabelul de date, ci poate fi folosit și ca instrument de reconciliere. Când vine vorba de reconciliere sau de potrivire a datelor, formula VLOOKUP conduce tabelul.
Pentru un exemplu, uitați-vă la tabelul de mai jos.
Aici avem două tabele de date, primul este Data 1 și al doilea este Data 2.
Acum trebuie să împăcăm dacă datele din două tabele se potrivesc sau nu. Primul mod de potrivire a datelor este funcția SUM în Excel la două tabele pentru a obține vânzările totale.
Date 1 - Tabel
Date 2 - Tabel
Am aplicat funcția SUMĂ atât pentru coloana Sumă vânzare a tabelului. La pasul de început în sine, am obținut diferența de valori. Tabelul cu date 1 care arată vânzările totale de 2,16,214 și tabelul cu date 2 care arată vânzările totale de 2,10,214 .
Acum trebuie să examinăm acest lucru în detaliu. Deci, să aplicăm funcția VLOOKUP pentru fiecare dată.
Selectați tabelul ca interval de date 1 .
Avem nevoie de datele din a doua coloană, iar domeniul de căutare este FALS, adică potrivire exactă.
Ieșirea este dată mai jos:
În celula următoare deduceți valoarea inițială cu valoarea de sosire.
După deducere, obținem rezultatul ca zero.
Acum copiați și lipiți formula în toate celulele pentru a obține valorile varianței.
În celulele G6 și G12 am obținut diferențele.
În Data 1 avem 12104 pentru Data 04-Mar-2019 și în Data 2 avem 15104 pentru aceeași dată, deci există o diferență de 3000.
În mod similar, pentru data 18-mar-2019 în Data 1, avem 19351 și în Data 2 avem 10351, deci diferența este de 9000.
# 2 - Match Data folosind funcția INDEX + MATCH
Pentru aceleași date, putem folosi funcția INDEX + MATCH. Putem folosi acest lucru ca o alternativă la funcția VLOOKUP.
Funcția INDEX utilizată pentru a obține valoarea din coloana selectată pe baza numărului de rând furnizat. Pentru a furniza numărul rândului, trebuie să folosim funcția MATCH pe baza valorii LOOKUP.
Deschideți funcția INDEX în celula F3.
Selectați tabloul ca interval de coloane de rezultate, adică de la B2 la B14.
Pentru a obține numărul rândului, deschideți acum funcția MATCH ca argument următor.
Selectați valoarea de căutare ca celulă D3.
Apoi selectați matrice de căutare ca coloană Data vânzării în Date 1
În tipul de potrivire selectați „0 - potrivire exactă”.
Închideți două paranteze și apăsați tasta Enter pentru a obține rezultatul.
Acest lucru oferă, de asemenea, același rezultat ca și VLOOKUP. Deoarece am folosit aceleași date, am obținut numerele așa cum sunt
# 3 - Creați-vă propria valoare de căutare
Acum am văzut cum să potrivim datele folosind funcțiile Excel. Acum vom vedea diferitele scenarii ale timpului real. Pentru acest exemplu, uitați-vă la datele de mai jos.
În datele de mai sus, avem date privind vânzările în funcție de zone și date, așa cum se arată mai sus. Trebuie să facem din nou procesul de potrivire a datelor. Să aplicăm funcția VLOOKUP conform exemplului anterior.
Avem multe varianțe. Să examinăm fiecare caz cu caz.
În celula I5 am obținut varianța 8300. Să vedem tabelul principal.
Chiar dacă în tabelul principal valoarea este 12104, am obținut valoarea 20404 din funcția VLOOKUP. Motivul pentru acest lucru este că VLOOKUP poate returna valoarea primei valori de căutare găsite.
În acest caz, valoarea căutării noastre este data, adică 20 martie 2019. În celula de mai sus pentru zona de nord pentru aceeași dată avem o valoare de 20404, deci VLOOKUP a returnat această valoare și pentru zona de est.
Pentru a depăși această problemă, trebuie să creăm valori de căutare unice. Combinați zona, data și valoarea vânzărilor atât în datele 1, cât și în datele 2.
Date 1 - Tabel
Date 2 - Tabel
Acum am creat o valoare unică pentru fiecare zonă cu valoarea combinată a zonei, a datei de vânzare și a sumei de vânzare.
Utilizarea acestor valori unice permite aplicarea funcției VLOOKUP.
Aplicați formula tuturor celulelor, vom obține varianța zero în toate celulele.
Astfel, prin utilizarea funcțiilor Excel putem să potrivim datele și să găsim variațiile. Înainte de a aplica formula, trebuie să ne uităm la duplicatele din valoarea de căutare pentru o reconciliere exactă. Exemplul de mai sus este cea mai bună ilustrare a valorilor duplicate în valoarea de căutare. În astfel de scenarii, trebuie să ne creăm propriile valori de căutare unice și să obținem rezultatul.