En klassisk situasjon: du har to lister som må slås sammen til én. I de innledende listene kan det dessuten være både unike elementer og samsvarende elementer (både mellom listene og innsiden), men ved utgangen må du få en liste uten duplikater (repetisjoner):
La oss tradisjonelt se på flere måter å løse et slikt vanlig problem på - fra primitiv "på pannen" til mer kompleks, men elegant.
Metode 1: Fjern duplikater
Du kan løse problemet på den enkleste måten - kopier elementene i begge listene manuelt til én og bruk deretter verktøyet på det resulterende settet. Fjern duplikater fra fanen Data (Data – Fjern duplikater):
Selvfølgelig vil denne metoden ikke fungere hvis dataene i kildelistene ofte endres – du må gjenta hele prosedyren etter hver endring på nytt.
Metode 1a. pivottabell
Denne metoden er faktisk en logisk fortsettelse av den forrige. Hvis listene ikke er veldig store og det maksimale antallet elementer i dem er kjent på forhånd (for eksempel ikke mer enn 10), kan du kombinere to tabeller til en ved direkte lenker, legge til en kolonne med en til høyre og lag en sammendragstabell basert på den resulterende tabellen:
Som du vet ignorerer pivottabellen repetisjoner, så ved utgangen vil vi få en kombinert liste uten duplikater. Hjelpekolonnen med 1 er bare nødvendig fordi Excel kan bygge sammendragstabeller som inneholder minst to kolonner.
Når de opprinnelige listene endres, vil de nye dataene gå til den kombinerte tabellen via direkte lenker, men pivottabellen må oppdateres manuelt (høyreklikk – Oppdater og lagre). Hvis du ikke trenger omberegning i farten, er det bedre å bruke andre alternativer.
Metode 2: Matriseformel
Du kan løse problemet med formler. I dette tilfellet vil omberegning og oppdatering av resultatene skje automatisk og umiddelbart, umiddelbart etter endringer i de originale listene. For enkelhets skyld og korthet, la oss gi listene våre navn. Liste 1 и Liste 2ved hjelp av Navnesjef tab formel (Formler – Navnebehandler – Opprett):
Etter navngiving vil formelen vi trenger se slik ut:
Ved første øyekast ser det skummelt ut, men faktisk er alt ikke så skummelt. La meg utvide denne formelen på flere linjer ved å bruke tastekombinasjonen Alt+Enter og rykke inn med mellomrom, slik vi gjorde, for eksempel her:
Logikken her er følgende:
- Formelen INDEX(List1;MATCH(0;COUNTIF($E$1:E1;Liste1); 0) velger alle unike elementer fra den første listen. Så snart de går tom, begynner den å gi en #N/A feil:
- Formelen INDEX(Liste2;MATCH(0;ANTALL($E$1:E1;Liste2; 0)) trekker ut de unike elementene fra den andre listen på samme måte.
- Nestet i hverandre implementerer to IFERROR-funksjoner utdata først av de unike fra liste-1, og deretter fra liste-2 etter hverandre.
Merk at dette er en matriseformel, dvs. etter skriving må den legges inn i en celle som ikke er vanlig Enter, men med en hurtigtast Ctrl+Skift+Enter og kopier (dra) deretter ned til de underordnede cellene med en marg.
I den engelske versjonen av Excel ser denne formelen slik ut:
=FEIL(FEILER(INDEKS(Liste1; MATCH(0; ANTALLHVIS($E$1:E1, Liste1); 0)), INDEKS(Liste2; SAMMENLIGN(0; ANTALLHVIS($E$1:E1, Liste2), 0)) ), "")
Ulempen med denne tilnærmingen er at matriseformler merkbart bremser arbeidet med filen hvis kildetabellene har et stort (flere hundre eller flere) antall elementer.
Metode 3. Power Query
Hvis kildelistene dine har et stort antall elementer, for eksempel flere hundre eller tusenvis, er det bedre å bruke en fundamentalt annen tilnærming i stedet for en langsom matriseformel, nemlig tilleggsverktøyene for Power Query. Dette tillegget er som standard innebygd i Excel 2016. Hvis du har Excel 2010 eller 2013, kan du laste ned og installere det separat (gratis).
Handlingsalgoritmen er som følger:
- Åpne en egen fane for det installerte tillegget Strømforespørsel (hvis du har Excel 2010-2013) eller bare gå til fanen Data (hvis du har Excel 2016).
- Velg den første listen og trykk på knappen Fra Tabell/Rekkevidde (Fra Range/Table). Når vi blir spurt om å lage et "smartbord" fra listen vår, er vi enige:
- Spørringsredigeringsvinduet åpnes, der du kan se de innlastede dataene og spørringsnavnet Tabell 1 (du kan endre den til din egen hvis du vil).
- Dobbeltklikk på tabelloverskriften (word Liste 1) og gi den et nytt navn (for eksempel Ansatte). Hva man skal navngi er ikke viktig, men det oppfunne navnet må huskes, fordi. den må brukes igjen senere ved import av den andre tabellen. Å slå sammen to tabeller i fremtiden vil bare fungere hvis kolonneoverskriftene deres samsvarer.
- Utvid rullegardinlisten i øvre venstre hjørne lukk og last ned Og velg Lukk og last inn... (Lukk&last til...):
- I den neste dialogboksen (det kan se litt annerledes ut – ikke bli skremt), velg Bare opprette en forbindelse (Bare opprette forbindelse):
- Vi gjentar hele prosedyren (punkt 2-6) for den andre listen. Når du gir nytt navn til en kolonneoverskrift, er det viktig å bruke samme navn (People) som i forrige spørring.
- I Excel-vinduet på fanen Data eller på fanen Strømforespørsel Velg Hent data – Kombiner forespørsler – Legg til (Hent data – Slå sammen søk – Legg til):
- I dialogboksen som vises, velg våre forespørsler fra rullegardinlistene:
- Som et resultat vil vi få en ny spørring, der to lister kobles sammen under hverandre. Det gjenstår å fjerne duplikater med knappen Slett rader – Fjern duplikater (Slett rader – Slett duplikater):
- Den ferdige spørringen kan gis nytt navn på høyre side av alternativpanelet, og gi den et fornuftig navn (dette vil faktisk være navnet på resultattabellen), og alt kan lastes opp til arket med kommandoen lukk og last ned (Lukk og last inn):
I fremtiden, med eventuelle endringer eller tillegg til de originale listene, vil det være nok å bare høyreklikke for å oppdatere resultattabellen.
- Hvordan samle flere tabeller fra forskjellige filer ved hjelp av Power Query
- Trekke ut unike elementer fra en liste
- Hvordan sammenligne to lister med hverandre for kamper og forskjeller