Fonction équivalente à Google spreadsheet "=QUERY" join() ?

Question

Cette question concerne la fusion de deux bases de données dans un tableur Google à l'aide de la fonction =QUERY.

J'ai un tableau comme suit dans l'intervalle A1:C3

a d g
b e h
c f i

J'ai une autre table

c j m
a k n
b l o

Je veux que la table finale ressemble à ceci

a d g k n
b e h l o 
c f i j m

Je peux le faire en utilisant une fonction vlookup assez facilement dans la cellule D1 et en la collant en bas et en haut, mais mon ensemble de données est énorme. Il me faudrait une page entière de vlookups et Google Spreadsheet me dit que j'ai atteint ma limite en termes de complexité.

Je regarde la référence du langage de requête de Google... il ne semble pas y avoir de type de fonctions de "jointure" mentionné. On pourrait penser qu'il s'agit d'une opération simple de type "jointure sur A".

Quelqu'un peut-il résoudre ce problème sans recourir à une liste de contrôle ?

Answer 1

Réponse courte

Langage de requête Google La version 0.7 (2016) n'inclut pas d'opérateur JOIN (LEFT JOIN) mais cela pourrait être réalisé en utilisant une formule de tableau dont le résultat pourrait être utilisé comme entrée pour la fonction QUERY ou pour d'autres utilisations.

Explication

Formules de tableaux et les fonctionnalités de traitement des tableaux de Google Sheets permet de faire un JOIN entre deux tables simples. Afin de faciliter la lecture, la formule proposée utilise des plages nommées au lieu de références de plages.

Plages nommées

• table1 : Sheet1!A1:C3
• table2 : Sheet2!A1:C3
• ID : Sheet1!A1:A3

Formule

\=ArrayFormula(
   {
     table1,
     vlookup(ID,table2,COLUMN(Indirect("R1C2:R1C"&COLUMNS(table2),0)),0)
   }
)

Remarques :

• Il est possible d'utiliser des fourchettes ouvertes, mais cela pourrait ralentir la feuille de calcul.
• Pour accélérer le temps de recalcul :

1. Remplacer Indirect("R1C2:R1C"&COLUMNS(table2),0) par un tableau de constantes allant de 2 au nombre de colonnes du tableau2.
2. Supprimer les lignes vides de la feuille de calcul

Exemple

Véase cette fiche pour un exemple

Note

En 2017, Google a amélioré l'article d'aide officiel en anglais concernant QUERY, Fonction QUERY . Il n'inclut pas encore des sujets comme celui-ci mais pourrait être utile pour comprendre comment il fonctionne.

Answer 2

Vous pouvez utiliser ARRAYFORMULA ou vous pouvez simplement faire glisser cette formule : après une importation ou QUERY -dans le premier tableau ; dans la colonne D :

=QUERY(Sheet2!A1:C3, "Select B,C WHERE A='" & A1 & "'", 0)

Answer 3

Ceci répond donc à la façon de procéder avec une fonction Vlookup, mais dans une seule cellule.
Dans votre exemple, étant donné que chaque tableau de données a les références de cellule suivantes :

Tableau 1 : Feuille1!A1:C3

a d g
b e h
c f i

Tableau 2 : Feuille 2!A1:C3

c j m
a k n
b l o

C'est ainsi que la formule doit être construite.

Formule de jonction

=ArrayFormula(
   {
     Sheet1!A1:C,
     vlookup(Sheet1!A1:A, {Sheet2!A1:A, Sheet2!B1:C}, {2,3}, false)
   }
)

Pour que cette formule fonctionne, il faut savoir comment utiliser les parenthèses dans la plage Vlookup. Vous définissez essentiellement la première référence de cellule de la plage comme la colonne qui doit correspondre à la clé de recherche Vlookup. Le reste des références de cellules dans la plage est en relation avec les colonnes que vous souhaitez joindre.

L'index est écrit sous la forme {2,3} pour renvoyer la deuxième et la troisième colonne de la plage (la plage se compose de trois colonnes au total) ; les crochets n'ont rien à voir avec la formule de tableau dans l'index Vlookup, mais ils sont nécessaires pour renvoyer plusieurs colonnes à partir de la fonction Vlookup. La raison pour laquelle vous n'écrivez pas {1,2,3} est que vous ne souhaitez pas inclure la colonne utilisée pour la jointure.

Exemple où la colonne du tableau 2 utilisée pour la jointure est située dans une colonne différente (à droite des données à joindre).

Ce type de formule de jointure peut être utilisé même si la colonne de jointure de la deuxième table est située comme troisième colonne de cette table. Supposons que les données brutes de cet exemple ressemblent à ceci :

Tableau 1 (feuille 1) :

a d g
b e h
c f i

Tableau2 (Feuille2) :

j m c
k n a
l o b

Si vous écrivez la formule de cette manière, vous obtiendrez toujours le résultat souhaité (tel qu'il apparaît dans le tableau des données jointes) :

=ArrayFormula(
   {
     Sheet1!A1:C,
     vlookup(Sheet1!A1:A, {Sheet2!C1:C, Sheet2!A1:B}, {2,3}, false)
   }
)

Le tableau des données jointes :

a d g k n
b e h l o 
c f i j m

Dans la formule de jonction, vous remarquerez que la troisième colonne du tableau 2 est la première référence de cellule de la plage Vlookup !
La raison pour laquelle cela fonctionne est que lorsque vous utilisez des crochets dans la plage (en conjonction avec Arrayformula), le Vlookup Search_Key ne cherchera PAS une colonne comme dénominateur commun dans les données brutes, mais utilisera le tableau entre crochets comme référence pour trouver une colonne comme dénominateur commun (par défaut, il s'agit de la première colonne de la plage).

J'ai rédigé un guide complet sur ce sujet, intitulé :

Maîtriser les formules de jointure dans Google Sheets

Answer 4

J'ai résolu ce problème en utilisant Javascript LINQ (language integrated query).

Il vous permet de spécifier Javascript avec des conditions de jointure complexes. Vous pouvez également effectuer d'autres requêtes SQL telles que grouper, projeter, trier et filtrer vos feuilles comme s'il s'agissait de tables de base de données. Consultez les liens ci-dessous.

Notez que dans le langage de requête LINQ, j'ai remplacé tous les espaces dans les noms de colonnes par des traits de soulignement pour qu'ils soient des identifiants JS valides.

https://docs.google.com/spreadsheets/d/1DHtQlQUlo-X_YVfo-Wo-b7315sSk2pxL5ci4Y9lxvZo/edit?usp=sharing

https://script.google.com/d/1R5L2ReHJrBRwyoSoVOFLzEQZiGtxidPfPkAeVownt7SWX6TpacY7gA7j/edit?usp=sharing

Answer 5

Si vous pouvez associer chaque "index" (a, b, c) à une ligne ou une colonne spécifique, vous pouvez utiliser la fonction INDEX fonction.

Dans ce cas, vous pourriez probablement faire correspondre "a" à la colonne A (ou ligne 1), "b" à la colonne B (ou ligne 2), et ainsi de suite.

También, Fusionner des tableaux semblent répondre exactement à ce cas d'utilisation.