Nesen mans draugs vērsās pie manis ar lūgumu palīdzēt ģenerēt visas iespējamās frāzes, kas sastāv no doto vārdu kopas. Šāda veida problēmas var rasties, veidojot atslēgvārdu un frāžu sarakstus tiešsaistes reklamēšanai un SEO veicināšanai, kad meklēšanas vaicājumā ir jāiziet visas iespējamās vārdu permutācijas:
Matemātikā šo darbību sauc par Dekarta produkts. Oficiālā definīcija ir šāda: kopu A un B Dekarta reizinājums ir visu pāru kopa, kuras pirmais komponents pieder kopai A, bet otrais komponents pieder kopai B. Turklāt kopu elementi var būt gan cipariem un tekstu.
Tulkojumā cilvēku valodā tas nozīmē, ka, ja kopā A mums ir, piemēram, vārdi “balts” un “sarkans”, bet kopā B “BMW” un “Mercedes”, tad pēc šo divu kopu Dekarta reizinājuma mēs get on output ir visu iespējamo frāžu variantu kopa, kas sastāv no abu sarakstu vārdiem:
- balts bmw
- sarkans bmw
- balts mersedess
- sarkanās merceses
... ti, tieši tas, kas mums vajadzīgs. Apskatīsim pāris veidus, kā atrisināt šo uzdevumu programmā Excel.
1. metode. Formulas
Sāksim ar formulām. Pieņemsim, ka kā sākotnējie dati mums ir trīs oriģinālo vārdu saraksti attiecīgi A, B un C kolonnās, un elementu skaits katrā sarakstā var atšķirties:
Vispirms izveidosim trīs kolonnas ar indeksiem, ti, vārdu kārtas numuriem no katra saraksta visās iespējamās kombinācijās. Pirmā vienību rinda (E2:G2) tiks ievadīta manuāli, bet pārējām izmantosim šādu formulu:
Loģika šeit ir vienkārša: ja indekss augstākajā iepriekšējā šūnā jau ir sasniedzis saraksta beigas, ti, ir vienāds ar elementu skaitu sarakstā, ko aprēķina funkcija COUNT (COUNTA), tad mēs restartējam numerāciju. Pretējā gadījumā mēs palielinām indeksu par 1. Pievērsiet īpašu uzmanību gudrai diapazonu fiksēšanai ar dolāra zīmēm ($), lai jūs varētu kopēt formulu uz leju un pa labi.
Tagad, kad mums ir vajadzīgo vārdu kārtas skaitļi no katra saraksta, mēs varam izvilkt pašus vārdus, izmantojot funkciju INDEX (INDEX) trīs atsevišķās kolonnās:
Ja iepriekš savā darbā ar šo funkciju neesat saskāries, tad ļoti iesaku to izpētīt vismaz pa diagonāli – tā palīdz daudzās situācijās un noder ne mazāk (un pat vairāk!) VPR (MEKLĒT).
Nu, pēc tam atliek tikai rindu pa rindiņai pielīmēt iegūtos fragmentus, izmantojot savienojuma simbolu (&):
… vai (ja jums ir jaunākā Excel versija) ar parocīgo funkciju COMBINE (TEXTJOIN), kas var salīmēt visu norādīto šūnu saturu, izmantojot doto atdalītāju (atstarpi):
2. metode. Izmantojot Power Query
Power Query ir jaudīga Microsoft Excel pievienojumprogramma, kas veic divus galvenos uzdevumus: 1. datu ielādi programmā Excel no gandrīz jebkura ārēja avota un 2. visa veida ielādētu tabulu transformācijas. Power Query jau ir iebūvēts programmā Excel 2016–2019, un programmai Excel 2010–2013 tas ir instalēts kā atsevišķs pievienojumprogramma (to var bez maksas lejupielādēt no oficiālās Microsoft vietnes). Ja vēl neesi sācis savā darbā izmantot Power Query, tad ir laiks par to padomāt, jo tādas transformācijas kā iepriekš aprakstītās tur tiek veiktas viegli un dabiski, tikai pāris kustību laikā.
Vispirms ielādēsim avotu sarakstus kā atsevišķus vaicājumus programmā Power Query. Lai to izdarītu, katrai tabulai veiciet šādas darbības:
- Pārvērtīsim galdus par “gudriem” ar pogu Formatēt kā tabulu tab Sākumlapa (Sākums — formatēt kā tabulu) vai īsinājumtaustiņš Ctrl+T. Katrai tabulai automātiski tiks piešķirts nosaukums Tabula 1,2,3, XNUMX, XNUMX…, ko tomēr var mainīt, ja vēlaties cilnē Konstruktors (Dizains).
- Kad tabulā ir iestatīta aktīvā šūna, nospiediet pogu No galda (No tabulas) tab Datums (Datums) vai cilnē Jaudas vaicājums (ja tas ir instalēts kā atsevišķa pievienojumprogramma programmai Excel 2010–2013).
- Atvērtajā vaicājumu redaktora logā atlasiet komandu Sākums — Aizvērt un ielādēt — Aizvērt un ielādēt… (Sākums — Aizvērt&Ielādēt — Aizvērt&Ielādēt uz...) un tad opcija Vienkārši izveidojiet savienojumu (Izveidot tikai savienojumu). Tādējādi ielādētā tabula paliks atmiņā un ļaus tai piekļūt arī turpmāk.
Ja jūs visu darāt pareizi, tad izvadei labajā panelī jābūt trim pieprasījumiem režīmā Tikai savienojums ar mūsu tabulu nosaukumiem:
Tagad ar peles labo pogu noklikšķiniet uz pirmā vaicājuma un atlasiet komandu saite (Atsauce)lai izveidotu tā atjaunināmu kopiju, un pēc tam, izmantojot komandu, pievienojiet datiem papildu kolonnu Kolonnas pievienošana ž – Pielāgota kolonna (Pievienot kolonnu -ž pielāgota kolonna). Formulas ievades logā ievadiet jaunās kolonnas nosaukumu (piemēram, Fragment2) un ļoti vienkāršu izteiksmi kā formulu:
=2. tabula
… ti, citiem vārdiem sakot, otrā vaicājuma nosaukums:
Pēc noklikšķināšanas uz OK redzēsim jaunu kolonnu, kuras katrā šūnā būs ligzdota tabula ar frāzēm no otrās tabulas (šo tabulu saturu var redzēt, ja noklikšķināsiet šūnas fonā blakus vārdam Tabula):
Atliek izvērst visu šo ligzdoto tabulu saturu, izmantojot pogu ar dubultbultiņām iegūtās kolonnas galvenē un noņemot atzīmi Izmantojiet sākotnējo kolonnas nosaukumu kā prefiksu (Izmantojiet sākotnējo kolonnas nosaukumu kā prefiksu):
... un mēs iegūstam visas iespējamās elementu kombinācijas no pirmajām divām kopām:
Turklāt viss ir līdzīgi. Pievienojiet vēl vienu aprēķināto kolonnu ar formulu:
=3. tabula
… un pēc tam vēlreiz izvērsiet ligzdotās tabulas — un tagad mums jau ir visas iespējamās opcijas, lai attiecīgi mainītu vārdus no trim kopām:
Atliek atlasīt visas trīs kolonnas no kreisās uz labo, turot Ctrl, un savienojiet to saturu, atdalot to ar atstarpēm, izmantojot komandu Apvienot kolonnas (Apvienot kolonnas) no cilnes transformācija (Pārveidot):
Iegūtos rezultātus var izlādēt atpakaļ lapā, izmantojot jau pazīstamo komandu Sākums — Aizvērt un ielādēt — Aizvērt un ielādēt… (Sākums — Aizvērt&Ielādēt — Aizvērt&Ielādēt uz...):
Ja nākotnē mūsu avota tabulās kaut kas mainīsies ar fragmentiem, tad pietiks tikai ar ģenerētā vaicājuma atjaunināšanu, ar peles labo pogu noklikšķinot uz iegūtās tabulas un izvēloties komandu Atjaunināt un saglabāt (Atjaunot) vai nospiežot īsinājumtaustiņu Ctrl+cits+F5.
- Kas ir Power Query, Power Pivot, Power Map un Power BI un kāpēc tiem ir nepieciešams Excel lietotājs
- Ganta diagrammas izveide programmā Power Query
- 5 veidi, kā izmantot funkciju INDEX