Calcul Grille Tarifaire Dynamique
Bonjour à tous, premier message sur ce forum...
Un petit cas jamais rencontré par moi, je ne fais que bidouiller Excel, mais qui j'espère sera simple pour vous !
Voilà mon soucis : j'ai un tableau de calcul de coût, pour du séjour touristique.
D'après mon modele ultra simplifié en pièce jointe : X client, Y jours sont mes variables. (en jaune en haut de la feuille)
Excel calcule selon les prestations (cout unitaire, fixes), le total du séjour par client et par jour.
J'ai besoin de sortir une grille tarifaire avec nombre de jour en abscisses et nombre de clients en ordonnées.
Dis différemment, afficher dans chaque case de la grille :
"valeur de la case (total par jour et par client) LORSQUE (variable nombre de jour=abscisse) ET (variable nombre de client= ordonné)"
Pour une cellule d'exemple :
B34=C21 LORSQUE B2=B32 ET B3=A34
Possible ça ? comment ?
Merci énormément pour votre aide !
Le plus simple est de faire une fonction qui consolide toutes les étapes de calcul en une seule. En B34 :
=((B13*A34+C14+B16*A34+C17)/B32)/A34ce qui, après un peu d'algèbre, donne:
=((B13+B16)*A34+C14+C17)/(B32*A34)Cette fonction étant un peu embêtante à glisser, tu peux utiliser les fonctions ligne et colonne comme ceci:
=(($B$13+$B$16)*(LIGNE(A34)-33)+$C$14+$C$17)/(COLONNE(B32)*(LIGNE(A34)-33))mais dans ce cas il faudra adapter la formule si tu déplaces ton tableau, si tu rajoutes des lignes en-dessus etc.
Merci Caspi, tu a bien vu mon fonctionnement. Des frais fixes (guide), des frais variables selon nbre de client. ça fonctionne. Le bazar vient de la complexité de mon calcul de cout, qui comporte de très nombreuses lignes...
Quand tu me dis "le plus simple c'est de faire comme ça", c'est sûr ? parceque c'est pas simple en vrai ! d'ou mon idée d'utiliser mon résultat (C21 sur mon exemple) et fonction des variables. (cases jaunes)
La fonction telle que je la demande n'existe pas sur excel ?
Merci pour la rapidité !
@Renaud : as-tu vu ma réponse
@Caspi : curieux, j'avais trouvé beaucoup plus simple, mais peut-être que je me trompe !
frais fixes / nb de clients + frais variablesBonjour à tous
Il faut utiliser une table à double entrée qui reprend le dernier calcul par rapport aux 2 variables (onglet Données, analyse scénario, Table)
@Steelson. Non hélas ça ne marche pas...
@78Chris. Oui ! ça sent bon ça ! Je connaissais pas les tables de données !
Je découvre ça, je teste et je vous dis.
Oui, il faut que le tableau s'adapte à tout les cas : modif d'un tarif (bouffe, guide dans mon exemple) , nombre de client, nombre de jours. Tout doit être dynamique.
mes lignes 'Total par jour et par personne ' est en fait une vérification du tableau lorsqu'on change les variables.
Il faut partir de mon total pour un cas pour déduire les autres cas, c'est bine le job du 'tableau de donnée', que j'arrive pas encore a faire fonctionner !
Ok, donc nouveaux problème.
Ci joint un modele simplifié beaucoup plus fidèle à la structure de mon fichier.
Et la table de donnée ne fonctionne pas comme je veux, car elle "se mord la queue" ! :
En faisant varier "nombre de jour suivant", toute la grille se modifie... pas bon.
Une idée ?
Grand merci.
RE
Si tu as toujours 1er jour et le reste en jours suivants, la cellule variable B2 fonctionne : on a bien 61 dans la table à l'intersection de 5 et 5
(les couleurs ne sont pas automatiques dans mon exemple)
Hello, une piste pour mon problème ? merci !
RE
Je t'ai répondu : je ne vois pas de problème donc si toi tu en vois un il faudrait être plus explicite...
OK pardon, voici donc :
Dans ta soluce la table me propose toutes les solutions découlant du couple 5 jours/ 7 clients.
Hors, si on modifie les variables, case jaune nb de jour et nb de client, le résultat prix par jour et par client ne correspond pas a cette table, et excel modifie toute la table. En d'autres termes il n'y a qu'une seule bonne réponse par table ! (a l'intersection du couple de paramètres).
Exemple : couple 5/7 en variable, lecture sur table 15 jours / 12 clients = 12,56€.
Hors, si j'incréments en variable 15 jours / 12 clients, il faut 37€ comme résultat.
je me fais comprendre ? merci !
Bonsoir Renaud974,
Pour moi 15jours/12 personnes avec Nourriture de 25 le 1er jour puis 20 les jours suivants j'obtiens 32,11 dans la grille tarifaire ainsi qu'en TOTAL PAR JOUR ET PAR PERSONNE : (cellule C37).
Si on change le nombre de jours entre 2 et 15 et le nombre de personnes entre 1 et 12 j'ai toujours le même résultat.
Bonne continuation
Christian
RE
Effectivement j'ai repris le calcul en prenant une colonne Coûts fixes et une colonnes Coûts variables et un nouveau calcul.
Cela me semble correct
Ouaip, ça fonctionne, j'explore les 2 pistes en parallèle : recomposition de la formule, en divisant couts fixes / couts variables, et
Ca fonctionne mais c'est un peu casse gueule si j'ajoute des prestation, par exemple.
Un ami m'a trouvé la solution élégante ! voir ci dessous. c'est bête comme chou....
Bon j'arrive pas encore à l'adapter à mon usine à gaz, mais je suis rassuré, c'est conceptuellement et mathématiquement faisable mon affaire...
Peut être ma réponse viendra d'un mix des 2 solutions.
Merci encore énormément pour votre participation à tous, vous tiens au courant si y'a encore des évolutions avant de passer en résolu.
RE
Ca fonctionne mais c'est un peu casse gueule si j'ajoute des prestation, par exemple.
Non car c'est basé sur les totaux de frais fixe et variables donc il faut juste les 4 totaux, le détail au dessus tu mets ce que tu veux autant de lignes de prestations que nécessaires...
Cela a vocation à remplacer tes calculs Coût unitaire, coût Total pour bien différencier ce qui est indépendant du nombre de pax de ce qui en dépend...
Un ami m'a trouvé la solution élégante ! voir ci dessous. c'est bête comme chou....
Vous avez repris la formule initiale du 1er exemple et juste inversé ton calcul de jours car effectivement c'est cela qui mettait le souk.
"frais fixes / nb de clients + frais variables" dans un tableau de données, mais je connaissais pas les tableaux de données !
Donc voici une élégante solution, un peu remise en page.
Merci à tous !