Pierre Fauconnier

Wouah. Plus de 600 vues de mon billet sur SOMME.SI.ENS en moins d'une semaine...

Elles vous intéressent donc, les fonctions SOMME.SI.ENS, MOYENNE.SI.ENS, NB.SI.ENS (et aussi MAX.SI.ENS et MIN.SI.ENS réservées à la version OFFICE365)?

Dans mon précédent billet, je vous disais comment créer les critères lorsque vous voulez prendre en compte les lignes pour lesquelles des valeurs sont différentes de la valeur du critère.

Aujourd'hui, nous allons voir comment tenir compte de valeurs partielles dans les colonnes. Il n'est pas possible, avec SOMME.SI.ENS, d'exprimer un critère qui travaille sur le résultat d'un traitement d'une donnée. Ainsi, vous ne pourrez pas, sans colonne de construction, calculer la somme des ventes d'un jour de la semaine par rapport aux dates de vente, car il est tout simplement impossible d'exprimer un critère sur cette valeur avec les fonctions XXX.SI.ENS (sauf, je le répète, à créer une colonne supplémentaire). C'est cependant possible avec une autre fonction d'Excel, SURPUISSANTE, mais je vous parlerai bientôt!

Il est toutefois possible de réaliser un traitement sur une partie textuelle de la valeur d'une cellule, grâce à l'utilisation des jokers, et cela sans devoir passer par des colonnes intermédiaires

Pour cela, regardons le tableau suivant. C'est un tableau reprenant des ventes d'articles, dont les codes sont composés de trois parties:

• les trois premiers chiffres renseignent la catégorie;
• la suite de six chiffres donnent le numéro de l'article;
• les deux derniers chiffres renseignent la couleur.

Il serait intéressant de pouvoir additionner les ventes pour une catégorie, c'est-à-dire pour une suite précise des trois premiers caractères. Pour réaliser cela, nous allons utiliser le joker *, qui remplace x caractères à partir d'où on le place dans le critère de recherche. Comme pour les opérateurs de comparaison dont je parlais précédemment, il convient de placer le joker à l'intérieur du critère.


Et bien évidemment, comme je ne peux pas saisir la catégorie en dur, c'est-à-dire en constante, je la déporte dans une cellule et je concatène le joker avec cette valeur grâce à l'opérateur & (concaténer = assembler des chaines de caractères entr'elles NDLR). Le critère "003*" signifie donc tout ce qui commence par 003.




Bien sûr, il est possible de placer le joker au début, au milieu ou à la fin du critère. C'est ainsi que si je souhaite obtenir le total des ventes relatives à des articles de couleur 02, j'utiliserai le joker * au début du critère. Le critère "*02" signifie donc tout ce qui se termine par 02.


Imaginons que, dans le code de l'article, le premier chiffre du groupe du milieu identifie le fournisseur. Si je souhaite obtenir le total des ventes pour le fournisseur 7, je vais devoir remplacer les quatre premiers caractères par n'importe quoi puis préciser le 7 puis demander n'importe quoi derrière. Pour remplacer un caractère à un endroit précis, j'utiliserai le joker ? qui remplace une fois un caractère. Dès lors, mon critère s'exprimera en utilisant les deux jokers au sein de la même chaine. "????7*" signifie quatre caractères puis le signe 7 puis zéro, un ou plusieurs caractères. L'erreur fréquente réside ici dans l'omission de * à la fin du critère. Attention: "????7" veut dire une chaine de cinq caractères qui se termine par 7. On voit ainsi que l'on peut aussi travailler sur la longueur du critère.


Comme on le voit, les propriétés sont déjà étonnantes. Il nous reste cependant quelques problèmes. Dans des cas un peu tordus, il se pourrait que l'un des jokers fasse partie du code. Si on eu l'idée "originale" de faire commencer les articles sans catégorie par un astérisque, comment trouver la somme des articles qui commencent par ce caractère? Eh bien, on va tout simplement indiquer à Excel que l'on utilise la caractère en tant que tel et pas en tant que joker en le faisant précéder du signe ~ (ça ne s'invente pas...). Le critère "~**" signifie qui commence par un astérisque avec n'importe quoi derrière. Le signe ~ est donc un caractère d'échappement, c'est-à-dire qu'il inhibe la signification particulière du caractère qu'il précède.


Et comme celui qui a inventé les codes articles a été particulièrement futé (suivez mon regard ), il a décidé que ce qui se terminait par le signe ~ signifiait que la couleur n'était pas précisée ou que cette info sur la couleur était sans objet. Il va donc falloir préciser à Excel que le ~ du critère est bien là comme caractère et pas comme caractère d'échappement. il faudra donc le doubler. Le critère "*~~" signifie donc n'importe quel code qui se termine par ~.


Et voilà! On a fait le tour. Ci-dessous un petit tableau récapitulatif.


Vous voyez que ces fonctions permettent pas mal de choses. A vous de jouer. Dans un prochain tuto, nous envisagerons une généralisation du traitement conditionnel des données grâce une fonction magique... (Chuut. Ce sera peut-être dans la hotte du Père Noël... )

Bon travail avec Excel et à bientôt pour un nouveau truc & astuce...