tartalom
Jelenleg a Microsoft Excelben csaknem ötszáz munkalapfunkció érhető el a Funkcióvarázsló ablakán keresztül – gomb fx a képletsávban. Ez egy nagyon tisztességes készlet, de ennek ellenére szinte minden felhasználó előbb-utóbb találkozik olyan helyzettel, amikor ez a lista nem tartalmazza a számára szükséges funkciót - egyszerűen azért, mert az nincs az Excelben.
Eddig a probléma megoldásának egyetlen módja a makrók, azaz a saját felhasználói függvény (UDF = User Defined Function) írása volt a Visual Basicben, ami megfelelő programozási készségeket igényel, és időnként egyáltalán nem egyszerű. Az Office 365 legújabb frissítéseivel azonban a helyzet jobbra változott – egy speciális „burkoló” funkcióval bővült az Excel LAMBDA. Segítségével most egyszerűen és szépen megoldódik a saját funkciók létrehozásának feladata.
Nézzük meg a használatának elvét a következő példában.
Amint azt valószínűleg Ön is tudja, az Excel számos dátumelemző funkcióval rendelkezik, amelyek lehetővé teszik egy adott dátumhoz tartozó nap, hónap, hét és év számának meghatározását. De valamiért nincs az a negyedszámot meghatározó függvény, amire szintén sokszor szükség van, nem? Javítsuk ki ezt a hiányosságot, és alkossunk vele LAMBDA saját új funkcióval oldja meg ezt a problémát.
1. lépés Írja fel a képletet
Kezdjük azzal, hogy manuálisan a szokásos módon egy lapcellába írunk egy képletet, amely kiszámítja, hogy mire van szükségünk. A negyedszám esetében ezt például így lehet megtenni:
2. lépés: becsomagolás LAMBDA-ba és tesztelés
Itt az ideje, hogy alkalmazzuk az új LAMBDA függvényt, és becsomagoljuk a képletünket. A függvény szintaxisa a következő:
=LAMBDA(Változó1; Változó2; ... VáltozóN ; Kifejezés)
ahol egy vagy több változó neve szerepel először, és az utolsó argumentum mindig egy képlet vagy egy számított kifejezés, amely ezeket használja. A változónevek nem nézhetnek ki cellacímként, és nem tartalmazhatnak pontokat.
A mi esetünkben csak egy változó lesz – az a dátum, amelyre a negyedévszámot számítjuk. Nevezzük meg a változót, mondjuk d. Ezután a képletünket egy függvénybe burkoljuk LAMBDA és az eredeti A2 cella címét egy fiktív változónévvel helyettesítve a következőt kapjuk:
Kérjük, vegye figyelembe, hogy egy ilyen átalakítás után a képletünk (sőt, helyes!) hibát kezdett produkálni, mert most az A2 cellából az eredeti dátum nem kerül át rá. A tesztelés és a megbízhatóság érdekében argumentumokat adhat át neki úgy, hogy hozzáadja azokat a függvény után LAMBDA zárójelben:
3. lépés: Hozzon létre egy nevet
Most következzen a könnyű és szórakoztató rész. Nyitunk Névkezelő lap képlet (Képletek — Névkezelő) és hozzon létre egy új nevet a gombbal Teremt (Teremt). Találjon ki és adjon meg egy nevet a jövőbeli funkciónknak (pl. Nomkvartala), és a terepen Link (Referencia) óvatosan másolja ki a képletsorból, és illessze be a függvényünket LAMBDA, csak az utolsó argumentum nélkül (A2):
Minden. Kattintás után OK a létrehozott függvény a munkafüzet bármely lapjának bármelyik cellájában használható:
Használja más könyvekben
LAMBDA és dinamikus tömbök
Függvénnyel létrehozott egyéni függvények LAMBDA sikeresen támogatja az új dinamikus tömbökkel és azok funkcióival végzett munkát (FILTER, EGYEDI, GRADE) 2020-ban hozzáadva a Microsoft Excelhez.
Tegyük fel, hogy szeretnénk létrehozni egy új, felhasználó által definiált függvényt, amely összehasonlítana két listát, és visszaadná a köztük lévő különbséget – az első listából azokat az elemeket, amelyek nem szerepelnek a másodikban. Az élet munkája, nem? Korábban ehhez a la függvényeket használták VPR (VLOOKUP), vagy PivotTables vagy Power Query lekérdezések. Most egy képlettel megteheti:
Az angol változatban ez lesz:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Itt a funkció COUNTIF megszámolja az első lista egyes elemeinek előfordulásának számát a másodikban, majd a függvényt FILTER csak azokat választja ki közülük, akiknél ez nem fordult elő. Ezt a szerkezetet becsomagolva LAMBDA és egy elnevezett tartomány létrehozása az alapján egy névvel, például KERESÉS FORGALMAZÁSA – kapunk egy kényelmes függvényt, amely két lista összehasonlításának eredményét adja vissza dinamikus tömb formájában:
Ha a forrásadatok nem közönséges, hanem „okos” táblák, akkor funkciónk is problémamentesen megbirkózik:
Egy másik példa a szöveg dinamikus felosztása XML formátumba konvertálásával, majd cellánkénti elemzésével a nemrégiben elemzett FILTER.XML függvény segítségével. Annak érdekében, hogy ezt az összetett formulát ne minden alkalommal kézzel reprodukáljuk, könnyebb lesz LAMBDA-ba csomagolni, és dinamikus tartományt létrehozni az alapján, azaz egy új kompakt és kényelmes funkciót, például RAZDTEXT elnevezéssel:
Ennek a függvénynek az első argumentuma a forrásszöveget tartalmazó cella, a második pedig az elválasztó karakter lesz, és az eredményt vízszintes dinamikus tömb formájában adja vissza. A funkció kódja a következő lesz:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
A példák sora végtelen – minden olyan helyzetben, amikor gyakran ugyanazt a hosszú és nehézkes képletet kell megadnia, a LAMBDA funkció észrevehetően megkönnyíti az életet.
Karakterek rekurzív felsorolása
Az összes korábbi példa a LAMBDA függvénynek csak az egyik, legnyilvánvalóbb oldalát mutatta meg – „burkolóként” való használatát a hosszú képletek becsomagolására és bevitelük egyszerűsítésére. Valójában a LAMBDA-nak van egy másik, sokkal mélyebb oldala is, amely szinte teljes értékű programozási nyelvvé varázsolja.
A helyzet az, hogy a LAMBDA-funkciók alapvetően fontos jellemzője, hogy be tudja őket implementálni rekurzió – számítási logika, amikor a számítás során a függvény meghívja magát. A megszokásból hátborzongatóan hangzik, de a programozásban a rekurzió gyakori dolog. Még a Visual Basicben lévő makrókban is megvalósítható, és most, mint látható, megérkezett az Excelhez. Próbáljuk megérteni ezt a technikát egy gyakorlati példán keresztül.
Tegyük fel, hogy szeretnénk létrehozni egy felhasználó által definiált függvényt, amely eltávolítja az összes megadott karaktert a forrásszövegből. Egy ilyen funkció hasznosságát szerintem nem kell bizonygatni – nagyon kényelmes lenne a szennyezett bemeneti adatokat törölni a segítségével, nem?
A korábbi, nem rekurzív példákhoz képest azonban két nehézség vár ránk.
- Mielőtt elkezdené írni a kódját, ki kell találnunk egy nevet a függvényünknek, mert ebben a név már magát a függvényt hívja meg.
- Egy ilyen rekurzív függvény beírása egy cellába és hibakeresése a LAMBDA utáni zárójelben lévő argumentumok megadásával (ahogy korábban tettük) nem fog működni. Azonnal „a semmiből” létre kell hoznia egy funkciót Névkezelő (Névkezelő).
Nevezzük el a függvényünket, mondjuk, CLEAN, és szeretnénk, ha két argumentuma lenne – a tisztítandó szöveg és a kizárt karakterek listája szöveges karakterláncként:
Hozzuk létre, mint korábban, a lapon képlet в Névkezelő nevű tartományt, nevezze el CLEAR-nek, és írja be a mezőbe Választék következő konstrukció:
=LAMBDA(t;d;HA(d=””;t;TISZTA(HELYETTES(t;BAL(d);””);KÖZÉP(d;2;255))))
Itt a t változó a törölni kívánt eredeti szöveg, a d pedig a törölni kívánt karakterek listája.
Mindez így működik:
Iteráció 1
A SUBSTITUTE(t;LEFT(d);””) töredék, ahogy sejthető, a t forrásszövegben törölni kívánt d halmaz bal oldali karakterének első karakterét egy üres szöveges karakterláncra cseréli, azaz eltávolítja a „ A”. Köztes eredményként a következőket kapjuk:
Vsh zkz n 125 rubel.
Iteráció 2
Ekkor a függvény meghívja magát, és bemenetként (az első argumentum) azt kapja, ami az előző lépésben a tisztítás után megmaradt, a második argumentum pedig a kizárt karakterek sorozata, amely nem az első, hanem a második karaktertől kezdődik, azaz „BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ”, a kezdeti „A” nélkül – ezt a MID funkció végzi. Mint korábban, a függvény a megmaradt karakterek közül (B) veszi az első karaktert a balról, és a neki adott szövegben (Zkz n 125 rubel) egy üres karakterláncra cseréli – köztes eredményként kapjuk:
125 ru.
Iteráció 3
A függvény újra meghívja magát, és első argumentumként megkapja azt, ami az előző iterációnál törölni kívánt szövegből megmaradt (Bsh zkz n 125 ru.), második argumentumként pedig a kizárt karakterek halmazát, amelyet még egy karakterrel lecsonkított a bal, azaz „VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.”, a „B” kezdőbetű nélkül. Ezután ismét elveszi az első karaktert a balról (B) ebből a halmazból, és eltávolítja a szövegből – ezt kapjuk:
sh zkz n 125 ru.
És így tovább – remélem érted az ötletet. Minden iterációnál az eltávolítandó karakterek listája a bal oldalon csonka lesz, és a készlet következő karakterét megkeressük és üres karakterrel helyettesítjük.
Amikor az összes karakter elfogy, ki kell lépnünk a ciklusból – ezt a szerepet csak a funkció tölti be IF (HA), amelybe a mi dizájnunk van becsomagolva. Ha már nincs törölnivaló karakter (d=””), akkor a függvény többé nem hívja meg magát, hanem egyszerűen vissza kell adnia a törölni kívánt szöveget (t változó) a végleges formájában.
A sejtek rekurzív iterációja
Hasonlóképpen megvalósíthatja egy adott tartomány celláinak rekurzív felsorolását. Tegyük fel, hogy egy lambda függvényt szeretnénk létrehozni CSERE LISTÁJA a forrásszöveg töredékeinek nagykereskedelmi pótlására adott hivatkozási lista szerint. Az eredménynek így kell kinéznie:
Azok. rendezvényünkön CSERE LISTÁJA három érv lesz:
- cella feldolgozandó szöveggel (forráscím)
- a keresésből keresendő értékekkel rendelkező oszlop első cellája
- az oszlop első cellája a keresésből származó helyettesítő értékekkel
A függvénynek fentről lefelé kell haladnia a könyvtárban, és sorrendben le kell cserélnie az összes lehetőséget a bal oldali oszlopban Megtalálni a megfelelő opciókhoz a jobb oldali oszlopból Helyettes. Ezt a következő rekurzív lambda függvénnyel valósíthatja meg:
A lefelé váltást minden iterációnál egy szabványos Excel függvény valósítja meg ÁRTALMATLANÍTÁS (OFFSET), amelynek ebben az esetben három argumentuma van – az eredeti tartomány, soreltolódás (1) és oszlopeltolás (0).
Nos, amint elérjük a könyvtár végét (n = “”), be kell fejeznünk a rekurziót – abbahagyjuk önmagunk hívását, és megjelenítjük, mi halmozódott fel a t forrásszöveg-változóban az összes csere után.
Ez minden. Nincsenek trükkös makrók vagy Power Query-lekérdezések – az egész feladatot egyetlen funkció oldja meg.
- Az Excel új dinamikus tömb funkcióinak használata: FILTER, SORT, UNIC
- Szöveg cseréje és tisztítása a SUBSTITUTE funkcióval
- Makrók és felhasználó által definiált függvények (UDF-ek) létrehozása VBA-ban