Excel heeft duizenden functies, maar de meeste gebruikers houden zich aan de basisfuncties, zoals SOM en GEMIDDELDE. Hoewel deze functies voldoende zijn voor eenvoudige taken, zijn er drie functies die complexere scenario's met veel minder moeite afhandelen. De functies RIJK, LATEN en LAMBDA worden minder vaak gebruikt, maar ze lossen specifieke problemen op die omslachtige oplossingen of lange, moeilijk te onderhouden formules vereisen.
Met deze functies kunt u dynamische, zelfstandige oplossingen bouwen die automatisch worden bijgewerkt, in plaats van meerdere hulpkolommen te maken of formules over tientallen cellen te kopiëren. Of u nu sequentiële gegevens genereert, complexe berekeningen beheert of herbruikbare aangepaste functies maakt, deze functies behoren tot de meest nuttige. Excel-functies die u veel werk kunnen besparen.
Snelle links
4. SEQUENCE-functie: automatisch gegevens genereren
Dynamische nummer- en datumreeksen maken
De SEQUENCE-functie creëert arrays van serienummers zonder dat u elke waarde handmatig hoeft in te voeren. Of u nu een lijst met medewerkers-ID's, factuurnummers of datumbereiken nodig hebt, deze functie verwerkt ze naadloos.
De formule is eenvoudig en duidelijk:
=VOLGORDE(rijen, [kolommen], [start], [stap])
Laten we de parameters eens analyseren:
- rijen: Hiermee geeft u het aantal getallen verticaal op.
- kolommen: Bepaalt de horizontale spreiding. Laat één kolom leeg.
- begin: Geeft het startnummer op, de standaardwaarde is 1.
- stap: Geeft de stapgrootte tussen getallen aan. De standaardwaarde is ook 1.
Gegeven een verkoopdataset is de functie VOLGORDE nuttig voor het genereren van referentiecijfers. De volgende formule genereert bijvoorbeeld cijfers van 1 tot en met 32.
=VOLGORDE(32)
Als u bij 1001 wilt beginnen, kunt u het volgende gebruiken:
=REEKS(32, 1, 1001)
De functie is ook handig bij datumreeksen. De volgende formule genereert twaalf opeenvolgende datums, beginnend op 1 januari. Dit is beter dan het handmatig invoeren van datums voor maandelijkse rapporten of projectplanningen.
=SEQUENCE(12, 1, DATE(2025, 1, 1), 1)
U kunt ook alleen werkdagen maken door de functies SEQUENCE en NUMBERS te combineren. Andere DATUM in Excel, zoals WORKDAY, voor geavanceerdere planningscenario's.
Grote SEQUENCE-arrays kunnen uw spreadsheets vertragen. Genereer niet meer dan 10,000 waarden tegelijk, tenzij dit absoluut noodzakelijk is. Als u grote datasets nodig hebt, overweeg dan om deze in kleinere delen op te splitsen of externe gegevensbronnen te gebruiken.
3. Met de LET-functie kunt u complexe formules onderhouden.
Elimineer herhalende berekeningen en verbeter de leesbaarheid.
LET wijst namen toe aan waarden binnen een formule. Dit voorkomt repetitieve berekeningen en maakt het werk leesbaarder. In plaats van dezelfde expressie meerdere keren te typen, kunt u deze één keer definiëren en er bij naam naar verwijzen.
De zinsstructuur volgt dit patroon:
=LET(naam1, waarde1, [naam2, waarde2, ...], berekening)
U kunt meerdere variabelen definiëren door meer naam-waardeparen toe te voegen. De berekening gebruikt uiteindelijk deze benoemde variabelen om het resultaat te produceren.
Stel dat u, gegeven een verkoopdataset, de commissie van een verkoper inclusief bonussen berekent. Zonder LET zou u schrijven:
=IF(G2*0.05>500, G2*0.05*1.1, G2*0.05)
De commissieberekening van B2*0.05 verschijnt twee keer. Met LET is het nog duidelijker:
=LET(commissie, G2*0.05, IF(commissie>500, commissie*1.1, commissie))
Het voert dezelfde berekening uit, maar stelt de commissie één keer in het begin in. U hoeft het commissietarief slechts op één plek te wijzigen.
Voor complexe winstmargeanalyses is LET nuttiger. Het volgende voorbeeld definieert elk onderdeel duidelijk.
=LET(omzet, G2, kosten, L2, marge, (omzet-kosten)/omzet, ALS(marge>0.3, "Hoog", ALS(marge>0.15, "Gemiddeld", "Laag")))
Deze formule berekent de winstmarge als percentage en categoriseert deze vervolgens als hoog (boven 30%), gemiddeld (15-30%) of laag (onder 15%). Elk onderdeel heeft een duidelijke naam, waardoor de logica gemakkelijk te volgen is.
Met deze methode wordt de complexiteit van de formule gehalveerd. Zorg ervoor dat u uw spreadsheets later gemakkelijker kunt corrigeren en wijzigen.
2. Met de LAMBDA-functie maakt u herbruikbare aangepaste functies.
Aangepaste functies maken voor terugkerende bedrijfslogica
Met de LAMBDA-functie kunt u aangepaste functies maken die u herhaaldelijk in uw werkmap kunt gebruiken. In plaats van overal formules te kopiëren, kunt u één functie maken die invoer accepteert en berekende resultaten retourneert.
De formule is:
=LAMBDA(parameter1, [parameter2, ...], berekening)
Parameters fungeren als tijdelijke aanduidingen: wanneer u de functie aanroept, geeft u daadwerkelijke waarden door die deze tijdelijke aanduidingen vervangen. De berekening gebruikt deze parameters om uitvoer te genereren.
Stel dat u regelmatig gewogen prestatiescores berekent. U zou dan een LAMBDA-functie kunnen maken zoals de volgende:
=LAMBDA(omzet, quotum, gewicht, (omzet/quotum)*gewicht)
Hiermee wordt een herbruikbare functie gemaakt die drie invoergegevens gebruikt: werkelijke verkopen, verkoopquota en een wegingsfactor. De functie retourneert een gewogen prestatiescore door de verkopen te delen door de quota en te vermenigvuldigen met de weging. Noem deze functie 'PerformanceScore' met behulp van Naambeheer in Excel.
Om uw LAMBDA-functie een naam te geven, gaat u naar Formules > Naambeheer > Nieuw.
U kunt deze functie nu overal in uw werkmap aanroepen.
=Prestatiescore(B2, C2, 0.7)
Met deze functie wordt een prestatiescore berekend op basis van het verkoopbedrag, het aandeel en de opgegeven wegingsfactor.
Om regio's te analyseren, kunt u een functie maken die regio's rangschikt op basis van omzet:
=LAMBDA(omzet, ALS(omzet>100000, "Hoog", ALS(omzet>50000, "Gemiddeld", "Laag")))
Deze functie classificeert de omzet in drie niveaus: hoog voor bedragen boven de $ 100,000, gemiddeld voor bedragen tussen de $ 50,000 en $ 100,000, en laag voor bedragen onder de $ 50,000. U kunt het 'Omzet' noemen en het als volgt in al uw werkbladen gebruiken:
=Omzet(J2)
De LAMBDA-functie werkt ook met andere functies en Hiermee kunt u formules schrijven als menselijke taal Gebruik beschrijvende namen in plaats van dubbelzinnige celverwijzingen.
U kunt uw LAMBDA-functies georganiseerd houden in de Naambeheerder door voorvoegsels zoals "fn_" te gebruiken voor alle aangepaste functies (bijvoorbeeld "fn_PerformanceScore"). Dit maakt ze gemakkelijker te vinden en voorkomt conflicten met reguliere benoemde scopes.
1. Ik combineer deze functies om krachtige oplossingen te creëren.
Het ontwikkelen van uitgebreide tools voor bedrijfsanalyse
Wanneer SEQUENCE, LET en LAMBDA samen worden gebruikt, lossen ze problemen op die anders meerdere hulpkolommen of complexe matrixformules zouden vereisen. Deze combinatie zorgt voor dynamische en onderhoudbare oplossingen.
Laten we eens kijken naar het bouwen van een tool voor verkoopprognoses met behulp van verkoopgegevens. De volgende formule berekent een verkoopprognose voor 12 maanden voor een enkel startbedrag. De formule begint met het definiëren van twee belangrijke variabelen met behulp van LET. De waarde uit cel G2 wordt gebruikt als basiswaarde voor de verkoop.
=LET(base_sale, G2, growth_rate, L2, ProjectMonthly, LAMBDA(month, base_sale * (1 + growth_rate)^month), ProjectMonthly(SEQUENCE(12)))
Vervolgens neemt u een maandelijkse groeivoet van 2 (0.04%) uit L4. U kunt deze waarde variëren om verschillende scenario's te modelleren. Vervolgens definieert u een kleine, herbruikbare functie genaamd ProjectMonthly. Deze functie berekent de verwachte omzet voor een bepaalde maand op basis van de basisomzet en groeivoet.
Bovendien roept het de functie ProjectMonthly aan en geeft het SEQUENCE(12) eraan door. Dit genereert een matrix met getallen van 1 tot en met 12, en LAMBDA past de berekeningen automatisch toe op elk getal in deze matrix.
Hier is een handige beloningscalculator die beloningen berekent op basis van het behalen van doelen.
=LAMBDA(verkoop, doel, LET(ratio, verkoop/doel, ALS(ratio>=1.2, verkoop*0.08, ALS(ratio>=1, verkoop*0.05, 0))))
Begin klein en bouw het vervolgens complexer op.
Deze functies werken het beste wanneer ze zorgvuldig worden gecombineerd. Begin met eenvoudige toepassingen: gebruik SEQUENCE om testgegevens te genereren, LET om dubbele berekeningen op te schonen en LAMBDA voor bedrijfsregels die u vaak gebruikt. Zodra u vertrouwd bent met elke functie afzonderlijk, zult u natuurlijke mogelijkheden vinden om ze te combineren tot geavanceerdere oplossingen.
De leercurve is niet steil, maar de beloning is enorm. Uw spreadsheets worden betrouwbaarder, gemakkelijker te controleren en eenvoudiger aan te passen wanneer de bedrijfsbehoeften veranderen. Dat maakt deze drie functies zo waardevol voor iedereen die regelmatig met data werkt.
