Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

XL 2013 Planning absences mensuel 1 ligne par employé

Great_Gaet

XLDnaute Nouveau
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 !
 

Pièces jointes

  • Help absences.xlsx
    14.7 KB · Affichages: 16
Solution
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

XLDnaute Accro
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

XLDnaute Barbatruc
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+
 

Pièces jointes

  • Help absences(1).xlsx
    16.9 KB · Affichages: 20

Great_Gaet

XLDnaute Nouveau
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

XLDnaute Accro
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

XLDnaute Accro

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).

Bonne exploitation,
xl-ment
zebanx
 

job75

XLDnaute Barbatruc
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+
 

Pièces jointes

  • VBA absences(1).xlsm
    25.7 KB · Affichages: 22
Dernière édition:

zebanx

XLDnaute Accro
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

XLDnaute Barbatruc
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.
 
Dernière édition:

zebanx

XLDnaute Accro
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

XLDnaute Barbatruc
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

XLDnaute Nouveau
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
 

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…