Microsoft 365 SOMMEPROD en vba excel

[iliess]

bonjour
je souhaite faire une analyse dans une grand base de données en utilisant la fonction SOMMEPROD
le problème que ma base de données est très grand et la fonction SOMMEPROD ralentie mon PC .
j'ai écris le code suivants et je souhaite affecter les résultat de SOMMEPROD dans un tableau Brr mais je n'arrive pas a faire ça.

voici mon code

Option Explicit
Sub test()
Dim ShGl As Worksheet, ShTVA9 As Worksheet
Dim Arr As Variant, Brr As Variant, Elem As Variant
Dim Mondico As New Dictionary
Dim i As Long, LrShGr As Long, ligne As Long
Set ShGl = ThisWorkbook.Worksheets("grandLivre")
LrShGr = ShGl.Cells(ShGl.Rows.Count, 1).End(xlUp).Row
Set ShTVA9 = ThisWorkbook.Worksheets("TVA9")
Set Mondico = CreateObject("Scripting.Dictionary")
Arr = ShGl.Range("A3:F" & LrShGr).Value

For i = LBound(Arr) To UBound(Arr)
Mondico(Arr(i, 6)) = ""
Next i
ShTVA9.[A5].Resize(Mondico.Count, 1) = Application.Transpose(Mondico.keys)

ReDim Brr(1 To Mondico.Count, 1 To 7)
For Each Elem In Mondico.keys
  
    '===============================================================================================================================================================
    Brr(1, 1) = Evaluate("SumProduct((Range("F3:F" & LrShGr) = Elem) * (Range("C3:C" & LrShGr) = "70101111") * (Range("E3:E" & LrShGr) - Range("D3:D" & LrShGr))")
    '===============================================================================================================================================================
 
Next Elem
End Sub

et voici mon fichier démo

salutations.

 

[nat54]

Bonjour,

Le fichier n'est pas passé.

PS : somme.si.ens est - gourmand (et + facile d'utilisation) que sommeprod

 

[iliess]

Bonjour,

Le fichier n'est pas passé.

PS : somme.si.ens est - gourmand (et + facile d'utilisation) que sommeprod

oui j'ai oublier le fichier et j'ai modifier

 

[Cousinhub]

Bonsoir,
Tu as 213 numéros de compte différents
Et dans ton exemple, tu n'en mets que 7

70101111

70101181

70104111

70104181

70111111

70114111

70114181

C'est voulu?
Un choix quelconque?
Précisé quelque part?
Comme tu disposes de 365, pense à modifier tes méthodes de calcul, peut-être?

 

[cp4]

Bonsoir @iliess 🙂, @nat54 😉, @Cousinhub 🙂,

@iliess : Il serait judicieux de nous expliquer les tenants et aboutissants de ton fichier.
Je me permets de te dire que tu fais les mêmes erreurs que moi, à mes débuts.
En effet, tu penses que ton problème ne se résout qu'avec SommeProd.
Or, par expérience un problème peut se solutionner de différentes manières.
Des exemples de Feu Jacques Boisgontier

Bonne soirée

 

[Cousinhub]

Hi,

En effet, je pense que ton problème ne se résout qu'avec SommeProd.

Euh, je peux m'inscrire en porte-à-faux?
PQ, nativement installé dans 365, le fait tout aussi bien... (voire en mieux....) 🤪

 

[cp4]

Hi,

Euh, je peux m'inscrire en porte-à-faux?
PQ, nativement installé dans 365, le fait tout aussi bien... (voire en mieux....) 🤪

Autant pour moi, je suis confus. Je voulais écrire "tu penses" ou "crois" que son idée est la bonne pour solutionner son problème. Perso, je n'ai jamais utilisé PQ.

 

[Cousinhub]

Autant pour moi, je suis confus. Je voulais écrire "tu penses" ou "crois" que son idée est la bonne pour solutionner son problème. Perso, je n'ai jamais utilisé PQ.

Pas de soucis, 👍, PQ est une étape à franchir, comme VBA l'a été en son temps...
Bonne soirée

 

[iliess]

Bonsoir,
Tu as 213 numéros de compte différents
Et dans ton exemple, tu n'en mets que 7

70101111

70101181

70104111

70104181

70111111

70114111

70114181

C'est voulu?
Un choix quelconque?
Précisé quelque part?
Comme tu disposes de 365, pense à modifier tes méthodes de calcul, peut-être?

Bonsoir mr
Oui les 7 compte c'est voulu car mes analyses basé sur les 7

 

[iliess]

Bonsoir @iliess 🙂, @nat54 😉, @Cousinhub 🙂,

@iliess : Il serait judicieux de nous expliquer les tenants et aboutissants de ton fichier.
Je me permets de te dire que tu fais les mêmes erreurs que moi, à mes débuts.
En effet, tu penses que ton problème ne se résout qu'avec SommeProd.
Or, par expérience un problème peut se solutionner de différentes manières.
Des exemples de Feu Jacques Boisgontier

Bonne soirée

ma base de données obtenir d'un logiciel de vente il ya des erreurs de calculs
Avec le détail je peux identifier la pièce qui contient l'erreur et corriger les paramètres de calculs

 

[Cousinhub]

Bonjour,
Dans le fichier joint, 2 tableaux dans le 2ème onglet
le premier, colonne A, contenant la liste des comptes à analyser (modifiable à souhait)
le deuxième, les calculs effectués par le biais de Power Query - Clic droit dans le tableau, "Actualiser", pour mettre à jour
Bonne journée

 

[iliess]

Bonjour Mr @Cousinhub
Merci pour votre réponse.
j'ai jamais utiliser le Power Query, c'est très utile et surtouts que je peux modifier la liste des compte a analysé.
j'essaye de faire la même chose pour comprendre comment utilisé le Power Query.
Salutations.

 

[iliess]

Bonjour
la méthode de Mr @Cousinhub fonctionne très bien mais je cherche en vba
j'ai trouver deux méthodes :
- Avec la fonction Somme.si.ens
- Tableau croisé dynamique
SVP s'il y a un moyen pour réduire le temps d'exécution car mon fichier est très grand (200000 lignes) et les deux méthodes garde beaucoup de temps.

Code Avec la fonction Somme.si.ens

Sub Avec_Somme_SI_ENS()
With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
End With

Dim ShGl As Worksheet, ShTVA9 As Worksheet
Dim Arr As Variant, Brr As Variant, Elem As Variant
Dim Mondico As New Dictionary
Dim i As Long, LrShGr As Long, ligne As Long, p As Long
Set ShGl = ThisWorkbook.Worksheets("grandLivre")
LrShGr = ShGl.Cells(ShGl.Rows.Count, 1).End(xlUp).Row
Set ShTVA9 = ThisWorkbook.Worksheets("TVA9")
Set Mondico = CreateObject("Scripting.Dictionary")
Arr = ShGl.Range("A3:G" & LrShGr).Value

For i = LBound(Arr) To UBound(Arr)
    If Left(Arr(i, 3), 2) = "70" Then
        Mondico(Arr(i, 7)) = ""
    End If
Next i
ShTVA9.Select
ShTVA9.[A5].Resize(Mondico.Count, 1) = Application.Transpose(Mondico.keys)

ReDim Brr(1 To Mondico.Count, 1 To 9)
p = 1
For Each Elem In Mondico.keys
    
    '===============================================================================================================================================================
    Brr(p, 1) = Application.WorksheetFunction.SumIfs(Range("GrandLivre[SOLDE]"), Range("GrandLivre[KEY]"), Elem, Range("GrandLivre[COMPTE]"), Range("B4"))
    Brr(p, 2) = Application.WorksheetFunction.SumIfs(Range("GrandLivre[SOLDE]"), Range("GrandLivre[KEY]"), Elem, Range("GrandLivre[COMPTE]"), Range("C4"))
    Brr(p, 3) = Application.WorksheetFunction.SumIfs(Range("GrandLivre[SOLDE]"), Range("GrandLivre[KEY]"), Elem, Range("GrandLivre[COMPTE]"), Range("D4"))
    Brr(p, 4) = Application.WorksheetFunction.SumIfs(Range("GrandLivre[SOLDE]"), Range("GrandLivre[KEY]"), Elem, Range("GrandLivre[COMPTE]"), Range("E4"))
    Brr(p, 5) = Application.WorksheetFunction.SumIfs(Range("GrandLivre[SOLDE]"), Range("GrandLivre[KEY]"), Elem, Range("GrandLivre[COMPTE]"), Range("F4"))
    Brr(p, 6) = Application.WorksheetFunction.SumIfs(Range("GrandLivre[SOLDE]"), Range("GrandLivre[KEY]"), Elem, Range("GrandLivre[COMPTE]"), Range("G4"))
    Brr(p, 7) = Application.WorksheetFunction.SumIfs(Range("GrandLivre[SOLDE]"), Range("GrandLivre[KEY]"), Elem, Range("GrandLivre[COMPTE]"), Range("H4"))
    Brr(p, 8) = Application.WorksheetFunction.SumIfs(Range("GrandLivre[SOLDE]"), Range("GrandLivre[KEY]"), Elem, Range("GrandLivre[COMPTE]"), Range("I4"))
    Brr(p, 9) = Application.WorksheetFunction.SumIfs(Range("GrandLivre[SOLDE]"), Range("GrandLivre[KEY]"), Elem, Range("GrandLivre[COMPTE]"), Range("J4"))
    '===============================================================================================================================================================
 p = p + 1
Next Elem
[B5].Resize(UBound(Brr, 1), UBound(Brr, 2)) = Brr
With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
End With
End Sub

Code avec Tableau croisé dynamique

Sub Avec_Tableau_croisé_dynamiQue()
With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
End With
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="GrandLivre", Version:=8).CreatePivotTable TableDestination:="Feuil2!R3C1", TableName:="Tableau croisé dynamique1", DefaultVersion:=8
Sheets("Feuil2").Select
    
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("KEY")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("SOLDE")
        .Orientation = xlDataField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("COMPTE")
        .Orientation = xlColumnField
        .Position = 1
        For i = 1 To .PivotItems.Count
            If .PivotItems(i).Name = "4451801" Or .PivotItems(i).Name = "445221" Or .PivotItems(i).Name = "70101111" Or .PivotItems(i).Name = "70101181" Or .PivotItems(i).Name = "70104111" Or .PivotItems(i).Name = "70104181" Or .PivotItems(i).Name = "70111111" Or .PivotItems(i).Name = "70114111" Or .PivotItems(i).Name = "70114181" Then
                .PivotItems(i).Visible = True
            Else:
                .PivotItems(i).Visible = False
            End If
        Next i
    End With
    
    With ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("SOLDE")
        .NumberFormat = "#,##0.00"
    End With
With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
End With
End Sub