Matrixformules - richtlijnen en voorbeelden

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

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

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.

   

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.

   

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:

   

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.

   

   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.

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:

   

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.

   

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