Matrixformules - richtlijnen en voorbeelden

Matrixformules - richtlijnen en voorbeelden

Als u een gevorderde gebruiker van Excel wilt worden, moet u weten hoe u matrixformules kunt gebruiken, waarmee u berekeningen kunt uitvoeren die met andere formules niet mogelijk zijn. Het volgende artikel is gebaseerd op een serie columns door Colin Wilcox voor ervaren Excel-gebruikers, op basis van hoofdstuk 14 en 15 van Excel 2002 Formulas, een boek van Excel-MVP John Walkenbach.

Informatie over matrixformules

Matrixformules worden ook wel 'CSE-formules' genoemd omdat u, in plaats van op Enter, op Ctrl+Shift+Enter moet drukken om deze te voltooien.

Het nut van matrixformules

Als u ervaring hebt met het gebruik van formules in Excel, weet u dat u hiermee een aantal redelijk ingewikkelde bewerkingen kunt uitvoeren. Zo kunt u de totale kosten van een lening met een looptijd van een willekeurig aantal jaren berekenen. Met matrixformules kunt u onder meer de volgende complexe taken uitvoeren:

  • Het aantal tekens in een celbereik tellen

  • Alleen getallen optellen die aan bepaalde voorwaarden voldoen, zoals de laagste waarden in een bereik van getallen of getallen die tussen een boven- en ondergrens vallen

  • Elke nde waarde in een bereik van waarden optellen

Matrices en matrixformules in vogelvlucht

Een matrixformule is een formule waarmee u meerdere berekeningen kunt uitvoeren voor een of meer items in een matrix. U kunt bij een matrix denken aan een rij of een kolom met waarden of aan een combinatie van rijen en kolommen met waarden. U kunt bijvoorbeeld een matrixformule maken in een celbereik en de matrixformule gebruiken om subtotalen in een kolom of rij te berekenen. Daarnaast kunt u een matrixformule opnemen in één cel en vervolgens één bedrag berekenen. Een matrixformule die meerdere cellen omvat, heet een formule in meerdere cellen en een matrixformule die één cel omvat, heet een formule in één cel.

De voorbeelden in het volgende gedeelte laten u zien hoe u matrixformules in meerdere cellen en in één cel kunt maken.

Probeer het zelf

In deze oefening wordt beschreven hoe u matrixformules in meerdere cellen en in één cel kunt gebruiken om een set verkoopcijfers te berekenen. Voor de eerste set wordt met een formule in meerdere cellen een set met subtotalen berekend. Voor de tweede set wordt met een formule in één cel een eindtotaal berekend.

Een matrixformule in meerdere cellen

Hier ziet u een werkmap die is ingesloten in de browser. Hoewel deze voorbeeldgegevens bevat, dient u zich te realiseren dat u geen matrixformules kunt maken of wijzigen in een ingesloten werkmap. U hebt het volledige Excel-programma nodig. U kunt de antwoorden zien in de ingesloten werkmap, alsmede tekst waarin wordt uitgelegd hoe de formule werkt, maar als u matrixformules echt wilt leren kennen, moet u de werkmap in Excel weergeven.

Een matrixformule in meerdere cellen maken

  1. Kopieer de hele onderstaande tabel en plak deze in cel A1 van een leeg werkblad in Excel.

    Verkoper

    Type
    auto

    Aantal
    verkocht

    Prijs
    per stuk

    Totaal-
    bedrag

    Barnhill

    Sedan

    5

    33000

    Coupé

    4

    37000

    Ingle

    Sedan

    6

    24000

    Coupé

    8

    21000

    Jordan

    Sedan

    3

    29000

    Coupé

    1

    31000

    Pica

    Sedan

    9

    24000

    Coupé

    5

    37000

    Sanchez

    Sedan

    6

    33000

    Coupé

    8

    31000

    Formule (eindtotaal)

    Eindtotaal

    '=SOM(C2:C11*D2:D11)

    =SOM(C2:C11*D2:D11)

  2. Als u het totaalbedrag van coupés en sedans voor elke verkoper wilt zien, selecteert u E2:E11, voert u de formule =C2:C11*D2:D11 in en drukt u op Ctrl+Shift+Enter.

  3. Voor het eindtotaal van de gehele verkoop selecteert u cel F11, voert u de formule =SOM(C2:C11*D2:D11) in en drukt u op Ctrl+Shift+Enter.

U kunt deze werkmap downloaden door op de groene Excel-knop in de zwarte balk onderaan de werkmap te klikken. Vervolgens kunt het bestand openen in Excel, de cellen met de matrixformules selecteren en op Ctrl+Shift+Enter drukken om de formule uit te voeren.

Als u in Excel werkt, zorg er dan voor dat Blad1 actief is en selecteer vervolgens de cellen E2:E11. Druk op F2 en typ de formule =C2:C11*D2:D11 in de huidige cel E2. Als u op Enter drukt, ziet u dat de formule alleen in cel E2 wordt ingevoerd en dat de waarde 165000 wordt weergegeven. Druk, na het typen van de formule, nu echter op Ctrl+Shift+Enter in plaats van alleen op Enter. Nu ziet u de resultaten in E2:E11. Zoals u ziet, wordt in de formulebalk de formule nu ook weergegeven als {=C2:C11*D2:D11}. Hieraan kunt u zien dat het een matrixformule betreft, zoals in de volgende tabel wordt weergegeven.

Wanneer u op Ctrl+Shift+Enter drukt, wordt de formule tussen accolades ({ }) geplaatst en wordt in elke cel van het geselecteerde bereik een exemplaar van de formule opgenomen. Dit gaat heel snel, dus in kolom E ziet u het totale verkoopbedrag per type auto per verkoper. Als u E2 selecteert en vervolgens E3, E4, enzovoort, zie u dat steeds dezelfde formule wordt weergegeven: {=C2:C11*D2:D11}.

De totalen in kolom E worden berekend met een matrixformule

Een matrixformule in één cel maken

Typ in cel F10 van de werkmap de volgende formule en druk vervolgens op Ctrl+Shift+Enter:

=SOM(C2:C11*D2:D11)

In dit geval worden de waarden in de matrix (het celbereik C2 tot en met D11) vermenigvuldigd en worden de totalen vervolgens bij elkaar opgeteld met de functie SOM. Het resultaat is een eindtotaal van € 1.590.000. Aan dit voorbeeld kunt u zien hoe krachtig dit soort formules kunnen zijn. Stel dat u 1.000 rijen met gegevens hebt. U kunt al deze gegevens of een gedeelte ervan bij elkaar optellen door een matrixformule in één cel te maken. In plaats van de formule door 1.000 rijen omlaag te slepen.

Zoals u ook ziet, is de formule in één cel (in cel G11) volledig onafhankelijk van de formule in meerdere cellen (de formule in de cellen E2 tot en met E11). Dit is een extra voordeel van het gebruik van matrixformule: flexibiliteit. U kunt de formules in kolom E wijzigen of de kolom in zijn geheel verwijderen, zonder dat dit van invloed is op de formule in cel G11.

Matrixformules bieden bovendien de volgende voordelen:

  • Consistentie    Als u op een van de cellen vanaf E2 of lager klikt, ziet u steeds dezelfde formule. Dankzij deze consistentie kunt u een grotere nauwkeurigheid waarborgen.

  • Veiligheid    U kunt de onderdelen van een matrixformule in meerdere cellen niet overschrijven. Klik bijvoorbeeld op cel E3 en druk op Delete. U moet het hele celbereik (E2 tot en met E11) selecteren en de formule voor de hele matrix wijzigen of de matrix zo laten. Bovendien moet u op Ctrl+Shift+Enter drukken om wijzigingen in de formule te bevestigen, waardoor de veiligheid verder wordt vergroot.

  • Geringere bestandsgrootte    Vaak kunt u in plaats van meerdere tussenliggende formules één matrixformule gebruiken. Zo wordt in de werkmap één matrixformule gebruikt om de resultaten in kolom E te berekenen. Als u standaardformules (zoals =C2*D2, C3*D3, C4*D4) had gebruikt, had u 11 verschillende formules moeten opgeven om dezelfde resultaten te verkrijgen.

De syntaxis van matrixformules

In het algemeen worden in matrixformules een standaardsyntaxis gebruikt. Alle matrixformules beginnen met een gelijkteken en u kunt de meeste ingebouwde Excel-functies gebruiken in matrixformules. Het voornaamste verschil is dat u bij een matrixformule op Ctrl+Shift+Enter drukt om deze in te voeren. Wanneer u dat doet, wordt de matrixformule in Excel tussen accolades geplaatst. Als u de accolades handmatig typt, wordt de formule omgezet in een tekenreeks en is deze niet bruikbaar.

Matrixfuncties zijn een erg efficiënte manier om complexe formules te maken. De matrixformule =SOM(C2:C11*D2:D11) is dezelfde als deze: =SOM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Matrixformules invoeren en wijzigen

Belangrijk    Druk op Ctrl+Shift+Enter wanneer u een matrixformule wilt invoeren of bewerken. Dit geldt voor zowel formules in één cel als formules in meerdere cellen.

Daarnaast moet u de volgende regels onthouden wanneer u met formules in meerdere cellen werkt:

  • Selecteer het bereik van cellen waarin de resultaten moeten worden opgenomen, voordat u de formule opgeeft. U hebt dit gedaan toen u de matrixformules in meerdere cellen hebt gemaakt, toen u de cellen E2 tot en met E11 selecteerde.

  • U kunt de inhoud van een afzonderlijke cel in een matrixformule niet wijzigen. Probeer dit uit door cel E3 in de werkmap te selecteren en op Delete te drukken. Er wordt een melding weergegeven met de boodschap dat u geen onderdeel van een matrix kunt wijzigen.

  • U kunt een matrixformule alleen in zijn geheel verplaatsen of verwijderen. Als u een matrixformule wilt verkleinen, moet u dus eerst de bestaande formule verwijderen en opnieuw beginnen.

  • U kunt een matrixformule verwijderen door de hele formule (bijvoorbeeld =C2:C11*D2:D11) te selecteren achtereenvolgens op Delete en op Ctrl+Shift+Enter te drukken.

  • U kunt geen lege cellen in een matrixformule in meerdere cellen invoegen of verwijderen.

Een matrixformule uitbreiden

Het kan voorkomen dat u een matrixformule moet uitbreiden. Dat is niet ingewikkeld, maar u moet u daarbij houden aan de regels die in het voorgaande richtlijnen worden beschreven.

Op dit werkblad hebben we extra verkoopgegevens geplaatst in de rijen 12 tot en met 17. Hier willen we de matrixformules bijwerken, zodat ze deze extra rijen bevatten.

U moet dit doen in het Excel-bureaubladprogramma (nadat u de werkmap naar de computer hebt gedownload).

Een matrixformule uitbreiden

  1. Kopieer de volledige tabel naar cel A1 in een Excel-werkblad.

    Verkoper

    Type
    auto

    Aantal
    verkocht

    Prijs
    per stuk

    Totaal-
    bedrag

    Barnhill

    Sedan

    5

    33000

    165000

    Coupé

    4

    37000

    148000

    Ingle

    Sedan

    6

    24000

    144000

    Coupé

    8

    21000

    168000

    Jordan

    Sedan

    3

    29000

    87000

    Coupé

    1

    31000

    31000

    Pica

    Sedan

    9

    24000

    216000

    Coupé

    5

    37000

    185000

    Sanchez

    Sedan

    6

    33000

    198000

    Coupé

    8

    31000

    248000

    Toth

    Sedan

    2

    27000

    Coupé

    3

    30000

    Wang

    Sedan

    4

    22000

    Coupé

    1

    41000

    Young

    Sedan

    5

    32000

    Coupé

    3

    36000

    Eindtotaal

  2. Selecteer cel E18, voer de formule voor het eindtotaal, =SOM(C2:C17*D2:D17), in cel A20 in en druk op Ctrl+Shift+Enter.
    Het resultaat moet 2.131.000 zijn.

  3. Selecteer het celbereik met de huidige matrixformule (E2:E11), plus de lege cellen (E12:E17) naast de nieuwe gegevens. Met andere woorden: selecteer de cellen E2:E17.

  4. Druk op F2 om de bewerkingsmodus te activeren.

  5. Wijzig C11 op de formulebalk in C17, wijzig D11 in D17 en druk op Ctrl+Shift+Enter.
    De formule in de cellen E2 tot en met E11 wordt bijgewerkt en een exemplaar van de formule wordt opgenomen in de nieuwe cellen, E12 tot en met E17.

  6. Typ de matrixformule = SOM(C2:C17*D2*D17) in cel F17, zodat deze verwijst naar de cellen van rij 2 tot en met rij 17, en druk op Ctrl+Shift+Enter om de matrixformule in te voeren.
    Het nieuwe eindtotaal moet nu 2.131.000 zijn.

Nadelen van het gebruik van matrixformules

Matrixformules bieden grote voordelen, maar hebben ook een paar nadelen:

  • Soms vergeet u op Ctrl+Shift+Enter te drukken. Dit kan zelfs ervaren Excel-gebruikers overkomen. Denk eraan dat u deze toetscombinatie moet gebruiken wanneer u een matrixformule wilt invoeren of bewerken.

  • Het is mogelijk dat andere gebruikers van de werkmap uw formules niet begrijpen. In de praktijk is er meestal weinig uitleg over matrixformules beschikbaar in een werkblad. Als anderen uw werkmappen moeten aanpassen, kunt u beter geen matrixformules gebruiken of ervoor zorgen dat deze personen begrijpen wat matrixformules zijn en hoe ze uw formules kunnen wijzigen, als dat nodig is.

  • Afhankelijk van de verwerkingssnelheid en het geheugen van uw computer kan het gebruik van grote matrixformules veel rekenkracht vergen en de prestaties nadelig beïnvloeden.

Naar boven

Informatie over matrixconstanten

Matrixconstanten zijn onderdeel van matrixformules. U maakt matrixconstanten door een lijst met items in te voeren en deze vervolgens handmatig tussen accolades ({ }) te plaatsen, bijvoorbeeld:

={1,2,3,4,5}

Nu weet u hoe belangrijk het is op Ctrl+Shift+Enter te drukken wanneer u matrixformules maakt. Aangezien matrixconstanten onderdeel zijn van matrixformules, plaatst u de constanten handmatig tussen accolades door deze te typen. Vervolgens drukt u op Ctrl+Shift+Enter om de hele formule in te voeren.

Als u de items met komma's van elkaar scheidt, maakt u een horizontale matrix (een rij). Als u de items met puntkomma's van elkaar scheidt, maakt u een verticale matrix (een kolom). Als u een tweedimensionale matrix wilt maken, scheidt u de items in elke rij met komma's en scheidt u de afzonderlijke rijen met puntkomma's.

Dit is een matrix in één rij: {1,2,3,4}. Dit is een matrix in één kolom: {1;2;3;4}. En dit is een matrix van twee rijen en vier kolommen: {1,2,3,4;5,6,7,8}. In de matrix met twee rijen, staat in de eerste rij 1, 2, 3 en 4 en in de tweede rij 5, 6, 7 en 8. Eén puntkomma, tussen 4 en 5, scheidt de twee rijen.

Net als matrixformules kunt u matrixconstanten gebruiken met de meeste ingebouwde functies van Excel. In de volgende gedeelten wordt uitgelegd hoe u de verschillende soorten constanten maakt en hoe u deze kunt gebruiken in combinatie met functies in Excel.

Naar boven

Eendimensionale en tweedimensionale constanten maken

Aan de hand van de volgende procedures kunt u oefenen met het maken van horizontale, verticale en tweedimensionale constanten.

Een horizontale constante maken

  1. Gebruik de werkmap uit de voorgaande column of maak een nieuwe werkmap.

  2. Selecteer de cellen A1 tot en met E1.

  3. Typ de volgende formule op de formulebalk en druk op Ctrl+Shift+Enter:

    ={1,2,3,4,5}

    In dit geval moet u de accolade-openen en de accolade-sluiten ({ }) typen.

    De volgende informatie wordt weergegeven.

    Horizontale matrixconstante in formule

Een verticale constante maken

  1. Selecteer een kolom met vijf cellen in de werkmap.

  2. Typ de volgende formule op de formulebalk en druk op Ctrl+Shift+Enter:

    ={1;2;3;4;5}

    De volgende informatie wordt weergegeven.

    Verticale matrixconstante in een matrixformule

Een tweedimensionale constante maken

  1. Selecteer in de werkmap een blok cellen van vier kolommen breed en drie rijen hoog.

  2. Typ de volgende formule op de formulebalk en druk op Ctrl+Shift+Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}

    U ziet het volgende resultaat:

    Tweedimensionale matrixconstante in matrixformule

Constanten gebruiken in formules

Hier volgt een eenvoudig voorbeeld met constanten:

  1. Maak in de voorbeeldwerkmap een nieuw werkblad.

  2. Typ 3 in cel A1 en typ vervolgens 4 in cel B1, 5 in cel C1, 6 in cel D1 en 7 in cel E1.

  3. Typ de volgende formule in cel A3 en druk op Ctrl+Shift+Enter:

    =SOM(A1:E1*{1,2,3,4,5})

    Zoals u ziet, wordt de constante in Excel tussen een extra stel accolades geplaatst omdat u deze als matrixformule hebt ingevoerd.

    Matrixformule met matrixconstante

    De waarde 85 wordt weergegeven in cel A3.

In het volgende gedeelte wordt uitgelegd hoe de formule werkt.

De syntaxis van een matrixconstante

De formule die u zojuist hebt gebruikt, bestaat uit meerdere onderdelen.

Syntaxis van matrixformule met matrixformule

1. Functie

2. Opgeslagen matrix

3. Operator

4. Matrixconstante

Het laatste element tussen de haakjes is de matrixconstante: {1,2,3,4,5}. Houd er rekening mee dat in Excel niet automatisch accolades worden geplaatst rond matrixconstanten, deze moet u zelf typen. Houd er ook rekening mee dat u, nadat u een constante hebt toegevoegd aan een matrixformule, op Ctrl+Shift+Enter moet drukken om de formule in te voeren.

Omdat in Excel eerst de bewerkingen worden uitgevoerd voor expressies die tussen haakjes zijn geplaatst, komen vervolgens de waarden in de werkmap (A1:E1) en de operator aan de beurt. Op dit punt worden de waarden in de opgeslagen matrix door middel van de formule vermenigvuldigd met de overeenkomende waarden in de constante. Dat komt neer op het volgende:

=SOM(A1*1,B1*2,C1*3,D1*4,E1*5)

Tot slot worden de waarden met de functie SOM bij elkaar opgeteld en wordt de uitkomst 85 weergegeven in cel A3.

Als u de opgeslagen matrix niet wilt gebruiken en de bewerking helemaal in het geheugen wilt uitvoeren, vervangt u de opgeslagen matrix door een andere matrixconstante:

=SOM({3,4,5,6,7}*{1,2,3,4,5})

U kunt dit proberen door de functie te kopiëren, een lege cel in de werkmap te selecteren, de formule op de formulebalk te plakken en op Ctrl+Shift+Enter te drukken. U ziet hetzelfde resultaat als bij de eerdere oefening met de matrixformule:

=SOM(A1:E1*{1,2,3,4,5})

Elementen die u in constanten kunt gebruiken

Matrixconstanten kunnen getallen, tekst, logische waarden (zoals WAAR en ONWAAR) en foutwaarden (zoals #N/B) bevatten. U kunt getallen gebruiken in de volgende notaties: integer, decimaal en wetenschappelijk. Als u tekst opneemt, moet u deze tussen aanhalingstekens plaatsen (").

Matrixconstanten mogen geen andere matrices, formules of functies bevatten. Met andere woorden: ze mogen alleen tekst of getallen bevatten die van elkaar worden gescheiden met komma's of puntkomma's. Er verschijnt een waarschuwingsbericht wanneer u een formule als {1,2,A1:D4} of {1,2,SOM(Q2:Z8)} invoert. Daarnaast mogen numerieke waarden geen procenttekens, dollartekens, komma's of haakjes bevatten.

Namen toewijzen aan matrixconstanten

Een van de beste manieren om matrixconstanten te gebruiken, is door deze een naam te geven. Benoemde constanten laten zich veel makkelijker hanteren en maken de complexiteit van matrixformules tot op zekere hoogte onzichtbaar voor anderen. U kunt een matrixconstante als volgt een naam geven en in een formule gebruiken:

  1. Ga naar het tabblad Formules en klik in de groep Gedefinieerde namen op Naam bepalen.
    Het dialoogvenster Naam bepalen wordt weergegeven.

  2. Typ in het vak Naam de naam Kwartaal1.

  3. Typ in het vak Verwijst naar de volgende constante (denk eraan dat u de accolades handmatig moet typen):

    ={"januari","februari","maart"}

    Het dialoogvenster ziet er nu zo uit:

    Dialoogvenster Naam bewerken met formule

  4. Klik op OK en selecteer vervolgens een rij met drie lege cellen.

  5. Typ de volgende formule en druk op Ctrl+Shift+Enter:

    =Kwartaal1

    Het volgende resultaat wordt weergegeven.

    Benoemde matrix die als formule is ingevoerd

Wanneer u een benoemde constante gebruikt als matrixformule, moet u niet vergeten het gelijkteken op te geven. Als u dat niet doet, wordt de matrix beschouwd als tekenreeks en functioneert de formule niet zoals verwacht. Tot slot nog dit: u kunt combinaties van tekst en getallen gebruiken.

Problemen met matrixconstanten oplossen

Wanneer de opgegeven matrixconstanten niet de verwachte resultaten opleveren, kan dat de volgende oorzaken hebben:

  • Mogelijk worden niet alle elementen met het juiste teken van elkaar gescheiden. Als u een komma of puntkomma weglaat of een teken op de verkeerde plaats zet, wordt de matrixconstante mogelijk niet op de juiste manier wordt gemaakt of kan er een waarschuwingsbericht verschijnen.

  • Mogelijk hebt u een celbereik geselecteerd dat niet overeenkomt met het aantal elementen in de constante. Als u bijvoorbeeld een kolom van zes cellen selecteert voor gebruik met een constante voor vijf cellen, verschijnt de fout #N/B in de lege cel. Omgekeerd geldt dat, als u te weinig cellen selecteert, de waarden worden weggelaten waarvoor geen corresponderende cel is.

Matrixconstanten in actie

Aan de hand van de volgende voorbeelden worden een paar manieren gedemonstreerd waarop u matrixconstanten kunt toepassen in matrixformules. In sommige van de voorbeelden worden rijen omgezet in kolommen en andersom met behulp van de functie TRANSPONEREN.

Elk item in een matrix vermenigvuldigen

  1. Maak een nieuw werkblad en selecteer een blok lege cellen met een breedte van vier kolommen en een hoogte van drie rijen.

  2. Typ de volgende formule en druk op Ctrl+Shift+Enter.

    ={1,2,3,4;5,6,7,8;9,10,11,12}*2

De items in een matrix tot de tweede macht verheffen

  1. Selecteer een blok lege cellen met een breedte van vier kolommen en een hoogte van drie rijen.

  2. Typ de volgende matrixformule en druk op Ctrl+Shift+Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

    U kunt ook de volgende matrixformule invoeren, waarin de operator caret (^) wordt gebruikt:

    ={1,2,3,4;5,6,7,8;9,10,11,12}^2

Een eendimensionale rij transponeren

  1. Selecteer een kolom van vijf lege cellen.

  2. Typ de volgende formule en druk op Ctrl+Shift+Enter.

    =TRANSPONEREN({1,2,3,4,5})

Hoewel u een horizontale matrixconstante hebt ingevoerd, kunt u de matrixconstante met de functie TRANSPONEREN toch omzetten in een kolom.

Een eendimensionale kolom transponeren

  1. Selecteer een rij van vijf lege cellen.

  2. Typ de volgende formule en druk op Ctrl+Shift+Enter.

    =TRANSPONEREN({1;2;3;4;5})

Hoewel u een verticale matrixconstante hebt ingevoerd, kunt u de matrixconstante met de functie TRANSPONEREN toch omzetten in een rij.

Een tweedimensionale constante transponeren

  1. Selecteer een blok cellen met een breedte van drie kolommen en een hoogte van vier rijen.

  2. Typ de volgende constante en druk op Ctrl+Shift+Enter:

    =TRANSPONEREN({1,2,3,4;5,6,7,8;9,10,11,12})

Met de functie TRANSPONEREN zet u elke rij om in een reeks kolommen.

Naar boven

Eenvoudige matrixformules gebruiken

In dit gedeelte worden voorbeelden gegeven van eenvoudige matrixformules.

Matrices en matrixconstanten maken op basis van bestaande waarden

In het volgende voorbeeld wordt uitgelegd hoe u matrixformules kunt gebruiken om koppelingen in te stellen tussen celbereiken in verschillende werkbladen. Daarnaast wordt gedemonstreerd hoe u op basis van dezelfde set waarden een matrixconstante kunt maken.

Een matrix maken op basis van bestaande waarden

  1. Selecteer de cellen C8:E10 op een werkblad in Excel en voer deze formule in:

    ={10,20,30;40,50,60;70,80,90}

    Zorg dat u eerst { (linkeraccolade) typt voordat u 10 typt en } (rechteraccolade) nadat u 90 typt, omdat u een matrix van getallen aan het maken bent.

  2. Druk op Ctrl+Shift+Enter, waarmee u deze matrix van getallen in het celbereik C8: E10 invoert met behulp van een matrixformule.
    Op het werkblad ziet het celbereik C8 tot en met E10 er als volgt uit:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Selecteer het celbereik C1 tot en met E3.

  4. Typ de volgende formule in de formulebalk en druk op Ctrl+Shift+Enter:

    =C8:E10

    Er wordt een matrix van 3x3 cellen weergegeven in de cellen C1 tot en met E3 met dezelfde waarde als in C8 tot en met E10.

Een matrixconstante maken op basis van bestaande waarden

  1. Terwijl de cellen C1:C3 zijn geselecteerd, drukt u op F2 om over te schakelen naar de bewerkingsmodus.
    De matrixformule moet nog steeds = C8:E10 zijn.

  2. Druk op F9 om de celverwijzingen om te zetten in waarden. De waarden worden omgezet in een matrixconstante. De formule moet nu ={10,20,30;40,50,60;70,80,90} zijn, net als in C8:E10.

  3. Druk op Ctrl+Shift+Enter als u de matrixconstante als matrixformule wilt invoeren.

Tekens tellen in een celbereik

In het volgende voorbeeld wordt gedemonstreerd hoe u het aantal tekens in een celbereik, spaties inbegrepen, kunt tellen.

  1. Kopieer deze hele tabel en plak deze in cel A1 in een werkblad.

  2. Selecteer cel A9 en druk op Ctrl+Shift+Enter om het totaal aantal tekens in de cellen A2:A6 (66) weer te geven.

  3. Selecteer cel A12 en druk op Ctrl+Shift+Enter om de inhoud van de langste cel in het bereik A2:A6 (cel A3) weer te geven.

Gegevens

Dit is een

reeks cellen

die samen

één zin

vormen.

Totaal aantal tekens in A2:A6

=SOM(LENGTE(A2:A6))

Inhoud van langste cel(A2)

=INDEX(A2:A6,VERGELIJKEN(MAX(LENGTE(A2:A6)),LENGTE(A2:A6),0),1)

De volgende formule wordt gebruikt in cel A9 om het totaal aantal tekens (45) in de cellen A2 tot en met A6 te tellen.

=SOM(LENGTE(A2:A6))

In dit geval wordt met de functie LENGTE de lengte van elke tekenreeks in de afzonderlijke cellen in het bereik opgehaald. Met de functie SOM worden deze waarden bij elkaar opgeteld en wordt het resultaat (45) weergegeven in de cel die de formule bevat, A9.

De n kleinste waarden in een bereik zoeken

In dit voorbeeld wordt gedemonstreerd hoe u de drie kleinste waarden kunt vinden in een bereik van cellen.

  1. Selecteer de cellen A16 tot en met A18.
    In deze cellen worden de resultaten opgeslagen die met de matrixformule worden opgehaald.

  2. Typ de volgende formule op de formulebalk en druk op Ctrl+Shift+Enter:

    =KLEINSTE(A5:A14,{1;2;3})

De waarden 400, 475 en 500 verschijnen in de cellen A16 tot en met A18.

In deze formule wordt een matrixconstante gebruik om de functie KLEINSTE driemaal te evalueren en de kleinste (1), de op een na kleinste (2) en de op twee na kleinste (3) leden op te halen uit de matrix in de cellen A1:A10. Als u meer waarden wilt vinden, voegt u meer argumenten aan de constante toe en breidt u het bereik A12:A14 uit met het overeenkomstige aantal resultaatcellen. U kunt deze formule ook combineren met aanvullende functies, zoals SOM of GEMIDDELDE. Voorbeeld:

=SOM(KLEINSTE(A 5 :A1 4 ,{1;2;3}))

=GEMIDDELDE(KLEINSTE(A 5 :A1 4 ,{1;2;3}))

De n grootste waarden in een bereik zoeken

Als u de grootste waarden in een bereik wilt vinden, kunt u de functie KLEINSTE vervangen door de functie GROOTSTE. Daarnaast worden in het volgende voorbeeld ook de functies RIJ en INDIRECT gebruikt.

  1. Selecteer de cellen A1 tot en met A3.

  2. Typ de deze formule op de formulebalk en druk op Ctrl+Shift+Enter:

    =GROOTSTE(A5:A14,RIJ(INDIRECT("1:3")))

De waarden 3200, 2700 en 2000 verschijnen in de cellen A12 tot en met A14.

Op dit punt kan het nuttig zijn iets meer te weten over de functies RIJ en INDIRECT. Met de functie RIJ kunt u een matrix van opeenvolgende gehele getallen samenstellen. Selecteer bijvoorbeeld een lege kolom van tien cellen in de oefenwerkmap, typ de volgende formule in de cellen A5:A14 en druk op Ctrl+Shift+Enter:

=RIJ(1:10)

Met deze formule wordt een kolom met tien opeenvolgende gehele getallen gemaakt. Als u een potentieel probleem wilt zien, voegt u een rij in boven het bereik met de matrixformule (boven rij 1 dus). In Excel worden de rijverwijzingen aangepast, waarop de formule gehele getallen van 2 tot 11 genereert. U kunt dat probleem verhelpen door de functie INDIRECT aan de formule toe te voegen:

=RIJ(INDIRECT("1:10"))

In de functie INDIRECT worden tekenreeksen gebruikt als argumenten. (Daarom wordt het bereik 1:10 tussen dubbele aanhalingstekens geplaatst.) In Excel worden tekstwaarden niet aangepast wanneer u rijen invoegt of de matrixformule verplaatst. Op die manier kunt u ervoor zorgen dat de functie RIJ altijd de gewenste matrix van gehele getallen genereert.

Laten we de formule die u eerder hebt gebruikt (=GROOTSTE(A5:A14,RIJ(INDIRECT("1:3")))) eens nader bekijken, te beginnen met de binnenste haakjes: met de functie INDIRECT wordt een set tekstwaarden geretourneerd, in dit geval de waarden 1 tot en met 3. Met de functie RIJ wordt een kolommatrix van drie cellen gegenereerd. De functie GROOTSTE gebruikt de waarden in het celbereik A5:A14 en wordt driemaal geëvalueerd, eenmaal voor elke verwijzing die wordt geretourneerd door de functie RIJ. De waarden 3200, 2700 en 2000 worden in de kolommatrix met drie cellen geplaatst. Als u meer waarden wilt vinden, breidt u het celbereik voor de functie INDIRECT uit.

Tot slot nog dit: u kunt deze formule gebruiken in combinatie met andere functies, zoals SOM en GEMIDDELDE.

De langste tekenreeks zoeken in een bereik van cellen

Deze formule werkt alleen wanneer een gegevensbereik één kolom met cellen bevat. Voer op Blad3 de volgende formule in cel A16 in en druk op Ctrl+Shift+Enter:

=INDEX(A6:A9,VERGELIJKEN(MAX(LENGTE(A6:A9)),LENGTE(A6:A9),0),1)

De tekst "groep cellen die" wordt weergegeven in cel A16.

Laten we de formule eens bekijken, te beginnen met de binnenste elementen. Met de functie LENGTE wordt de lengte van de afzonderlijke items in het celbereik A6:A9 opgehaald. Met de functie MAX wordt bepaald welke van deze items de grootste waarde bevat die overeenkomt met de cel met de langste tekenreeks, cel A7.

Hier wordt het wat ingewikkelder. Met de functie VERGELIJKEN wordt het verschil (de relatieve positie) bepaald ten opzichte van de cel met de langste tekstreeks. Hiervoor zijn drie argumenten nodig: een opzoekwaarde, een opzoekmatrix en een vergelijkingstype. Met de functie VERGELIJKEN wordt in de opzoekmatrix gezocht naar de opgegeven opzoekwaarde. In dit geval is de opzoekwaarde gelijk aan de langste tekenreeks:

(MAX(LENGTE( A6 : A9 ))

en die tekenreeks bevindt zich in de volgende matrix:

LENGTE( A6:A9 )

Het argument voor het vergelijkingstype is 0. Het vergelijkingstype kan bestaan uit de waarde 1, 0 of -1. Als u 1 opgeeft, wordt met VERGELIJKEN de grootste waarde opgehaald die kleiner is dan of gelijk is aan de opzoekwaarde. Als u 0 opgeeft, wordt met VERGELIJKEN de eerste waarde opgehaald die precies gelijk is aan de opzoekwaarde. Als u -1 opgeeft, wordt met VERGELIJKEN de kleinste waarde gevonden die groter is dan of gelijk is aan de opgegeven opzoekwaarde. Als u het vergelijkingstype weglaat, wordt aangenomen dat 1 wordt bedoeld.

Bij de functie INDEX worden de volgende argumenten opgegeven: een matrix, en een rij- en kolomnummer binnen die matrix. Het celbereik A6:A9 levert de matrix, met de functie VERGELIJKEN wordt het celadres bepaald en het laatste argument (1) geeft aan dat de waarde afkomstig is uit de eerste kolom in de matrix.

Naar boven

Geavanceerde matrixformules gebruiken

In dit gedeelte worden voorbeelden gegeven van geavanceerde matrixformules.

Een bereik met foutwaarden optellen

De functie SOM in Excel werkt niet wanneer u een bereik probeert op te tellen dat een foutwaarde bevat, zoals #N/B. In dit voorbeeld wordt gedemonstreerd hoe u de waarden in het bereik Gegevens kunt optellen als dit bereik fouten bevat.

=SOM(ALS(ISFOUT(Gegevens),"",Gegevens))

Met deze formule wordt een nieuwe matrix gemaakt die de oorspronkelijke waarden bevat, minus eventuele foutwaarden. Met de functie ISFOUT wordt het celbereik (Gegevens) doorzocht op fouten, te beginnen bij de binnenste functies. Met de functie ALS wordt een bepaalde waarde opgehaald als wordt voldaan aan de opgegeven voorwaarde (WAAR) en een andere waarde als niet wordt voldaan aan de opgegeven voorwaarde (ONWAAR). In dit geval worden er lege tekenreeksen ("") geretourneerd voor alle foutwaarden omdat wordt voldaan aan de opgegeven voorwaarde en worden de overige waarden in het bereik (Gegevens) geretourneerd omdat deze niet aan de opgegeven voorwaarde voldoen. Dat wil zeggen: deze cellen bevatten geen foutwaarden. Vervolgens wordt met de functie SOM het totaal voor de gefilterde matrix berekend.

Het aantal foutwaarden in een bereik tellen

De formule in dit voorbeeld lijkt op die in het vorige voorbeeld, maar met deze formule wordt het aantal foutwaarden in het bereik Gegevens opgehaald. Ze worden dus niet eruit gefilterd:

=SOM(ALS(ISFOUT(Gegevens),1,0))

Met deze formule wordt een matrix gemaakt die de waarde 1 bevat voor de cellen met foutwaarden en de waarde 0 voor de cellen die geen fouten bevatten. U kunt de formule vereenvoudigen en hetzelfde resultaat bereiken door het derde argument bij de functie ALS te verwijderen:

=SOM(ALS(ISFOUT(Gegevens),1))

Als u het argument niet opgeeft, is het resultaat van de functie ALS ONWAAR als een cel geen foutwaarde bevat. U kunt de formule nog verder vereenvoudigen:

=SOM(ALS(ISFOUT(Gegevens)*1))

Deze versie werkt omdat WAAR*1=1 en ONWAAR*1=0.

Waarden optellen op basis van voorwaarden

Het is mogelijk dat u waarden bij elkaar moet optellen afhankelijk van bepaalde voorwaarden. Met deze matrixformule worden alleen de positieve gehele getallen in het bereik Verkopen opgeteld:

=SOM(ALS(Verkopen>0,Verkopen))

Met de functie ALS wordt een matrix van positieve waarden en foutieve waarden samengesteld. De functie SOM negeert in wezen de foutieve waarden omdat 0+0=0. Het celbereik dat u in deze formule gebruikt, kan bestaan uit een willekeurig aantal rijen en kolommen.

U kunt ook waarden optellen die aan meerdere voorwaarden voldoen. Met deze matrixformule worden bijvoorbeeld waarden berekend die groter zijn dan 0 en kleiner dan of gelijk aan 5:

=SOM((Verkopen>0)*(Verkopen<=5)*(Verkopen))

Denk eraan dat deze formule een fout oplevert als het bereik een of meer niet-numerieke cellen bevat.

U kunt ook matrixformules maken met een OF-voorwaarde. Zo kunt u waarden optellen die kleiner dan 5 en groter dan 15 zijn:

=SOM(ALS((Verkopen<5)+(Verkopen>15),Verkopen))

Met de functie ALS kunt u alle waarden zoeken die kleiner dan 5 en groter dan 15 zijn, en deze waarden vervolgens doorgeven naar de functie SOM.

U kunt de functies EN en OF niet rechtstreeks in matrixformules gebruiken omdat deze functies één resultaat opleveren (WAAR of ONWAAR), terwijl voor matrixfuncties juist matrices van resultaten nodig zijn. U kunt dit probleem omzeilen door de logica te gebruiken die in de voorgaande formule wordt toegepast. Met andere woorden: u voert rekenkundige bewerkingen uit op waarden die voldoen aan de voorwaarde OF of EN, zoals optellen of vermenigvuldigen.

Een gemiddelde berekenen waarbij nullen worden genegeerd

In dit voorbeeld wordt gedemonstreerd hoe u nullen verwijdert uit een bereik wanneer u het gemiddelde van de waarden in dat bereik wilt berekenen. In deze formule wordt het gegevensbereik Verkopen gebruikt:

=GEMIDDELDE(ALS(Verkopen<>0,Verkopen))

Met de functie ALS wordt een matrix van waarden samengesteld die niet gelijk zijn aan 0, waarna deze waarden worden doorgegeven aan de functie GEMIDDELDE.

Het aantal verschillen tussen twee celbereiken tellen

Met deze matrixformule worden de waarden in twee celbereiken, MijnGegevens en JouwGegevens, vergeleken en wordt het aantal verschillen tussen beide bereiken geretourneerd. Als de inhoud van beide bereiken identiek is, is het resultaat van de formule 0. U kunt deze formule alleen gebruiken als de celbereiken even groot zijn en dezelfde dimensie hebben (bijvoorbeeld als MijnGegevens een bereik heeft van 3 rijen en 5 kolommen, moet JouwGegevens ook 3 rijen en 5 kolommen bevatten):

=SOM(ALS( MijnGegevens =JouwGegevens,0,1))

Met deze formule wordt een nieuwe matrix samengesteld die even groot is als de bereiken die u wilt vergelijken. Met de functie ALS wordt de matrix gevuld met de waarde 0 en de waarde 1 (0 voor niet-overeenkomende cellen en 1 voor identieke cellen). Vervolgens wordt met de functie SOM de som van de waarden in de matrix geretourneerd.

U kunt de formule als volgt vereenvoudigen:

=SOM(1*( MijnGegevens <> JouwGegevens ))

Net als de formule waarmee foutwaarden in een bereik worden geteld, werkt ook deze formule omdat WAAR*1=1, en ONWAAR*1=0.

De locatie van de maximumwaarde in een bereik zoeken

Met deze matrixformule wordt het rijnummer opgehaald van de maximumwaarde in het bereik met één kolom Gegevens:

=MIN(ALS(Gegevens=MAX(Gegevens),RIJ(Gegevens),""))

Met de functie ALS wordt een nieuwe matrix gemaakt die overeenkomt met het bereik Gegevens. Als een corresponderende cel de maximumwaarde in het bereik bevat, bevat de matrix het rijnummer van de cel. Anders bevat de matrix een lege tekenreeks (""). De functie MIN gebruikt de nieuwe matrix als tweede argument en retourneert de kleinste waarde. Deze komt overeen met het rijnummer van de cel met de maximumwaarde in Gegevens. Als het bereik Gegevens identieke maximumwaarden bevat, wordt met de formule de rij met de eerste waarde opgehaald.

Als u het celadres van een maximumwaarde wilt ophalen, gebruikt u de volgende formule:

=ADRES(MIN(ALS(Gegevens=MAX(Gegevens),RIJ(Gegevens),"")),KOLOM(Gegevens))

Naar boven

Meer hulp nodig?

U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community, ondersteuning vragen in de Answer-community of een nieuwe functie of verbetering voorstellen in Excel User Voice.

Zie ook

Overzicht van formules

Uw vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagents.

×