Ligne et colonne de la dernière cellule non vide

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


Sujets que vous pourriez aussi aimer :


Coordonnées des cellules

Il est parfois utile de connaître les coordonnées des cellules portant des valeurs remarquables ou placées elles-mêmes dans des situations remarquables. Ces coordonnées permettent alors de les désigner dynamiquement pour les incorporer dans d'autres traitements. C'est le cas que nous proposons d'aborder ici.



Dans l'exemple illustré par la capture, nous repérons dynamiquement la dernière valeur saisie dans le tableau. Il s'agit de la donnée non vide située la plus à droite et ce, quelle que soit la ligne. Dans un tableau de synthèse sur la droite, nous récupérons par calculs ses indices de colonne et de ligne. Nous en déduisons ses coordonnées. Et bien sûr, si une nouvelle valeur venait à être inscrite encore plus loin, tous les repérages et calculs se mettraient à jour. Nous en profitons aussi pour livrer les coordonnées des valeurs seuilles, celles du maximum et du minimum.

Source et présentation
Pour ces travaux, nous suggérons de récupérer tout d'abord ce tableau de saisie.

• Télécharger le classeur coordonnees-cellule-max.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é,

Sur le tableau de saisie, vous notez la présence d'une mise en forme conditionnelle. La plus grande valeur est repérée en vert tandis que la plus petite est repérée en rouge. Sur la droite du tableau, les coordonnées de certaines cellules remarquables sont attendues. Il s'agit tout d'abord de déceler la position en colonne et en ligne de la dernière valeur inscrite dans le tableau. Il s'agit donc de la valeur située dans la colonne la plus à droite. Ces résultats sont attendus respectivement en cellules P7 et Q7. En-dessous, nous devons identifier ces positions pour les valeurs seuilles, soit la plus grande et la plus petite. Ces indications viendront recouper le repérage exercé par la mise en forme conditionnelle.

En déployant la zone Nom en haut à gauche de la feuille Excel, vous remarquez que les données du tableau sont reconnues sous l'intitulé Tab. Nous exploiterons ce nom dans la construction des formules.

Extrémités des colonnes
Une fois n'est pas coutume, nous proposons de débuter par le repérage dynamique. Chaque ligne du tableau doit être surlignée jusqu'à sa dernière valeur inscrite. Ce repérage viendra appuyer les calculs à suivre. Il s'agit de connaître l'indice de colonne le plus grand. Donc cette règle nécessite l'emploi des fonction Max et Colonne. Ce repérage est soumis à condition. Cette colonne doit nécessairement porter une valeur. Donc la fonction conditionnelle Si doit être appelée.

• Sélectionner toutes les cellules du tableau, soit la plage C6:N15,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir la commande 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,
• Taper le symbole égal (=) pour initier la syntaxe de la règle,
• Inscrire la fonction pour la position en colonne, suivie d'une parenthèse, soit : Colonne(,
• Désigner la première vente du tableau en cliquant sur sa cellule C6,
• Enfoncer trois fois la touche F4 du clavier pour libérer complètement la cellule,

Vous le savez, une mise en forme conditionnelle raisonne de façon chronologique. De la sorte, toutes les cellules du tableau vont être passées en revue tour à tour. L'objectif pour chaque ligne est de déceler la valeur inscrite située le plus à droite.

• Fermer la parenthèse de la fonction Colonne,
• Puis, taper l'inégalité suivante : <=,
• Inscrire la fonction du maximum suivie d'une parenthèse, soit : Max(,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner l'intégralité de la première ligne du tableau, ce qui donne : $C$6:$N$6,
• Enfoncer deux fois la touche F4 du clavier pour la libérer en ligne, soit : $C6:$N6,

De cette manière, chaque cellule du tableau est comparée à une valeur de sa propre ligne. L'objectif est de confronter les positions en colonne dès lors qu'un contenu existe. Vous l'avez compris, en raisonnant sur des plages entières, nous engageons un traitement semblable à ceux des calculs matriciels.

• De fait, taper l'inégalité suivante : <>'',
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire la fonction Colonne suivie d'une parenthèse, soit : Colonne(,
• Désigner de nouveau la première ligne du tableau, ce qui donne : $C$6:$N$6,
• Enfoncer deux fois la touche F4 du clavier pour la libérer en ligne, soit : $C6:$N6,

En résumé donc, tant que l'indice de colonne pour la cellule en cours d'analyse est inférieur ou égal à l'indice de colonne le plus grand et portant une valeur, nous devons faire réagir les attributs de format. Mais avant cela, nous devons achever la syntaxe de la règle.

• Fermer la parenthèse de la fonction Colonne,
• Fermer la parenthèse de la fonction Si,
• Fermer la parenthèse de la fonction Max,
• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un jaune pâle,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un jaune foncé pour la couleur du texte,
• Valider ces attributs de format avec le bouton Ok,
• De retour sur la première boîte de dialogue, valider la règle avec le bouton Ok,

Comme vous pouvez le voir, chaque ligne est surlignée jusqu'à son extrémité portant effectivement une valeur.



Et si vous ajoutez une donnée dans une colonne située plus à droite, la mise en forme progresse pour rejoindre ce nouveau point de référence. Nous avons donc réussi à repérer les extrémités de colonnes pour chaque ligne.

La syntaxe complète de la règle de mise en forme conditionnelle est la suivante :

=COLONNE(C6)<=MAX(SI($C6:$N6<>''; COLONNE($C6:$N6)))

Coordonnées de la dernière cellule
Dans le cas illustré par la capture ci-dessus, nous devons trouver les positions en ligne et en colonne de la valeur 445. C'est elle qui est située le plus à droite. L'indice de colonne n'est pas un souci. Dans un raisonnement matriciel, il suffit de répliquer la technique précédente exploitant les fonctions Max, Si et Colonne. L'indice de ligne est plus problématique et nous le verrons.

• Sélectionner la cellule P7 et taper le symbole égal (=) pour initier la formule matricielle,
• Inscrire la fonction Max suivie d'une parenthèse, soit : Max(,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner l'intégralité du tableau par son nom, soit : Tab,
• Puis, taper l'inégalité suivante : <>'',

Dans ce processus matriciel, toutes les cellules désignées vont être analysées à la recherche de celles qui ne sont pas vides. Comme ce critère est imbriqué dans la fonction Max, nous n'avons plus qu'à ressortir l'indice de colonne de chaque cellule répondant au cas. Il en résultera l'indice le plus grand, soit la position de la donnée placée le plus à droite.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire la fonction Colonne suivie d'une parenthèse, soit : Colonne(,
• Désigner tout le tableau par son nom, soit : Tab,
• Fermer la parenthèse de la fonction Colonne,
• Fermer la parenthèse de la fonction Si,
• Fermer la parenthèse de la fonction Max,
• Enfin, valider nécessairement cette formule matricielle par le raccourci CTRL + MAJ + Entrée,

Dans notre cas, le résultat renvoyé est la valeur 12. Il s'agit bien de la position en colonne L de la dernière donnée que nous avons inscrite à titre d'essai.

La syntaxe de la formule matricielle que nous avons construite est la suivante :

{=MAX(SI(Tab<> ''; COLONNE(Tab)))}

Le même procédé ne peut être entrepris pour déceler l'indice de ligne de cette donnée. La fonction Max renverrait la position de la dernière cellule inscrite le plus bas dans le tableau. L'astuce consiste à déceler la ligne de cette valeur dans la colonne que nous venons de trouver. Mais pour l'exploiter, nous devons la retranscrire en lettre (L). Cette colonne peut être traduite grâce à la fonction Excel Adresse. Le problème est qu'elle requiert un indice de ligne en premier argument. Qu'à cela ne tienne, nous allons lui passer la première ligne et retravailler le résultat de manière à ne garder que la lettre de la colonne. Pour la mise en oeuvre, nous proposons d'exercer ce calcul dans une cellule arbitraire, par exemple S6.

• En cellule S6, inscrire tout d'abord la formule suivante : =ADRESSE(1;P7;4),

Nous passons l'indice de colonne précédemment trouvé (P7) en second argument de la fonction. Le troisième argument ainsi réglé permet de retourner les coordonnées relatives, sans les dollars donc. A validation, le résultat est le suivant dans notre cas : L1. La lettre de la colonne est bien transcrite mais elle est accompagnée de son indice de ligne. Il suffit de tronquer cette chaîne en ne conservant que le premier caractère grâce à la fonction Excel Gauche.

• En S6, adapter le calcul comme suit : =Gauche(ADRESSE(1; P7;4 ); 1) et le valider,

Cette fois, seule la lettre de la colonne persiste. Mais pour trouver la position en ligne, nous devons analyser la colonne complète, soit L:L. Il suffit de concaténer ce résultat avec lui-même.

• En S6, adapter la formule comme suit :

=GAUCHE(ADRESSE(1; P7; 4); 1) & ':' & GAUCHE(ADRESSE(1; P7; 4); 1)

Vous notez la présence des deux points concaténés entre les deux expressions. A validation, nous obtenons bien l'information dynamique de colonne : L:L. Nous devons l'exploiter selon les mêmes techniques que précédemment pour trouver la position en ligne de la donnée la plus à droite, soit en colonne L ici.

• Cliquer sur la cellule P7 du précédent calcul,
• Dans la barre de formule, sélectionner toute la syntaxe,
• La copier avec le raccourci clavier CTRL + C,
• Sortir de la barre de formule à l'aide de la touche Echap du clavier,
• Sélectionner maintenant la cellule Q7 pour l'indice de ligne,
• Dans sa barre de formule, coller (CTRL + V) la syntaxe précédemment copiée,

L'analyse ne porte plus sur le tableau intitulé Tab. Elle doit se faire sur la colonne dont nous avons reconstruit les coordonnées en cellule S6. Mais pour que ces coordonnées pointent effectivement sur la colonne en question, nous devons l'imbriquer dans la fonction Indirect. De plus, nous devons remplacer la fonction Colonne par la fonction Ligne.

• En cellule Q7, adapter la syntaxe comme suit :

=MAX(SI(INDIRECT(S6)<>''; LIGNE(INDIRECT(S6))))

• Valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Nous obtenons bien l'indice de ligne de la dernière cellule inscrite dans cette colonne. De fait, en cellule P9, nous pouvons facilement recomposer les coordonnées de cette cellule remarquable, grâce à la fonction Adresse.

• En cellule P9, construire et valider le calcul suivant : =ADRESSE(Q7;P7),

Le résultat fait ressortir la cellule $L$9. Et c'est bien elle qui porte en effet la donnée placée le plus à droite.



Si vous inscrivez une vente dans une cellule du mois de Novembre, les coordonnées de repérage s'actualisent instantanément.

Maintenant que les coordonnées dynamiques de cette cellule remarquable sont parfaitement réceptionnées en P9, nous pouvons les exploiter très simplement dans une règle de mise en forme conditionnelle. Son rôle est de mettre en lumière cette valeur à l'extrémité horizontale.

• Sélectionner toutes les données du tableau, soit la plage de cellules C6:N15,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir la commande Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Cliquer dans la zone du dessous pour l'activer,
• Puis, construire la syntaxe suivante :

=ET(LIGNE(C6)=LIGNE(INDIRECT($P$9)); COLONNE(C6)=COLONNE(INDIRECT($P$9)))

Dès lors que les indices de ligne et de colonne correspondent, nous savons qu'il s'agit de la bonne case. Attention, il faut absolument défiger la cellule analysée (C6). Les dollars ne doivent pas apparaître pour permettre l'analyse chronologique.

• Cliquer sur le bouton Format en bas de la boîte dedialogue,
• Dans l'onglet Remplissage de la boîte qui suit, choisir un orange foncé,
• Dans l'onglet Police, choisir un gris très clair avec la liste déroulante,
• Valider ces attributs de format par le bouton Ok,
• Puis, valider la création de la règle de mise en forme conditionnelle,

Le repérage visuel est parfaitement fonctionnel. Il est bien entendu dynamique. Vous le constatez en réalisant des modifications d'inscriptions.

Enfin, pour trouver les positions des plus grandes et plus petites valeurs, il suffit de décliner la première formule que nous avons utilisée pour la position en colonne.

En cellule P13 : =MAX(SI(Tab=MAX(Tab); COLONNE(Tab)))
En cellule Q13 : =MAX(SI(Tab=MAX(Tab); LIGNE(Tab)))
En cellule P15 : =MAX(SI(Tab=MIN(Tab); COLONNE(Tab)))
En cellule Q15 : =MAX(SI(Tab=MIN(Tab);LIGNE(Tab)))

Chacune de ces quatre formules doit naturellement être validée par le raccourci matriciel CTRL + MAJ + Entrée. Nous exploitons tantôt la fonction Max, tantôt la fonction Min dans l'expression du critère. Cette restriction émise sur le tableau entier permet de libérer les indices concordants grâce aux fonctions Colonne et Ligne. Mais pour qu'ils ressortent avec le calcul, nous englobons le tout dans une autre fonction Max. Un calcul matriciel considère tous les résultats. Les premiers à tomber sont les non concordances, ignorées donc vides. Grâce à la fonction Max chapotant le calcul, nous pointons directement sur la bonne valeur calculée et nous la restituons.