Comment évaluer une formule sur Excel : le déroulement pas à pas

Dans ce tutoriel, je vais vous montrer comment utiliser la fonctionnalité d’évaluation de formule sur Excel, qui permet de décomposer le déroulement d’une formule pas-à-pas

 

Téléchargement

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

 

 

Tutoriel Vidéo

Partie 1 : Comment évaluer une formule sur Excel : le déroulement pas à pas (voir le tutoriel)

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

Partie 2 : Utiliser l’outil d’évaluation pour construire des formules complexes sur Excel (SOMMEPROD) (voir le tutoriel)

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

 

1. Présentation (et exemple simple)

Qu’on se le dise, en dehors de quelques rares bugs connus, Excel effectuera toujours les calculs que nous lui demandons ! Lorsqu’un résultat retourné n’est pas celui que nous espérons, cela viendra pratiquement dans tous les cas d’une erreur dans la rédaction des formules.

Excel formation - 026 Evaluer une formule - 01

Tout le monde commet des erreurs, y compris les utilisateurs les plus chevronnés.

L’important dans ce cas-là est d’identifier cette erreur pour la corriger au plus vite.

Excel formation - 026 Evaluer une formule - 02

Et justement, pour y parvenir Excel nous propose un outil extrêmement pratique : il s’agit de l’outil d’évaluation des formules.

 

2. Exemple d’utilisation de l’évaluation des formules

Pour introduire le fonctionnement de cet outil, nous allons commencer par un exemple très simple :

Excel formation - 026 Evaluer une formule - 03

Dans ce document, qui reprend la synthèse d’exploitation d’une entreprise, nous souhaitons simplement connaître le montant de la marge brute réalisée en Europe.

 

 =B9+B10 

 

Il s’agit de l’opération la plus simple que nous puissions demander à Excel, une simple addition.

Par contre, il peut être intéressant de voir comment Excel procède pour résoudre ce calcul.

Pour cela, nous sélectionnons la cellule qui contient le calcul, puis nous nous rendons dans le menu Formules du ruban et dans le groupe Vérification des formules, nous cliquons sur le bouton Évaluer la formule :

Excel formation - 026 Evaluer une formule - 04

Excel nous présente alors la fenêtre d’Évaluation des formules :

Excel formation - 026 Evaluer une formule - 05

Dans celle-ci, nous retrouvons dans le cadre blanc la formule de la cellule sélectionnée, ainsi que les coordonnées de celles-ci juste avant.

Dans la formule, nous retrouvons un terme souligné (B9). Il s’agit du premier élément qu’Excel va calculer.

Un clic sur le bouton Évaluer permet de remplacer la référence à cette formule par sa valeur :

Excel formation - 026 Evaluer une formule - 06

Ensuite, Excel souligne le second élément afin de pouvoir effectuer exactement la même opération :

Excel formation - 026 Evaluer une formule - 07

Nous constatons au passage que le montant des achats de marchandises vendues est repris en négatif, exactement de la même manière que nous le retrouvons exprimé dans le tableau.

Et pour finir, l’ensemble des valeurs numériques sont maintenant soulignées, ce qui signifie qu’Excel va effectuer le dernier calcul pour obtenir le résultat attendu, c’est-à-dire effectuer la somme des deux valeurs :

Excel formation - 026 Evaluer une formule - 08

Pour revisionner le détail du calcul, nous pouvons alors cliquer sur le bouton Redémarrer.

 

3. Afficher la résolution pas à pas

Lorsque le résultat d’une cellule dépend d’un calcul (simple référence à une autre cellule, ou calcul complexe), il est possible de remonter tout à l’origine.

Pour cela attardons nous sur le résultat net de l’entreprise :

  • Nous sélectionnons la cellule :

Excel formation - 026 Evaluer une formule - 09

  • Puis nous lançons l’outil d’évaluation de la formule :

Excel formation - 026 Evaluer une formule - 10

Ici nous comprenons bien que la formule consiste ici encore en une simple addition.

En revanche, si nous analysons le document, nous constatons que la première cellule insérée dans cette addition (la cellule E16) correspond à l’EBE réalisé au niveau mondial, et il s’agit donc d’un calcul.

Lorsque nous cliquons sur Pas à pas détaillé, Excel va donc nous détailler le processus de résolution de cette cellule dans un nouveau cadre :

Excel formation - 026 Evaluer une formule - 11

La référence à la cellule E16 passe alors en bleu et nous retrouvons l’évaluation de son résultat en dessous.

Excel formation - 026 Evaluer une formule - 12

Ainsi il est possible de redescendre jusque trois niveaux au total.

Ensuite, il suffit de cliquer sur le bouton Évaluer pour obtenir le résultat de la cellule E14 :

Excel formation - 026 Evaluer une formule - 13

Un clic sur le bouton Pas à pas sortant permet de remonter d’un niveau :

Excel formation - 026 Evaluer une formule - 14

4. Évaluer une formule complexe

Pour ce nouvel exemple, nous souhaitons obtenir le montant d’un poste en fonction d’un pays donné.

Excel formation - 027 Evaluer une formule complexe - 01

Le poste et la zone sont sélectionnés à partir d’une liste déroulante simple. Pour savoir comment insérer une liste déroulante dans une feuille de calcul, suivez ce tutoriel.

Excel formation - 027 Evaluer une formule complexe - 02

Ensuite nous utilisons une formule SOMMEPROD() pour obtenir le résultat désiré :

 =SOMMEPROD((A9:A18=B24)*(B8:E8=B25)*B9:E18) 

L’outil d’évaluation de formule est le complément parfait pour bien appréhender le fonctionnement de cette formule relativement complexe à appréhender :

Excel formation - 027 Evaluer une formule complexe - 03

Nous pouvons donc commencer évaluer cette formule, nous voyons déjà qu’Excel va commencer par récupérer la valeur de la cellule B24 dans laquelle se trouve le poste pour lequel nous souhaitons obtenir le détail (dans notre exemple, il s’agit du poste « Frais pays »).

Un second appui permet de remplacer les valeurs contenues dans la plage de cellules située aux coordonnées A9 à A18 :

Excel formation - 027 Evaluer une formule complexe - 04

Comme nous pouvons le constater sur la capture ci-dessus, les valeurs correspondantes sont saisies sous la forme d’une liste dans laquelle chaque élément est séparé par un point-virgule, et tous les éléments sont encadrés par des accolades.

Dans Excel, ces accolades permettent d’identifier une matrice, c’est-à-dire un tableau de données qui peut être composé de plusieurs lignes et colonnes et sur lequel il est possible d’effectuer des opérations. Chaque point-virgule représente une nouvelle ligne de cette matrice, et chaque virgule simple entre deux éléments représente quant à elle une nouvelle colonne.

À cette étape de la résolution de la formule, nous observons que chaque élément est séparé par un point-virgule et il n’y a aucun point. Nous pouvons donc en déduire que la matrice stockée dans la mémoire d’Excel est une matrice verticale de dimension 10x1 (1 lignes et une seule colonne).

Excel formation - 027 Evaluer une formule complexe - 05

Dans l’étape suivante, Excel passe en revu chaque élément de cette matrice pour le comparer avec la valeur de la cellule B24. Lorsque ces deux valeurs sont équivalentes, alors Excel affecte la valeur booléenne (c’est-à-dire qui peut être également à VRAI ou à FAUX) à cet élément.

Ainsi, nous nous retrouvons avec une nouvelle matrice composée uniquement de VRAI et de FAUX (cette matrice a toujours une dimension de 10x1).

Cela étant fait, Excel effectue le même test pour déterminer quel pays est sélectionné (valeur de la cellule B25 : « ASIE ») :

Excel formation - 027 Evaluer une formule complexe - 06

Ici nous constatons que les éléments sont séparés, non plus par des points-virgules comme nous venons juste de le voir dans la matrice précédente, mais par des virgules.

Cela signifie que cette matrice a pour dimension 1x4.

Ensuite Excel compare les éléments de la matrice avec la valeur de la cellule B25 (« ASIE ») afin de remplacer les valeurs de la matrice par des VRAI ou des FAUX :

Excel formation - 027 Evaluer une formule complexe - 07

Ensuite, et c’est là l’opération la plus complexe Excel va multiplier les deux matrices ensemble pour n’en former qu’une de dimension 10x4 !

Excel formation - 027 Evaluer une formule complexe - 08

Sachant que dans cette opération, un résultat « FAUX » équivaut à 0, tandis que « VRAI » équivaut à 1 :

Excel formation - 027 Evaluer une formule complexe - 09

Ensuite, il suffit de multiplier chaque élément de la première matrice en colonne par les éléments de la second, cette fois-ci en ligne, afin d’obtenir une matrice en deux dimensions :

Excel formation - 027 Evaluer une formule complexe - 10

Nous obtenons ainsi une matrice de dimension 4x10 composée de 0 et de 1.

 

Excel formation - 027 Evaluer une formule complexe - 11

Pour terminer, nous pouvons maintenant multiplier chacun des éléments avec l’éléments situé à la même position de la matrice représenté par la plage des cellules ayant pour coordonnées B9 jusqu’à E18 :

 

Excel formation - 027 Evaluer une formule complexe - 12

Évidemment, chaque élément multiplié par 0 est égal à 0 et chaque élément multiplié par 1, vaut la valeur de celui-ci :

Excel formation - 027 Evaluer une formule complexe - 13

 

Excel formation - 027 Evaluer une formule complexe - 14

Et pour finir la dernière opération consiste à effectuer la somme de la valeur de chacun de ces éléments pris indépendamment les uns des autres :

(Facile, il ne reste qu’un seul élément différent de zéro !)

Excel formation - 027 Evaluer une formule complexe - 15

 

Article initialement publié le 13 août 2019, puis modifié le 12 septembre 2019

 



Articles qui pourraient vous intéresser

Comment lancer une macro VBA (13 méthodes !) [#08 FORMATION EXCEL VBA COMPLETE]

Les tableaux croisés dynamiques, c’est facile !

Dates et heures sur Excel : #05 Comment calculer l'âges et l’ancienneté sur Excel

Tout sur les procédures Function en VBA [#07 FORMATION EXCEL VBA COMPLETE]

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.