Les formules Excel ne fonctionnent pas : raisons possibles et comment y remédier !

Si vous travaillez avec des formules dans Excel, vous rencontrerez tôt ou tard le problème où les formules Excel ne fonctionnent pas du tout (ou donnent le mauvais résultat).

Bien que cela aurait été formidable s'il n'y avait eu que quelques raisons possibles pour les formules de dysfonctionnement. Malheureusement, il y a trop de choses qui peuvent mal tourner (et cela arrive souvent).

Mais comme nous vivons dans un monde qui suit le principe de Pareto, si vous recherchez certains problèmes courants, cela résoudra probablement 80% (ou peut-être même 90% ou 95% des problèmes où les formules ne fonctionnent pas dans Excel).

Et dans cet article, je mettrai en évidence les problèmes courants qui sont probablement la cause de votre Les formules Excel ne fonctionnent pas.

Alors, commençons!

Syntaxe incorrecte de la fonction

Permettez-moi de commencer par souligner l'évidence.

Chaque fonction dans Excel a une syntaxe spécifique, telle que le nombre d'arguments qu'elle peut prendre ou le type d'arguments qu'elle peut accepter.

Et dans de nombreux cas, la raison pour laquelle vos formules Excel ne fonctionnent pas ou donnent le mauvais résultat peut être un argument incorrect (ou des arguments manquants).

Par exemple, la fonction RECHERCHEV prend trois arguments obligatoires et un argument facultatif.

Si vous fournissez un mauvais argument ou ne spécifiez pas l'argument facultatif (là où il est nécessaire pour que la formule fonctionne), cela vous donnera un mauvais résultat.

Par exemple, supposons que vous ayez un ensemble de données comme indiqué ci-dessous où vous devez connaître le score de la note à l'examen 2 (dans la cellule F2).

Si j'utilise la formule ci-dessous, j'obtiendrai le mauvais résultat, car j'utilise la mauvaise valeur dans le troisième argument (celui qui demande le numéro d'index de colonne).

=RECHERCHEV(A2,A2:C6,2,FAUX)

Dans ce cas, la formule calcule (car elle renvoie une valeur), mais le résultat est incorrect (au lieu du score de l'examen 2, elle donne le score de l'examen 1).

Un autre exemple où vous devez être prudent sur les arguments est lors de l'utilisation de RECHERCHEV avec la correspondance approximative.

Étant donné que vous devez utiliser un argument facultatif pour spécifier où vous voulez que RECHERCHEV fasse une correspondance exacte ou approximative, ne pas le spécifier (ou utiliser le mauvais argument) peut causer des problèmes.

Vous trouverez ci-dessous un exemple où j'ai les données de notes pour certains étudiants et je souhaite extraire les notes de l'examen 1 pour les étudiants dans le tableau de droite.

Lorsque j'utilise la formule RECHERCHEV ci-dessous, une erreur s'affiche pour certains noms.

=RECHERCHEV(E2,$A$2:$C$6,2)

Cela se produit car je n'ai pas spécifié le dernier argument (qui est utilisé pour déterminer s'il faut faire une correspondance exacte ou approximative). Lorsque vous ne spécifiez pas le dernier argument, il effectue automatiquement une correspondance approximative par défaut.

Comme nous devions faire une correspondance exacte dans ce cas, la formule renvoie une erreur pour certains noms.

Bien que j'aie pris l'exemple de la fonction RECHERCHEV, dans ce cas, c'est quelque chose qui peut s'appliquer à de nombreuses formules Excel qui ont également des arguments facultatifs.

Lisez également : Identifier les erreurs à l'aide du débogage de formules Excel

Espaces supplémentaires provoquant des résultats inattendus

Les espaces de début et de fin sont difficiles à trouver et peuvent causer des problèmes lorsque vous utilisez une cellule qui les contient dans des formules.

Par exemple, dans l'exemple ci-dessous, si j'essaie d'utiliser RECHERCHEV pour récupérer le score de Mark, cela me donne l'erreur #N/A (l'erreur non disponible).

Bien que je puisse voir que la formule est correcte et que le nom « Mark » est clairement là, il y a la liste, ce que je ne peux pas voir, c'est qu'il y a un espace de fin dans la cellule qui porte le nom (dans la cellule D2).

Excel ne considère pas le contenu de ces deux cellules de la même manière et, par conséquent, il le considère comme une incompatibilité lors de la récupération de la valeur à l'aide de RECHERCHEV (ou il peut s'agir de toute autre formule de recherche).

Pour résoudre ce problème, vous devez supprimer ces caractères d'espace supplémentaires.

Vous pouvez le faire en utilisant l'une des méthodes suivantes :

1. Nettoyez la cellule et supprimez tous les espaces de début/de fin avant de l'utiliser dans des formules
2. Utilisez la fonction TRIM dans la formule pour vous assurer que tous les espaces de début/de fin/double sont ignorés.

Dans l'exemple ci-dessus, vous pouvez utiliser la formule ci-dessous à la place pour vous assurer que cela fonctionne.

=RECHERCHEV(AJUSTER(D2),$A$2:$B$6,2,0)

Bien que j'aie pris l'exemple RECHERCHEV, il s'agit également d'un problème courant lorsque vous travaillez avec des fonctions TEXTE.

Par exemple, si j'utilise la fonction LEN pour compter le nombre total de caractères dans une cellule, s'il y a des espaces de début ou de fin, ceux-ci seraient également comptés et donneraient le mauvais résultat.

À emporter / Comment réparer: Si possible, nettoyez vos données en supprimant les espaces de début/de fin ou les doubles espaces avant de les utiliser dans les formules. Si vous ne pouvez pas modifier les données d'origine, utilisez la fonction TRIM dans les formules pour vous en occuper.

Utilisation du calcul manuel au lieu d'un calcul automatique

Ce paramètre peut vous rendre fou (si vous ne savez pas que c'est la cause de tous les problèmes).

Excel a deux modes de calcul - Automatique et Manuel.

Par défaut, le mode automatique est activé, ce qui signifie que dans le cas où j'utilise une formule ou apporte des modifications aux formules existantes, il effectue automatiquement (et instantanément) le calcul et me donne le résultat.

C'est le réglage auquel nous sommes tous habitués.

Dans le paramètre automatique, chaque fois que vous apportez une modification à la feuille de calcul (comme la saisie d'une nouvelle formule pour même du texte dans une cellule), Excel recalcule automatiquement tout (oui, tout).

Mais dans certains cas, les utilisateurs activent le paramètre de calcul manuel.

Cela se fait principalement lorsque vous avez un fichier Excel lourd avec beaucoup de données et de formules. Dans de tels cas, vous ne voudrez peut-être pas qu'Excel recalcule tout lorsque vous apportez de petites modifications (car cela peut prendre quelques secondes, voire quelques minutes) pour que ce recalcul se termine.

Si vous activez le calcul manuel, Excel ne calculera pas à moins que vous ne le forciez.

Et cela peut vous faire penser que votre formule ne calcule pas.

Tout ce que vous avez à faire dans ce cas est soit de remettre le calcul en automatique, soit de forcer un recalcul en appuyant sur la touche F9.

Voici les étapes pour changer le calcul de manuel à automatique :

1. Cliquez sur l'onglet Formule
2. Cliquez sur Options de calcul
3. Sélectionnez Automatique

Important : si vous changez le calcul de manuel en automatique, c'est une bonne idée de créer une sauvegarde de votre classeur (juste au cas où cela ferait se bloquer votre classeur ou ferait planter Excel)

À emporter / Comment réparer: Si vous remarquez que vos formules ne donnent pas le résultat attendu, essayez quelque chose de simple dans n'importe quelle cellule (comme ajouter 1 à une formule existante. Une fois que vous avez identifié le problème comme celui où le mode de calcul doit être modifié, effectuez un calcul de force en utilisant F9.

Suppression des lignes/colonnes/cellules menant à #REF ! Erreur

L'une des choses qui peuvent avoir un effet dévastateur sur vos formules existantes dans Excel est lorsque vous supprimez toute ligne/colonne qui a été utilisée dans les calculs.

Lorsque cela se produit, parfois, Excel ajuste la référence elle-même et s'assure que les formules fonctionnent correctement.

Et parfois… ça ne peut pas.

Heureusement, une indication claire que vous obtenez lorsque les formules se cassent lors de la suppression de cellules/lignes/colonnes est le #REF ! erreur dans les cellules. Il s'agit d'une erreur de référence qui vous indique qu'il y a un problème avec les références dans la formule.

Laissez-moi vous montrer ce que je veux dire en utilisant un exemple.

Ci-dessous, j'ai utilisé la formule SUM pour ajouter les cellules A2: A6.

Maintenant, si je supprime l'une de ces cellules/lignes, la formule SUM renverra un #REF ! Erreur. Cela se produit parce que lorsque j'ai supprimé la ligne, la formule ne sait pas à quoi faire référence maintenant.

Vous pouvez voir que le troisième argument de la formule est devenu #REF ! (qui faisait précédemment référence à la cellule que nous avons supprimée).

À emporter / Comment réparer: avant de supprimer des données utilisées dans les formules, assurez-vous qu'il n'y a pas d'erreurs après la suppression. Il est également recommandé de créer régulièrement une sauvegarde de votre travail pour vous assurer d'avoir toujours quelque chose sur quoi vous appuyer.

Placement incorrect des parenthèses (BODMAS)

Au fur et à mesure que vos formules commencent à devenir plus grosses et plus complexes, c'est une bonne idée d'utiliser des parenthèses pour être absolument clair sur quelle partie appartient ensemble.

Dans certains cas, vous pouvez avoir la parenthèse au mauvais endroit, ce qui peut vous donner un mauvais résultat ou une erreur.

Et dans certains cas, il est recommandé d'utiliser des parenthèses pour s'assurer que la formule comprend ce qui doit être groupé et calculé en premier.

Par exemple, supposons que vous ayez la formule suivante :

=5+10*50

Dans la formule ci-dessus, le résultat est 505 car Excel effectue d'abord la multiplication puis l'addition (car il existe un ordre de priorité en ce qui concerne les opérateurs).

Si vous voulez qu'il fasse d'abord l'addition puis la multiplication, vous devez utiliser la formule ci-dessous :

=(5+10)*50

Dans certains cas, l'ordre de priorité peut fonctionner pour vous, mais il est toujours recommandé d'utiliser la parenthèse pour éviter toute confusion.

De plus, au cas où vous seriez intéressé, vous trouverez ci-dessous l'ordre de priorité des différents opérateurs souvent utilisés dans les formules :

Opérateur	La description	Ordre de préséance
: (deux points)	Varier	1
(seul espace)	Intersection	2
, (virgule)	syndicat	3
-	Négation (comme dans -1)	4
%	Pourcentage	5
^	Exponentiation	6
* et /	Multiplication & division	7
+ et -	Addition soustraction	8
&	Concaténation	9
= =	Comparaison	10

À emporter / Comment réparer: Utilisez toujours des parenthèses pour éviter toute confusion, même si vous connaissez l'ordre de préséance et que vous l'utilisez correctement. Les parenthèses facilitent l'audit des formules.

Utilisation incorrecte des références de cellules absolues/relatives

Lorsque vous copiez et collez des formules dans Excel, il ajuste automatiquement les références. Parfois, c'est exactement ce que vous voulez (surtout lorsque vous copiez-collez des formules dans la colonne), et parfois vous ne voulez pas que cela se produise.

Une référence absolue est lorsque vous corrigez une référence de cellule (ou une référence de plage) afin qu'elle ne change pas lorsque vous copiez et collez des formules, et une référence relative est une référence qui change.

Vous pouvez en savoir plus sur les références absolues, relatives et mixtes ici.

Vous pouvez obtenir un résultat incorrect si vous oubliez de remplacer la référence par une référence absolue (ou vice versa). C'est quelque chose qui m'arrive assez souvent lorsque j'utilise des formules de recherche.

Permettez-moi de vous montrer un exemple.

Ci-dessous, j'ai un ensemble de données où je veux récupérer le score à l'examen 1 pour les noms de la colonne E (un cas d'utilisation simple de RECHERCHEV)

Vous trouverez ci-dessous la formule que j'utilise dans la cellule F2, puis que je copie dans toutes les cellules en dessous :

=RECHERCHEV(E2,A2:B6,2,0)

Comme vous pouvez le voir, cette formule donne une erreur dans certains cas.

Cela se produit parce que je n'ai pas verrouillé l'argument du tableau de la table - c'est A2:B6 dans la cellule F2, alors qu'il aurait dû être $A$2 :$B$6

En plaçant ces signes dollar avant le numéro de ligne et l'alphabet de colonne dans une référence de cellule, j'oblige Excel à conserver ces références de cellule fixes. Ainsi, même lorsque je copie cette formule, le tableau du tableau continuera à faire référence à A2: B6

Conseil de pro: Pour convertir une référence relative en référence absolue, sélectionnez cette référence de cellule dans la cellule et appuyez sur la touche F4. Vous remarquerez que cela change en ajoutant les signes dollar. Vous pouvez continuer à appuyer sur F4 jusqu'à ce que vous obteniez la référence souhaitée.

Référence incorrecte aux noms de feuille / classeur

Lorsque vous faites référence à d'autres feuilles ou classeurs dans une formule, vous devez suivre un format spécifique. Et si le format est incorrect, vous obtiendrez une erreur.

Par exemple, si je veux faire référence à la cellule A1 dans Sheet2, la référence serait =Feuille2!A1 (où il y a un signe d'exclamation après le nom de la feuille)

Et s'il y a plusieurs mots dans le nom de la feuille (disons que c'est Exemple de données), la référence serait ='Exemple de données'!A1 (où le nom est entouré de guillemets simples suivis d'un signe d'exclamation).

Si vous faites référence à un classeur externe (disons que vous faites référence à la cellule A1 dans « Exemple de feuille » dans le classeur nommé « Exemple de classeur »), la référence sera comme indiqué ci-dessous :

='[Exemple de classeur.xlsx]Exemple de feuille'!$A$1

Et si vous fermez le classeur, la référence change pour inclure le chemin complet du classeur (comme indiqué ci-dessous):

='C:\Users\sumit\Desktop\[Example Workbook.xlsx]Example Sheet'!$A$1

Si vous finissez par changer le nom du classeur ou de la feuille de calcul auquel la formule fait référence, cela vous donnera un #REF ! Erreur.

Lisez aussi : Comment trouver des liens externes et des références dans Excel

Références circulaires

Une référence circulaire est lorsque vous faites référence (directement ou indirectement) à la même cellule où la formule est calculée.

Vous trouverez ci-dessous un exemple simple, où j'utilise la formule SUM dans la cellule A4 tout en l'utilisant dans le calcul lui-même.

=SOMME(A1:A4)

Bien qu'Excel vous affiche une invite vous informant de la référence circulaire, il ne le fera pas pour chaque instance. Et cela peut vous donner le mauvais résultat (sans aucun avertissement).

Si vous suspectez une référence circulaire en jeu, vous pouvez vérifier quelles cellules l'ont.

Pour ce faire, cliquez sur l'onglet Formule et dans le groupe « Audit de formule », cliquez sur l'icône déroulante Vérification des erreurs (la petite flèche pointant vers le bas).

Passez le curseur sur l'option Référence circulaire et cela vous montrera la cellule qui a le problème de référence circulaire.

Cellules formatées en texte

Si vous vous trouvez dans une situation où dès que vous tapez la formule en appuyant sur Entrée, vous voyez la formule au lieu de la valeur, il s'agit clairement d'un cas où la cellule est formatée en texte.

Lorsqu'une cellule est formatée en tant que texte, elle considère la formule comme une chaîne de texte et l'affiche telle quelle.

Cela ne l'oblige pas à calculer et à donner le résultat.

Et il a une solution facile.

1. Changez le format en « Général » à partir de « Texte » (c'est dans l'onglet Accueil du groupe Nombres)
2. Accédez à la cellule contenant la formule, entrez dans le mode d'édition (utilisez F2 ou double-cliquez sur la cellule) et appuyez sur Entrée

Si les étapes ci-dessus ne résolvent pas le problème, une autre chose à vérifier est si la cellule a une apostrophe au début. Beaucoup de gens ajoutent une apostrophe pour convertir des formules et des nombres en texte.

S'il y a une apostrophe, vous pouvez simplement la supprimer.

Le texte est automatiquement converti en dates

Excel a cette mauvaise habitude de convertir ce qui ressemble à une date en une date réelle. Par exemple, si vous entrez 1/1, Excel le convertira en 01-janvier de l'année en cours.

Dans certains cas, cela peut être exactement ce que vous voulez, et dans certains cas, cela peut jouer contre vous.

Et comme Excel stocke les valeurs de date et d'heure sous forme de nombres, dès que vous entrez 1/1, il le convertit en un nombre représentant le 1er janvier de l'année en cours. Dans mon cas, lorsque je fais cela, il le convertit en le nombre 43831 (pour 01-01-2020).

Cela pourrait gâcher vos formules si vous utilisez ces cellules comme argument dans une formule.

Comment régler ceci?

Encore une fois, nous ne voulons pas qu'Excel choisisse automatiquement le format pour nous, nous devons donc spécifier clairement le format nous-mêmes.

Vous trouverez ci-dessous les étapes pour modifier le format en texte afin qu'il ne convertisse pas automatiquement le texte en dates :

1. Sélectionnez les cellules/plage dont vous souhaitez modifier le format
2. Cliquez sur l'onglet Accueil
3. Dans le groupe Nombre, cliquez sur la liste déroulante Format
4. Cliquez sur Texte

Désormais, chaque fois que vous entrez quelque chose dans les cellules sélectionnées, il sera considéré comme du texte et non modifié automatiquement.

Remarque : Les étapes ci-dessus ne fonctionneraient que pour les données saisies après que le formatage a été modifié. Cela ne changera aucun texte qui a été converti à ce jour avant que vous ayez apporté cette modification de mise en forme.

Un autre exemple où cela peut être vraiment frustrant est lorsque vous entrez un texte/un nombre qui a des zéros non significatifs. Excel supprime automatiquement ces zéros non significatifs car il les considère comme inutiles. Par exemple, si vous entrez 0001 dans une cellule, Excel le remplacera par 1. Si vous souhaitez conserver ces zéros non significatifs, suivez les étapes ci-dessus.

Les lignes/colonnes cachées peuvent donner des résultats inattendus

Il ne s'agit pas d'une formule vous donnant le mauvais résultat mais d'utiliser la mauvaise formule.

Par exemple, supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que je souhaite obtenir la somme de toutes les cellules visibles dans la colonne C.

Dans la cellule C12, j'ai utilisé la fonction SOMME pour obtenir la valeur de vente totale pour tous ces enregistrements donnés.

Jusqu'ici tout va bien!

Maintenant, j'applique un filtre à la colonne de l'article pour n'afficher que les enregistrements des ventes d'imprimantes.

Et voici le problème - la formule dans la cellule C12 montre toujours le même résultat - c'est-à-dire la somme de tous les enregistrements.

Comme je l'ai dit, la formule ne donne pas le mauvais résultat. En fait, la fonction SOMME fonctionne très bien.

Le problème est que nous avons utilisé la mauvaise formule ici.

La fonction SOMME ne peut pas tenir compte des données filtrées et vous donner le résultat pour toutes les cellules (masquées ou visibles). Si vous souhaitez uniquement obtenir la somme/le nombre/la moyenne des cellules visibles, utilisez les fonctions SOUS-TOTAL ou AGGREGATE.

Clé à emporter - Comprendre l'utilisation correcte et les limites d'une fonction dans Excel.

Ce sont quelques-unes des causes courantes que j'ai vues où votre Les formules Excel peuvent ne pas fonctionner ou donner des résultats inattendus ou erronés. Je suis sûr qu'il existe de nombreuses autres raisons pour lesquelles une formule Excel ne fonctionne pas ou ne se met pas à jour.

Si vous connaissez une autre raison (peut-être quelque chose qui vous agace souvent), faites-le moi savoir dans la section commentaires.

J'espère que vous avez trouvé ce tutoriel utile!