Numeriblog Google Workspace Google Sheets, le tableur surpuissant Google Sheets : nettoyer et optimiser sa table de données en 9 conseils

Google Sheets : nettoyer et optimiser sa table de données en 9 conseils

Google Sheets, le tableur hyperperformant de Google, fonctionne en ligne. Comme ses collègues Docs et Slides, Google Sheets coche toutes les cases de la facilité d’accès depuis n’importe quel appareil, de la collaboration et de […]

personnes ont consulté cet article

5 minutes

Rédigé par Antoine MARTIN - il y a 3 semaines

Ce que vous allez découvrir

  • 1. Supprimer les cellules inutiles : on n’est pas à Versailles ici !
  • 2. Vérifier la pertinence d’un calcul avant de l’effectuer 
  • 3. Faites une seule fois mille calculs plutôt que mille calculs à la suite
  • 4. Choisissez la fonction à bon escient : On n’écrase pas une mouche avec un bulldozer !
  • 5. Utiliser les fonctions "volatiles" avec parcimonie 
  • 6. Utiliser des références
  • 7. Limiter les mises en forme conditionnelles
  • 8. Partitionner le fichier
  • 9. Intégrer les images sans les copier-coller.

Google Sheets : nettoyer et optimiser sa table de données en 9 conseils

Google Sheets, le tableur hyperperformant de Google, fonctionne en ligne. Comme ses collègues Docs et Slides, Google Sheets coche toutes les cases de la facilité d’accès depuis n’importe quel appareil, de la collaboration et de la mise à jour des fonctionnalités. Mais, avouons-le, l’outil de gestion des feuilles de calcul de Google souffre parfois de lenteur d’exécution car il dépend de la disponibilité des serveurs de Google et de la qualité de la connexion Internet. 

Voici quelques conseils de bonnes pratiques pour optimiser la réactivité de vos feuilles de calcul sur Google Sheets !

1. Supprimer les cellules inutiles : on n’est pas à Versailles ici !

La notion de sobriété énergétique s’applique aussi aux fichiers de calcul.

La capacité actuelle d’un fichier Google Sheets est de 10 millions de cellules, qu’elles contiennent une valeur, une formule ou qu’elles soient vides. C’est un grand nombre et pour autant il est facile à atteindre. 

Par défaut, une feuille contient 1000 lignes (de 1 à 1000) et 26 colonnes (de A à Z).

Mais au fur et à mesure de son utilisation, cette même feuille peut contenir plusieurs centaines de milliers de lignes et jusqu’à 18 278 colonnes ! (source) . Cela peut arriver suite à une mauvaise manipulation ou un héritage d’un fichier Excel importé sur Google Sheets.

Il faut chasser les cellules vides. Les petits réflexes accumulés offrent de grands résultats. De plus, cela offre un confort de lecture non négligeable.

Comment supprimer les cellules vides : 

Une fois que vous avez créé votre table de données, vos graphiques, vos tableaux croisés dynamiques ou vos tableaux de bord, pensez à faire la chasse à toutes les cellules vides : 

  • sélectionnez les colonnes et les lignes inutiles ;
  • supprimez-les. 

Voici une table de données avant nettoyage : 

Voici la table de données après suppression des colonnes et lignes inutiles :

Si le fichier a besoin de plus de places pour s’exprimer, il créera automatiquement les colonnes et les lignes nécessaires 😉

Astuce : Si vous avez besoin d’un graphique, ajoutez une seule colonne assez grande pour le contenir. 

Mais gardez à l’esprit que l’ajout  d’une colonne vide impacte le quota de 10 millions de cellules d’autant de lignes déjà créées dans cette même table.

2. Vérifier la pertinence d’un calcul avant de l’effectuer 

Le dimanche matin avant d’aller chercher des croissants, demandez-vous tout d’abord si la boulangerie est ouverte ! (vécu)

Dans une feuille de calcul Google Sheets, il faut prendre les mêmes précautions : avant d’effectuer un calcul, assurez-vous que celui-ci soit utile, voire possible.

Par exemple, avant de calculer le montant global d’une ligne de commande d’articles, vérifiez d’abord si la colonne “Article” contient une valeur avant de chercher le résultat de la multiplication du nombre d’articles par le prix unitaire. 

Ajoutez une condition (par exemple ici : <> » » signifie est différent de vide) avant la formule : 

= SI(A2<>""; si vrai exécuter la formule...  ;   )

Exemple : cette feuille affiche dans la colonne E le montant global de chaque ligne, soit la multiplication du prix unitaire par la quantité vendue. 

La formule, une simple multiplication, est étirée jusqu’en bas du tableau. 

Le résultat : 

Les lignes qui ne contiennent pas d’informations pour l’instant affichent 0,00€. Non seulement cela alourdit la lecture mais en plus, cela allonge le temps de calcul du fichier.

Pour autant, la formule doit être présente sur ces lignes vides en prévision des futures ventes de l’entreprise ! 

Ajoutez une condition pour que le calcul se fasse uniquement si c’est utile. 

Par exemple : effectuer la multiplication si l’ID du produit n’est pas vide, sinon ne rien faire. 

=SI(A2<>;"";C2*D2;)

L’affichage est plus propre et surtout, quelle économie d’énergie pour Sheets !

Attention, il ne suffit pas de vérifier si l’opération rencontre une erreur avec SI.ERREUR() avant d’afficher le résultat pour alléger le fichier, car dans ce cas, Sheets doit effectuer l’opération avant de l’afficher ou non, donc les performances seront toujours impactées.

L’économie est encore plus intéressante avec le conseil suivant !

3. Faites une seule fois mille calculs plutôt que mille calculs à la suite

Google Sheets offre la possibilité d’appliquer certaines fonctions à une plage de données au lieu d’une seule cellule. 

Par exemple, dans le cas de calcul d’une multiplication : 

La colonne E contient 999 formules étirées jusqu’à la dernière ligne (parce qu’ici il n’y a “que” 1000 lignes. Cela pourrait être beaucoup plus.)

=C2*D2
=C3*D3
=C4*D4
...

La colonne F ne contient qu’une seule formule. 

=ArrayFormula(C:C*D:D)

L’économie d’énergie est flagrante ici. À ceci s’ajoute la centralisation de la formule en un seul endroit ce qui permet une mise à jour rapide et automatiquement appliquée à toute la colonne.

Astuce 1 : Affichez le titre de la colonne si la ligne est la première : 

=ArrayFormula(si(ligne(C:C)=1;"Montant";C:C*D:D))

Astuce 2 : Appliquez le conseil précédent, c’est à dire vérifier si le calcul est utile avant de l’effectuer, sur la condition de calcul de l’opération

=ArrayFormula(SI(C:C<>"";SI(LIGNE(C:C)=1;"Montant";C:C*D:D);))

Le résultat est le même : 

Cliquez ici pour en savoir plus sur ArrayFormula() 

4. Choisissez la fonction à bon escient : On n’écrase pas une mouche avec un bulldozer !

Chaque fonction a son but et son périmètre d’action. Plusieurs buts peuvent être atteints avec des fonctions différentes, alors le choix se fera au regard du contexte. 

Par exemple : Pour filtrer des données, nous avons le choix entre Filter() (en savoir plus) et Query() (en savoir plus)

Chaque fonction présente ses intérêts mais aussi ses impacts sur les performances de fonctionnement. Choisissez la bonne fonction adaptée aux besoins recherchés !

5. Utiliser les fonctions « volatiles » avec parcimonie 

Les fonctions volatiles sont des fonctions qui se mettent à jour en permanence. La fonction =MAINTENANT() par exemple affiche la date et l’heure actuelle à la seconde près. 

Liste des fonctions volatiles : 

  • AUJOURDHUI()
  • MAINTENANT() 
  • ALEA()
  • ALEA.ENTRE.BORNES() 

Par exemple : Vous avez besoin de connaître le nombre de jours passés depuis chaque vente d’un produit. 

=DATEDIF(C2;AUJOURDHUI();"D")

Google Sheets doit rechercher 21 fois la date d’aujourd’hui, qui sera toujours la même au moment du calcul. 

Il vaut mieux rechercher une seule fois la valeur de la date du jour et faire référence à cette valeur dans chaque formule : 

=DATEDIF(C2;$H$1;"D")

6. Utiliser des références

Dans la lignée du conseil précédent, il faut parfois mieux recourir à des références au lieu de données brutes dans les formules. 

Par exemple : dans notre feuille de ventes d’articles, nous cherchons à afficher automatiquement le nom du produit en fonction de son identifiant : 

La formule est extrêmement laborieuse à construire et à mettre à jour ! De plus elle sollicite beaucoup le moteur de calcul de Sheets.

=SI (A2="PROD-0001" ; "Livre 01" ; SI (A2="PROD-0002" ; "Livre 02" ; SI (A2="PROD-0003" ; "Livre 03" ; SI (A2="PROD-0004" ; "Livre 04" ; SI (A2="PROD-0005" ; "Livre 05" ; SI (A2="PROD-0006" ; "Livre 06" ; SI (A2="PROD-0007" ; "Cahier 01" ; SI (A2="PROD-0008" ; "Cahier 02" ; SI (A2="PROD-0009" ; "Cahier 03" ; SI (A2="PROD-0010" ; "Cahier 04" ; SI (A2="PROD-0011" ; "Cahier 05" ; SI (A2="PROD-0012" ; "Cahier 06" ; SI (A2="PROD-0013" ; "Cahier 07" ; SI (A2="PROD-0014" ; "Cahier 08" ; SI (A2="PROD-0015" ; "Cahier 09" ; SI (A2="PROD-0016" ; "Cahier 10" ; SI (A2="PROD-0017" ; "Cahier 11" ; SI (A2="PROD-0018" ; "Cahier 12" ; SI (A2="PROD-0019" ; "Cahier 13" ; SI (A2="PROD-0020" ; "Stylo 01" ; SI (A2="PROD-0021" ; "Stylo 02" ; SI (A2="PROD-0022" ; "Stylo 03" ; SI (A2="PROD-0023" ; "Stylo 04" ; SI (A2="PROD-0024" ; "Stylo 05" ; SI (A2="PROD-0025" ; "Stylo 06" ; SI (A2="PROD-0026" ; "Stylo 07" ; SI (A2="PROD-0027" ; "Stylo 08" ; SI (A2="PROD-0028" ; "Stylo 09" ; SI (A2="PROD-0029" ; "Stylo 10" ; SI (A2="PROD-0030" ; "Stylo 11" ; SI (A2="PROD-0031" ; "Stylo 12" ; )))))))))))))))))))))))))))))))

Dans ce cas, il est préférable d’utiliser une table de références et de rechercher la valeur “nom de l’article” en fonction de la valeur “Id de l’article”.

La formule devient alors : 

=xlookup(A2;PRODUITS!A:A;PRODUITS!B:B;)

en appliquant les conseils précédents (vérifier si le calcul est utile et une seule formule avec Arrayformula()) vous obtiendrez : 

=ArrayFormula(SI(A:A&lt;&gt;"";SI(LIGNE(A:A)=1;"Nom de l'article";xlookup(A2;PRODUITS!A:A;PRODUITS!B:B;));))

7. Limiter les mises en forme conditionnelles

Les mises en formes conditionnelles permettent de modifier le format d’une cellule (couleur de fond, couleur de la police, gras ou italique) en fonction de valeur de la même cellule ou d’une autre cellule. 

En savoir plus ici sur les mises en forme conditionnelles.

Elles apportent un confort de lecture pour mettre en avant des informations. 

Cependant elles sont très consommatrices d’énergie. Il arrive aussi qu’une mise en forme soit démultipliée sur de nombreuses plages en cas de copié collé de lignes. 

Si votre feuille de calcul met beaucoup de temps à charger, pensez à nettoyer voire supprimer les mises en forme conditionnelles.

8. Partitionner le fichier

Votre fichier contient de nombreuses feuilles chacune dédiée à une série de calcul, créez un fichier par groupe de calcul et importez les données selon les besoins avec des fonctions Importrange()

9. Intégrer les images sans les copier-coller.

Conseil de Google, si votre fichier contient des images, il vaut mieux les afficher avec la fonction “IMAGE()” plutôt que de les copier et coller depuis votre presse-papier.


Voici donc les conseils que je communique régulièrement aux utilisateurs de Google Sheets, il en existe de nombreux autres, n’hésitez pas à les partager dans les commentaires. 

Si votre feuille de calcul montre des signes de fatigue, appliquez ces bonnes pratiques ou bien faites appel à Numericoach, qui propose des temps d’accompagnement pour optimiser vos fichiers de calcul !

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

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
guest
0 Commentaires
Commentaires en ligne
Afficher tous les commentaires