6. Arrays en matrices

Spreadsheets zijn bij uitstek geschikt om bewerkingen met numerieke tabellen of matrices te maken.

Hierna worden de volgende bewerkingen behandeld:

• Twee n x m matrices optellen.
• Twee n x m matrices aftrekken.
• Een n x m matrix met een scalair (getal) vermenigvuldigen.
• Een matrix transponeren.
• Een n x m matrix met een m x p matrix vermenigvuldigen.
• De determinant van een n x n matrix berekenen.
• De inverse van een n x n matrix berekenen.
• De matrixvergelijking A.x = b oplossen (stelsel van Cramer).
• Bewerkingen met vectoren.

Sommige van deze bewerkingen kunnen door Excel worden uitgevoerd op een rechthoekige (of vierkante) tabel met getallen, waarbij je het resultaat uiteraard op een vrije plaats in het werkblad plaatst. Voor andere bewerkingen moet je met arrays werken. Dit zijn eveneens rechthoekige tabellen met getallen, waarbij de tabel getallen als één geheel wordt opgevat en je geen bewerkingen (zoals het opnemen in een formule) met individuele elementen doen.

In de rest van dit hoofdstuk wordt met een tabel getallen een tabel bedoeld waarin de getallen individueel kunnen worden behandeld; anders wordt de term array gebruikt.

Je zal in dit hoofdstuk werken met een werkboek Matrix.xls. Oprn dit bestand vanuit P:\Excel 97 openen en bewaar het op je I: schijf.

6.1 Bewerkingen met een tabel met getallen

Vooreerst zal je twee tabellen getallen en een scalair getal aanbrengen en een naam geven. Verderop zal je er een aantal bewerkingen mee uitvoeren. Dit zal uiteindelijk leiden tot het werkblad Bewerkingen met matrices (figuur 1). Geef vooraf de naam aan het werkblad door zijn sheet tab te dubbelklikken en de naam in te vullen.

Je voert de getalwaarden in de 2 x 3 matrices A en B het gemakkelijkst in door eerst de matrix te selecteren en vervolgens de waarden in te typen per kolom. Met ENTER ga je naar het volgende element in de kolom. Wanneer je de matrices per rij wilt invullen, moet je na elk element op tab drukken in plaats van op ENTER. Je kan het kader vóór of na het intypen van de waarden aanbrengen met de 11de knop van de Borders lijst. Geef verder sheet-level namen:

6.1.1 Som en verschil van een tabel getallen

Je kan de matrix A+B op twee manieren berekenen:

• Met de copy en paste special methode.
• Met formules.

figuur 1: Het werkblad 'Bewerkingen met matrices'

Gebruik de copy and paste special methode om A+B te berekenen:

1. Selecteer tabel A (B3:D4).
2. Kopieer met de Copy knop naar het clipboard.
3. Selecteer de nog lege tabel A+B (G3:I4).
4. Plak de inhoud van het clipboard met ENTER of de Paste knop.
5. Selecteer tabel B (B6:D7).
6. Kopieer met de Copy knop naar het clipboard.
7. Selecteer tabel A+B (G3:I4).
8. Plak de inhoud van het clipboard met het commando
   
   figuur 2: Het Paste Special dialoogvenster

   Edit, Paste Special...

   Kies in het Paste Special dialoogvenster (figuur 2) de opties Paste All except borders en Operation: Add. De te plakken celinhouden worden opgeteld bij de celinhouden die er reeds staan. Druk eventueel op esc om de stippellijn rond tabel B te verwijderen.

Gebruik de methode met formules om A–B te berekenen:

1. Selecteer tabel A–B (G6:I7).
2. Typ de formule voor de eerste cel (G6), =B3+B6 in en beëindig met CTRL+ENTER of sleep de fill handle naar G6:I7.
3. Breng (pas) achteraf het kader aan met de 11de knop uit de Borders lijst.

Opmerkingen:

• Je kan de copy and paste methode vervangen door de CTRL+sleep methode wanneer de twee ranges in hetzelfde werkblad staan. Dit geldt echter niet voor de copy and paste special bewerkingen.
• Je moet het kopiëren van de formule door te slepen met de fill handle in twee stappen doen, afzonderlijk voor het kopiëren binnen de rij en binnen de kolommen.
• Het optellen, aftrekken, vermenigvuldigen en delen verloopt steeds op analoge wijze. Bij de copy and paste special methode moet je in het Paste Special dialoogvenster de Operations Add, Subtract, Multiply of Divide kiezen. Bij de methode met de formules moet je de bewerkingstekens +, –, * en / gebruiken.
• Het hier beschreven vermenigvuldigen en delen is niet gelijk aan de vermenigvuldiging van twee matrices of het vermenigvuldigen van de eerste matrix met de geïnverteerde van de tweede. De getallen worden cel per cel met elkaar vermenigvuldigd of door elkaar gedeeld.
• De op te tellen waarden kunnen gewone getallen zijn of formules. Dit kan andere effecten hebben in de getallentabel met de resultaten. Bij de copy and paste special methode moet je zo nodig aandacht besteden aan de vorm van het plakken:

  Paste All	Plak formules en formaten.
  Paste Formulas	Plak formules maar wijzig de formaten niet.
  Paste Values	Zet bij het plakken de formules om naar hun getalwaarden.
  Paste Formats	Plak enkel de formaten en laat de inhoud van de cellen ongewijzigd.
  Paste Comments	Plak commentaren.
  Paste Validation	Plak regels waaraan de invoer moet voldoen.
  Paste All except borders	Plak formules en formaten behalve kaders.

• De optie Paste All except borders is zeer interessant. In vele gevallen, zoals bij het transponeren, zijn de afmetingen van de Copy en Paste ranges verschillend. Het plakken van kaders verloopt dan nooit correct: Reeds aanwezige kaders worden vernietigd en ongewenste nieuwe lijnen komen erbij. Met deze optie kan je gerust vooraf kaders aanbrengen. Bij het plakken blijven die dan behouden.
• De Paste Operations in het Paste Special dialoogvenster zullen ook werken met formules. In dit geval wordt een bestaande formule achteraan aangevuld met de nieuwe formule. Er worden haakjes geplaatst zodat de bewerkingen in de volgorde van het plakken geschieden (van links naar rechts).
• Wanneer je Skip blanks aankruist in het Paste Special dialoogvenster zal het plakken enkel gebeuren in cellen die gegevens bevatten. De inhoud van cellen met gegevens zal op deze wijze niet worden vervangen door die van lege cellen (maar eventueel wel door de nieuwe gegevens).
• Met Transpose kan je de getallentabel transponeren (rijen en kolommen verwisselen) (zie infra).
• De Paste Link knop in het Paste Special dialoogvenster zal bij het plakken tussen verschillende toepassingen of documenten het verband met het origineel bewaren. Hierbij wordt gebruik gemaakt van OLE (Object Linking and Embedding). Telkens het origineel wordt gewijzigd zal ook de met de Paste Link knop geplakte kopie worden gewijzigd.
• Het commando Edit, Paste komt overeen met de standaard opties Paste All en Operation: None van het commando Edit, Paste Special.... Het kopiëren met copy en paste special waarbij je de optie Paste Formats kiest, komt overeen met het gebruik van de Format Painter knop.

6.1.2 Een tabel getallen met een scalair (getal) vermenigvuldigen

In cel B9 staat het getal n (waarde 5) waarmee de matrix A moet worden vermenigvuldigd tot het resultaat in G9:I10.

Het berekenen van de matrix n.A kan eveneens op twee wijzen gebeuren:

• Met de copy en paste special methode.
• Met formules.

Bij de copy and paste special methode voer je de volgende acties uit:

1. Selecteer tabel A (B3:D4).
2. Kopieer met de Copy knop naar het clipboard.
3. Selecteer de nog lege tabel n.A (G9:I10).
4. Plak de inhoud van het clipboard met ENTER of de Paste knop.
5. Selecteer n (B9).
6. Kopieer met de Copy knop naar het clipboard.
7. Selecteer tabel n.A (G9:I10).
8. Plak de inhoud van het clipboard met het commando

   Edit, Paste Special..., Paste All except borders, Operation: Multiply

   De reeds aanwezige celinhouden worden vermenigvuldigd met de geplakte waarde.

Bij de methode met de formules voer je de volgende acties uit:

1. Selecteer tabel n.A (G9:I10).
2. Typ de formule voor de eerste cel (G9), =B3*$B$9 of =B3*n in en beëindig met CTRL+ENTER of sleep de fill handle naar G9:I10.
3. Breng (pas) achteraf het kader aan met de 11de knop van de Borders lijst.

Opmerkingen:

• Je kan de hier besproken bewerkingen uitvoeren met twee getallen, twee tabellen van gelijke afmetingen en een tabel enerzijds en een getal anderzijds. In dit laatste geval wordt elk element van de tabel met het getal bewerkt.
• Bij de werkwijze met de formules zijn de referenties van belang. Indien beide tabellen gelijke afmetingen hebben moet je relatieve referenties gebruiken. Bij het vermenigvuldigen van een tabel met een getal moet je relatieve referenties gebruiken voor de tabel en absolute referenties voor het getal.
• Bij het commando Edit, Paste Special... mag je de kaders niet mee plakken, om te vermijden dat de randen rond de resulterende tabel verdwijnen. Dit geldt enkel wanneer de verschillende tabellen niet dezelfde afmetingen of randen hebben. Wanneer de gekopieerde cel B9 een getalwaarde bevat (zoals hier) kan je Paste All except borders gebruiken. Wanneer de gekopieerde cellen echter formules bevatten, moet je plakken met Paste Values. Helaas is er geen optie Paste Values except borders !

6.1.3 Een tabel getallen transponeren

Transponeer nu de matrices in de vorm van de getallentabellen A en A+B (verwissel rijen en kolommen). Hierbij is enkel nog de copy en paste special methode mogelijk:

1. Selecteer tabel A (B3:D4).
2. Kopieer met de Copy knop naar het clipboard.
3. Selecteer de nog lege tabel trans(A) (B12:C14) of zijn eerste cel (B12).
4. Plak de inhoud van het clipboard met het commando

Edit, Paste Special..., Paste All except borders, Operation: None, kruis Transpose aan

Bij het plakken wordt de tabel getransponeerd.

5. Klik de 11de knop in de Borders lijst om het kader aan te brengen.

Opmerkingen:

• Het transponeren van cellen met formules met relatieve referenties schept meestal problemen.
• Het aanbrengen van het kader moet in een afzonderlijke bewerking (vooraf of achteraf) gebeuren. Daarom mag je de randen niet mee plakken.

6.2 Arrays

Met de hierboven gebruikte methodes is het niet mogelijk matrices te vermenigvuldigen (de laatste bewerkingen in figuur 1) of vierkante matrices te inverteren. Daarvoor zijn functies nodig die niet één resultaat opleveren maar een hele tabel aan resultaten. Zulke tabellen, die steeds in hun geheel worden behandeld, worden arrays genoemd. Het zijn steeds formules (die beginnen met een = teken), die voor alle cellen uit de tabel gelijk zijn. Dit houdt in dat de formule eigenlijk maar éénmaal voorkomt. Het berekende en getoonde resultaat kan echter in alle cellen van de array verschillen.

Een array formule wordt onderscheiden van een gewone formule doordat ze tussen accolades staat { }. Deze worden aangebracht door het intypen van de formule niet te beëindigen met ENTER, maar wel met SHIFT+CTRL+ENTER. De formule zal dan in elke cel worden aangebracht.

6.2.1 Array constanten en array formules

Ook constanten (getallen, teksten) kunnen in een array voorkomen. In dat geval worden ze als een formule aangebracht door ze na het = teken tussen { } te plaatsen, waarbij de waarden in een rij door komma's worden gescheiden en de rijen door kommapunten. Zo kan je de 2 x 3 matrix

in B3:D4 invoeren door eerst de 2 x 3 matrix te selecteren, daarna de formule ={1,2,3;4,5,6} in te typen en deze met SHIFT+CTRL+ENTER te beëindigen. Dit wordt dan in de formulebalk genoteerd als

Deze werkwijze biedt nochtans weinig voordelen t.o.v. het intypen van een getal in elke cel.

Je kan een gewone formule in een geselecteerde tabel omvormen tot een array formule op de volgende wijze:

1. Selecteer de tabel.
2. Klik in de formulebalk. De formule van de eerste cel wordt getoond.
3. Druk op SHIFT+CTRL+ENTER om de formule tot een array formule en de tabel tot een array om te vormen. In de formulebalk worden { } rond de formule geplaatst.

Opmerking:

• Omdat formules in een array meestal een andere gedaante hebben dan formules in een cel, zal deze werkwijze vaak geen zinvolle resultaten opleveren en vaak zelfs in een foutmelding resulteren. Zo zal de array gedaante van de formule om de cellen van A en B op te tellen in A+B er als volgt uit zien:

  G3:I4:

  {=B2:D3+B5:D6}

  en niet als

  {=B2+B5}

  wat je zou bekomen door de tabel A+B te selecteren, in de formulebalk te klikken (de formule =B2+B5 wordt getoond) en op SHIFT+CTRL+ENTER te drukken. De hier vermelde techniek is dus vooral van belang om een formule te corrigeren die per ongeluk met ENTER of CTRL+ENTER werd beëindigd in plaats van met SHIFT+CTRL+ENTER.

Omgekeerd kan je een array met formules in een gegevenstabel omvormen op de volgende wijze:

1. Selecteer de tabel.
2. Klik in de formulebalk.
3. Druk op CTRL+ENTER om de array tot een gewone tabel om te vormen. Nu staat de eerste waarde uit de array in elke cel. De { } rond de formule in de formulebalk werden verwijderd.

Opmerking:

• Het is niet mogelijk een individuele cel van een array te editeren. Bij het verlaten van de formulebalk met ENTER verschijnt een foutmelding. Je moet het editeren beëindigen met CTRL+ENTER of SHIFT+CTRL+ENTER.

Array formules hebben meestal dezelfde gedaante als gewone formules. Het onderscheid met gewone formules is:

• Het intypen van array formules wordt beëindigd met SHIFT+CTRL+ENTER in plaats van met ENTER of CTRL+ENTER.
• Een array formule wordt tussen { } geplaatst.
• In alle cellen van de array staat dezelfde array formule. Deze heeft vaak een andere gedaante dan een gewone formule.
• Je kan een afzonderlijk element in een array niet in een formule opnemen of bewerken. Wil je dit toch doen, dan moet je de array formule in een tabel met gewone formules omzetten.
• Sommige formules kunnen enkel werken met array formules als argument. Andere hebben steeds een array als resultaat.

6.2.2 Bewerkingen met arrays die je ook met gewone tabellen getallen kan uitvoeren

Je kan de volgende bewerkingen zowel met gewone tabellen getallen als met arrays uitvoeren:

• Arrays (matrices) optellen en aftrekken.
• Een array (matrix) vermenigvuldigen met een scalair (getal).
• Een array (matrix) transponeren.

Bekijk opnieuw de lay-out van Bewerkingen met matrices (figuur 1). De formules zijn in array gedaante:

De eerste drie formules, die constanten weergeven, vergen geen verduidelijking. Je kan de matrices met constante waarden zowel in de vorm van een tabel getallen opgeven als in de vorm van een array. Omdat deze laatste werkwijze meer typwerk vergt en kans op typfouten meebrengt, en daarenboven het verwijzen naar individuele cellen niet mogelijk is, is het aan te bevelen arrays die zuiver uit getalconstanten bestaan niet te gebruiken.

Je bekomt de formule voor A+B als volgt bekomen vooraf de namen A en B zijn gedefinieerd:

1. Selecteer de range voor de array A+B.
2. Typ de formule in: =A+B.
3. Beëindig de formule met SHIFT+CTRL+ENTER.

Op een analoge wijze verkrijg je de formule voor het verschil van twee matrices (A–B) en de vermenigvuldiging van de matrix met een scalair getal (n.A).

Bij het transponeren van een matrix gebruik je de functie TRANSPOSE(array):

1. Selecteer de range voor de array trans(A) of trans(A+B).
2. Klik de Paste Function knop. Zoek de TRANSPOSE functie in het Paste Function dialoogvenster in de Lookup & Reference categorie.
3. Typ het argument in (A of A+B) in het TRANSPOSE dialoogvenster en beëindig de formule met SHIFT+CTRL+ENTER.

Bij deze werkwijze blijft het verband tussen de oorspronkelijke cellen met waarden en de cellen met formules bestaan. Ze is daarenboven eenvoudiger dan de methode met tabellen getallen.

Opmerkingen:

• Let er op dat je het TRANSPOSE dialoogvenster sluit met SHIFT+CTRL+ENTER en niet met ENTER of de OK knop!
• In het Paste Function dialoogvenster kan je de meeste array functies terugvinden in de Math & Trig categorie. Ze beginnen meestal met de letter M. De TRANSPOSE functie vormt hierop een uitzondering. Ze is terug te vinden in de Lookup & Reference categorie. Na een eerste gebruik van de functie bevindt ze zich echter ook in de Most Recently Used categorie, waarin je steeds de tien meest onlangs gebruikte functies kan terugvinden.
  
  figuur 3: De voorstelling van array elementen in het MMULT dialoogvenster
• Bij het opstellen van de array functies met de Paste Function knop wordt in de dialoogvensters gebruik gemaakt van de array voorstelling van constanten (figuur 3). Dit gebeurt om de resultaten zo beknopt mogelijk genoteerd te kunnen weergeven.

6.2.3 Arrays vermenigvuldigen

Je vermenigvuldigt matrices met de functie MMULT(array1,array2).

De MMULT heeft twee argumenten die in het algemeen beide arrays moeten zijn. Daarenboven moeten de regels die geldig zijn bij de vermenigvuldiging van matrices worden gerespecteerd:

• Wanneer je het product maakt van de matrix A en de matrix B, wordt dit genoteerd als:

  {=MMULT(A,B)}

  waarbij het aantal rijen in matrix B gelijk moet zijn aan het aantal kolommen in matrix A.

• Een element van de productmatrix is de som van de producten van de elementen uit de corresponderende rij in A met de elementen uit de corresponderende kolom in B.

Maak als voorbeeld de producten trans(A) . B en B . trans(A). De formules voor het product van matrices zijn:

Opmerkingen:

• Het product van matrices is niet-commutatief. Dit heeft als gevolg dat de volgorde van de matrices van belang is. Niet alleen de getalwaarden in het product maar ook het aantal rijen en kolommen van de productmatrix kunnen er worden door beïnvloed!
• Je kan de MMULT functie ook gebruiken om het product te maken van tabellen getallen. Indien de matrices trans(A) en B als tabellen getallen en niet als arrays werden opgesteld, kan je de MMULT functie gebruiken en het resultaat als een tabel getallen voorstellen (dus niet tussen { }), wanneer je in de MMULT functie een rij en kolom uit de betreffende matrices vermeldt en gebruik maakt van gemengde referenties. Selecteer vooraf de hele productmatrix en stel de formule op voor het eerste element (eerste rij, eerste kolom). Dit element wordt gevormd als het product van een rijmatrix en een kolommatrix. In de rijmatrix hou je de rijreferenties relatief en de kolomreferenties absoluut; in de kolommatrix hou je de rijreferenties absoluut en de kolomreferenties relatief. Wanneer je de formule beëindigt met CTRL+ENTER wordt ze in alle cellen van de tabel getallen correct ingevuld:

  B16:	=MMULT($B12:$C12*B$6:B$7)

  In de overige cellen zijn de formules dan:

  C16:	=MMULT($B12:$C12*C$6:C$7)
  D16:	=MMULT($B12:$C12*D$6:D$7)
  B17:	=MMULT($B13:$C13*B$6:B$7)
  C17:	=MMULT($B13:$C13*C$6:C$7)
  D17:	=MMULT($B13:$C13*D$6:D$7)
  B18:	=MMULT($B14:$C14*B$6:B$7)
  C18:	=MMULT($B14:$C14*C$6:C$7)
  D18:	=MMULT($B14:$C14*D$6:D$7)

  Het vergt meer werk en meer kans op vergissingen wanneer je de productmatrix als een tabel getallen opstelt dan als één array formule. Daarenboven zijn er nu negen formules in plaats van één wanneer je een array formule gebruikt.

• Wanneer het resultaat van de MMULT functie een matrix is, dan wordt deze steeds als een array voorgesteld. De argumenten kunnen dan zowel arrays als ranges zijn (maar de dimensies moeten kloppen). Wanneer het resultaat van de MMULT functie één enkel getal is kan dit als een getal en als een array worden voorgesteld. In dit geval is het eerste argument steeds een range of array bestaande uit één rij en het tweede argument een range of array bestaande uit één kolom.

Wanneer je in het View tabblad van het Options dialoogvenster Formulas aankruist, zie je de formules (figuur 4), maar de {} worden enkel in de formulebalk getoond.

figuur 4: De formules in het werkblad 'Bewerkingen met matrices'

6.2.4 Een matrix inverteren

Je kan eveneens de inverse van een matrix berekenen, wanneer deze althans bestaat. Hiervoor moet de matrix vierkant zijn (evenveel rijen als kolommen hebben) en een van nul verschillende determinant hebben. Je gebruikt hierbij de functies MDETERM(array) en MINVERSE(array).

• De MDETERM functie kan als argument een vierkante range of een array hebben en heeft als resultaat een getal.
• De MINVERSE functie moet steeds als argument een vierkante array hebben en heeft als resultaat steeds opnieuw een vierkante array.

figuur 5: Het werkblad 'Stelsel van Cramer'

Stel als voorbeeld het werkblad Stelsel van Cramer (figuur 5) op. Drie matrices hebben sheet-level namen gekregen: A (B3:D5), b (G3:G5) en x (G9:G11). De formules zijn:

Je kan een stelsel van Cramer oplossen (stelsel met evenveel lineaire vergelijkingen als onbekenden en juist één oplossing) met behulp van deze functies. Het stelsel vergelijkingen kan worden geschreven in de gedaante

A . x = b

De oplossing van het stelsel wordt bekomen door

x = A^-1 . b

of met de array formule:

6.3 Bewerkingen met vectoren

Wanneer je vectoren definieert als matrices die bestaan uit één kolom (of één rij), dus als kolomvectoren (of rijvectoren), dan kan je hierop ook enkele bewerkingen uitvoeren, die steeds één enkel getal als resultaat hebben. De vectoren moeten steeds dezelfde dimensie hebben (zelfde type – rij of kolom – en evenveel elementen). Deze formules zijn geïllustreerd in het werkblad Bewerkingen met vectoren (figuur 6).

figuur 6: Het werkblad 'Bewerkingen met vectoren'

De formules zijn:

Opmerkingen:

• Al deze functies bevinden zich in de Math & Trig categorie in het Paste Function dialoogvenster.
• Je kan verifiëren dat de volgende rekenregels gelden:

  sumx2my2(x,y) = sumsq(x) – sumsq(y) = sumproduct((x+y),(x–y))

  sumx2py2(x,y) = sumsq(x) + sumsq(y)

  sumxmy2(x,y) = sumsq(x) + sumsq(y) – 2*sumproduct(x,y) = sumsq(x–y)

• Je kan de functie SUMXMY2(x,y) gebruiken om het kwadraat van de (Euclidische) afstand tussen twee punten met coördinaatvectoren x en y te berekenen. De afstand zelf wordt dan gegeven via de functie SQRT (vierkantswortel):

  =SQRT(SUMXMY2(x,y))