Fonction EQUIV sur plusieurs colonnes (Résolu)

[whiskyfizz]

Bonjour !

Je suis débutant sous Excel, donc je m'excuse d'avance si ma question a déjà été traitée. J'ai fait une recherche sur le forum et je n'ai rien trouvé.

Avant de vous poser ma question, je vous présente mon jeu de données. Il y a 4 colonnes. La première se nomme "numéro de la semaine". Les trois autres représentent trois équipes de football américain et se nomment respectivement "équipe 1", "équipe 2", et "équipe 3". On a donc 212 cases (en comptant les titres des colonnes) puisqu'il y a 52 semaines dans une année. Si on exclue la première colonne et la première ligne, alors chaque case représente le nombre de mètres parcourus d'une équipe pour une semaine donnée. Cette valeur est donc susceptible de changer tous les jours (et c'est d'ailleurs le cas).

Voici mon problème. Je cherche à savoir quelle équipe à parcouru le plus grand nombre de mètres dans l'année et à identifier la semaine pendant laquelle cette valeur a été atteinte. Pour trouver la valeur maximale, il suffit d'utiliser la fonction MAX. C'est pour le reste que je rencontre des difficultés. Il me semble qu'il faut utiliser les fonctions INDEX et EQUIV, mais ces dernières ne fonctionnent que sur une ligne ou une colonne. Il faudrait que je puisse rechercher la valeur maximale dans les trois colonnes avec EQUIV pour avoir sa position relative qui, grâce à INDEX, me permettra de récupérer le numéro de la semaine sur la première colonne et le nom de l'équipe sur la première ligne.

D'avance merci pour votre aide.

Whiskyfizz.

 

[Jocelyn]

Re : Fonction EQUIV sur plusieurs colonnes

Bonjour le Forum,
Bonjour whiskyfizz,

je ne sais pas si je pourrais t'aider, mais un fichier exemple de quelquue ligne de donnée avec le résultat souhaité calculé mabuellement suivant ton exemple pourrait nous aider à t'aider.

a te relire

Cordialement

 

[whiskyfizz]

Re : Fonction EQUIV sur plusieurs colonnes

Voilà, j'ai rajouté un fichier pour illustrer mon problème. Merci.

 

[Jocelyn]

Re : Fonction EQUIV sur plusieurs colonnes

re,

un essai en fichier solution un peu "capilotractée" mais qui a l'air de vouloir fonctionner


EDIT Bonjour jean-Marcel

 

[mapomme]

Re : Fonction EQUIV sur plusieurs colonnes

Bonsoir whiskyfizz, Jocelyn, Jean-Marcel ,

Un essai avec gestion des ex-aequos. Les formules en B10 et B11 sont matricielles. Les copier vers la droite.
B10:

=SI(ESTERREUR(PETITE.VALEUR(SI($B$2:$D$6=$B$9;LIGNE($B$2:$D$6)+COLONNE($B$2:$D$6)/10000;"");COLONNE()-1));"";ENT(PETITE.VALEUR(SI($B$2:$D$6=$B$9;LIGNE($B$2:$D$6)+COLONNE($B$2:$D$6)/10000;"");COLONNE()-1))-1)

B11:

=SI(ESTERREUR(PETITE.VALEUR(SI($B$2:$D$6=$B$9;LIGNE($B$2:$D$6)+COLONNE($B$2:$D$6)/10000;"");COLONNE()-1));"";INDEX($A$1:$D$1;10000*MOD(PETITE.VALEUR(SI($B$2:$D$6=$B$9;LIGNE($B$2:$D$6)+COLONNE($B$2:$D$6)/10000;"");COLONNE()-1);1)))

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.

 

[whiskyfizz]

Re : Fonction EQUIV sur plusieurs colonnes

Jean-Marcel : je n'avais pas du tout pensé à la fonction SOMMEPROD pour ce genre d'usage, donc merci. La formule a en plus l'avantage d'être courte.
Jocelyn : vos formules fonctionnent, mais je vais prendre celle de Jean-Marcel pour le numéro de la semaine. Pour le nom de l'équipe, je voulais savoir s'il n'y aurait pas un équivalent plus matriciel de votre solution (dans la même veine que la solution proposée par Jean-Marcel). En effet, je n'ai pour l'instant que 4 équipes, mais ce nombre va sensiblement augmenter, et en utilisant votre solution, la formule risque de devenir très longue.

Merci pour votre rapidité de réponse.

 

[whiskyfizz]

Re : Fonction EQUIV sur plusieurs colonnes

Bonsoir mapomme.

 

[whiskyfizz]

Re : Fonction EQUIV sur plusieurs colonnes

J'ai testé la solution de Jean-Marcel et elle fonctionne parfaitement pourvu qu'en A2:A6 on ait des chiffres. Toutefois, si on a des lettres, alors on obtient #VALEUR!. En revanche la formule de Jocelyn fonctionne pour des chiffres et des lettres. Je vous demande cela car je compte utiliser cette fonction dans un autre tableau avec une colonne "mois" (qui aura donc 12 lignes). Serait-il donc possible de modifier la solution avec SOMMEPROD pour qu'elle fonctionne avec des lettres ?

 

[whiskyfizz]

Re : Fonction EQUIV sur plusieurs colonnes

Bonsoir !

Je reviens sur ce fil avec un peu de retard pour le fermer. En effet, un autre membre (qui se nomme "OOoiste") de la communauté des utilisateurs d'excel (sur un autre forum) m'a proposé la solution suivante qui améliore la formule de Jean-Marcel :

(.) en B10 :

=INDEX(A1:A6;MAX((B2:D6=MAX(B2:D6))*LIGNE(B2:D6)))

(.) en B11 :

=INDEX(A1:D1;MAX((B2:D6=MAX(B2:D6))*COLONNE(B2:D6)))

Bravo et merci !