Atteindre la dernière cellule d’une feuille avec un lien hypertexte

Cette astuce a un but plus péd­a­gogique que pra­tique.
Il est en effet évi­dent que la solu­tion en VBA est plus pro­pre et sim­ple.
L’autre solu­tion qui con­siste à utilis­er la com­bi­nai­son CTRL + Flèche bas est aus­si plus sim­ple (atten­tion cepen­dant aux arrêt intem­pes­tifs sur les cel­lules vides).

Vue d’ensem­ble des for­mules :

Recherche de la dernière cel­lule vide

Tout d’abord, on se sou­vien­dra des deux for­mules suiv­antes qui per­me­t­tent d’at­tein­dre la dernière cel­lule con­tenant :
— Soit des nom­bres : =EQUIV(E1+307;Feuil1!$A:$A)
— Soit du texte : =EQUIV(“*”;Feuil1!$A:$A;-1)

On notera que si la dernière valeur est une erreur, elle n’est pas con­sid­érée.

En com­bi­nant les deux for­mules, on a ensuite la dernière valeur de la colonne, à con­di­tion que cette colonne con­ti­enne à la fois des nom­bres et du texte :
=MAX(EQUIV(“*”;Feuil1!$A:$A;-1);EQUIV(E1+307;Feuil1!$A:$A))
Sinon, on obtient l’er­reur #N/A

On peut donc gér­er l’er­reur #N/A de la manière suiv­ante :
=MAX(
SI(ESTNA(EQUIV(“*”;Feuil1!$A:$A;-1));0;EQUIV(“*”;Feuil1!$A:$A;-1));
SI(ESTNA(EQUIV(E1+306;Feuil1!$A:$A));0;EQUIV(E1+306;Feuil1!$A:$A)))

Créer la référence en toute let­tre

Il s’ag­it ici de trou­ver la référence de la cel­lule sous la forme A1.
Pour cela, on utilise la fonc­tion ADRESSE :
=ADRESSE(MAX(
SI(ESTNA(EQUIV(“*”;Feuil1!$A:$A;-1));0;EQUIV(“*”;Feuil1!$A:$A;-1));
SI(ESTNA(EQUIV(E1+306;Feuil1!$A:$A));0;EQUIV(E1+306;Feuil1!$A:$A)));1)
qui ren­voit donc la valeur $A$6 dans notre exem­ple.

Créa­tion du lien hyper­texte

On peut finale­ment créer un lien hyper­texte pointant sur A6 à l’aide de la for­mule suiv­ante :
=LIEN_HYPERTEXTE(“#Feuil1!$A$6”;“Pointe sur A6”)

et donc, en reprenant la for­mule précé­dente, nous obtenons :
=LIEN_HYPERTEXTE(
“#Feuil1!” &
ADRESSE(MAX(
SI(ESTNA(EQUIV(“*”;Feuil1!$A:$A;-1));0;EQUIV(“*”;Feuil1!$A:$A;-1));
SI(ESTNA(EQUIV(E1+306;Feuil1!$A:$A));0;EQUIV(E1+306;Feuil1!$A:$A)));1);
“Dernière ligne”)

qui trans­formera la cel­lule en lien hyper­texte.

Un peu de sim­pli­fi­ca­tion …

Là, on peut “sim­pli­fi­er” un peu en util­isant un nom reprenant la posi­tion de la dernière cel­lule.

Pour cela, dans Inser­tion — Nom — Définir, on va créer le nom derniere_ligne et lui attribuer la for­mule suiv­ante :
=”#Feuil1!” &
ADRESSE(MAX(
SI(ESTNA(EQUIV(“*”;Feuil1!$A:$A;-1));0;EQUIV(“*”;Feuil1!$A:$A;-1));
SI(ESTNA(EQUIV(Feuil1!C3+306;Feuil1!$A:$A));0;EQUIV(Feuil1!C3+306;Feuil1!$A:$A)))
;1)

Il ne reste plus qu’à écrire la for­mule suiv­ante :
=LIEN_HYPERTEXTE(derniere_ligne;“Dernière ligne”)

Ces for­mules sont bien sur un peu trop com­plex­es par rap­port au poblème ini­tial, mais elles per­me­t­tent d’ou­vrir un cer­tain nom­bre de pos­si­bil­ités quand à la créa­tion de liens hyper­textes dynamiques et de recherch­es de dernières cel­lules.

Source d’in­spi­ra­tion — entre autres :
AndrewEx­celTip

Gae­tan Mour­mant
http://www.polykromy.com

En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel

Le défi !

Nou­velle rubrique (déjà) pour XLéra­teur

Il s’ag­it de trou­ver des défis sous Excel à résoudre.

Le genre de ques­tions qui vous a pris des jours et des jours et que vous n’avez tou­jours par résolu…Et pour­tant intu­itive­ment, il doit y avoir une solu­tion !!!

On va com­mencer avec un rel­a­tive­ment sim­ple :

En masquant des colonnes (sélec­tion­ner plusieurs colonnes, clic droit dessus et choisir mas­quer), quel est le nom le plus long que l’on puisse écrire avec les colonnes vis­i­bles restantes ?!

Mon record : 4 avec BEBE : B‑E-BE 😉

Premier défi

A vous de jouer !

Gae­tan Mour­mant
PS : Cette idée provient d’un autre blog Anglais.

En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel

Trous de mémoire…

Bon­jour et bonne semaine,

Si comme moi, vous avez une mémoire très très faible (et Google ne fais qu’empirer les choses !), vous aurez besoin de ces quelques for­mules :

Tout d’abord, quel jour sommes-nous ?

Pour une mise à jour à chaque recal­cul : =aujour­d­hui()
Pour une écri­t­ure en “dur” et immuable dans le temps : CTrl + ;

Après, l’autre ques­tion clas­sique :

“Mais, com­bi­en y‑a-t-il de jours au mois de Mai ?”

Soit donc en A1 une date quelque­conque de Mai. Met­tons le 1er Mai, nous pou­vons alors utilis­er la for­mule suiv­ante :
=jour(date(annee(A1) ;mois(A1)+1;0))

Si on recopie A1 vers le bas en cli­quant avec le bou­ton droit de la souris, on obtient ceci :

Il ne reste plus qu’à recopi­er la for­mule B1 pour avoir la cor­re­spon­dance des mois.
On peut aus­si rem­plac­er A1 par aujour­d­hui() pour avoir le nom­bre de jours du mois courant.

Oui, mais Octo­bre, c’est bien le 11ème mois de l’an­née ?!

Si finale­ment, vous n’êtes pas capa­ble de vous sou­venir qu’Oc­to­bre est le 10ème mois de l’an­née (je crois 😉 ), il ne reste plus qu’à trans­former le for­mat de la cel­lule pour affich­er le mois en entier.
Par exem­ple, en choi­sis­sant mmmm aaa (Menu — For­mat — Cel­lule…) :

et donc finale­ment, on a un tableau de ce genre :

Mon autre source per­ma­nente de ques­tion­nement, ce sont les années bisex­tiles !

D’où
=si(mois(date(annee(A1);2;29)) =2;“Année bisextile”;“Année non bisex­tile”)

On résoud le prob­lème en regar­dant si le 29 févri­er “existe” dans Excel.
D’ac­cord, je triche un peu et je laisse Excel régler le prob­lème, mais ca fonc­tionne !

Sur ce, j’e­spère ne rien avoir oublié (!) et vous souhaite un bon lun­di.

Gae­tan Mour­mant
www.polykromy.com

En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel