En klassisk situation: du har to lister, der skal slås sammen til én. Desuden kan der i de indledende lister være både unikke elementer og matchende (både mellem listerne og inde), men ved udgangen skal du få en liste uden dubletter (gentagelser):
Lad os traditionelt se på flere måder at løse et sådant almindeligt problem på - fra primitivt "på panden" til mere komplekst, men elegant.
Metode 1: Fjern dubletter
Du kan løse problemet på den enkleste måde - kopier manuelt elementerne fra begge lister til en og anvend derefter værktøjet til det resulterende sæt. Fjern duplikater fra fanen data (Data – Fjern dubletter):
Selvfølgelig vil denne metode ikke fungere, hvis dataene i kildelisterne ofte ændres - du skal gentage hele proceduren efter hver ændring igen.
Metode 1a. drejetabel
Denne metode er faktisk en logisk fortsættelse af den forrige. Hvis listerne ikke er særlig store, og det maksimale antal elementer i dem er kendt på forhånd (f.eks. ikke mere end 10), så kan du kombinere to tabeller til én ved direkte links, tilføje en kolonne med dem til højre og opbyg en oversigtstabel baseret på den resulterende tabel:
Som du ved ignorerer pivottabellen gentagelser, så ved output får vi en kombineret liste uden dubletter. Hjælpekolonnen med 1 er kun nødvendig, fordi Excel kan bygge oversigtstabeller, der indeholder mindst to kolonner.
Når de originale lister ændres, vil de nye data gå til den kombinerede tabel via direkte links, men pivottabellen skal opdateres manuelt (højreklik – Opdater & Gem). Hvis du ikke har brug for genberegning i farten, så er det bedre at bruge andre muligheder.
Metode 2: Matrixformel
Du kan løse problemet med formler. I dette tilfælde vil genberegningen og opdateringen af resultaterne ske automatisk og øjeblikkeligt umiddelbart efter ændringer i de originale lister. For nemheds skyld og korthed, lad os give vores lister navne. Liste 1 и Liste 2ved brug af Navn Manager fanen Formula (Formler — Navneadministrator — Opret):
Efter navngivning vil formlen, vi skal bruge, se sådan ud:
Ved første øjekast ser det uhyggeligt ud, men i virkeligheden er alt ikke så skræmmende. Lad mig udvide denne formel på flere linjer ved hjælp af Alt+Enter-tastkombinationen og indrykning med mellemrum, som vi for eksempel gjorde her:
Logikken her er følgende:
- Formlen INDEX(List1;MATCH(0;COUNTIF($E$1:E1;Liste1); 0) vælger alle unikke elementer fra den første liste. Så snart de løber tør, begynder den at give en #N/A fejl:
- Formlen INDEX(Liste2;MATCH(0;ANTAL($E$1:E1;Liste2; 0)) udtrækker de unikke elementer fra den anden liste på samme måde.
- Indlejret i hinanden implementerer to IFERROR-funktioner outputtet først af de unikke fra liste-1 og derefter fra liste-2 efter hinanden.
Bemærk, at dette er en matrixformel, dvs. efter indtastning skal den indtastes i en celle, der ikke er almindelig Indtast, men med en tastaturgenvej Ctrl+Flytte+Indtast og kopier (træk) derefter ned til de underordnede celler med en margen.
I den engelske version af Excel ser denne formel ud som:
=IFERROR(IFERROR(INDEKS(Liste1, MATCH(0, ANTALHVIS($E$1:E1, Liste1), 0)), INDEKS(Liste2, MATCH(0, ANTALHVIS($E$1:E1, Liste2), 0)) ), "")
Ulempen ved denne fremgangsmåde er, at matrixformler mærkbart bremser arbejdet med filen, hvis kildetabellerne har et stort (flere hundrede eller flere) antal elementer.
Metode 3. Power Query
Hvis dine kildelister har et stort antal elementer, for eksempel flere hundrede eller tusinder, så er det i stedet for en langsom matrixformel bedre at bruge en fundamentalt anderledes tilgang, nemlig Power Query-tilføjelsesværktøjerne. Denne tilføjelse er som standard indbygget i Excel 2016. Hvis du har Excel 2010 eller 2013, kan du downloade og installere det separat (gratis).
Handlingsalgoritmen er som følger:
- Åbn en separat fane for den installerede tilføjelse Strømforespørgsel (hvis du har Excel 2010-2013) eller bare gå til fanen data (hvis du har Excel 2016).
- Vælg den første liste, og tryk på knappen Fra tabel/område (Fra Range/Tabel). Når vi bliver spurgt om at oprette en "smart tabel" fra vores liste, er vi enige:
- Forespørgselsredigeringsvinduet åbnes, hvor du kan se de indlæste data og forespørgselsnavnet Tabel 1 (du kan ændre den til din egen, hvis du vil).
- Dobbeltklik på tabeloverskriften (word Liste 1) og omdøb den til en hvilken som helst anden (f.eks Mennesker). Hvad man præcis skal navngive er ikke vigtigt, men det opfundne navn skal huskes, fordi. den skal bruges igen senere ved import af den anden tabel. At flette to tabeller i fremtiden vil kun fungere, hvis deres kolonneoverskrifter matcher.
- Udvid rullelisten i øverste venstre hjørne luk og download Og vælg Luk og indlæs… (Luk&indlæs til...):
- I den næste dialogboks (det kan se lidt anderledes ud – vær ikke forskrækket), skal du vælge Bare opret en forbindelse (Opret kun forbindelse):
- Vi gentager hele proceduren (punkt 2-6) for den anden liste. Når du omdøber en kolonneoverskrift, er det vigtigt at bruge samme navn (People) som i den forrige forespørgsel.
- I Excel-vinduet på fanen data eller på fanen Strømforespørgsel Vælg Hent data – Kombiner anmodninger – Tilføj (Hent data – flet forespørgsler – tilføj):
- I dialogboksen, der vises, skal du vælge vores anmodninger fra rullelisterne:
- Som følge heraf får vi en ny forespørgsel, hvor to lister bliver forbundet under hinanden. Det er tilbage at fjerne dubletter med knappen Slet rækker – Fjern dubletter (Slet rækker - Slet dubletter):
- Den færdige forespørgsel kan omdøbes i højre side af indstillingspanelet, hvilket giver den et fornuftigt navn (dette vil faktisk være navnet på resultattabellen), og alt kan uploades til arket med kommandoen luk og download (Luk&indlæs):
I fremtiden, med eventuelle ændringer eller tilføjelser til de originale lister, vil det være nok blot at højreklikke for at opdatere resultattabellen.
- Sådan samler du flere tabeller fra forskellige filer ved hjælp af Power Query
- Udtrækning af unikke genstande fra en liste
- Hvordan man sammenligner to lister med hinanden for kampe og forskelle