Excel
Formules en functies
Formules
Formules
Matrixformules - richtlijnen en voorbeelden
Applies ToExcel voor Microsoft 365 Excel voor Microsoft 365 voor Mac Excel 2024 Excel 2024 voor Mac Excel 2021 Excel 2021 voor Mac Excel 2019 Excel 2016 Excel voor iPad Excel voor iPhone

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

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 of ondersteuning vragen in de Communities.

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

De functie UNIEK

#OVERLOOP! -fouten in Excel

Impliciete snijpuntoperator: @

Overzicht van formules

Facebook LinkedIn E-mail

Meer hulp nodig?

Meer opties?

Verken abonnementsvoordelen, blader door trainingscursussen, leer hoe u uw apparaat kunt beveiligen en meer.

Voordelen van Microsoft 365-abonnementen

Microsoft 365-training

Microsoft-beveiliging

Toegankelijkheidscentrum