Réponses multiples avec la fonction INDEX

[Hervé]

Bonjour et merci à tous pour vos précieux conseils et pour le temps que vous consacrez à nous répondre.

Avec la formule :

Index(Plage des noms; Equiv(Date & Heure; Plage des dates & Plage des heures; 0))

, j'obtiens l'affichage d'une seule réponse. Même s'il y a plusieurs résultats, seul le premier de la liste apparaît.

Vous aurez deviné ma question : Comment faire pour afficher l'intégralité des résultats ?

Merci encore.

Hervé

 

[Michel_M]

Bonjour Hervé

Ci joint proposition

A+
Michel

 

[Monique]

Bonjour,

Sans colonne intermédiaire, en utilisant la fonction Decaler() une fois la 1ère valeur trouvée.
D'accord, ça ne simplifie pas les formules.

 

[JCA06]

Bonjour Hervé, Michel, Monique, le Forum,

Jentame un projet sur des calculs dancienneté et jai bien limpression, Monique, que ta combinaison de fonctions INDEX, EQUIV, DECALER est ce dont jai besoin.

Cependant, jai besoin de bien comprendre la combinaison pour ladapter à mes besoins.

Serait-ce abuser de ton extrême bonté de bien vouloir la commenter en clair ?

Jai le début : =SI(SOMME((C$10:C$15=B$2)*(D$10$15=C$2)*1)<=NBVAL(F$10:F10);"" : si toutes les valeurs ont été reportées alors "" ;

Pour la suite, je suis largué !
INDEX(DECALER(B$10:B$15;EQUIV(F10;B$10:B$15;0);EQUIV(B$2&C$2;DECALER(C$10:C$15;EQUIV(F10;B$10:B$15;0)&DECALER(D$10$15;EQUIV(F10;B$10:B$15;0);0);0)))

Pour ce qui est des calculs, je sais que je vais trouver mon bonheur dans l'excellent fichier DatesetHeuresCMC.xls (et qui c'est le M ?) que j'ai récupéré dans les téléchargements !

Merci déjà pour ça !!

 

[Monique]

Re,

Pour la suite, c'est la fonction que tu avais déjà utilisé pour trouver 1 valeur selon 2 critères :
=INDEX( plage des noms ; EQUIV( critère date & critère heure ; plage des dates & plage des heures ;0))
Sauf que si tu recopies cette formule, elle te donnera toujours le même résultat : le premier.

Une fois le 1er nom trouvé, on décale donc la plage des noms, celle des dates et celles des heures, de façon à éliminer de la plage de recherche la valeur déjà trouvée.
C'est la fonction DECALER () qui fait ça.
Decaler ( nb de lignes ; nb de colonnes ; hauteur ; largeur )
Pour toi, c'est Decaler ( nb de lignes ; 0 colonne )
Et c'est même Decaler ( nb de lignes ; )

Nb de lignes, c'est EQUIV ( valeur déjà trouvée ; plage des noms ; 0 )
Equiv te donne le n° de ligne à l'intérieur de la plage des noms.
Le 1er nom de la liste : Equiv ( "zaza" ; plage des noms ; 0 ) = 1
Le 2ème nom de la liste : Equiv ( "zizi" ; plage des noms ; 0 ) = 2

La formule Decaler ( plage des noms ; Equiv ( "zaza" ; plage des noms ; 0 ) ; 0 )
revient à ceci : Decaler ( plage des noms ; 1 ligne ; 0 colonne )
et la formule ne cherche plus vraiment dans la plage "Nom", de B10 à B15,
mais elle cherche dans la plage B11:B16 (si Equiv () = 1, en gardant l'exemple de ton fichier)

Du coup, la formule est longue, parce qu'on décale 3 plages.

Sinon, tu as 2 calculs d'ancienneté dans le fichier "Dates et Heures", un avec un pourcentage de temps (de travail ou autre), l'autre sans.

 

[Monique]

Re,

Excuse moi, je n'ai pas vu que JCA06 et Hervé n'étaient pas une seule et unique personne.

 

[JCA06]

Merci Monique,

Je commence à comprendre l'utilité de ces fonctions et la puissance de leur combinaison !

Pour ce qui est des calculs, je pense vraiment ne faire que des copier/coller tellement votre trio respire la maîtrise du temps à travers le travail de titan représenté par le classeur Dates et Heures !

La seule chose que je pourrai faire sera de découper les formules en sous-formules identifiées par des noms, de sorte que ce fichier puisse être repris par la suite par d'autres personnes.

Merci pour tout et pour tous !

 

[JCA06]

Bonjour Monique, le Forum,

Comme je l'ai noté hier, je commence à comprendre !

Cela veut dire aussi que je n'ai pas fini !!

Je reprend ou j'étais largué :

INDEX(DECALER(B$10:B$15;EQUIV(F10;B$10:B$15;0);EQUIV(B$2&C$2;DECALER(C$10:C$15;EQUIV(F10;B$10:B$15;0)&DECALER(D$10$15;EQUIV(F10;B$10:B$15;0);0);0)))

Ce qui me gène, c'est ton premier EQUIV : EQUIV(F10;B$10:B$15;0) dans la mesure ou il recherche la ligne avec le même nom sans s'assurer que les conditions de date et d'heure sont remplies.

Dans ton exemple, si tu remplaces les deux premières lignes (B10:C11) par :
zeze 03/06/2004 10:15
zeze 03/06/2004 09:15
Tu devrais constater que le résultat devient trois fois zeze... sauf si c'est moi qui ai bidouillé les bonnes formules !

Le problème est que là, ça se complique !

A plus.

 

[Monique]

Bonjour,

Oui, tu as raison, le décalage ne doit pas se faire sur la valeur "Equiv" d'une seule donnée,
mais sur le max des Equiv des 3 données.
Pour la ligne 11, pour les 3 arguments nom, date et heure, c'est :
DECALER(Nom;MAX(EQUIV(F10;Nom;0);EQUIV(B$2;Date;0);EQUIV(C$2;Hr;0))
DECALER(Date;MAX(EQUIV(F10;Nom;0);EQUIV(B$2;Date;0);EQUIV(C$2;Hr;0))
DECALER(Hr;MAX(EQUIV(F10;Nom;0);EQUIV(C$2;Hr;0);EQUIV(B$2;Date;0))
Je crois que c'est bon, comme ça.

Le fichier est refait.
Par la même occasion, la 1ère formule est mise au conditionnel,
il y a aussi un format conditionnel mettant en couleur les lignes répondant aux 2 critères.
(nettement plus simple, la formule du format conditionnel)

 

[Hervé]

Bonjour Monique, JCA06, Michel et le Forum,

Encore Merci pour vos explications et le temps consacré à nous désembourber.

Ca marche


Hervé

 

[Monique]

Re,

Oublié fichier...

 

[JCA06]

Bonjour Hervé, Monique, le Forum,

Désolé, mais j'ai l'impression que ce n'est pas encore ça !

Si tu copies B1111 en B1010, tu obtiens 4 fois zeze... a plus zozo !

Mais bon, pour te dire d'où cela vient, il faudrait que je fasse des progrès dans ces fonctions INDEX/EQUIV/DECALER !

Je considère que ces fonctions, avec la fonction SOMMEPROD, représentent une frontière entre les pas mauvais et les très bons en fonctions de feuille excel. Je pense aussi que le pourcentage d'utilisateurs qui jouent avec ne doit pas dépasser 2 %.

C'est paradoxal tout de même : il y a quelques mois j'étais très bon en excel, puis j'ai trouvé ce forum, j'ai fait énormément de progrès, et maintenant je sais que je suis tout juste pas mauvais !

D'après Platon, Socrate, considéré comme le plus sage des sages, a dit : "je sais que je ne sais rien"
Avant de connaitre xld, je pensais en savoir beaucoup...

Je ne désespère pas d'y arriver un jour, mais pour le moment c'est loin d'être le cas. Du coup, j'ai actionné les touches Alt+F11 et je suis passé par vba à base de méthode Autofilter.

On se retrouvera sur les calculs de dates !!

Merci et ... Bonne fête Monique !!

 

[Monique]

Bonjour,

et merci.

C'est pas du jeu, ça.
Si on met des doublons, on n'y arrive pas, avec ce système.
Quoique, dans ton exemple...
Tu copies B1111 en B1010,
tu tries B1015 par ordre décroissant des noms, et tu l'as, ton Zozo
D'accord, là, je triche.

S'il y a des doublons, il faut passer par un compte intermédiaire, sinon, je ne vois pas.
En A10 à copier vers le bas :
=SI((C10=B$2)*(D10=C$2)=0;"";1+MAX(A$9:A9))
Tu nommes "No" cette plage A10:A15
En H10 à copier vers le bas :
=SI(MAX(No)>NBVAL(H$9:H9);INDEX(Nom;EQUIV(LIGNES(H$10:H10);No;0));"")
C'est court, pas matriciel, simple et tout et tout.
Et ça marche avec ou sans doublons.