Combinant dues llistes sense duplicats

Una situació clàssica: tens dues llistes que s'han de combinar en una sola. A més, a les llistes inicials hi pot haver elements únics i coincidents (tant entre les llistes com dins), però a la sortida cal obtenir una llista sense duplicats (repeticions):

Vegem tradicionalment diverses maneres de resoldre un problema tan comú: des del primitiu "al front" fins a més complexos, però elegants.

Mètode 1: elimina els duplicats

Podeu resoldre el problema de la manera més senzilla: copieu manualment els elements d'ambdues llistes en una i després apliqueu l'eina al conjunt resultant. Elimina els duplicats de la pestanya dades (Dades — Elimina els duplicats):

Per descomptat, aquest mètode no funcionarà si les dades de les llistes d'origen canvien sovint; haureu de tornar a repetir tot el procediment després de cada canvi. 

Mètode 1a. taula dinàmica

Aquest mètode és, de fet, una continuació lògica de l'anterior. Si les llistes no són molt grans i es coneix per endavant el nombre màxim d'elements (per exemple, no més de 10), podeu combinar dues taules en una mitjançant enllaços directes, afegir una columna amb els de la dreta i construïu una taula resum basada en la taula resultant:

Com sabeu, la taula dinàmica ignora les repeticions, de manera que a la sortida obtindrem una llista combinada sense duplicats. La columna auxiliar amb 1 només és necessària perquè Excel pot crear taules de resum que continguin almenys dues columnes.

Quan es canvien les llistes originals, les dades noves aniran a la taula combinada mitjançant enllaços directes, però la taula dinàmica s'haurà d'actualitzar manualment (clic dret - Actualitza i desa). Si no necessiteu un recàlcul sobre la marxa, és millor utilitzar altres opcions.

Mètode 2: Fórmula de matriu

Podeu resoldre el problema amb fórmules. En aquest cas, el recàlcul i l'actualització dels resultats es produiran de manera automàtica i instantània, immediatament després dels canvis a les llistes originals. Per comoditat i brevetat, donem noms a les nostres llistes. Llista 1 и Llista 2ús Gestor de noms llengüeta fórmula (Fórmules — Gestor de noms — Crear):

Després de nomenar, la fórmula que necessitem serà així:

A primera vista, sembla esgarrifós, però, de fet, no tot fa tanta por. Permeteu-me ampliar aquesta fórmula en diverses línies utilitzant la combinació de tecles Alt+Enter i sagnat amb espais, com vam fer, per exemple aquí:

La lògica aquí és la següent:

• La fórmula INDEX(Llista1;COINCIDENT(0;COMPTARSI($E$1:E1;Llista1); 0) selecciona tots els elements únics de la primera llista. Tan bon punt s'acaben, comença a donar un error #N/A:

  

• La fórmula INDEX(Llista2;COINCIDENT(0;COMPTARSI($E$1:E1;Llista2); 0)) extreu els elements únics de la segona llista de la mateixa manera.
• Imbricades entre si dues funcions IFERROR implementen la sortida primer de les úniques de la llista-1, i després de la llista-2 una darrere l'altra.

Tingueu en compte que aquesta és una fórmula de matriu, és a dir, després d'escriure, s'ha d'introduir en una cel·la que no sigui normal Enter, però amb una drecera de teclat Ctrl+Canviar+Enter i després copieu (arrossegueu) cap avall a les cel·les secundàries amb un marge.

A la versió anglesa d'Excel, aquesta fórmula sembla:

=SIERROR(SIERROR(ÍNDEX(Llista1, COINCORDA(0, COUNTIF($E$1:E1, Llista1), 0)), INDEX(Llista2, COINCIDENT(0, COUNTIF($E$1:E1, Llista2), 0)) ), "") 

L'inconvenient d'aquest enfocament és que les fórmules de matriu alenteixen notablement el treball amb el fitxer si les taules font tenen un gran nombre d'elements (uns centenars o més). 

Mètode 3. Power Query

Si les vostres llistes d'origen tenen un gran nombre d'elements, per exemple, diversos centenars o milers, aleshores, en lloc d'una fórmula de matriu lenta, és millor utilitzar un enfocament fonamentalment diferent, és a dir, les eines complementàries de Power Query. Aquest complement està integrat a Excel 2016 de manera predeterminada. Si teniu Excel 2010 o 2013, podeu descarregar-lo i instal·lar-lo per separat (de franc).

L'algoritme d'accions és el següent:

1. Obriu una pestanya independent del complement instal·lat Power Consulta (si teniu Excel 2010-2013) o simplement aneu a la pestanya dades (si teniu Excel 2016).
2. Seleccioneu la primera llista i premeu el botó De Taula/Range (Des del rang/taula). Quan se'ns pregunta sobre la creació d'una "taula intel·ligent" de la nostra llista, estem d'acord:

   

3. S'obre la finestra de l'editor de consultes, on podeu veure les dades carregades i el nom de la consulta Taula 1 (podeu canviar-lo pel vostre si voleu).
4. Feu doble clic a la capçalera de la taula (word Llista 1) i canviar-li el nom a qualsevol altre (per exemple Directori). Què anomenar exactament no és important, però cal recordar el nom inventat, perquè. s'haurà de tornar a utilitzar més tard quan s'importi la segona taula. La fusió de dues taules en el futur només funcionarà si els encapçalaments de les columnes coincideixen.
5. Amplieu la llista desplegable a la cantonada superior esquerra tancar i descarregar I tria Tanca i carrega... (Tancar i carregar a...):

   

6. Al quadre de diàleg següent (pot semblar una mica diferent; no us alarmeu), seleccioneu Només cal crear una connexió (Només crea connexió):

   

7. Repetim tot el procediment (punts 2-6) per a la segona llista. Quan es canvia el nom d'un encapçalament de columna, és important utilitzar el mateix nom (Persones) que a la consulta anterior.
8. A la finestra d'Excel de la pestanya dades o a la pestanya Power Consulta Escollir Obteniu dades – Combina sol·licituds – Afegeix (Obteniu dades — Combina consultes — Afegiu):

   

9. Al quadre de diàleg que apareix, seleccioneu les nostres sol·licituds de les llistes desplegables:

   

10. Com a resultat, obtindrem una nova consulta, on es connectaran dues llistes una sota l'altra. Queda per eliminar duplicats amb el botó Suprimeix files: elimina els duplicats (Suprimeix files — Elimina els duplicats):

    

11. La consulta acabada es pot canviar el nom a la part dreta del tauler d'opcions, donant-li un nom correcte (de fet serà el nom de la taula de resultats) i tot es pot carregar al full amb l'ordre tancar i descarregar (Tancar i carregar):

    

En el futur, amb qualsevol canvi o addició a les llistes originals, n'hi haurà prou amb fer clic amb el botó dret per actualitzar la taula de resultats.

• Com recopilar diverses taules de diferents fitxers mitjançant Power Query
• Extracció d'elements únics d'una llista
• Com comparar dues llistes entre si per a coincidències i diferències