Két lista egyesítése ismétlődések nélkül

Klasszikus helyzet: két listája van, amelyeket össze kell vonni egybe. Ezenkívül a kezdeti listákban egyedi elemek és egyező elemek is lehetnek (mind a listák között, mind belül), de a kimeneten egy listát kell kapnia ismétlődések (ismétlések) nélkül:

Hagyományosan nézzünk meg többféle megoldást egy ilyen gyakori probléma megoldására – a primitív „homlok”-tól a bonyolultabb, de elegánsabbig.

1. módszer: Az ismétlődések eltávolítása

A problémát a legegyszerűbb módon oldhatja meg – manuálisan másolja át mindkét lista elemeit egybe, majd alkalmazza az eszközt a kapott halmazra. Távolítsa el a másolatokat a lapról dátum (Adatok – Ismétlődések eltávolítása):

Természetesen ez a módszer nem fog működni, ha a forráslistákban szereplő adatok gyakran változnak – minden változtatás után újra meg kell ismételni a teljes eljárást. 

1a. módszer. Pivot tábla

Ez a módszer tulajdonképpen az előző logikus folytatása. Ha a listák nem túl nagyok, és előre ismert a bennük lévő elemek maximális száma (például legfeljebb 10), akkor közvetlen hivatkozásokkal két táblázatot egyesíthet egybe, hozzáadhat egy oszlopot a jobb oldalon lévőkkel, és a kapott táblázat alapján készítsen összefoglaló táblázatot:

Mint ismeretes, a pivot tábla figyelmen kívül hagyja az ismétlődéseket, így a kimeneten egy kombinált listát kapunk ismétlődések nélkül. Az 1-es kiegészítő oszlopra csak azért van szükség, mert az Excel legalább két oszlopot tartalmazó összefoglaló táblázatokat tud készíteni.

Az eredeti listák megváltoztatásakor az új adatok közvetlen hivatkozásokon keresztül a kombinált táblába kerülnek, de a pivot táblát manuálisan kell frissíteni (jobb egérgombbal - Frissítés és mentés). Ha nincs szüksége az újraszámításra menet közben, akkor jobb, ha más lehetőségeket használ.

2. módszer: Tömbképlet

Képletekkel meg tudod oldani a problémát. Ebben az esetben az eredmények újraszámítása és frissítése automatikusan és azonnal megtörténik, az eredeti listák változása után azonnal. A kényelem és a rövidség kedvéért adjunk nevet listáinknak. Lista 1 и Lista 2segítségével Névkezelő lap képlet (Képletek — Névkezelő — Létrehozás):

Az elnevezés után a szükséges képlet így fog kinézni:

Első pillantásra hátborzongatónak tűnik, de valójában nem minden olyan ijesztő. Hadd bővítsem ki ezt a képletet több sorra az Alt+Enter billentyűkombinációval és szóközökkel behúzva, ahogy tettük, például itt:

A logika itt a következő:

• Az INDEX(Lista1;MATCH(0;COUNTIF($E$1:E1;Lista1); 0) képlet az első listából kiválasztja az összes egyedi elemet. Amint elfogy, #N/A hibát jelez:

  

• Az INDEX(Lista2;MATCH(0;COUNTIF($E$1:E1;Lista2; 0)) képlet ugyanúgy kinyeri az egyedi elemeket a második listából.
• A két egymásba ágyazott IFERROR függvény először a list-1 egyedi, majd egymás után a list-2 egyedi kimenetét valósítja meg.

Vegyük figyelembe, hogy ez egy tömbképlet, azaz beírás után olyan cellába kell beírni, ami nem közönséges belép, de billentyűkóddal Ctrl+műszak+belép majd másolja (húzza) le a gyermekcellákba margóval.

Az Excel angol verziójában ez a képlet így néz ki:

=IFERROR(IFERROR(INDEX(Lista1, MATCH(0, COUNTIF($E$1:E1, Lista1), 0)), INDEX(Lista2, MATCH(0, COUNTIF($E$1:E1, Lista2), 0)) ), ") 

Ennek a megközelítésnek az a hátránya, hogy a tömbképletek észrevehetően lelassítják a fájllal végzett munkát, ha a forrástáblázatok nagy (több száz vagy több) elemszámmal rendelkeznek. 

3. módszer: Power Query

Ha a forráslisták sok elemet tartalmaznak, például több száz vagy ezer, akkor a lassú tömbképlet helyett jobb egy alapvetően más megközelítést használni, nevezetesen a Power Query bővítmény eszközeit. Ez a bővítmény alapértelmezés szerint be van építve az Excel 2016-ba. Ha Excel 2010 vagy 2013 verziója van, azt külön (ingyenesen) letöltheti és telepítheti.

A műveletek algoritmusa a következő:

1. Nyissa meg a telepített bővítmény külön lapját Teljesítmény lekérdezés (ha van Excel 2010-2013), vagy egyszerűen lépjen a lapra dátum (ha van Excel 2016).
2. Válassza ki az első listát, és nyomja meg a gombot Táblázatból/Tartományból (Tartományból/táblázatból). Amikor arról kérdezünk, hogy hozzon létre egy „intelligens táblázatot” a listánkból, egyetértünk:

   

3. Megnyílik a lekérdezésszerkesztő ablak, ahol láthatjuk a betöltött adatokat és a lekérdezés nevét Táblázat 1 (ha akarod, megváltoztathatod a sajátodra).
4. Kattintson duplán a táblázat fejlécére (szó Lista 1), és nevezze át bármilyen másra (például Emberek (People)). Hogy pontosan mit nevezzünk el, az nem fontos, de a kitalált nevet meg kell jegyezni, mert. később a második tábla importálásakor újra fel kell használni. Két tábla egyesítése a jövőben csak akkor működik, ha az oszlopfejlécek egyeznek.
5. Bontsa ki a bal felső sarokban található legördülő listát zárja be és töltse le És válasszon Zárja be és töltse be… (Bezárás és betöltés…):

   

6. A következő párbeszédpanelen (lehet, hogy kicsit másképp néz ki – ne ijedjen meg) válassza a lehetőséget Csak hozzon létre egy kapcsolatot (Csak kapcsolat létrehozása):

   

7. A teljes eljárást (2-6. pont) megismételjük a második listánál. Egy oszlopfejléc átnevezésénél fontos, hogy ugyanazt a nevet (Személyek) használjuk, mint az előző lekérdezésnél.
8. A lapon található Excel ablakban dátum vagy a lapon Teljesítmény lekérdezés A pop-art design, négy időzóna kijelzése egyszerre és méretének arányai azok az érvek, amelyek a NeXtime Time Zones-t kiváló választássá teszik. Válassza a Adatok lekérése – Kérelmek egyesítése – Hozzáadás (Adatok lekérése – Lekérdezések egyesítése – Hozzáfűzés):

   

9. A megjelenő párbeszédpanelen válassza ki kéréseinket a legördülő listákból:

   

10. Ennek eredményeként egy új lekérdezést kapunk, ahol két lista kapcsolódik egymás alá. Marad a másolatok eltávolítása a gombbal Sorok törlése – Ismétlődések eltávolítása (Sorok törlése — Ismétlődések törlése):

    

11. Az elkészült lekérdezés a beállítások panel jobb oldalán átnevezhető, értelmes nevet adva neki (valójában ez lesz az eredménytábla neve) és mindent fel lehet tölteni a lapra a paranccsal zárja be és töltse le (Bezárás és betöltés):

    

A jövőben az eredeti listák bármilyen módosítása vagy kiegészítése esetén elegendő lesz a jobb gombbal kattintva frissíteni az eredménytáblázatot.

• Több tábla összegyűjtése különböző fájlokból a Power Query segítségével
• Egyedi elemek kinyerése listából
• Hogyan hasonlítsunk össze két listát egymással az egyezések és a különbségek tekintetében