Itération d'optimisation

**Schnd** · 23/08/2016, 02h24

Bonjour!

Je suis étudiant en école d'ingénieur en stage en Nouvelle Zélande sur un problème de mécanique des fluides modélisé sous Excel 2016 (Mac) et je rencontre un problème.

Pour une géométrie de tube fixe, je rentre en entrée un débit, je récupère la valeur des pertes de charge. J'aimerais mettre la main sur le débit à appliquer pour avoir une perte de charge imposée.
En théorie il suffirait d'inverser les matrices de mon calcul initial, malheureusement le calcul n'est pas aussi simple et je n'y arrive pas.. J'ai donc choisi d'utiliser le solveur.
J'ai choisi de dupliquer ma feuille de calcul qui me donne les pertes de charge en fonction du débit. Dans une case j'ai mis la valeur que je souhaite obtenir. Je crée une case "Erreur" qui fait la différence entre la valeur souhaitée et celle obtenue.
Dans le solveur, je lui demande de changer la valeur d'une seule case (le débit) afin que la case "Erreur" soit inférieure à 0,01.

C'est très fonctionnel et j'obtiens, pour chaque valeur de perte de charge, le débit escompté.

Maintenant, j'aimerais que ce calcul se fasse automatiquement, sans que je n'aie à rentrer la valeur de la perte de charge à chaque fois.
Est ce que quelqu'un a idée de la façon de procéder ? Ca m'aiderait beaucoup

J'ai donc une colonne en entrée, qu'il faudra injecter case par case dans mon calculateur, qui s'inverse grâce à l'utilisation du solveur en optimisant toujours la même case, et obtenir une colonne en sortie.
J'espère avoir été assez clair, n'hésitez pas à demander s'il y a des points obscurs, j'essaierais de détailler.

Merci d'avance,
Vincent

**halaster08** · 23/08/2016, 09h12

Bonjour,

Connais-tu l'enregistreur de macro?
C'est très pratique ça te permet de récuperer le code d'une action que tu fais "à la main".
En l'occurence ça pourrais te permettre de récupérer le code du solveur.
Ensuite il suffit d'adapter un peu et de rajouter une boucle.

**Schnd** · 23/08/2016, 12h38

Oui j'ai essayé et ça n'a pas enregistré les infos relatives au solveur...
J'ai donc juste retrouvé les opérations de changement de feuilles, de sélection de cases mais pas d'info relatives au solveur. C'est pour ça que quand je vois des lignes de code pour d'autres problèmes je n'arrive pas à comparer parce que je ne les comprends pas trop trop ...

**Theze** · 23/08/2016, 12h58

Bonjour,

Voici deux fonctions. L'une retourne le débit en litres/heure quand J lui est passé en argument (en mmCE) et l'autre retourne J quand le débit lui est passé en argument (en litres/heure). Pour ces deux fonctions, le fluide est de l'eau claire et les natures de tubes sont : Cuivre, Acier et Per (polyéthylène réticulé) pour la rugosité et ces tubes sont circulaires. Adapte les valeurs dans la proc de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
 
Sub Test()
 
    Dim Diametre As Single
    Dim TempEau As Single
    Dim NatTube As String
    Dim J As Single
    Dim Debit As Single
 
    NatTube = "Cuivre" 'pour connaître la rugosité du tube
    TempEau = 55 'en °C
    Diametre = 26 'en mm
 
    J = 231 'en mm de colonne d'eau
    MsgBox RetourneDebit(J, NatTube, Diametre, TempEau) 'en litres/heure
 
    Debit = 5000 'en litres/heure
    MsgBox RetournePerte(Debit, NatTube, Diametre, TempEau) 'en mmCE
 
End Sub
 
Function RetournePerte(Debit As Single, _
                       NatureTube As String, _
                       Diametre As Single, _
                       Temp_Eau As Single) As Double
 
    Dim A As Single '3 coefficients intermédiaires qui permettent d'éviter le recours à la fonction de Colebrook
    Dim B As Single
    Dim C As Single
    Dim Cpe As Single 'coefficient de perte de charge
    Dim J As Single
    Dim Rug As Single
    Dim M_Eau As Single
    Dim Re As Single
    Dim V As Single
 
    Const Pi As Single = 3.14159265358979
 
    Select Case NatureTube
 
        Case "Cuivre": Rug = 0.0000015
        Case "Acier": Rug = 0.00005
        Case "Per": Rug = 0.000003
 
    End Select
 
    M_Eau = 0.0000000008 * Temp_Eau ^ 5 + _
            -0.000000303 * Temp_Eau ^ 4 + _
            0.000053678 * Temp_Eau ^ 3 + _
            -0.00780474 * Temp_Eau ^ 2 + _
            0.0523705799 * Temp_Eau + _
            999.83932
 
        V = ((Debit / 3600) * 0.001) / ((Diametre / 2 * 0.001) ^ 2 * Pi)
 
        Re = (V * (Diametre * 0.001)) / (-2.2183992 * 10 ^ -12 * Temp_Eau ^ 3 + _
              0.00051252585 * 10 ^ -6 * Temp_Eau ^ 2 - _
              0.043254681 * 10 ^ -6 * Temp_Eau + 1.6993718 * 10 ^ -6)
 
        Select Case Re
 
            Case Is <= 2320 'régime laminaire
                Cpe = 64 / Re
 
            Case Is <= 3158 + 48000 * Rug / Diametre 'régime intermédiaire
                Cpe = -1.292 * 10 - 2 + 8.88 * 10 - 5 * Re ^ 0.8
 
            Case Else 'régime turbulent
                A = -2 * Log((Rug / (Diametre / 1000)) / 3.71 + 12 / Re) / Log(10)
                B = -2 * Log((Rug / (Diametre / 1000)) / 3.71 + 2.51 * A / Re) / Log(10)
                C = -2 * Log((Rug / (Diametre / 1000)) / 3.71 + 2.51 * B / Re) / Log(10)
                Cpe = (A - ((A - B) ^ 2) / (A + C - (2 * B))) ^ -2
 
        End Select
 
        J = Cpe / (Diametre / 1000) * V ^ 2 / (2 * 9.81) * 1000
 
    RetournePerte = J
 
End Function
 
Function RetourneDebit(Max_mmCE As Single, _
                       NatureTube As String, _
                       Diametre As Single, _
                       Temp_Eau As Single) As Double
 
    Dim A As Single '3 coefficients intermédiaires qui permettent d'éviter le recours à la fonction de Colebrook
    Dim B As Single
    Dim C As Single
    Dim Cpe As Single 'coefficient de perte de charge
    Dim J As Single
    Dim Rug As Single
    Dim D As Single
    Dim M_Eau As Single
    Dim Re As Single
    Dim V As Single
 
    Const Pi As Single = 3.14159265358979
 
    Select Case NatureTube
 
        Case "Cuivre": Rug = 0.0000015
        Case "Acier": Rug = 0.00005
        Case "Per": Rug = 0.000003
 
    End Select
 
    M_Eau = 0.0000000008 * Temp_Eau ^ 5 + _
            -0.000000303 * Temp_Eau ^ 4 + _
            0.000053678 * Temp_Eau ^ 3 + _
            -0.00780474 * Temp_Eau ^ 2 + _
            0.0523705799 * Temp_Eau + _
            999.83932
 
    Do
 
        D = D + 0.1
 
        V = ((D / 3600) * 0.001) / ((Diametre / 2 * 0.001) ^ 2 * Pi)
 
        Re = (V * (Diametre * 0.001)) / (-2.2183992 * 10 ^ -12 * Temp_Eau ^ 3 + _
              0.00051252585 * 10 ^ -6 * Temp_Eau ^ 2 - _
              0.043254681 * 10 ^ -6 * Temp_Eau + 1.6993718 * 10 ^ -6)
 
        Select Case Re
 
            Case Is <= 2320 'régime laminaire
                Cpe = 64 / Re
 
            Case Is <= 3158 + 48000 * Rug / Diametre 'régime intermédiaire
                Cpe = -1.292 * 10 - 2 + 8.88 * 10 - 5 * Re ^ 0.8
 
            Case Else 'régime turbulent
                A = -2 * Log((Rug / (Diametre / 1000)) / 3.71 + 12 / Re) / Log(10)
                B = -2 * Log((Rug / (Diametre / 1000)) / 3.71 + 2.51 * A / Re) / Log(10)
                C = -2 * Log((Rug / (Diametre / 1000)) / 3.71 + 2.51 * B / Re) / Log(10)
                Cpe = (A - ((A - B) ^ 2) / (A + C - (2 * B))) ^ -2
 
        End Select
 
        J = Cpe / (Diametre / 1000) * V ^ 2 / (2 * 9.81) * 1000
 
    Loop While J <= Max_mmCE
 
    RetourneDebit = D
 
End Function

**halaster08** · 23/08/2016, 13h52

Oui j'ai essayé et ça n'a pas enregistré les infos relatives au solveur...

Etrange ...

Voici ce que me donne l'enregistreur de macro:
Nom : exemple.jpg
Affichages : 810
Taille : 51,1 Ko

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Range("D1").GoalSeek Goal:=1, ChangingCell:=Range("A1")

**Schnd** · 23/08/2016, 14h10

Ah y'a du fluxeux ici

Merci d'avoir pris le temps de répondre, même si la solution ne m'aide pas trop. Le code proposé a l'air de fournir une réponse pour un tube quelconque, et mon tube est loin d'être quelconque.
Je m'intéresse à un capillaire dont les dimensions sont connues grâce à une IRM. J'ai donc en entrée une longueur de tube, et en 86 points de ce tube j'ai la surface mouillée et le périmètre. Comme j'impose le débit, je calcule la vitesse en les 86 points de ce tube, puis les nombres de Reynolds associés, et donc le coefficient de friction local (je suis tout le temps en laminaire). Enfin, en additionnant les 85 coefficients (85 portions de tube pour 86 points) les uns aux autres je récupère ma perte de charge dûe à la friction. Je fais pareil pour les pertes de charge singulières liées aux variations de section (élargissement/rétrécissement brusque) et j'ai donc ma perte de charge totale.
Quand j'utilise le solveur, c'est pour déterminer le débit tel que, pour cette même géométrie, la somme des pertes de charge soit égale à la valeur que je souhaite.

Halaster08, pour ta macro, juste en tapant ça ça te donne l'antécédent ? Il te le calcule dans la case A1, mais comment faire pour faire varier la valeur objectif (tu as marqué 1 dans ton exemple, est ce que je peux lui demander de prendre les valeurs stockées dans un colonne ?) afin d'avoir une colonne en sortie ?

Merci en tout cas, on progresse

**halaster08** · 23/08/2016, 14h32

Envoyé par Schnd

Halaster08, pour ta macro, juste en tapant ça ça te donne l'antécédent ? Il te le calcule dans la case A1, mais comment faire pour faire varier la valeur objectif (tu as marqué 1 dans ton exemple, est ce que je peux lui demander de prendre les valeurs stockées dans un colonne ?) afin d'avoir une colonne en sortie ?

Bien sur que oui tu peux le faire, il suffit de faire une boucle par exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
For i = 1 To 10
    Cells(i, 4).GoalSeek Goal:=Cells(i, 5), ChangingCell:=Cells(i, 1)
Next i

**eriiic** · 23/08/2016, 19h00

Bonjour,

dans l'aide vba tu as toutes les fonctions du solveur en recherchant Solver.
Comme apparemment tu as défini manuellement les plages et contraintes c'est surtout SolverSolve (lancer le solveur) qui t'intéresse.
Dans l'aide de cette fonction tu as 'comment activer le solveur pour le vba', et un exemple d'utilisation.
En résumé en vba (comme on ne sait pas ce que tu sais faire):
1) remplir les conditions initiales
2) SolverSolve, contrôler si réponse ok
3) récupérer le résultat
retour au 1) tant qu'il en reste

Doc plus complètes sur le solveur chez l'éditeur : http://www.solver.com/fls-register?d...tion=node/6935
eric

**Schnd** · 24/08/2016, 23h49

Ok je vais regarder tout ça merci de votre aide

**Schnd** · 26/08/2016, 06h23

Bonjour!

Des nouvelles! Bon il s'avère que je n'ai pas du tout réussi à incorporer le solveur dans mes macros.. En revanche, j'ai trouvé une solution alternative (je m'y suis pris calmement et j'ai inversé mes matrices), et qui marche. C'est peut être moins propre qu'une jolie macro, mais le résultat y est

Merci du temps que vous avez pu consacrer à m'aider !

**halaster08** · 26/08/2016, 08h51

Puis-je savoir quel est le problème avec la méthode que je t'avais proposé?

Itération d'optimisation

Macros et VBA Excel

Discussions similaires

Partager

Partager