Formule en SI complexe (enfin je trouve ^^)
MacBeth182
-
Raymond PENTIER Messages postés 58991 Date d'inscription Statut Contributeur Dernière intervention -
Raymond PENTIER Messages postés 58991 Date d'inscription Statut Contributeur Dernière intervention -
Bonjour,
Voilà, je suis entrin de faire un tableau sur Excel mais je n'arrive pas à savoir comment lui expliquer ce que je veux ^^.
Imaginons mon tableau ensemble :
J'ai un tableau ou je rentre des factures. Ce tableau est composé :
- d'une colonne A ou j'inscris la date de la facture
- d'une colonne B ou j'inscrie le numéro de la facture
- d'une colonne C ou j'inscris le montant hors taxes de l'article (HT)
- d'une colonne D que calcul le montant de l'article avec la T.V.A. (TTC)
Jusque ici tout va bien.
Mais lorsque j'ai plusieurs articles dans une facture je remplie toutes les données dans les colonnes pour la 1ère ligne mais juste avec le montant hors taxes du 1er article, puis dans une seconde ligne je ne remplie que le montant hors taxes du 2ème article, [...], puis dans une N ème ligne je ne remplie que le montant hors taxes du N ème article. Je souhaiterai que la colonne D (calculant le montant de l'article avec la T.V.A.) ne se calcul qu'à la ligne du dernière article (le N ème) en les additionnants tous et en leurs ajoutant leurs T.V.A. sans qu'il me prenne les montant hors taxes des articles des factures précédentes (soit, il doit s'arrêter à la première ligne qu'il rencontre ou la date [soit la colonne A] soit remplie, en la comprenant aussi dans son calcul). Autrement dit, je voudrai que la colonne D (calculant le montant de l'article avec la T.V.A.) ne soit remplie que dans la dernière ligne de chaque facture.
Je vous remercie d'avance pour votre aide et si vous ne comprenez pas bien ou que vous avez des questions n'hésitez pas.
Voilà, je suis entrin de faire un tableau sur Excel mais je n'arrive pas à savoir comment lui expliquer ce que je veux ^^.
Imaginons mon tableau ensemble :
J'ai un tableau ou je rentre des factures. Ce tableau est composé :
- d'une colonne A ou j'inscris la date de la facture
- d'une colonne B ou j'inscrie le numéro de la facture
- d'une colonne C ou j'inscris le montant hors taxes de l'article (HT)
- d'une colonne D que calcul le montant de l'article avec la T.V.A. (TTC)
Jusque ici tout va bien.
Mais lorsque j'ai plusieurs articles dans une facture je remplie toutes les données dans les colonnes pour la 1ère ligne mais juste avec le montant hors taxes du 1er article, puis dans une seconde ligne je ne remplie que le montant hors taxes du 2ème article, [...], puis dans une N ème ligne je ne remplie que le montant hors taxes du N ème article. Je souhaiterai que la colonne D (calculant le montant de l'article avec la T.V.A.) ne se calcul qu'à la ligne du dernière article (le N ème) en les additionnants tous et en leurs ajoutant leurs T.V.A. sans qu'il me prenne les montant hors taxes des articles des factures précédentes (soit, il doit s'arrêter à la première ligne qu'il rencontre ou la date [soit la colonne A] soit remplie, en la comprenant aussi dans son calcul). Autrement dit, je voudrai que la colonne D (calculant le montant de l'article avec la T.V.A.) ne soit remplie que dans la dernière ligne de chaque facture.
Je vous remercie d'avance pour votre aide et si vous ne comprenez pas bien ou que vous avez des questions n'hésitez pas.
A voir également:
- Formule en SI complexe (enfin je trouve ^^)
- Formule si et - Guide
- Mise en forme conditionnelle excel formule si - Guide
- Formule moyenne excel plusieurs colonnes - Guide
- Formule mathématique - Télécharger - Études & Formations
- Formule somme excel colonne - Guide
7 réponses
Bonjour
Attention la somme des TVA n'est généralement pas égale à la TVA sur la somme ..
Il faut que dans ta colonne D tu n'écrives qu'une seule formule par facture ( je la ferais dans une ligne vide suivant le dernier article, pour pouvoir facilement rajouter une ligne article ) qui calcule la TVA sur la somme des valeurs HT de la colonne C.
Il faudra construire ta formule manuellement pour chaque facture.
Il y a d'autres solutions, un peu plus complexes, pour ne faire la somme que des lignes ayant la même référence de facture, ou faire le total des factures dans une autre feuille, avec une somme conditionnelle.
Tu peux aussi ( premier cas ) construire une formule calculant la TVA sur le total HT de la colonne C, mais ne l'affichant que si le numéro de facture de la ligne suivante est différent du numéro en cours dans la ligne.Il faut que le "début" de la somme change à chaque facture.
En fait, une bonne conception serait d'avoir un fichier pour toutes les lignes de toutes les factures, classé par numéro de facture, et un autre fichier pour les totaux des factures. Mais c'est un peu plus complexe.
A+
Nyctaclope
Attention la somme des TVA n'est généralement pas égale à la TVA sur la somme ..
Il faut que dans ta colonne D tu n'écrives qu'une seule formule par facture ( je la ferais dans une ligne vide suivant le dernier article, pour pouvoir facilement rajouter une ligne article ) qui calcule la TVA sur la somme des valeurs HT de la colonne C.
Il faudra construire ta formule manuellement pour chaque facture.
Il y a d'autres solutions, un peu plus complexes, pour ne faire la somme que des lignes ayant la même référence de facture, ou faire le total des factures dans une autre feuille, avec une somme conditionnelle.
Tu peux aussi ( premier cas ) construire une formule calculant la TVA sur le total HT de la colonne C, mais ne l'affichant que si le numéro de facture de la ligne suivante est différent du numéro en cours dans la ligne.Il faut que le "début" de la somme change à chaque facture.
En fait, une bonne conception serait d'avoir un fichier pour toutes les lignes de toutes les factures, classé par numéro de facture, et un autre fichier pour les totaux des factures. Mais c'est un peu plus complexe.
A+
Nyctaclope
Bonjour,
As tu qu'un seul taux de TVA ?
mais fiscalement parlant, la TVA se calcule sur le Total HT comme l'a dit Nyctaclope et si tu regarde des factures, tu verras que le récapitulatif se situe toujours sur la même ligne en bas de la feuille A4 avec + ou - de blanc suivant l'article commandé
enfin la fiscalité oblige à indiquer le HT, la TVA suivant les taux,et le TTC pour que le client puisse récupérer la TVA et éventuellement se justifier(toi aussi, d'ailleurs).
As tu qu'un seul taux de TVA ?
mais fiscalement parlant, la TVA se calcule sur le Total HT comme l'a dit Nyctaclope et si tu regarde des factures, tu verras que le récapitulatif se situe toujours sur la même ligne en bas de la feuille A4 avec + ou - de blanc suivant l'article commandé
enfin la fiscalité oblige à indiquer le HT, la TVA suivant les taux,et le TTC pour que le client puisse récupérer la TVA et éventuellement se justifier(toi aussi, d'ailleurs).
Le taux de T.V.A. est le même pour tout les articles. Cette feuille en réalité est une feuille placé en 1ère page d'un classeur réunissant les factures faites au client, afin de retrouver tous les renseignements en un clin d'oeil. Ce tableau existe depuis quelques années mais le problème c'est que l'on doit tout modifier dessus selon le contenu des factures. Si elle a une T.V.A. ou non, si il y a plusieurs ligne dû à plusieurs articles dans la même facture, tout doit alors être modifié manuellement. Je souhaite donc faire un tableau "Intelligent" qui regarde si il y a des données dans cette ligne comment il doit les calculer si elles ont une T.V.A. ou non, si la ligne est relié à d'autres lignes pour ne faire qu'une facture, et donc si elle doit ajouter les autres lignes... enfin bref pour le moment j'ai réussit à expliquer à mon tableau, par des formules en SI, que si il n'y a rien dans la ligne il ne faut pas mettre 0,00 € mais laisser la cellule vide et la lui faire identifier une cellule afin de prendre dedans le renseignement lui permettant de voir si la facture est française ou nous et donc si il doit calculer la T.V.A. ou non. Maintenant je souhaiterai qu'il apprenne dans cette même formule en SI, à voir si il fait parti d'une facture composé de plusieurs lignes, si il est dans le milieu ou le début de la facture et donc doit laisser son TTC vide ou si il est la dernière ligne et qu'il doit alors additionner son HT avec celui des autres lignes de la même facture et d'ajouter la T.V.A. à cette somme. Il devra de ce fait être capable de voir le début de la facture, soi la 1ère ligne qu'il croise qui a une date et un numéro de facture et de se dire qu'il doit donc additionner de ce montant avec ceux des lignes du dessous jusqu'à celui de sa ligne. en gros je recherche une grosse formule, peu importe la longueur, permettant de tout faire ça.
Même si pour ça il doit y avoir une 20ène de =SI ^^.
Même si pour ça il doit y avoir une 20ène de =SI ^^.
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Re
Effectivement , ce n'est plus le même problème ...
Pour l'histoire de TVA ou pas, il suffit d'utiliser une TVA de taux zéro, cela unifie le problème.
Une petite zone de données dans un coin, avec code et taux de TVA, et récupération du taux par une fonction Recherchev() basée sur le code saisi ...
Quant au fait que la feuille est en tête du classeur, et est un récapitulatif, il n'est donc pas normal qu'elle contienne les lignes de détail articles, qui devraient être dans le corps du classeur.
Tu ne rendras pas ton tableau "intelligent" avec une cascade de SI(), car le problème n'est pas là...
Désolé, mais ton application est mal conçue, et pas étonnant que tu en payes le prix ...
Si l'on applique les règles de conception de bases de données ( trop long à t'expliquer ), il faut concevoir ainsi ton application :
0// sans doute un fichier pays, avec code de Tva applicable
1// un fichier clients ( code client,libellé, adresse(s), code pays ( d'où code Tva ) ...
2// un fichier factures( récap géné ), contenant TOUTES les factures de TOUS les clients ( date, code client, code facture, HT , TVA ... )
3// un fichier lignes de factures, relatif à toutes les factures
4// et sans doute un fichier articles, accessoirement un fichier tarif(s) ( selon pays ? ) et un fichier TVA ( divers taux ).
5// et peut être, pour aller jusqu'au bout, un fichier détail règlements ..
C'est le principe général, mais il peut y avoir des variantes ...
Ne pas le respecter dès le départ de la conception ne peut qu'amener des difficultés à plus ou moins long terme ...et les retouches deviennent de plus en plus lourdes, libre à toi de ne pas me croire ..
Tous ces fichiers étant distincts, et se renvoyant les données de l'un à l'autre, pour ne pas les dupliquer, par des formules de type RechercheV(), ou éventuellement des formules de type "base de données", selon la conception retenue.
Normalement c'est plutôt du ressort d'un logiciel de type Access, qui est une "vraie" base de données.
C'est encore ( limite ) du ressort d'Excel, au risque de le mettre à genoux si les fichiers sont volumineux ..
Si tu tiens à rester sous Excel, à ta place je recommencerais tout , tranquillement , avec un autre classeur, que je ne mettrais en service que le moment venu après tests effectués. Je suis certain que tu gagneras du temps. Les données de base sont facilement récupérables par copier coller, pour ne pas avoir à tout ressaisir ..
A+
Nyctaclope
Le plus joli des poèmes de la mathématique : e^(i.PI)=i^2
trois nombres "sacrés" d'horizons différents qui se donnent la main ...
Effectivement , ce n'est plus le même problème ...
Pour l'histoire de TVA ou pas, il suffit d'utiliser une TVA de taux zéro, cela unifie le problème.
Une petite zone de données dans un coin, avec code et taux de TVA, et récupération du taux par une fonction Recherchev() basée sur le code saisi ...
Quant au fait que la feuille est en tête du classeur, et est un récapitulatif, il n'est donc pas normal qu'elle contienne les lignes de détail articles, qui devraient être dans le corps du classeur.
Tu ne rendras pas ton tableau "intelligent" avec une cascade de SI(), car le problème n'est pas là...
Désolé, mais ton application est mal conçue, et pas étonnant que tu en payes le prix ...
Si l'on applique les règles de conception de bases de données ( trop long à t'expliquer ), il faut concevoir ainsi ton application :
0// sans doute un fichier pays, avec code de Tva applicable
1// un fichier clients ( code client,libellé, adresse(s), code pays ( d'où code Tva ) ...
2// un fichier factures( récap géné ), contenant TOUTES les factures de TOUS les clients ( date, code client, code facture, HT , TVA ... )
3// un fichier lignes de factures, relatif à toutes les factures
4// et sans doute un fichier articles, accessoirement un fichier tarif(s) ( selon pays ? ) et un fichier TVA ( divers taux ).
5// et peut être, pour aller jusqu'au bout, un fichier détail règlements ..
C'est le principe général, mais il peut y avoir des variantes ...
Ne pas le respecter dès le départ de la conception ne peut qu'amener des difficultés à plus ou moins long terme ...et les retouches deviennent de plus en plus lourdes, libre à toi de ne pas me croire ..
Tous ces fichiers étant distincts, et se renvoyant les données de l'un à l'autre, pour ne pas les dupliquer, par des formules de type RechercheV(), ou éventuellement des formules de type "base de données", selon la conception retenue.
Normalement c'est plutôt du ressort d'un logiciel de type Access, qui est une "vraie" base de données.
C'est encore ( limite ) du ressort d'Excel, au risque de le mettre à genoux si les fichiers sont volumineux ..
Si tu tiens à rester sous Excel, à ta place je recommencerais tout , tranquillement , avec un autre classeur, que je ne mettrais en service que le moment venu après tests effectués. Je suis certain que tu gagneras du temps. Les données de base sont facilement récupérables par copier coller, pour ne pas avoir à tout ressaisir ..
A+
Nyctaclope
Le plus joli des poèmes de la mathématique : e^(i.PI)=i^2
trois nombres "sacrés" d'horizons différents qui se donnent la main ...
Bien je comprends. Je vais donc continuer de travailler avec mon tableau présent.
Je suis désolé d'avoir ouvert un dialogue inutile.
Merci
Je suis désolé d'avoir ouvert un dialogue inutile.
Merci
Bonjour MacBeth182.
En faisant volontairement abstraction des messages #1 à 7, je réponds directement et précisément à ta question initiale :
Colonne D, tu fais normalement tes calculs de montant+TVA, mais tu appliques à ces cellules la couleur de police blanche ; ainsi ces données n'apparaissent ni à l'écran ni à l'impression. Tu ne mets de la couleur qu'à la dernière ligne ...
En faisant volontairement abstraction des messages #1 à 7, je réponds directement et précisément à ta question initiale :
Colonne D, tu fais normalement tes calculs de montant+TVA, mais tu appliques à ces cellules la couleur de police blanche ; ainsi ces données n'apparaissent ni à l'écran ni à l'impression. Tu ne mets de la couleur qu'à la dernière ligne ...