Préambule

Beaucoup de personnes utilises des formules. Bien souvent elles ne fonctionnent pas comme elles le souhaiteraient.

Pourquoi ?

Car les références utilisées dans les cellules sont erronées.

Pour assurer le bon fonctionnement des formiules, Il est indispensable de maîtriser le référencement des cellules.

Qu'est-ce qu'une référence ?

 

Les références de cellules sont en fait les adresses des cellules. Ces adresses sont indispensables pour que excel sache où aller chercher les informations.

Une formule peut avoir besoin d'un ou plusieurs "arguments" ( donnée d'entrée) pour effectuer un calcul. Elle aura donc besoin d'une ou plusieurs adresse pour fonctionner.

Dans Excel les adresses prenne la forme d'une lettre suivit d'un nombre. Exemple : A1

La lettre correspond à la colonne et le nombre correspond à la ligne concerné.

Lorsque l'on fait appel à l'adresse A1 on fait référence à la valeur contenu dans la cellule A1.

 

1. Fonctionnement :

1.1 Les différents types de références

 

Si l'on veut utiliser les formules de calcul il est important de bien comprendre comment sont référencer les cellules. Il existe 4 types de références

  • Référence relative
  • Référence absolue
  • Ligne absolue
  • Colonne absolue

 

Nous allons détaillé chacun de ces types de référence. Je vais volontairement expliquer dans le détail le fonctionnement du référencement des cellules car cette base est indispensable pour utiliser efficacement excel.

 

  • Référence relative : La référence de la cellule est relative. Si l'on fait un copier/coller d'une formule contenant cette référence ( Par exemple A1 ) alors la référence de la formule s'ajustera en fonction du nouvel emplacement.

EXEMPLE

1. Entrer dans la cellule A1 le texte comme indiquer ci-dessous :

 

 

2. Utiliser dans la cellule A2 la formule MAJUSULE qui a pour fonction de transformer le texte un texte minuscule (ici le contenu de la cellule A1 : Les chanteurs) en texte en majuscule.

La référence utilisé dans la formule majuscule doit être "A1"

Ensuite appuyer sur entrée. Le résultat obtenu doit être le suivant :.

 

3. Inscriver dans la cellule A2 le texte suivant :

 

4.Sélectionner et copier la cellule B1

 

 

4.Coller la formule dans la cellule B2

 

 

 

Le résultat obtenu est le suivant :

 

Quelle remarques pouvons nous faire ?

En faisant un copier/coller nous n'obtenons pas dans la cellule B2 le meme contenu que celui de la cellule B1.
Pourquoi ? --> Car seule la formule a été copié telle quelle. La référence dans la formule, elle , a changé. On peut le constater en double cliquant dans la cellule B2, alors on s'apercevra que la référence n'est plus A1 mais A2.

 

 

 

  • Référence absolue : Si l'on fait un copier/coller d'une formule contenant cette référence ( Par exemple $A$1 ) alors la référence de la formulene changera pas.Le symbole dollars "$" permet de transformer les références relative en référence absolue.

 


     

Ainsi si l'on répète le copier/coller comme dans l'exemple ci-dessus alors le résultat sera le suivant :

 

 

On remarque que la référence est la même dans la cellule B1 et B2. Dans les deux cas la formule "=MAJUSCULE($A$1)" utilise la référence "$A$1"

 

 

Astuce : Lorsque vous avez saisi le code suivant : "=MAJUSCULE(A1), en appuyant sur la touche F4 vous obtiendrez automatiquement le résultat suivant "=MAJUSCULE($A$1)"

 

  • Ligne absolue : Dans ce cas la référence est partielment absolue, seule la ligne est figée. La colonne reste relative au nouvel emplacement. Une référence avec ligne absolue s'écrit de la façon suivante "A$1"
  • Colonne absolue : Dans ce cas la référence est partielment absolue, seule la colonne est figée. La ligne reste relative au nouvel emplacement. Une référence avec colonne absolue s'écrit de la façon suivante "$A1"

Note : Vous remarquerez que le signe dollars se place juste en avant ce qui doit resté figé. Avant le"1" si l'on veut que la référence reste figé sur la ligne "1". Juste avant le "A" si l'on veut que la référence reste figée sur la colonne "A". Et devant la référence de ligne et de colonne si l'on veut qu'il y est une référence absolue sur une cellule.

 

Astuce :

  • En appuyant 1 fois sur la touche F4 vous obtiendrez une référence absolue
  • En appuyant 2 fois sur la touche F4 vous obtiendrez une ligne absolue
  • En appuyant 3 fois sur la touche F4 vous obtiendrez une colonne absolue

 

1.2 Utiliser des noms

 

Excel offre la possibilité de nommer différents éléments. Par exemple vous pouvez nommer une cellule. Il procéder de la façon suivante :

 

1. Sélectionner la cellule à nommer:

 

2. Ensuite vous devez cliquer sur le bouton "Définir un nom" de l’onglet "Formule":

 

3. Indiquer le nom que vous souhaitez attribuer à la cellule "B2"

 

Une autre solution consiste à taper directement le nom dans la zone nom. (liste déroulante à gauche de la barre formule)

 

L'utilisation des noms permet d'avoir des repères plus parlant qu'une référence de cellule tel que "$B$2".

Le nom "resultat" pourra ensuite avantageusement être utilisé dans le contexte suivant :

 

Cela équivaut à la formule suivante, avec l'avantage d'être beaucoup plus expicite.

 

Dans les 2 cas le résultat est le suivant :

 

1.3 Création automatique de zones noms

Excel permet de créer automatiquement des zones nom à partir des intitulés d'un tableau

 

 

 

Vous remarquez que la plage de cellules correspondant à la ligne hiver est nommé "Hiver"

Il en sera de même pourla plage de cellules de la colonne min qui sera nommé "min"

 

 

 

La somme des température minimum sera obtenu par la formule suivante :

 

Le résultats suivant s'affiche :

 

1.4 Les intersection des noms

 

Il est possible de faire référence à l'intersection de 2 plages en utilisant l'opérateur intersection. L'opérateur intersection est en fait le caractère "espace".

 

Le résultat affiché est effectivement la valeur "12".

 

 

 

>