Discussion :

[Zagarague]

Bonjour à tous,

Je fais une nouvelle fois appel à votre maîtrise pour trouver l'explication de mon problème.
Je souhaite insérer une formule qui va s’incrémenter et chercher dans plusieurs feuilles des somme.si.ens, selon le nombre de feuilles.
Il y a actuellement une trentaine de feuilles.

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
Sub Formules_Graph()
Dim n As Integer
Dim FormuleCelluleC, FormuleCelluleD, FormuleCelluleE, FormuleCelluleF, FormuleCelluleG, FormuleCelluleL  As String
 
FormuleCelluleC = "=0"
FormuleCelluleD = "=0"
FormuleCelluleE = "=0"
FormuleCelluleF = "=0"
FormuleCelluleG = "=0"
FormuleCelluleL = "=0"
For n = 10 To Sheets.Count
FormuleCelluleC = FormuleCelluleC & "+SOMME.SI.ENS('" & Sheets(n).Name & "'!$I$4:$I$1500;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" >= ""&$B4;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" <= ""&FIN.MOIS($B4;0))-SOMME.SI.ENS('" & Sheets(n).Name & "'!$J$4:$J$1500;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" >= ""&$B4;'28'!$G$4:$G$1500;"" <= ""&FIN.MOIS($B4;0))"
'FormuleCelluleD = FormuleCelluleD & "+SOMME.SI.ENS('" & Sheets(n).Name & "'!$I$4:$I$1500;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" >= ""&$B4;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" <= ""&FIN.MOIS($B4;0))-SOMME.SI.ENS('" & Sheets(n).Name & "'!$J$4:$J$1500;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" >= ""&$B4;'28'!$G$4:$G$1500;"" <= ""&FIN.MOIS($B4;0))"
'FormuleCelluleE = FormuleCelluleE & "+SOMME.SI.ENS('" & Sheets(n).Name & "'!$I$4:$I$1500;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" >= ""&$B4;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" <= ""&FIN.MOIS($B4;0))-SOMME.SI.ENS('" & Sheets(n).Name & "'!$J$4:$J$1500;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" >= ""&$B4;'28'!$G$4:$G$1500;"" <= ""&FIN.MOIS($B4;0))"
'FormuleCelluleF = FormuleCelluleF & "+SOMME.SI.ENS('" & Sheets(n).Name & "'!$I$4:$I$1500;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" >= ""&$B4;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" <= ""&FIN.MOIS($B4;0))-SOMME.SI.ENS('" & Sheets(n).Name & "'!$J$4:$J$1500;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" >= ""&$B4;'28'!$G$4:$G$1500;"" <= ""&FIN.MOIS($B4;0))"
'FormuleCelluleG = FormuleCelluleG & "+SOMME.SI.ENS('" & Sheets(n).Name & "'!$I$4:$I$1500;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" >= ""&$B4;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" <= ""&FIN.MOIS($B4;0))-SOMME.SI.ENS('" & Sheets(n).Name & "'!$J$4:$J$1500;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" >= ""&$B4;'28'!$G$4:$G$1500;"" <= ""&FIN.MOIS($B4;0))"
'FormuleCelluleL = FormuleCelluleL & "+SOMME.SI.ENS('" & Sheets(n).Name & "'!$I$4:$I$1500;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" >= ""&$B4;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" <= ""&FIN.MOIS($B4;0))-SOMME.SI.ENS('" & Sheets(n).Name & "'!$J$4:$J$1500;'" & Sheets(n).Name & "'!$G$4:$G$1500;"" >= ""&$B4;'28'!$G$4:$G$1500;"" <= ""&FIN.MOIS($B4;0))"
Next
MsgBox (FormuleCelluleC)
MsgBox (FormuleCelluleD)
Range("C4").FormulaLocal = FormuleCelluleC
Range("D4").FormulaLocal = FormuleCelluleD
Range("E4").FormulaLocal = FormuleCelluleE
Range("F4").FormulaLocal = FormuleCelluleF
Range("G4").FormulaLocal = FormuleCelluleG
Range("L4").FormulaLocal = FormuleCelluleL
 
 
End Sub

Lorsque je lance la macro, la 1e ligne fait s’arrêter le code, et indique une erreur 7 « Mémoire insuffisante ».

Code :

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

Range("C4").FormulaLocal = FormuleCelluleC

A priori, il y a environ 9000 caractères dans la chaine string.

Savez-vous comment résoudre cet épineux sujet ? Je n’ai rien trouvé, hormis spécifier string*64000 pour dimensionner mes variables, mais ça ne change rien.
Merci pour votre aide.
A vous lire

[laurent_ott]

Bonjour.
Voir ce lien qui indique qu'une formule ne doit pas dépasser 8192 caractères :
https://support.microsoft.com/fr-fr/...7-269d656771c3

Attention dans la déclaration de vos variables, le AS String n'est valable que pour la dernière déclaration, les précédentes sur la ligne seront de type Variant, car As String doit être déclaré pour chaque variable même si elles sont sur la même ligne.

Pour résoudre votre problème il serait peut être plus simple de faire une fonction en VBA.

Bonne continuation.

[Zagarague]

Bonjour,

Je vous remercie pour votre réponse. Quel dommage que les formules soient aussi limitées !
C'est parti pour tout calculer sans mise à jour dynamique, et à faire pour toutes les lignes (j'imagine qu'une autre boucle for devrait suffire) (avec une formule bien écrite, j'espérais n'avoir qu'à la glisser vers le bas).
Merci pour le conseil sur la déclaration des variables, je l'ignorais !

Belle journée

[laurent_ott]

Vous pouvez aussi utiliser en VBA :
Application.Evaluate(...)
Ce qui permet de faire un calcul en utilisant les fonctions d'excel.
Donc mémoriser le résultat dans une variable.
Je n'ai pas compris ce que vous souhaitez faire mais cela peut vous servir.
Bonne continuation.

[Zagarague]

Bonjour,

Je me permets de rouvrir ce fil.
Voici la méthode que j'ai cru comprendre devoir faire pour obtenir le même résultat qu'avec une formule :

Code :

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

1
2
3
4
5
6
7
8
9
Dim n As Integer
Dim Z As Integer
For Z = 4 To 15
    Range("C" & Z).Value = "0"
 
    For n = 10 To Sheets.Count
    Range("C" & Z).Value = Range("C" & Z).Value + WorksheetFunction.SumIfs(Sheets(n).Range("I4:I1500"), Sheets(n).Range("G4:G1500"), ">=" & Range("B" & Z), Sheets(n).Range("G4:G1500"), "<=" & WorksheetFunction.EoMonth(Range("B" & Z), 0))
    Next n
Next Z

J'ai la forte impression que le résultat s'ajoute sans prendre en compte la borne inférieure (date < B4 par exemple).
par exemple plus parlant :
Imaginons :
ActiveSheet.B4 = 01/01/2022
sheet(n).G4:G1500, on a des dates du 01/01/2022 au 31/12/2022
sheet(n).I5:I1500 , on a des valeurs de 1 à 200

En colonne I, le sumifs devrait sommer 4, 1, et 200 entre le 01/01/2022 et le 31/01/2022 => résultat 205
ActiveSheet.C4 = 205

Ensuite :
ActiveSheet.C4 = 01/02/2022
En colonne I, le sumifs devrait sommer 3 et 2 entre le 01/02/2022 et le 28/02/2022 => résultat 5
ActiveSheet.C5 = 210 alors que ca devrait etre 5

Je ne sais pas si j'ai un problème de code avec le SumIfs ou l'expression des arguments de date, ou bien si j'ai un problème de maths/boucles...

A vous lire avec plaisir

Cordialement,

[Arkham46]

Bonjour,

Une formule de ce style permet de sommer sur plusieurs feuilles :

Code :

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

=SOMMEPROD(SOMME.SI.ENS(INDIRECT({"Feuil2";"Feuil3"} & "!$A:$A");INDIRECT({"Feuil2";"Feuil3"} & "!$B:$B");A1))

Pour ne pas répéter les noms de feuilles {"Feuil2";"Feuil3"}, on peut le remplacer par :
- une valeur dans le gestionnaire de noms (valeur = la liste entre accolade avec un = au début)
- un range "classique" contenant la liste des noms de feuilles
- un tableau renvoyé par une fonction VBA (qui renvoi un tableau du style Dim lListeFeuilles(1 To 29) As String)

[Raccourcix]

Bonjour,

Je vous remercie pour votre réponse. Quel dommage que les formules soient aussi limitées !
C'est parti pour tout calculer sans mise à jour dynamique, et à faire pour toutes les lignes (j'imagine qu'une autre boucle for devrait suffire) (avec une formule bien écrite, j'espérais n'avoir qu'à la glisser vers le bas).
Merci pour le conseil sur la déclaration des variables, je l'ignorais !

Belle journée

Bonjour

Les formules ne sont pas "limitées", il faut changer de point de vue pour réaliser une trentaine de SOMME.SI.ENS. C'est un problème de conception.

Vous commencez par regrouper toutes vos données dans une seule base avec Power Query et ensuite vous créez un TCD ou un tableau suivant vos critères.
C'est le plus simple, le plus souple à l'usage et le plus efficace.

Quelles sont vos données sources ? pourquoi avez-vous 30 onglets ? Pourquoi n'utilisez-vous des tableaux plutôt que des plages I4:I1500 ?
Quelle est votre version d'Excel ?

Stéphane

[Zagarague]

Merci pour vos retours/idées.

@Stéphane, je suis assez novice, et ne maîtrise pas du tout PowerQuery. Pour vous répondre, le fichier doit être utilisé par plusieurs collaborateurs, et chacun est intéressé par un onglet seulement. Les données brutes sont traitées d’abord sur chacun de ces onglets (je ne peux pas modifier cette trame et ce process), et je ne fais qu’une synthèse de ces onglets. De plus, les sources des données sont très hétéroclites. Enfin, j’ai indiqué le code pour une colonne, mais il y en a 6 à traiter de la sorte (ca ne change pas le pertinence de votre proposition, mais je ne sais pas du tout la mettre en œuvre).
Je ne voulais pas insulter les limites Excel, mais faire état de ma déception de béotien 😉

@arkhan, je vais voir comment créer un tableau (une liste string qui renverrait un résultat du type "{« feuil2 » ; « feuil3 » ;…}", changeant en fonction du nombre et du nom des feuilles si j’ai bien compris), qui liste les noms des feuilles et l’intégrer dans votre proposition de formule (ça revient peut-être à la proposition de Stéphane que je n’ai pas comprise).

Encore merci, je vous tiendrais au courant.
Dans l’absolu, hormis l’ineptie possible de mon approche, n’hésitez pas si vous arrivez à déterminer si mon code précédent est erroné avec Sumifs et la manipulation des dates ou si j’ai commis une erreur de logique 😊

[EricDgn]

Bonjour,

Une autre approche pour réduire le nombre de caractères: nommer les plages dans chaque feuille (le même nom pouvant être utilisés dans chaque feuille). A tester.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub Formules_Graph0()
    Dim n As Integer
    Dim FormuleCelluleC As String
    FormuleCelluleC = ""
    For n = 10 To Sheets.Count
        Worksheets(n).Names.Add Name:="X", RefersTo:="=$I$4:$I$1500"
        Worksheets(n).Names.Add Name:="Y", RefersTo:="=$G$4:$G$1500"
        Worksheets(n).Names.Add Name:="Z", RefersTo:="=$J$4:$J$1500"
        FormuleCelluleC = FormuleCelluleC & "+SOMME.SI.ENS('" & Sheets(n).Name & "'!X;'" & Sheets(n).Name & "'!Y;"" >= ""&$B4;'" & Sheets(n).Name & "'!Y;"" <= ""&FIN.MOIS($B4;0))-SOMME.SI.ENS('" & Sheets(n).Name & "'!Z;'" & Sheets(n).Name & "'!Y;"" >= ""&$B4;'28'!Y;"" <= ""&FIN.MOIS($B4;0))"
'       --->                                                                                                                                                                                                                                                      --28-- ?
    Next
    Range("C1").FormulaLocal = FormuleCelluleC
End Sub

(Formule en elle-même non vérifiée)
Cordialement.

[Zagarague]

Bonjour,

Merci pour cette idée. Hélas j'ai peur qu'à terme, le nombre de caractères gagné ne suffise pas.

Je suis passé par la solution de Arkham. Au final il doit y avoir un problème de formule. Voici ce que j'ai. Le calcul ne renvoie pas d'erreur, mais le résultat est "0" alors que ça devrait être un nombre... Un pro des indirect aurait une idée ?

Code formule :

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

=SOMMEPROD(SOMME.SI.ENS(INDIRECT(NomFeuilles&"!$I$4:$I$1500");INDIRECT(NomFeuilles&"!$G$4:$G$1500");">=$B5";INDIRECT(NomFeuilles&"!$G$4:$G$1500");"<="&FIN.MOIS($B4;0)))

avec Nomfeuilles obtenu ainsi :

Code :

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

ActiveWorkbook.Names.Add Name:="NomFeuilles", RefersTo:=Sheets("Sommaire").Range("A1", Sheets("Sommaire").Range("A2").End(xlDown))

Merci encore pour votre aide, je pense qu'on touche au but !

[Arkham46]

Bonjour,

Il faut évaluer B5 et donc le sortir de la chaîne de caractères.

Code :

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

">=$B5"

A remplacer par :

Code :

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

">=" & $B5

[Zagarague]

Oh, l'erreur de débutant ! à force de voir des $ et des &!", j'avais plus les yeux en face des trous !
Merci d'avoir pris la peine de me répondre !
Et un grand merci à tous. Sujet résolu