Combineer kolommen in Excel en genereer alle mogelijke combinaties

Excel consolideren

Heeft u twee verschillende datasets, bijvoorbeeld een klantenlijst en een productenlijst, en wilt u deze combineren om een ​​nieuwe lijst te krijgen met alle mogelijke combinaties van klanten en producten?

De oplossing is vrij eenvoudig, haalbaar met MS Excel in een paar minuten, en vereist, zelfs met duizenden ingangen, niet veel moeite - veel minder dan het kopiëren van de tweede lijst voor elke waarde van de eerste lijst - deze laatste oplossing zou uren duren met honderden inzendingen. Zie hieronder hoe u Excel-kolommen combineert en alle mogelijke combinaties Excel genereert.

Hoe alle mogelijke permutaties in Excel te genereren of op te sommen

• Maak een numerieke identificatiecode van 1 tot het aantal vermeldingen voor elk bronbestand en, in het resultaatbestand:
• Maak een numerieke ID (ID) in kolom A gaande van 1 tot de vermenigvuldiging van het aantal eerste bestandsingangen en tweede bestandsinvoeringen, maak zoveel regels als de combinatie van beide gegevenssets,
• Voeg een kolom toe, voer deze formule in en vouw deze uit tot de laatste regel = ROUNDUP (A2 / [Tweede aantal bestandsboekingen]; 0),
• Voeg een kolom toe, voer deze formule in en vouw deze uit tot de laatste regel = A2 - ([Tweede aantal bestandsboekingen] * (B2-1)),
• Voeg zoveel kolommen toe als u wilt krijgen van eerste en tweede bestanden, en doe vlookups op overeenkomstige identifier in resultaatbestand en bronbestanden.

Gegevens consolideren in Excel

Zie hieronder een volledig voorbeeld om kolommen met Excel-bladen te combineren, met een Klantenlijst en een Productenlijst.

Begin met het maken van identifiers in beide bestanden, door een kolom aan de linkerkant toe te voegen, waarden 1 en 2 in de eerste twee regels in te voeren, de twee cellen te selecteren, de muiscursor naar de rechterbenedenhoek van de selectie te verplaatsen en te dubbelklikken op het + teken om het identificatie-increment uit te breiden naar de laatste regel.

Als gevolg hiervan zijn de ID's verhoogd tot de laatste regel.

Hoe kolommen in Excel te combineren

Neem de maximale ID-nummers van zowel klanten (C) als producten (P). Maak een nieuw bestand, met een kolom-ID en herhaal de uitbreiding van het ID-teken, tot aan de regel (C * P) + 1. In onderstaand voorbeeld 7 verschillende waarden voor klanten en 7 voor producten, resulterend in 7 * 7 = 49 combinaties, + 1 lijn om de headerlijn goed te maken.

Hoe cellen in Excel te combineren

Word een Excel Pro: word lid van onze cursus!

Verhoog uw vaardigheden van Novice tot Hero met onze Excel 365 Basics -cursus, ontworpen om u in slechts enkele sessies bekwaam te maken.

Schrijf zich hier in

In kolom B plaatsen we de P-regels per klant, omdat elk van hen één regel per product heeft. Dit wordt gedaan door de onderstaande formule te gebruiken in de tweede regel van het blad en deze naar beneden uit te vouwen (denk aan de dubbele klik op het + -pictogram in de benedenhoek van de celselectie om dit te doen), waarbij X het tweede aantal bestandsaantekeningen is

In kolom C zetten we een telling voor elk van de klanten, van 1 tot Aantal producten. Dezelfde bewerking als hiervoor, met een andere formule (huidige regel-ID minus de telling bereikt voor vorige Klantregel), X is het tweede aantal bestandsingangen

Combineer meerdere kolommen in Excel in één kolom

Controleer of het werkt. Klanten worden P-maal herhaald en voor elk van hen wordt de product-ID herhaald van 1 tot P

Genereer alle permutaties

En dat is het! Vervolgens voegt u voor elke kolom die u wilt overnemen van het bestand Klanten of Producten een nieuwe kolom toe en voert u een Vlookup uit op de bijbehorende ID en het bronbestand.

Veelgestelde Vragen

Hoe kunnen gebruikers efficiënt meerdere kolommen in Excel combineren om een ​​lijst met alle mogelijke combinaties te genereren zonder complexe formules te gebruiken?

Gebruikers kunnen de functie Power Query van Excel gebruiken om kolommen samen te voegen en combinaties te maken. Door de kolommen te laden als een tabel op de voedingsquery, een aangepaste samenvoegbewerking toepassen en vervolgens de resulterende tabel uitbreiden, kunnen alle mogelijke combinaties systematisch worden gegenereerd en geretourneerd naar Excel.

Voltooi 2019 Excel voor beginners in video