Utilisation de GRANDE.VALEUR et EQUIV, formules matricielles
Rechercher la meilleure note de chaque individu de la liste
sélectionner W3:W7, inscrire dans la barre de formule :
=INDEX($B$1:$B$9;GRANDE.VALEUR(SI(EQUIV($B3:$B7;$B3:$B7;0)=LIGNE()-2;LIGNE($B3:$B7);0);LIGNE($B3:$B7)-2))
valider la formule par CTRL+MAJ+Entrée (formule matricielle)
En X3, inscrire dans la barre de formules :
=MAX(($C$3:$C$7)*($B$3:$B$7=W3))
valider la formule par CTRL+MAJ+Entrée (formule matricielle)
recopier vers le bas jusqu'en X7
=EQUIV($B3;$B$3:$B$7;0) va donner la position de truc (texte de B3) dans la matrice B3:B7
en fait, la formule donne la position du premier "truc" s'il y en a plusieurs
la formule matricielle sur 6 lignes {=EQUIV($B3:$B7;$B3:$B7;0)} donne donc la position des noms inscrits en B3:B7 dans la liste B3:B7. S'il n'y avait pas de doublons, elle renverrait {1;2;3;4;5;6}.
Dans le cas précis elle renvoie {1;2;1;1;5} puisque EQUIV("truc";$B$3:$B$7;0) renvoie toujours 1.
Les numéros qui sont différents de {1;2;3;4;5;6} permettent donc de repérer les doublons.
Les doublons sont définis par la condition =SI(EQUIV($B3:$B7;$B3:$B7;0)=LIGNE()-2;…..
LIGNE()-2 correspond en effet à {1;2;3;4;5;6} puisque la liste démarre en ligne 3
La matrice {=SI(EQUIV($B3:$B7;$B3:$B7;0)=LIGNE()-2;LIGNE($B3:$B7);0)} va donc renvoyer les numéros de ligne des noms qui ne sont pas en doublon, et 0 pour les doublons.
Pour classer les doublons en dernier, on utilise GRANDE.VALEUR(liste;n) qui donne la ènième valeur d'une liste.
La matrice (sur 5 lignes) {=GRANDE.VALEUR(SI(EQUIV($B3:$B7;$B3:$B7;0)=LIGNE()-2;LIGNE($B3:$B7);0);LIGNE($B3:$B7)-2)} renvoie les numéros de ligne des noms qui ne sont pas en doublons.
La fonction INDEX permet de retrouver les noms dans la liste
{=INDEX($B$1:$B$9;GRANDE.VALEUR(SI(EQUIV($B3:$B7;$B3:$B7;0)=LIGNE()-2;LIGNE($B3:$B7);0);LIGNE($B3:$B7)-2))}
reste à partir des noms, à trouver la meilleure note, ce que fait en X3 la formule matricielle :
{=MAX(($C$3:$C$7)*($B$3:$B$7=W3))}
Avec des formules un peu plus compliquées, on peut éliminer les zéros en W6:X7 :
En W3:W7, formule matricielle
{=SI(ESTERREUR(INDEX($B$3:$B$7;PETITE.VALEUR(SI(EQUIV($B$3:$B$7;$B$3:$B$7;0)=LIGNE(INDIRE
CT("1:"&LIGNES($B$3:$B$7)));EQUIV(B3:B7;B3:B7;0);"");LIGNE(INDIRECT("1:"&LIGNES($
B$3:$B$7))))));"";INDEX($B$3:$B$7;PETITE.VALEUR(SI(EQUIV($B$3:$B$7;$B$3:$B$7;0)=LIGNE(IND
IRECT("1:"&LIGNES($B$3:$B$7)));EQUIV(B3:B7;B3:B7;0);"");LIGNE(INDIRECT("1:"&LIGNE
S($B$3:$B$7))))))}
En X3, formule matricielle {=SI(W3<>"";MAX(($C$3:$C$7)*($B$3:$B$7=W3));"")} à recopier vers le bas.
Remarque : encore un cas où l'utilisation d'un filtre est plus rapide
Comment faire en sorte que le graphique se mette à jour quand on ajoute une ligne ?
Par exemple pour un graphique historique de cours de bourse, quand on ajoute une donnée (le dernier cours de clôture par exemple).
De multiples solutions sont possibles :
- modifier manuellement la plage source à chaque nouvelle saisie
- copier les nouveaux points de données dans le graphique
- faire un graphique plus grand d'une ligne que nécessaire.
- utiliser un graphique en nuage de points
- utiliser une plage nommée variable comme zone de données source
Graphique / Données Source / Plage de données ou série,
Modifier la plage de données en l'allongeant.
Sélectionner les nouvelles valeurs (valeurs et étiquettes ou abscisses), Edition / copier
Sélectionner le graphique / Edition / collage spécial / Ajouter les cellules comme nouveaux points
Avant d'ajouter une donnée, insérer une nouvelle ligne entre le tableau et cette dernière ligne qui reste vierge.
Le tableau de données du graphique, limité par cette ligne vierge, devient donc élastique.
Inconvénient de la méthode, il ne faut pas oublier d'insérer une nouvelle ligne à chaque saisie.
Prévoir une zone de graphique suffisamment large pour permettre l'ajout de nouvelles données
Les cellules vides ne sont pas prises en compte par le graphique en nuage de points.
L'échelle de l'axe des abscisses se règle automatiquement, et la taille de l'axe est donc toujours adaptée au graphique Le graphique grandit avec le tableau
La plage qui sert de données sources s'adapte à la longueur du tableau de données
Pour définir la plage : Insertion / nom / définir /
plagex =DECALER($A$2;;;NBVAL($A:$A);1)
Insertion / nom / définir / plagey =DECALER($B$2;;;NBVAL($A:$A);1).
Utiliser les plages ainsi nommées comme plages source pour le graphique :
Graphique / Données Source / Série / Valeurs =graf.xls!plagey / Etiquettes de l'axe =graf.xls!plagex
(graf.xls est le nom du fichier Excel contenant les données source).
Ajouter des étiquettes sur chaque point d'un graphique
Une lacune d'Excel : Excel ne sait pas afficher automatiquement à coté du point autre chose que la valeur (y) ou la donnée x (étiquette pour les courbes, valeur de l'abscisse pour les nuages de points). Les étiquettes doivent être changées l'une après l'autre manuellement.
Il est impossible de remédier à ce problème sans macro.
Un mauvais palliatif, dans le cas d'un graphique "en courbe", consiste à créer dans le graphique une seconde courbe identique à la première, ce qui permet de créer un axe des abscisses secondaire qui peut porter des étiquettes différentes du premier.
Il suffit de masquer l'un des axes (graphique / options / axes) pour obtenir une courbe avec ses étiquettes.
L'intérêt est tout relatif puisqu'on ne peut pas utiliser cette technique pour un graphe en nuage de points…
