Créer une fonction avec un champ nommé


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]).

Champs nommé Fonction 01

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).

Champs nommé Fonction 02

 

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).

Champs nommé Fonction 03 

Etape 2

Cela fonctionne très bien mais nous allons utiliser un champ nommé.

RUBAN: Formules > Noms définis > Gestionnaire de noms

Champs nommé Fonction 04

Puis cliquez sur Nouveau.

Champs nommé Fonction 05

Et remplissez la fenêtre comme ci-dessous:

Champs nommé Fonction 06

 

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().

Champs nommé Fonction 07

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)

Champs nommé Fonction 08

 

Etape 5

A présent, essayez d’insérer une nouvelle ligne. Vous pouvez constater que nous n’avons plus de problème:

Champs nommé Fonction 09

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 !