Numeriblog › Google Workspace › Google Sheets, le tableur surpuissant › Google Sheets : traiter, utiliser, partager les données efficacement : 2/5 – mettre en relation des tables de données avec Google Sheets

Google Sheets : traiter, utiliser, partager les données efficacement : 2/5 – mettre en relation des tables de données avec Google Sheets

Dans l’article précédent, nous avons dressé quelques bonnes pratiques à appliquer dans la construction de tables de données. Une de celles-ci consistait à créer une table pour chaque famille d’information. Il s’agit à présent de […]

Laisser une évaluation

2 304
personnes ont consulté cet article

3 minutes

Rédigé par Antoine MARTIN - il y a 4 ans et modifié le 28/07/2023 à 14:56

Ce que vous allez découvrir

  • Les mises en relation possibles
  • Client cherche adresse(s)... plus si affinité. One-to-many
  • Les centres d’intérêts des clients - many-to-many

Google Sheets : traiter, utiliser, partager les données efficacement : 2/5 – mettre en relation des tables de données avec Google Sheets

Dans l’article précédent, nous avons dressé quelques bonnes pratiques à appliquer dans la construction de tables de données. Une de celles-ci consistait à créer une table pour chaque famille d’information. Il s’agit à présent de mettre ces tables dédiées en relation. L’occasion de mettre en application des fonctions de recherche conditionnelles (FILTER() ou QUERY()) et des fonctions d’affichage (JOIN()).

Les mises en relation possibles

Tentons une analogie avec les relations humaines… imaginons l’information A et l’information B et quel type de relation elles pourraient vivre :

Monogame

Fidèles et inséparables, A et B sont réunis pour la vie.

Il sont sur la même ligne dans la même table de données.

relation one-to-one

Polygame

A est volage, il a plusieurs B différents…

A et B ont chacun leur table dédiée. Dans la table de B, un champ précise à quel A il est attaché. B ne peut être lié qu’à un seul A.

relation one-to-many

Échangisme

A a plusieurs B, mais B a plusieurs A aussi ! C’est Dallas !!

A et B ont chacun leur table dédiée, une troisième table recense toutes les relations existantes entre A et B.

relation many-to-many

Client cherche adresse(s)… plus si affinité. One-to-many

Dans l’article précédent, nous avons créé une table « CLIENTS » et une table « ADRESSES ». La table « ADRESSES » possède un champ « CLIENT_ID ». Ce champ permet d’indiquer à quel client appartient chaque adresse. Nous sommes dans une relation one-to-many : plusieurs adresses pour le même client.

Le client CL-001 possède quatre adresses différentes.

Comment exploiter cette mise en relation ?

TP : afficher dans la table de données « CLIENTS » la liste des adresses connues.

1. Créez une colonne « Adresses » (notez le pluriel).

2. Dans la cellule I2 : recherchons la liste des adresses qui sont associées au client de la ligne, c’est à dire à l’ID de la cellule A2 : Je propose d’utiliser la fonction FILTER(). Pour des recherches plus complexes utilisez QUERY()

=FILTER('DONNEES ADRESSES'!A:A;'DONNEES ADRESSES'!B:B=A2)

3. Les ID des adresses de CL-001 s’affichent. Soyons un peu plus humains et affichons l’adresse compète :

=FILTER('DONNEES ADRESSES'!H:H&" > "&'DONNEES ADRESSES'!C:C&" "&'DONNEES ADRESSES'!D:D&" "&'DONNEES ADRESSES'!E:E&" "&'DONNEES ADRESSES'!F:F&" "&'DONNEES ADRESSES'!G:G;'DONNEES ADRESSES'!B:B = A2)

4. Les données occupent les lignes d’autres clients. Il faut afficher le résultat de la recherche dans une seule cellule. JOIN() avec CAR(10) qui fusionnera les réponses en les séparant par un saut de ligne.

=JOIN(CAR(10);FILTER(‘DONNEES ADRESSES’!H:H& » > « &’DONNEES ADRESSES’!C:C& » « &’DONNEES ADRESSES’!D:D& » « &’DONNEES ADRESSES’!E:E& » « &’DONNEES ADRESSES’!F:F& » « &’DONNEES ADRESSES’!G:G;’DONNEES ADRESSES’!B:B = A2))

5. Ajoutez une condition pour éviter les #NA si aucune adresse n’existe pour le client (IFNA()), et une condition (IF()) pour ne pas effectuer la recherche si le nom n’existe pas (économisons les temps de calcul)

=IF(D2<>"";IFNA(JOIN(CAR(10);FILTER('DONNEES ADRESSES'!H:H&" > "&'DONNEES ADRESSES'!C:C&" "&'DONNEES ADRESSES'!D:D&" "&'DONNEES ADRESSES'!E:E&" "&'DONNEES ADRESSES'!F:F&" "&'DONNEES ADRESSES'!G:G;'DONNEES ADRESSES'!B:B = A2)));"")

6. Étirez ou copiez-collez la formule sur toute la colonne. Malheureusement Arrayformula n’est pas exploitable avec la fonction FILTER.

Cet exemple nous permet d’exploiter la relation one-to-many entre deux tables. il en existe beaucoup d’autres, je vous invite à lire l’article qui traite de l’édition d’un devis en exploitant la relation Numéro de devis et Ligne de devis.

Les centres d’intérêts des clients – many-to-many

Imaginons à présent que les clients indiquent leurs centres d’intérêt : SPORT et/ou LOISIR et/ou CULTURE etc. Chaque client peut avoir plusieurs centres d’intérêt et chaque centre d’intérêt peut réunir plusieurs clients…

Créez une table de données « DONNEES INTERETS », sans oublier l’ID.

Il n’est pas possible de créer une colonne CLIENT comme dans la table ADRESSES, car un centre d’intérêt ne peut être liée qu’à un seul client. Et comme nous voulons associer plusieurs centres d’interet au même client, il n’est pas possible non plus de créer une colonne « INTERET » dans la table « CLIENTS »…. Il faut alors gérer cette relation dans une nouvelle table.

Créez une table « RELATIONS CLIENT INTERET »avec trois colonne : ID, CLIENT, INTERET

Cette table relationnelle, même si elle n’est pas très lisible en l’état, nous permet de créer des liens entre un client et un intérêt. Les possibilités sont infinies.

Elle met en lumière et justifie l’utilisation des ID de chaque table.

A vous de créer les tableaux de bord qui répondent à votre besoin en allant chercher les informations au bon endroit :

par exemple

  • Quels sont les centres d’intérêt de chaque client ?
  • Quels clients correspondent à chaque entre d’intérêt ?
  • …

Après avoir construit les tables de données, nous avons évoqué la question de la relation entre les différentes tables. Cette mise en relation est essentielle dans la compréhension de la manipulation de données, Sheets permet de les gérer à partir du moment où les tables sont proprement construites et où les relations ont été judicieusement tissées.

Dans le prochain article (en préparation !), nous évoquerons la question du traitement des données : ajouter (« insert » dans SQL), modifier une ligne (« update ») et supprimer (« delete ») une ligne, sans accéder à la table directement !

N’hésite pas à laisser un commentaires.

Besoin d'un peu plus d'aide sur Sheets ?

Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !

Découvrir nos formations Sheets

Articles similaires

  • Articles connexes
  • Plus de l'auteur

Rédacteur

Photo de profil de l'auteur
Antoine MARTIN

Consultant et formateur sur les outils bureautiques, j'ai intégré l'équipe de Numericoach en 2020. J'accompagne les utilisateurs de Google Workspace à trouver des solutions répondant à leurs besoins. Mes domaines de prédilections sont les outils Sheets, Docs, Slides et Google Apps Script.

S’abonner
Notification pour
1 Commentaire
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
christelle stumpf

Merci très interresant

0