Comment intégrer des données provenant de plusieurs sources ? 3 méthodes sur Excel

Vous avez des chiffres d’affaires dans un tableau, des objectifs dans un autre, et il faut agréger les données pour les analyser ensemble ? Sur Excel, il existe aujourd’hui plusieurs manières de croiser les données de tableaux différents, et le bon choix dépend de la consolidation de données que vous visez.

Dans cet article, on passe en revue 3 niveaux de méthodes, du plus simple au plus puissant :

• Niveau 1 : les fonctions de recherche (RECHERCHEV, RECHERCHEX, INDEX/EQUIV)
• Niveau 2 : la fusion de requêtes avec Power Query
• Niveau 3 : les relations entre tables avec Power Pivot

Le prérequis commun : les trois méthodes reposent sur une colonne en commun entre les tableaux, qui sert de critère pour coupler les informations.

Niveau 1 : agréger les données avec les fonctions de recherche

Longtemps les stars d’Excel, les fonctions de recherche ne sont quasiment plus à présenter. C’était le moyen le plus rapide et le plus efficace de combiner des données de plusieurs sources dans un même classeur.

RECHERCHEV : la fonction historique

RECHERCHEV retrouve une information facilement, en partant de la valeur commune pour aller chercher la correspondance dans la table matrice. Voici sa structure :

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

Elle permettait déjà une approche par intervalles quand le critère de recherche est un nombre. Mais elle a ses talons d’Achille :

• La valeur recherchée doit se trouver à droite de la colonne de comparaison.
• Il faut compter le nombre de colonnes qui séparent la colonne de comparaison de la colonne de résultats (contournable avec EQUIV ou COLONNES, mais c’est une limite).
• Un seul résultat par formule, sans pouvoir choisir lequel s’affiche en cas de doublons.

INDEX/EQUIV : plus de souplesse

Le couple INDEX/EQUIV permet, par rapport à RECHERCHEV :

• de faire des recherches sur des tableaux à double entrée ;
• de contrôler un peu plus finement 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 renvoie un numéro de position : EQUIV(valeur_cherchée ; tableau_recherche (d'une ligne OU d'une colonne) ; [type]).

RECHERCHEX : la plus puissante

Microsoft a ensuite sorti RECHERCHEX, qui résout la plupart des problèmes de RECHERCHEV :

• un gestionnaire d’erreurs intégré quand la correspondance n’est pas trouvée ;
• plus besoin de compter les colonnes : colonne de recherche et colonne de résultats sont sélectionnées séparément ;
• possibilité de renvoyer plusieurs colonnes de résultats simultanément ;
• une recherche par intervalle plus fine, avec correspondance approximative pour les textes ;
• la possibilité de changer le sens de la recherche (auparavant, de haut en bas uniquement) ;
• les recherches à plusieurs critères pour les utilisateurs avertis.

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

Pour comparer ces fonctions, consultez notre article sur les 10 meilleures fonctions de recherche sur Excel.

La limite des formules

RECHERCHEX et toutes les recherches par formule atteignent leurs limites quand les sources de données ne se trouvent pas dans le classeur. Que faire si le tableau dans lequel on cherche est dans un autre logiciel ? C’est là qu’on passe au niveau 2.

‍

Niveau 2 : la consolidation de données avec la fusion de requêtes Power Query

On est toujours sur Excel, mais pas tout à fait : la fusion de requêtes se fait dans Power Query.

Pourquoi les formules ne suffisent plus

Malgré la richesse des fonctions de recherche, il subsiste des limites. Aujourd’hui, les sources de données sont mises à jour en permanence, et elles ne sont pas toujours sur Excel :

• API,
• Google Sheets,
• listes SharePoint,
• et bien d’autres sources.

Il faut donc un lien dynamique entre les sources. Copier-coller les données pour les interroger avec des formules n’est plus une alternative fiable.

La solution : la fusion de requêtes. Pour bien démarrer, lisez Comment bien débuter sur Power Query ou notre guide Découvrez Power Query : définition, concepts et mode d’emploi.

Comment ça marche ?

Les prémices sont les mêmes que pour les formules : une colonne commune de chaque côté. Dans certains cas, la clé est répartie sur plusieurs colonnes, et on peut alors utiliser plusieurs colonnes en commun.

Exemple : croiser chiffres d’affaires et objectifs

Imaginons deux tables :

• une avec les chiffres d’affaires mensuels de nos commerciaux ;
• une avec les objectifs mensuels fixés.

On cherche à croiser les informations.

‍

1. Dans l’onglet Accueil, cliquez sur Fusionner des requêtes.
2. Dans la fenêtre qui s’ouvre, sélectionnez les colonnes qui servent de critères. Ici, il y en a deux, pour associer le bon objectif au bon consultant et à la bonne date.

Voici le résultat final :

La fusion de requêtes est donc plus puissante que les fonctions de recherche. Pour aller plus loin, découvrez le comparatif fusion de requêtes et modèle en étoile.

‍

Niveau 3 : les relations sur Power Pivot pour agréger les données et calculer

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

C’est là que le modèle de données de Power Pivot entre en jeu :

• Il permet de coupler les tables, toujours à partir d’au moins une colonne en commun.
• Son but n’est pas forcément d’afficher les colonnes côte à côte, mais d’effectuer directement des calculs (des mesures) impliquant les deux tableaux à la fois.

La contrainte de la relation 1-*

Pour garantir l’exactitude des calculs, la relation entre deux tables sur Power Pivot doit être de type 1-* (lire « 1 vers plusieurs »). Cela signifie que chaque valeur doit être en un seul exemplaire dans au moins un des deux tableaux. Sinon, Power Pivot refuse de créer le lien.

Cette règle est aussi conseillée, mais pas obligatoire, dans les deux autres niveaux :

• avec les formules, seule la première correspondance remonte ;
• avec la fusion de requêtes, on risque d’obtenir des doublons malvenus.

‍

Quand privilégier Power Pivot ?

Cette alternative est parfaite si le but n’est pas d’enrichir un tableau, mais d’utiliser les deux tableaux en même temps dans un tableau croisé dynamique pour dégager des indicateurs. Dans cette optique, l’outil est plus performant que la fusion de requêtes.

Pour mettre cela en pratique, découvrez comment créer un tableau de bord commercial sur Excel.