Recherche de donnees et remplissage automatique
Bonjour à toutes et tous,
Déjà, un grand merci pour ce forum, j'ai déjà pu trouver mon bonheur à bien des égards grâce à lui. C'est vraiment top !
Mais aujourd'hui je dois aller plus en profondeur dans mon utilisation d'excel.
En PJ, mon fichier.
Je souhaite compléter l'onglet "ANOMALIES" à l'aide des autres onglets.
- PLUS DE 6H DE TRAVAIL SANS PAUSE (cellules A-B-C) :
Je souhaite aller chercher dans l'onglet "POINTAGES" tous les salariés qui ont travaillé plus de 06h00 consécutives (cellule W) en excluant de la recherche les salariés dont le contrat (cellule i) sont "TC 35H Pause Auto"
>> il faudrait ainsi que Excel cherche dans la cellule W de l'onglet "POINTAGES" tous les pointages supérieurs à 06:00 et qu'il reporte les données dans l'onglet "ANOMALIES" en mettant la date et le nom du salarié.
- PLUS DE 10H DE TRAVAIL DANS LA MEME JOURNEE (cellules D-E-F) :
Deux cas de figure :
> Pour les "TC 35H Pause Auto" : demande assez similaire à la demande précédente, sauf qu'il faut chercher les plus de 10h.
> Pour les autres : Il faut pour chaque jour additionner les temps travaillés si il y a plusieurs créneaux. Par exemple pour SALARIE 3, il faudra additionner les cellules W14 et W15 pour le lundi, W73 et W74 pour le mardi, etc...
- PLUS DE 12H D'AMPLITUDE DANS LA MEME JOURNEE (cellules G-H-I-J) :
Peut-être un peu plus compliqué... je souhaite aller chercher dans l'onglet "POINTAGES" tous les salariés qui ont travaillé sur amplitude de plus de 12h dans la même journée (qui a commencé à 10h le matin et terminé à 23h par exemple).
>> il faudrait ainsi que excel compare la cellule K et N sur plusieurs lignes.
Par exemple, pour SALARIE 1, il faudra que Excel compare la cellule K11 et N12, puis K70 et N71, ... Et compléter l'onglet "ANOMALIES" si c'est plus de 12h de différence.
- CRENEAUX INFERIEURS A 2H (cellules K-L-M) :
C'est la même demande que pour les plus de 6h, sans exclure les TC 35H Pause Auto. et cette fois-ci en mettant tous les créneaux de strictement moins de 2h.
- TEMPS DE REPOS INFERIEUR A 12H ENTRE 2 JOURNEES (cellules N-O-P-Q) :
je souhaite aller chercher dans l'onglet "POINTAGES" tous les salariés qui ont pointé sur amplitude de moins de 12h entre deux journées (qui a terminé le lundi à 23h et repris le mardi à 09h par exemple).
>> il faudrait ainsi que excel compare la cellule N et K sur plusieurs jours.
Par exemple, pour SALARIE 1, il faudra que Excel compare la cellule K70 et N12, puis N134 et N71, ... Et compléter l'onglet "ANOMALIES" si c'est plus de 12h de différence.
- MINEURS : PLUS DE 04H30 DE TRAVAIL SANS PAUSE (cellules R-S-T) :
Tout pareil que le "PLUS DE 6H DE TRAVAIL...", en prenant en compte en plus dans l'onglet "CONTACTS" tous les salariés dont l'âge est strictement inférieur à 18 ans.
- MINEURS : TRAVAIL APRES 22H
Il faudrait que Excel cherche tous les salariés dont l'âge est strictement inférieur à 18 ans (onglet CONTACTS) qui ont pointés après 22h (colonne "N" dans l'onglet "POINTAGES").
Voilà ! J'ai fait le tour de ma demande...
En espérant trouver quelqu'un qui pourra m'aider, me donner une idée de formule à utiliser.
En tout cas, vous êtes géniaux !!
A dispo si vous avez des questions ! J'espère déjà avoir été assez complet !
Bonjour BEN-73 et
Une petite présentation ICI serait la bienvenue
Si vous ne l'avez pas encore fait, je vous invite à lire la charte du forum [A LIRE AVANT DE POSTER] ainsi que ses fonctionnalités
qui vous aideront dans vos demandes et réponses sur ce forum.
Concernant votre roman demande, je pense qu'il faudrait procéder par étape
Perso quand je vois ça je suis fatigué d'avance et ça ne me donne pas envie de répondre
Merci de votre participation
Cordialement
Bonsoir, Hello Bruno,
Une approche avec Power query à finaliser si compatible avec ta demande.
Cordialement.
Bonjour à tous
PowerQuery également
J'ai cela
On pourrait remplir de façon similaire les 3 colonnes Début, Fin et Temps Chiffrable
@brunoM45 : Merci beaucoup pour ton accueil.
Ma petite présentation est faite :)
Et oui, j'ai fait un roman. Sur la charte, il est bien dit d'être le plus précis possible, j'ai fait au mieux
@Zebulon2 et @78chris c'est super méga génial votre solution. C'est quoi PowerQuery ?
Merci infiniment !
Bonjour
C quoi ? Un couteau suisse de traitement de données
https://www.lecfomasque.com/comment-utiliser-power-query/
Je poste mon classeur : j'ai tout fait en une requête mais on pourrait découper pour simplifier la compréhension du néophyte
Merci @78chris pour ce retour :)
Effectivement ça m'a l'air d'être complet comme outil. Je vais essayer de m'y mettre pour le comprendre.
Dans le cas de mon fichier, c'est une "trame" que je souhaite utiliser toutes les semaines. Il faudra que je refasse le "codage" toutes les semaines, où je peux modifier les données quelque part directement avec un copier coller ?
RE
Tu vides le tableau source, sauf le titre, y copies les nouvelles données et tu actualises.
Autre solution plus propre : laisser les données dans un fichier externe qu'on écrase ai fil des extractions et actualiser
Si le fichier doit changer de nom, prévoir quelques lignes de code VBA pour récupérer le choix du fichier concerné et le passer en paramètre à la requête avant d'actualiser
C'est super ! Un grand merci !
Encore deux questions / demandes pour pousser vraiment le dossier à son paroxysme :
- Dans mon fichier source avec les vrais noms des salariés, l'onglet contacts mets les noms sous le format "NOM, Prénom" au lieu de "Prénom NOM".
J'ai trouvé cette formule =DROITE(B2;NBCAR(B2)-CHERCHE(", ";B2)-1)&"."&GAUCHE(B2;CHERCHE(", ";B2)-1) pour faire le switch. Mais est-ce que cette formule peut être programmée directement pour que PowerQuery le fasse automatiquement ?
- Pour vraiment pousser... (sans vouloir abuser des bonnes choses !) : Je souhaite savoir qui était le manager présent lorsque l'anomalie a eu lieu.
Par exemple, le 29/10/2022, le salarié 14 a travaillé 6h sans pause. C'était le salarié 47 qui était le manager en poste (sur la colonne H, il y a le statut "MANA" pour manager). Je ne sais pas si c'est possible de paramétrer ça, mais ça serait fabuleux !
Un grand grand merci encore pour votre aide !
RE
Si je comprend bien Nom et prénom sont séparés par virgule espace ?
Il y a 2 managers certains jours même on se base sur le créneau horaire (grandes amplitudes) mais aussi des créneaux sans Manager (créneaux courts le soir)
Le 1er cas est solvable (on peut mettre les 2), mais pour le second comme il faut trouver des chevauchements sur des créneaux différents c'est compliqué (pas de solution à ce stade)
Oui il y a une virgule et un espace entre Nom et prénom. Il faudrait enlever la virgule et inverser le Prénom et le Nom pour faire "Prénom Nom".
Il y a toujours deux managers (du moins, il y aura... Dans le fichier que j'avais joins, on avait un qui n'était pas encore noté en MANA, mais ce sera fait pour les prochains).
Et je ne sais pas pourquoi, je n'ai plus accès à PowerQuerry depuis aujourd'hui.
RE
Oui il y a une virgule et un espace entre Nom et prénom. Il faudrait enlever la virgule et inverser le Prénom et le Nom pour faire "Prénom Nom".
Il y a toujours deux managers (du moins, il y aura... Dans le fichier que j'avais joins, on avait un qui n'était pas encore noté en MANA, mais ce sera fait pour les prochains).
Comme dit, noter les deux managers est faisable mais pour l'instant pas de solution pour les créneaux sans manager : on perd des anomalie. Je m'y repencherai à l'occasion
C'est l'onglet Données pour PowerQuery
Ou dans cet onglet Requête le bouton modifier à gauche
Hello Chris,
J'ai essayé d'améliorer le fichier pour répondre à ça :
- Dans mon fichier source avec les vrais noms des salariés, l'onglet contacts mets les noms sous le format "NOM, Prénom" au lieu de "Prénom NOM".
J'ai trouvé cette formule =DROITE(B2;NBCAR(B2)-CHERCHE(", ";B2)-1)&"."&GAUCHE(B2;CHERCHE(", ";B2)-1) pour faire le switch. Mais est-ce que cette formule peut être programmée directement pour que PowerQuery le fasse automatiquement ?
Mais je n'y arrive pas...
J'ai essayé en fait d'ajouter une colonne et de mettre la formule dans la nouvelle colonne, mais ça met un message d'erreur.
Bonjour
Comme dit, noter les deux managers est faisable mais pour l'instant pas de solution pour les créneaux sans manager : on perd des anomalie. Je m'y repencherai à l'occasion
Tant que ce problème n'est pas résolu... et qu'on n'a pas de fichier avec des noms formatés selon le vrai fichier on n'avancera pas
Et voici :)
Est-ce que sur le rendu final, on peut aussi mettre le type d'anomalie dans la première colonne, avant la date ?
Et Chris : est-ce que tu donnes des cours de PowerQuery ? J'apprends vite, il faut juste que je comprenne le fonctionnement.
RE
L'onglet pointage sur lequel se base la requête ne contient pas de virgule dans le colonne Salarié
De ce fait la jointure ne se fait pas entre les 2 tables...
Sinon pour inverser c'est
Text.AfterDelimiter([Salarié],", ")& ", "&Text.BeforeDelimiter([Salarié],",")Mais cette formule, il faut l'insérer où ?
Bonjour
Dans une nouvelle colonne PowerQuery (simple) ou bien une commande remplacement (plus complexe)