Bonjour Gigi, Didier, le forum,

Construction du tableau par formules et sans recours à l'itération

Dans ta feuille de calcul "Gestion froid - chaud", quand tu changes la valeur de la cellule C20, cela fait claculer les formules

I5 et I6, puis les formules C2 à C5 et les formules C7 à C9.

Ces formules dépendent aussi de données dans les cellules F2, F3, F7 à F9.

Je ne parlerai pas des autres cellules, elles ne varient pas lors du changement de la valeur en C20.

Pour la construction du tableau que je place en feuille "Feuil1"

les valeurs calculées :

En colonne J en parant de la ligne 3, j'ai reporté la valeur de la cellule C20, celle-ci allant de 1 à 365

Dans les colonnes suivantes, les valeurs des champs calculés :

Pour les formules du basic, elles reprennent en gros les formules de la feuille, je n'ai adapté que les références, les cellules sont à copier en ligne 4

Après recopie vers le bas des formules, tu auras toutes les valeurs de ton tableau.

Toujours sur la même feuille,

les données fixes

Dans les colonnes suivantes les valeurs des champs calculés :

Les formules respectives à ces champs, à mettre en ligne 4

Ces formules récupèrent quand le jour est identique à la valeur de la cellule W, la valeur de la cellule correspondant de la feuille "Gestion Froid - chaud", dans le cas contraire elle conserve leur propre valeur.

Par contre, tu as peut-être remarqué, il y a un décalage sur les 3ème et 4ème formules. En fait cela est normal, comme je te l'indiquai dans mon premier post, les formules itératoires sont calculées différement des autres formules. Les formules "standard" sont calculées normalement avant les formules itératoires, donc si l'on veut garder la correspondance de ligne il faut un décalage. Les formules en C7, C8, C9 de la feuille "Gestion froid - chaud" ne sont pas des formules itératoires, mais comme elles en dépendent, elles seront calculées après les formules itératoires. L'ordre de calcul de formules, la priorité à une importance capitale.

La première partie de la formule SI('Gestion froid - chaud'!C$20=1;0; sert d'initialisation de la valeur de la cellule. Quand on joue avec l'itération et les formules itératoires, il est bon d'avoir une phase d'initialisation, elle permet de remettre en ordre les valeurs en cas d'une erreur.

Met la valeur de la cellule C20 de la feuille "Gestion Froid - chaud" à 1, et ensuite recopier les formules vers le bas.

Tu n'auras que des 0, et tu es encore loin d'avoir le tableau complet.

Clique sur ton bouton d'incrémentation jusqu'à la valeur 10, revient sur la feuille "Feuil1", normalement tu ne devrais avoir que les quatre premières lignes de ton tableau avec les valeurs mémorisées.

Pour remplir le tableau complétement jusqu'à la dernière ligne, tu as deux possibilités :

cliquer, cliquer, cliquer et recliquer......

ou utiliser l'option du nb maximal d'itérations.

On va utiliser les options d'itération. Va dans les options d'excel pour changer la valeur du nombre maximal d'itérations, tu dois indiquer une valeur supérieure à 369 (Excel permet de mettre une valeur entre 1 et 32767).

En changeant cette valeur, tu demandes à excel de boucler x fois le calcul de la feuille, quand une "événement calculate" ce produit.

Mais pour que le calcul boucle de lui même il y encore quelque changement à faire.

Supprime tes deux boutons, il ne vont plus te servir, même en VBA.

On va utiliser la la cellule C20 pour faire le compteur de la boucle, comme les formules dépendent déjà de cette valeur, cela évitera de tout modifier.

On va mettre dans cette cellule la formule ci-dessous

Comme tu peux le voir, je fais appel au contenu de la cellule B23, cette cellule permettra de passer à passer du mode paramètres, mode qui te permettra de modifier les données fixes, et le mode de calcul du tableau.

Pour ce faire, créer une liste de validation sur la cellule B23. (je ne peux t'expliquer dans les détails la manip, la version 2003 étant différent à la verison 2010)

Dans la liste de validation, j'ai indiqué ceci

le plus important étant que le texte du deuxième argument de la liste de validation soit identique au texte du comparateur dans la formule.

Si tu as suivi toute les manip, passe au mode "Calcul du Tableau", normalement le tableau doit être complet sur la feuille "Feuil1"

Malheureusement du dois avoir des erreurs sur la feuille "Gestion froid - chaud", il faut corriger quelques formules, pour soigner la présentation.

Voilà on a fini avec la partie du tableau construit avec les formules itéraotires.

Pour être exhaustif avec les options d'itération, excel dans sa boucle itération, fait ceci

Il fait ceci jusqu'à à arriver au terme du Nb maximal d'itérations indiqués dans les options, ou si toutes les valeurs sont identiques (à la tolérance près) à l'images de départ. Cette tolérance est la valeur indiquée dans le champ écart maixal dans les options de calcul.

Encore une petite chose, A ce que j'ai pû voir, sur les nouvelles versions d'office 2007 et 2010 sur PC, et la version 2011 sur mac, sur la version d'office 2008 sur mac, l'itération ne fonctionne pas.

Microsoft a supprimé dans la boucle d'itération l'acutalisation de l'écran, on ne peut plus observer les modifications des valeurs dans les cellules. Snif. Mais le calcul est plus rapide.

Tu peux retrouver sur le site de didier un fichier à télécharger sur la réalisation de grille de sudoku à l'aide de formules et des options d'itération, ou j'utilise ce que je t'ai expliqué.

Voici le code VBA

Sub GiGi()
'Déclaration de variable
Dim I As Integer 'cette variable sera utilisée pour la boucle
Dim TempOpInc As Single

'Les formules de la feuille ne seront pas recalculés pendant la création du tableau
Application.EnableEvents = False

'on ecrit les données fixes dans les cellules de la feuille Feuil1
With Worksheets("Gestion froid - chaud")
    Worksheets("Feuil1").Range("AG4") = .Range("F2")
    Worksheets("Feuil1").Range("AG5") = .Range("F3")

    Worksheets("Feuil1").Range("AG7") = .Range("F7")
    Worksheets("Feuil1").Range("AG8") = .Range("F8")
    Worksheets("Feuil1").Range("AG9") = .Range("F9")
End With

'on peut passer au calcul des valeurs du tableau par VBA
With Worksheets("Feuil1")
    'on écrit la valeur 1 dans la cellule AI3
    .Range("AI3") = 1
   
    'Creation de la boucle pour calculer les X lignes
    For I = 4 To 367
        .Cells(I, 35) = I - 2
        .Cells(I, 36) = 0.8 * .Cells(I - 1, 36) + 0.2 * .Cells(I, 3)
        .Cells(I, 37) = .Cells(I - 1, 36)
        .Cells(I, 38) = .Cells(I, 3)
        .Cells(I, 39) = .Cells(I - 1, 38)
       
        TempOpInc = 0.33 * .Cells(I, 36) + 18.8
        'Test MaxC1 (h)
        If .Range("AG5") > TempOpInc + .Range("AG7") Then
            .Cells(I, 40) = .Range("AG5")
        Else
            .Cells(I, 40) = TempOpInc + .Range("AG7")
        End If
       
        'Test MaxC2 (h)
        If .Range("AG5") > TempOpInc + .Range("AG8") Then
            .Cells(I, 41) = .Range("AG5")
        Else
            .Cells(I, 41) = TempOpInc + .Range("AG8")
        End If
       
        'Test MaxC3 (h)
        If .Range("AG5") > TempOpInc + .Range("AG9") Then
            .Cells(I, 42) = .Range("AG5")
        Else
            .Cells(I, 42) = TempOpInc + .Range("AG9")
        End If
    Next I
End With
'Les formules de la feuille de calcul se recalcul
Application.EnableEvents = True
End Sub

J'ai mis des instructions Application.EnableEvents = xxx (en début et fin de code), pour accélérer la création du tableau, je te déconseille d'utiliser cette instruction dans tes propres codes. 

Je ne vais pas m'étendre sur les fonctions ou instructions de ce code, nous avons tous nos manières de faire du code.

Si tu essayes ces trois possibilités tu veras que la plus rapide sera le tableau réalisé en VBA, la plus simple ou la plus abordable pour la première solution, et celle de l'itération te fait passer pour un monstre.

Voilà, j'attends tes remarques sur ce que je viens de t'expliquer. J'entends de ta part que cet échange soit construtif, si tu veux des éclairssiements j'essayerai d'y apporter de la clarté.

@+Jean-Marie