Vous avez construit un tableau Excel parfait, mais dès que vous essayez d'utiliser ses en-têtes dans un menu déroulant, tout se brise. La validation des données d'Excel est notoirement pointilleuse avec les tableaux, mais il existe une solution de contournement intelligente. Alors, arrêtez de coder en dur vos menus et utilisez plutôt cette synchronisation dynamique.
Imaginez que vous ayez un tableau Excel formaté nommé T_Inventaire. Dans la zone de recherche, vous souhaitez deux menus déroulants : un dans la cellule H2 pour sélectionner un produit et un dans la cellule I1 pour sélectionner un attribut (comme le coût ou le fournisseur). Lorsque les deux sont sélectionnés, la cellule I2 doit renvoyer le résultat correct.
Sans l'astuce que je vais vous montrer, vous pourriez saisir manuellement un attribut dans la cellule I1. Cependant, cela empêche instantanément l’automatisation de votre recherche, ce qui donne lieu à une feuille de calcul moins professionnelle et à un processus qui s’interrompra avec une seule faute de frappe.
Une autre option consiste à pointer vers la plage fixe $A$1:$E$1 dans une règle de validation des données. Le problème avec cette approche est que si vous ajoutez une nouvelle colonne à votre table, la plage référencée ne s'étendra pas automatiquement.
En bref, vous voulez un menu qui se met à jour à mesure que votre table s'agrandit.
Pourquoi les tableaux interrompent la validation des données
Les tableaux Excel utilisent des références structurées (comme T_Inventory[#Headers]), qui sont excellents car ils s'ajustent automatiquement lorsque les données changent. Le problème est que la validation des données attend une plage spécifique de cellules ou un nom défini, mais elle ne comprend pas nativement la syntaxe de référence structurée entre crochets utilisée par les tableaux.
Pour contourner ce problème, utilisez une plage nommée comme pont entre votre table et l'outil de validation des données.
Pourquoi ma formule Excel a-t-elle des doubles crochets ?
De nombreux crochets peuvent sembler désordonnés, mais ils sont là pour une raison.
La solution : Mettre en place votre recherche dynamique
Construire ce moteur de recherche comporte trois étapes. Cependant, l'étape 2 est celle où le seul héros L'astuce est révélée : un pont intelligent qui rend vos menus déroulants vraiment dynamiques et incassables.
Étape 1 : Créez une plage nommée pour les produits (cellule H2)
Bien que la validation des données déteste les références structurées, elle aime les plages nommées. C'est le moyen le plus simple d'obtenir la liste de produits dans votre zone de recherche.
Tout d’abord, sélectionnez toutes les cellules de la colonne Produit.
Passez votre souris sur le bord supérieur de la cellule d'en-tête Produit jusqu'à ce que le curseur se transforme en flèche noire vers le bas, puis cliquez une fois. Cela sélectionne les données de cette colonne sans sélectionner l'en-tête.
Ensuite, dans la zone Nom, saisissez un nom mémorable (comme Liste de produits) et appuyez sur Entrée.
Étant donné que cette plage nommée fait partie d’un tableau, elle s’agrandira automatiquement si vous ajoutez de nouvelles lignes.
Ensuite, sélectionnez la cellule H2 (où ira la liste déroulante des produits) et dans l'onglet Données, cliquez sur « Validation des données ».
Enfin, dans le champ Autoriser, sélectionnez « Liste », tapez un signe égal (=) suivi du nom que vous venez de créer, puis cliquez sur « OK ».
Désormais, lorsque vous sélectionnez la cellule H2, une liste déroulante de votre produit apparaît.
Comment créer une liste déroulante à partir d'une colonne de données dans Excel
La méthode que vous utilisez dépend de la façon dont vos données sont formatées.
Bien que la validation des données ferme généralement la porte aux en-têtes de tableau, vous pouvez contourner cette restriction en créant une plage nommée dynamique. Pour ce faire, dans l'onglet Formules, cliquez d'abord sur « Gestionnaire de noms ». Ensuite, cliquez sur « Nouveau ».
Dans le champ Nom, saisissez Liste d'en-tête. Ensuite, supprimez tout ce qui se trouve dans le champ Fait référence à et, avec le curseur dans le champ désormais vide, sélectionnez tous les en-têtes de votre tableau. Vérifiez que le champ contient désormais une référence structurée aux en-têtes de votre tableau (par opposition aux références directes aux cellules) :
=T_Inventory[#Headers]
Ensuite, cliquez sur « OK » et « Fermer » pour fermer les boîtes de dialogue.
Maintenant, sélectionnez la cellule I1 (où ira la liste déroulante des attributs) et dans la boîte de dialogue Validation des données, sélectionnez « Liste » dans le champ Autoriser, comme vous l'avez fait à l'étape 1. Cette fois, cependant, dans le champ Source, tapez un signe égal (=) suivi du nom que vous venez de créer.
Lorsque vous cliquez sur « OK », vous verrez que le menu déroulant de la cellule I1 contient les en-têtes de votre tableau.
Contrairement à d’autres méthodes, ce lien est totalement dynamique. Si vous renommez un en-tête de colonne ou même le tableau entier, le gestionnaire de noms se mettra automatiquement à jour et votre menu déroulant ne sera jamais interrompu.
Étape 3 : La formule du moteur (cellule I2)
Avec un produit sélectionné dans la cellule H2 et un attribut sélectionné dans la cellule I1, vous n'avez besoin que d'une formule supplémentaire pour tout rassembler. Dans ce cas, vous devez utiliser INDEX et MATCH :
=INDEX(T_Inventory,MATCH(H2,T_Inventory[Product],0),MATCH(I1,T_Inventory[#Headers],0))
Assurez-vous que le nom de la colonne dans la formule ([Product]) correspond exactement au nom de la première colonne de votre table.
Voici comment fonctionne la formule :
- INDICE(T_Inventaire…) : Il s'agit de la carte de l'ensemble de votre ensemble de données.
- MATCH(H2…): Cela trouve la ligne spécifique de votre produit.
- CORRESPONDRE(I1…): Cela trouve la colonne spécifique pour votre attribut.
Maintenant, jouez avec votre recherche : sélectionnez un produit et un attribut différents, et voyez le résultat dans la mise à jour de la cellule I2. Cependant, vous remarquerez que lorsque vous sélectionnez « Coût » dans la cellule I1, le résultat dans la cellule I2 est un nombre simple et non formaté plutôt qu'une devise.
En effet, la cellule I2 est un caméléon, basculant entre différents types de données dans vos colonnes. Mais ne vous inquiétez pas, il existe un moyen simple de résoudre ce problème.
Résoudre le problème de formatage
Pour garantir que le résultat dans la cellule I2 est affiché sous forme de devise lorsque vous sélectionnez « Coût », vous devez saisir une règle de formatage conditionnel rapide.
Sélectionnez la cellule et dans l'onglet Accueil, cliquez sur Mise en forme conditionnelle > Nouvelle règle.
Maintenant, cliquez sur « Utiliser une formule pour déterminer les cellules à formater » et dans le champ en bas de la boîte de dialogue, tapez :
=$I$1="Cost"
Ensuite, cliquez sur « Format » et dans l'onglet Numéro, sélectionnez « Comptabilité » ou « Devise » et assurez-vous que le symbole correct est sélectionné. Cliquez ensuite deux fois sur « OK » pour fermer les deux boîtes de dialogue.
Format de devise et de numéro comptable dans Excel : quelle est la différence ?
Donnez du style à vos feuilles de calcul financières !
Désormais, lorsque la cellule I1 contient le mot « Coût », le résultat de la cellule I2 se transforme automatiquement dans la devise sélectionnée.
Utilisez la même méthode pour les autres formats de nombres. Par exemple, pour une date, votre formule serait =$I$1= »Date » et votre sélection de format numérique serait « Date ». Les colonnes de texte et numériques standard ne nécessitent pas de formatage spécial.
Le test final : ajouter une colonne
Le véritable pouvoir de cette configuration réside dans la manière dont elle gère le changement. Dans la cellule F1 (la première cellule à droite de votre tableau), tapez un nouvel en-tête de colonne, comme Réapprovisionnépour créer une nouvelle colonne de tableau. Ensuite, développez votre liste déroulante dans la cellule I1 et vous verrez que votre nouvel en-tête de colonne est déjà là.
Les listes déroulantes de validation des données sont en réalité plus utiles que vous ne le pensez. En plus de les utiliser pour créer un sélecteur d'en-tête de tableau, vous pouvez les utiliser pour créer des graphiques dynamiques qui se mettent à jour en fonction de votre sélection. Si vous souhaitez aller encore plus loin dans votre automatisation, vous pouvez créer des listes déroulantes dépendantes dans lesquelles les options d'un menu changent en fonction de ce que vous avez choisi dans un autre.
- Système d'exploitation
-
Windows, macOS, iPhone, iPad, Android
- Essai gratuit
-
1 mois
Microsoft 365 inclut l'accès aux applications Office telles que Word, Excel et PowerPoint sur jusqu'à cinq appareils, 1 To de stockage OneDrive et bien plus encore.
Vous pouvez lire l’article original (en Angais) sur le sitewww.howtogeek.com