Formule Somme Conditionnelle Et Jours Ouvres

[SOL]

Bonjour,

Je ne suis pas experte dans excel et me trouve devant un problème.
Je vous explique :
Je dois (pour mon job) créer un planning de gestion de présence, congés... et créer des colonnes récapitulatives en fin de tableau (comptabilisant le nombre de P, CA, RTT...) donc j'ai mis en place la formule suivante =NB.SI(C1:AI2;"p") pour la colonne des présences...
Mais voilà mon problème, si par erreur les salariés (qui remplissent eux même leur planning) saisissent un CA sur un samedi ou un dimanche , celui-ci est comptabilisé à tort.
Donc voilà ma question, comment rajouter ou modifier ma formule pour que seul le nombre de P ou CA sur les jours ouvrés comptent (sans bloquer la saisie sur le week-end car il peut y avoir des activités autres pour certains personnels) ????????
Je remercie d'avance et vivement celui ou celle qui pourra me venir en aide.
SOL

 

[TheLio]

Re : Formule Somme Conditionnelle Et Jours Ouvres

Salut l'forum et Sol,
tu peux créer une liste de caractères autorisés et ensuite avec >validation>liste>le nom de ta liste...
essaye et si ça coince mets nous un p'tit bout d'fichier
@++
TheLio

 

[Banzai64]

Re : Formule Somme Conditionnelle Et Jours Ouvres

Bonjour

Avec SommeProd

 

[fred65200]

Re : Formule Somme Conditionnelle Et Jours Ouvres

bonjour le fil,

un petit clin d'œil à Banzaï64

avec son fichier et une formule matricielle

=SOMME(SI((JOURSEM(A2:A43;1)<6)*(C2:C43="CA");1;0)) à valider par Ctrl + Shif + Enter

variante avec sommeprod en considérant que les données dans la colonne A sont des dates.
=SOMMEPROD((JOURSEM(A2:A43;2)<6)*(C2:C43="CA"))

Les jours passent et se ressemblent...

salutations
fred65200

 

[JNP]

Re : Formule Somme Conditionnelle Et Jours Ouvres

Bonjour ,
Je ne voudrais pas jouer les troubles fêtes, mais il me semble qu'il reste un petit défaut: les jours fériés .
Donc, soit il faut mettre un fe pour les jours fériés et les rajouter à la première formule de multiplication par 0 les sa et di, soit on peut détecter la couleur de la cellule par une fonction VBA type:

Function Couleur(Cellule)
If Cellule.Interior.ColorIndex = 4 Then Couleur = 0 Else Couleur = 1
End Function

et simplement mettre les samedis, dimanches et fériés en vert, tout en multipliant la formule par la condition

 

[wilfried_42]

Re : Formule Somme Conditionnelle Et Jours Ouvres

Bonjour à tous

une variante pour les jours feries
si j'ai compris, les dates sont entre A2 et A43

=Sommeprod((nb.jours.ouvres(A2:A43;A2:A43;plage_jours_feries)>0)*(C2:C43="CA")*1)

Ce devrait faire l'affaire

 

[SOL]

Re : Formule Somme Conditionnelle Et Jours Ouvres

Merci bcp pour tous vos coups de main, c'est génial !
Cpt, je ne suis pas experte et je n'ai pas réussi à appliquer la formule de Wilfried 42 concernant les jours fériés.
C'est exactement ça vous avez bien compris mon pb : je ne veux pas que les CA soient décomptés ni les week-end, ni les jours fériés.
Pour l'instant j'ai réussi à recopier la formule des jours ouvrés (la formule de Banzai 64) mais pas pour les jours fériés. Pouvez-vous m'aider de nouveau ?
Et puis, si je complique les choses, certains personnels peuvent avoir une activité le week-end, comment faire pour que ces P se reportent directement sur les samedis, dimanches ou fériés (pour les disinguer)
Comme je ne sais pas si je suis claire, je joints mon petit fichier.
Je vous remercie vraiment bcp.

 

[fred65200]

Re : Formule Somme Conditionnelle Et Jours Ouvres

bonsoir le fil

après m'être triturer les méninges un bon bout de temps, j'en arrive à la conclusion que la formule SOMMEPROD avec NB.JOURS.OUVRES ne peut pas fonctionner
NB.JOURS.OUVRES n'agit pas sur une plage de cellules mais une date de départ et une date de fin.

N'hésitez pas à me contredire si je me trompe car là je sèche.

je renvoie le classeur avec les modifications demandées ou ce que j'en ai compris. Je n'ai pas trouvé d'autre alternative qu'une formule matricielle pour les jours de semaine, les jours fériés, et les "CA"

Une plage nommée "JF_2007" pour les jours fériés

Une mise en forme conditionnelle pour les week ends et les jours fériés.
Bref c'est dans le classeur.

cordialement
fred65200

 

[Banzai64]

Re : Formule Somme Conditionnelle Et Jours Ouvres

Bonjour

Bon j'ai peut-être une solution (Faut tester)
avec SOMMEPROD


PS: La liste des jours fériés je l'ai piquée chez Excelabo
Rendons à César ce qui est à Excelabo
Ce lien n'existe plus

 

[fred65200]

Re : Formule Somme Conditionnelle Et Jours Ouvres

bonjour Banzaï64,
je regarde ton classeur.
en attendant
une petite fonction bien utile de Frédéric Sigoneau

Function JoursFeries(An)
Dim NbOr, Epacte, Ajust As Integer
Dim PLune, LPaques, Arr(10) As Long
If ActiveWorkbook.Date1904 Then Ajust = 1462
  'calcul du Lundi de Pâques
  NbOr = (An Mod 19) + 1
  Epacte = (11 * NbOr - (3 + Int(2 + Int(An / 100)) * 3 / 7)) Mod 30
  PLune = DateSerial(An, 4, 19) - ((Epacte + 6) Mod 30)
  If Epacte = 24 Then PLune = PLune - 1
  If Epacte = 25 And (An >= 1900 And An < 2200) Then PLune = PLune - 1

  LPaques = PLune - Weekday(PLune) + vbMonday + 7        'Lundi Pâques
  
  'tableau des fériés
  Arr(0) = DateSerial(An, 1, 1) - Ajust
  
  Arr(1) = LPaques - Ajust
  Arr(2) = LPaques + 38 - Ajust  'Ascencion
  Arr(3) = LPaques + 49 - Ajust  'Pentecôte
  Arr(4) = DateSerial(An, 5, 1) - Ajust
  Arr(5) = DateSerial(An, 5, 8) - Ajust
  Arr(6) = DateSerial(An, 7, 14) - Ajust
  Arr(7) = DateSerial(An, 8, 15) - Ajust
  Arr(8) = DateSerial(An, 11, 1) - Ajust
  Arr(9) = DateSerial(An, 11, 11) - Ajust
  Arr(10) = DateSerial(An, 12, 25) - Ajust
  
  'tri du tableau
  Dim i%, j%, K%, tmp
  For i = LBound(Arr) To UBound(Arr)
    j = i
    For K = j + 1 To UBound(Arr)
      If Arr(K) <= Arr(j) Then j = K
    Next K
    If i <> j Then
      tmp = Arr(j): Arr(j) = Arr(i): Arr(i) = tmp
    End If
  Next i
  
  'renvoi du résultat
  On Error GoTo Fin
  If Application.Caller.Rows.Count > 1 Then
    JoursFeries = Application.Transpose(Arr)
    Exit Function
  End If
Fin:
  JoursFeries = Arr
End Function 'fs

@+
fred65200

 

[Banzai64]

Re : Formule Somme Conditionnelle Et Jours Ouvres

Bonjour fred65200

Merci ca peut aider

 

[fred65200]

Re : Formule Somme Conditionnelle Et Jours Ouvres

re :

Finalement, je ne me suis pas assez trituré le méninge.

Je n'ai pas pensé à Jours=Ferie.
Je prend note.
Chapeau bas
Donc pour totaliser les "P" des jours fériés
=SOMMEPROD((Jours=Ferie)*(Abrev="P"))
plus simple que
=SOMMEPROD((NON(ESTNA(EQUIV(C2:I2;JF_2007;0))))*(C3:I3="P"))
Totalise les "P" des jours fériés

Je viens de réaliser que je l'avais presque. encore trop compliqué mais presque.
=SOMMEPROD((JOURSEM(C2:I2;2)<6)*(C3:I3="CA"))-SOMMEPROD((NON(ESTNA(EQUIV(C2:I2;JF_2007;0))))*(C3:I3="CA"))
la prochaine fois peut être*

merci et bonne nuit
fred65200

 

[JNP]

Re : Formule Somme Conditionnelle Et Jours Ouvres

Bonjour ,
Je sens que ma proposition n'a pas fait mouche, pourtant, les fonctions VBA donnent un résultat très acceptable. Ci joint un petit fichier d'essai sur le 1° semestre (je ne te conseille pas ta formule par semaine, il va y avoir 52 onglets par personnes...)
J'ai fait le tableau (faux car les résultats ne tiennent pas compte des samedis, dimanches et fériés)... C'est fait exprès
Normalement, une barre d'outil Planning devrait apparaître.
Sélectionne "Mettre samedis, dimanches et jours fériés", et en même temps que le tableau se mettra en forme, les calculs seront justes... (Grand merci à Fred65200 et Frédéric Sigoneau, j'ai utilisé leur formule miracle dans mon exemple )
Il te restera a utiliser les menus "RTT" et autres pour remplir les cases.
Si tu veux éviter les mauvaises manips des gens, tu verrouilles la feuille et tu rajoutes dans les macros le déverrouillage>/verrouillage !

 

[wilfried_42]

Re : Formule Somme Conditionnelle Et Jours Ouvres

bonjour à tous

Comme je me suis planté avec le sommeprod(nb.jours.ouvres parceque nb.jours.ouvres ne fonctionne pas sur des matrices.

Comme je n'aime pas rester sur un echec lol ben j'ai ecit la fonction que voici

Function gw_nbsi_ouvres(plage1 As Range, plage2 As Range, ferie As Range, test As Range, critere As String, gw_type As Byte) As Long
    Dim gwcel As Range, c As Long, gwfer As Range, gwtest As Range, drapeau As Boolean, i As Long
    Application.Volatile
    Dim tablo, tablo1
    tablo1 = Split(critere, ",")
    drapeau = True
    Select Case gw_type
        Case 1
            ReDim tablo(UBound(tablo1)) As Long
            For i = LBound(tablo1) To UBound(tablo1)
                tablo(i) = Val(tablo1(i))
            Next i
        Case 0
            ReDim tablo(UBound(tablo1)) As String
            For i = LBound(tablo1) To UBound(tablo1)
                tablo(i) = tablo1(i)
            Next i
        Case 2
            ReDim tablo(UBound(tablo1)) As Double
            For i = LBound(tablo1) To UBound(tablo1)
                tablo(i) = Val(tablo1(i))
            Next i
        Case 5
            ReDim tablo(UBound(tablo1)) As Date
            For i = LBound(tablo1) To UBound(tablo1)
                On Error GoTo erreur
                tablo(i) = CDate(tablo1(i))
                On Error GoTo 0
                GoTo suite
erreur:
                MsgBox "Vous avez une date incorrect dans votre liste"
                drapeau = False
                Exit For
suite:
            Next i
    End Select
    If drapeau = False Then Exit Function
    c = 0
    For Each gwcel In plage1
        c = c + 1
        drapeau = True
        For i = gwcel To plage2(c)
            If Weekday(i, vbMonday) > 5 Then drapeau = False
            For Each gwfer In ferie
                If i = gwfer Then drapeau = False
            Next
        Next i
        If drapeau = True Then
            drapeau = False
            For i = LBound(tablo) To UBound(tablo)
                If test(c) = tablo(i) Then drapeau = True
            Next i
        End If
        If drapeau = True Then gw_nbsi_ouvres = gw_nbsi_ouvres + 1
    Next
        
End Function

Fichier joint ---> ne tenir compte que de la 2eme feuille