Sisällys
Yksi YouTube-kanavani katsotuimmista videoista on video Flash Fillistä Microsoft Excelissä. Tämän työkalun ydin on, että jos sinun täytyy jollakin tavalla muuttaa lähdetietojasi, sinun on vain aloitettava tulosten kirjoittaminen viereiseen sarakkeeseen. Useiden manuaalisesti kirjoitettujen solujen jälkeen (yleensä 2-3 riittää) Excel "ymmärtää" tarvitsemiesi muunnosten logiikan ja jatkaa automaattisesti kirjoittamaasi suorittaen kaiken yksitoikkoisen työn puolestasi:
Tehokkuuden kvintesenssi. Maaginen "tee se oikein" -painike, jota me kaikki rakastamme niin paljon, eikö niin?
Itse asiassa tällaisen työkalun analogi on olemassa Power Queryssä - siellä sitä kutsutaan Sarake esimerkeistä (Sarake esimerkeistä). Itse asiassa tämä on pieni tekoäly, joka on sisäänrakennettu Power Queryyn, joka voi nopeasti oppia tiedoistasi ja sitten muuttaa niitä. Tarkastellaan tarkemmin sen ominaisuuksia useissa käytännön skenaarioissa ymmärtääksemme, missä siitä voi olla meille hyötyä todellisissa tehtävissä.
Esimerkki 1. Tekstin liimaus/leikkaus
Oletetaan, että meillä on Excelissä tällainen "älykäs" taulukko työntekijöiden tiedoilla:
Lataa se Power Queryyn tavallisella tavalla – painikkeella Taulukosta/alueesta kieleke Päiväys (Tiedot – taulukosta/alueesta).
Oletetaan, että meidän on lisättävä sarake, jossa on sukunimet ja nimikirjaimet jokaiselle työntekijälle (Ivanov SV ensimmäiselle työntekijälle jne.). Voit ratkaista tämän ongelman käyttämällä jompaakumpaa kahdesta menetelmästä:
- napsauta hiiren kakkospainikkeella sarakkeen otsikkoa, jossa on lähdetiedot, ja valitse komento Lisää sarake esimerkeistä (Lisää sarake esimerkeistä);
- valitse yksi tai useampi sarake, jossa on tietoja ja -välilehdeltä Sarakkeen lisääminen valitse joukkue Sarake esimerkeistä. Tässä avattavassa luettelossa voit määrittää, onko kaikki vai vain valitut sarakkeet analysoitava.
Sitten kaikki on yksinkertaista – oikealla näkyvään sarakkeeseen alamme syöttää esimerkkejä halutuista tuloksista, ja Power Queryyn sisäänrakennettu tekoäly yrittää ymmärtää muunnoslogiikkaamme ja jatkaa eteenpäin yksinään:
Voit muuten syöttää oikeat valinnat mihin tahansa tämän sarakkeen soluun, eli ei välttämättä ylhäältä alas ja riviin. Voit myös helposti lisätä tai poistaa sarakkeita analyysistä myöhemmin käyttämällä otsikkorivin valintaruutuja.
Kiinnitä huomiota ikkunan yläosassa olevaan kaavaan – tämän älykäs Power Query luo saadaksemme tarvitsemamme tulokset. Tämä on muuten perustavanlaatuinen ero tämän työkalun ja Välitön täyttö Excelissä. Pikatäyttö toimii kuin "musta laatikko" – ne eivät näytä meille muutosten logiikkaa, vaan antavat vain valmiita tuloksia ja pidämme niitä itsestäänselvyytenä. Täällä kaikki on läpinäkyvää ja voit aina täysin selvästi ymmärtää, mitä tiedoilla oikein tapahtuu.
Jos näet, että Power Query "sai idean", voit painaa painiketta turvallisesti OK tai pikanäppäimellä Ctrl+enter – Mukautettu sarake Power Queryn keksimällä kaavalla luodaan. Muuten, sitä voidaan myöhemmin helposti muokata tavalliseksi manuaalisesti luotavaksi sarakkeeksi (komennolla Sarakkeen lisääminen – Mukautettu sarake) napsauttamalla vaiheen nimen oikealla puolella olevaa rataskuvaketta:
Esimerkki 2: Tapaus kuten lauseissa
Jos napsautat hiiren kakkospainikkeella sarakkeen otsikkoa, jossa on teksti, ja valitset komennon Muutos (Muuttaa), näet kolme komentoa, jotka vastaavat rekisterin muuttamisesta:
Kätevä ja siisti, mutta esimerkiksi tästä listasta minulta henkilökohtaisesti on aina puuttunut yksi vaihtoehto – tapaus kuten lauseissa, jolloin isot kirjaimet (isot kirjaimet) eivät ole jokaisen sanan ensimmäinen kirjain, vaan vain solun ensimmäinen kirjain, ja loput tekstistä, kun Tämä näkyy pienillä (pienillä) kirjaimilla.
Tämä puuttuva ominaisuus on helppo toteuttaa tekoälyllä Sarakkeet esimerkeistä – Anna vain muutama vaihtoehto, jotta Power Query jatkaa samassa hengessä:
Tässä kaavana Power Query käyttää joukkoa toimintoja Teksti.Ylä и Teksti.Alempi, muuntaa tekstin isoiksi ja pieniksi kirjaimiksi ja toiminnot Teksti.Aloita и Teksti.Mid – Excelin LEFT- ja PSTR-funktioiden analogit, jotka pystyvät poimimaan tekstistä alimerkkijonon vasemmalta ja keskeltä.
Esimerkki 3. Sanojen permutaatio
Joskus vastaanotettua dataa käsiteltäessä on välttämätöntä järjestää solujen sanat uudelleen tiettyyn järjestykseen. Tietysti voit jakaa sarakkeen erillisiin sanasarakkeisiin erottimella ja liimata sitten takaisin määritetyssä järjestyksessä (älä unohda lisätä välilyöntejä), mutta työkalun avulla Sarake esimerkeistä kaikki on paljon helpompaa:
Esimerkki 4: Vain numerot
Toinen erittäin tärkeä tehtävä on poistaa solun sisällöstä vain numeroita (numeroita). Kuten aiemmin, siirry välilehdelle ladattuasi tiedot Power Queryyn Sarakkeen lisääminen – Sarake esimerkeistä ja täytä pari solua manuaalisesti, jotta ohjelma ymmärtää, mitä tarkalleen haluamme saada:
Bingo!
Jälleen kannattaa katsoa ikkunan yläosaa varmistaaksesi, että Query loi kaavan oikein – tässä tapauksessa se sisältää funktion Teksti. Valitse, joka, kuten arvata saattaa, poimii annetut merkit lähdetekstistä luettelon mukaan. Myöhemmin tätä luetteloa voidaan tietysti helposti muokata kaavapalkissa tarvittaessa.
Esimerkki 5: Vain teksti
Kuten edellisessä esimerkissä, voit vetää ulos ja päinvastoin – vain tekstiä, poistaa kaikki numerot, välimerkit jne.
Tässä tapauksessa käytetään funktiota, jonka merkitys on jo vastakkainen – Text.Remove, joka poistaa merkit alkuperäisestä merkkijonosta tietyn listan mukaan.
Esimerkki 6: Tietojen poimiminen aakkosnumeerisesta puurosta
Power Query voi auttaa myös vaikeissa tapauksissa, kun solun aakkosnumeerisesta puurosta täytyy poimia hyödyllistä tietoa, esimerkiksi saada tilinumero tiliotteen maksutarkoituksen kuvauksesta:
Huomaa, että Power Queryn luoma muunnoskaava voi olla melko monimutkainen:
Lukemisen ja ymmärtämisen helpottamiseksi se voidaan muuntaa paljon järkevämpään muotoon käyttämällä ilmaista verkkopalvelua. Power Query Formatter:
Erittäin kätevä asia – kunnioitus tekijöille!
Esimerkki 7: Päivämäärien muuntaminen
Työkalu Sarake esimerkeistä voidaan soveltaa myös päivämäärä- tai päivämäärä-aikasarakkeisiin. Kun syötät päivämäärän ensimmäiset numerot, Power Query näyttää luettelon kaikista mahdollisista muunnosvaihtoehdoista:
Joten voit helposti muuntaa alkuperäisen päivämäärän mihin tahansa eksoottiseen muotoon, kuten "vuosi-kuukausi-päivä":
Esimerkki 8: Luokittelu
Jos käytämme työkalua Sarake esimerkeistä sarakkeeseen, jossa on numeerisia tietoja, se toimii eri tavalla. Oletetaan, että työntekijän testitulokset on ladattu Power Queryyn (ehdolliset pisteet välillä 0-100) ja käytämme seuraavaa ehdollista asteikkoa:
- Masters – yli 90 pistettä saaneet
- Asiantuntijat – pisteytetään 70:stä 90:een
- Käyttäjiä - 30-70
- Aloittelijat – alle 30 pisteet saaneet
Jos lisäämme luetteloon esimerkeistä sarakkeen ja aloitamme näiden asteiden järjestämisen manuaalisesti, niin pian Power Query poimii ideamme ja lisää sarakkeen kaavalla, jossa operaattorit ovat sisäkkäin if logiikka toteutetaan, hyvin samankaltainen kuin mitä tarvitsemme:
Jälleen, et voi painaa tilannetta loppuun, vaan napsauta OK ja korjaa sitten kynnysarvot jo kaavassa – se on nopeampi näin:
Päätelmät
Varmasti työkalu Sarake esimerkeistä ei ole "taikapilleri" ja tiedoissa tulee ennemmin tai myöhemmin epätyypillisiä tilanteita tai erityisen huomiotta jätettyjä "kolhoosin" tapauksia, jolloin Power Query epäonnistuu eikä pysty selvittämään mitä haluamme. oikein meille. Apuvälineenä se on kuitenkin erittäin hyvä. Lisäksi tutkimalla hänen luomiaan kaavoja voit laajentaa tietämyksesi M-kielen toiminnoista, joista on aina hyötyä tulevaisuudessa.
- Tekstin jäsentäminen säännöllisillä lausekkeilla (RegExp) Power Queryssa
- Sumea tekstihaku Power Queryssä
- Flash Täytä Microsoft Excelissä