XL 2013 Planning absences mensuel 1 ligne par employé

Great_Gaet · 10 Mars 2020

Bonjour chère forum,

Je vous contacte car j'ai besoin d'aide sur un fichier de suivi d'absences que j'essaie de mettre en place.
J'ai un onglet par mois sur lequel se trouve la liste des employés avec leur numéro, leur nom et leur poste.
Je dispose d'une base qui répertorie les absences par salarié en indiquant la date de début et la date de fin (ATTENTION : je ne peux pas modifier la base, c'est une extraction d'un logiciel tiers)

Mon problème est que certain employé prennent plusieurs congés dans le même mois ce qui créé donc plusieurs ligne de données pour un même employé sur la base.
Je n'arrive pas afficher sur la même ligne de mon planning les absences quand elles ne sont pas consécutives (ex : 04/05/2020 à 06/05/2020 puis sur la ligne du dessous : 10/05/2020 à 15/05/2020).

Je suis ouvert à tout type de solutions formules ou macro (btw je ne dispose pas de la rechercheX)

Ci joint un extrait de mon fichier pour plus de détail.

Merci d'avance à tout ceux qui prendront le temps et essayeront de m'aider !

job75 · 12 Mars 2020

Bonjour le fil, le forum,

Pour forcer le recalcul des fonctions je viens de corriger, une seule instruction suffit au lieu de 2 :

VB:

Private Sub Workbook_Open()
Call Dico
'---force le recalcul des fonctions---
Cells.Replace "Calcul", "Calcul", xlPart
End Sub

Cela divise par 2 la durée du recalcul des fonctions.

Pour tester j'ai modifié le fichier comme suit :

- dans la feuille "Base", 5000 num de 1 à 5000 en colonne A, sur 7 x 2500 = 17500 lignes

- dans les feuilles des mois 5000 numéros qui se suivent en colonne A.

Chez moi la macro Dico s'exécute alors en 0,8 seconde et le recalcul des fonctions en 10 secondes (pour une feuille), c'est acceptable.

Bonne journée.

Nairolf · 10 Mars 2020

Salut Great_Gaet,

Essaye avec cette formule en "D7" à étirer horizontalement et verticalement :

Code:

=SI(SOMMEPROD(($B7=Base!$B$2:$B$8)*(D$5>=Base!$E$2:$E$8)*(D$5<=Base!$F$2:$F$8))=0;"";INDEX(Base!$D$2:$D$8;SOMMEPROD(($B7=Base!$B$2:$B$8)*(D$5>=Base!$E$2:$E$8)*(D$5<=Base!$F$2:$F$8)*(LIGNE(Base!$D$2:$D$8)-1));1))

job75 · 10 Mars 2020

Bonjour Great_Gaet, Nairolf,

Formule en D7 à propager à droite et vers le bas :

Code:

=REPT("CP";SOMMEPROD(("CP"=Base!$D$2:$D$8)*($A7=Base!$A$2:$A$8)*(D$5>=Base!$E$2:$E$8)*(D$5<=Base!$F$2:$F$8)))&REPT("CM";SOMMEPROD(("CM"=Base!$D$2:$D$8)*($A7=Base!$A$2:$A$8)*(D$5>=Base!$E$2:$E$8)*(D$5<=Base!$F$2:$F$8)))

PS : dans quelle langue Dimanche commence par un B ???

A+

Great_Gaet · 11 Mars 2020

Nairolf, Job75,

Vos deux formules fonctionnent mais quand je les appliquent sur mon vraie fichier qui contient environ 5k lignes cela rame énormément (j'ai mis 30min pour appliquer la formule sur 1 seul mois). Ma base est mise à jour régulièrement alors n'y a t-il pas une autre formule ou moyen pour que cela soit plus rapide ?

Merci d'avance !

GG

Nairolf · 11 Mars 2020

Salut,

J'imagine que ça a ramé quand tu as copié la formule sur les autres cellules.
C'est normal avec le nombre de ligne que tu indiques, Excel recalcul toutes les cellules à chaque copie de la formule, la formule étant un peut complexe, cela prend du temps.

Pour résoudre le problème de lenteur lors de la mise en place des formules, il faut, avant la copie, se mettre en mode de calcul "Manuel" (dans onglet "Formule"/"Option de calcul"), puis remettre ensuite en "Automatique", le calcul des cellules ne se fera que lorsque tu remettras en automatique.

zebanx · 11 Mars 2020

Great_Gaet à dit:
Nairolf, Job75,

Vos deux formules fonctionnent mais quand je les appliquent sur mon vraie fichier qui contient environ 5k lignes cela rame énormément (j'ai mis 30min pour appliquer la formule sur 1 seul mois). Ma base est mise à jour régulièrement alors n'y a t-il pas une autre formule ou moyen pour que cela soit plus rapide ?

Merci d'avance !

GG

Bonjour Great_gaet, job75 , nairolf, le forum

Le travail effectué par Jacques BOISGONTIER sur ce fil est (comme d'habitude) remarquable.
Il donne en tout cas des perspectives pour réaliser ce travail à partir de macros mais à partir d'un seul fichier de restitution (et ici avec AM/PM).

transposer une BD en tableau

Bonjour, J'ai une BD avec en colonne : des noms, dates, demi-journées, activités, commentaires. A la vue des (très bons, encore merci pour cette source d'inspiration !) tutos de JB, j'ai vu qu'il était possible de transposer via Dictionary une BD en tableau : Je pourrais ré-utiliser ce code ...

www.excel-downloads.com

Bonne exploitation,
xl-ment
zebanx

job75 · 11 Mars 2020

Bonjour Great_Gaet, Nairolf, zebanx,

Voyez cette fonction VBA et cette macro dans Module1 :

VB:

Public d As Object 'mémorise la variable

Function Calcul(num As Range, dat As Range) As String
Calcul = d(num & Chr(1) & dat.Value2)
End Function

Sub Dico()
Dim tablo, i&, dat1, dat2, j&
Set d = CreateObject("Scripting.Dictionary")
tablo = Sheets("Base").[A1].CurrentRegion.Resize(, 6)
For i = 2 To UBound(tablo)
    dat1 = tablo(i, 5): dat2 = tablo(i, 6)
    If IsDate(dat1) And IsDate(dat2) Then
        For j = dat1 To dat2
            d(tablo(i, 1) & Chr(1) & j) = tablo(i, 4)
        Next j
    End If
Next
End Sub

Le Dictionary et les fonctions sont recalculés à l'ouverture du fichier ou à l'activation d'une feuille :

VB:

Private Sub Workbook_Open()
Call Dico
'---force le recalcul des fonctions---
Cells.Replace "Calcul", "Calcul", xlPart
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Workbook_Open
End Sub

Fichier .xlsm joint à adapter pour votre fichier de 5000 lignes, dites-nous ce que ça donne.

Edit : j'ai corrigé la Workbook_Open.

A+

zebanx · 11 Mars 2020

Re-bonjour JOB75

Pourrais-tu m'indiquer stp l'utilité de chr(1) dans le code (jamais utilisé jusqu'à présent et premières recherches internet ne donnent pas grand chose...)?
Je t'en remercie par avance et bravo pour ce code.

Bonne journée
zebanx

job75 · 11 Mars 2020

Le séparateur Chr(1) est une sécurité puisqu'on concatène 2 nombres entiers.

Edit : dans la macro Dico le test And dat1 <= dat2 est inutile, je vais l'enlever.

zebanx · 11 Mars 2020

Je te remercie pour ta célérité.
Jamais vu avant le forçage des fonctions de cette manière, très intéressant aussi.
Cells.Replace "Calcul", "Calculx", xlPart
Cells.Replace "Calculx", "Calcul"

J'espère que le code conviendra bien au demandeur sur sa rapidité -)
Bonne soirée
zebanx

job75 · 12 Mars 2020

Bonjour le fil, le forum,

Pour forcer le recalcul des fonctions je viens de corriger, une seule instruction suffit au lieu de 2 :

VB:

Private Sub Workbook_Open()
Call Dico
'---force le recalcul des fonctions---
Cells.Replace "Calcul", "Calcul", xlPart
End Sub

Cela divise par 2 la durée du recalcul des fonctions.

Pour tester j'ai modifié le fichier comme suit :

- dans la feuille "Base", 5000 num de 1 à 5000 en colonne A, sur 7 x 2500 = 17500 lignes

- dans les feuilles des mois 5000 numéros qui se suivent en colonne A.

Chez moi la macro Dico s'exécute alors en 0,8 seconde et le recalcul des fonctions en 10 secondes (pour une feuille), c'est acceptable.

Bonne journée.

Great_Gaet · 12 Mars 2020

Bonjour à tous,

Job75 ton code fonctionne très bien, j'ai juste mis un peu de temps à le mettre en place sur mon fichier n'étant pas un expert en VBA ! Merci beaucoup pour ton aide et le temps que tu as consacrer à mon problème.

Aux autres, merci aussi d'avoir proposer vos solutions, tout me sera utile.

Bonne continuation à tous,

GG

job75 · 12 Mars 2020

Bravo d'avoir su appliquer mes codes à votre fichier.

Avec mes codes votre fichier de 5000 lignes s'ouvre en combien de temps ?

XL 2013 Planning absences mensuel 1 ligne par employé

Great_Gaet

XLDnaute Nouveau

Pièces jointes

job75

Nairolf

XLDnaute Accro

job75

XLDnaute Barbatruc

Pièces jointes

Great_Gaet

XLDnaute Nouveau

Nairolf

XLDnaute Accro

zebanx

XLDnaute Accro

transposer une BD en tableau

job75

XLDnaute Barbatruc

Pièces jointes

zebanx

XLDnaute Accro

job75

XLDnaute Barbatruc

zebanx

XLDnaute Accro

job75

XLDnaute Barbatruc

Great_Gaet

XLDnaute Nouveau

job75

XLDnaute Barbatruc

Discussions similaires