Onlangs benaderde een vriend van mij me met een verzoek om te helpen bij het genereren van alle mogelijke zinnen bestaande uit een reeks gegeven woorden. Dit soort problemen kunnen zich voordoen bij het samenstellen van lijsten met trefwoorden en woordgroepen voor online adverteren en SEO-promotie, wanneer u alle mogelijke permutaties van woorden in een zoekopdracht moet doorlopen:
In de wiskunde heet deze bewerking Cartesiaans product. De officiële definitie is als volgt: het cartesiaanse product van verzamelingen A en B is de verzameling van alle paren, waarvan de eerste component tot verzameling A behoort en de tweede component tot verzameling B. Bovendien kunnen de elementen van verzamelingen beide zijn. cijfers en tekst.
Vertaald in mensentaal betekent dit dat als we in verzameling A bijvoorbeeld de woorden "wit" en "rood" hebben, en in verzameling B "BMW" en "Mercedes", dan na het cartesiaanse product van deze twee verzamelingen krijgen op de uitvoer is de verzameling van alle mogelijke varianten van zinnen, bestaande uit de woorden van beide lijsten:
- witte BMW
- rode BMW
- witte Mercedes
- rode mercedes
… dwz precies wat we nodig hebben. Laten we eens kijken naar een aantal manieren om deze taak in Excel op te lossen.
Methode 1. Formules
Laten we beginnen met formules. Laten we aannemen dat we als initiële gegevens drie lijsten met originele woorden hebben in respectievelijk kolommen A, B en C, en dat het aantal elementen in elke lijst kan variëren:
Laten we eerst drie kolommen maken met indices, dwz rangtelwoorden van woorden uit elke lijst in alle mogelijke combinaties. De eerste rij eenheden (E2:G2) wordt handmatig ingevoerd en voor de rest gebruiken we de volgende formule:
De logica hier is eenvoudig: als de index in de bovenste vorige cel het einde van de lijst al heeft bereikt, dwz gelijk is aan het aantal elementen in de lijst berekend door de functie COUNT (AANTALA), dan herstarten we de nummering. Anders verhogen we de index met 1. Let vooral op de slimme vaststelling van de ranges met dollartekens ($) zodat je de formule naar beneden en naar rechts kunt kopiëren.
Nu we de rangtelwoorden hebben van de woorden die we nodig hebben uit elke lijst, kunnen we de woorden zelf extraheren met behulp van de functie INDEX (INHOUDSOPGAVE) in drie afzonderlijke kolommen:
Als je deze functie nog niet eerder in je werk bent tegengekomen, dan raad ik je ten zeerste aan om het in ieder geval diagonaal te bestuderen – het helpt in veel situaties en is niet minder (en zelfs meer!) VPR (VERT.ZOEKEN).
Welnu, daarna blijft het alleen om de resulterende fragmenten regel voor regel te lijmen met behulp van het aaneenschakelingssymbool (&):
… of (als je de laatste versie van Excel hebt) met de handige functie COMBINEREN (TEXTJOIN), die de volledige inhoud van de opgegeven cellen kan lijmen door een bepaald scheidingsteken (spatie):
Methode 2. Via Power Query
Power Query is een krachtige invoegtoepassing voor Microsoft Excel die twee hoofdtaken uitvoert: 1. gegevens in Excel laden vanuit bijna elke externe bron, en 2. allerlei soorten transformaties van geladen tabellen. Power Query is al ingebouwd in Excel 2016-2019 en voor Excel 2010-2013 is het geïnstalleerd als een aparte invoegtoepassing (u kunt het gratis downloaden van de officiële Microsoft-website). Als u nog niet bent begonnen met het gebruik van Power Query in uw werk, is het tijd om erover na te denken, omdat transformaties zoals hierboven beschreven daar gemakkelijk en natuurlijk worden gedaan, in slechts een paar bewegingen.
Laten we eerst de bronlijsten als afzonderlijke query's in Power Query laden. Voer hiervoor voor elke tabel de volgende stappen uit:
- Laten we met een knop van tafels "slimme" tafels maken Opmaken als een tabel tab Home (Home - Opmaken als tabel) of sneltoets Ctrl+T. Elke tafel krijgt automatisch een naam Tabel1,2,3…, die echter desgewenst kan worden gewijzigd op het tabblad Constructeur (Ontwerp).
- Nadat u de actieve cel in de tabel hebt ingesteld, drukt u op de knop Van de tafel (Van tafel) tab Data (Datum) of op het tabblad Power Query (als je het hebt geïnstalleerd als een aparte invoegtoepassing voor Excel 2010-2013).
- In het query-editorvenster dat wordt geopend, selecteert u de opdracht Home — Sluiten en laden — Sluiten en laden in… (Home — Sluiten&Laden — Sluiten&Laden naar..) en dan de optie Maak gewoon een verbinding (Alleen verbinding maken). Hierdoor blijft de geladen tabel in het geheugen staan en kan deze in de toekomst worden geopend.
Als je alles correct doet, zou de uitvoer in het rechterpaneel drie verzoeken in de modus moeten zijn Alleen verbinding met onze tafelnamen:
Klik nu met de rechtermuisknop op de eerste query en selecteer de opdracht Link (Referentie)om er een bijwerkbare kopie van te maken en vervolgens een extra kolom aan de gegevens toe te voegen via de opdracht Een kolom toevoegen ž – Aangepaste kolom (Kolom toevoegen -ž Aangepaste kolom). Voer in het formule-invoervenster de naam van de nieuwe kolom in (bijvoorbeeld Fragment2) en een uiterst eenvoudige uitdrukking als formule:
=Tabel2
… dat wil zeggen, met andere woorden, de naam van de tweede query:
Na het klikken op OK we zullen een nieuwe kolom zien, in elke cel waarvan er een geneste tabel zal zijn met zinnen uit de tweede tabel (u kunt de inhoud van deze tabellen zien als u op de achtergrond van de cel naast het woord klikt tafel):
Het blijft over om alle inhoud van deze geneste tabellen uit te breiden met behulp van de knop met dubbele pijlen in de kop van de resulterende kolom en het vinkje weg te halen Gebruik de originele kolomnaam als voorvoegsel (Gebruik de originele kolomnaam als voorvoegsel):
... en we krijgen alle mogelijke combinaties van elementen uit de eerste twee sets:
Verder is alles vergelijkbaar. Voeg nog een berekende kolom toe met de formule:
=Tabel3
..., en breid dan de geneste tabellen weer uit - en nu hebben we al alle mogelijke opties voor het permuteren van woorden uit respectievelijk de drie sets:
Het blijft om alle drie de kolommen van links naar rechts te selecteren, terwijl u ingedrukt houdt Ctrl, en voeg hun inhoud gescheiden door spaties aan elkaar met behulp van de opdracht Kolommen samenvoegen (Kolommen samenvoegen) van het tabblad Transformatie (Transformeren):
De resulterende resultaten kunnen weer op het blad worden gelost met het al bekende commando Home — Sluiten en laden — Sluiten en laden in… (Home — Sluiten&Laden — Sluiten&Laden naar..):
Als er in de toekomst iets verandert in onze brontabellen met fragmenten, dan volstaat het om de gegenereerde query bij te werken door met de rechtermuisknop op de resulterende tabel te klikken en de opdracht te kiezen Bijwerken en opslaan (Vernieuwen) of door op de sneltoets te drukken Ctrl+anders+F5.
- Wat is Power Query, Power Pivot, Power Map en Power BI en waarom hebben ze een Excel-gebruiker nodig?
- Een Gantt-diagram maken in Power Query
- 5 manieren om de INDEX-functie te gebruiken