Calcul Matriciel sur dates

[peterguy]

Bonjour à tous les amis du forum. Je sèche sur le problème suivant:
J'ai un tableau de 500 lignes comme ci dessous:

............Début..........Fin.........Motif
NomA...14/01/09...17/04/09...Compta
NomB...25/03/09...14/04/09...Classement
NomC...18/02/09...10/06/09...Classement
NomD...15/03/09...21/07/09...Compta
Etc...
................Janv | Fev | Mar | Avr | Mai | juin | juillet etc..
Compta :......18..|..28.|..48..|..47.|..31.|..30.|..21
Classement :

Je souhaiterai le total de jours mensuel pour chaque motif, par exemple pour compta, en mars j'aurai 31 (ligne NomA) + 17 (ligne NomD) soit 48 Jours.
D'avance, mille fois merci, Peterguy.

 

[ROGER2327]

Re : Calcul Matriciel sur dates

Bonjour peterguy

Un essai par formule matricielle dans le classeur joint.​

ROGER2327

 

[peterguy]

Re : Calcul Matriciel sur dates

Un immense merci à Roger pour ses formules matricielles qui fonctionnent à merveille
Peterguy.

 

[mercibien]

Re : Calcul Matriciel sur dates

Bonjour ROGER2327;
Merci de bien vouloir nous ( les débutants dans le calcul matriciel) davantage votre formule.
En effet on apprend beaucoup dans ce forum par les contributions des personnes comme vous dévoué à aider les autres et à partager leur savoir...
Merci beaucoup

 

[peterguy]

Re : Calcul Matriciel sur dates

Bonjour Roger

De nouveau Peterguy, j'ai un petit soucis sur ton tableau, en effet tant que les périodes sont incluses au sein des 12 mois, ça marche très bien, cependant le soucis est pour les périodes à cheval en début ou fin d'année par exemple du 20/12/2008 au 15/01/2009 ou bien du 10/12/2009 au 20/01/2010.
Une petite modification?

 

[ROGER2327]

Re : Calcul Matriciel sur dates

RE...

Normal. J'ai traité les données, toutes sur la même année. Donc, pas tenu compte de l'année... Mais on va réfléchir... A bientôt.​

ROGER2327

 

[ROGER2327]

Re : Calcul Matriciel sur dates

RE...RE...

Voyez si

[COLOR="DarkSlateGray"]=SOMME(($D$2:$D$21=$F2)*(SI(DATE(ANNEE(H$1);MOIS(H$1);1)<DATE(ANNEE($B$2:$B$21);MOIS($B$2:$B$21);1);0;DATE(ANNEE(H$1);MOIS(H$1)+1;1)-SI($B$2:$B$21<H$1;H$1;$B$2:$B$21)-SI(DATE(ANNEE(H$1);MOIS(H$1);1)<DATE(ANNEE($C$2:$C$21);MOIS($C$2:$C$21);1);0;DATE(ANNEE(H$1);MOIS(H$1)+1;1)-SI($C$2:$C$21<H$1;H$1;1+$C$2:$C$21)))))[/COLOR]

fait l'affaire.
J'en ai profité pour qu'il n'y ait plus besoin de colonnes supplémentaires. (La colonne G n'a d'ailleurs jamais servi à rien...)​

ROGER2327

 

[ROGER2327]

Re : Calcul Matriciel sur dates

Bonjour ROGER2327;
Merci de bien vouloir nous ( les débutants dans le calcul matriciel) davantage votre formule.

Bonjour mercibien

Je suppose qu'il s'agit d'expliquer d'où sort cette formule. Je vais essayer. Tous les commentaires qui suivent s'appliquent au fichier ci-joint, et ne tiennent pas compte des messages #5, 6, 7.

Principe de la formule :

Pour le mois M et une ligne de données, le nombre de jours à prendre en compte est:
___si Début>="1/M+1/2009" : 0
___si Début<"1/M+1/2009" ET Début>="1/M/2009" :
______si Fin<"1/M+1/2009" : ("1/M+1/2009"-Début)-("1/M+1/2009"-Fin)
______si Fin>="1/M+1/2009" : "1/M+1/2009"-Début
___si Début<"1/M/2009" :
______si Fin<"1/M+1/2009" : ("1/M+1/2009"-"1/M/2009")-("1/M+1/2009"-Fin)
______si Fin>="1/M+1/2009" : "1/M+1/2009"-"1/M/2009"

Remarque. Le jour de fin (Fin) n'est pas comptabilisé. Si on veut l'inclure, on modifie légèrement :

___si Début>="1/M+1/2009" : 0
___si Début<"1/M+1/2009" ET Début>="1/M/2009" :
______si Fin<"1/M+1/2009" : ("1/M+1/2009"-Début)-("1/M+1/2009"-(1+Fin))
______si Fin>="1/M+1/2009" : "1/M+1/2009"-Début
______si Début<"1/M/2009" :
______si Fin<"1/M+1/2009" : ("1/M+1/2009"-"1/M/2009")-("1/M+1/2009"-(1+Fin))
______si Fin>="1/M+1/2009" : "1/M+1/2009"-"1/M/2009"

Écrire cela directement conduirait à une formule inutilement longue. On peut la réduire en remarquant que
______("1/M+1/2009"-Début)
et____("1/M+1/2009"-"1/M/2009")
peuvent être remplacés par
______("1/M+1/2009"-MAX(Début;"1/M/2009"))
ou____("1/M+1/2009"-(Début>="1/M/2009")*Début-(Début<"1/M/2009")*"1/M/2009")

Le plus difficile est fait. Le reste est de la cuisine : tout est dans l'aide d'Excel.

L'illustration de ce raisonnement est l'objet des tableaux 2 et 3.
___Formule en H25 :
___=SI(MOIS(H$24)<MOIS($B25);0;I$24-MAX(H$24;$B25)-SI(MOIS(H$24)<MOIS($C25);0;I$24-MAX(H$24;1+$C25)))
___Formule en H48 :
___=SI(MOIS(H$47)<MOIS($B48);0;I$47-SI($B48<H$47;H$47;$B48)-SI(MOIS(H$47)<MOIS($C48);0;I$47-SI($C48<H$47;H$47;(1+$C48))))

Quoi que plus longue, c'est cette dernière forme que j'ai retenue, car la fonction MAX étant intrinsèquement de nature matricielle, son emploi dans une formule matricielle est délicate.

Pour l'instant, ces formules ne traitent qu'une ligne, sans tenir compte de la colonne D.

Traitement de plusieurs lignes. Il faut SOMMER les résultats obtenus dans les lignes du tableau 3. C'est là que la formule va devenir MATRICIELLE. On veut traiter toutes les lignes : il suffit de remplacer $B48 par $B$48:$B$67, $C48 par $C$48:$C$67 et on écrit en H48 :
___=SOMME(SI(MOIS(H$47)<MOIS($B$48:$B$67);0;I$47-SI($B$48:$B$67<H$47;H$47;$B$48:$B$67)-SI(MOIS(H$47)<MOIS($C$48:$C$67);0;I$47-SI($C$48:$C$67<H$47;H$47;(1+$C$48:$C$67)))))
(à valider par Ctrl + Maj + Entrée)
Cette formule fait en même temps tout le travail des formules en H48:H67 et additionne leurs résultats.
Voir l'application à la ligne 69.

Prise en compte de la colonne D. TABLEAU 4.
On écrit en F73, F74, ... les valeurs de la colonne D que l'on veut sélectionner.
Il faut maintenant utiliser la formule du tableau précédent en ne lui faisant prendre en compte que les lignes qui nous intéressent.
Il suffit d'écrire en H73 :
___=SOMME(($D$73:$D$92=$F73)*(SI(MOIS(H$72)<MOIS($B$73:$B$92);0;I$72-SI($B$73:$B$92<H$72;H$72;$B$73:$B$92)-SI(MOIS(H$72)<MOIS($C$73:$C$92);0;I$72-SI($C$73:$C$92<H$72;H$72;(1+$C$73:$C$92))))))
(à valider par Ctrl + Maj + Entrée)

Fignolage : Il n'aura pas échappé au lecteur que la colonne G n'est jamais utilisée : on peut la supprimer.
Il n'est pas satisfaisant pour l'esprit qu'une colonne supplémentaire T soit utilisée. Mais on remarque qu'elle n'est utilisée que par la colonne S, pour fournir la date du 1er du mois suivant celui de la colonne S.
Il y a une parenthèse inutile. D'où la formule en H2 :
___=SOMME(($D$2:$D$21=$F2)*(SI(MOIS(H$1)<MOIS($B$2:$B$21);0;DATE(ANNEE(H$1);MOIS(H$1)+1;1)-SI($B$2:$B$21<H$1;H$1;$B$2:$B$21)-SI(MOIS(H$1)<MOIS($C$2:$C$21);0;DATE(ANNEE(H$1);MOIS(H$1)+1;1)-SI($C$2:$C$21<H$1;H$1;1+$C$2:$C$21)))))

Amélioration : Tout cela est brut de décoffrage. Il serait souhaitable que nous ne fussions pas limité à une plage de données de vingt lignes et que nous pussions étendre cette plage à volonté. Le recours aux plages nommées dynamiques fera l'affaire. A suivre...​

Voilà... Les améliorations possibles sont les bienvenues.​

ROGER2327

 

[peterguy]

Re : Calcul Matriciel sur dates

Extraordinaire, Roger2327, et encore merci mille fois pour la nouvelle formule (message #7) qui fonctionne à merveille y compris pour les périodes à cheval en début ou en fin d'année, le calcul s'effectue parfaitement à compter du 01 janvier et s'arrête bien au 31 décembre.
Bravo également pour la démonstration magistrale des formules.
Bien cordialement, Peterguy.

 

[ROGER2327]

Re : Calcul Matriciel sur dates

Bonne nuit à tous

J'ajoute deux versions utilisant des plages nommées. Elles permettent d'allonger la liste des données sans obliger à modifier les formules. Ces formules me semblent compliquées : si les spécialistes de la formule (dont je ne suis pas) peuvent améliorer la chose, qu'ils ne se gênent pas ! (Merci d'avance.)​

ROGER2327