Sur Excel, il existe aujourd'hui plusieurs manières de croiser les données provenant de tableaux différents. Toutes ces manières reposent sur un seul prérequis : il existe une colonne en commun entre les tableaux qui va servir de critère pour coupler les informations.

‍

Avec l'approche la plus simpliste, nous pouvons imaginer le fonctionnement ainsi : si la valeur contenue dans la cellule considérée de la colonne du premier tableau correspond à la valeur considérée dans la colonne en commun du 2ème tableau, alors je vais chercher les informations qui sont sur la même ligne du 2ème tableau. Sinon, je regarde la suivante, puis la suivante, jusqu'à ce que je trouve une correspondance, ou bien que le 2ème tableau se termine. Puis je reproduis la même opération sur toutes les lignes du premier tableau.

‍

Considérons les 3 niveaux « de recherche » à notre disposition sur Excel :

Niveau 1 : Utiliser une fonction de recherche (RECHERCHEV traditionnellement, RECHERCHEX plus récemment, et le couple INDEX/EQUIV)

Niveau 2 : Fusionner des requêtes via Power Query

Niveau 3 : Créer des relations entre les tables sur Power Pivot

‍

Niveau 1 : les fonctions de recherche

‍

Longtemps les stars d'Excel, les fonctions de recherche ne sont quasiment plus à présenter. Pour cause, c'était le moyen le plus rapide et efficace pour combiner des données de plusieurs sources différentes. En figure de proue, nous avons RECHERCHEV, qui permet de retrouver une information assez facilement. En voici la structure :

‍

= RECHERCHEV(valeur_cherchée ; table_matrice ; no_index_col ; [valeur_proche])

‍

Cette fonction, qui était la plus connue à une époque, était puissante puisqu'elle partait de la valeur en commun pour aller retrouver la correspondance dans la table matrice. En plus de ça, elle permettait déjà une approche par intervalles dans le cas où le critère de recherche était un nombre. Cela étant, elle a tout de même ses talons d'Achille.

‍

Plusieurs contraintes sont donc inhérentes à cette fonction : d'abord, il faut que la valeur que nous recherchons se trouve bien à droite de la colonne « de comparaison ». Ensuite, il fallait compter le nombre de colonnes qui séparait la colonne de comparaison de la colonne de résultats. Certes, c'était bien contournable en utilisant des fonctions supplémentaires telles que EQUIV ou COLONNES, mais cela représentait bien une limite. Ensuite, un seul résultat était possible par formule, et nous ne pouvions pas choisir lequel serait affiché, dans le cas de doublons.

‍

Dans la même veine, il y avait également le couple INDEX/EQUIV qui présentait, par rapport à RECHERCHEV, la possibilité d'effectuer des recherches sur des tableaux à double entrée, ainsi que de contrôler d'un peu plus près la recherche par intervalles. Voici la structure :

‍

= INDEX(matrice ; numéro de ligne ; numéro de colonne), les deux arguments numéro de ligne et numéro de colonne sont remplacés par une fonction EQUIV qui a une structure proche de RECHERCHEV mais qui ne ressort qu'un numéro de position : EQUIV(valeur_cherchée ; tableau_recherche (d'une ligne OU d'une colonne) ; [type]).

‍

Plus tard, Microsoft a sorti une fonction qui a résolu pas mal des problèmes de RECHERCHEV, donnant la fonction la plus puissante quand il s'agit des fonctions de recherche : RECHERCHEX. Parmi les évolutions qu'elle apporte : un gestionnaire d'erreurs intégré pour quand la correspondance n'est pas trouvée, plus besoin de compter les colonnes dans un tableau, puisque maintenant la colonne de recherche et la colonne de résultats sont sélectionnées séparément, il est possible de sélectionner plusieurs colonnes de résultats simultanément, la recherche par intervalle est plus fine, avec l'intégration de correspondance approximative pour les textes, et la possibilité de changer le sens de la recherche, jusqu'ici possible de haut en bas uniquement. Enfin, pour les utilisateurs avertis, elle permet également d'effectuer des recherches à plusieurs critères. En voici donc la structure :

‍

=RECHERCHEX(valeur_cherchée ; tableau_recherche ; tableau_renvoyé ; [si_non_trouvé] ; [mode_correspondance] ; [mode_recherche]).

‍

Là où RECHERCHEX et toutes les recherches via formule atteignent leurs limites, c'est quand les sources de données ne se trouvent pas déjà dans le classeur. Que se passe-t-il si le tableau dans lequel je recherche des données ne se trouve même pas sur Excel, mais dans un autre logiciel ? C'est à ce moment-là que nous passons au niveau 2.

‍

Niveau 2 : la fusion de requêtes

‍

Nous sommes toujours sur Excel, mais pas complètement. La fusion de requêtes se fait sur Power Query. Malgré la richesse et l'évolution des fonctions de recherche au fil du temps, il subsiste des limites. Aujourd'hui, nous sommes dans une ère où les sources de données sont constamment mises à jour, et où elles ne sont pas toujours sur Excel (API, Google Sheet, Listes SharePoint, et autres sources…). C'est pourquoi il faut un lien dynamique entre les sources et copier-coller celles-ci pour en effectuer des recherches en utilisant des formules n'est plus une alternative fiable pour pallier ça. Solution : la fusion de requêtes.

‍

Les prémices sont les mêmes, il y a une colonne qui va faire le lien de chaque côté donc qui sera en commun entre les deux tables (au minimum, il existe également des cas avec plusieurs colonnes en commun, pour les cas où la clé est distribuée sur plusieurs colonnes).

‍

Prenons un exemple, cette fois-ci, pour illustrer ça :

Imaginons que nous avons d'un côté une table avec les chiffres d'affaires mensuels respectifs de nos commerciaux, et de l'autre côté les objectifs mensuels fixés, et que nous cherchons à croiser les informations :

Puis, en cliquant sur Fusionner des requêtes dans Accueil, j'obtiens cette fenêtre, où je vais sélectionner les colonnes (puisqu'ici, j'ai deux critères pour croiser les informations) qui vont me servir pour mettre le bon objectif, en face du bon consultant et de la bonne date (nous détaillerons les types de jointure et ce qu'ils font dans un futur article, 100% dédié aux fusions de requêtes) :

Le résultat final est donc celui-ci :

Nous sommes donc sur une étape encore plus puissante que les fonctions de recherche. Maintenant, attaquons le niveau 3, spécialisé dans les calculs : les relations sur Power Pivot.

‍

Niveau 3 : Les relations sur Power Pivot

‍

La fusion de requêtes est très efficace pour combiner des tables en quelques clics. Seulement, elle pose parfois des soucis, notamment quand on cherche à utiliser le tableau combiné sur des volumes importants.

C'est ici que le modèle de données entre en jeu sur Power Pivot : il permet de coupler les tables, toujours en partant du principe qu'il y a au minimum une colonne en commun entre les deux tables.

Le but ici n'est pas forcément d'afficher les colonnes des deux tableaux côte à côte mais de pouvoir directement effectuer des calculs (des mesures) qui impliquent les deux tableaux à la fois.

‍

Il y a également une contrainte supplémentaire, pour garantir l'exactitude des calculs, la relation entre deux tables (sur Power Pivot en tous cas) doit être de type 1-*. C'est également conseillé dans les deux autres niveaux mais pas obligatoire, puisque dans le cas des formules, il ne remontera que la première correspondance, et dans le cas de la fusion de requêtes, il risque d'y avoir des doublons malvenus.

‍

1-* (lire 1 vers plusieurs) veut dire que chaque valeur doit être en un seul exemplaire dans au moins un des deux tableaux, sinon Power Pivot refusera tout simplement de créer le lien.

Voici un exemple de lien possible :

Si nous résumons, cette alternative est parfaite si le but n'est pas forcément d'enrichir un tableau mais de permettre l'utilisation des deux tableaux en même temps dans un tableau croisé dynamique pour effectuer des calculs, donc dégager des indicateurs directement. Dans cette optique-là, l'outil est plus performant que la fusion de requêtes.

‍

Quelle technique utiliser ?

‍

La réponse à cette question est… ça dépend. Si les 3 outils existent encore, c'est qu'ils ont des utilisations où ils sont les plus adéquats. Résumons tout cela en un tableau (la croix est la technique conseillée) :