Hay Paula, össze tudsz hasonlítani listákat Excelben?
vagy
Mi a legjobb módszer két adatkészlet összehasonlítására Excelben?
Nagyon gyakran van szükség az Excelben két lista vagy két adatkészlet összehasonlítására a hiányzó vagy egyező elemek keresésére. Mivel ez az Excel, mindig többféleképpen lehet dolgokat csinálni, beleértve az adatok összehasonlítását is. A képletektől és a feltételes formázástól a Power Query-ig. Ebben a cikkben két lista összehasonlításának számos módját nézzük meg az Excelben, és megvizsgáljuk egy adathalmaz teljes sorainak összehasonlítását is.
Meglehet, hogy az Ön által preferált módszer nem szerepel az alábbiakban. Ha nem, miért nem írja meg kommentben, és osztja meg velünk, hogyan szeret összehasonlítani két listát vagy adathalmazt az Excelben. Várom a hozzászólásait.
Az 1. listát szeretnénk összehasonlítani a 2. listával.
Tartalom
Gyors feltételes formázás két adatoszlop összehasonlításához
A feltételes formázás törlése
Adatok összehasonlítása az Excelben a MATCH függvény használatával
2 lista összehasonlítása az Excel 365-ben a MATCH vagy XMATCH funkcióval mint Dinamikus tömb függvény
MATCH és dinamikus tömbök 2 lista összehasonlításához
XMATCH Excel 365 két lista összehasonlításához
Táblázatok – Listák összehasonlítása az Excelben, ahol a tartományok mérete változhat
Kiemeli a különbségeket a listákban az egyéni feltételes formázás használatával
Másolás. formula az Egyéni feltételes formázáshoz
Más képletek két lista összehasonlításához az Excelben
VLOOKUP két lista összehasonlításához az Excelben
XLOOKUP két lista összehasonlításához az Excelben
COUNTIF két lista összehasonlításához az Excelben
Hogyan hasonlítsunk össze 2 adathalmazt az Excelben
.
Listák vagy adatkészletek összehasonlítása a Power Query használatával
Gyors feltételes formázás két adatoszlop összehasonlításához
A feltételes formázás lehetővé teszi egy cella vagy tartomány kiemelését előre meghatározott kritériumok alapján. A két oszlop gyors vizuális összehasonlításának leggyorsabb és legegyszerűbb módja az előre definiált duplikált értékek kiemelése szabály használata.
Kezdje a két adatoszlop kijelölésével.
A Kezdőlap lapon válassza a Feltételes formázás legördülő menüpontot. Ezután válassza a Cellák kiemelésének szabályai menüpontot. Ezután válassza a Duplikált értékek lehetőséget.
Megnyílik a Duplikált értékek beállítási mező, ahol meghatározhatja a formázást, és választhat a Duplikált vagy az Egyedi értékek között.
A Duplikált kiválasztásával minden ismétlődő bejegyzés a kiválasztott formázást kapja. Most már gyorsan láthatja az 1. listában szereplő tételeket, amelyek a 2. listában is szerepelnek, mivel ezek a formázott tételek. Gyorsan láthatja azokat a tételeket is a 2. listában, amelyek nem szerepelnek az 1. listában, mivel ezekre nem alkalmaznak formázást.
Mégis formázhatja az Egyedi tételeket. Ezt úgy érhetjük el, hogy a Duplikált értékek beállítási mezőben kiválasztjuk az Egyedi elemet.
Ebben az esetben két különböző feltételes formázást alkalmaztunk. A piros a duplikátumokat, a zöld pedig az egyedi elemeket jelzi.
Megjegyzem, nem csak az adatokkal rendelkező cellákat vettem, hanem az összes A-C oszlopot megragadtam. A B oszlopban nincsenek adatok, így az nem befolyásolhatja az eredményeket. Ezek a cellák azonban tartalmazzák az alkalmazott feltételes formázást, ezért jobb gyakorlat lenne, ha csak a szükséges cellákat jelölné ki.
A feltételes formázás törlése
A feltételes formázás törléséhez először jelölje ki a cellát, illetve a tartományt. Ezután válassza ki a Főoldal szalag feltételes formázás legördülő menüpontját. Ezután válassza a Szabályok törlése lehetőséget. Végül válassza a Szabályok törlése a kijelölt cellákból lehetőséget.
Ha egyszerre több feltételes formázást alkalmaz, és ezek közül csak egyet szeretne eltávolítani, válassza a feltételes formázás legördülő menüpontból a Szabályok kezelése lehetőséget. Jelölje ki a törölni kívánt szabályt, majd válassza a Szabály törlése lehetőséget.
Az OK gomb megnyomásával a szabály eltávolításra kerül a Szabálykezelőből, és a cellák többé nem tartalmazzák a formázást.
Ez tehát a legalapvetőbb módja két lista összehasonlításának az Excelben. Gyors, egyszerű és hatékony. Képleteken alapuló feltételes formázást is alkalmazhat, amit a cikk későbbi részében megnézünk.
Adatok összehasonlítása az Excelben a MATCH függvénnyel
Megannyi keresési képlet létezik, amelyeket két tartomány vagy lista összehasonlítására használhat az Excelben. Az első, amit megnézünk, a MATCH függvény.
A MATCH függvény visszaadja a relatív pozíciót egy listában. Egy számot a keresési tömbben elfoglalt pozíciója alapján, ha megtalálható.
A MATCH szintaxisa a következő
=MATCH (keresési érték, Keresési tömb, Egyezés típusa)
Hol keresési érték az az érték, amelynek egyezést szeretne találni. Lookup array az a lista, amelyben egyezést keresünk. A Match type pedig lehetővé teszi a pontos vagy közelítő egyezés közötti választást.
Meg akarunk írni egy egyezésképletet, hogy megnézzük, hogy a 2. listában lévő elemek szerepelnek-e az 1. listában.
Az E3-as cellába a következő képletet írhatjuk be
=MATCH(C2, $A$2:$A$21,0)
A képlet kitöltésével az Excel azon értékek esetében, ahol talál egyezést, az adott egyezés pozícióját adja vissza. Ahol nincs találat, ott a visszatérési érték egy #N/A lesz.
Nagyon gyakran a relatív pozíció vagy az #N/A nem jelent számunkra értéket, és ezeket az értékeket igaz vagy hamis értékké kell alakítanunk. Ehhez egyszerűen kibővíthetjük a Match képletünket egy logikai függvény segítségével. Mivel a Match egy számot ad vissza, használhatjuk az ISNUMBER függvényt
=ISNUMBER(MATCH(C2, $A$2:$A$21,0))
2 lista összehasonlítása az Excel 365-ben a MATCH vagy az XMATCH mint dinamikus tömb funkcióval
Ha az Excel 365-öt használja, további alternatívák állnak rendelkezésére, amikor a MATCH-t használja listák vagy adatok összehasonlítására. Mivel az Excel 365 tömbökben gondolkodik, most már átadhatunk egy tömböt a MATCH keresési értékeként, és az eredményünk kiömlik helyettünk. Így nem kell lemásolnunk a képletet, és mivel csak 1 képlet van, a táblázatunk kevésbé lesz hibaérzékeny és kompaktabb.
MATCH és dinamikus tömbök 2 lista összehasonlításához
Ha még nem ismeri a dinamikus tömböket, javaslom, hogy olvassa el ezt a cikket: Excel Dynamic Arrays – A new way to model your Excel Spreadsheets: to get a better understand on how they work and spill ranges.
Az egyetlen változás a megfelelési képletben az, hogy ahelyett, hogy a C2-es cellát választanánk ki keresési értékként, a C2:12
=ISNUMBER(MATCH (C2:C12,$A$2:$A$21,0))
Mint látható, az egyik képlet az E oszlopba ontja az eredményeket.
XMATCH Az Excel 365 két lista összehasonlítására
Az Excel 365 bevezette az új XMATCH függvényt is. Az XMATCH a MATCH függvényhez hasonlóan egy listán belüli relatív pozíciót ad vissza. Most már ismeri az XLOOKUP-ot, amely a régi VLOOKUP függvényt váltja fel, tudja, hogy az XLOOKUP további képességekkel rendelkezik. Ez új feltételek formájában jelenik meg a képlet szintaxisában, mint például a keresési mód és az egyezés típusai. Nos, az XMATCH is rendelkezik ezzel az extra képességgel elődjéhez, a MATCH-hoz képest.
Az XMATCH szintaxisa a következő
XMATCH (Lookup Value, Lookup Array, ,)
Hol
Lookup Value az az érték, amelynek a relatív pozícióját keresi
Lookup Array a Lookup Value-t tartalmazó sor vagy oszlop
A Match mode opcionális. A régi MATCH függvénytől eltérően az alapértelmezett a pontos egyezés. Választhat a
- Pontos egyezés vagy a következő legkisebb
- Pontos egyezés vagy a következő legnagyobb
- Hálójegyes egyezés
A keresési mód szintén opcionális. Az alapértelmezett (és a régi MATCH függvényben az egyetlen lehetőség) a felülről lefelé történő keresés. Az utolsótól az elsőig és a bináris keresés is választható. Ha bináris keresésekkel dolgozik. A wildcard match opció nem működik.
Az XMATCH segítségével vagy Dynamic tömböket, vagy cellahivatkozásokat használhatunk a képlet létrehozásához, ahogyan azt a MATCH esetében is megnéztük. Ebben a példában dinamikus tömböket fogunk használni. A képlet nagyon hasonló ahhoz, amit a MATCH esetében használtunk; kivéve, hogy nem kell a 0-t választanunk a pontos egyezéshez, mivel az XMATCH-ban ez az alapértelmezett beállítás. Keverjük egy kicsit a dolgokat, ezúttal nézzük meg, hogy a 2. listában lévő elemeket keressük meg, nem pedig az egyes listában.
Ebben az esetben használhatjuk a képletet
=NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))
Ahol a not az igazat hamisra, a hamisat pedig igazra változtatja.
Táblázatok – Listák összehasonlítása az Excelben, ahol a tartományok mérete változhat
Az eddig vizsgált képletek mindegyikében olyan cellatartományt választottunk ki a Match függvényeinkben, amely nem dinamikus. Ez azt jelenti, hogy ha új adatokat adunk hozzá az egyik listához, akkor egy manuális lépéssel frissítenünk kell a képletünket, hogy tartalmazza az új adatokat.
A listák táblázattá alakításához jelöljük ki az egyik listát, és nyomjuk le a CTRL billentyűt. Ez a billentyűkombináció a táblázattá alakításhoz. Ha a cellatartományban kijelölte a fejlécet, győződjön meg róla, hogy bejelölte a jelölőnégyzetet, amely megerősíti, hogy a táblázat rendelkezik fejléccel.
A táblázatok természetüknél fogva strukturált elnevezést használnak. Ezért amikor képletet ír, és kiválaszt egy oszlopot a táblázatból, akkor nem a cellahivatkozások, hanem az oszlop neve jelenik meg.
Nézzük az előző képletünket, amely az XMATCH segítségével a 2. listában lévő, de az 1. listában nem szereplő elemeket keresi, ezt a függvényt most újraírhatjuk a táblázathivatkozások használatával
=NOT (ISNUMBER(XMATCH(list2,lista1)))
Most, mivel táblázatokat használtunk, ha új sort adunk hozzá valamelyik táblázathoz, a kiöntési tartományunk is megnő, hogy tartalmazza az új adatokat.
Különbségek kiemelése a listákban egyéni feltételes formázás használatával
A cikk korábbi részében megnéztük, hogyan lehet nagyon gyorsan összehasonlítani a két listát a duplikátumokra vonatkozó előre meghatározott szabály segítségével. Használhatjuk azonban az Egyéni feltételes formázást is. Ha nem ismeri az Egyéni feltételes formázást, javaslom, hogy olvassa el ezt a cikket: Excel dinamikus feltételes formázási trükkök:
A különbségek kiemeléséhez itt két különböző megközelítést kell megvizsgálnunk. Ha nem használunk táblázatokat, és létrehoztunk egy igaz/hamis képletet a különbségek azonosítására, akkor foghatunk egy másolatot a képletből, és ezt hozzáadhatjuk az Egyéni formázásunkhoz. Táblák használatával azonban ki kell kényszerítenünk a cellahivatkozások használatát.
A képlet másolása az Egyéni feltételes formázáshoz
Kezdésként vegyük a képlet másolatát. Mivel a képletet a táblázatkezelőben teszteltük, láthatjuk, hogy működik, mielőtt a feltételes formázásban használnánk. Ez a legjobb gyakorlat, mivel a relatív és abszolút cellahivatkozásoknál nagyon gyakran nehéz lehet a képlet helyes beállítása.
Kijelöljük azokat a cellákat, amelyekre az egyéni formázást szeretnénk alkalmazni. Ezután a Kezdőlap szalagról válassza a feltételes formázás legördülő menüpontot, és válassza az Új szabály
Az Új formázási szabály beállítási mező megnyílik, és válassza a Formula használata lehetőséget annak meghatározásához, hogy mely cellákat kell formázni. Ezután illessze be a képletet, és állítsa be a formázás típusát
Ez azt eredményezi, hogy mindkét listában lévő összes cellát a kiválasztott formátum szerint formázza.
Ne feledje, hogy a feltételes formázás alkalmazásához egy cellatartományt választottunk ki, és ez nem dinamikus. Ha táblázatokat használnánk, akkor ez anélkül frissülne, hogy bármit is módosítanánk!
Két lista összehasonlítására használt egyéb képletek az Excelben
Az Excelben két lista összehasonlítására számos képletet használhatunk. A MATCH és az XMATCH képleteket már megnéztük, de most még néhányat megnézünk. A keresőfüggvények bármelyike valóban működik néhány másikkal együtt!
VLOOKUP két lista összehasonlításához az Excelben
Ha nem ismeri a VLOOKUP-ot, itt olvashat róla. Egyszerűen fogalmazva a VLOOKUP visszaadja a megfelelő értéket egy cellából, ha nincs megfelelő érték, akkor #N/A hiba érkezik vissza. Példánkban szöveggel dolgozunk. Tehát elvégezhetünk egy VLOOKUP-ot, és tesztelhetjük, hogy visszaad-e szöveget. Ha számokat használnánk, akkor az ISTEXT-et ISNUMBER-re cserélhetnénk.
Az =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))
Vagy ha dinamikus tömböket használnánk az Excel 365-ben, akkor a
=ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))
XLOOKUP két lista összehasonlítására az Excelben
Az Excel 365-ben bevezetésre került az XLOOKUP, amelyről bővebben itt olvashat. A VLOOKUP-hoz nagyon hasonlóan az XLOOKUP is visszaadja a megfelelő értéket egy cellából, és meghatározhat egy eredményt, ha az értéket nem találja. Dinamikus tömbök használatával a függvény a következő lenne
=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))
COUNTIF két lista összehasonlítására az Excelben
A COUNTIF függvény megszámolja, hogy egy érték vagy szöveg hányszor szerepel egy tartományban. Ha az értéket nem találja, 0 értéket ad vissza. Ezt kombinálhatjuk egy IF utasítással, hogy visszaadjuk az igaz és hamis értékeinket.
=IF(COUNTIF (A2:A21,C2:C12)<>0, “True”, “False”)
Hogyan hasonlítsunk össze 2 adathalmazt az Excelben
A két lista összehasonlítása elég egyszerű, és ennek több módját is megnéztük most. Két adatkészlet összehasonlítása azonban kicsit nehezebb lehet.
Nézzünk egy példát. Van két adattáblánk, amelyek mindegyike ugyanazokat az oszlopfejléceket tartalmazza. Ha megnézzük a képet, láthatjuk, hogy e két táblázat összevetéséhez egynél több oszlopot kellene megvizsgálnunk.
Ha egynél több oszlopot kell megvizsgálnunk, a megoldás egy összetett oszlop létrehozása lenne, amely az adatokat egy oszlopban egyesíti. Ezáltal minden sorhoz létrehozunk egy egyedi oszlopot, amelyet aztán a megfelelő oszlopként használhatunk
Ebben a példában a Név és a DoB-t kombinálhatjuk, hogy minden egyes táblázatnak egyedi azonosítót adjunk
Egy cella tartalmának összekapcsolására sokféle mód van, ebben az esetben egy egyszerű konkatenálást fogunk végezni. Mivel táblázatokat használunk, a képlet a táblázat elnevezési formátumát fogja cipelni.
= &”-“&
Ismételje meg a lépéseket a második táblázaton.
Most a fenti példák bármelyikét használhatjuk a két új adatoszlop megfeleltetésére. Ahol egyeznek, ott tudjuk, hogy a teljes sorra egyeznek.
Listák vagy adatkészletek összehasonlítása a Power Query segítségével
Listákat és adatkészleteket az Excels Power Query segítségével is összehasonlíthat. A táblázatokhoz való csatlakozással, majd a táblázatok összevonásával, különböző join típusok használatával összehasonlíthatjuk a két listát.
Ebben a videóban megtanulhatja, hogyan hasonlíthat össze vagy egyeztethet össze két különböző adathalmazt az Excels Power query segítségével
Ezzel a videóval és a gyakorlással együtt egy adathalmaz is jár, amelyet ebből a cikkből szerezhet meg.