Microsoft 365 Supprimer les blancs se trouvant à la fin d'une liste en cascade

papillondelumiere · 2 Août 2020

Bonjour à tous et toutes,

Je suis à la recherche d'une formule qui me permette d'avoir une liste déroulante en cascade sans blanc. J'ai déjà regardé plusieurs discussions sur le sujet présentes sur ce forum et après plusieurs heures d'essais en vain, je n'arrive pas à l'appliquer à mon cas.

Je vous joins un fichier permettant de vous illustrer mon cas. Mon fichier comporte cinq feuilles :
- La première correspond à ma liste de matière première (qui peut évoluer dans le temps).
- La deuxième correspond à la réception de mes matières premières (tenue de manière chronologique).
- La troisième correspond à ma production journalière (de soupe, de ratatouille etc.) dans laquelle je précise quelles matières premières j'ai utilisé (quel bocal, quelle capsule et quels ingrédients) et dans quel n° de lot j'ai pioché.
- La quatrième et cinquième feuille correspondent à des feuilles (masquées) me permettant d'effectuer des calculs pour mes listes déroulantes en cascade. Quand je choisis une matière première, on me propose ensuite les n° de lots associés à cette matière première (quand je choisis l'ail, on me propose seulement Lotail1, Lotail2 etc.).

Mon soucis est le suivant : Je souhaite avoir dans mes listes déroulantes que mes valeurs et non les espaces vides qui apparaissent en blanc.

Merci pour le temps que vous pourriez prendre pour m'aider. Je reste à votre disposition pour plus d'informations.
Belle journée !

goube · 3 Août 2020

Re,
Dans ce cas, une proposition avec des listes crées avec Power Query et une macro d'actualisation des dites listes à l'ouverture de la feuille Paramètres.
Je me suis permis de modifier la structure de ton fichier.
Teste et dis moi si cela peut te convenir.
Cordialement.

goube · 2 Août 2020

Bonsoir,

Pour solutionner ton souci, pas de lignes vides dans un tableau.

Un peu de lecture : https://fauconnier.developpez.com/tutoriels/tableaux-structures/#LIII-F-2
extrait :

Cependant, je conseille vivement de ne laisser aucune ligne vide dans un tableau structuré. Les mécanismes de dimensionnement automatique lors de l’ajout de données, la recopie automatique des formules et mises en forme conditionnelles permettent aisément de ne pas devoir prévoir de nouvelles données.

La présence de lignes entièrement vides dans le tableau ne peut que nuire à l’analyse des données (TCD, filtres automatiques, formules…). Par exemple, si je veux compter le nombre de fiches dans mon tableau structuré, je peux utiliser la formule =Lignes(Tableau). Cependant, la fonction compte également les lignes vides, ce qui empêche une analyse correcte.

Cordialement.

papillondelumiere · 3 Août 2020

Bonjour Goube,

Merci pour ta réponse ! J'avais pensé judicieux d'entrer mes données une ligne sur deux puisque dans ma 'Feuille cachée 1' je transpose ma liste de matière première et que pour réaliser ma liste en cascade j'ai besoin d'une colonne vide à la gauche de chaque matière première. J'ai tout de même suivis ton conseil et enlevées les lignes vides de mon tableau dans mon fichier original.

Comme tu peux le voir dans le fichier que tu as joint à ton message, il y a un décalage dans ma 'Feuille cachée 1' :

Ainsi, je me demande maintenant si dans ma fonction suivante
=SI(TRANSPOSE('Liste matière première'!A2:A998)=0;"";TRANSPOSE('Liste matière première'!A2:A998))
il serait possible d'indiquer à Excel que je souhaite insérer une colonne vide à la gauche de chaque matière première ?

Quant aux vides, je me référais à ceux se trouvant à la fin de ma liste déroulante après mes données et qui font que le curseur descend très bas inutilement. Est-ce possible de les enlever ?

Merci encore pour le temps que vous pourriez prendre.
Bonne journée

goube · 3 Août 2020

Bonjour,

Est il possible d'utiliser les macros et connais tu Power Query ?
Cordialement.

papillondelumiere · 3 Août 2020

Il est visiblement possible d'utiliser les macros (je viens d'ajouter l'onglet Développeur à mon ruban), cependant je ne sais pas encore l'utiliser. Quant à Power Query je ne le connais pas non plus.

Merci et bel après midi

goube · 3 Août 2020

Re,
Dans ce cas, une proposition avec des listes crées avec Power Query et une macro d'actualisation des dites listes à l'ouverture de la feuille Paramètres.
Je me suis permis de modifier la structure de ton fichier.
Teste et dis moi si cela peut te convenir.
Cordialement.

papillondelumiere · 3 Août 2020

Cela semble super, je me permets de prendre quelques minutes afin de m'approprier ton document et de te donner une réponse (à savoir si cela me convient ou non). Merci beaucoup pour le temps que tu as pris !

papillondelumiere · 3 Août 2020

J'ai finis ma lecture et ton fichier est vraiment plus clair et simple d'utilisation que mon fichier initial ! Il y a deux/trois points que j'aimerais voir avec toi si tu as le temps pour cela.

Il s'avère que dans mon cas les n° de lot pourront être entrés manuellement uniquement pour les légumes (qui sont produits sur notre ferme et pour lesquels je peux donc donner le n° de lot de mon choix puisque nous sommes les fournisseurs). Quant aux autres matières premières (contenants, couvercles et autres aliments) les n° de lot seront choisis par les différents fournisseurs et ne seront donc pas uniformes entres eux.
Je m'interroge quant à la mise à jour du tableau se trouvant dans les colonnes N et O de la feuille 'Paramètres', comment se fait-elle ?
Enfin, si j'ajoute la matière première "Aubergine" comment Excel comprend-il que mon n° de lot Aubergine001 existe (et qu'il puisse me l'afficher dans ma feuille 'Production journalière') ? Cela doit être lié au point précédent je suppose.

Sinon, j'ai bien apprécié ton code "CON", "COU", "MAT" qui s'avère être très utile.
Merci bien Goube

goube · 4 Août 2020

Re,

papillondelumiere à dit:
Je m'interroge quant à la mise à jour du tableau se trouvant dans les colonnes N et O de la feuille 'Paramètres', comment se fait-elle ?

Il s'agit d'une requête Power Query issue du tableau de la feuille Réception matière première.

Pour afficher les requêtes, Onglet Données, Requêtes et connexions une fenêtre s'affiche sur la droite de l'écran.

papillondelumiere à dit:
Enfin, si j'ajoute la matière première "Aubergine" comment Excel comprend-il que mon n° de lot Aubergine001 existe (et qu'il puisse me l'afficher dans ma feuille 'Production journalière') ? Cela doit être lié au point précédent je suppose.

- Dans la feuille Paramètres, saisir sur la première ligne vide de la colonne E le nouveau produit
-Activer la feuille Réception matière première, se positionner sur la dernière ligne du tableau en colonne D, tabuler sur la droite pour générer une nouvelle ligne (ce qui activera la validation de données, la liste déroulante est triée 1° par type, 2° par ordre alphabétique)
- Saisir en colonne C la nouvelle réception
- Activer la feuille Production journalière, les listes déroulantes des colonnes I & K doivent être mises à jour.

L'ouverture ou la fermeture de la feuille Paramètres actualise les requêtes du classeur. Sinon pour actualiser manuellement, Onglet Données, Actualiser tout ou dans la fenêtre des connexions clic droit sur une requête, Actualiser.

Il est possible de créer des formulaires de saisie pour alimenter différents tableaux.

Cordialement.

papillondelumiere · 5 Août 2020

Bonjour Goube,

Je lis tout juste ta réponse de la veille et te remercie encore une fois pour ton aide et la clarté de ta réponse. Tu as parfaitement répondu à mon soucis, cela fait plaisir !

Dernière question : Afin de reproduire cela dans mon classeur d'origine, dois-je comme toi effectuer toutes les manipulations ou de simples copié-collé seraient suffisants ?

Bonne journée !

goube · 5 Août 2020

Bonjour papillondelumiere, le forum,

Content de voir que ma proposition répond à une partie de tes demandes.

papillondelumiere à dit:
Dernière question : Afin de reproduire cela dans mon classeur d'origine, dois-je comme toi effectuer toutes les manipulations ou de simples copié-collé seraient suffisants ?

les copiés-collés devraient convenir, mais il faudra certainement en adapter certains.

Bien garder les mêmes noms pour que les formules et requêtes fonctionnent. Sinon adapter.

Pour copier les requêtes Onglet Données, Requêtes et connexions,
dans la fenêtre qui s'ouvre sur la droite de l'écran, double clic sur la requête à copier ou clic droit Modifier,
Onglet Accueil, Section Requête, Editeur avancé, coller le code ci-dessous.

pour la requête : TBaseProduit (tableau6)

VB:

let
    Source = Excel.CurrentWorkbook(){[Name="TProduit"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Nom", type text}, {"Type", type text}, {"Référence", type text}}),
    #"Texte inséré avant le délimiteur" = Table.AddColumn(#"Type modifié", "Texte avant le délimiteur", each Text.BeforeDelimiter([Nom], "_"), type text),
    #"Colonnes renommées" = Table.RenameColumns(#"Texte inséré avant le délimiteur",{{"Texte avant le délimiteur", "Texte"}}),
    #"Lignes triées" = Table.Sort(#"Colonnes renommées",{{"Type", Order.Ascending}, {"Nom", Order.Ascending}})
in
    #"Lignes triées"

pour la requête : TLot (tableau5)

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="TReception"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Date réception", type any}, {"N° de lot MP", type text}, {"Nom MP", type text}, {"Référence MP", type text}}),
    #"Lignes triées" = Table.Sort(#"Type modifié",{{"Nom MP", Order.Ascending}}),
    #"Colonnes permutées" = Table.ReorderColumns(#"Lignes triées",{"Date réception", "Nom MP", "N° de lot MP", "Référence MP"}),
    #"Autres colonnes supprimées" = Table.SelectColumns(#"Colonnes permutées",{"Nom MP", "N° de lot MP"})
in
    #"Autres colonnes supprimées"

Macros à placer dans la feuille Paramètres.
Onglet Développeur, Visual Basic, dans la fenêtre de gauche cliquer sur la feuille Paramètres (ou sur celles qui contiendra les différents tableaux), coller les macros suivantes.

Code:

Option Explicit
Private Sub Worksheet_Activate()
    ThisWorkbook.RefreshAll
End Sub

Private Sub Worksheet_Deactivate()
    ThisWorkbook.RefreshAll
End Sub

Si tu rencontres des problèmes, n'hésites pas à me recontacter.

Bonne continuation.

papillondelumiere · 27 Août 2020

Bonjour Goube,

Cela fait déjà plusieurs semaines que tu m'as aidé sur ce post, je t'en remercie encore et je reviens vers toi pour te poser une question d'amélioration. Si tu as le temps et l'envie de te pencher sur la question, voilà ce que je souhaite faire à partir de mon classeur.

Mes listes déroulantes en cascade se mettent automatiquement à jour dès que j'entre une nouvelle matière première et je souhaiterais désormais que cette liste déroulante ne me propose plus les numéros de lots pour lesquels le stock est épuisé. Cela peut-il se faire ? Si oui comment ? A savoir que dans mon classeur je dispose d'une colonne précisant le stock disponible pour chaque numéro de lot et qu'une fois le stock fini la valeur "0" est affichée.

Merci pour le temps que tu pourrais prendre.
Belle fin de journée.

goube · 27 Août 2020

Bonjour papillondelumiere,

Pourrais tu mettre une copie de ton fichier sur le site que je puisse voir ce qu'il est possible de faire.
Cordialement.

papillondelumiere · 29 Août 2020

Bonjour Goube,
Je n'avais pas vu ta réponse excuse-moi. Oui bien sûr, le voici.
Merci et bonne journée !

goube · 29 Août 2020

Bonjour papillondelumiere,

A quel endroit apparait le stock ?

papillondelumiere à dit:
je souhaiterais désormais que cette liste déroulante ne me propose plus les numéros de lots pour lesquels le stock est épuisé.

Cordialement.

Microsoft 365 Supprimer les blancs se trouvant à la fin d'une liste en cascade

papillondelumiere

XLDnaute Nouveau

Pièces jointes

goube

goube

XLDnaute Accro

Pièces jointes

papillondelumiere

XLDnaute Nouveau

goube

XLDnaute Accro

papillondelumiere

XLDnaute Nouveau

goube

XLDnaute Accro

Pièces jointes

papillondelumiere

XLDnaute Nouveau

papillondelumiere

XLDnaute Nouveau

goube

XLDnaute Accro

Pièces jointes

papillondelumiere

XLDnaute Nouveau

goube

XLDnaute Accro

papillondelumiere

XLDnaute Nouveau

goube

XLDnaute Accro

papillondelumiere

XLDnaute Nouveau

Pièces jointes

goube

XLDnaute Accro

Discussions similaires