Cet article va vous expliquer comment combiner toutes les lignes de 2 colonnes différentes pour fabriquer une nouvelle liste. Pour cela, nous allons utiliser Power Query
C'est quoi Power Query ?
Power Query est l'outil moderne d'importation et de transformation des données. Son utilisation est très simple et ne nécessite pas de connaissance avancé d'Excel ni de connaissance en programmation.
Si vous voulez vous former à l'utilisation de Power Query, je vous ai conçu cette formation sur Udemy très simple d'accès.
Point de départ, 2 listes distinctes
Prenons 2 listes,
- l'une contient la liste de vos vendeurs (5 lignes).
- l'autre contient 10 dates (jours ouvrés).
Ce que nous cherchons à faire, c'est de croiser toutes les cellules pour avoir autant de combinaisons possibles entre les vendeurs et dates soit =5*10 => 50 combinaisons à créer.
Pour éviter de fastidieux copier-coller, vous pouvez effectuer ce travail en 3 actions seulement.
Étape 1 : Insérer vos données dans deux Tableaux
Mettre ses données dans une Table, cela permet de donner un nom à la plage de données et cela va nous servir pour la suite. Pour insérer vos données dans une Table, il suffit de sélectionner l'une des cellules de vos données et d'activer le menu Insertion > Tableau
Il est aussi important de donner un nom à vos Tables. De cette façon, il sera plus facile d'identifier les données par la suite (voir étape 2).
Étape 2 : Transférer un premier Tableau dans Power Query
La cellule active est toujours dans votre Tableau et là, vous sélectionnez le menu Données > A partir d'un Tableau
Vos données sont maintenant chargées dans le nouvel outil de manipulation des données d'Excel, Power Query.
Le nom de la requête est le nom de votre Tableau. D'où l'importance du choix du nom de votre Tableau.
Etape 3 : Charger ces données en mémoire
Vous n'avez pas besoin de transformer ou de modifier les données. Nous allons quitter Power Query et charger les données en mémoire.
Pour cela, nous allons choisir le menu Accueil > Fermer et Charger > Fermer et Charger dans ...
Et dans la nouvelle boîte de dialogue choisissez l'option Ne créer que la connexion. De cette façon, les données seront chargées dans Power Query mais pas dans une feuille Excel.
Étape 4 : Transférer le deuxième Tableau dans Power Query
Nous allons maintenant charger le deuxième Tableau de données (les dates) dans Power Query.
- Insertion des données dans une Table (comme à l'étape 2).
- Données > A partir d'un Tableau ou d'une plage.
Étape 5 : Changer le type de données
Dans Power Query, il est très important de respecter le type de données contenues dans les colonnes. C'est le même principe que pour une base de données. Nous allons donc indiquer que le type de données de ce second Tableau c'est des dates uniquement.
- Cliquer sur l'icône en haut à droite de l'entête de colonne pour faire apparaître le menu des types de données.
- Choisissez le type date (et pas date et heure comme c'est le cas actuellement).
Et donc maintenant votre colonne ne contient plus que des dates, sans les heures.
Etape 6 : Combiner les 2 requêtes
Nous allons maintenant croiser les 2 requêtes (c'est le nom une fois les données chargées dans Power Query).
- Restez dans la requête contenant les dates.
- Allez dans le menu Ajouter une colonne > Colonne personnalisée.
C'est maintenant que la magie opère. Commencez par donner un nom à la colonne qui va être ajoutée (1) et écrivez la formule suivante :
= NomdelaRequete[NomdelaColonne]
Dans cet exemple la fonction s'écrit
= tblVendeur[Vendeur]
Il est très important de respecter exactement l'écriture de la requête et de la colonne que vous voulez importer. Power Query est sensible à la casse c'est-à-dire aux majuscules et minuscules.
Le résultat se traduit par l'ajout d'une colonne avec l'instruction List.
Étape 6 bis: Détailler le contenu d'une cellule List
Pour comprendre ce qui se cache derrière l'indication List, il suffit de cliquer sur l'une des cellules qui contient le mot List. Mais attention, ne cliquez pas sur le mot List, juste dans la cellule. Cette action vous affiche dans une nouvelle fenêtre ce que contient l'instruction List. Et là, vous voyez bien s'afficher la liste des vendeurs.
Étape 7 : Déployer la liste
Pour combiner les deux listes, il suffit de cliquer sur l'icône de l'entête de la colonne et de sélectionner l'option Développer sur de nouvelles lignes.
Et Voilàààà ! Toutes les lignes sont immédiatement croisées.
Etape 8 : Charger le résultat dans Excel
Il ne nous reste plus qu'à transférer ce résultat dans Excel en cliquant sur le menu Accueil > Fermer & Charger. Le résultat s'affiche dans Excel dans un nouveau Tableau
Lavabre
20/11/2020 @ 09:13
Impeccable !!! Merci beaucoup!!
Gaelle
14/09/2020 @ 11:43
Bonjour,
Est-il possible d'utiliser cette technique de combinaison, en lien avec la création d'une relation entre les deux tableaux (dans l'exemple suivant la parcelle).
Par exemple :
Tab1:
Parcelle1 Infra1
Parcelle1 infra2
Parcelle2 infra2
Tab2 :
Parcelle1 Proprio1
Parcelle1 Proprio2
Parcelle2 Proprio2
Tableau recherché :
Parcelle1 Proprio1 Infra1
Parcelle1 Proprio2 Infra1
Parcelle1 Proprio1 Infra2
Parcelle1 Proprio2 Infra2
Parcelle2 Proprio2 Infra2
Merci de votre réponse
Nadine JACQUES
03/09/2020 @ 15:41
Bonjour,
J'ai une grande BDD (8000 lignes) et je voudrais faire du tri.
Mon objectif est que pour chaque catégorie, une nouvelle feuille se crée mais que mon tableau et la feuille soit liés.
Je souhaiterais que les mises à jours se mette des deux côté.
Un conseil? Query, connexion indirect? Ou autre...
Merci pour votre aide !
Nadine J