Microsoft 365 Combien de groupes d'un nombre défini de cellules vide sur un ensemble de plages non contigües

QualitéNG · 23 Novembre 2021

Bonjour à toutes et à tous,

J'ai appris à utiliser Excel dans le cadre professionnel à l'aide de tutos mais je suis aujourd'hui dépassé, j'ai besoin d'aide. Merci pour vos réponses.

Je gère pour mon employeur un planning annuel de collaborateurs ayant un grand nombre de conditions de remplissage. Je ne peux pas créer un roulement prédéfini, mais je peux créer des aides de remplissage en fonction des conditions. L'une d'elle est l'équilibre entre salarié.

On a de la chance de travailler dans une entreprise où on peut dégager des WE de 5j ou plus. Je voudrais compter ces WE pour chacun afin d'équilibrer au besoin.

Il y a une cinquantaine de collaborateurs dans un tableau de 700 lignes (les jours de l'année + les lignes de calculs). Je dois trouver une formule pour compter les WE de 5j (ou plus) par personne automatiquement.

Ce n'est pas très compliqué en soit, j'ai trouvé une formule pour cela.

Mais voilà, j'aime bien faire compliqué. Entre un dimanche et un lundi, j'ai des lignes de calcul. Elles séparent les semaines et mes plages ne sont pas contigües.

Je ne sais pas comment faire avec mes connaissances limitées. Pour information je ne maîtrise pas le VBA (il faudrait que je me penche dessus un de ces jours).

J'ai mis un fichier en lien pour illustrer avec un cas simple.

Merci à tous pour vos conseils.

QualitéNG · 1 Novembre 2022

Bonjour à vous,

Je pensais avoir résolu mon problème en adaptant le VBA proposé par @sylvanu. Du moins cela fonctionnait lors de mes précédents tests.

Mais voilà, aujourd'hui je devais concrétiser l'essai et je n'obtiens pas le résultat escompté. J'ai le résultat, j'ouvre le fichier sur un autre PC et je perds le résultat. Bref, mes connaissances sont trop limitées en VBA, c'est sans doute banal mais j'abandonne cette piste prometteuse et je reviens à ce que je connais.

Finalement j'ai trouvé en imbriquant plusieurs formules. Ce n'est franchement pas élégant mais ça fonctionne.

Comme dit précédemment, je suis contraint de figer le nombre de lignes de calcul entre chaque semaine. Disons 2 dans mon exemple. Au final, je ne...

JHA · 23 Novembre 2021

Bonjour à tous,

Peut-être un début de piste

JHA

sylvanu · 23 Novembre 2021

Bonjour QualitéNG,

QualitéNG à dit:
il faudrait que je me penche dessus un de ces jours

C'est toujours ce qu'on dit.

Alors en PJ un bon moyen de commencer avec une fonction simple :

VB:

Function CompteWE5j(Nom$, Plage As Range)
    For Each C In Plage                             ' Pour toutes les cellules de Plage
        Ligne = C.Row                               ' On extrait le N° de ligne
        If Cells(Ligne, "A") <> "" Then             ' Si la cellule en A est non vide alors
            If C = "" Then                          ' Si la cellule de la colonne considérée est vide
                N = N + 1                           ' On la compte
            Else
                N = 0                               ' Sinon on remet le compteur à 0
            End If
        End If
        If N = 5 Then                               ' Si le compteur à atteint 5
            CompteWE5j = CompteWE5j + 1             ' On renvoie +1 dans la fonction
            N = 0
        End If
    Next C
End Function

La syntaxe à utiliser est : =CompteWE5j(Prénom;Plage)

( NB: Par formule je ne vois pas comment faire. Chacun son truc. )

QualitéNG · 23 Novembre 2021

Magique, super rapide. Je vais étudier tout ça !

QualitéNG · 23 Novembre 2021

Merci à @JHA et à @sylvanu pour vos réponses éclaires.

La solution de JHA correspond mieux à mes connaissances mais je n'ai pas encore étudier. Ca me parait vraiment complexe à mettre à en place. Je vais l'étudier dans un second temps.

Quant à la solution VBA, c'est très séduisant. C'est celle que je viens d'étudier. Bon maintenant, avec ma logique de non initié, j'entrevois l'idée mais je ne comprends pas tout.

Première question de celui qui ne connait pas : comment cette fonction extrait de son comptage les lignes intermédiaires ?
Deuxième question de celui qui n'y comprend rien : pourquoi quand j'utilise sur les 2 premières semaines, nickel, et quand j'étends ma sélection à 6 semaines, cela ne fonctionne plus ?!
Troisième question de celui qui devrait mieux se former : comment la fonction gère les semaines suivantes non remplies ?

Evidemment j'ai utilisé cette fonction dans mon fichier de base (en prenant soin de copier le code VBA en amont). J'ai cru un instant que cela fonctionnait (en voyant le résultat attendu apparaitre) et pouf mon résultat est revenu à 0. Il doit y avoir une tonne de choses évidentes que je n'ai pas en connaissance.

sylvanu · 23 Novembre 2021

Re, Bonsoir JHA,

QualitéNG à dit:
comment cette fonction extrait de son comptage les lignes intermédiaires ?

Je ne tiens compte que des lignes où en colonne A on a quelque chose. Donc les lignes où il y a "calculs" sont automatiquement ignorées.

QualitéNG à dit:
j'étends ma sélection à 6 semaines, cela ne fonctionne plus ?!

Car pour la semaine 5 il n'y a rien en colonne A donc ignorées.
En PJ j'ai modifiée la macro pour rendre le calcul automatique sans avoir à faire Recalculer.

NB: Pour les semaines vides la colonne A doit être vide sinon il faut modifier la macro en conséquence.

JHA · 23 Novembre 2021

Bonjour à tous,
Bonjour Sylvanu,

J'ai ajouté la gestion d'erreur

JHA

QualitéNG · 23 Novembre 2021

Vous êtes au top !

sylvanu à dit:
Je ne tiens compte que des lignes où en colonne A on a quelque chose. Donc les lignes où il y a "calculs" sont automatiquement ignorées.

Ok je me sens nul c'était évident. Il faudra donc que je fasse une modification du type :
If Cells(Ligne, "A") = "l" or = "m" or "j" or "v" or "s" or "d" Then (désolé je découvre les fonctionnalités du forum en même temps, ça fait 10 ans que je n'en ai pas parcourru)
Ca passe en VBA ?

sylvanu à dit:
NB: Pour les semaines vides la colonne A doit être vide sinon il faut modifier la macro en conséquence.

Dac facile, il suffit de rajouter une condition sur la première ligne de calcul. Il s'agit d'une ligne qui compte le nombre de jours dans la semaine. Du type :
If Cells(Ligne, "A") = "J S*" and C= -0 'Le nom des calculs est J S1 ou J S2
End function
Ca passerait ? Comment faire ?

sylvanu à dit:
En PJ j'ai modifiée la macro pour rendre le calcul automatique sans avoir à faire Recalculer.

Nickel !

sylvanu à dit:
C'est toujours ce qu'on dit.

Mais c'est très séduisant, je dois vraiment m'y mettre, tu m'as convaincu !

JHA à dit:
J'ai ajouté la gestion d'erreur

Je viens de voir ça. Je ne comprends pas les formules, ça m'intéresse beaucoup, je dois pouvoir en tirer un enseignement, comment la fonction MOD se rend utile ici ?! Je vais étudier ça ^^

En tout cas merci merci, je ne regrette pas mon inscription !

sylvanu · 23 Novembre 2021

Bonsoir,

QualitéNG à dit:
If Cells(Ligne, "A") = "l" or = "m" or "j" or "v" or "s" or "d" Then

Non, la syntaxe est :

VB:

If Cells(Ligne, "A") = "l" or Cells(Ligne, "A")= "m" or Cells(Ligne, "A")="j" or Cells(Ligne, "A")="v" or Cells(Ligne, "A")="s" or Cells(Ligne, "A")="d" Then

Mais à quoi servirait cette ligne ? Il suffit de faire :

Code:

If Cells(Ligne, "A") <> "" Then

En PJ j'ai modifié la macro :
1- La syntaxe est =CompteWE5j(B4:B1000) le prénom a disparu, il ne servait à rien.
2- On peut avoir des semaines "vides", on ne tient compte que des semaines remplies
3- Donc on peut prévoir des semaines à venir vides, elles seront ignorées.
4 J'ai modifier les N° semaines avec un chiffre et un format personnalisé. C'est plus simple car c'est automatique.

JHA · 24 Novembre 2021

Bonjour à tous,

Ton fichier est constitué de 7 lignes pour la semaine plus 2 lignes de calcul donc toutes les 9 lignes on a les mêmes données.
La fonction mod(ligne();9) te permet de constituer une séquence de numéros (de 0 à 8).

La formule "=SOMMEPROD((MOD(LIGNE(B$4:B$39)-4;9)=8)*(B$4:B$39>=5))" recherchera dans chaque N°8 de la séquence mod() si la valeur est supérieure ou égale à 5.

Dans le premier fichier , j'ai volontairement laissé le calcul de la fonction mod() afin de te faciliter la compréhension de cette fonction.

JHA

QualitéNG · 15 Décembre 2021

Bonjour à vous,

J'ai enfin pu testé votre solution (retour de vacances). J'ai été séduit par celle faisant appel au VBA.

Malheureusement cela ne fonctionne pas. J'ai adapté la solution à mon cas. De toute évidence, mes connaissances sont trop limitées.

J'ai modifié le fichier pour qu'il colle plus à mon cas. Autant avoir le cas recherché.
J'ai basé la condition sur la colonne contenant le numéro de semaine. Est-ce que le fait que les cellules soient fusionnées en colonne pose problème ? A tous les coups oui. J'ai tenté en faisant la condition sur la colonne des jours avec l, m, j, v, s, ou d, mais cela ne change guère les choses.
Ensuite la plage concernée est potentiellement variable en fonction des employées. J'ai donc fait appel à une fonction indirect (je sais j'aime la vie compliquée). En plus je fais apparaître le résultat sur une autre feuille.
Quelle erreur j'ai commise ?

Je ne peux pas envoyer le fichier pour des raisons de confidentialité mais j'ai un autre souci. Il fait 1,5Mo, j'ai des synchro drive foireuses. J'ai eu beau l'alléger, assurer une connexion en fibre, utiliser un pc plus puissant et plus récent, la synchro foire. Mon informaticien a tout essayé mais rien n'y fait. Des idées ?
De plus j'ai beaucoup de formules et de MFC. Est-ce que ces formules et MFC ne posent pas finalement problème. Par exemple quand je fais une modif, le fichier rame comme pas possible avant de la prendre en compte. Du coup est-ce que le VBA ne pourrait pas m'aider pour les MFC ?

sylvanu · 16 Décembre 2021

Bonjour,
En PJ un essai. La colonne repère est la D et non la B.
A noter que pour Jack et Paul, je ne trouve pas votre attendu. Votre avis ?

QualitéNG · 3 Septembre 2022

Bonjour à vous,

On dit souvent "quand on y arrive plus, on met de coté pour revenir plus tard, on y verra sans doute plus clair". Et bien j'ai mis de coté pendant 9 mois car je n'y arrivais pas.

Je suis revenu sur le problème il y a quelques minutes et j'ai trouvé une solution. C'est du bricolage mais ça fonctionne très bien.

Je connais le nombre de lignes de calcul que j'ai entre chaque semaine, dans mon exemple 2. Je vais faire en sorte que ce nombre devienne une constante. J'ai donc repris le VBA proposé par @sylvanu et au lieu de compter les cellules vides, j'ai compté les cellules différentes de mon jour de travail. Je n'ai plus qu'à soustraire le nombre de lignes de calcul (ici 2) et si j'atteins 5 je comptabilise.

Cela me contraint à figer le nombre de lignes de calcul entre chaque semaine (easy !) et à adapter le code aux différents jours de travail (il n'y a pas forcément écrit "travaille", ça dépend des types de journée, je devrais pouvoir m'en sortir)

Le problème est donc résolu.

Merci beaucoup pour votre aide, j'ai d'autres problématiques que je vais m'empresser de vous présenter sur d'autres discussions.

QualitéNG · 1 Novembre 2022

Bonjour à vous,

Je pensais avoir résolu mon problème en adaptant le VBA proposé par @sylvanu. Du moins cela fonctionnait lors de mes précédents tests.

Mais voilà, aujourd'hui je devais concrétiser l'essai et je n'obtiens pas le résultat escompté. J'ai le résultat, j'ouvre le fichier sur un autre PC et je perds le résultat. Bref, mes connaissances sont trop limitées en VBA, c'est sans doute banal mais j'abandonne cette piste prometteuse et je reviens à ce que je connais.

Finalement j'ai trouvé en imbriquant plusieurs formules. Ce n'est franchement pas élégant mais ça fonctionne.

Comme dit précédemment, je suis contraint de figer le nombre de lignes de calcul entre chaque semaine. Disons 2 dans mon exemple. Au final, je ne cherche plus 5j minimum entre 2 jours de travail mais 7 car 5 cellules vides + 2 cellules de calcul.

Par chance, les jours de travail sont des cellules contenant du texte ce qui les différentient des calculs intermédiaires. Donc j'ai créé une matrice de fréquence des jours consécutifs contenant du texte (c'est la première fois que j'utilise cette formule, il y a sans doute moyen d'optimiser tout cela car je ne la maitrise pas).

Une fois cette matrice créée, je la filtre pour en tirer uniquement une matrice contenant des séquences supérieur ou égale à 7. Cela réduit grandement le nombre de lignes.

Maintenant j'ai une matrice comportant autant de lignes que de WE d'au moins 5J. Je compte les lignes et c'est gagné.

Je remercie les gens qui prennent le temps de répondre à nos problèmes. Cela m'a permis de progresser. Mon problème est résolu mais si quelqu'un est motivé pour optimiser mon code, je suis preneur. J'ai toujours un fichier qui subit des lenteurs à cause du manque d'optimisation. J'ai toujours besoin d'apprendre.

Je joins mon fichier exemple pour illustrer.

Microsoft 365 Combien de groupes d'un nombre défini de cellules vide sur un ensemble de plages non contigües

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

XLDnaute Nouveau

Pièces jointes

Discussions similaires