FONCTION SI, CONDITIONS ET CRITÈRES – EXCEL 2007
Avec le support précédent nous avons appris à mettre en oeuvre les fonctions SI et leur imbrication
Néanmoins les calculs étaient basés uniquement sur des valeurs et critères statiques comme l‛il-lustre cette figure.
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.
Vous trouverez le classeur dans le dossier Sources.
PANNEAU DE CONTRÔLE, VALEURS DYNAMIQUES
● Sur les cellules G1 à H4, réalisez 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 suf-fira de modifier les contraintes (Conditions de passage, appréciations) pour que les résultats de calcul se met-tent à jour.
Maintenant, il s‛agit de mettre à jour 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 la formule puisse être reproduite sur toutes les lignes de la colonne.
Comment faire référence à une cellule Excel ?
FONCTIONS SI ET RÉFÉRENCES ABSOLUES
- Sélectionnez la première cellule du calcul (E7),
- Enfoncez la touche F2 pour passer en mode saisie,
- Sélectionnez la valeur 10 du premier critère,
- Et cliquez sur la cellule correspondante du panneau G2,
Ainsi nous remplaçons le nombre 10 du critère par une valeur dynamique.
- Enfoncez la touche F4 du clavier pour figer cette cellule,
- Sélectionnez ensuite le texte L’élève passe avec les guillemets,
- Cliquez sur la cellule H2 du panneau pour le remplacer,
- Enfoncez la touche F4 du clavier pour figer cette référence,
…
- Terminez toute la formule en suivant le même procédé de manière à remplacer toutes les valerus statiques par les cellules du panneau de contrôle,
- Une fois la formule terminée, validez par CTRL + Entrée,
- Puis tirez la poignée de la cellule pour reproduire le calcul sur toute la colonne E,
- N’hésitez pas à utiliser la balise active qui se déclenche (petit bouton) pour rétablir la mise en forme.
Voici le résultat que vous devez obtenir.
Pour constater que désormais tout est bien dynamique, nous allons modifier quelques contraintes du problème.
- Modifiez 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.
- Modifiez maintenant le texte l’élève passe par Admis,
- Puis, modifiez 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électionnez toutes les cellules du calcul (E7 à E15),
- Déroulez le bouton Mise en forme conditionnelle du ruban Accueil,
- Cliquez sur Gérer les règles tout en bas,
- Dans la boîte de dialogue, cliquez sur Modifier les règles,
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électionnez = »L’élève redouble » dans la zone de saisie,
- Cliquez sur la cellule H4 du panneau de contrôle,
Celle-ci est automatiquement figée avec les Dollars par Excel.
- Cliquez sur Ok pour valider la première boîte de dialogue,
- Cliquez de nouveau sur Ok pour valider la seconde.
Cette fois le format conditionnel est lui aussi dynamique. Vous pouvez faire l‛essaie en modifiant en cellule H4 le texte Recalé en Refusé. Vous constatez que le format conditionnel prend en compte le changement.