Nylig henvendte en venn av meg meg med en forespørsel om å hjelpe med genereringen av alle mulige fraser som består av et sett med gitte ord. Problemer av denne typen kan oppstå når du setter sammen lister over søkeord og fraser for nettbasert annonsering og SEO-promotering, når du trenger å gå gjennom alle mulige permutasjoner av ord i et søk:
I matematikk kalles denne operasjonen Kartesisk produkt. Den offisielle definisjonen er som følger: det kartesiske produktet av sett A og B er settet av alle par, hvor den første komponenten tilhører sett A, og den andre komponenten tilhører sett B. Dessuten kan elementene i sett være både tall og tekst.
Oversatt til menneskelig språk betyr dette at hvis vi i sett A har for eksempel ordene "hvit" og "rød", og i sett B "BMW" og "Mercedes", så etter det kartesiske produktet av disse to settene get on output er settet med alle mulige varianter av fraser, som består av ordene fra begge listene:
- hvit bmw
- rød bmw
- hvit Mercedes
- rød mercedes
… dvs. akkurat det vi trenger. La oss se på et par måter å løse denne oppgaven i Excel.
Metode 1. Formler
La oss starte med formler. La oss anta at vi som startdata har tre lister med originalord i henholdsvis kolonne A, B og C, og antall elementer i hver liste kan variere:
La oss først lage tre kolonner med indekser, dvs. ordenstall på ord fra hver liste i alle mulige kombinasjoner. Den første raden med enheter (E2:G2) legges inn manuelt, og for resten bruker vi følgende formel:
Logikken her er enkel: hvis indeksen i den overordnede forrige cellen allerede har nådd slutten av listen, dvs. er lik antall elementer i listen beregnet av funksjonen COUNT (COUNTA), så starter vi nummereringen på nytt. Ellers øker vi indeksen med 1. Vær spesielt oppmerksom på den smarte fikseringen av områdene med dollartegn ($), slik at du kan kopiere formelen nedover og til høyre.
Nå som vi har ordenstallene til ordene vi trenger fra hver liste, kan vi trekke ut selve ordene ved å bruke funksjonen INDEX (INDEKS) i tre separate kolonner:
Hvis du ikke har kommet over denne funksjonen i arbeidet ditt før, anbefaler jeg deg på det sterkeste å studere den i det minste diagonalt - den hjelper i mange situasjoner og er nyttig ikke mindre (og enda mer!) VPR (VISNING).
Vel, etter det gjenstår det bare å lime de resulterende fragmentene linje for linje ved å bruke sammenkoblingssymbolet (&):
… eller (hvis du har den nyeste versjonen av Excel) med den praktiske funksjonen KOMBINERE (TEKST BLI MED), som kan lime hele innholdet i de spesifiserte cellene gjennom et gitt skilletegn (mellomrom):
Metode 2. Gjennom Power Query
Power Query er et kraftig tillegg for Microsoft Excel som utfører to hovedoppgaver: 1. laste inn data til Excel fra nesten hvilken som helst ekstern kilde, og 2. alle slags transformasjoner av innlastede tabeller. Power Query er allerede innebygd i Excel 2016-2019, og for Excel 2010-2013 er det installert som et eget tillegg (du kan laste det ned fra det offisielle Microsoft-nettstedet gratis). Hvis du ennå ikke har begynt å bruke Power Query i arbeidet ditt, er det på tide å tenke på det, fordi transformasjoner som de som er beskrevet ovenfor, gjøres der enkelt og naturlig, med bare et par bevegelser.
La oss først laste inn kildelistene som separate spørringer i Power Query. For å gjøre dette, for hver tabell, utfør følgende trinn:
- La oss gjøre tabeller om til "smarte" med en knapp Formater som en tabell tab Hjemprodukt (Hjem — Formater som tabell) eller hurtigtast Ctrl+T. Hvert bord vil automatisk få et navn Tabell 1,2,3…, som imidlertid kan endres om ønskelig på fanen Constructor (Design).
- Etter å ha satt den aktive cellen i tabellen, trykk på knappen Fra bordet (Fra tabell) tab Data (Dato) eller på fanen Strømforespørsel (hvis du har det installert som et eget tillegg for Excel 2010-2013).
- Velg kommandoen i spørringsredigeringsvinduet som åpnes Hjem — Lukk og last — Lukk og last inn... (Hjem — Lukk&last — Lukk&last til..) og deretter alternativet Bare opprette en forbindelse (Opprett kun tilkobling). Dette vil forlate den innlastede tabellen i minnet og tillate tilgang til den i fremtiden.
Hvis du gjør alt riktig, bør utgangen i høyre panel være tre forespørsler i modusen Kun tilkobling med våre tabellnavn:
Høyreklikk nå på den første spørringen og velg kommandoen link (Henvisning)for å lage en oppdaterbar kopi av den, og deretter legge til en ekstra kolonne til dataene via kommandoen Legge til en kolonne ž – Egendefinert kolonne (Legg til kolonne -ž egendefinert kolonne). I formelinntastingsvinduet skriver du inn navnet på den nye kolonnen (for eksempel Fragment2) og et ekstremt enkelt uttrykk som en formel:
=Tabell2
… dvs. med andre ord navnet på den andre spørringen:
Etter å ha klikket på OK vi vil se en ny kolonne, i hver celle som det vil være en nestet tabell med fraser fra den andre tabellen (du kan se innholdet i disse tabellene hvis du klikker i bakgrunnen av cellen ved siden av ordet Bord):
Det gjenstår å utvide alt innholdet i disse nestede tabellene ved å bruke knappen med doble piler i overskriften til den resulterende kolonnen og fjerne merket Bruk originalt kolonnenavn som prefiks (Bruk originalt kolonnenavn som prefiks):
… og vi får alle mulige kombinasjoner av elementer fra de to første settene:
Videre er alt likt. Legg til en annen beregnet kolonne med formelen:
=Tabell3
…, og utvide deretter de nestede tabellene igjen – og nå har vi allerede alle mulige alternativer for å permutere ord fra henholdsvis de tre settene:
Det gjenstår å velge alle tre kolonnene fra venstre til høyre, mens du holder Ctrl, og sett sammen innholdet deres atskilt med mellomrom ved å bruke kommandoen Slå sammen kolonner (Slå sammen kolonner) fra fanen Transformation (Forvandle):
De resulterende resultatene kan lastes tilbake på arket med den allerede kjente kommandoen Hjem — Lukk og last — Lukk og last inn... (Hjem — Lukk&last — Lukk&last til..):
Hvis noe i fremtiden endres i kildetabellene våre med fragmenter, vil det være nok bare å oppdatere den genererte spørringen ved å høyreklikke på den resulterende tabellen og velge kommandoen Oppdater og lagre (Forfriske) eller ved å trykke på hurtigtasten Ctrl+andre+F5.
- Hva er Power Query, Power Pivot, Power Map og Power BI og hvorfor trenger de en Excel-bruker
- Opprette et Gantt-diagram i Power Query
- 5 måter å bruke INDEX-funksjonen på