Exercice Excel débutant sur la facturation client

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


Sujets que vous pourriez aussi aimer :


Exercice Excel sur la facturation client

Nous abordons ici le quatrième exercice Excel, niveau débutant. Dans les précédentes mises en pratique, nous avons appris les techniques professionnelles permettant de bâtir des calculs dynamiques. Nous souhaitons mettre en oeuvre ces acquis pour dresser les factures des clients.



La capture ci-dessus illustre le tableau à partir duquel nous allons devoir résoudre le cas.

Source et présentation de la problématique
Nous proposons donc de récupérer le classeur Excel de ce tableau pour focaliser notre attention sur l'élaboration des formules.

• Télécharger le classeur facturation-client.xlsx en cliquant sur son lien,
• Double cliquer sur le nom du fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau jaune de sécurité,

Ce classeur est constitué d'une seule feuille comme en témoigne son unique onglet, en bas à gauche de l'interface Excel.



Des mises en forme spéciales, avec fusion de cellules notamment, sont proposées en entête de feuille pour la présentation de la facture.

Les données à considérer sont situées dans le tableau qui débute à partir de la ligne 10.



En colonne B et à partir de la ligne 11, sont énumérées les références des articles achetés par le client en cours. La colonne C propose les désignations correspondantes. En colonne D, sont énumérées les quantités achetées par le client, pour ces produits respectifs.



La colonne E recense les prix de vente de ces articles. En colonne F, une remise en pourcentage est accordée. Elle varie selon le produit. Il va donc s'agir de l'intégrer dans les calculs. Et justement, la colonne G propose de livrer le premier résultat dynamique. Il s'agit de calculer le prix unitaire remisé par article. Dès lors, nous devrons en déduire pour chacun, le total hors taxes, en considérant les quantités respectivement achetées.

Et la somme de ces totaux hors taxes par article, doit conduire au montant total hors taxe à facturer au client. Ce résultat doit apparaître en cellule H18. Après application du taux de TVA, nous pourrons produire la facture finalisée avec le montant TTC de la commande.



Calculer des montants remisés
Le premier calcul en colonne G doit fournir le prix unitaire remisé pour chacun des articles. Ces opérations doivent être dynamiques, comme nous l'avons appris. Elles doivent donc être bâties sur les références des cellules. Ainsi, si les prix ou remises évoluent, les calculs se mettent automatiquement à jour.



Dans l'exemple du premier article en ligne 11, le prix sans remise en E11 est affiché à 795 Euros. En F11, il est indiqué qu'une remise de 3% doit lui être déduite. Le calcul consiste donc à soustraire le montant de cette remise au montant d'origine, soit : E11 - E11*F11.

• Cliquer sur le premier prix unitaire remisé à calculer, soit sur la cellule G11,
• Taper le symbole égal (=) pour débuter la formule,
• Sélectionner le premier prix unitaire sans remise, soit la cellule E11,

La référence de la cellule apparaît ainsi dans la syntaxe de la formule.

• Taper le symbole moins (-) du pavé numérique pour enclencher la soustraction,
• Cliquer de nouveau sur le même prix unitaire sans remise, soit E11,
• Taper le symbole de l'étoile (*) du pavé numérique pour enclencher la multiplication,
• Cliquer alors sur le premier montant de la remise, soit la cellule F11,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Cette combinaison de touches permet de valider le calcul en conservant la cellule active. Nous souhaitons en effet l'exploiter dans la foulée.



Le premier résultat tombe. Il s'agit de le répliquer sur les autres cellules de la colonne. C'est fondamental, on ne refait jamais deux fois le même calcul dans Excel. En bas à droite de la cellule du résultat, soit en G11, vous notez la présence d'un petit carré. Ce carré est la poignée de la cellule. Si vous pointez dessus avec la souris, son curseur se transforme en une petite croix noire. Il est possible de tirer cette poignée pour répliquer le calcul sur les lignes du dessous. Mais dans l'exercice précédent, nous avons appris une méthode encore plus efficace.

• Placer le curseur de la souris au-dessus de ce carré pour qu'il se transforme en une croix,
• Puis, double cliquer avec le bouton gauche,

Excel détecte automatiquement les bornes du tableau. De fait, il réplique la logique du calcul de la remise sur l'ensemble des cellules concernées.

• Cliquer sur la dernière d'entre elles, soit G16 pour la sélectionner,

Le calcul que nous avons bâti à l'origine implique les cellules de la ligne 11 : =E11-E11*F11. En double cliquant sur la poignée du résultat, nous avons intimé l'ordre de répliquer sa logique sur les lignes du dessous. Ce déplacement a imposé aux références des cellules impliquées de s'adapter. Ainsi, 5 lignes plus bas, ce sont les cellules de la ligne 16 qui sont intégrées dans la formule : =E16-E16*F16. De fait, nous obtenons bien le prix unitaire remisé pour l'article concerné. Par ce déplacement, on dit que les références sont relatives. Cette méthode du double clic sur la poignée est donc particulièrement intéressante.

Nous aurions pu présenter ce calcul de façon plus propre ou plus conventionnelle. Accorder une remise de 3% revient à payer 97% du prix, c'est-à-dire (100% - 3%) ce qui équivaut à (1-0,03). En langage dynamique, avec les références des cellules, le pourcentage total payé est donc de (1-F11). Il doit être appliqué sur le prix de l'article. Le calcul est donc le suivant : E11*(1-F11). Les parenthèses sont nécessaires car la multiplication est prioritaire sur la soustraction. Or, c'est bien cette différence calculée en amont qui doit être appliquée sur le prix d'origine.

• Sélectionner tous les résultats précédents, soit la plage de cellules G11:G16,
• Enfoncer la touche Suppr du clavier pour effacer tous les résultats de calculs,
• Cliquer alors sur la cellule G11 pour la sélectionner indépendamment,
• Taper le symbole égal (=) pour initier la formule,
• Cliquer sur le prix unitaire sans remise, soit la cellule E11 pour l'inclure dans la syntaxe,
• Taper le symbole de l'étoile (*) pour enclencher la multiplication,
• Ouvrir la parenthèse pour opérer la factorisation,
• Taper le chiffre 1 suivi du symbole moins (-),
• Cliquer alors sur le montant de la remise à appliquer, soit la cellule F11,
• Fermer la parenthèse du calcul et valider par le raccourci CTRL + Entrée,
• Puis, double cliquer sur la poignée de la cellule du résultat,

Comme vous le constatez, les résultats sont les mêmes. Cela prouve que les deux formules sont identiques et que dans les deux cas, les références des cellules se sont adaptées au déplacement du calcul. Mais ici, la syntaxe est plus sobre. Elle est factorisée.

Totaux selon quantités et remises
Nous devons exploiter les résultats dynamiques précédents pour calculer les totaux hors taxes en colonne H. Bien entendu, pour les besoins de la facturation, ces totaux doivent considérer les remises mais aussi les quantités achetées. Le calcul est trivial. Il consiste à multiplier le prix unitaire remisé par la quantité. Mais une fois encore, tout comme pour les exercices précédents, nous proposons de changer de méthode. Il s'agit de nous familiariser avec la technique consistant à présélectionner toutes les cellules des résultats à trouver, pour les répliquer encore plus simplement. Bien entendu, la méthode précédente faisant intervenir la poignée serait parfaitement fonctionnelle.

• Sélectionner tous les totaux à calculer, soit la plage de cellules H11:H16,

Comme nous l'avions déjà remarqué lors des mises en pratique précédentes, dans une plage présélectionnée, la première cellule n'est pas grisée. Excel indique qu'elle est active par défaut. Nous allons donc pouvoir construire le calcul comme s'il lui était dédié. Puis, la réplication reproduira la logique sur toutes les cellules de la plage ainsi présélectionnée.

• Taper le symbole égal (=) pour débuter le calcul,

Il s'inscrit en effet dans la première cellule de la plage sélectionnée, soit H11.

• Cliquer sur le prix unitaire remisé de la même ligne, soit la cellule G11,
• Taper le symbole de l'étoile (*) du pavé numérique pour enclencher la multiplication,
• Cliquer alors sur la quantité de la même ligne, soit la cellule D11,
• Puis, valider nécessairement la formule par le raccourci clavier CTRL + Entrée,

Sans avoir besoin d'utiliser la poignée, grâce à la présélection et à la validation par le raccourci clavier, le calcul est répliqué sur l'ensemble des cellules.

La formule que nous avons bâtie est donc la suivante : =G11*D11.

• Cliquer sur le dernier total hors taxes, soit la cellule H16 pour la sélectionner,

Le constat est le même que précédemment. Les références des cellules impliquées dans la formule ont suivi le déplacement du calcul, bien que la méthode soit différente. Le total HT calculé en ligne 16 correspond bien au résultat de l'article concerné sur cette même ligne.

Nous avons pris soin de bâtir tous nos calculs sur des variables, soit à partir des références des cellules. De fait, ils sont tous dynamiques et pour preuve :

• Sélectionner le PUHT sans remise du premier article, soit la cellule E11,
• Saisir directement le prix 815 à la place de l'ancien montant de 795,
• Puis, valider la modification avec la touche Entrée du clavier,

Comme cette cellule est impliquée dans le calcul du PUHT avec remise et celui du Total HT, les deux cellules G11 et H11 se sont automatiquement actualisées pour intégrer le nouveau prix de vente. Si nous avions terminé cette facturation, le montant global de la facture du client, aurait lui aussi été affecté.

Total de la facture client
En H18, nous devons additionner tous les totaux HT des produits achetés. Les cellules sont nombreuses. Il est préférable d'exploiter la somme automatique plutôt que de poser manuellement l'opération. De ce résultat, nous déduirons le montant de la TVA et donc le total TTC à facturer.

• Sélectionner la cellule H18 du total hors taxes à calculer,
• Tout à fait à droite du ruban Accueil, cliquer sur le bouton Somme automatique,

Ce bouton est matérialisé par une lettre M orientée à 90 degrés, comme l'illustre la capture ci-dessus. Il s'agit en fait d'un Sigma grecque. Son info-bulle indique qu'il est possible d'enclencher la somme automatique par le raccourci clavier Alt + =.

Quoiqu'il en soit, la syntaxe de la formule s'inscrit automatiquement dans la cellule où elle est appelée, soit H18 : =SOMME(H11:H17). Comme il s'agit d'une fonction automatique, Excel place le symbole égal pour nous. Puis il appelle la fonction Somme. Toute fonction requiert les cellules à calculer entre ses parenthèses. Il s'agit de la plage H11:H17. Cette suggestion intuitive n'est pas tout à fait correcte même si elle conduirait au résultat. Excel propose toutes les cellules numériques de la colonne en descendant jusqu'à la case adjacente : H17. Mais cette dernière est en dehors du tableau et elle est vide. Il est plus correct de ne pas l'inclure. C'est pourquoi la fonction Somme automatique est en attente de correction de la part de l'utilisateur.

• Resélectionner précisément la plage de cellules H11:H16,
• Puis valider la fonction par la touche Entrée par exemple,

Nous obtenons bien le montant total hors taxes dynamique de la facture du client.

Pour terminer la facturation, il reste deux opérations à accomplir. Elles sont très simples. La première consiste à calculer le montant de la TVA. Il s'agit d'une multiplication. Mais nous pourrions réaliser le calcul final directement, pour conduire au montant TTC. Le raisonnement est le même que pour le calcul du prix unitaire avec remise, à une différence près. Il ne s'agit plus d'une soustraction mais d'une addition. Cette TVA est à compter en plus. Donc le calcul consiste à ajouter au total HT (H18), le montant de la TVA sur ce total (G19*H18), ce qui donne : H18 + H18*G19. Mais nous pourrions aussi le factoriser : =H18*(1+G19). Cependant, dans cet exercice, il est proposé de décomposer le calcul.

• Cliquer sur la cellule H19 pour la sélectionner,
• Taper le symbole égal (=) pour initier la formule,
• Cliquer sur le total hors taxes pour intégrer sa cellule H18 dans la syntaxe du calcul,
• Taper le symbole de l'étoile (*) du pavé numérique pour enclencher la multiplication,
• Cliquer sur la TVA pour intégrer sa cellule G19 dans la syntaxe de la formule,
• Puis, valider le calcul à l'aide de la touche Entrée par exemple,

Nous obtenons un résultat cohérent qui représente bien les 20% du montant total. Dès lors, nous pouvons en déduire le montant TTC à facturer au client. Il consiste en une simple addition du total HT et du montant de la TVA précédemment calculé. Ce calcul peut être posé manuellement plutôt que de faire intervenir la fonction Somme automatique d'Excel.

• Sélectionner la cellule du total TTC à calculer, soit H20,
• Taper le symbole égal (=) pour débuter l'opération,
• Sélectionner le total hors taxes de la facture pour intégrer sa cellule H18 dans la formule,
• Taper le symbole plus (+) du pavé numérique pour enclencher l'addition,
• Cliquer sur le montant de la TVA pour intégrer sa cellule H19 dans le calcul,
• Valider l'opération à l'aide de la touche Entrée par exemple.

Nous venons de finaliser les calculs dynamiques pour cette facturation client. Le total TTC obtenu apparaît tout à fait cohérent dans la mesure où il est supérieur de 20% au montant hors taxes de la facture. Souvenez-vous, si l'une des données change, comme une quantité, un PUHT ou une remise, tous les calculs liés se mettent à jour en cascade, jusqu'au montant final TTC.

Formater les données numériques
Nous devons peaufiner la présentation pour afficher les valeurs monétaires au format Euro. Nous le répétons dans chaque exercice, un format doit être appliqué. La devise ne doit jamais être saisie dans la cellule. La colonne des quantités ne doit fort logiquement pas être altérée. Il en va de même pour celle des remises. En revanche, les totaux calculés entre les lignes 18 et 20 sont concernés. Nous proposons de les intégrer dans la même sélection pour optimiser les opérations.

• Sélectionner la plage de cellules E11:E16,
• En maintenant la touche CTRL enfoncée, sélectionner la plage de cellules G11:H16,
• Toujours avec la touche CTRL enfoncée, sélectionner la plage de cellules H18:H20,

Nous l'avons déjà évoqué et pratiqué, la touche CTRL permet d'additionner les sélections pour les regrouper. La cellule H18 de la deuxième plage n'apparaît pas grisée. Mais ne vous y trompez pas, elle est bien sélectionnée. Comme précédemment, Excel indique qu'elle est active par défaut.

• Dans la section Nombre du ruban Accueil, cliquer sur le bouton Format Nombre Comptabilité,

Ce bouton applique donc le format monétaire aux cellules sélectionnées. Son raccourci clavier est CTRL + M, M pour Money en américain. La présentation proposée est plus intuitive.