Ecretage et valeur max variable

[LaurentOASIIS]

Bonjour,
Je sèche sur un problème d’écrêtage de valeur.
Je dois écrêter des séries de valeurs, pour limiter la valeur maximum. Pour ce faire je dispose de 7500 unités que je peux répartir comme bon me semble. Ces 7500 unités me sont disponibles tous les jours.
Dans la pièce jointe, il y a une série pour une journée en question, j’ai bien essayé plusieurs solutions mais en vain , si quelqu’un sait me dépanner je lui en serais très reconnaissant. Cette opération doit être répétée pour les 365 jours de notre chère année, mais, une fois la formule en main je pense être capable de l’étirer sur toute l’année.

Encore merci.

 

[CISCO]

Re : Ecretage et valeur max variable

Bonsoir

Pourrais tu nous expliquer un peu ce que tu veux, à quoi correspond chacune de tes colonnes... ? Etant un peu du domaine, je devine ce que tu veux, mais, dans le doute, ne voulant pas chercher dans une mauvaise voie pour rien...

@plus

 

[CISCO]

Re : Ecretage et valeur max variable

Bonsoir

Je ne sais pas si j'ai bien compris ta demande... A tout hasard, une proposition en pièce jointe avec une colonne et une recherche de valeur cible.

En G7, la somme à atteindre. En G8, la puisssance (écrire n'importe quelle valeur). Faire "Données" --> "Analyse de scénarios" --> "Valeur cible". Dans la fenêtre qui s'ouvre, dans Cellule à définir, écrire G7. Dans Valeur à atteindre, écrire 7500. Et dans Cellule à modifier, écrire G8 (la puissance à optimiser).

Et hop, dans cet exemple, excel propose une puissance proche de 5400.

@ plus

 

[job75]

Re : Ecretage et valeur max variable

Bonsoir LaurentOASIIS,

Ce genre de problème peut se régler par la commande Valeur cible.

Comme il faut l'exécuter pour chaque jour, le plus simple est de faire une boucle dans une macro :

Sub ValeurCible()
Dim i&
Application.ScreenUpdating = False
i = 10
[H:H].ClearContents 'RAZ
Range("I34:M" & Rows.Count).ClearContents 'RAZ
While Cells(i, 1) <> ""
  Cells(i + 23, "I").GoalSeek Goal:=7500, ChangingCell:=Cells(i + 23, "H")
  If Cells(i + 23, "H") < 0 Then Cells(i + 23, "H") = Application.Max(Cells(i, "F").Resize(23))
  i = i + 24
  If Cells(i, 1) <> "" Then Cells(i - 24, 9).Resize(24, 5).Copy Cells(i, "I")
Wend
End Sub

Voir le fichier joint.

Nota 1 : je n'ai pas beaucoup testé, voyez sur votre fichier réel.

Nota 2 : attention, il ne faut pas de signe $ dans les formules de la plage (copiée) I10:I33.

Edit : hello CISCO, je ne t'avais pas vu

A+

 

[job75]

Re : Ecretage et valeur max variable

Re,

En fait j'ai mal percuté sur l'exemple du jour 22/9.

Quand la valeur en colonne H devient négative, il faut la ramener à zéro, on écrète tout :

Sub ValeurCible()
Dim i&
Application.ScreenUpdating = False
i = 10
[H:H].ClearContents 'RAZ
Range("I34:M" & Rows.Count).ClearContents 'RAZ
While Cells(i, 1) <> ""
  Cells(i + 23, "I").GoalSeek Goal:=7500, ChangingCell:=Cells(i + 23, "H")
  If Cells(i + 23, "H") < 0 Then Cells(i + 23, "H") = 0
  i = i + 24
  If Cells(i, 1) <> "" Then Cells(i - 24, 9).Resize(24, 5).Copy Cells(i, "I")
Wend
End Sub

Fichier (2).

Bonne nuit et A+

 

[job75]

Re : Ecretage et valeur max variable

Bonjour le fil, le forum,

Ceci est plus propre :

Sub ValeurCible()
Dim i&
Application.ScreenUpdating = False
[H:H].ClearContents 'RAZ
Range("I34:M" & Rows.Count).ClearContents 'RAZ
For i = 10 To Range("A" & Rows.Count).End(xlUp).Row Step 24
  If i > 10 Then Cells(i - 24, "I").Resize(24, 5).Copy Cells(i, "I")
  Cells(i + 23, "H") = 0
  If Cells(i + 23, "I") > 7500 Then _
    Cells(i + 23, "I").GoalSeek Goal:=7500, ChangingCell:=Cells(i + 23, "H")
Next
End Sub

Par ailleurs le traitement du 1er jour s'effectue en 2,6 millièmes de seconde sur Win XP - Excel 2003.

Sur la même base, pour 365 jours, la durée d'exécution sera donc de 0,95 seconde.

Fichier (3).

A+

 

[job75]

Re : Ecretage et valeur max variable

Re,

Concernant le graphique, il n'est pas question d'en faire un pour chaque jour.

Voyez le fichier (4) avec la date en K7 et les noms définis Colonne1 et Colonne2 :

=DECALER(Feuil1!$F$1;EQUIV(Feuil1!$K$7;Feuil1!$A:$A;0)+5;;15)

=DECALER(Feuil1!$J$1;EQUIV(Feuil1!$K$7;Feuil1!$A:$A;0)+5;;15)

Ils permettent de définir les 2 séries du graphique.

Edit : pour faire bon poids j'ai ajouté cette macro dans le code de la feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$K$7" Then Exit Sub
Dim i As Variant
i = Application.Match([K7], [A:A], 0)
If IsError(i) Then Exit Sub
Application.Goto Cells(i, "A"), True
With Me.ChartObjects(1)
  .Top = Cells(i + 6, "K").Top
  .Left = Cells(i + 6, "K").Left
End With
End Sub

A+

 

[LaurentOASIIS]

Re : Ecretage et valeur max variable

Bonjour tout le monde,
Déjà merci de vous êtes penché sur mon souci, votre aide m'est précieuse.
@Job75, ta macro correspond bien à ce que je vaux faire, j'essaye de la transformé pour la faire fonctionner sur une année entière avec trois types d'heures, creuses, pleines et pointes. (tout cela me remets à mon niveau en macro ! je suis nul ! )
Malheureusement je sèche encore.
Du coup je te glisse un fichier avec un jour d'hiver suivi d'un jour d'été (je peux pas mettre l'année entière le fichier est trop lourd) , si jamais tu as le temps d'y jeter un œil .
Si pas le temps ou pas envie, pas de soucis, je me débrouillerais avec ce que tu m'as déjà fournis

Merci A+

ou sinon un lien vers le fichier complet :
https://copy.com/OQwMJ8k6uMRz

 

[job75]

Re : Ecretage et valeur max variable

Re,

Impossible de travailler sans comprendre

Ajoutez une colonne qui contiendra les maxima d'écrétage (ils seront déterminés par la commande Valeur cible).

Et dans les autres colonnes mettez les formules nécessaires en fonction de ces maxima.

On ne peut pas inventer ces formules à votre place.

A+

 

[CISCO]

Re : Ecretage et valeur max variable

Bonjour à tous, bonjour job75, bonjour LaurentOasiis

Laurent, est-ce que tu as jeté un oeil sur ma proposition ? Est-ce que j'étais parti dans la bonne direction, ou non ?

Pour optimiser ce genre de problème, il nous faudrait même avoir les tarifs, les pénalités en cas de dépassement...

@ plus

 

[job75]

Re : Ecretage et valeur max variable

Re, hello CISCO,

Bon je pense avoir compris quelles formules il faut utiliser.

Voyez le fichier (zippé) joint et les formules en colonnes D E F (toujours sans signe $).

La macro du bouton :

Sub ValeurCible()
Dim i&
Application.ScreenUpdating = False
Range("D34:G" & Rows.Count).ClearContents 'RAZ
For i = 10 To Range("A" & Rows.Count).End(xlUp).Row Step 24
  Cells(10, "D").Resize(24, 3).Copy Cells(i, "D")
  Cells(i, "G") = 0
  If Cells(i, "D") > 7500 Then _
    Cells(i, "D").GoalSeek Goal:=7500, ChangingCell:=Cells(i, "G")
Next
End Sub

Sur Win XP - Excel 2003 elle s'exécute en 3,7 secondes pour tout le tableau.

Nota : si vous voulez un graphique comme précédemment il faudra ajouter une ou 2 colonnes pour les valeurs plafonnées.

A+

 

[job75]

Re : Ecretage et valeur max variable

Re,

Sauf erreur les PTE se limitent à la plage 18:00-21:00 et les HP à la plage 06:00-21:00.

On peut donc retirer les formules à l'extérieur de ces plages.

La durée d'exécution se réduit alors à 1,2 seconde.

Fichier (2).

A+

 

[job75]

Re : Ecretage et valeur max variable

Re,

Pour finir, j'ai ajouté le même graphique avec la même logique qu'au post #7.

La colonne H (kWh écrétés) définit la 2ème série.

Fichier (3).

A+

 

[LaurentOASIIS]

Re : Ecretage et valeur max variable

Re,
@Cisco, Oui c'est ce qu'il me fallait, la difficulté suivante résidant de le fait de répéter le démarche pour chaque jour de l'année.

Sinon, merci beaucoup pour votre aide, @job75 je vais me servir de ton dernier post pour finaliser cette histoire de stockage de Glace !

A+

 

[job75]

Re : Ecretage et valeur max variable

Re,

Je viens de voir qu"en mars les "HP" vont jusqu'à 21:00, je corrige les fichiers des posts #12 et #13.

A+