Compacité/Lisibilité d’une formule

Quand on cherche à construire une formule un tant soit peu complexe avec Excel, on a fréquemment le choix entre une formule plus compacte – mais moins facile à comprendre – et une formule plus facilement compréhensible – mais plus lourde.

Dans l’exemple que nous étudions aujourd’hui, nous devons calculer la rémunération de personnes dont le tarif horaire est indiqué en colonne A et le nombre d’heures de travail dans la journée dans la colonne B.

La rémunération de toute heure au-delà de 8 heures est majorée de 50%, et celle de toute heure au-delà de 12 heures est doublée.


La formule en C2 est :
=si(B2>12;(8*A2)+(4*1,5*A2)+((B2-12)*A2*2);si(B2>8;(8*A2)+((B2-8)*A2*1,5);A2*B2))

La formule en D2 est :
=A2*(B2+0,5*(max(0;B2-8)+max(0;B2-12)))

La formule en D2 est certes deux fois plus compacte que la formule en C2 mais – pour beaucoup de gens – elle sera nettement moins compréhensible que la formule en C2.

Remarque – Bien entendu, nous déconseillons formellement l’utilisation – comme c’est le cas dans cet exemple – de constantes cachées (les valeurs 1 ; 1,5 ; 2 ; 8 ; 12). Nous ne l’avons fait ici que pour simplifier la présentation du cas et les formules. Normalement, nous aurions mis tous ces paramètres dans ces cellules de la feuille : ainsi, les formules resteraient « bonnes » même si l’on devait un jour modifier ces valeurs.

Nombre de caractères d’un bloc

Votre objectif est aujourd'hui de calculer le nombre de caractères dans un bloc de cellules – le bloc A2:B3, dans notre exemple – en comptant ou pas, selon le cas, d’éventuels espaces intermédiaires.

La copie d’écran ci-dessus représente notre solution avec, en bas de l’image, la description des formules utilisée en C5 et C6.

Une fois de plus, nous constatons la richesse de la fonction SommeProd() qui permet souvent d’atteindre – mais de façon plus légère – le genre de résultat pour lequel on pense en général devoir utiliser une formule matricielle.

La fonction Substitue() – nous l'avons déjà vu dans des articles antérieurs – est pour sa part assez peu connue et se révèle pourtant fréquemment utile.

Masquage des lignes à quantité 0

Vous souhaitez – par voie de macro – masquer automatiquement toutes les lignes pour lesquelles une valeur de 0 a été saisie pour la quantité. Mais en revanche, vous ne souhaitez pas le faire pour les lignes dont la valeur dans la colonne « Quantité » n’a pas encore été saisie. Voici un exemple avec la feuille avant et après l’exécution de la macro :

Pour que cette macro fonctionne, il faut que vous ayez préalablement sélectionné le bloc B2:B17 des valeurs à contrôler. Voici la macro :

Sub Cache_lignes()
For Each Cell In Selection
If Cell = 0 And Not (IsEmpty(Cell)) Then Cell.EntireRow.Hidden = True
Next Cell
End Sub

Remarque – On pourrait, bien évidemment, ajouter au début de la macro une commande demandant à l’utilisateur de sélectionner le bloc des valeurs à contrôler.

La hiérarchie avec Excel 2003/XP

Les versions Excel 2003 ou XP ne possèdent pas les objets SmartArt que nous avons vus dans les articles du 11 mai au 15 juin. Supposons que nous souhaitions créer la même hiérarchie avec ces « anciennes » versions d’Excel qu’avec la version 2007.

Vous pouvez prendre dans la barre d’outil « Dessin » le rectangle à bords arrondis. Vous le clonez pour créer une ombre bleue. Pour associer à chaque bloc un des textes de A1 à A7, vous sélectionnez le bloc voulu puis, dans la barre de formule, entrez la formule =$A$1 pour le premier bloc.

Vous créez ainsi un lien entre chaque bloc et la cellule associée. Cela vous permet – contrairement à ce qui est possible avec SmartArt en Excel 2007 ! – de créer aisément un lien entre la cellule et le titre du bloc : quand la cellule est modifiée, le titre du bloc évolue de même.

Il ne reste plus qu’à utiliser des connecteurs pour relier les blocs. Voici le résultat final :

Remarque 1 – Après avoir créé ces liens, n’oubliez pas de sélectionner successivement chaque paire (bloc + ombre) afin de la grouper.

Remarque 2 – Sélectionnez ensuite l’ensemble du schéma, faites un clic droit, activez la commande « Mettre en forme l’objet » et ensuite, dans l’onglet
« Propriétés », la commande « Ne pas déplacer ou dimensionner avec les cellules ». Le schéma devient alors indépendant de la présentation de la feuille.

Après tout cela, les connecteurs jouant bien leur rôle, il suffit de déplacer un bloc (ici, le bloc « Etranger ») pour que les connecteurs suivent :

En conclusion, avec cette solution pour Excel 2003 ou XP, le processus est plus long mais il y a deux avantages en fin de course :
1) Il y a un lien dynamique entre les cellules et les noms des blocs
2) Le pinceau fonctionne maintenant entre les objets (dégroupés !)

Remarque 3 – Bien évidemment, avec Excel 2007, vous pouvez utiliser aussi cette solution pour créer vos schémas hiérarchiques.

Macros Excel 4 et calcul complet

Quand un classeur utilise des macros Excel 4, comme nous l’avons fait dans notre modèle il y a quatre jours, un message d’alerte apparaît automatiquement à l’ouverture du fichier :

Vous ne pouvez malheureusement pas éviter ce message d’alerte. C’est le prix à payer pour pouvoir bénéficier de la grande richesse des commandes macros Excel 4 !

Un problème de recalcul

Vous avez peut-être aussi remarqué que la liste des feuilles du classeur ne s’actualise pas quand on renomme une feuille, ou quand on en ajoute ou détruit une. L’utilisation des macros Excel 4 est en effet un des cas où le simple recalcul d’Excel avec [F9] ne suffit pas.

Quand le simple recalcul d’Excel ne fonctionne pas, il reste heureusement le « calcul complet », qui est déclenché par la combinaison [Ctrl]-[Alt]-[F9]. Avec cette combinaison, vous actualisez effectivement la liste des feuilles.

J’appelle cela le « calcul complet » car cette combinaison, transcrite en VBA, correspond à la commande Application.CalculateFull.

Liste des feuilles d’un classeur

Le 14 décembre 2006, nous avons vu quelles formules utiliser pour trouver les noms du classeur actif, de son chemin et de la feuille active. Pour tout cela, nous avons utilisé les fonctions cellule() et info().

Aujourd’hui, nous complèterons cela en vous montrant comment obtenir la liste des feuilles du classeur actif.

Pour cela, il faut déjà identifier le nom du document, ce qui est fait en D1 dans l’exemple ci-dessous à l’aide de la formule :
=gauche(Classeur;cherche("]";Classeur))

Cette formule utilise le nom « Classeur » dont nous voyons la définition dans la copie d’écran provenant de Name Manager.

La formule magique, à saisir en A1 et à reproduire dans la colonne, est la suivante :
=si(ligne()<=nbval(Feuilles);droite(index(Feuilles; ligne());nbcar(index(Feuilles; ligne()))-nbcar($D$1));"")

Cette formule utilise le nom « Feuilles » qui partage avec le nom « Classeur » la particularité d’utiliser des commandes macro Excel 4 !

Vous pouvez lire à ce sujet avec intérêt les articles du 8 et du 18 février 2006, ainsi que celui du 10 décembre 2006 : ces articles ouvrent de façon marquante l'éventail des possibilités d'Excel, avec des fonctionnalités ignorées par la quasi-totalité de ses utilisateurs !

Enrichissez la collection SmartArt

Microsoft a enrichi la collection SmartArt de sept nouveaux types de graphes que vous pouvez télécharger gratuitement à l’adresse :

http://office.microsoft.com/en-us/templates/CT101636101033.aspx

Voici ce que donnent ces objets supplémentaires :

Au cas où les quelque 84 objets « standard » de SmartArt ne vous suffiraient pas…