Dans ce tutoriel, je vais vous montrer comment deux simples accolades peuvent permettre à Excel de faire des choses que nos formules habituelles ne savent pas toujours gérer facilement.
Nous allons par exemple compter en une seule formule toutes les commandes qui ont le statut « À préparer » OU « En attente stock », retourner uniquement deux colonnes précises alors qu’elles ne sont même pas côte à côte, puis découper instantanément un texte contenant plusieurs séparateurs différents.
Le point commun entre ces trois astuces tient simplement dans ces deux caractères : { }.
Et restez bien jusqu’au troisième exemple : nous utiliserons plusieurs séparateurs simultanément dans FRACTIONNER.TEXTE pour transformer une donnée difficile à exploiter en plusieurs colonnes propres avec une seule formule.
Téléchargement
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons les commandes d’une boutique en ligne.
Chaque ligne correspond à une commande avec son numéro, le client, le canal de vente, son statut, son montant, la ville de livraison et une dernière colonne contenant plusieurs informations logistiques regroupées dans une seule cellule.
Avant de commencer avec les formules, nous allons transformer cette plage en tableau structuré, ce qui va nous simplifier son exploitation.
Nous cliquons dans l’une des cellules du tableau, puis nous utilisons le raccourci [Ctrl]+[L].
Excel sélectionne automatiquement la plage A6:G16, nous vérifions que « Mon tableau comporte des en-têtes » est coché, puis nous validons avec « OK ».
Nous nous rendons ensuite dans l’onglet « Création de tableau » et nous remplaçons le nom proposé par Excel par « Commandes ».
Nous pourrons ainsi utiliser une référence comme « Commandes[Statut] » plutôt qu’une plage classique comme $D$7:$D$16. Si nous ajoutons une nouvelle commande en dessous, la formule suivra automatiquement.
2. Comprendre les accolades
Avant d’utiliser les accolades dans des formules plus complexes, commençons par voir concrètement ce qu’elles permettent de faire.
Plaçons-nous dans une cellule vide, puis saisissons :
={10;20;30}
Lorsque nous validons avec [Entrée], Excel 365 affiche automatiquement les trois valeurs les unes sous les autres. Nous venons de créer ce qu’Excel appelle une « constante matricielle », c’est-à-dire un ensemble de plusieurs valeurs écrites entre accolades.
Ici, le point-virgule utilisé à l’intérieur des accolades permet de passer à la ligne suivante.
Pour créer une matrice horizontale, c’est-à-dire afficher les valeurs sur plusieurs colonnes, nous utilisons le point « . » comme séparateur :
={10.20.30}
Après validation, Excel affiche cette fois 10 dans J6, 20 dans K6 et 30 dans L6.
Nous pouvons donc retenir une règle très simple : dans une constante matricielle, le point-virgule permet généralement de descendre d’une ligne, tandis que le point permet de passer à la colonne suivante.
Nous pouvons même combiner les deux pour construire une petite matrice de plusieurs lignes et plusieurs colonnes :
={10.20;30.40}
Excel retourne alors un tableau de deux lignes et deux colonnes : 10 et 20 sur la première ligne, puis 30 et 40 sur la seconde.
L’intérêt est que ces valeurs existent directement dans la formule. Nous n’avons donc pas besoin de saisir 10, 20 et 30 dans trois cellules séparées avant de pouvoir les utiliser.
Attention toutefois à ne pas confondre ces accolades avec celles des anciennes formules matricielles validées avec [Ctrl]+[Maj]+[Entrée].
Dans notre cas, c’est bien nous qui saisissons manuellement les accolades pour fournir plusieurs valeurs à une fonction.
Maintenant que nous savons créer une liste verticale, une liste horizontale et même une petite matrice directement dans une formule, nous allons voir pourquoi cela devient particulièrement utile dans des cas beaucoup plus concrets.
3. Compter plusieurs critères avec une logique « OU »
Commençons par une situation très courante.
Nous voulons connaître le nombre de commandes qui nécessitent encore une action de notre part. Pour notre exemple, cela correspond aux commandes dont le statut est soit « À préparer », soit « En attente stock ».
Le premier réflexe pourrait être d’utiliser NB.SI.ENS en combinant les deux tests sur la colonne .
Dans J7, nous saisissons :
=NB.SI.ENS(Commandes[Statut];"À préparer";Commandes[Statut];"En attente stock")
Nous appuyons sur [Entrée] et Excel nous retourne zéro.
Le problème vient du fonctionnement de NB.SI.ENS. Lorsque nous lui donnons plusieurs critères, Excel applique une logique « ET ».
Notre formule recherche donc une commande dont le statut serait simultanément « À préparer » ET « En attente stock ».
Évidemment, aucune cellule de notre colonne Statut ne peut contenir les deux valeurs à la fois.
Nous pourrions résoudre le problème en additionnant deux NB.SI :
=NB.SI(Commandes[Statut];"À préparer")+NB.SI(Commandes[Statut];"En attente stock")
Cela fonctionne, mais nous allons utiliser une méthode plus intéressante.
Nous remplaçons les deux critères par une constante matricielle :
=NB.SI.ENS(Commandes[Statut];{"À préparer";"En attente stock"})
Nous validons.
Cette fois, Excel retourne deux résultats. Il compte d’un côté les commandes « À préparer » et de l’autre les commandes « En attente stock ».
Dans notre exemple, nous obtenons 3 puis 2.
Pourquoi ? Tout simplement parce que NB.SI.ENS reçoit successivement les deux valeurs contenues entre les accolades.
Mais nous ne voulons évidemment pas afficher deux nombres. Nous voulons connaître le nombre total de commandes à traiter.
Nous ajoutons donc simplement SOMME autour de notre formule :
=SOMME(NB.SI.ENS(Commandes[Statut];{"À préparer";"En attente stock"}))
Excel retourne maintenant 5.
Nous venons de créer une logique « OU » dans une seule formule.
Cette technique devient particulièrement pratique lorsque nous avons davantage de critères. Si nous voulons également intégrer les commandes « Expédiée », il suffit de l’ajouter dans la constante :
=SOMME(NB.SI.ENS(Commandes[Statut];{"À préparer";"En attente stock";"Expédiée"}))
Retenons surtout le principe : les accolades permettent ici de passer plusieurs critères là où nous n’en aurions normalement saisi qu’un.
4. Retourner uniquement les colonnes qui nous intéressent
Passons maintenant à un deuxième cas.
Imaginons que nous souhaitions créer une extraction de notre tableau contenant uniquement le nom du client et le montant de sa commande.
Le problème, c’est que ces deux informations ne sont pas côte à côte. Entre « Client » et « Montant », nous avons également « Canal » et « Statut ».
Nous allons commencer dans la cellule J10 avec la fonction FILTRE :
=FILTRE(Commandes[[Client]:[Montant]];{1.0.0.1})
Nous validons avec [Entrée].
Excel retourne uniquement deux colonnes : « Client » et « Montant ».
Pour comprendre la formule, regardons d’abord la plage « Commandes[[Client]:[Montant]] ».
Elle contient quatre colonnes dans cet ordre : Client, Canal, Statut et Montant.
Nous lui associons ensuite la constante « {1.0.0.1} », où les quatre valeurs correspondent exactement aux quatre colonnes.
Le premier 1 indique que nous gardons « Client ». Le premier 0 retire « Canal », le deuxième 0 retire « Statut » et le dernier 1 conserve « Montant ».
Nous pouvons donc simplement lire cette matrice comme « garder, retirer, retirer, garder ».
Cette technique devient encore plus utile lorsqu’elle est associée à RECHERCHEX.
Saisissons par exemple « CMD-1007 » dans J7.
Nous voulons retrouver cette commande, mais uniquement afficher le client et le montant.
Nous saisissons :
=RECHERCHEX(J7;Commandes[N commande];FILTRE(Commandes[[Client]:[Montant]];{1.0.0.1});"Commande introuvable")
Nous appuyons sur [Entrée].
Excel affiche « Emma Moreau » dans la première cellule et « 189 » dans la cellule voisine.
RECHERCHEX a donc trouvé la ligne correspondant à CMD-1007, mais la fonction FILTRE lui a fourni uniquement les colonnes Client et Montant.
Si nous remplacions notre masque par :
={1.1.0.1}
nous conserverions cette fois Client, Canal et Montant.
Sur les versions récentes d’Excel 365, la fonction CHOISIRCOLS peut également sélectionner directement des colonnes précises. Elle sera souvent plus lisible pour un nouveau fichier, mais cette méthode avec les accolades reste particulièrement intéressante pour comprendre une idée importante : une constante matricielle peut aussi servir de masque composé de 1 et de 0 pour dire à Excel quelles informations conserver.
5. Découper un texte avec plusieurs séparateurs en une seule formule
Terminons avec la colonne « Détail brut ».
Dans G7, nous avons par exemple :
« Léa Martin - Express / Lille ; 2 colis »
Cette cellule contient en réalité quatre informations : le client, le type de livraison, la ville et le nombre de colis.
Le problème, c’est qu’elles sont séparées avec trois caractères différents : un tiret, une barre oblique et un point-virgule.
Avec Excel 365, nous pouvons utiliser la fonction FRACTIONNER.TEXTE.
Dans J14, essayons tout d’abord :
=FRACTIONNER.TEXTE(G7;"-")
Après validation, Excel coupe bien notre texte au niveau du tiret.
Nous récupérons « Léa Martin » d’un côté, mais tout le reste, « Express / Lille ; 2 colis », reste regroupé dans une deuxième cellule.
Nous pourrions imbriquer plusieurs FRACTIONNER.TEXTE, mais les accolades vont justement nous éviter cela.
Nous remplaçons notre séparateur unique par une constante contenant les trois caractères :
=FRACTIONNER.TEXTE(G7;{"-" ;"/" ;";"})
Nous validons avec [Entrée].
Excel découpe cette fois notre texte en quatre cellules : « Léa Martin », « Express », « Lille » et « 2 colis ».
Une seule formule a donc traité simultanément trois séparateurs différents.
Nous pourrions bien sûr utiliser « Données », puis « Convertir » pour séparer manuellement une colonne.
Mais la formule présente ici un avantage très concret : le résultat reste lié à la cellule source.
Si nous remplaçons le contenu de G7, les quatre informations extraites se mettent immédiatement à jour.
Pensez simplement à laisser suffisamment de cellules vides à droite de la formule. FRACTIONNER.TEXTE génère un tableau dynamique et doit pouvoir « déverser » son résultat. Si une cellule contient déjà quelque chose dans cette zone, Excel affichera l’erreur « #PROPAGATION! ».
