« Oh mon Dieu » les formules excel ne fonctionnent pas dans mon rapport.
Cela vous dit quelque chose non ?
Si oui, ne vous inquiétez pas, vous êtes juste l’un des nombreux utilisateurs d’excel qui sont confrontés à ce problème très souvent.
Il suffit d’y penser.
Vous avez construit un modèle excel pour les prévisions de ventes en utilisant diverses logiques avec des formules assez compliquées.
Et lorsque vous essayez d’utiliser ce modèle, vous voyez que certaines de vos formules ne fonctionnent pas ou semblent cassées.
Basiquement, deux choses peuvent se produire ici :
D’abord, vous pourriez avoir envie de dire quelque chose bruyamment .
D’autre part, vous faites un effort supplémentaire et corrigez toutes les formules.
Donc, pour éviter tous ces tracas, nous avons écrit cet article approfondi couvrant la plupart des raisons pour lesquelles les formules excel ne fonctionnent pas.
Et comment les réparer.
Les formules excel ne fonctionnent pas : Pourquoi & Comment les réparer
Selon l’article du Washington post sur les erreurs excel, « les chercheurs australiens ont découvert qu’environ 1 sur 5 de ces articles comprenaient des erreurs dans leurs listes de gènes qui étaient dues à la conversion automatique par Excel des noms de gènes en des choses comme des dates de calendrier ou des nombres aléatoires. »
La recherche originale a été menée par le Journal Genome Biology a constaté qu’il y a une tendance croissante d’année en année en termes de nombre d’erreurs.
Vous pouvez télécharger les matériaux de recherche complets sur le site SourceForge (Open Source) pour une lecture plus approfondie.
Le document de recherche ci-dessus peut ne pas être directement lié aux formules ne fonctionnant pas dans Excel, mais il y a un point ici :
Un logiciel très bien construit & testé comme MS Excel peut se tromper en termes de compréhension de nos données qu’en est-il de vous & moi ?
Donc, voici nos 10 principales raisons
#1 Options de calcul
Changer l’option de calcul en ‘manuel’ est une meilleure pratique mais vous devez être très prudent lors de l’utilisation.
Par défaut, l’option de calcul sera définie sur automatique dans excel.
Mais il y a des cas où vous avez pu transformer cela en ‘manuel’ pour accélérer le processus.
Si vous mettez cette option sur ‘ON’ alors vos formules ne seront pas mises à jour automatiquement, ce qui à son tour augmente la vitesse de traitement de la feuille de calcul.
Une fois que vous aviez terminé, vous devez cliquer sur Enregistrer ou Actualiser le classeur pour recalculer. Si vous ne l’avez pas fait, alors vous vous retrouverez avec des résultats incorrects.
Comment résoudre ce problème :
Allez dans Formules → Calcul → Cliquez sur l’option Calculer maintenant/Calculer la feuille.
Vous pouvez également utiliser la touche de raccourci F9 pour rafraîchir le classeur.
Voici notre guide vidéo sur les options de calcul excel.
Conseils :
- Prenez l’habitude d’appuyer sur Ctl+S
- N’utilisez pas cette option pour chaque classeur, sauf si vous avez un énorme fichier avec beaucoup de formules
- Cette option est spécifique au classeur donc vous devez l’activer/désactiver dans chaque classeur que vous voulez utiliser
#2 La formule Excel ne calcule pas juste en montrant la formule
Montrer les formules est une option dans Excel pour afficher toutes les formules du classeur en un seul clic.
Cette option est très utile pour trouver toutes les formules dans une feuille de calcul.
Ayant dit cela, nous devons être un peu prudents lors de l’utilisation de l’option show formulas, sinon, vous pourriez vous retrouver avec quelque chose comme ci-dessous.
Vous voyez que la formule Today est affichée comme une formule au lieu d’afficher la date réelle ?
Il en va de même pour les formules Randbetween.
Comment résoudre ce problème :
C’est très simple :
Allez dans Formules → Sous Vérification des formules → Cliquez sur « Afficher les formules » (Avec le premier clic, les formules s’afficheront et le Second clic, elles se cacheront derrière les cellules et commenceront à fonctionner)
Lire aussi : 51 meilleurs trucs et astuces excel
#3 L’espace(s) supplémentaire(s) est un casse-tête supplémentaire
Vous avez extrait des données en utilisant le format CSV (valeurs séparées par des virgules), alors vos données sont surtout exposées à des espaces indésirables.
Savez-vous que les espaces supplémentaires causent beaucoup de problèmes lors de l’utilisation de la formule Vlookup ?
Il est parfois assez difficile d’identifier ces espaces.
Par exemple, disons que si vous voulez faire un Vlookup en utilisant le nom de l’employé comme base, alors votre nom d’employé ressemble à ce qui suit
Avez-vous remarqué ?
Bien que les deux noms soient similaires à notre œil nu, il y a un espace vide dans le deuxième nom.
Ceci ne répondrait pas aux critères de correspondance exacte dans la formule Vlookup, ce qui entraîne donc une erreur #N/A.
Comment résoudre ce problème :
Le problème d’espace(s) supplémentaire(s) peut être résolu en utilisant la formule Trim.
En général, la formule Trim trouvera plus d’un espace entre les mots et elle les supprimera.
Conseils :
- C’est toujours une bonne idée d’utiliser la formule Trim avant le Vlookup dans les deux tableaux (lookup & tableau de données)
- La fonction trim permettra . ignorer les espaces vides entre les lettres (elle ne fonctionnera qu’avec les mots)
#4 Se débarrasser des caractères non imprimables/cachés
Similaire aux espaces de tête, les caractères non imprimables sont également l’une des raisons pour lesquelles les formules excel ne fonctionnent pas.
Dans le cas où vous avez des données avec ces caractères, il est préférable de les enlever avant de travailler davantage.
Comment résoudre ce problème :
Similairement à l’élagage, vous pouvez utiliser la formule CLEAN pour supprimer ces caractères.
N°5 Formatage Excel – n’essayez pas de comparer des pommes avec des oranges
Il est très important d’avoir un formatage correct avant d’utiliser des formules comme Vlookup, Hlookup et Match & Index.
Les nombres formatés comme un texte sont l’une des raisons courantes pour lesquelles les formules ne fonctionnent pas dans excel.
Bien qu’ils ressemblent à des nombres, ils n’en sont pas.
Par exemple, sur le côté gauche, vous pouvez voir que l’ID du client ressemble à des nombres.
Mais, si vous vous référez à la section de formatage ci-dessus, vous pourriez voir qu’ils ont été formatés comme du texte.
Ceci, à son tour, déclenchera l’erreur #N/A lors de l’ajout de la formule Vlookup.
Le pire, c’est que c’est un peu difficile à identifier.
Comment résoudre ce problème :
Vérifiez que tous vos chiffres sont formatés comme des chiffres et non comme du texte. Il vaut la peine de reformater la colonne de consultation avant d’utiliser la formule Vlookup.
Conseils :
- En général, les chiffres seront toujours alignés sur le côté droit de la cellule (mais dans notre exemple ci-dessus, ils ne le sont pas)
- Parfois, vous pouvez voir une petite pointe verte dans le coin supérieur gauche, cela indique que quelque chose ne va pas avec cette cellule
#6 Références circulaires (Erreurs dues à un cercle sans fin)
En termes simples, votre plage de formule est dans une boucle sans fin ou elle est dans un cercle.
Selon les règles de calcul d’excel, vos formules ne doivent pas référencer les cellules de résultat comme une plage. Si vous le faites, des erreurs de référence circulaire apparaîtraient.
Personnellement, j’ai lutté de nombreuses fois avec ce type d’erreurs.
Par exemple, dans notre tableau Budget des dépenses ci-dessous, nous devons ajouter un certain bouchon (numéro d’ajustement) pour correspondre aux objectifs.
Donc, nous avons calculé le montant différentiel dans la ligne 13, et maintenant nous avons juste besoin de les mettre dans la ligne 9.
Une façon simple est de lier les cellules de la ligne 13 dans la ligne 9.
Si vous faites cela, votre formule de liaison ne fonctionnera pas au lieu de cela, vous pouvez voir zéro et éventuellement vous pourriez aussi voir des flèches de couleur bleue pointant des références de cellules.
En général, cela devrait fonctionner.
Mais techniquement, ce sera une erreur car les références de nos cellules de formule sont en boucle.
Comment résoudre ce problème :
D’abord, nous devons identifier les cellules d’erreur.
Allez dans l’onglet Formules → cliquez sur la flèche déroulante Vérification des erreurs → placez le curseur sur Références de cellules → maintenant vous verrez la ou les plages d’erreurs)
Deuxièmement, maintenant que vous connaissez la plage de références circulaires, allez dans chaque cellule d’erreur et corrigez les formules.
#7 Utiliser les guillemets doubles de manière incorrecte – erreur humaine
Nous utilisons souvent les guillemets doubles lorsque nous écrivons des formules.
Mais, il est très important de comprendre quand les utiliser et quand ne pas les utiliser.
Par exemple, pour joindre deux nombres, nous pouvons utiliser une formule comme ci-dessous.
=1234&5678 = 12345678
Alternativement, pour joindre deux chaînes de texte, vous ne pouvez pas faire la même chose au lieu de cela, vous devez spécifier le texte entre guillemets doubles.
=John&Smith : Mauvaise façon d’utiliser
= »John »& »Smith » = C’est la bonne façon.
Il en va de même pour les formules conditionnelles comme IF, AND, OR. Il est très important de spécifier le texte entre guillemets doubles.
Sinon, c’est l’une des raisons pour lesquelles les formules excel ne fonctionnent pas.
#8 BODMAS – règle de base pour chaque calcul
Savez-vous que chaque calcul dans excel est basé sur la règle ‘BODMAS’ ?
Oh, attendez… qu’est-ce que la règle BODMAS ?
C’est une règle mathématique pour déterminer un ordre d’opérations.
Ok, simplifions encore.
Pouvez-vous deviner quelle est la réponse à la question ci-dessous?
10+10*2 = ?
C’est 40, félicitations vous avez tort.
Pouvez-vous essayer le même calcul dans excel ?
Vous verrez très probablement 30, en effet c’est la bonne réponse… mais comment ?
Selon la règle du BODMAS, le calcul se fera sur la base d’une séquence c’est-à-dire → B- parenthèses, O- ordre, D- division, M- multiplication, A- addition & S- soustraction.
Donc, la séquence de la formule ci-dessus est → 10*2 = 20 → 20+10 =30.
Vos formules peuvent se tromper si vous ne tenez pas compte du BODMAS.
Comment résoudre ce problème :
C’est très simple, il suffit de changer la séquence en utilisant des parenthèses comme ci-dessous.
=(30+40)*2
Donc, tout ce qui est entre parenthèses sera calculé en premier et ensuite il passera à l’opération suivante.
#9 Utilisation incorrecte du référencement » absolu «
Tous ceux qui utilisent Excel savent pour la plupart ce qu’est le référencement absolu.
Si ce n’est pas le cas, arrêtez-vous ici et apprenez-en plus. Voici un tutoriel rapide sur les références de cellules.
Avec l’aide du référencement absolu, vous pouvez fixer votre plage de formules afin de pouvoir copier d’une cellule à l’autre.
C’est en effet une fonctionnalité très utile. Cela étant dit, si vous ne savez pas comment l’utiliser, alors vos formules ne fonctionneront pas comme elles ont l’intention de le faire.
Certainement, c’est l’une des principales raisons pour lesquelles les formules excel ne fonctionnent pas lorsqu’elles sont copiées d’une cellule à une autre.
Par exemple, dans notre tableau ci-dessus, nous avons utilisé le référencement absolu des cellules pour nous faciliter la copie des formules.
Mais, nous avons fait une erreur.
Au lieu d’utiliser le gel partiel, nous avons utilisé la touche de raccourci F4 et fixé toute la plage.
Ceci a en effet entraîné des résultats incorrects.
Donc, la bonne façon aurait dû être d’utiliser la référence absolue pour seulement les lignes comme ceci =SUM(D$3:D$6).
Comment résoudre ce problème :
Malheureusement, il n’y a pas de solution rapide pour ce type d’erreurs. Vous devez le faire manuellement en analysant toutes les formules.
Mais, le meilleur moyen que j’ai trouvé jusqu’à présent est de recouper les formules & résultats dès que j’utilise le référencement relatif absolu &.
Veuillez me faire part de vos solutions de contournement à ce stade.
Conseils:
- Ne pas utiliser la touche de raccourci F4 pour le gel partiel
- Essayer d’inclure un signe dollar ($) manuellement pour la plage partielle
#10 Arguments de formule incorrects
Les arguments de formule incorrects peuvent avoir un impact négatif sur les résultats de la formule.
Juste pour vous donner un peu de perspective, comprenons la syntaxe vlookup ci-dessous, en particulier la dernière .
L’argument de recherche de plage a deux options à sélectionner.
- Vrai – Correspondance approximative
- Faux – Correspondance exacte
Personnellement, je pense qu’utiliser l’option « Correspondance approximative » revient à se suicider.
Parce que la correspondance approximative ne renverra jamais des résultats précis.
Dans notre exemple ci-dessus, nous avons ajouté la formule Vlookup dans la cellule F3 pour trouver le montant du revenu correspondant.
Si vous observez bien, le montant du revenu 25k pour l’id client 6378993 est incorrect. Il aurait dû être de 65k à la place.
Alors, qu’est-ce qui n’a pas fonctionné ?
Comme je l’ai mentionné précédemment, j’ai utilisé » Approximate match » sous l’argument de recherche de plage.
Le » match » est une autre formule où vous avez une option pour sélectionner des arguments similaires.
Comment résoudre ce problème :
Ne jamais utiliser ‘Approximate match’ dans vos formules vlookup.
Maintenant c’est votre tour
Vous pensez avoir d’autres raisons pour lesquelles les formules excel ne fonctionnent pas ?
Ou, peut-être avez-vous une question à poser.