Difference between revisions of "FR/Documentation/Calc/Sommes et calculs conditionnels"

From Apache OpenOffice Wiki
< FR‎ | Documentation‎ | Calc
Jump to: navigation, search
m (NB.SI)
m
 
(20 intermediate revisions by 4 users not shown)
Line 6: Line 6:
 
=Sommes et calculs conditionnels dans Calc=
 
=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.
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==
 
==NB==
Line 15: Line 14:
 
==NBVAL==
 
==NBVAL==
  
La [[Documentation/FR/Calc:_fonction_NBVAL|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.
+
La [[Documentation/FR/Calc:_fonction_NBVAL|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==
 
==NB.VIDE==
Line 35: Line 34:
 
==SOMME.SI==
 
==SOMME.SI==
  
La [[Documentation/FR/Calc:_fonction_SOMME.SI|fonction SOMME.SI]] totalise les éléments qui vérifient une condition unique. Par exemple <tt>'''SOMME.SI(A1:A4; =rouge”; B1:B4)'''</tt> totalise les valeurs de la plage <tt>'''B1:B4'''</tt> qui correspondent à la valeur “<tt>'''rouge'''</tt>” dans la plage <tt>'''A1:A4'''</tt>.
+
La [[Documentation/FR/Calc:_fonction_SOMME.SI|fonction SOMME.SI]] totalise les éléments qui vérifient une condition unique. Par exemple <tt>'''SOMME.SI(A1:A4; "=rouge"; B1:B4)'''</tt> totalise les valeurs de la plage <tt>'''B1:B4'''</tt> qui correspondent à la valeur “<tt>'''rouge'''</tt>” dans la plage <tt>'''A1:A4'''</tt>.
  
 
==BDNB, BDNBVAL, BDPRODUIT==
 
==BDNB, BDNBVAL, BDPRODUIT==
  
Les fonctions [[Documentation/FR/Calc:_fonction_BDNB|BDNB]], [[Documentation/FR/Calc:_fonction_BDNBVAL|BDNBVAL]] et [[Documentation/FR/Calc:_fonction_BDSOMME|BDSOMME]] agissent de la même façon que <tt>'''NB'''</tt>, <tt>'''NBVAL'''</tt> et <tt>'''SOMME'''</tt>, à 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, <tt>'''BDNB(A1:C5; 0; E6:F7)'''</tt> compte le nombre de lignes de la plage <tt>'''A1:C5'''</tt> pour lesquelles les conditions figurant dans la plage  <tt>'''E6:F7'''</tt> sont toutes vérfiées.
+
Les fonctions [[Documentation/FR/Calc:_fonction_BDNB|BDNB]], [[Documentation/FR/Calc:_fonction_BDNBVAL|BDNBVAL]] et [[Documentation/FR/Calc:_fonction_BDSOMME|BDSOMME]] agissent de la même façon que <tt>'''NB'''</tt>, <tt>'''NBVAL'''</tt> et <tt>'''SOMME'''</tt>, à 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, <tt>'''BDNB(A1:C5; 0; E6:F7)'''</tt> compte le nombre de lignes de la plage <tt>'''A1:C5'''</tt> pour lesquelles les conditions figurant dans la plage  <tt>'''E6:F7'''</tt> sont toutes vérifiées.
  
 
==Conditions dans la sélection des cellules==
 
==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  <tt>'''A1:A6'''</tt> contient une liste de couleurs et <tt>'''B1:B6'''</tt> une liste de tailles, il est possible d'entrer dans la cellule <tt>'''D1'''</tt> la formule <tt>'''<nowiki>=(A1=”rouge”)</nowiki>'''</tt>, qui renvoie <tt>'''VRAI'''</tt> ou <tt>'''FAUX'''</tt> selon que le contenu de la cellule <tt>'''A1'''</tt> est <tt>'''rouge'''</tt> ou pas. Une alternative consiste à entrer dans la cellule <tt>'''D1'''</tt> la formule <tt>'''<nowiki>=ET(A1=”rouge”; B1=”grand”)</nowiki>'''</tt> ou <tt>'''<nowiki>=((A1=”rouge”) ET (B1=”grand”))</nowiki>'''</tt>, qui renvoie <tt>'''VRAI'''</tt> si le contenu de la cellule <tt>'''A1'''</tt> est <tt>'''red'''</tt> ET celui de la cellule <tt>'''B1'''</tt> est <tt>'''grand'''</tt> et qui renvoie <tt>'''FAUX'''</tt> dans les autres cas. Copier et coller cette formule dans les cellules de la plage <tt>'''D2:D6'''</tt> permet d'obtenir une série de cellules contenant <tt>'''VRAI'''</tt> si les conditions sont vérifiées et <tt>'''FAUX'''</tt> autrement.
+
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  <tt>'''A1:A6'''</tt> contient une liste de couleurs et <tt>'''B1:B6'''</tt> une liste de tailles, il est possible d'entrer dans la cellule <tt>'''D1'''</tt> la formule <tt>'''<nowiki>=(A1="rouge")</nowiki>'''</tt>, qui renvoie <tt>'''VRAI'''</tt> ou <tt>'''FAUX'''</tt> selon que le contenu de la cellule <tt>'''A1'''</tt> est <tt>'''rouge'''</tt> ou pas. Une alternative consiste à entrer dans la cellule <tt>'''D1'''</tt> la formule <tt>'''<nowiki>=ET(A1="rouge"; B1="grand")</nowiki>'''</tt> ou <tt>'''<nowiki>=((A1="rouge") ET (B1="grand"))</nowiki>'''</tt>, qui renvoie <tt>'''VRAI'''</tt> si le contenu de la cellule <tt>'''A1'''</tt> est <tt>'''rouge'''</tt> ET celui de la cellule <tt>'''B1'''</tt> est <tt>'''grand'''</tt> et qui renvoie <tt>'''FAUX'''</tt> dans les autres cas. Copier et coller cette formule dans les cellules de la plage <tt>'''D2:D6'''</tt> permet d'obtenir une série de cellules contenant <tt>'''VRAI'''</tt> si les conditions sont vérifiées et <tt>'''FAUX'''</tt> autrement.
  
 
[[Image:Fr-calc-Sommeconditions.png|exemple]]
 
[[Image:Fr-calc-Sommeconditions.png|exemple]]
  
En terme de calcul numérique, <tt>'''VRAI'''</tt> est traité en tant que <tt>'''1'''</tt>, et <tt>'''FAUX'''</tt> est traité en tant que <tt>'''0'''</tt>. Aussi, entrer <tt>'''<nowiki>=SOMME(D1:D6)</nowiki>'''</tt> totalisera simplement ces <tt>'''1'''</tt> et ces <tt>'''0'''</tt>, et retournera le total des éléments qui sont à la fois <tt>'''rouge'''</tt> ET <tt>'''grand'''</tt>.
+
En terme de calcul numérique, <tt>'''VRAI'''</tt> est traité en tant que <tt>'''1'''</tt>, et <tt>'''FAUX'''</tt> est traité en tant que <tt>'''0'''</tt>. Aussi, saisir <tt>'''<nowiki>=SOMME(D1:D6)</nowiki>'''</tt> totalisera simplement ces <tt>'''1'''</tt> et ces <tt>'''0'''</tt>, et renverra le total des éléments qui sont à la fois <tt>'''rouge'''</tt> ET <tt>'''grand'''</tt>.
  
  
En fait, puisque <tt>'''VRAI'''</tt> et <tt>'''FAUX'''</tt> valent <tt>'''1'''</tt> et <tt>'''0'''</tt>, le recours à la fonction <tt>'''ET'''</tt> n'est pas indispensable - dans <tt>'''D1'''</tt> il est possible de simplement écrire <tt>'''<nowiki>=(A1=”rouge”)*(B1=”grand”)</nowiki>'''</tt>, et copier/coller cette formule dans la plage de cellules <tt>'''D2:D6'''</tt>.
+
En fait, puisque <tt>'''VRAI'''</tt> et <tt>'''FAUX'''</tt> valent <tt>'''1'''</tt> et <tt>'''0'''</tt>, le recours à la fonction <tt>'''ET'''</tt> n'est pas indispensable - dans <tt>'''D1'''</tt> il est possible de simplement écrire <tt>'''<nowiki>=(A1="rouge")*(B1="grand")</nowiki>'''</tt>, et copier/coller cette formule dans la plage de cellules <tt>'''D2:D6'''</tt>.
  
Maintenant, supposons que <tt>'''C1:C6'''</tt> contient une liste de poids de ces articles, et que nous souhaitons connaître le poids total de tous les articles <tt>'''grand'''</tt> <tt>'''rouge'''</tt>. En <tt>'''D1'''</tt> nous écrivons <tt>'''<nowiki>=(A1=”rouge”)*(B1=”grand”)*C1</nowiki>'''</tt>, et effectuons un copier/coller dans la plage de cellules <tt>'''D2:D6'''</tt>. <tt>'''D1'''</tt> contiendra le poids mentionné en <tt>'''C1'''</tt> si les conditions sont vérifiées (et zéro autrement) et ainsi de suite pour <tt>'''D2:D6'''</tt>. Ainsi <tt>'''<nowiki>=SOMME(D1:D6)</nowiki>'''</tt> nous donnera maintenant le poids total.
+
Maintenant, supposons que <tt>'''C1:C6'''</tt> contient une liste de poids de ces articles, et que nous souhaitons connaître le poids total de tous les articles <tt>'''grand'''</tt> <tt>'''rouge'''</tt>. En <tt>'''D1'''</tt> nous écrivons <tt>'''<nowiki>=(A1="rouge")*(B1="grand")*C1</nowiki>'''</tt>, et effectuons un copier/coller dans la plage de cellules <tt>'''D2:D6'''</tt>. <tt>'''D1'''</tt> contiendra le poids mentionné en <tt>'''C1'''</tt> si les conditions sont vérifiées (et zéro autrement) et ainsi de suite pour <tt>'''D2:D6'''</tt>. Ainsi <tt>'''<nowiki>=SOMME(D1:D6)</nowiki>'''</tt> nous donnera maintenant le poids total.
  
 
[[Image:Fr-calc-Sommeconditions2.png|exemple]]
 
[[Image:Fr-calc-Sommeconditions2.png|exemple]]
  
D'une autre manière, il est possible de remplir la plage <tt>'''D1:D6'''</tt> avec une formule de matrice. En <tt>'''D1'''</tt>, on peut écrire  <tt>'''<nowiki>=(A1:A6=”rouge”)*(B1:B6=”grand”)*C1:C6</nowiki>'''</tt>, et valider en pressant simultanément Ctrl+Shift+Enter. Toutes les cellules dans la plage <tt>'''D1:D6'''</tt> affichent maintenant les poids souhaités, comme précédemment.
+
D'une autre manière, il est possible de remplir la plage <tt>'''D1:D6'''</tt> avec une formule de matrice. En <tt>'''D1'''</tt>, on peut écrire  <tt>'''<nowiki>=(A1:A6="rouge")*(B1:B6="grand")*C1:C6</nowiki>'''</tt>, et valider en pressant simultanément Ctrl+Maj+Entrée. Toutes les cellules dans la plage <tt>'''D1:D6'''</tt> affichent maintenant les poids souhaités, comme précédemment.
  
 
==SOMMEPROD==
 
==SOMMEPROD==
Line 63: Line 62:
  
  
L'exemple de totalisation de la section précédente, <tt>'''A1:A6=”rouge”'''</tt>, <tt>'''B1:B6=”grand”'''</tt> et <tt>'''C1:C6'''</tt> peut être traité comme 3 matrices séparées,non affichées et calculées de manière interne.
+
L'exemple de totalisation de la section précédente, <tt>'''A1:A6="rouge"'''</tt>, <tt>'''B1:B6="grand"'''</tt> et <tt>'''C1:C6'''</tt> peut être traité comme 3 matrices séparées, non affichées et calculées de manière interne.
  
<tt>'''<nowiki>=SOMMEPROD(A1:A6=”rouge”; B1:B6=”grand”; C1:C6)</nowiki>'''</tt> va multiplier les éléments correspondants des matrices mentionnées et renvoyer leur somme, savoir :
+
<tt>'''<nowiki>=SOMMEPROD(A1:A6="rouge"; B1:B6="grand"; C1:C6)</nowiki>'''</tt> 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 + ...'''
+
'''(A1="rouge")*(B1="grand")*C1 + (A2="rouge")*(B2="grand")*C2 + ...'''
  
 
[[Image:Fr-calc-Sommeconditions3.png|example]]
 
[[Image:Fr-calc-Sommeconditions3.png|example]]
Line 73: Line 72:
 
Ceci donne à nouveau le poids total, sans avoir recours à une colonne supplémentaire.
 
Ceci donne à nouveau le poids total, sans avoir recours à une colonne supplémentaire.
  
 
+
Notez que les formules <tt>'''SOMMEPROD'''</tt> 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.
Notez que les formules <tt>'''SOMMEPROD'''</tt> 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 oeuvre 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.
 
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.
Line 83: Line 80:
 
Une alternative à SOMMEPROD est d'utiliser la [[Documentation/FR/Calc:_fonction_SOMME|fonction SOMME]]. L'exemple précédent serait rédigé :
 
Une alternative à SOMMEPROD est d'utiliser la [[Documentation/FR/Calc:_fonction_SOMME|fonction SOMME]]. L'exemple précédent serait rédigé :
  
  '''<nowiki>=SOMME( (A1:A6=”rouge”)*(B1:B6=”grand”)*C1:C6) )</nowiki>'''
+
  '''<nowiki>=SOMME( (A1:A6="rouge")*(B1:B6="grand")*C1:C6) )</nowiki>'''
  
 
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.
 
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.
Line 91: Line 88:
 
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.
 
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==
+
==Trucs et Astuces : Vérifiez les paramètres==
  
 
En manipulant du texte avec certaines fonctions (comme <tt>'''SOMME.SI'''</tt>), 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.
 
En manipulant du texte avec certaines fonctions (comme <tt>'''SOMME.SI'''</tt>), 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.
  
[[Image:Fr-calc-Sommeconditions4.png|settings]]
+
[[Image:Fr-calc-Sommeconditions4.png|Paramétrages]]
  
 
Une solution peut consister à inclure, en haut de la feuille de calcul, un contrôle de l'exactitude des réglages. Par exemple :
 
Une solution peut consister à inclure, en haut de la feuille de calcul, un contrôle de l'exactitude des réglages. Par exemple :
Line 103: Line 100:
 
affichera un message d'erreur si les caractères génériques dans les formules ne sont pas autorisés.
 
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 <tt>'''A3'''</tt> entrez le texte :
+
Un autre exemple – dans la cellule <tt>'''A3'''</tt> saisissez le texte :
  
 
  '''Vérification : '''
 
  '''Vérification : '''
  
Dans la cellule <tt>'''A4'''</tt> entrez :
+
Dans la cellule <tt>'''A4'''</tt> saisissez :
  
 
  '''<nowiki>="Les expressions régulières sont "&SI(NB.SI(A3;".*"); "activées"; "désactivées")</nowiki>'''
 
  '''<nowiki>="Les expressions régulières sont "&SI(NB.SI(A3;".*"); "activées"; "désactivées")</nowiki>'''
  
Dans la cellule <tt>'''A5'''</tt> entrez :
+
Dans la cellule <tt>'''A5'''</tt> saisissez :
  
 
  '''<nowiki>="L'option exactitude comme affiché est "&SI(NB.SI(A3;"<>e"); "activée"; "désactivée")</nowiki>'''
 
  '''<nowiki>="L'option exactitude comme affiché est "&SI(NB.SI(A3;"<>e"); "activée"; "désactivée")</nowiki>'''
  
[[Image:Fr-calc-Sommeconditions5.png|Vérification des parametres]]
+
[[Image:Fr-calc-Sommeconditions5.png|Vérification des paramètres]]
  
 
ou mieux encore, utilisez des messages d'erreurs appropriés.
 
ou mieux encore, utilisez des messages d'erreurs appropriés.
  
==Trucs et Astuces: Valeurs entre deux dates==
+
==Trucs et Astuces : Valeurs entre deux dates==
  
Les dates sont stockés 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:
+
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 :
  
 
  '''<nowiki>=SOMMEPROD(A1:A6>DATEVAL("5 Nov 06"); A1:A6<DATEVAL("5 Dec 06"))</nowiki>'''
 
  '''<nowiki>=SOMMEPROD(A1:A6>DATEVAL("5 Nov 06"); A1:A6<DATEVAL("5 Dec 06"))</nowiki>'''
  
  
Si vous exprimez les dates avec des barres obliques (par exemple. “<tt>'''1/2/2005'''</tt>”) vous pouvez vous passer de la fonction <tt>'''DATEVAL'''</tt> , 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'autre comme le 2 Janvier 2005.
+
Si vous exprimez les dates avec des barres obliques (par exemple. “<tt>'''1/2/2005'''</tt>”) vous pouvez vous passer de la fonction <tt>'''DATEVAL'''</tt> , 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==
+
==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 <tt>'''A1:A5'''</tt>, le plus simple est de saisir :
 
Pour faire la somme des 3 plus grandes valeurs de la plage <tt>'''A1:A5'''</tt>, le plus simple est de saisir :
Line 143: Line 140:
 
'''<nowiki>=SOMME(B1:B3)</nowiki>'''</tt> pour obtenir le résultat.
 
'''<nowiki>=SOMME(B1:B3)</nowiki>'''</tt> pour obtenir le résultat.
  
[[Image:Fr-calc-Sommeconditions6.png|example]]
+
[[Image:Fr-calc-Sommeconditions6.png|exemple]]
  
Cette methode est très claire et à recommander pour le cas général.
+
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 :
 
Cependant si vous voulez obtenir ce résultat en passant par une seule cellule, vous pouvez utiliser :
Line 151: Line 148:
 
  '''<nowiki>=SOMMEPROD(GRANDE.VALEUR(A1:A5; LIGNE(A1:A3)))</nowiki>'''
 
  '''<nowiki>=SOMMEPROD(GRANDE.VALEUR(A1:A5; LIGNE(A1:A3)))</nowiki>'''
  
[[Image:Fr-calc-Sommeconditions7.png|example]]
+
[[Image:Fr-calc-Sommeconditions7.png|exemple]]
  
 
Ici <tt>'''LIGNE(A1:A3)'''</tt> est un tableau d'une colonne et 3 lignes contenant les nombres <tt>'''1'''</tt>, <tt>'''2'''</tt>, et <tt>'''3'''</tt>.
 
Ici <tt>'''LIGNE(A1:A3)'''</tt> est un tableau d'une colonne et 3 lignes contenant les nombres <tt>'''1'''</tt>, <tt>'''2'''</tt>, et <tt>'''3'''</tt>.
  
<tt>'''GRANDE.VALEUR(A1:A5; LIGNE(A1:A3))'''</tt> est un tableau d'une colonne et 3 lignes contenant les plus grands nombres et <tt>'''SOMMEPROD'''</tt> fait juste la somme des 3. On pourrait aussi utiliser <tt>'''SOMME'''</tt> à la place de <tt>'''SOMMEPROD'''</tt> mais dans ce cas la formule doit être saisie comme un tableau de cellule en en tapant Ctrl+Maj+Entrée.
+
<tt>'''GRANDE.VALEUR(A1:A5; LIGNE(A1:A3))'''</tt> est un tableau d'une colonne et 3 lignes contenant les plus grands nombres et <tt>'''SOMMEPROD'''</tt> fait juste la somme des 3. On pourrait aussi utiliser <tt>'''SOMME'''</tt> à la place de <tt>'''SOMMEPROD'''</tt> mais dans ce cas la formule doit être saisie comme une une formule de matrice en en tapant Ctrl+Maj+Entrée.
  
  
Line 163: Line 160:
 
Pour additionner les plus petits nombres, utilisez <tt>'''PETITE.VALEUR(...)'''</tt> au lieu de <tt>'''GRANDE.VALEUR(...)'''</tt>.
 
Pour additionner les plus petits nombres, utilisez <tt>'''PETITE.VALEUR(...)'''</tt> au lieu de <tt>'''GRANDE.VALEUR(...)'''</tt>.
  
==Trucs et Astuces: Faire la somme de cellules correspondant à d'autres cellules vides==
+
==Trucs et Astuces : Faire la somme de cellules correspondant à d'autres cellules vides==
  
 
  '''<nowiki>=SOMMEPROD(ESTVIDE(A1:A5); B1:B5)</nowiki>'''
 
  '''<nowiki>=SOMMEPROD(ESTVIDE(A1:A5); B1:B5)</nowiki>'''
Line 171: Line 168:
 
D'autre fonctions similaires (par exemple <tt>'''ESTTEXTE'''</tt>, <tt>'''ESTNUM'''</tt>) peuvent être utilisées de la même façon.
 
D'autre fonctions similaires (par exemple <tt>'''ESTTEXTE'''</tt>, <tt>'''ESTNUM'''</tt>) peuvent être utilisées de la même façon.
  
==Trucs et Astuces: Faire la somme de plus d'une colonne==
+
==Trucs et Astuces : Faire la somme de plus d'une colonne==
  
 
  '''<nowiki>=SOMMEPROD(D1:D6="rouge";E1:E6+F1:F6)</nowiki>'''
 
  '''<nowiki>=SOMMEPROD(D1:D6="rouge";E1:E6+F1:F6)</nowiki>'''
Line 177: Line 174:
 
Fera la somme des cellules <tt>'''E1:E6'''</tt> et <tt>'''F1:F6'''</tt> qui correspondent aux cellules dans <tt>'''D1:D6'''</tt> contenant <tt>'''rouge'''</tt>. Par exemple si <tt>'''D2'''</tt> et <tt>'''D4'''</tt> contiennent rouge, le résultat est <tt>'''E2+F2+E4+F4'''</tt>.
 
Fera la somme des cellules <tt>'''E1:E6'''</tt> et <tt>'''F1:F6'''</tt> qui correspondent aux cellules dans <tt>'''D1:D6'''</tt> contenant <tt>'''rouge'''</tt>. Par exemple si <tt>'''D2'''</tt> et <tt>'''D4'''</tt> contiennent rouge, le résultat est <tt>'''E2+F2+E4+F4'''</tt>.
  
==Trucs et Astuces: Faire la somme chaque 'n' lignes==
+
==Trucs et Astuces : Faire la somme chaque 'n' lignes==
  
 
  '''<nowiki>=SOMMEPROD(MOD(LIGNE(A1:A8); 2)=0; </nowiki> A1:A8)'''
 
  '''<nowiki>=SOMMEPROD(MOD(LIGNE(A1:A8); 2)=0; </nowiki> A1:A8)'''
Line 185: Line 182:
 
[[Image:Fr-calc-Sommeconditions8.png|exemple]]
 
[[Image:Fr-calc-Sommeconditions8.png|exemple]]
  
==Trucs et Astuces: Faire la somme d'objets respectant un certains formatage==
+
==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 fonte d'une cellule, si aucune formule relative à la couleur ou à la fonte n'existe, il sera nécessaire de faire appel à une macro.
+
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é==
+
==Trucs et Astuces : Faire la somme d'objet similaires dans une liste séparée==
  
Disons que <tt>'''A1:A9'''</tt> contient une liste de dates, <tt>'''B1:B9'''</tt> contient des numéros de téléphones, et <tt>'''C1:C9'''</tt> le coût de ces appels.<tt>'''F1:F5'''</tt> est une liste de certains numéros , et vous voulez établir les coûts d'appels à ces numéros.
+
Disons que <tt>'''A1:A9'''</tt> contient une liste de dates, <tt>'''B1:B9'''</tt> contient des numéros de téléphones, et <tt>'''C1:C9'''</tt> le coût de ces appels. <tt>'''F1:F5'''</tt> est une liste de certains numéros et vous voulez établir les coûts d'appels à ces numéros.
  
  
Dans la cellule <tt>'''D1'''</tt> entrez:
+
Dans la cellule <tt>'''D1'''</tt> saisissez :
  
 
  '''<nowiki>=ESTNUM(EQUIV(B1; F$1:F$5; 0))*C1</nowiki>'''
 
  '''<nowiki>=ESTNUM(EQUIV(B1; F$1:F$5; 0))*C1</nowiki>'''
Line 204: Line 201:
 
[[Image:Fr-calc-Sommeconditions9.png|exemple]]
 
[[Image:Fr-calc-Sommeconditions9.png|exemple]]
  
Pour faire le calcul sans colonne supplémentaire , on peut utiliser :
+
Pour faire le calcul sans colonne supplémentaire, on peut utiliser :
  
 
  <nowiki>=SOMMEPROD(NON(ESTERREUR(EQUIV(B1:B9; F$1:F$5; 0))); C1:C9)</nowiki>
 
  <nowiki>=SOMMEPROD(NON(ESTERREUR(EQUIV(B1:B9; F$1:F$5; 0))); C1:C9)</nowiki>
  
Nous avons vu plus haut qu'il est important de vérifier le 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.
+
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.
  
 
{{PDL1}}
 
{{PDL1}}
[[Category: Documentation]] [[Category: Documentation/FR]]
+
[[Category: FR/Documentation/Calc|Sommes et calculs conditionnels]]  
 
[[en:Documentation/How_Tos/Conditional_Counting_and_Summation]]
 
[[en:Documentation/How_Tos/Conditional_Counting_and_Summation]]

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.

exemple

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.

exemple

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 + ...

example

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.

Paramétrages

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")

Vérification des paramètres

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.

exemple

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)))

exemple

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........

exemple

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.

exemple

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.

Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages