Excel: Identifier les éléments hors normes

Publié le 02 décembre 2013
par Sophie Marchand M.Sc., CPA, CGA, MVP
Courbes de tendance

Excel: Identifier les éléments hors normes

Il y a quelques temps, un lecteur m’a demandé comment il pouvait faire apparaître automatiquement des avertissements dans son tableau d’analyse, lorsque certaines mesures n’étaient pas conformes à des standards établis. L’article suivant montre donc comment utiliser la fonction Si (If en anglais) avec des imbrications de Et (And en anglais) et comment utiliser le format conditionnel, pour faire apparaître les éléments hors norme d’un tableau de données.

.

Cas à résoudre

Le but est de faire apparaître un message, dans la colonne F, pour faire ressortir tous les items HF, i.e. Hors format.

.

Fonction Si (if)

.

Fonction SI avec un ET imbriqué

Pour faire apparaître un “HF” devant tous les items qui ne respectent pas au minimum l’un des standards, j’ai utilisé la fonction SI avec un ET imbriqué, tel qu’illustré ci-dessous. Ainsi, quand tous les standards sont respectés, on observe la mention “Format” et quand au minimum l’un d’entre eux n’est pas respecté, on observe la mention “HF” pour Hors format. La formule s’écrit comme suit:

.

En anglais =IF(AND(C7>=$C$4,C7<=$C$5,D7>=$D$4,D7<=$D$5,E7>=$E$4,E7<=$E$5),”Format”,”HF”) ou;

En français =SI(ET(C7>=$C$4;C7<=$C$5;D7>=$D$4;D7<=$D$5;E7>=$E$4;E7<=$E$5);”Format”;”HF”)

.

Fonction SI (if)

.

Format conditionnel pour faire ressortir le résultat final

On peut également utiliser le format conditionnel pour faire ressortir tous les éléments hors normes.

.

Format conditionnel

.

Dans ce cas, on obtiendrait ce qui suit:

.

Format conditionnel

.

Format conditionnel pour faire ressortir les éléments perturbateurs

On peut aussi utiliser le format conditionnel pour faire ressortir les dimensions problématiques, comme suit:

.

Format conditionnel

.

Dans ce cas, on obtiendrait ce qui suit:

Format conditionnel

.

Conclusion

Si vous utilisez une formule de Si imbriquée avec du format conditionnel pour faire ressortir les éléments hors norme d’un tableau, tel qu’illustré ci-haut, vous serez toujours avisé instantanément des données hors normes et vous pourrez réagir en conséquence. Bien entendu, pour automatiser le tout, il s’agirait de copier votre formule et votre format conditionnel aux nouvelles lignes de données qui viendront s’ajouter ou d’utiliser la fonctionnalité “Mettre sous forme de tableau”.

 

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

3 réflexions sur “Excel: Identifier les éléments hors normes”

  1. Bonjour,
    Dans le cas où il y a plusieurs conditions à vérifier, au lieu d’avoir plusieurs SI et ET imbriqués, on peut utiliser la fonction SOMMEPROD.

    Soit ceci (fait le total des conditions respectées, le * est équivalent à ET) :
    =SI(SOMMEPROD((D5:F5>=$D$3:$F$3)*(D5:F5<=$D$4:$F$4)*1)=3,"Format","HF")

    ou encore ceci (fait le total des conditions non respectées, le + est équivalent à OU):
    =SI(SOMMEPROD(((D5:F5$D$4:$F$4))*1),”HF”,”Format”)

    La deuxième version retourne 0 si toutes les conditions sont respectées. Alors que la première version retourne une valeur égale au nombres de conditions respectées. L’avantage de la première version est qu’on n’a pas besoin d’aller modifier la formule (=3 ou =4 ou =X) si on change le nombre de conditions.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top