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
# 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; inkább 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.
Nézze meg például 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 egyeztetésének legelső 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 mutatja a teljes értékesítés 2,16,214, és az 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 1. adat 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. adatban pedig 15104 van ugyanarra a napra, tehát 3000-es különbség van.
Hasonlóképpen, az 1. adat 2019. március 18-i dátumára 19351, a 2. adatokban 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 a megadott sorszám alapján lekérdezze az értéket a kiválasztott oszlopból. A sorszám megadásához a LOOKUP érték alapján a 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 eredmény oszloptartományként, azaz B2-től B14-ig.
A sorszám megszerzéséhez nyissa meg most a MATCH függvényt, mint következő argumentumot.
Válassza ki a keresési értéket D3 cellaként.
Ezután válassza ki a keresési tömböt Értékesítési dátum oszlopként az 1. adatban.
Az egyezési típusban válassza a „0 - Pontos egyezés” lehetőséget.
Csukjon 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 - Saját keresési érték létrehozása
Most láttuk, hogyan lehet egyeztetni az adatokat az excel függvények segítségével. Most meglátjuk a valós idejű különböző forgatókönyveket. Ebben a példában nézze meg az alábbi adatokat.
A fenti adatokban a zónánként és a dátum szerint értékesítési adatokkal rendelkezünk, a fentiek szerint. Ú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áljuk 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ábla értéke 12104, a 20404 értéket 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 egy dátum, azaz 2019. március 20. Az északi zóna fenti cellájában ugyanarra a dátumra 20404-es értékünk van, így a VLOOKUP ezt az értéket a keleti zónára is visszaküldte.
A probléma leküzdéséhez 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 felhasználásával alkalmazzuk a VLOOKUP függvényt.
Alkalmazza a képletet az összes cellára; minden cellában megkapjuk a nulla varianciáját.
Így az excel függvények használatával össze tudjuk egyeztetni 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 példá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.
