Cet article fait partie d’une série consacrée à Google Sheets.
Vous pouvez copier
le fichier exemple pour retrouver toutes les formules utilisées dans les Cas pratiques.

Dans les articles précédents, vous avez approfondi les formules et les fonctions de Google Sheets. Nous avons appliqué ces concepts à quelques lignes simples mais imaginez maintenant devoir traiter un tableau de plusieurs milliers de lignes contenant chacune des dizaines de cellules . Vous pourriez vous en sortir en étendant vos formules mais les différentes manipulations vous feraient perdre beaucoup de temps.

Pour vous permettre de travailler avec de telles quantités d'informations, Google Sheets vous offre plusieurs options que nous allons creuser ensemble.


Les filtres


C'est l'option la plus accessible pour trier et filtrer un grand volume de données. 

Cas pratique
Sélectionnez les données que vous voulez filtrer puis rendez-vous dans le menu "Données" ⇢ "Créer un filtre".

Une flèche apparait alors à côté des colonnes que vous pouvez filtrer. En la cliquant, vous accéderez aux options de tri et de filtre. Qu'il s'agisse de filtrer par valeurs ou par conditions, les options sont nombreuses. Et si vous ne trouvez pas votre bonheur, vous pouvez même entrer vos propres functions de filtre. 


Une fois vos données triées et filtrées, leur analyse est bien plus facile. Il est également plus simple d'y appliquer vos formules car seules les informations pertinentes sont affichées à l'écran. 


Les filtres sont parfait pour chercher une information en particulier. Par exemple, je peux filtrer les tickets de Sophie afin de connaitre son historique d'achat.

Les filtres sont cependant limités. Il permettent de répondre à des questions simples et montrent leurs limites dès que vous avez besoin de croiser des informations.
Pas de panique, les tableaux croisés dynamiques sont là pour prendre le relais.


Les tableaux croisés dynamiques


Les tableaux croisés dynamiques ont une réputation sulfureuse. Si on leur reconnait leur finesse d'analyse, on les imagine souvent compliqués à mettre en place. 

Au final, un tableau croisé dynamique (TCD pour les initiés) n'est rien de plus que la version électronique du tableau à double entrées que nous connaissons tous. Pour vous faciliter la vie, l'éditeur de tableaux croisés dynamiques de Sheets est l'un des plus pratiques du marché. 

Bien utilisé, le tableau croisé dynamique et son éditeur vous permettent de trouver une aiguille dans un botte de foin. Voyons ensemble comment faire.

Cas pratique
Pour créer un nouveau tableau croisé dynamique, sélectionnez la plage de données qui vous intéresse puis rendez-vous dans le menu
"Données" ⇢ "Tableau croisé dynamique".
Vous voilà désormais aux commandes de l'éditeur de TCD

Nous voulons connaitre le chiffre d'affaires généré par chaque client. Pour y arriver, le logiciel va devoir additionner tous les tickets d'un même client et recommencer pour tous les clients.

 On désigne alors la colonne "Client" pour représenter les lignes du tableau croisé dynamique.

Il va maintenant falloir calculer le chiffre d'affaires pour chaque ligne (qui affiche un client, vous suivez ?). On ajoute donc la colonne "Total" comme première valeur du TCD et Sheets affiche la somme de la colonne par défaut.

On peut aller encore plus loin et vouloir connaitre le chiffre d'affaires que les clients génèrent chaque jours. Il faut alors préciser au TCD qu'il doit "découper" le chiffre d'affaires en fonction de la date. Pour y arriver, il suffit d'ajouter "Date" aux colonnes du TCD et Sheets fera la somme du Total par Client et par Date.

N'hésitez pas à tester les différentes options proposées par l'éditeur de TCD pour en découvrir toutes les possibilités. Vous pouvez bien entendu appliquer vos fonctions préférées sur votre nouveau tableau croisé dynamique et faire de beaux graphiques.

Attention cependant au caractère dynamique de votre TCD. En effet, si vos données d'entrée changent, votre TCD changera avec elles. Si par exemple, de nouveaux tickets arrivent à de nouvelles dates, et avec de nouveaux clients, le nombre de lignes et de colonnes du TCD sera modifié. Conduisant vos formules et graphiques à changer eux aussi, pour parfois, ne plus pouvoir afficher une information cohérente.

Encore une fois, si vous atteignez la limite des tableaux croisés dynamiques, Google Sheets a encore plusieurs tours dans son sac.


Les fonctions FILTER et ARRAYFORMULA


Pour afficher vos résultats, un tableau croisé dynamique passe son temps à filter des colonnes. Par exemple, afficher le chiffre d'affaires de Sophie dans un TCD revient à calculer la somme de la colonne Total pour toutes les lignes dont le client est Sophie.

Ce calcul peut également être accomplie par une fonction Sheets : FILTER

FILTER renvoie une version filtrée d'une plage source en fonction d'au moins une condition. Ainsi, seule les lignes ou colonnes qui répondent aux conditions spécifiées sont renvoyées par la fonction filter. Le résultat de la fonction filter est donc une nouvelle plage qu'on peut afficher et sur laquelle appliquer des fonctions.

Pour calculer la somme des tickets de caisses de Sophie, on pourrait alors écrire :

=SOMME(FILTER(total;client="Sophie")

Pour faciliter la lecture des formules, nous utiliserons des plages nommées dans les références de nos prochains exemples.

Si on découpe chaque étape de la formule, on voit que FILTER renvoie toutes les lignes de total pour lesquelles client = "Sophie". Et le résultat de FILTER est passé en paramètre d'entrée de la fonction SOMME.

Cette syntaxe fonctionne car SOMME peut prendre une plage comme paramètre d'entrée.

De la même manière, le ticket moyen de Sophie peut se calculer ainsi :

=MOYENNE(FILTER(total;client="Sophie")

Aussi, il serait pratique de pouvoir utiliser le résultat de FILTER en entrée de fonctions qui demandent une valeur d'entrée unique. C'est précisément le but de la fonction ARRAYFORMULA.

Il est possible de réaliser tous les traitements possibles et imaginables sur vos données en utilisant les bonnes combinaisons de fonctions. Après quelques essais, le couple FILTER + ARRAYFORMULA donnera libre court à vos les calculs les plus pointus.  

En poussant cette porte, vous entrez de pleins pieds dans le monde passionnant de la programmation fonctionnelle. Paradigme parfaitement adapté à l'analyse de données, vous pourrez alors concevoir vos documents Google Sheets comme de véritable petites applications rapides et fiables.

Et si, par hasard, cette dernière solution ne vous convenait toujours pas, il existe une ultime solution que nous détaillerons dans un prochain article. 

Si vous connaissez d'autres méthodes et que vous souhaitez nous les partager, nous sommes disponible sur le chat à droite de la fenêtre.

A très vite


Avez-vous trouvé votre réponse?