XL 2013 Addition conditionnelle sur 2 feuilles

[AntoineLR]

Bonjour à tous,
Il y a quelques temps, j'avais reçu une aide précieuse grâce à vous, et aujourd'hui, malgré mes "bidouillages" de formules, mes essais 3D, mes connaissances ne me suffisent plus et je cale.

Mon problème :
Faire l'addition TTC des notes d’hôtel (onglet hotel) avec les notes correspondantes de restaurant (onglet Rest) par taux TVA
Un client vient à l’hôtel (N°Regl. = 8) , une facture unique est délivrée (No note).
Et comme il mange au restaurant plusieurs fois, plusieurs factures sont émises (N° ticket), à additionner avec celle de l’hôtel au moment du départ.
Dernière condition : trier les totaux par taux 10 ou 20. (Rest!(Q:Q)
Afficher les résultats en Export!(D7:E7)

Les données sont extraites de 2 logiciels de caisse différents sous forme excel, les colonnes ne sont pas modifiables.

Merci d'avance à vous pour votre aide précieuse.
Bonne journée, Antoine
PS : veuillez m'excuser pour avoir posté ma discussion au mauvais endroit..

 

[CISCO]

Bonjour

Est-ce que c'est quelque chose comme ça que tu veux?

@ plus

P.S1 : J'ai fait comme-ci l’hôtel était à 10 %. Si ce n'est pas le cas, il suffit d'inverser les valeurs dans D6 et E6.

PS2 : Dans ton vrai fichier, il faudra travailler avec des plages dynamiques pour tenir compte du nombre variable de lignes de chacune des colonnes utilisées.

 

[AntoineLR]

Bonjour CISCO,
Merci pour ta réponse rapide !
PS1 : la TVA pour hôtellerie est à 20%
PS2 : les données qui remontent du logiciel sont brutes, je ne dois pas les toucher pour éviter une erreur de manipulation. Et je les sélectionne mois par mois, ce qui fait que le calcul sur les dates dans la formule n'est pas nécessaire. C'est pour ça que si on peux formuler sur les colonnes entières, c'est mieux, car en effet le nombre de lignes va augmenter cet été.

Concernant le résultat, je pense qu'il y a un soucis :
- si je filtre manuellement la valeur "8" dans Hotel!$C:$C , le montant TTC à 20% est de 4043.40€
- A cela je dois extraire dans Rest!$R:$R le montant TTC correspondant trié par taux TVA les No note 9 10 11 12 13 17 18 22 23 0 69 79 80 84 78 135 172, etc... ,
En filtrant manuellement seulement les tickets 9 10 11 12 13, j'obtiens déjà 48.20 € au taux de 10%..
Il m'en manque beaucoup, le percepteur va pas être content

A bientôt,
Antoine

 

[CISCO]

Bonjour CISCO,
Merci pour ta réponse rapide !
....
Concernant le résultat, je pense qu'il y a un soucis :
...
- A cela je dois extraire dans Rest!$R:$R le montant TTC correspondant trié par taux TVA les No note 9 10 11 12 13 17 18 22 23 0 69 79 80 84 78 135 172, etc... ,
...

A bientôt,
Antoine

Et comment est-ce qu'on peut voir ça sur ton fichier ?

@ plus

 

[AntoineLR]

Mince, je pensais être explicite... voir quoi?
Mon fichier a 3 feuilles : Export pour le résultat, hotel pour les données de l’hôtel, et Rest pour les données du restaurant.
Il faut que j'additionne le coût du restaurant au coût de l’hôtel.
D'un coté j'ai des factures (N°note) de l'hotel , qu'il faut extraire et rapprocher aux coûts de restaurant (N°ticket)

exemple : QQ est resté à l'hotel et a généré une note N°11 pour 20.40 €
Coté restaurant, il a consommé sur la même note N°11 pour 4.25€ au taux de 20% et pour 16.15€ au taux de 10%

Suis-je plus compréhensible?
@Toute Antoine

 

[CISCO]

Bonsoir

J'ai écrit que j'ai considéré que la TVA sur les nuitées étaient de 10 %. Donc, cela fait pour le client 8, 4043.40 + 48 + 0 + 0 + 0 + 0 = 4091,40 ce que donne la formule proposée dans mon dernier fichier.

Comme tu écris que les nuitées sont à 20 %, comme déjà écrit dans mon dernier fichier, il suffit de mettre la valeur de D6 dans E6, et inversement, ce qui donne dans D7 4043,4 + 26 = 4069,4 et dans E7 48 + 0 +0 +0 +0.

Tu as été assez explicite dans tes premiers posts, mais comme tu n'as pas fait l'inversion dont je parlais dans mon dernier post, comme je pensais que tu ne parlais que de la facture du client n°8...

@ plus

 

[AntoineLR]

Bonjour CISCO,
Je te remercie de te donner tant de mal, pour la TVA de l’hôtellerie, j'ai bien compris ce que tu m'as dit, et j'ai eu un doute ce WE pour le taux de TVA, je changerai de case si c'est 10 au lieu de 20%
Par contre, imagine que je n'ai eu sur le mois que 26€ de restauration, et là mon taulier met la clef sous la porte !!
Il y a une subtilité que je vais tenter de t’expliquer au mieux.
La sélection "8" de la page hôtel correspond à tous les clients du mois à l’hôtellerie, tu l'as bien compris.
Si j'utilise le filtre automatique et sélectionne comme mon fichier joint la valeur "8", j'ai un total TTC d’hôtellerie du mois pour 4043.40 €, tu l'as bien compris.

Cette sélection faite, tu vois dans la feuille hôtel la liste des factures correspondantes (N°note) qu'il va falloir sélectionner et additionner par taux avec ce que les clients ont aussi mangé en Restauration (dans la feuille Rest).
Il y a 81 notes :
9 10 11 12 13 17 18 22 23 0 69 79 80 84 78 135 172 179 180 184 186 196 197 196 205 212 227 232 233 234 235 272 274 275 320 321 322 324 325 326 330 333 336 337 338 340 345 347 351 357 358 365 366 368 388 390 393 417 430 433 434 457 500 519 526 532 542 544 548 554 555 557 559 564 595 631 634 653 685 694 698

Maintenant, le but du jeux est de sélectionner ces 81 notes d’hôtel dans la feuille Rest (N°ticket)
C'est ce que j'ai fait manuellement dans mon fichier joint. Et c'est là que se trouve toute ma difficulté.

Ceci fait, il ne reste plus qu'à filtrer soit la valeur TVA10 ou TVA20 pour obtenir le résultat :
Restauration à 10% : 2630.35
Restauration à 20% : 1467.25
Hotellerie à 20% : 4043.40

Soit un total de 5510.65 €

Bonne journée,
Antoine

 

[JCGL]

Bonjour à tous,

Un essai sur la base d'un TCD

A+ à tous

 

[CISCO]

Bonjour à tous, bonjour JCGL

Bonjour CISCO,
...
La sélection "8" de la page hôtel correspond à tous les clients du mois à l’hôtellerie, tu l'as bien compris.
....
Bonne journée,
Antoine

Et non, je ne l'ai pas compris : Pour moi, le n°8 correspond à un client, pas à tous les clients. Dans ces conditions, à qui correspondent les autres n° ?


@ plus

 

[CISCO]

Rebonjour

Cf. en pièce jointe, avec deux formules matricielles à valider avec les 3 touches Ctrl+maj+entrer.

@ plus

 

[AntoineLR]

Bonsoir à Cisco et JCGL,
Franchement, vous m'avez montré que votre participation à mon problème Excel est une clef de voute d'une passion. Je m'en suis éloigné, je le regrette. Je vous remercie sincèrement ce soir de votre aide.
Pour Cisco, non le "8" correspond au CA hôtellerie d'un mois (y'en a quand même pour plus de 4000 €, je veux bien te payer un verre, mais pas à St Barth !!!)
Malheureusement, je ne peux pas mettre cette solution dans les mains d'une novice (et de moi-même avec mon peu d'expérience) une formule matricielle à valider par une combinaison de touches.

Je vais encore travailler et contrôler le TCD de JCGL qui me parait très intéressant sur deux points : il ne m'inflige pas un résultat en Export!($D5:$E5) , mais il me propose de vérifier par le TCD le Chiffre d'Affaires des tickets d’hôtellerie et les tickets de restauration correspondants. Et cerise sur le gâteau, j'ai le détail des notes par taux de TVA.
(Contrôler uniquement à cause de la variation des lignes des données que je compte "écraser " d'un mois sur l'autre)

Je vous remercie tous les deux.
Je retiendrai encore une fois que poser une question n'est pas si facile. Merci pour vos réponses.
Pouce en l'air pour le TCD ! Merci JCGL

Antoine

 

[CISCO]

Bonsoir

Et voui, avec un TCD, c'est bien plus pratique.

@ plus