XL 2016 Tableau récap des meilleurs élèves

[Esfloria]

Bonsoir,

Intro : je travaille dans un centre de formation et nous avons besoin de sélectionner sur une grande quantité de candidats un tout petit nombre pré établi.
Chaque formateur a un nombre de dossiers à noter mais chacun fait ça dans son coin sur un tableau Excel (identique dans la structure tout de même).

Mon idée : vu que c'est un peu le rush, j'ai envie de trouver une solution de traitement rapide des résultats. J'ai mis en pj ce à quoi pourrait ressembler le fichier que je voudrais proposer à ma directrice avec des données fictives évidemment ... Merci la fonction aléatoire pour trouver des noms et prénoms !!!

Le projet : je me suis dit que si je récupère toutes les feuilles de mes formateurs et que je les mets dans un seul classeur Excel, je voudrais ensuite pouvoir faire apparaître "automatiquement" dans la feuille [Analyse] par exemple les 30 meilleurs candidats (basés sur la colonne "note finale", tous formateurs confondus (avec possibilité de mise à jour automatique si correction ou ajout ou suppression de données dans les feuilles individuelles).

Jusqu'ici : Le copier/coller ça va être trop pénible et long parce qu'on parle d'éventuellement 300 à 400 lignes à traiter. En cas de loupé, ça va être mortel.
La mise en forme auto c'est bien sur 1 seul tableau mais ... comme je disais : plusieurs formateurs et plusieurs candidats ... Donc ça ne va pas m'aider ...
J'ai essayé les TCD mais je ne vois pas trop comment les agencer pour que ça ressemble à quelques chose d'exploitable.
J'ai testé la fonction consolider mais avec les numéros de dossiers ça m'a sorti n'importe quoi et en plus je n'avais plus les noms et prénoms ...

Quelqu'un aurait-il une idée s'il vous plaît ?

J'ai entendu parler des macro mais comme je ne sais même pas vers quelle solution m'orienter, je ne peux rien en faire

Merci de votre aide !

 

[chris]

Bonjour

Avec 2016 ni macros ni copier coller : PowerQuery.

Met les classeurs dans un dossier et tu synthétises par une requête

Au préalable nommer chaque tableau T_Notes en lieu et place des noms automatiques, type Tableau1

Dans le fichier joint, modifier le chemin d'accès aux classeurs (décompresser le zip dans un dossier) puis Données, Actualiser Tout

 

[Esfloria]

Bonsoir,

Merci pour cette astuces. J'ai pas tout bien compris concernant la partie requête et chemin d'accès ... j'ai essayé de le modifier mais ça ne changeait rien ... J'ai quand même bidouillé un truc à partir de tes explications qui a tout de même fonctionné. J'ai bien réussi la partie sur T_Notes mais après j'ai pataugé complètement. J'ai fini par compiler mes tableaux sur une seule page avec les requêtes (j'aurais au moins appris ça, je ne connaissais pas du tout cet outil) et j'ai utilisé la mise en forme conditionnelle avec "les 10 premiers ..." où j'ai demandé les 30 du coup.

Je me suis baladée dans Requêtes et Connexions pour comprendre NB_Notes mais j'ai rien compris non plus, y a pas grand chose qui s'affichait ... Et pour le chemin d'accès ça marchait pas du tout ... Dans la partie Requêtes j'avais des points d'exclamations pour dire qu'y avait des erreurs.

Pour précision, en fait c'est bien Office 2019 qu'ils ont ... et moi aussi d'ailleurs. Du coup, comme ça pourrait me servir dans une autre entreprise ou chez moi (sait on jamais), sur 2019 est ce qu'il y a un autre moyen d'arriver à faire ce que je demandais initialement ou bien il faut passer par Query intégré d'office ?

 

[chris]

Bonjour

J'avais effectivement oublié une variable pour le chemin

Je n'ai pas compris ta dernière phrase : PowerQuery fait partir d'Excel 2019

Sinon faut programmer en VBA et c'est plus compliqué

 

[Esfloria]

Bonjour,

Ce que je voulais dire c'est que sur 2016 il faut activer Power Query si j'ai bien compris alors qu'il intégré à la version 2019 (ou 365 avec leur nouveau système de nom). Du coup je me demandais s'il existait un autre moyen de réaliser ce que je voulais faire sur Excel 2019 / 365 qu'avec les requêtes et connexions.

Je sais pas si c'est plus clair ...

Faut que je me renseigne sur les requêtes et connexions parce que j'ai regardé le détail de ce que tu as fait et ce qui apparait sur ce que moi j'ai fait et ... c'est pas la même chose ... Mais je ne maîtrise pas du tout ces outils, c'est tout nouveau pour moi. Aurais-tu des vidéos de qualité à me conseiller pour me familiariser avec ces outils stp ? et comment tu as créé le NB_Notes qui apparaît dans la zone Nom en haut à gauche et qui correspond à la cellule B3 ?? Je vois pas de formule ni rien et juste quand je change le nombre 30 en
45 ou 12, ça se met à jour automatiquement.

Désolée si ce que je demande n'est pas forcément limpide mais je ne maîtrise pas tout le vocabulaire lié à Excel ... La base oui mais quand ça devient complexe ... Je rame

 

[chris]

Noooon

Sauf forçage à l'installation pour le refuser, PowerQuery est dans 2016.

2019 et 365 sont 2 versions différentes.

PowerQuery est la techno la plus simple et la plus récente pour ce type de besoin
Donc sauf à apprendre à programmer en VBA...

En partant de 0 sans chemin paramétrable

• Dans une cellule taper le nombre voulu. Garder cette cellule sélectionnée
• Nommer la cellule NB_Notes (Formules, Définir un nom)
• sur 2016 Données, Nouvelle Requête, A partir d'un fichier, A partir d'un dossier
  Choisir le dossier, puis dans la fenêtre suivante, Combiner et transformer
• sélectionner T_Notes dans la fenêtre suivante : le clic sur OK ouvre PowerQuery
• sélectionner la requête Transformer l'exemple de fichier
  on va traiter ce fichier exemple et le modèle de traitement se construit automatiquement
  • il n'est pas certain qu'il y ait toujours 6 notes et la moyenne au bout
    on va donc
    supprimer la colonne Note finale
    puis décroiser les colonnes de notes : sélection des colonnes Numéro dossier, Nom, Prénom, clic droit, Dépivoter les autres colonnes
    Dans la barre de formule remplacer Valeur par Notes
  • supprimer la colonne Attribut
  • sélection des colonnes Numéro dossier, Nom, Prénom, clic droit, Modifier le type, Texte
  • sélection de la colonne Notes, clic droit, Modifier le type, Nombre Décimal
• sélectionner la requête Notes (ou portant le nom du dossier)
  • supprimer la dernière étape
  • modifier l'étape Colonnes renommées1 : remplacer dans la barre de formule Source.Name par Formateur
  • idem pour l'étape suivante
  • sélectionner la colonne Formateur, clic droit remplacer les valeurs : .xlsx par rien
  • cliquer sur la double flèche près du titre Transformer le fichier
  • sélection des colonnes Numéro dossier, Nom, Prénom, Transformer, Regrouper par, Moyenne sur Notes, nommée Note Finale
  • tri pas Note et Numéro de dossier
  • Ajouter une colonne, colonne d'index à partir de 1
    Dans la barre de formule remplacer Index par Rang
  • filtre de rang sur une valeur puis dans le barre de formule remplacer la valeur par
    Excel.CurrentWorkbook(){[Name="NB_Notes"]}[Content][Column1]{0}
• sortir par fermer et Charger dans, Tableau et choisir l'emplacement

 

[Esfloria]

Merciii ! J'ai pas le temps de le tester dans l'immédiat mais je fais un retour dès que je peux pour voir si j'ai bien tout saisi ou pas ...
Et pour ce qui est de Power Query dans 2016 et autre, en fait c'est ce que tu as dit sauf que j'ai pas les bons termes lol. C'est juste que sur YouTube, j'avais vu qu'il fallait aller dans les options pour l'activer dans 2016 pour qu'il apparaisse dans le ruban par la suite. Et que dans les versions postérieures, il n'y avait plus besoin de l'activer.
Par contre je croyais que 365 était le nouveau nom de 2019 ... J'ai vu mal comprendre l'information que j'avais entendu, merci pour ton éclairage.
Je reviens vers toi dès que j'ai pu tester ta méthode détaillée.

A bientôt et merci de ta patience