Microsoft 365 SOMMEPROD en vba excel

iliess

XLDnaute Occasionnel
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
VB:
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.
 

Pièces jointes

  • Sommeprod vba .xlsm
    979.3 KB · Affichages: 4
Dernière édition:

Cousinhub

XLDnaute Barbatruc
Inactif
Bonsoir,
Tu as 213 numéros de compte différents
Et dans ton exemple, tu n'en mets que 7
70101111701011817010411170104181701111117011411170114181
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

XLDnaute Barbatruc
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
 
Dernière édition:

iliess

XLDnaute Occasionnel
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

XLDnaute Barbatruc
Inactif
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
 

Pièces jointes

  • PQ_Sommeprod.xlsx
    505.4 KB · Affichages: 4

iliess

XLDnaute Occasionnel
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
VB:
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
Code:
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
 

Pièces jointes

  • Sommeprod vba .xlsm
    850.5 KB · Affichages: 2

Discussions similaires

Réponses
3
Affichages
194
Réponses
2
Affichages
434

Statistiques des forums

Discussions
313 283
Messages
2 096 810
Membres
106 751
dernier inscrit
Souleymani