Découvrez comment déchiffrer n’importe quelle formule Excel en 5 minutes (même les plus complexes) !

Dans ce tutoriel, je vais vous montrer comment écrire des formules Excel claires, dynamiques et lisibles, en utilisant des outils puissants comme les tableaux Excel, les références structurées, les formules multi-lignes et les noms définis.

Le tout sur un cas pratique très simple à comprendre : le suivi de la production d'œufs dans un élevage.

L’objectif est double.

D’un côté, apprendre à structurer une formule proprement, sans perdre la tête dans des références comme C2:C90. Et de l’autre, rendre notre fichier intuitif à utiliser, même plusieurs mois plus tard, sans devoir décoder chaque cellule manuellement.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous enregistrons chaque jour la production d’œufs de trois poulaillers distincts, avec pour chacun une alimentation différente.

Les données sont saisies jour par jour, et à chaque ligne correspond un enregistrement de la production du jour pour un poulailler donné.

»««

Cette base va nous permettre d’exploiter toutes les bonnes pratiques de construction de formules Excel lisibles, puissantes et maintenables.

2. Transformer notre tableau en un vrai tableau Excel structuré

Pour commencer, nous allons transformer ce simple bloc de cellules en un tableau structuré Excel.

Nous commençons par cliquer sur n’importe quelle cellule de notre tableau, puis nous appuyons sur le raccourci [Ctrl]+[L].

Une fenêtre apparaît. Nous vérifions que l’option « Mon tableau comporte des en-têtes » est bien cochée, puis nous cliquons sur « OK ».

Excel transforme alors automatiquement notre plage en un tableau avec un format de mise en forme. Ce tableau possède désormais un nom par défaut, comme « Tableau1 » ou « Tableau2 ».

Pour que nos formules soient plus lisibles, nous allons renommer ce tableau. Pour cela, nous nous rendons dans l’onglet « Création de tableau » (ou « Outils de tableau »), et dans la zone « Nom du tableau », nous écrivons par exemple : « tOeufs ».

Dès à présent, nous allons pouvoir écrire nos formules en utilisant des noms clairs comme «tOeufs[Alimentation]» plutôt que des plages comme « C2:C10 ». C’est beaucoup plus lisible et auto-explicatif.

3. Construire une formule dynamique pour analyser nos données

Nous allons maintenant créer une formule qui permet de calculer automatiquement le nombre total d’œufs produits selon le type d’alimentation choisi par l’utilisateur.

Nous nous plaçons dans la cellule F1, et écrivons le texte : « Alimentation choisie : »

Juste à côté, en G1, nous saisissons une valeur d’exemple, par exemple : « Maïs ».

Ensuite, nous sélectionnons cette cellule G1, et dans la zone de nom (à gauche de la barre de formule), nous écrivons « aliment » puis nous validons avec [Entrée]. Cela permet de créer un nom défini : désormais, « aliment » désigne la cellule G1.

Nous allons maintenant écrire une formule qui utilise ce nom, et les colonnes de notre tableau structuré.

En cellule F3, nous écrivons la formule suivante :

  =SOMME.SI(tOeufs[Alimentation];aliment;tOeufs[Nb_œufs]) 

Décomposons-la ensemble :

• «tOeufs[Alimentation]» : c’est la colonne contenant le critère.
• «aliment» : c’est la valeur à rechercher (dans G1).
• «tOeufs[Nb_œufs]» : c’est la colonne à additionner.

Dès que nous validons, Excel nous renvoie le total des œufs produits avec l’alimentation choisie.

Si nous modifions la cellule G1 avec une autre valeur comme « Blé » ou « Mélange », le total s’adapte automatiquement.

4. Rendre nos formules ultra lisibles avec des astuces avancées

   4.1. Formules sur plusieurs lignes avec [Alt]+[Entrée]

Une formule, ce n’est pas juste une ligne de texte ! Nous avons tout à gagner à l’écrire sur plusieurs lignes pour faciliter la lecture.

Pour cela, nous sélectionnons notre formule dans la barre de formule, et nous insérons des retours à la ligne avec le raccourci [Alt]+[Entrée].

Nous obtenons par exemple :

=SOMME.SI(
    tOeufs[Alimentation];
    aliment;
    tOeufs[Nb_œufs]
) 

Ce petit détail fait toute la différence quand nous revenons sur notre fichier après plusieurs jours ou quand un collègue doit l’utiliser.

   4.2. Utiliser la zone de nom pour gagner en clarté et en rapidité

Lorsque nous écrivons une formule un peu longue, l’un des problèmes les plus courants, c’est la lisibilité des plages de cellules utilisées, surtout si nous n’utilisons pas de tableau structuré comme nous l’avons vu un peu plus tôt.

On voit souvent des formules comme : 

  =SOMME.SI.ENS($D$2:$D$100;$B$2:$B$100;"Maïs") 

 Mais dans 6 mois, allons-nous vraiment nous souvenir que $B$2:$B$100 contient les types d’alimentation, et que $D$2:$D$100 contient les œufs ?

La solution simple, c’est d’utiliser la zone de nom pour nommer non pas une simple cellule, mais une plage complète de plusieurs cellules que nous utilisons souvent.

Prenons un exemple très simple. Nous voulons calculer le total d’œufs pondus, mais uniquement pour les poulaillers B et C.

Nous sélectionnons la colonne contenant les poulaillers (ici B2:B10), puis nous nous rendons dans la zone de nom, juste à gauche de la barre de formule. Nous y tapons « poulaillers », puis nous validons avec [Entrée].

Faisons la même chose pour la colonne des nombres d’œufs (D2:D10), que nous appelons « œufs ».

Désormais, nous pouvons écrire :

=SOMME.SI(poulaillers;"B";œufs)

Cette formule est immédiatement compréhensible, même pour quelqu’un qui ne connaît pas le fichier.

Et surtout, si les données changent de position (par exemple si nous insérons des colonnes), nos noms définis resteront valides.

C’est une excellente habitude à prendre dans tous les fichiers Excel un peu complexes, car elle permet d’écrire des formules qui ressemblent presque à des phrases !

   4.3. Tester une portion de formule avec [F9]

Une autre astuce très pratique consiste à tester une portion d’une formule.

Nous sélectionnons une partie d’une formule, par exemple «tOeufs[Nb_œufs]», puis nous appuyons sur [F9].

Excel affiche directement la liste des valeurs trouvées.

C’est très utile pour comprendre pourquoi un résultat est faux, ou vérifier ce qu’une fonction renvoie.

Une fois la vérification faite, nous appuyons sur [Ctrl]+[Z] pour restaurer la formule.

Mais que faire quand notre formule est vraiment longue, avec plusieurs niveaux de calculs ? C’est là que l’outil « Évaluer la formule » devient indispensable.

Pour l’utiliser, nous nous rendons dans l’onglet « Formules », et nous cliquons sur le bouton « Évaluer la formule » (il est situé vers la droite, dans le groupe « Audit de formules »).

Une fenêtre s’ouvre, nous montrant une partie de la formule avec une sous-ligne indiquant la partie qui va être évaluée.

Nous cliquons sur « Évaluer ». Excel calcule ce morceau, puis nous montre la suite.

À chaque clic sur « Évaluer », nous avançons étape par étape dans l’évaluation de la formule.

Ce débogueur est très utile quand :

• Nous voulons vérifier dans quel ordre Excel interprète les fonctions imbriquées
• Nous voulons voir quelles sont les valeurs réellement prises en compte dans une formule complexe
• Nous voulons comprendre pourquoi une formule retourne une erreur comme #N/A, #VALEUR! ou #REF!

Et cerise sur le gâteau : si une cellule utilisée dans la formule contient elle-même une autre formule, nous pouvons cliquer sur « Examiner » pour entrer dans la formule de cette cellule. Cela nous permet de dérouler l’ensemble du calcul jusqu’au bout.

Une fois que nous avons compris où se situe le problème, nous pouvons fermer la fenêtre, corriger notre formule, puis réessayer.

5. Aller plus loin avec les noms définis

Les noms définis ne servent pas uniquement à nommer des cellules.

Nous pouvons aussi les utiliser pour gérer des constantes dans nos formules.

Reprenons notre cas.

Disons que nous voulons toujours faire le calcul pour « Maïs » sans passer par une cellule dédiée.

Nous allons créer un nom défini.

Dans l’onglet « Formules », nous cliquons sur « Définir un nom ». Nous appelons ce nom «alimentParDéfaut», et nous entrons comme valeur «"Maïs"« (avec les guillemets).

Dans notre formule, nous écrivons alors :

  =SOMME.SI(tOeufs[Alimentation];alimentParDéfaut;tOeufs[Nb_œufs]) 

Le résultat sera identique, mais si un jour nous voulons changer « Maïs » par « Blé », il nous suffira de modifier la définition du nom, sans avoir à toucher à la formule.

C’est extrêmement pratique pour centraliser les paramètres.