Werk je met Excel 2019 of nieuwer, of met Excel 365, dan kan je een speciale groep functies gebruiken, de zogeheten dynamische matrixfuncties. Het bijzondere hiervan is dat één formule een tabel met gegevens vult (een matrix). De functies van deze groep geven een matrix (tabel) als resultaat. Vanuit de cel met de formule worden aangrenzende cellen (naar rechts en omlaag) gevuld. Dit betekent dat Excel de juiste grootte van de matrix creëert als je op de Enter-toets drukt. Als je bij de functies FILTER, UNIEK en SORTEREN gegevens aan de basislijst toevoegt of eruit verwijdert, wordt de grootte van de matrix automatisch (dynamisch) aangepast.
Heb je een matrixformule van deze groep ingevoerd (die een tabel maakt) en klik je op een van de cellen in het nieuwe gebied, dan toont Excel een gekleurd kader om dat gebied. Dit kader verdwijnt als je klikt op een cel buiten dat gebied. Je kan alleen de eerste cel van de gemaakte matrix bewerken, dat wil zeggen: de cel linksboven in die matrix. Selecteer je een andere cel in die matrix, dan kan je de formule wel in de formulebalk zien, maar deze is vaag en kan niet worden veranderd. Wil je de formule aanpassen, klik dan op de cel linksboven in de matrix en breng de wijziging aan. Zodra je op de Enter-toets drukt, zal Excel de rest van de matrix automatisch vergroten of verkleinen.
Het vullen van de matrix wordt ‘overlopen’ genoemd. Als de formule bij het maken van de matrix op een gevulde cel stuit, wordt deze gehinderd en verschijnt de foutmelding #OVERLOPEN! (‘kan niet overlopen’, in het Engels: #SPILL!). Zorg ervoor dat er voldoende ruimte is om de benodigde cellen te kunnen vullen. Of klik op de gele ruit met het uitroepteken en kies Selecteer belemmerende cel; Excel selecteert dan de cel die in de weg zit. Maak die cel leeg om de weg vrij te maken.
• Deze melding verschijnt ook als op cellen waarin de gegevens moeten komen, Samenvoegen en centreren is toegepast. Maak dan de samenvoeging van de cellen in kwestie ongedaan of verplaats de formule naar een andere cel, zodat deze niet op de samengevoegde cellen stuit.
Een tabel met oplopende getallen maken
De functie REEKS maakt een tabel met opeenvolgende cijfers. De syntaxis van REEKS ziet er als volgt uit:
=REEKS(aantal rijen hoog; aantal kolommen breed; eventueel begingetal; eventueel stap)
Je geeft op hoeveel rijen hoog en hoeveel kolommen breed de matrix moet worden; desgewenst geef je het getal op waarmee je wilt beginnen; en eventueel hoe groot de stap naar het volgende getal moet zijn. Excel maakt een tabel in de opgegeven afmetingen, gevuld met oplopende cijfers. Deze cijfers beginnen standaard bij 1 en anders bij het getal dat je hebt opgegeven. Wil je bijvoorbeeld de reeks 1 tot en met 10 onder elkaar in B3 tot en met B12, dan plaats je in B3 de formule:
=REEKS(10;1)
Vanaf B3 worden er tien cellen onder elkaar gevuld; de breedte is één kolom. De getallen komen in de cellen B3 tot en met B12. Er is geen begingetal opgegeven en geen stap, dus de reeks begint met 1 en wordt steeds 1 groter. Wil je 20 cellen vanaf D2 onder elkaar vullen en drie kolommen naast elkaar en moeten de getallen oplopen vanaf 25, dan plaats je in D2 de formule:
=REEKS(20;3;25)
De cellen D2 tot en met F21 worden gevuld; de reeks begint bij 25 en omdat er geen stap is ingesteld, worden de cijfers vanaf 25 steeds één groter. De getallen worden horizontaal geplaatst: eerst worden D2, E2 en F2 gevuld, dan D3, E3, F3 enzovoort. Wil je onder elkaar een reeks van 15 even getallen van 10, 12, enzovoort, te beginnen in D1, dan plaats je in D1 de formule:
=REEKS(15;1;10;2)
Deze formule vult 15 cellen onder elkaar; de tabel is één kolom breed; de reeks begint bij 10; en de stap is 2. Je krijgt de even getallen van 10 tot en met 38 onder elkaar.
De functie REEKS maakt een tabel met oplopende getallen.
Een tabel met willekeurige getallen vullen
Heb je een lijst met willekeurige getallen nodig (bijvoorbeeld voor steekproeven), neem dan de functie ASELECT.MATRIX. De syntaxis van ASELECT.MATRIX ziet er als volgt uit:
ASELECT.MATRIX(aantal rijen hoog; aantal kolommen breed; kleinste getal; grootste getal; hele getallen of decimale waarden)
Geen van de argumenten is verplicht. Je geeft op hoeveel rijen hoog de tabel moet zijn of hoeveel kolommen breed, je mag een minimumwaarde opgeven en een maximumwaarde. Standaard geeft deze functie decimale getallen, maar als je als laatste argument WAAR opgeeft, krijg je hele getallen. Excel maakt een tabel in de opgegeven omvang, met (decimale) getallen binnen deze grenzen.
Als je geen aantal rijen en kolommen opgeeft, geen grenswaarden en niet dat je hele getallen wilt, verschijnt er één willekeurig getal tussen 0 en 1 in de cel, met de formule:
=ASELECT.MATRIX( )
Wil je een tabel van vijf cellen onder elkaar en drie cellen naast elkaar met willekeurige getallen tussen 0 en 1, dan is je formule:
=ASELECT.MATRIX(5;3)
Het volgende voorbeeld vult ook een tabel van vijf cellen hoog bij drie cellen breed, maar nu met een reeks willekeurige, decimale getallen tussen 1 en 100. De formule luidt:
=ASELECT.MATRIX(5;3;1;100)
De hoogte is 5 cellen, de breedte is 3 cellen, het kleinste getal is 1 en het grootste getal is 100 (en decimale waarden is de standaard). Wil je een tabel van dezelfde afmetingen vullen, maar nu met hele getallen, dan geef je dat aan het eind op met WAAR, als volgt.
=ASELECT.MATRIX(5;3;1;100;WAAR)
• Als je grenzen voor de getallen opgeeft, moet het eerste getal (het minimum) kleiner zijn dan het tweede getal (het maximum). Anders zal de foutmelding #WAARDE! verschijnen.
• Zodra je op de functietoets F9 drukt, worden er door de functie ASELECT.MATRIX andere getallen in de cellen geplaatst.
De functie ASELECT.MATRIX genereert een tabel met willekeurige getallen.
De 100+ beste tips & trucs voor Excel
Dit artikel is een hoofdstuk uit het boek De 100+ beste tips & trucs voor Excel van Wim de Groot, een uitgave van Van Duuren Media.
Excel is een echt werkpaard, maar het is niet het makkelijkste programma om onder de knie te krijgen. Als gevolg van de vele verborgen mogelijkheden loop je al gauw het risico dat je met eenvoudige en routineklussen langer bezig bent dan strikt noodzakelijk, of erger nog: het bijltje erbij neergooit. En dat is jammer, want Excel biedt héél veel kracht, voor elk type gebruiker.
In dit praktische boekje deelt de auteur meer dan 100 van zijn favoriete Excel-tips die hij in de afgelopen jaren heeft ontdekt. Aan de hand van veel voorbeelden worden zaken toegelicht die het werken met Excel sneller, efficiënter en leuker maken. De tips en trucs uit dit boek zijn ingedeeld in de volgende categorieën:
- Werken met werkbladen
- Gegevens invoeren
- Werkbladen vormgeven
- Werken met lijsten
- Formules opstellen
- Werken met datum en tijd
- Grafieken maken
- Werkbladen en cellen beveiligen
De 100+ beste tips & trucs voor Excel is onder meer verkrijgbaar bij Standaard Boekhandel, voor 27,99 euro.
