Microsoft 365 Valeur saisie dans liste déroulante non reprise dans consolidation

[Carnould]

Bonjour à tous,
J'ai une saisie d'information dont certaines valeurs sont contrôlées à l'aide d'une liste déroulante.
Lorsque j'utilise les valeurs saisies de cette manière par la fonction "consolider" (Données/Consolider) les valeurs saisies ne sont pas reprises alors que tout fonctionne correctement si je les utilise dans des formules (de type SI(...)).
Merci par avance pour votre aide.
Carnould

 

[chris]

Bonjour

Données, Consolider date de Mathusalem et n'est pas compatible avec les fonctionnalités implémentées depuis 2007
Je ne saurais trop te conseiller de moderniser ton approche surtout que tu as 365 et donc pas mal d'outils plus performants.

Si tu veux qu'on regarde néanmoins ton problème il faut joindre un fichier représentatif.

 

[Carnould]

Bonjour Chris,
Merci pour ce précieux conseil et surtout pour la rapidité de ta réponse.
Ton post a attisé ma curiosité de débutant en Excel et je découvre qu'il existe des "tableaux croisés dynamiques pouvant exploiter plusieurs tables". Est-ce à cela que tu pensais pour remplacer "données/consolider" ?
J'ai un dossier ne contenant que des feuilles identiques bien structurées en colonnes dont la première contient une sorte d'index unique chaque ligne de toutes les feuilles.
Je veux récupérer toutes ces lignes dynamiquement dans un autre classeur (ou à défaut dans une autre feuille de ce classeur) pour ensuite exploiter les infos (tableaux croisés, etc...).
Si besoin je ferai un classeur avec des données non confidentielles pour te les envoyer.
Merci
Christian

 

[chris]

Bonjour

Si tes sources ont toutes la même structure et peuvent être mises sous forme de tableau structuré

Tableaux structurés Excel

Tableaux structurés Excel

fauconnier.developpez.com

ce sera un jeu d'enfant de les consolider avec PowerQuery

Oui poste un classeur représentatif pour qu'on te donne le cheminement

 

[Carnould]

Bonjour,

Quelle réactivité ! Merci.
Voici mon dossier épuré de ce qui est confidentiel. J'y joins un dossier de tarifs, nécessaire pour le bon fonctionnement des feuilles. Chaque feuille représente une personne. Chaque ligne représente une activité de cette personne avec ou sans partie financière.

Je souhaite réunir dans une seule feuille les colonnes de B à P, la partie "vol" du tableau avec la ligne 3 comme titres et les lignes 4 à 108 comme données.

La colonne B est la concaténation de plusieurs éléments d'une ligne et représente un index unique. J'ai imaginé cela pour éviter d'avoir des cumuls de ligne mais bien des lignes séparées dans le dossier récapitulatif dans le cas ou j'aurais utilisé les tableaux croisés dynamiques. Si certaines lignes n'ont pas cet index cela signifie qu'elles ne doivent pas être réunies dans la feuille récapitulative (événements financiers seuls, sans vol ne m'intéresse pas à ce niveau là).

La colonne C est là pour rattacher la ligne à une personne dont le nom est le nom de la feuille.
Autrement dit : les colonnes B et C ne sont pas nécessaire dans mes feuilles mais j'ai imaginé qu'elles le seraient dans le dossier récapitulatif.

En exploitation il y aura une centaine de feuilles dans ce dossier principal (nombre évolutif en fonction de l'arrivée de nouvelles personnes).
Merci !
Christian

 

[chris]

Bonjour

Compte tenu de la structure de tes tableaux, on ne peut les mettre sous forme de tableaux structurés.
Du coup il faut créer une plage nommée pour chaque onglet (inutile de prendre plus de lignes que de dates, d'où la formule donnant une plage dynamique)
J'ai pris le nom de l'onglet sans espaces ce qui permet de récupérer la source et donc le nom du pilote et ai donc limité aux colonnes D à P (la 1ère a été reconstituée dans la synthèse mais pas sûr de l'utilité)

J'ai affiché le résultat de la requête dans un onglet mais on pourrait construire un TCD directement sur la requête et donc éviter cet onglet.

Maj par Données, Actualiser Tout

 

[Carnould]

Wahou !
Je n'ai pas tout compris pour pouvoir reproduire cela mais je vais approfondir. En tout cas c'est efficace. J'imagine pouvoir faire d'autres requêtes à partir de cette synthèse à défaut de pouvoir faire des tris directement sur celle-ci.
Je trierai sur cette fameuse colonne B (concaténation) qui me donne un tri ascendant intéressant.
Un grand merci. Cela vaut bien l'abonnement que je viens de prendre à l'instant.
Il me reste à trouver où et comment cela a été fait. Je débute en Excel mais j'espère que mon expérience d'il y a 30 ans de Cobol, GAP et autres outils du genre m'aideront à retrouver des réflexes.
Bien cordialement
Christian

 

[chris]

RE

Donc 1ère chose à faire, la création des plages nommées : regarde dans l'exemple (onglet Formules, Gestionnaire de noms)

Ensuite on lance PowerQuery : Données, Obtenir des données, A partir d'autres sources, Requête Vide
Dans PowerQuery :

• dans la barre de formule taper : =Excel.CurrentWorkBook() (en respectant la casse)
  cela affiche un tableau de deux colonnes.
• dérouler Name et ajouter un filtre : filtre textuel, est différent de Synthèse (pour éviter que la synthèse une fois finie se mélange aux données des onglets)
  Si tu as d'autres plages nommées dans ton classeur réel il faut aussi les filtrer
• cliquer ensuite sur la double flèche près du titre Content
• onglet Transformer, utiliser la 1ère ligne comme en-tête
• renommer les colonnes des minutes des heures de début et de fin
• dans la colonne Appareil filtrer null et Appareil (comme dans Excel)
• sélectionner le colonne Date et Accueil, type de données, Date
• si tu veux remettre ta concaténation : onglet Ajouter une colonne, Colonne personnalisée
  nom ID, formule
  =[Appareil]&Number.ToText(Date.Year([Date]))&Number.ToText(Date.Month([Date]),"00")&Number.ToText(Date.Day([Date]),"00")&Number.ToText([H. Début],"000")&Number.ToText([H. Début2],"00")
• renommer la requête Synthèse (dans le volet de droite)
• Sortir par Fermer et charger dans, Table et choisir l'emplacement

Tu vois rien de très compliqué...
Toute plage nommée ajoutée (autre onglet) et toute ligne ajoutée dans un onglet sont automatiquement prises en compte à l'actualisation.

 

[Carnould]

Si avec ça je n'y arrive pas demain ou jeudi, je me mettrai au tricot.
Mille merci Chris

 

[Carnould]

Bonsoir Chris,
J'ai bien avancé dans la compréhension de ce que tu as fait. Je n'ai pas encore tout compris mais arrive toutefois à reproduire (parfois un peu bêtement par mimétisme).
Je te pose encore cette question :
La démarche suivante est-elle la bonne pour créer une nouvelle feuille (nouvel onglet pilote).
-par clic droit sur l'onglet je créé une COPIE d'un onglet modèle. Cela me génère automatiquement un nouveau nom dans le gestionnaire des noms en gardant le nom de l'onglet d'origine.
-je modifie le nom du pilote de cet onglet dupliqué. Le nom dans le gestionnaire ne changeant pas
-j'édite le nom dans le gestionnaire des noms pour l'adapter au nouveau pilote.
-Impossible de changer le champ "étendue" pour le mettre à "classeur" comme tu l'avais fait mais peut-être n'est-ce pas gênant, je n'en mesure pas les conséquences.

Est-ce ainsi que je devrai procéder pour créer toutes mes feuilles ?
Merci par avance.
Christian

 

[chris]

Bonjour

Oui mais il faut, je pense, recréer le nom car pas sûr qu'une portée onglet suffise.

Peut-être plus simple de partir d'un modèle vierge (juste les titres et formules) et créer le nom après duplication.

Eventuellement si tu as beaucoup d'onglets à faire d'un coup tu pourrais utiliser VBA pour créer les noms

 

[Carnould]

Bonsoir Chris,

J'ai un peu modifié ce dossier des vols pour inclure des feuilles servant aux contrôles de valeurs (Param_div, Param_tarif).
J'ai supprimé certaines colonnes devenues obsolètes pour notre activité.
J'ai repris à zéro la construction d'une synthèse (que j'ai appelée "Planche").
Je n'ai pu suivre tes conseils pour créer les noms avec VBA que je ne connais pas.

J'ai essayé la solution d'un modèle vierge et de dupliquer puis créer les noms des plages de données "à la main" : la tâche sera longue et source d'erreur pour la reprise de l'existant (~80 feuilles) mais je peux le faire pour la reprise de l'existant s'il n'y a pas d'autres solutions que de passer par VBA.

Lorsque je duplique une feuille ayant déjà son "nom de plage", comme je te le disais dans un post précédent, je n'ai pu vérifier si le résultat est incorrect (cas des 2 feuilles les plus à droite) car lorsque je quitte l'éditeur PowerQuery, seule la fonction "Fermer et charger" est disponible. Celle de "Fermer et Charger dans..." Table est grisée.

Autre sujet indépendant : a ce stade de mon fichier, est-il encore temps d'avoir une construction de tables dynamiques pour les feuilles "pilotes" ? Si oui, comment la faire sans que cela casse toutes les autres fonctions en place ?

Merci pour ton aide.
Bien cordialement
Christian

 

[chris]

Bonjour

Pourquoi prendre les colonnes B et C alors que j'ai montré au #6 qu'elles ne sont pas utiles à la synthèse ?

A priori tu n'es pas parti d'une copie du modèle pour créer Paul Hochon et du coup la plage nommée est un second JeanBon2paris

Pour Fermer et charger, si tu repars de mon fichier il a en mémoire de ne charger que le connexion, cela peut venir de cela

....
Autre sujet indépendant : a ce stade de mon fichier, est-il encore temps d'avoir une construction de tables dynamiques pour les feuilles "pilotes" ? Si oui, comment la faire sans que cela casse toutes les autres fonctions en place ?

Pas compris ce que tu appelles "avoir une construction de tables dynamiques pour les feuilles pilotes"

 

[Carnould]

Bonsoir Chris
Pour la col B, j'ai hésité car il me semblait plus pratique de l'avoir dès l'origine.
Pour la C, mettre le nom du pilote en regard de chaque ligne le semblait important pour la suite et je ne comprenait pas comment tu avais fait pour le mettre dans la synthèse alors que l'info n'était pas dans la ligne.
Pour Paul Hochon c'est volontairement que j'ai procédé par copie pour voir si cela aurait un impact sur le résultat.

Pour la table dynamique mal comprise. Je voulais dire que j'avais créé mes feuilles en ayant figé un nombre de lignes et en ayant mis les formules dans chacune d'elles. Au lieu d'avoir un tableau qui s'agrandit tout seul dès que l'on ajoute une ligne et qui propage les formules sur les nouvelles lignes. Table dynamique n'est peut être pas le bon terme. Et je ne sais comment le faire maintenant que les formules sont introduites.

Pour Fermer et Charger, c'est un paramètre quelque part ?

Merci Chris. Tu dors parfois ? Tu reponds tellement vite quelque soit l'heure...

Christian

 

[chris]

Bonjour

Quand on synthétise des plage nommées on récupère le nom de la plage, ce pourquoi j'avais pris le nom du pilote sans espace qui permet de remplacer la colonne C
On pourrait même mettre un _ à la place de l'espace dans le nom de la plage et remplacer le _ par l'espace dans la requête.

Comme déjà dit je ne vois pas trop l'utilité de la colonne B, non utilisée par ailleurs semble-t-il.
Si elle est utile dans chaque onglet garde-là, si elle est utile seulement dans la synthèse, pas d’intérêt de conserver toutes ces colonnes dans le classeur là où une suffit. Sinon...

Pour les tableaux structurés, cela éviterait de nommer les plages, il suffirait juste de correctement nommer les tableaux (ne pas garder le nom automatique type Tableau1).
Cependant pour pouvoir respecter la norme des tableaux structurés il faudrait

• supprimer ta ligne 4,
• dé-fusionner tes titres en colonne F à I
• n'utiliser comme titres du tableau que la ligne 3
• supprimer les lignes entièrement vides

Pour Fermer et Charger, si on commence une nouvelle requête, sur 365 si on ne précise rien, par défaut il sort en créant une table par requête.
J'ai paramétré le mien pour ne pas le faire.
Sur l'add on (2010-2013), si on choisit à la première sortie d'une requête créée dans un classeur Fermer et charger dans, Connexion seulement, il conserve ce paramètre pour ce classeur.