Dans cette promenade détaillée, vous apprendrez comment effectuer avec succès une jointure complète sur deux tables dans Google Sheets.
Une jointure complète combine toutes les lignes des deux tables, qu’elles aient ou non des valeurs correspondantes dans un champ commun. Elle nécessite la présence d’une colonne d’identifiant unique dans chaque table.
Les enregistrements correspondants occupent la même ligne, tandis que les enregistrements non correspondants de l’une ou l’autre table sont inclus avec des valeurs manquantes pour les champs correspondants dans l’autre table.
Avant de commencer, assurez-vous de :
- Avoir une colonne d’identifiant unique pour chaque table. Cette colonne doit contenir des valeurs distinctes pour chaque enregistrement de la table de droite. Cela représente le scénario le plus courant lors de la jointure de deux tables.
- Que les types de données des colonnes d’identifiant dans les deux tables sont compatibles. Par exemple, elles doivent être toutes les deux du texte ou toutes les deux des nombres.
Maintenant, nous allons explorer des tables d’exemples, les résultats attendus, la formule et une explication pour effectuer une jointure complète dans Google Sheets.
Note : Si vous avez des identifiants en double dans les colonnes d’identifiant (tables de gauche et de droite), veuillez consulter ce guide : Conquérir les identifiants en double : Maîtriser les jointures gauche, droite, interne et complète dans Google Sheets.
Tables d’exemples et table résultante de la jointure complète
Commandes (Table de gauche) :
| ID de commande | ID du client | Article | Prix |
|---|---|---|---|
| 1 | 1 | Article 1 | 10 |
| 2 | 2 | Article 2 | 15 |
Clients (Table de droite) :
| ID du client | Nom | Ville |
|---|---|---|
| 1 | Jean | Paris |
| 3 | Marie | Lyon |
Résultat attendu de la jointure complète :
| ID du client | ID de commande | Article | Prix | Nom |
|---|---|---|---|---|
| 1 | 1 | Article 1 | 10 | Jean |
| 2 | 2 | Article 2 | 15 | |
| 3 | Marie |
Dans cet exemple, les deux tables partagent une colonne commune « ID du client ».
Une jointure complète combine toutes les lignes en fonction de cet identifiant partagé, y compris la possibilité que certains clients n’aient pas de commandes ou que certaines commandes n’aient pas d’informations sur le client correspondant.
Formule tableau pour joindre complètement deux tables dans Google Sheets
La formule de tableau suivante effectue une jointure complète entre deux tables dans Google Sheets. Elle suppose que les enregistrements de la table de droite sont uniques.
Formule :
=ArrayFormula( LET( lt, A2:D6, lt_id, B2:B6, rt, F2:H5, rt_id, F2:F5, key, VSTACK(lt_id, TOCOL(IFNA(XMATCH(rt_id, lt_id)/0, rt_id), 3)), look_up, IFNA(VLOOKUP(key, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt)+1), 0), HSTACK(key, )), IFNA(HSTACK(lt, look_up)) ) )
Où :
- A2:D6 est la plage de la table de gauche, y compris la ligne d’en-tête.
- B2:B6 est la colonne d’identifiant unique dans la table de gauche, y compris le libellé du champ.
- F2:H5 est la plage de la table de droite, y compris la ligne d’en-tête.
- F2:F5 est la colonne d’identifiant unique dans la table de droite, y compris le libellé du champ.
Dans la formule ci-dessus, vous devez remplacer IFNA(HSTACK(lt, look_up)) par CHOOSECOLS(IFNA(HSTACK(lt, look_up)), {5, 1, 3, 4, 7, 8}) pour obtenir un résultat similaire à la table résultante ci-dessus. Cette modification, spécifiquement CHOOSECOLS, sélectionne les colonnes requises à partir de la jointure complète dans l’ordre souhaité.
Note : Si une colonne dans l’une des deux tables contient des dates, vous devez formater cette colonne dans la table jointe en tant que Format > Nombre > Date.
Consultez la cellule A11 de la cinquième feuille du tableau d’exemple fourni pour la formule de tableau de jointure complète.
Tableau d’exemple
Explication de la formule
Pour simplifier la modification de la formule de tableau de jointure complète, nous avons utilisé la fonction LET.
La fonction LET attribue un nom à un résultat de calcul ou à une référence de cellule/plage. Cela vous permet de réutiliser ces valeurs dans votre formule sans répéter le calcul ou la référence, améliorant ainsi les performances et la lisibilité.
Syntaxe de la fonction LET :
LET(nom1, expression_de_valeur1, [nom2, ...], [expression_de_valeur2, ...], expression_de_formule)
Références de plage attribuées
Voici les références de plage pour la jointure complète de deux tables dans Google Sheets :
-
lt(nom1) : Définit la plage de données de la table de gauche (Commandes), y compris la ligne d’en-tête.- A2:D6 (expression_de_valeur1) : Plage de données de la table de gauche (Commandes).
-
lt_id(nom2) : Définit la colonne d’identifiant de la table de gauche (Commandes), y compris la ligne d’en-tête.- B2:B6 (expression_de_valeur2) : Colonne d’identifiant de la table de gauche (Commandes).
-
rt(nom3) : Définit la plage de données de la table de droite (Clients), y compris la ligne d’en-tête.- F2:H5 (expression_de_valeur3) : Plage de données de la table de droite (Clients).
-
rt_id(nom4) : Définit la colonne d’identifiant de la table de droite (Clients), y compris la ligne d’en-tête.- F2:F5 (expression_de_valeur4) : Colonne d’identifiant de la table de droite (Clients).
Note : Vous devez uniquement spécifier les expressions_de_valeur ci-dessus 1 à 4 pour vos deux tables. La formule de tableau de jointure complète s’occupe du reste.
Résultats de calcul intermédiaires attribués
Clés VLOOKUP : Clés de recherche pour la jointure complète
key(nom5) : Définit les clés de recherche pour VLOOKUP.VSTACK(lt_id, TOCOL(IFNA(XMATCH(rt_id, lt_id)/0, rt_id), 3))(expression_de_valeur5) :
Cette « clé » est un élément crucial pour effectuer une jointure complète dans Google Sheets. Elle combine verticalement deux composantes :
- Tous les identifiants uniques de la table de gauche : cela garantit que tous les enregistrements de la table de gauche sont inclus dans la jointure.
- Les identifiants uniques de la table de droite qui sont absents de la table de gauche : cela est réalisé grâce à une combinaison de XMATCH, de conversion d’erreurs et d’IFNA.
- XMATCH localise et renvoie l’indice (position) de chaque identifiant de la table de droite dans la table de gauche. Pour les valeurs non trouvées, il renvoie #N/A.
- Diviser la position par zéro déclenche intentionnellement le retour des identifiants correspondants sous forme de valeurs DIV/0!.
- IFNA remplace les valeurs #N/A par les identifiants de droite correspondants.
- Enfin, TOCOL convertit cette liste d’identifiants uniques « droite seulement » en une seule colonne.
En empilant (VSTACK) cette colonne « droite seulement » avec les identifiants de la table de gauche, la « clé » garantit que toutes les lignes des deux tables sont incluses dans la jointure complète en fonction de leur présence d’ID unique.
VLOOKUP : L’âme de la jointure complète de deux tables dans Google Sheets
VLOOKUP est un élément crucial de la jointure complète entre deux tables dans Google Sheets.
look_up(nom6) :IFNA(VLOOKUP(key, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt)+1), 0), HSTACK(key, ))(expression_de_valeur6) :
Cette partie de la formule effectue une opération de recherche de données dans la table de droite (rt) en fonction de la colonne clé créée précédemment. Analysons-la étape par étape :
- VLOOKUP :
VLOOKUP(key, HSTACK(rt_id, rt), SEQUENCE(1, COLUMNS(rt)+1), 0)est la fonction de recherche principale.- key : Il s’agit de la colonne contenant les identifiants uniques combinés des deux tables (créée par VSTACK précédemment).
- HSTACK(rt_id, rt) : Cela spécifie la plage de recherche. Elle combine la colonne d’identifiant de la table de droite (rt_id) avec la table de droite (rt) horizontalement.
- SEQUENCE(1, COLUMNS(rt)+1) : Génère une plage de nombres représentant les positions des colonnes dans la table de droite.
- 0 : Cela indique qu’une correspondance exacte est requise pour la recherche.
- IFNA :
- Cette fonction encapsule VLOOKUP pour gérer les erreurs potentielles.
- Si une correspondance est trouvée pour une clé dans la table de droite, VLOOKUP renvoie les données correspondantes de la plage de recherche (HSTACK(rt_id, rt)).
- Si aucune correspondance n’est trouvée, VLOOKUP renvoie un tableau d’erreurs #N/A, dont le nombre d’erreurs correspond à la taille de la plage de recherche (nombre de colonnes).
- IFNA remplace les erreurs potentielles par un modèle dérivé de HSTACK(key, ). Ce modèle reproduit la clé dans la première cellule et remplit les cellules restantes avec des valeurs NA, correspondant au nombre de colonnes renvoyées par VLOOKUP en cas de recherches réussies.
Partie de l’expression de la formule du tableau de jointure complète
Ce segment fusionne les données de la table de gauche (lt) avec le résultat de la recherche (look_up) et remplace les #N/A (retournés lors de la mise en pile) par des blancs.
IFNA(HSTACK(lt, look_up))
Décomposition de la formule :
- HSTACK(lt, look_up) : Cette partie concatène horizontalement (empile) les données de la table de gauche (lt) avec les résultats de la recherche (look_up) de la table de droite.
- IFNA(HSTACK(lt, look_up)) : Cela encapsule l’étape précédente avec la fonction IFNA. Cette fonction remplace les erreurs potentielles rencontrées lors de la recherche par des valeurs vides.
Notes importantes :
- Le résultat de la recherche comprend tous les enregistrements de la table de droite. Cela garantit une jointure complète, même pour les enregistrements sans informations correspondantes dans la table de gauche.
- Envelopper cette formule dans CHOOSECOLS est nécessaire lorsque la jointure complète crée plusieurs colonnes d’identifiants uniques. Cette fonction vous permet de sélectionner des colonnes spécifiques à partir du résultat de la jointure, y compris la colonne clé que vous avez créée.
Ressources connexes
- Comment réaliser une jointure gauche entre deux tables dans Google Sheets.
- Comment réaliser une jointure droite entre deux tables dans Google Sheets.
- Comment réaliser une jointure interne entre deux tables dans Google Sheets.
