Si vous avez suivi ou si vous suivez éventuellement notre formation Excel – Trucs de pro, vous avez vu ou verrez comment utiliser un ensemble de fonctions combinées telles que INDEX, CHOISIR et INDIRECT pour effectuer des recherches verticales et horizontales approximatives dans un ensemble de matrices disproportionnées. Vous avez vu ou verrez également que la tâche n’est pas simple. Cet article vise à vous présenter une deuxième approche, à l’aide de Power Query, qui au final, donne beaucoup plus de souplesse.
Découvrir les données de départ
Pour illustrer notre problématique, nous utiliserons les mêmes matrices qu’en formation, i.e. les matrices A, B et C, qui présentent les rabais accordés aux clients par 3 fournisseurs, en fonction du volume et de la distance. Ces 3 matrices sont présentées ci-dessous, sous forme de tableaux.
À noter que ces trois tables sont situées dans 3 onglets différents mais elles pourraient aussi bien être dans le même onglet ou même, dans différents fichiers.
Nous avons également inséré un tableau qui comprend les principaux paramètres, que l’usager peut manipuler à sa guise. Au final, l’usager choisira un fournisseur, un volume et une distance, cliquera sur Actualiser tout et obtiendra le taux de rabais associé.
Importer les matrices dans Power Query
Pour importer les 4 tableaux dans Power Query, vous pouvez procéder de deux façons. La première consiste à cliquer d’abord sur un premier tableau puis à aller dans le menu Données et à cliquer sur À partir d’un tableau.
Si vous jetez un coup d’oeil à la formule ainsi créée, vous verrez que Power Query utilise la fonction Excel.CurrentWorkbook() et l’applique à la table DonnéesA (dans ce cas-ci, nous avions cliqué sur le tableau du fournisseur A, que nous avions nommé DonnéesA dans Excel) et va chercher son contenu.
Maintenant que nous connaissons la fonction qui permet d’importer des tableaux dans Power Query, nous pouvons simplement insérer des requêtes vides et y inscrire les 3 autres fonctions nécessaires à l’importation de nos 3 derniers tableaux.
Par exemple, pour le tableau de paramètres, la fonction sera la suivante (à noter qu’entre parenthèses, on retrouve le nom du tableau donc il faut y inscrire le nom que vous lui avez attribué dans Excel).
Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation des données avec nos formations sur Power Query et Power Pivot dans Excel. |
|---|
Extraire les paramètres
Maintenant que nous avons importé nos 4 tableaux dans Power Query, nous allons récupérer les paramètres du tableau du même nom. Pour ce faire, nous allons d’abord faire une référence à la requête qui nous a permis d’importer ce tableau. Pourquoi? Parce que nous construirons 3 nouvelles requêtes qui viendront s’alimenter à cette première requête.
Ce faisant, nous allons créer 3 références en cliquant d’abord avec le bouton droit de la souris sur la requête originale et en choisissant Référence.
Nous allons d’abord extraire l’information par rapport au fournisseur. Pour cela, nous allons cliquer avec le bouton droit de la souris sur la valeur actuelle du fournisseur et nous allons choisir l’option Drill-down.
Nous allons répéter l’expérience pour extraire également le volume et la distance précisés par l’usager dans les paramètres. Au final, nous aurons donc 3 valeurs extraites des paramètres de notre tableau DonnéesSources. Nous avons rassemblés ces requêtes dans un groupe nommé Paramètres.
Au final, quand nous explorerons les dépendances entre les requêtes de cet exercice, nous verrons que la requête DonnéesSources sert à alimenter les 3 autres requêtes qui elles, permettent d’extraire les paramètres du tableau d’entrée de données.
Fusionner les informations des fournisseurs
Dans un autre groupe nommé Données départ, nous avons placé nos 3 requêtes avec les données des matrices des 3 fournisseurs.
L’idée c’est ensuite de les ajouter bout à bout à l’aide de la fonctionnalité Ajouter des requêtes comme nouvelles.
Cela nous permet d’obtenir le tableau suivant.
On peut ensuite permuter les colonnes pour présenter le tableau dans le bon ordre.
Ajuster le tableau fusionné des matrices
Étant donné que les 3 matrices sont disproportionnées (elles n’ont pas les mêmes éléments en lignes et en colonnes et elles ont plus ou moins de lignes et de colonnes), nous observons plusieurs cellules avec des null. L’idée est de remplacer ces null avec des valeurs. Lesquelles? Dans notre exemple, il s’agit de répéter les valeurs de la borne précédente. Par exemple, ci-dessous, pour le fournisseur C, pour un volume de 50, le rabais est de 5% entre 0 et 300 km. Il faudrait donc remplacer les null de la colonne 100 et 200 par 5%. Autrement dit, il nous faudrait faire un remplissage vers la droite… mais cette option n’est pas disponible dans Power Query. Nous pouvons toutefois effectuer un remplissage vers le bas. Nous allons donc créer une étape intermédiaire qui consistera à transposer le tableau. Avant de transposer le tableau, on doit d’abord prendre les titres de colonnes et les amener à l’intérieur du tableau. Ceci peut être effectuer avec la fonctionnalité Utiliser les en-têtes comme première ligne du menu Accueil.
Nous remplaçons ensuite les null par des 0 (en cliquant sur la colonne avec le bouton droit de la souris) dans la colonne 3.
Nous obtenons donc le tableau suivant.
Nous sommes alors en mesure de sélectionner l’ensemble de nos colonnes et d’appliquer la fonctionnalité Remplir vers le bas du menu Transformer.
Nous pourrons ensuite transposer le tableau à nouveau pour revenir à la présentation d’origine. Remarquez que le fournisseur C, pour le volume 50, présente maintenant un rabais de 5% pour 0, 100 et 200 unités.
Nous devons ensuite utiliser la première ligne pour les en-têtes et remplacer les tirets (-) par de grandes valeurs puisque ces tirets représentent toutes les valeurs non définies à l’extrémité des tableaux. Nous avons choisi ici un très grand nombre, que nous savons impossible à atteindre.
Extraire les informations recherchées des matrices
Pour comprendre la fonction à utiliser en M pour filtrer une colonne, nous pouvons d’abord effectuer un filtre réel sur un des fournisseurs, dans la colonne Fournisseurs.
Une fois le filtre appliqué, nous pouvons observer la fonction dans la barre de formules et remplacer ce qui suit le signe = par Fournisseur, i.e. le nom de la valeur que nous avons extraite plus tôt de notre tableau de paramètres. Cette valeur agit ici comme un paramètre, qui est contrôlé par l’usager, à partir de la feuille Excel.
Pour retenir la bonne ligne, nous devons utiliser le même type de filtre. Ce qui est important à comprendre ici, c’est que vous pourrez, selon le contexte, utiliser <, > ou =. Les symboles < et > seront utiles dans des contextes où les informations recherchées ne sont pas exactes. Ici, dans notre exemple, l’usager peut faire des recherches approximatives, i.e. rechercher des valeurs qui ne sont pas présentes dans les volumes et/ou distances des matrices. Il faut alors préciser si on veut aller chercher l’information sur la ligne/colonne inférieure ou supérieure.
Dans notre exemple, on veut extraire l’information de la valeur la plus proche mais inférieure. Ce faisant, on fait d’abord un filtre pour exclure toutes les lignes de volumes supérieures au volume recherché par l’usager et ensuite, on peut trier la colonne de volume en ordre décroissant, puis ne conserver que la première ligne.
L’option Conserver les premières lignes se trouve dans le menu déroulant des options de tableau dans le coin supérieur gauche de votre requête.
Nous obtiendrons donc un tableau d’une seule ligne. Nous pourrons supprimer la colonne Fournisseur, sélectionner la colonne Volume et Supprimer le tableau croisé dynamique des autres colonnes, afin d’obtenir tous les rabais possibles par distance, pour ce niveau de volume.
On pourra à nouveau remplacer le tiret par une très grande valeur.
Et à nouveau, nous pourrons répéter le même processus, i.e. d’abord effectuer un filtre sur les distances inférieures à celle spécifiée par l’usager dans les paramètres.
Trier en ordre décroissant de valeur et ne conserver que la première ligne. Nous obtiendrons ainsi un tableau comme le suivant:
Pour extraire le rabais, il s’agira de cliquer sur la valeur avec le bouton droit de la souris et choisir Drill-Down. Vous verrez ainsi l’extraction d’un nombre décimal, soit le taux de rabais recherché.
Tester la requête dans vos matrices
Nous pouvons maintenant nous amuser à tester notre requête en modifiant les paramètres et en cliquant ensuite sur l’option Actualiser Tout du menu Données.
Par exemple, pour le fournisseur A, nous obtenons 15.50% de rabais pour un volume de 435 et une distance de 210 puisque la requête cherche les informations sur les lignes et colonnes inférieures donc sur la ligne 400 et sur la colonne 200.
Pour le fournisseur B, nous obtenons 12.00% de rabais pour un volume de 333 et une distance de 133 puisque la requête cherche les informations sur les lignes et colonnes inférieures donc sur la ligne 250 et sur la colonne 100.
Pour le fournisseur C, nous obtenons 8.20% de rabais pour un volume de 217 et une distance de 302 puisque la requête cherche les informations sur les lignes et colonnes inférieures donc sur la ligne 150 et sur la colonne 300.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Afin d’explorer les principales fonctions et fonctionnalités de Power Query, qui permet d’importer, de transformer et de fusionner des données de diverses sources et de pouvoir les analyser efficacement, suivez la formation Excel – Introduction à Power Query et au langage M.
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
