Formule sous conditions

[fenec]

Bonjour le forum,

Dans mon tableau en "T8", je cherche en vain la formule qui me donnerait mes droits prévisionnels en fonction des conventions mais je n’y parviens d’où ma demande.
La convention dit que l’on récupère 1 RCN toutes les 10 nuits.
Je souhaiterais donc une formule qui me donnerait le prévisionnel quelque soit la tournante choisie en "H4". Je pense qu’il faut utiliser la fonction NB.SI mais je n’arrive à rien d’autre qu’à obtenir le nombre de nuit total à l’année si je fais :

=NB.SI(E15:AN45;"N")

Je bloque sur la condition des 10 nuits.
J’espère être assez précis
Cordialement

Philippe

 

[Ninter]

Re : Formule sous conditions

Bonjour,
Pas sûr d'avoir tout compris mais une ébauche quand même!

Ninter

 

[fenec]

Re : Formule sous conditions

Re, bonjour Ninter

Viens de voir votre proposition mais elle ne correspond pas à mon attente.
Avec votre solution, vous calculez les droits à l’année alors que je voudrais que les droits s’incrémentent mensuellement lorsque 10 nuits sont effectuées et que ce calcul soit perpétuel d’une année à l’autre en prenant en compte les nuits non utilisées sur l’année en cours.

A+

 

[job75]

Re : Formule sous conditions

Bonsoir fenec, salut Ninter,

Avec le tableau qui se modifie chaque mois il faut passer par du VBA.

Dans le code de la feuille "Calendrier" :

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [C4,E4]) Is Nothing Then
  RCN [C8], [T8]
  'autres macros éventuellement
End If
End Sub

Sub RCN(report As Range, resu As Range)
Dim mois&, i&, jours&
mois = Int(Val(CStr([C4])))
If mois < 1 Then MsgBox "Mois non valide !": Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les événements
report = Val(CStr(report)) 'sécurité
jours = report
For i = 1 To mois
  [C4] = i
  jours = jours + Application.CountIf([F15:F45], "N")
Next
resu = Int(jours / 10)
Application.EnableEvents = True
Application.ScreenUpdating = True
If mois = 12 Then MsgBox jours - 10 * resu & _
  " jours RCN à reporter sur l'année suivante", , "Report RCN"
End Sub

La cellule C8 doit contenir le report de jours de l'année précédente.

Le résultat est bien sûr en cellule T8.

Fichier joint.

A+

 

[job75]

Re : Formule sous conditions

Re,

Bon pas besoin de report si l'on calcule les RCN depuis la date indiquée en M8 :

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [C4,E4]) Is Nothing Then
  RCN [M8], [T8]
  'autres macros éventuellement
End If
End Sub

Sub RCN(debut As Range, resu As Range)
Dim mois&, an&, i&, jours&
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les événements
mois = Int(Abs(Val(CStr([C4]))))
an = Int(Abs(Val(CStr([E4]))))
[E4] = Year(debut)
For i = Month(debut) To DateDiff("m", debut, DateSerial(an, mois, 1)) + 1
  [C4] = i
  jours = jours + Application.CountIf([F15:F45], "N")
Next
resu = Int(jours / 10)
[C4] = mois
[E4] = an
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Fichier (2).

Edit : le message au début n'était pas indispensable.

A+

 

[fenec]

Re : Formule sous conditions

Bonsoir Job75

Viens juste de voir tes réponses je te tiens au courant demain dès que possible pour l'heure je part au travail étant de nuit .
Bonne fin de soirée et déjà merci pour venir une fois de plus à mon secours
A+

Philippe

 

[job75]

Re : Formule sous conditions

Re,

Le calcul est beaucoup plus rapide en incrémentant les années au lieu des mois :

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [C4,E4,M8]) Is Nothing Then
  RCN [M8], [T8]
  'autres macros éventuellement
End If
End Sub

Sub RCN(debut As Range, resu As Range)
Dim mois&, an&, jours&, i&
mois = Int(Val(CStr([C4])))
an = Int(Abs(Val(CStr([E4]))))
If mois < 1 Or mois > 12 Then _
  MsgBox "Entrez un mois entre 1 et 12": resu = "": Exit Sub
If DateSerial(an, mois, 1) >= debut Then
  Application.ScreenUpdating = False
  Application.EnableEvents = False 'désactive les événements
  [C4] = 1
  [E4] = Year(debut)
  '---déduction des mois du début---
  If Month(debut) > 1 Then _
    jours = -Application.CountIf([E15:E45].Resize(, 3 * Month(debut) - 3), "N")
  '---années entières---
  For i = [E4] To an - 1
    [E4] = i
    jours = jours + Application.CountIf([E15:AN45], "N")
  Next
  '---dernière année---
  [E4] = an
  jours = jours + Application.CountIf([E15:E45].Resize(, 3 * mois), "N")
  [C4] = mois
  Application.EnableEvents = True
End If
resu = Int(jours / 10)
End Sub

Fichier (3).

Edit : ajouté M8 dans Intersect(Target, [C4,E4,M8])

A+

 

[fenec]

Re : Formule sous conditions

Bonjour le forum, Job75

Venant de regarder tes solutions la troisième serait génial mais….
Etant partie sur l’idée d’une formule et non d'une macro évènementielle, le problème est que :

1. Je n’ai pas dans ma demande précisé que l’on ne récupère 1 RCN toutes les 10 nuits que si tournantes en H4 : 3x8 ou 5x8 en effet cette convention ne s’applique pas pour les SD.
2. Pour obtenir le prévisionnel il faut changer à chaque fois le mois en D4 serait-il possible d’obtenir le résultat automatiquement en ajoutant une cellule mois(aujourdhui) par exemple afin de laisser le calendrier comme défini au départ ou part la cellule changement de poste B7.
3. Le résultat obtenu est en jours, j’ai essayé de modifier le code pour obtenir des heures mais rien à faire suis vraiment pas doué avec le VBA.
J’ai essayé ça mais bien sur ca ne fonctionne pas.

resu = Int(jours / 10)*8

4. Cette demande étant liée à celle pour laquelle tu m’as déjà aide, te joint un fichier sans données confidentielles car je ne parviens pas à adapter ton code vu que mon calendrier n’est pas sur la même page comme dans mon premier fichier exemple.
5. Un dernier souci c’est qu’avec l’insertion de ligne définir la cellule de départ risque de poser problème puisque volatile.

Pardonne moi pour toutes ces remarques mais j’essaie, enfin, j’espère d’avoir relevé tous les soucis. Je comprends que tu ne poursuives pas vu la demande et le travail à effectuer car tu as déjà fait un super travail et t’en remercie.

Bonne soirée, je pars au boulot

Cordialement

Philippe

 

[fenec]

Re : Formule sous conditions

Bonsoir le forum,

Ne parvenant pas à m’en sortir avec le vba de Job75 (trop peut de connaissance) me permet un petit up au cas ou quelqu’un aurait une solution par formule .

Cordialement

Philippe