Contents [show]
Onlangs het 'n vriend van my my genader met 'n versoek om te help met die generering van alle moontlike frases wat uit 'n stel gegewe woorde bestaan. Probleme van hierdie soort kan ontstaan wanneer jy lyste van sleutelwoorde en frases saamstel vir aanlyn advertensies en SEO-bevordering, wanneer jy deur al die moontlike permutasies van woorde in 'n soektog moet gaan:
In wiskunde word hierdie bewerking genoem Cartesiese produk. Die amptelike definisie is soos volg: die Cartesiese produk van versamelings A en B is die versameling van alle pare, waarvan die eerste komponent aan versameling A behoort, en die tweede komponent aan versameling B behoort. Verder kan die elemente van versamelings beide wees nommers en teks.
Vertaal in mensetaal beteken dit dat as ons in versameling A byvoorbeeld die woorde “wit” en “rooi”, en in stel B “BMW” en “Mercedes” het, dan na die Cartesiese produk van hierdie twee stelle ons kry op die uitset is die stel van alle moontlike variante van frases, saamgestel uit die woorde van beide lyste:
- wit bmw
- rooi bmw
- wit Mercedes
- rooi mercedes
… dws net wat ons nodig het. Kom ons kyk na 'n paar maniere om hierdie taak in Excel op te los.
Metode 1. Formules
Kom ons begin met formules. Kom ons neem aan dat ons as aanvanklike data drie lyste oorspronklike woorde in onderskeidelik kolomme A, B en C het, en die aantal elemente in elke lys kan verskil:
Kom ons maak eers drie kolomme met indekse, dit wil sê ranggetalle van woorde uit elke lys in alle moontlike kombinasies. Die eerste ry eenhede (E2:G2) sal met die hand ingevoer word, en vir die res sal ons die volgende formule gebruik:
Die logika hier is eenvoudig: as die indeks in die superieure vorige sel reeds die einde van die lys bereik het, maw gelyk is aan die aantal elemente in die lys wat deur die funksie bereken word COUNT (COUNTA), dan herbegin ons die nommering. Andersins verhoog ons die indeks met 1. Let veral op die slim vasstelling van die reekse met dollartekens ($) sodat jy die formule na onder en na regs kan kopieer.
Noudat ons die rangnommers van die woorde wat ons benodig uit elke lys het, kan ons die woorde self onttrek deur die funksie te gebruik INDEKS (INDEKS) in drie aparte kolomme:
As jy nog nie vantevore hierdie funksie in jou werk teëgekom het nie, raai ek jou sterk aan om dit ten minste skuins te bestudeer – dit help in baie situasies en is nie minder (en selfs meer nie!) nuttig! VPR (VERSOEKING).
Wel, daarna bly dit net om die resulterende fragmente reël vir reël vas te plak deur die samevoegingssimbool (&) te gebruik:
… of (as jy die nuutste weergawe van Excel het) met die handige funksie kombineer (TEKSSLUIT), wat die hele inhoud van die gespesifiseerde selle deur 'n gegewe skeidingskarakter (spasie) kan plak:
Metode 2. Deur Power Query
Power Query is 'n kragtige byvoeging vir Microsoft Excel wat twee hooftake verrig: 1. laai data in Excel vanaf byna enige eksterne bron, en 2. allerhande transformasies van gelaaide tabelle. Power Query is reeds in Excel 2016-2019 ingebou, en vir Excel 2010-2013 is dit as 'n aparte byvoeging geïnstalleer (jy kan dit gratis van die amptelike Microsoft-webwerf aflaai). As jy nog nie Power Query in jou werk begin gebruik het nie, dan is dit tyd om daaroor na te dink, want transformasies soos die hierbo beskryf word maklik en natuurlik daar gedoen, in net 'n paar bewegings.
Kom ons laai eers die bronlyste as aparte navrae in Power Query. Om dit te doen, voer die volgende stappe vir elke tabel uit:
- Kom ons verander tafels in "slim" tafels met 'n knoppie Formateer as 'n tabel tab What is This (Tuis — Formateer as tabel) of sleutelbordkortpad Ctrl+T. Elke tafel sal outomaties 'n naam kry Tabel 1,2,3..., wat egter verander kan word indien verlang op die blad konstruktor (Ontwerp).
- Nadat u die aktiewe sel in die tabel gestel het, druk die knoppie Van die tafel af (Vanaf tabel) tab data (Datum) of op die blad Kragnavraag (as jy dit as 'n aparte byvoeging vir Excel 2010-2013 geïnstalleer het).
- Kies die opdrag in die navraagredigeerdervenster wat oopmaak Tuis — Maak toe en laai — Maak toe en laai in... (Tuis — Maak toe en laai — Maak toe en laai na..) en dan die opsie Skep net 'n verbinding (Skep slegs verbinding). Dit sal die gelaaide tabel in die geheue laat en dit in die toekoms toelaat om toegang te verkry.
As u alles korrek doen, moet die uitset in die regterpaneel drie versoeke in die modus wees Slegs verbinding met ons tabelname:
Regskliek nou op die eerste navraag en kies die opdrag Link (Verwysing)om 'n opdateerbare kopie daarvan te maak, en voeg dan 'n bykomende kolom by die data via die opdrag Voeg 'n kolom by ž – Pasgemaakte kolom (Voeg kolom by -ž gepasmaakte kolom). In die formule-invoervenster, voer die naam van die nuwe kolom in (byvoorbeeld Fragment2) en 'n uiters eenvoudige uitdrukking as 'n formule:
=Tabel2
… dws, met ander woorde, die naam van die tweede navraag:
Na kliek op OK ons sal 'n nuwe kolom sien, in elke sel waarvan daar 'n geneste tabel sal wees met frases uit die tweede tabel (jy kan die inhoud van hierdie tabelle sien as jy in die agtergrond van die sel langs die woord klik Tabel):
Dit bly om al die inhoud van hierdie geneste tabelle uit te brei deur die knoppie met dubbele pyle in die kop van die resulterende kolom te gebruik en ontmerk Gebruik oorspronklike kolomnaam as voorvoegsel (Gebruik oorspronklike kolomnaam as voorvoegsel):
… en ons kry alle moontlike kombinasies van elemente uit die eerste twee stelle:
Verder is alles soortgelyk. Voeg nog 'n berekende kolom by met die formule:
=Tabel3
…, en brei dan die geneste tabelle weer uit – en nou het ons reeds al die moontlike opsies om woorde uit die drie stelle onderskeidelik te permuteer:
Dit bly om al drie kolomme van links na regs te kies, hou Ctrl, en koppel hul inhoud geskei deur spasies met behulp van die opdrag Voeg kolomme saam (Voeg kolomme saam) vanaf die blad Transformasie (Transformeer):
Die gevolglike resultate kan terug op die blad afgelaai word met die reeds bekende opdrag Tuis — Maak toe en laai — Maak toe en laai in... (Tuis — Maak toe en laai — Maak toe en laai na..):
As iets in die toekoms in ons brontabelle met fragmente verander, sal dit genoeg wees om net die gegenereerde navraag by te werk deur regs te klik op die resulterende tabel en die opdrag te kies Dateer op en stoor (Verfris) of deur die sleutelbordkortpad te druk Ctrl+alt+F5.
- Wat is Power Query, Power Pivot, Power Map en Power BI en hoekom het hulle 'n Excel-gebruiker nodig
- Skep 'n Gantt-kaart in Power Query
- 5 maniere om die INDEX-funksie te gebruik