Neseniai mano draugas kreipėsi į mane su prašymu padėti generuoti visas įmanomas frazes, sudarytas iš pateiktų žodžių rinkinio. Tokio pobūdžio problemų gali kilti sudarant internetinei reklamai ir SEO reklamai skirtų raktinių žodžių ir frazių sąrašus, kai paieškos užklausoje reikia peržiūrėti visas galimas žodžių permutacijas:
Matematikoje ši operacija vadinama Dekarto gaminys. Oficialus apibrėžimas yra toks: aibių A ir B Dekarto sandauga yra aibė visų porų, kurių pirmasis komponentas priklauso aibei A, o antrasis komponentas priklauso aibei B. Be to, aibių elementai gali būti abu skaičiai ir tekstas.
Išvertus į žmonių kalbą, tai reiškia, kad jei A aibėje turime, pavyzdžiui, žodžius „balta“ ir „raudona“, o aibėje B „BMW“ ir „Mercedes“, tai po šių dviejų aibių Dekarto sandauga Gauti išvestį yra visų galimų frazių variantų rinkinys, sudarytas iš abiejų sąrašų žodžių:
- baltas bmw
- raudonas bmw
- baltas mersedesas
- raudonasis mercedesas
... ty tai, ko mums reikia. Pažvelkime į keletą būdų, kaip išspręsti šią užduotį programoje „Excel“.
1 metodas. Formulės
Pradėkime nuo formulių. Tarkime, kad kaip pradinius duomenis turime tris originalių žodžių sąrašus atitinkamai A, B ir C stulpeliuose, o elementų skaičius kiekviename sąraše gali skirtis:
Pirmiausia padarykime tris stulpelius su indeksais, ty kiekvieno sąrašo žodžių eilės skaičiais visomis įmanomomis kombinacijomis. Pirmoji vienetų eilutė (E2:G2) bus įvedama rankiniu būdu, o likusioms naudosime šią formulę:
Logika čia paprasta: jei indeksas aukščiau esančiame ankstesniame langelyje jau pasiekė sąrašo pabaigą, ty yra lygus elementų skaičiui sąraše, kurį apskaičiuoja funkcija SKAIČIAVIMAS (COUNTA), tada pradedame numeruoti iš naujo. Kitu atveju padidiname indeksą 1. Ypatingą dėmesį atkreipkite į protingą diapazonų fiksavimą dolerio ženklais ($), kad galėtumėte nukopijuoti formulę žemyn ir į dešinę.
Dabar, kai turime eilinius žodžių, kurių mums reikia, skaičius iš kiekvieno sąrašo, galime išskirti pačius žodžius naudodami funkciją RODYKLĖ (INDEKSAS) į tris atskirus stulpelius:
Jei dar nesusidūrėte su šia funkcija savo darbe, primygtinai patariu ją išstudijuoti bent įstrižai – ji padeda daugelyje situacijų ir praverčia ne mažiau (o dar daugiau!) VPR (ŽIŪRĖTI).
Na, o po to lieka tik suklijuoti gautus fragmentus eilutė po eilutės naudojant sujungimo simbolį (&):
… arba (jei turite naujausią „Excel“ versiją) su patogia funkcija KOMBINUOKITE (TEXTJOIN), kuris gali suklijuoti visą nurodytų langelių turinį per nurodytą skyriklio simbolį (tarpą):
2 būdas. Per Power Query
Power Query yra galingas Microsoft Excel priedas, kuris atlieka dvi pagrindines užduotis: 1. įkelia duomenis į Excel beveik iš bet kokio išorinio šaltinio ir 2. atlieka visas įkeltų lentelių transformacijas. Power Query jau integruota į Excel 2016-2019, o Excel 2010-2013 ji įdiegta kaip atskiras priedas (galite atsisiųsti iš oficialios Microsoft svetainės nemokamai). Jei dar nepradėjote naudoti Power Query savo darbe, tuomet laikas apie tai pagalvoti, nes tokios transformacijos, kaip aprašytos aukščiau, ten atliekamos lengvai ir natūraliai, vos per porą judesių.
Pirmiausia įkelkime šaltinių sąrašus kaip atskiras „Power Query“ užklausas. Norėdami tai padaryti, kiekvienoje lentelėje atlikite šiuos veiksmus:
- Paverskime lenteles „išmaniaisiais“ su mygtuku Formatuokite kaip lentelę kortelė Pagrindinis (Pagrindinis – formatuoti kaip lentelę) arba spartusis klavišas "Ctrl"+T. Kiekviena lentelė bus automatiškai pavadinta 1,2,3 lentelė…, kurį, jei pageidaujama, galima pakeisti skirtuke Konstruktorius (Dizainas).
- Nustatę aktyvų langelį lentelėje, paspauskite mygtuką Nuo stalo (Iš lentelės) kortelė Duomenys (Data) arba skirtuke „Power Query“ (jei jį įdiegėte kaip atskirą „Excel 2010–2013“ priedą).
- Atsidariusiame užklausų rengyklės lange pasirinkite komandą Pagrindinis puslapis – Uždaryti ir įkelti – Uždaryti ir įkelti… (Pagrindinis – Uždaryti&Įkelti – Uždaryti&Įkelti į...) ir tada variantas Tiesiog sukurkite ryšį (Sukurti tik ryšį). Tai paliks įkelta lentelę atmintyje ir leis ją pasiekti ateityje.
Jei viską padarysite teisingai, dešiniajame skydelyje išvestis turėtų būti trys režimo užklausos Tik ryšys su mūsų lentelių pavadinimais:
Dabar dešiniuoju pelės mygtuku spustelėkite pirmąją užklausą ir pasirinkite komandą ryšys (Nuoroda)kad padarytumėte atnaujinamą jo kopiją, o tada naudodami komandą prie duomenų pridėkite papildomą stulpelį Stulpelio pridėjimas ž – Custom stulpelis (Pridėti stulpelį -ž tinkintas stulpelis). Formulės įvesties lange įveskite naujo stulpelio pavadinimą (pvz., Fragment2) ir labai paprastą išraišką kaip formulę:
=2 lentelė
… ty, kitaip tariant, antrosios užklausos pavadinimas:
Spustelėję OK pamatysime naują stulpelį, kurio kiekviename langelyje bus įdėta lentelė su frazėmis iš antrosios lentelės (šių lentelių turinį pamatysite paspaudę langelio fone prie žodžio Lentelė):
Belieka išplėsti visą šių įdėtų lentelių turinį, naudojant mygtuką su dvigubomis rodyklėmis gauto stulpelio antraštėje ir panaikinus žymėjimą Naudokite pradinį stulpelio pavadinimą kaip priešdėlį (Kaip priešdėlį naudokite pradinį stulpelio pavadinimą):
... ir mes gauname visus galimus elementų derinius iš pirmųjų dviejų rinkinių:
Be to, viskas panašu. Pridėkite kitą apskaičiuotą stulpelį su formule:
=3 lentelė
…, o tada vėl išplėskite įdėtas lenteles – ir dabar jau turime visas galimas parinktis atitinkamai pakeisti žodžius iš trijų rinkinių:
Belieka pasirinkti visus tris stulpelius iš kairės į dešinę, laikant "Ctrl", ir sujunkite jų turinį, atskirtą tarpais, naudodami komandą Sujungti stulpelius (Sujungti stulpelius) iš skirtuko Transformacija (Transformacija):
Gauti rezultatai gali būti iškelti atgal į lapą naudojant jau pažįstamą komandą Pagrindinis puslapis – Uždaryti ir įkelti – Uždaryti ir įkelti… (Pagrindinis – Uždaryti&Įkelti – Uždaryti&Įkelti į...):
Jei ateityje kas nors pasikeis mūsų šaltinio lentelėse su fragmentais, pakaks tik atnaujinti sugeneruotą užklausą dešiniuoju pelės klavišu spustelėjus gautą lentelę ir pasirinkus komandą Atnaujinkite ir išsaugokite (Atnaujinti) arba paspausdami spartųjį klavišą "Ctrl"+Kitas+F5.
- Kas yra „Power Query“, „Power Pivot“, „Power Map“ ir „Power BI“ ir kodėl jiems reikalingas „Excel“ vartotojas
- Ganto diagramos kūrimas Power Query
- 5 būdai, kaip naudoti funkciją INDEX