Optimisation de recherche dans tableau Excel
Bonjour,
A partir d’un tableau de données assez conséquent, je souhaite pouvoir connaitre le nombre de personnes remplissant certains critères.
Une personne (identifiée par son Nom et son ID), est acceptée dans un groupe par le biais d’un parrain. Elle obtient alors un ticket d’entrée avec un numéro, et possède une date d’entrée dans ce groupe. En le quittant, elle obtient une date de départ.
Sur une ligne on retrouve donc le nom de la personne, son appartenance à un groupe, son ID, le numéro du ticket d’entrée dans un groupe, la date d’adhésion au groupe et enfin la date de départ de ce même groupe :
Groupe 1 | Numéro ticket 1 | Pierre Paul Jacques | Parrain | ID | Date adhésion |Date départ
Groupe 2 | Numéro ticket 2 | Pierre Paul Jacques | Parrain | ID | Date adhésion | Date départ
[ ……. ]
Problème : le nom d’une personne apparait plusieurs fois dans le tableau car elles peuvent avoir appartenu aux 2 groupes, avoir intégré le premier groupe 2 fois (en entrant donc dans ce groupe une deuxième fois avant de le quitter à nouveau), ce qui ajoutera encore plus de lignes, tel que :
Groupe 1 | Numéro ticket 1 | Pierre Paul Jacques | Parrain | ID | Date adhésion |Date départ
Groupe 1 | Numéro ticket 2 | Pierre Paul Jacques | Parrain | ID | Date adhésion |Date départ
Groupe 2 | Numéro ticket 3 | Pierre Paul Jacques | Parrain | ID | Date adhésion | Date départ
[ ……. ]
Cependant, même si une personne quitte le 2eme groupe (et donc possède une date de départ pour le groupe 2), elle ne pourra pas le réintégrer. Il y aura donc au maximum 3 lignes concernant une même personne.
Mon souhait est de connaitre les personnes ayant quitté le groupe 1 pour le groupe 2 (aucune importance si la personne soit entrée une ou deux fois dans le groupe 1) tout en remplissant ces critères :
- Que la différence entre la date de départ du groupe 1 (si la personne y est entrée 2 fois alors on prend la deuxième date de départ du groupe 1, la plus récente) et la date d’adhésion au groupe 2 soit d’au maximum un an
- Que le parrain pour le groupe 1 et 2 soit le même (si le parrain de la personne pour le groupe 1 n’est pas le même pour le groupe 2 cela ne compte pas)
- Connaitre ce nombre par parrain
Étant donné qu’il faut comparer plusieurs lignes car les informations nécessaires pour une seule personne ne se trouvant pas sur une même ligne, je peine énormément à trouver comment gérer ce problème de manière rapide et efficace.
J’ai dans un premier temps listé à la main les personne remplissant ces critères en coloriant les lignes, puis en triant avec la formule « =SI(couleurcellule(n°cellule)>0;"Oui";"Non") », pour enfin créer un TCD à partir de ce tableau filtré et connaitre ce nombre de personne par parrain, mais la tâche est fastidieuse et je n’ai aucune idée de comment procéder de manière optimisée pour un jeu de données possédant des milliers de lignes ..
Des idées svp ?
Bonjours, et bienvenue
Long discours mais sans fichier (https://forum.excel-pratique.com/viewtopic.php?f=2&t=13) ... à brûle-pourpoint je proposerais de faire un TCD et des segments.
Merci !
Oui je suis désolé pour l'absence de fichier, il est assez conséquent.
Néanmoins si cela peut aider, j'ai créé un deuxième fichier (beaucoup moins fourni) qui montre à peu près comment les données sont structurées, si jamais cela peut aider ..
Pour le TCD j'ai déjà essayé mais je ne vois pas comment procéder. Et le mot "segment" ne me parle pas vraiment je le crains ..
En effet, un TCD 'est pas suffisant .... je regarde le sujet cet après-midi.
- Que le parrain pour le groupe 1 et 2 soit le même (si le parrain de la personne pour le groupe 1 n’est pas le même pour le groupe 2 cela ne compte pas)
Peut-il y avoir des parrains différents pour le groupe 1 ? si oui quelle est la règle ?
tous groupes 1 et 2 confondus donc ...- Connaitre ce nombre par parrain
Pour éviter des formules extrêmement complexes, je suis passé par des TCD.
- Que le parrain pour le groupe 1 et 2 soit le même (si le parrain de la personne pour le groupe 1 n’est pas le même pour le groupe 2 cela ne compte pas)
Peut-il y avoir des parrains différents pour le groupe 1 ? si oui quelle est la règle ?
tous groupes 1 et 2 confondus donc ...- Connaitre ce nombre par parrain
Oui plusieurs parrains pour le 1er groupe c'est possible :
exemple : un parrain pour l'entrée dans le groupe 1, puis un deuxième lorsqu'une personne sort et ré entre dans le groupe 1, cela ne fait pas parti de la problématique.
Mais si ce deuxième parrain est encore présente lors du passage au groupe 2, là il compte.
L'important est le passage d'un groupe à un autre en gardant le même parrain.
Donc la connaissance du nombre par parrain est souhaitable seulement pour les parrains qui reste le même lors du passage du groupe 1 vers le 2.
donc un même ID peut-être attribué à 2 personnes différentes ?
Non un ID est propre à une personne !
ce n'est pas le cas de ton fichier !
Désolé j'ai peut-être formulé mon problème de manière un peu brut, ceci étant ma première publication je manque encore sûrement de clarté.
Un ID est bien propre à une personne, tout comme son Nom.
Je viens de revérifier le petit fichier que j'ai envoyé auparavant, les ID sont tous uniques et propres à une seule personne pourtant, y a-t-il un endroit ou j’ai pu faire une erreur ?
ID 39
Du coup, ma proposition https://forum.excel-pratique.com/viewtopic.php?p=844342#p844342 pourrait être simplifiée.
Ton sujet est complexe et amèneraient des formules matricielles, c'est pour cela que je suis passé par des TCD. Mais je vais tenter le coup par des formules.
Du coup, ma proposition https://forum.excel-pratique.com/viewtopic.php?p=844342#p844342 pourrait être simplifiée.
Ton sujet est complexe et amèneraient des formules matricielles, c'est pour cela que je suis passé par des TCD. Mais je vais tenter le coup par des formules.
Concernant l'ID "39" c'est une erreur, au temps pour moi !
Pour ma part j'ai réutilisé tes TCD, ils fonctionnent et je comprend la logique, il y a juste les formules permettant de déterminer le parrain du groupe 2 et de savoir si c'est le même parrain entre les 2 groupes que je ne saisi pas bien. Mais sinon tout le début m'a bien aidé à entamer le problème !
Je suis sur les formules ... voici un début (je me suis basé ici uniquement sur l'ID)
In fine ce n'est pas si complexe que cela !
pour les parrains
mais elles fonctionnent ? elles donnent bien les bonnes valeurs ?
ton sujet n'est pas simple, les formules non plus !!
Je les ai testées sur mon fichier avec beaucoup plus de données et oui elles affichent toutes des résultats.
Cependant il y a quelques détails/erreur que je ne n'arrive pas à démêler :
Exemples :
Pour certaines personnes, le parrain 1 du groupe 1 et le parrain 2 du groupe 1 sont inversés.
Pour d'autres, les colonnes Parrain 1 groupe1, Parrain 2 groupe 1 et Parrain 1 groupe 2 sont remplies alors que les 3 sont le même parrain.
Les formules sont assez complexes et c'est justement parce que je ne les comprend pas que je n'arrive pas à les corriger ou à comprendre ce qu'elles affichent et comment.
Cela dit j'ai déjà fait un sacré bon en avant, mais je concède que le problème est très difficile et que sa résolution n'est pas une mince affaire..
Pour certaines personnes, le parrain 1 du groupe 1 et le parrain 2 du groupe 1 sont inversés.
solution 1 : trie par date de début et l'ordre sera respecté
solution 2 : on peut triturer encore un peu plus la formule pour tenir compte des dates si la solution 1 n'est pas possible pour toi
Pour d'autres, les colonnes Parrain 1 groupe1, Parrain 2 groupe 1 et Parrain 1 groupe 2 sont remplies alors que les 3 sont le même parrain.
dans ce que je t'ai donné, j'ai juste extrait le nom des parrains, il faut donc ajouter une colonne pour compter le nombre de parrains différents, je vais regarder ce point
C'est le moins qu'on puisse dire ! et encore elles ne sont pas matricielles. On pourrait faire plus simple t plus lisible avec une macro car on peut s'épancher sur plusieurs lignes. J vais tenter de les expliquer (encore que parfois c'est de la recette de cuisine)Les formules sont assez complexes et c'est justement parce que je ne les comprend pas que je n'arrive pas à les corriger ou à comprendre ce qu'elles affichent et comment.
voir page suivante pour le nombre d parrains différents