Format conditionnel de valeur

Nous avons déjà parlé des formats conditionnels dans nos messages des dates suivantes : 20 oct 05, 20 juin 06, 24 juin 06, 18 juil 06 et 18 déc 06.

Aujourd’hui, à la demande d’un lecteur du blog, nous allons voir comment, dans une matrice (cela marchera a fortiori pour un vecteur), l’on peut créer un format conditionnel qui mette en bleu sur fond jaune les trois plus grandes valeurs et en rouge sur fond cyan les trois plus petites valeurs de la matrice.

Nous avons créé pour cela une matrice de 15 lignes et 5 colonnes, dans laquelle toutes les cellules sont définies par la formule =ent(100*alea()) qui tire un nombre au hasard entre 0 et 99.

Le tableau reproduit ci-dessous montre le résultat obtenu.

Le format conditionnel à créer est le suivant (pour la cellule A1). En fait, nous avons sélectionné le bloc A1:E15, puis défini le format ci-dessous en utilisant la commande « Mise en forme conditionnelle » du menu Format, et enfin validé par OK :

Remarque 1 – Quand on définit un format conditionnel, la référence de la cellule active est par défaut représentée en absolu. Il faut donc la rendre relative (ici, A1 au lieu de $A$1) dans un tel cas.

Remarque 2 – Notez que quatre cellules ont été formatées en rouge sur fond cyan, au lieu de trois, car il y avait deux ex-aequo pour la troisième valeur la plus basse.

Une protection par validation

La protection assurée par Excel grâce à la commande « Protection » du menu Outils ne représente qu’une sécurité très limitée. En quelques minutes sur Internet, vous trouverez des logiciels de déprotection pour Excel, Word, Powerpoint, ou d'autres logiciels encore.

En outre, une feuille protégée présente de nombreux dysfonctionnements. Pour n’en citer qu’un, les filtres automatiques des bases de données ne fonctionnent plus et on ne peut pas les activer ni les désactiver.

Si donc vous souhaitez protéger – dans une feuille Excel – certaines cellules contre des actions maladroites (on ne peut rien faire contre des actions malveillantes), il est bien plus efficace d'utiliser une protection par validation de cellule.

Pour cela, activez la cellule concernée, appelez la commande « Validation » du menu Données, et réglez-la comme dans la copie d’écran ci-dessous :

Validez en cliquant dans le bouton « OK » et le tour est joué ! Vous pouvez le vérifier aussitôt en essayant de saisir n’importe quoi : toute saisie est alors refusée…

Pourquoi cela fonctionne-t-il ? Tout simplement parce que la formule « >1 » n’aboutit jamais à un résultat VRAI. Une formule telle que « B2>1 » peut donner un résultat VRAI, mais pas tout simplement « >1 ».

Remarque 1 – Certes, nous aurions pu faire plus simple encore en entrant la formule « 0 », puisque 0 dans Excel correspond à FAUX. Mais je trouve « >1 » plus esthétique !

Remarque 2 – Attention ! La protection assurée par cette solution bloque toute saisie. En revanche, un contrôle de validation, quel qu’il soit, ne saurait vous protéger contre le collage dans la cellule ainsi « protégée » du contenu d’une autre cellule.

La validation de cellule (b)

Nous avons introduit la validation de cellules dans le dernier message, sans toutefois commenter les options d’autorisation du dernier écran. Nous voyons dans cet écran que nous pouvons valider :
- un nombre entier, entre deux bornes
- un nombre décimal, entre deux bornes
- une liste (nous en reparlons plus bas)
- une date, entre deux dates
- une heure, entre deux heures
- une longueur de texte, entre deux bornes
- un critère personnalisé (voir plus bas)

Voici ce que devient l’écran quand on sélectionne l’option « Nombre entier » :

La validation par liste

La validation par liste s’effectue soit en identifiant la liste des valeurs possibles − par exemple en entrant Oui;Non −, soit en sélectionnant un vecteur, horizontal ou vertical, contenant la liste des valeurs possibles.

Remarque 1 − On ne peut alors faire référence ni à un bloc d’une feuille différente, ni à une matrice (il faut que ce soit un vecteur horizontal ou vertical).

Remarque 2 − En revanche, rien ne vous empêche de définir la « source » de validation, c’est-à-dire le bloc de cellules en question, par une formule telle que =Jules, où Jules est un nom faisant référence à un vecteur d’une autre feuille !

La validation personnalisée

Nous avons là une fonctionnalité d’une puissance quasiment illimitée, que seuls peu d’utilisateurs d’Excel connaissant vraiment ou – a fortiori – maîtrisent.

Nous pouvons entrer n’importe quelle formule, avec des références absolues, relatives ou mixtes. Seules les saisies pour lesquelles cette formule est valide (donc aboutit à VRAI) seront alors valides.

Supposons par exemple que vous vous trouviez dans la cellule B1 et que vous ne vouliez accepter que des nombres impairs. Vous entrez alors la formule : =B1=2*ent(B1/2)+1.

Vous pouvez par exemple, dans une colonne « Crédit utilisé par le client », entrer une formule qui bloque la saisie de tout emprunt dépassant un montant défini à l’aide d’autres renseignements concernant ce client.

Remarque 3 − On retrouve dans ces formules le même potentiel que dans les formules servant à définir les formats personnalisés.

La validation de cellule (a)

Nous avons déjà parlé de la commande « Validation » du menu Données le 22 et le 26 septembre 2006, sans en dire beaucoup de choses.

Cette commande, apparue avec Excel 98, est particulièrement précieuse. Quand elle est apparue, j’ai créé une application – un système expert de diagnostic – dans laquelle elle était indispensable, et qui a amené un grand groupe à acquérir plus tôt qu’il ne l’aurait désiré des licences de cette version pour pouvoir utiliser cette application. Si vous voulez en savoir plus à ce sujet, lisez l’article « Spreadsheet-based Professional Modelling » :
http://ite.pubs.informs.org/Vol4No2/Thiriez/

Sélectionnez la cellule à laquelle vous voulez attribuer un format conditionnel, puis le menu Données et enfin la commande « Validation ». Vous obtenez aussitôt l’écran suivant :

L’onglet « Options » permet de spécifier les conditions que l’on souhaite imposer à la saisie des données dans cette cellule.

L’onglet « Message de saisie » sert à définir un message d’assistance à la saisie, qui apparaît aussitôt que l’on sélectionne la cellule en question, à condition que la case « Quand la cellule est sélectionnée » reste cochée (on ne voit d’ailleurs pas bien à quoi cet onglet peut servir si la commande n’est pas cochée).

Grâce à l’onglet « Alerte d’erreur », enfin, vous choisissez le mode d’action à entreprendre selon le « Style » que vous aurez sélectionné :

• Alerte : le message d’alerte apparaît si la saisie n’est pas conforme, et l’utilisateur ne peut rien faire d’autre que de recommencer…

• Avertissement : le message d’alerte apparaît quand la saisie n’est pas conforme et l’utilisateur a la possibilité de la valider quand même, de l’infirmer ou d’annuler.

• Informations : le message d’alerte apparaît quand la saisie n’est pas conforme et l’utilisateur a la possibilité de la valider quand même ou de l’annuler.

Remarque – On ne voit pas bien l’intérêt de la nuance entre les options
« Avertissement » et « Informations ». Cela aurait été probablement plus clair si, dans ce dernier cas, on affichait le message puis que l’on validait alors avec un unique bouton « OK »…

Dans le premier onglet, le menu déroulant « Autoriser » donne le choix entre plusieurs critères de validation, que nous commenterons dans le prochain article :

Paiement à 15 ou à 45 jours

Certains lecteurs m’ont informé par mail de leur difficulté à trouver la formule permettant de gérer les paiements à 15 ou 45 jours, malgré les explications du dernier paragraphe de ma rubrique précédente :

« Si des délais de paiement de 15 ou 45 jours sont envisageables, il suffit de faire un test pour vérifier si c’est le cas avec si(mod($B$1;30)>0;...) et, quand ce test est positif, de faire la moyenne des deux valeurs concernées. »

La formule à placer en B5 n’est pas, je l’avoue humblement, la plus simple qui soit...

En effet, il faut déjà traiter séparément le cas des multiples de 30 et celui des multiples impairs de 15. Mais il y a aussi un autre piège : il faut vérifier, dans ce dernier cas, qu’il y ait bien une valeur dans la seconde colonne à laquelle on fait référence !

La formule utilisée en B5 est la suivante :
=si(colonne()-$B$1/30>1;si(mod($B$1;30)=0;decaler(B4;0;-$B$1/30);
0,5*decaler(B4;0;1-$B$1/30)+si(colonne()-$B$1/30>2;
0,5*decaler(B4;0;-$B$1/30);0));"")

Remarque – Cette formule aurait été plus complexe encore si je n’avais pas analysé la façon dont decaler() fonctionne quand un argument n’est pas entier. Sinon, il m’aurait fallu utiliser ent(-$B$1/30) ou (-$B$1+15)/30 à la place de -$B$1/30 et modifier en conséquence la constante de décalage.

En outre, si l’on pense à utiliser la moyenne, cela évite le test de l’avant-dernière ligne de la formule ci-dessus. En effet, on ne peut pas ajouter à une valeur une cellule contenant du texte, mais on peut faire la moyenne de deux cellules dont l’une contient du texte !

La formule la plus simple pour la cellule B5 est donc :
=si(colonne()-$B$1/30>1;si(mod($B$1;30)=0;decaler(B4;0;-$B$1/30);
moyenne(decaler(B4;0;1-$B$1/30):decaler(B4;0;-$B$1/30);0));"")

Comme quoi, même pour un petit modèle comptable de prévision des encaissements, il faut se creuser les méninges pour éviter le recours à des formules monstrueuses !

Introduction à la fonction Decaler()

La fonction decaler() est une fonction précieuse d’Excel qui permet de récupérer le résultat obtenu quand on décale – par rapport à une cellule de référence – d’un nombre de lignes et d’un nombre de colonnes donnés. Nous l’avons déjà utilisée dans nos rubriques du 15 novembre 2005 et du 11 décembre 2005.

Sa syntaxe est = decaler(référence;nb. lignes; nb.colonnes)

Nous en voyons un exemple ci-dessous :

En B1, nous avons entré le délai de paiement – ici la valeur 30 – et nous avons donné à cette cellule le format personnalisé 0" jours".

La formule de B5, recopiée ensuite vers la droite, est :
=si(colonne()-$B$1/30>1;decaler(B4;0;-$B$1/30);"")

Si maintenant nous passons le délai de paiement à 60 jours, le résultat exact apparaît aussitôt :

Et s’il y a un paiement à 15 jours ou à 45 jours ?

Si des délais de paiement de 15 ou 45 jours sont envisageables, il suffit de faire un test pour vérifier si c’est le cas avec si(mod($B$1;30)>0;...) et, quand ce test est positif, de faire la moyenne des deux valeurs concernées.