Matrixformules - richtlijnen en voorbeelden

Matrixformules - richtlijnen en voorbeelden

Een matrixformule is een formule waarmee meerdere berekeningen kunnen worden uitgevoerd op een of meer items in een matrix. U kunt een matrix beschouwen als een rij of kolom met waarden of een combinatie van rijen en kolommen met waarden. Matrixformules kunnen meerdere resultaten of één resultaat retourneren.

Vanaf de update van september 2018 voor Microsoft 365worden formules die meerdere resultaten retourneren, automatisch overlopen in aangrenzende cellen. Deze gedragswijziging gaat tevens vergezeld van een aantal nieuwe dynamische matrixfuncties. Of ze nu gebruikmaken van bestaande functies of van dynamische matrixfuncties, dynamische matrixformules hoeven slechts in één cel te worden ingevoerd en bevestigd door op Enter te drukken. Bij oudere matrixformules moest vroeger eerst het hele uitvoerbereik worden geselecteerd, waarna de formule moest worden bevestigd met Ctrl+Shift+Enter. Ze worden gewoonlijk aangeduid met CSE-formules.

Met matrixformules kunt u complexe taken uitvoeren, zoals:

  • Snel voorbeelden van gegevenssets maken.

  • Het aantal tekens in een celbereik tellen.

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

  • Elke n-de waarde in een bereik van waarden optellen.

De volgende voorbeelden laten zien hoe u matrixformules in meerdere cellen of in één cel kunt maken. Voor zover mogelijk zijn er voorbeelden opgenomen met een aantal dynamische matrixformules en met bestaande matrixformules die zowel als dynamische als verouderde matrices zijn ingevoerd.

Download onze voorbeelden

Een voorbeeldwerkmap downloaden met alle voorbeelden van matrixformules uit dit artikel.

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

    Matrixfunctie in meerdere cellen in cel H10 =F10:F19*G10:G19 om het aantal verkochte auto's per eenheidsprijs te berekenen

  • Hier wordt voor elke verkoper de totaalverkoop van coupés en sedans berekend door =F10:F19*G10:G19 in cel H10 in te voeren.

    Wanneer u op Enter drukt, ziet u dat de resultaten overlopen in de cellen H10:H19. Wanneer u een cel binnen het overloopbereik selecteert, wordt het bereik gemarkeerd met een rand. U kunt ook zien dat de formules in de cellen H10:H19 grijs worden weergegeven. Deze staan er slechts als referentie, dus als u de formule wilt aanpassen, moet u cel H10 selecteren. Hierin bevindt zich namelijk de hoofdformule.

  • Een matrixformule in één cel

    Matrixformule in één cel voor het berekenen van een eindtotaal met =SOM(F10:F19*G10:G19)

    Typ of kopieer =SUM(F10:F19*G10:G19) in cel H20 van de voorbeeldwerkmap en druk op Enter.

    In dit geval worden de waarden in de matrix (het celbereik van F10 tot en met F19) vermenigvuldigd en wordt de SOM-functie gebruikt om de totalen bij elkaar op te tellen. Het verkoopresultaat heeft als eindtotaal een bedrag van €1.590.000.

    Dit voorbeeld laat zien hoe krachtig dit type formule kan zijn. Stel dat u 1000 rijen met gegevens hebt. U kunt een deel of alle gegevens optellen door een matrixformule in één cel te maken in plaats van dat u de formule over de 1000 rijen naar beneden sleept. Zoals u ook ziet, is de formule in één cel (in cel H20) volledig onafhankelijk van de formule in meerdere cellen (de formule in de cellen H10 tot en met H19). Dit is een extra voordeel van het gebruik van matrixformule: flexibiliteit. U kunt de formules in kolom H wijzigen zonder dat dit van invloed is op de formule in cel H20. Het kan ook een goede gewoonte zijn onafhankelijke totalen zoals deze te hebben. Hiermee wordt namelijk de nauwkeurigheid van de resultaten gevalideerd.

  • Matrixformules bieden bovendien de volgende voordelen:

    • Consistentie    Als u op een van de cellen vanaf H10 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 H11 en druk op Delete. De uitvoer van de matrix wordt niet gewijzigd. U moet de bovenste linkercel in de matrix selecteren (cel H10) als u de uitvoer wilt wijzigen.

    • Kleinere bestanden    Vaak kunt u één matrixformule gebruiken in plaats van een aantal tussenliggende formules. Zo wordt in het voorbeeld van de autoverkoop één matrixformule gebruikt om de resultaten in kolom E te berekenen. Als u standaardformules (zoals =F10*G10, F11*G11, F12*G12, enzovoort) had gebruikt, had u 11 verschillende formules moeten opgeven om dezelfde resultaten te verkrijgen. Dat is nog niet zo erg, maar stel dat u in totaal duizenden rijen had? Dat kan een heel verschil maken.

    • Efficiëntie    Matrixfuncties kunnen een efficiënte manier zijn om complexe formules te maken. De matrixformule =SOM(F10:F19*G10:G19) is dezelfde als deze: =SOM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Overlopen    Dynamische matrixformules lopen automatisch over in het uitvoerbereik. Wanneer uw brongegevens zich in een Excel-tabel bevinden, wordt de grootte van de dynamische matrixformule automatisch aangepast als u gegevens toevoegt of verwijdert.

    • Fout #OVERLOOP!    Door dynamische matrices is de fout #OVERLOOP! geïntroduceerd.Hiermee wordt aangegeven dat het beoogde overloopbereik om de een of andere reden is geblokkeerd. Als u de blokkade opheft, loopt de formule automatisch over.

Matrixconstanten maken deel uit 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} or ={"januari"\"februari"\"maart"}

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 door komma's en scheidt u de afzonderlijke rijen door puntkomma's.

Aan de hand van de volgende procedures kunt u oefenen met het maken van horizontale, verticale en tweedimensionale constanten. Er worden voorbeelden getoond van het gebruik van de functie REEKS voor het automatisch genereren van matrixconstanten, en van handmatig ingevoerde matrixconstanten.

  • Een horizontale constante maken

    Gebruik de werkmap uit de voorgaande column of maak een nieuwe werkmap. Selecteer een lege cel en voer de =REEKS(1,5) in. Met de functie REEKS wordt een matrix van één rij bij vijf kolommen gebouwd, dezelfde als ={1\2\3\4\5}. Het volgende resultaat wordt weergegeven:

    Een horizontale matrixconstante maken met =REEKS(1,5) of ={1,2,3,4,5}

  • Een verticale constante maken

    Selecteer een lege cel met ruimte eronder en voer =REEKS(5) of ={1;2;3;4;5} in. Het volgende resultaat wordt weergegeven:

    Een horizontale matrixconstante maken met =REEKS(5) of ={1;2;3;4;5}

  • Een tweedimensionale constante maken

    Selecteer een lege cel met ruimte rechts en onder de cel en voer een =REEKS(3,4) in. U ziet het volgende resultaat:

    Een matrixconstante van 3 bij 4 kolommen maken met =REEKS(3,4)

    U kunt ook de volgende gegevens invoeren: of ={1\2\3\4;5\6\7\8;9\10\11\12}, maar let goed op waar u puntkomma's versus komma's plaatst.

    Zoals u ziet, biedt de optie REEKS belangrijke voordelen boven het handmatig invoeren van de matrixconstanten. Dit bespaart u vooral tijd, maar het kan ook fouten voorkomen als gevolg van handmatige invoer. Het is ook gemakkelijker te lezen, vooral omdat de puntkomma's moeilijk te onderscheiden kunnen zijn van de komma's als scheidingsteken.

Hier ziet u een voorbeeld waarbij matrixconstanten worden gebruikt als onderdeel van een grotere formule. Ga in het voorbeeldwerkboek naar het blad Constante in een formule of maak een nieuw werkblad.

In cel D9 hebben we =REEKS(1,5,3,1) ingevoerd, maar u kunt ook 3, 4, 5, 6 en 7 invoeren in de cellen A9:H9. Er is niets speciaals aan die bepaalde selectie van getallen, we hebben ter onderscheiding gewoon iets anders dan 1-5 gekozen.

Voer in cel E11 =SOM(D9:H9*REEKS(1,5)) of =SOM(D9:H9*{1\2\3\4\5}) in. Als resultaat wordt 85 weergegeven.

Matrixconstanten gebruiken in formules. In dit voorbeeld hebben we =SOM(D9:H(*REEKS(1,5)) gebruikt.

Met de functie REEKS wordt het equivalent van de matrixconstante {1\2\3\4\5} gemaakt. Aangezien in Excel eerst de bewerkingen worden uitgevoerd voor expressies die tussen haakjes zijn geplaatst, komen vervolgens de celwaarden in D9:H9 en de vermenigvuldigingsoperator (*) 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(D9*1,E9*2,F9*3,G9*4,H9*5) of =SOM(3*1,4*2,5*3,6*4,7*5)

Tot slot worden de waarden met de functie SOM bij elkaar opgeteld en wordt als resultaat 85 weergegeven.

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

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

Elementen die u in matrixconstanten 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 ("tekst").

  • 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.

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:

Ga naar Formules > Gedefinieerde namen > Naam bepalen. Typ in het vak Naam de naam Kwartaal1. Typ in het vak Verwijst naar de volgende constante (denk eraan dat u de accolades handmatig moet typen):

={"januari"\"februari"\"maart"}

Als het goed is, ziet het dialoogvenster er nu zo uit:

Een benoemde matrixconstante uit Formules > Gedefinieerde namen > Naambeheer > Nieuw

Klik op OK, selecteer een rij met drie lege cellen en voer =Kwartaal1 in.

Het volgende resultaat wordt weergegeven:

Gebruik een benoemde matrixconstante in een formule, zoals =Kwartaal1, waarbij kwartaal1 is gedefinieerd als ={"Januari","Februari","Maart"}

Als u de resultaten verticaal in plaats van horizontaal wilt laten overlopen, kunt u =TRANSPONEREN(Kwartaal1) gebruiken.

Als u een lijst met twaalf maanden wilt weergeven, bijvoorbeeld bij het gebruik van een financiële expressie, kunt u er een baseren op het huidige jaar met de functie REEKS. Het handige van deze functie is dat, hoewel alleen de maand wordt weergegeven, er een geldige datum achter staat die u in andere berekeningen kunt gebruiken. U vindt deze voorbeelden in de werkbladen Benoemde matrixconstante en Snelle voorbeeldgegevensset in het voorbeeldwerkboek.

=TEKST(DATUM(JAAR(VANDAAG()),REEKS(1,12),1),"mmm")

Een combinatie van de functies TEKST, DATUM, JAAR, VANDAAG en REEKS gebruiken om een dynamische lijst van 12 maanden te maken

Hiermee gebruikt u de functie DATUM om een datum te maken op basis van het huidige jaar. Met REEKS wordt een matrixconstante gemaakt van 1 tot en met 12 voor januari tot en met december. Vervolgens wordt met de functie TEKST de weergavenotatie geconverteerd naar 'mmm' (jan, feb, mrt, enzovoort). Als u de volledige maandnaam wilt weergeven, zoals januari, gebruikt u "mmmm".

Wanneer u een benoemde constante gebruikt als matrixformule, moet u niet vergeten het gelijkteken op te geven, zoals in =Kwartaal1, en niet alleen maar Kwartaal1. 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 functies, tekst en getallen gebruiken. Het hangt allemaal af van hoe creatief u wilt zijn.

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

    Voer =REEKS(1,12)*2 of ={1\2\3\4;5\6\7\8;9\10\11\12}*2 in

    U kunt ook delen door middel van (/), optellen door middel van (+) en aftrekken door middel van (-).

  • De items in een matrix tot de tweede macht verheffen

    Voer =REEKS(1,12)^2 of ={1\2\3\4;5\6\7\8;9\10\11\12}^2 in

  • De vierkantswortel van gekwadrateerde items in een matrix berekenen

    Voer =WORTEL(REEKS(1,12)^2) of =WORTEL({1\2\3\4;5\6\7\8;9\10\11\12}^2) in

  • Een eendimensionale rij transponeren

    Voer =TRANSPONEREN(REEKS(1,5)) of =TRANSPONEREN({1\2\3\4\5}) in

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

  • Een eendimensionale kolom transponeren

    Voer =TRANSPONEREN(REEKS(5,1)) of =TRANSPONEREN({1;2;3;4;5}) in

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

  • Een tweedimensionale constante transponeren

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

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

In dit gedeelte worden voorbeelden gegeven van eenvoudige matrixformules.

  • Een matrix maken op basis van bestaande waarden

    In het volgende voorbeeld wordt uitgelegd hoe u met matrixformules een nieuwe matrix maakt op basis van een bestaande matrix.

    Enter =REEKS(3,6,10,10) of ={10\20\30\40\50\60;70\80\90\100\110\120;130\140\150\160\170\180} in

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

    Voer vervolgens =D9# of =D9:I11 in een lege cel in. Er wordt een matrix van 3 x 6 cellen weergegeven met dezelfde waarden als in D9:D11. Het #-teken wordt de overloopbereikoperator genoemd. Dit is in Excel de manier om te verwijzen naar het hele matrixbereik. U hoeft het dus niet te typen.

    Gebruik de operator voor het overgelopen bereik (#) om te verwijzen naar een bestaande matrix

  • Een matrixconstante maken op basis van bestaande waarden

    U kunt de resultaten van een overloopmatrixformule omzetten in de onderdelen ervan. Selecteer cel D9 en druk op F2 om over te schakelen naar de bewerkingsmodus. Druk vervolgens op F9 om de celverwijzingen naar waarden te converteren. De waarden worden omgezet in een matrixconstante. Wanneer u op Enter drukt, moet de formule, =D9#, nu ={10\20\30;40\50\60;70\80\90} zijn.

  • Tekens tellen in een celbereik

    In het volgende voorbeeld wordt getoond hoe u het aantal tekens in een celbereik kunt tellen. Dit is inclusief spaties.

    Het totale aantal tekens in een bereik en andere matrices tellen voor het werken met teksttekenreeksen

    =SOM(LENGTE(C9:C13))

    In dit geval wordt met de functie LENGTE de lengte van de tekenreeks in de afzonderlijke cellen in het bereik opgehaald. Met de functie SOM worden deze waarden bij elkaar opgeteld en wordt het resultaat (66) weergegeven. Als u het gemiddelde aantal tekens wilt weten, kunt u gebruikmaken van:

    =GEMIDDELDE(LENGTE(C9:C13))

  • Inhoud van langste cel in bereik C9:C13

    =INDEX(C9:C13,VERGELIJKEN(MAX(LENGTE(C9:C13)),LENGTE(C9:C13),0),1)

    Deze formule werkt alleen wanneer een gegevensbereik één kolom met cellen bevat.

    Laten we de formule eens bekijken, te beginnen met de binnenste elementen. De functie LENGTE retourneert de lengte van alle items in het celbereik D2:D6. De functie MAX berekent de grootste waarde van deze items. Deze komt overeen met de langste tekenreeks, in cel D3.

    Nu wordt het wat ingewikkelder. Met de functie VERGELIJKEN wordt de verschuiving (de relatieve positie) berekend 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(C9:C13)

    en die tekenreeks bevindt zich in de volgende matrix:

    LENGTE(C9:C13)

    Het argument van het vergelijkingstype is in dit geval 0. Het vergelijkingstype kan de waarde 1, 0 of -1 hebben.

    • 1: hiermee wordt de grootste waarde geretourneerd die kleiner is dan of gelijk is aan de opzoekwaarde.

    • 0: hiermee wordt de eerste waarde geretourneerd die precies gelijk is aan de opzoekwaarde.

    • -1: hiermee wordt de kleinste waarde geretourneerd 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 C9:C13 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.

    Als u de inhoud van de kleinste tekenreeks wilt hebben, vervangt u MAX in het bovenstaande voorbeeld door MIN.

  • De n kleinste waarden in een bereik zoeken

    In dit voorbeeld ziet u hoe u de drie kleinste waarden in een celbereik kunt vinden, waarbij een matrix met voorbeeldgegevens in de cellen B9:B18 is gemaakt met: =INTEGER(RANDARRAY(10,1)*100). RANDARRAY is een vluchtige functie, dus u krijgt telkens wanneer er een berekening wordt uitgevoerd een nieuwe verzameling willekeurige getallen.

    Excel-matrixformule om de op één na kleinste waarde te vinden: =KLEINSTE(B9#;REEKS(D9))

    Voer =KLEINSTE(B9#;REEKS(D9), =KLEINSTE(B9:B18,{1;2;3}) in)

    In deze formule wordt een matrixconstante gebruikt om de functie KLEINSTE drie keer te evalueren en de kleinste drie leden in de matrix te retourneren die is opgenomen in de cellen B9:B18, waarbij 3 een variabele waarde is in cel D9. Als u meer waarden wilt vinden, kunt u de waarde in de functie REEKS verhogen of meer argumenten aan de constante toevoegen. U kunt deze formule ook combineren met aanvullende functies, zoals SOM of GEMIDDELDE. Voorbeeld:

    =SOM(KLEINSTE(B9#,REEKS(D9))

    =GEMIDDELDE(KLEINSTE(B9#,REEKS(D9))

  • 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.

    Voer =GROOTSTE(B9#,RIJ(INDIRECT("1:3"))) of =GROOTSTE(B9:B18,RIJ(INDIRECT("1:3"))) in

    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 cel en voer het volgende in:

    =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"))

    De functie INDIRECT gebruikt teksttekenreeksen als argumenten (daarom staat het bereik 1:10 tussen aanhalingstekens). In Excel worden tekstwaarden niet aangepast wanneer u rijen invoegt of de matrixformule verplaatst. Hierdoor genereert de functie RIJ altijd de matrix met gehele getallen die u wilt gebruiken. U kunt net zo goed REEKS gebruiken:

    =REEKS(10)

    Laten we de formule die u eerder hebt gebruikt, =GROOTSTE(B9#,RIJ(INDIRECT("1:3"))), bekijken, beginnend met de binnenste haakjes en naar buiten werkend: de functie INDIRECT retourneert een verzameling tekstwaarden, in dit geval de waarden 1 tot en met 3. De functie RIJ genereert op zijn beurt een kolommatrix van drie cellen. De functie GROOTSTE gebruikt de waarden in het celbereik B9:B18 en wordt drie keer geëvalueerd - één keer voor elke verwijzing die door de functie RIJ wordt geretourneerd. Als u meer waarden wilt vinden, voegt u een groter celbereik toe aan de functie INDIRECT. Tot slot nog dit: u kunt deze formule, net als in de voorbeelden met KLEINSTE, gebruiken in combinatie met andere functies, zoals SOM en GEMIDDELDE.

  • 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 #WAARDE! of #N/B. In dit voorbeeld wordt getoond hoe u de waarden in het bereik Gegevens kunt optellen als dit bereik fouten bevat:

    Gebruik matrices om fouten te corrigeren. Met =SOM(ALS(ISFOUT(Gegevens),"'',Gegevens) wordt bijvoorbeeld het bereik met de naam Gegevens opgeteld, zelfs als het fouten bevat, zoals #VALUE! of #NA!.

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

    Met de formule wordt een nieuwe matrix gemaakt die de oorspronkelijke waarden minus eventuele foutwaarden bevat. Te beginnen met de binnenste functies zoekt de functie ISFOUT in het celbereik (Gegevens) naar fouten. Met de functie ALS wordt een bepaalde waarde geretourneerd als de opgegeven voorwaarde WAAR is. Er wordt een andere waarde geretourneerd als deze ONWAAR is. In dit geval retourneert de functie lege tekenreeksen ("") voor alle foutwaarden omdat deze WAAR als resultaat geven. De functie retourneert de resterende waarden uit het bereik (Gegevens) omdat deze ONWAAR zijn, wat betekent dat ze geen foutwaarden bevatten. Vervolgens berekent de functie SOM het totaal voor de gefilterde matrix.

  • Het aantal foutwaarden in een bereik tellen

    Deze formule lijkt op de vorige, maar met deze formule wordt het aantal foutwaarden in het bereik Gegevens geretourneerd. Ze worden er dus niet uit 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.

Het is mogelijk dat u waarden bij elkaar moet optellen afhankelijk van bepaalde voorwaarden.

U kunt matrices gebruiken om te berekenen op basis van bepaalde voorwaarden. Met =SOM(ALS(Verkopen>0,Verkopen)) worden alle waarden groter dan 0 in het bereik met de naam Verkopen opgeteld.

Met bijvoorbeeld deze matrixformule worden alleen de positieve gehele getallen in het bereik Verkoop opgeteld. Dit is het celbereik E9:E24 in het bovenstaande voorbeeld:

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

Met de functie ALS wordt een matrix van positieve 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 2500:

=SOM((Verkoop>0)*(Verkoop<2500)*(Verkoop))

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. U kunt bijvoorbeeld waarden optellen die groter zijn dan 0 OF kleiner dan 2500:

=SOM(ALS((Verkoop>0)+(Verkoop<2500),Verkoop))

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.

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 Verkoop gebruikt:

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

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.

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 UwGegevens ook 3 rijen en 5 kolommen bevatten:

=SOM(ALS(MijnGegevens=UwGegevens,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<>UwGegevens))

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

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. Anders bevat de matrix een lege tekenreeks (""). De functie MIN gebruikt de nieuwe matrix als tweede argument en geeft als resultaat de kleinste waarde. Deze waarde komt overeen met het rijnummer van de maximumwaarde in Gegevens. Als het bereik Gegevens identieke maximumwaarden bevat, retourneert de formule de rij van de eerste waarde.

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

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

U vindt vergelijkbare voorbeelden in het voorbeeldwerkboek op het werkblad Verschillen tussen gegevenssets.

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

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

Verkoper

Autotype

Aantal verkocht

Prijs per eenheid

Totale verkoop

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)

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

  2. 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.

Wanneer u op Ctrl+Shift+Enter drukt, wordt de formule wordt tussen accolades ({}) gezet, en er wordt een exemplaar van de formule in elke cel van het geselecteerde bereik ingevoegd. 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 D13 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 van C2 tot en met D11) vermenigvuldigd en wordt de functie SOM gebruikt om de totalen bij elkaar op te tellen. Het verkoopresultaat heeft als eindtotaal een bedrag van €1.590.000. Dit voorbeeld laat zien hoe krachtig dit type formule kan zijn. Stel dat u 1000 rijen met gegevens hebt. U kunt een deel of alle gegevens optellen door een matrixformule in één cel te maken in plaats van dat u de formule over de 1000 rijen naar beneden sleept.

U ziet ook dat de formule in één cel (in cel D13) volledig onafhankelijk is 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 matrixformules: 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 D13.

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 Verwijderen. U moet het hele celbereik (E2 tot en met E11) selecteren en de formule voor de hele matrix wijzigen of de matrix zo laten. Als extra veiligheidsmaatregel moet u op Ctrl+Shift+Enter drukken om elke wijziging in de formule te bevestigen.

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

In het algemeen wordt in matrixformules de standaardsyntaxis voor formules gebruikt. Alle matrixformules beginnen met een gelijkteken (=) en u kunt de meeste ingebouwde Excel-functies in matrixformules gebruiken. Het belangrijkste verschil is dat u bij het gebruik van een matrixformule op Ctrl+Shift+Enter moet drukken om de formule in te voeren. Als u dit doet, wordt de matrixformule tussen accolades geplaatst. Als u de accolades handmatig typt, wordt de formule geconverteerd naar een teksttekenreeks die niet werkt.

Matrixfuncties kunnen een efficiënte manier zijn 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).

Belangrijk: Druk op Ctrl+Shift+Enter telkens wanneer u een matrixformule wilt invoeren. 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.

  • Als u een matrixformule wilt verwijderen, selecteert u het hele formulebereik (bijvoorbeeld E2:E11) en drukt u op Verwijderen.

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

Het kan voorkomen dat u een matrixformule moet uitbreiden. Selecteer de eerste cel in een bestaand matrixbereik en ga door tot u het hele bereik hebt geselecteerd waarnaar u de formule wilt uitbreiden. Druk op F2 om de formule te bewerken en druk vervolgens op Ctrl+Shift+Enter om de formule te controleren nadat u het formulebereik hebt aangepast. Het belangrijkste is om het hele bereik te selecteren, te beginnen met de cel linksboven in de matrix. De cel linksboven is de cel die wordt bewerkt.

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

  • Het kan zijn dat u vergeet 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.

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.

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

Een horizontale constante maken

  1. Selecteer cellen A1 tot en met E1 in een leeg werkblad.

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

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

    In dit geval moet u de accolades openen en sluiten ({ }) typen. De tweede set wordt automatisch toegevoegd.

    Het volgende resultaat 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 in 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 in 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 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. Nadat u een constante aan een matrixformule hebt toegevoegd, moet u op Ctrl+Shift+Enter 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 in 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})

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.

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 een rij met drie lege cellen.

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

    =Kwartaal1

    De volgende informatie 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.

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 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.

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.

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 deze matrix met getallen in het celbereik C8: E10 wordt ingevoerd met behulp van een matrixformule. Op uw werkblad moeten C8 tot en met E10 er als volgt uitzien:

    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

    In de cellen C1 tot en met E3 wordt een matrix van 3x3 cellen weergegeven die dezelfde waarde hebben 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. 

  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.

  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.

    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)

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

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

De volgende formule wordt gebruikt in cel A8 om het totaal aantal tekens (66) 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 (66) weergegeven.

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. Voer enkele willekeurige getallen in de cellen A1:A11 in.

  2. Selecteer de cellen C1 tot en met C3. In deze cellen worden de resultaten opgeslagen die door de matrixformule worden geretourneerd.

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

    =KLEINSTE(A1:A11,{1;2;3})

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. U kunt deze formule ook combineren met aanvullende functies, zoals SOM of GEMIDDELDE. Voorbeeld:

=SOM(KLEINSTE(A1:A10,{1\2\3})

=GEMIDDELDE(KLEINSTE(A1:A10,{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 D1 tot en met D3.

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

    =GROOTSTE(A1:A10,RIJ(INDIRECT("1:3")))

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 met tien cellen in uw oefenwerkboek, voer deze matrixformule in en druk vervolgens 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"))

De functie INDIRECT gebruikt teksttekenreeksen als argumenten (daarom staat het bereik 1:10 tussen dubbele aanhalingstekens). In Excel worden tekstwaarden niet aangepast wanneer u rijen invoegt of de matrixformule verplaatst. Hierdoor genereert de functie RIJ altijd de matrix met gehele getallen die u wilt gebruiken.

Laten we eens kijken naar de formule die u eerder hebt gebruikt, =GROOTSTE(A5:A14,RIJ(INDIRECT("1:3"))), beginnend met de binnenste haakjes: de functie INDIRECT retourneert een verzameling tekstwaarden, in dit geval de waarden 1 tot en met 3. De functie RIJ genereert op zijn beurt een kolommatrix van drie cellen. De functie GROOTSTE gebruikt de waarden in het celbereik A5:A14 en wordt drie keer geëvalueerd - één keer voor elke verwijzing die door de functie RIJ wordt geretourneerd. De waarden 3200, 2700 en 2000 worden geretourneerd in de kolommatrix met drie cellen. Als u meer waarden wilt vinden, voegt u een groter celbereik toe aan de functie INDIRECT.

Net als in eerdere voorbeelden kunt u deze formule gebruiken in combinatie met andere functies, zoals SOM en GEMIDDELDE.

De langste tekenreeks in een celbereik zoeken

Ga terug naar het eerdere voorbeeld met de tekenreeks, voer de volgende formule in een lege cel in en druk op Ctrl+Shift+Enter:

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

De tekst "groep cellen die" wordt weergegeven.

Laten we de formule eens bekijken, te beginnen met de binnenste elementen. De functie LENGTE retourneert de lengte van alle items in het celbereik A2:A6. De functie MAX berekent de grootste waarde van deze items. Deze komt overeen met de langste tekenreeks, in cel A3.

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(A2:A6)))

en die tekenreeks bevindt zich in de volgende matrix:

LENGTE(A2:A6)

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 A2:A6 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.

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 de formule wordt een nieuwe matrix gemaakt die de oorspronkelijke waarden minus eventuele foutwaarden bevat. Te beginnen met de binnenste functies zoekt de functie ISFOUT in het celbereik (Gegevens) naar fouten. Met de functie ALS wordt een bepaalde waarde geretourneerd als de opgegeven voorwaarde WAAR is. Er wordt een andere waarde geretourneerd als deze ONWAAR is. In dit geval retourneert de functie lege tekenreeksen ("") voor alle foutwaarden, omdat deze WAAR als resultaat geven. De functie retourneert de resterende waarden uit het bereik (Gegevens) omdat deze ONWAAR zijn, wat betekent dat ze geen foutwaarden bevatten. Vervolgens berekent de functie SOM het totaal voor de gefilterde matrix.

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=UwGegevens,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<>UwGegevens))

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. Anders bevat de matrix een lege tekenreeks (""). De functie MIN gebruikt de nieuwe matrix als tweede argument en retourneert de kleinste waarde. Deze waarde komt overeen met het rijnummer van de maximumwaarde in Gegevens. Als het bereik Gegevens identieke maximumwaarden bevat, retourneert de formule de rij van de eerste waarde.

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

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

Bevestiging

Delen uit dit artikel zijn 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 de vroegere Excel-MVP John Walkenbach.

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

Dynamische matrices en overloopgedrag van matrices

Dynamische matrixformules versus oudere CSE-matrixformules

De functie FILTER

De functie ASELECT.MATRIX

De functie VOLGORDE

De functie SORTEREN

De functie SORTBY

De functie UNIQUE

#SPILL! -fouten in Excel

Impliciete snijpuntoperator: @

Overzicht van formules

Meer hulp nodig?

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

Was deze informatie nuttig?

×