Optimisation de 4 paramètres avec excel

[Résolu/Fermé]
Signaler
Messages postés
51
Date d'inscription
mercredi 9 juin 2010
Statut
Membre
Dernière intervention
11 février 2017
-
Messages postés
51
Date d'inscription
mercredi 9 juin 2010
Statut
Membre
Dernière intervention
11 février 2017
-
Bonjour à tous,

Plusieurs fois je me suis posé la question alors je prend l'exemple de la randonnée comme prétexte.

Existe-il sous Excel un moyen pour :
- rentrer plusieurs items (ie 50 items = 50 lignes),
- y associer leurs caractéristiques en colonne (par ex. kcal, volume, poids, prix),

- et de demander à Excel les 10 items qui ont le meilleur rapport poids vs énergie vs volume vs prix.

(donc ici les 10 ayant le plus petit poids, avec le plus d'énergie, avec le moins de volume et le moins cher parmi les 50)

Pour revenir à la randonnée cela permettrait d'optimiser le poids du sac.

Merci !

5 réponses

Messages postés
51
Date d'inscription
mercredi 9 juin 2010
Statut
Membre
Dernière intervention
11 février 2017

Bonjour albkan,

Pour faire le 3) .. a) b) c) d)

Vous pensez à la fonction Trier qui se trouve dans l'onglet Données, comme l'image ci-dessous ? Car il ne s'agit pas vraiment de trier des groupes puis des sous groupes puis des sous-sous-groupes etc. mais plus de connaitre l'optimum..

Messages postés
24163
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
2 septembre 2021
6 873
Bonjour,

Tu demandes une sélection en fonction de critères n'ayant pas forcément de liens entre eux.
Il faudrait que tu établisses une note avec pondération de chaque critères en fonction de ce que tu veux privilégier.
eric
Messages postés
51
Date d'inscription
mercredi 9 juin 2010
Statut
Membre
Dernière intervention
11 février 2017

OK. Prenons par exemple :
- le poids 3 sur 5
- le volume 2 sur 5
- les kcal 4 sur 5
- le prix 5 sur 5

Plus le chiffre est élevé plus le critère est à prendre en considération.

Ensuite je gère comment ? (il s'agit de moyenne pondérée... ?)
Messages postés
24163
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
2 septembre 2021
6 873
Tu ne peux pas comparer directement des litres avec des grammes et des euros.
D'autant plus que les échelles ne sont pas comparables (3-6 € à comparer à 100-200 gr) d'une part, et que d'autre part tu vas vouloir le maximum de kcal pour le minimum de volume (inversion mini/maxi intéressant)

Je te suggère dans un premier temps de ramener tes valeurs à une note de 0 à 10, le mini de la colonne ayant 10 (ou 0 pour les kcal) et le maxi ayant 0 (10 pour les kcal).
Les formules pour kcal sont donc différentes des autres.
Et ensuite d'appliquer la pondération. Il faudra sans doute que tu tâtonnes un peu pour trouver les coefficients qui te satisfassent.

Exemple ci-joint, avec en plus un TOP3 avec un TCD.
https://www.cjoint.com/c/FDpkhHiwRtA
eric
Messages postés
51
Date d'inscription
mercredi 9 juin 2010
Statut
Membre
Dernière intervention
11 février 2017
>
Messages postés
24163
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
2 septembre 2021

Merci eric, j'ai utilisé plusieurs fois ton fichier et il fait très bien le job. Du coup je post le message comme résolu.

Du sur-mesure CCM, merci beaucoup !

Bonne journée :)
Messages postés
51
Date d'inscription
mercredi 9 juin 2010
Statut
Membre
Dernière intervention
11 février 2017

D'accord, je pense avoir saisi la démarche je vais m'exercer un peu et je laisse le fil ouvert pour l'instant.
Merci pour ton fichier
Messages postés
1978
Date d'inscription
mercredi 27 juillet 2005
Statut
Membre
Dernière intervention
28 septembre 2020
844
Bonjour à tous,

Une autre approche, graphique, en repérant chaque article par ses coordonnées dans le plan poids/énergie x volume/prix.


Ensuite tu peux choisir graphiquement la zone optimale ou la construire par formule :
{(x,y) t.q. x>=0, y>=0, x+y<=a, a étant un réel à faire varier} pour avoir le triangle sous la droite x+y=a
ou encore
{(x,y) t.q. x>=0, y>=0, x<=a, y<=b, a et b étant des réels à faire varier} pour avoir le rectangle (0,0), (a,0), (a,b), (0,b).

Tu peux aussi calculer la valeur a qui te permet d'obtenir le nombre souhaité d'articles

Cordialement
Messages postés
51
Date d'inscription
mercredi 9 juin 2010
Statut
Membre
Dernière intervention
11 février 2017

Bonsoir, merci pour votre réponse illustrée. Avez-vous ramené toutes les valeurs sur une échelle de 1 à 10 comme le suggérait eriiic ci-dessus ?
Comment faire si il y + de 4 critères (6 ou 7) ?

J'ai pensé à faire un diagramme radar pour comparer les surfaces, puis sélectionner les surfaces les plus petites et dont le centre de gravité serait du coté "énergie".

Mais je vois pas comment mettre ça au point. Je pensais être plus doué que ça sous excel ou qu'au moins il existait un outils que j'ignorais.

Merci
Messages postés
1978
Date d'inscription
mercredi 27 juillet 2005
Statut
Membre
Dernière intervention
28 septembre 2020
844 >
Messages postés
51
Date d'inscription
mercredi 9 juin 2010
Statut
Membre
Dernière intervention
11 février 2017

Bonjour,

Maintenant que je commence à comprendre ton problème, je chercherais :
1) à raisonner en dimension 3 : poids/kcal, volume et prix
2) à minimiser la norme euclidienne de chaque article dans cet espace
3) à travailler sur des données centrées -réduites

Il suffira de faire un tri sur les normes pour avoir ton classement.
Cette approche se généralise à n dimensions et ne nécessite que des opérations de base.

Cdlt

Remarque : tu aurais dû mettre ton fichier à disposition
Utilisateur anonyme >
Messages postés
51
Date d'inscription
mercredi 9 juin 2010
Statut
Membre
Dernière intervention
11 février 2017

Bonjour Ipalgo,

Sur ton message 2, tu as écrit : « Il ne s'agit pas vraiment de trier des groupes,
puis des sous-groupes, puis des sous-sous-groupes etc. mais plus de connaître
l'optimum. » ; je comprends tout à fait, et je pense que tous les conseils qu'on
t'a donné ensuite sont de bons conseils.

Je me rappelles aussi d'une méthode pour résoudre un système d'inéquations
prenant en compte plusieurs contraintes, afin de minimiser ou optimiser une
fonction (méthode du « simplexe », améliorée ensuite par la méthode du « dual
simplexe »). Peut-être cela peut servir pour le problème en cours ? Mais ne m'en
demande pas plus, car c'est trop vieux et je ne serai pas capable de le refaire !

================================================================

D'autre part, j'aimerai que tu me dises ce que tu penses du fichier Excel 2003
ci-joint. C'est une méthode avec 4 tris successifs, qui ne nécessite pas
d'appliquer un système de notation.

Aucune macro VBA ; il s'agit seulement d'utiliser Données / Trier... sur les bonnes
lignes, selon le bon critère (en commençant par le plus important), et dans le bon
sens (croissant ou décroissant).

Tu verras que les commentaires sous le tableau aident beaucoup !

----------------------------------------------------------------------------------------------------------------

La feuille T0 est une simple reprise de ton tableau initial.

Les 4 feuilles suivantes sont nommées T1 à T4 ; prends Tn au choix comme :
a) Tableau numéro n
b) Tri n° n

----------------------------------------------------------------------------------------------------------------

T1 : Tri 1, de tout le tableau (lignes 3 à 16), selon les prix, par ordre croissant (>) ;
supposition : tu ne t'intéresse qu'aux items dont le prix est inférieur à 15 € ;
donc pour le tri suivant, tu ne t'occupera plus QUE des lignes 3 à 10.

NB  Tu as donc éliminé 6 items (lignes 11 à 16) car ils sont trop chers.

----------------------------------------------------------------------------------------------------------------

T2 : Tri 2, pour les lignes 3 à 10, selon kcal et par ordre décroissant (<) ;
supposition : tu ne t'intéresse qu'aux items dont kcal >= 320 kilocalories ;
donc pour le tri suivant, tu ne t'occupera plus QUE des lignes 3 à 7.

NB  Tu as donc éliminé 3 items (lignes 8 à 10) car trop peu énergétiques.

----------------------------------------------------------------------------------------------------------------

T3 : Tri 3, pour les lignes 3 à 7, selon le poids et par ordre croissant (>) ;
supposition : tu ne t'intéresse qu'aux items dont le poids est < à 70 grammes ;
donc pour le tri suivant, tu ne t'occupera plus QUE des lignes 3 à 5.

NB  Tu as donc éliminé 2 items (lignes 6 et 7) car trop lourds.

----------------------------------------------------------------------------------------------------------------

T4 : Tri 4, pour les lignes 3 à 5, selon le volume et par ordre croissant (>) ;
supposition : tu ne t'intéresse qu'aux items dont volume est < à 50 unités ;
donc il reste l'item 1.

NB  Tu as donc éliminé 2 items (lignes 4 et 5).

----------------------------------------------------------------------------------------------------------------

Conclusion : tu as trouvé l'item optimum qui satisfait tous tes critères !  😊

Sac randonnée ; fichier Excel 2003
 
Messages postés
51
Date d'inscription
mercredi 9 juin 2010
Statut
Membre
Dernière intervention
11 février 2017

Pardon, j'aurais du poster un fichier exemple tout de suite. le voici : https://www.cjoint.com/c/FDrtUD8wdJo

@albkan. Ton exemple fait encore un tri en cascade et non une optimisation de plusieurs critères. enfin.. si je l'ai bien compris.

@JvDo. OK pour la méthode proposé. Merci.
Utilisateur anonyme
Oui, tu as bien compris : mon exemple fait bien un tri en cascade
puisqu'il y a 4 tris successifs. Mais j'avais espéré que tu aimerais,
de la façon dont je l'ai fait, et avec en plus les commentaires bien
pratiques situés sous le tableau.

Note que si on automatise les suppositions (dont leur sens, tel que
prix inférieur à 15 €), il devrait être possible de tout automatiser
pour faire automatiquement les 4 tris successifs ; cela sur une
seule feuille T0. Ainsi, le tableau T0 deviendrait directement celui
de la feuille T4. Ce n'est qu'une remarque, et non une suggestion,
puisque cette approche ne te convient pas.

Je ne t"en parlerai donc pas plus, et pour ma part, j'arrête le suivi :
les autres intervenants peuvent mieux t'aider que moi au sujet de
l'optimisation de critères.  😊
Messages postés
51
Date d'inscription
mercredi 9 juin 2010
Statut
Membre
Dernière intervention
11 février 2017
> Utilisateur anonyme
Merci à toi, tes commentaires m'ont aidé :)
a+
Messages postés
1978
Date d'inscription
mercredi 27 juillet 2005
Statut
Membre
Dernière intervention
28 septembre 2020
844 >
Messages postés
51
Date d'inscription
mercredi 9 juin 2010
Statut
Membre
Dernière intervention
11 février 2017

Bonsoir,

ton exemple est plus simple que ce que tu décrivais auparavant.

Il te suffit de diviser les kcal produit par la somme du poids du produit et du poids de l'eau à rajouter.
Tu obtiens un ratio qui te permet de trier des produits.

Pour les desserts, les amandes et barres chocolatées sortent en tête.
Pour les plats, le Beef Jerky - Boeuf séché se détache nettement des 2 muesli.

Si tu avais des contraintes supplémentaires sur le poids, le volume et le prix, il faudrait une autre approche..... et un autre fichier de données.

Cordialement

Bonjour Ipalgo,

1) Pour le nombre de lignes, Excel 2003 en a plus de 65000 et
Excel 2007 (ou ultérieur) plus de 1 000 000 ! Alors oui, et sans
aucun doute, il peut facilement accepter 50 lignes seulement !

----------------------------------------------------------------------------

2) Pour chaque item (de chaque ligne), on peut effectivement
indiquer les caractéristiques que tu as indiquées.

On aurait alors le tableau suivant (présentation simplifiée) :

      A       B            C      D       E       F
1
2          Produit     kcal   vol   poids   prix
3          item 1
4          item 2
5          item 3
6          item 4
7

Remarque : pour une présentation plus aérée, je préfère laisser
vides la colonne A et la ligne 1 (marges).

----------------------------------------------------------------------------

3) Oui, il sera possible de voir, parmi 50 produits (ou même plus
    si tu le souhaites), quels sont les 10 produits qui ont :

    a) le plus petit poids ; puis pour ceux-ci :
        b) le plus d'énergie ; puis pour ceux-ci :
            c) le moins de volume ; puis pour ceux-ci :
                d) le prix le plus bas

    pour un sac de randonnée optimisé qui augmentera le plaisir
    de longues balades en pleine nature !  😊