Indhold
I øjeblikket har Microsoft Excel næsten fem hundrede regnearksfunktioner tilgængelige via funktionsguidens vindue – knappen fx i formellinjen. Dette er et meget anstændigt sæt, men ikke desto mindre støder næsten enhver bruger før eller siden i en situation, hvor denne liste ikke indeholder den funktion, han har brug for - simpelthen fordi den ikke er i Excel.
Indtil nu var den eneste måde at løse dette problem på makroer, dvs. at skrive din egen brugerdefinerede funktion (UDF = User Defined Function) i Visual Basic, hvilket kræver passende programmeringsevner og til tider slet ikke er let. Men med de seneste Office 365-opdateringer har situationen ændret sig til det bedre - en særlig "wrapper"-funktion er blevet tilføjet til Excel LAMBDA. Med dens hjælp er opgaven med at skabe dine egne funktioner nu løst nemt og smukt.
Lad os se på princippet om dets brug i det følgende eksempel.
Som du højst sandsynligt ved, har Excel flere funktioner til datoparsing, der giver dig mulighed for at bestemme nummeret på dagen, måneden, ugen og året for en given dato. Men af en eller anden grund er der ingen funktion, der bestemmer nummeret på kvartalet, hvilket også ofte er nødvendigt, vel? Lad os rette op på denne mangel og skabe med LAMBDA egen ny funktion til at løse dette problem.
Trin 1. Skriv formlen
Lad os starte med, at vi manuelt på sædvanlig måde vil skrive en formel i en arkcelle, der beregner, hvad vi har brug for. I tilfælde af kvartalsnummeret kan dette for eksempel gøres sådan:
Trin 2. Indpakning i LAMBDA og test
Nu er det tid til at anvende den nye LAMBDA-funktion og pakke vores formel ind i den. Funktionens syntaks er som følger:
=LAMBDA(Variabel 1; Variabel 2; ... VariabelN ; Udtryk)
hvor navnene på en eller flere variable er anført først, og det sidste argument altid er en formel eller et beregnet udtryk, der bruger dem. Variabelnavne bør ikke ligne celleadresser og bør ikke indeholde prikker.
I vores tilfælde vil der kun være én variabel – den dato, som vi beregner kvartalstallet for. Lad os kalde variablen for det, f.eks. d. Så pakker vores formel ind i en funktion LAMBDA og ved at erstatte adressen på den oprindelige celle A2 med et fiktivt variabelnavn, får vi:
Bemærk venligst, at efter en sådan transformation begyndte vores formel (faktisk korrekt!) at producere en fejl, for nu er den oprindelige dato fra celle A2 ikke overført til den. For afprøvning og tillid kan du sende argumenter til den ved at tilføje dem efter funktionen LAMBDA i parentes:
Trin 3. Opret et navn
Nu til den nemme og sjove del. Vi åbner Navn Manager fanen Formula (Formler – Navneadministrator) og opret et nyt navn med knappen Opret (Skab). Kom med og indtast et navn til vores fremtidige funktion (f.eks. Nomkvartala), og i marken Link (Reference) kopier forsigtigt fra formellinjen og indsæt vores funktion LAMBDA, kun uden det sidste argument (A2):
Alt. Efter at have klikket på OK den oprettede funktion kan bruges i enhver celle på ethvert ark i denne projektmappe:
Bruges i andre bøger
LAMBDA og dynamiske arrays
Brugerdefinerede funktioner oprettet med en funktion LAMBDA med succes understøtte arbejdet med nye dynamiske arrays og deres funktioner (FILTER, ENESTÅENDE, GRADE) føjet til Microsoft Excel i 2020.
Lad os sige, at vi vil oprette en ny brugerdefineret funktion, der sammenligner to lister og returnerer forskellen mellem dem – de elementer fra den første liste, som ikke er i den anden. Livets arbejde, ikke? Tidligere brugte de til dette enten funktioner a la VPR (OPLYSNING), eller pivottabeller eller Power Query-forespørgsler. Nu kan du gøre med én formel:
I den engelske version vil det være:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Her er funktionen TÆL.HVIS tæller antallet af forekomster af hvert element i den første liste i den anden og derefter funktionen FILTER udvælger kun de af dem, der ikke havde disse forekomster. Ved at pakke denne struktur ind LAMBDA og oprette et navngivet område baseret på det med et navn, f.eks. SØGE DISTRIBUTION – vi får en praktisk funktion, der returnerer resultatet af at sammenligne to lister i form af et dynamisk array:
Hvis kildedataene ikke er almindelige, men "smarte" tabeller, vil vores funktion også klare sig uden problemer:
Et andet eksempel er dynamisk opdeling af tekst ved at konvertere den til XML og derefter parse den celle for celle ved hjælp af funktionen FILTER.XML, som vi for nylig parsede. For ikke at reproducere denne komplekse formel manuelt hver gang, vil det være lettere at pakke den ind i LAMBDA og skabe et dynamisk område baseret på det, dvs. en ny kompakt og praktisk funktion, som f.eks. navngiver den RAZDTEXT:
Det første argument for denne funktion vil være cellen med kildeteksten, og det andet - separatortegnet, og det vil returnere resultatet i form af et vandret dynamisk array. Funktionskoden vil være som følger:
=LAMBDA(t;d; TRANSPOSER(FILTER.XML(“
Listen af eksempler er uendelig – i enhver situation, hvor du ofte skal indtaste den samme lange og besværlige formel, vil LAMBDA-funktionen gøre livet mærkbart lettere.
Rekursiv optælling af tegn
Alle tidligere eksempler har kun vist én, den mest åbenlyse, side af LAMBDA-funktionen - dens brug som en "indpakning" til at pakke lange formler ind i den og forenkle deres input. Faktisk har LAMBDA en anden, meget dybere side, der gør det til næsten et fuldgyldigt programmeringssprog.
Faktum er, at en grundlæggende vigtig egenskab ved LAMBDA-funktioner er evnen til at implementere dem i rekursion – logik af beregninger, når funktionen i beregningsprocessen kalder sig selv. Ud fra vanen lyder det måske uhyggeligt, men i programmering er rekursion en almindelig ting. Selv i makroer i Visual Basic kan du implementere det, og nu er det, som du kan se, kommet til Excel. Lad os prøve at forstå denne teknik med et praktisk eksempel.
Antag, at vi vil oprette en brugerdefineret funktion, der fjerner alle givne tegn fra kildeteksten. Nytten af en sådan funktion, tror jeg, du behøver ikke at bevise - det ville være meget praktisk at rydde strøede inputdata med dens hjælp, ikke?
Men sammenlignet med de tidligere, ikke-rekursive eksempler, venter der os to vanskeligheder.
- Vi bliver nødt til at finde på et navn til vores funktion, før vi begynder at skrive dens kode, for i den vil dette navn allerede blive brugt til at kalde selve funktionen.
- Indtastning af en sådan rekursiv funktion i en celle og fejlretning af den ved at angive argumenter i parentes efter LAMBDA (som vi gjorde tidligere) vil ikke fungere. Du bliver nødt til at oprette en funktion med det samme "fra bunden" ind Navn Manager (navneadministrator).
Lad os kalde vores funktion, f.eks. CLEAN, og vi vil gerne have, at den har to argumenter – teksten, der skal renses, og listen over udelukkede tegn som en tekststreng:
Lad os oprette, som vi gjorde tidligere, på fanen Formula в Navneadministrator navngivet område, navngiv det RYD og indtast i feltet Range følgende konstruktion:
=LAMBDA(t;d;HVIS(d=””;t;RYD(ERSTATNING(t;VENSTRE(d);””);MIDDEL(d;2;255))))
Her er variablen t den originale tekst, der skal slettes, og d er listen over tegn, der skal slettes.
Det hele fungerer sådan her:
Iteration 1
Fragmentet SUBSTITUTE(t;LEFT(d);"") erstatter, som du måske gætter, det første tegn fra venstre tegn fra sættet d, der skal slettes i kildeteksten t med en tom tekststreng, dvs. fjerner " EN". Som et mellemresultat får vi:
Vsh zkz n 125 rubler.
Iteration 2
Så kalder funktionen sig selv og modtager som input (det første argument) det, der er tilbage efter rensning i det forrige trin, og det andet argument er strengen af udelukkede tegn, der starter ikke fra det første, men fra det andet tegn, dvs. "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," uden det indledende "A" - dette gøres af MID-funktionen. Som før tager funktionen det første tegn fra venstre af de resterende (B) og erstatter det i teksten givet til det (Zkz n 125 rubler) med en tom streng - vi får som et mellemresultat:
125 ru.
Iteration 3
Funktionen kalder sig selv igen og modtager som det første argument, hvad der er tilbage af teksten, der skal ryddes ved den forrige iteration (Bsh zkz n 125 ru.), Og som det andet argument er sættet af udelukkede tegn afkortet med et tegn mere til den venstre, dvs. "VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.," uden initialet "B". Så tager den igen det første tegn fra venstre (B) fra dette sæt og fjerner det fra teksten - vi får:
sh zkz n 125 ru.
Og så videre – jeg håber du får ideen. Med hver iteration vil listen over tegn, der skal fjernes, blive afkortet til venstre, og vi vil søge efter og erstatte det næste tegn fra sættet med et tomrum.
Når alle tegnene løber tør, bliver vi nødt til at forlade løkken - denne rolle udføres bare af funktionen IF (HVIS), hvori vores design er pakket ind. Hvis der ikke er tegn tilbage at slette (d=””), så skal funktionen ikke længere kalde sig selv, men skal blot returnere den tekst, der skal slettes (variabel t) i sin endelige form.
Rekursiv iteration af celler
På samme måde kan du implementere en rekursiv opregning af celler i et givet område. Antag, at vi vil oprette en lambda-funktion ved navn UDSKIFTNINGSLISTE til engrosudskiftning af fragmenter i kildeteksten i henhold til en given referenceliste. Resultatet skulle se sådan ud:
De der. på vores funktion UDSKIFTNINGSLISTE der vil være tre argumenter:
- celle med tekst, der skal behandles (kildeadresse)
- den første celle i en kolonne med værdier for at søge fra opslag
- den første celle i kolonnen med erstatningsværdier fra opslag
Funktionen skal gå fra top til bund i mappen og erstatte sekventielt alle muligheder fra venstre kolonne At finde til de tilsvarende muligheder fra højre kolonne Erstatning. Du kan implementere dette med følgende rekursive lambda-funktion:
Skift ned ved hver iteration er implementeret af en standard excel-funktion BORTSKAFFELSE (OFFSET), som i dette tilfælde har tre argumenter – det oprindelige område, rækkeskift (1) og kolonneskift (0).
Nå, så snart vi når slutningen af mappen (n = “”), skal vi afslutte rekursionen – vi stopper med at kalde os selv og viser, hvad der er akkumuleret efter alle udskiftningerne i kildetekstvariablen t.
Det er alt. Ingen vanskelige makroer eller Power Query-forespørgsler – hele opgaven løses af én funktion.
- Sådan bruger du Excels nye dynamiske array-funktioner: FILTER, SORT, UNIC
- Udskiftning og oprydning af tekst med SUBSTITUTE-funktionen
- Oprettelse af makroer og brugerdefinerede funktioner (UDF'er) i VBA