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.

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].

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.

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…