Äskettäin eräs ystäväni otti minuun pyynnön auttaa luomaan kaikki mahdolliset lauseet, jotka koostuvat tietyistä sanoista. Tällaisia ongelmia voi syntyä, kun laaditaan avainsana- ja lauseluetteloita verkkomainonnan ja hakukoneoptimoinnin edistämiseen, kun sinun täytyy käydä läpi kaikki mahdolliset sanamuodot hakukyselyssä:
Matematiikassa tätä operaatiota kutsutaan karteesinen tuote. Virallinen määritelmä on seuraava: Joukkojen A ja B karteesinen tulo on kaikkien parien joukko, joiden ensimmäinen komponentti kuuluu joukkoon A ja toinen komponentti joukkoon B. Lisäksi joukkojen alkiot voivat olla molemmat numeroita ja tekstiä.
Ihmiskielelle käännettynä tämä tarkoittaa, että jos joukossa A on esimerkiksi sanat "valkoinen" ja "punainen" ja joukossa B "BMW" ja "Mercedes", niin näiden kahden joukon karteesisen tulon jälkeen get on output on joukko kaikkia mahdollisia lausekkeiden muunnelmia, joka koostuu molempien luetteloiden sanoista:
- valkoinen bmw
- punainen bmw
- valkoinen Mercedes
- punainen mercedes
… eli juuri sitä mitä tarvitsemme. Katsotaanpa pari tapaa ratkaista tämä tehtävä Excelissä.
Menetelmä 1. Kaavat
Aloitetaan kaavoilla. Oletetaan, että lähtötietona meillä on kolme listaa alkuperäisistä sanoista sarakkeissa A, B ja C, ja kunkin listan elementtien määrä voi vaihdella:
Tehdään ensin kolme saraketta indekseillä, eli kustakin listasta sanojen järjestysluvut kaikissa mahdollisissa yhdistelmissä. Ensimmäinen yksikkörivi (E2:G2) syötetään manuaalisesti, ja loput käytämme seuraavaa kaavaa:
Logiikka tässä on yksinkertainen: jos ylemmän edellisen solun indeksi on jo saavuttanut listan lopun, eli on yhtä suuri kuin funktion laskema elementtien lukumäärä listassa COUNT (COUNTA), aloitamme sitten numeroinnin uudelleen. Muussa tapauksessa lisäämme indeksiä yhdellä. Kiinnitä erityistä huomiota alueiden fiksuun kiinnittämiseen dollarimerkeillä ($), jotta voit kopioida kaavan alas ja oikealle.
Nyt kun meillä on kustakin luettelosta tarvitsemiemme sanojen järjestysnumerot, voimme poimia itse sanat funktiolla INDEX (INDEKSI) kolmeen erilliseen sarakkeeseen:
Jos et ole aiemmin törmännyt tähän toimintoon työssäsi, suosittelen ehdottomasti tutkimaan sitä ainakin vinottain - se auttaa monissa tilanteissa ja on hyödyllinen vähintäänkin (ja jopa enemmän!) VPR (HAKU).
No, sen jälkeen jää vain liimata tuloksena olevat fragmentit rivi riviltä käyttämällä ketjutussymbolia (&):
… tai (jos sinulla on uusin Excel-versio) kätevällä toiminnolla YHDISTÄÄ (TEKSTIJOIN), joka voi liimata määritettyjen solujen koko sisällön tietyn erotinmerkin (välilyönnin) kautta:
Tapa 2. Power Queryn kautta
Power Query on tehokas Microsoft Excelin apuohjelma, joka suorittaa kaksi päätehtävää: 1. ladata tietoja Exceliin lähes mistä tahansa ulkoisesta lähteestä ja 2. kaikenlaisia ladattujen taulukoiden muunnoksia. Power Query on jo sisäänrakennettu Exceliin 2016-2019, ja Excel 2010-2013:ssa se on asennettu erillisenä apuohjelmana (voit ladata sen Microsoftin viralliselta verkkosivustolta ilmaiseksi). Jos et ole vielä aloittanut Power Queryn käyttöä työssäsi, niin on aika miettiä sitä, sillä yllä kuvatut muunnokset tehdään siellä helposti ja luonnollisesti, muutamalla liikkeellä.
Ensin ladataan lähdeluettelot erillisinä kyselyinä Power Queryyn. Tee tämä jokaiselle taulukolle seuraavasti:
- Tehdään pöydät "älykkäiksi" painikkeella Muotoile taulukkona kieleke Koti (Etusivu — Muotoile taulukkona) tai pikanäppäimellä Ctrl+T. Jokaiselle taulukolle annetaan automaattisesti nimi Taulukko 1,2,3, XNUMX, XNUMX…, jota voidaan kuitenkin haluttaessa muuttaa välilehdellä Rakentaja (Design).
- Kun olet asettanut aktiivisen solun taulukkoon, paina -painiketta Pöydältä (taulukosta) kieleke Päiväys (Päivämäärä) tai välilehdellä Tehokysely (jos se on asennettu erillisenä apuohjelmana Excel 2010-2013:lle).
- Valitse avautuvassa kyselyn muokkausikkunassa komento Etusivu — Sulje ja lataa — Sulje ja lataa… (Etusivu — Sulje&Lataa — Sulje&Lataa kohteeseen...) ja sitten vaihtoehto Luo vain yhteys (Luo vain yhteys). Tämä jättää ladatun taulukon muistiin ja mahdollistaa sen käytön tulevaisuudessa.
Jos teet kaiken oikein, oikean paneelin lähdön tulisi olla kolme pyyntöä tilassa Vain yhteys taulukoiden nimillä:
Napsauta nyt hiiren kakkospainikkeella ensimmäistä kyselyä ja valitse komento Linkki (Viite)tehdäksesi siitä päivitettävän kopion ja lisää sitten tietoihin komennon avulla ylimääräinen sarake Sarakkeen lisääminen ž – Mukautettu sarake (Lisää sarake -ž mukautettu sarake). Syötä kaavan syöttöikkunaan uuden sarakkeen nimi (esimerkiksi Fragmentti2) ja äärimmäisen yksinkertainen lauseke kaavaksi:
= Taulukko2
… eli toisin sanoen toisen kyselyn nimi:
Napsauttamisen jälkeen OK näemme uuden sarakkeen, jonka jokaisessa solussa on sisäkkäinen taulukko, jossa on lauseita toisesta taulukosta (näet näiden taulukoiden sisällön, jos napsautat solun taustaa sanan vieressä Pöytä):
Jäljelle jää laajentaa näiden sisäkkäisten taulukoiden sisältö käyttämällä kaksoisnuolta painiketta tuloksena olevan sarakkeen otsikossa ja poistamalla valinta Käytä etuliitteenä alkuperäistä sarakkeen nimeä (Käytä alkuperäistä sarakkeen nimeä etuliitteenä):
… ja saamme kaikki mahdolliset elementtien yhdistelmät kahdesta ensimmäisestä joukosta:
Lisäksi kaikki on samanlaista. Lisää toinen laskettu sarake kaavalla:
= Taulukko3
… ja laajenna sitten sisäkkäisiä taulukoita uudelleen – ja nyt meillä on jo kaikki mahdolliset vaihtoehdot kolmen joukon sanojen muuntamiseen:
Jäljelle jää valita kaikki kolme saraketta vasemmalta oikealle pitämällä Ctrl, ja yhdistä niiden sisältö välilyönneillä erotettuna komennolla Yhdistä sarakkeet (Yhdistä sarakkeet) välilehdeltä Muutos (Muuttaa):
Tuloksena saadut tulokset voidaan purkaa takaisin arkille jo tutulla komennolla Etusivu — Sulje ja lataa — Sulje ja lataa… (Etusivu — Sulje&Lataa — Sulje&Lataa kohteeseen...):
Jos jatkossa jokin muuttuu fragmenteilla varustetuissa lähdetaulukoissamme, riittää, että päivität luodun kyselyn napsauttamalla tuloksena olevaa taulukkoa hiiren kakkospainikkeella ja valitsemalla komennon Päivitä & Tallenna (Virkistää) tai painamalla pikanäppäintä Ctrl+muut+F5.
- Mitä ovat Power Query, Power Pivot, Power Map ja Power BI ja miksi ne tarvitsevat Excel-käyttäjän
- Gantt-kaavion luominen Power Queryssä
- 5 tapaa käyttää INDEX-toimintoa