Formules in Excel zijn al een programmeertaal. Wanneer u een model in Excel bouwt, schrijft u in feite een programma om een set outputs te berekenen op basis van een set inputs. Het Calc-team Redmond heeft gewerkt aan een aantal verbeteringen in de Excel-formuletaal om Excel een beetje meer als een programmeertaal te maken. De eerste hiervan, de LET-functie, is nu in bèta. Iedereen die zich aanmeldt voor het Insider Fast-kanaal van Office 365 moet toegang hebben tot LET.
Soms bouw je een formule die steeds weer naar dezelfde deelberekening moet verwijzen. Met de LET-functie kunt u een variabele definiëren en de berekening voor die variabele. Uw berekening kan maximaal 126 variabelen bevatten. Elke variabele kan de berekeningen in de vorige variabelen hergebruiken. Het laatste argument in de LET-functie is een formule die een waarde (of een matrix) naar de cel retourneert. Die laatste formule verwijst naar variabelen die eerder in de LET-functie zijn gedefinieerd.
Dit is het gemakkelijkst te zien als ik je een voorbeeld laat zien. Ik vond willekeurig een formule gepost op het prikbord in 2010. Deze formule, van lid Special-K99, is ontworpen om het voorlaatste woord in een zin te vinden.
Als ik de originele formule stap voor stap zou bouwen, zou ik deze in stappen bouwen.
-
Stap 1: Neem in B4 de TRIM van de originele zin om herhaalde spaties te verwijderen.
TRIM-functie om herhaalde spaties te verwijderen. -
Stap 2: Zoek uit hoeveel woorden er in B4 staan door de LEN van de bijgesneden tekst te vergelijken met de lengte van de bijgesneden tekst na het verwijderen van spaties met SUBSTITUTE. In een zin van vier woorden zijn er drie spaties. In de huidige opgave wil je het tweede woord vinden, dus het minteken aan het einde van deze formule.
LEN- en SUBSTITUTE-functies om woorden te tellen -
Stap 3: Voeg een karaat (^) toe voor het gewenste woord. Dit gebruikt opnieuw SUBSTITUTE maar maakt gebruik van het derde argument in SUBSTITUTE om de 2e spatie te vinden. Het zal niet altijd de 2e spatie zijn. U moet het resultaat van stap 2 gebruiken als het derde argument in stap 3.
Karaat gebruiken in VERVANGEN -
Stap 4: Isoleer alle woorden na de karaat met MID en FIND.
Isoleer alle woorden na de karaat met MID en FIND -
Stap 5: Isoleer het voorlaatste woord met MID en opnieuw FIND.
Isoleer het voorlaatste woord met MID en FIND
Wanneer ze worden opgesplitst in kleine berekeningen, zoals hierboven weergegeven, kunnen veel mensen de logica van de berekening volgen. Ik bouw regelmatig formules met behulp van de hierboven getoonde methode.
Maar ik wil geen vijf kolommen opnemen voor één formule, dus begin ik die vijf formules samen te voegen tot één formule. De formule in F4 gebruikt E4 twee keer. Kopieer alles in de formulebalk voor E4 na het gelijkteken. Gebruik Plakken om E4 op beide plaatsen te vervangen. Ga door met het vervangen van celverwijzingen door hun formules totdat het enige waarnaar wordt verwezen door de uiteindelijke formule cel A4 is. Op dit punt heb je een waanzinnig lange formule:
Waarom is dit zo verwarrend? Hoe veranderden vijf formules met een gemiddelde lengte van 24 tekens in een formule van 370 tekens? Dit komt doordat er in de uiteindelijke formule in totaal 12 keer naar de eenvoudige formule in B4 wordt verwezen. Als je = TRIM (A4) niet hebt opgeslagen in cel B4, typ je uiteindelijk TRIM (A4) twaalf keer in de uiteindelijke formule.
Hier ziet u hoe vaak elk van de subformules in de uiteindelijke formule wordt gebruikt.
LET aan de redding
Met de LET-functie kunt u variabelen één keer in de formule definiëren en deze variabelen later in de formule opnieuw gebruiken. In de onderstaande afbeelding worden vier variabelen gedefinieerd in de variabeledefinities voordat een laatste berekening het laatste woord retourneert.
Het lijkt erop dat de beste praktijk hier is om Alt + Enter te gebruiken na elke variabeledefinitie in de formule. Hoewel uw formules A, B, C en D kunnen zijn, kan het geen kwaad om betekenisvolle variabelenamen te gebruiken, net zoals u dat in elke programmeertaal zou doen.
Merk op in de bovenstaande afbeelding dat na het definiëren van TRIMTEXT als = TRIM (A4), de TRIMTEXT-variabele opnieuw wordt gebruikt in de definitie voor WhichSpace en CaratText.
Bekijk video
Je kunt hier de stappen bekijken om de subformules te combineren in de megaformule en in de LET-functie:
Bij andere tests berekent de LET-formule ongeveer 65% sneller dan de vergelijkbare megaformule.
