formule pour compter les différences de dates

[C@thy]

Bonjour le forum,

j'ai 2 feuilles de données.
Sur chaque feuille, j'ai une colonne date début date de fin et une colonne montants.
(La plage étant variable, je l'ai nommée avec DECALER.)

Je dois totaliser dans une feuille graph tous les montants pour les lignes dont l'échéance (différence entre date de début et date de fin) est à 1 jour et (ailleurs) à 3 jours... Sauf que, si la date de début est un vendredi et la date de fin un lundi, l'échéance n'est pas à 3 mais 1 jour...
ensuite je dois faire le graphe.

L'exemple joint a été fait totalement à la mano...
le but c'est de pouvoir copier les données dans les 2 premières feuilles et que les calculs soient faits automatiquement...
Double difficulté : d'une part il faut récupérer et totaliser les données sur la feuille graph d'autre part il faut créer le graphique...

Comme tout cela me semble extrêmement compliqué, pour commencer simplement(ou presque...), il faut sommer en K et L les J+1 et les J+3 (seulement pour les données de la feuille depots) et créer le graphe en histogramme...

Si vous pouvez m'aider dans cette tâche, ce serazit vraiment super extra génial...

Un grand Merci aux formulistes

Bises

Edit : on ne tient pas compte des jours fériés dans le calcul du délai

C@thy

 

[C@thy]

Re : formule pour compter les différences de dates

Hello,

j'ai réussi à isoler ceux qui sont j+3 par la formule suivante :
=SI(NB.JOURS.OUVRES(DEPOTS!$E4;DEPOTS!$F4)-1>1;DEPOTS!$C4;"")
le problème c'est que ça me crée des lignes vides, mais on peut peut-être faire avec...
en fait, avec NB.JOURS.OUVRES c'est impeccable ...

Edit : donc ce qu'il me reste à faire c'est reporter le montant en K et L en fonction de la date, avec, sans doute un SOMMEPROD?...

C@thy

 

[job75]

Re : formule pour compter les différences de dates

Bonjour chère C@thy,

Vois le fichier joint.

Il faut commencer par définir correctement les noms depo et repo.

Ensuite formule matricielle en J6 qui liste les dates chronologiquement :

=INDEX(depo;EQUIV(MIN(SI(NB.SI(J$5:J5;INDEX(depo;;4))= 0;NB.SI(INDEX(depo;;4);"<"&INDEX(depo;;4))));SI(NB.SI(J$5:J5;INDEX(depo;;4))=0;NB.SI(INDEX(depo;;4);"<"&INDEX(depo;;4)));0);4)

Formule en K6 :

=SOMMEPROD((INDEX(depo;;4)=$J6)*(MatJoursOuvres($J6;INDEX(depo;;5))>1)*INDEX(depo;;6))

La fonction NB.JOURS.OUVRES ne permet pas le calcul matriciel (du moins sur Excel 2003), j'utilise donc à la place cette fonction VBA :

Function MatJoursOuvres(dat As Date, Vdat As Range)
'Vdat est un vecteur colonne
Dim mat&(), i&, j&
ReDim mat(1 To Vdat.Count, 1 To 1)
For i = 1 To UBound(mat)
  For j = dat + 1 To Vdat(i, 1)
    If Weekday(j, 2) < 6 Then mat(i, 1) = mat(i, 1) + 1
  Next
Next
MatJoursOuvres = mat 'vecteur colonne
End Function

Les formules en L6, M6 et P6 ainsi que les noms définis pour le graphique coulent de source.

Edit : j'ai été voir sur Excel 2010, NB.JOURS.OUVRES ne fonctionne pas davantage en matriciel.

A+

 

[C@thy]

Re : formule pour compter les différences de dates

Un immense MERCI Gérard, encore une fois à mon secours... (je te dois la vie je ne sais combien de fois...)

SUPERR!!!

C'était pas facile...

Mille mercis

Bises et bon ouik

C@thy

 

[job75]

Re : formule pour compter les différences de dates

Re,

Je pense que tu auras bien vu que ma fonction MatJoursOuvres ne compte pas le jour du début.

Alors que NB.JOURS.OUVRES le compte.

A+

 

[Modeste geedee]

Re : formule pour compter les différences de dates

Bonsour® Maud ???

Double difficulté : d'une part il faut récupérer et totaliser les données sur la feuille graph d'autre part il faut créer le graphique...

Comme tout cela me semble extrêmement compliqué, pour commencer simplement(ou presque...), il faut sommer en K et L les J+1 et les J+3 (seulement pour les données de la feuille depots) et créer le graphe en histogramme...

Si vous pouvez m'aider dans cette tâche, ce serazit vraiment super extra génial...

Un grand Merci aux formulistes

Bises

Edit : on ne tient pas compte des jours fériés dans le calcul du délai

C@thy

une proposition avec TCDs et Fonction NB.JOURS.OUVRES
pour collecter les données à représenter :
mise en place simple et rapide
en ajoutant 2 colonnes au tableau initial
puis création de TCD

la difficulté est survenue lorsque j'ai voulu mettre les données dans un seul graphe ...
car impossible d'ajouter de nouvelles séries dans le graphe issu d'un autre TCD

j'ai énormément triché en superposant les 2 graphes dont les zones de l'un sont transparentes...
grosses suées à aligner et dimensionner les divers éléments communs

 

[C@thy]

Re : formule pour compter les différences de dates

Rebonjour les amis,

oups modeste, je ne t'avais pas remercié pour ton TCD... (soluce que les utilisateurs n'avaient pas adoptée...)

je relance ce fil car j'ai un petit souci :

un jour qui n'est pas fait comme les autres, voilà que je n'ai qu'une seule opération de dépôts, (feuille dépots ligne 4 seulement)
et là, patatras!... ça ne marche plus... (la belle formule matricielle de J6...).

Si j'ose encore abuser de votre compétence, les zamis... En vous remerciant

Biz

C@thy

 

[job75]

Re : formule pour compter les différences de dates

Bonsoir C@thy,

Nous ne nous étions pas encore vus cette année, alors je te souhaite une très bonne année 2015.

Pour ton problème d'une seule ligne en feuille DEPOTS, il suffit de définir le nom depo par :

=DECALER(DEPOTS!$B$4;;;MAX(2;NBVAL(DEPOTS!$B:$B)-2);8)

Bien sûr le graphique n'a plus belle allure (à cause de la ligne 7 qui ne contient que des zéros) mais la ligne 6 est renseignée, c'est le principal.

Bonne nuit et A+

 

[C@thy]

Re : formule pour compter les différences de dates

Merci Gerard. Je te souhaite également une très heureuse année 2015 ainsi qu'à Marie-Anna.

Merci pour ta soluce que j'adopte immédiatement.

Bises et bonne nuit..

C@thy

 

[job75]

Re : formule pour compter les différences de dates

Re,

Pour que le graphique soit présentable, on peut alourdir la formule en J6 sur le principe :

=SI(formule;formule;#N/A)

Ainsi il n'y a maintenant que des #N/A en ligne 7.

Fichier (2).

A+

 

[C@thy]

Re : formule pour compter les différences de dates

Ok un très grand merci à toi.

Bises et bonne journée

C@thy

 

[job75]

Re : formule pour compter les différences de dates

Bonjour C@thy, le forum,

Pas besoin de modifier la formule en J6, on peut laisser la ligne 7 avec les zéros.

Modifier simplement la définition du nom X :

=DECALER(graph!$J$6;;;NB.SI(graph!$J:$J;">0"))

Fichier (3).

A+

 

[C@thy]

Re : formule pour compter les différences de dates

Ok merci c'est super!

Biz

C@thy

 

[C@thy]

Re : formule pour compter les différences de dates

Arf!... encore un cas tordu :
(Le fichier a été redéfini en supprimant les colonnes inutiles de A à I. Du coup la belle formule est en A6 au lieu de J6.)

il se peut que dans la feuille repo on ait une date qui ne figure pas dans depots (ex. le 05/12), par consequent dans la liste des dates (la belle et lonnngue formule matricielle) il faudrait aussi intégrer les dates de la feuille repo...
et ça, c'est (quasi) impossible??? (pour l'instant on écrit la date à la main en colonne A au lieu de la formule... (ben oui, j'ai un peu honte de le dire!...)

Merci et pardon de t'embêter encore, on n'a que des cas tordus en ce moment... j'ai redéfini repo de la même façon que depo car en fait c'est exactement le même traitement dans la colonne verte que dans la rose, mais avec les données de l'autre feuille.
J'ai essayé de faire 2 colonnes dates une pour depo une pour repo, mais cela ne fonctionne pas car on a un graphe empilé donc il faut les mêmes dates....screugneugneu...

C@thy

..

 

[job75]

Re : formule pour compter les différences de dates

Bonjour C@thy,

il se peut que dans la feuille repo on ait une date qui ne figure pas dans depots (ex. le 05/12), par consequent dans la liste des dates (la belle et lonnngue formule matricielle) il faudrait aussi intégrer les dates de la feuille repo...
et ça, c'est (quasi) impossible???

Tout est possible, mais il faut alors utiliser une fonction VBA :

Function MesDates(Vdat1 As Range, Vdat2 As Range)
'Vdat1 et Vdat2 sont des vecteurs colonnes
Dim d As Object, c As Range, a
Set d = CreateObject("Scripting.Dictionary")
For Each c In Vdat1
 If c <> "" Then d(c.Value) = ""
Next
For Each c In Vdat2
 If c <> "" Then d(c.Value) = ""
Next
a = d.keys
tri a, 0, UBound(a)
MesDates = a 'vecteur ligne
End Function

Les dates sont triées par la macro Quick sort.

La fonction est utilisée en A6 et suivantes par cette formule :

=INDEX(MesDates(INDEX(depo;;4);INDEX(repo;;4));LIGNES(A$6:A6))

Fichier (4).

A+