Automatiser un calcul d'un nombre de cellules de même couleur

Bonjour,

Je débute en VBA. J'ai réussi en copiant une formule de calculer le nombre de cellules de couleur différentes. Par contre je souhaiterai que ce calcul s'applique en bas de chaque colonne et sur la fin des lignes d'une même semaine. je l'ai fait manuellement dans le fichier test. Par contre je n'arrive pas à créer un bouton sur lequel je peux cliquer à chaque fois qu'un changement est fait ou alors le must c'est automatiser le changement.

merci de votre aide. Aujourd'hui je le fais à la main

Bonjour,

Voir ton fichier si j'ai bien compris!

Slts

Bonjour,

Pas besoin de bouton si vous utilisez une fonction personnalisée.

Pour comptabiliser les matin en ligne 58:

Function NbColorMat(Plage As Range) As Long
    For Each Cell In Plage
      If Left(Cell, 2) = "7h" Then NbColorMat = NbColorMat + 1
    Next
End Function

pour comptabiliser les soirs en ligne 59:

Function NbColorSoir(Plage As Range) As Long
    For Each Cell In Plage
      If Left(Cell, 3) = "13h" Then NbColorSoir = NbColorSoir + 1
    Next
End Function

pour comptabiliser les semaines en colonne I:

Function NbColorSem(Plage As Range) As Long
    For Each Cell In Plage
      If Left(Cell, 2) = "7h" Or Left(Cell, 3) = "13h" Then NbColorSem = NbColorSem + 1
    Next
End Function

Cdlt

Bonjour,

quand je télécharge le fichier , j'ai les semaines 9 et 10 qui reste avec #valeur et je ne comprends pas pourquoi.

Par contre pour la deuxième réponse c'est un peu plus compliqué pour mon niveau je ne comprends pas la formule.

merci de votre retour

Bonjour,

Par contre pour la deuxième réponse c'est un peu plus compliqué pour mon niveau je ne comprends pas la formule.

Les formules proposées ont été créé en vba, c'est ce qu'on appelle des fonctions personnalisées.

Avec le fichier joint, ce sera peut-être plus clair pour vous.

Cdlt

Bonjour,

Effectivement avec le fichier je comprends mieux : merci.

Par contre comment je fais pour ajouter au nombre du soir ceux qui commence à 14H15 ? et pour comptabiliser la semaine ce sont des heures que je veux comptabiliser et non pas les couleurs

Désolée je me suis mal exprimée et en plus je débute double peine ...

Bonjour à tous,

En reprenant la proposition de @Arturo83,

Par contre comment je fais pour ajouter au nombre du soir ceux qui commence à 14H15 ?

Function NbColorSoir(Plage As Range) As Long
    For Each Cell In Plage
      If (Left(Cell.Value, 3) = "13h") Or (Left(Cell.Value, 5) = "14h15") Then
        NbColorSoir = NbColorSoir + 1
      End If
    Next
End Function

Pour comptabiliser la semaine ce sont des heures que je veux comptabiliser et non pas les couleurs

Là je n'ai pas bien compris "comment" vous "comptez les semaines" ? Je vois que la fonction de Arturo compte les cellules commençant par "7h" ou "13h" sur la ligne. Si le calcul est correct et que c'est juste le nom de la fonction qui ne vous convient pas, vous pouvez le changer à votre guise, par exemple :

Function NbSem(Plage As Range) As Long
…

Attention, ensuite dans le classeur il faut aussi changer dans les formules car vous allez avoir un #NOM? Puisque l'ancienne fonction n'existe plus.

Bonjour,

En fait en colonne j'avais pris la fonction som couleur et je la multiplie en fonction de la couleur par le nombre d'heures pour obtenir le nombre d'heures fait par semaine. là je ne sais pas comment faire

merci de votre aide

Bonjour,

Sur la ligne du nombre de matins (58), faites la somme du nombre de matins obtenu et multipliez la par la quantité d'heures que vous voulez. Quel est le problème ? Moi j'ai beaucoup de mal à vous suivre désolé.

Sans parler du fait qu'utiliser les couleurs me semble inutile puisque vos cellules contiennent déjà des infos. Dans Excel on ne calcule pas avec les couleurs, c'est juste visuel. Dans votre cas on peut lire le contenu des cellules c'est suffisant, plus simple, et sans erreur.

Bonjour,

En fait pour les colonnes j'ai besoin de savoir le nombre d'heures effectuées en fin de semaine car je calcule ensuite que sur 10 semaines je suis bien à 350H et les couleurs sont un repère visuel pour les professionnels voilà pourquoi j'ai ses contraintes et le nombre d'heures par couleur ne sont pas les mêmes j'ai des 7H, 8H, 9H

voilà pourquoi parfois tout est utile

Bonjour

Avec les modifications:

le code modifié:

Function NbColorMat(Plage As Range) As Long
    For Each Cell In Plage
        On Error Resume Next
        If Cell <> "" And Left(Cell, 1) / 24 <= 12 / 24 Then
            If Err.Number = 0 Then NbColorMat = NbColorMat + 1
        End If
        On Error GoTo 0
    Next
End Function

Function NbColorSoir(Plage As Range) As Long
    For Each Cell In Plage
        On Error Resume Next
        If Left(Cell, 2) * 1 / 24 >= 12 / 24 Then
            If Err.Number = 0 Then NbColorSoir = NbColorSoir + 1
        End If
        On Error GoTo 0
    Next
End Function

Function NbColorSem(Plage As Range) As Long
    For Each Cell In Plage
      If IsNumeric(Left(Cell, 1)) Then NbColorSem = NbColorSem + 1
    Next
End Function

Cdlt

Bon excusez moi mais vous vous tarabiscotez la tete pour quelque chose de très simple.

Regardez ci-joint comment procéder à plusieurs calculs, sans macros ni couleurs.

Bonsoir,

Très bien pour le calcul des heures par contre je n'ai plus le calcul du nombre de personnes qui travaillent le matin et le nombre le soir ou il faut que je mele vos deux réponses.

je n'ai plus le calcul du nombre de personnes qui travaillent le matin et le nombre le soir

Dans mon fichier, il y a bien ces résultats lignes 58 et 59

en ligne 58 il apparait 10 et en ligne 59 il apparait 4 alors que je devrais avoir 6 et 3 et en i8 je devrais avoir 36H

Bonjour,

Ci-joint un calcul du nombre de personnes par matin/soir, adapté aux plages données. Sinon oui vous pouvez utiliser les fonctions de arturo qui font le meme calcul mais en VBA.

J'ai essayé de corriger les calculs pour que cela fonctionne sur XL2019, dites moi si c'est bon, sinon il faudra peut etre rester en VBA.

Effectivement, j'ai fait une petite erreur, voici les codes corrigés pour les lignes 58 et 59

Function NbColorMat(Plage As Range) As Long
    For Each Cell In Plage
        On Error Resume Next
        If Cell <> "" And Left(Cell, 1) / 24 <= 12 / 24 Then
            If Err.Number = 0 Then NbColorMat = NbColorMat + 1
        End If
        On Error GoTo 0
    Next
    For Each Cell In Plage
        On Error Resume Next
        If Left(Cell, 2) * 1 / 24 >= 12 / 24 Then
            If Err.Number = 0 Then NbColorSoir1 = NbColorSoir1 + 1
        End If
        On Error GoTo 0
    Next
    NbColorMat = NbColorMat - NbColorSoir1
End Function

Function NbColorSoir(Plage As Range) As Long
    For Each Cell In Plage
        On Error Resume Next
        If Left(Cell, 2) * 1 / 24 >= 12 / 24 Then
            If Err.Number = 0 Then NbColorSoir = NbColorSoir + 1
        End If
        On Error GoTo 0
    Next
    For Each Cell In Plage
        On Error Resume Next
        If Left(Cell, 2) * 1 / 24 <= 12 / 24 Then
            If Err.Number = 0 Then NbColorMat1 = NbColorMat1 + 1
        End If
        On Error GoTo 0
    Next
    NbColorSoir = NbColorSoir - NbColorMat1
End Function

pour le total des heures par semaines il faut passer par un tableau de correspondance c'est ce qu"à fait saboh, (je ne vais pas refaire la même chose) donc reprendre ce qu'il à déjà fait et l'appliquer dans les colonnes de la semaine

Merci à tous les deux pour votre investissement. @Saboh merci cela fonctionne : est il possible d'avoir une petite explication pour que je puisse ensuite modifier les plages car je voudrais aussi prendre des plages en dessous : c'est à dire déplacer les tableaux que tu as ajouté et mettre des lignes supplémentaires de remplaçant à la place. Si j'ai bien compris pour que cela compte il faut que l'horaire parte à 7H et si je mets 8H cela ne compte plus : est ce exact ?

merci de votre retour

Bonjour,

Comme mentionné par Arturo, les formules font appel à un petit tableau que j'ai rajouté en bas "Correspondances", dans lequel sont inscrites les durées correspondantes aux plages que vous avez dans votre tableau (par exemple 7h-14h => durée = 7h). C'est ici que vous pouvez ajouter/supprimer des éléments, et les modifier (si par exemple il y a une pause). Ne touchez pas aux "$", ils permettent d'étendre les fonctions facilement.

Ensuite en I8 vous ne devriez pas avoir à modifier la formule, si ce n'est "B8:H8" qui correspond à la semaine. Mais si vous copiez/collez la cellule I8 en Q8 par exemple, la formule s'adapte automatiquement.

=SIERREUR(SOMMEPROD(INDEX(Table1[Durée (h)];EQUIV(B8:H8;Table1[Designation];0)));"")

La cellule est ensuite étirée jusqu'en bas.

Ensuite en B60 il y a la formule ci-dessous

=NB.SI(B$8:B$56;$A60)*INDEX(Table1;EQUIV($A60;$B$80:$B$87;0);2)

B8:B56 c'est la colonne de la journée, à étendre vers le bas si vous avez plus de lignes.

A60 c'est la référence à chercher dans le tableau de correspondances.

B80:B87 c'est la 1e colonne du tableau de correspondances. Malheureusement Excel est très bete et je ne peux pas bien automatiser cette partie, donc si vous rajoutez des lignes au tableau de correspondances, pensez à ralonger aussi cette partie.

Vous pouvez ensuite étirer vers le bas et la droite.

Enfin, les calculs du nombre de personnes le matin et le soir :

Là c'est plus compliqué, je me suis adapté à vos valeurs actuelles.

Les heures du matin sont celles commencant par 7 :

=SOMME(--(GAUCHE(B$8:B$56;1)="7"))

En voulant ajouter celles commençant par 8, il faut plutot utiliser la formule suivante

=SOMMEPROD((GAUCHE(B$8:B$56;1)="7")+(GAUCHE(B$8:B$56;1)="8"))

Vous comprenez le principe j'espère, pour commencer à 9 on rajouter la meme chose en changeant le numéro entre guillements.

Les heures du soir sont celles où les 2 premiers caractères ne contiennent pas "h" :

=SOMMEPROD(--NON(ESTVIDE(B$8:B$56));--ESTERREUR(CHERCHE("h";GAUCHE(B$8:B$56;2))))

Cependant cela veut dire que les plages "10h-15h" ou "11h-13h" sont considérées du soir. C'est pourquoi il est vraiment important de fournir un fichier représentatif de vos vraies valeurs. Par ailleurs je ne saurai trop vous recommander de suivre une formation Excel sur les formats horaires, ça vous(/nous) aurait évité bien des galères.

Pour ne prendre que les plages commençant après 12h, la formule suivante devrait convenir

=SOMMEPROD(--NON(ESTVIDE(B$8:B$56));--ESTERREUR(CHERCHE("h";GAUCHE(B$8:B$56;2)));--(SIERREUR(CNUM(GAUCHE(B$8:B$56;2));0)>=12))

Bonne journée.

Bonjour,

Votre explication est très claire et très compréhensive et je vous en remercie beaucoup. Je suis ok avec vous sur le fait de suivre une formation excel, c'est ce que j'ai demandé à mon entreprise mais pour l'instant normalement 2025 prévu au plan de formation....

Vos explications me permettront de faire des changements et ajouter des lignes en dessous. Encore merci beaucoup