Préparez votre certification
Microsoft Office Specialist

Gérer plusieurs classeurs

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.

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.

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.

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.

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.

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.

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.

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

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. À partir du classeur cible ouvrez le bouton Styles de cellules et cliquez sur la commande Fusionner des styles


3. Dans la boite de dialogue qui apparait, sélectionnez le nom du classeur source puis cliquez sur le bouton OK


4. 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

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.

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.

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

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.

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).

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.

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

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

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

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. Naviguer dans l'arborescence de l'ordinateur jusqu'à trouver le fichier .bas voulu, le sélectionner puis cliquer sur le bouton Ouvrir

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.

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

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.

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.
Répétez les étapes 2 et 3 pour tous les classeurs sources.
4. Précisez si les données source possèdent une ligne d'entête et/ou une colonne d'entête
5. 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

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.

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

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.

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

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

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.

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.

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.

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

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.

Combiner des tableaux à l'aide de Power Query

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



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



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.

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.

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. Naviguer dans l'explorateur pour atteindre le fichier recherché, une fois trouvé le sélectionner puis cliquer sur Importer



5. 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



À 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.

6. 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.

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.

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.

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.

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.

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

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.

Avant d'aborder les transformations à effectuer, voyons d'abord quelques constituants de la fenêtre 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.

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

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.

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

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.

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).

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.

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

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.

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



• Cliquer droit sur l'entête de la colonne puis choisir Renommer dans la liste et saisir le nouveau nom

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



• Clic-droit sur leur entête puis choisir Supprimer les colonnes dans le menu contextuel

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.

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.

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.

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

Liste des clients de l'entreprise

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

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.

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.

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. Définir le champ commun entre les deux tables en cliquant dessus. Dans le cas présent, il s'agit du champ ID_Client.

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).

Un clic sur le bouton de déploiement de la colonne de droite, permet d'afficher une fenêtre pour 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.

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.

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.

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.

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

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)

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)

Jointure Externe Entière

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

Jointure Interne

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

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)

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)

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.

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.

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.

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

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.

En cliquant sur ce bouton, vous ouvrez un dossier où sont répertoriés les classeurs fermés sans avoir été 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é

Recevez ce cours au format PDF en envoyant un message ici