Apprendre à poser des raisonnements dans des feuilles de calcul Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Fonction Si – Calculs et conditions – Critères avancés
Nous proposons ici d'aborder l'utilisation de la fonction de raisonnement dans Excel, la fonction Si, au travers d'un cas pratique très simple mettant en oeuvre seulement des valeurs et critères statiques. Bien sûr, l'objectif d'Excel est de créer des feuilles complètement dynamiques où les données se mettent automatiquement à jour en fonction des hypothèses placées dans un tableau de bord. Puis nous aborderons la notion de fonction Si combinée avec l'utilisation des références absolues par le biais d'un panneau de contrôle.

• Télécharger le classeur fonction-si-calcul-condition.xlsx, en cliquant sur ce lien,
• L'ouvrir dans Excel,

Ce tableau est la synthèse d'un conseil de classe. Dans les premières colonnes apparaissent les prénoms des élèves, en colonne D leur moyenne générale respective et enfin en colonne E, l'avis du conseil à définir.

Fonction Si - Construction
Objectif : Partons sur un cas simple. Dans la colonne Avis du conseil doit apparaître le texte L'élève passe si sa moyenne générale est supérieure ou égale à 10 et L'élève redouble dans le cas contraire. La fonction Si est plus complexe que les formules de calcul que nous avons abordées jusqu'alors. Elle nécessite trois arguments. Le premier argument est le critère, soit une cellule que l'on compare à une valeur. Le deuxième argument correspond à l'action que doit effectuer la formule lorsque le critère est vérifié. Le troisième élément correspond à l'action que doit effectuer la formule lorsque le critère n'est pas vérifié. Chaque argument étant séparé par un point-virgule. Littéralement : =SI(Critère;Alors;Sinon) Si la moyenne est >= 10 (Critère), écrire le texte L'élève passe (Alors) sinon écrire le texte L'élève redouble (Sinon).

• Sélectionner toutes les cellules du calcul (E7 à E15),
• Taper =pour initialiser le calcul,
• Taper le nom de la fonction SI,
• Ouvrir la parenthèse,

Comme vous le constatez, une info-bulle apparaît confirmant que la fonction requiert trois arguments. Ces info-bulles sont apparues depuis la version 2003. Lorsque vous ouvrez la parenthèse d'une fonction et qu'aucune info-bulle ne s'affiche, vous savez d'ores et déjà que le calcul comporte une erreur. Sans doute un souci dans le nom de la fonction.

• Cliquer sur la première moyenne générale (D7), pour poser le critère,
• Taper le symbole supérieur (>) suivi du signe égal (=),
• Puis taper 10,

Nous venons de spécifier le critère. Est-ce que la moyenne est supérieure ou égale à 10 ?

• Taper un point-virgule (;) pour passer à la suite,

Vous notez que le deuxième argument de l'info-bulle se met en gras. Ainsi vous savez en temps réel ce que vous devez indiquer à la fonction SI. Nous devons maintenant écrire le texte L'élève passe. Tout texte doit être encadré de guillemets dans une fonction Excel.

• Ouvrir les guillemets (Touche 3 du clavier),
• Taper le texte L'élève passe,
• Fermer les guillemets (Touche 3 du clavier),
• Taper un point-virgule (;),
• Ouvrir de nouveau les guillemets,
• Taper le texte L'élève redouble,
• Fermer les guillemets,
• Enfin ne pas oublier de fermer la parenthèse,
• Valider le calcul par CTRL + Entrée.

Celui-ci est reporté sur toute la colonne et chaque avis du conseil correspond bien à la moyenne générale en regard. Notez que si vous modifiez l'une des moyennes, l'avis du conseil s'adapte automatiquement. Le résultat est intéressant mais pas vraiment satisfaisant. La fonction Si dans son expression la plus simple ne permet de poser qu'un seul critère et donc de n'envisager que deux possibilités ! Imaginons que le conseil souhaite faire passer l'élève dont la moyenne est supérieure ou égale à 10, redoubler l'élève dont la moyenne est inférieure ou égale à 9,5 et débattre pour l'élève dont la note est proche de la moyenne, entre 9,5 et 10. Ce cas de figure envisage trois possibilités. Nous devons pour cela poser deux critères. La solution proposée par Excel est l'imbrication de fonction SI.

Imbrication de SI – Augmenter le nombre de conditions

• Supprimer les calculs précédemment réalisés,
• Sélectionner les cellules E7 à E15,
• Taper le symbole égal (=) pour initialiser le calcul,
• Taper SI et ouvrir la parenthèse,
• Cliquer sur la première moyenne (D7),
• Taper >=,
• Puis taper 10 suivi d'un point-virgule (;),
• Ouvrir les guillemets (Touche 3 du clavier),
• Taper le texte L'élève passe,
• Fermer les guillemets (Touche 3 du clavier),
• Taper un point-virgule (;),

A ce stade la formule ne change pas. C'est à partir de ce point que nous devons poser un nouveau critère sous peine de ne pouvoir envisager que deux solutions. Pour cela, nous allons imbriquer une deuxième fonction SI à l'intérieur de la première au niveau du SINON de la première fonction SI.

• Taper SI et ouvrir la parenthèse,
• Sélectionner de nouveau la moyenne générale (D7),
• Taper >=,
• Taper 9,5,

Ce qui en ce point signifie compris entre 9,5 et 10. En effet le compris entre ne s'exprime pas dans une formule Excel, il se déduit par l'imbrication des critères. Si Excel lit jusqu'au deuxième SI de la formule, cela veut dire que le premier critère >=10 n'est pas vérifié. S'il vérifie maintenant que la moyenne est >=9,5, nous saurons que la note est bien comprise entre les deux.

• Taper un point-virgule (;),
• Ouvrir les guillemets,
• Taper le texte Cas à étudier,
• Fermer les guillemets,
• Taper un point-virgule (;),
• Ouvrir de nouveau les guillemets,
• Taper le texte L'élève redouble,
• Fermer les guillemets,
• Enfin ne pas oublier de fermer deux parenthèses,
• Valider le calcul par CTRL + Entrée.

En effet le dernier cas se déduit des deux premiers critères. S'ils ne sont pas vérifiés, nous savons que la note n'est ni au-dessus de 10, ni comprise entre 9,5 et 10. Dans ce cas, pas besoin d'une nouvelle fonction SI, il suffit d'écrire l'action L'élève Redouble dans la partie SINON de la deuxième fonction. Remarque : Nous fermons deux parenthèses car la seconde fonction SI est imbriquée dans la première. Il faut fermer autant de parenthèses qu'il y en a d'ouvertes.

Vous constatez que les résultats sont reproduits sur l'intégralité de la colonne et que les trois possibilités sont bien envisagées. Vous pouvez continuer l'imbrication de fonctions SI de la même façon. Lorsque N possibilités se présentent, vous utilisez N-1 fonctions SI. Ici nous avions 3 possibilités et nous avons employé 2 fonctions SI. A partir d'un certain nombre néanmoins, la syntaxe se complique et il sera temps de se demander si l'utilisation de la fonction SI est judicieuse. Nous verrons plus loin que d'autres fonctions Excel permettent de répondre pertinemment au problème lorsque le nombre de possibilités est trop grand. Il convient maintenant de faire ressortir en couleur les élèves qui redoublent pour avoir une idée de la proportion de réussite par le biais d'une lecture rapide de la feuille. Pour cela, nous mettons en oeuvre le format conditionnel.

Format conditionnel des résultats
Les élèves qui redoublent doivent apparaître en couleur.

• Sélectionner toutes les cellules du calcul (E7 à E15),
• Dérouler le bouton Mise en forme conditionnelle du ruban Accueil,
• Pointer sur Règles de mise en surbrillance des cellules,
• Cliquer sur Egal à,
• Dans la zone de saisie, taper l'élève redouble,

Pour la reconnaissance de la règle, attention de taper le texte exactement comme vous l'avez saisi dans la formule. Vous notez que les cellules concernées sont instantanément mises en surbrillance,

• Dérouler la liste déroulante sur la droite,
• Cliquer sur Texte rouge,
• Valider en cliquant sur Ok.

La lecture est désormais plus efficace. En un clin d'oeil nous remarquons qu'il y a deux redoublements. Bien sûr ce format est dynamique. Il suffirait de modifier l'une des moyennes pour faire redoubler un nouvel élève et constater l'apparition d'une autre cellule en rouge. Dernier petit point, vous notez la présence d'une cellule verte en bas de la colonne E. Cette cellule doit afficher le nombre d'étudiants admis. Nous savons faire des sommes sur des valeurs numériques mais qu'en est il lorsqu'il s'agit de compter des cellules de texte ? Excel propose une fonction de dénombrement qui s'appelle NB.SI.

Dénombrement selon critère
La fonction NB.SI attend deux arguments. Le premier correspond à la plage de cellules sur laquelle elle doit compter. Le second correspond au critère pour savoir quoi compter.

• Sélectionner la cellule E18,
• Taper = pour lancer le calcul,
• Taper NB.SI,

Attention de ne pas réaliser le point (.) de la fonction avec le pavé numérique qui conduira à une virgule(,) chez nous les français.

• Ouvrir la parenthèse,

Notez une fois de plus l'apparition instantanée de l'info-bulle qui confirme que la fonction attend deux indications.

• Sélectionner les cellules E7 à E15,
• Taper un point-virgule (;),
• Ouvrir les guillemets, le critère est un texte,
• Taper fidèlement L'élève passe,
• Fermer les guillemets,
• Fermer la parenthèse,
• Valider le calcul par Entrée.

Le résultat retourné est 5. Résultat parlant pour celui qui conçoit le calcul mais pas forcément pour l'utilisateur à plus forte raison si le nombre d'étudiants est plus important. Pour cela nous allons mettre en oeuvre la concaténation. L'objectif est d'afficher dans la cellule 5 admissions. 5 étant toujours le résultat dynamique du calcul assemblé avec un texte d'explication.

• Sélectionner la cellule E18,
• Enfoncer la touche F2 du clavier pour forcer la saisie,
• Enfoncer la touche Fin du clavier pour placer le curseur à la fin de la formule,
• Taper & ' Admissions',
• Valider par Entrée.

Nous assemblons le résultat numérique au texte Admissions précédé d'un espace.

Jusqu'à maintenant les calculs sont basés uniquement sur des valeurs et critères statiques. Dans la fonction Si, la cellule D7 est comparée à une valeur statique (10), la première action de la fonction SI est un texte statique (L'élève passe). Pour que ce modèle de conseil de classe puisse s'appliquer à tous les établissements avec leurs propres conditions d'admissions et leurs propres appréciations, toutes les valeurs doivent être dynamiques. Pour cela nous allons concevoir un panneau de contrôle auquel les formules feront référence.

Panneau de contrôle, Valeurs dynamiques et Références absolues

• Sur les cellules G1 à H4, réaliser le panneau de contrôle présenté par la figure,

En plaçant toutes les valeurs dans un tableau externe, les formules feront référence à des cellules dont il suffira de modifier les contraintes (Conditions de passage, appréciations) pour que les résultats de calcul se mettent à jour. Maintenant, il s'agit d'adapter les formules de la colonne E. Toutes les valeurs statiques doivent être remplacées par les cellules correspondantes du panneau de contrôle. Ces cellules doivent être figées (Touche F4) du clavier pour que le calcul puisse être reproduit sur toutes les lignes de la colonne.

Fonctions SI et Références absolues

• Sélectionner la première cellule du calcul (E7),
• Enfoncer la touche F2 pour passer en mode saisie,
• Sélectionner la valeur 10 du premier critère,
• Et cliquer sur la cellule correspondante du panneau G2,

Ainsi nous remplaçons le nombre 10 du critère par une valeur dynamique.

• Enfoncer la touche F4 du clavier pour figer cette cellule,
• Sélectionner ensuite le texte L'élève passe avec les guillemets,
• Cliquer sur la cellule H2 du panneau pour le remplacer,
• Enfoncer la touche F4 du clavier pour figer cette référence,
• Terminer toute la formule en suivant le même procédé de manière à remplacer toutes les valeurs statiques par les cellules du panneau de contrôle,
• Une fois la formule terminée, valider par CTRL + Entrée,
• Puis tirer la poignée de la cellule pour reproduire le calcul sur toute la colonneE,

N'hésitez pas à utiliser la balise active qui se déclenche (petit bouton) pour rétablir la mise en forme.

Pour constater que désormais tout est bien dynamique, nous allons modifier quelques contraintes du problème.

• Modifier la valeur 10 du panneau de contrôle en 12,
• Changer la valeur 9,5 à 10,5,

Vous le remarquez, tous les résultats se mettent instantanément à jour dans la colonne E. Le format conditionnel précédemment posé renforce cette effet puisque tous les redoublements s'affichent en rouge.

• Modifier maintenant le texte l'élève passe par Admis,
• Puis, modifier le texte l'élève redouble par Recalé,

Là encore, nous constatons que tous les résultats se mettent instantanément à jour ce qui n'aurait pas été possible avec les fonctions statiques du début de l'exercice. Néanmoins ce n'est pas le cas du format conditionnel. Ce dernier était calibré pour afficher en rouge tous les textes L'élève redouble. Comme nous avons modifié la contrainte dans le panneau de contrôle ce format n'est plus fonctionnel. Nous devons le rendre lui aussi dynamique.

Format conditionnel dynamique

• Sélectionner toutes les cellules du calcul (E7 à E15),
• Dérouler le bouton Mise en forme conditionnelle du ruban Accueil,
• Cliquer sur Gérer les règles tout en bas,
• Dans la boîte de dialogue, cliquer sur Modifier la règle,

La règle en cours (Valeur de la cellule...égale à...='L'élève redouble') apparaît dans la section inférieure. Il suffit de remplacer le critère statique 'L'élève redouble' par la cellule du panneau de contrôle.

• Sélectionner ='L'élève redouble' dans la zone de saisie,
• Cliquer sur la cellule H4 du panneau de contrôle,

Celle-ci est automatiquement figée avec les Dollars par Excel.

• Cliquer sur Okpour valider la première boîte de dialogue,
• Cliquer de nouveau sur Ok pour valider la seconde.

Cette fois le format conditionnel est lui aussi dynamique. Vous pouvez faire l'essai en modifiant en cellule H4 le texte Recalé en Refusé. Vous constatez que le format conditionnel prend en compte le changement.


Tableau d'échéances – Pratique de la fonction SI
Nous proposons un exercice relativement simple permettant de pratiquer les fonctions conditionnelles SI et leurs imbrications. Ici, nous travaillons en valeurs statiques pour nous concentrer sur la construction de ces formules.

• Télécharger le classeur de l'exercice en cliquant sur ce lien,
• L'ouvrir dans Excel,

Il s'agit d'un tableau d'échéances des paiements de factures de clients recensés dans la première colonne. Apparaissent pour chacun d'entre eux le montant de la facture à payer et l'état en cours de leur versement. Trois cas se présentent : Certains clients ont réglé leur facture, d'autres n'ont pas tout payé et d'autres encore ont trop payé. C'est pourquoi après avoir calculé le reste à payer, l'exercice propose d'ajouter le commentaire soldé, ou avoir ou relance dans la colonne Observations selon le cas. Enfin, les mauvais payeurs devront être pénalisés à hauteur de 5% du montant de la facture dans la colonne Pénalités.

Calcul du reste - Soustraction
Le reste à payer est la différence entre le montant de la facture et le montant versé :

• Sélectionner toutes les cellules du reste (E5 à E15),
• Taper = pour initialiser le calcul,
• Cliquer sur la première cellule de la facture (C5),
• Taper le symbole moins (-) pour la soustraction,
• Cliquer sur la première cellule du versement (D5),
• Valider par CTRL + Entrée pour reproduire le calcul,

Vous obtenez tous les restes à payer en regard des clients. Certains sont positifs, d'autres négatifs et d'autres encore sont nuls. Si des symboles dièse (#) apparaissent, cela signifie que la largeur de colonne est insuffisante pour afficher l'intégralité des valeurs numériques. Il ne s'agit pas d'une erreur.

• Placer le curseur de la souris à l'intersection des étiquettes de colonne E et F,

Il se transforme en une double flèche noire.

• Double cliquer sur cette intersection.

Vous remarquez que la colonne est désormais suffisamment large. Le double clic à l'intersection des étiquettes est une méthode qui permet d'ajuster les colonnes et les lignes à leur contenu.

Observation - Problème - Fonction Si
Nous devons résoudre le problème de la colonne Observations. Nous devons envisager trois cas (Mauvais payeur : Relance, Trop payé : Avoir, Paiement exact : Soldé). Comme nous l'avons expliqué précédemment, si N cas se présentent, nous devons utiliser N-1 fonctions si. Donc ici nous avons besoin de 3-1=2 fonctions SI. Pour savoir si le client est un bon ou mauvais payeur, la solution la plus simple consiste à regarder si le reste à payer est négatif ou positif.

• Sélectionner toutes les cellules de la colonne Observations (F5 à F15),
• Taper = pour lancer le calcul,
• Taper SI et ouvrir la parenthèse,
• Sélectionner la première cellule du reste (E5),
• Taper >0 pour savoir si c'est un mauvais payeur,
• Taper un point-virgule (;),

Nous venons de poser le critère à vérifier si le reste à payer est positif ce qui signifierait que le client est un mauvais payeur (Relance).

• Taper alors le texte Relance entre guillemets,
• Tapez un point-virgule (;),

Nous arrivons dans la partie Sinon de la fonction SI. Comme il reste deux possibilités (Avoir et Soldé), une seule fonction Si est insuffisante, nous devons en imbriquer une autre.

• Taper de nouveau SI et ouvrir la parenthèse,
• Sélectionner la première cellule du reste (E5),
• Taper le critère <0 pour savoir si c'est un mauvais payeur,
• Taper un point-virgule (;),

Nous venons poser le deuxième critère qui, s'il est vérifié signifie que le client a trop payé et que nous lui devons de l'argent (Avoir).

• Taper alors le texte Avoir entre guillemets,
• Taper un point-virgule (;),

Nous arrivons dans la partie SINON de la deuxième fonction SI. Si la lecture de la formule parvient jusqu'à ce point, cela signifie que ni le premier critère n'est vérifié, ni le second. Si le reste n'est ni positif, ni négatif, par déduction, il est forcément nul (Soldé).

• Taper le texte Soldé entre guillemets,
• Fermer les deux parenthèses des deux fonctions SI,
• Valider par CTRL +Entrée pour reproduire le calcul.

La formule finale est la suivante : =SI(E5>0;'Relance';SI(E5<0;'Avoir';'Soldé')). Vous pouvez noter que les observations sont cohérentes et surtout dynamiques puisqu'il suffirait par exemple qu'un mauvais payeur ajoute la différence pour que son statut passe à Soldé.

Calcul des pénalités, si
Tous les clients ne doivent pas être pénalisés, mais seulement les mauvais payeurs. Les mauvais payeurs ont un reste positif. C'est donc le critère le plus simple que nous pouvons poser pour savoir si nous calculons le montant de la pénalité ou non. C'est tout naturellement que la fonction SI s'impose à nous. Et pour une fois nous allons intégrer un calcul dans la fonction conditionnelle.

• Sélectionner toutes les cellules de la colonne Pénalités (G5 à G15),
• Taper = pour initialiser le calcul,
• Taper SI et ouvrir la parenthèse,
• Cliquer sur la première cellule du reste (E5),
• Taper le critère >0 pour savoir si c'est un mauvais payeur,
• Taper un point-virgule (;),
• Cliquer sur la cellule de la facture (C5),
• Puis taper *0,05,

Pas de guillemets ici car si le critère est vrai nous calculons directement le montant de la pénalité en multipliant (*) le montant de la facture (C5) par 5% (0,05).

• Taper un point-virgule (;),

Nous arrivons au sinon de la fonction SI. Cette branche doit toujours être remplie, sous peine de générer une erreur, même si a priori il n'y a rien à faire. Ici il faut expliquer que dans le cas contraire (Bon payeur), il faut laisser la cellule vide, ce qui se traduit par deux guillemets. Les guillemets permettent d'insérer du texte. S'il n'y a rien entre le guillemet ouvrant et fermant, le résultat est une cellule vide.

• Ouvrir et fermer les guillemets,
• Fermer la parenthèse de la fonction SI,
• Valider le calcul par CTRL + Entrée.

Seuls les mauvais payeurs sont en effet pénalisés. Enfin, vous pouvez éventuellement utiliser la fonction Nb.Si pour compter le nombre de mauvais payeurs en bas de la colonne Observations. L'explication sur cette fonction de dénombrement a été donnée plus haut.