L’astuce ici est de créer une nouvelle « Fonction » grâce aux champs nommés. Il existe par exemple la fonction AUJOURDHUI mais il est très facile de créer ainsi HIER, et sans la moindre macro. Mais en utilisant les champs nommés. Il est également possible de récupérer la valeur de la cellule au-dessus par exemple. C’est ce que nous allons faire et je vais vous montrer quel intérêt nous pouvons y trouver !
L’Intérêt
Dans l’exemple ci-dessous, l’objectif est pour chaque famille, de fournir un n° unique (colonne C). Pour cela, nous créons une formule (que vous pouvez voir en colonne D):
=SI(A3<>[@Nom];1;C3+1)
Cette formule compare le Nom ([@Nom]) avec celui de la ligne supérieure (A3). Si nous avons le même nom de famille, alors on prend le numéro au-dessus (C3) et on ajoute 1. Sinon, on mettra 1 car c’est un nouveau nom. Il faut bien entendu trier par Nom au préalable.
Pour la colonne E, on souhaite compter le nombre de personne de chaque famille. La formule apparaît en colonne F:
=NB.SI([Nom];[@Nom])
Cela correspond à dire: nombre de Noms identiques [@Nom]) dans la colonne Nom ([Nom]).
A présent, nous souhaitons rajouter une ligne (cf. Ligne 5). Vous pouvez constater que la formule reste la même et pointe toujours vers la ligne 4. Du coup, nous avons deux fois le numéro 2 pour la famille SMITH. Bien sûr, il est possible avec une formule de mettre à jour cela pour obtenir le résultat souhaité. Nous le verrons à l’étape 1. Toutefois, il sera encore plus facile d’utiliser un champ nommé pour créer une nouvelle fonction (Étapes 2 à 4).
Comment Faire ?
Afin de bien voir la différence avec ce que nous venons de faire, nous allons garder les deux colonnes C et D correspondant aux numéros et taille chaque famille. Et nous allons rajouter une nouvelle colonne qui nous permettra de récupérer dans un premier temps le contenu de la cellule de la ligne supérieure de la colonne A, correspondant au Nom.
Etape 1
Voici la formule que nous allons utiliser:
=INDIRECT(ADRESSE(LIGNE()-1;1))
Pour bien comprendre la formule, comme pour toute formule Excel, il faut commencer de l’intérieur vers l’extérieur.
LIGNE()-1: renvoie le numéro de la ligne supérieure.
ADRESSE(num_ligne;num_colonne): renvoie l’adresse de type $A$4 avec num_ligne correspondant à la ligne visée (ici la ligne juste au-dessus) et au numéro de colonne (ici 1, c’est à dire la colonne A).
INDIRECT(adresse): renvoie le contenu de l’adresse. Par exemple, si l’adresse est A4, la fonction renverra « SMITH ».
L’idée est de dire que lorsque je vais ajouter une ligne, je vais toujours chercher la ligne juste au-dessus (LIGNE()-1).
Etape 2
Cela fonctionne très bien mais nous allons utiliser un champ nommé.
RUBAN: Formules > Noms définis > Gestionnaire de noms
Puis cliquez sur Nouveau.
Et remplissez la fenêtre comme ci-dessous:
Etape 3
Refaites la même chose avec C_Sup. L’objetif est ici de trouver le contenu de la cellule située juste au-dessus, en colonne C. Ici nous avons mis 3 pour la colonne mais pour être sûr d’utiliser la cellule située juste au-dessus, quelque soit la colonne, nous aurions pu mettre COLONNE().
Etape 4
Nous allons à présent refaire la même formule que dans la partie « L’intérêt » pour définir le numéro (cf. colonne C) mais en remplaçant A3 et C3 par les champs nommés créés.
Avant: =SI(A3<>[@Nom];1;C3+1)
Après: =SI(A_Sup<>[@Nom];1;C_Sup+1)
Etape 5
A présent, essayez d’insérer une nouvelle ligne. Vous pouvez constater que nous n’avons plus de problème:
Conclusion
N’hésitez pas à utiliser les champs nommés pour créer de nouvelles fonctions. Par exemple, créez le champ nommé suivant:
Nom: Hier
Fait référence à: =AUJOURDHUI()-1
Et maintenant vous pouvez utiliser « Hier » comme champ nommé. Il vous retournera TOUJOURS la date de la veille. N’hésitez pas à être créatif et à partager sur le blog !
