Setningaframleiðandi úr tilteknum brotum

Nýlega leitaði vinur minn til mín með beiðni um að hjálpa til við að búa til allar mögulegar setningar sem samanstanda af settum tilteknum orðum. Vandamál af þessu tagi geta komið upp við að setja saman lista yfir leitarorð og orðasambönd fyrir netauglýsingar og SEO kynningu, þegar þú þarft að fara í gegnum allar mögulegar orðbreytingar í leitarfyrirspurn:

Í stærðfræði er þessi aðgerð kölluð Cartesísk vara. Opinbera skilgreiningin er sem hér segir: Kartesísk afurð menganna A og B er mengi allra para, þar sem fyrsti hluti þeirra tilheyrir mengi A og annar hluti tilheyrir mengi B. Þar að auki geta þættir mengja verið bæði tölur og texti.

Þýtt á mannamál þýðir þetta að ef í mengi A höfum við til dæmis orðin „hvítur“ og „rautt“ og í mengi B „BMW“ og „Mercedes“, þá á eftir kartesísku afurð þessara tveggja menga get on the output er mengi allra mögulegra afbrigða af orðasamböndum, samsett úr orðum beggja listanna:

• hvítur bmw
• rauður bmw
• hvítur Mercedes
• rauð Mercedes
  

… þ.e. bara það sem við þurfum. Við skulum skoða nokkrar leiðir til að leysa þetta verkefni í Excel.

Aðferð 1. Formúlur

Byrjum á formúlum. Gerum ráð fyrir að sem upphafsgögn höfum við þrjá lista yfir frumorð í dálkum A, B og C, í sömu röð, og fjöldi þátta í hverjum lista getur verið mismunandi:

Fyrst skulum við búa til þrjá dálka með vísitölum, þ.e. raðtölur orða úr hverjum lista í öllum mögulegum samsetningum. Fyrsta röð eininga (E2:G2) verður færð inn handvirkt og fyrir rest notum við eftirfarandi formúlu:

Rökfræðin hér er einföld: ef vísitalan í efstu fyrri reitnum hefur þegar náð enda listans, þ.e. er jöfn fjölda staka í listanum sem reiknaður er út af fallinu COUNT (COUNTA), þá endurræsum við tölusetninguna. Annars hækkum við vísitöluna um 1. Gefðu sérstaklega gaum að snjöllum festingum á bilunum með dollaramerkjum ($) svo þú getir afritað formúluna niður og til hægri.

Nú þegar við höfum raðtölur orðanna sem við þurfum úr hverjum lista, getum við dregið út orðin sjálf með því að nota fallið INDEX (VÍSITALA) í þrjá aðskilda dálka:

Ef þú hefur ekki rekist á þessa aðgerð áður í starfi þínu, þá ráðlegg ég þér eindregið að kynna þér hana að minnsta kosti á ská – hún hjálpar í mörgum aðstæðum og nýtist ekki síður (og jafnvel meira!) VPR (SKRÁNING).

Jæja, eftir það er aðeins eftir að líma brotin sem myndast línu fyrir línu með því að nota samtengingartáknið (&):

… eða (ef þú ert með nýjustu útgáfuna af Excel) með handhægu aðgerðinni Sameina (TEXTJOIN), sem getur límt allt innihald tilgreindra frumna í gegnum tiltekið skiljustaf (bil):

Aðferð 2. Í gegnum Power Query

Power Query er öflug viðbót fyrir Microsoft Excel sem framkvæmir tvö meginverkefni: 1. hleðsla gagna í Excel frá nánast hvaða utanaðkomandi aðilum sem er og 2. alls kyns umbreytingar á hlaðnum töflum. Power Query er nú þegar innbyggt í Excel 2016-2019 og fyrir Excel 2010-2013 er það sett upp sem sérstök viðbót (þú getur halað því niður af opinberu Microsoft vefsíðunni ókeypis). Ef þú hefur ekki enn byrjað að nota Power Query í vinnunni þinni, þá er kominn tími til að hugsa um það, því umbreytingar eins og þær sem lýst er hér að ofan eru gerðar þar auðveldlega og eðlilega, í aðeins nokkrum hreyfingum.

Fyrst skulum við hlaða upprunalistanum sem aðskildar fyrirspurnir í Power Query. Til að gera þetta skaltu framkvæma eftirfarandi skref fyrir hverja töflu:

1. Við skulum breyta borðum í „snjöll“ með hnappi Snið sem töflu flipi Heim (Heima - Snið sem töflu) eða flýtilykla Ctrl+T. Hvert borð fær sjálfkrafa nafn Tafla 1,2,3…, sem þó er hægt að breyta ef þess er óskað á flipanum Framkvæmdaaðili (Hönnun).
   
2. Þegar þú hefur stillt virka reitinn í töflunni skaltu ýta á hnappinn Frá borði (Úr töflu) flipi Gögn (Dagsetning) eða á flipanum Orkufyrirspurn (ef þú ert með það uppsett sem sérstakt viðbót fyrir Excel 2010-2013).
3. Í fyrirspurnarritaraglugganum sem opnast velurðu skipunina Heim — Loka og hlaða — Loka og hlaða inn... (Heima — Loka&hlaða — Loka&hlaða til..) og svo valmöguleikinn Búðu bara til tengingu (Búa til aðeins tengingu). Þetta mun skilja hlaðna töfluna eftir í minni og leyfa aðgang að henni í framtíðinni.

Ef þú gerir allt rétt, þá ætti framleiðsla á hægri spjaldi að vera þrjár beiðnir í ham Aðeins tenging með töflunöfnum okkar:

Hægrismelltu núna á fyrstu fyrirspurnina og veldu skipunina Link (Tilvísun)til að gera uppfæranlegt afrit af því og bæta síðan viðbótardálki við gögnin með skipuninni Að bæta við dálki ž – Sérsniðinn dálkur (Bæta við dálki -ž sérsniðnum dálki). Í formúluinnsláttarglugganum, sláðu inn nafn nýja dálksins (til dæmis Fragment2) og afar einföld tjáning sem formúlu:

=Tafla2

… þ.e., með öðrum orðum, nafn seinni fyrirspurnarinnar:

Eftir að smella á OK við munum sjá nýjan dálk, í hverri reit þar sem verður hreiður tafla með orðasamböndum úr annarri töflunni (þú getur séð innihald þessara taflna ef þú smellir í bakgrunni reitsins við hliðina á orðinu Tafla):

Það er eftir að stækka allt innihald þessara hreiðra taflna með því að nota hnappinn með tvöföldum örvum í haus dálksins sem myndast og taka hakið af Notaðu upprunalega dálknafnið sem forskeyti (Notaðu upprunalegt dálknafn sem forskeyti):

… og við fáum allar mögulegar samsetningar frumefna úr fyrstu tveimur settunum:

Ennfremur er allt svipað. Bættu við öðrum reiknuðum dálki með formúlunni:

=Tafla3

…, og stækkaðu svo hreiðruðu töflurnar aftur – og nú höfum við nú þegar alla mögulega möguleika til að breyta orðum úr settunum þremur, í sömu röð:

Það er eftir að velja alla þrjá dálkana frá vinstri til hægri, halda Ctrl, og sameina innihald þeirra aðskilið með bilum með því að nota skipunina Sameina dálka (Sameina dálka) af flipanum Umbreyting (Breyta):

Niðurstöðurnar sem myndast er hægt að afferma aftur á blaðið með skipuninni sem þegar er kunnugleg Heim — Loka og hlaða — Loka og hlaða inn... (Heima — Loka&hlaða — Loka&hlaða til..):

Ef í framtíðinni eitthvað breytist í upprunatöflunum okkar með brotum, þá er nóg að uppfæra fyrirspurnina sem myndast með því að hægrismella á töfluna sem myndast og velja skipunina Uppfærðu og vistaðu (Endurnýja) eða með því að ýta á flýtilykla Ctrl+Alt+F5.

• Hvað er Power Query, Power Pivot, Power Map og Power BI og hvers vegna þurfa þeir Excel notanda
  
• Að búa til Gantt mynd í Power Query
• 5 leiðir til að nota INDEX aðgerðina