Sisällys
Tällä hetkellä Microsoft Excelissä on lähes viisisataa laskentataulukkotoimintoa käytettävissä Function Wizard -ikkunan – painikkeen kautta fx kaavapalkissa. Tämä on erittäin kunnollinen sarja, mutta siitä huolimatta melkein jokainen käyttäjä kohtaa ennemmin tai myöhemmin tilanteen, jossa tämä luettelo ei sisällä hänen tarvitsemaansa toimintoa - yksinkertaisesti siksi, että se ei ole Excelissä.
Tähän asti ainoa tapa ratkaista tämä ongelma on ollut makrojen eli oman käyttäjän määrittämän funktion (UDF = User Defined Function) kirjoittaminen Visual Basicissa, mikä vaatii asianmukaisia ohjelmointitaitoja ja ei ajoittain ole ollenkaan helppoa. Uusimpien Office 365 -päivitysten myötä tilanne on kuitenkin muuttunut parempaan – Exceliin on lisätty erityinen "kääre"-toiminto LAMBDA. Sen avulla omien toimintojen luontitehtävä ratkeaa nyt helposti ja kauniisti.
Katsotaanpa sen käytön periaatetta seuraavassa esimerkissä.
Kuten todennäköisesti tiedät, Excelissä on useita päivämäärän jäsennystoimintoja, joiden avulla voit määrittää päivän, kuukauden, viikon ja vuoden numeron tietylle päivämäärälle. Mutta jostain syystä ei ole toimintoa, joka määrää vuosineljänneksen numeron, jota myös usein tarvitaan, eikö niin? Korjataan tämä puute ja luodaan LAMBDA oma uusi toiminto tämän ongelman ratkaisemiseksi.
Vaihe 1. Kirjoita kaava
Aloitetaan siitä, että kirjoitamme manuaalisesti tavalliseen tapaan arkin soluun kaavan, joka laskee tarvitsemamme. Neljännesluvun tapauksessa tämä voidaan tehdä esimerkiksi näin:
Vaihe 2. Kääriminen LAMBDA:han ja testaus
Nyt on aika ottaa käyttöön uusi LAMBDA-funktio ja kääriä kaavamme siihen. Funktioiden syntaksi on seuraava:
=LAMBDA(Muuttuja1; Muuttuja2; ... MuuttujaN ; Ilmaisu)
jossa yhden tai useamman muuttujan nimet luetellaan ensin ja viimeinen argumentti on aina niitä käyttävä kaava tai laskettu lauseke. Muuttujien nimet eivät saa näyttää soluosoitteilta, eivätkä ne saa sisältää pisteitä.
Meidän tapauksessamme on vain yksi muuttuja – päivämäärä, jolle laskemme vuosineljänneksen numeron. Kutsutaan sen muuttuja, vaikkapa d. Kääri sitten kaavamme funktioon LAMBDA ja korvaamalla alkuperäisen solun A2 osoitteen kuvitteellisella muuttujan nimellä, saamme:
Huomaa, että tällaisen muunnoksen jälkeen kaavamme (itse asiassa oikein!) alkoi tuottaa virhettä, koska nyt alkuperäistä päivämäärää solusta A2 ei siirretä siihen. Testauksen ja varmuuden vuoksi voit välittää sille argumentteja lisäämällä ne funktion jälkeen LAMBDA suluissa:
Vaihe 3. Luo nimi
Nyt helppo ja hauska osa. Avaamme Name Manager kieleke kaava (Kaavat — Name Manager) ja luo uusi nimi -painikkeella luoda (Luoda). Keksi ja kirjoita nimi tulevalle toiminnollemme (esim. Nomkvartala) ja kentällä Linkki (Viite) kopioi varovasti kaavapalkista ja liitä funktiomme LAMBDA, vain ilman viimeistä argumenttia (A2):
Kaikki. Napsautuksen jälkeen OK luotua funktiota voidaan käyttää missä tahansa tämän työkirjan arkin solussa:
Käytä muissa kirjoissa
LAMBDA ja dynaamiset taulukot
Mukautetut funktiot, jotka on luotu funktiolla LAMBDA tukee menestyksekkäästi työtä uusien dynaamisten taulukoiden ja niiden toimintojen kanssa (SUODATIN, UNIK, GRADE) lisätty Microsoft Exceliin vuonna 2020.
Oletetaan, että haluamme luoda uuden käyttäjän määrittämän funktion, joka vertaa kahta listaa ja palauttaa niiden välisen eron – ne ensimmäisen luettelon elementit, jotka eivät ole toisessa. Elämäntyö, eikö? Aikaisemmin he käyttivät tähän joko funktioita a la VPR (HAKU)tai Pivot-taulukot tai Power Query -kyselyt. Nyt voit tehdä yhden kaavan:
Englanninkielisessä versiossa se tulee olemaan:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Tässä toiminto LASKE.JOS laskee ensimmäisen luettelon kunkin elementin esiintymisten määrän toisessa ja sitten funktion SUODATIN valitsee niistä vain ne, joilla ei ole ollut näitä tapahtumia. Käärimällä tämä rakenne sisään LAMBDA ja luodaan sen perusteella nimetty alue esimerkiksi nimellä HAKU JAKELU – saamme kätevän funktion, joka palauttaa kahden listan vertailun tuloksen dynaamisen taulukon muodossa:
Jos lähdetiedot eivät ole tavallisia, vaan "älykkäitä" taulukoita, myös toimintomme selviää ilman ongelmia:
Toinen esimerkki on tekstin dynaaminen jakaminen muuntamalla se XML-muotoon ja jäsentämällä se sitten solu kerrallaan käyttämällä äskettäin jäsentämäämme FILTER.XML-funktiota. Jotta tätä monimutkaista kaavaa ei toistettaisi manuaalisesti joka kerta, on helpompi kääriä se LAMBDA:han ja luoda sen perusteella dynaaminen alue, eli uusi kompakti ja kätevä toiminto, joka nimeää sen esimerkiksi RAZDTEXT:
Tämän funktion ensimmäinen argumentti on solu, jossa on lähdeteksti, ja toinen - erotinmerkki, ja se palauttaa tuloksen vaakasuuntaisen dynaamisen taulukon muodossa. Toimintokoodi tulee olemaan seuraava:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Esimerkkiluettelo on loputon – missä tahansa tilanteessa, jossa joudut usein syöttämään saman pitkän ja hankalan kaavan, LAMBDA-toiminto helpottaa elämää huomattavasti.
Merkkien rekursiivinen luettelointi
Kaikki aiemmat esimerkit ovat osoittaneet vain yhden, ilmeisimmän puolen LAMBDA-funktiosta – sen käytön "kääreenä" pitkien kaavojen käärimiseksi siihen ja niiden syöttämisen yksinkertaistamiseksi. Itse asiassa LAMBDA:lla on toinen, paljon syvempi puoli, joka muuttaa sen melkein täysimittaiseksi ohjelmointikieleksi.
Tosiasia on, että LAMBDA-toimintojen perustavanlaatuinen ominaisuus on kyky toteuttaa ne rekursio – laskutoimitusten logiikka, kun laskentaprosessissa funktio kutsuu itseään. Tottumuksesta se saattaa kuulostaa kammottavalta, mutta ohjelmoinnissa rekursio on yleinen asia. Jopa Visual Basicin makroissa voit ottaa sen käyttöön, ja nyt, kuten näet, se on tullut Exceliin. Yritetään ymmärtää tämä tekniikka käytännön esimerkillä.
Oletetaan, että haluamme luoda käyttäjän määrittämän funktion, joka poistaisi kaikki annetut merkit lähdetekstistä. Tällaisen toiminnon hyödyllisyyttä ei mielestäni tarvitse todistaa - sen avulla olisi erittäin kätevää tyhjentää roskainen syöttödata, eikö niin?
Verrattuna edellisiin, ei-rekursiivisiin esimerkkeihin, meitä odottaa kuitenkin kaksi vaikeutta.
- Meidän on keksittävä nimi funktiollemme ennen kuin alamme kirjoittaa sen koodia, koska siinä tätä nimeä käytetään jo itse funktion kutsumiseen.
- Tällaisen rekursiivisen funktion syöttäminen soluun ja virheenkorjaus määrittämällä argumentit suluissa LAMBDA:n jälkeen (kuten teimme aiemmin) ei toimi. Sinun on luotava toiminto välittömästi "tyhjästä". Name Manager (Nimipäällikkö).
Kutsukaamme funktioamme esimerkiksi CLEANiksi ja haluamme, että sillä on kaksi argumenttia – puhdistettava teksti ja poissuljettujen merkkien luettelo tekstimerkkijonona:
Luodaan, kuten teimme aiemmin, välilehdelle kaava в Nimipäällikkö nimeltä alue, anna sille nimi CLEAR ja kirjoita kenttään alue seuraava rakenne:
=LAMBDA(t;d;JOS(d="";t;TYHJENNÄ(KORVAA(t;VASEN(d);"");KESKUS(d;2;255))))
Tässä muuttuja t on alkuperäinen poistettava teksti ja d on luettelo poistettavista merkeistä.
Kaikki toimii näin:
Iteraatio 1
Fragmentti SUBSTITUTE(t;LEFT(d);""), kuten arvata saattaa, korvaa lähdetekstissä t poistettavan joukon d ensimmäisen merkin vasemmasta merkistä tyhjällä tekstimerkkijonolla, eli poistaa " A". Välituloksena saamme:
Vsh zkz n 125 ruplaa.
Iteraatio 2
Sitten funktio kutsuu itseään ja syötteenä (ensimmäinen argumentti) saa sen, mikä jää jäljelle edellisen vaiheen puhdistuksen jälkeen, ja toinen argumentti on poissuljettujen merkkien merkkijono, joka alkaa ei ensimmäisestä vaan toisesta merkistä, eli “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ”, ilman alkukirjainta A – tämän tekee MID-toiminto. Kuten ennenkin, funktio ottaa ensimmäisen merkin jäljellä olevista (B) vasemmalta ja korvaa sen sille annetussa tekstissä (Zkz n 125 ruplaa) tyhjällä merkkijonolla – saamme välituloksena:
125 ru.
Iteraatio 3
Funktio kutsuu itseään uudelleen ja vastaanottaa ensimmäisenä argumenttina sen, mikä on jäljellä edellisessä iteraatiossa poistettavasta tekstistä (Bsh zkz n 125 ru.), ja toisena argumenttina poissuljettujen merkkien joukon, joka on katkaistu vielä yhdellä merkillä. vasen, eli "VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.", ilman alkukirjainta "B". Sitten se ottaa jälleen ensimmäisen merkin vasemmalta (B) tästä joukosta ja poistaa sen tekstistä - saamme:
sh zkz n 125 ru.
Ja niin edelleen – toivottavasti ymmärrät idean. Jokaisen iteroinnin yhteydessä poistettavien merkkien luettelo katkaistaan vasemmalla, ja etsimme ja korvaamme seuraavan merkin joukosta tyhjällä.
Kun kaikki merkit loppuvat, meidän on poistuttava silmukasta – tämä rooli on vain funktion suorittama IF (JOS), johon mallimme on kääritty. Jos poistettavia merkkejä ei ole jäljellä (d=””), funktion ei pitäisi enää kutsua itseään, vaan sen tulee yksinkertaisesti palauttaa poistettava teksti (muuttuja t) lopullisessa muodossaan.
Solujen rekursiivinen iteraatio
Vastaavasti voit toteuttaa tietyn alueen solujen rekursiivisen luettelon. Oletetaan, että haluamme luoda lambda-funktion nimeltä VAIHTOLUETTELO lähdetekstin fragmenttien tukkukorvaamiseen tietyn viiteluettelon mukaan. Tuloksen pitäisi näyttää tältä:
Nuo. tilaisuudessamme VAIHTOLUETTELO argumentteja on kolme:
- solu, jossa on käsiteltävä teksti (lähdeosoite)
- sarakkeen ensimmäinen solu, jossa on haettavissa olevia arvoja
- sarakkeen ensimmäinen solu, jossa on korvausarvot hausta
Toiminnon tulee siirtyä ylhäältä alas hakemistossa ja korvata peräkkäin kaikki vaihtoehdot vasemmasta sarakkeesta Löytää vastaaviin vaihtoehtoihin oikeasta sarakkeesta korvike. Voit toteuttaa tämän seuraavalla rekursiivisella lambda-funktiolla:
Vaihto alaspäin jokaisessa iteraatiossa toteutetaan tavallisella Excel-funktiolla HÄVITTÄMINEN (OFFSET), jolla on tässä tapauksessa kolme argumenttia – alkuperäinen alue, rivinsiirto (1) ja sarakkeen siirto (0).
No, heti kun saavumme hakemiston loppuun (n = ""), meidän on lopetettava rekursio – lopetamme itsemme kutsumisen ja näytämme mitä on kertynyt kaikkien korvausten jälkeen lähdetekstimuuttujassa t.
Siinä kaikki. Ei hankalia makroja tai Power Query -kyselyitä – koko tehtävä ratkaistaan yhdellä toiminnolla.
- Kuinka käyttää Excelin uusia dynaamisia taulukkofunktioita: FILTER, SORT, UNIC
- Tekstin korvaaminen ja puhdistaminen SUBSTITUTE-toiminnolla
- Makrojen ja käyttäjän määrittämien funktioiden (UDF) luominen VBA:ssa