SI.CONDITIONS, SIERREUR et SI.NON.DISP : conditions multiples et erreurs

Dans l’épisode précédent, un SI à deux issues a suffi. Mais un barème de remises, un taux par tranche ou un classement en cinq catégories demanderait des SI emboîtés sur plusieurs niveaux, que personne ne relit sans douleur. Cet épisode présente la fonction qui règle ce problème, puis traite l’autre grande famille de difficultés des formules : les erreurs (#N/A, #DIV/0!, #VALEUR!) et la manière de les gérer sans perdre en fiabilité.

Ce que vous saurez faire à la fin de l’épisode

• écrire un barème avec SI.CONDITIONS dans le bon ordre ;
• utiliser SI.MULTIPLE pour convertir des codes en libellés ;
• lire un code d’erreur et en déduire la cause ;
• choisir entre tester la cause, SIERREUR et SI.NON.DISP ;
• éviter de masquer des anomalies réelles.

SI.CONDITIONS : un barème en une formule

=SI.CONDITIONS(test1; valeur1; test2; valeur2; …; VRAI; valeur_par_défaut)     (anglais : IFS)

Excel évalue les tests dans l’ordre et renvoie la valeur du premier test vrai. L’astuce VRAI; valeur_par_défaut à la fin joue le rôle du « sinon ».

Atlas Négoce applique à ses clients un barème de remise sur le montant hors taxes de la commande : 10 % à partir de 50 000, 7 % à partir de 20 000, 3 % à partir de 5 000, rien en dessous.

Figure 1 : six commandes. Repère 1 : la formule de la colonne C ; repère 2 : 5 000 exactement obtient 3 % car le test est « supérieur ou égal ».

=SI.CONDITIONS(B2>=50000;10%; B2>=20000;7%; B2>=5000;3%; VRAI;0)

Résultats :

Total des remises : 6 200 + 1 505 + 150 + 0 + 2 380 + 0 = 10 235,00.

L’ordre est décisif. Les tests vont du seuil le plus élevé au plus bas. Si l’on écrivait d’abord B2>=5000;3%, une commande de 62 000 serait aussi supérieure à 5 000 : Excel s’arrêterait au premier test vrai et appliquerait 3 %. Un barème s’écrit toujours en partant du cas le plus exigeant. Testez systématiquement les valeurs aux bornes : 4 999 (0 %), 5 000 (3 %), 19 999,99 (3 %), 20 000 (7 %).

Sans SI.CONDITIONS (Excel avant 2019), la même règle s’écrit :

=SI(B2>=50000;10%;SI(B2>=20000;7%;SI(B2>=5000;3%;0)))

Pour des barèmes qui comptent plus de quatre tranches, ni l’un ni l’autre n’est idéal : mieux vaut une table de correspondance et une recherche approchée (épisode 20), qui permet de modifier le barème sans toucher à la formule.

SI.MULTIPLE : convertir des codes

Quand on convertit une valeur exacte en une autre, SI.MULTIPLE (anglais : SWITCH) est plus simple :

=SI.MULTIPLE(B2; "AC";"Achats"; "VT";"Ventes"; "BQ";"Banque"; "Opérations diverses")

La dernière valeur, sans test, est le résultat par défaut. Pratique pour transformer les codes de journaux en libellés. Pour plus d’une dizaine de codes, préférez une table de correspondance et RECHERCHEX (épisode 20).

Les erreurs : lire le message

Une erreur n’est pas une panne, c’est un diagnostic. Voici ce que dit chaque code :

Traiter une erreur attendue : tester la cause

Marge brute en pourcentage du prix de vente : =(B2-C2)/B2. Pour un produit dont le prix de vente est nul (un échantillon gratuit), le résultat est #DIV/0!.

Figure 2 : la colonne D (repère 1) renvoie #DIV/0! pour le produit C ; la colonne E (repère 2) affiche « n.d. ».

Deux écritures donnent « n.d. » :

1. Tester la cause : =SI(B2=0;"n.d.";(B2-C2)/B2). La formule dit explicitement ce qu’elle veut éviter.
2. SIERREUR (anglais : IFERROR) : =SIERREUR((B2-C2)/B2;"n.d."). Plus courte, mais elle masque toutes les erreurs.

Les résultats pour les quatre produits : 35,0 % pour le produit A (1 200 de prix, 780 de coût), 8,9 % pour le B (450 contre 410), « n.d. » pour le C, 25,0 % pour le D (2 000 contre 1 500).

La seconde écriture est tentante, et c’est elle qui génère le plus de bugs invisibles. Si quelqu’un supprime la colonne C par mégarde, (B2-C2) devient #REF! et SIERREUR l’affiche… comme « n.d. », sans la moindre alerte. Règle pratique : testez la cause quand elle est connue ; réservez SIERREUR aux cas où l’erreur est attendue et sans conséquence, et ajoutez dans ce cas une colonne ou un compteur de contrôle qui dénombre les « n.d. » (=NB.SI(E2:E5;"n.d.")).

SI.NON.DISP : traiter uniquement « introuvable »

SI.NON.DISP (anglais : IFNA) ne traite que #N/A, l’erreur de « valeur non trouvée ». Elle est faite pour les recherches : un compte absent du plan comptable est une information, tandis qu’une autre erreur (par exemple #REF!) est une vraie anomalie qui doit rester visible.

Figure 3 : cinq comptes saisis, vérifiés dans un plan de quatre comptes. Le compte 6199 est inconnu.

La formule de la colonne B :

=SI.NON.DISP(EQUIV(A2; $E$2:$E$5; 0); "inconnu")

EQUIV renvoie la position du compte dans le plan (1 pour 3421, 4 pour 6111) ou #N/A. SI.NON.DISP remplace ce #N/A par « inconnu ». La colonne C en tire un diagnostic : =SI(ESTNUM(B2);"Compte du plan";"À créer ou à corriger"). Les cinq comptes donnent : 3421 → position 1, 4411 → 2, 6111 → 4, 6199 → inconnu, 5141 → 3. Un compte inconnu n’est pas masqué : il est signalé, et c’est exactement ce qu’un contrôle attend.

Les fonctions ESTNA, ESTERREUR et ESTERR testent le type d’erreur : =SI(ESTERREUR(A2);…) pour traiter tout type d’erreur, =SI(ESTNA(A2);…) pour #N/A seulement.

Bonnes pratiques

1. Ne cachez pas une erreur dont vous ignorez la cause. Comprendre l’erreur d’abord, la traiter ensuite.
2. Préférez tester la cause (SI(B2=0;…)) à SIERREUR, sauf pour un résultat sans importance.
3. Gardez un compteur d’anomalies en haut de la feuille : =NB.SI(plage;"À corriger").
4. Pour les barèmes, classez les seuils du plus élevé au plus bas et testez les bornes.
5. Documentez le défaut : « n.d. » veut dire que le diviseur est nul ; « inconnu » que le compte n’existe pas dans le plan.
6. Utilisez le volet Évaluation de formule (Formules › Évaluation de formule) pour suivre une formule étape par étape quand une erreur persiste.

À vous de jouer

Dans le classeur d’exercice :

1. Onglet Remises : complétez le taux, la remise et le net HT avec SI.CONDITIONS. Quel est le total des remises ?
2. Modifiez le montant de CMD-204 à 5 000 puis à 19 999. Que devient le taux ? Remettez 4 999.
3. Inversez l’ordre des tests (3 % à partir de 5 000 en premier) pour constater l’erreur de logique : quel taux obtient CMD-201 ?
4. Onglet Marges : remplacez SIERREUR par un test de la cause pour le produit C.
5. Onglet Comptes : ajoutez le compte 6199 au plan : que devient la ligne ?

Correction. Le total des remises est 10 235,00. CMD-204 à 5 000 obtient 3 % (150,00) ; à 19 999 obtient aussi 3 % (599,97). Avec les tests dans le mauvais ordre, CMD-201 (62 000) obtiendrait 3 % au lieu de 10 % : le barème doit toujours commencer par le seuil le plus élevé. La formule de remplacement est =SI(B4=0;"n.d.";(B4-C4)/B4). Après ajout du compte 6199 au plan (par exemple en E6, en étendant la plage de l’EQUIV à $E$2:$E$6), la ligne passe à « Compte du plan ».

À retenir

• SI.CONDITIONS lit les tests dans l’ordre : commencez par le seuil le plus élevé, terminez par VRAI; défaut.
• Une erreur est un message : #N/A valeur introuvable, #DIV/0! division par zéro, #VALEUR! mauvais type, #REF! référence supprimée.
• Testez la cause quand vous la connaissez ; SIERREUR masque tout, y compris les bugs.
• SI.NON.DISP ne traite que #N/A : c’est le bon réflexe pour une recherche.
• Un contrôle doit signaler les cas anormaux, pas les cacher.

Épisode 15 : les fonctions de texte, pour décomposer un numéro de compte, un code tiers ou un ICE sans retaper.