Itération d'une formule?

Bonjour à toutes et tous,

J'ai beau chercher sur les forums je ne trouve tjrs pas la solution à mon problème. J'imagine que c'est un problème d'itération mais à part le re-calcul de feuille etc etc je ne comprends vraiment pas grands chose donc merci de votre aide.

Mon problème (je le simplifie) est:

J'ai un nombre A qui me permet via une formule + d'autres valeurs d'obtenir un nombre B. Ce nombre B me donne, via une autre formule + d'autres valeurs un nombre C. J'aimerais réussir à ce qu'Excel calcule tout seul le nombre C en faisant varier A de 0 à 90. Ensuite j'aimerais qu'il me donne la valeur max de C (fastoche) et la valeur de A correspondant (moins fastoche).

Le problème c'est que les "autres valeurs" changent selon un pas de 1 allant de 1 à 8760... Alors j'ai réussi à réaliser tout ça en faisant une sorte de tableau croisé mais ça m'affiche TOUS les résultats et ça alourdit énormément le fichier.

Quelqu'un-a-t-il une idée?

Merci

oui

joins un fichier exemple

Salut xavier1616 et le forum

Personnellement, ça me rappelle l'utilisation du solveur

Aide Excel

À propos du Solveur

Le Solveur fait partie d'une série de commandes aussi appelées outils d'analyses de simulation (analyse de scénarios : procédure consistant à modifier les valeurs des cellules afin de voir de quelle manière elles affectent le résultat des formules de la feuille de calcul.

Par exemple, faire varier le taux d’intérêt utilisé dans un tableau d’amortissement pour calculer le montant des paiements constitue une analyse de scénarios.). Le Solveur vous permet de trouver une valeur optimale pour une formule (formule : suite de valeurs, références de cellule, noms, fonctions ou opérateurs dans une cellule permettant de générer une nouvelle valeur. Une formule commence toujours par le signe égal (=).) dans une cellule, appelée cellule cible, d'une feuille de calcul. Il fonctionne avec un groupe de cellules associées, soit directement, soit indirectement, à la formule de la cellule cible. Il adapte les valeurs des cellules que vous souhaitez modifier, appelées cellules variables, pour fournir le résultat spécifié à partir de la formule de la cellule cible. Vous pouvez poser des contraintes (contraintes : limites imposées à un problème du Solveur. Vous pouvez appliquer des contraintes à des cellules ajustables (modifiables), à la cellule cible ou à d’autres cellules liées directement ou indirectement à la cellule cible.) pour limiter les valeurs susceptibles d'être utilisées comme modèles par le Solveur et ces contraintes peuvent se référer à d'autres cellules affectant la formule de la cellule cible.

Utilisez le Solveur pour déterminer la valeur minimale ou maximale d'une cellule en modifiant d'autres cellules. Par exemple, vous pouvez modifier le montant de votre budget publicitaire prévu et voir l'incidence sur le bénéfice prévu.

A+

Bonjour,

Je joins un fichier xlsx exemple "Essai1".

J'ai beaucoup simplifié le problème.

J'aimerais savoir comment trouver la valeur optimale (donc max) de la moyenne de G en faisant varier Béta de 0 à 90 ( avec un pas de 1 ).

Merci d'avance.

Beta n'intervient dans aucun calcul ???

jmd a écrit :

Beta n'intervient dans aucun calcul ???

Beta (i.e. la colonne E) intervient dans les calculs de G (i.e. la colonne F). Mais chacune de ces lignes sembles indépendantes. Il n'a donc pas une valeur cible mais 24 valeurs cibles (E2:E25).

Béta intervient dans le calcul de G.

Pour être plus clair, mon réel problème c'est de construire un outil qui me permette de calculer l'angle d'inclinaison (béta) d'un capteur solaire afin d'obtenir un ensoleillement (G) optimal sur une période déterminée (dans l'exemple de 1 à 24h mais dans mon vrai problème c'est 1 à 8760 soit un an). Donc j'ai bien 8760 lignes totalement indépendantes (car je fais intervenir des valeurs d'azimut, hauteur de soleil, orientation, rayonnement direct, diffus etc etc, valeurs qui ne sont jamais identiques sur les 8760 lignes (la course du soleil ainsi que son ensoleillement varient toute l'année à chaque heure).

Bref, l'idée, c'est de faire varier béta (l'angle d'inclinaison du capteur) de 0 à 90° (pas de 1°) et de regarder la moyenne sur un an de l'ensoleillement reçu. Donc trouver Béta optimal.

Est-ce que l'utilisation d'un solveur (dont je ne connais pas le fonctionnement encore) peut m'être utile.

xavier1616 a écrit :

Béta intervient dans le calcul de G.

Pour être plus clair, mon réel problème c'est de construire un outil qui me permette de calculer l'angle d'inclinaison (béta) d'un capteur solaire afin d'obtenir un ensoleillement (G) optimal sur une période déterminée (dans l'exemple de 1 à 24h mais dans mon vrai problème c'est 1 à 8760 soit un an). Donc j'ai bien 8760 lignes totalement indépendantes (car je fais intervenir des valeurs d'azimut, hauteur de soleil, orientation, rayonnement direct, diffus etc etc, valeurs qui ne sont jamais identiques sur les 8760 lignes (la course du soleil ainsi que son ensoleillement varient toute l'année à chaque heure).

Bref, l'idée, c'est de faire varier béta (l'angle d'inclinaison du capteur) de 0 à 90° (pas de 1°) et de regarder la moyenne sur un an de l'ensoleillement reçu. Donc trouver Béta optimal.

Est-ce que l'utilisation d'un solveur (dont je ne connais pas le fonctionnement encore) peut m'être utile.

Le solveur est extrêmement utile lorsque tu as une seule valeur cible. Dans ton cas, ça devient assez laborieux.

Il faudrait que tu simplifies ton problème. Je ne pense pas que ça ne porte pas à conséquence vu qu'au bout du compte, tu cherches à trouver une moyenne.

Tu pourrais faire une moyenne des autres paramètres sur une nouvelle ligne et ensuite utiliser le solveur sur cette ligne là.

Tu pourrais également vérifier les heures de lever et coucher du soleil et enlever les heures de nuit : quelque soit l’inclinaison de ton panneau, je ne sais pas si tu auras beaucoup de soleil à 3 heures du matin.

Enfin, revérifie l’équation de ta colonne F ; En l’état actuel des choses, un E maximum sera toujours la solution pour avoir un F maximum ; donc, si tu ne changes pas l’équation de ta colonne F, je peux dors et déjà te dire que ta solution sera E = 90.

Le concept de « Garbage in, garbage out » me vient à l’esprit : le résultat de ton modèle ne sera jamais meilleur que ton modèle lui-même.

Merci pour ta réponse olwagner2, je vais sûrement suivre tes conseils (pour infos les équations du fichier exemple sont bidons j'ai mis des formules simples, les vraies équations sont beaucoup plus longues et Béta optimal devrait se trouver dans les 30 ou 40° je pense.

xavier1616 a écrit :

Béta intervient dans le calcul de G.

Pour être plus clair, mon réel problème c'est de construire un outil qui me permette de calculer l'angle d'inclinaison (béta) d'un capteur solaire afin d'obtenir un ensoleillement (G) optimal sur une période déterminée (dans l'exemple de 1 à 24h mais dans mon vrai problème c'est 1 à 8760 soit un an). Donc j'ai bien 8760 lignes totalement indépendantes (car je fais intervenir des valeurs d'azimut, hauteur de soleil, orientation, rayonnement direct, diffus etc etc, valeurs qui ne sont jamais identiques sur les 8760 lignes (la course du soleil ainsi que son ensoleillement varient toute l'année à chaque heure).

Bref, l'idée, c'est de faire varier béta (l'angle d'inclinaison du capteur) de 0 à 90° (pas de 1°) et de regarder la moyenne sur un an de l'ensoleillement reçu. Donc trouver Béta optimal.

Est-ce que l'utilisation d'un solveur (dont je ne connais pas le fonctionnement encore) peut m'être utile.

Bonsoir,

Dans la mesure où vos lignes sont indépendantes, je suis tenté de dire qu'une simple boucle pourrait vous calculer votre optimum sans simplifier votre problème et ses informations.

Serait-il possible d'envoyer le fichier réel ou simplement un petit bout (si les données sont confidentielles) afin d'évaluer le temps de calcul avec les vraies formules (beaucoup plus longues).

En balayant uniquement de [0,90] à [30,40], on pourrait déjà réduire considérablement le temps nécessaire à la boucle.

Cordialement,

Thomas.

Bonjour,

Alors, oui je vous envoie le fichier réel (taillé un max car il faisait 6 Mo). Rien n'est confidentiel mais merci de ne pas diffuser sur le net svp.

J'ai un petit peu avancé dans le problème. J'ai laissé tomber le coup de faire une moyenne des colonnes etc etc ça donne des résultats erronés. Mais j'ai réussi grâce au solveur à calculer le béta optimal d'une ligne. (il me semble que dans mon fichier il y a une ligne ou deux de déjà faite. Le résultat est logique (64° d'inclinaison en hiver, le soleil étant très bas en hiver, et 20° environ en été).

Cependant, je ne sais pas comment rapidement demander à Excel de faire cette manip' pour les 8760 lignes.

PS: même si le béta optimal annuel est estimé aux alentours de 30 ou 40, je souhaite réaliser les calculs de 0 à 90°.

Est-ce que quelqu'un sait s'il est possible d'intégrer le solveur à une macro ou à un programme sous VBA?

Bonsoir Xavier 1616,

Concernant le fichier, mon seul but est de vous aider, je n'ai aucun intérêt à le diffuser sur la toile, soyez donc sans crainte.

Concernant votre problème, je peux vous proposer une solution par macro trouvant l'optimum dans un délai tout à fait correct (environ une seconde).

Comme avancé dans mon précédent post, je n'ai pas utilisé de solveur mais une simple boucle calculant les 91 cas possibles (angle bêta de 0 à 90°), et déterminant à la fin la valeur optimale de G et bien entendu, la valeur de bêta associée.

L'adaptation au fichier réel n'est pas compliqué, mais je peux vous la faire si vous le souhaitez. Vu la taille du fichier, contactez-moi par MP afin que je vous donne mon adresse e-mail et que vous puissiez me l'envoyer.

Cordialement,

Fatos

Bonjour,

Une deuxième version pour corriger deux coquilles :

• Dans le nom du graphique : la courbe est uniquement sur le mois de janvier et non l'année
• une déclaration de variable inutile dans la macro VBA

Bonne fin de semaine à tout le monde,

Fatos.

Bonjour

Merci Fatos, votre aide m'a beaucoup servi. Du coup j'ai repris votre code et je l'ai modifié (non sans mal vu mon niveau...) pour l'appliquer au fichier réel. ça marche impec. J'ai supprimé la variable g inutile

Donc merci beaucoup.

A présent, j'aimerais savoir une petite chose (je garde la même conversation même si c'est un autre problème).

J'ai créé 1 onglet par ville (donc 4) et un 5e onglet "simulation" où je ferai apparaître une macro regroupant tout.

Mon but, c'est de mettre un choix de ville (validation de données/liste etc). dans une case (la C3) et d'ajouter au code une fonction "SI".

Comme ça, cela me permettrait d'aller chercher seulement dans l'onglet de la ville choisie le Béta optimal calculé et de l'afficher en H3.

J'ai essayé d'écrire quelque chose mais ne connaissant pas la syntaxe (j'ai pourtant cherché sur le net et testé plusieurs trucs) j'ai une erreur de compilation au niveau de ma fct IF.

Mon code avec "SI":

Sub CALCUL()

Dim b As Integer

Dim n As Integer

Application.ScreenUpdating = False

If ThisWorkbook.Sheets(5).Cells(3, 3).Value = "CARPENTRAS" Then n = 1

ElseIf ThisWorkbook.Sheets(5).Cells(3, 3).Value = "LA ROCHELLE" Then n = 2

ElseIf ThisWorkbook.Sheets(5).Cells(3, 3).Value = "TRAPPES" Then n = 3

Else: n = 4

End If

For b = 0 To 90

ThisWorkbook.Sheets(n).Cells(2, 20).Value = b

ThisWorkbook.Sheets(n).Cells(2 + b, 25).Value = ThisWorkbook.Sheets(n).Cells(2, 23).Value

Next

ThisWorkbook.Sheets(5).Cells(3, 8 ).Value = ThisWorkbook.Sheets(n).Cells(3, 28).Value

Application.ScreenUpdating = True

End Sub

Vous voyez des erreurs ?

Bonjour,

Dans le code en soi, je ne vois pas d'erreur à priori.

Vous souhaitez donc réaliser le calcul uniquement de la ville choisie dans votre liste déroulante, c'est bien ça ?

Il est plus facile de déboguer avec le support. Donc si vous pouviez envoyer votre fichier. Merci d'avance.

Cordialement,

Fatos