Calculer les coordonnées des dernières cellules 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 :


Coordonnées des dernières cellules

Cette nouvelle astuce Excel tient à démontrer comment il est possible de calculer dynamiquement les coordonnées des dernières cellules dans un tableau évolutif. Il peut s'agir de déceler l'emplacement de la dernière colonne. Mais il peut aussi s'agir de trouver l'emplacement de la dernière valeur numérique ou de la dernière valeur textuelle.



Dans l'exemple illustré par la capture, nous parvenons à afficher le numéro de la dernière colonne, les coordonnées de la toute dernière cellule et respectivement le dernier prix ainsi que le dernier texte. Et bien entendu, ces résultats sont parfaitement dynamiques pour s'adapter aux potentiels ajouts de données, aussi bien en ligne qu'en colonne.

Classeur source
Cette étude requiert de récupérer un tableau de données pour focaliser l'attention sur les techniques de calcul.

• Télécharger le classeur dernieres-cellules.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é,

Comme vous pouvez le voir, ce classeur est composé de deux feuilles. La seconde feuille nommée Compl. propose des données supplémentaires que nous exploiterons pour vérifier et valider le dynamisme de nos calculs. Et précisément, nous partons du principe que le tableau de la première feuille n'est pas figé ni en nombre de lignes ni en nombre de colonnes. Et pour le constater, il suffit de consulter la syntaxe attachée à la plage nommée désignant le tableau.

• 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,

Une boîte de dialogue s'affiche et indique que le tableau de données est reconnu sous l'intitulé tab. Sa syntaxe est la suivante :

=DECALER(Parc!$B$4;;; NBVAL(Parc!$B:$B)-2; NBVAL(Parc!$3:$3)-1)

La fonction Excel Decaler est à l'honneur pour faire varier les bornes du tableau aussi bien en largeur qu'en hauteur tout en considérant le point de départ en cellule B4.

• Fermer le gestionnaire de noms,

Nous sommes ainsi de retour sur la première feuille.

Détecter la dernière colonne
Pour calculer l'indice de la dernière colonne du tableau, nous devons déjà être en mesure de connaître l'indice de la première colonne. Nous partons du principe que le tableau ne débute pas forcément en colonne B. Pour trouver le plus petit indice, nous pouvons exploiter la fonction Min. Pour le plus grand, nous pouvons exploiter la fonction Max.

• Cliquer sur G4 pour sélectionner l'indice de colonne à calculer,
• Taper le symbole égal (=) pour initier la syntaxe du calcul,
• Inscrire la fonction pour la plus petite valeur suivie d'une parenthèse, soit : Min(,
• Inscrire la fonction donnant l'indice de colonne, suivie d'une parenthèse, soit : Colonne(,
• Désigner le tableau aux bornes dynamiques par son nom, soit : Tab,
• Fermer la parenthèse de la fonction Colonne puis fermer la parenthèse de la fonction Min,

A ce stade du calcul, nous sommes censés obtenir l'indice le plus petit, soit la position de la première colonne du tableau. Nous devons lui ajouter le nombre de colonnes pour atteindre la dernière d'entre elles.

• Taper le symbole plus (+) pour annoncer l'addition à suivre,
• Inscrire la fonction comptabilisant les colonnes, suivie d'une parenthèse, soit : Colonnes(,

Attention, il s'agit du pluriel de la précédente fonction Colonne. Son calcul n'est pas le même.

• Désigner de nouveau le tableau dynamique par son nom, soit : Tab,
• Fermer la parenthèse de la fonction Colonnes,

Le calcul n'est pas terminé. Nous devons retrancher une unité à ce résultat. Comme la première colonne est déjà incluse dans le décompte, l'addition (2+3=5) conduit une rangée trop loin.

• Retrancher une unité, soit : -1,
• Puis, valider la formule avec la touche Entrée du clavier,

En réponse, nous obtenons l'indice 4 qui correspond effectivement à la colonne D, soit la dernière colonne du tableau. Nous vérifierons plus tard le bon dynamisme de ce calcul. Avant de poursuivre, il peut être intéressant de fournir ce résultat en lettre, soit D pour 4. C'est la fonction Excel Adresse qui permet de recomposer les coordonnées d'une cellule. Mais sur ces coordonnées, nous ne souhaitons conserver que l'indice de colonne. Il suffit donc de l'indiquer dans la fonction Gauche pour prélever le premier caractère des coordonnées calculées, soit uniquement la lettre.

• En cellule G4, adapter le calcul comme suit :

=MIN(COLONNE(tab)) + COLONNES(tab)-1   & " soit " & GAUCHE(ADRESSE(1; MIN(COLONNE(tab)) + COLONNES(tab)-1; 4); 1)

A l'indice numérique précédemment calculé, nous concaténons la lettre correspondante. Pour cela, nous nous basons sur une cellule de la première ligne pour l'indice de la dernière colonne : ADRESSE(1; MIN(COLONNE(tab)) + COLONNES(tab)-1; 4). Imbriquée dans la fonction Gauche, seule persiste la lettre effectivement.

• Valider le calcul,

Comme vous pouvez le voir, la transcription fonctionne et le résultat devient plus évident.

Détecter la dernière cellule
Pour identifier les coordonnées de la toute dernière cellule du tableau, nous devons trouver la dernière ligne et croiser ce renseignement avec celui de la dernière colonne. Dès lors, en transmettant ces informations à la fonction Adresse, nous obtiendrons les coordonnées de cette dernière cellule. Nous savons déjà trouver l'indice de la dernière colonne. Pour la dernière ligne, le principe est identique. Il suffit simplement de remplacer les fonctions Colonne et Colonnes par les fonctions Ligne et Lignes.

• Sélectionner la cellule G7 et taper le symbole égal (=) pour débuter le calcul,
• Inscrire la fonction pour les coordonnées, suivie d'une parenthèse, soit : Adresse(,
• En premier argument, construire le calcul suivant : Ligne(tab) + lignes(tab)-1,

Comme nous le disions, avec la même méthode que précédemment, nous passons l'indice de la dernière ligne en premier argument de la fonction Adresse. Nous devons maintenant lui passer l'indice de la dernière colonne.

• Taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne,
• Puis, construire le calcul suivant : Colonne(tab) + Colonnes(tab)-1,
• Fermer alors la parenthèse de la fonction Adresse et valider le calcul avec la touche Entrée,

Le résultat renvoie la cellule D19 et il s'agit bien des coordonnées de la toute dernière cellule du tableau. Comme vous le constatez, les coordonnées sont inscrites en références absolues, en atteste la présence des dollars. Pour obtenir des coordonnées relatives, il suffit simplement de passer la valeur 4 en dernier argument de la fonction Adresse.

=ADRESSE(LIGNE(tab)+LIGNES(tab)-1; COLONNE(tab)+COLONNES(tab)-1; 4)

L'emploi de la fonction Min n'est donc finalement pas nécessaire. Il est intéressant de constater sur le tableau désigné, que les fonctions ligne et Colonne pointent naturellement sur la première rangée.



Détecter le dernier nombre
Désormais, l'enjeu est dedéceler les coordonnées de la cellule portant le dernier prix. Nous pourrions envisager de décliner les techniques précédentes. Mais dans le cas du repérage d'une valeur numérique ou d'un texte, il existe une astuce encore plus simple de mise en oeuvre. Pour les prix, il suffit de faire une recherche approchante sur un BigData. En passant à la fonction Equiv une valeur numérique forcément beaucoup plus grande que toutes les autres, elle retournera la dernière valeur trouvée dans le tableau.

• Sélectionner la cellule G10 et taper le symbole égal (=) pour démarrer la formule,
• Inscrire la lettre D entre guillemets, soit : "D",

Nous savons en effet que tous les prix sont placés dans cette colonne. Nous disposons donc du premier indice pour procéder à la construction des coordonnées.

• Ajouter le symbole de concaténation (&) pour poursuivre la construction,
• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• En guise d'élément cherché, lui transmettre un très grand nombre, comme : 1000000,

Cette valeur ne sera jamais atteinte et donc jamais trouvée. La fonction Equiv, en mode approximatif, n'aura d'autre choix que de se rabattre sur la dernière donnée numérique parcourue dans la colonne de recherche.

• Taper un point-virgule (;) pour passer dans l'argument de la rangée de recherche,
• Désigner intégralement la colonne D en saisissant ses références, soit : D:D,
• Taper un point-virgule (;) pour passer dans l'argument du mode de recherche,
• Inscrire le chiffre 1,

Comme l'indique l'info-bulle, en cas de recherche infructueuse, c'est la dernière donnée parcourue directement inférieure qui sera sélectionnée.

• Fermer la parenthèse de la fonction Equiv,
• Puis, valider le calcul par la touche Entrée du clavier,

Le résultat retourné est pour l'instant strictement identique à celui du précédent calcul. En effet, le dernier prix est aussi la dernière cellule du tableau.



Mais nous le verrons, ces résultats évolueront lorsque nous choisirons d'ajouter des colonnes et des lignes au tableau.

Détecter le dernier texte
Nous souhaitons maintenant connaître les coordonnées du dernier modèle. Nous connaissons sa colonne. Il s'agit de la colonne C. Sa ligne doit être calculée à plus forte raison que de nouveaux modèles sont susceptibles d'être ajoutés. La technique est finalement similaire à la précédente. Nous devons enclencher la fonction Equiv sur du BigData, en d'autres termes sur un texte qui sera toujours plus grand que les autres. Par recherche approchante, nous retournerons la dernière cellule inscrite dans la colonne, soit le dernier modèle.

• Sélectionner la cellule G13 et taper le symbole égal (=) pour initier la formule,
• Taper la lettre de la colonne entre guillemets, soit : "C",
• Ajouter le symbole de concaténation (&) pour poursuivre la construction,
• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• En guise d'élément cherché, fournir un texte plus grand que les autres : "zzzzz",
• Taper un point-virgule (;) pour passer dans l'argument de la rangée de recherche,
• Saisir les coordonnées de la colonne C complète, soit : C:C,
• Taper un point-virgule (;) pour passer dans l'argument du mode de recherche,
• Comme précédemment, taper le chiffre 1 pour une recherche approximative,
• Fermer la parenthèse de la fonction Equiv,
• Puis, valider la formule à l'aide de la touche Entrée du clavier,

Nous obtenons bien la cellule C19 qui est la dernière cellule du tableau à porter un modèle. Il est temps désormais de vérifier que toutes nos opérations s'adaptent à des contextes évolutifs.

• En bas de la fenêtre Excel, cliquer sur l'onglet Compl. Pour activer sa feuille,
• En colonne G, sélectionner toutes les années, soit la plage de cellules G3:G19,
• La copier avec le raccourci clavier CTRL + C,
• Revenir sur la feuille Parc et sélectionner la cellule E3,
• Coller la précédente sélection à l'aide du raccourci clavier CTRL + V,

Instantanément, les deux premiers calculs s'actualisent. La dernière colonne devient la cinquième et la dernière cellule devient la E19.

• Revenir sur la feuille Compl.,
• Sélectionner les données supplémentaires, soit la plage de cellules B3:E6,
• Copier ces informations (CTRL +C),
• Revenir sur la feuille Parc et sélectionner la cellule B20,
• Coller ces informations à l'aide du raccourci clavier CTRL + V,

Cette fois, ce sont les trois derniers résultats qui s'actualisent parfaitement, celui de la dernière cellule, du dernier prix et du dernier modèle.