Comment contrôler le « Sigma » ?

Parfois, le « Sigma » ne donne pas le résultat attendu…

Dans l’exemple ci-dessous, nous avons une facture dans laquelle les formules de la dernière colonne mettent un texte vide à la place du produit
« Px Unit » * « Quant » chaque fois que l’on a affaire à un code erroné.

Voici ce qui se passe si l’on place le curseur dans la cellule du total et que l’on clique alors dans l’outil « Sigma » :

Quand, après avoir placé le curseur dans la case du total, dans le coin inférieur droit du tableau, on clique sur le « Sigma », on constate qu’elle ne remonte pas plus haut que H7 car il y a du texte en H6 !

La solution consiste à préalablement sélectionner le bloc H2:H9, puis à cliquer alors dans le « Sigma ».

Remarque 1 – Cela aurait fonctionné de la même façon si nous avions plutôt sélectionné le bloc H3:H9… Le bel effort de démarrer en H2 n'a pas réussi à faire une formule "englobante" partant réellement du teste :(

Remarque 2 – Il y a un raccourci clavier pour le « Sigma », la combinaison [Alt]-[=].

Une belle fonctionnalité du "Sigma"

L’outil « Sigma » est certainement l’un des outils d’Excel les plus utilisés. Il est en effet particulièrement efficace pour obtenir en un rien de temps une formule de sommation.

Comment expliquer, alors qu’il est tellement connu, qu’il soit si « mal » connu ? Il possède en effet une propriété remarquable ignorée de la grande majorité des utilisateurs d’Excel.

Prenons le petit modèle ci-dessous, dans lequel nous avons sélectionné le bloc A1:F5 incluant à la fois les titres et les données :

Eh bien, si vous cliquez maintenant sur l’outil « Sigma », vous obtenez d’un coup d’un seul tous les totaux des lignes et des colonnes. N’est-ce pas merveilleux ?

Remarque 1 – Et cela fonctionne quelque soit le nombre de lignes ou de colonnes, ce qui vous évitera de scroller comme un fou pour aller chercher les extrémités du bloc !

Remarque 2 – N’est-il pas surréaliste que tant d’utilisateurs aient pu passer tant d’heures sur Excel, depuis tant d’années, sans avoir jamais entendu parler de cette fonctionnalité ?

Bascule Premier plan / Arrière-plan

Nous avons vu il y a quatre jours comment afficher ou masquer un bouton par macro.

Pour rendre un bouton inactif, une seconde solution est possible : faire passer le bouton à « cacher » derrière une zone de texte dont on pourrait si nécessaire – pour la rendre invisible, ou simplement moins visible – masquer les bordures.

Ici, pour qu’elle soit plus visible, nous avons peint ici la zone de texte en jaune.

La macro passe la zone de texte en premier plan quand elle est à l’arrière-plan, et vice versa. Elle utilise une variable publique pour mémoriser si le bouton Jules se trouve au premier plan ou à l’arrière-plan.

Etrange coïncidence – Avec ces deux derniers articles, je vous ai montré comment masquer ou occulter un bouton alors même que nous apprenions il y a quelques jours que Monsieur Bouton et ses amis dirigeants de la société Générale avaient réussi – avec un cynisme cupide qui fait penser à Madoff – à se faire voter de majestueuses primes en récompense de leur performance notable : la baisse de plus de 50% en un an du cours de la Société Générale.

Afficher ou masquer un bouton

Un lecteur me demandait récemment comment afficher ou masquer un bouton selon des choix effectués par l’utilisateur dans la feuille de calcul. Cela n’est pas bien difficile grâce à la propriété « visible » du Visual Basic.

Dans le modèle ci-dessus, l’utilisateur définit ses choix en E1:E3. Un clic dans le bouton « Action ! » lance la macro qui affiche ou masque les boutons en conséquence. Cela suppose bien entendu que les boutons portent les noms indiqués en D1:D3.

Remarque 1 – Pour nommer un bouton, on le sélectionne, on entre le nom voulu dans la zone « Nom », à gauche de la barre de formule, puis on valide avec [Entrée].

Remarque 2 – Pour les boutons, j'ai utilisé la barre d'outils "Formulaires", qui est mieux adaptée pour les objets à placer dans les feuilles de calcul.

Les formats « téléphone » d'Excel

Dans les formats numériques d’Excel, il y a une catégorie « Spécial » dans laquelle on trouve plusieurs formats numériques particuliers : code postal, n° de Sécurité Sociale, n° de téléphone…

L’écran reproduit ci-dessous montre ce que donnent les différents formats téléphoniques :

Nous avons reproduit en colonne C le format numérique précis correspondant à chaque option. Pour les comprendre, sachez que le « 0 » représente un chiffre obligatoire et le « # » un chiffre facultatif.

Remarque – Pour savoir quel est le format numérique précis correspondant à un format, il suffit de se placer sur une cellule (A6 dans notre exemple) puis de sélectionner la catégorie « Personnalisée » comme nous l’avons fait ci-dessus.

Flashage sans format conditionnel

Encore une intervention d’un lecteur qui me demande s’il est possible de faire flasher les cellules(cf. messages du 2 et du 6 mars) sans passer par des formats conditionnels.

Certes, c’est possible, mais au prix cependant d’une complexification sensible de la macro. Voici une solution, une des plus simples mais probablement pas la plus compacte. Ceci dit, elle est assez facile à comprendre :

Cette macro suppose que l’on ait donné le nom « Tableau » à la partie intérieure du tableau, donc en excluant les titres de ligne et de colonne.
On pourrait aussi attribuer le nom au tableau entier, titres compris, mais il faudrait alors modifier la macro en conséquence.

Remarque – Un avantage de cette solution par rapport aux solutions des deux articles précédents est qu’aucun flashage ne survient quand on active une cellule située hors du tableau mais placée dans une ligne, ou dans une colonne, du tableau.

Un flashage plus complet encore…

Il suffit que vous donniez la main pour que l’on vous demande le bras…

En effet, un autre lecteur, après avoir vu l’article présenté il y a quatre jours sur le flashage des titres de ligne et de colonne, m’a demandé si l’on pouvait aussi faire flasher, à l’intérieur du tableau, les cellules menant à la cellule active, dans sa ligne et dans sa colonne.

Vous savez bien qu’avec Excel tout, ou presque, est possible !
Voici donc la solution :

Toutes les cellules intérieures du tableau ont reçu le format conditionnel ci-dessus où vous ne pouvez pas lire la formule de la seconde condition en entier. La voici :
=ou(et(colonne()=$H$1;ligne()<$G$1);et(ligne()=$G$1;colonne()<$H$1))

Remarque – Le seul problème éventuel qui reste est que, si l’on sélectionne une cellule placée sous le tableau, ou à sa droite, on a droit à l’illumination de la colonne ou de la ligne correspondante du tableau. Si cela vous dérange, je vous laisse imaginer la solution :)

Flash du titre de ligne et de colonne

Un lecteur m’a demandé récemment comment mettre en relief, dans un tableau, le titre de ligne et le titre de colonne de la cellule active.

Il n’y a aucune façon d’atteindre cet objectif sans passer par une macro. La solution que je vous propose ici est à double détente : il y a une macro événementielle qui note le numéro de la ligne et de la colonne de la cellule active, et un format conditionnel dans les titres qui atteint l’objectif visé.

La macro événementielle met en G1 et H1 le numéro de la ligne et de la colonne de la cellule active. Nous ne voyons pas ces numéros à l’écran car je leur ai attribué une couleur de police blanche :)

Nous voyons en bas de la copie d’écran le format conditionnel utilisé pour la cellule B1 et ensuite recopié à droite. Vous en déduirez sans difficulté le format conditionnel de la cellule A2, que l’on reproduira ensuite vers le bas.