Hoe gebruik je dynamische matrixformules in Microsoft Excel?

Als je dagelijks met spreadsheets werkt, zul je gemerkt hebben dat zodra het aantal rijen en kolommen toeneemt, Klassieke Excel-formules schieten soms tekort. Om complexe berekeningen gemakkelijk uit te voeren. Daar komen dynamische matrixformules van pas, een van die nieuwe dingen die je, zodra je ze onder de knie hebt, in bijna elk boek zult gebruiken.

In moderne versies van Excel (vooral in Microsoft 365We hebben een nieuwe rekenmodule waarmee één formule meerdere resultaten tegelijk kan genereren, verspreid over meerdere aangrenzende cellen, zonder dat handmatig kopiëren nodig is. Dankzij deze functionaliteit, “overloop” van resultaten als geheelNu is het veel gemakkelijker om te sorteren, filteren, lijsten te genereren of geavanceerde berekeningen uit te voeren zonder gebruik te hoeven maken van... Tricks zeldzame toetsencombinaties zoals Ctrl+Shift+Enter.

Gerelateerd artikel:

Automatiseer grafieken met benoemde bereiken en dynamische reeksen in Excel.

Wat is overloop in dynamische matrixformules?

In het nieuwe berekeningsmodel van Excel kan een formule niet slechts één waarde retourneren, maar een hele georganiseerde set resultaten; deze set wordt meestal een resultaat genoemd. overlopende uitvoermatrix of bereikWanneer dit gebeurt, plaatst Excel die waarden automatisch in nabijgelegen cellen, waarbij de formule naar beneden, naar rechts of in beide richtingen wordt uitgebreid, afhankelijk van de grootte van het resultaat.

Bijvoorbeeld bij het schrijven van de formule =SORTEREN(D2:D11,1,-1) In één cel (bijvoorbeeld F2) genereert Excel een aflopend gesorteerde lijst op basis van het bereik D2:D11. Het resultaat beslaat 10 rijen, maar u voert de formule slechts in één cel in; de overige posities worden door Excel zelf opgevuld met behulp van dit overloopmechanisme.

Formules die de grootte van hun resultaat kunnen aanpassen op basis van de brongegevens, worden genoemd dynamische matrixformulesWanneer deze formules een resultaatbereik opleveren dat buiten de cel valt waarin de formule is geschreven, spreekt men van een "overloop" van de formule. Het gebied dat de formule beslaat, wordt het overloopbereik genoemd.

In de praktijk betekent dit dat veel functies, zoals SORT, FILTER, RANDOMAR, SEQUENCE of UNIQUE, nu zo zijn ontworpen dat ze een resultaat retourneren. complete, direct bruikbare datamatricesop een veel directere en beter leesbare manier dan met de oude matrixformules.

Hoe het overloopbereik zich gedraagt ​​in Excel

Wanneer u een dynamische matrixformule bevestigt door alleen op de Enter-toets te drukken, analyseert Excel het resultaat en past automatisch de grootte van het uitvoerbereik aan om alle waarden die de formule retourneert te kunnen verwerken. Plaats vervolgens elk element van de matrix in de bijbehorende cel binnen dat overloopbereik.

Als je een van deze formules toepast op een lijst of tabel met gegevens, kan het erg praktisch zijn om de brongegevens om te zetten in een Excel-tabel met gestructureerde verwijzingenDe tabellen passen zich automatisch aan wanneer u rijen toevoegt of verwijdert, zodat dynamische matrixformules die ze gebruiken, automatisch worden bijgewerkt zonder dat u iets hoeft te doen.

Het is belangrijk om te weten dat overloopformules niet werken binnen tabellen zelf: Overloop is niet toegestaan ​​in een Excel-tabel.In plaats daarvan kunt u die formules beter in het reguliere raster plaatsen (buiten de tabel) en de tabel alleen als gegevensbron gebruiken. Tabellen zijn bedoeld om records op te slaan, niet om er een volledig uitvoerbereik in uit te breiden.

Wanneer u op een cel binnen het overloopbereik klikt, tekent Excel een gemarkeerd kader rond alle betreffende cellen. Dit maakt het in één oogopslag duidelijk. hoe ver de formule zich uitstrekt en welke cellen ervan afhankelijk zijn.Zodra je een cel buiten dat bereik selecteert, verdwijnt de rand.

Een ander belangrijk detail is dat in een overloopbereik alleen de eerste cel (die in de linkerbovenhoek) de formule daadwerkelijk bevat. De overige cellen tonen de resultaten, maar als je er een selecteert, wordt de formule gedimd weergegeven in de formulebalk. Je kunt het niet rechtstreeks wijzigen.U moet altijd de broncel bewerken; nadat u deze hebt gewijzigd en op Enter hebt gedrukt, zal Excel het hele overlopende bereik in één keer opnieuw berekenen.

Overlappende fouten en #OVERFLOW-bericht!

Om een ​​dynamische matrixformule soepel te laten uitbreiden, moet het uitvoergebied in Excel leeg zijn. Als gegevens, formules of andere elementen zich in een van de cellen bevinden waar de resultaten moeten verschijnen, treedt er een fout op. Overloopbereik overlap en Excel kan de bewerking niet voltooien.

In dat geval krijgt u in plaats van het verwachte resultaat een foutmelding te zien. #OVERLOOP! In de cel waar je de formule hebt ingevoerd. Zo geeft Excel aan dat er een vergrendeling is die voorkomt dat de matrix in alle cellen wordt geplaatst waar de resultaten moeten worden opgeslagen.

Als u de betreffende formule selecteert, toont Excel met een stippellijn het bereik waar de formule naartoe wil overlopen, inclusief eventuele cellen die het proces blokkeren. Deze weergave stelt u in staat om De problematische cellen gemakkelijk lokaliseren zodat ze geleegd kunnen worden of de inhoud ervan naar een andere locatie kan worden overgebracht.

Zodra je de gegevens verwijdert die de uitbreiding belemmeren (bijvoorbeeld losse waarden of eerdere formules), zal Excel bij het opnieuw berekenen van het werkblad de formule correct laten overlopen. In veel gevallen is het verwijderen van een paar cellen al voldoende om de foutmelding #OVERFLOW! direct te laten verdwijnen.

Het is ook mogelijk dat de formule zelf onjuist is ontworpen en een array retourneert die te groot is voor de beschikbare ruimte. In dergelijke gevallen is het raadzaam de formule te controleren. zowel de logica van de formule als de vrije ruimte eromheen om ervoor te zorgen dat het outputbereik naar behoefte kan worden uitgebreid.

Verschillen tussen dynamische matrixformules en traditionele CSE-formules

Vóór de komst van dynamische matrices werden matrixformules geïntroduceerd met de bekende combinatie Ctrl+Shift+Enter (CSE)Deze oude formules worden in Excel nog steeds ondersteund omwille van achterwaartse compatibiliteit, maar de aanbevolen werkwijze is tegenwoordig om met het nieuwe dynamische model te werken, dat eenvoudiger is en minder foutgevoelig.

Een van de grote voordelen van dynamische matrixformules is dat Je hoeft de formule maar één keer in te voeren, in de cel linksboven.De overige resultaten verschijnen automatisch dankzij de overloop. In oudere CSE-formules moest je het hele bereik selecteren waar je de resultaten wilde weergeven en vervolgens de formule bevestigen met Ctrl+Shift+Enter.

Dynamische formules kunnen hun grootte ook aanpassen wanneer de brongegevens veranderen. Als u meer rijen aan de gegevensbron toevoegt, kan de matrix groter worden; als u gegevens verwijdert, kan deze kleiner worden – allemaal zonder dat u het bereik handmatig hoeft te bewerken. Met oudere CSE-matrices, als het retourgebied te klein wasDe resultaten werden afgekapt; als ze te uitgebreid waren, konden er fouten zoals #N/A optreden.

Een ander interessant verschil is dat veel klassieke functies, zoals RAND, ROW of COLUMN, nu worden geëvalueerd in een context van één cel (1x1). Als u meerdere willekeurige resultaten of getallenreeksen wilt genereren op basis van rijen en kolommen, is het aan te raden functies zoals te gebruiken. willekeurige matrix of reeksdie ontworpen zijn om complete matrices terug te geven en perfect werken met de nieuwe dynamische engine.

Daarnaast bestond er in het verleden een fenomeen dat bekend stond als een "CSE-breuk", waarbij bepaalde verouderde matrixformules die van elkaar afhankelijk waren, onafhankelijk van elkaar konden worden berekend en resultaten konden opleveren. inconsistente of moeilijk te debuggen resultatenBij dynamische arrays verdwijnt dit gedrag: als er circulaire verwijzingen zijn, zal Excel deze als zodanig markeren in plaats van de logica van de formule te verstoren.

Het aanpassen van een dynamische matrixformule is ook handiger. Je hoeft alleen de broncel te bewerken en de rest wordt automatisch bijgewerkt; bij CSE-formules was dit nodig. Bewerk het gehele betreffende bereik in één keer.Dit werd behoorlijk omslachtig bij grote bereiken. Bovendien was het, wanneer een werkblad een actief bereik met een CSE-formule bevatte, niet mogelijk om rijen of kolommen in te voegen of te verwijderen die dat bereik overlapten, totdat de overgeërfde matrixformule eerst was verwijderd of gewijzigd.

Het gebruik van sleutelfuncties met dynamische arrays

Tot de krachtigste functies die gebruikmaken van dynamische arrays behoren: FILTEREN, SORTEREN, SORTEREN OP, UNIEK, WILLEKEURIGE ARRAY en VOLGORDEZe retourneren allemaal volledige bereiken, dus ze passen perfect bij het overloopgedrag, en er zijn handige hulpmiddelen zoals Excel Formule Bot die het gebruik ervan vergemakkelijken.

Met de FILTER-functie kunt u bijvoorbeeld alleen de rijen uit een gegevenstabel extraheren die aan bepaalde criteria voldoen. De resultaten worden automatisch bijgewerkt wanneer de brongegevens wijzigen. Deze functie is zeer goed te combineren met UNIQUE, waarmee het mogelijk is om... Verkrijg lijsten met waarden zonder duplicaten. uit kolommen met veel herhaalde gegevens.

De MATRIZALEAT-functie genereert een reeks willekeurige getallen in een blok, ideaal voor simulaties of tests; SECUENCIA daarentegen retourneert matrices met reeksen opeenvolgende getallen, waardoor u de stapgrootte en matrixgrootte naar behoefte kunt aanpassen. Beide functies zijn gebaseerd op het nieuwe matrixmodel en zijn ontworpen voor vul grote gedeelten van het vel in één keer..

Tot slot maken SORT en SORTBY het veel gemakkelijker om gesorteerde lijsten te maken van bestaande kolommen of tabellen. In plaats van handmatig sorteren of complexe combinaties van functies te gebruiken, kunt u nu één enkele formule schrijven die retourneert de gesorteerde gegevens en het past zich automatisch aan veranderingen in de oorspronkelijke waarden aan.

Al deze functies kunnen met elkaar worden gecombineerd en stellen u dankzij overloop in staat zeer geavanceerde oplossingen te bouwen zonder macro's of verouderde, moeilijk te onderhouden matrixformules, en uw werkmappen te automatiseren. Office-scripts in Excel Web Het kan een grote hulp zijn.

Verschillen in berekening tussen dynamische matrices en overgeërfde matrices

Als je nog steeds werkt met oudere boeken die CSE-matrixformules gebruiken, is het belangrijk om te onthouden dat je bij het converteren ervan naar hun eigen formaten rekening moet houden met de volgende instellingen. Een equivalente dynamiek kan het gedrag enigszins veranderen. In sommige gevallen wel. Hoewel de conversie in de meeste gevallen direct is, is het raadzaam het resultaat te controleren.

De gebruikelijke manier om een ​​verouderde matrix om te zetten naar een dynamische matrix is ​​door de eerste cel van het matrixbereik te zoeken, de formuletekst te kopiëren, het hele oude bereik te verwijderen en vervolgens Herschrijf de formule alleen in de cel linksboven. Met de nieuwe aanpak zal Excel automatisch omgaan met overlopende resultaten.

Besteed tijdens de overgang extra aandacht aan functies die voorheen afhankelijk waren van impliciet intersectiegedrag of speciale evaluaties binnen CSE-formules. Excel beschikt nu over de impliciete intersectie-operator (@)Dit dient om in bepaalde gevallen het oude gedrag te repliceren, maar de algemene aanbeveling blijft om de formules expliciet te herschrijven en daarbij gebruik te maken van de nieuwe functies.

Excel biedt ook beperkte ondersteuning wanneer een dynamische matrix verwijst naar gegevens in een andere werkmap. Een correcte werking is alleen gegarandeerd wanneer Beide bestanden zijn tegelijkertijd geopend.Als u het bronwerkblad sluit, kunnen gekoppelde dynamische matrixformules de foutmelding #REF! retourneren wanneer u probeert bij te werken. Het is daarom belangrijk om hier rekening mee te houden bij complexe modellen met meerdere externe verwijzingen; raadpleeg in dergelijke gevallen hoe u dit kunt oplossen. Werkmappen opslaan en delen Om problemen te voorkomen.

Hoewel CSE-formules voor achterwaartse compatibiliteit zullen blijven bestaan, raadt Microsoft zelf aan om ze niet langer in nieuwe projecten te gebruiken en in plaats daarvan dynamische arrays te gebruiken. Leesbaarheid, onderhoudsgemak en robuustheid Deze nieuwe formules maken ze de voorkeursoptie voor de toekomst.

Overloopbereik en praktische bewerking op het blad

Wanneer een formule overloopt, wordt het gebied dat deze inneemt het overloopbereik genoemd. In dit bereik bevat, zoals we al hebben vermeld, alleen de eerste cel de daadwerkelijke formule. De overige cellen tonen resultaten, maar deze worden "aangestuurd" vanuit de broncel, wat betekent dat De gehele matrix gedraagt ​​zich als één geheel..

Als je bijvoorbeeld de functie schrijft... =BOVEN(E7:E19) In één cel ziet u hoe Excel de tekst uit dat bereik over meerdere rijen heen omzet naar hoofdletters. Als u een van de cellen in het uitvoerbereik selecteert, ziet u het resultaat, maar wanneer u naar de formulebalk kijkt, ziet u dat de inhoud gedimd wordt weergegeven. Dit geeft aan dat de cel nog niet volledig is ingevuld. Het is niet direct bewerkbaar.Eventuele wijzigingen moeten worden aangebracht in de cel waar u de formule oorspronkelijk hebt ingevoerd.

Deze werkwijze heeft een duidelijk voordeel: het voorkomt dat u per ongeluk slechts een deel van het bereik wijzigt en de rest ongewijzigd laat, wat vaak leidt tot moeilijk te vinden fouten. Als u de formule moet wijzigen, hoeft u deze slechts één keer in de broncel te bewerken, op Enter te drukken en Excel werkt de formule automatisch bij. herberekent het hele blok consistent.

In de praktijk heeft dit ook gevolgen voor het verwijderen of verplaatsen van gegevens. Als u een enkele cel binnen het overloopbereik probeert te verwijderen, waarschuwt Excel u dat u een dynamische matrix beïnvloedt. Om problemen te voorkomen, is het meestal het beste om de hele cel te verwijderen of te knippen. rechtstreeks de broncel, die de rest van het bereik meesleept.Of pas de formule aan om een ​​matrix van een andere grootte te verkrijgen.

Houd er bij het combineren van overloopbereiken met andere formules rekening mee dat deze cellen samen opnieuw worden berekend. Verwijzingen naar dynamische arrays moeten zorgvuldig worden gedaan, waarbij optimaal gebruik wordt gemaakt van de nieuwe engine om onnodige circulaire verwijzingen of conflicten met andere delen van het werkblad te voorkomen.

Voorbeelden van geavanceerde matrixformules met echte gegevens.

Veel klassieke matrixformules zijn nog steeds zinvol, vooral bij het werken met grote bereiken wanneer je gegevens nodig hebt. complexe voorwaardelijke bewerkingenLaten we een aantal typische voorbeelden bekijken die u wellicht erg nuttig zult vinden in rapporten en modellen.

Stel je voor dat je een benoemd bereik hebt met de naam 'Data' dat enkele foutwaarden bevat, zoals #N/A. Als je de SUM-functie rechtstreeks op dat bereik toepast, krijg je een foutmelding. Om dit te voorkomen, kun je een matrix gebruiken die fouten negeert met een formule zoals deze: =SOM(ALS(ISERROR(Data);"";Data)), wat intern een array creëert waarin fouten worden vervangen door lege strings voordat ze worden opgeteld.

Volgens datzelfde idee kun je ook tellen hoeveel fouten er in een bereik zitten. Een formule zoals =SOM(ALS(ISERROR(Data);1;0)) Het genereert een array met 1 als er een fout is en 0 als er geen fout is. De totale som geeft het aantal foutieve cellen weer. Je kunt het zelfs vereenvoudigen tot =SOM(ALS(ISERROR(Data);1)) en ga nog een stap verder naar =SOM(ALS(ISFOUT(Gegevens)*1)), gebruikmakend van het feit dat TRUE*1 gelijk is aan 1 en FALSE*1 gelijk is aan 0.

Een ander veelvoorkomend scenario is het optellen van waarden op basis van voorwaarden. Je hebt bijvoorbeeld een bereik met de naam 'Verkoop' en wilt alleen de positieve waarden optellen met een formule zoals: =SOM(ALS(Omzet>0;Omzet))Hier genereert de IF-functie een array met de waarden die aan de voorwaarde voldoen en met onware waarden voor de rest, die de SUM-functie in de praktijk negeert.

Als u meerdere voorwaarden tegelijk wilt toepassen, kunt u logische expressies vermenigvuldigen om een ​​"EN"-bewerking te simuleren en ze vervolgens bij elkaar optellen. Een typisch voorbeeld hiervan is: =SOM((Verkoop>0)*(Verkoop<=5)*(Verkoop))Deze methode berekent de som van de verkopen die groter zijn dan 0 en kleiner dan of gelijk aan 5. Houd er echter rekening mee dat het bereik geen tekstcellen mag bevatten, anders kunnen er fouten optreden.

Om een ​​"OF"-bewerking na te bootsen, kunt u sommen van logische expressies gebruiken: bijvoorbeeld, =SOM(ALS((Verkoop<5)+(Verkoop>15);Verkoop))waarbij waarden kleiner dan 5 of groter dan 15 bij elkaar worden opgeteld. Op deze manier kunt u geavanceerde bewerkingen uitvoeren zonder direct gebruik te maken van de AND- en OR-functies, die een enkele logische waarde retourneren en niet een volledige reeks resultaten.

Statistische berekeningen en vergelijkingen met matrixformules

Matrixformules zijn ook erg handig voor Gemiddelden berekenen of vergelijkingen maken tussen volledige bereiken.Een klassiek voorbeeld is het berekenen van het gemiddelde van een dataset, waarbij nullen worden uitgesloten. Als uw bereik 'Verkoop' heet, is de formule =GEMIDDELDE(ALS(Verkoop<>0;Verkoop)) Het creëert een array met alleen de niet-nulwaarden en geeft deze door aan AVERAGE, waardoor records die geen informatie bevatten, worden weggelaten.

Een ander interessant voorbeeld is het vergelijken van twee bereiken van dezelfde grootte, bijvoorbeeld MyData en YourData. Als je wilt weten hoeveel cellen er tussen beide verschillen, kun je het volgende gebruiken: =SOM(ALS(MijnGegevens=JouwGegevens;0;1))Deze formule genereert een reeks nullen wanneer de waarden overeenkomen en enen wanneer ze verschillen, en telt vervolgens de resultaten op. Als de twee bereiken identiek zijn, geeft de formule 0 terug.

Deze vergelijking kan verder worden vereenvoudigd met =SOM(1*(MijnGegevens<>UwGegevens))waarbij de logische vergelijking (MyData<>YourData) TRUE of FALSE oplevert, die vervolgens door vermenigvuldiging met 1 worden omgezet in 1 en 0. Ook hier is de sleutel om optimaal gebruik te maken van het gedrag van booleaanse expressies binnen arrays.

Je kunt ook matrixformules gebruiken om de maximale waarde in een bereik te vinden en de positie ervan te bepalen. Als je een bereik met de naam 'Data' hebt, is een van de mogelijkheden: =MIN(ALS(Gegevens=MAX(Gegevens);RIJ(Gegevens);»»))Deze formule maakt een matrix aan waarin alleen de cellen met een waarde gelijk aan het maximum het bijbehorende rijnummer bevatten; de rest wordt omgezet in een lege tekenreeks. MIN retourneert vervolgens het kleinste rijnummer onder deze kandidaten, oftewel de eerste keer dat de maximale waarde voorkomt.

Als je in plaats van de rij de volledige celverwijzing wilt, kun je ADRES combineren met de bovenstaande logica met behulp van iets als: =ADRES(MIN(ALS(Gegevens=MAX(Gegevens);RIJ(Gegevens);»»));KOLOM(Gegevens))zodat je een referentie krijgt zoals "$B$7" die precies aangeeft waar de maximale waarde zich in het bereik bevindt.

Praktisch voorbeeld: verkoop per product met behulp van matrixformules

Om het potentieel van matrixformules beter te begrijpen, kunt u zich een kleine tabel met autoverkopen voorstellen, met kolommen voor de verkoper, voertuigtype, verkochte eenheden, eenheidsprijs en totale omzetNeem aan dat kolom C en kolom D respectievelijk het aantal verkochte exemplaren en de prijs per stuk bevatten.

Als u deze tabel naar Excel kopieert, kunt u het bereik E2:E11 selecteren en een formule invoeren zoals =C2:C11*D2:D11In oudere versies moest je de formule bevestigen met Ctrl+Shift+Enter om deze om te zetten in een klassieke matrix die de vermenigvuldiging per rij in één keer zou uitvoeren. Excel zou vervolgens de totale omzet voor elke rij berekenen door het aantal eenheden te vermenigvuldigen met de eenheidsprijs.

Het belangrijkste detail is dat je altijd moet kiezen alle cellen die de resultaten zullen bevatten Voordat je de matrixformule schrijft, is het belangrijk om dit te doen. Als je dit niet doet, worden mogelijk slechts enkele waarden berekend, of moet je het proces meerdere keren herhalen, wat inefficiënt is.

In deze context is het ook gebruikelijk om een ​​matrixformule voor één cel te gebruiken om het totaal van alle verkopen te berekenen. Je kunt bijvoorbeeld naar cel B13 gaan en het volgende invoeren: =SOM(C2:C11*D2:D11)Wanneer je de formule bevestigt (in het klassieke model met Ctrl+Shift+Enter), vermenigvuldigt Excel elk paar waarden in C en D en telt alle producten bij elkaar op. retourneer een enkele geaggregeerde waarde.

Hoewel deze voorbeelden gebruikmaken van het inherente gedrag van CSE-arrays, zijn de onderliggende ideeën hetzelfde als die gebruikt worden bij de huidige dynamische arrays: bewerkingen uitvoeren op hele bereiken tegelijk en meerdere of geaggregeerde resultaten retourneren zonder dat er tussenliggende hulpformules in elke rij nodig zijn.

Tegenwoordig kunnen veel van deze taken worden opgelost door dynamische functies en overloop te combineren, waardoor spreadsheets mogelijk worden. schoner, gemakkelijker te lezen en eenvoudiger te onderhoudenvooral wanneer de hoeveelheid gegevens toeneemt of wanneer meerdere gebruikers aan hetzelfde boek werken.

Het beheersen van dynamische matrixformules in Excel verandert de manier waarop u spreadsheets bouwt volledig: door te begrijpen hoe overloop werkt, hoe uitvoerbereiken zich gedragen, hoe ze verschillen van oude CSE-formules en hoe u praktische voorbeelden kunt toepassen om fouten te negeren, voorwaardelijke sommen uit te voeren of bereiken te vergelijken, werkt u uiteindelijk met veel flexibelere, geautomatiseerde en betrouwbare modellen. Dit bespaart tijd bij elke update en minimaliseert handmatige fouten.

Isaac

Gepassioneerd schrijver over de wereld van bytes en technologie in het algemeen. Ik deel mijn kennis graag door te schrijven, en dat is wat ik in deze blog ga doen: je de meest interessante dingen laten zien over gadgets, software, hardware, technologische trends en meer. Mijn doel is om u te helpen op een eenvoudige en onderhoudende manier door de digitale wereld te navigeren.