Különböző módszerek az adatok egyeztetésére az Excelben
Különböző módszerek léteznek az adatok excelben való egyeztetésére, ha meg akarjuk egyezni az oszlop adatait, akkor tegyük fel, hogy ellenőrizni akarjuk a kettősséget, használhatunk feltételes formázást a kezdőlapról, vagy pedig ha kettőbe akarjuk illeszteni az adatokat, ill. több különböző oszlopban használhatunk feltételes függvényeket, például if függvényt.
- 1. módszer - A Vlookup függvény használata
- 2. módszer - Az Index + egyezés funkció használata
- 3. módszer - Saját keresési érték létrehozása
Most részletezzük az egyes módszereket
Ezt a Match Data Excel sablont innen töltheti le - Match Data Excel sablon# 1 - Adatok egyeztetése a VLOOKUP függvény használatával
A VLOOKUP nemcsak arra szolgál, hogy megszerezze a szükséges információkat az adattáblából, hanem egyeztetési eszközként is használható. Amikor egyeztetésre vagy az adatok egyeztetésére kerül sor, a VLOOKUP képlet vezeti a táblázatot.
Például nézze meg az alábbi táblázatot.
Két adattáblázatunk van, az első az 1. adat, a második a 2. adat.
Most össze kell egyeztetnünk, hogy a két táblázat adatai egyeznek-e vagy sem. Az adatok első egyeztetési módja a SUM függvény az excelben két táblában, hogy megkapja a teljes eladást.
1. adat - táblázat
2. adat - táblázat
A SUM függvényt mindkét táblázat Eladási összeg oszlopához alkalmaztam. Magában a kezdeti lépésben megkapta az értékek különbségét. Adat 1 táblázat bemutatja az összes értékesítés 2,16,214 és adatok 2 táblázat mutatja a teljes értékesítés 2,10,214 .
Most ezt kell részletesen megvizsgálnunk. Tehát alkalmazzuk a VLOOKUP függvényt minden dátumra.
Válassza ki a táblázat tömböt Data Data tartományként.
Szükségünk van a második oszlop adataira, és a keresési tartomány FALSE, azaz Pontos egyezés.
A kimenet az alábbiakban látható:
A következő cellában vonja le az eredeti értéket az érkezés értékével.
Levonás után az eredmény nulla.
Most másolja és illessze be a képletet az összes cellába, hogy megkapja a varianciaértékeket.
A G6 és G12 cellában megkapjuk a különbségeket.
Az 1. adatban 12104 van a 2019. március 04-i dátumra, a 2. adatokban pedig 15104 van ugyanarra a napra, tehát 3000-es különbség van.
Hasonlóképpen, a 2019. március 18-i dátumra az 1. adatban 19351, a 2. adatban pedig 10351 van, tehát a különbség 9000.
# 2 - Adatok egyeztetése az INDEX + MATCH függvény használatával
Ugyanezen adatokhoz használhatjuk az INDEX + MATCH függvényt. Ezt a VLOOKUP funkció alternatívájaként használhatjuk.
Az INDEX függvény arra szolgál, hogy az értéket a kiválasztott oszlopból kapja meg a megadott sorszám alapján. A sorszám megadásához a LOOKUP értéken alapuló MATCH függvényt kell használnunk.
Nyissa meg az INDEX függvényt az F3 cellában.
Válassza ki a tömböt az eredmény oszloptartományként, azaz B2-től B14-ig.
Annak érdekében, hogy a sorszámot nyissa meg, a következő argumentumként nyissa meg a MATCH függvényt.
Válassza ki a keresési értéket D3 cellaként.
Ezután válassza a Keresési tömb eladási dátum oszlopát az 1. adatban.
A mérkőzés típusában válassza a „0 - Pontos egyezés” lehetőséget.
Zárjon be két zárójelet és nyomja meg az Enter billentyűt az eredmény eléréséhez.
Ez ugyanazt az eredményt adja, mint csak a VLOOKUP. Mivel ugyanazokat az adatokat használtuk, a számokat úgy kaptuk, ahogy vannak
# 3 - Készítse el saját keresési értékét
Most láttuk, hogyan lehet egyeztetni az adatokat az excel függvények segítségével. Most látni fogjuk a valós idő eltérő forgatókönyvét. Ebben a példában nézze meg az alábbi adatokat.
A fenti adatokban a fentiek szerint a zónánként és a dátum szerint értékesítési adataink vannak. Újra el kell végeznünk az adategyeztetési folyamatot. Alkalmazzuk a VLOOKUP függvényt az előző példa szerint.
Sok eltérést kaptunk. Vizsgálja meg az egyes eseteket.
Az I5 cellában a 8300 varianciát kaptuk. Nézzük meg a fő táblázatot.
Annak ellenére, hogy a főtáblában az érték 12104, a 20404 értékét a VLOOKUP függvényből kaptuk. Ennek oka az, hogy a VLOOKUP visszaadhatja az első megtalált keresési érték értékét.
Ebben az esetben a keresési értékünk dátum, azaz 2019. március 20. Az északi zóna fenti cellájában ugyanarra a napra 20404-es értéket adunk meg, így a VLOOKUP ezt az értéket a keleti zónára is visszaküldte.
A probléma megoldásához egyedi keresési értékeket kell létrehoznunk. Kombinálja a zónát, a dátumot és az értékesítési összeget mind az 1. , mind a 2. adatban.
1. adat - táblázat
2. adat - táblázat
Most minden egyes zónához egyedi értéket hoztunk létre a Zóna, Eladási dátum és Eladási összeg együttes értékével.
Ezen egyedi értékek használata lehetővé teszi a VLOOKUP függvény alkalmazását.
Alkalmazza a képletet az összes cellára, az összes cellában megkapjuk a nulla varianciáját.
Így az excel függvények segítségével össze tudjuk hangolni az adatokat, és megtalálhatjuk az eltéréseket. A képlet alkalmazása előtt meg kell vizsgálnunk a duplikátumokat a keresési értékben a pontos egyeztetés érdekében. A fenti példa a duplikált értékek legjobb illusztrációja a keresési értékben. Ilyen esetekben saját egyedi keresési értékeket kell létrehoznunk, és el kell érnünk az eredményt.