Julien Crego
Facture : calculs simples dans un tableau TAB04
Cet exercice consiste à construire une facture sur un logiciel tableur, il s’agit d’utiliser les calculs classiques de TVA avec ou sans références absolues puis de mettre en forme la facture.
Consignes de l’exercice
Téléchargez les fichiers nécessaires pour l’exercice ci-dessous :
Créez un tableau à partir de la plage de cellules A12:G18 (les articles de la factures) et nommez-le Facture.
Solution
- Sélectionnez les données.
- Cliquez sur Tableau dans l’onglet Insertion.
- Ajustez les options du tableau si nécessaire (plages de cellules et tableau avec en-tête).
- Personnalisez le nom du tableau dans le champ correspondant dans l’onglet Création de tableau.
Modifiez l’affichage du contenu des cellules de la colonnes TVA % du tableau Facture pour les afficher en pourcentage sous la forme 20,0%.
Solution
- Sélectionnez les cellules de la colonne (sans l’en-tête).
- Faites un clic droit sur la sélection et choisissez l’option Format de cellule.
- Sélectionnez la catégorie Pourcentage.
- Définissez le nombre de décimales que vous souhaitez afficher : 1.
Références dans un tableau
Sur Excel, la notation des références dans un tableau peut s’afficher sous une forme spéciale faisant référence au libellé de la colonne pour la ligne courante.
Par exemple =[@Quantités] dans la cellule F13 est équivalent à la référence à la cellule B13 c’est à dire la ligne 13 de la colonne Quantités. La même référence dans la cellule F14 correspondra donc à la cellule B14. Cette notation correspond donc à des références relatives qui se mettront donc systématiquement à jour.
Si cette notation vous pose problème, vous pouvez la désactiver dans les préférences d’Excel. Il s’agit pour cela de décocher l’option Utiliser les noms de tableaux dans les formules.
Dans la colonne F, saisissez la formule adaptée permettant de calculer la TVA en fonction du nombre d’articles et du prix unitaire hors taxe.
Solution
La formule à saisir dans la cellule F13 est la suivante :
- Notation classique :
=B13*D13*E13. - Notation tableau :
=[@Quantités]*[@[Prix unitaire HT]]*[@[TVA %]]
Dans la colonne G, saisissez la formule adaptée permettant de calculer le total TTC en fonction du nombre d’articles et du prix unitaire hors taxe et de la TVA.
Solution
La formule à saisir dans la cellule G13 est la suivante :
- Notation classique :
=B13*D13+F13. - Notation tableau :
=[@Quantités]*[@[Prix unitaire HT]]+[@TVA]
Modifiez l’affichage du contenu des cellules des trois colonnes Prix unitaire HT, TVA et Total TTC du tableau Facture pour les afficher les valeurs sous la forme 10 000,00 €.
Solution
- Sélectionnez les cellules de la colonne (sans l’en-tête).
- Faites un clic droit sur la sélection et choisissez l’option Format de cellule.
- Sélectionnez la catégorie Monétaire.
- Définissez le nombre de décimales que vous souhaitez afficher : 2.
Modifiez le tableau Facture afin de lui ajouter une ligne de total. Pour cette question, il ne s’agit pas de mettre en forme la ligne à la main.
Solution
- Sélectionnez le tableau en sélectionnant une des cellules qui le composent.
- Activez l’onglet Création de tableau.
- Cochez la case Ligne Total dans la section Options de style de tableau.
Question Bonus :
La ligne 18 de notre facture contient des 0 alors qu’aucun article n’a été saisi. Modifiez les formules TVA et Total TTC pour faire en sorte que rien ne s’affiche s’il manque une des valeurs pour la quantité, le prix unitaire ou le taux de TVA.
Solution
Cette question peut être traitée avec une conditionnelle SI(). Cette fonction supporte trois arguments : un test logique, que faire si le résultat est vrai et que faire si il est faux. Pour le test, il sera nécessaire d’avoir trois conditions de réunies, il est donc nécessaire d’utiliser la fonction ET().
Ainsi, nous avons : =SI(ET(condition1 ; condition2 ; condition3) ; Si vrai on calcule ; si faux on affiche rien).
Formule pour la colonne TVA dans la cellule F13 :
- Notation classique :
=SI(ET(B13>0;D13>0;E13>0);B13*D13*E13;"") - Notation tableau :
=SI(ET([@Quantités]>0;[@[Prix unitaire HT]]>0;[@[TVA %]]>0);[@Quantités]*[@[Prix unitaire HT]]*[@[TVA %]];"")
Formule pour la colonne Total TTC dans la cellule G13 :
- Notation classique :
=SI(ET(B13>0;D13>0;F13>0);B13*D13+F13;"") - Notation tableau :
=SI(ET([@Quantités]>0;[@[Prix unitaire HT]]>0;[@[TVA %]]>0);[@Quantités]*[@[Prix unitaire HT]]+[@TVA];"")
Une autre solution moins explicite mais plus lisible consiste à calculer le produits des trois valeurs. Si une des cellule est vide alors sa valeur est égal à 0. Si l’on multiplie n’importe quelle valeur par 0, nous obtenons 0, nous pouvons donc utiliser la formule suivante pour la colonne Total TTC dans la cellule G13 :
- Notation classique :
=SI(B14*D14*E14=0;"";B14*D14*E14)ou=SI(PRODUIT(B14:E14)=0;"";PRODUIT(B14:E14)) - Notation tableau :
=SI(PRODUIT(Facture[@[Quantités]:[TVA %]])=0;"";PRODUIT(Facture[@[Quantités]:[TVA %]]))
Il existe encore d’autres tests possibles pour vérifier l’existence de contenu dans les cellules comme par exemple avec la fonction estvide().
Sauf mention contraire*, l’article Facture : calculs simples dans un tableau TAB04 et son contenu par Julien Crego sont mis à disposition selon les termes de la licence Creative Commons
Attribution – Pas d’Utilisation Commerciale – Partage dans les Mêmes Conditions 4.0 International
Cette licence vous permet de remixer, arranger, et adapter cette œuvre à des fins non commerciales tant que vous créditez la source en citant le nom des auteurs et que les nouvelles œuvres sont diffusées selon les mêmes conditions.
* Cette notice est intégrée automatiquement à la fin de chaque article de ce site.
Pour le bonus , concernant les notation tableaux y’a une erreur de syntaxe on ne peut pas utiliser F13 dans la formule sachant que c’est la cellule a remplir :
les bonnes syntaxes sont :
F13) =SI(ET([@Quantités]>0;[@[Prix unitaire HT]]>0;[@[TVA %]]>0);[@Quantités]*[@[Prix unitaire HT]]*[@[TVA %]]; » »)
G13) =SI(ET([@Quantités]>0;[@[Prix unitaire HT]]>0;[@TVA]>0);[@Quantités]*[@[Prix unitaire HT]]+[@TVA]; » »)
Merci pour votre vigilance, la solution a été corrigée !