En klassisk situation: du har två listor som måste slås samman till en. Dessutom, i de initiala listorna kan det finnas både unika element och matchande (både mellan listorna och inuti), men vid utgången måste du få en lista utan dubbletter (upprepningar):
Låt oss traditionellt titta på flera sätt att lösa ett sådant vanligt problem - från primitivt "på pannan" till mer komplext, men elegant.
Metod 1: Ta bort dubbletter
Du kan lösa problemet på det enklaste sättet – kopiera elementen i båda listorna manuellt till en och använd sedan verktyget på den resulterande uppsättningen. Ta bort dubbletter från fliken Data (Data – Ta bort dubbletter):
Naturligtvis kommer den här metoden inte att fungera om data i källlistorna ofta ändras – du måste upprepa hela proceduren efter varje ändring igen.
Metod 1a. pivottabell
Denna metod är i själva verket en logisk fortsättning på den föregående. Om listorna inte är särskilt stora och det maximala antalet element i dem är känt i förväg (till exempel högst 10), kan du kombinera två tabeller till en genom direktlänkar, lägga till en kolumn med en till höger och skapa en sammanfattningstabell baserad på den resulterande tabellen:
Som ni vet ignorerar pivottabellen upprepningar, så vid utgången får vi en kombinerad lista utan dubbletter. Hjälpkolumnen med 1 behövs bara eftersom Excel kan bygga sammanfattningstabeller som innehåller minst två kolumner.
När de ursprungliga listorna ändras kommer de nya uppgifterna att gå till den kombinerade tabellen via direktlänkar, men pivottabellen måste uppdateras manuellt (högerklicka – Uppdatera & spara). Om du inte behöver omberäkning i farten, är det bättre att använda andra alternativ.
Metod 2: Matrisformel
Du kan lösa problemet med formler. I det här fallet kommer omräkningen och uppdateringen av resultaten att ske automatiskt och omedelbart, omedelbart efter ändringar i de ursprungliga listorna. För enkelhetens skull och korthetens skull, låt oss ge våra listor namn. Lista 1 и Lista 2med hjälp av Namnhanterare fliken formeln (Formler — Namnhanterare — Skapa):
Efter namngivning kommer formeln vi behöver se ut så här:
Vid första anblicken ser det läskigt ut, men i själva verket är allt inte så läskigt. Låt mig utöka den här formeln på flera rader med hjälp av tangentkombinationen Alt+Enter och indrag med mellanslag, som vi gjorde, till exempel här:
Logiken här är följande:
- Formeln INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) väljer alla unika element från den första listan. Så fort de tar slut börjar den ge ett #N/A-fel:
- Formeln INDEX(List2;MATCH(0;COUNTIF($E$1:E1;Lista2; 0)) extraherar de unika elementen från den andra listan på samma sätt.
- Inkapslade i varandra två IFERROR-funktioner implementerar utdata först av de unika från list-1, och sedan från list-2 en efter en.
Observera att detta är en matrisformel, dvs efter att ha skrivit måste den anges i en cell som inte är vanlig ange, men med ett kortkommando ctrl+shift+ange och kopiera (dra) sedan ner till de underordnade cellerna med en marginal.
I den engelska versionen av Excel ser denna formel ut så här:
=IFERROR(IFERROR(INDEX(Lista1, MATCH(0, ANTALOM($E$1:E1, List1), 0)), INDEX(Lista2, MATCH(0, ANTALOM($E$1:E1, List2), 0)) ), "")
Nackdelen med detta tillvägagångssätt är att matrisformler märkbart saktar ner arbetet med filen om källtabellerna har ett stort (flera hundra eller fler) antal element.
Metod 3. Power Query
Om dina källlistor har ett stort antal element, till exempel flera hundra eller tusentals, är det bättre att istället för en långsam arrayformel använda ett fundamentalt annorlunda tillvägagångssätt, nämligen Power Query-tilläggsverktygen. Detta tillägg är inbyggt i Excel 2016 som standard. Om du har Excel 2010 eller 2013 kan du ladda ner och installera det separat (gratis).
Handlingsalgoritmen är som följer:
- Öppna en separat flik för det installerade tillägget Strömfråga (om du har Excel 2010-2013) eller gå bara till fliken Data (om du har Excel 2016).
- Välj den första listan och tryck på knappen Från tabell/sortiment (Från Range/Table). När vi tillfrågas om att skapa ett "smart bord" från vår lista, håller vi med:
- Fönstret för frågeredigerare öppnas, där du kan se inlästa data och frågenamnet Tabell 1 (du kan ändra det till ditt eget om du vill).
- Dubbelklicka på tabellhuvudet (ord Lista 1) och byt namn på den till någon annan (till exempel Personer). Exakt vad man ska namnge är inte viktigt, men det påhittade namnet måste komma ihåg, eftersom. den kommer att behöva användas igen senare när den andra tabellen importeras. Att slå samman två tabeller i framtiden fungerar bara om deras kolumnrubriker matchar.
- Utöka rullgardinsmenyn i det övre vänstra hörnet stäng och ladda ner Och välj Stäng och ladda in... (Stäng och ladda till...):
- I nästa dialogruta (det kan se lite annorlunda ut – bli inte rädd), välj Skapa bara en anslutning (Skapa bara anslutning):
- Vi upprepar hela proceduren (punkterna 2-6) för den andra listan. När du byter namn på en kolumnrubrik är det viktigt att använda samma namn (People) som i föregående fråga.
- I Excel-fönstret på fliken Data eller på fliken Strömfråga Välja Hämta data – Kombinera förfrågningar – Lägg till (Hämta data – sammanfoga frågor – lägg till):
- I dialogrutan som visas väljer du våra förfrågningar från rullgardinslistorna:
- Som ett resultat kommer vi att få en ny fråga, där två listor kommer att kopplas samman under varandra. Det återstår att ta bort dubbletter med knappen Ta bort rader – Ta bort dubbletter (Ta bort rader – Ta bort dubbletter):
- Den färdiga frågan kan döpas om på höger sida av alternativpanelen, vilket ger den ett vettigt namn (detta kommer att vara namnet på resultattabellen faktiskt) och allt kan laddas upp till arket med kommandot stäng och ladda ner (Stäng&Ladda):
I framtiden, med eventuella ändringar eller tillägg till de ursprungliga listorna, kommer det att räcka med att bara högerklicka för att uppdatera resultattabellen.
- Hur man samlar in flera tabeller från olika filer med Power Query
- Extrahera unika föremål från en lista
- Hur man jämför två listor med varandra för matchningar och skillnader