L'informatique à votre portée
L'informatique prêt-à-porter

Faites appel à moi pour vos formations
sur les dpts 81, 31, 82, 11 et 67 (Strasbourg)
Confiez moi vos développements Office

FacebookTwitterGoogle BookmarksLinkedin

Dépanner des formules

6 août 2012
Imprimer E-mail

Savoir relire une formule d'Excel 2010, 2007 ou 2003

Lorsque vous rencontrez une valeur d’erreur, ou un résultat incohérent, votre premier réflexe doit être la vérification des références de cellules.

  • Cliquez dans la barre de formule, ces références sont aussitôt colorées dans la formule et également dans la feuille. les bordures des plages sont de la même couleur que leurs références dans la barre de formule
    Dans l’exemple, ci-après, vous pouvez constatez que la plage B3 :B13 n’a pas la même taille que la plage A3 :A14, ce qui provoque l’erreur.

corriger une formule d'Excel 2010

Pour corriger la taille de vos plages, il suffit d'utiliser l'une de ces méthodes (ou les deux) :

  1. Cliquer sur l’un des carrés, aux angles de la plage puis glisser pour l’agrandir ou la diminuer
    corriger une formule d'Excel 2010
  2. Cliquer sur l’un des bords de la plage, puis glisser pour déplacer la sélection de la plage.
    corriger une formule d'Excel 2010

Corriger une formule contenant une ou plusieurs fonctions dans Excel 2010, Excel 2013 ou antérieur

Lorsqu’une formule contient plusieurs fonctions imbriquées il est parfois difficile de la relire et la corriger. Pour simplifier cette lecture, utilisez la commande Insertion de fonction :

  1. Cliquez dans la barre de formule sur le nom de la fonction que vous voulez vérifier
  2. Cliquez sur le bouton ƒx. La fenêtre Arguments de fonction s’affiche aussitôt.

    Assistant fonction dans Excel 2010, Excel 2013, 2007 et 2003


Sans quitter cette fenêtre, cliquez sur une autre fonction. La boîte de dialogue affiche alors les arguments de cette nouvelle fonction. Vous pouvez ainsi vérifier les arguments de chacune des fonctions en cliquant sur son nom dans la barre de formule.
Sur l'exemple ci-dessous, le 1er écran affiche les arguments du SI.

assistant fonction pour corriger une formule

Sur le 2e écran, ce sont les arguments de la fonction ET qui s'affiche car le curseur a été placé sur le nom de cette fonction dans la barre de formule

assistant fonction pour corriger une formule


Sur cette fenêtre, les arguments sont physiquement séparés, de plus à droite de ces derniers, vous visualisez leur résultat. Sous l’ensemble de ces arguments, le résultat de la fonction s’affiche et enfin en bas à gauche vous voyez le résultat de la formule complète.

Assistant fonction pour lire une formule

 

Trouver les cellules contenant une formule

Corriger une erreur dans une cellule, c'est bien, mais comment rapidement retrouver toutes les formules de ma feuille ?

Dans Excel 2010 et 2007 c'est tout simple :

  1. Placez vous dans une cellule de votre feuille
  2. cliquez simplement sur la commande Formule du bouton Recherchez et sélectionnez de l'onglet Accueil

Evaluer une formule d'Excel dans Excel 2010 ou Excel 2013

Pour corriger vos formules, vous avez une autre possibilité : l’évaluation de formules. Cette commande permet l’évaluation en pas à pas d’une formule.

Prenons par exemple une formule qui doit calculer la TVA après l’application d’une remise. Vous pouvez avoir saisi machinalement cette formule : =HT-Remise*TxTva ce qui affiche un net à payer de 1861,14 € au lieu de 1089,90 €. Pourquoi ?

Evaluer une formule dans Excel 2010

Sélectionnez cette cellule et sur l’onglet Audit de formule, cliquez sur le bouton Evaluer la formule :

Evaluer une formule dans Excel 2010

Cliquez maintenant sur le bouton Evaluer. Les 3 premiers clics sur ce bouton vont simplement remplacer les références de cellule par leurs valeurs numériques. Au 4ème clic, la multiplication sera calculée et affiche son résultat, le 5ème clic évalue la soustraction. Vous constatez que la 1ère opération calculée par Excel est la multiplication.

1 1ère étape de l'évaluation d'une formule dans Excel 2010 2 2e étape de l'évaluation d'une formule dans Excel 2010 3 3e étape de l'évaluation d'une formule dans Excel 2010
4 4e étape de l'évaluation d'une formule dans Excel 2010 5 5e étape de l'évaluation d'une formule dans Excel 2010

En effet Excel 2010 respecte un ordre de priorité dans les opérateurs, les multiplications et divisions ont un rang de calcul supérieur à l’addition et la soustraction. Pour notre calcul de TVA, nous devrions d’abord soustraire la remise au total HT avant d’appliquer le taux de TVA. Afin de forcer Excel a calculer la formule dans l’ordre que nous voulons, il suffit d’ajouter des parenthèses autour des calculs prioritaires, ce qui donne cette fois =(HT-Remise)*TxTva.

 

La réévaluation de cette formule montre que maintenant la formule se calcule dans le bon sens.

Evaluer une formule dans Excel 2010 ce qui donne Evaluer une formule dans Excel 2010 et Excel 2013
Vous pouvez également évaluer une partie de votre formule directement dans la barre de formule : sélectionnez ce que vous voulez évaluer dans la barre de formule, puis appuyez sur la touche F9. Le résultat s’affiche aussitôt.

Evaluer une formule dans Excel 2010 avec la touche F9  ce qui donne  Evaluer une formule dans Excel 2010 avec la touche F9

Les codes d'erreur sur les formules d'Excel 2010, Excel 2013 ou antérieur

Excel 2010 vous aide dans l’identification de la source de votre erreur en affichant un code d’erreur, comprendre ces codes est donc indispensable.

##### : La cellule n’est pas assez large pour afficher le nombre (ou la cellule contient une heure négative).

#VALEUR ! Un des arguments de la fonction ou de la formule n’est pas du bon type ou est manquant. Exemple vous essayez d’additionner une cellule contenant un nombre et une cellule contenant un texte : le texte n’est pas une valeur appropriée.

#DIV/0 ! Vous essayez de diviser par zéro ou par une cellule vide (équivalent à la valeur 0), ce qui est impossible.

#NOM ! La formule fait référence à un nom n’existant pas ou vous avez fait une erreur de saisie quant au nom d’une fonction.

#N/A ! Non disponible (not available) ou Pas de réponses (no answer). Vous retrouvez souvent cette erreur avec la fonction RECHERCHEV si la fonction n’a pas trouvé de données correspondante.

#NUL! La formule fait référence à une intersection de plages qui ne correspond à rien. Cette erreur arrive lorsque vous tapez une plage en mettant un espace entre les 2 noms de cellule au lieu des 2 points.
Exemple : =SOMME(C3 C4) au lieu de =SOMME(C3 :C4)

#NOMBRE ! Une référence contient une valeur numérique qui n’est pas valide. Par exemple, vous demandez la racine carrée d’un nombre négatif.

#REF! Une référence de cellule n'est pas valide. Par exemple, vous avez supprimé une cellule à laquelle une formule fait référence

Corriger une formule Excel 2010, Excel 2013 ou antérieur

Vous pouvez également retrouver une aide complémentaire sur les pages du site Microsoft Office listée sur Correction de formules