NL/Documentation/How Tos/Matrices gebruiken

Introductie

Matrices kunnen handig zijn bij het reduceren van het aantal cellen dat u gebruikt, maar ze kunnen ietwat ingewikkeld te begrijpen zijn. Op sommige manieren kunnen zij de mogelijkheden van een werkblad uitbreiden tot voorbij het punt waarvoor werkbladen bedoeld waren. Bijna alle taken zijn mogelijk zonder hen.

Een matrix (array) is eenvoudigweg een rechthoekig blok van informatie dat Calc kan bewerken in een formule - dat betekent dat het informatie is die is georganiseerd in rijen en kolommen. Een matrix kan cellen zijn op een werkblad of intern door Calc worden behandeld.

Er zijn 2 manieren om een matrix in een formule te specificeren:

• als een bereik - bijvoorbeeld A2:C3.

• als een "matrix als regel", bijvoorbeeld {1; 5; 3 | 6; 2; 4} (deze zijn volledig functioneel vanaf OOo2.4, maar zij bestaan in eerdere versies - zie Matrix-problemen). U typt accolades { } rondom een matrix als regel. Items op een regel worden gescheiden door een punt-komma ‘;’, en rijen worden gescheiden door het teken pipe ‘|’. Elke rij moet hetzelfde aantal elementen hebben (het is niet juist om te schrijven {1; 2; 3 | 4; 5} omdat er 3 elementen in de bovenste rij staan en slechts 2 in de volgende rij). Matrices als regel mogen gemixte inhoud hebben, bijvoorbeeld {4; 2; "hond" | -22; "kat"; 0}. Een matrix als regel mag echter geen verwijzingen bevatten (bijv.: A4), of formules (bijv.: PI(), 2*3 ), of percentages (bijv.: 5%).

U kunt een naam geven aan een celbereik: selecteer het bereik en kies Invoegen - Namen - Definiëren.

U kunt een naam geven aan een matrix als regel: kies Invoegen - Namen - Definiëren; type de matrix (bijv.: {1; 3; 2} inclusief de accolades) in het vak Toegewezen aan.

Functies die matrices als parameters begrijpen

Sommige functies, zoals SOM(), GEMIDDELDE(), VERGELIJKEN() en ZOEKEN(), accepteren één of meer van hun parameters als matrices.

Bijvoorbeeld:

SOM( A2:C3 ) geeft de som terug van de getallen in het bereik A2:C3.

SOM( {3; 2; 4} ) geeft 9 terug, de som van de getallen in matrix als regel {3; 2; 4}.

SOM verwacht/begrijpt ook enkele (‘scalaire’) waarden - SOM( B5; 7 ) geeft de som terug van B5 en 7.

Functies die geen matrix als parameter verwachten

Sommige functies, zoals ABS(), WORTEL(), COS(), LENGTE() verwachten dat hun parameters ‘scalair’ zijn - dat betekent één enkele waarde zoals 354 of "hond" of de inhoud van een cel bijv.: B5.

Voorbeelden:

WORTEL(4) geeft 2 terug.

LENGTE("hond") geeft 4 terug.

U kunt echter nog steeds een matrix gebruiken waar één enkele waarde wordt verwacht - bijvoorbeeld WORTEL( {9; 4} ). Als u de formule invoert door ‘normaal’ op Enter te drukken, zal Calc de formule als volgt evalueren met behulp van één enkele waarde uit de matrix.

Als het een matrix als regel is:

Calc za; de eerste waarde gebruiken (die ‘linksboven’).

Voorbeeld:

=WORTEL( {9; 4 | 25; 16} ) drukken op Enter geeft 3 terug, de wortel van het eerste element in de matrix (9).

Als het een bereik is:

1. Calc zal een fout teruggeven, tenzij de matrix één enkele rij of één enkele kolom is.

2. Voor een bereik van één enkele rij of één enkele kolom zal Calc de waarde gebruiken van de cel waar de kolom/rij van de formule-cel kruist met de matrix (of een fout teruggeven als er geen kruising is).

Voorbeelden:

De formule =ABS(B2:B5) wordt 'normaal' ingevoerd in cel D3, dat is op rij 3. Rij 3 kruist B2:B5 bij cel B3, dus de geëvalueerde formule is =ABS(B3).

De formule =LENGTE(B5:D5) wordt 'normaal' ingevoerd in cel B1, dat is in kolom B. Kolom B kruist B5:D5 bij cel B5, dus de geëvalueerde formule is =LENGTE(B5).

Matrixformules

The real power of arrays comes when you enter a formula in a special way, as an ‘array formula’. You do this by pressing Cntrl-Shift-Enter instead of the Enter button (or by ticking the Array checkbox if you are using the Formula Wizard).

If in cell B1 you enter ={3; 4} ‘normally’ by pressing Enter, the first value 3 is displayed in the cell.

If in cell B2 you type ={3; 4} but press Cntrl-Shift-Enter instead of Enter, the cell becomes an 'array formula'. The formula now returns the entire array {3; 4}. Cell B2 displays 3 and cell C2 displays 4.

Note that if you entered the formula using the Enter key, simply selecting the cell and pressing Cntrl-Shift-Enter will not convert the cell to an array expression - you must make an actual edit (such as adding then deleting a character), or click the Function Wizard and tick the Array checkbox.

If you now try to edit cell B2, you are told that "you cannot change only part of an array". To edit an array you must select the entire array, either with the mouse or by typing Cntrl-/ (hold the Cntrl key and press the slash key ‘/’).

The formula bar indicates that this is an array formula by enclosing it in curly braces {}. You do not need to type these - they will disappear while you edit the formula, and Calc will show them again when you have finished editing.

Array formula calculations

When Calc evaluates an array formula, it treats ‘unexpected arrays’ as a series of values (rather than using a single value), calculating a result for each of the array elements, and returning an array of results.

Example:

=SQRT( {16; 4; 25} ) when entered by pressing Cntrl-Shift-Enter instead of Enter returns an array of results, with 1 row and 3 columns - {4; 2; 5}. If the formula is in cell B2, Calc places the results in cells B2:D2. 4 is placed in B2, 2 in C2 and 5 in D2.

The process in effect works thus:

1. All ‘unexpected arrays’ in the same array calculation should be the same size (see this issue: Issue 46681).
2. The result will be returned in an array of that size.
3. The calculation is done for each element in turn, with the result returned in the corresponding element of the output array.

Example:

With the array formula =SQRT( {16; 4; 25} ):

1. There is only one array, with 1 row and 3 columns.
2. The result will be returned in an array with 1 row and 3 columns.
3. The calculation is done for 16 first, then for 4, then for 25, giving the array result {4; 2; 5}.

Example:

With the array formula =SQRT( {8 | 18} * 2 ) in cell A5:

1. There is only one array, with 2 rows and 1 column.
2. The result will be returned in an array with 2 rows and 1 column.
3. The calculation is: first element SQRT(8*2) = 4; second element SQRT(18*2) = 6; the array result is thus {4 | 6} - that is, 4 in cell A5 and 6 in cell A6.

The result of an array expression is an array, which can be used within the formula.

Example:

=SUM(SQRT( {16; 4; 25} )) as an array formula. The calculation of SQRT( {16; 4; 25} ), as before, yields a result of {4; 2; 5}, thus giving SUM( {4; 2; 5} ), returning a final result in the cell of 4+2+5 = 11.

Example:

With the array formula =SUM(IF(A1:A4>0; B1:B4; 0))

1. The two arrays A1:A4 and B1:B4 both have 4 rows and 1 column.
2. The result of the IF() array calculation will be an array of that size, which SUM() will add up.
3. If A1>0 the first element is B1; else 0. If A2>0 the second element is B2; else 0 .... The array presented to SUM() has the values in B1:B4 where the adjacent value in A1:A4 is >0. The final output is the sum of the values in B1:B4 for which the adjacent value in A1:A4 is >0.

Array functions

Some functions calculate their result as an array. If they are to return that array, they must be entered as an array formula, by pressing Cntrl-Shift-Enter (or by ticking the Array checkbox if you are using the Formula Wizard). If, instead, they are entered 'normally' with the Enter button, just the (single) top left element of the calculated array will be returned. These funtions are listed in the Array function category, and are:

MINVERSE, MMULT, MUNIT, TRANSPOSE, FREQUENCY, GROWTH, LINEST, LOGEST, TREND

For example: =MUNIT(2) entered as an array formula in cell B1 returns the 2 x 2 unit (identity) matrix as the array {1; 0 | 0; 1} - that is, cells B1, B2, C1, C2 show 1, 0, 0, 1. If this formula is entered 'normally', just the 'top left' value (1) is returned in cell B1.

Some functions (including some listed above) take parameters that are forced to evaluate as an array formula, even when the formula is entered 'normally':

MDETERM, MINVERSE, MMULT, SUMPRODUCT, SUMX2MY2, SUMX2PY2, SUMXMY2, CORREL, COVAR, FORECAST, FTEST, INTERCEPT, MODE, PEARSON, PROB, RSQ, SLOPE, STEYX, TTEST

For example: MODE(ABS(A1:A3)) (in normal mode) forces ABS(A1:A3) to be evaluated as an array formula, returning an array of the absolute values of A1:A3 from which MODE selects the most common value to be returned.

For example: PROB(A1:A5; B1:B5/100; 3) (in normal mode) forces B1:B5/100 to be evaluated as an array formula, returning an array where each value is one hundredth of its value in B1:B5 (perhaps because the probabilities were expressed in percent).

A few of these functions have some (array) parameters that are forced to evaluate as an array formula, and other (single value) parameters that evaluate normally.

For example: PROB(A1:A5; B1:B5; {3; 4}) (in normal mode) does not evaulate {3; 4} as an array, because this would mean PROB returning an array. It evaluates PROB(A1:A5; B1:B5; 3) - that is, it interprets {3; 4} in scalar mode, taking the 'top left' value 3. However, if entered as an array formula it will of course return an array.

Tips and Tricks

All these examples are to be entered as an array expression, by pressing Cntrl-Shift-Enter.

Sum of entries matching multiple conditions

SUM( (A1:A6="red")*(B1:B6="big")*C1:C6 ) returns the sum of entries in C1:C6 whose A column entries are "red" AND whose B column entries are "big". A1:A6 and B1:B6 each produce a 6 element array of TRUE or FALSE - which in number calculations are 1 or 0. Thus if A2 contains "red" and B2 contains "big" the second element of the array is 1 * 1 * C2 = C2. If A2 contains "blue" instead, the second element of the array is 0 * 1 * C2 = 0.

SUM( ((A1:A6="red")+(B1:B6="big")>0)*C1:C6 ) returns the sum of entries in C1:C6 whose A column entries are "red" OR whose B column entries are "big".

SUM( MOD((A1:A6="red")+(B1:B6="big");2)*C1:C6 ) returns the sum of entries in C1:C6 either whose A column entries are "red" OR whose B column entries are "big" but not both (exclusive OR)

SUM( NOT((A1:A6="red")+(B1:B6="big"))*C1:C6 ) returns the sum of entries in C1:C6 where neither the A column entry is "red" NOR the B column entry is "big".

Count of entries matching multiple conditions

SUM( (A1:A6="red")*(B1:B6="big") ) returns the number of rows whose A column entries are "red" AND whose B column entries are "big".

Maximum in a particular month

MAX(IF(MONTH(B1:B9)=5; C1:C9; 0)) returns the maximum value in C1:C9 where the corresponding B1:B9 date is in May (month 5). The MIN() function would provide the minimum in the month.

Average of entries meeting a condition

AVERAGE(IF(A1:A9="red"; B1:B9; "")) returns the average of entries in B1:B9 whose A column entries are “red”. The AVERAGE function ignores any blank entries.

The MEDIAN function can be used similarly.

Dynamic sorting of a column

=LARGE(B3:B9;ROW(B3:B9)+1-ROW(B3)) entered in cell C3 returns an array in C3:C9 which is B3:B9 in descending order. +1-ROW(3) is a constant - you could write =LARGE(B3:B9;ROW(B3:B9)-2) instead.

=SMALL(B3:B9;ROW(B3:B9)-2) returns B3:B9 in ascending order.

Sum ignoring errors

SUM( IF(ISERROR(A1:A9); 0; A1:A9) ). Normally SUM will propagate any error found.

Average, ignoring zero entries

AVERAGE(IF(B1:B9<>0; B1:B9; ""))

Test a cell for one of a set of values

OR(B2={2; 5; 6}) as an array expression, or OR(B2=2; B2=5; B2=6) as a ‘normal’ formula.

Sum of the smallest 4 numbers

SUM(SMALL(B3:B9; {1;2;3;4})) or SUM(SMALL(B3:B9;ROW(A1:A4))). ROW(A1:A4) produces {1;2;3;4}.

Last used cell in a column

MAX(ROW(B1:B9)*(B1:B9<>"")) returns the row number of the last cell used.

Issues

• Inline arrays work in OOo2.3, but fail if there are any spaces, or negative numbers. This is fixed for OOo2.4, although spaces are removed rather than ignored (issue 82644).
• It will be possible to include different size arrays in a formula, as there is a defined calculation process in the forthcoming international standard ODFF. Calc should comply in OOo3.0 (issue 46681).
• Some functions cannot yet be used in array formulas: COUNTIF, SUMIF (issue 65866); MATCH (issue 8947); ISLOGICAL (issue 87219).
• OFFSET has limited behaviour within an array formula.
• Names defined by Insert - Names - Define can be used within an array formula, but labels (either Insert - Names - Labels or automatically recognised at the head of a column) should not be used (issue 87268).
• In Excel, not all functions can be used in array formulas. For example CONCATENATE works in Calc but not in Excel. Be aware of Excel's limitations if you plan to use a Calc spreadsheet in Excel.
• Volatile functions such as RAND, RANDBETWEEN, NOW may not be recalculated in array formulas; for example in {=A1:A3+RAND()} just one random number is generated (issue 67135).
• Array formulas may slow down your computer if you have large arrays.