Revision as of 19:32, 8 December 2007

Template:Documentation/FR/AideDemandee

Cette page est en cours de traduction d'après la page http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Conditional_Counting_and_Summation dont le lien est dans l'OLH.

Sommes et calculs conditionnels dans Calc

Ceci est un résumé sur les différentes façons de compter des cellules et de faire la somme de leurs contenu en fonction du résultat de certains tests.

NB

La fonction NB compte le nombre de cellules qui contient des nombres et ignorera les autres. Par exemple les cellules contenant du texte seront ignorées.

NBVAL

La fonction NBVAL compte le nombre de cellules quelque soit leurs contenus (du texte, des nombres, des erreurs, des valeurs logiques ou des formules ). Elle ignore les cellules vides.

NB.VIDE

La fonction NB.VIDE compte le nombre de cellules vides.

SOMME

La fonction SOMME fait la somme des nombres contenus dans les cellules spécifiées. Voir plus bas sur la facon de l'utiliser comme une fonction conditionnelle.

SOUS.TOTAL

La fonction SOUS.TOTAL renvoie les résultats NB, NBVAL ou SOMME pour des données filtrées, donc pour les données contenues dans des cellules, précédemment choisies grâce à un filtre.

NB.SI

La fonction NB.SI compte les éléments qui remplissent une condition unique. Par exemple NB.SI(A1:A4; “>4”) compte les cellules de la plage A1:A4 qui sont supérieures à 4.

SOMME.SI

La fonction SOMME.SI totalise les éléments qui vérifient une condition unique. Par exemple SOMME.SI(A1:A4; ”=rouge”; B1:B4) totalise les valeurs de la plage B1:B4 qui correspondent à la valeur “rouge” dans la plage A1:A4.

BDNB, BDNBVAL, BDPRODUIT

Les fonctions BDNB, BDNBVAL et BDSOMME agissent de la même façon que NB, NBVAL et SOMME, à cette différence près que les cellules comptées ou totalisées sont choisies en fonction d'une série de conditions (désignée sous le vocable "critères de recherche"). Par exemple, BDNB(A1:C5; 0; E6:F7) compte le nombre de lignes de la plage A1:C5 pour lesquelles les conditions figurant dans la plage E6:F7 sont toutes vérfiées.

Conditions dans la selection des cellules

Un moyen très simple de compter ou de totaliser en utilisant plusieurs conditions consiste à indiquer ces conditions dans une nouvelle ligne ou une nouvelle colonne. Par exemple A1:A6 contient une liste de couleurs et B1:B6 une liste de tailles, il est possible d'entrer dans la cellule D1 la formule =(A1=”rouge”), qui renvoie VRAI ou FAUX selon que le contenu de la cellule A1 est rouge ou pas. Une alternative consiste à entrer dans la cellule D1 la formule =ET(A1=”rouge”; B1=”grand”), qui renvoie VRAI si le contenu de la cellule A1 est red ET celui de la cellule B1 est grand et qui renvoie FAUX dans les autres cas. Copier et coller cette formule dans les cellules de la plage D2:D6 permet d'obtenir une série de cellules contenant VRAI si les conditions sont vérifiées et FAUX autrement.

En terme de calcul numérique, VRAI est traité en tant que 1, et FAUX est traité en tant que 0. Aussi, entrer =SOMME(D1:D6) totalisera simplement ces 1 et ces 0, et retournera le total des éléments qui sont à la fois rouge ET grand.

En fait, puisque VRAI et FAUX valent 1 et 0, le recours à la fonction ET n'est pas indispensable - dans D1 il est possible de simplement écrire =(A1=”rouge”)*(B1=”grand”), et copier/coller cette formule dans la plage de cellules D2:D6.

Maintenant, supposons que C1:C6 contient une liste de poids de ces articles, et que nous souhaitons connaître le poids total de tous les articles grand rouge. En D1 nous écrivons =(A1=”rouge”)*(B1=”grand”)*C1, et effectuons un copier/coller dans la plage de cellules D2:D6. D1 contiendra le poids mentionné en C1 si les conditions sont vérifiées (et zéro autrement) et ainsi de suite pour D2:D6. Ainsi =SOMME(D1:D6) nous donnera maintenant le poids total.

D'une autre manière, il est possible de remplir la plage D1:D6 avec une formule de matrice. En D1, on peut écrire =(A1:A6=”rouge”)*(B1:B6=”grand”)*C1:C6, et valider en pressant simultanément Ctrl+Shift+Enter. Toutes les cellules dans la plage D1:D6 affichent maintenant les poids souhaités, comme précédemment.

SOMMEPROD

The fonction SOMMEPROD can be used to perform the counting and summation calculations in the previous section, without using extra columns. It is necessary to understand array formulas to understand this.

Using the summation example from the previous section, A1:A6=”red”, B1:B6=”big” and C1:C6 may be treated as 3 separate arrays, not displayed but internally calculated.

=SUMPRODUCT(A1:A6=”red”; B1:B6=”big”; C1:C6) will multiply corresponding elements of the arrays together and return their sum, i.e.:

(A1=”red”)*(B1=”big”)*C1 + (A2=”red”)*(B2=”big”)*C2 + ...

This again gives us the total weight, without requiring an extra column.

Notice that SUMPRODUCT formulas are simply entered by pressing the Enter key - they do not require Ctrl_Shift_Enter even though arrays are involved.

Also, be aware that calculations using very large arrays take a lot of computer processing time, and may slow the spreadsheet down.

SOMME with array formulas

An alternative to SUMPRODUCT is to use the SUM function. The previous example would be written:

=SUM( (A1:A6=”red”)*(B1:B6=”big”)*C1:C6) )

and entered as an array formula by pressing Ctrl_Shift_Enter. As with SUMPRODUCT, this works by multiplying corresponding elements of the arrays together and returning their sum.

DataPilot

Another way to approach conditional counting and summation is to use the DataPilot to generate an interactive table, whereby data can be arranged and summarised according to different points of view.

Trucs et Astuces: checking settings

When matching text with some functions (such as SUMIF) , the results may well depend on the settings on the Tools menu-> Options-> OpenOffice.org Calc-> Calculate page. If the user's settings are incorrect, the results may therefore be wrong.

One solution is to include prominently in the spreadsheet a check that the settings are correct. For example:

=IF(ISERR(SEARCH(".";"a"));"ERROR: please enable regular expressions";"")

will show an error message if regular expressions are disabled.

Another example - in cell A3 enter the text:

Check: 

In cell A4 enter:

="Regular expressions are "&IF(COUNTIF(A3;".*"); "enabled"; "disabled")

In cell A5 enter:

="Whole cell matching is "&IF(COUNTIF(A3;"<>e"); "enabled"; "disabled")

or better, use appropriate error messages.

Trucs et Astuces: items between two dates

Dates are stored internally as numbers and can thus be compared easily. For example, to count the number of cells in A1:A6 between two dates, you could use:

=SUMPRODUCT(A1:A6>DATEVALUE("5 Nov 06"); A1:A6<DATEVALUE("5 Dec 06"))

If you express the dates with slashes (e.g. “1/2/2005”) you can dispense with the DATEVALUE function, as Calc will convert the date. However, be aware that in one country this text may be converted to 1Feb05 and in another to 2Jan05.

Trucs et Astuces: summing the largest/smallest items

To add up the largest 3 numbers in A1:A5, the most straightforward method is to enter:

=LARGE(A1:A5; 1) in cell B1

=LARGE(A1:A5; 2) in cell B2

=LARGE(A1:A5; 3) in cell B3

so that the largest 3 numbers are in B1:B3, and then use the formula:

=SUM(B1:B3) to give the result.

This method is very clear, and generally therefore to be recommended.

However if you wish to derive the same result in a single cell, you could use

=SUMPRODUCT(LARGE(A1:A5; ROW(A1:A3)))

Here ROW(A1:A3) is a 1 column 3 row array containing the numbers 1, 2, 3.

LARGE(A1:A5; ROW(A1:A3)) is then a 1 column 3 row array containing the largest 3 numbers and SUMPRODUCT simply adds them up. We could use SUM instead of SUMPRODUCT but in that case the formula must be entered as an array formula by pressing Ctrl_Shift_Enter.

To add up the largest 4 numbers (say), use ...ROW(A1:A4).. instead of ...ROW(A1:A3)..

To add the smallest numbers, use SMALL(...) instead of LARGE(...).

Trucs et Astuces: summing matching blank, etc cells

=SUMPRODUCT(ISBLANK(A1:A5); B1:B5)

sums those cells in B1:B5 corresponding to blank cells in A1:A5.

Other similar functions (e.g. ISTEXT, ISNUMBER) can be used in the same way.

Trucs et Astuces: summing more than one column

=SUMPRODUCT(D1:D6="red";E1:E6+F1:F6)

will sum cells in E1:E6 and in F1:F6 which correspond to cells in D1:D6 containing red. For example if D2 and D4 contain red, the result is E2+F2+E4+F4.

Trucs et Astuces: summing every nth row

=SUMPRODUCT(MOD(ROW(A1:A8); 2)=0;  A1:A8)

will sum every second row in A1:A8. Change the =0 to =1 to sum every second row, but starting at the first row. Change the 2 to 3 to sum every third row, and so on.

Trucs et Astuces: summing items with certain formatting

The CELL function returns information about cells, for instance the format that numbers or dates are displayed in and the column width. It can thus be used with one of the methods above. There is no function that returns the colour or font of a cell. If no independent formula relating to colour or font exists, then it may be necessary to use a macro.

Trucs et Astuces: summing matching items in a separate list

Say that A1:A9 contains a list of dates, B1:B9 contains phone numbers and C1:C9 the costs of making phone calls. F1:F5 is a list of certain phone numbers, and you want to know the total cost of calls to these numbers.

In cell D1 enter:

=ISNUMBER(MATCH(B1; F$1:F$5; 0))*C1

and copy/paste down to D2:D9

=SUM(D1:D9) now gives the total cost of calls.

To perform this calculation without an extra column, you could use:

=SUMPRODUCT(NOT(ISERROR(MATCH(B1:B9; F$1:F$5; 0))); C1:C9)

We discussed above how it can be important to check Calc settings; this is a good example - if the user has regular expressions turned on (the default) a telephone number written as (720) 528-1700 is interpreted as a regular expression and might match entries other than (720) 528-1700.