Utilisation de SumIfs avec des array comme plage

Bonjour à tous,

Je viens vers vous car je suis débutant en VBA et bloqué sur mon code. Je commence à travailler avec les variables tableaux pour optimiser la rapidité des procédures car mon fichier contient plus de 80 000 lignes sur 8 colonnes.

Je souhaiterais réaliser la somme de Product Quantity (4ème colonne) selon 3 conditions, le Site Id en colonne 1, le Transaction Type en colonne 2 et le Transaction Successful en colonne 3.

Merci de votre aide !

PS: je ne souhaite pas passer par un TCD et le fichier joint n'est qu'une partie du fichier complet

Bonjour Guillaume44000,

Voici un essai en pièce jointe:

Bonjour,

Tout d'abord merci d'avoir pris le temps de rechercher !

malheureusement ce n'est pas vraiment se que je souhaite.

Pour repréciser j'ai fait un exemple du type de donnée que je souhaite avec la formule dans le fichier ci-joint.

Mais au vu de la volumétrie de donnée que le fichier initial contient, le fichier devient très lent lorsque je filtre ma BD avec des formules.

Donc voila pourquoi j'avais pensé à l'utilisation des tableaux qui est beaucoup plus rapide.

Merci pour votre aide.

bonjour

salut Florian

faire un simple TCD et le filtrer (j'ai choisis 2 sites pour te montrer)

il n'y a aucune formule ni aucun VBA

le temps de calcul est... instantané

note : utilise les fonctionnalités d'Excel est gage de rapidité et surtout de fiabilité

amitiés

Bonjour,

@jmd,

Le demandeur a été clair :

PS: je ne souhaite pas passer par un TCD et le fichier joint n'est qu'une partie du fichier complet

Il est donc inutile d'intervenir avec tes lapalissades habituelles.

Cdlt.

salut Jean-Eric

eh bien c'est une mauvaise idée de ne pas utiliser la fonctionnalité d'analyse sans doute la plus puissante d'Excel, hyper-rapide et hyper-fiable

très robuste aux changement d'environnement

facile à modifier dans 1 an ou 2

mais ce n'est que mon avis, et bien entendu j'ose le donner

amitiés à toi et à tous

Bonjour,

On peut bâtir un fonction perso qui fait ça mais c'est plus lent que le Somme.Si().

Boisgontier

Bonjour

Traiter de grosses table de données est plus efficace avec des outils orientés BD que tableur.

On peut donc utiliser PowerQuery, intégré à partir de 2016 et en add on sur 2010 et 2013.

De plus le poids résultant est faible puisque que c'est une simple requête.

Bonjour

Traiter de grosses table de données est plus efficace avec des outils orientés BD que tableur.

On peut donc utiliser PowerQuery, intégré à partir de 2016 et en add on sur 2010 et 2013.

De plus le poids résultant est faible puisque que c'est une simple requête.

re

j'approuve à 100%

bonne journée à tous

Bonjour à tous,

@jmd, j'avais effectivement pensé aux TCD mais Guillaume44000 a indiqué qu'il n'en souhaitait pas, mais effectivement je suis d'accord avec toi sur la puissance de calcul et de rapidité que propose les TCD

PS: je ne souhaite pas passer par un TCD et le fichier joint n'est qu'une partie du fichier complet

@ Guillaume44000, tu indiques :

je suis débutant en VBA et bloqué sur mon code. Je commence à travailler avec les variables tableaux pour optimiser la rapidité des procédures car mon fichier contient plus de 80 000 lignes sur 8 colonnes.

Ce que je te propose est bien du VBA avec l'utilisation de variable tableau, l'éxécution est un peu lente mais le code parcours tout fichier et plusieurs fois. Maintenant si tu souhaites que faire une somme des " Card Recharge Completion" avec les "Transaction Successful" en "Yes", sa va fortement améliorer la vitesse du code.

Faut-il faire une modification du code, dans ce sens ?

@78chris, Je suis totalement d'accord avec toi, depuis que j'ai franchis le pas sur l'utilisation de Power Query je l'utilise de plus en plus. Je le recommande effectivement sur des très grands tableaux.

Bonjour,

Effectivement, je souhaite seulement faire la somme des quantités et des montants sur les 2 critères "Card Recharge Completion" et "Transaction Successful" en "Yes" sachant que dans mon anglet final j'ai déjà la liste de mes "Site Id".

Je suis bien conscient de l'avantage des TCD mais je confirme vouloir faire sans.

Merci donc pour votre aide sur mon sujet.

Voici un nouveau fichier avec les critères voulus, le code est beaucoup plus rapide mais pourrait l'être encore plus si la colonne "ID" est triée au préalable soit en ordre croissant ou décroissant, du coup on pourrait rajouter une condition pour sortir de la boucle, si l'ID recherché est déjà passé en revue.

Un nouveau fichier j'avais oublié " Product Price".

Bonjour,

Un autre exemple avec TCD sans TCD !...

Cdlt.

Voici un nouveau fichier avec les critères voulus, le code est beaucoup plus rapide mais pourrait l'être encore plus si la colonne "ID" est triée au préalable soit en ordre croissant ou décroissant, du coup on pourrait rajouter une condition pour sortir de la boucle, si l'ID recherché est déjà passé en revue.

Re, pour ma part dans mon fichier final, j'ai déjà une liste trié sans doublon de tous les ID dans une colonne. Si c'est plus simple pour vous, je souhaite seulement les deux sommes.si qui correspond à chaque implant.

j'ai essayé de vous remettre un exemple en pj. (La macro devra seulement remplir les informations en rouges)

Bonjour,

Après plusieurs bidouillage sur le code de Florian53, j'ai trouvé mon bonheur. Je transmet le fichier avec le code final.

Il est sans doute pas optimal mais me convient, si jamais vous constatez un risque d'anomalie dit le moi.

Merci à tous je valide le sujet.

Bonjour Guillaume44000 ,

Un fichier en retour avec quelques modif pour une optimisation du temps d’exécution.

Bonjour Guillaume44000 ,

Un fichier en retour avec quelques modif pour une optimisation du temps d’exécution.

test sumifs avec array (3).xlsm

Merci pour cette optimisation !

Mais dans le code que tu me présente cela coupe la procédure si un Site Id de m'on onglet SYNTHESE n'est pas dans ma BD. J'ai besoin que le code gère cette anomalie en donnant la valeur 0.

J'ai essayé de modifie ton code en conséquence, mais j'ai pas réussi.

Merci de ton aide !

Bonjour,

Sub SousTotalNonTriéTotCol()
  Set f = Sheets("BD")
  Set d = CreateObject("Scripting.Dictionary")
  TblE = Range("A2:E" & [a65000].End(xlUp).Row)                         ' Table entrée
  Dim TblS(): ReDim TblS(1 To UBound(TblE), 1 To UBound(TblE, 2))       ' Table sortie
  For i = LBound(TblE) To UBound(TblE)
    If d.Exists(TblE(i, 1)) Then
      lig = d(TblE(i, 1))              ' Récupération index TblS()
    Else
      d(TblE(i, 1)) = d.Count + 1: lig = d.Count: TblS(lig, 1) = TblE(i, 1)
    End If
   For c = 4 To UBound(TblE, 2)        ' Totalisation numérique
     If TblE(i, c) <> "" Then
        TblS(lig, c - 2) = TblS(lig, c - 2) + CDbl(TblE(i, c))
     End If
   Next c
  Next i
  f.[N2].Resize(d.Count, UBound(TblS, 2) - 2) = TblS
End Sub

Boisgontier

Bonjour,

Sub SousTotalNonTriéTotCol()
  Set f = Sheets("BD")
  Set d = CreateObject("Scripting.Dictionary")
  TblE = Range("A2:E" & [a65000].End(xlUp).Row)                         ' Table entrée
  Dim TblS(): ReDim TblS(1 To UBound(TblE), 1 To UBound(TblE, 2))       ' Table sortie
  For i = LBound(TblE) To UBound(TblE)
    If d.Exists(TblE(i, 1)) Then
      lig = d(TblE(i, 1))              ' Récupération index TblS()
    Else
      d(TblE(i, 1)) = d.Count + 1: lig = d.Count: TblS(lig, 1) = TblE(i, 1)
    End If
   For c = 4 To UBound(TblE, 2)        ' Totalisation numérique
     If TblE(i, c) <> "" Then
        TblS(lig, c - 2) = TblS(lig, c - 2) + CDbl(TblE(i, c))
     End If
   Next c
  Next i
  f.[N2].Resize(d.Count, UBound(TblS, 2) - 2) = TblS
End Sub

Boisgontier

Bonjour, ce code prend en compte tous les types de ventes. Pour ma part je souhaite seulement la somme des "Card Recharge Completion" qui sont "Yes".

Rechercher des sujets similaires à "utilisation sumifs array comme plage"