Références absolues pour appliquer remise et TVA sur Excel
Un exercice corrigé sur les références absolues d'Excel pour appliquer un taux de remise et de TVA à toute une colonne avec les $, avec le fichier Excel à télécharger.
Tu as une liste de produits avec leur prix hors taxes, et tu veux leur appliquer un même taux de remise puis la TVA. La tentation, c'est de retaper le taux dans chaque formule, mais le jour où il change, tu repasses sur toutes les lignes. Dans cet exercice, on va voir ensemble la vraie solution, qui consiste à ranger chaque taux dans une seule cellule et à la verrouiller avec les $.
L'objectif est de comprendre la différence entre une référence qui glisse quand on recopie et une référence absolue qui reste plantée sur sa cellule. Une fois ce réflexe acquis, tu calcules remise, prix net, TVA et prix TTC d'une colonne entière en recopiant une seule formule, et tu ne crains plus de changer un taux !
Ce que tu vas construire
- Faire pointer toutes les lignes vers un taux rangé dans une seule cellule de paramètre.
- Comprendre la différence entre une référence relative et une référence absolue.
- Verrouiller une cellule avec les $ pour qu'elle ne bouge pas à la recopie.
- Calculer remise, prix net HT, TVA et prix TTC pour toute une colonne.
- Arrondir proprement les montants à deux décimales avec ARRONDI.
- Changer un taux une seule fois et voir tout le tableau se recalculer.
À connaître avant de commencer
- Savoir écrire une formule simple avec une multiplication.
- Savoir recopier une formule le long d'une colonne.
- Savoir passer d'un onglet à l'autre dans un classeur.
Voici les données de départ, réparties sur 2 onglets comme dans le fichier (clique sur un onglet sous le tableau pour changer de feuille). Copie-les ou , puis entraîne-toi avant de regarder le corrigé.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Produit | Prix HT | Remise | Prix net HT | TVA | Prix TTC |
| 2 | Clavier sans fil | 29,90 | ||||
| 3 | Souris ergonomique | 45,00 | ||||
| 4 | Casque audio | 89,90 | ||||
| 5 | Webcam HD | 59,90 | ||||
| 6 | Tapis de bureau | 19,90 | ||||
| 7 | Hub USB-C | 34,90 | ||||
| 8 | Support écran | 74,90 | ||||
| 9 | Lampe de bureau | 39,90 | ||||
| 10 | Disque externe | 99,00 | ||||
| 11 | Câble HDMI | 12,90 |
| A | B | |
|---|---|---|
| 1 | Paramètre | Taux |
| 2 | Taux de remise | 10% |
| 3 | Taux de TVA | 20% |
Résoudre l'exercice étape par étape
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Astuces pour aller plus loin
La touche F4 pose les $ pour toi
Plutôt que de taper les $ à la main, clique sur la référence dans ta formule puis appuie sur F4. Excel ajoute les deux $ d'un coup. Appuie encore pour passer en référence mixte, ligne ou colonne seule verrouillée.
Range chaque taux dans une cellule, jamais en dur
Ne tape jamais 0,2 directement dans tes formules de TVA. Mets le taux dans une cellule dédiée et pointe-la. Le jour où la TVA change, une seule modification suffit et tout le tableau suit.
Repère vite les références qui glissent
Si après recopie tes montants tombent à zéro ou deviennent incohérents dès la deuxième ligne, c'est presque toujours une référence absolue oubliée. Clique sur une formule du bas et regarde si elle pointe encore la cellule du taux.
S'entraîner sur 3 exercices similaires à références absolues pour appliquer remise et TVA
État de rapprochement bancaire automatisé
Pointer ton relevé contre la comptabilité avec RECHERCHEV, puis dresser l'état de rapprochement bancaire en deux colonnes jusqu'à l'égalité des soldes rectifiés.
Voir l'exercice
Détecter les anomalies d'un journal comptable
Passe au crible un extrait de journal comptable en partie double pour repérer les écritures saisies deux fois, les pièces déséquilibrées et les montants hors seuil avec NB.SI.ENS, SOMME.SI et SI.
Voir l'exercice
Budget de trésorerie prévisionnel mois par mois
Construis un budget de trésorerie prévisionnel, des encaissements à 30 et 60 jours à la TVA à décaisser, puis le solde de fin de mois et l'alerte qui repère les mois de tension avant qu'ils arrivent.
Voir l'exercice
Tu as peut-être encore une question
Une référence absolue est une cellule verrouillée avec des $, comme $B$2, qui ne bouge pas quand on recopie la formule. À l'inverse, une référence relative comme A2 se décale automatiquement ligne par ligne. On verrouille les cellules qui doivent rester fixes, comme un taux.
Place le curseur sur la référence dans la formule, puis appuie sur la touche F4. Excel ajoute les deux $ d'un coup, par exemple $B$2. Tu peux aussi taper les dollars à la main devant la lettre de colonne et le numéro de ligne.
Range le taux dans une seule cellule, puis pointe-la en référence absolue dans ta formule, par exemple =B2*Paramètres!$B$2. Recopie la formule vers le bas. Le prix change à chaque ligne mais le taux reste le même partout, et un seul changement met tout à jour.
Le plus souvent, c'est une référence absolue oubliée. En recopiant, la référence du taux glisse vers une autre cellule, souvent vide, et le calcul tombe à zéro ou prend le mauvais taux. Reviens sur la formule, vérifie que le taux est bien écrit avec les $, puis recopie de nouveau.
La formule de la TVA dans Excel multiplie le montant hors taxes par le taux, =D2*Paramètres!$B$3 avec le taux de 20 % rangé dans une cellule verrouillée par les $, et le prix TTC s'obtient en ajoutant cette TVA au hors taxes, =D2+E2, ou d'un coup avec =D2*(1+Paramètres!$B$3). Comme on manipule de l'argent, enveloppe le calcul dans ARRONDI avec 2 décimales, =ARRONDI(D2*Paramètres!$B$3;2), pour que chaque TVA s'arrête au centime comme sur une facture. Écrire 0,2 en dur dans la formule marche aussi, mais le jour où le taux change, il faut rouvrir toutes les lignes.
Pour enlever la TVA d'un prix TTC dans Excel, divise le TTC par 1 plus le taux, =F2/(1+Paramètres!$B$3), ce qui redonne le hors taxes, et la TVA seule vaut le TTC moins ce hors taxes. Multiplier le TTC par 0,8 ou lui retirer 20 % est l'erreur classique, parce que la TVA a été calculée sur le hors taxes, plus petit que le TTC, et le résultat tombe faux de quelques euros. Avec un taux à 5,5 % ou 10 %, la même division marche, seul le contenu de la cellule du taux change.
Pour aller plus loin
Tout ce dont tu as besoin pour continuer à t’entraîner se trouve ici.
Récupère notre modèle de comptabilité simple
Télécharger le fichier
Ton cours Excel gratuit
3 chapitres pour apprendre les bases et découvrir l'automatisation.
Voir le cours
Télécharge notre cours Excel complet en PDF
Télécharger
Le quiz qui révèle ton vrai niveau
Faire le test
Le dictionnaire des formules Excel
Chercher une formule