Comment écrêter valeur

Question

Je cherche quelqu'un qui pourrait me donner la formule ou commande pour compléter mon tableau excel! Alors, j'espère me faire comprendre : Je suis en attente d'activer une batterie virtuelle ( SVP, pas de débat sur les batteries virtuelles). J'alimente en données tous les jours ma production solaire (colonne B) et la consommation électrique de mon domicile (colonne C) ce qu'en découle un remplissage d'une batterie virtuelle (colonne D). Mon fournisseur de batterie virtuelle propose plusieurs niveaux de stockage 100,300, 600 kWh., etc. Mon but est de remplir les colonnes F à J pour choisir l'offre la plus adaptée à mon profil. Dans la colonne F (100 kWh), je voudrais le report du remplissage de batterie virtuelle (colonne D) sans dépasser 100 (maximum du remplissage), mais, lors d'un jour en déficits d'ensoleillement, me soustraire la valeur négative inscrite en rouge. Et le même résonnement pour les différentes capacités de batteries. Merci pour votre aide Je poste une copie d'écran, car je ne peux pas encore mettre de fichiers dans mon post...

saboh12617 · Accepted Answer

La formule que je vous ai donné est correcte, mais je crois que vous avez fait une erreur dans les références : =MAX(0; MIN(100; E3+$D4)) Réfère à E3 = charge retenue du jour précédent, càd cellule immédiatement AU-DESSUS de la cellule courante, et D4 réfère à la charge/décharge du jour courant, càd cellule immédiatement à gauche de la cellule courante. Je vous assure que l'erreur doit se trouver dans votre recopie car pour ma part j'ai testé la formule avant de vous l'envoyer, avec vos valeurs, et je suis retombé tout juste. Il n'y a pas a se compliquer la tete avec des SI ou autres conditions, c'est un calcul simple. Edit : je viens de retester la formule, et je remarque que sur votre fichier drive les colonnes sont décalées de 1 vers la droite… La formule devient donc, En F3 =MAX(0;MIN(100;F2+E3))...

Klin89 · Accepted Answer

Re Cyril24,

Pour ne pas en rester là,

une macro toute simple qui garantit de ne pas dépasser les seuils indiqués et de descendre sous le seuil de 0.

Option Explicit
Sub test()
    Dim rng As Range, r As Range, e, col, cumul As Double
    With Sheets("Feuil1")
        Set rng = .Range("e2:e" & .Range("e" & .Rows.Count).End(xlUp).Row)
    End With
    col = Array(Array(100, 2), Array(300, 3), Array(600, 4), Array(900, 5), Array(1200, 6))
    For Each e In col
        cumul = 0
        For Each r In rng
            cumul = cumul + r.Value
            ' Si le cumul dépasse le seuil indique, on le fixe au seuil indique
            If cumul > e(0) Then
                cumul = e(0)
            End If

            ' Si le cumul est négatif, on le fixe à 0
            If cumul < 0 Then
                cumul = 0
            End If
            ' Placer le résultat dans la colonne correspondante
            r(, e(1)).Value = cumul
        Next
    Next
End Sub

Edit : la ligne vide sous l'en-tête a été supprimée dans le fichier joint.

klin89

9batterie-seuil.xlsm (26.08 Ko)

saboh12617 · Answer

Il manque des infos, notamment la charge initiale, pour la 1e valeur de la colonne. Ensuite votre demande m'échappe un peu, j'ai peut etre mal compris mais sur Internet il est expliqué que votre batterie répartie l'énergie sur les usages, pour ne pas avoir à tout stocker justement. Donc je ne comprends pas trop d'utiliser la colonne D plutot que E pour le calcul. Ensuite vous indiquez "me soustraire la valeur en rouge", ok mais la soustraire où ? A la cellule au dessus ? Je suis parti sur cette hypothèse. La formule correspondant à votre demande, en F4, serait =SI($D4&gt;0;MIN(100;$D4);F3-$E4) Étirable vers la droite (il faut remplacer 100) ou bien si vous avez juste 100 en F1 avec un format personnalisé, vous pouvez le mettre dans le MIN. En F3 cela dépend de la charge initiale. Mais bon, je n'y connais pas grand-chose et je me trompe probablement, mais votre approche me semble un peu "simple" pour le choix d'une batterie virtuelle. Pour moi c'est celle d'une batterie standard...

saboh12617 · Answer

Merci pour votre retour, effectivement j'avais mal compris le principe. Le qualificatif "virtuel" prend tout son sens maintenant. Cependant je ne peux m'empecher de penser que seule la colonne E est utile au calcul. Je m'explique : vous indiquez que "seule votre sur-consommation est facturée". Donc, pour moi, il suffit de regarder le bilan en fin de journée, et de le sommer à celui de la journée précédente. On écrète à la capacité de la batterie, le surplus est perdu. Ensuite, d'après votre dernier retour, vous souhaitez garder et additionner les charges négatives (journées de surconsommation), j'imagine pour harmoniser vos valeurs avec les journées de surproduction. Cela dépend en fait de si le fournisseur calcule à l'heure, la journée, le mois… Dans tous les cas, en utilisant simplement la colonne E, j'écrirai en F4 à étirer vers le bas : =MIN(100; $E4+F3)) Et en F3, 0. Cette formule devrait reprendre la charge du jour précédent, et ajouter le bilan du jour courant. En respectant la capacité maximale. Le dollar en E est pour étirer vers la droite. J'espère que cela correspond davantage...

saboh12617 · Answer

Oui c'est bien ce que je pensais c'est le calcul tout à fqit basique que vous cherchez au final, mais l'explication était trop confuse. Un exemple vaut mille mots. Ci-après la formule mathématique classique pour garder un résultat entre deux bornes (en E4, à étendre vers le bas): =MAX(0; MIN(100; E3+$D4)) Vous n'aviez jusqu'à présent pas mentionné la borne minimale, meme plutôt l'inverse puisque vous vouliez "soustraire la sous-production". La formule est assez claire, 0 et 100 sont les bornes, et vous ajoutez la prod/déficit indiquée en colonne D à la charge de la veille...

Klin89 · Answer

En résumé, d'après ce que je comprends : Tant que les valeurs ajoutées en colonne E n'atteignent pas le seuil de 100, elles sont cumulées. Une fois ce seuil de 100 atteint en colonne F, ce seuil reste bloqué à ce niveau, sauf en cas d'apparition d'une valeur négative en colonne E qui fera diminuer à nouveau ce seuil. Pas testé les solutions de saboh12617 😔, joins un fichier. Intéressant comme exercice maintenant que j'ai compris la problématique, j'ai mis un like 🥰 vu que je suis nul en formules. Une solution en VBA 😍, je suis preneur. klin89...

saboh12617 · Answer

Je vous en prie.

Si par ailleurs vous voulez une formule unique étirable, toujours en se basant sur le fichier drive, vous pouvez remplacer le "100" dans la formule par

GAUCHE(M258;NBCAR(M258)-NBCAR(" kwh"))      ou       GAUCHE(M258;NBCAR(M258)-4)

Soit la formule finale en F3:

=MAX(0;MIN(GAUCHE(F$1;NBCAR(F$1)-4); $F2+E3))

étirable vers le bas et la droite.

Klin89 · Answer

Re Cyril24,

À partir de la colonne 6, tu peux ajouter autant de colonnes que nécessaire pour le seuil des kWh.

Option Explicit
Sub test1()
    Dim i As Long, j As Long, cumul As Double, kwh As Long, spacePos As Byte
    With Sheets("Feuil1")

        ' Boucle pour parcourir les colonnes à partir de la 6ème colonne (colonne F)
        ' jusqu'à la dernière colonne non vide dans la première ligne.
        For j = 6 To .Cells(1, .Columns.Count).End(xlToLeft).Column

            ' Initialisation du cumul à 0 pour chaque colonne
            cumul = 0

            ' Trouver la position de l'espace dans la cellule (ligne 1, colonne j)
            spacePos = InStr(.Cells(1, j), " ")

            ' Extraire la valeur numérique avant l'espace (le nombre de kWh)
            kwh = Left(.Cells(1, j), spacePos - 1)

            ' Boucle pour parcourir les lignes de la colonne E (colonne 5),
            ' à partir de la ligne 2 jusqu'à la dernière ligne non vide
            For i = 2 To .Range("e" & .Rows.Count).End(xlUp).Row

                ' Ajouter la valeur de la colonne E à chaque ligne au cumul
                cumul = cumul + .Cells(i, 5).Value

                ' Si le cumul dépasse le seuil (kwh), on le fixe au seuil
                If cumul > kwh Then
                    cumul = kwh
                End If

                ' Si le cumul est négatif, on le fixe à 0
                If cumul < 0 Then
                    cumul = 0
                End If
                ' Place la valeur du cumul dans la cellule correspondante (ligne i, colonne j)
                .Cells(i, j).Value = cumul
            Next
        Next
    End With
End Sub

klin89