Monsieur Excel
Pour tout savoir faire sur Excel !

14 août 2019

Personnalisation de la QAT

Chères lectrices, chers lecteurs,
Je me suis permis de prendre une quinzaine de jours de
« vacances »Me voici prêt à reprendre le flambeau :)

 La Quick Access Toolbar (QAT) est la petite barre d’outils apparaissant normalement en haut de votre fenêtre Excel. Elle vous permet de gagner beaucoup de temps pour les accès aux commandes qui vous sont particulièrement utiles. Je constate souvent chez mes clients que nombreux sont ceux qui ne les utilisent pas à hauteur de leur potentiel.

La QAT est l’ensemble des commandes Excel dont les icônes apparaissent en blanc dans la bande verte en haut de la fenêtre. Le dernier outil de la bande sert à personnaliser la QAT. Quand vous cliquez dessus, un menu déroulant apparaît, dont vous sélectionnez l’avant-dernière commande, Autres commandes.

Vous obtenez alors l’écran des commandes courantes, qui affiche une petite soixantaine de commandes parmi lesquelles vous pouvez sélectionner celle qui vous intéresse puis cliquer sur le bouton Ajouter pour l’incorporer à la QAT.


Vous pouvez noter que, dans ma QAT minimale, j’ai déjà ajouté – c’est le minimum pour moi ! – trois commandes : Sélectionner les objets, Table de données et Appareil photo.

Remarque 1 – Il est intéressant d’avoir un accès direct à la commande Table de données : sinon, il faut passer par l’onglet Données, puis par la commande Analyse scénarios et enfin par la commande Table de données.

En haut à gauche de la fenêtre, un menu déroulant affiche « Commandes courantes ». Vous pouvez le dérouler et sélectionner Toutes les commandes. Vous passez alors de la soixantaine de commandes à – tenez-vous bien ! – plus de 1.800 commandes.

Remarque 2 – Si un jour vous avez du temps, vous pouvez essayer de toutes les consulter, une par une. Je suis sûr que – ce faisant – vous tomberez tôt ou tard sur une merveille, comme l’outil Appareil photo, qui est une véritable merveille car il prend des photos dynamiques : dès que leur modèle change, ces photos changent aussi !

Mon seul regret par rapport à la QAT est que les icônes sont très petites et que – surtout ! – il n’y a hélas pas d’option pour les rendre plus grandes, ce qui est regrettable pour ceux qui, à mon instar, possèdent des yeux fatigués…

25 juillet 2019

La fonction si.multiple()

La fonction si.multiple() est une fonction étrange. Son objectif annoncé est de permettre à l’utilisateur d’éviter, en recourant à elle, l’imbrication de plusieurs si() les uns dans les autres. Sa syntaxe annoncée est :
=si.multiple(expression;valeur1;résultat1;défaut_ou_valeur2;résultat2;...)

Quand on regarde les blogs ou les vidéos Excel, on voit à ce sujet de multiples exemples dans lesquels on a du mal à voir l’intérêt de la fonction, par exemple :
=si.multiple(B4;1;"lundi";2;"mardi";3;"mercredi";"autre")

Dans un tel cas, ou pourrait tout aussi bien utiliser la fonction recherche() ou la fonction choisir(), et ce serait bien plus léger… Ou même, jusqu’à un certain point, utiliser tout simplement un format comme jjjj

Je vais vous montrer une application plus créative de cette fonction, en reprenant notre base habituelle, que nous avons légèrement modifiée pour une troisième option de sexe.

Nous voulons obtenir en colonne H le prénom et le nom de la personne s’il s’agit d’un homme, et le prénom et le salaire s’il s’agit d’une femme, et un troisième message si le sexe n’est pas renseigné :


La formule de H6, reproduite vers le bas, est alors :
=B6&" "& si.multiple (A6;"M";C6;"F";" : "& texte (E6;"# ##0 €");"X";": sans sexe... ")
Avec cet exemple, je ne pense pas que vous puissiez trouver une formule plus légère qui engendre le même résultat…

Remarque 2 – Faites attention aux jokers « ? » et « * » qui ne se comportent pas comme l’on pourrait s’y attendre avec cette fonction. Supposons qu’une cellule contienne Alfred et que l’on écrive dans une autre cellule =substitue(cellule;"f?";"g?") ou =substitue(cellule;"f*";"g*") : le résultat restera « Alfred » –  et non « Algred » – dans les deux cas !

Lors de mes interventions sur les modèles de mes clients, j’ai constaté plus d’une fois que des solutions compliquées avaient été mises en œuvre là où de simples substitue() auraient résolu le problème de façon bien plus rapide et élégante.

18 juillet 2019

La fonction somme.si.ens()

Pour continuer dans la série commencée le 26 juin dernier, je vous présent aujourd’hui une fonction disponible avec Excel 2019 et Office 365, la fonction somme.si.ens(), dont la syntaxe est =somme.si.ens(plage_somme;plage_critères;critères;...) sachant que l’on peut avoir autant de blocs plage_critères;critères que l’on veut.

Si nous reprenons notre base habituelle, nous pouvons par exemple – grâce à cette fonction – calculer la somme des salaires gagnés par des hommes de 26 et 27 ans. Une formule possible pour atteindre ce but est, dans la cellule H11 :
=SOMME.SI.ENS($E$6:$E$30;$A$6:$A$30;"m";$D$6:$D$30;">="&M8;$D$6:$D$30;"<="&M9)

Notez que les pros d’Excel pouvaient depuis longtemps atteindre le même objectif à l’aide d’une formule matricielle, comme nous l’avons fait en H12 avec :
=somme((E6:E30)*(A6:A30="m")*(D6:D30>25)*(D6:D30<28 o:p=""><28 b="">

C’est plus court, mais, la formule étant matricielle, elle a dû être validée avec [Ctrl]-[Maj]-[Entrée].

10 juillet 2019

Un gadget dans Joindre.Texte()

L’article précédent nous a montré comment, en utilisant la fonction Joindre.Texte(), nous pouvions demander à une simple formule – assistée il est vrai d’une validation matricielle ! – de faire, en créant une liste, tout un travail correspondant normalement à une boucle de calcul.

On peut seulement reprocher à cette liste le fait d’utiliser « M » et « F » au lieu de « Mr. » et « Mme ». Essayez d’imaginer comment vous pourriez arriver à ce résultat, tel qu’on peut le voir dans la seconde image ci-dessous :


J’y suis parvenu à l’aide d’une fonction très peu utilisée par la grande majorité des utilisateurs d’Excel, et même complètement ignorée de la plupart d’entre eux, la fonction Substitue(). L’astuce consiste à insérer la formule présentée dans l’article précédent à l’intérieur de deux appels à Substitue(), l’un pour remplacer les « M » par « Mr. » et l’autre les « F » par « Mme ».

Voici donc la formule de la cellule I6, reproduite ensuite vers le bas, dans laquelle nous avons mis en rouge ce qui est nouveau :
=substitue(substitue(joindre.texte(" - ";;SI($D$6:$D$30<>H6;"";$A$6:$A$30&" "&$B$6:$B$30&" "&$C$6:$C$30));"M ";"Mr. ");"F ";"Mme. ")

Remarque 1 – N’oubliez pas de valider cette formule comme formula matricielle, validée avec [Ctrl]-[Maj]-[Entrée].

La fonction substitue() sert à remplacer, y compris plusieurs fois dans la même cellule, une chaîne de caractères par une autre chaîne de caractères.

Remarque 2 – Faites attention aux jokers « ? » et « * » qui ne se comportent pas comme l’on pourrait s’y attendre avec cette fonction. Supposons qu’une cellule contienne Alfred et que l’on écrive dans une autre cellule =substitue(cellule;"f?";"g?") ou =substitue(cellule;"f*";"g*") : le résultat restera « Alfred » –  et non « Algred » – dans les deux cas !

Lors de mes interventions sur les modèles de mes clients, j’ai constaté plus d’une fois que des solutions compliquées avaient été mises en œuvre là où de simples substitue() auraient résolu le problème de façon bien plus rapide et élégante.

02 juillet 2019

La sublimation de Joindre.Texte()

Nous avons présenté dans le dernier article la fonction Joindre.Texte(). Elle est déjà intéressante telle quelle mais, comme nous allons le voir ci-après, elle peut devenir sublime quand elle est utilisée en formule matricielle.

Reprenons l’exemple de notre base. Nous constatons que toutes les personnes de la base ont un âge compris entre 25 et 32 ans.


Question : que feriez-vous pour que la colonne I liste (Sexe, Prénom et Nom) toutes les personnes ayant un âge donné (colonne H) comme nous pouvons le voir dans la seconde copie d’écran ci-dessous ?

Essayez donc de voir si vous trouvez une solution avant de lire la suite…


La formule utilisée en I6, compte tenu de la grande complexité du travail demandé, est finalement assez courte :
=joindre.texte(" - ";1;si($D$6:$D$30<>H6;"";$A$6:$A$30&" "&$B$6:$B$30&" "&$C$6:$C$30))

Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Remarque – Nous avons même éliminé le second argument de la fonction joindre.texte : en effet, c’est « 1 » ou « Vrai » par défaut…

26 juin 2019

Concat() et Joindre.Texte()

Depuis des années, nous avions dans Excel une fonction dont l’utilité était pour le moins douteuse, la fonction Concatener(). En effet =concatener(B1;C1) est équivalent à =B1&C1. Pourquoi faire simple quand on peut faire compliqué ?

Nous avons maintenant, avec Excel 2019 et Office 365 une fonction qui remplace Concatener() tout en enrichissant ses fonctionnalités de façon notable, la fonction Concat(). Plus, en guise de prime, une toute nouvelle fonction – elle aussi dans le domaine de la gestion de chaînes de caractères – TextJoin().

Les fonctions Concatener() et Concat()

Dans l’exemple de la copie d’écran ci-dessous, nous avons entré les formules dans la colonne A et reproduit le texte de ces formules en colonne B. Comme le montrent clairement les cellules B1 et B2, il semble clair que l’utilisation de la fonction Concatener ne semble présenter aucun avantage par rapport au simple « & ».

La nouvelle fonction Concat() ne fait rient de plus, comme on peut le voir en ligne 3. On peut se demander quel est l’intérêt, à part l’économie de 4 caractères, de remplacer une fonction inutile par une autre fonction tout aussi inutile…

Syntaxe de la fonction Joindre.Texte()

La fonction Joindre.Texte(), en revanche, représente à la fois une réelle innovation et un apport utile.

La syntaxe est : Joindre.Texte(délimiteur;ignorer_si_vide;texte1;...)

Quand ignorer_si_vide est égal à 1 ou VRAI, on ignore les cellules vides.

La chaîne de caractères résultant ne peut pas dépasser 32 767 caractères. Au-delà, une erreur #VALEUR! est renvoyée.

Pour obtenir le même résultat avec cette nouvelle fonction qu’en A1:A3, il faut entrer en A4 la formule :
=joindre.texte(" ";0;C1:D1;":";E1)


Certes, ce n’est pas plus court que les formules précédentes, mais cette fonction ouvre de nouvelles possibilités dont nous verrons une illustration dans le prochain article.

18 juin 2019

Liste déroulante interactive (c)

Nous avons vu dans le dernier article comment limiter notre menu déroulant aux seules villes dont le nom contient une certaine chaîne de caractères, « el » en l’occurrence.

Ce qui est particulièrement intéressant, et auquel vous avez pu ne pas penser, c’est que l’on peut utiliser dans cette chaîne de caractères les deux fameux jokers, « ? » pour un caractère unique et « * » pour une chaîne de caractères de n’importe quelle longueur.

Utilisation du joker « ? »

Avec le joker « ? », si nous entrons la chaîne « o?o », nous obtenons toutes les villes dans lesquelles on peut trouver deux « o » séparés par exactement un caractère.

Nous constatons qu’il n’y a que trois villes dans notre liste qui respectent cette condition car il n’y a pas d’ascenseur à droite :


Utilisation du joker « * »

Avec le joker « ? », si nous entrons la chaîne « o*o », nous obtenons toutes les villes dans lesquelles on peut trouver deux « o » séparés par un caractère ou par plusieurs caractères.

Le menu déroulant nous affiche les 7 premières villes respectant cette condition mais l’ascenseur à droite nous indique qu’il y en a d’autres encore…


Remarques sur les jokers

En règle générale, les deux jokers « ? » et « * » fonctionnent très bien pour localiser – et c’est vrai aussi dans Word ou dans Powerpoint – des chaînes de caractères, comme nous venons de le voir avec nos deux exemples.

On peut seulement regretter qu’il n’y ait pas en plus – quand on utilise la commande « Remplacer » – deux autres caractères signifiant que l’on veut garder les lettres concernées par le « ? » et le « * » exactement comme elles l’étaient auparavant. Hélas, Excel entre littéralement des « ? » et des « * » dans le texte remplacé.