rapporter à des cellules antécédentes et avec conditions (?)

[msco]

Bonjour,

Déjà, mes excuses, je ne suis clairement pas à l'aise avec excel et mon inaptitude en fera soupirer plus d'un-e. Ainsi j'ai besoin de votre aide et votre patience.

Le contexte: je dois créer un graphique pour la pluviométrie. Le pluviomètre nous indique à chaque basculement combien d'eau est tombée et nous avons uniquement les heures de basculement. Grâce au temps il nous est possible de savoir combien il a plu entre telle et telle heure (t2-t1). Maintenant les basculements n'arrivent pas de façon régulière. La formule doit prendre en compte le fait que la valeur du basculement doit être supérieure à 0 (ça j'arrive), se reporter à la date du-dit basculement et à la date du basculement le précédant pour en faire la différence de temps (impliquant que la valeur à la colonne des basculements doit être > à 0 et qu'il doit se reporter à la colonne des dates mais la même ligne).

Je l'aurai fait à la main mais j'ai plus de 160k lignes et pas assez de patience et lorsqu'il s'agit d'Excel (ou autre truc info sauf word, pp, solitaire) je suis débile.

Merci d'avance pour votre aide et un bon dimanche à tous!

 

[Modeste]

Re : rapporter à des cellules antécédentes et avec conditions (?)

Bonjour msco et bienvenue

Le mieux serait (comme le suggère la Charte) de joindre un bout de fichier, avec des données représentatives de ce qui figure dans le fichier original (c'est-à-dire les différents cas de figures qui peuvent se présenter).
Tu ajouterais "à la main" ou dans les explications ici, le résultat attendu pour ces différents cas.

En attendant de te relire, je retourne à ma partie de solitaire

 

[msco]

Re : rapporter à des cellules antécédentes et avec conditions (?)

Bonjour Modeste et merci pour ta réponse!

Il sera effectivement plus facile de comprendre où je veux en venir avec une petite illustration du problème! Merci pour ta remarque.
Et donc ci-joint un exemple de mon fichier. J'espère avoir correctement formulé la chose et que ce ne sera pas trop compliqué pour vous non plus!

Bonne partie de solitaire, bonne lecture et merci!

 

[Modeste]

Re : rapporter à des cellules antécédentes et avec conditions (?)

Re-bonjour,

Il y a bien une possibilité par formule matricielle ... Le problème de ce type de formules est qu'elles sont gourmandes en ressources (et donc ralentissent sensiblement les calculs sur de gros volumes de données!)

Teste donc ce qui suit en E6:

=SI(B6=0;"";B6/(A6-INDEX($A$1:$A6;MAX(6;SI($B$5:$B5>0;LIGNE($B$5:$B5))))))

Une fois la formule collée en E6, n'appuie pas sur 'Enter', mais sur Ctrl+Shift+Enter (tu devrais voir des accolades autour de la formule)

Recopie ensuite la formule vers le bas en testant sur des plages de 500 lignes, puis 5000, 20000, etc. pour voir si ça devient "infernal" à partir d'un certain point ... et dis-nous.

Les autres solutions seraient soit d'utiliser une colonne "intermédiaire" (sans certitude à ce stade), soit une macro.
Précise aussi à quelle fréquence doit se faire la mise à jour du fichier.

 

[msco]

Re : rapporter à des cellules antécédentes et avec conditions (?)

Re-bonjour!

Alors merci beaucoup, c'était magnifique, jusqu'à ce qu'on atteigne les 80k (mi-chemin, caramba!). Ensuite message de mémoire insuffisante et crash d'Excel.
Le fichier n'aura pas besoin d'être mis à jour. Les mesures ont été faites sur une certaine période et ne devraient pas à être modifiées ou complétées.
Les solutions que tu proposes seraient donc macro ou colonne intermédiaire? Laquelle serait la plus simple (tant pour toi que pour moi, je ne veux pas te gâcher ton dimanche) et comment m'y prendre?

Mille mercis pour ton aide!

 

[Modeste]

Re : rapporter à des cellules antécédentes et avec conditions (?)

je ne veux pas te gâcher ton dimanche

Comment la chose se pourrait-elle? Je navigue du jardin ensoleillé à Excel (puis retour) ... c'est donc un beau dimanche!

Un essai par macro (avec des tableaux en mémoire, sinon ça "moulinerait" pendant un temps certain, ici aussi!)
- Ouvre ton fichier (j'ai testé ici avec 70000 lignes, ça a été rapide, mais restons sages: teste d'abord avec un nombre de lignes à peu près équivalent)
- clique droit sur l'onglet de la feuille concernée > Visualiser le code
- dans la fenêtre qui s'ouvre, colle le code ci-dessous
- en restant n'importe où dans le code en question, appuie sur la touche F5

Sub foutuePluie()
Dim tabRes()
tablo = [A6].Resize(Cells(Rows.Count, 1).End(xlUp).Row - 5, 2)
nbLig = Cells(Rows.Count, 1).End(xlUp).Row - 5
ReDim tabRes(nbLig, 0)
first = True
For lig = 1 To UBound(tablo)
    If tablo(lig, 2) > 0 Then
        For recul = lig - 1 To 1 Step -1
            If first Then tabRes(lig - 1, 0) = tablo(lig, 2) / (tablo(lig, 1) - tablo(1, 1)): first = False: Exit For
            If tablo(recul, 2) > 0 Then tabRes(lig - 1, 0) = tablo(lig, 2) / (tablo(lig, 1) - tablo(recul, 1)): Exit For
        Next recul
    Else
        tabRes(lig - 1, 0) = 0
    End If
Next lig
Cells(6, 5).Resize(UBound(tabRes) + 1, 1) = tabRes
End Sub

La première date doit être en A6 et les mesures de pluviométrie dans la colonne voisine, comme dans ton exemple. Les résultats seront insérés en colonne E. On modifiera, si besoin.

 

[msco]

Re : rapporter à des cellules antécédentes et avec conditions (?)

Attention à ne pas te faire rincer maintenant que ça s'est couvert!

Ton code marche comme un charme, sur les 165380 lignes!!! Et je dois dire que ta petite touche, "foutuepluie", a été remarquée et je ne peux pas te dire à quelle point c'est correct!!

Je te remercie pour tout le temps et l'effort que tu as mis pour résoudre mon problème. Tu sauras que tu auras pris part à un projet de semestre de 2ème année d'école d'ing. sur les traitements des eaux de chaussées d'un pont en suisse qui sera utilisée plus tard pour l'étude officielle! (un peu de contexte et de feedback me fait généralement plaisir quand j'aide quelqu'un je sais pas pour toi.)
Tu as ma gratitude et reconnaissance éternelle et celle de mes collègues très certainement.
Belle continuation à toi et s'il ne pleut pas encore chez toi, puisses-tu profiter encore un peu du soleil et du jardin!

 

[Modeste]

Re : rapporter à des cellules antécédentes et avec conditions (?)

Re,

Arfff foutuepluie c'est juste un réflexe belgo-belge ... bien qu'ici le soleil luise (encore) avec générosité

Merci pour ce retour