Inhalter
Am Moment huet Microsoft Excel bal fënnefhonnert Aarbechtsblatfunktiounen verfügbar duerch d'Funktion Wizard Fënster - Knäppchen fx an der Formelbar. Dëst ass e ganz uerdentleche Set, awer trotzdem, bal all Benotzer begéint fréier oder spéider eng Situatioun wou dës Lëscht net déi Funktioun enthält déi hie brauch - einfach well et net an Excel ass.
Bis elo war deen eenzege Wee fir dëse Problem ze léisen Makroen, also schreiwen Är eege Benotzerdefinéiert Funktioun (UDF = User Defined Function) a Visual Basic, déi entspriechend Programméierungsfäegkeeten erfuerdert an heiansdo guer net einfach ass. Wéi och ëmmer, mat de leschten Office 365 Updates huet d'Situatioun sech zum bessere geännert - eng speziell "Wrapper" Funktioun ass an Excel bäigefüügt LAMBDA. Mat senger Hëllef ass d'Aufgab fir Är eege Funktiounen ze kreéieren elo einfach a schéin geléist.
Loosst eis de Prinzip vu senger Benotzung am folgende Beispill kucken.
Wéi Dir héchstwahrscheinlech wësst, huet Excel verschidde Dateparsingfunktiounen, déi Iech erlaben d'Zuel vum Dag, Mount, Woch a Joer fir e bestëmmten Datum ze bestëmmen. Mee aus iergendengem Grond gëtt et keng Funktioun déi d'Zuel vum Véierel bestëmmt, wat och dacks gebraucht gëtt, oder? Loosst eis dës Defizit fixéieren an erstellen mat LAMBDA eegen nei Funktioun fir dëse Problem ze léisen.
Schrëtt 1. Schreift d'Formel
Loosst eis mat der Tatsaach ufänken datt mir manuell op déi üblech Manéier eng Formel an enger Blatzell schreiwen, déi berechent wat mir brauchen. Am Fall vun der Véierelzuel kann dat zum Beispill esou gemaach ginn:
Schrëtt 2. Wrapping an LAMBDA an Testen
Elo ass et Zäit déi nei LAMBDA Funktioun anzesetzen an eis Formel dran ze wéckelen. D'Funktioun Syntax ass wéi follegt:
=LAMBDA(Variabel 1; Variabel 2; … VariabelN ; Ausdrock)
wou d'Nimm vun enger oder méi Variabelen als éischt opgelëscht sinn, an dat lescht Argument ass ëmmer eng Formel oder e berechent Ausdrock deen se benotzt. Variabel Nimm sollen net wéi Zelladressen ausgesinn a sollten keng Punkte enthalen.
An eisem Fall gëtt et nëmmen eng Variabel - den Datum fir deen mir d'Véierelzuel berechnen. Loosst eis d'Variabel dofir nennen, soen d. Da wéckelt eis Formel an eng Funktioun LAMBDA an d'Adress vun der ursprénglecher Zell A2 mat engem fiktiven Variabelnumm ersetzen, kréie mir:
Notéiert w.e.g. datt no sou enger Transformatioun eis Formel (tatsächlech richteg!) ugefaang huet e Feeler ze produzéieren, well elo ass den ursprénglechen Datum vun der Zell A2 net op et transferéiert. Fir Testen a Vertrauen, kënnt Dir Argumenter un et passéieren andeems Dir se no der Funktioun bäidréit LAMBDA an parentheses:
Schrëtt 3. Schafen engem Numm
Elo fir den einfachen a lëschtegen Deel. Mir maachen op Numm Manager Reiter Formule (Formelen - Numm Manager) a kreéiert en neien Numm mam Knäppchen schafen (Erstellen). Kommt mat a gitt en Numm fir eis zukünfteg Funktioun (zum Beispill, Nomkvartala), an am Feld Link (Referenz) kopéiert virsiichteg aus der Formelbar a paste eis Funktioun LAMBDA, nëmmen ouni dat lescht Argument (A2):
Alles. Nodeems Dir op klickt OK déi erstallt Funktioun kann an all Zell op all Blat vun dësem Aarbechtsbuch benotzt ginn:
Benotzt an anere Bicher
LAMBDA an dynamesch Arrays
Benotzerdefinéiert Funktiounen erstallt mat enger Funktioun LAMBDA erfollegräich Aarbecht mat neien dynamesche Arrays an hir Funktiounen ënnerstëtzen (FILTER, UNIK, Schouljoer) zu Microsoft Excel am Joer 2020 bäigefüügt.
Loosst eis soen, mir wëllen eng nei Benotzerdefinéiert Funktioun erstellen déi zwou Lëschte vergläichen an den Ënnerscheed tëscht hinnen zréckginn - déi Elementer vun der éischter Lëscht déi net an der zweeter sinn. Liewen d'Aarbecht, ass et net? Virdrun, fir dës benotzt se entweder Funktiounen a la VPR (VLOOKUP), oder PivotTables, oder Power Query Ufroen. Elo kënnt Dir mat enger Formel maachen:
An der englescher Versioun wäert et sinn:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Hei d'Funktioun COUNTIF zielt d'Zuel vun Optriede vun all Element vun der éischter Lëscht an der zweeter, an dann d'Funktioun FILTER wielt nëmmen déi vun hinnen, déi dës Optriede net haten. Andeems Dir dës Struktur wéckelt LAMBDA an erstellt eng benannt Gamme baséiert op et mat engem Numm, zum Beispill, Sichen DISTRIBUTION - mir kréien eng praktesch Funktioun déi d'Resultat vum Verglach vun zwou Lëschten a Form vun enger dynamescher Array zréckkënnt:
Wann d'Quelldaten net gewéinlech sinn, awer "smart" Dëscher, wäert eis Funktioun och ouni Probleemer këmmeren:
En anert Beispill ass dynamesch opzedeelen Text vun Ëmwandlung et zu XML an dann parsing et Zell fir Zell mat der FILTER.XML Funktioun mir viru kuerzem parsed. Fir dës komplex Formel net all Kéier manuell ze reproduzéieren, ass et méi einfach et an LAMBDA ze wéckelen an en dynamesche Beräich ze kreéieren op Basis vun deem, also eng nei kompakt a praktesch Funktioun, déi se nennt, zum Beispill RAZDTEXT:
Dat éischt Argument vun dëser Funktioun wäert d'Zelle mat dem Quelltext sinn, an déi zweet - de Separator Charakter, an et gëtt d'Resultat a Form vun enger horizontaler dynamescher Array zréck. De Funktiounscode wäert wéi follegt sinn:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
D'Lëscht vun de Beispiller ass endlos - an all Situatioun wou Dir dacks déi selwecht laang an ëmständlech Formel aginn musst, wäert d'LAMBDA Funktioun d'Liewen merkbar méi einfach maachen.
Rekursiv Opzielung vu Personnagen
All virdrun Beispiller hunn nëmmen eng, déi offensichtlechst Säit vun der LAMBDA Funktioun gewisen - seng Notzung als "Wrapper" fir laang Formelen dran ze packen an hir Input ze vereinfachen. Tatsächlech huet LAMBDA eng aner, vill méi déif Säit, déi et zu bal eng vollwäerteg Programméierungssprooch mécht.
De Fakt ass datt eng fundamental wichteg Feature vu LAMBDA Funktiounen d'Fäegkeet ass se an ëmzesetzen Rekursioun - Logik vun de Berechnungen, wann am Prozess vun der Berechnung d'Funktioun sech selwer nennt. Vun der Gewunnecht kann et grujheleg kléngen, awer an der Programméierung ass Rekursioun eng gemeinsam Saach. Och a Makroen an Visual Basic kënnt Dir et ëmsetzen, an elo, wéi Dir gesitt, ass et op Excel komm. Loosst eis probéieren dës Technik mat engem praktesche Beispill ze verstoen.
Ugeholl mir wëllen eng Benotzerdefinéiert Funktioun erstellen déi all gegebene Charaktere vum Quelltext ewechhuelen. D'Nëtzlechkeet vun esou enger Funktioun, ech mengen, Dir braucht net ze beweisen - et wier ganz bequem fir dreckeg Inputdaten mat senger Hëllef ze läschen, richteg?
Wéi och ëmmer, am Verglach zu de fréiere, net-rekursive Beispiller, erwaarden eis zwee Schwieregkeeten.
- Mir musse mat engem Numm fir eis Funktioun erauskommen ier mer de Code ufänken ze schreiwen, well an deem gëtt dësen Numm scho benotzt fir d'Funktioun selwer ze nennen.
- Sou eng rekursiv Funktioun an eng Zell aginn an ze debuggéieren andeems Argumenter an Klammern no LAMBDA spezifizéieren (wéi mir virdru gemaach hunn) funktionnéiert net. Dir musst eng Funktioun direkt "vun Null" erstellen Numm Manager (Numm Manager).
Loosst eis eis Funktioun nennen, sot CLEAN, a mir hätte gär datt et zwee Argumenter huet - den Text fir ze botzen an d'Lëscht vun ausgeschlossene Zeechen als Textstring:
Loosst eis erstellen, wéi mir virdru gemaach hunn, op der Tab Formule в Numm Manager genannt Beräich, Numm et KLOER a gitt am Feld Rei folgend Konstruktioun:
=LAMBDA(t;d;IF(d="";t;CLEAR(SUBSTITUT(t;LEFT(d);"");MID(d;2;255))))
Hei ass d'Variabel t den ursprénglechen Text deen geläscht gëtt, an d ass d'Lëscht vun den Zeeche fir ze läschen.
Et funktionnéiert alles esou:
Iteratioun 1
D'Fragment SUBSTITUTE(t;LEFT(d);""), wéi Dir vläicht roden, ersetzt den éischte Charakter vum lénksen Zeechen aus dem Set d, deen am Quelltext t geläscht gëtt, duerch eng eidel Textstring, also läscht de " A". Als Tëscheresultat kréie mir:
Vsh zkz n 125 Rubelen.
Iteratioun 2
Da rifft d'Funktioun sech an als Input (dat éischt Argument) kritt dat wat no der Botzen am virege Schrëtt iwwreg ass, an dat zweet Argument ass d'String vun ausgeschlossene Zeechen, déi net vum éischte, mee vum zweete Charakter ufänken, dh "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYYA. ", ouni den initialen "A" - dëst gëtt vun der MID Funktioun gemaach. Wéi virdru hëlt d'Funktioun den éischte Charakter vu lénks vun de verbleiwen (B) an ersetzt se am Text deen et gëtt (Zkz n 125 Rubel) mat engem eidele String - mir kréien als Zwëscheresultat:
125 ru.
Iteratioun 3
D'Funktioun nennt sech erëm, a kritt als éischt Argument dat wat vum Text lénks ass fir bei der viregter Iteratioun ze läschen (Bsh zkz n 125 ru.), An als zweet Argument ass de Set vun ausgeschlossene Charaktere vun engem méi Charakter ofgeschnidden fir déi lénks, dh "VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,"Ouni den initialen "B". Dann hëlt et erëm den éischte Charakter vu lénks (B) aus dësem Set an hëlt en aus dem Text - mir kréien:
sh zkz n 125 ru.
An esou weider - ech hoffen Dir kritt d'Iddi. Mat all Iteratioun gëtt d'Lëscht vun den Zeeche fir ze läschen lénks ofgeschnidden, a mir sichen no an ersetzen den nächste Charakter aus dem Set mat engem Void.
Wann all d'Personnagen auslafen, musse mir d'Loop verloossen - dës Roll gëtt just vun der Funktioun ausgefouert IF (WANN), an deem eisen Design agewéckelt ass. Wann et keng Zeeche méi sinn fir ze läschen (d=””), da soll d'Funktioun sech net méi uruffen, mee einfach den Text zréckginn fir ze läschen (Variabel t) a senger definitiver Form.
Rekursiv Iteratioun vun Zellen
Ähnlech kënnt Dir eng rekursiv Opzielung vun Zellen an engem bestëmmte Beräich implementéieren. Ugeholl mir wëllen eng Lambda Funktioun erstellen genannt ERSETZUNG LËSCHT fir Grousshandel Ersatz vun Fragmenter am Quelltext no enger bestëmmter Referenz Lëscht. D'Resultat soll esou ausgesinn:
Déi. bei eiser Funktioun ERSETZUNG LËSCHT et ginn dräi Argumenter:
- Zell mat Text fir ze veraarbecht (Quelladress)
- déi éischt Zell vun enger Kolonn mat Wäerter fir aus dem Lookup ze sichen
- déi éischt Zell vun der Kolonn mat Ersatzwäerter aus dem Lookup
D'Funktioun soll vun uewe bis ënnen am Verzeechnes goen an sequenziell all Optiounen aus der lénkser Kolonn ersetzen Fannen op déi entspriechend Optiounen aus der rietser Kolonn Substitute. Dir kënnt dëst mat der folgender rekursiver Lambda Funktioun ëmsetzen:
Shift down bei all Iteratioun gëtt vun enger Standard Excel Funktioun implementéiert VERSTÄNNEGUNG (OFFSET), déi an dësem Fall dräi Argumenter huet - d'Original Gamme, Zeile Verréckelung (1) a Kolonn Verréckelung (0).
Gutt, soubal mir d'Enn vum Verzeechnes erreechen (n = ""), musse mir d'Rekursioun ophalen - mir stoppen eis selwer ze ruffen a weisen wat no all Ersatzstécker an der Quelltextvariabel t accumuléiert ass.
Dat ass alles. Keng komplizéiert Makroen oder Power Query Ufroen - déi ganz Aufgab gëtt vun enger Funktioun geléist.
- Wéi benotzen ech déi nei dynamesch Arrayfunktiounen vun Excel: FILTER, SORT, UNIC
- Ersetzen a botzen Text mat der SUBSTITUTE Funktioun
- Erstellt Makroen a Benotzerdefinéiert Funktiounen (UDFs) an VBA