Dernière ligne d'un texte ou d'un nombre 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 :


Dernière ligne de texte ou de nombre

Cette nouvelle astuce Excel tient à démontrer comment récupérer l'indice de ligne de la dernière cellule inscrite pour un texte mais aussi pour un nombre.



Dans l'exemple illustré par la capture, du tout-venant est présenté dans une colonne isolée, sur la gauche de la feuille. On y trouve effectivement aussi bien des nombres que du texte ou encore des cellules vides. Et malgré cette absence de cohérence, deux calculs sont capables de définir respectivement quelle est la ligne de la dernière cellule portant un nombre et quelle est la ligne de la dernière cellule portant un texte.

Classeur source
Pour la démonstration de cette nouvelle astuce, nous suggérons de récupérer l'ébauche de ce classeur.

• Télécharger le classeur derniere-ligne-utilisee.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é,

Nous retrouvons bien les informations mélangées dans une première colonne sur la gauche de la feuille. Les calculs pour la dernière ligne doivent respectivement être exercés en cellules E4 et E5. Dans ce cas précis, il n'est pas compliqué d'identifier que le dernier texte est sur la ligne 15 et que le dernier nombre est sur la ligne 16. Mais dans le cas de tableaux plus denses, le problème est tout autre. Et cette astuce fonctionne à merveille quelle que soit la densité d'informations.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,

Vous notez la présence d'une plage nommée. Elle est intitulée liste. Elle fait référence à la colonne du tout-venant, de la cellule C4 à la cellule C30. Nous avons volontairement prévu un peu large pour ajouter des données à titre de tests.

• Cliquer sur le bouton Fermer de la boîte de dialogue pour revenir sur la feuille,

Bien entendu, nous exploiterons ce nom de plage dans la construction des formules.

Dernière ligne numérique
Certaines fonctions usuelles sont capables de s'accommoder des informations parasites. C'est ainsi que la somme, la moyenne ou encore le max et le min peuvent agir sur une plage de cellules tout en faisant abstraction des cases vides ou des données textuelles. Et nous entendons le démontrer.

• En cellule E4, taper et valider la formule suivante : =MAX(Liste),

Comme vous le constatez, malgré ce mélange de données, c'est bien l'information numérique la plus grande qui est extraite. Forts de ce constat, nous sommes tentés de rechercher la position de cette valeur seuil en exploitant la fonction Equiv, mais de façon approximative. En majorant ce seuil et en enclenchant la recherche sur la liste elle-même, c'est la dernière valeur numérique qui sera détectée. Donc nous pourrons prélever la position.

• Sélectionner de nouveau la cellule E4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,

Cette action a pour effet de remplacer l'ancienne syntaxe.

• Inscrire la fonction de recherche de position suivie d'une parenthèse ouvrante, soit : Equiv(,
• Inscrire la fonction pour le maximum suivie d'une parenthèse, soit : Max(,
• Désigner la plage par son nom, soit : Liste,
• Fermer alors la parenthèse de la fonction Max,
• Puis, ajouter une unité pour la majoration, soit : +1,

Nous venons de renseigner l'argument de la valeur cherchée. Comme le max est majoré, cette donnée ne sera jamais trouvée.

• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner de nouveau la colonne par son nom, soit : Liste,
• Fermer la parenthèse de la fonction Equiv,
• Puis, valider le calcul par la touche Entrée du clavier,

La formule répond par le nombre 13. Ce résultat n'est pas cohérent dans la mesure où la dernière information numérique est placée sur la ligne 16. Et pourtant, cette anomalie s'explique fort logiquement. La fonction Equiv, raisonne de façon relative par rapport à la liste sur laquelle elle est appliquée. Elle indique donc qu'il s'agit de la treizième ligne de la plage nommée. En effet, la position 1 est en ligne 4, donc la position 13 (12+1) et en ligne 16 (12+4). Et c'est effectivement la dernière information numérique qui est placée sur la ligne 16.

• En C16, remplacer la valeur 51 par le chiffre 2,

La sentence est la même bien que l'information numérique soit la plus petite de la plage. Et si vous ajoutez un nombre sur l'une des cellules du dessous, sa ligne relative est automatiquement repérée par la formule. Pour corriger ce décalage, il suffit simplement d'ajouter l'indice de la borne inférieure de la liste.

• En cellule E4, adapter la précédente formule comme suit :

=EQUIV(MAX(Liste)+1; Liste) + MIN(LIGNE(Liste)-1)

Grâce à la fonction Ligne, nous sommes en mesure de prélever l'indice de ligne d'une cellule. Comme elle est appliquée à la plage entière (Liste) et qu'elle est imbriquée dans la fonction Min, c'est la première ligne de la plage qui est retournée. Comme celle-ci est déjà intégrée dans le décompte (position 1), nous retranchons une unité pour l'exclure. Et à validation de la formule, vous constatez que nous fournissons l'indice exact de la dernière ligne occupée par une information numérique.

En résumé et pour explication : Nous ne réalisons pas une recherche exacte. En effet, nous n'avons pas renseigné le troisième argument de la fonction Equiv. Dans ces conditions, c'est une recherche approchante qui est entreprise. Comme la valeur n'est jamais trouvée, puisque le maximum est majoré, la recherche approximative s'arrête sur la dernière donnée numérique trouvée. Elle est donc considérée comme la plus proche. Et c'est ainsi que la position de cette dernière valeur numérique est renvoyée.

Si nous avions voulu éviter ce recadrage, il se serait agi de raisonner de façon absolue, soit sur la colonne complète, pour partir de la première ligne et pour une syntaxe plus simple :

=EQUIV(MAX(C:C)+1;C:C)

Dernière ligne textuelle
Finalement, pour trouver le dernier texte dans la colonne, le principe est assez similaire. Cependant, la fonction Max agit uniquement sur des nombres et nous l'avons constaté. L'astuce consiste à rechercher approximativement le texte le plus grand. Et quel terme pourrait être supérieur à une succession de la dernière lettre de l'alphabet, soit de z, comme par exemple : "zzzzz" ? Aucun bien entendu, cela va de soi.

• Cliquer sur la cellule E5 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction de recherche de position suivie d'une parenthèse, soit : Equiv(,
• En guise d'élément cherché, fournir une succession de z, par exemple : "zzzzz",
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner la plage par son nom, soit : Liste,
• Dès lors, fermer la parenthèse de la fonction Equiv,

Comme précédemment, nous ne renseignons pas le troisième argument de cette fonction. En conséquence, nous engageons une recherche approximative.

• Comme précédemment, exploiter les fonctions Min et Ligne pour absorber le décalage :

+ MIN(LIGNE(Liste)-1)

• Enfin, valider la formule par la touche Entrée du clavier,

La réponse est la ligne 15 et effectivement, le dernier texte est placé sur la quinzième ligne de la feuille dans cette colonne. La syntaxe complète de la formule que nous avons construite est la suivante :

=EQUIV("zzzzz"; Liste) + MIN(LIGNE(Liste)-1)



Bien sûr, si vous supprimez le dernier texte, la position calculée remonte pour s'ajuster. Et si vous ajoutez une information textuelle sur l'une des cellules du dessous, le résultat s'adapte pour aller chercher ce dernier indice.

Là encore, nous pouvons simplifier la syntaxe en raisonnant de façon absolue, soit sur la colonne complète : =EQUIV("zzzzz"; C:C).

Et bien sûr après quelques essais, en supprimant le dernier texte ou en l'ajoutant plus bas dans la colonne, vous constatez que le résultat du calcul s'ajuste en parfaite cohérence.

Repérer les dernières lignes
Pour parfaire la solution, nous proposons de repérer ces dernières lignes dans des couleurs dynamiques. Il est donc question de bâtir deux règles de mise en forme conditionnelle. Les règles sont simples. Il s'agit d'établir la correspondance entre la ligne de la cellule et le résultat renvoyé par le calcul. Lorsque la concordance est avérée, la cellule doit se parer d'une couleur destinée à attirer l'attention de l'utilisateur.

• Sélectionner la plage de cellules C4:C30,,

De cette manière, nous désignons toutes les cellules de la plage nommée Liste.

• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Dans la zone de saisie du dessous, bâtir la syntaxe suivante : =LIGNE(C4)=$E$4,

Nous débutons l'analyse à partir de la première cellule de la plage, soit C4. Comme cette analyse est chronologique, nous ne la figeons pas pour que toutes les cellules du dessous puissent être étudiées et comparées à la donnée de référence, placée en cellule E4. Comme ce résultat sur l'indice de la dernière ligne numérique doit toujours être utilisé dans la comparaison, au gré de la progression de l'analyse, nous conservons sa cellule totalement figée, en atteste la présence des dollars.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• A l'aide de la seconde liste déroulante, choisir un vert assez vif pour la couleur du texte,
• Valider cet attribut de format par le bouton Ok,
• Puis, valider la création de la règle de mise en forme de nouveau par le bouton Ok,

De retour sur la feuille, vous constatez que le dernier nombre surgit effectivement. Nous entendons désormais profiter de la sélection toujours active sur la plage pour construire la seconde règle.

• Cliquer de nouveau sur le bouton Mise en forme conditionnelle,
• En bas 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 ...,
• Dans la zone de saisie du dessous, bâtir la syntaxe suivante : =Ligne(C4)=$E$5,

Le principe est strictement identique mais la comparaison est réalisée par le résultat sur l'indice de la dernière cellule de texte.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, choisir un orange assez vif avec la seconde liste déroulante,
• Valider une première fois puis une seconde fois par le bouton Ok,

De retour sur la feuille, vous notez que les deux positions sont désormais sanctionnées visuellement. Cet artifice est non négligeable. Il permet à l'utilisateur de pointer directement sur les éléments cherchés et repérés par les calculs.



Et bien sûr, à chaque modification dans la colonne, en même temps que les calculs de repérage s'actualisent, les couleurs sanctionnant les positions s'ajustent automatiquement.