Découvrez l’astuce Excel qui va RÉVOLUTIONNER vos Tableaux (opérateur # Dièse)
Dans ce tutoriel, je vais vous montrer comment utiliser l'opérateur de déversement # (dièse) dans Excel 365, une fonctionnalité qui va transformer votre façon de travailler avec les formules matricielles dynamiques.
Nous allons découvrir ensemble comment référencer intelligemment des plages de données qui s'ajustent automatiquement, comment créer des listes déroulantes dépendantes sophistiquées, et comment utiliser les noms définis pour rendre vos fichiers plus professionnels et maintenables.
Ces techniques vont vous permettre de gagner un temps considérable dans la gestion de vos tableaux de bord et de vos systèmes de validation de données.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons les ventes d'un magasin de sport réparti sur plusieurs régions de France.
Avant de commencer, il est important de comprendre ce que sont les matrices dynamiques dans Excel 365.
Il s’agit d’une matrice dynamique qui est le résultat d'une formule retournée automatiquement sur plusieurs cellules adjacentes.
Ce comportement s'appelle le « déversement ». Lorsqu'une formule génère une matrice dynamique, Excel affiche une bordure bleue autour de toutes les cellules concernées dès que nous sélectionnons l'une d'entre elles.
L'opérateur # (dièse) est une notation spéciale qui nous permet de référencer l'intégralité d'une matrice dynamique sans avoir à connaître sa taille exacte. Au lieu d'écrire par exemple F7:F8, nous pouvons simplement écrire F7# et Excel comprendra automatiquement qu'il doit prendre toutes les cellules dans lesquelles la formule de F7 a déversé son résultat.
2. Comprendre et utiliser les matrices dynamiques avec l'opérateur
Commençons par créer notre première matrice dynamique.
Nous nous positionnons dans une cellule à droite du tableau (par exemple en F7) et nous allons utiliser la fonction UNIQUE() pour extraire une liste des régions sans doublon. Nous saisissons la formule suivante :
=UNIQUE(A7:A18)
Cette formule va analyser toutes les valeurs de la colonne Région et retourner uniquement les valeurs uniques, non pas comme un seul résultat, mais quatre résultats qui se déversent automatiquement dans les cellules suivantes.
Nous reconnaissons la formule dynamique parce-ce qu'une bordure bleue entoure les quatre cellules déversées.
Cette bordure bleue est l'indicateur visuel qu'Excel utilise pour nous montrer l'étendue de la matrice dynamique.
Passons maintenant à l'utilisation de l'opérateur # pour référencer cette matrice. Nous nous positionnons dans la cellule à droite où nous allons créer une formule qui compte le nombre de régions uniques :
=NBVAL(F7#)
Nous remarquons qu'au moment où nous ajoutons le symbole dièse après F7, Excel change l'affichage et la bordure bleue réapparaît autour de nos quatre régions.
Nous fermons la parenthèse et nous appuyons sur [Entrée]. Le résultat affiché est 4, ce qui correspond bien au nombre de régions uniques.
Il existe une deuxième méthode pour référencer une matrice dynamique : utiliser la souris.
Nous effaçons la formule que nous venons d’insérer et nous recommençons.
Nous saisissons à nouveau « =NBVAL( », puis nous sélectionnons les quatre cellules de la plage déversée en maintenant le bouton de la souris enfoncé.
Au moment où nous relâchons le bouton, Excel a automatiquement converti notre sélection avec le caractère dièse dans la barre de formule.
Si nous avions sélectionné seulement une partie de la matrice, Excel n'aurait pas ajouté le symbole #.
Pour bien comprendre l'avantage de l'opérateur #, ajoutons temporairement une nouvelle ligne dans notre tableau avec :
- « Centre » comme région,
- « Vélo Route » comme produit,
- 380 comme prix et
- 7 comme quantité.
Dès que nous appuyons sur [Entrée], notre matrice dynamique en s'étend automatiquement pour inclure « Centre », et le comptage passe automatiquement de 4 à 5, sans modification de notre part.
C'est toute la puissance de l'opérateur # : il s'adapte dynamiquement aux changements.
Nous supprimons maintenant cette ligne temporaire en cliquant avec le bouton droit sur le numéro de ligne 14 et en choisissant « Supprimer ».
3. Créer un système de listes déroulantes dépendantes
Nous allons maintenant créer un système de listes déroulantes dépendantes.
L'objectif est de permettre à l'utilisateur de sélectionner une région dans une liste, puis de voir apparaître uniquement les produits disponibles dans cette région.
Commençons par préparer notre zone de travail.
La première étape consiste à créer une liste horizontale des régions.
Nous nous positionnons dans J7 et nous saisissons :
=TRANSPOSE(UNIQUE(A7:A18))
Cette formule combine UNIQUE() et TRANSPOSE() qui transforme une liste verticale en liste horizontale.
Nous appuyons sur [Entrée] et nos quatre régions s'affichent horizontalement.
Passons à la génération automatique des produits par région.
Nous nous positionnons dans G8, juste en dessous de « Nord ».
Nous saisissons :
=FILTRE(B7:B18;A7:A18=J7;"")
Le premier argument « B7:B18 » est la plage à filtrer (les produits).
Le deuxième argument « A7:A18=J7 » est notre condition : nous gardons les lignes où la région correspond à J7.
Le troisième argument « "" » permet d'afficher une cellule vide si aucun résultat n'est trouvé, plutôt qu'une erreur.
Nous appuyons sur [Entrée] et nous voyons les trois produits du Nord se déverser.
Pour étendre la formule sur les autres cellules, nous allons la passer en référence absoule, en ajoutant les symboles « $ » devant les plages du tableau :
=FILTRE($B$7:$B$18;$A$7:$A$18=J7;"")
Puis, nous étendons la formule en utilisant la poignée de recopie :
Excel ajuste automatiquement les références.
Créons maintenant la liste déroulante pour la région.
Nous cliquons sur F7, nous supprimons la formule inséré un peu plus tôt, puis nous nous rendons dans « Données » puis « Validation des données ».
Dans « Autoriser », nous sélectionnons « Liste ».
Dans le champ « Source », nous saisissons « =J7# ».
Le symbole # indique que nous référençons toute la matrice dynamique.
Nous cliquons sur « OK » et une flèche déroulante apparaît. Nous la testons et sélectionnons « Nord ».
Passons à la liste déroulante dépendante pour les produits.
Nous allons utiliser RECHERCHEX() pour retourner les produits selon la région sélectionnée.
Testons d'abord dans F8 :
=RECHERCHEX($J$7;$J$7:$M$7;$J$8:$M$10)
Le premier argument est la région cherchée, le deuxième est la plage d'en-têtes, le troisième est la plage de résultats.
Nous étendons jusqu'à la colonne N pour permettre l'ajout de nouvelles régions.
Nous appuyons sur [Entrée] et tous les produits se déversent automatiquement.
Testons en changeant la région en F7 : les produits se mettent à jour instantanément.
Maintenant, nous pouvons couper la formule en la sélectionnant, puis en appuyant sur [Ctrl]+[X].
Il ne reste plus qu’à créer la validation pour les produits.
Nous cliquons sur F8, puis « Données » puis « Validation des données ».
Nous sélectionnons « Liste » et dans « Source », et nous y collons la formule :
Testons maintenant le système complet.
Nous changeons la région en G2 pour « Ouest » et nous vérifions que la liste déroulante en H2 propose uniquement les produits de cette région.
Notre système fonctionne parfaitement.
4. Utiliser les noms définis avec l'opérateur
Notre système fonctionne, mais la formule dans la validation est longue et complexe.
Excel nous offre une solution pour la simplifier : les noms définis.
Nous nous rendons dans « Formules » puis « Définir un nom ».
Dans « Nom », nous saisissons « Produits_Region ».
Dans « Commentaire », nous pouvons écrire : « Retourne la liste des produits pour la région sélectionnée en G7 ».
Dans « Fait référence à », nous collons à nouveau la formule :
Lorsque nous valions, Excel ajoute automatiquement le nom de la feuille de calcul devant chaque référence :
Nous cliquons sur « Fermer ».
Testons ce nom défini :
=Produits_Region
Maintenant tous les produits se déversent.
Changeons la région en G7 : la liste met à jour automatiquement.
Pour finir, modifions notre validation en G8.
Nous effaçons le contenu du champ « Source ».
Nous appuyons sur [F3] pour ouvrir la liste des noms définis, nous sélectionnons « Produits_Region » et nous cliquons sur « OK ».
Excel insère le nom automatiquement.
Nous cliquons sur « OK ».
Notre liste fonctionne exactement comme avant, mais avec un nom défini plus lisible et maintenable.
L'avantage devient évident pour des fichiers complexes : si nous devons modifier la logique, nous n'avons qu'à modifier le nom défini une fois au lieu de modifier toutes les validations individuellement.
Il existe deux approches valides :
- La première, avec formules directes, est plus rapide pour des besoins ponctuels.
- La seconde, avec noms définis, demande plus de temps initial mais rend les fichiers plus professionnels et maintenables. Le choix dépend de votre contexte : fichier personnel rapide ou outil partagé évolutif.