La formule ultime de Google Sheets pour rejoindre des tableaux

Vous vous êtes déjà demandé quelle formule vous pouvez utiliser pour joindre des tables dans Google Sheets ? Et automatiquement ? Le genre de formule magique qui serait facile à réutiliser et qui fait exactement ce dont vous avez besoin. Si vous voulez savoir comment — alors continuez à lire.

Every user finds it extremely important to find efficient Google Sheets formula that can work wonders for their data. However, it has been a challenge to get such effective formulas because chaining multiple functions can quickly create complex queries.

While many formulas are at our disposal, most require some creativity to make them ideal for your situations. So, when it comes to joining two or more tables, we have to depend on some other functions and reference knowledge to create a Google Sheets formula that can do the job effectively.

Si vous avez trouvé le processus de jointure de tables ennuyeux ou compliqué, cet article vous aidera à comprendre tout ce que vous devez savoir. Plongeons donc directement dans cette formule Google Sheet pour joindre vos tables !

What Google Sheets formula do you need to join tables?

Comme nous l'avons dit, fusionner deux tables implique de combiner plusieurs fonctions. La formule que nous allons créer inclut deux fonctions puissantes de Google Sheets pour joindre nos tables : la fonction FILTER et la fonction VLOOKUP.

Comme vous pouvez le voir, les fonctions ne sont pas nouvelles, mais lorsqu'elles sont combinées avec quelques autres astuces, elles peuvent révolutionner la façon dont vous traitez vos données les plus complexes. Avant de plonger dans la formule, nous devons solidifier notre compréhension de ces fonctions de la requête avant de pouvoir la créer.

Alors, retroussons nos manches et mettons-nous au travail.

La fonction FILTER

Google Sheet formula FILTER is a powerful slice and dice function. The syntax for this function is:

=FILTER(plage, condition1, [condition2, …])

Où :

• La plage est la plage de cellules que vous souhaitez filtrer.
• Condition1 est la ligne ou la colonne qui correspond à l'ensemble de données et renvoie un tableau avec des valeurs booléennes : les valeurs TRUE ou FALSE basées sur vos conditions.
• Condition2 : Bien que ce soit un argument optionnel, il sert la même fonction que condition1, vous aidant à enchaîner les conditions. 

La fonction FILTER renvoie la version filtrée de votre plage. Elle renvoie uniquement les lignes ou colonnes qui répondent aux conditions spécifiées que vous fournissez. 

Donc, si cette fonction peut renvoyer une ligne ou une colonne en fonction de nos conditions, ce sera l'une de nos fonctions utiles.

La fonction VLOOKUP

Lorsque vous souhaitez faire référence à des données provenant de différentes feuilles ou tables dans Google Sheets, la fonction VLOOKUP est très utile. Cette fonction de recherche verticale est puissante et peut travailler avec des données provenant de feuilles externes, la rendant puissante et parfaite pour notre objectif.

You can use the Google Sheets formula VLOOKUP to source or lookup values that are matching on another table, so here’s the syntax:

=RECHERCHEV(clé_de_recherche, plage, index, [est_trié])

Où :

• La clé de recherche : Le paramètre clé_de_recherche représente les valeurs que nous voulons rechercher. Vous pouvez entrer des données, comme ‘Texas’, 3400, ou A12. Ici, vous pouvez combiner des valeurs provenant de plusieurs colonnes pour obtenir une colonne de recherche unique.
• La plage : The second parameter in the VLOOKUP Google Sheets formula is the range: the references to cells where you want to search your data. 
• L'index : Un autre paramètre de la fonction RECHERCHEV est l'index. L'index est la position de la colonne que vous souhaitez obtenir. Le numéro de colonne commence toujours à partir de la première cellule de votre plage de recherche, en commençant par 1 et non 0.
• Le paramètre est_trié : Le dernier paramètre d'une fonction RECHERCHEV est est_trié. Ce paramètre indique si nous devons trier la première colonne dans un ordre croissant ou non. Ainsi, ses valeurs peuvent être soit FAUX soit VRAI. Lorsque défini sur FAUX, la plage de recherche ne sera pas triée et restera dans l'ordre d'origine.

Illustration de RECHERCHEV

To illustrate how to join a table using the Google Sheets formula VLOOKUP , we will use the following tables for our example. Here, we have two tables that share a common key, the Nom.

If we need a Google Sheets formula to join the two tables above, here’s how we will achieve the results in Sheet C below:

Table A énumère les Noms et Départements des employés. La deuxième table énumère les Noms et leur Salaire. Si vous avez besoin de regrouper tous ces détails dans un tableau, vous pouvez utiliser VLOOKUP. Vous allez associer chaque nom dans le nouveau Tableau C (Où il avait le Noms seulement) avec le nom correspondant dans FeuilleB. 

Comment avons-nous fait cela ?

Dans B2 du Tableau C, we used this Google Sheets formula to get the Départements des employés :

=RECHERCHEV(A2,FeuilleA!A2:B4,2,FAUX) et appuyez sur Entrée. 

Où :

A2 est la clé de recherche. FeuilleA ! est une référence à FeuilleA avec table A. A2:B4 est notre plage de recherche sur Feuille A. 2 est l'index de la colonne que nous voulons sélectionner dans A2:B4.

Activé C2 de SheetC, use this Google Sheets formula to get the salary for the employees from FeuilleB: 

=VLOOKUP(A2,SheetB!A2:B4,2,FALSE) et appuyez sur Entrée.

Remarque : Faites glisser la formule vers d'autres cellules pour obtenir leurs valeurs.

Limitations de la fonction VLOOKUP et comment les surmonter

Plusieurs inconvénients sont associés à l'utilisation de la fonction VLOOKUP seule. Que faire si vous souhaitez remplir toute la colonne ou la ligne sans le faire manuellement ? Que faire si les valeurs que vous recherchez ne sont pas uniques ? Et que faire si les valeurs que vous recherchez ne se trouvent pas dans la première colonne de votre plage ?

There are two straightforward Google Sheets formula to deal with these limitations. Let’s check them out!

Utilisation des concaténations

Les concaténations sont un moyen de chaîner les valeurs de deux ou plusieurs cellules en une seule valeur. Lorsque vos colonnes contiennent des valeurs qui ne sont pas uniques, cela peut poser problème à la fonction VLOOKUP. Par conséquent, nous allons utiliser des concaténations pour créer des colonnes uniques afin que cette fonction fonctionne de manière optimale.

There are several ways to concatenate with Google Sheets formula, but we will only focus on the easiest: using the ampersand ‘&.’ For example, to concatenate cells A12 et A15, utilisez simplement =A12&A15 comme votre formule.

Because we want to do this virtually for our VLOOKUP Google Sheets formula, we will apply it to our search keys and range. So, let’s create another table with the following values:

Si vous essayez de dupliquer ou d'utiliser VLOOKUP pour remplir une autre table sans valeurs uniques, la valeur de la première occurrence de la clé de recherche sera utilisée tout au long de la recherche. Voici l'exemple d'utilisation de la table ci-dessus pour reproduire la table. Vérifiez que le deuxième Eric’s État et Salaire ne sont pas précises.

Pour éviter de telles erreurs, nous pouvons concaténer deux cellules ou colonnes pour rendre la clé unique, ce qui signifie que nous aurons une meilleure chance d'obtenir des données précises. Nous avons combiné le Nom et État pour différencier les deux Eric in the list. Here’s the complete Google Sheets formula: 

=VLOOKUP(H10&I10, {D10&E10, F10}, 2, FALSE)

Did you see we used the curly brackets to get that? What does it mean? To solve the other limitation of the Google Sheets formula VLOOKUP, we introduced the concept of arrays to allow us to create a virtual table and change the order of these tables without creating them manually.

Alors, voici la deuxième solution à la limitation :

Utilisation des arrays

Que sont les arrays dans Google Sheets? If you have a background in coding, you should know what an array means. However, it may mean different when you use it in Google Sheets formula. Arrays are tables with columns or rows of values. 

Pour différencier une ligne de valeurs d'une colonne de valeurs dans un array, la virgule est utilisée pour représenter les colonnes, et le point-virgule représente les lignes. Si c'est déroutant, voici un exemple.

Si je veux représenter nos données dans notre dernier tableau sous forme d'array, une ligne de Eric de Colorado sera {Eric,Colorado,40500}. Et les colonnes de Noms sont représentés comme, {Eric;Jane;Eric;John}.

Revenons à notre utilisation ci-dessus ; après avoir concaténé le Noms et États, sans tableaux, vous devrez créer une Colonne d'Aide to hold the concatenation. So, that is where arrays help us. With the arrays, we can now create a virtual column and use the VLOOKUP Google Sheets formula to use that table to get us the results we want.

Ainsi, la table virtuelle du tableau {D10&E10, F10} signifie que la table va fusionner les valeurs de D10 et E10 dans une colonne, et ajoutez la valeur de F10 à la colonne suivante. Donc, le tableau d'aide complet devrait ressembler à celui-ci :

Comment gérer les erreurs dans les résultats

Even when everything might seem right, sometimes errors might behave awkwardly in Google Sheets formula. So, it is crucial to have a way to handle such issues. If you deal with a small dataset, you can find and fix the errors manually if it is well with you.

Un exemple d'erreur est le #N/A erreur qui se produit soit lorsque la clé de recherche dans la formule VLOOKUP n'a trouvé aucune donnée dans une ligne sans données, soit lorsque la fonction FILTER ne trouve aucune ligne correspondante.

Heureusement, Google Sheets propose plusieurs formules pour gérer de telles erreurs. Par exemple, la fonction IFNA trouvera les cellules avec #N/A errors on your formula and replace them with a custom message. If you want to add zeros to such cells, here’s the Google Sheets formula:

Syntaxe :

=IFNA(formule, “message”)

Example, the Google Sheets formula:

=IFNA(FILTRE(RECHERCHEV(clé_de_recherche,plage,indice,est_trié), condition), “RECHERCHEV n’a trouvé aucune valeur”)

Bien que cet exemple puisse sembler idéal, imbriquer plusieurs formules de gestion d'erreurs peut compliquer vos fonctions. La bonne nouvelle est qu'il existe une alternative : la fonction IFERROR, qui est une sorte de solution universelle pour toutes les erreurs.

The syntax and usage are just like IFNA; that is, the Google Sheets formula:

=IFERROR(formule, “message”).

Notre formule en action : Fusionner deux tableaux dans Google Sheets

Mais rappelez-vous de la fonction FILTRE que nous avons introduite plus tôt ? Où s'intègrera-t-elle dans cette formule ?

Pour répondre à cela, laissez-moi vous présenter une autre limitation de la fonction VLOOKUP que nous n'avons pas encore abordée. La fonction VLOOKUP ne remplit pas automatiquement d'autres cellules avec des valeurs : vous devez copier et coller ou faire glisser les formules pour les utiliser dans d'autres cellules.

Mais c'est un autre travail que vous ne voudrez pas faire. Alors, comment pouvez-vous y remédier ? Voici comment vous pouvez le faire :

=FILTER(VLOOKUP(plageDeColonnes,{tableau},index ou {tableau d'index},est_trié))

Utiliser la fonction FILTER pour remplir les résultats de VLOOKUP

En utilisant la fonction FILTER pour filtrer les résultats de VLOOKUP sous la condition que la plage ne soit pas vide, vous pourrez fusionner vos tables dans Google Sheets automatiquement.

Dans notre exemple, notre fonction VLOOKUP trouve des valeurs dans la plage que nous avons fournie. La fonction FILTER va parcourir toutes les valeurs pour chaque cellule dans la plage VLOOKUP jusqu'à ce que la condition devienne fausse ; c'est-à-dire — lorsque VLOOKUP ne renvoie rien. La fonction FILTER remplit ensuite toutes les valeurs reçues dans leurs colonnes respectives dans notre nouvelle table.

Comme vous l'avez vu, nous avons joint trois tableaux différents en utilisant les fonctions VLOOKUP et FILTER. Décomposons maintenant les formules que nous avons utilisées :

La formule pour obtenir Nom et État de Tableau 1 :

=FILTER(VLOOKUP(I10:I14, {I10:K14},{1,2},FALSE), I10:I14<>"")

Ici, nous filtrons la plage de colonnes à partir de I10:I14 de la plage I10 à K14 avec VLOOKUP. Ensuite, nous incluons un index de tableau sur les colonnes que nous souhaitons sélectionner dans la plage ; c'est-à-dire l'index de colonne un et deux, {1,2}: Nom et État.

La formule pour obtenir Pays préféré de la Table 2 :

=FILTER(VLOOKUP(M10:M14, {M10:N14},2,FALSE), M10:M14<>"")

À partir de notre gamme M10:N10, nous voulons sélectionner la deuxième colonne (sans tri), et notre plage de colonnes M10:M14 ne doit pas être vide selon notre condition FILTER.

Enfin, la formule pour obtenir ID et Code de zone de la Table 3 :

=FILTER(VLOOKUP(P10:P14, {P10:S14},{2,3},FALSE), P10:P14<>"")

Réflexions finales sur la requête Google Sheets pour joindre plus de tables

Merging two tables can be daunting, but with the functional Google Sheets formula that we shared to join your tables, we believe it’s now easier. By combining two powerful functions that we already know and use, the process is even more clear.

To merge different tables with Google Sheets formula, use the following syntax:

=FILTER(VLOOKUP(plage_de_colonnes, {plage_de_données},{index des colonnes},est_trié), condition) 

Si vous rencontrez toujours des difficultés à utiliser ces formules, envisagez d'utiliser Dataslayer pour Google Sheets, ce qui vous permet de fusionner facilement deux tableaux, d'importer des données à partir de différentes feuilles de calcul et de travailler avec plus de 40 sources de données différentes pour vos rapports. Voulez-vous en savoir plus ? Commencez dès aujourd'hui gratuitement !