mosprepaPréparez votre certification
Microsoft Office Specialist

MS Excel 2013 et +

Gérer plusieurs classeurs

Dans le milieu professionnel, il est rare que vous travailliez sur un seul classeur Excel. Vous aurez toujours besoin d'échanger des informations entre différents classeurs (Importer/Exporter), de fusionner ou de consolider plusieurs classeurs, de copier des styles ou des macros et bien d'autres manipulations entre classeurs.

Dans ce cours on va voir les points qui suivent.

  1. Modification de modèles existants
    1. Créer un modèle à partir de modèle prédéfini
    2. Ouvrir un modèle personnalisé pour modification
    3. Copie des styles d'un modèle à un autre
    4. Copie des macros d'un classeur à un autre
  2. Fusion de plusieurs classeurs
    1. Consolider des données avec Excel
    2. Lien vers des données externes
    3. Combiner des classeurs avec Power Query
  3. Gestion de plusieurs versions d'un classeur

Recevez ce cours au format PDF en envoyant un message ici

Modification de modèles existants

Un modèle Excel est un fichier de base utilisé comme un "moule" pour créer des classeurs de feuilles de calcul. Le fichier modèle porte l'extension xltx et contient les paramètres de construction d'un classeur ainsi que les informations de mise en page des feuilles. Le but dans l'utilisation des modèles est d'avoir des documents harmonieux et homogènes émanant des différents utilisateurs qui les produisent au sein d'une même entité (Entreprise, société, établissement, etc.).

Créer un modèle à partir de modèles prédéfinis de Excel

Bien que Excel fournit un grand nombre de modèles que ce soit en local ou bien en ligne, il n'est pas rare que vous soyez dans le besoin de créer vos propres modèles mieux adaptés à vos besoins.

La méthode la plus facile pour créer un classeur modèle est de partir d'un classeur déjà existant qui se rapproche le plus de ce que nous désirons avoir, lui apporter les modifications nécessaires puis de l'enregistrer comme modèle.

Choisir un modèle

Prenons un exemple. Supposons que l'on veuille faire le suivi des absences des employés dans les différents services d'une entreprise. Chaque chef de service doit remplir, pour chaque jour, une feuille Excel dans laquelle sera noté l'état quotidien des absences. Si on confie aux responsables des services de créer chacun son classeur à sa guise, on obtiendra un travail non homogène avec des mises en formes différentes et des contenus différents ; ce qui ne facilite pas le traitement final des absences. L'idéal serait de créer un classeur modèle qui respecte la charte graphique de l'entreprise (logo, couleurs, police, etc.) et qui contient les informations nécessaires pour la gestion des absences. Ce classeur modèle sera partagé avec les différents services qui n'auront qu'à le remplir.

Excel propose un modèle intitulé : Calendrier des absences des employés, ce modèle se rapproche bien de ce que nous cherchons ; nous allons donc créer un classeur basé sur ce modèle que nous modifierons puis enregistrerons comme un nouveau modèle personnalisé.

Pour ce faire, sous l'onglet Fichier, on clique sur Nouveau et dans la rubrique PROPOSÉS on cherche l'icône Calendrier des absences des employés, comme le montre la capture suivante.

Excel, Ouvrir un modèle pour le personnaliser

Si cette icône n'apparait pas, vous pouvez lancer une recherche par mot clé en utilisant le mot Calendrier dans la zone Rechercher des modèles en ligne. Cette opération nécessite une connexion Internet.

Une fois cliqué sur ce bouton, vous obtenez la fenêtre suivante pour valider la création d'un classeur à partir de ce modèle. Cliquez alors sur le bouton Créer.

Excel, Créer un classeur à partir d'un modèle

On obtient alors le calendrier des absences des employés, qui est formé de plusieurs feuilles : une feuille par mois (Janvier, Février, ...., Décembre) et une autre feuille contenant la liste des employés. Dans la ligne 2 se trouve l'explication des clés des motifs des absences. Le fichier contient aussi plein de formules, beaucoup de zones nommées et des mises en forme conditionnelles. Bref, c'est un calendrier bien travaillé mais ça n'empêche pas que nous apportions notre touche personnelle pour mieux l'adapter à nos besoins.

Excel, Calendrier des absences

Effectuer des modifications

Vu que ce n'est pas le sujet de ce tutoriel, on ne va pas faire toutes les modifications nécessaires, on va se contenter de ces trois rectifications très simples :

  • Modifier l'année : mettre 2022 au lieu de 2019, il suffit de la modifier sur la feuille Janvier, elle sera répercutée sur les autres.
  • Modifier la légende en mettant l'étiquette Chargé(e) de mission à la place de Personnalisé 1 avec la clé CM et pour l'étiquette Personnalisé 2 on va saisir Non Justifié avec la clé NJ.
  • Insérer une image comme logo de l'établissement

Avant de créer le modèle il faudra penser à vider les tableaux des différentes feuilles en effaçant toutes les absences marquées.

Excel, Modifications effectuées sur le modèle

Travail à faire :

Pour garantir l'homogénéité de la saisie et protéger le classeur contre toute modification intempestive, les manipulations suivantes sont nécessaires avant de créer le modèle.

  1. Dans une nouvelle feuille, créer un tableau listant les différentes clés des motifs des absences (C, P, M, CM, NJ) et limiter la saisie dans les plages de cellules C7:AG11 à cette liste sur toutes les feuilles des mois.
  2. Créer une mise en forme conditionnelle pour hachurer les jours du weekend dans la zone C7:AG11 de toutes les feuilles des mois
  3. Protéger avec mot de passe et sans autoriser la sélection des cellules verrouillées, les feuilles des mois en excluant de la protection les plages de cellules B7:AG11
  4. Protéger avec mot de passe, la structure du classeur

Excel, Améliorer et protéger le calendrier

Créer le nouveau modèle

Maintenant que des modifications sont faites sur le fichier source, il y en a d'autres à faire certes, il est temps de créer notre modèle personnalisé. Pour ce faire, on procède à l'enregistrement du fichier en lui donnant un nom significatif et choisir le type de fichier Modèle Excel (*xltx) puis valider l'enregistrement.

Excel, Enregistrer un classeur modèle

Pas besoin de renseigner l'emplacement de l'enregistrement, le chemin par défaut est prédéfini dans les options de Excel.

Excel, Le chemin par défaut d'enregistrement des modèles office personnels

Ouvrir un modèle personnalisé pour modification

Tous les modèles personnalisés créés avec les logiciels Ms Office sont enregistrés par défaut dans le dossier Modèles Office personnalisés qui se trouve dans le dossier Documents.

Excel, Le dossier des modèles office personnels

Un double-clic sur un fichier modèle ne l'ouvre pas comme tel, mais plutôt il en crée une copie qui porte le même nom suivi d'un numéro d'indice puis l'extension normale du logiciel. Dans notre cas le double-clic va créer le classeur Calendrier des absences1.xlsx, et toutes les modifications qui seront portées sur ce classeur n'affecteront en rien le modèle.

Excel, Nouveau calendrier des absences

Pour apporter des modifications sur un classeur modèle, il faudrait faire un clic droit puis de choisir la commande Ouvrir

Excel, Ouvrir un modèle Excel pour modification

Cette opération mène à ouvrir le modèle en mode modifications (création) avec l'extension xltx, sur lequel nous pouvons alors effectuer les corrections voulues puis procéder à un enregistrement normal (Ctrl + S ou autre méthode).

Copie des styles d'un modèle à un autre

Un style est une mise en forme pré-élaborée de cellule, qui est réutilisable à volonté. Vous pouvez mettre en forme une cellule comme vous le désirez puis sauvegarder cette mise en forme comme un style personnalisé après lui avoir donné un nom. Vous pouvez aussi utiliser des styles contenus dans d'autres classeurs en les important sur le vôtre.

Pour copier un style de cellule d'un classeur à un autre n'est pas compliqué.

  1. Ouvrez le classeur qui contient le style à copier (le classeur source) et le classeur vers lequel vous voulez faire la copie du style (le classeur cible)

  2. Excel, Copier un style de cellule
  3. À partir du classeur cible ouvrez le bouton Styles de cellules et cliquez sur la commande Fusionner des styles

  4. Excel, Copier un style de cellule
  5. Dans la boite de dialogue qui apparait, sélectionnez le nom du classeur source puis cliquez sur le bouton OK

  6. Excel, Copier un style de cellule
  7. Une dernière boite de dialogue vous demande si vous voulez fusionner les styles qui portent le même nom. Selon le cas, validez par Oui ou bien par Non

  8. Excel, Copier un style de cellule

Le style nommé Style Bleu est maintenant importé dans le classeur modèle cible, on peut alors l'appliquer dans tous les nouveaux classeurs bâtis sur ce modèle.

Excel, Style importé dans le classeur cible

Copie des macros d'un classeur à un autre

Les macros dans Excel sont des instructions écrites en VBA, elles jouent un rôle incontestable dans l'automatisation des tâches répétitives et sont fréquemment utilisées. Si vous créez une macro ou bien vous la recevez de la part de quelqu'un de confiance (Voir encadré), vous pouvez la transmettre d'un classeur à un autre par des opérations de Cliquer/Glisser ou bien d'Import/Export. Pour ce faire, vous devez passer par le VBE l'éditeur VB accessible via le bouton Visual Basic sous l'onglet Développeur ou bien par le raccourci clavier ALT + F11.

Excel, Ouvrir l'éditeur VB

Que vous utilisez l'une ou l'autre méthode, vous aboutissez sur la fenêtre de l'éditeur VB.

Excel, L'interface de l'éditeur VB

Mise en garde

Des macros faites par des personnes malintentionnées, peuvent être nuisibles voire destructrices pour vos données et votre ordinateur de travail, pour cette raison, n'utilisez que des macros de sources fiables. Les fichiers Excel contenant des macros portent l'extension .xlsm

Pour l'exemple on va créer dans un premier classeur, une macro toute simple nommée mettreEnRouge qui met le fond d'une cellule sélectionnée en rouge. C'est juste pour une démonstration.

Excel, Macro mettre En Rouge

Copier et Coller une macro

Cette macro, on voudrait la copier dans un autre classeur, pour ce faire, tout en gardant le classeur qui contient la macro, on ouvre le classeur vers lequel on veut la copier puis on lance l'éditeur VBE (ALT + F11).

Excel, Les deux classeurs ouverts avec l'éditeur VBE

Un zoom sur l'explorateur de projet, à gauche de l'éditeur, montre que le Classeur1 contient le dossier nommé Modules dans lequel se trouve le module Module1 qui, à son tour, contient le code VBA de notre macro mettreEnRouge. Dans le Classeur2, on ne trouve pas de dossier Modules ce qui signifie qu'il ne contient pas de macros.

Excel, Les deux classeurs dans l'explorateur de projet

Maintenant que nous-en sommes là, l'opération est d'une facilité extrême puisqu'il suffit de cliquer-glisser le module Module1 du classeur1 vers le classeur2, comme le montre l'animation ci-après

Excel, Copier une macro vers un autre classeur

Cette opération a pour effet de créer dans le classeur de destination le dossier Modules et d'y insérer le module1 qui contient la macro mettreEnRouge. Désormais, cette macro est disponible dans les deux classeurs.

La méthode cliquer/glisser n'est utilisable que si le classeur source et le classeur de destination sont ouverts sur le même poste, mais qu'en est-il si le classeur de destination n'est pas encore prêt ? ou bien si vous voulez partager une macro avec quelqu'un d'autre ? Dans ces cas, on doit avoir recours aux opérations d'Import/Export.

Exporter et Importer une macro

Dans le classeur source, l'exportation d'une macro se fait en quelques petites étapes

  1. Cliquer droit sur le module qui contient la macro, ici c'est Module1
  2. Dans le menu contextuel qui apparait, cliquer sur la commande Exporter un fichier
  3. Dans la boite de dialogue qui s'ouvre, définir l'emplacement d'enregistrement
  4. Cliquer sur le bouton Enregistrer

Excel, Exporter une macro

Les macros exportées sont enregistrées avec l'extension .bas

Les Modules sont un moyen de regroupement des macros dans un soucis d'organisation ; ainsi un module peut contenir une ou plusieurs macros, de même un classeur (projet) peut contenir plusieurs modules.

Du côté du classeur de destination, on doit faire une importation du module contenant la macro.

  1. On effectue un clic-droit sur le classeur de destination VBAProject(Classeur2) puis on choisit Importer un fichier

  2. Excel, Importer une macro
  3. Naviguer dans l'arborescence de l'ordinateur jusqu'à trouver le fichier .bas voulu, le sélectionner puis cliquer sur le bouton Ouvrir

  4. Excel, Importer une macro

Fusion de plusieurs classeurs

Avoir des informations brutes ne servirait pas à grand-chose, si on ne les exploite pas pour en sortir d'autres données qui permettront de faire des évaluations et de prendre des décisions afin de redresser une situation ou bien d'éviter des pertes. Les opérations de Consolidation, de lien vers des données externes et de combinaison sont des manipulations que vous utiliserez fréquemment pour rassembler des données afin de faire des synthèses et éventuellement pouvoir prendre des décisions. Effectuer l'une ou l'autre de ces opérations dépend de ce que vous voulez faire des informations collectées.

Consolider plusieurs feuilles

On voudrait obtenir dans un tableau récapitulatif, le total des quantités vendues et des articles retournés pour cause d'une défaillance. Ces données sont contenues dans quatre classeurs trimestriels ayant la même structure ; l'outil Consolider est le moyen approprié dans ce cas.

Excel, Consolider des feuilles

L'outil Consolider est accessible dans le groupe Outils de données dans l'onglet Données.

Excel, L'outil Consolider

Avant de cliquer sur le bouton Consolider vous devrez d'abord activer, dans le classeur cible, la cellule à partir de laquelle vous voudrez insérer le tableau de synthèse. La fenêtre de l'assistant s'ouvre alors pour permettre le paramétrage de la consolidation.

Excel, La fenêtre de l'outil Consolider

  1. Dans la zone Fonction, en ouvrant la liste, choisissez l'opération à effectuer sur les données, à savoir Somme, Moyenne, Max ou autre
  2. Pour Référence, cliquez sur la petite étiquette à droite du champ pour aller définir la plage de cellules où se trouvent les données dans un des classeurs sources
  3. Cliquez sur le bouton Ajouter pour ajouter cette référence à la liste des références.
  4. Répétez les étapes 2 et 3 pour tous les classeurs sources.
  5. Précisez si les données source possèdent une ligne d'entête et/ou une colonne d'entête
  6. Validez en cliquant sur le bouton OK

Si vous activez la case Lier aux données source, tout changement dans les tableaux d'origine sera répercuté sur le tableau de synthèse.

Le classeur cible et les classeurs source étant déjà ouverts, on procède à la consolidation comme le montre l'animation ci-après

Excel, L'outil Consolider

Lien vers des données externes

Le puisement des données à partir des feuilles de différents classeurs, pour en faire des synthèses, peut aussi se faire à l'aide de liens créés vers ces données externes. Ces liens sont créés via des formules de calcul faisant référence à ces données là où elles sont. Une formule de calcul qui commence toujours par le symbole égal (=) rappelons-le, peut faire référence à des cellules de la même feuille, mais aussi à des cellules situées dans une autre feuille du même classeur voire dans un autre classeur enregistré sur votre ordinateur ou bien sur le réseau.

Dans ce tableau qui se trouve dans un classeur à part, on voudrait calculer les totaux des salaires des employés pour chaque service. Ces salaires sont enregistrés dans des classeurs déjà ouvert, dédiés chacun à un service.

Excel, Tableau pour créer des liens

Commençons par le total des salaires du service Production.

  1. Dans la cellule C4 de ce tableau on saisit la fonction de calcul, ici c'est la fonction SOMME puis on ouvre la parenthèse.
  2. On bascule vers le classeur du service Production et on sélectionne la plage de cellule F5:F9 qui contiennent les salaires des employés de ce service, puis on ferme la parenthèse et on valide avec la touche Entrer

Excel, Saisir la formule pour créer le lien

Après validation, le résultat est là, dans la cellule C4 du tableau cible et la barre de formule affiche l'expression de calcul utilisée avec entre parenthèses la référence vers laquelle elle pointe.

Excel, Saisir la formule pour créer le lien

La capture suivante montre les détails de l'expression de calcul avec le lien de référence.

Excel, Expression de calcul avec lien de référence

On fait de même pour les autres services pour compléter le tableau.

Excel, Expression de calcul avec lien de référence

Pour l'instant, les classeurs sources sont ouverts, mais Que se passe-t-il si on les ferme ?

Dans ce cas, Excel remplace la référence par le chemin complet pour accéder à la source. Ce chemin complet est mis entre quottes simples suivi du point d'exclamation puis du nom du tableau et la plage de cellules contenant les données.

Excel, Expression de calcul avec lien de référence

Si vous fermez tous les classeurs (Sources et cible) puis vous ouvrez uniquement le classeur cible qui contient les liens, vous obtenez le prompt suivant qui vous demande l'activation on non de la mise à jour des liens.

Excel, Demande de confirmation de la mise à jour des liens

Si vous optez pour Ne pas mettre à jour vous travaillerez avec les données telles qu'elles sont affichées et qui ne sont pas mises à jour.

Si vous choisissez Mettre à jour, vous obtenez des messages d'erreur de référence. Pourquoi ? Parce que les classeurs sources sont fermés.

Excel, Erreur de référence des liens

Si vous rencontrez une telle situation, pas de panique. Suivez les étapes ci-après

  1. Ouvrez l'onglet Données
  2. Dans le groupe Connexions cliquez sur la commande Modifier les liens
  3. Dans la boite de dialogue qui s'ouvre, sélectionnez toutes les sources
  4. Cliquez sur le bouton Ouvrir la source

Excel, Ouvrir les sources des liens

Cette manipulation a pour effet d'ouvrir tous les classeurs sources et de mettre les données à jour.

La boite de dialogue propose aussi le bouton Modifier la source, utilisé comme son nom l'indique, pour modifier la source de données et le bouton Rompre la liaison qui sert à casser le lien entre la cible et la source.

Les liens vers des données externes se trouvent dans les formules des cellules mais aussi dans tout objet Excel susceptible de contenir des références tels que les Graphiques les listes, les Formes et les formules des mises en forme conditionnelles.

Combiner des tableaux à l'aide de Power Query

Power Query est un outil d'Extraction de Transformation et de Chargement de bases de données de toute taille (point faible de Excel) et à partir de diverses sources. Il est créé par Microsoft et utilisé dans Excel mais aussi dans d'autres logiciels tels que Power BI et Azur.

Un peu de vocabulaire avant de commencer.

  • Le fait de demander à Power Query d'extraire des données et de les transformer s'appelle une Requête
  • Combiner des requêtes veut dire : mettre les tableaux extraits ensemble l'un la suite de l'autre
  • Si les tableaux sont mis ensemble verticalement, c'est à dire les lignes de l'un à la suite des lignes de l'autre, on parle d'un Ajout
  • Excel, Tableaux ajoutés

    Remarquez que les tableaux à ajouter ont la même structure : même nombre de colonnes avec les mêmes entêtes.

  • Si les tableaux sont mis ensemble horizontalement, c'est à dire les colonnes de l'un à la suite des colonnes de l'autre, on parle d'une Fusion
  • Excel, Tableaux fusionnés

    Remarquez que les tableaux à fusionner n'ont pas la même structure, ils pourraient ne pas avoir le même nombre de colonnes, mais ils ont une colonne en commun la colonne Code produit.

Ajouter des tableaux

Situation d'utilisation :

Dans le but de créer une fiche de synthèse, vous êtes chargé(e) de la collecte des informations concernant les employés de votre entreprise. Pour faciliter l'opération, vous avez préparé sur Excel une fiche gabarit, que vous avez partagée avec les différents services sur le réseau de l'entreprise. En retour vous avez reçu les feuilles Excel suivantes contenant chacune les informations demandées dûment renseignées.

Excel, Informations des employés

Ces informations étant éparpillées dans cinq classeurs Excel différents (un classeur de chaque service), votre tâche consiste à toutes les rassembler dans un seul fichier Excel pour pouvoir faire une synthèse des salaires et des anciennetés des employés. Vous aurez besoin de passer par Power Query en trois étapes : d'abord l'Extraction puis éventuellement la Transformation en cas de besoin et en fin le Chargement dans Excel. Téléchargez les fichiers source ici, c'est un fichier .rar que vous devrez décompresser.

Excel, Synthèse des salaires et des anciennetés des employés

L'extraction

Commençons par le commencement, aller chercher les données à partir des fichiers Excel collectés. Pour ce faire, sous l'onglet Données groupe Récupérer et transformer, on effectue les opérations suivantes :

  1. Cliquer sur le bouton Nouvelle Requête pour ouvrir la liste
  2. On pointe sur la première ligne de la liste À partir d'un fichier
  3. Dans la sous-liste qui apparait à droite, on clique sur À partir d'un classeur
  4. Excel, Récupérer les données à partir d'un classeur

  5. Naviguer dans l'explorateur pour atteindre le fichier recherché, une fois trouvé le sélectionner puis cliquer sur Importer
  6. Excel, Récupérer les données à partir d'un classeur

  7. Dans la fenêtre qui s'ouvre, sélectionner le tableau des données (ou bien toute la feuille), un aperçu du contenu du tableau apparait dans la partie droite de la fenêtre, puis ouvrir le bouton Charger et choisir Charger dans
  8. Excel, Récupérer les données à partir d'un classeur

    À remarquer que le tableau sélectionné ici, T_Synthese du classeur SyntheseSalairesAnciennete.xlsx ne contient qu'une seule ligne vide. C'est ici où seront ajoutés les autres tableaux.

  9. Dans la nouvelle fenêtre qui s'ouvre et qui nous invite à choisir une option de chargement des données, on opte pour Créer une connexion uniquement puis cliquer sur le bouton Charger. Ce n'est pas la peine de les charger pour l'instant, on se contente alors de créer une liaison.
  10. Excel, Récupérer les données à partir d'un classeur

Notre première Requête est exécutée, elle a créé le lien avec le tableau T_Synthese et porte le nom de ce même tableau. Le volet de droite affiche la liste de toutes les requêtes du classeur. Comme il est mentionné dans la capture ci-dessous, la requête a créé uniquement la connexion et n'a pas chargé les données.

Excel, Récupérer les données à partir d'un classeur

Ces opérations doivent être faites pour chacun des classeurs collectés. Une fois que toutes les connexions sont créées avec les différents classeurs, le volet droit, Requêtes de classeur, montre la liste des requêtes effectuées.

Excel, Les requêtes d'un classeur

Maintenant que toutes les connexions vers les différentes tables sont créées, il est temps de demander à Power Query de les combiner en les ajoutant verticalement l'une à la suite de l'autre. On va effectuer une opération d'ajout, à cet effet, et toujours sous l'onglet Données groupe Récupérer et transformer bouton Nouvelle Requête, on pointe sur la ligne Combiner des requêtes puis on sélectionne Ajouter.

Excel, Ajouter des requêtes

Le clic sur ce bouton a pour effet d'ouvrir la fenêtre Ajout pour définir les requêtes (les tables) à ajouter. Dans le champ Sélectionner la table primaire ... sélectionner la table à laquelle on souhaite ajouter des données, dans le cas présent c'est la table/requête T_Synthese. Dans le second champ, on sélectionne la table à ajouter à la table primaire. Pour valider le paramétrage, on clique sur OK.

Excel, Sélectionner les tables à ajouter

Si la version de Excel sur laquelle vous travaillez ne permet d'ajouter plusieurs tables à la fois, comme c'est le cas ici, vous devez faire l'ajout en plusieurs étapes en ajoutant à chaque fois une nouvelle table à la table primaire. Pour échapper à cette corvée, voir encadré ci-dessous.

Le résultat de l'ajout de la table T_Achats à la table T_Synthese, est montré par la capture suivante

Excel, Deux tables ajoutées

Pour remédier au problème signalé ci-dessus et éviter de répéter l'opération de l'ajout plusieurs fois, surtout si vous avez beaucoup de requêtes à ajouter, on peut compléter la commande manuellement en écrivant les noms des autres requêtes séparés par des virgules, dans la barre de formule puis valider par la touche Entrer du clavier.

Excel, Saisir du code à la main

Résultat : Les lignes des cinq requêtes (T_Achat, T_ComptabilitéFinance, T_MarketingVentes, T_Production et T_RH) sont ajoutées à la suite des lignes de la requête T_Synthese.

Excel, Résultat de l'opération de l'ajout

Avant d'aborder les transformations à effectuer, voyons d'abord quelques constituants de la fenêtre Power Query.

Excel, La fenêtre de la requête Power Query

  1. Dans la partie gauche de la fenêtre, on trouve la liste des requêtes, en bas de la liste on voit la requête Append1 nouvellement créée et qui est sélectionnée
  2. La barre de formule contient le code exécuté par la requête sélectionnée, c'est écrit en Langage M
  3. Au centre de la fenêtre, s'affiche le résultat de la requête
  4. À droite de la fenêtre, dans le volet Paramètres d'une requête, se trouve le champ Nom de la requête que l'on peut modifier
  5. et en bas du volet, on voit la liste des tâches exécutées par la requête. Pour l'instant une seule tâche y figure : Source, qui veut dire rapatrier les données à partir de la source, et la source ici c'est les six requêtes/tables citées dans le code.
La transformation

Power Query est très riche en fonctionnalités de transformation de données. On en trouve, en plus de celles utilisées pour l'extraction des données, des fonctionnalités pour la modification de la structure du tableau et d'autres pour la modification des données. Dans le cas présent on n'a pas beaucoup de transformations à faire à part les points suivants :

  • Supprimer les lignes vides
  • Ajouter les colonnes Age et Ancienneté
    1. Ajouter la colonne
    2. Supprimer des colonnes
    3. Modifier le format
    4. Arrondir le chiffre

Commençons tout d'abord par l'élimination des lignes vides du tableau, elles contiennent des valeurs Null. Il en existe une seule, la toute première du tableau.

Excel, Une ligne vide sous Power Query

Pour supprimer les lignes vides dans une table Power Query, nul besoin de sélectionner des lignes, on procède selon les étapes suivantes :

  1. Activer l'onglet Dossier racine
  2. Aller dans le groupe Réduire les lignes
  3. Ouvrir le bouton Supprimer des lignes
  4. Choisir la commande Supprimer les lignes vides

Excel, Supprimer les lignes vides sous Power Query

Une fois la table purifiée des lignes vides, cette étape est rajoutées à la liste des étapes dans le volet Paramètres d'une requête à droite de la fenêtre.

Excel, Lignes vides supprimées sous Power Query

Dans l'étape suivante, on ajoute la colonne Âge dans laquelle on va calculer l'âge de chaque employé en effectuant la différence entre la date du jour et la date de naissance de chacun. Power Query facilite beaucoup la tâche puisqu'il suffit de :

  1. Sélectionner la colonne D.Naissance
  2. Activer l'onglet Ajouter une colonne
  3. Dans le groupe Date et heure de début ouvrir le bouton Date
  4. Cliquer sur la commande Âge

Excel, Ajouter la colonne Âge sous Power Query

Immédiatement, une colonne nommée AgeFromDate est ajoutée à droite de la table, elle contient des nombres un peu bizarres ce qui fait qu'elle nécessite des modifications. Remarquez qu'à chaque fois qu'on effectue une étape, celle-ci s'ajoute à la liste des étapes appliquées dans le volet de droite.

Excel, Ajouter la colonne Âge sous Power Query

Pour rectifier l'affichage de ces nombres, on sélectionne cette nouvelle colonne puis on ouvre le bouton Durée et clique sur la ligne Total années qui permet de convertir ces nombres en nombre d'années qui se sont écoulées depuis la date de naissance d'un employé jusqu'à la date du jour (la date système).

Excel, Modifier les nombres obtenus en nombre d'années sous Power Query

Cette commande a pour effet de créer une nouvelle colonne nommée Total années qui contient des nombres réels indiquant l'âge des employés en nombre d'années.

Excel, Modifier les nombres obtenus en nombre d'années sous Power Query

Encore une transformation à faire, c'est de ne garder que le nombre d'années entières et ce en éliminant la partie décimale des nombres obtenus. Pour ce faire, on doit arrondir ces nombres à l'entier inférieur en passant par le bouton Arrondi dans le groupe À partir d'un nombre et de choisir la commande Arrondi à l'entier inférieur

Excel, Arrondir des nombre sous Power Query

Encore une nouvelle colonne RoundDown créée et cette fois c'est le résultat attendu. Elle contient l'âge des employés en nombre d'années entières.

Excel, Arrondir des nombre sous Power Query

Il ne reste plus que deux petites touches pour finaliser le travail : Renommer la colonne RoundDown et supprimer les colonnes intermédiaires.

Pour renommer une colonne, on peut utiliser l'une des trois méthodes :

  • On double-clique sur son entête puis saisir le nouveau nom
  • La colonne à renommer étant sélectionnée, passer par l'onglet Transformer et utiliser le bouton Renommer qui se trouve dans le groupe N'importe quelle colonne
  • Excel, Renommer une colonne sous Power Query

  • Cliquer droit sur l'entête de la colonne puis choisir Renommer dans la liste et saisir le nouveau nom
  • Excel, Renommer une colonne sous Power Query

Les colonnes AgeFromDate et Total années sont des colonnes intermédiaires dont on n'a plus besoin, on peut alors les supprimer en procédant suivant l'une des deux manières après les avoir sélectionnées :

  • Sous l'onglet Dossier racine dans le groupe Gérer les colonnes, ouvrir le bouton Supprimer les colonnes et choisir la commande Supprimer les colonnes sélectionnées
  • Excel, Supprimer des colonnes sous Power Query

  • Clic-droit sur leur entête puis choisir Supprimer les colonnes dans le menu contextuel
  • Excel, Supprimer des colonnes sous Power Query

Attention !

La commande Supprimer d'autres colonnes, supprime les colonnes autres que celles qui sont sélectionnées

Le résultat après les transformations

Excel, Le résultat après les transformations

Sur la droite de la fenêtre figure la liste de toutes les étapes suivies pour aboutir à ce résultat.

La commande Âge permet de calculer la différence entre la date du jour (la date système) et une date antérieure. Son nom n'est peut-être pas très expressif puisqu'elle ne sert pas qu'à calculer les âges, on va d'ailleurs l'utiliser peut calculer l'ancienneté des employés.

Excel, La commande Âge pour calculer la différence entre la date du jour et une date antérieure

Le chargement dans Excel

Dans Power Query l'ajout des tables l'une à la suite de l'autre est à présent réalisé, de même toutes les modifications nécessaires. Il reste maintenant à exporter ce résultat vers Excel. On utilise à cet effet le bouton Fermer et charger qui se trouve à l'extrême gauche du ruban sous l'onglet Dossier racine.

Power Query, Fermer et charger dans Excel

Résultat : Les données collectées des différents services sont chargées dans une nouvelle feuille Excel mises ensemble dans un seul tableau avec toutes les modifications apportées pendant le traitement sous Power Query.

Power Query, Données chargées dans Excel

Travail à faire :

Maintenant que vous-en êtes arrivé là, vous devriez être capable, en vous basant sur la colonne D.Embauche, de calculer l'ancienneté des employés en nombre d'années entières. N'oubliez pas les points suivants :

  1. Utiliser la commande Âge
  2. Transformer le résultat en nombre d'années Total années
  3. Arrondir le résultat à l'entier inférieur
  4. Renommer la nouvelle colonne en lui donnant le nom Ancienneté
  5. Supprimer les colonnes superflues
  6. Charger la table dans Excel

Excel, Anciennetés calculées sous Power Query

Fusionner des tableaux

La fusion à l'aide de Power Query permet de mettre ensemble les colonnes de deux tables en se basant sur un champ commun entre les deux. Pour illustrer ceci, on va se baser sur les deux tableaux suivants qui se trouvent chacun dans un classeur à part et dans lesquels on trouve les détails des ventes et des clients. Dans le premier tableau, se trouvent les informations sur les articles vendus : Désignation, Quantité vendue et le code du client ayant effectué l'achat et le deuxième tableau, contient les informations concernant les clients le Code client et le Nom client.

Ventes réalisées pour la catégorie Souris pendant le mois Mars

Excel, Les achats des clients pour le mois Mars

Liste des clients de l'entreprise

Excel, Liste des clients

Ces deux tables sont minimes, c'est seulement pour l'illustration, mais gardez à l'esprit que Power Query, c'est fait pour traiter de très grandes quantités de données (Plusieurs millions de lignes).

Remarquez que le premier tableau ne contient pas les noms des clients. Notre objectif ici est d'extraire cette information à partir de la table Clients et la fusionner avec la table Ventes pour obtenir le résultat ci-après

Excel, Les extractions désirées

C'est un résultat que l'on peut obtenir grâce aux fonctions RechercheV et RechercheX oui, mais vu que ces fonctions ont leurs limites, Power Query forme une alternative beaucoup plus puissante.

Commençons par la connexion aux deux fichiers Excel que vous pouvez télécharger ici. Cette opération est déjà vue dans un paragraphe précédent, vous devriez être capable de la faire.

Travail à faire

Dans un nouveau classeur, créez deux requêtes pour se connecter à ces deux fichiers Excel, ne chargez pas les données, créez uniquement les connexions avec les tables T_Clients et T_VentesMars.

Excel, Connexions créées

Maintenant que les requêtes de connexion avec les deux tables sources sont créées, on peut lancer une nouvelle requête qui va réaliser la fusion des deux précédentes. Pour ce faire, dans le groupe Récupérer et transformer sous l'onglet Données, on ouvre le bouton Nouvelle requête puis dans la ligne Combiner des requêtes, choisir Fusionner.

Excel, Fusionner les requêtes

La fenêtre Fusionner s'ouvre alors pour paramétrer la fusion. Ce paramétrage consiste à :

  1. Définir dans le premier champ, la table qui va être considérée comme primaire (la table gauche) et dans le deuxième champ, la table qui va être considérée comme secondaire (la table droite).

  2. Excel, La fenêtre pour paramétrer la fusion des requêtes
  3. Définir le champ commun entre les deux tables en cliquant dessus. Dans le cas présent, il s'agit du champ ID_Client.

  4. Excel, Le champ commun entre les deux tables

Après validation de ces réglages, l'éditeur de requête de Power Query s'ouvre et affiche tous les items de la table primaire (ici la table T_Clients) avec en annexe une colonne à droite qui fait référence aux éléments de la table secondaire (ici la table T_VentesMars).

Excel, Editeur de requête Power Query

Un clic sur le bouton de déploiement de la colonne de droiteExcel, Bouton de déploiement, permet d'afficher une fenêtre pour choisir les champs de la table secondaire à afficher.

Excel, Choisir les champs de la table secondaire à afficher

Le clic sur OK affiche les lignes de la table secondaire avec des valeurs Null dans certaines lignes. Ces lignes correspondent aux clients qui n'ont pas commandé de souris pendant le mois Mars.

Excel, Table secondaire déployée

Autrement dit, cette requête affiche tous les éléments de la table primaire (ici la table T_Clients) qu'ils aient acheté des souris ou non. Ce résultat ne répond pas à notre attente. Ce que nous cherchons, rappelons-le, c'est les noms des clients qui ont acheté des souris, c'est à dire les clients qui figurent à la fois dans la table T_Clients et dans la table T_VentesMars. Nous devons donc reformuler la requête.

Pour modifier la requête, on doit cliquer sur la roue dentée qui est en face de Source pour faire réapparaitre la fenêtre Fusionner, apporter les modifications nécessaires puis valider. Dans notre cas on va cocher la case Inclure uniquement les lignes correspondantes.

Excel, Modifier la requête

Après modification de la requête, on obtient un résultat satisfaisant. Il ne reste plus qu'à renommer les colonnes puis charger dans Excel.

Excel, Tables fusionnées

Avant de charger le résultat de la requête dans Excel on a procédé, comme le montre l'animation suivante, à apporter quelques modifications :

  • Supprimer les colonnes non désirées par un clic droit sur l'en-tête puis supprimer
  • Renommer les colonnes qui porte le préfixe NewColumn par un double-clic sur l'en-tête puis saisir le nouveau nom

Le chargement dans Excel se fait via le bouton Charger et fermer sous l'onglet Dossier racine.

Excel, Tables fusionnées

Cette requête a renvoyé les clients qui figurent à la fois sur la liste des clients et sur la liste des ventes, en se basant sur le champ ID_Client qui est commun aux deux listes. Ce rendu est possible grâce à la jointure Interne. D'autres jointures sont proposées par Power Query comme c'est détaillé dans le paragraphe suivant.

Les différents types de jointures

Les jointures permettent d'extraire des données à partir de deux tables selon différentes associations et ainsi répondre aux questions de type :

  • Quels sont les éléments qui appartiennent à l'une des listes et pas à l'autre ?
  • Quels sont les éléments qu'on trouve à la fois dans les deux listes ?
  • Afficher les éléments qui appartiennent à l'une OU l'autre liste

Power Query propose six types de jointures

Excel, Les types des jointures Power Query

Jointure Externe Gauche

La jointure Externe gauche renvoie tous les éléments de la liste gauche (liste 1), qu'ils appartiennent ou non à la liste droite (liste 2)

Excel, Rendu de la jointure Externe gauche

Jointure Externe Droite

La jointure Externe droite renvoie tous les éléments de la liste droite (liste 2), qu'ils appartiennent ou non à la liste gauche (liste 1)

Excel, Rendu de la jointure Externe droite

Jointure Externe Entière

La jointure Externe Entière renvoie tous les éléments des deux listes, ça correspond à l'Union des ensembles.

Excel, Rendu de la jointure Externe Entière

Jointure Interne

La jointure Interne renvoie les items qui appartiennent à la fois aux deux listes, autrement dit l'intersection des deux listes.

Excel, Rendu de la jointure interne

Jointure Gauche Opposée

La jointure Gauche Opposée renvoie les items qui appartiennent uniquement à la liste gauche (liste 1) en excluant ceux de la liste droite (liste 2)

Excel, Rendu de la jointure Gauche Opposée

Jointure Droite Opposée

La jointure Droite Opposée renvoie les items qui appartiennent uniquement à la liste droite (liste 2) en excluant ceux de la liste gauche (liste 1)

Excel, Rendu de la jointure Droite Opposée

Gestion de plusieurs versions d'un classeur

Vous n'êtes jamais à l'abri d'une coupure d'électricité ou bien d'une plantation de votre ordinateur alors que vous êtes en plein travail sur un classeur. Il peut vous arriver aussi de fermer un classeur inintentionellement sans l'avoir enregistré. Il peut vous arriver aussi que vous ayez envie de retrouver un classeur que vous aviez fermé sans l'enregistrer pour le reformuler et surprise ce classeur est introuvable. Conséquence de tout ça, tout ce que vous avez fait est perdu et vous devez tout refaire, si des précautions n'ont pas été prises en amont. Pour éviter ces problèmes, vous devez vérifier si les options de récupération automatique sont bien activées, ces réglages sont accessibles dans les options de Excel page Enregistrement.

Excel, Les options de l'enregistrement automatique activées

En activant ces options, Excel effectue, à intervalle de temps régulier que vous définissez vous-même, des enregistrements automatiques des différentes versions de votre classeur alors que vous êtes en train de travailler dessus. Ça vous permettra de récupérer ces différentes versions en cas de besoin.

La section Informations du menu Fichier affiche à droite du bouton Gérer le classeur, l'historique des différentes versions du classeur sur lequel vous travaillez, qui ont été enregistrées automatiquement. Un clic droit sur une des versions affichées, donne la possibilité de l'ouvrir ou bien de la supprimer.

Excel, Historique des classeurs non enregistrés

Si vous décidez de restaurer une version, cliquez sur la commande Ouvrir la version, pour obtenir le bandeau de restauration avec le bouton Restaurer.

Excel, Historique des classeurs non enregistrés

Un clic sur ce bouton fait apparaitre un message d'avertissement que vous devez valider.

Excel, Confirmation de la restauration de l'ancienne version

Notez qu'il est parfois possible de restaurer même des classeurs qui sont fermés depuis quelques jours sans avoir été enregistrés. Dans la rubrique Ouvrir du menu Fichier tout en bas vous trouvez le bouton Restaurer des classeurs non enregistrés.

Excel, Récupérer un classeur non enregistré

En cliquant sur ce bouton, vous ouvrez un dossier où sont répertoriés les classeurs fermés sans avoir été enregistrés.

Excel, Restauration des classeurs non enregistrés

Vous obtenez alors sous le ruban un bandeau indiquant la récupération avec un bouton pour enregistrer le classeur récupéré

Excel, Restauration des classeurs non enregistrés

Recevez ce cours au format PDF en envoyant un message ici

MOS Prepa