Facturation automatisée de clients avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Réaliser une facturation avec Excel

Le cas pratique que nous proposons de réaliser ici permet de mettre en application de nombreuses notions importantes d'Excel traitées par les différents supports de formation. Ainsi nous exploiterons la fonction conditionnelle Si, la recherche d'information RechercheV ainsi que les calculs de base comme la somme et même les listes déroulantes. Sans plus attendre, entrons dans le vif du sujet :

• Télécharger le classeur facturation-excel-tva-ht-ttc.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel,

Il s'agit d'un tableau conçu pour réaliser la facturation d'un commerce. Dans la colonne Code article, doit être saisie la référence de l'article acheté. Dans la colonne Désignation doit apparaître le libellé de l'article correspondant. Dans un premier temps nous le saisirons nous-même, puis nous verrons que nous pouvons le récupérer automatiquement selon le code article. Dans la colonne Quantité, doit être saisi le nombre d'articles achetés pour cette référence. Dans la colonne P.U – H.T doit figurer le prix unitaire hors taxes de l'article acheté. La dernière colonne, Montant en H.T est un calcul multipliant le prix unitaire par la quantité. Tout en bas du tableau doivent apparaître la somme HT de la commande ainsi que le montant total TTC calculé sur la base de la TVA en pourcentage. Tous les articles du commerce sont référencés dans un tableau sur la seconde feuille articles. Ce tableau est volontairement simplifié.


Le montant Hors Taxes
Avant de calculer le montant Hors Taxes, nous avons besoin de saisir quelques lignes pour simuler la facturation. En vous basant sur les références de la feuille articles :

• Réaliser les saisies comme l'illustre la capture ci-dessous,
• Puis, sélectionner toutes les cellules de la colonne Montant en H.T,

Vous remarquez que la première cellule de la plage sélectionnée est la cellule active. Nous allons poser le calcul de la multiplication sur l'ensemble de ces cellules en une fois.

• Taper le symbole = pour débuter le calcul,
• Cliquer sur la cellule de la quantité de la même ligne soit E6,
• Taper le symbole * du pavé numérique pour réaliser une multiplication,
• Cliquer sur la cellule du prix unitaire Hors Taxes, soit F6,
• Valider ce calcul avec le raccourci clavier CTRL + Entrée,

Le raccourci CTRL + Entrée permet de répercuter la logique du calcul sur l'ensemble des cellules présélectionnées. Ainsi, tous les calculs de la colonne Montant en H.T sont prêts. Si vous ajoutez des références avec quantités et prix unitaires Hors Taxes, le calcul se met à jour. La formation sur les raccourcis clavier Excel vous enseigne les astuces pour gagner du temps.


Sur l'exemple de la figure ci-dessus, nous avons ajouté l'étagère de rangement avec son prix unitaire et la quantité achetée. Le montant HT s'est mis à jour tout seul grâce au calcul précédemment réalisé.

La somme des montants Hors Taxes
En bas du tableau, en cellule G32, nous allons poser le calcul de la somme des montants Hors Taxes.

• Sélectionner la cellule G32,
• Réaliser le raccourci clavier ALT + Entrée,

Le calcul de la somme se déclenche avec l'apparition de la fonction somme(). Cependant la plage de cellule proposée par Excel n'est pas tout à fait correcte en raison des cellules vides intercalées dans le modèle de la facture. Donc, avant de valider ce calcul :

• Redéfinir la plage à sommer en sélectionnant les cellules G6 à G26,
• Puis valider le calcul en enfonçant la touche Entrée du clavier.

Vous obtenez le total HT en bas de colonne. Là encore le calcul est posé. Cela signifie que si vous ajoutez une nouvelle ligne d'achat, le total HT, prendra en compte le nouveau montant et se mettra à jour. La formation sur les calculs simples avec Excel vous enseigne les automatismes pour réaliser facilement bon nombre d'opérations.

Le total TTC de la facture
Le montant TTC est un calcul qui fait référence au total HT et au taux de TVA. On ajoute au montant HT, la part de pourcentage que coûte la TVA sur ce même montant HT. Le calcul est donc le suivant : montant_HT + montant_HT*TVA. Si nous factorisons, ce qui est plus propre, ce calcul équivaut à : montant_HT*(1+TVA). Ce calcul ramené aux cellules de la feuille de facturation donne : =G32*(1+F33).

• Sélectionner la cellule G35 pour réaliser le calcul du TTC,
• Taper le symbole = pour débuter le calcul,
• Cliquer sur la cellule G32 du total Hors Taxes pour la désigner,
• Taper le symbole * du pavé numérique pour la multiplication,
• Ouvrir une parenthèse pour factoriser le calcul,
• Taper 1 +,
• Puis cliquer sur la cellule F33 de la TVA pour la désigner,
• Fermer la parenthèse et valider le calcul par CTRL + Entrée,

Vous obtenez le montant total de la facturation, toutes taxes comprises. Là encore le calcul étant posé, si vous ajoutez un article avec sa quantité et son prix unitaire, tout se met à jour instantanément : Le montant HT de l'article, le total HT et le total TTC de la facture. La feuille de facturation est tout à fait fonctionnelle. Cependant, si nous sommes puristes, à usage professionnel, nous devons ajouter quelques options fort utiles.

Récupération d'informations – RechercheV
Tout d'abord, la seule information qui doit réellement être saisie dans cette feuille est le code article. Tout le reste, sauf la quantité, doit se mettre à jour automatiquement : Désignation, Prix unitaire, Montant HT, Total HT, Total TTC. C'est le principe du scan de l'article dans les supermarchés. La facturation devient ainsi beaucoup plus sécurisée et vous gagnez un temps considérable. La fonction qui permet de récupérer les informations correspondant au code article est la fonction RechercheV(), selon les informations détenues dans la feuille articles. Il y a une formation sur l'extraction d'informations avec la rechercheV sur le site pour apprendre à exploiter cette puissante fonction Excel. Tout d'abord, nous allons faire en sorte que le code article de la première colonne puisse se choisir à l'aide d'une liste déroulante, pour simuler le lecteur code à barres.

• Sélectionner toutes les cellules des codes articles soit C6:C26,
• Activer le ruban Données,
• Dans le ruban, cliquer sur le bouton Validation de données,
• Dans la boîte de dialogue qui suit, choisir Liste dans la zone Autoriser,
• Pour définir cette liste, cliquer dans un premier temps dans la zone Source,
• Puis cliquer en bas du classeur sur l'onglet de la feuille articles,
• Sélectionner toutes les références des articles de cette feuille soit C7:C15,
• Cliquer sur Ok pour valider la création de la liste déroulante.

Le support de formation sur les listes déroulantes vous enseigne plus d'information à ce sujet, notamment comment lier dynamiquement des listes déroulantes entre elles. Désormais, lorsque vous sélectionnez l'une des cellules de la colonne des codes articles, vous remarquez la présence d'une liste déroulante. Elle vous permet de choisir parmi les références des produits. Seulement pour l'instant, lorsque vous validez un choix, forcément les désignations et prix unitaires ne sont pas rapatriés. Nous n'avons pas encore utilisé la fonction RechercheV(). Nous devons exploiter deux fois cette fonction RechercheV. Une première fois dans la colonne Désignation afin de récupérer automatiquement la désignation du produit correspondant au code article. Une seconde fois, dans la colonne P.U - H.T afin de récupérer automatiquement le prix unitaire hors taxes du produit choisi par la liste déroulante. La fonction RechercheV requiert quatre paramètres. Trois sont réellement obligatoires. Tout d'abord la valeur qui est recherchée dans le tableau pour trouver le reste, il s'agit du code article. Ensuite, le tableau dans lequel la recherche doit être effectuée, soit le tableau de la feuille articles. En troisième paramètre intervient le numéro de colonne du tableau dans lequel se trouve l'information correspondante à récupérer, par exemple, la désignation se situe dans la deuxième colonne de ce tableau. Et enfin un paramètre booléen que nous fixerons à Faux afin que la fonction RechercheV ne tente pas de se rapprocher du résultat si elle ne trouve pas le code article demandé dans le tableau de recherche. =recherchev(code_article,tableau_articles,2,Faux).

• Sélectionner toutes les cellules de la colonne Désignation soit D6:D26,
• Taper le symbole = pour débuter la formule,
• Saisir le nom de la fonction recherchev et ouvrir la parenthèse, soit =recherchev(,
• Cliquer sur le premier code article à rechercher, soit la cellule C6 et taper un point-virgule (;),
• Cliquer sur l'onglet de la feuille articles et sélectionner tout le tableau, soit C6:E16,
• Dans la foulée, enfoncer la touche F4 du clavier,

Des dollars viennent alors encadrer les références de la plage de cellules. On parle de références absolues pour qu'Excel désigne toujours les mêmes bornes du tableau bien qu'il réplique le calcul sur l'ensemble de la colonne Désignation, chaque fois une ligne plus bas. Cette notion est importante et puissante dans Excel. Le support sur les références absolues dans Excel vous permet de parfaitement comprendre cette notion.

• Taper de nouveau un point-virgule (;),
• Saisir 2 qui est le numéro relatif de colonne où se trouve la désignation dans le tableau sélectionné,
• Taper de nouveau un point-virgule (;) et saisir Faux,
• Fermer la parenthèse et valider le calcul par CTRL + Entrée, pour le répercuter sur toutes la plage présélectionnée,

Le résultat à ce stade est presque parfait mais il y a encore un souci. Tout d'abord, vous remarquez que réapparaissent les désignations correspondant aux codes articles saisis. Si dans la colonne Code article, vous ajoutez une nouvelle référence à l'aide de la liste déroulante, sa désignation s'inscrit automatiquement dans la colonne Désignation. En revanche, lorsqu'aucun code article n'est présent, un message d'erreur apparaît dans la colonne Désignation. #N/A pour Not Availiable, soit non disponible. En effet, nous demandons à Excel de réaliser la recherche coûte que coûte, même s'il n'y a rien à rechercher. Donc pour palier le problème, il faut ajuster la formule pour lui indiquer que s'il n'y a rien à rechercher, la fonction RechercheV() ne doit pas être enclenchée. C'est la fonction Si() d'Excel qui permet de réaliser ce test et d'imbriquer la fonction RechercheV(). Certes, cela devient plus complexe mais le résultat est tout à fait remarquable. La formation pour apprendre à poser des raisonnements dans Excel enseigne toutes les subtilités de la fonction Si().

• Sélectionner de nouveau toute la plage de cellules des désignations, soit D6:D26,
• Enfoncer la touche F2 du clavier pour passer en mode saisie de la cellule,
• Modifier la formule comme suit : =SI(C6='';'';RECHERCHEV(C6;articles!$C$6:$E$15;2;FAUX)),
• Valider par CTRL + Entrée pour répercuter le calcul sur la colonne.

Les messages d'erreur #N/A disparaissent et la récupération de la désignation au choix d'un code article dans la liste déroulante fonctionne parfaitement. =SI(C6='';''; : le premier paramètre est le critère. Si la cellule C6 est vide soit si aucun code article n'est présent. Alors (;) nous n'inscrivons rien dans la cellule (''), sinon (;), nous lançons la recherchev car le sinon sous-entend qu'il y a bien un code article à rechercher. Du coup, il est très simple d'adapter cette formule dans la colonne des prix unitaires. En effet, la seule chose à modifier est le numéro de colonne de retour, soit le 2 qui devient 3.

• Adapter la formule précédente dans la colonne P.U - H.T,

Les prix unitaires sont bien rapatriés. Désormais, si vous ajoutez une référence avec la liste déroulante, la désignation et le prix unitaire s'inscrivent automatiquement. Le Montant HT quant à lui se calcule instantanément si la quantité et bien saisie, ce qui explique pourquoi sur la capture du dessus, les deux derniers montants HT ne sont pas encore calculés. Du fait de nos calculs, un problème survient dans la colonne Montant en H.T. Le message d'erreur indique que le calcul ne peut pas être réalisé sur des cellules vides. Nous allons encore une fois palier le problème à l'aide de la fonction SI() et exactement de la même manière. Si la cellule du prix unitaire est vide, le calcul ne doit pas être fait, sinon il doit être enclenché : =SI(F6='';'';E6*F6).

• Adapter le calcul de la colonne Montant en H.T de façon à ce qu'il n'y ait plus d'erreur,

Les erreurs disparaissent. Cependant si vous ajoutez une nouvelle référence à l'aide de la liste déroulante avec une quantité, toutes les données de la facture se calculent et se mettent à jour. Nous obtenons donc un modèle de facturation tout à fait fonctionnel et professionnel.

Protéger les cellules contenant des formules
Pour parfaire le modèle, nous devons faire en sorte de protéger les cellules sur lesquelles sont bâtis les calculs. Il faut éviter que par mégarde, l'utilisateur écrive dessus au risque de perdre les formules. Nous devons empêcher la saisie sur ces dernières. Le raisonnement est en fait inverse. En effet, par défaut, toutes les cellules d'une feuille Excel sont protégées. Mais cet état devient actif lorsque vous protégez le classeur lui-même. Donc avant de protéger le classeur, pour que toutes les cellules ne soient pas interdites à la saisie, nous allons indiquer celles dans lesquelles l'utilisateur pourra continuer de saisir les données. Puis nous protègerons le classeur. Les cellules dans lesquelles l'utilisateur pourra continuer d'intervenir sont les cellules des colonnes Code article et Quantité.

• Sélectionner les cellules de la première colonne soit C6:C26,
• Tout en maintenant la touche CTRL enfoncée, sélectionner les cellules de la deuxième colonne, soit E6:E26,

Le support de formation sur les raccourcis clavier Excel vous enseigne notamment ces techniques de sélection à l'aide des touches du clavier.

• Dans le ruban Accueil, cliquer sur la flèche du bouton Format,
• Tout en bas de la liste, choisir Format de cellule,
• Dans la boîte de dialogue qui suit, activer l'onglet Protection,
• Décocher la case Verrouillée et cliquer sur Ok pour valider,
• Réaliser le même processus pour la cellule de la TVA,

La TVA peut en effet varier, il est donc utile de la laisser libre. Nous venons de déverrouiller les cellules dans lesquels l'utilisateur pourra continuer d'intervenir. Maintenant, nous devons protéger la feuille pour que la protection globale soit effective. Avant cela, nous allons épurer la facture.

• Supprimer les données saisies dans les colonnes Codes article et Quantité,
• Activer le ruban Révision,
• Cliquer sur le bouton Protéger la feuille,
• Valider directement la boîte de dialogue en cliquant sur Ok.

Toutes les cellules portant des calculs sont désormais protégées. Plus de risque de les endommager. Dans cette boîte de dialogue, vous remarquez que vous pouvez ajouter un mot de passe afin de renforcer la protection. De même, vous pouvez cocher d'autres options afin de protéger aussi les formats de cellules par exemple. Ainsi, la mise en forme, les couleurs, les bordures ne peuvent plus être modifiées. A ce stade, si vous choisissez un code article avec la liste déroulante, il s'inscrit dans la cellule et les désignations et prix unitaires sont bien récupérés. Si vous tapez une quantité, la saisie est autorisée et tous les calculs se font. Par contre, si vous tentez de saisir dans toute autre cellule, un message Excel apparaît, vous informant que vous n'y êtes pas autorisé. En tant que concepteur, si vous souhaitez modifier certaines données de la facturation, dans le ruban Révision, vous devez d'abord cliquer sur le bouton Ôter la protection de la feuille.


La facturation ainsi conçue est de plus en plus professionnelle et presque prête à l'emploi.

Le modèle de facturation
Pour que la facture que nous venons de créer soit totalement fonctionnelle nous devons en créer un modèle. Un modèle Excel, lorsque vous l'appelez, propose une copie du modèle original. Cela signifie d'une part que vous ne risquez pas d'endommager la source. Vous récupérez bien toutes les fonctionnalités de la facturation source mais dans un classeur détaché du modèle. Vous ne partez plus d'une ancienne facturation réalisée pour un autre client. Ainsi toute facture passée peut être archivée sans risque d'être endommagée. Pour créer le modèle, les données saisies étant supprimées :

• Cliquer sur Fichier en haut à gauche puis sur Enregistrer sous et sur Parcourir,
• Dans la boîte de dialogue Enregistrer sous, choisir Modèle Excel dans la zone Type,
• Nommer le fichier et cliquer sur le bouton Enregistrer.

Au moment où vous choisissez le type Modèle Excel, vous remarquez en haut de la boîte de dialogue qu'Excel change l'emplacement d'enregistrement. En effet, un modèle pour qu'il puisse être exploité doit être enregistré dans les dossiers modèles d'Excel. Vous devez donc accepter cet emplacement. Maintenant que le modèle est créé, il doit être appelé à chaque nouvelle facture à concevoir.

• Fermer le classeur en cours par CTRL + W par exemple,
• Cliquer ensuite de nouveau sur Fichier en haut à gauche,
• Dans la liste choisir Nouveau,

Cette vue suggère par défaut tous les modèles Excel selon des thématiques précises dans une rubrique nommée Proposés. Vous remarquez la présence d'une nouvelle rubrique Personnel du fait de la création de notre propre modèle de facturation.

• Cliquer sur la rubrique Personnel,

Notre modèle y figure. Si vous en créez d'autres, ils se rangeront ici. Pour les exploiter, il suffit de les sélectionner.

• Cliquer sur le modèle facturation,

Vous récupérez la facture précédemment conçue complètement fonctionnelle. Cependant, si vous regardez la barre de titre, son nom est différent. Cette nouvelle facture est détachée du modèle et ne risque pas de l'endommager par inadvertance.