Depuis que les menus déroulants de validation des données ont été ajoutés à Excel en 1997, les gens ont essayé de trouver un moyen de modifier le deuxième menu déroulant en fonction de la sélection dans le premier menu déroulant.
Par exemple, si vous choisissez Fruit dans A2, le menu déroulant A4 offrira Apple, Banana, Cherry. Mais si vous choisissez les herbes de A2, la liste en A4 offrirait de l'anis, du basilic, de la cannelle. Il y a eu de nombreuses solutions au fil des ans. Je l'ai couvert au moins deux fois dans le podcast:
- La méthode classique utilisait beaucoup de plages nommées comme le montre l'épisode 383.
- Une autre méthode utilisait les formules OFFSET dans l'épisode 1606.
Avec la sortie des nouvelles formules Dynamic Array dans Public Preview, la nouvelle fonction FILTER nous donnera une autre façon de faire la validation dépendante.
Dites que ceci est votre base de données de produits:
Utilisez une formule de =SORT(UNIQUE(B4:B23))dans D4 pour obtenir une liste unique des classifications. Il s'agit d'un tout nouveau type de formule. Une formule dans D4 renvoie de nombreuses réponses qui se répandront dans de nombreuses cellules. Pour faire référence à la gamme Spiller, vous utiliseriez à la =D4#place de =D4.
Sélectionnez une cellule pour contenir le menu Validation des données. Choisissez Alt + DL pour ouvrir la validation des données. Remplacez Autoriser par "Liste". Spécifiez =D4#comme source de la liste. Notez que le Hashtag (#) est le Spiller - cela signifie que vous faites référence à toute la gamme Spiller.
Le plan est que quelqu'un choisisse une classification dans le premier menu déroulant. Ensuite, une formule de =FILTER(A4:A23,B4:B23=H3,"Choose Class First")dans E4 renverra tous les produits de cette catégorie. Notez que l'utilisation de "Choose Class First" comme troisième argument facultatif. Cela évitera une #VALEUR! erreur d'apparition.
Il peut y avoir un nombre différent d'éléments dans la liste en fonction de la catégorie sélectionnée. La configuration de la validation des données pointant vers =E4#s'étendra ou se réduira avec la longueur de la liste.
Regarder la vidéo
Transcription vidéo
Apprendre Excel à partir de, Podcast Episode 2248: Validation dépendante à l'aide de tableaux.
Et bien Salut. Cela a déjà été abordé deux fois dans le podcast, comment faire la validation dépendante, et ce qu'est la validation dépendante, c'est que vous devez choisir, d'abord, une catégorie, puis, en réponse, à cela, le deuxième menu déroulant deviendra juste le éléments de cette catégorie, et, avant, c'était compliqué, et avec les nouveaux tableaux dynamiques qui ont été annoncés en septembre 2018… et ceux-ci sont en cours de déploiement, vous devez donc avoir Office 365. En ce moment, le 10 octobre, j'ai entendu qu'ils sont sur environ 50% des initiés d'Office, donc ils les déploient très lentement. Ce sera probablement jusqu'au premier semestre de 2019 avant que vous les obteniez, mais cela nous permettra de faire une validation dépendante de manière beaucoup plus facile.
Donc, j'ai deux formules ici. La première formule est l'UNIQUE de toutes les classifications et je l'ai envoyée dans la commande SORT. Donc, cela me donne 1 formule renvoyant 5 résultats et qui vit dans D4. Donc, ici, là où je veux choisir la validation des données, je vais (DL - 1:09)… la SOURCE va être = D4 #. Ce # - nous l'avons appelé le spiller - assurez-vous qu'il renvoie tous les résultats de D4. Donc, si j'ajoute une nouvelle catégorie ici et que cela augmente, D4 # récupérera ce montant supplémentaire, d'accord? (= TRIER (UNIQUE (B4: B23)))
Donc, cette première validation est assez simple, mais maintenant que nous savons que nous avons choisi CITRUS - cela va être plus difficile - je veux filtrer la liste dans la colonne A où l'élément de la colonne B est égal à l'élément choisi d'accord? Donc, d'abord nous devons les laisser choisir quelque chose et ensuite, une fois que je sais que c'est AGRUMES, puis me donner le LIME, l'ORANGE et la TANGERINE, ils choisiraient autre chose. BAIE. Regarde ça. Les journaux scientifiques disent qu'une banane est une baie. Je ne suis pas d'accord avec ça. Cela ne me semble pas une baie, mais ne me blâmez pas. J'utilise juste, vous savez, Internet. BANANE, SURFRE et FRAMBOISE.
Maintenant, vous savez, le problème avec ceci est que quelqu'un va d'abord venir ici sans avoir rien choisi, et, dans ce cas, nous avons CHOISIR LA CLASSE EN PREMIER qui est ce troisième argument qui dit que si rien n'est trouvé, d'accord? Donc, vous savez, de cette façon, si nous commençons dans ce scénario, le choix va être CHOISIR LA CLASSE EN PREMIER. L'idée est qu'ils choisissent la classe, le légume, cette mise à jour, puis ces éléments proviennent de cette liste. La VALIDATION DES DONNÉES ici, bien sûr, eh bien, c'est un autre spiller, = E4 # pour que cela fonctionne, d'accord? Donc, c'est cool. (= FILTRE (A4: A23, B4: B23 = H3, "Choisissez d'abord la classe"))
Consultez mon livre Excel Dynamic Arrays. C'est… ça va être gratuit jusqu'à la fin de 2018. Vérifiez le lien ci-dessous dans la description YouTube, comment vous pouvez le télécharger, pour cet exemple même plus 29 autres exemples d'utilisation de ces éléments.
Eh bien, terminez pour aujourd'hui. Les tableaux dynamiques nous offrent une autre façon de faire la validation dépendante. Si vous n'êtes pas sur Office 365 et que vous ne les avez pas encore, n'hésitez pas à revenir, je suppose, à la vidéo 1606 qui montre l'ancienne façon de procéder.
Je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger le fichier Excel
Pour télécharger le fichier Excel: depend-validation-using-arrays.xlsx
Pour en savoir plus sur les tableaux dynamiques, consultez Tableaux dynamiques Excel directement au point.
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"Ne supprimez jamais un fichier Excel sans l'avoir d'abord sauvegardé."
Mike Alexander
