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

**novice06** · 29/04/2015, 10h06

bonjour,

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

avec peut-être

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.WorksheetFunction.SumIfs

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

FormulaR1C1

?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=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

**Menhir** · 29/04/2015, 10h23

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.

**Pierre Fauconnier** · 29/04/2015, 13h39

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?

**joe.levrai** · 29/04/2015, 13h51

Bonjour,

je me pose en conciliateur

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 ?)

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..

**Menhir** · 29/04/2015, 14h31

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

(mais je me soigne

).

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

**Pierre Fauconnier** · 29/04/2015, 14h42

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...

**Pierre Fauconnier** · 29/04/2015, 14h44

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...

**novice06** · 29/04/2015, 15h41

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

=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 : test_vba.xlsm

merci

**novice06** · 29/04/2015, 16h07

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

somme.si.ens

test_vba.xlsm que j'aimerais passé sous vba si cela peu aider à mon problème de ressources et de plantage

**Menhir** · 29/04/2015, 16h28

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.

Au moins un For...to.

**novice06** · 29/04/2015, 17h28

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

=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 :

Sélectionner tout - Visualiser dans une fenêtre à part

=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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**Menhir** · 29/04/2015, 17h54

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.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Ou, encore mieux

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**novice06** · 29/04/2015, 20h06

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

application.function.sumifs

pour que ce soit la macro qui calcule ?

j'en suis donc à ce code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**novice06** · 29/04/2015, 23h58

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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)))))

**novice06** · 30/04/2015, 09h41

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

**Menhir** · 30/04/2015, 10h59

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().

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 ?

Envoyé par novice06

par contre comment transposer cette

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**novice06** · 30/04/2015, 11h21

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?!

**Menhir** · 30/04/2015, 11h25

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.

**novice06** · 30/04/2015, 11h48

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 !

**Menhir** · 30/04/2015, 12h00

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

Transposer somme.si.ens avec critère sur date et heures en vba ? [XL-2010]

Excel

Discussions similaires

Partager

Partager