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

1

Google Sheets est un tableur aux possibilités de calcul, de stockage et de collaboration indéniables. Son utilisation, couplée à celle de toutes les applications de la galaxie Google, s’impose dans de nombreuses entreprises. Il atteint parfois un niveau d’exploitation semblable à celui d’un système d’information complet, à l’instar d’un ERP, un CRM, une GED… Dans cette série d’articles, fort de mon expérience de consultant et développeur d’applications web, je vous propose d’identifier quelques pratiques indispensables à la mise en place d’un système d’information pérenne 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()) est 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 entre 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.


Vous avez un projet autour de G Suite
et vous souhaitez nous solliciter :

formation | développement | paramétrage G Suite | interventions | …


Si vous avez trouvé une faute d’orthographe, veuillez nous en informer en sélectionnant le texte en question et en appuyant sur Ctrl + Entrée .

1 COMMENTAIRE

LAISSER UN COMMENTAIRE

S'il vous plaît entrez votre commentaire!
S'il vous plaît entrez votre nom ici

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.