Google Sheets : générez vos devis avec des feuilles de calcul

1

Dans cet article, nous nous pencherons sur la question de la construction et de la génération des devis à l’aide de plusieurs feuilles de calcul Google Sheets.

Nous évoquerons ainsi de façon transversale :

  • la différence entre une base de données et une vue d’informations (à ce propos lire cet article);
  • les fonctionnalités de partage et d’affichage d’informations (#validation des données, #plages nommées, #importrange(), #filter(), #query()) ;
  • l’organisation de feuilles de calculs au sein d’une équipe ;
  • la génération de PDF à partir de Google Sheets.

Contexte

Vous travaillez pour l’agence de graphistes « 10Zaïne » que nous avons déjà croisée dans l’article suivant : Recenser le temps passé sur les projets .

Vous possédez déjà une base de données CLIENTS.

Vous êtes missionné.e pour mettre en place un système de création de devis.

Organigramme des données

La création d’un devis simple demande la mise en commun de plusieurs données qui proviennent chacune d’une table différente. Pour faire simple, voici un organigramme des tables de données et de leurs interactions :

  • Une table DEVIS centralise les informations de base d’un devis : La référence, la date, le titre (qui pourrait reprendre le titre du projet), le client (à aller chercher dans la table « clients » externalisée) et la somme totale du devis (calculée grâce à la table « LIGNES ») ;
  • Une table LIGNES DE DEVIS détaille les tâches devisées, relatives à un devis (référence à aller chercher dans « DEVIS »), l’intitulé de la tâche, le prix unitaire, la quantité et le prix total de la ligne ;
  • Une table CLIENTS : un annuaire indispensable à toute organisation.

Voyons comment organiser ces tables et comment en extraire un devis propre et communicable à vos clients !

Créez la table DEVIS

Créer une nouveau fichier Google Sheets que vous pouvez appeler GESTION COMMERCIALE séparée de celle des CLIENTS.

En effet, la base clients peut être utilisée par des collaborateurs de votre agence qui ne doivent pas avoir accès aux informations commerciales des devis. Il vaut mieux ne pas tout mélanger.

Créez dans GESTION COMMERCIALE une feuille DEVIS : que vous pouvez remplir avec des données fictives. Laissez la colonne CLIENT vide pour l’instant.

Importez les informations « CLIENTS »

Préparez la table « clients »

Ouvrez votre fichier CLIENTS (pour créer ce fichier voir : l’article suivant)

Il est possible d’importer la totalité de la feuille mais je préconise de n’importer que les colonnes qui nous intéressent.

Nous allons pour cela identifier clairement chaque colonne du fichier « clients » en utilisant les plages nommées.

Les plages nommées dans ce cas précis permettent

  • de n’importer que certaines colonnes
  • de ne pas être tributaire des manipulations de colonnes effectuées sur la base de données clients.
Création d’une plage nommée
Vue de toutes les plages créés, j’ai assemblé les colonnes « adresses » en un seule plage.

Importez les données

Dans le fichier SUIVI COMMERCIAL, créez une nouvelle feuille. J’ai l’habitude de nommer les feuilles dans lesquelles j’importe des données externes avec le préfixe « IMPORT… ». Nommons donc cette nouvelle feuille IMPORT CLIENTS.

Dans la cellule A1, nous allons importer avec la fonction importrange() la plage nommée CLIENT_NOM venant du fichier CLIENTS.

La fonction IMPORTRANGE() contient deux arguments obligatoires :

  • l’URL du fichier de base, à récupérer dans la fonction « partager » du fichier :
Récupérez l’URL du Sheets en utilisant la fonction « Partager »
  • la plage nommée à importer
    (ou la plage classique de type ‘NOMDEFEUILLE’!A:F)
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/15wAIwAmv8ddyn1vY6Z2RpC-WuzX-aIQKNjASbu5JcIQ/edit?usp=sharing";"CLIENTS_RAISON_SOCIALE")

Vous pouvez utiliser l’URL de mon fichier pour l’exercice, pensez à adapter l’URL de votre fichier ensuite.

Recopiez la formule dans les cellules suivantes en changeant le nom de la plage nommée désirée. Je vais prendre uniquement la civilité, le nom de famille du client et l’adresse postale. Nous en aurons besoin pour éditer le devis.

Importation de plages nommées contenant une seule colonne chacune.
Importation d’une plage nommée contenant plusieurs colonnes.

Je vous conseille de protéger et masquer la feuille, elle ne sert que de sources de données pour alimenter les autres tableaux.

Vous possédez maintenant dans le fichier GESTION COMMERCIALE une feuille contenant les informations de base des clients, synchronisée avec la base CLIENTS.

Toute modification apportée sur la base est automatiquement reportée dans l’import. Cette synchronisation des données permet d’ajouter ou modifier une information client à un seul endroit !

Par contre aucune modification n’est possible dans la feuille IMPORT CLIENTS. Essayez, cela entraînera une erreur.

Associez la table DEVIS et la table IMPORT CLIENTS

Dans la feuille DEVIS, ajoutez la validation des données en utilisant la colonne « raison sociale » de la feuille CLIENTS.

Vous pouvez à présent choisir le client correspondant à chaque devis.

Créez la table LIGNES DE DEVIS

Cette nouvelle feuille, dans le fichier GESTION COMMERCIALE va permettre de lister toutes les lignes du devis. Le principe est d’ « appeler » le numéro du devis en utilisant une liste déroulante, et de renseigner les informations propres à chaque ligne.

Indiquez pour chaque ligne l’intitulé de la tâche, le prix unitaire, la quantité et le total de la ligne par un arrayformula simple :

=arrayformula(C2:C*D2:D)
Exemple de lignes de devis concernant le devis 2020-001

Créez le générateur de devis

Enfin, sur une dernière feuille, créez un devis. Cette feuille est destinée à être imprimée et communiquée au client. Soignez la présentation !

! Article à venir sur le paramétrage d’une feuille de calcul pour être imprimée !

La seule cellule modifiable est le numéro du devis. Toutes les autres informations sont soit fixes, soit en lien avec le numéro du devis.

Sélection du numéro du devis : créez une liste de choix à l’aide de la validation des données en allant chercher les numéros de devis.

Ce n’est pas obligatoire, mais cela permet de retrouver plus facilement le N° de devis et d’éviter les erreurs de saisie.


Affichage du titre du devis :

=RECHERCHEV(B2;DEVIS!A:C;3;FAUX)

Affichage du nom du client :

 =RECHERCHEV(B2;DEVIS!A:D;4;FAUX)

Affichage de l’adresse postale du client :

Il faut chercher les informations dans IMPORT CLIENT en lien avec le nom du client

 =RECHERCHEV(C3;'IMPORT CLIENTS'!A:D;4;FAUX) 

Code postal et ville concaténés avec « & » :

=RECHERCHEV(C3;'IMPORT CLIENTS'!A:E;5;FAUX)&" "&RECHERCHEV(C3;'IMPORT CLIENTS'!A:F;6;FAUX)

Affichage des lignes du devis :

vous pouvez utiliser FILTER :

=FILTER('LIGNES DEVIS'!B:E;'LIGNES DEVIS'!A:A=B2)

ou QUERY :

 =QUERY('LIGNES DEVIS'!A:E;"SELECT B,C,D,E WHERE A = '"&B2&"' ") 

pour afficher les lignes correspondant au devis affiché dans la cellule B2.

Avec Query, nous récupérons automatiquement les titres des colonnes de LIGNES DEVIS.

Affichage du total HT, taxes et total TTC :

Il faut laisser un certain nombre de lignes en prévision d’un gros devis. Pour améliorer l’affichage, il suffira de regrouper les lignes vides.

En bas du tableau, ajoutez une sommes de totaux des lignes :

=SOMME(E11;E34)

La TVA et le montant de la TVA

=E36*D35 

La somme totale :

 =D35+E37 

Exemples de devis édités :

Notez que vous pouvez mélanger les lignes, en ajouter ou en retirer dans la feuille LIGNES DEVIS. Exemple avec trois devis détaillés :

Résultats des devis dans la feuille GENERATEUR DEVIS

Devis 2020-001

Devis 2020-002

Devis 2020-003

Téléchargez le devis au format PDF

Il ne vous reste « plus qu’à » télécharger la feuille au format PDF en paramétrant correctement l’impression du fichier (article à venir !!)

Stockez votre devis et envoyez-le à votre client.

Bien sûr, il existe des techniques pour automatiser l’envoi et assurer le suivi commercial du devis, gérer les différentes versions, relancer le client… mais c’est une autre histoire 🙂 !

N’hésitez pas à nous contacter pour être accompagnés dans votre gestion des devis au sein de votre entreprise, ou pour d’autres demandes.


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.