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 la mise en forme conditionnelle, pour faire apparaître les éléments hors normes d’un tableau de données.
Cas à résoudre : Éléments hors normes
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 avec un ET imbriqué
Pour faire apparaître un “HF” devant tous les items qui ne respectent pas au minimum l’un des standards (éléments hors normes), 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”)
Mise en forme conditionnelle pour faire ressortir les éléments hors normes
Voici comment on peut configurer une règle de mise en forme conditionnelle pour mettre en évidence les résultats hors normes.
Dans ce cas, on obtiendrait ce qui suit:
Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière. |
|---|
Mise en forme conditionnelle pour les autres éléments d’une même ligne
On peut aussi utiliser une règle de mise en forme conditionnelle pour faire ressortir les dimensions problématiques, comme suit:
Dans ce cas, on obtiendrait ce qui suit:
Conclusion
Si vous utilisez une formule SI imbriquée avec des règles de mise en forme conditionnelle pour faire ressortir les éléments hors norme d’un tableau, tel qu’illustré ci-dessus, 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”.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Pour couvrir plusieurs aspects avancés de la modélisation financière, qui sont indispensables afin de créer des modèles financiers dynamiques et flexibles, suivez notre formation Excel – Modélisation financière niveau 2.
Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Modélisation financière niveau 2 :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
Bonjour Sophie Marchand, je vous remercie pour cette démonstration cela me fais plus dans mes connaissances.
Cordialement
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.
Bonjour Jean-Marc,
Merci pour votre commentaire.
J’avais en effet déjà rédigé un article sur l’emploi de la fonction SOMMEPROD pour faire des sommes avec plusieurs critères. On peut le retrouver juste ici: http://lecfomasque.com/2011/08/29/excel-un-truc-puissant-dont-vous-ne-pourrez-plus-vous-passer/.
Au plaisir,
Sophie