Contact: 514-605-7112 / info@lecfomasque.com

Excel : Recherches approximatives dans un ensemble de matrices disproportionnées

Publié le : 10 juin 2019

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.

 

FournisseurA
Fournisseur B
FournisseurC

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

 

Résultat Power Query

 

Importer les données 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.

 

Données à 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.

 

Excel CurrentWorkbook

 

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.

 

RequêteVide

 

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

 

Paramètres

 

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.

 

Référence Power Query

 

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.

 

Drill-down sur paramètres

 

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.

 

ParamètresRequêtes

 

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.

Dépendance des requêtes

 

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.

 

Données départ requête

 

L’idée c’est ensuite de les ajouter bout à bout à l’aide de la fonctionnalité Ajouter des requêtes comme nouvelles.

Ajouter des requêtes

 

Cela nous permet d’obtenir le tableau suivant.

 

Fusion 3 requêtes

 

On peut ensuite permuter les colonnes pour présenter le tableau dans le bon ordre.

 

Colonnes permutées

 

Ajuster le tableau fusionné

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

 

En-têtes rétrogradés

 

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.

Remplacer null par 0

 

Nous obtenons donc le tableau suivant.

 

Table transposée

 

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.

 

Rempli vers le bas

 

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.

 

Table retransposée

 

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.

 

Ent-têtes promus valeur remplacé et type modifié

 

Extraire les informations recherchées par l’usager

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.

 

Filtrer les lignes

 

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.

 

Remplacer paramètres

 

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.

 

Paramètres volume

 

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.

Tri décroissant

 

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.

 

Conserver les premières lignes

 

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.

 

Supprimer colonne et dépivoter les autres

 

On pourra à nouveau remplacer le tiret par une très grande valeur.

 

Valeurs remplacées

 

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.

 

Filtrer sur distance

 

Trier en ordre décroissant de valeur et ne conserver que la première ligne. Nous obtiendrons ainsi un tableau comme le suivant:

 

Tri décroissant et conserver première ligne

 

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

 

drill-down sur rabais

 

Tester la requête

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.

Cas2

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.

Cas1

 

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.

 

Cas3

 

Pour vous initier à Power Query, vous pouvez suivre notre formation Excel – Introduction à Power Query et au langage M, qui est disponible en ligne, en classe et en entreprise.

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

Laisser un commentaire