Latest revision as of 12:53, 6 May 2009

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 leur 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 quel que soit leur contenu (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 ci-dessous l'utilisation de cette fonction en combinaison avec une condition.

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érifiées.

Conditions dans la sélection 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") ou =((A1="rouge") ET (B1="grand")), qui renvoie VRAI si le contenu de la cellule A1 est rouge 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, saisir =SOMME(D1:D6) totalisera simplement ces 1 et ces 0, et renverra 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+Maj+Entrée. Toutes les cellules dans la plage D1:D6 affichent maintenant les poids souhaités, comme précédemment.

SOMMEPROD

La fonction SOMMEPROD peut être utilisée pour effectuer les comptages et les totalisations de la section précédente, sans avoir à recourir à des colonnes supplémentaires. Il est nécessaire de comprendre les formules matricielles pour comprendre cela.

L'exemple de totalisation de la section précédente, A1:A6="rouge", B1:B6="grand" et C1:C6 peut être traité comme 3 matrices séparées, non affichées et calculées de manière interne.

=SOMMEPROD(A1:A6="rouge"; B1:B6="grand"; C1:C6) va multiplier les éléments correspondants des matrices mentionnées et renvoyer leur somme, à savoir :

(A1="rouge")*(B1="grand")*C1 + (A2="rouge")*(B2="grand")*C2 + ...

Ceci donne à nouveau le poids total, sans avoir recours à une colonne supplémentaire.

Notez que les formules SOMMEPROD sont simplement entrées en pressant la touche Entrée – elles ne nécessitent pas la combinaison Ctrl+Maj+Entrée, même si elles mettent en œuvre les matrices.

Il est également nécessaire d'avoir conscience du fait que les calculs portant sur des matrices de grande taille nécessitent beaucoup de temps processeur et sont susceptibles de ralentir la feuille de calcul.

SOMME avec des formules matricielles

Une alternative à SOMMEPROD est d'utiliser la fonction SOMME. L'exemple précédent serait rédigé :

=SOMME( (A1:A6="rouge")*(B1:B6="grand")*C1:C6) )

et saisit comme une formule matricielle en pressant Ctrl+Maj+Entrée. Comme avec SOMMEPROD, ceci agit en multipliant entre eux les éléments correspondants des matrices et en renvoyant leur somme.

Le pilote de données

Une autre approche des sommes et calculs conditionnels consiste à recourir au Pilote de données et générer une table interactive, dans laquelle les données peuvent être arrangées et résumées de différentes façons.

Trucs et Astuces : Vérifiez les paramètres

En manipulant du texte avec certaines fonctions (comme SOMME.SI), le résultat obtenu peut dépendre des réglages effectués dans la page Menu Outils-> Options-> OpenOffice.org Calc-> Calcul. Si les réglages de l'utilisateur sont incorrects, les résultats obtenus peuvent, de ce fait, être faux.

Une solution peut consister à inclure, en haut de la feuille de calcul, un contrôle de l'exactitude des réglages. Par exemple :

=SI(ESTERR(CHERCHE(".";"a"));"ERREUR: veuillez autoriser les caractères génériques dans les formules";"")

affichera un message d'erreur si les caractères génériques dans les formules ne sont pas autorisés.

Un autre exemple – dans la cellule A3 saisissez le texte :

Vérification : 

Dans la cellule A4 saisissez :

="Les expressions régulières sont "&SI(NB.SI(A3;".*"); "activées"; "désactivées")

Dans la cellule A5 saisissez :

="L'option exactitude comme affiché est "&SI(NB.SI(A3;"<>e"); "activée"; "désactivée")

ou mieux encore, utilisez des messages d'erreurs appropriés.

Trucs et Astuces : Valeurs entre deux dates

Les dates sont stockées en interne comme des nombres et peuvent donc être comparées facilement. Par exemple pour compter le nombre de cellules dans A1:A6 entre deux dates vous pouvez utiliser :

=SOMMEPROD(A1:A6>DATEVAL("5 Nov 06"); A1:A6<DATEVAL("5 Dec 06"))

Si vous exprimez les dates avec des barres obliques (par exemple. “1/2/2005”) vous pouvez vous passer de la fonction DATEVAL , puisque Calc convertira la date. Cependant tenez compte du fait que dans certains pays cette date peut être interprétée comme le 1 Février 2005 et dans d'autres comme le 2 Janvier 2005.

Trucs et Astuces : Faire la somme des plus grandes valeurs / ou plus petites

Pour faire la somme des 3 plus grandes valeurs de la plage A1:A5, le plus simple est de saisir :

=GRANDE.VALEUR(A1:A5; 1) dans la cellule B1

=GRANDE.VALEUR(A1:A5; 2) dans la cellule B2

=GRANDE.VALEUR(A1:A5; 3) dans la cellule B3

Pour que les 3 plus grands nombres apparaissent dans B1:B3, puis d'utiliser la formule :

=SOMME(B1:B3)</tt> pour obtenir le résultat.

Cette méthode est très claire et à recommander pour le cas général.

Cependant si vous voulez obtenir ce résultat en passant par une seule cellule, vous pouvez utiliser :

=SOMMEPROD(GRANDE.VALEUR(A1:A5; LIGNE(A1:A3)))

Ici LIGNE(A1:A3) est un tableau d'une colonne et 3 lignes contenant les nombres 1, 2, et 3.

GRANDE.VALEUR(A1:A5; LIGNE(A1:A3)) est un tableau d'une colonne et 3 lignes contenant les plus grands nombres et SOMMEPROD fait juste la somme des 3. On pourrait aussi utiliser SOMME à la place de SOMMEPROD mais dans ce cas la formule doit être saisie comme une une formule de matrice en en tapant Ctrl+Maj+Entrée.

Pour additionner les 4 plus grand nombres, utilisez ...LIGNE(A1:A4).. au lieu de ...LIGNE(A1:A3)..

Pour additionner les plus petits nombres, utilisez PETITE.VALEUR(...) au lieu de GRANDE.VALEUR(...).

Trucs et Astuces : Faire la somme de cellules correspondant à d'autres cellules vides

=SOMMEPROD(ESTVIDE(A1:A5); B1:B5)

Fera la somme des cellules dans B1:B5 correspondant aux cellules vides dans A1:A5.

D'autre fonctions similaires (par exemple ESTTEXTE, ESTNUM) peuvent être utilisées de la même façon.

Trucs et Astuces : Faire la somme de plus d'une colonne

=SOMMEPROD(D1:D6="rouge";E1:E6+F1:F6)

Fera la somme des cellules E1:E6 et F1:F6 qui correspondent aux cellules dans D1:D6 contenant rouge. Par exemple si D2 et D4 contiennent rouge, le résultat est E2+F2+E4+F4.

Trucs et Astuces : Faire la somme chaque 'n' lignes

=SOMMEPROD(MOD(LIGNE(A1:A8); 2)=0;  A1:A8)

Fera la somme de chaque deuxième ligne dans A1:A8. Changer le =0 en =1 permettra de commencer à la première ligne. Changer le 2 en 3 pour faire la somme toutes les trois lignes, etc........

Trucs et Astuces : Faire la somme d'objets respectant un certain formatage

La fonction CELLULE renvoie des informations sur les cellules, par exemple le format d'affichage des nombres ou des dates, et la largeur des colonnes. Elle peut être utilisée dans les mêmes conditions que les paragraphes précédents. Il n'y a pas de fonction qui renvoie la couleur ou la police d'une cellule, si aucune formule relative à la couleur ou à la police n'existe, il sera nécessaire de faire appel à une macro.

Trucs et Astuces : Faire la somme d'objet similaires dans une liste séparée

Disons que A1:A9 contient une liste de dates, B1:B9 contient des numéros de téléphones, et C1:C9 le coût de ces appels. F1:F5 est une liste de certains numéros et vous voulez établir les coûts d'appels à ces numéros.

Dans la cellule D1 saisissez :

=ESTNUM(EQUIV(B1; F$1:F$5; 0))*C1

et copiez/collez vers le bas dans D2:D9

=SOMME(D1:D9) donne maintenant le coût total des appels.

Pour faire le calcul sans colonne supplémentaire, on peut utiliser :

=SOMMEPROD(NON(ESTERREUR(EQUIV(B1:B9; F$1:F$5; 0))); C1:C9)

Nous avons vu plus haut qu'il est important de vérifier les paramétrages de Calc, ceci en est un bon exemple car si l'utilisateur a activé les expressions régulières (réglages par défaut) un numéro de téléphone écrit comme (720) 528-1700 est interprété comme une expression régulière et pourrait correspondre à d'autres lignes que (720) 528-1700.