Hiljuti pöördus üks mu sõber minu poole palvega aidata genereerida kõiki võimalikke fraase, mis koosnevad etteantud sõnade komplektist. Sellised probleemid võivad tekkida veebireklaamide ja SEO reklaamide märksõnade ja fraaside loendite koostamisel, kui peate otsingupäringus läbi vaatama kõik võimalikud sõnade permutatsioonid:
Matemaatikas nimetatakse seda tehet Descartes'i toode. Ametlik määratlus on järgmine: hulkade A ja B Descartes'i korrutis on kõigi paaride hulk, mille esimene komponent kuulub hulka A ja teine komponent hulka B. Lisaks võivad hulkade elemendid olla mõlemad numbrid ja tekst.
Inimkeelde tõlgituna tähendab see seda, et kui komplektis A on meil näiteks sõnad “valge” ja “punane” ning komplektis B “BMW” ja “Mercedes”, siis nende kahe hulga Deskarti korrutise järel oleme väljund on kõigi võimalike fraasivariantide kogum, mis koosneb mõlema loendi sõnadest:
- valge bmw
- punane bmw
- valge Mercedes
- punane mercedes
… st just see, mida me vajame. Vaatame paar võimalust selle ülesande lahendamiseks Excelis.
Meetod 1. Valemid
Alustame valemitega. Oletame, et lähteandmetena on meil kolm algsete sõnade loendit vastavalt veergudes A, B ja C ning elementide arv igas loendis võib varieeruda:
Alustuseks teeme kolm veergu indeksitega ehk iga loendi sõnade järgarvud kõigis võimalikes kombinatsioonides. Esimene ühikute rida (E2:G2) sisestatakse käsitsi ja ülejäänud jaoks kasutame järgmist valemit:
Loogika on siin lihtne: kui indeks ülemises eelmises lahtris on juba jõudnud loendi lõppu, st võrdub funktsiooniga arvutatud loendis olevate elementide arvuga COUNT (COUNTA), siis alustame nummerdamist uuesti. Vastasel juhul suurendame indeksit 1 võrra. Pöörake erilist tähelepanu dollarimärkidega ($) vahemike nutikale fikseerimisele, et saaksite valemit alla ja paremale kopeerida.
Nüüd, kui meil on igast loendist vajalike sõnade järjekorranumbrid, saame funktsiooni abil eraldada sõnad ise INDEX (INDEX) kolme eraldi veergu:
Kui te pole selle funktsiooniga oma töös varem kokku puutunud, siis soovitan tungivalt seda vähemalt diagonaalselt uurida – see aitab paljudes olukordades ja on kasulik mitte vähem (ja isegi rohkem!) VPR (OTSING).
Noh, pärast seda jääb üle vaid saadud fragmendid rida-realt liimida, kasutades konkatenatsioonisümbolit (&):
… või (kui teil on Exceli uusim versioon) käepärase funktsiooniga COMBINE (TEXTJOIN), mis suudab liimida kogu määratud lahtrite sisu antud eraldaja (tühiku) kaudu:
2. meetod. Power Query kaudu
Power Query on Microsoft Exceli võimas lisandmoodul, mis täidab kahte peamist ülesannet: 1. laadib Excelisse andmeid peaaegu igast välisest allikast ja 2. laadib kõikvõimalikud laaditud tabelite teisendused. Power Query on juba Excelisse 2016–2019 sisse ehitatud ja Exceli 2010–2013 jaoks installitakse see eraldi lisandmoodulina (saate selle tasuta alla laadida Microsofti ametlikult veebisaidilt). Kui sa pole veel Power Queryt oma töös kasutama hakanud, siis on aeg sellele mõelda, sest ülalkirjeldatuga sarnased teisendused tehakse seal lihtsalt ja loomulikult, vaid paari liigutusega.
Esiteks laadime allikaloendid Power Querysse eraldi päringutena. Selleks tehke iga tabeli puhul järgmised toimingud.
- Muudame lauad nupuga “nutikateks”. Vorminda tabelina tab Avaleht (Avaleht — vorminda tabelina) või klaviatuuri otsetee Ctrl+T. Igale tabelile antakse automaatselt nimi Tabel 1,2,3, XNUMX, XNUMX…, mida saab aga soovi korral vahekaardil muuta Ehitaja (Kujundus).
- Pärast aktiivse lahtri määramist tabelis vajutage nuppu Laualt (Tabelist) tab kuupäev (Kuupäev) või vahekaardil Toite päring (kui see on installitud eraldi lisandmoodulina Exceli 2010–2013 jaoks).
- Avanevas päringuredaktori aknas valige käsk Avaleht — sulge ja laadi — sulge ja laadi... (Avaleht — Sulge&laadi — Sule&laadi...) ja siis valik Looge lihtsalt ühendus (Loo ainult ühendus). See jätab laaditud tabeli mällu ja võimaldab sellele tulevikus juurde pääseda.
Kui teete kõik õigesti, peaks parempoolse paneeli väljund režiimis olema kolm päringut Ainult ühendus meie tabelinimedega:
Nüüd paremklõpsake esimesel päringul ja valige käsk on siin (Viide)teha sellest värskendatav koopia ja seejärel lisada käsu kaudu andmetele täiendav veerg Veeru lisamine ž – Kohandatud veerg (Lisa veerg -ž kohandatud veerg). Sisesta valemi sisestusaknasse uue veeru nimi (näiteks Fragment2) ja ülilihtne avaldis valemina:
=Tabel2
… ehk teisisõnu teise päringu nimi:
Pärast klõpsamist nupul OK näeme uut veergu, mille igas lahtris on pesastatud tabel teise tabeli fraasidega (nende tabelite sisu näed kui klõpsad lahtri taustal sõna kõrval Tabel):
Jääb üle kogu nende pesastatud tabelite sisu laiendada, kasutades saadud veeru päises topeltnooltega nuppu ja tühjendada märke Kasutage eesliitena algset veeru nime (Kasutage eesliitena algset veeru nime):
… ja saame kõik võimalikud elementide kombinatsioonid kahest esimesest komplektist:
Lisaks on kõik sarnased. Lisage veel üks arvutatud veerg järgmise valemiga:
=Tabel3
… ja seejärel laiendage uuesti pesastatud tabeleid – ja nüüd on meil juba kõik võimalikud valikud sõnade permuteerimiseks vastavalt kolmest komplektist:
Jääb valida kõik kolm veergu vasakult paremale, hoides all Ctrlja ühendage nende sisu tühikutega eraldatuna käsu abil Ühendage veerud (Ühenda veerud) vahekaardilt Transformation (teisendus):
Saadud tulemused saab juba tuttava käsuga lehele tagasi laadida Avaleht — sulge ja laadi — sulge ja laadi... (Avaleht — Sulge&laadi — Sule&laadi...):
Kui tulevikus meie lähtetabelites fragmentidega midagi muutub, siis piisab genereeritud päringu värskendamisest, paremklõpsates saadud tabelis ja valides käsu Värskenda ja salvesta (Värskenda) või vajutades kiirklahvi Ctrl+muu+F5.
- Mis on Power Query, Power Pivot, Power Map ja Power BI ning miks on neil vaja Exceli kasutajat
- Gantti diagrammi loomine Power Querys
- 5 võimalust funktsiooni INDEX kasutamiseks