Automatiser tableau de congés ...

Y compris Power BI, Power Query et toute autre question en lien avec Excel
s
shomaely
Jeune membre
Jeune membre
Messages : 25
Inscrit le : 11 mai 2017
Version d'Excel : 2016

Message par shomaely » 16 mai 2017, 09:16

Bonjour à tous.

Je vais directement vous mettre mon ébauche (exemple) en pièce jointe car je sent que mon explication va être vaseuse.

Il y a 2 feuilles.

La 1ere, c'est mon tableau que j'aimerais automatiser...

tout va ce passer en feuille 2

Ligne 1 j'ai mis en place un menu déroulant pour la sélection des employés

Et en colonne A/B ce sont les dates à poser

Mon objectif :

Je rentre les dates et je sélectionne le nom de l'employé ainsi que le motif concernant l'arrêt à la date en question. (cp, rtt ....)

J'aimerais que dès que ce soit fait .... ça remplisse automatiquement mon tableau en feuille 1


De plus, dans la feuille 1 vous pouvez voir une colonne JO et une colonne HEURES ... j'aimerais que ces colonnes se remplissent également automatiquement
JO = nombre de jours ouvrés posés durant les CP
HEURES = nombre d'heure équivalente à un RTT (7h/jour) je forcerais moi même le nombre d'heure lorsque certain ne prennent que 3 ou 4h pour les demi journées.

et enfin, à la fin de mon tableau principale j'ai indiqué le nombre restant de RTT à chacun ... je vous le donne en mille ... j'aimerais que les heures se décomptent automatiquement lorsque je pose les RTT.

Merci à tous et .... PLEASE, HELP ME !!!!!!
Avatar du membre
Efgé
Membre fidèle
Membre fidèle
Messages : 334
Appréciations reçues : 9
Inscrit le : 21 juin 2013
Version d'Excel : 2007

Message par Efgé » 16 mai 2017, 11:42

Bonjour shomaely

/!\ Ton fichier est loin d'être anonyme......

Je pense qu'il faut revenir aux basics: Une base de données pour la saisie des congés et une feuille de présentation.
Une proposition
Pour la feuille 2 (Data)
Saisir les dates début/Fin les Noms/Prénoms et les types de congés.
(Tu peux mettre n'importe quel Type de congés, il sera pris en compte dans le tableau de reporting)

Pour la feuille 1 (Report)
Tu mets la date de l'année (+1) en A2 (cellule jaune). Tout est automatique pour les dates.
Le type de congé s'inscrit dans les cellules
J'ai revu tes fériés pour qu'ils soient dynamiques et sur deux ans.
J'ai rendu dynamique les "F" de la ligne 3

J'ai saisi des congés pour les deux premiers noms en Juin, Juillet et Août pour exemple.

La limite de la proposition:
L'utilisation d'une formule matricielle peut ralentir les classeur suivant le PC. A voir.

Pour tes calculs il suffit de compter le nombrez de RTT *7 et le nombre de CP

Cordialement
Modifié en dernier par Efgé le 16 mai 2017, 15:08, modifié 1 fois.
s
shomaely
Jeune membre
Jeune membre
Messages : 25
Inscrit le : 11 mai 2017
Version d'Excel : 2016

Message par shomaely » 16 mai 2017, 12:59

Tout d'abord merci beaucoup c'est incroyable.

Petite chose que je n'avais pas précisé c'est que je suis un peu une quiche sur Excel et mon nouveau boulot m'oblige à devenir un pro, lol.

Du coup, accepterait tu de bien vouloir m'expliquer (dans un langage pour nul :lol: ) histoire que je puisse reproduire ce genre de manip à l'avenir car je vais avoir plus d'une vingtaine de tableau à effectuer et j'ai pas forcément envie de devoir passer par la forum à chaque fois (ça ne fait pas très sérieux).

En tout cas merci énormément du temps que tu a passer pour m'aider.

PS : dans ton tableau tu bloque les week-end et férié afin que les CP ne rentre pas automatiquement dedans mais dans mon entreprise, les samedi sont compter dans les CP.

Merci encore, vraiment.
Avatar du membre
Efgé
Membre fidèle
Membre fidèle
Messages : 334
Appréciations reçues : 9
Inscrit le : 21 juin 2013
Version d'Excel : 2007

Message par Efgé » 16 mai 2017, 13:20

Re Bonjour :)

/!\ Merci de supprimer ton premier fichier qui n'est pas anonyme...


quand tu dis:
shomaely a écrit :...dans mon entreprise, les samedi sont compter dans les CP....
Faut-il que tous les samedi soient comptés comme CP, et donc marqués comme tels ou faut-il que lors de congés pris les samedi appariassent dans le tableau Report ?
La réponse va modifier le système de calcul des CP.
Si tu préfère, mets une feuille Excel avec CP sur un ou deux mois pour une personne où il y a des congés(comme tu veux que cela apparaisse).


Cordialement
s
shomaely
Jeune membre
Jeune membre
Messages : 25
Inscrit le : 11 mai 2017
Version d'Excel : 2016

Message par shomaely » 16 mai 2017, 13:41

Ce que j'entend par là c'est que quand quelqu'un prend des congés, si il y a des samedi, les CP seront comptés dans les samedi.

8 jours de congés a partir du vendredi par exemple :

vendredi : CP
samedi : CP
dimanche : rien
lundi : CP
mardi : CP
mercredi : CP
jeudi : CP
vendredi : CP
samedi : CP
Avatar du membre
Efgé
Membre fidèle
Membre fidèle
Messages : 334
Appréciations reçues : 9
Inscrit le : 21 juin 2013
Version d'Excel : 2007

Message par Efgé » 16 mai 2017, 13:53

Re
OK
Ma question est surtout sur les samedi ou il n'y a pas de congés (je connais quelqu'un qui travail dans le BTP et ses jours de congés sont augmentés du nombre de samedi de l'année de référence).

Cordialement
s
shomaely
Jeune membre
Jeune membre
Messages : 25
Inscrit le : 11 mai 2017
Version d'Excel : 2016

Message par shomaely » 16 mai 2017, 14:02

Ca honnêtement je ne sais pas, je viens d'arriver dans cette boite et je ne connais pas encore trop leur fonctionnement.

Tout ce que je sais c'est que quand je pose les congés des mecs, faut que les samedi soient pris en compte. lol

Tant que nous y sommes, j'ai un petit soucis que j'aimerais te demander :

J'ai un tableau avec une colonne dans laquelle apparaissent des chiffres allant de 0 à 15.
sur une 2eme feuille j'ai fait 2 colonnes : l'une comprenant les chiffres de 0 à 15 et la 2eme juste à côté dans laquelle j'aimerais qu'apparaissent le total de chaque chiffre

Sachant que dans la feuille 1 (tableau initial), les chiffres sont aléatoire.
Avatar du membre
Efgé
Membre fidèle
Membre fidèle
Messages : 334
Appréciations reçues : 9
Inscrit le : 21 juin 2013
Version d'Excel : 2007

Message par Efgé » 16 mai 2017, 14:22

Re
shomaely a écrit :Ca honnêtement je ne sais pas, je viens d'arriver dans cette boite et je ne connais pas encore trop leur fonctionnement.

Tout ce que je sais c'est que quand je pose les congés des mecs, faut que les samedi soient pris en compte. lol

Tant que nous y sommes, j'ai un petit soucis que j'aimerais te demander :

J'ai un tableau avec une colonne dans laquelle apparaissent des chiffres allant de 0 à 15.
sur une 2eme feuille j'ai fait 2 colonnes : l'une comprenant les chiffres de 0 à 15 et la 2eme juste à côté dans laquelle j'aimerais qu'apparaissent le total de chaque chiffre

Sachant que dans la feuille 1 (tableau initial), les chiffres sont aléatoire.
Ca ne veux rien dire.
Pour l'instant, nous allons regarder le fichier de base, ensuite nous verrons le reste, si le Capricorne n'est pas en Cancer, si je peux, si...... ...tu dépose un fichier anonyme représentatif... :)

Pour l'instant je te fait l'explication du fichier test rtt(2).xlsx. (Prends le avant que je ne le supprime pour manque d'anonymat et confirme moi que tu l'as pris)

Cordialement
s
shomaely
Jeune membre
Jeune membre
Messages : 25
Inscrit le : 11 mai 2017
Version d'Excel : 2016

Message par shomaely » 16 mai 2017, 14:26

Lol, je savais que j'étais pas clair dans mes explications. En même temps, tenter d'être claire sur un sujet qu'on ne connait pas, c'est pas évident.

C'est bon j'ai enregistré ton fichier.
Avatar du membre
Efgé
Membre fidèle
Membre fidèle
Messages : 334
Appréciations reçues : 9
Inscrit le : 21 juin 2013
Version d'Excel : 2007

Message par Efgé » 16 mai 2017, 15:07

Re

Commençons les explications.
Pour la feuille "Data" : Rien de spécial, c'est un simple tabelau de données.

Pour la feuille "Report"

La Cellule Report!A2 est nommée "An"
En Report!E2 la formule =DATE(An-1;5;1) affiche le 1er Mai de l'année -1 saisie
De Report!F2 à Report!NE2 (=E2+1) on incrémenbte les dates (+1 = le lendemain)
En Report!E1:NE1 on recopie la date (=E2) et on mets un format de cellule personalisé qui ne fait apparître que le jour (JJJ)

On crée une liste des jours fériés de l'année N+1 (la valeur saisie en Report!$A$2 - 1 ) à l'année N (la valeur saisie en Report!$A$2)
Pour chaque jour Férié je te laisse te documenter, mais ces formules, données par Roger2327, sont viables.
Cette plage est nommée "Dates_Feries"
En $E$3:$NE$3 la formule =SI(NB.SI(Dates_Feries;E2);"F";"") permet de dire Si la date en E2 est présente dans la liste des fériés, on met "F" sinon on ne mets rien sinon autrechose.


Maintenat passons à la formule matricielle (matricielle = à valider par CTRL + Maj + Entrée):
Les formules matricielles travaillent sur des plages de données.
Prenons l'exemple en A4:
=SI(OU(JOURSEM(E$2;2)>5;NB.SI(Dates_Feries;E$2)>0);"";SIERREUR(INDEX(Tableau1[[Type]:[Type]];PETITE.VALEUR(SI((Tableau1[[Nom]:[Nom]]=$A4)*(Tableau1[[Prénom]:[Prénom]]=$B4)*(E$2>=Tableau1[[Début]:[Début]])*(E$2<=Tableau1[[Fin]:[Fin]]);LIGNE(INDIRECT("A1:A"&NBVAL(Tableau1[[Fin]:[Fin]]))));1));""))
Décomposons
=SI(OU(JOURSEM(E$2;2)>5;NB.SI(Dates_Feries;E$2)>0);"";"autre chose")
Si le jour de la semaine présent en colonne E ligne 2 (ligne bloquée) est un W.E OU La date en E$2 (ligne bloquée) est présente dans les fériés, on n'affiche rien

Le "autre chose"
SIERREUR(INDEX(Tableau1[[Type]:[Type]];PETITE.VALEUR(SI((Tableau1[[Nom]:[Nom]]=$A4)*(Tableau1[[Prénom]:[Prénom]]=$B4)*(E$2>=Tableau1[[Début]:[Début]])*(E$2<=Tableau1[[Fin]:[Fin]]);LIGNE(INDIRECT("A1:A"&NBVAL(Tableau1[[Fin]:[Fin]]))));1));""))
Décomposons:
SIERREUR("quelquechose";"")
Si il y a une erreur dans la formule on n'affiche rien

Le quelquechose:
INDEX(Tableau1[[Type]:[Type]];PETITE.VALEUR(SI((Tableau1[[Nom]:[Nom]]=$A4)*(Tableau1[[Prénom]:[Prénom]]=$B4)*(E$2>=Tableau1[[Début]:[Début]])*(E$2<=Tableau1[[Fin]:[Fin]]);LIGNE(INDIRECT("A1:A"&NBVAL(Tableau1[[Fin]:[Fin]]))));1))
INDEX(plage;ligne;colonne)

Donc dans la plage Tableau1[[Type]:[Type]]
On cherche la ligne:
PETITE.VALEUR(SI((Tableau1[[Nom]:[Nom]]=$A4)*(Tableau1[[Prénom]:[Prénom]]=$B4)*(E$2>=Tableau1[[Début]:[Début]])*(E$2<=Tableau1[[Fin]:[Fin]]);LIGNE(INDIRECT("A1:A"&NBVAL(Tableau1[[Fin]:[Fin]]))));1)
Si
La plus petite valeur (si la colonne Nom du tableau Data est égale au nom du tableau Report colonne A)
PETITE.VALEUR(SI((Tableau1[[Nom]:[Nom]]=$A4)
ET (*)(que le Prénom du tableau Report, colonne B, est égal au prénom du tableau Data)
(Tableau1[[Prénom]:[Prénom]]=$B4)

Et (*) (que la date en ligne 2 est égale ou supérieure à la date de la colonne Début du tableau data
(E$2>=Tableau1[[Début]:[Début]])

Et (*) (que la date en ligne 2 est inférieure ou supérieure à la date de la colonne Début du tableau data
(E$2<=Tableau1[[Fin]:[Fin]]

Alors
On récupère la ligne correspondante à tous les critères:
LIGNE(INDIRECT("A1:A"&NBVAL(Tableau1[[Fin]:[Fin]))

Pour la colonne c'est ;1 puisque qu'INDEX est sur une seule colonne.

N'hésite pas à consulter l'aide d'Excel et au besoin à poser des questions sur le forum ;-)

En P.J le classeur final anonyme
Cordialement
test rtt(3).xlsx
(641.31 Kio) Téléchargé 138 fois
Répondre
  • Sujets similaires
    Réponses
    Vues
    Dernier message