For nylig henvendte en af mine venner sig til mig med en anmodning om at hjælpe med genereringen af alle mulige sætninger bestående af et sæt givne ord. Problemer af denne art kan opstå, når du udarbejder lister over søgeord og sætninger til online annoncering og SEO-promovering, når du skal gennemgå alle mulige ordskift i en søgeforespørgsel:
I matematik kaldes denne operation Kartesisk produkt. Den officielle definition er som følger: det kartesiske produkt af mængderne A og B er mængden af alle par, hvoraf den første komponent tilhører mængden A, og den anden komponent tilhører mængden B. Desuden kan elementerne i mængder være begge dele. tal og tekst.
Oversat til menneskeligt sprog betyder det, at hvis vi i sæt A f.eks. har ordene "hvid" og "rød", og i sæt B "BMW" og "Mercedes", så efter det kartesiske produkt af disse to sæt get on output er sættet af alle mulige varianter af sætninger, der består af ordene fra begge lister:
- hvid bmw
- rød bmw
- hvid Mercedes
- rød mercedes
… altså lige hvad vi har brug for. Lad os se på et par måder at løse denne opgave i Excel.
Metode 1. Formler
Lad os starte med formler. Lad os antage, at vi som indledende data har tre lister med originale ord i henholdsvis kolonne A, B og C, og antallet af elementer i hver liste kan variere:
Lad os først lave tre kolonner med indeks, altså ordenstal af ord fra hver liste i alle mulige kombinationer. Den første række af enheder (E2:G2) vil blive indtastet manuelt, og for resten vil vi bruge følgende formel:
Logikken her er enkel: hvis indekset i den overordnede forrige celle allerede har nået slutningen af listen, dvs. er lig med antallet af elementer i listen beregnet af funktionen COUNT (COUNTA), så genstarter vi nummereringen. Ellers øger vi indekset med 1. Vær særlig opmærksom på den smarte fastgørelse af intervallerne med dollartegn ($), så du kan kopiere formlen ned og til højre.
Nu hvor vi har ordenstallene for de ord, vi skal bruge fra hver liste, kan vi udtrække selve ordene ved hjælp af funktionen INDEX (INDEKS) i tre separate kolonner:
Hvis du ikke er stødt på denne funktion i dit arbejde før, så råder jeg dig kraftigt til at studere den i det mindste diagonalt – den hjælper i mange situationer og er nyttig ikke mindre (og endnu mere!) VPR (OPLYSNING).
Nå, efter det er det kun tilbage at lime de resulterende fragmenter linje for linje ved hjælp af sammenkædningssymbolet (&):
… eller (hvis du har den seneste version af Excel) med den praktiske funktion FORENE (TEXTJOIN), som kan lime hele indholdet af de specificerede celler gennem et givet separatortegn (mellemrum):
Metode 2. Gennem Power Query
Power Query er et kraftfuldt tilføjelsesprogram til Microsoft Excel, der udfører to hovedopgaver: 1. indlæsning af data til Excel fra næsten enhver ekstern kilde, og 2. alle former for transformationer af indlæste tabeller. Power Query er allerede indbygget i Excel 2016-2019, og til Excel 2010-2013 er det installeret som et separat tilføjelsesprogram (du kan downloade det fra det officielle Microsoft-websted gratis). Hvis du endnu ikke er begyndt at bruge Power Query i dit arbejde, så er det tid til at tænke over det, for transformationer som dem, der er beskrevet ovenfor, udføres der nemt og naturligt, i blot et par bevægelser.
Lad os først indlæse kildelisterne som separate forespørgsler i Power Query. For at gøre dette skal du udføre følgende trin for hver tabel:
- Lad os forvandle borde til "smarte" med en knap Formater som en tabel fanen Home (Hjem — Formater som tabel) eller tastaturgenvej Ctrl+T. Hvert bord får automatisk et navn Tabel 1,2,3..., som dog kan ændres, hvis det ønskes på fanen Constructor (Design).
- Når du har indstillet den aktive celle i tabellen, skal du trykke på knappen Fra bordet (Fra tabel) fanen data (Dato) eller på fanen Strømforespørgsel (hvis du har det installeret som et separat tilføjelsesprogram til Excel 2010-2013).
- I forespørgselsredigeringsvinduet, der åbnes, skal du vælge kommandoen Hjem — Luk og indlæs — Luk og indlæs... (Hjem — Luk&indlæs — Luk&indlæs til..) og så muligheden Bare opret en forbindelse (Opret kun forbindelse). Dette vil efterlade den indlæste tabel i hukommelsen og gøre det muligt at få adgang til den i fremtiden.
Hvis du gør alt korrekt, skal outputtet i højre panel være tre anmodninger i tilstanden Kun tilslutning med vores tabelnavne:
Højreklik nu på den første forespørgsel og vælg kommandoen Link (Reference)at lave en opdaterbar kopi af den, og derefter tilføje en ekstra kolonne til dataene via kommandoen Tilføjelse af en kolonne ž – Brugerdefineret kolonne (Tilføj kolonne -ž brugerdefineret kolonne). I formelindtastningsvinduet skal du indtaste navnet på den nye kolonne (for eksempel Fragment2) og et ekstremt simpelt udtryk som en formel:
=Tabel2
… dvs. med andre ord navnet på den anden forespørgsel:
Efter at have klikket på OK vi vil se en ny kolonne, hvor der i hver celle vil være en indlejret tabel med sætninger fra den anden tabel (du kan se indholdet af disse tabeller, hvis du klikker i baggrunden af cellen ved siden af ordet Bordlampe):
Det er tilbage at udvide alt indholdet af disse indlejrede tabeller ved at bruge knappen med dobbeltpile i overskriften på den resulterende kolonne og fjerne markeringen Brug det originale kolonnenavn som præfiks (Brug originalt kolonnenavn som præfiks):
… og vi får alle mulige kombinationer af elementer fra de første to sæt:
Yderligere er alt ens. Tilføj endnu en beregnet kolonne med formlen:
=Tabel3
…, og udvid så de indlejrede tabeller igen – og nu har vi allerede alle mulige muligheder for at permutere ord fra henholdsvis de tre sæt:
Det er tilbage at vælge alle tre kolonner fra venstre mod højre, mens du holder Ctrl, og sammenkæde deres indhold adskilt af mellemrum ved hjælp af kommandoen Flet kolonner (Flet kolonner) fra fanen Transformation (Transformer):
De resulterende resultater kan fjernes tilbage på arket med den allerede velkendte kommando Hjem — Luk og indlæs — Luk og indlæs... (Hjem — Luk&indlæs — Luk&indlæs til..):
Hvis noget i fremtiden ændrer sig i vores kildetabeller med fragmenter, vil det være nok bare at opdatere den genererede forespørgsel ved at højreklikke på den resulterende tabel og vælge kommandoen Opdater & Gem (Opdater) eller ved at trykke på tastaturgenvejen Ctrl+andre+F5.
- Hvad er Power Query, Power Pivot, Power Map og Power BI, og hvorfor har de brug for en Excel-bruger
- Oprettelse af et Gantt-diagram i Power Query
- 5 måder at bruge INDEX-funktionen på