XL 2019 Sommer colonne critère texte.

Créer une discussion
Répondre

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
A

aurelie74

XLDnaute Nouveau
Bonjour,
Difficile d'expliciter clairement le sujet de discussion.... veuillez m'en excuser...
Ci-joint un planning mensuel plutôt banal avec par salarié, par jour, un type d'évènement.
Le type d'évènement sélectionné déclenche un nombre d'heure théorique (paramétré dans un autre onglet).
Le nombre d'heure réel sera renseigné ultérieurement.
Chaque évènement est également référencé (paramétré dans un autre onglet) comme appartenant à une tournée du matin ou du soir.
En fin de tableau, un recap souhaité du nombre d'heure totale théorique affecté par tournée ; soit par matin et par soir.

Je bloque sur la formule qui me permettrai de sommer les heures correspondantes à l'évènement 'matin' et 'soir'.
Surement des imbrications de somme prod, index, equiv, decaler...
Mais je tourne en rond...
merci pour votre aide précieuse.

=> résultat souhaité dans l'onglet matrice en lignes 61
 

Pièces jointes

Solution
sylvanu
Re,
Ca me semble trop complexe en formules, alors une PJ avec deux fonctions perso :
VB: 
Function HeuresMatin(Plage As Range)
    Dim Evenement, cell, Temps
    Set Evenement = Sheets("PARAMETRES").[G2:J22]
    For Each cell In Plage
        If cell <> "" And Not IsNumeric(cell) Then
            On Error Resume Next
            Temps = 0
            Select Case Application.VLookup(cell, Evenement, 4, 0)
                Case "M":   Temps = Application.VLookup(cell, Evenement, 2, 0)
                Case "M+S": Temps = Application.VLookup(cell, Evenement, 2, 0) / 2
            End Select
            HeuresMatin = HeuresMatin + Temps
        End If
    Next cell
End Function
VB: 
Function HeuresSoir(Plage As Range)
    Dim Evenement, cell...
Trier par date Trier par votes
Re,
Sans réponse, on peut au moins traiter le Total Journée avec une fonction perso :
VB: 
Function TotalHeure(Plage As Range)
    Dim Evenement, cell
    Set Evenement = Sheets("PARAMETRES").[G2:H22]
    For Each cell In Plage
        If cell <> "" And Not IsNumeric(cell) Then
            On Error Resume Next
            TotalHeure = TotalHeure + Application.VLookup(cell, Evenement, 2, 0)
        End If
    Next cell
End Function
Mais encore une question :
On comptabilise par rapport aux heures dans Matrice, ou dans Paramètres ?
La macro compte à partir de Paramètres.
 

Pièces jointes

Vote positif 0 Vote négatif
ALS35 à dit:
Bonjour,
Il serait bien de nous dire quels événements appartiennent à Matin ou Soir
Cordialement
Cliquez pour agrandir...
Bonjour, effectivement je n'ai pas été suffisamment précise. La catégorie des événements se trouve dans l'onglet paramètres puisque c'est voué à être une donnée paramétrable selon l'entité
sylvanu à dit:
Bonjour,
Et précisez si M+S doit être comptabilisé comme Matin ou Soir.
Cliquez pour agrandir...
Dans l'idéal, M+S doit pour moitié d'affecter pour moitié au matin et pour moitié au soir !
 
Vote positif 0 Vote négatif
sylvanu à dit:
Re,
Sans réponse, on peut au moins traiter le Total Journée avec une fonction perso :
VB: 
Function TotalHeure(Plage As Range)
    Dim Evenement, cell
    Set Evenement = Sheets("PARAMETRES").[G2:H22]
    For Each cell In Plage
        If cell <> "" And Not IsNumeric(cell) Then
            On Error Resume Next
            TotalHeure = TotalHeure + Application.VLookup(cell, Evenement, 2, 0)
        End If
    Next cell
End Function
Mais encore une question :
On comptabilise par rapport aux heures dans Matrice, ou dans Paramètres ?
La macro compte à partir de Paramètres.
Cliquez pour agrandir...
Bonjour et merci pour votre aide.
Normalement, la ligne des heures théoriques de l'onglet 'matrice' reprend les heures indiquées dans 'paramètres' donc les 2 conviennent
 
Vote positif 0 Vote négatif
Re,
Ca me semble trop complexe en formules, alors une PJ avec deux fonctions perso :
VB: 
Function HeuresMatin(Plage As Range)
    Dim Evenement, cell, Temps
    Set Evenement = Sheets("PARAMETRES").[G2:J22]
    For Each cell In Plage
        If cell <> "" And Not IsNumeric(cell) Then
            On Error Resume Next
            Temps = 0
            Select Case Application.VLookup(cell, Evenement, 4, 0)
                Case "M":   Temps = Application.VLookup(cell, Evenement, 2, 0)
                Case "M+S": Temps = Application.VLookup(cell, Evenement, 2, 0) / 2
            End Select
            HeuresMatin = HeuresMatin + Temps
        End If
    Next cell
End Function
VB: 
Function HeuresSoir(Plage As Range)
    Dim Evenement, cell, Temps
    Set Evenement = Sheets("PARAMETRES").[G2:J22]
    For Each cell In Plage
        If cell <> "" And Not IsNumeric(cell) Then
            On Error Resume Next
            Temps = 0
            Select Case Application.VLookup(cell, Evenement, 4, 0)
                Case "S":   Temps = Application.VLookup(cell, Evenement, 2, 0)
                Case "M+S": Temps = Application.VLookup(cell, Evenement, 2, 0) / 2
            End Select
            HeuresSoir = HeuresSoir + Temps
        End If
    Next cell
End Function
( Toutes les catégories où il n'y a rien ne sont pas prises en compte )
 

Pièces jointes

Vote positif 0 Vote négatif
Solution
sylvanu à dit:
Re,
Ca me semble trop complexe en formules, alors une PJ avec deux fonctions perso :
VB: 
Function HeuresMatin(Plage As Range)
    Dim Evenement, cell, Temps
    Set Evenement = Sheets("PARAMETRES").[G2:J22]
    For Each cell In Plage
        If cell <> "" And Not IsNumeric(cell) Then
            On Error Resume Next
            Temps = 0
            Select Case Application.VLookup(cell, Evenement, 4, 0)
                Case "M":   Temps = Application.VLookup(cell, Evenement, 2, 0)
                Case "M+S": Temps = Application.VLookup(cell, Evenement, 2, 0) / 2
            End Select
            HeuresMatin = HeuresMatin + Temps
        End If
    Next cell
End Function
VB: 
Function HeuresSoir(Plage As Range)
    Dim Evenement, cell, Temps
    Set Evenement = Sheets("PARAMETRES").[G2:J22]
    For Each cell In Plage
        If cell <> "" And Not IsNumeric(cell) Then
            On Error Resume Next
            Temps = 0
            Select Case Application.VLookup(cell, Evenement, 4, 0)
                Case "S":   Temps = Application.VLookup(cell, Evenement, 2, 0)
                Case "M+S": Temps = Application.VLookup(cell, Evenement, 2, 0) / 2
            End Select
            HeuresSoir = HeuresSoir + Temps
        End If
    Next cell
End Function
( Toutes les catégories où il n'y a rien ne sont pas prises en compte )
Cliquez pour agrandir...
Ouha super ! merci beaucoup pour votre réactivité et pour votre aide précieuse qui fonctionne !
Je ne suis pas suffisamment à l'aise avec les macros et pensais plutôt à une formule mais c'est exactement ce qu'il me fallait. merci
 
Vote positif 0 Vote négatif
Bonjour à tous,
Hello Sylvanu,
Une tentative en formules en créant les listes de catégories dans l'onglet Paramètres
VB: 
matin en J61
=SOMMEPROD(SOMME.SI.ENS(J$14:J$59;J$12:J$57;L_M))+SOMMEPROD(SOMME.SI.ENS(J$14:J$59;J$12:J$57;L_MS))/2
soir en J62
=SOMMEPROD(SOMME.SI.ENS(J$14:J$59;J$12:J$57;L_S))+SOMMEPROD(SOMME.SI.ENS(J$14:J$59;J$12:J$57;L_MS))/2
Cordialement
 

Pièces jointes

Vote positif 0 Vote négatif
ALS35 à dit:
Bonjour à tous,
Hello Sylvanu,
Une tentative en formules en créant les listes de catégories dans l'onglet Paramètres
VB: 
matin en J61
=SOMMEPROD(SOMME.SI.ENS(J$14:J$59;J$12:J$57;L_M))+SOMMEPROD(SOMME.SI.ENS(J$14:J$59;J$12:J$57;L_MS))/2
soir en J62
=SOMMEPROD(SOMME.SI.ENS(J$14:J$59;J$12:J$57;L_S))+SOMMEPROD(SOMME.SI.ENS(J$14:J$59;J$12:J$57;L_MS))/2
Cordialement
Cliquez pour agrandir...
Merci pour cette proposition de formule qui me parle davantage que la macro !
Avec toutefois une petite modification si je peux me permettre : =SOMMEPROD(SOMME.SI.ENS(DECALER(J$12:J$59;1;0);J$12:J$59;L_M))+SOMMEPROD((SOMME.SI.ENS(DECALER(J$12:J$59;1;0);J$12:J$59;L_MS))/2)
Mais c'est super !
 
Vote positif 0 Vote négatif
aurelie74 à dit:
Merci pour cette proposition de formule qui me parle davantage que la macro !
Avec toutefois une petite modification si je peux me permettre : =SOMMEPROD(SOMME.SI.ENS(DECALER(J$12:J$59;1;0);J$12:J$59;L_M))+SOMMEPROD((SOMME.SI.ENS(DECALER(J$12:J$59;1;0);J$12:J$59;L_MS))/2)
Mais c'est super !
Cliquez pour agrandir...
Re,
La formule que je t'ai proposée compte les heures réelles, et la tienne compte les heures théoriques, mais peut-être n'avais-je pas compris !
Cordialement
 
Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

B
  • Question Question
XL 2019 Somme des valeurs d'une colonne de listbox en fonction d'un critère
Réponses
1
Affichages
878
vgendron
vgendron
G
  • Résolu(e)
Microsoft 365 Formule avec pour référence le résultat d'une autre formule pour indiquer la colonne de référence
Réponses
1
Affichages
614
JHA
J
H
  • Question Question
Microsoft 365 Faire la somme de la colonne j2 à jx avec X écris dans une cellule
Réponses
2
Affichages
838
hemg68
H
M
  • Question Question
Somme de plusieurs colonnes avec une seule colonne de critère
Réponses
10
Affichages
3 K
job75
job75
P
  • Question Question
Sommer differentes colonnes sur même ligne avec critère texte
Réponses
1
Affichages
722
Guest
G
D
  • Question Question
Problème de somme automatisé avec un nombre de colonne variant
Réponses
7
Affichages
1 K
dealise
D
B
  • Question Question
Somme.si.ens avec calcul sur le critère
Réponses
7
Affichages
4 K
PhY
P
L
  • Question Question
nb.si avec critère dans une autre colonne
Réponses
5
Affichages
11 K
cecilette
cecilette
E
  • Question Question
Somme par période
Réponses
6
Affichages
1 K
Eljarod
E
Retour