Transposer somme.si.ens avec critère sur date et heures en vba ?

bonjour,

j'aurais besoin de votre aide pour transposer cette formule excel en code vba si cela est possible ?

avec peut-être

Code:

---

Application.WorksheetFunction.SumIfs

---

? mais pour la suite ?!... ou bien avec

Code:

---

FormulaR1C1

---

?

Code:

---

=SOMME.SI.ENS($AF$5:AF5;$B$5:B5;">="&TEMPS(HEURE(B5);MINUTE(B5);SECONDE(B5))-TEMPS(0;0;10)+DATE(ANNEE(B5);MOIS(B5);JOUR(B5)))

---

merci par avance

Il faut éviter d'essayer de reproduire en code VBA ce qu'on fait avec des fonctions Excel. Ce n'est pas du tout la même philosophie.

Ici, en l’occurrence, le plus simple est de te faire une boucle For...to avec un If.
Tu gagneras en temps de développement sans perdre en temps d'exécution.
Il est inutile de se compliquer la vie en essayant de faire ça en une seule ligne. Les contraintes du VBA ne sont pas celles des formules dans la feuille.

Salut.

Tu vas avoir deux avis pour le prix d'un... Sauf qu'ils seront contradictoires ;).

Perso, je privilégie de loin l'utilisation de fonctions telles que sumifs à des boucles (voir ma signature à ce propos ^^). Pourquoi? Parce que les boucles, mine de rien, c'est de la vraie programmation, et cela demande donc vite un certain bagage en langage de programmation, en gestion des variables et autres "babioles".

Alors que la transposition d'une formule Excel en VBA est quelque chose de "relativement" simple, et que tu peux en fait travailler sur plusieurs axes en fonction de ce que tu veux réaliser, puisque tu peux:
écrire la formule dans une cellule (puis éventuellement faire un copier/coller spécial valeurs en VBA;
utiliser la plupart des formules via Application.WorksheetFunctions;
utiliser Evaluate("...");

Chacune des possibilités répondant à un besoin précis. Pourrais-tu préciser ton besoin?

Bonjour,

je me pose en conciliateur :ptdr:

je suis entièrement d'accord sur le fait qu'il est préférable de passer par les formules Excel dans ta macro, plutôt qu'une boucle
la formule sera moins lourde.. elle est native dans Excel
la boucle va consommer des ressources, prendre du temps à s'effectuer etc...


MAIS !!

quand je regarde ta formule , je me dis qu'il faudra peut être :

- écrire la formule en VBA (Application.Worksheetfunction.sumifs etc..)
- réaliser une boucle pour remplir tes cellules avec cette boucle, dans la mesure où ta plage d'addition va évoluer au fur et à mesure des lignes

(on est d'accord que c'est pour automatiser ton fichier qui fait la somme sur les 10 dernières secondes ?) :P


Tu vois, rien n'est tranché en VBA, on pèse toujours le pour et le contre
c'est pour ça que tu auras parfois plusieurs avis différents, mais qui donneront toujours un résultat (que la macro soit optimisée ou non)

le principal : c'est qu'on est tout les 3 (et d'autres contributeurs !) là pour t'aider
charge à toi de nous apporter de plus amples explications sur la formule que tu veux faire :

- sur quelle plage ?
- le comportement attendu ?
- etc..

Maitriser les subtilités des boucles While (et autres du même genre) demande en effet une certaine pratique.

En revanche, comprendre le principe d'une boucle For...to est à la portée du premier venu en 10 min (je parle d'expérience et j'avais 14 ans quand j'ai passé ces 10 min).

Mais il est vrai que mon avis n'est pas objectif. J'avoue que je préfère utiliser des fonctions simples, basiques, les briques Légo de la programmation, plutôt que des "macro-fonctions" qui font plus de choses mais qu'il est plus difficile de comprendre parfaitement et de maitriser les effets. Il m'arrive même parfois (rarement) de préférer une boucle à un Find :oops: (mais je me soigne ;) ).

C'est une question d'habitude et de sensibilité.

On pourrait lancer un débat, ça animerait un peu le forum...

Du moment que cela ne parte pas en troll, de bonnes choses pourraient en sortir avec exemples, vécu des uns et des autres, etc...

Tant que cela se fait dans l'esprit des forums, pourquoi pas? ;)

Mais cela nous éloigne quelque peu de la question initiale... :oops:

Citation:

---

Envoyé par Menhir

[...]
En revanche, comprendre le principe d'une boucle For...to est à la portée du premier venu en 10 min (je parle d'expérience et j'avais 14 ans quand j'ai passé ces 10 min).
[...]

---

Oui, mais savoir déterminer le début, et surtout la fin de la boucle, ça peut déjà être plus comique, si on reste en programmation pure => il y a matière à débat... :mrgreen:

merci pour vos contributions...et voici en gros le but de la formule

le fichier est une extraction de mesures sur 15 jours faites à peu près à la même heure tout au long de la journée par tranche de 1 secondes à 4 minutes donc il peut y avoir énormément de données ( 230 000 lignes pour 15 jours)
colonne B : heure (le format d'extraction est du type (25/03/2015 08:10:05) )
B4 : ligne de la 1ère heures (heures minutes secondes) pris en compte
AF5 : 1ère données binaire
colonne AF : données binaire 0 ou 1

Code:

---

=SOMME.SI.ENS($AF$4:AF5;$B$4:B5;">="&TEMPS(HEURE(B5);MINUTE(B5);SECONDE(B5))-TEMPS(0;0;10)+DATE(ANNEE(B5);MOIS(B5);JOUR(B5)))

---

colonne AG où je met la formule : calculer sur 10 secondes glissantes précédent l'heure correspondant à la ligne sur laquelle on se trouve la somme des données de la colonne AF


la formule est très bien mais sur les données d'une journée. Sur plus de données il n'y a plus assez de ressources sur le PC pour faire tous les calculs et tout plante.

j'espérais que la transposition de cette formule en code vba utiliserait moins de ressources ou par le biais de pause peut-être tous les x calculs pourrait faire en sorte que tout se passe bien jusqu'au bout ?!


le fichier test : Pièce jointe 176416

merci

le fichier sur lequel j'ai intégré la formule

Code:

---

somme.si.ens

---

Pièce jointe 176420 que j'aimerais passé sous vba si cela peu aider à mon problème de ressources et de plantage

Citation:

---

Envoyé par novice06

la formule est très bien mais sur les données d'une journée. Sur plus de données il n'y a plus assez de ressources sur le PC pour faire tous les calculs et tout plante.

---

La fonction SOMME.SI.ENS est vraiment très puissante mais elle est aussi TRES gourmande en ressource, comme les formules matricielle.

L'avantage de passer par une macro en VBA, c'est qu'on peut enchaîner les calculs les uns à la suite des autres au lieu qu'ils soient faits en parallèle comme dans une feuille de calcul. Ca évite de surcharger la mémoire. En somme, au lieu de faire quelques milliers de calculs en même temps, tu les fais les uns après les autres.

Cela dit, maintenant, la question ne se pose plus : si tu as plusieurs calculs, tu vas devoir un peu toucher aux boucles VBA. :mrgreen:
Au moins un For...to.

voici mon code qui fonctionne mais une fois le process terminé je m'aperçois d'une chose en regardant la formule qui s'est inscrit dans la cellule : les plages sont figés avec des $

Code:

---

=SOMME.SI.ENS($AF$4:$AF5;$B$4:$B$5;">="&TEMPS(HEURE($B$5);MINUTE($B$5);SECONDE($B$5))-TEMPS(0;0;10)+DATE(ANNEE($B$5);MOIS($B$5);JOUR($B$5)))

---

alors que je voudrais la voir apparaitre comme ceci pour qu'une personne qui rajoute des données intermédiaires par exemple sans avoir a relancé le code puisse simplement tirer la formule vers le bas ?!

Code:

---

=SOMME.SI.ENS($AF$4:$AF5;$B$4:$B5;">="&TEMPS(HEURE($B5);MINUTE($B5);SECONDE($B5))-TEMPS(0;0;10)+DATE(ANNEE($B5);MOIS($B5);JOUR($B5)))

---

merci par avance

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

sub glissant()   Dim ShtName As String, NameFich As String, chemin As String Dim a As Long, derligne As Long     chemin = Workbooks(ActiveWorkbook.Name).Path NameFich = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)   ActiveWorkbook.SaveAs Filename:= _ chemin & "\" & NameFich & ".xlsm", FileFormat _ :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False     derligne = Sheets(ShtName).Range("K65536").End(xlDown).Row dercolonn = Range("IV1").End(xlToLeft).Column   choixsec = InputBox("Combien de secondes glissante (1 à 59) ? ", "Calcul glissant")   For a = 5 To derligne If Cells(a, 2) = "" Then Exit Sub   Cells(a, dercolonn + 1).FormulaR1C1 = _ "=SUMIFS(R4C" & dercolonn & ":RC" & dercolonn & ",R4C2:R" & a & "C2,"">=""&TIME(HOUR(R" & a & "C2),MINUTE(R" & a & "C2),SECOND(R" & a & "C2))-TIME(0,0," & choixsec & ")+DATE(YEAR(R" & a & "C2),MONTH(R" & a & "C2),DAY(R" & a & "C2)))"   Next a   MsgBox "Calcul effectué avec succès !", vbOKOnly ActiveWorkbook.Save   End Sub

---

Si ta macro ne sert qu'à mettre les formules dans les cellules, tu ne gagnes rien en terme de ressources.*
Si tu veux gagner quelque chose, il faut que ce soit la macro qui fasse les calculs et que tu ne mettes dans les cellules que la valeur des résultats.

Citation:

---

Code:

---

derligne = Sheets(ShtName).Range("K65536").End(xlDown).Row

---

---

Ca, ça ne marche pas.
Si tu te places dans une cellule en fin de colonne (65536), il faut faire ta recherche de la dernière ligne en remontant (xlUp) et non en descendant.
Donc choisit entre les deux formules suivante (la première étant préférable) :

Code:

---

1 2	derligne = Sheets(ShtName).Range("K65536").End(xlUp).Row derligne = Sheets(ShtName).Range("K5").End(xlDown).Row

---

Ou, encore mieux

Code:

---

derligne = Sheets(ShtName).Cells(Rows.Count,11).End(xlUp).Row

---

qui permet de t'affranchir du nombre de cellules max (bien utile si la macro doit être utilisable avec d'autres versions d'Excel).

Je ne comprends pas non plus à quand sert ton If puisque tu as déterminé un peu avant ta dernière cellule.
Ou alors, il aurait mieux valu écrire :

Code:

---

1 2	derligne = Sheets(ShtName).Cells(Rows.Count,11).End(xlUp).Row derligne = Min(derligne , Sheets(ShtName).Range("C5").End(xlDown).Row

---

Ce qui permet de calculer ta limite une seule fois et évite de faire un test à chaque tour de ta boucle, ce qui ralentit ton code.

ok j'ai bien noté une partie des modifications à faire pour déterminer la dernière ligne et la dernière colonne d'ailleurs sur le même principe. puis la suppression du if contrôlant si la cellule est vide

par contre comment transposer cette

Code:

---

1 2	Cells(a, dercolonn + 1).FormulaR1C1 = _ "=SUMIFS(R4C" & dercolonn & ":RC" & dercolonn & ",R4C2:R" & a & "C2,"">=""&TIME(HOUR(R" & a & "C2),MINUTE(R" & a & "C2),SECOND(R" & a & "C2))-TIME(0,0," & choixsec & ")+DATE(YEAR(R" & a & "C2),MONTH(R" & a & "C2),DAY(R" & a & "C2)))"

---

qui envoie la formule dans la cellule en

Code:

---

application.function.sumifs

---

pour que ce soit la macro qui calcule ?

j'en suis donc à ce code:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

Sub glissant()   Dim ShtName As String, NameFich As String, chemin As String Dim a As Long, derligne As Long     chemin = Workbooks(ActiveWorkbook.Name).Path NameFich = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)   ActiveWorkbook.SaveAs Filename:= _ chemin & "\" & NameFich & ".xlsm", FileFormat _ :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False   ShtName = ActiveSheet.Name derligne = Sheets(ShtName).Cells(Rows.Count, 11).End(xlUp).Row dercolonn = Sheets(ShtName).Cells(1, Columns.Count).End(xlToLeft).Column   choixsec = InputBox("Combien de secondes glissante (1 à 59) ? ", "Calcul glissant")   For a = 5 To derligne   Cells(a, dercolonn + 1).FormulaR1C1 = _ "=SUMIFS(R4C" & dercolonn & ":RC" & dercolonn & ",R4C2:R" & a & "C2,"">=""&TIME(HOUR(R" & a & "C2),MINUTE(R" & a & "C2),SECOND(R" & a & "C2))-TIME(0,0," & choixsec & ")+DATE(YEAR(R" & a & "C2),MONTH(R" & a & "C2),DAY(R" & a & "C2)))"   Next a   MsgBox "Calcul effectué avec succès !", vbOKOnly ActiveWorkbook.Save   End Sub

---

en attendant un possible retour de votre part...merci et bonne soirée

j'ai tenté cette transposition mais cela bloque sur Year me disant que ) est attendu et je ne vois pas où ? d'une part et d'autres part je ne sais pas si le code est correct sans parler de la parenthèse !?

Code:

---

1 2	Cells(a, dercolonn + 1)=application.WorksheetFunction.SumIfs(range(cells(4,dercolonn),cells(a,dercolonn)),range(cells(4,2),cells(a,2)), ">=" & time(hour(cells(a,2)),minute(cells(a,2)),second(cells(a,2)) - (time(0,0,choixsec)) + (date(year(cells(a,2)),month(cells(a,2)),day(cells(a,2)))))

---

Code:

---

1 2 3 4 5 6 7

Cells(a, dercolonn+1) =application.WorksheetFunction.SumIfs _ (range(cells(4,dercolonn),cells(a,dercolonn)), _ range(cells(4,2),cells(a,2)), _ ">=" & time(hour(cells(a,2)),minute(cells(a,2)),second(cells(a,2)) _ - (time(0,0,choixsec)) _ + (dateserial(year(cells(a,2)),month(cells(a,2)),day(cells(a,2)))))

---

en passant de date à dateserial cela ne bloque plus par contre en lançant le code j'ai droit à un erreur d'exécution '13' Incompatibilité de type


une idée svp ?

Citation:

---

j'ai tenté cette transposition mais cela bloque sur Year me disant que ) est attendu et je ne vois pas où ? d'une part et d'autres part je ne sais pas si le code est correct sans parler de la parenthèse !?

---

Je pense que ton erreur a été de traduire le fonction TEMPS d'Excel en Time de VBA qui n'ont pas du tout le même usage (Time sert à donner l'heure en cours). Il aurait fallu que tu utilises TimeSerial qui fonctionne comme TEMPS().

Citation:

---

Envoyé par novice06

en lançant le code j'ai droit à un erreur d'exécution '13' Incompatibilité de type

---

Quand tu cliques sur Débugage, quelle ligne passe en surbrillance ?

Citation:

---

Envoyé par novice06

par contre comment transposer cette

Code:

---

1 2	Cells(a, dercolonn + 1).FormulaR1C1 = _ "=SUMIFS(R4C" & dercolonn & ":RC" & dercolonn & ",R4C2:R" & a & "C2,"">=""&TIME(HOUR(R" & a & "C2),MINUTE(R" & a & "C2),SECOND(R" & a & "C2))-TIME(0,0," & choixsec & ")+DATE(YEAR(R" & a & "C2),MONTH(R" & a & "C2),DAY(R" & a & "C2)))"

---

---

Pour garder le principe dont je parlais au début du sujet (de petites fonctions simples plutôt qu'une grosse fonction compliquées) :

Code:

---

1 2 3 4 5 6

Dim MaLigne as Long   Cells(a, dercolonn + 1).Value = 0 For MaLigne = 4 to a If DateDiff("s";Cells(MaLigne,2).Value,Cells(a,2).Value) <= 10 Then Cells(a, dercolonn + 1) = Cells(a, dercolonn + 1) + Cells(MaLigne,32).Value Next MaLigne

---

A mettre à l'intérieur de ta boucle "For a= ... Next a", Excepté le Dim a mettre en début de code.

Je ne l'ai pas testée, il se peut qu'il faille inverser le "a" et le MaLigne dans le test If.

Mais on voit dans cet exemple que la fonction DateDiff de VBA peut grandement simplifier ta fonction, ce qui est un bon exemple pour illustrer ce que je disais dans mon premier message sur la transposition des fonctions Excel en VBA.

merci
effectivement cette méthode est fonctionnelle par contre multiplie par 10 voire plus le temps de traitement


en revenant à mon code et prenant en compte timeserial j'en suis à (en simplifiant un peu pour plus de visibilité)

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Dim a As Long, derligne As Long, dercolonn As Long Dim criteretime As Double   derligne = Sheets(ShtName).Cells(Rows.Count, 1).End(xlUp).Row dercolonn = Sheets(ShtName).Cells(1, Columns.Count).End(xlToLeft).Column   choixsec = 10   criteretime = TimeSerial(Hour(Cells(a, 2)), Minute(Cells(a, 2)), (Second(Cells(a, 2)) - choixsec)) + (DateSerial(Year(Cells(a, 2)), Month(Cells(a, 2)), Day(Cells(a, 2))))     For a = 5 To derligne   Cells(a, dercolonn + 1) = Application.WorksheetFunction.SumIfs _ (Range(Cells(4, dercolonn), Cells(a, dercolonn)), _ Range(Cells(4, 2), Cells(a, 2)), _ ">=" & criteretime)   Next a

---

chacun des éléments contient en faisant pas à pas les bonne données mais le résultat obtenus est 0 à chaque calcul...il ne fait pas la somme ?!

Citation:

---

Envoyé par novice06

effectivement cette méthode est fonctionnelle par contre multiplie par 10 voire plus le temps de traitement

---

Faire tous les calculs en parallèle est forcément plus rapide mais ça sature ta mémoire. Tu ne peux pas avoir quelque chose qui fait moins travailler la mémoire mais qui n'augmente pas le temps. C'est vouloir le beurre et l'argent du beurre.

l'une renvoi la formule dans la cellule et utilise énormément de ressources et de temps

Code:

---

1 2	Cells(a, dercolonn + 1).FormulaR1C1 = _ "=SUMIFS(R4C" & dercolonn & ":RC" & dercolonn & ",R4C2:R" & a & "C2,"">=""&TIME(HOUR(R" & a & "C2),MINUTE(R" & a & "C2),SECOND(R" & a & "C2))-TIME(0,0," & choixsec & ")+DATE(YEAR(R" & a & "C2),MONTH(R" & a & "C2),DAY(R" & a & "C2)))"

---

l'autre renvoi le résultat et utilise moins de ressources mais beaucoup plus de temps de traitement

Code:

---

If DateDiff("s";Cells(MaLigne,2).Value,Cells(a,2).Value) <= 10 Then Cells(a, dercolonn + 1) = Cells(a, dercolonn + 1) + Cells(MaLigne,32).Value

---

dans les 2 cas le résultat final est positif en terme de valeur recherchée par le calcul mais le temps de traitement est considérable.

application.worksheetFunction.sumifs est à priori ultra rapide comparé aux 2 précédente méthodes mais le tout est de savoir le configurer correctement surement ?! ce qui n'est apparemment pas mon cas ! :(

Dans mon code, je suis parti sur le principe de cumuler les valeurs dans la cellule elle-même.
Je pense qu'en utilisant une variable pour faire les calculs intermédiaires, ça accélèrerait sans doute les choses.

Code:

---

1 2 3 4 5

MonCalcul = 0 For MaLigne = 4 to a If DateDiff("s";Cells(MaLigne,2).Value,Cells(a,2).Value) <= 10 Then MonCalcul = MonCalcul + Cells(MaLigne,32).Value Next MaLigne Cells(a, dercolonn + 1).Value = MonCalcul

---

MonCalcul est à déclaré en Double
Si tu as moins de 35000 lignes, tu peux déclarer MaLigne en Integer.