FR/Calc/Sommes et calculs conditionnels

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,   ou   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  compte les cellules de la plage   qui sont supérieures à.

SOMME.SI
La fonction SOMME.SI totalise les éléments qui vérifient une condition unique. Par exemple  totalise les valeurs de la plage   qui correspondent à la valeur “ ” dans la plage.

BDNB, BDNBVAL, BDPRODUIT
Les fonctions BDNB, BDNBVAL et BDSOMME agissent de la même façon que,   et  , à 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,  compte le nombre de lignes de la plage   pour lesquelles les conditions figurant dans la plage    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   contient une liste de couleurs et   une liste de tailles, il est possible d'entrer dans la cellule   la formule , qui renvoie   ou   selon que le contenu de la cellule   est   ou pas. Une alternative consiste à entrer dans la cellule  la formule   ou , qui renvoie   si le contenu de la cellule   est   ET celui de la cellule   est   et qui renvoie   dans les autres cas. Copier et coller cette formule dans les cellules de la plage  permet d'obtenir une série de cellules contenant   si les conditions sont vérifiées et   autrement.



En terme de calcul numérique,  est traité en tant que , et   est traité en tant que. Aussi, saisir  totalisera simplement ces   et ces , et renverra le total des éléments qui sont à la fois   ET.

En fait, puisque  et   valent   et , le recours à la fonction   n'est pas indispensable - dans   il est possible de simplement écrire  , et copier/coller cette formule dans la plage de cellules.

Maintenant, supposons que  contient une liste de poids de ces articles, et que nous souhaitons connaître le poids total de tous les articles. En  nous écrivons , et effectuons un copier/coller dans la plage de cellules. contiendra le poids mentionné en  si les conditions sont vérifiées (et zéro autrement) et ainsi de suite pour. Ainsi  nous donnera maintenant le poids total.



D'une autre manière, il est possible de remplir la plage  avec une formule de matrice. En, on peut écrire   , et valider en pressant simultanément Ctrl+Maj+Entrée. Toutes les cellules dans la plage  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,,   et   peut être traité comme 3 matrices séparées, non affichées et calculées de manière interne.

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  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 ), le résultat obtenu peut dépendre des réglages effectués dans la page menu Outils > Options >LibreOffice 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  saisissez le texte :

Vérification : 

Dans la cellule  saisissez :

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

Dans la cellule  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 déc. 06")) 

Si vous exprimez les dates avec des barres obliques (par exemple. “ ”) vous pouvez vous passer de la fonction, 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 2011 et dans d'autres comme le 2 janvier 2011.

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, le plus simple est de saisir :

dans la cellule

dans la cellule

dans la cellule

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

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  est un tableau d'une colonne et 3 lignes contenant les nombres ,  , et.

est un tableau d'une colonne et 3 lignes contenant les plus grands nombres et  fait juste la somme des 3. On pourrait aussi utiliser  à la place de   mais dans ce cas la formule doit être saisie comme une formule de matrice en en tapant Ctrl+Maj+Entrée.

Pour additionner les 4 plus grand nombres, utilisez  au lieu de

Pour additionner les plus petits nombres, utilisez  au lieu de.

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  correspondant aux cellules vides dans.

D'autre fonctions similaires (par exemple,  ) 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  et   qui correspondent aux cellules dans   contenant. Par exemple si  et   contiennent le texte Rouge, le résultat est.

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. Changer le   en   permettra de commencer à la première ligne. Changer le  en   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'objets similaires dans une liste séparée
Disons que  contient une liste de dates,   contient des numéros de téléphones, et   le coût de ces appels. est une liste de certains numéros et vous voulez établir les coûts d'appels à ces numéros.

Dans la cellule  saisissez :

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

et copiez/collez vers le bas dans

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.