Recherche Excel avec date
Résolu
Bonjour grande communaute, je suis desolee par avance des fautes d'accent, je suis a Dublin sur un clavier QWERTY, dur dur de trouver les accents aha
La question a deja ete posee sous cette forme mais je ne rencontre pa le meme soucis...
Voila, l'entreprise dans laquelle je bosse a un systeme de carte pour entrer et sortir du batiment, pour controler ainsi leurs horaires de boulot et s'assurer qu'ils ne sont pas surmenes. Bref la question n'est pas la, ces donnees recueillies jour apres jours vont dans un logiciel special (Nera Report) et ne sont affreusement pas exploitables mais sont quand meme transferables sur un fichier excel.
Je suis donc chargee de creer un Excel fonctionnel ou une fois les donnes du logiciel copiees collees dans la premiere feuille Excel, la deuxieme feuille Excel donne ce qu'il faut.
Je dois donc faire en sorte que jour par jour, en face du nom de l'employe, l'heure d'arrivee et la derniere heure de sortie (et pas les allees et venues comptabilisees a midi etc) soient visibles ainsi que le nombre d'heure travaillees.
Pour le moment, j'ai effectue une RECHERCHEV pour le nom des employes et ca fonctionne nickel (mais j'ai peur que tout soit decale et donc inutilisable pour un mois ne comportant pas 31 jours par exemple.) mais pour la date, c'est le bordel... j'ai cherche sur mister internet mais rien de concluant, ou des fonctions que je ne connais pas...
Voila un exemple de l'Excel en question https://www.cjoint.com/c/FFvpG3fnaQJ ,
Merci infiniment d'avance pour vos aide,
Bonne journee/soiree
La question a deja ete posee sous cette forme mais je ne rencontre pa le meme soucis...
Voila, l'entreprise dans laquelle je bosse a un systeme de carte pour entrer et sortir du batiment, pour controler ainsi leurs horaires de boulot et s'assurer qu'ils ne sont pas surmenes. Bref la question n'est pas la, ces donnees recueillies jour apres jours vont dans un logiciel special (Nera Report) et ne sont affreusement pas exploitables mais sont quand meme transferables sur un fichier excel.
Je suis donc chargee de creer un Excel fonctionnel ou une fois les donnes du logiciel copiees collees dans la premiere feuille Excel, la deuxieme feuille Excel donne ce qu'il faut.
Je dois donc faire en sorte que jour par jour, en face du nom de l'employe, l'heure d'arrivee et la derniere heure de sortie (et pas les allees et venues comptabilisees a midi etc) soient visibles ainsi que le nombre d'heure travaillees.
Pour le moment, j'ai effectue une RECHERCHEV pour le nom des employes et ca fonctionne nickel (mais j'ai peur que tout soit decale et donc inutilisable pour un mois ne comportant pas 31 jours par exemple.) mais pour la date, c'est le bordel... j'ai cherche sur mister internet mais rien de concluant, ou des fonctions que je ne connais pas...
Voila un exemple de l'Excel en question https://www.cjoint.com/c/FFvpG3fnaQJ ,
Merci infiniment d'avance pour vos aide,
Bonne journee/soiree
32 réponses
Résumé de la discussion
Le dossier vise à transformer des données brutes de pointage en un fichier Excel à deux feuilles qui, jour par jour, affiche pour chaque employé l’heure d’arrivée, la dernière heure de sortie et le total d’heures travaillées. Le principal défi réside dans la gestion des dates et le décalage potentiel lors de l’import, notamment pour des mois qui n’ont pas 31 jours. Les solutions évoquées incluent la création d’un modèle mensuel réutilisable (copie/renommage pour chaque mois) et l’éventuelle utilisation de macros VBA pour automatiser l’extraction et l’assemblage des données (dernier départ par jour et heures travaillées). Des échanges abordent aussi des approches fondées sur des cascade de formules et des champs nommés pour contourner les limites des fonctions standards et assurer une mise à jour fiable des résultats.
-
Re,
incrémenter est l'action pour dupliquer le contenu d'une cellule ou d'une plage avec le cliqué glissé
saisir la formule dans une cellule
ensuite observer la cellule active, en bas à droite tu remarquera un petit carré et lorsque tu passeras le pointeur de ta souris sur ce petit carré, le pointeur prend la forme d'une croix, à ce moment bien précis clic gauche sur ce petit carré et sans lâcher le clic, faire glisser vers le bas sur une hauteur souhaitée et lâcher le clic.
la formule sera recopiée en actualisant la cellule cible, exemple Sheet1!C8 deviendra Sheet1!C9 puis Sheet1!C10 etc ...
par contre si tu écris Sheet1!$C$8 restera Sheet1!$C$8
-
Re,
Oh ok merci, je ne savais pas que c'etait le mot propre...Donc non ca ne fonctionne toujours pas, cela ne fait que reporter l'heure indiquee en sheet1 a la meme cellule, or je souhaite garder la toute premiere heure d'arrivee comme tu avais reussi a le faire pour la derniere heure d'arrivee avec cette formule (en D8): =IF(A8<>"";"";IFERROR(SUBSTITUTE(IF(Sheet1!J8>0;Sheet1!J8;IF(Sheet1!H8>0;Sheet1!H8;IF(Sheet1!F8>0;Sheet1!F8;IF(Sheet1!D8>0;Sheet1!D8;IF(Sheet1!J9>0;Sheet1!J9;IF(Sheet1!H9>0;Sheet1!H9;IF(Sheet1!F9>0;Sheet1!F9;IF(Sheet1!D9>0;Sheet1!D9;""))))))));" ";"")*1;"*"))
-
-
Re,
regarde en C8 avec cette formule et incrémente voir si c'est ce que tu cherches
=SI(B8="/";"";SI(Sheet1!C8<>"";Sheet1!C8;SI(ET(B8="";C7<>"");C7;"")))
A+
Mike-31
Une période d'échec est un moment rêvé pour semer les graines du savoir. -
Re,
mais s'il y a une étoile en C9, C10, C11, C12 et C14 tu ne peux pas faire de calcul sur ces lignes en colonne E c'est ce que tu veux
en C8 colle cette formule et incrémente la vers le bas
A+
Mike-31
Une période d'échec est un moment rêvé pour semer les graines du savoir.-
Re,
le but est en effet de faire les calculs d'heures, mais des justes! donc si l'employe ne swipe pas tant pis! dans ce cas la, pas de total d'heure! mais surtout pas un compte d'heures faux!
Car ca, faire un total ligne par ligne quelques soient les valeurs, le logiciel de base le fait deja, moi je cherche a faire un vrai calcul exploitable avec le nombre d'heures veritable, que ce soit malheureusement une * ou un vrai total
D'ou ma requete de garder la premiere heure d'arrivee si plusieurs lignes (quelque soit cette donnee) et la derniere heure d'arrivee si plusieurs lignes(quelque soit cette donnee encore)
Tu vois ce que je veux dire ou pas? -
-
Re,
tant mieux, n'hesite pas a me demander des precisions, je ne suis pas forcement toujours claire quand j'explique.
Le dernier fichier ne resout pas les problemes de conservation des premieres heures d'arrivee au jour n pour l'employe x
Exemple: en C33 et C34, le 15/03/2016 Andrea Barker arrive au bureau la toute premiere fois de la journee a 8h59. Puis elle sort X fois du bureau et provoque donc un changement de ligne sur le logiciel, qui est ici visible en sheet1 (lignes 33 et 34 remplies en sheet 1 de la colonne C a la colonne J car elle sort, revient, sort revient, sont donc visibles toutes ses heures d'allees et venues de C a J). Sa premiere heure d'arrivee est donc 8h59. Or comme dit plus haut, on se balance des heure d'allees et venues, pour avoir un calcul du volume horaire juste, il faut conserver cette premiere heure d'arrivee au bureau ce jour la qui est donc 8h59.
En sheet2 C34, j'aimerai donc qu'il soit marque 8h59, soit sa premiere heure d'arrivee au bureau.
Tout comme tu l'avais fait avec la derniere heure de depart de chacun, ici pour Andrea 22h21 en D33 et D34 nickel.
Tout en prenant en compte que ces decalages de lignes peuvent se faire sur 6 lignes voire plus
-
-
Re,
pourquoi en colonne C ça ne va pas, si tu prends exemple sur les premières lignes sheet1, en
en C8 tu as 07:26
en C9 *
en C10 *
en C15 06:42
sheet 2 que devrait il y avoir en
C8
C9
C10
C11
C12
C13
C14
C15
A+
Mike-31
Une période d'échec est un moment rêvé pour semer les graines du savoir.-
Re,
Pas sur l'exemple que tu m'as envoyé, dessus il y a en C8 7:26, arrivée du cleaner nickel puis après ça garde 7:26 tout le temps alors que leur heure d'arrivée est inconnue,
Il faudrait garder la première heure d'arrivée de l'employé pour chaque date et non la dernière indiquée... car sinon cela fausse les comptages d'heures...
Donc en C9-10-11 etc il devrait y avoir une *
Et en C34 par exemple, il faudrait qu'il y ait 8:59 car c'est sa première heure d'arrivée, tu vois ce que je veux dire ou je suis pas claire?
-
-
Re,
alors le fichier du post 53 doit répondre aux attentes, le revoilà ci dessous
https://www.cjoint.com/c/FFEmcrmtkvj
-
Re,
en C8 sheet2 colle plutôt cette formule qui évitera d'afficher 0:00 qui n'ont aucune raison d'être comme dans ces cellules C10, C38, C64 etc ...
=SI(B8<>B7;SI(Sheet1!C8<>"";Sheet1!C8;"");SI(B8="/";"";SI(OU(D7="*";SUBSTITUE(Sheet1!C8;" ";"")="*");C7;Sheet1!C8)))-
Re,
Desolee pour le retard de ma reponse, j'etais malade hier,
Merci, en effet les 0:00 ne s'affiche plus c'est nickel!
En revanche, j'aurai bien aime garder la toute premiere heure d'arrivee des employes dans les bureaux, car avec la formule que tu me donnes, on repart avec le probleme du changement de ligne... c'est a dire que quand un employe entre une premiere fois dans les bureau puis fait de nombreux aller-retours (provoquant alors les nombreux changements de lignes), il faudrait garder la toute premiere heure d'arrivee.
Avec la precedente formule, cela fonctionnait mais que lors d'un decalage de 2 lignes...
Voila les 2 exemples:
celui avec l'ancienne formule:
https://www.cjoint.com/c/FFEjNrEtjFJ
comme tu peux le voir, en C84 il y a 1 seul retour a la ligne en sheet1 et la premiere heure d'arrivee est bien conservee en revanche en C116, il y a 2 retour a la ligne en sheet1 et la la premiere heure d'arrivee (une * ici bref) n'est pas conservee...
Avec la nouvelle formule:
https://www.cjoint.com/c/FFEjGrpUB4J
Il n'y a plus de pb de 0:00 mais la premiere heure d'arrivee n'est pas conservee comme precedemment (ex en C34 ou l'heure devrait etre la meme qu'en C33, soit 8:59)
-
-
Re,
teste ce fichier voir si c'est bon après je te dirai comment ça marche, mais j'ai due traiter les heures de départ 00:00 de la colonne J sheet1 qui ne me semble pas convenir
https://www.cjoint.com/c/FFCqElneCAj
-
Re,
Merci! ça à l'air de bien coller pour les nombreux changements de lignes possibles, je regarderai ça demain avec le logiciel sous la main du coup.
Par ailleurs, j'ai remodifié la formule en C (heure d'arrivée) car je devais garder l'* pour les premières heures d'arrivées non swipées, car sinon ça fausse les heures de travail des employés (puisque que du coup, la dernière heure d'arrivée indiquée était reprise (ce qui est nickel pour les retour à la ligne correspondant à la même date mais qui n'allait pas pour les nouvelles dates... Bref j'explique très mal mais le pb me paraît résolu aha) https://www.cjoint.com/c/FFCubXH0FsL
Ah mince, cela pose problème pour le calcul des heures c'est ça?
Merci encore, bonne soirée
-
-
Re,
pour l'instant on traitait 2 lignes, mais est il est possible d'avoir plus de 6 lignes sans sortie
et est il possible d'avoir des sorties à 0:00
A+
Mike-31
Une période d'échec est un moment rêvé pour semer les graines du savoir.-
-
Re,
Je ne sais pas si ca peut specialement t'aider, mais j'ai eu une idee pour le probleme des lignes:
Deja, j'ai modifie la formule dans la colonne B en sheet2 (celle qui contient les dates https://www.cjoint.com/c/FFCkCtbu8JJ ) pour qu'un / s'affiche lorsqu'une case n'affiche pas de date et n'est pas censee en contenir une (ainsi seules restent blanches les cellules qui sont en fait la continuite des allees et venues d'une personne a la meme date), peut-etre peut-on alors utiliser une macro avec une boucle tant que qui dirait "tant que Bn est egal a une date ou un espace vide, remplacer le contenu de la cellule Cn+1, Cn+2 (etc jusqu'a ce que la condition soit fausse) par celui de Cn (soit la premiere heure d'arrivee)"
Cela eviterait peut-etre toutes les conditions? Est-ce faisable et pertinent?
C'est juste une idee car je ne sais pas trop utiliser les macros donc pas moyen de savoir si c'est specialement plus pratique...
Merci encore et bonne journee
-
-
Re,
ta formule va bientôt ressembler à une usine à gaz, pour contourner la limite d'argument il faut faire des cascades mais il faut que je regarde comment contourner avec des champs nommés
-
Bonjour, je suis desolee de reouvrir le sujet... en explorant tous les fichiers de donnees relatives aux heures d'allees et venues des employes, je me suis rendue compte que si les heures de depart/arrivees pouvant donc bien aller a la ligne suivante si plus nombreuses que le nombre de colonnes propose (https://www.cjoint.com/c/FFxmkJZ1M1J elles peuvent ausi et surtout largement depasser 2 lignes.... https://www.cjoint.com/c/FFypkVLUm5J
J'ai essaye de reprendre tes formules (=IF(A9<>"";"";IFERROR(SUBSTITUTE(IF(Sheet1!J9>0;Sheet1!J9;IF(Sheet1!H9>0;Sheet1!H9;IF(Sheet1!F9>0;Sheet1!F9;IF(Sheet1!D9>0;Sheet1!D9;IF(Sheet1!J10>0;Sheet1!J10;IF(Sheet1!H10>0;Sheet1!H10;IF(Sheet1!F10>0;Sheet1!F10;IF(Sheet1!D10>0;Sheet1!D10;""))))))));" ";"")*1;"*")) en adaptant et augmantnt le nombre de lignes (en continuant avec 11, 12 etc) mais Excel m'indique que la formule contient trop d'arguments... J'ai essaye d'autres formules mais elle n'aboutissent pas a qqch de concluant...
Desolee de revenir vers ce sujet clos mais... penses-tu pouvoir m'aider?
Merci,
Bonne fin de journee,
Anthea -
Re,
regarde comme ça voir si c'est bon, si c'est bon les cellules colorisées jaune seront éventuellement masquées
https://www.cjoint.com/c/FFxtmNQYvhj
-
Re,
Ouah... Franchement merci, respect, chapeau bas, bravo... C'est exactement ce que je voulais et recherchais, merci infiniment!! sujet clos, enfin aha!
Merci énormément, tu as été très patient, efficace et rapide malgré mon manque d'expertise et la découverte progressive des petits couacs...
Encore merci et bonne soirée à toi
-
-
Re,
tu peux me dire quelles lignes posent problème et me donner les valeurs de départ à retenir et de départ
-
Re,
Oui bien sur, les lignes en questions sont surlignées et les valeurs de départs et arrivées à retenir sont en légende sur le côté
https://www.cjoint.com/c/FFxri5tOvgr
-
-
Re,
Contrôle les résultat colonne E on verra après pour masquer les valeurs de remplacement colonne C et D
https://www.cjoint.com/c/FFxpAqEkFzj
tu remarqueras deux cellule en jaune es ce possible
A+
Mike-31
Une période d'échec est un moment rêvé pour semer les graines du savoir.-
Re,
Pour les horaires d'entrées et sorties situées sur une même ligne, le résultats et nickel et c'est pile ce qu'il me faut! mais dès que c'est sur 2 lignes ça coince... Aurais-tu la solution du problème?
Pour le nombre d'heures en jaune je t'avoue que je n'en sais rien du tout... Je demanderai à ma manager demain
Merci beaucoup et bonne soirée
-
-
Re,
pour Adrea arrivée le 14/03/2016 à 8:52 départ 14/03/2016 à 18:48 soit 9:56
mais le 15/03/2016 arrivée 8:59 il n'y a pas de départ donc il faudrait prendre le départ de la ligne inférieure 22:21
mais hic il y a une arrivée à 15:28 et sans date même anomalie pour le 16/03/2016 et la Excel est incapable de deviner ce qu'il faut faire
reprend le fichier en en colonne E saisi manuellement le résultat que tu souhaite voir ce qui est possible de faire
-
En effet... c'est ce que je t'expliquais plus haut, bcp d'allees et venues et donc le logiciel interne a mis tout ca a la ligne suivante...
A ce moment la, il faudrait conserver la toute toute premiere heure d'arrivee et utiliser la toute derniere heure de depart... En effet ce n'est pas trop possible pour Excel de deviner, peut etre faudrait il "dupliquer" la date sur la case d'en dessous si celle-ci se trouve etre vide?
Voila du coup ce que j'aimerai qu'il affiche:
https://www.cjoint.com/c/FFxoBK6EZyJ
-
-
Re,
regarde les chiffre en rouge colonne E voir si c'est ce qu'il faut faire et si la valeur affichée est bonne
https://www.cjoint.com/c/FFxnIRwzC8j
-
Serait-il possible de me montrer sur Andrea plutot s'il-te-plait?
Car les employes charges de l'entretien comme tu as pu le voir ont des horaires tres decales et ne passe quasiment jamais leur carte donc ce n'est pas tres utile d'exploiter les donnees relatives a leur passage
De plus, une etoile signifie une absence de passage de carte, mais un depart quand meme, il faudrait alors que la fonction s'arrete aussi si elle rencontre une etoile dans la case depart et qu'il n'y a rien dans la case arrivee d'apres, car sinon le total d'heure est errone
Merci beaucoup
-
-
Re,
donne un exemple parce que la je ne comprends plus, dans ton post 31 tu me dis NON et dans ton dernier post il faudrait boucler sur les lignes suivantes ?
-
Re,
alors par rapport à mon dernier fichier que faut il faire !
-
Re,
tu veux dire que sur une ligne il y a une arrivée sans départ, dans ce cas on prend le dernier départ de la ligne suivante s'il n'y a pas d'arrivée sur cette ligne
c'est ça
-
Non, lorsque il y a une arrivee au bureau, soit l'employee part en passant sa carte et une heure s'affiche dans une des colonnes departs, tout va bien et dans ce cas les colonnes qui suivent ce depart sont vierges, soit il oubli de passer sa carte en sortant et dans ce cas il y a une * pour indiquer que l'employe est parti mais n'est pas revenu.
Dans tout les cas, la derniere colonne remplie est une colonne depart que ce soit avec une etoile (swipe out non fait) ou avec une heure (swipe out fait)
Je t'envoie un screenshot, ce sera peut etre plus parlant, desolee c'est un peu grossier
https://www.cjoint.com/c/FFxmkJZ1M1J
-
-
-
Mon dieu..... Merci!!! c'est parfait, c'est exactement ce qu'il me fallait!!!
Une derniere derniere derniere chose, je crois que c'est enfin le dernier des couacs, ce que je t'ai dit plus haut avec les derniers departs, il semblerait que ca continue sur plusieurs lignes... jusqu'a temps que l'employe ne swipe plus in a priori...
Grr, pardon de t'en rajouter petit a petit a chaque fois...
Et pour les arrivees sans depart, tu veux dire celle la par exemple?
25/03/2016 08:49 * 00:00
Ici c'est normal, l'etoile signifie au'il a oublie de swipe out mais n'est plus revenu au bureau, il est donc definitvement parti mais on ne sait pas quand
-
-
Re,
Donc on prend le dernier départ dans l'ordre colonne J si non H si non F ou D
- 1
- 2
Suivant
