Različne metode za ujemanje podatkov v Excelu
Obstajajo različne metode za ujemanje podatkov v Excelu, če se želimo ujemati s podatki v istem stolpcu, recimo, da želimo preveriti dvojnost, lahko uporabimo pogojno oblikovanje z zavihka doma ali pa če želimo podatke ujemati v dveh več različnih stolpcev lahko uporabimo pogojne funkcije, kot je if.
- 1. način - Uporaba funkcije Vlookup
- 2. način - Uporaba funkcije kazala in ujemanja
- 3. način - ustvarite lastno vrednost iskanja
Zdaj pa podrobno razpravljajmo o vsaki od metod
To predlogo Excel Data Match lahko prenesete tukaj - Predloga Match Data Excel# 1 - Ujemanje podatkov s pomočjo funkcije VLOOKUP
VLOOKUP se ne uporablja samo za pridobivanje zahtevanih informacij iz podatkovne tabele, temveč se lahko uporablja tudi kot orodje za usklajevanje. Ko gre za uskladitev ali ujemanje podatkov, tabela vodi formula VLOOKUP.
Za primer si oglejte spodnjo tabelo.
Tu imamo dve podatkovni tabeli, prva je Data 1 in druga Data 2.
Zdaj se moramo uskladiti, ali se podatki v dveh tabelah ujemajo ali ne. Prvi način ujemanja podatkov je funkcija SUM v Excelu do dveh tabel, da dobimo skupno prodajo.
Podatki 1 - tabela
Podatki 2 - tabela
Uporabil sem funkcijo SUM za oba stolpca tabele Prodajni znesek. Na samem začetnem koraku smo dobili razliko v vrednosti. Tabela podatkov 1 prikazuje skupno prodajo 2,16,214 in tabela podatkov 2 prikazuje skupno prodajo 2,10,214 .
Zdaj moramo to podrobno preučiti. Torej, uporabimo funkcijo VLOOKUP za vsak datum.
Izberite polje tabele kot obseg podatkov 1 .
Potrebujemo podatke iz drugega stolpca in obseg iskanja je FALSE, tj. Natančno ujemanje.
Rezultat je podan spodaj:
V naslednji celici se odšteje prvotna vrednost s prispelo vrednostjo.
Po odštevanju dobimo rezultat kot nič.
Zdaj kopirajte in prilepite formulo v vse celice, da dobite vrednosti variance.
V celicah G6 in G12 smo dobili razlike.
V podatkih 1 imamo 12104 za datum 4. marec 2019, v podatkih 2 pa imamo 15104 za isti datum, zato je razlika 3000.
Podobno imamo za datum 18.3.2019 v Podatku 1 19351, v Podatku 2 pa 10351, torej je razlika 9000.
# 2 - Ujemanje podatkov s pomočjo funkcije INDEX + MATCH
Za enake podatke lahko uporabimo funkcijo INDEX + MATCH. To lahko uporabimo kot alternativo funkciji VLOOKUP.
Funkcija INDEX, ki se uporablja za pridobitev vrednosti iz izbranega stolpca na podlagi podane številke vrstice. Za vnos številke vrstice moramo uporabiti funkcijo MATCH, ki temelji na vrednosti LOOKUP.
Odprite funkcijo INDEX v celici F3.
Izberite polje kot obseg stolpcev z rezultati, tj. B2 do B14.
Da bi dobili številko vrstice, odprite funkcijo MATCH zdaj kot naslednji argument.
Izberite vrednost iskanja kot celico D3.
Nato izberite iskalno polje kot stolpec Datum prodaje v Podatki 1.
V vrsti ujemanja izberite “0 - Natančno ujemanje”.
Zaprite dva oklepaja in pritisnite tipko Enter, da dobite rezultat.
To daje tudi enak rezultat kot samo VLOOKUP. Ker smo uporabili enake podatke, smo dobili številke, kakršne so
# 3 - Ustvarite lastno vrednost iskanja
Zdaj smo videli, kako ujemati podatke s pomočjo Excelovih funkcij. Zdaj bomo videli drugačen scenarij v realnem času. Za ta primer si oglejte spodnje podatke.
V zgornjih podatkih imamo podatke o prodaji po območjih in datumih, kot je prikazano zgoraj. Ponovno moramo opraviti postopek ujemanja podatkov. Uporabimo funkcijo VLOOKUP kot v prejšnjem primeru.
Dobili smo veliko odstopanj. Naj preuči vsak primer posebej.
V celici I5 smo dobili varianco 8300. Oglejmo si glavno tabelo.
Čeprav je v glavni tabeli vrednost 12104, smo iz funkcije VLOOKUP dobili vrednost 20404. Razlog za to je, da lahko VLOOKUP vrne vrednost prve najdene iskalne vrednosti.
V tem primeru je naša iskalna vrednost datum, tj. 20. marec 2019. V zgornji celici za severno območje za isti datum imamo vrednost 20404, zato je VLOOKUP vrnil to vrednost tudi za vzhodno območje.
Da bi odpravili to težavo, moramo ustvariti edinstvene vrednosti iskanja. Združite območje, datum in količino prodaje v podatkih 1 in podatkih 2.
Podatki 1 - tabela
Podatki 2 - tabela
Zdaj smo za vsako cono ustvarili edinstveno vrednost s kombinirano vrednostjo cone, datuma prodaje in zneska prodaje.
Z uporabo teh edinstvenih vrednosti lahko uporabimo funkcijo VLOOKUP.
Uporabite formulo za vse celice, dobili bomo varianco nič v vseh celicah.
Tako lahko z uporabo Excelovih funkcij primerjamo podatke in poiščemo variance. Pred uporabo formule si moramo ogledati dvojnike v iskalni vrednosti za natančno uskladitev. Zgornji primer je najboljši prikaz podvojenih vrednosti v iskalni vrednosti. V takih scenarijih moramo ustvariti lastne edinstvene vrednosti iskanja in priti do rezultata.