Microsoft 365 SOMMEPROD en vba excel

iliess · 19 Janvier 2024

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.

nat54 · 19 Janvier 2024

Bonjour,

Le fichier n'est pas passé.

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

iliess · 19 Janvier 2024

nat54 à dit:
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 · 19 Janvier 2024

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 · 19 Janvier 2024

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 · 19 Janvier 2024

Hi,

cp4 à dit:
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 · 19 Janvier 2024

Cousinhub à dit:
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 · 19 Janvier 2024

cp4 à dit:
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 · 19 Janvier 2024

Cousinhub à dit:
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 · 19 Janvier 2024

cp4 à dit:
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 · 20 Janvier 2024

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 · 20 Janvier 2024

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 · 21 Janvier 2024

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

Microsoft 365 SOMMEPROD en vba excel

iliess

XLDnaute Occasionnel

Pièces jointes

nat54

XLDnaute Barbatruc

iliess

XLDnaute Occasionnel

Cousinhub

XLDnaute Barbatruc

cp4

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

cp4

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

iliess

XLDnaute Occasionnel

iliess

XLDnaute Occasionnel

Cousinhub

XLDnaute Barbatruc

Pièces jointes

iliess

XLDnaute Occasionnel

iliess

XLDnaute Occasionnel

Pièces jointes

Discussions similaires

Microsoft 365 SOMMEPROD en vba excel

XLDnaute Occasionnel

Pièces jointes

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Occasionnel

XLDnaute Barbatruc

Pièces jointes

XLDnaute Occasionnel

XLDnaute Occasionnel

Pièces jointes

Discussions similaires

Privacy & Transparency

Privacy & Transparency