Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Microsoft 365 SOMMEPROD en vba excel

I

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:
Trier par date Trier par votes

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?
 
Vote positif 0 Vote négatif

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:
Vote positif 0 Vote négatif
I

iliess

XLDnaute Occasionnel
Bonsoir mr
Oui les 7 compte c'est voulu car mes analyses basé sur les 7
 
Vote positif 0 Vote négatif
I

iliess

XLDnaute Occasionnel
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
 
Vote positif 0 Vote négatif

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
Vote positif 1 Vote négatif
I

iliess

XLDnaute Occasionnel
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.
 
Vote positif 0 Vote négatif
I

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
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

I
  • Résolu(e)
Microsoft 365 Variable pour plusieurs macros
Réponses
3
Affichages
275
iliess
I
I
  • Résolu(e)
Microsoft 365 rechercheV entre deux tableau vba excel
Réponses
7
Affichages
607
job75
I
  • Résolu(e)
Microsoft 365 Placer un tableau VBA dans un tableau structuré Excel
Réponses
14
Affichages
459
mapomme
I
  • Résolu(e)
Microsoft 365 remplir un tableau Arr() en vba à partir des cellules visible
Réponses
4
Affichages
758
iliess
I
C
  • Résolu(e)
XL 2021 Additionner résultats d'une boucle for en vba
Réponses
9
Affichages
381
Claudinedu13
C
R
  • Résolu(e)
XL 2019 amélioration d'une macro (actualisation auto et automatisation)
Réponses
11
Affichages
535
rh.finances
R
N
  • Résolu(e)
Microsoft 365 Mettre la 4ème colonne d'une ListBox au format [h]:mm
3 4 5
Réponses
68
Affichages
1 K
NONO14
N
N
  • Question
Microsoft 365 Code VBA
Réponses
7
Affichages
553
TooFatBoy
G
  • Question
Microsoft 365 Export données
Réponses
4
Affichages
426
GClaire
G
M
  • Résolu(e)
XL 2021 PUBLIPOSTAGE : Mise en forme du corps de message issu d'une cellule excel (VBA)
Réponses
4
Affichages
329
Minus&Cortex
M
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…