Calcul dans excel, prévision 2007

[invite709e46e7]

Bonjour,

Je souhaiterais obtenir votre aide, je suis nulle en calcul et en utilisation d'excel :

Est-il possible de résoudre un tel tableau ?
2007 Client 1 Client 2 Client 3 Client 4 Total
Produit 1 0 500
Produit 2 160
Produit 3 0 210
Produit 4 0 300
Produit 5 0 450
Produit 6 0 260
Produit 7 0 0 270
Total 840 570 330 410 2150

Il faut tenir compte des valeurs nulles.

J'ai le total général des colonnes et des lignes, il faut que la somme des valeurs manquantes correspondent aux totaux indiqués.

J'ai les données de 2006, il faudrait tenir compte des proportions de 2006 pour remplir le tableau 2007 qui sont des prévisions.

Je les mets aussi au cas où :

2006 Client 1 Client 2 Client 3 Client 4 Total
Produit 1 320 100 100 0 520
Produit 2 60 30 90 15 195
Produit 3 100 40 0 15 155
Produit 4 0 220 50 65 335
Produit 5 200 80 0 185 465
Produit 6 0 90 40 90 220
Produit 7 180 30 0 0 210
Total 860 590 280 370 2100


C'est pour le boulot, je travaille sur excel. Bien entendu, ce tableau est réduit par rapport au nb de produits.

Je ne sais pas si c'est possible de résoudre ça et en tout cas je remercie d'avance ceux qui pourront m'éclairer même si c'est pour dire qu'il n'y a pas de solution.
Je n'arrive vraiment pas à trouver une formule de calcul.

Cela fait longtemps que je n'ai fait des maths et là je sèche complètement

Je remercie par avance tous ceux qui pourront m'aider.

Bon après-midi!!!
-----

[invite709e46e7]

je vois que mes tableaux n'ont pas gardé leur forme. Comment fait-on pour éditer un message pour le modifier ?

Je vais recopier les tableaux en espérant qu'ils gardent leur mise en forme. J'espère vraiment que quelqu'un pourra me dépanner en m'expliquant comment calculer.

2007 ; Client 1;Client 2;Client 3;Client 4; Total
Produit 1; ; ; ; 0 ; 500
Produit 2; ; ; ; ; 160
Produit 3; ; ; 0 ; ; 210
Produit 4; 0 ; ; ; ; 300
Produit 5; ; ; ; 0 ; 450
Produit 6; 0 ; ; ; ; 260
Produit 7; ; ; 0 ; 0 ; 270
Total; 840 ; 570 ; 330 ; 410 ; 2150

Voilà les données 2006,pour savoir les proportions au cas où.

Merci d'avance, je suis complètement démunie face à ce tableau.

2006; Client 1; Client 2; Client 3; Client 4; Total
Produit 1; 320 ; 100 ; 100 ; 0 ; 520
Produit 2; 60 ; 30 ; 90 ; 15 ; 195
Produit 3; 100 ; 40 ; 0 ; 15 ; 155
Produit 4; 0 ; 220 ; 50 ; 65 ; 335
Produit 5; 200 ; 80 ; 0 ; 185 ; 465
Produit 6; 0 ; 90 ; 40 ; 90 ; 220
Produit 7; 180 ; 30 ; 0 ; 0 ; 210
Total; 860 ; 590 ; 280 ; 370 ; 2100

[Ciscoo]

Bonjour Petitpoisson, euh Petitpoison.

1) Mets donc tes extraits de tableaux en pièce jointe. Cela sera plus compréhensible pour tout le monde.

2) Si tu veux vraiment te former sur Excel, va donc faire un tour sur un forum un peu plus spécialisé sur ce sujet, excel-download par exemple.

Bon courage.

[invite709e46e7]

Bonsoir Ciscoo !

Merci de vous intéresser à mon souci!

J'ai mis en pièce jointe mon fichier.

En fait, ce qui m'avancerai, ce serait déjà de connaître la formule mathématique, et si je n'arrive pas à écrire la formule dans Excel, je me renseignerai sur le forum que vous m'indiquez.

Merci )

[A voir en vidéo sur Futura]

[mécano41]

Bonjour,

J'ai mis les formules "somme" dans les cellules correspondantes du premier tableau (pour info. en G10 j'ai ajouté la somme de la colonne F). Je suppose que c'est ce que tu veux faire.

Pour le tableau des prévisions, il faudrait préciser ce que tu veux faire car je n'ai pas compris.

Cordialement

[invite709e46e7]

En fait, sur le tableau prévion 2007, on m'a donné les totaux globaux. Les valeurs nulles signifient que le client ne commadera pas ce produit.

Il faut que je trouve toutes les autres valeurs (on m'a dit d'utiliser les valeurs 2006 pour pondérer cela, je ne sais pas si c'est très mathématique comme explication)

Et quand on additionne les valeurs trouvées, on doit retomber sur les totaux aussi bien horizontelement que verticalement.

Moi qd je cherche, soit je trouve de valeurs et je retrouve les totaux des colonnes et pas les totaux des lignes, soit l'inverse.

Je ne sais pas si il y a un solution mathématique à ce casse-tête!

Merci d'essayer de m'aider!!! ))

[inviteaf1870ed]

Je doute qu'il y ait une solution exacte, car les lignes et les colonnes varient dans des proportions différentes. Donc si on prend pour proportion celle des lignes, les colonnes ne tomberont pas juste, et réciproquement.
Peut être que la solution est de prendre un coefficient de proportionnalité plus complexe, il faut alors utiliser le solveur d'Excel, mais le résultat sera approché.

[mécano41]

J'ai mis deux solutions dans le fichier :

- la première fait une optimisation sur les quantités par client. Les résultats de ces quantités sont donc les plus proches de la prévision.

- la seconde fait une optimisation sur les quantités par produits. Cette fois, les résultats de ces quantités sont les plus proches de la prévision.

Je ne sais pas si cette approche convient.

Peut être que si quelqu'un maîtrise bien les optimisations par algorithme SIMPLEXE il y aurait une solution plus précise mais j'en doute...

Cordialement

[invite709e46e7]

Merci beaucoup Mécano41!

Je suis arrivée à la même conclusion que vous, c'est à dire avoir une approche.

Je pensais que mon niveau de math était en cause.

Ericcc, vous parlez d'utiliser le solveur d'excel, pouvez-vous m'éclairer quant à ma culture personnelle où dois-je aller sur un autre forum ?

Merci beaucoup vraiment !!!

[mécano41]

Bonjour,

J'ai tenté avec le solveur : cela a l'air de fonctionner (a priori, je pensais qu'il ne trouverait pas à cause des nombreuses contraintes mais...)

J'ai expliqué l'utilisation dans le fichier EXCEL.

J'ai essayé avec plusieurs séries de nombres et il a trouvé (je ne dis pas que dans certains cas il ne va pas se planter...)

Cordialement

[Ciscoo]

Bonjour à tous, Bonjour Petitpoison.

Bien vu, Mécano41...

@+

[invite709e46e7]

Merci 1000 fois mécano41 !!!

Je vais étudier votre solution et le solveur par la même occasion. Je reviens vers vous si j'ai encore une question!!!

Excellente journée !!!!

[invite709e46e7]

Bonjour,

J'ai étudié votre solution avec le solveur et j'aurais quelques questions à vous poser :

- Dans les contraintes, pourquoi faut-il écrire deux fois
$c$17:$f$23>=0 ?

- Si dans le tableau figure des sous-totaux, il faut lancer plusieurs fois le solveur, ou alors on peut définir des plages ? Par exemple, si après les trois premiers produits figurait un sous total et après le produit 7 aussi?
la somme des 2 sous-totaux est égale au total général.

Je pose la question car j'ai un autre tableau sous cette forme.

- peut-on utiliser le solveur quelque soit le nombre de lignes ou de colonnes?

Mille mercis

[mécano41]

Bonjour,

Question 1 : c'est une erreur de ma part. Tu cliques sur l'une des lignes et tu fais "supprimer"

Question 2 : il ne faut pas prendre en compte les sous-totaux. Tu ne considères que les plages de nombres à faire varier. Les cellules des sous-totaux comporteront une formule "somme(...)" qui ne doit pas bouger. Pour définir des plages tu sélectionnes une première cellule puis tu cliques sur les autres en maintenant la touche CTRL appuyée

Question 3 : il n'y a théoriquement pas de limite ; simplement le calcul est d'autant plus long qu'il y a de cellules à prendre en compte.


Bon courage

[invite709e46e7]

Mille mercis !!!!

[invite709e46e7]

Bonjour,

Je sais que ce forum n'est pas dédié à Excel, mais j'aurais encore une question concernant le paramétrage du solveur.

En haut de la fenêtre du solveur, on définit la cellule cible.
Dessous on peut définir max, min et valeur. Pourquoi dans le cas de mon pb, tu as laissé sur max ? Et pourquoi ne met-on pas égale à 2500 vu que c'est la valeur recherchée ? Ton paramétrage marche super bien, c'est juste pour que je comprenne mieux son fonctionnement.

Bon après-midi !

[mécano41]

Bonjour,

C'est seulement parce que j'ai raisonné en commerçant (bien que cela ne soit pas ma partie! ) et j'ai pensé que, comme c'est le total des ventes, on voudrait, dans le cas où l'on ne tomberait pas sur le nombre exact souhaité, prendre plutôt le maximum.

Je viens d'essayer avec "valeur = 2150", cela donne des résultats légèrement différents mais qui semblent également corrects.

Cordialement

[mécano41]

J'ai oublié : si tu as besoin d'autres renseignements sur EXCEL (en dehors du présent sujet), tu peux poster (toujours sur FUTURA) dans "Informatique / logiciels-sofware-open source"

[invite709e46e7]

ok Merci beaucoup !!!

[invite709e46e7]

Bonjour,

J'ai reçu le tableau excel contenant les données réelles, j'ai configuré le solveur, mais il me dit qu'il y a trop de cellules variables.

Les données commencent à la cellule c127 à q236.

Entretemps il y a un tas de conditions, mais là je viens de faire un essai en supprimant toutes les conditions et il me met toujours le même message d'erreur.

Je ne sait pas du coup comment appréhender un début de solution, car je n'ai aucune données intermédiaire pour les valeurs budget
Y aurai-t-il un truc pour le forcer à marcher qd même?


Merci d'avance!

[invite709e46e7]

Re,

Je joins le fichier contenant les données 2006 sous le format du tableau donné l'autre fois en exemple.

Les cellules violettes doivent être égales à 0.

Merci d'avance,

[mécano41]

Bonjour,

Je suis en train de regarder mais, à tout hasard, as-tu essayé d'augmenter beaucoup le temps de calcul qui est actuellement à 100 s (dans options du solveur)

[mécano41]

Je viens d'essayer, ça ne change rien. Je cherche...

[mécano41]

J'ai trouvé d'où cela vient mais malheureusement je ne vois pas comment le résoudre pour l'instant. J'ai lu dans l'aide qu'il ne supporte que 200 cellules variables.

Je vais chercher s'il n'y aurait pas une astuce pour traiter le problème en plusieurs fois...

[invite709e46e7]

Re!

Merci !!!

[mécano41]

J'ai mis un appel à l'aide dans la rubrique "Logiciels..."

[invite709e46e7]

ok merci!!!

[mécano41]

Bonjour,

Je n'ai rien pour le moment et, pour l'instant, pas beaucoup d'aide!

Avec ton nouveau tableau, je viens de voir un autre point qu'il faudra préciser, quelle que soit la solution de résolution (s'il y en a une ) : il y a, dans la liste, des produits qui n'étaient pas vendus en 2006 et qui sont budgétisés en 2007. Je pense qu'il faudra fixer une règle de répartition avant de faire le traitement sinon, il y aura une infinité de solutions.
Les chiffres de vente prévisionnelle pourraient être, par exemple, répartis en fonction du chiffre d'affaire par client (en tenant compte des endroits où il a été décidé de mettre zéro).

Autre point : dans mes tableaux, j'avais mis comme contraintes : nombres entiers. Je pensais alors que les nombres étaient des quantités de produits. Au vu de ton dernier tableau, j'ai vu qu'il s'agissait de montants donc, ce n'est plus utile, quel que soit le mode de résolution.

Cordialement

[invite709e46e7]

Bonjour Mécano41!

J'ai découvert hier les nouvelles contraintes dont tu me parles en recevant les données réelles.

ce sont bien des qtés vendues, mais j'aurais aussi à faire le même genre de calculs pour concernant les valeurs.

Je prend note de tes observations.

Merci pour tout !!!

[mécano41]

Bonjour,

N'ayant pas trouvé de solution avec le solveur, je me suis rabattu vers ma première idée, en faisant plusieurs passes, quatre, en l'occurrence.

J'ai d'abord inclus les nouveaux produits, en appliquant une formule répartissant le budget prévu proportionnellement au chiffre réalisé avec chaque client, l'année précédente et je fais calculer les rapports "prévision/total calculé" par ligne et par colonne. Ensuite :

1- un tableau multiplie dans chaque ligne par le rapport correspondant de la dernière colonne. Nouveaux rapports en dernière ligne.

2- un autre tableau multiplie dans chaque colonne par le rapport correspondant de la dernière ligne. Nouveaux rapports en dernière colonne.

3- idem 1, mais en appliquant les rapports du 2.

4- idem 2 mais en appliquant les rapports du 3.

En quatre passes, l'écart final est compris entre -0,48% et +0,36%. On devrait pouvoir améliorer en continuant mais je ne sais pas si ce sera très utile. Le cas échéant, il faudrait voir pour automatiser la manip.

Si tu n'as pas trouvé d'autre solution, il serait bon que tu examines celle-ci en détail pour vérifier que rien ne cloche (car j'avoue que je sature un peu et que j'ai les yeux qui se croisent sur les chiffres et je risque de passer à côté de quelque chose )

Cordialement