Kombinera kolumner i Excel och generera alla möjliga kombinationer

Excel konsolidera

Har två olika dataset, till exempel en kundlista och en produktlista, och vill kombinera dem för att få en ny lista med alla möjliga kombinationer av kunder och produkter?

Lösningen är ganska enkel, möjlig med MS Excel om några minuter, och inte ens med tusentals inlägg kräver det mycket arbete - mycket mindre än att kopiera klistra in den andra listan för varje värde i den första listan - den här sista lösningen skulle ta timmar med hundratals poster. Se nedan hur man kombinerar Excel-kolumner och genererar alla möjliga kombinationer Excel.

Hur genererar eller listar alla möjliga permutationer i Excel

• Skapa en numerisk identifierare från 1 till antalet poster för varje källfil och i resultatfilen:
• Skapa en numerisk identifierare (ID) i kolumn A som går från 1 till multipliceringen av antalet första filposter och andra filposter, skapa så många rader som kombinationen av båda datasætten,
• Lägg till en kolumn, skriv in denna formel och expandera den till sista raden = ROUNDUP (A2 / [Andra filposter räknas]; 0),
• Lägg till en kolumn, ange denna formel och expandera den till den sista raden = A2 - ([Räkning av andra poster] * (B2-1)),
• Lägg till så många kolumner som du vill hämta från första och andra filer, och gör loopar på motsvarande identifierare i resultatfil och källfiler.

Konsolidera data i Excel

Se nedan ett komplett exempel för att kombinera Excel-arks kolumner, med en kundlista och en produktlista.

Börja med att skapa identifierare i båda filerna genom att lägga till en kolumn till vänster, ange värden 1 och 2 i de två första raderna, välj de två cellerna, flytta muspekaren till det nedre högra hörnet på valet och dubbelklicka på + tecknet för att expandera identifieringsinkrementet till den sista raden.

Som ett resultat har identifierarna ökats upp till sista raden.

Hur man kombinerar kolumner i excel

Ta upp de maximala identifieringsnumren för både Kunder (C) och Produkter (P). Skapa en ny fil med en kolumnbeteckning och upprepa identifieringsexpanderingsoperationen, ned till raden (C * P) + 1. I exempel nedan visas 7 olika värden för kunder och 7 för produkter, vilket resulterar i 7 * 7 = 49 kombinationer, + 1 linje för att kompensera för rubriklinjen.

Hur man kombinerar celler i excel

Bli en Excel Pro: Gå med i vår kurs!

Höj dina färdigheter från nybörjare till hjälte med vår Excel 365 -grunder, utformad för att göra dig skicklig i bara några få sessioner.

Anmäla sig här

I kolumn B lägger vi P-linjerna per kund, eftersom var och en av dem kommer att ha en rad per produkt. Detta görs genom att använda följande formel i arkets andra rad och expandera den till botten (kom ihåg dubbelklick på + -ikonen i det nedre hörnet av cellvalet för att göra det), där X är den andra filposten

I kolumn C lägger vi ett tal för var och en av Kunderna, från 1 till Produkter räknas. Samma operation som tidigare, med en annan formel (nuvarande radidentifierare minus räkningen uppnådd för tidigare kundlinje), X är den andra filposten räknat

Kombinera flera kolumner i Excel till en kolumn

Kontrollera om det har fungerat. Kunderna upprepas P gånger, och för var och en av dem upprepas produktidentifieraren från 1 till P

Generera alla permutationer

Och det är allt! Sedan, för varje kolumn du vill ta över från Kunder eller Produktfil, lägg till en ny kolumn och utför en Vlookup på motsvarande identifierare och källfil.

Vanliga Frågor

Hur kan användare effektivt kombinera flera kolumner i Excel för att generera en lista över alla möjliga kombinationer utan att använda komplexa formler?

Användare kan använda Excels Power Query -funktion för att slå samman kolumner och skapa kombinationer. Genom att ladda kolumnerna som en tabell för att driva frågan, tillämpa en anpassad sammanslagningsoperation och sedan utvidga den resulterande tabellen, kan alla möjliga kombinationer systematiskt genereras och returneras till Excel.

Komplett Excel Excel för nybörjare i video