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
- 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.
- 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.
- 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).
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 messageSP_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
- 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.
- 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)
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
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)
- Par exemple pour exclure également les lundis,
SP_Exclurelundisetmercredis.png
la condition à rajouter est :
(JOURSEM(plage;2)<>1)
- 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
- 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
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.
- 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.
- 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
- 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
- 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
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
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
Vous ne pouvez pas consulter les pièces jointes insérées à ce message.