Résoudre une équation à itération avec et sans VBA sur Excel

 Récemment, je me suis heurter à un problème bien fastidieux, je devais résoudre une équation à itérations sur Excel, et cela, de manière automatique. Bien entendu , j'ai mis beaucoup de temps à trouver un moyen de le faire et j'ai décidé de vous partager cette petite technique, ce petit Tips.

Sommaire :

1. Résoudre l'équation manuellement avec GoalSeek ( ou valeur cible )
2. Résoudre l'équation automatiquement en VBA

1 Résoudre l'équation manuellement avec GoalSeek ( ou valeur cible )

Premièrement, je souhaite vous montrer quelle est l'équation que nous allons chercher à résoudre :

Avec :

Facteur de friction de Darcy-Weisbach, f

Hauteur de rugosité, ε

Diamètre hydraulique, Dh

Nombre de Reynolds, Re

VOUS DEVEZ FAIRE ATTENTION A POSITIONNER AU MÊME ENDROIT QUE MOI VOS CELLULE SINON, LE TUTO EST CADUC.

Je vais travailler avec les valeurs suivantes pour la rugosité, le diamètre hydraulique et le nombre de Reynolds:

Une fois cette partie éditée , nous allons créer un second tableau qui comportera le facteur de friction , les deux membre de l'équation ainsi qu'une case Objectif...

Pour la valeur ( temporaire ) du facteur de friction , vous devez mettre une petite valeur positive , j'ai choisi ici 0.01

Ensuite on complète les membres de gauche et de droite de l'équation , voici ici les formules:

Membre gauche : =1/RACINE(D11)

Membre droit : =-2*LOG10(D5/(3,7*D6)+2,51/(D7*RACINE(D11)))

Maintenant , avant de pouvoir utiliser GoalSeek , il faut définir un objectif. Le role de GoalSeek sera de rendre cette valeur la plus proche de 0 possible. Pour éviter que l'objectif soit une valeur négatif et que GoalSeek ne fonctionne pas , nous allons calculer le carré de la différence des deux membres de l'équation.

Formule de l'objectif : =(D12-D13)^2

Une fois toutes ces étapes réalisées , nous sommes prêts à utiliser GoalSeek.

Pour aller sur GoalSeek , vous devez aller dans Données puis Analyse de Scénario et choisir Valeur Cible 

Cela ouvrira une fenêtre pop-up. Voici un screen de comment la remplir :

Nous pouvons l'analyser ensemble. La cellule que Excel va modifier correspond au facteur de friction pour obtenir la valeur la plus proche de 0 dans la cellule objectif.

Une fois que vous avez appuyer sur OK, voici la fenêtre qui apparait :

Excel a donc trouvé une solution à 10*-3 près , ce qui est un niveau de précision largement suffisant.

Et vous avez le facteur de friction correspondant au valeur que vous avez entré.

L'inconvénient de cette méthode et qu' a chaque fois que vous modifié une valeur tel que la rugosité ou encore le diamètre, vous devez répéter l'opération pour aller chercher GoalSeek.

Heureusement, cela peut être corrigé et être fait automatiquement avec Excel VBA.

2. Résoudre l'équation automatiquement en VBA

Pour recalculer automatiquement le facteur de friction des lors que vous modifié une valeur, vous devez utiliser Excel VBA.

Pour pouvoir l'utiliser , il faut enregistrer votre fichier Excel en .xlsm afin qu'il prenne en charge les macros.

Pour cela , vous devez créer un module VBA. Vous devez aller dans la barre de Recherche , taper VBA puis cliquer sur Afficher les macros

Une fenêtre pop-up va ensuite apparaitre, sur cette fenêtre vous entrez le nom de votre macro : j'ai mis GoalSeek.

Une fois le nom entré, vous devriez être sur cette page :

 ### Si vous n'arriver pas à créer des macros ( bouton grisé ) faites [Ctrl] + [F11] ###

Dans votre module , vous devez taper le code suivant :

Sub goalseek()

Application.ScreenUpdating = False

Static isWorking As Boolean

If Round(Range("D14").Value, 4) <> 0 And Not isWorking Then

    isWorking = True

    Range("D11").Value = 0.01

    Range("D14").GoalSeek Goal:=0, ChangingCell:=Range("D11")

    isWorking = False

End If

Application.ScreenUpdating = True

End Sub

Une fois le code tapé dans le module, il faut ajouter le code suivant dans la feuille de travail ( Feuil1 ):

Private Sub Worksheet_Calculate()

goalseek

End Sub

Je ne vais pas m'attarder sur le fonctionnement du code mais maintenant , votre équation à itération se résout automatiquement.

Voici une petite vidéo qui vous montre tout et qui prouve mes dires:

Voila , ce petit tuto est terminé, si jamais vous avez un problème avec n'hésitez pas à me contacter via ma page de contact , ou a me laisser un petit mot dans les commentaires.

@+