Microsoft 365 VBA - EXCEL Compiler plusieurs lignes d'une extraction en un minimum de lignes

[Pb68]

Bonjour à tous,

Malgré quelques recherches, je n'ai pas trouvé un exemple de fichier se rapprochant suffisamment de ma problématique.
Régulièrement, j’effectue une extraction d'environ 17000 lignes d'un logiciel de gestion des temps.
Le grand nombre de lignes s'explique par le nombre de salariés multiplié par le nombre de jours dans l'extraction (généralement 28 jours).
Il y a 4 types de congés différents.

Je souhaite compiler toutes les lignes comportant des congés en gardant les informations suivantes : date 1er jour de congé, date dernier jour de congé, type de congé et nombre de jours de congé.

Vous trouverez en pièce jointe un fichier d'exemple avec 2 feuilles (extraction et résultat souhaité).
Je pensais qu'une macro pouvait peut être faire ce travail mais mon niveau débutant ne me permet pas d'y parvenir.

J'espère avoir été assez clair et je vous remercie.

PB

 

[sylvanu]

Bonjour PB,
Un essai en PJ.
Le fichier exeple est trop "simple" pour tester toutes les configurations. Donc faites un retour sur votre propre fichier.

Sub ComptageCongés()
    Dim tablo, i%, j%, N%, Lecr%, Début, Fin
    Application.ScreenUpdating = False
    With Sheets("extraction")
        DL = .[A65500].End(xlUp).Row
        .Range("P2:P" & DL).FormulaLocal = "=SI(I2=1;H2;SI(K2=1;J2;SI(M2=1;L2;SI(O2=1;N2;0))))"   ' En col P, 1 si une absence sinon 0
        tablo = .Range("A2:P" & DL)                         ' On met tout dans un tableau
        .[P:P].ClearContents                                ' On efface les formules
    End With
    With Sheets("Synthèse")
        .[A2:J10000].ClearContents
        Lecr = 2                                            ' Init ligne d'écriture
        DL = UBound(tablo)
        For i = 1 To DL
            If tablo(i, 16) <> 0 Then                       ' Si 1 alors il y a un congés sur la ligne
                Début = tablo(i, 7)                         ' On enregistre la date de début
                j = i
                While tablo(j, 16) = tablo(i, 16)           ' Tant que le congé n'a pas changé
                    j = j + 1                               ' On continue
                Wend
                Fin = tablo(j - 1, 7)                       ' Sinon on enregistre la date de Fin
                For N = 1 To 6                              ' On recopie les 6 1eres colonnes
                    .Cells(Lecr, N) = tablo(i, N)
                Next N
                .Cells(Lecr, 7) = tablo(i, 16)              ' Type congé
                .Cells(Lecr, 8) = Début                     ' date début
                .Cells(Lecr, 9) = Fin                       ' Date de fin
                .Cells(Lecr, 10) = 1 + Fin - Début          ' Nombre de jours
                Lecr = Lecr + 1: i = j                      ' Réinit pointeurs
            End If
        Next i
    End With
End Sub

 

[Hasco]

Bonjour,

Par requête power query, c'est le genre de requête où il faut intervenir dans la barre de formule de l'éditeur surtout pour l'étape de regroupement

Cordialement

 

[Pb68]

Bonjour Sylvanu,

Merci beaucoup ! le résultat est déjà très impressionnant.

J'ai inséré la macro dans mon extraction réelle et j'ai un écart uniquement sur un type de congé. II concerne 4 personnes sur un total de 101 personnes. En nombre de jours, j'ai un écart de 70 jours sur 891 attendus.

Je suis en train d'essayer de trouver la raison mais pour le moment je n'ai pas trouvé.

	Jours Attendus	Résultat macro	Ecart
Nom Prénom 1	3	0	3
Nom Prénom 2	35	3	32
Nom Prénom 3	35	19	16
Nom Prénom 4	19	0	19
Total général	891	821	70

Ce qui est bizarre c'est que parfois je n'ai pas le salarié dans la synthèse et d'autres fois j'ai une absence partielle.
Ces personnes ne sont pas à une extrémité du tableau.

Je continue mes recherches. Encore merci pour votre aide précieuse.

 

[sylvanu]

Le fichier exemple est trop "simple"

Si dans votre "vrai" fichier vous remplacez Matricule par 1,2,3,4, et NOMPRENOM par Nom1, Nom2, il devient totalement anonyme.
Si vous le postez je pourrais aussi regarder.

 

[Hasco]

Re,

Et j'ai bossé pour rien !

 

[Pb68]

Bonjour,

Par requête power query, c'est le genre de requête où il faut intervenir dans la barre de formule de l'éditeur surtout pour l'étape de regroupement

Cordialement

Bonjour Hasco,

Je vous remercie mais je n'y connais rien à power query. J'ai ajouté la fonctionnalité à Excel en ayant un message d'avertissement ma version d'excel ou de power query est plus ancienne que la vôtre.
En essayant de comprendre le fonctionnement, j'ai vu que l'étape "lignes groupées" ne fonctionnait pas (Expression.Error : Désolé... Nous n'avons pas pu appliquer l'opérateur < aux types Record et Record.)

Comme je suis vraiment au stade 0 de power query, je préférerais partir sur une macro en attendant de me former à power query.

Merci beaucoup pour votre temps et pour votre aide.

Si dans votre "vrai" fichier vous remplacez Matricule par 1,2,3,4, et NOMPRENOM par Nom1, Nom2, il devient totalement anonyme.
Si vous le postez je pourrais aussi regarder.

Je vous remercie. Je vais joindre un nouveau fichier encore ce soir en faisant comme vous avez dit.

 

[Hasco]

Bonsoir,

Oui bien-entendu, vous faites comme vous le souhaitez.
Je rajoute pour l'exemple le même fichier avec de données rajoutées pour illustrés des cas différents/

pour le MATRICULE 1 une période du 23 au 27 avec même type de congés -> ok
pour le MATRICULE 2 une période du 20 au 22 type CANC et du 23 au 25 Type CP -> ok

Quant à votre premier message d'erreur concernant la compatibilité, ne vous inquiétez pas, cela fonctionne sur votre version,
Pour ce qui concerne l'autre message, je ne vois pas où dans l'étape de regroupement l'opérateur < (inférieur) est appliqué ? Ailleurs non plus !?
Peut-être un doigt qui a traîner sur une touche quand il ne fallait pas !?

[Edit de 19:14] modifié le fichier qui contient deux requêtes avec deux méthodes différentes[/Edit]

Cordialement

 

[Pb68]

Bonsoir,

Oui bien-entendu, vous faites comme vous le souhaitez.
Je rajoute pour l'exemple le même fichier avec de données rajoutées pour illustrés des cas différents/

pour le MATRICULE 1 une période du 23 au 27 avec même type de congés -> ok
pour le MATRICULE 2 une période du 20 au 22 type CANC et du 23 au 25 Type CP -> ok

Quant à votre premier message d'erreur concernant la compatibilité, ne vous inquiétez pas, cela fonctionne sur votre version,
Pour ce qui concerne l'autre message, je ne vois pas où dans l'étape de regroupement l'opérateur < (inférieur) est appliqué ? Ailleurs non plus !?
Peut-être un doigt qui a traîner sur une touche quand il ne fallait pas !?



Cordialement

Je vous remercie, je vais enregistrer le fichier et m'en servir quand je serai formé à cet outil. Bonne soirée !

Bonjour Hasco,

Je vous remercie mais je n'y connais rien à power query. J'ai ajouté la fonctionnalité à Excel en ayant un message d'avertissement ma version d'excel ou de power query est plus ancienne que la vôtre.
En essayant de comprendre le fonctionnement, j'ai vu que l'étape "lignes groupées" ne fonctionnait pas (Expression.Error : Désolé... Nous n'avons pas pu appliquer l'opérateur < aux types Record et Record.)

Comme je suis vraiment au stade 0 de power query, je préférerais partir sur une macro en attendant de me former à power query.

Merci beaucoup pour votre temps et pour votre aide.


Je vous remercie. Je vais joindre un nouveau fichier encore ce soir en faisant comme vous avez dit.

Vous trouverez en PJ le fichier complet avec le détail de l'écart rencontré. Encore merci, je ne vais plus pouvoir répondre ce soir. Bonne soirée !

 

[sylvanu]

Bonsoir,
Oups, sorry. Le bug est corrigé, voir PJ.
Il se produisant lorsque deux MAT se suivait avec le même type de congés.
Faites un retour, cela me semble ok, on obtient bien les mêmes résultats.

 

[Pb68]

Bonsoir,
Oups, sorry. Le bug est corrigé, voir PJ.
Il se produisant lorsque deux MAT se suivait avec le même type de congés.
Faites un retour, cela me semble ok, on obtient bien les mêmes résultats.

Bonjour Sylvanu,
Un très grand merci pour votre aide, c'est juste parfait !
Cette macro nous fera gagner beaucoup de temps. J'espère un jour arrivé à faire par moi-même quelque chose d'approchant et de pouvoir aider à mon tour une personne en difficulté.
Très bonne continuation à vous !

 

[sylvanu]

Bonjour Pb68,
Je viens de m'apercevoir que pour trouver ce fameux bug j'avais fait pas mal de versions intermédiaires avec des "trucs" pour m'aider à trouver.
Et je m'aperçois que cela pourrait vous être utile comme la possibilité de trier des colonnes ascendant ou descendant sur les noms, les types, les nombres de jours ( il suffit de cliquer sur une cellule de 1-xx à 6-xx ) , ainsi que la synthèse des résultats comme vous l'aviez fait.
J'ai aussi déplacé la macro de Thisworkbook où elle n'avait rien à y faire, à dans la page Synthèse, ce qui est plus propre.
Donc j'ai remis ça "plus esthétique", vous l'avez en PJ.

 

[gbinforme]

Bonjour,
Le sujet est résolu par plus rapide que moi mais je vous mets tout de même le résultat de ma cogitation qui fonctionne de façon un peu différente avec des paramètres colonnes modifiable simplement.
Je suis surpris aussi que l'on ne puisse pas prendre de demi journées de RTT par exemple et c'est pris en compte.
Si cela peut servir ou inspirer d'autres lecteurs j'en serais ravi.
Bonne année à tous.

 

[Pb68]

Bonjour Sylvanu, Bonjour Gbinforme,
Merci pour vos messages, j'ai récupéré les deux fichiers et je les regarderai avec attention.
Bonne journée !