Utiliser un TCD comme base de données

Bonjour à tous,

J'ai beau chercher, je n'arrive pas à trouver un titre explicite pour ma question...

Voilà mon problème: j'extraie régulièrement les badgeages des employés, et le fichier m'affiche une ligne par badgeage. Pour pouvoir l'exploiter, j'ai besoin d'une ligne par jour et par employé, avec une colonne pour chaque badgeage effectué ce jour-là par cet employé.

Avec une formule SI et OU, j'assigne un n° de badgeage à chaque horaire pour un même salarié et un même jour (badgeage n°1, badgeage n°2, etc), puis je transforme en tableau pour qu'il intègre automatiquement un éventuel ajout ou suppression de ligne. Je crée ensuite un TCD avec les noms et dates en ligne et les n° de badgeages en colonne, et la somme des horaires en valeur.

J'aimerais ensuite pouvoir exploiter les valeurs de ce TCD dans un 3ème onglet pour pouvoir effectuer toutes sortes de calcul (temps travaillé, temps de repos...) et de statistiques.

Mais comment faire pour que ces calculs se fassent si de nouvelles lignes sont ajoutées dans le 1er onglet et se répercutent dans le TCD?

Le résultat final espéré serait une sorte de matrice dans laquelle je n'aurais qu'à copier/coller le contenu brut de mon extraction dans le 1er onglet, pour avoir le résultat final dans le dernier onglet qui s'adapte automatiquement.

Je vous joins un fichier en PJ, j'espère que ce sera plus clair pour vous.

15test-excel.zip (7.60 Ko)

Merci d'avance pour votre aide!

Cordialement

Bonico

Bonjour

Ton exemple ne montre pas du tout ce que tu cherches à obtenir en bout de chaîne.

Peux-tu préciser :

  • si le 1er badgeage d'une personne sur une journée est le début et le suivant la fin et ainsi de suite
  • les résultats à obtenir
  • si tu peux installer l'add on PowerQuery (intégré à partir de 2016 mais utilisable sur 2010 et 2013 en add on) qui simplifierait à mon avis le traitement
  • pourquoi tu utilises le format xls périmé depuis plus de 10 ans

Bonjour,

Désolé, je ne suis pas un grand expert d'excel.

Pour ce qui est du format, j'ai toujours entendu dire que le format xlsx n'apportait pas grand chose par rapport à du xls, si ce n'est que c'est un format propriétaire Microsoft beaucoup moins ouvert que xls, et qu'il est il est donc moins facilement lisible pour des personnes n'ayant pas la même version du pack office ou qui utilisent un autre tableur qu'excel...

Aucune idée concernant Powerquery, je me renseignerai.

Pour ce qui est des 2 autres questions:

- Le 1er badgeage d'une journée est le début et le suivant n'est pas forcément la fin, c'est juste le badgeage n°2 de la journée: le badgeage de fin de journée sera le dernier de la journée pour un même salarié et une même date, étant précisé que le nombre de badgeages sur une même journée n'est pas limité (il doit en principe être un nombre pair). C'est le calcul fait dans la colonne D du 1er onglet ("BD").

- Résultat à obtenir: je voudrais qu'à chaque fois que j'exporte une liste de badgeages de notre logiciel de gestion et que je les copie/colle dans le 1er onglet, tout se mette à jour tout seul et que j'obtienne dans l'onglet final (3ème onglet) une ligne par salarié et par date, avec les différents badgeages de la journée dans chaque colonne.

Mon problème, c'est que si je copie/colle une liste de 50 lignes dans mon 1er onglet (qui en compte 44, ligne d'en-têtes incluse), les lignes supplémentaires seront bien prises en compte dans mon TCD (puisque la source de celui-ci est le "tableau" du 1er onglet), mais pas dans mon onglet final.

Or, mon souhait serait qu'une fois que mon fichier est bien "paramétré", le seul onglet à modifier soit le 1er ("BD").

J'espère que ces précisions te permettront d'y voir plus clair dans ma demande, et merci d'avance!

Cordialement

Bonico

RE

Pour ce qui est du format, j'ai toujours entendu dire que le format xlsx n'apportait pas grand chose par rapport à du xls, si ce n'est que c'est un format propriétaire Microsoft beaucoup moins ouvert que xls, et qu'il est il est donc moins facilement lisible pour des personnes n'ayant pas la même version du pack office ou qui utilisent un autre tableur qu'excel...

Il y a de très grandes différences : poids du fichier, prise en charge des nouvelles fonctionnalités Excel apparues depuis la version 2007 et il y a en a beaucoup...

xls est totalement propriétaire (et peut contenir à ton insu des informations très personnelles comme c'est cas dans le format doc) alors qu'au contraire xlsx est basé sur l'xml, non propriétaire (même si Microsoft l'a un peu personnalisé)

xlsx a plus de 10 ans : tous les tableurs autres qu'Excel non antédiluviens savent l'ouvrir et Microsoft fournit un utilitaire pour les versions Excel antérieures à 2007

- Le 1er badgeage d'une journée est le début et le suivant n'est pas forcément la fin, c'est juste le badgeage n°2 de la journée: le badgeage de fin de journée sera le dernier de la journée pour un même salarié et une même date, étant précisé que le nombre de badgeages sur une même journée n'est pas limité (il doit en principe être un nombre pair). C'est le calcul fait dans la colonne D du 1er onglet ("BD").

- Résultat à obtenir: je voudrais qu'à chaque fois que j'exporte une liste de badgeages de notre logiciel de gestion et que je les copie/colle dans le 1er onglet, tout se mette à jour tout seul et que j'obtienne dans l'onglet final (3ème onglet) une ligne par salarié et par date, avec les différents badgeages de la journée dans chaque colonne.

Justement je demande les calculs du 1er onglet et le contenu du dernier afin de comprendre le résultat final souhaité sinon impossible de te proposer des solutions.

Un TCD peut être exploité avec LIREDONNEESTABCROISDYNAMIQUE mais vu l'évolutivité de tes données cela ne me parait pas le meilleure piste...

Bonjour Chris,

C'est noté pour le format xlsx. J'ai réenregistré mon fichier dans ce format, que tu trouveras ci-joint.

15test-excel.xlsx (19.88 Ko)

Justement je demande les calculs du 1er onglet et le contenu du dernier afin de comprendre le résultat final souhaité sinon impossible de te proposer des solutions.

Tu veux que je te donne le détail de mes formules? Tu peux le retrouver dans le fichier, mais voici:

  • après avoir copié/collé mes données (liste de badgeages) dans le 1er onglet, je les trie par salarié (col A)/date (col B)/heure (col C). Ce 1er onglet étant un "tableau", il intègre automatiquement tout ajout ou suppression de ligne lorsque je fais un nouveau copié/collé de données.
  • en colonne D de ce 1er onglet, une formule permet de dire s'il s'agit du 1er, 2ème, 3ème, etc badgeage de la journée. La formule vérifie si le salarié et la date sont identiques à la ligne au-dessus. Si oui, le n° du badgeage est le n° de badgeage du dessus +1. Si le salarié ou la date (ou les deux) sont différents, le n° du badgeage est 1.
  • Le TCD du 2ème onglet met mes données dans la forme souhaitée (= 1 ligne par salarié et par date). La source du TCD étant le "tableau" du 1er onglet, mon TCD prend bien compte les éventuels ajout ou suppression de lignes du 1er onglet en cas de nouvel import de données.
  • Je comptais sur mon 3ème onglet (dont la source est le TCD du 2ème onglet et qui utilise bien la formule LIREDONNEESTABCROISDYNAMIQUE) pour ajouter des formules de calcul (temps de travail...).

Mais ce dernier ne prend pas en compte les éventuels ajouts/suppression de lignes du 1er onglet en cas de nouvel import de données.

Si tu vois une autre solution pour arriver au même résultat...

Cordialement

Bonico

Re

En principe on utilise pour LIREDONNEESTABCROISDYNAMIQUE, les titres de l'onglet de restitution pas ceux du TCD sinon on a peu d’intérêt à utiliser cette fonction...

Donc ce sont des noms, dates et titres de l'onglet synthèse qui sont à utiliser.

Cependant ce qui est nécessaire pour t'aider c'est ce qu'il y a après cette phase puisque tu écris "pouvoir effectuer toutes sortes de calcul (temps travaillé, temps de repos...) et de statistiques"

Tu reste scotché sur ton idée de départ pour aller de ton point de départ à ton point d'arrivée alors que j'essaye sans succès de te faire comprendre qu'il y a sans doute d'autres voies plus performantes mais je n'ai toujours pas obtenu le détail du point d'arrivée...

Le 1er résultat je l'ai déjà via PowerQuery depuis ma 1ère réponse

badgeage

double post, sorry

Bonjour Chris,

OK OK, pour tout te dire, le point d'arrivée quand j'ai posé ma question, c'était d'arriver à un tableau où j'aurais une ligne par salarié et par jour. J'avais as encore trop réfléchi aux calculs qui suivraient.

Mais j'ai poussé ma réflexion plus loin pour essayer d'identifier à quoi mon véritable produit fini pourrait ressembler. Tu le trouveras ci-joint.

13test-excel.xlsx (26.95 Ko)

Pour faire court, deux données sources seraient à mettre à jour manuellement (onglets "BD" et "liste de salariés"), et j'aimerais que le reste du fichier se mette à jour tout seul. Le but serait d'arriver à des "fiches statistiques" pour chacune des directions de mon entreprise. Cela dit, j'ai l'impression que même les formules (NB.SI. ENS notamment) que j'utilise dans mes fiches ne fonctionnent pas très bien.

Voilà, tu as le point de départ, et le point d'arrivée. Mais comme dit le proverbe, l'important n'est pas la destination, mais le chemin pour y arriver (ou un truc dans le genre). Merci à toi en tout cas de prendre le temps de m'aider.

Cordialement

Bonico

PS: j'ai essayé d'installer power query, mais seul l'administrateur peut l'installer sur mon PC pro. Les utilisateurs qui voudront visualiser mon fichier voire y mettre les données sources à jour devront-elles avoir installé power query?

Bonjour,

Quelqu'un peut m'aider sur ce sujet?

Comme le soulignait Chris, je me suis peut être trop focalisé sur la méthode pour parvenir au résultat avant de définir avec précision ce résultat et le point de départ.

Avec mon dernier post, il le semble que ces éléments sont maintenant bien posés.

Quelqu'un a t il une solution à mon problème initial, ou bien une solution alternative à proposer?

Merci d'avance pour votre aide!

Cordialement

Bonico

Bonjour,

Quelqu'un peut m'aider sur ce sujet?

Comme le soulignait Chris, je me suis peut être trop focalisé sur la méthode pour parvenir au résultat avant de définir avec précision ce résultat et le point de départ.

Avec mon dernier post, il le semble que ces éléments sont maintenant bien posés.

Quelqu'un a t il une solution à mon problème initial, ou bien une solution alternative à proposer?

Merci d'avance pour votre aide!

Cordialement

Bonico

RE

J'avais avancé avec PowerQuery et obtenu cela

badgeage2

Mais le traitement n'est pas des plus simples et, d'autres sujets m'ayant occupé, j'avais oublié le fichier...

Il manque quelques infos à ton énoncé : si le nombre de badgeages est impair on fait quoi ?

Ci-joint une proposition par formule ajoutées au tableau source + TCD

Bonjour Chris,

Honte à moi ! J'avais laissé tombé ce sujet car je n'avais pas vu ta réponse (eh oui, il y a une page 2...)

Entre-temps je me suis mis à Power Query et j'ai résolu mon problème. Ma solution est assez compliquée et lourde (formules + TCD + requête power query), mais ça fonctionne.

Je vais examiner attentivement ton fichier et voir si je peux simplifier ma solution.

En tout cas merci beaucoup pour ton aide, grâce à power query (on abrège en PQ?), je peux faire l'équivalent d'une macro sans rien connaître du VBA. La classe!

Au fait, pour répondre à ta question: si le nombre de badgeages est impair, on ne fait pas de calcul pour cette journée.

Ce sujet peut être clôturé. Je vois pas trop où cliquer mais je vais regarder après avoir validé ce post.

Cordialement

Bonico

Bonjour,

La solution alternative à powerquery était de ne pas créer d'onglet synthèse mais d'appliquer directement les formules

dans l'onglet du TCD.

BOnjour

La solution alternative à powerquery était de ne pas créer d'onglet synthèse mais d'appliquer directement les formules

dans l'onglet du TCD.

C'est une solution : j'avais aussi donné une solution non PowerQuery avec des formules amont du TCD pour garder la souplesse du TCD

Rechercher des sujets similaires à "utiliser tcd comme base donnees"