Découvrir SommeProd: Compteur et sommes conditionnelles

Venez découvrir tous les tutoriels, modèles et autres foires aux questions afin de maîtriser rapidement votre suite bureautique favorite.

Modérateur : Vilains modOOs

Règles du forum
Aucune question dans cette section !
Cette section est faite pour présenter les tutoriels. Si vous avez une question sur l'installation, le fonctionnement ou l'utilisation, vous devez poster dans la section du module où se produit le problème.
Répondre
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

Découvrir SommeProd: Compteur et sommes conditionnelles

Message par Papayes »

Bonjour,
Jean-Yves LUCCA alias papayes a écrit :Ce tutoriel est sous licence GNU F.D.L. (Licence de Documentation Libre GNU)
en français : http://cesarx.free.fr/gfdlf.html, officiel : http://www.gnu.org/licenses/licenses.html#GPL
Vous êtes libre de le reproduire, le recopier, le réutiliser, l'améliorer, le modifier, le distribuer à condition de lui attribuer les mêmes libertés et de citer son origine : ForumOpenOffice
Il met à jour l'édition du 8 novembre 2005 "Réaliser des sommes conditionnelles" placée à l'époque sur Framasoft
.
Sommes Conditionnelles
Utilisation de la Fonction SOMMEPROD()
en remplacement de la fonction compteur : NB.SI et de la fonction SOMME.SI


Objectifs :
Extraire avec une seule formule des données d’un tableur suivant une ou plusieurs conditions.
Compter le nombre de fois qu’une donnée répond à une ou plusieurs conditions.
Réaliser des sommes sous un ou plusieurs critères.

Méthode :
Suivre un cheminement pédagogique afin de faire découvrir et employer une fonction qui en 2005 n’était pratiquement pas documentée.
Rappeler (rapidement) la fonction SOMME() ou SOMME.SI() et son emploi comme formule matricielle, afin de mieux comprendre la fonction SOMMEPROD() pour analyser son fonctionnement au travers d’exemples divers.

Plan du tutoriel
I. Rappel sur la fonction SOMME Aller au message
  1. Désactiver l'AutoSaisie
  2. Problème posé.
II. Somme sous une condition Aller au message
  • SOMME.SI() pour information
III. Nommer les plages Aller au message
  1. Copier-glisser des formules
  2. Références relatives ou absolues
  3. Nommer pour faciliter l'écriture et la lecture des formules.

IV. Notion de matrice appliquée à la fonction SOMME Aller au message
  1. Définition d'une matrice
  2. Définition d'une formule de matrice
  3. Exemple de la fonction matricielle de SOMME
SOMMEPROD()

V. Découverte de SOMMEPROD Aller au message
  1. Ne pas utiliser l'Assistant fonction ;
  2. Validation par la Touche Entrée simplement ;
  3. Fonction Compteur sous une seule condition ;
  4. Utilisation de la syntaxe SOMMEPROD(matrice1=critère;matrice2) ;
  5. Utilisation de la syntaxe SOMMEPROD((matrice1=critère)*matrice2 à sommer) ;
  6. Sommer sous plusieurs conditions.
VI. Compter deux ou plusieurs colonnes sous une seule condition Aller au message
  1. Exemple de compteur sur 2 colonnes ;
  2. Exemple sur une semaine ouvrée ;
  3. Exemple sur des semaines ouvrées, les jours entrés sous forme de date.
VII. Compteur et Somme de produits scalaires. Aller au message
  1. Compteur sous une condition ;
  2. Compteur sous deux conditions ;
  3. Extraction de données : Compteur appliqué à une plage à sommer.
VIII. Traitement des valeurs numériques.Aller au message
  1. Comparaison avec une seule valeur numérique( > n ou >= n) ;
  2. Comparaison avec une valeur et un texte ;
  3. Encadrement de 2 valeurs n1<x<n2 ;
  4. Raisonnement soustractif x > n1 mais x < n2 avec n1>n2.
IX. Autres extractions d'une même plage : les sommes matricielles Aller au message
  1. Extraction avec des valeurs discontinues ;
  2. Extraction avec des critères "texte".
X. Traitement des chaînes de caractères -1 Aller au message
  1. Fonctions GAUCHE(texte;nombre) ;
  2. Fonction DROITE(texte;nombre).
XI. Traitement des chaines de caractères -2 Aller au message
  1. Statistiques de Rentrée d'une École élémentaire de 11 classes ;
  2. Utilisation de NBCAR(texte) comme "nombre" dans la fonction GAUCHE(texte;nombre) ;
  3. Statistiques de Rentrée pour des Classes à plusieurs niveaux.
XII. Exemples d'extractions diverses par l'utilisation des fonctions "Date" Aller au message
  1. Extraction de données issues d'un tableau de journées ouvrées et datées ;
    • Exclusion d'un même jour sur une période : Fonction JOURSEM() ;
      Exclusion de plusieurs jours sur une période.
  2. Extraction d'un mois : Fonction MOIS() ;
  3. Extraction d'une semaine : Fonction NO.SEMAINE ;
  4. Complexité de certaines formules.

suite ➫
"Tout ce qui n'est pas donné est perdu"
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

I. Rappel : la fonction SOMME

Message par Papayes »

I. Rappel sur la fonction SOMME
SOMME sous une seule condition


I. La fonction Somme : le B.A.-BA
  1. Désactivez la complémentation automatique ou AutoSaisie
    Lorsqu'il s'agit d'écrire dans le Champ de saisie de la Barre de formule,
    obtenu par le raccourci clavier F2,
    la complémentation automatique des textes ou des nombres peut être très gênante.
    Je vous conseille de la désactiver.
    Menu> Outils> Contenu des cellules > AutoSaisie.
  2. Des Sommes à calculer : Le problème posé

    Calculer pour une journée, le nombre d'heures effectuées
    • en catégorie Prestataire (P),
    • en catégorie Mandataire (M)
    • et le Total des heures ainsi effectuées.
    Pour ce B.A.-BA, (P), (M) sont fixes.
    Seules les heures sont variables dans le respect des conventions collectives :wink:
    • SommeSimple1.png
    1. Somme de cellules non adjacentes
      La fonction SOMME est utilisée pour l'affichage dans une cellule choisie de la somme de plusieurs cellules.
      Sa syntaxe est :
      =SOMME(Nombre1;Nombre2;...)
      Nombre1 à Nombre30 représentent de 1 à 30 arguments dont la somme est à calculer.
      Procédure :
      • Comme les heures (P) ne sont pas adjacentes, écrire dans la cellule C6 où doit apparaître le résultat : =SOMME(
        Sélectionner ensuite à la souris la cellule C2 taper ; sélectionner ensuite à la souris la cellule C4
        fermer la parenthèse et valider avec la touche Entrée
        =SOMME(C2;C4)
        Remarque : On peut écrire aussi : =SOMME(C2+C4) ou plus simplement =C2+C4
    2. Somme de cellules adjacentes ou "Plage de cellules"
      =SOMME(Plage)
      Plage représente toutes les cellules adjacentes figurant entre deux cellules extrêmes séparées par deux points :
      Image
      Procédure :
      • 1.Comme les heures en M sont uniques, faites un lien entre C7 et C3 en tapant en C7 : =C3 et valider avec la touche Entrée.
        2. La somme Total des heures peut être calculée avec les deux cellules adjacentes C6 et C7
        Cliquer sur C8,
        Cliquer sur le bouton Grand Sigma de la barre de formule
        Image
        La plage de cellule C6:C7 s'affiche en contrasté entre parenthèses dans la formule
        =SOMME(C6:C7)
        Il suffit de valider avec la touche Entrée.
SommeSimple.ods
Exercice
(10.7 Kio) Téléchargé 655 fois
➫ à suivre
"Tout ce qui n'est pas donné est perdu"
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

La Fonction SOMME.SI()

Message par Papayes »

II. Somme sous une condition
SOMME.SI() pour information.


Objectif : Utiliser une formule tenant compte d'une condition

Exercice :
Calculer pour une journée, le nombre d'heures effectuées
  1. par la catégorie Prestataire (P)
  2. par la catégorie Mandataire (M)
  3. et le Total des d'heures.
P et M sont variables mais ne peuvent être triées car elles doivent appartenir à un document complexe à imprimer.

La fonction =SOMME.SI()
Cette fonction permet d'additionner plusieurs cellules en fonction d'un critère (ou condition) déterminé.
Sa syntaxe est:
=SOMME.SI(plage;critère;plage_somme)
  • plage est la plage de cellules contenant le critère à retenir
  • critère est LE critère à retenir (chiffre ou texte)
  • plage_somme est la plage de cellules où se trouvent les nombres à additionner. Je préfère l'expression "plage à sommer".
  1. Pour les Heures de Prestataires en C6
    • Ne sommer la plage des heures que si la plage "Catégorie" contient "P"
      donc la plage sous condition est celle de la "Catégorie" soit B2:B5
      le critère est que la cellule contienne "P" soit "P" avec des double apostrophes puisque c'est un caractère alphabétique (ou chaîne de caractères)
      plage_somme est la plage des heures à sommer soit C2:C5

      Remarque importante : Les deux plages doivent comporter le même nombre de cellules.

      La formule est :
      =SOMME.SI(B2:B5;"P";C2:C5)
      SommeSICalcCouleur1.png
    • Utilisation de l'Assistant Fonctions (facultatif)
      En cliquant sur l'icône Assistant fonction de la Barre de formule (F2)
      Image
      La boite de dialogue Assistant Fonction apparaît :
      Assistant Fonction SommeSI1.png
      Sous l'onglet Fonctions, dans les rubriques
      Catégorie, choisir Mathématique dans la liste déroulante.
      Fonction choisir SOMME.SI dans la liste déroulante.

      Cliquer sur le bouton >>Suivant,
      Assistant Fonction SommeSI2.png
      Remplir successivement les 3 champs plage ,critères et plage_somme)
      • Cas d'un grand écran : Déplacer légèrement la boite de dialogue pour pouvoir sélectionner simplement les plages à l'aide de la souris.
      • Sinon, utiliser l'aide des icônes-boutons Sélectionner pour remplir les 2 champs relatifs aux plages
        • Utilisation de Assistant fonction.png
      La formule s'affiche =SOMME.SI(B2:B5;"P";C2:C5)
      et lorsque une valeur dans le champ Résultat est affichée (ici 4), cliquer sur OK
  2. Pour les heures de Mandataires en C7
    • Le raisonnement est le même.
      Ne sommer la plage des heures que si la plage "Catégorie" contient "M"
      • plage sous condition est celle de "Catégorie" soit toujours B2:B5
        critère est que la cellule contienne M soit "M"
        plage_somme ou plage à sommer est la plage des heures à sommer C2:C5
      la formule est donc
      =SOMME.SI(B2:B5;"M";C2:C5)
      Image
    • Si vous utilisez de nouveau l'Assistant Fonction, désormais dans la boite de dialogue Assistant Fonctions,
      Sous l'onglet Fonctions, choisir dans la rubrique Catégorie: Les dernières utilisées Il faut remonter l'ascenseur au plus haut à chaque fois, là pas d'amélioration depuis la version 2
      :idea: mais il suffit plus simplement de taper dans le cadre Catégorie sélectionné : dd
      puis choisir la Fonction SOMME.SI etc.
  3. Le total des heures
    • tout à fait normalement :
      =SOMME(C6:C7)
    • En final :
      Image
SommeSi.ods
Exercice
(10.18 Kio) Téléchargé 593 fois
➫ à suivre
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

III. Utiliser les NOMS de plage

Message par Papayes »

III. Conseils d'utilisation : Utiliser les NOMS de plage

Rappel ou initiation :
A. Copier-Glisser des formules
Pour recopier une formule vers le bas ou vers la droite :
Sélectionner la cellule à copier. Le coin inférieur droit arbore un petit carré noir...
C'est une poignée. Placé sur la poignée, le curseur se transforme en croix +
Cliquer sur la poignée et faire glisser jusqu'à la cellule de fin de calcul. Valider par Entrée.

Le résultat est parfois étonnant, même avec une formule simple...
  • Image
B. Références relatives ou absolues
La plage C1:C3 est composée des références relatives des cellules :
Elle s'adapte automatiquement dès que l'on copie les formules.

Par opposition, la plage composée de référence absolue $C$1:$C$3
est utilisée lorsqu'un calcul fait référence à des cellules spécifiques de la feuille
qui ne doivent pas être adaptées lors de la copie de formule.


Sur une référence d'une cellule C1
$C1 La lettre de la colonne C ne changera pas, le numéro de la ligne s'adaptera,
C$1 Le numéro de la ligne 1 ne changera pas mais la lettre de la colonne s'adaptera,
$C$1 Ni la lettre de la colonne C ni le numéro de la ligne 1 ne seront modifiées.

:idea: Pour convertir la référence affichée dans la ligne de saisie, de relative en absolue et vice versa : il suffit d'appuyer simultanément sur Maj+F4.
Exemple de l'adresse relative C1, le curseur entre C et 1:
  • la première fois que vous appuyez sur ces deux touches, ligne et colonne deviennent absolues $C$1 ;
  • la deuxième fois, seule la ligne devient absolue C$1 ;
  • la troisième fois, uniquement la colonne $C1,
  • et une quatrième fois rend la référence relative dans les deux sens C1.

C. Nommer les plages pour mieux visualiser

Pour éviter d'utiliser les références absolues, lors des glisser-copier de formules,
il est préférable de donner un NOM aux différentes cellules
ou aux plages de cellules afin de faciliter la lecture des formules.

Menu>Insertion>Noms > Définir... (ou Ctrl+F3 )
Menu_Inertion_Noms_Définir.png
Je conseille d'utiliser systématiquement les Noms des plages afin de mieux visualiser les formules même si je donne dans ce tutoriel également les formules avec les plages comme "B2:B5".
  1. Définir le premier Nom
    Sélectionnez la plage désirée B2:B5
    Dans la boite de dialogue Définir des noms,
    Image
    taper le nom désiré : Catégorie (1)
    Le programme affiche dans le champ Assigné à le nom de la feuille de calcul et automatiquement la plage avec ses références absolues.
    Cliquer sur le bouton Ajouter (2)
  2. Définir un autre Nom
    Dans la boite de dialogue Définir des noms,
    Image
    taper le Nom désiré : Heures (1)

    Déplacer la boite de dialogue pour avoir accès au classeur
    Cliquez dans le champ Assigné à (2)
    Avec la souris, sélectionner simplement la plage des cellules à nommer : C2:C5
    le champ Assigné à se remplit
    Validez avec le bouton Ajouter (3)pour définir un autre nom ou OK pour terminer.
Il sera toujours possible de remplacer un nom
ou de modifier son assignation par la même boite d'outils "Définir un nom".
Voici la feuillet avec les formules aux plages nommées.
  • Image
Ouvrez le navigateur dans la barre d'outils Standard ou par F5
pour visionner les Noms de plage qui ont été définis.
SommeSiNommée.ods
(10.15 Kio) Téléchargé 619 fois
➫ à suivre
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

IV. Introduction aux formules matricielles

Message par Papayes »

IV. Notion de matrice appliquée à la fonction SOMME
  1. Définition d'une matrice
    Dans une feuille de calcul, une matrice est une plage de cellules liées contenant des valeurs.

    Une plage de cellules comme B2:B5 est une matrice notée {B2:B5}
    La plage C2:C5 des heures en est une autre, notée {C2:C5}
    Une plage carrée composée de 3 lignes et de 3 colonnes est une matrice 3 x 3
    Image
    La matrice la plus petite qui puisse exister est la matrice 1 x 2 ou 2 x 1. Elle est constituée de deux cellules adjacentes.
  2. Définition d'une formule de matrice

    Il s'agit d'une formule permettant d'évaluer les différentes valeurs d'une plage de cellules.
    Mais contrairement à une formule simple, elle est capable
    • de traiter plusieurs valeurs à la fois,
    • de renvoyer plusieurs valeurs.
    Le résultat d'une formule de matrice est une autre matrice.
  3. Exemple de la fonction matricielle SOMME appliquée au même problème

    La syntaxe de cette fonction est:
    SOMME((matrice=critère)*matrice à sommer)

    La fonction matricielle permet de comparer la matrice "Catégorie" {B2:B5} avec la présence d'une donnée par exemple "P".
    Explicatif :
    • Le critère "P" appartient-il à {B2:B5} est appliqué à chaque cellule
      par la formule logique =D2="P", =D3="P", etc. appliquée en D.
      Le résultat de cette comparaison est "VRAI" si le critère est satisfait et FAUX s'il ne l'est pas.
      Avec la même formule logique =(D2="P)*1, la multiplication par 1 permet d'afficher directement 1 pour "VRAI" et 0 pour "FAUX"
      Les résultats de cette comparaison forment une matrice temporaire {D2:D5} qui est utilisée dans une multiplication avec la matrice des Heures {C2:C5}

      Image
      Ce produit scalaire forme la matrice résultat de la multiplication {G2:G5}.
      La somme de ces valeurs donne le nombre d'heures recherchées (4).
    SOMME((matrice=critère)*matrice à sommer)

    1. Pour les heures de Prestataires en C6,
    la formule devient : SOMME((matrice Catégorie="P")*matrice des Heures)
    Taper en C6 la formule entièrement à la main en faisant attention à la double parenthèse (( après SOMME qui est imposée par la parenthèse fermante ) qui sépare la quote " du signe *

    =SOMME((Catégorie="P")*Heures)
    ou =SOMME((B2:B5="P")*C2:C5)
    Pour que la formule soit bien interprétée comme une formule de matrice, vous devez la fermer en appuyant sur les touches Maj +Ctrl+Entrée
    Image


    2. Pour les heures de Mandataires en C7
    la formule devient : SOMME((matrice de Catégorie="M")*matrice des Heures)
    Taper en C7 la formule entièrement à la main en faisant très attention aux parenthèse et aux quotes ".

    =SOMME((Catégorie="M")*Heures)
    ou =SOMME((B2:B5="M")*C2:C5)
    Valider par Maj + Ctrl+ Entrée

    Image

    Si vous vous trompez lors d'une frappe dans le champ de la barre de formule, effacez et recommencez.
    Si vous avez le message #VALEUR dans la cellule, touche Supp et supprimer tout avant de refrapper.

    L'édition et la modification des formules matricielles est assez complexe :
    Aide F1 a écrit :Édition de formules de matrice
    1. Sélectionnez la plage de cellules ou la matrice contenant la formule de matrice. Pour sélectionner la matrice entière, positionnez le curseur de cellule à l'intérieur de la plage de matrice, puis appuyez sur Ctrl+ /, où / est la touche de division du clavier numérique.
    2. Appuyez sur F2 ou placez le curseur sur la ligne de saisie. Ces deux actions permettent de modifier la formule.
    3. Après avoir effectué des modifications, appuyez sur Ctrl+Maj+Entrée
    Ce n'est jamais évident ce qui explique mon conseil ci-dessus.

    Attention à l'endroit où se trouve votre curseur quand vous cliquez lorsque vous êtes en train d'écrire une formule dans le Champs de saisie de la Barre des formules.
    Toute écriture manuelle est un exercice d'attention sur les quotes et le nombre de parenthèses.
    Avec les fonctions matricielles, le réflexe du Maj +Ctrl+Entrée s'impose.

    Le logiciel rajoute automatiquement les { } pour indiquer que la formule est matricielle , n'essayez pas d'écrire manuellement les { }.
Fichier exemple :
SommeMatriceConditionelle2.ods
(12.07 Kio) Téléchargé 765 fois


Heureusement que la fonction SOMMEPROD échappe à ces {} et la validation se fait par la seule touche Entrée

à suivre ➫
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

V. Découvrir SommeProd

Message par Papayes »

Découverte de la fonction =SOMMEPROD()

Objectif :
Découvrir la fonction SOMMEPROD() appliquée à ce problème à une seule condition.


A. Ne pas utiliser l'Assistant de fonction

➫ La fonction SOMMEPROD appartient à la Catégorie Matrice.

Comme telle, elle fonctionne avec la syntaxe SOMMEPROD(matrice1;matrice2;...;matrice30)

Elle calcule la somme du produit des différentes matrices : SOMME(matrice1 x matrice2 x ...x matrice30)
d'où l'origine de son nom.
  1. Exemple sur 2 matrices :
    Image
  2. Autre exemple d'application : avec cette syntaxe, elle permet de calculer le produit scalaire de deux vecteurs.
    • Image
    Produitvectoriel2.png
Remarque 1. La liste d'arguments doit contenir au moins une matrice.
Si une seule matrice figure en argument,
SOMMEPROD(matrice1),
tous les éléments de la matrice sont additionnés.


Remarque 2. SOMMEPROD renvoie un seul nombre. Il n'est donc pas nécessaire de saisir la fonction sous forme de fonction de matrice.
L'utilisation de SOMMEPROD avec des attributs conditionnels est différent et ne permet pas d'utiliser l'Assistant de fonction
SommeProdSimple1.ods
(34.85 Kio) Téléchargé 883 fois
B. Fonction conditionnelle de SOMMEPROD()

Pour montrer l'analogie avec SOMME() matricielle
=SOMMEPROD(plage=critère;somme_plage)
ou
=SOMMEPROD((plage=critère)*somme_plage)

plage est la plage de cellule contenant le critère à retenir
critère est LE critère à retenir (chiffre ou texte)
somme_plage est la plage de cellule où se trouvent les nombres à additionner.
  • Autres syntaxes pour les mémoriser :
    Je préfère en fait cette syntaxe très personnelle plus mnémotechnique :
    =SOMMEPROD(matrice=critère;matrice à sommer)
    ou
    =SOMMEPROD(plage=critère;plage à sommer)

    et encore davantage celles-ci équivalentes avec la multiplication * qui sont les plus utiles :
    =SOMMEPROD((matrice=critère)*matrice à sommer)
    =SOMMEPROD((plage=critère)*plage à sommer)

    car le raisonnement s'inspire du produit scalaire : la matrice sous condition est comparée au critère, cellule après cellule, avec comme résultat soit 1 (VRAI) soit 0 (FAUX) contenu dans une matrice temporaire qui est ensuite multipliée par la matrice des valeurs à additionner.

C. Validation par touche Entrée
OpenOffice.org en version 1.1.4 ou 1.1.5 n'acceptait cette utilisation que si on la considérait "comme" une fonction matricielle et imposait la validation par Maj+Ctrl+Entrée. A partir de la version 2.0, SOMMEPROD est considérée comme une formule matricielle qui renvoie un nombre et
il suffit de taper simplement la touche Entrée pour valider.


D. Utilisation de la syntaxe Sommeprod(matrice1=critère;matrice2) (facultatif)
avec le signe point virgule ; entre les attributs.
Facultatif = "pour vous faire la main" sur l'écriture des formules matricielles.
  1. Pour les Heures de Prestataires en C6
    matrice sous condition est la plage de la Catégorie : B2:B5="P" ou Catégorie="P"
    matrice à sommer est la plage des Heures : C2:C5 ou Heures
    Tapez en C6,
    =SOMMEPROD(Catégorie="P";Heures)
    Validez par la touche Entrée
    Image
    Notez que le signe ; est un séparateur donc pas de parenthèse entre la quote " et la suite.
  2. E. Pour les Heures de Mandataires, en C7
    matrice sous condition est la plage de la Catégorie : B2:B5="M"
    matrice à sommer est la plage des Heures : C2:C5 ou Heures
    Deux possibilités d'écriture :
    1. =SOMMEPROD(Catégorie="M";Heures)
      Cliquez dans C6, faites un glisser-coller vers le bas pour remplir la cellule C7, et validez par Entrée.
      Dans le Champs de saisie de la Barre de formules, remplacez P par M et validez.
    2. =SOMMEPROD(B2:B5="M";C2:C5)
      Cliquez dans C7 et tapez votre formule.
    Image

E. Utilisation de la syntaxe: SOMMEPROD((Matrice sous condition)*matrice à sommer).
Avec le signe * : Cette écriture sera utilisée par la suite pour les conditions multiples.

=SOMMEPROD((plage=critère)*plage à sommer)

Mais attention aux deux parenthèses (( indispensables puisqu'il faut en mettre une pour séparer la deuxième quote " du signe *.
  1. Pour les Heures de Prestataires, en C6
    Tapez dans le Champ de saisie de la Barre de formule
    Attention aux deux parenthèses (( indispensables puisqu'il faut en mettre une ) pour séparer la deuxième quote " du signe *.
    =SOMMEPROD((Catégorie="P")*Heures)
    Image
  2. Pour les heures de Mandataires, en C7
    Deux possibilités d'écriture (pour vous faire la main) :
    1. =SOMMEPROD((Catégorie="M")*Heures)
      Cliquez C6, faites un glisser-coller vers le bas pour remplir la cellule C7, et valider par Entrée.
      Dans le Champs de saisie de la Barre de formule, remplacez P par M et Validez.
    2. =SOMMEPROD((B2:B5="M")*C2:C5)
      Cliquer dans C7 et tapez votre formule.
    SommeProd2MetTotal.png
SommeProd1.ods
(13.76 Kio) Téléchargé 784 fois
à suivre ➫
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

VI. Sommer des colonnes sous une condition

Message par Papayes »

VI. Sommer deux ou plusieurs colonnes sous une condition

I. Premier exemple avec deux colonnes
  1. Objectif de l'exercice
    Sommer deux colonnes par exemple les heures de lundi et de mardi,
    la matrice à sommer doit être la somme des deux matrices des heures du lundi et du mardi.
    Elle s'écrit (matrice1 à sommer+matrice2 à sommer) entre parenthèses.
    La formule prend cette syntaxe :

    =SOMMEPROD((matrice=critère)*(matrice1 à sommer+matrice2 à sommer))
  2. Modification du Classeur de l'exemple
    • Modifiez votre classeur avec deux colonnes Lundi en C et Mardi en D
    • Nommez les 2 plages :
      Lundi pour la plage C2:C5 représentant la matrice1
      Mardi pour la plage D2:D5 représentant la matrice2
    Image
    et son fichier exercice :
    SP_Exercice_2colonnes.ods
    (11.45 Kio) Téléchargé 863 fois
    Avec ces nouveaux noms, écrivons les formules partielles comme dans le message précédant.
    Cela permettra de vérifier votre habileté et les résultats obtenus.
  3. Somme partielle par catégorie et par jour
    1. Pour les Prestataires du Lundi en C6,
      Taper la formule puis Valider par Entrée
      =SOMMEPROD((Catégorie="P")*Lundi)
    2. Pour les Mandataires du lundi en C7,
      Cliquer C6 et copier-glisser vers le bas en C7, Valider.
      Remplacer le P par le M. Valider
      =SOMMEPROD((Catégorie="M")*Lundi)
    3. Pour les Prestataires du mardi en D6,
      Cliquer. C6 et copier-glisser à droite en D6 et Valider.
      Remplacer Lundi par Mardi et Valider.
      =SOMMEPROD((Catégorie="P")*Mardi)
    4. Pour les Mandataires du mardi en D7,
      Cliquer D6 et copier-glisser vers le bas en D7 et Valider.
      Remplacer le P par le M. et Valider.
      =SOMMEPROD((Catégorie="P")*Mardi)
  4. Somme globale par catégorie
    Nous pouvons faire la somme totale des heures de lundi et mardi suivant les catégories sans utiliser les résultats partiels:
    Pour le Total Prestataire en C10,
    la forme est SOMMEPROD((matrice catégorie="P")*(matrice des heures du lundi + matrice des heures du mardi)) et la formule est
    =SOMMEPROD((Catégorie="P")*(Heureslundi+heuresmardi))
    Pour le Total Mandataire en C11,
    Cliquer C10, copier-glisser vers le bas en C11 et Valider
    Changer le P en M et Valider.
    =SOMMEPROD((Catégorie="M")*(Heureslundi+heuresmardi))
    SPLundietMardi.png
    et le fichier exemple :
    SPSemaine1.png

II. Deuxième exemple appliqué à une semaine ouvrée
  1. Objectif :
    Après cette première initiation, continuons à nous exercer sur un cas plus vraisemblable,
    mais en se forçant à appliquer toutes les formules intermédiaires pour s'entraîner,
    l'utilisation des collages évitant de rendre l'exercice trop fastidieux.
    SP_Exemple_2colonnes.ods
    (12.44 Kio) Téléchargé 772 fois
    et le fichier exercice (uniquement les données, les plages sont également à nommer) correspondant :
    SPSemaineExercice0.ods
    (14.38 Kio) Téléchargé 644 fois
  2. Exercice :
    1. Plages (Matrices) à nommer :
      Catégorie : B2:B5
      Semaine : C2:H5
      1. Méthode longue consistant à nommer tous les jours de la semaine :
        Lundi : C2:C5, Mardi: D2:D5, Mercredi : E2:E5, Jeudi : F2:F5, Vendredi : G2:G5 et Samedi : H2:H5

        ➫ Formules à appliquer en C6 (Lundi)
        =SOMMEPROD((Catégorie="P")*Lundi)
        puis faites un recopiage vers la droite jusqu'à Samedi H6 et modifiez les noms de jour dans les formules pour chaque jour,
        exemple pour Samedi en H6 =SOMMEPROD((Catégorie="M")*Samedi)
        Faites ensuite un recopiage vers le bas de C6:H6,et modifiez le P en M.

        ➫ Formule à appliquer en C10 (Heures des Prestataires pour la semaine)
        =SOMMEPROD((Catégorie="P")*(Lundi+Mardi+Mercredi+Jeudi+Vendredi+Samedi))

        ➫ Formule à appliquer en C11 (Heures des Mandataires pour la semaine) par collage de la précédente et modification du P en M
        =SOMMEPROD((Catégorie="M")*(Lundi+Mardi+Mercredi+Jeudi+Vendredi+Samedi))
      2. La méthode la plus rapide, dans ce cas, est de ne pas définir les Noms des différents jours mais simplement de remplir les plages dans les formules.

        ➫ Formules à appliquer en C6 et C7 (Lundi)
        =SOMMEPROD((Catégorie="P")*C2:C5)
        =SOMMEPROD((Catégorie="M")*C2:C5)

        et de faire un recopiage de ces 2 cellules jusqu'au Samedi.
        Les résultats journaliers sont les mêmes...

        La formule avec la matrice Semaine C2:H5 donnera heureusement le même résultat...
    2. Formule globale justifiant pleinement l'utilisation de SOMMEPROD :
      ➫ Formule à appliquer en E10 (Heures des Prestataires pour la semaine)
      =SOMMEPROD((Catégorie="P")*Semaine)

      ➫ Formule à appliquer en E11 (Heures des Mandataires pour la semaine) par collage de la précédente et modification du P en M
      =SOMMEPROD((Catégorie="M")*Semaine)
      SPSemaine2.png
      et le fichier final.
      SP_Semaine_Exemple.ods
      (12.75 Kio) Téléchargé 670 fois

III.Troisième exemple d'un tableur avec des semaines ouvrées rentrées sous forme de date.
  1. Objectif
    Encore plus proche de la réalité, un tableur avec le même problème mais avec les jours rentrés sous forme de date.
    Les dates, rentrées comme 1/04/2013, sont formatées NNNNJ/MM afin de faire apparaître le nom des jours.
    SP_Exercice_2SemainesDate.png
    et son fichier exercice :
    SP_Exercice_2Semaines__Date.ods
    (15.27 Kio) Téléchargé 613 fois
  2. Exercice
    Matrice à nommer
    Catégorie : B2:B5
    Données : C2:O5 en fait celles que vous désirez sommer,
    ou C2:N5 pour ne pas commencer la troisième semaine...

    Formules à appliquer en C6 et en C7
    =SOMMEPROD((Catégorie="P")*C2:C5) puis copiage à droite
    =SOMMEPROD((Catégorie="M")*C2:C5) puis copiage à droite
    même procédure que l'exercice précédent sans utiliser de nom des jours.

    Formules globales à appliquer en C10 et C11
    =SOMMEPROD((Catégorie="P")*Données)
    =SOMMEPROD((Catégorie="M")*Données)
    SP_Exemple_2SemainesDate.png
    et son fichier exemple (final)
    SP_Exemple_2Semaines__Date.ods
    (16.73 Kio) Téléchargé 599 fois
  3. L'intérêt de SOMMEPROD est évident dans le calcul global sur l'ensemble des données pour chaque catégorie.
    Nous retrouverons en fin de tutoriel le traitement d'une période sur plusieurs semaines
    avec la possibilité d'exclure certains jours ou même d'un mois.
Mais continuons à bien comprendre cette étonnante fonction.
➫ à suivre
Pièces jointes
SP_Semaine_Exemple.ods
(12.75 Kio) Téléchargé 607 fois
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

VII. Compteur, Produit scalaire et Somme

Message par Papayes »

Comprendre la Fonction SOMMEPROD, attribut par attribut
VII. Compteur, Produit scalaire et Somme


Objectifs :
➫ Appliquer une formule si riche en possibilités sans bien comprendre son fonctionnement,
c'est se promettre à des messages #ERREUR qui vous feront perdre beaucoup de temps
sauf à aller proposer votre fichier sur le forum !

➫ Utilisons à la fonction SOMMEPROD() avec un autre exemple pour décomposer son fonctionnement,
  1. en analysant son utilisation comme Compteur
    équivalent à la fonction NB.SI() qui donne le nombre d'éléments répondant à un critère,
  2. comme Compteur à plusieurs critères,
  3. puis en lui appliquant une plage à sommer.
Tableau de travail
SP_Floc_AntibesMoisBlanc.png
SP_Floc_1.png
Le Floc est un apéritif à base d'Armagnac et de jus de raisin non fermenté,
spécialité gasconne comme l'est le Ratafia en Aveyron. À consommer avec modération.

Exercice d'application

Nommez les plages car l'exemple s'y prête, même si je donne la formule avec les plages.
Rappel : Ctrl+F3 ou Menu : Insertion > Noms > Définir
  • Client : A2:A16
    Mois : B2:B16
    Blanc : C2:C16
    Rouge : D2:D16
I. Utiliser SOMMEPROD comme Compteur sous une condition
  1. Une seule condition : Trouver le nombre de fois que la "Cave Antibes" est citée dans la liste "Client"

    Appliquons la formule avec son seul premier attribut :

    =SOMMEPROD(plage=critère)

    Le Critère est que la "Cave Antibes" figure dans la plage "Client".
    =SOMMEPROD(Client="Cave Antibes") ou =SOMMEPROD(B2:B16="Cave Antibes")
    La fonction renvoie 3
    SP_Floc_Compteur1ET2.ods
    Fichier exemple (Exercice en Feuillet1) et les feuilles suivantes explicatives
    (16.71 Kio) Téléchargé 838 fois
    • SP_Floc_AntibesMoisBlanc.png
      Explicatif : Elle compare chaque cellule de la plage "Client" à "Cave Antibes"
      Le résultat de la comparaison est "VRAI" si le critère est satisfait, et "FAUX" s'il ne l'est pas.
      Les valeurs de {1} sont obtenues avec la formule logique =(A2="Cave Antibes")*1
      La multiplication par 1 renvoie 1 si "VRAI" et 0 si "FAUX".
      On obtient une matrice temporaire : la plage "Résultat de la Condition" dont la Somme est 3.

      La fonction joue ici le même rôle que
      =NB.SI(Client;"Cave Antibes")
  2. Deuxième condition : Trouver le nombre de fois que le mois de Mars est cité.
    La formule est donc
    =SOMMEPROD(Client="Mars")
    la fonction renvoie 4
    • SP_Floc_Cave_Antibes.png
      Explicatif : En C, chaque cellule de la matrice Mois est comparée à "Mars"
      Le résultat de la comparaison est "VRAI" si le critère est satisfait, et "FAUX" s'il ne l'est pas.
      La multiplication par 1 de la formule logique affiche 1 pour "VRAI" et 0 pour "FAUX". La somme de la matrice « Résultat Condition Mars » est 4
      .
II. Comme Compteur à deux conditions

Trouver le nombre de fois que la double condition est remplie :
Nombre de ventes à la Cave Antibes ET en Mars.

=SOMMEPROD((Client="Cave Antibes")*(Mois="Mars")
SP_Floc_Mois.png
  • Explicatif : Le produit matriciel des 2 matrices résultats {1}*{2} (en multipliant cellule par cellule), donne une matrice E.
    Sa Somme donne 2.
=SOMMEPROD((Client="Cave Antibes")*(Mois="Mars")
La fonction renvoie bien 2

La syntaxe de SOMMEPROD, compteur sous 2 conditions est donc :
=SOMMEPROD((plage1=critère1)*(plage2=critère)


III. Le Compteur à deux conditions appliqué à une plage à sommer.

ou Extraire le nombre de données remplissant 2 conditions.

On applique le Compteur sous 2 conditions à la plage à sommer :
=SOMMEPROD((plage1=critère1)*(plage2=critère)*plage à sommer)
  • SP_Floc_AntibesMois.png
    Explicatif : La multiplication des deux matrices résultats {1}*{2} par le nombre de bouteilles de Floc Blanc, cellule par cellule, donne la matrice G
    La somme de la matrice G représente le nombre de bouteilles vendues en Mars à la Cave d'Antibes.
=SOMMEPROD((Client="Cave Antibes")*(Mois="Mars")*Blanc)
renvoie bien 84.
  •  Ajout : 
    • En Résumé : Syntaxe de SOMMEPROD()

      La syntaxe (toujours très personnelle) est :
      1. Comme compteur sous une condition
      =SOMMEPROD((plage=critère)

      2. Comme compteur sous des conditions multiples
      =SOMMEPROD((plage1=critère1)*(plage2=critère2)*(plage3=critère3)...

      3. Comme Somme sous conditions multiples
      =SOMMEPROD((plage1=critère1)*(plage2=critère2)*(plage3=critère3)*(plage à sommer)

      plage est la plage de cellule (ou la matrice) contenant le critère à retenir
      • (plage1 = plage2 = plage3 pour des extractions sous conditions dans une même colonne)
      critère1 est LE critère à retenir (chiffre ou texte) dans la même plage.
      plage à sommer est la plage de cellules (ou matrice) où se trouvent les nombres à additionner.
     


à suivre ➫
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

VIII. SommeProd et les valeurs numériques

Message par Papayes »

VIII. Travailler avec des valeurs numériques

Objectif :
Si le critère utilisé était jusqu'à présent un texte, et comme "qui peut le plus peut le moins" ,
traiter des valeurs numériques est aisé, les nombres étant toujours plus facile à traiter que du texte dans un tableur.
Le fichier de travail précédent va nous servir de base d'exercices.
Les critères sont des intervalles de valeurs, plus petit que, plus grand que ou compris entre.

I. Comparaison avec une seule valeur.

Compter le nombre de fois où les ventes de Floc Blanc sont supérieures ou égales à 36 bouteilles
  • SP_Floc_Encadrement2.png
    Explicatif : 36 est comparé à chaque cellule de la matrice "Blanc";
    Le résultat de la comparaison est 1 si le critère est satisfait, et 0 s'il ne l'est pas, donne une matrice en D.
    La somme de la matrice {1} « Résultat de laCondition >=36 {1} » est 8.

    La multiplication de {1} par le nombre de bouteilles de Floc blanc, cellule par cellule, se fait en F.
    La somme donne le nombre de bouteilles vendues sous cette condition.
La syntaxe du compteur SOMMEPROD est : =SOMMEPROD(plage=critère)
d'où
=SOMMEPROD(Blanc>=36)
ou =SOMMEPROD(C2:C16>=36)
et renvoie 8

La quantité de bouteilles de Floc blanc sous cette condition est le produit du compteur avec la plage à sommer "Blanc" :
=SOMMEPROD((plage=critère)*(plage à sommer)
d'où
=SOMMEPROD((Blanc>=36)*Blanc)
ou =SOMMEPROD((C2:C13>=36)*C2:C13)
et renvoie 388.


II. Comparaison avec une valeur et une seconde condition Texte

Compter le nombre de fois où les ventes de Floc blanc sont supérieures ou égales à 36 bouteilles
avec un deuxième critère Texte, comme celui durant le mois de "Mars" :
  • SP_Floc_Encadrement1.png

    Explicatif : La seconde condition crée la matrice {2} « Résultat Condition Mars {2}.
    La multiplication de {1} par {2}, cellule par cellule, donne la matrice en F
    La multiplication du résultat {1} *{2} par le nombre de bouteilles de blanc cellule par cellule se trouve en G .
    La somme de G donne le nombre de bouteilles vendues sous ces deux conditions.
Syntaxe de SOMMEPROD comme compteur sous 2 conditions : =SOMMEPROD((plage=critère1)*(plage=critère2)
=SOMMEPROD((Blanc>=36)*(Mois="Mars"))
ou =SOMMEPROD((C2:C16>=36)*(B2:B16="Mars"))
renvoie 2

Syntaxe de SOMMEPROD comme Somme des produits scalaires :
=SOMMEPROD((plage=critère1)*(plage=critère2)*(plage à sommer)
d'où
=SOMMEPROD((Blanc>=36)*(Mois="Mars")*Blanc)
ou =SOMMEPROD((C2:C16>=36)*(B2:B16="Mars")*C2:C16)
renvoie 96

III. Encadrement de deux valeurs.

Compter le nombre de ventes de Floc blanc comprises entre deux valeurs >12 mais < 60
  • Supérieur_ou_égal_à_36.png
    Explicatif: Même raisonnement,
    la matrice {1} est le résultat de la comparaison avec 12
    la matrice {2} celle avec 60

Syntaxe de SOMMEPROD comme compteur sous 2 conditions : =SOMMEPROD((plage=critère1)*(plage=critère2)
d'où
=SOMMEPROD((Blanc>12)*(Blanc<60)
ou
=SOMMEPROD((C2:C13>12)*(C2:C13<60)
renvoie 9

Syntaxe de SOMMEPROD comme Somme des produits scalaires :
=SOMMEPROD((plage=critère1)*(plage=critère2)*(plage à sommer)

d'où la quantité de Floc Blanc sous cet encadrement est
=SOMMEPROD((Blanc>12)*(Blanc<60)*(Blanc)
ou bien
=SOMMEPROD((C2:C13>12)*(C2:C13<60)*C2:C13)
renvoie 262

IV. Utilisation de SOMMEPROD avec un raisonnement soustractif

Compter le nombre de ventes comprises entre deux valeurs >=24 et < 60
  1. Application de la syntaxe précédente :
    La quantité recherchée répond aux deux critères <=24 et <60 :
    =SOMMEPROD((plage=critère1)*(plage=critère2)
    donne
    =SOMMEPROD((Blanc>=24)*(Blanc<60)
  2. Une autre piste de la fonction SOMMPROD appliquée à ce cas de comparaison entre deux valeurs.

    Une logique différente appliquée à ce même encadrement
    soit en math : x ∈ [24;60[
    suivant le schéma soustractif suivant :
    Image
    fait que la quantité recherchée est égale aux Blancs <60 diminués des Blancs strictement inférieurs à 24.

    Essayez cette formule avec le signe moins -
    =SOMMEPROD((Blanc<=60)-(Blanc<24))
    Naturellement cela donne la même valeur mais il faut aimer soustraire !

    Mais attention aux parenthèses pour obtenir la quantité de bouteilles !
    =SOMMEPROD((Blanc<60)*(Blanc<24)*Blanc)
    =SOMMEPROD(((Blanc<60)-(Blanc<24))*Blanc)
    • Tableau explicatif :
      SP_Floc_36+mars.png
      Trois matrices "Résultat de la comparaison"
      • {1} <60 en colonne D
      • {2} >=2460 en colonne E
      • {3} <24 en colonne F
      Les deux produits scalaires en G : {1}*{2}
      en I : {1}-{3} et les deux colonnes calculées avec "Blanc"[/color][/i]
Fichier joint : 5 feuilles : Feuillet 1 : Exercice, puis Blanc<36, AvecMars, Encadrement1 et Encadrement 2 (soustratif)
SP_Floc_Valeurs_Numériques.ods
(24.5 Kio) Téléchargé 600 fois
à suivre ➫
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

IX. SommeProd et les sommes matricielles

Message par Papayes »

IX. Autres extractions de données d'une même plage

Objectif :
La syntaxe précédente de SOMMEPROD nous entraîne vers l'utilisation des sommes matricielles pour extraire des valeurs discontinues,
des nombres avec l'exemple I
et des données texte dans l'exemple II.
Le fichier de travail précédent va nous servir encore de base d'exercices.

I. Utilisation de SOMMEPROD avec des valeurs discontinues

Compter le nombre de ventes inférieures à 24 ET supérieures à 48
  • SP_Floc_Durand_Martin.png
    Explicatif: Même raisonnement pour {1}et{2}.
    Le produit scalaire {1}*{2} rend bien logiquement 0, il est à proscrire ici.
    Il faut effectivement réaliser un test logique « OU » :
    l'addition des deux résultats ligne par ligne, c.à.d. quand l'une OU l'autre du test est VRAIE.
    La formule appliquée pour la somme matricielle, par exemple pour la ligne 2
    =(OU(D2;E2))*1
    la multiplication par 1 rendant 1 pour « VRAI » et 0 pour « FAUX ».
    Ce qui se traduit dans la colonne G à l'addition des deux matrices {1}+{2}
    Sa somme de G donne le nombre de fois où les critères sont réalisés soit 7 ;
    Le produit scalaire par Blanc donne en H le nombre d'éléments concernés ;
    La somme de cette colonne donne le résultat recherché soit 282
    .
Ce raisonnement est donc traduit par un + entre les attributs.

La syntaxe du compteur SOMMEPROD :
=SOMMEPROD((plage1=critère1)+(plage1=critère2))
La fonction devient
=SOMMEPROD((Blanc<24)+(Blanc>40))
ou
=SOMMEPROD((C2:C16<24)+(C2:C16>40))
renvoie 7

La syntaxe de SOMMEPROD appliqué à une plage à sommer :
=SOMMEPROD(((plage1=critère1)+(plage1=critère2))*plage à sommer)
donc
=SOMMEPROD(((Blanc<24)+(Blanc>40))*Blanc)
ou
=SOMMEPROD(((C2:C16<24)+(C2:C16>40))*C2:C16)
donne 282.

II. Extraction de données "Texte"

Exercice : Compter le nombre des ventes pour "Durand" ET "Martin"
  • SP_Floc_Valeurs_discontinues.png
    Explicatif : Chaque cellule de la matrice Client est comparée à "Durand"
    Le résultat de la comparaison est 1 si le critère est satisfait, et 0 s'il ne l'est pas, directement grâce à la *1.
    La matrice « Résultat Condition Durand {1} » est en D
    Chaque cellule de la matrice Client est comparée à "Martin", et donne la matrice {2} en E

    On réalise un test logique « OU » : addition des deux résultats ligne par ligne, c.à.d. quand l'une ou l'autre du test est VRAIE (=1).
    La formule appliquée pour la somme matricielle, par exemple pour la ligne 2
    =(OU(D2;E2))*1
    Ce qui se traduit en F par l'addition des deux matrices {1}+{2}.
Ce raisonnement est donc traduit par un + entre les attributs.

La syntaxe de SOMMEPROD comme compteur avec 2 conditions
=SOMMEPROD((plage1=critère1)+(plage1=critère2))
donne le nombre de fois ou les deux clients sont concernés :
=SOMMEPROD((Client="Durand")+(Client="Martin"))
=SOMMEPROD((A2:A16="Durand")+(A2:A16="Martin"))

La syntaxe de SOMMEPROD comme Somme du produit scalaire
=SOMMEPROD(((plage1=critère1)+(plage1=critère2))*plage à sommer)
et le nombre de leurs achats de Floc blanc se traduit par :
=SOMMEPROD(((Client="Durand")+(Client="Martin"))*Blanc)
=SOMMEPROD(((A2:A16="Durand")+(A2:A16="Martin"))*C2:C16)

Fichier joint : 3 feuilles Feuillet1 : Exercice, puis Valeursdiscontinues et DurandMartin
SP_Floc_Sommes_matricielles.ods
(14.77 Kio) Téléchargé 612 fois
à suivre ➫
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

X. SOMMEPROD et les chaines de caractères

Message par Papayes »

X. SOMMEPROD et les Chaînes de caractères - 1

Objectif Comme toute fonction, SOMMEPROD peut se combiner avec d'autres fonctions.
Utilisation des fonctions de traitement de chaines de caractères
GAUCHE et DROITE

Extraire dans le même classeur, les ventes pour les "Caves Vinicoles".
Il y en a quatre. La formule précédente peut être appliquée et une simple l'addition des 4 plages sous critère donne le résultat,
mais il est plus rapide d'utiliser les fonctions de Traitement des chaînes de caractères.


I. SOMMEPROD et les premiers caractères d'une plage
  1. Rappel : La Fonction GAUCHE
    Renvoie le ou les premiers caractères d'une chaîne de texte.
    Syntaxe
    GAUCHE(texte;nombre)
    "texte" est le texte dans lequel les mots partiels initiaux sont déterminés.
    "nombre" est facultatif et indique le nombre de caractères à extraire. Si ce paramètre n'est pas défini, un seul caractère est renvoyé.
    Exemple : Gauche("Cave Antibes";2) renvoie Ca.
  2. Extraire suivant les premiers caractères
    Travailler avec les seules Caves :
    SP_Floc_Gauche.png

    Explicatif : La fonction GAUCHE appliquée à la matrice Client avec 4 caractères ligne par ligne rend la matrice {1}
    La chaîne de caractère "Cave" est comparée à chaque cellule de la matrice {1}
    Chaque résultat de la comparaison est "VRAI" si le critère est satisfait, et "Faux" s'il ne l'est pas. La multiplication par 1 permet d’afficher directement 1 pour "VRAI" et 0 pour "FAUX"
    La matrice « Résultat de la Condition {1}="Cave" » est en D et sa somme est 9.


    En fait, cela revient à remplacer la matrice Client par la matrice GAUCHE(Client;4)

    Le compteur conditionnel est donc =
    =SOMMEPROD(GAUCHE(Client;4)="Cave")
    ou
    =SOMMEPROD(GAUCHE(A2:A16;4)="Cave")

    et sa syntaxe
    =SOMMEPROD(GAUCHE(plage;nombre)=critère)
  3. Exercices
    Il ne reste plus qu'à appliquer les autres conditions et les plages à sommer suivant la syntaxe connue.
    =SOMMEPROD((GAUCHE(plage;nombre)=critère)*plage à sommer)
    Exemples pour les caves et divers critères d'extraction.
    Feuille ExercicesGAUCHE du fichier joint
    SP_Floc_Droite.png
    • Vente de Blanc
      298 =SOMMEPROD((GAUCHE(Client;4)="Cave")*Blanc)
      Vente de Rouge
      186 =SOMMEPROD((GAUCHE(Client;4)="Cave")*Rouge)
      Vente de Blanc et Rouge
      484 =SOMMEPROD((GAUCHE(Client;4)="Cave")*(Blanc+Rouge))
      Vente de Blanc et de Rouge en mars
      192 =SOMMEPROD((GAUCHE(Client;4)="Cave")*(Blanc+Rouge)*(Mois="Mars"))
      Etc.

II. SOMMEPROD et les derniers caractères d'une plage
Extraire le Nom de la cave ou la Ville de livraison impose de traiter la partie droite de la plage "Client"
  1. Rappel : La Fonction DROITE
    renvoie le ou les derniers caractères d'une chaîne de texte.
    Syntaxe
    DROITE(texte;nombre)
    "texte" est le texte dont la partie de droite doit être déterminée.
    "nombre" (facultatif) est le nombre de caractères à extraire à la fin.
    Exemple
    DROITE("Antibes";3) renvoie bes.
  2. Extraire suivant les derniers caractères
    Travailler avec une seule cave bien déterminée : exemple Auch
    DROITE("Auch";4) renvoie Auch.
    SP_Floc_Gauche_Droite_Chaines_de_caractères.ods
    (20.02 Kio) Téléchargé 655 fois
    Explicatif :
    La fonction DROITE appliquée à la matrice Client avec 4 caractères, ligne par ligne, rend la matrice {1} en C
    Chaque résultat de la comparaison logique =(C2="Auch")*1 est "VRAI" si le critère est satisfait, et "Faux" s'il ne l'est pas ; la multiplication par 1 permet d’afficher directement 1 pour "VRAI" et 0 pour "FAUX"
    construit la matrice « Résultat de la Condition Cave{2} » en D dont la somme est 4.


    En fait, on remplace la matrice Client par la matrice DROITE(Client;4)

    La formule à appliquer est donc, comme simple compteur :
    =SOMMEPROD(DROITE(Client;4)="Auch")
    dont la syntaxe sera :
    =SOMMEPROD(DROITE(plage;nombre)=critère)
  3. Exercices
    Il ne reste plus qu'à appliquer les autres conditions et les plages à sommer suivant la syntaxe connue.
    =SOMMEPROD((GAUCHE(plage;nombre)=critère)*plage à sommer)
    Exemples pour les caves et divers critères d'extraction.
    Feuille ExercicesDROITE du fichier joint ci-dessus.
    • Ventes de Blanc à la Cave d'Auch :
      124 =SOMMEPROD((DROITE(Client;4)="Auch")*Blanc)
      Ventes de Rouge
      78 =SOMMEPROD((DROITE(Client;4)="Auch")*Rouge)
      Ventes Blanc et Rouge
      202 =SOMMEPROD((DROITE(Client;4)="Auch")*(Blanc+Rouge))
      Vente de Blanc et de Rouge en mars
      60 =SOMMEPROD((DROITE(Client;4)="Auch")*(Mois="Mars")*(Blanc+Rouge))
      etc.
SP_Floc_Droite.png
Suite ➫
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

XI. SommeProd et les statistiques de rentrée

Message par Papayes »

XI. SOMMEPROD et les chaînes de caractères - 2

Objectif : Autre exemple des fonctions de Traitement des chaînes de caractères combinées avec SOMMEPROD.
Utilisation de la fonction NBCAR pour simplifier la formulation.

Dans les statistiques de rentrée demandés aux Directeurs d'école,
il faut sommer les élèves par niveaux CP , CE , CM, etc.
à partir des effectifs des classes.

La fonction SOMMEPROD va faciliter le traitement de ces statistiques. Je donne deux exemple, le premier pour initiation, le second, un peu plus corsé comme exercice d'application.

I. Exemple simple : École à 11 classes à un niveau de cours

Image
  1. Extraire suivant les premiers caractères

    En exemple, calculer le nombre d'élèves du niveau CE (les classes de CE1 et de CE2).
    La fonction GAUCHE permet d’éliminer le 1 et le 2 de "CE1" ou "CE2".
    SP_Ecole_élémentaire_9classes.ods
    (21.15 Kio) Téléchargé 570 fois
    Explicatif: La fonction GAUCHE avec seulement 2 caractères est appliquée à la matrice Classes, ligne par ligne qui rend la matrice {1}
    Chaque cellule de la matrice {1} est comparée à la chaîne de caractère "CE" par la formule logique =E2="CE"
    Chaque résultat de la comparaison est "VRAI" si le critère est satisfait, et "FAUX" s'il ne l'est pas. La multiplication par 1 =(E2="CE")*1 affiche directement le résultat par 1 ou 0.
    La matrice « Résultat de la Condition CE {1} » donne 4 comme Somme.

    En fait, la matrice "GAUCHE(Classes;2)" remplace la matrice Classes comme plage.

    SOMMEPROD comme compteur des Classes commençant par CE est:
    =SOMMEPROD(GAUCHE(Classes;2)="CE")
    qui renvoie 4
    Syntaxe utilisée :
    =SOMMEPROD(GAUCHE(texte;nombre)=critère")

    Exercice :
    Il ne reste plus qu'à appliquer éventuellement les autres conditions et les plages à sommer suivant la syntaxe connue.
    =SOMMEPROD((GAUCHE(texte;nombre)=critère")*plage à sommer)
    • Nombre de filles de CE:
      =SOMMEPROD((GAUCHE(Classes;2)="CE")*Filles)
      Nombre de garçons de CE:
      =SOMMEPROD((GAUCHE(Classes;2)="CE")*Garçons)
      Nombre total des élèves de CE:
      =SOMMEPROD((GAUCHE(Classes;2)="CE")*(Filles+Garçons))
  2. Utilisation de la fonction NBCAR
    Cette fonction permet d'éviter l'emploi des quottes dans les formules précédentes.
    Il suffit de référencer sur la feuille les expressions "CP", "CE", "CM"
    une par cellule.
    • ➫ Si vous désirez faire des collages de formule, il est nécessaire d'utiliser les références absolues
      ou carrément de nommer ces 4 cellules ce qui rend plus lisible la formule.
      Exemple : "CP" en A20 : soit $A$20 ou nom de cellule : CP
    La fonction NBCAR(texte) renvoie le nombre de caractères y compris les espaces vides
    et remplace le nombre de la fonction GAUCHE(texte;nombre).
    • ➫ Attention à na pas oublier la seconde parenthèse après NBCAR(A21)
    Statistique_9Classe1.png
    Exemple : feuille "NBCAR" du fichier .ods joint.
    • Si "CE" est référencé en A21,
      le compteur de Classes de CE devient:
      =SOMMEPROD(GAUCHE(Classes;NBCAR(A21))=A21).
      Et le nombre de filles de CE est
      =SOMMEPROD((GAUCHE(Classes;NBCAR(A21))=A21)*Filles)

      ou en employant les références absolues
      =SOMMEPROD(GAUCHE(Classes;NBCAR($A$21))=$A$21).
      =SOMMEPROD((GAUCHE(Classes;NBCAR($A$21))=$A$21)*Filles)

      ou mieux en utilisant les noms de cellules
      =SOMMEPROD(GAUCHE(Classes;NBCAR(CE))=CE)
      =SOMMEPROD((GAUCHE(Classes;NBCAR(CE))=CE)*Filles)
      etc.
    L'avantage est de pouvoir traiter le cas des CM1 et CM2, c'est à dire de 3 caractères significatifs, sans modifier la formule générale de recherche hormis la référence du Niveau.
    Exemple de la feuille "ExerciceCM" du fichier joint
    • Si "CM" est référencé en A22 nommée CM
      le compteur des classes de CM est
      =SOMMEPROD(GAUCHE(Classes;NBCAR(CM))=CM)
      Le Nombre de Filles de CM
      =SOMMEPROD((GAUCHE(Classes;NBCAR(CM))=CM)*Filles)
      etc
      .
Statistique_9Classe2.png
II. Cas d'une École élémentaire avec des classes à plusieurs niveaux

Le raisonnement est identique. Il faut cependant concevoir autrement le tableur afin de traiter en particulier les différents niveaux par classes.
SOMMEDPROD utilisera comme plage de critère la plage des Niveaux (au lieu de Classes dans les exemples précédents).
La feuille 1 du fichier .ods vous donne un exemple de répartition par niveau et par sexe.
Statistique_9Classe3.png
La feuille Statistique présente l'extraction du nombre d'élèves de niveau CE
SP_Ecole_élémentaire_11classes.ods
(16.92 Kio) Téléchargé 594 fois
  • Nom des plages
    Niveau = D2:D14
    Filles = F2:F14
    Garçons = G2:G14
    SOMMEPROD comme compteur pour le Niveau CE
    6 =SOMMEPROD(GAUCHE(Niveau;2)="CE")
    6 =SOMMEPROD(GAUCHE(D2:D14;2)="CE")

    Extraction Filles de CE
    50 =SOMMEPROD((GAUCHE(Niveau;2)="CE")*Filles)
    50 =SOMMEPROD((GAUCHE(D2:D14;2)="CE")*F2:F14)
    Extraction Garçons de CE
    54 =SOMMEPROD((GAUCHE(Niveau;2)="CE")*Filles)
    54 =SOMMEPROD((GAUCHE(D2:D14;2)="CE")*G2:G14)
    Extraction Filles et des Garçons de CE
    104 =SOMMEPROD((GAUCHE(Niveau;2)="CE")*(Filles+Garçons))
    104 =SOMMEPROD((GAUCHE(D2:D14;2)="CE")*(F2:F14+G2:G14))
La feuille NBCAR permet de définir les formules et extraire le nombre d'élèves du niveau CE en utilisant la Fonction NbCAR(texte) à la place de "nombre" de GAUCHE(texte,nombre).
Statistique_9Classe0.png
On préfèrera également nommer les 3 cellules CP, CE, CM
  • Nom des cellules
    CP=$A$20
    CE=$A$21
    CM=$A$22
afin de faciliter la lecture des formules et ainsi éviter les références absolues si l'on veut copier-glisser les formules.
SOMMEPROD comme Compteur des classes de CE
6 =SOMMEPROD(GAUCHE(Niveau;NBCAR(CE))=CE)
6 =SOMMEPROD(GAUCHE($D$2:$D$14;NBCAR($A$21))=$A$21)
6 =SOMMEPROD(GAUCHE(D2:D14;NBCAR(A21))=A21)
  • Extraction Filles de CE
    50 =SOMMEPROD((GAUCHE(Niveau;NBCAR(CE))=CE)*Filles)
    50 =SOMMEPROD((GAUCHE($D$2:$D$14;NBCAR($A$21))=$A$21)*$F$2:$F$14)
    50 =SOMMEPROD((GAUCHE(D2:D14;NBCAR(A21))=A21)*F2:F14)
    Extraction Garçons de CE
    54 =SOMMEPROD((GAUCHE(Niveau;NBCAR(CE))=CE)*Garçons)
    54 =SOMMEPROD((GAUCHE($D$2:$D$14;NBCAR($A$21))=$A$21)*$G$2:$G$14)
    54 =SOMMEPROD((GAUCHE(D2:D14;NBCAR(A21))=A21)*G2:G14)
    Extraction Filles+Garçons de CE
    104 =SOMMEPROD((GAUCHE(Niveau;NBCAR(CE))=CE)*(Filles+Garçons))
    104 =SOMMEPROD((GAUCHE($D$2:$D$14;NBCAR($A$21))=$A$21)*($F$2:$F$14+$G$2:$G$14))
    104 =SOMMEPROD((GAUCHE(D2:D14;NBCAR(A21))=A21)*(F2:F14+G2:G14))

La feuille ExempleCM applique NBCAR au tableau pour le niveau CM.
StatistiquesSimple11classes1.png
SOMMEPROD comme Compteur des classes de CM
5 =SOMMEPROD(GAUCHE(Niveau;NBCAR(CM))=CM)
5 =SOMMEPROD(GAUCHE($D$2:$D$14;NBCAR($A$22))=$A$22)
5 =SOMMEPROD(GAUCHE(D2:D14;NBCAR(A22))=A22)
  • Extraction Filles de CM
    51 =SOMMEPROD((GAUCHE(Niveau;NBCAR(CM))=CM)*Filles)
    51 =SOMMEPROD((GAUCHE($D$2:$D$14;NBCAR($A$22))=$A$22)*$F$2:$F$14)
    51 =SOMMEPROD((GAUCHE(D2:D14;NBCAR(A22))=A22)*F2:F14)

    Extraction Garçons de CM
    51 =SOMMEPROD((GAUCHE(Niveau;NBCAR(CM))=CM)*Garçons)
    51 =SOMMEPROD((GAUCHE($D$2:$D$14;NBCAR($A$22))=$A$22)*$G$2:$G$14)
    51 =SOMMEPROD((GAUCHE(D2:D14;NBCAR(A22))=A22)*G2:G14)

    Extraction Filles+Garçons de CM
    102 =SOMMEPROD((GAUCHE(Niveau;NBCAR(CM))=CM)*(Filles+Garçons))
    102 =SOMMEPROD((GAUCHE($D$2:$D$14;NBCAR($A$22))=$A$22)*($F$2:$F$14+$G$2:$G$14))
    102 =SOMMEPROD((GAUCHE(D2:D14;NBCAR(A22))=A22)*(F2:F14+G2:G14))
Fichier .ods de travail
StatistiquesSimple11classes3.png
➫ à suivre
"Tout ce qui n'est pas donné est perdu"
Avatar de l’utilisateur
Papayes
Membre fOOndateur
Membre fOOndateur
Messages : 4154
Inscription : 07 déc. 2005 14:55
Localisation : Vic-Fezensac dans le Gers

XII. Extractions diverses avec les Fonctions "Date"

Message par Papayes »

XII. Exemples d'extractions diverses et utilisation de fonctions "Date" combinées avec >SOMMEPROD()

Objectif : Exemples qui combinent diverses fonctions Date.
La première difficulté réside sur l'utilisation même des fonctions Date&heure (partie A),
l'autre difficulté dans l'emboitement des parenthèses ((...)) lorsque la formule devient trop complexe
mais ce problème est commun à toutes les fonctions : il faut bien respecter le système d'emboitage des éléments la composant.
J'ose espérer que le tutoriel vous permettra de réalisez ces écritures en vous ayant donné le réflexe de penser "matrice".
Cependant cela ne doit pas empêcher de construite des tableurs mieux élaborés en fonction des objectifs d'exploitation et d'utiliser différents noms de plage afin de faciliter les diverses extractions.

A. Notion de date des Fonctions Date&heure
  1. Assistant fonction Date&heure
    Pour faire apparaître la catégorie Date&heure directement dans l'Assistant fonction,
    tapez dans le premier cadre sélectionné d ;
    pour obtenir les Dernières utilisées, retapez d ou tapez dd.
  2. Fonctions Date&heure pour Calc et Excel :
    Les fonctions dont les noms se terminent par _ADD renvoient les mêmes résultats que les fonctions Microsoft Excel correspondantes.

    ➫ Pour obtenir des résultats conformes aux normes internationales, nous utiliserons des fonctions sans _ADD.
    Par exemple, la fonction NO.SEMAINE calcule le numéro de la semaine d'une date donnée conformément à la norme internationale ISO 8601,
    alors que la fonction NO.SEMAINE_ADD renvoie le même numéro de semaine que Microsoft Excel.
  3. Notion de date pour Calc
    Toute date est pour un tableur un nombre décimal calculé à partir de la date origine définie par le format de date standard que vous sélectionnez dans
    Menu : Outils > Options > OpenOffice.org Calc > Calculer >
    Date : 30.12.1899 (par défaut).
    • Options_calc_calculer.png
    La partie entière du nombre décimal correspond au jour
    exemple : 17/04/2013 = 41381
    et sa partie décimale correspond aux heures, minutes et secondes.
    à midi pile: 17/04/2013 12:00:00 = 41381,5
    et 3 min 45 s plus tard : 17/04/2013 12:03:45 =41381,5026041667

    L'affichage d'une date se fait ensuite suivant les règles de formatage obtenues dans
    Menu : Format > Cellules > (ou clic droit dans la cellule) >
    Formatage des cellules >
    Onglet Nombres : Catégorie Date >
    Choisir un Format
    Formatage_Cellules_Nombres.png
    ou utiliser un format personnalisé dans le cadre Description de format
    comme celui de l'exemple NNNNJ/MM
    pour l'affichage du nom du jour "mercredi 17/04".
B. Extraction de données issues d'un tableau de journées ouvrées.

Revenons au tableau du message VI.3 retour à ce message
SP_Exemple_2SemainesDate.png
L'exemple des semaines ouvrées, s'était arrêté au simple calcul du nombre total des heures de la période,
avec comme conditions la répartition entre les heures réalisées en tant que "Prestataire" (P) ou "Mandataire" (M)
  • =SOMMEPROD((Catégorie="P")*Données)
  • =SOMMEPROD((Catégorie="M")*Données)
On peut vouloir extraire ou exclure
  • la quantité d'heures excepté tel ou tels jours
  • la quantité d'heures pour un seul mois,
  • la quantité d'heures pour une seule semaine, etc.
La syntaxe de SOMMEPROD sous des conditions multiples à appliquer :
=SOMMEPROD((plage1=critère1)*(plage2=critère2)*(plage3=critère3)*(plage à sommer)
plage n est la plage de cellule (ou la matrice) contenant le critère à retenir
  • (plage1 = plage2 = plage3 pour des extractions sous conditions dans une même colonne)
critère1 est LE critère à retenir (chiffre ou texte) dans la même plage.
plage à sommer est la plage de cellules (ou matrice) où se trouvent les données à additionner.


Exclusion d'un même jour sur une période
Exemple : exclure tous les mercredis des calculs
  1. Fonction JOURSEM()
    Renvoie un nombre entier représentant le jour de la semaine (lundi, mardi, etc.) correspondant à la valeur d'une date donnée (17/04/2013).
    Syntaxe
    JOURSEM(numéro_série;type)
    type détermine le type de calcul.
    • Pour type = 1, les jours de la semaine sont comptés en commençant par dimanche (c'est le jour par défaut, même si le paramètre type est manquant).
    • Pour type = 2 les jours de la semaine sont comptés en commençant par lundi = 1.
      Le standard international ISO 8601 a décrété que le lundi doit être le premier jour de la semaine
      .
    • Pour type = 3, les jours de la semaine sont comptés en commençant par lundi = 0.
    numéro_série est un nombre décimal correspondant à une valeur de date pour lequel on cherche à déterminer le jour de la semaine.
    ➫ Exemple : si A1=17/04/2013 alors JOURSEM(A1;2) rend 3 soit mercredi.
  2. JOURSEM() et SOMMEPROD() appliqués à ce tableau

    La formule qui détermine le numéro des différents jours sélectionnés avec lundi comme premier jour de la semaine est
    JOURSEM(plage;2)
    avec plage celle des jours sélectionnés.

    L'exclusion de tous les mercredis des calculs est une nouvelle condition employant la syntaxe
    (plage=critère)
    avec
    • plage est ici la plage des jours sélectionnés transformés en numéro de jours par la formule JOURSEM().
    • critère est "différent du mercredi" soit <> 3
    La nouvelle condition à appliquer est donc (JOURSEM(plage;2)<>3)
    • Matrices horizontales.png
      Explicatif : Si l'on crée une ligne en 12 pour afficher le numéro des différents jours obtenus par la formule JOURSEM(Jours ;2),
      on obtient une nouvelle matrice composé des numéros des jours. Comparer chaque cellule avec la condition <> 3 produit la matrice {3} « Vrai »=1  et « FAUX »=0. Il faut donc faire le produit scalaire de cette matrice {3} avec les matrices {1} des "Prestataires" ou {2} des "Mandataires".
    Le calcul total des heures exceptés les mercredis devient
    =SOMMEPROD((Catégorie="P")*(JOURSEM(plage;2)<>3)*Données)
    =SOMMEPROD((Catégorie="M")*(JOURSEM(plage;2)<>3)*Données)

    Il reste à déterminer la plage de la période à prendre en compte et de la nommer pour appliquer la formule.
    Jours = C1:N1 pour les 2 semaines de lundi (C) à samedi (N)
    Données = C2:N5 avec la plage des données correspondante,

    Nombre d'heures des Prestataires sauf les mercredis
    =SOMMEPROD((Catégorie="P")*(JOURSEM(Jours;2)<>3)*Données)
    Nombre d'heures des Mandataires sauf les mercredis
    =SOMMEPROD((Catégorie="M")*(JOURSEM(Jours;2)<>3)*Données)

    Dans le fichier SP_Exemple_2Semaines_Date_sauf_mercredis.ods joint, en feuille 2 "Explicatif",
    vous trouverez l'explicatif de fonctionnement comme dans les exemples précédents
    ainsi que l'exemple d'une formule complexe de Moyenne du paragraphe E. Complexité de certaines formules .
SP_Exemple_2Semaines__Date_sauf_mercredis.ods
(21.2 Kio) Téléchargé 569 fois
Exclusion de plusieurs même jours sur une période

Le raisonnement est identique, il s'agit d'appliquer une nouvelle condition à la formule précédente.
(JOURSEM(plage;2)<>x)
x représentant la valeur représentant le jour de la semaine.
en respectant la syntaxe
=SOMMEPRD((plage1;critère1)*(plage2;critère2)*(plage2;critère3)*(plage2;critère4)*plage à sommer)
  1. Par exemple pour exclure également les lundis,
    SP_Exclurelundisetmercredis.png
    la condition à rajouter est :
    (JOURSEM(plage;2)<>1)
  2. La formule pour l'exclusion des lundis et mercredis devient
    Nombre d'heures des Prestataires sauf les lundi et mercredis
    =SOMMEPROD((Catégorie="P")*(JOURSEM(Jours;2)<>1)*(JOURSEM(Jours;2)<>3)*Données)
    Nombre d'heures des Mandataires sauf les lundis et mercredis
    =SOMMEPROD((Catégorie="M")*(JOURSEM(Jours;2)<>1)*(JOURSEM(Jours;2)<>3)*Données)
    SP_Exemple_2Semaines__Date_sauf_lundisetmercredis.ods
    (20.05 Kio) Téléchargé 495 fois
  3. Le cas des Dimanches est traité dans les exemples suivants.
C. Exclusion d'un même mois sur une période annelle.
SP_Extractions_sur_2_mois.png
Le raisonnement reste identique mais en appliquant une fonction relative aux mois.
Fonction MOIS()
Renvoie le mois de l'année compris entre 1 et 12 correspondant à une valeur de date donnée
.
Syntaxe
Fonction MOIS(nombre)
nombre est un nombre décimal correspondant à la valeur de la date.
  • Si A1 = 1/04/2013 alors Mois(A1) rend 4
    Dans cet exemple qui ne contient que la dernière quinzaine d'Avril, si vous ne désirez que celle-ci,
    le critère reste MOIS(Jours)=4
    • :!: Attention : Cas du mois de Décembre avec des cellules vides.
      Pour Calc,si la cellule A1 est vide, la formule MOIS(A1) renvoie 12 donc VRAI=1 dans la comparaison logique.
      mais si A1 est vide, la comparaison logique =A1 renvoie 0 qui est la date origine du classeur (30/12/1899).
      Sous Excel, cela se produit pour Janvier... ce que vous retrouveriez si vous adoptez l'option du 01/01/1900 ou du 01/01/1904 dans
      Menu : Outils > Options > OpenOffice.org Calc > Calculer > Date > Voir plus haut le rappel sur la date origine.
      Pour que la formule soit correcte, il faut exclure les cellules vides en rajoutant une simple condition
      *(plage<>"") à adapter à votre tableau.
  • Exemples : Total des heures exceptées celles du mois de mars :
    691 =SOMMEPROD((Catégorie="P")*(MOIS(Jours)=3)*Données)
    Total des heures exceptées celles d'Avril
    535 =SOMMEPROD((Catégorie="P")*(MOIS(Jours)=4)*Données)
SP_Exemple_sur 2_mois.ods
(20.8 Kio) Téléchargé 538 fois
D. Exclusion d'une semaine sur une période annelle.

Le raisonnement reste identique mais en appliquant une fonction relative aux semaines.
Fonction NO.SEMAINE()
calcule le numéro de semaine de l'année
.

Rappel : Le standard international ISO 8601 a décrété que le lundi doit être le premier jour de la semaine.
Une semaine qui s'étend en partie sur une année et en partie sur une autre se voit assigner un nombre dans l'année qui contient le plus grand nombre de ses jours. Cela signifie que le numéro de semaine 1 de n'importe quelle année est celle qui contient le 4 janvier.
  1. Syntaxe
    NO.SEMAINE(nombre;mode)
    nombre correspond au numéro de série interne de la date.
    mode indique le début de la semaine et le type de calcul.
    • 1 = Dimanche
      2 = Lundi
  2. Pour exclure une semaine particulière,
    la nouvelle condition à inclure dans la formule est
    (NO.SEMAINE(plage;2)<>x)
    x représentant la valeur représentant la semaine.
    en respectant la syntaxe
    =SOMMEPRD((plage1;critère1)*(plage2;critère2)*(plage2;critère3)*(plage2;critère4)*plage à sommer)
    SP_NOSEMAINE2.png
  3. Pour connaitre le numéro de la semaine considérée que vous voulez extraire,
    il suffit de placer dans une cellule : NO.SEMAINE(cellule;2)
    exemple n° de la semaine du 15/03/2013 en C2 : =NO.SEMAINE(C2;2) qui rend 11
  4. Exemple :
    Extraire les résultats de la première semaine d'avril
    n° de la semaine du 15/04/2013 en T2 : =NO.SEMAINE(T2;2) qui rend 14
    les formules deviennent
    =SOMMEPROD((Catégorie="P")*(NO.SEMAINE(Jours);2)=14)*Données)
    =SOMMEPROD((Catégorie="M")*(NO.SEMAINE(Jours);2)=14)*Données)

    Pour obtenir le nombre d'heures totales sans les heures de cette semaine
    Exclure cette première semaine d'Avril :
    =SOMMEPROD((Catégorie="P")*(NO.SEMAINE(Jours);2)<>14)*Données)
    =SOMMEPROD((Catégorie="M")*(NO.SEMAINE(Jours;2)<>14)*Données)
SP_Exemple_sur 2_moisNOSEMAINE.ods
(20.58 Kio) Téléchargé 542 fois
D. Complexité de certaines formules.
Exemple du calcul d'une moyenne
Plutôt que d'écrire une formule de ce genre,
  • avec les références absolues
    =SOMMEPROD(($B$2:$B$5="P")*(JOURSEM($C$1:$N$5;2)<>3)*Données)/SOMMEPROD(JOURSEM($C$1:$N$5;2)<>3)
  • même avec le nom des plage
    =SOMMEPROD((Catégorie="P")*(JOURSEM(Jours;2)<>3)*Données)/SOMMEPROD(JOURSEM(Jours;2)<>3)
il vaut quand même mieux sur la feuille ou sur une autre, placer les résultats partiels documentés.
  • A30 : Somme des valeurs : =SOMMEPROD((Catégorie="P")*(JOURSEM(Jours;2)<>3)*Données)
  • A31 : Nombre de valeurs : =SOMMEPROD(JOURSEM(Jours;2)<>3)
  • Moyenne : =A30/A31
:D

Cela rejoint ma préférence pour le calcul des 2 Catégories avec une écriture somme simple
A20 : Prestataires (P) = =SOMMEPROD((Catégorie="P")*Données)
A21 : Mandataires (M) = =SOMMEPROD((Catégorie="M")*Données)
Total : =A20+A21 ou SOMME(A20;A21)
plutôt que =SOMMEPROD(((Catégorie="P")+(Catégorie="M"))*Données) !

Mais tous les goûts sont dans la nature...

Je vous renvoie pour d'autres exemples et possibilités au tutoriel réalisé depuis par Gérard 24
Somme avec plusieurs critères (conditions)


Ce tutoriel figure également au format .odt et en .pdf sur le Site de Documentation officielle
Lien direct du fichier en PDF
Lien direct du fichier writer .odt


Cordialement vôtre,
Jean-Yves LUCCA
"Tout ce qui n'est pas donné est perdu"
Répondre