Position de la dernière ligne selon critère avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Ligne du dernier résultat

Comment repérer et atteindre la dernière ligne remplie correspondant à un critère, c'est ce que propose de démontrer cette nouvelle astuce Excel.



Sur l'exemple finalisé illustré par la capture, nous travaillons sur un tableau relatant les chiffres d'affaires réalisés par les commerciaux d'une entreprise. Ces chiffres ont été archivés au coup par coup, soit date à date. Sur la droite de ce tableau, l'utilisateur peut choisir l'un des commerciaux par le biais d'une liste déroulante. A validation, nous devons être en mesure de calculer la position de sa première et de sa dernière vente. Et dans le même temps, nous devons fournir des liens cliquables permettant d'atteindre ces cellules. Enfin, une mise en forme conditionnelle doit faire ressortir dynamiquement et en couleur ces deux lignes remarquables.

Classeur source et présentation
Pour la démonstration de cette nouvelle astuce Excel, nous proposons de baser nos manipulations sur ce tableau des chiffres d'affaires qu'il s'agit donc de récupérer.

• Télécharger le classeur ligne-dernier-sur-critere.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Vous pouvez noter que les indices de ligne sont repérés en première colonne du tableau. Il s'agit d'une numérotation relative, car elle débute à partir de la première ligne du tableau et non de la feuille. Les trois dernières colonnes du tableau sont nommées. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel. Celui qui nous intéresse représente la plage des vendeurs. Elle est reconnue sous l'intitulé Vendeurs que nous exploiterons dans la construction des formules.

Dernière position du vendeur choisi
Pour trouver le dernier résultat du commercial choisi avec la liste déroulante, nous devons engager un raisonnement matriciel. Il consiste à déterminer quelle est la ligne relative la plus grande dans le tableau, pour le commercial sélectionné. Pour ce faire, les fonctions Max, Si et Ligne sont nécessaires.

• Cliquer sur la cellule G9 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction pour le maximum, suivie d'une parenthèse, soit : Max(,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,

Comme nous le disions, nous cherchons la ligne la plus grande dans le tableau, mais sous condition. Et cette condition dépend du vendeur choisi. Le critère à honorer doit donc être vérifié sur la plage des vendeurs.

• Désigner la plage des commerciaux par son intitulé, soit : Vendeurs,
• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Désigner le commercial choisi en cliquant sur sa cellule G6,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire la fonction pour l'indice de ligne suivie d'une parenthèse, soit : Ligne(,
• Désigner de nouveau la plage des commerciaux par son nom, soit : Vendeurs,
• Fermer la parenthèse de la fonction Ligne,

A ce stade et en respectant la condition émise en amont sur le nom du commercial, nous sommes censés retourner l'indice de ligne le plus grand, soit la dernière position pour le commercial choisi, mais en valeur absolue. Or le tableau débute à partir de la quatrième ligne. Et il pourrait commencer à partir d'un autre indice. Nous devons donc retrancher les lignes en trop, en partant de la première ligne du tableau, soit du plus petit indice.

• Taper le symbole moins (-) pour annoncer le retranchement à suivre,
• Inscrire la fonction du minimum suivie d'une parenthèse, soit : Min(,
• Inscrire la fonction pour l'indice de ligne suivie d'une parenthèse, soit : Ligne(,
• Désigner de nouveau la plage des commerciaux par son nom, soit : Vendeurs,
• Fermer la parenthèse de la fonction Ligne,
• Puis, fermer la parenthèse de la fonction Min,
• Ajouter une unité à ce résultat, soit : +1,

En effet, il s'agit d'annuler la position du début retournée par la fonction Min. Trois lignes existent au-dessus du tableau et on en soustrait 4. Donc avec ce réajustement, on annule les trois lignes situées au-dessus.

• Fermer la parenthèse de la fonction Si,
• Puis, fermer la parenthèse de la fonction Max,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

La ligne relative 47 est annoncée pour le vendeur Hamalibou. Et en atteignant la fin du tableau, vous constatez que ce repérage est parfaitement exact. Bien entendu, si vous changez de commercial à l'aide de la liste déroulante, le calcul de la position s'ajuste en cohérence avec la nouvelle contrainte.

Dans l'enchaînement et par déclinaison, nous pouvons nous soucier de calculer la première position pour le vendeur mentionné. Dans la syntaxe du précédent calcul matriciel, il suffit de remplacer la fonction Max par la fonction Min.

• En cellule G15, adapter la syntaxe de la formule matricielle comme suit :

{=MIN(SI(Vendeurs=G6; LIGNE(Vendeurs) - MIN(LIGNE(Vendeurs)) + 1))}



Naturellement, vous devez veiller à valider cette formule par le raccourci clavier CTRL + MAJ + Entrée puisqu'il s'agit d'un raisonnement matriciel.

Liens cliquables vers les positions
En cellules G12 et G18, il est désormais proposé d'offrir des liens cliquables à l'utilisateur. Ils doivent respectivement permettre de sélectionner la cellule du dernier et du premier résultat pour le commercial choisi. C'est bien sûr la fonction Excel Lien_Hypertexte qui est à l'honneur. Elle doit recomposer les coordonnées de la cellule à atteindre en prélevant l'indice de ligne fourni par le calcul matriciel, tout en lui ajoutant trois unités pour considérer les trois lignes situées au-dessus du tableau. Nous pourrions calculer cet écart dynamiquement comme nous l'avons fait précédemment (MIN(LIGNE(Vendeurs))-1). Concernant l'indice de colonne, il est fixe. Nous proposons de nous baser sur la quatrième, soit la colonne D.

• En cellule G12, construire la syntaxe suivante :

=LIEN_HYPERTEXTE(\"#D\" & G9+3; \"D\" & G9+3 & \" - Atteindre\")

En premier argument de la fonction Lien_Hypertexte, nous passons les coordonnées de la cellule. Ces coordonnées, nous les recomposons avec l'indice fixe de colonne (D) et le numéro de ligne calculé et incrémenté de trois unités. Comme vous le savez, pour pointer en interne, nous devons préfixer ces coordonnées d'un Hashtag à concaténer (\"#D\" &). En deuxième argument de la fonction Lien_Hypertexte, nous passons le texte à afficher en lieu et place du lien. Nous recomposons les coordonnées de la cellule cible accompagnée du texte explicatif Atteindre.

A validation de la formule et au clic sur le lien, vous constatez que le focus est effectivement donné à la dernière opération réalisée par le commercial désigné par la liste déroulante.

Pour créer le lien cliquable permettant d'atteindre le premier résultat du commercial désigné, il suffit de copier la cellule G12 en G18. Comme nous n'avons pas utilisé les références absolues, les coordonnées se reconstruisent en prenant compte de l'indice de ligne calculé en cellule G15.



La syntaxe adaptée naturellement est donc la suivante :

=LIEN_HYPERTEXTE(\"#D\" & G15+3; \"D\" & G15+3 & \" - Atteindre\")

Repérer les lignes en couleur
Pour une solution dynamique plus aboutie, nous proposons de faire ressortir en couleur les lignes repérées par les positions des calculs matriciels. Pour cela, il suffit simplement de faire la correspondance entre les nombres de la première colonne du tableau et les positions extraites G9 et G15.



En effet et malgré les apparences, ce sont bien des nombres qui sont inscrits dans la colonne B. Vous pouvez le constater en sélectionnant l'un d'entre eux et en consultant sa barre de formule.

C'est un format personnalisé qui permet de lui associer une lettre à l'affichage.

• Sélectionner toutes les cellules du tableau, soit la plage de cellules B4:E51,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, opter pour l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Dès lors, taper le symbole égal (=) pour initier la syntaxe de la règle de mise en forme,

Deux conditions peuvent être vérifiées sans être recoupées. Il peut s'agir de la première ligne ou de la dernière ligne pour le commercial désigné. Nous devons donc employer la fonction Excel non exclusive pour énumérer les critères. Il s'agit de la fonction Ou.

• Inscrire la fonction d'énumération non exclusive, suivie d'une parenthèse, soit : Ou(,
• Désigner le premier numéro de ligne en cliquant sur sa cellule B4, ce qui donne : $B$4,
• Enfoncer deux fois la touche F4 du clavier pour libérer la ligne de la cellule, soit : $B4,

Nous le répétons à chaque occasion. L'étude d'une mise en forme conditionnelle est chronologique. Le critère doit toujours être vérifié dans cette colonne des numéros. C'est la raison pour laquelle nous la conservons figée avec le dollar devant l'indice de colonne ($B). Mais, au fil de la progression de l'analyse, ce sont tous les numéros du dessous qui doivent être comparés avec la position repérée par le calcul matriciel. Donc, nous libérons son indice de ligne pour la laisser progresser avec l'étude de la règle.

• Taper le symbole égal (=) pour annoncer le premier critère à honorer,
• Puis, désigner la dernière position calculée en cliquant sur sa cellule G9, soit : $G$9,

Cette fois, nous la conservons complètement figée puisque chaque numéro devra être comparé à la valeur qu'il porte.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Sélectionner de nouveau le premier numéro en cliquant sur sa cellule B4, soit : $B$4,
• Comme précédemment, enfoncer deux fois la touche F4 du clavier, ce qui donne : $B4,
• Taper le symbole égal (=) pour annoncer la seconde condition,
• Sélectionner la première position calculée en cliquant sur sa cellule G15, soit : $G$15,
• Fermer alors la parenthèse de la fonction Ou,

Lorsque l'une ou l'autre condition est vérifiée, en d'autres termes s'il s'agit de la première opération ou de la dernière pour le commercial choisi, la ligne toute entière doit ressortir dynamiquement en couleur.

• Pour cela, cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la seconde liste déroulante, choisir un vert assez vif pour la couleur du texte,
• Valider ce réglage en cliquant sur le bouton Ok,
• De retour sur la première boîte de dialogue, valider la règle de mise en forme par Ok,

Comme vous pouvez le voir, les lignes dont les positions sont identifiées par les calculs matriciels, sont désormais repérées dynamiquement en couleur dans le tableau. Il s'agit d'une méthode efficace pour simplifier l'interprétation des données. Et bien sûr, si vous changez de commercial avec la liste déroulante, les couleurs se déplacent en corrélation avec les positions recalculées.



La syntaxe de la règle de mise en forme conditionnelle que nous avons construite est très simple :

=OU($B4=$G$9; $B4=$G$15)