Optimisation de recherche dans tableau Excel
Pour le nombre de parrains différents, en matriciel
=SOMME(SI(Tableau2[@[Parrain1 groupe1]:[Parrain groupe2]]<>"";1/NB.SI(Tableau2[@[Parrain1 groupe1]:[Parrain groupe2]];Tableau2[@[Parrain1 groupe1]:[Parrain groupe2]])))Laurent Longre a écrit :=NB.SI(Plage;Plage)renvoie une matrice contenant le nombre de répétitions de chaque valeur à l'intérieur de la plage indiquée. Si une valeur est répétée N fois, la matrice correspondante comporte N fois le nombre N.
En faisant la somme des inverses de ces nombres, on obtient N*(1/N), soit 1 pour chacune des valeurs dupliquées. Le résultat est le nombre de valeurs uniques de la plage.
Comme la plage utilisée ne doit pas comprendre de cellules vides, on la modifie ainsi :
=SOMME(SI(Plage<>"";1/NB.SI(Plage;Plage)))
Je vais tenter de mettre vos réponses sur pied dans mon fichier.
Plus tôt j'étais en train de mettre une autre alternative au point à partir des formules précédentes.
L'idée étant de séparer en 2 listes distinctes les personnes se trouvant dans le groupe 1 et les personnes dans le groupe 2 (même ci elles possèdes toutes les deux des noms en commun), car je me suis rendu compte que :
- D'une part des personne pouvaient en fin de compte se trouver 2 fois dans le groupe 2 (et donc posséder 2 dates d'entrée distinctes pour le groupe 2)
- Que j'avais déjà à disposition ces deux listes distinctes
J'ai donc filtré la liste du groupe 1 pour n'obtenir qu'un ID unique et distinct pour chaque personne, et utilisé ta formule pour détermine la date de sortie MAX du groupe 1 (donc la plus récente)
J'ai utilisé le même processus pour la liste du groupe 2, cependant m'étant rendu compte qu'une personne pouvait posséder 2 dates d'entrée dans ce groupe, impossible de recréer le même tableau que pour la liste du groupe 1, car je n'ai pas réussi à déterminer la formule me permettant de connaitre la première date d'entrée d'une personne dans le groupe 2..
En trouvant cette formule, j'arriverai à obtenir 2 listes distinctes possédant un ID unique par personne qui liste la date de sortie MAX de chaque personne et la date d'entrée "MIN" de chaque personne, ce qui me permettrait finalement de comparer la différence de ces deux date et de déterminer si le parrain est le même (en croiser les données de chaque liste). C'est un peu plus long mais cela me permettrait d'arriver au terme du problème.
Donc si quelqu'un à une idée de comment déterminer cette formule, je suis preneur !
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.
J'ai utilisé le même processus pour la liste du groupe 2, cependant m'étant rendu compte qu'une personne pouvait posséder 2 dates d'entrée dans ce groupe
Donc la spécification, évolue...
Donc si quelqu'un à une idée de comment déterminer cette formule, je suis preneur !
si tu avais un jeu de donnés ...
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.
J'ai utilisé le même processus pour la liste du groupe 2, cependant m'étant rendu compte qu'une personne pouvait posséder 2 dates d'entrée dans ce groupe
Donc la spécification, évolue...
Donc si quelqu'un à une idée de comment déterminer cette formule, je suis preneur !
si tu avais un jeu de donnés ...
Mea culpa ..
Je vais mettre un fichier plus conséquent sur pied et le poster ici !
Le fichier étant trop volumineux, je l'ai zippé, cependant je ne sais pas si le format conviendra.
Dans ce fichier il y a donc tous les cas de figure, il me manque la formule pour déterminer la date d'entrée dans le groupe 2 MIN (la plus petite s'il y en a deux, donc l'inverse de la formule précédente)
formule pour déterminer la date d'entrée dans le groupe 2 MIN
j'ai aussi modifié la formule du max car ton onglet est maintenant dédié au groupe 1, cela simplifie
=MAX(SI(Tableau1[ID]=[@ID];Tableau1[Date sortie]))=MIN(SI(Tableau4[ID]=[@ID];Tableau4[Date entrée]))MAIS ... avec XL2016 tu as beaucoup plus simple !!
= MIN.SI.ENS ( plage valeurs ; plage1 ; critère1 ; plage2 ; critère2 )et
= MAX.SI.ENS ( plage valeurs ; plage1 ; critère1 ; plage2 ; critère2 )La formule du min n'est toutefois pas valable si une valeur n'était pas renseignée, car elle vaudrait 0. Il faudrait une formule un peu plus alambiquée ! C'est vrai aussi pour la formule matricielle proposée ci-avant.
S'il s'agit de comparer la date sortie max du groupe 1 et la date min d'entée groupe 2, on peut aussi passer simplement par un seul TCD
Ton fichier est lourd car il comportant des millions de lignes blanches !!
Les formules concernant les dates MAX pour le groupe 1 et MIN pour le groupe 2 fonctionnent !
J'ai pu terminer ces tableaux et passer à l'étape comparaison des parrains.
Pour chacun des 2 tableaux j'ai ajouté une colonne "Parrain" avec pour chaque ligne (correspondant à un ID) la formule :
=INDEX(Tableau1[Parrain];SOMMEPROD(GRANDE.VALEUR((Tableau1[Date de sortie]=[@[Date de sortie]])*(Tableau1[ID]=[@[ID]])*(LIGNE(ID]));1))-1)
Cette formule était présente dans les fichiers précédents que vous m'avez envoyés, et à l'air de fonctionner pour trouver le parrain correspondant en fonction de l'ID et de la date d'entrée/sortie (je ne suis pas sûr de savoir si c'est la meilleure option mais elle semblait fonctionnelle, je ne la comprend juste pas très bien).
J'ai donc ensuite construit un 3ème tableau (sans compter un tableau intermédiaire) avec dans un premier temps 3 colonnes :
ID | Date de sortie groupe 1 MAX | Date d'entrée groupe 2 MIN, et ce pour chaque ID commun aux 2 premiers tableaux.
Puis une colonne "Écart" (pour le calcul de différence entre les 2 dates), deux colonnes "Parrain Groupe 1" et "Parrain Groupe 2" (pour donner le parrain correspondant pour les deux dates mais avec un seul ID, à l'aide de 2 RECHERCHEV), et enfin une colonne "Validé" comportant la formule
=SI(ET(ET(écart<=365;écart>0);Parrain groupe 1=Parrain groupe 2);"OUI";"NON") pour lister tous les cas remplissant tous les critères initiaux.
J'ai seulement un doute sur la première formule que je vous ai énoncé plus haut (celle pour déterminer le Parrain correspondant, sachant qu'une personne peut apparaitre plusieurs fois dans un même tableau à cause des différentes dates), mais sinon le problème à l'air d'être résolu.
Je joins un fichier listant les différentes modifications, si vous pensez que des apports ou des améliorations s'imposent.
Je n'avais pas fait attention (je ne comprenais pas la phrase au début), je viens de faire la manipulation F5 -> Supprimer cellules vides, effectivement c'est tout de suite mieux, merci !
=INDEX(Tableau1[Parrain];SOMMEPROD(GRANDE.VALEUR((Tableau1[Date de sortie]=[@[Date de sortie]])*(Tableau1[ID]=[@[ID]])*(LIGNE(ID]));1))-1)J'ai seulement un doute sur la première formule que je vous ai énoncé plus haut (celle pour déterminer le Parrain correspondant, sachant qu'une personne peut apparaitre plusieurs fois dans un même tableau à cause des différentes dates), mais sinon le problème à l'air d'être résolu.
La formule est correcte, pas de problème.
Voici le fichier à ma sauce (peu de modif, sauf noms de tableau plus mnémonique -encore que- et un onglet en moins)
=INDEX(Tableau1[Parrain];SOMMEPROD(GRANDE.VALEUR((Tableau1[Date de sortie]=[@[Date de sortie]])*(Tableau1[ID]=[@[ID]])*(LIGNE(ID]));1))-1)Excel va créer une matrice répondant à ces valeurs
(Tableau1[Date de sortie]=[@[Date de sortie]]) * (Tableau1[ID]=[@[ID]]) * (LIGNE(ID]) avec donc des numéros de lignes qui respectent les critères ID et date
et ensuite prendra la plus grande valeur de ligne (ce n'est pas forcément la date la plus récente !)
si tu veux, on peut changer
La formule {=MAX(SI(_TG1[ID]=[@ID];_TG1[Date sortie]))} est utilisée pour déterminer la date MAX pour une personne, donc la plus récente : dans l'exemple elle fonctionne
La formule {=MIN(SI(_TG2[ID]=[@ID];_TG2[Date entrée]))} est utilisée pour déterminer la date MIN pour une personne, donc la plus ancienne : dans l'exemple elle fonctionne aussi
Enfin, la formule =INDEX(_TG1[Parrain];SOMMEPROD(GRANDE.VALEUR((_TG1[Date sortie]=[@[Date sortie]])*(_TG1[ID]=[@ID])*(LIGNE(_TG1[ID]));1))-1)
est utilisée pour déterminer le Parrain correspondant en fonction de l'ID et de la date correspondante, et ce pour les deux tableaux (Groupe 1 et Groupe 2) : ici je veux seulement que la formule puisse déterminer à partir d'un ID et d'une date le Parrain correspondant (ce qui est affiché sur la même ligne depuis les tableaux de données d'origine) pour le stocker dans ces 2 autres tableaux. Les résultats semblent correspondre, mais ce n'est qu'un petit fichier d'exemple. Penses-tu que la formule nécessite d'être modifiée ?
Pour le tableau de synthèse rien à redire il est nickel.
[quote=nc0las post_id=846038 time=1583832366 user_id=68858]
Enfin, la formule =INDEX(_TG1[Parrain];SOMMEPROD(GRANDE.VALEUR((_TG1[Date sortie]=[@[Date sortie]])*(_TG1[ID]=[@ID])*(LIGNE(_TG1[ID]));1))-1)
Les résultats semblent correspondre, mais ce n'est qu'un petit fichier d'exemple. Penses-tu que la formule nécessite d'être modifiée ?
[/quote]
La formule est correcte ... on pourrait aussi la remplacer par
{=INDEX(_TG1[Parrain];MAX((_TG1[Date sortie]=[@[Date sortie]])*(_TG1[ID]=[@ID])*(LIGNE(_TG1[ID]))-1))}en matriciel ... mais attention avec INDEX quand le n° de ligne vaut 0 !
C'est noté !
Il me semble que mon problème est résolu, je tâcherai de mieux synthétiser la prochaine fois.
Un immense merci pour toutes ces réponses et pour votre disponibilité !