http://tssperformance.com/wp-admin/theme-editor.php?file=content-archive-portfolio.php&theme=canvas

Cas un peu compliqué associant les plages nommées, les fonctions INDEX, EQUIV, MAX, INDIRECT…

Bonjour

J’ai longtemps cherché un titre pour résumé ce cas, et vous l’avez compris, j’ai rien trouvé d’intéressant. Juste que c’est un cas assez compliqué que vous pourrez rencontrer. Dans un précédent article (ici) je vous apprenait à faire une « somme sélective », il s’agit ici d’une suite de cas dans laquelle on veut retrouver le meilleur vendeur selon le tableau choisi.

Il s’agit d’un résumé disons simpliste, mais je peut vous le dire, la solution finale sera un peu compliquée. Regardons bien le cas en question:

Le problème:

On veut retrouver le meilleur vendeur des ventes du produit1 ou du produit2, selon le choix qu’on aura fait dans la cellule B10. Dans cette cellule, nous avons une liste déroulante dans laquelle nous choisissons le tableau dans lequel on trouvera le meilleur vendeur.

 

La bonne de solution:

Pour y arriver, il faudra qu’on passe par plusieurs étapes et peut-être plusieurs gorgées de café aussi 🙂 . Déjà il faut noter que nous avons déjà nommé (voir article précédent ici):

  • les plages B5:C8, Produit1
  • les plages E5:F8, Produit2

Il faut comprendre que la difficulté est de trouver le bon tableau et la bonne colonne. Si il fallait simplement trouver le meilleur vendeur du tableau des produit1, on aurait écrit simplement la formule qui suit:

=INDEX(Produit1;EQUIV(MAX($C$5:$C$8);$C$5:$C$8;0);1)

  • la fonction MAX permet de retrouver dans la colonne des montants, le montant le plus élevé;
  • la fonction EQUIV permet de retrouver la position du montant le plus élevé (fonction MAX) dans la colonne des montants. Cette position sera considérée comme le numéro de ligne dans la fonction INDEX
  • la fonction INDEX dans ce cas est utilisé sous forme matriciel et retrouve le nom du meilleur vendeur en faisant l’intersection dans le tableau du Produit1, entre la position du plus grand montant dans la plage C5:C8 (numéro de ligne) et la 1ère colonne (le dernier 1 de la formule).

Nous allons utiliser cette même structure pour résoudre notre problème. En fait non seulement la difficulté est d’automatisé le choix du tableau mais aussi les colonnes correspondantes.

Pour retrouver les éléments nous allons utiliser la fonction INDIRECT et nommer les éléments.

 

Retrouver le tableau choisi et les colonnes des noms et des montants selon du tableau en question:

Pour retrouvons le nom du tableau choisi:

  • dans le ruban, aller Formules/Noms définis/Définir un nom
  • remplir la boîte de dialogue comme ci-dessous:

Pour retrouver la colonne choisie des noms :

  • dans le ruban, aller Formules/Noms définis/Définir un nom
  • remplir la boîte de dialogue comme ci-dessus:

Pour retrouver la colonne choisie des montants:

  • dans le ruban, aller Formules/Noms définis/Définir un nom
  • remplir la boîte de dialogue comme ci-dessus:

La formule finale (après le café):

=INDEX(Nom_tab_choisi;EQUIV(MAX(Montant_tab_choisi);Montant_tab_choisi;0))

Bon pour finir, un petit café serait le bienvenu.

Evidemment si vous avez d’autres idées pour résoudre ce cas, n’hésitez pas à partager avec nous.

, , ,

No comments yet.

Laisser un commentaire