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

[RESOLU] Moyenne multi-critere (bcp data - meilleure solution en temps d'execution)

C

Carnage029

XLDnaute Occasionnel
Bonjour a tous,

Premierement desole pour ce titre tres peu attirant, je vais vous decrier ce que je cherche a faire.
(voir le fichier attaché)

Je cherche a faire une moyenne de plusieurs colonnes, avec un critere sur les "tetes" de lignes.
Cependant, dans mon fichier j'ai 500 colonnes et 1200 lignes, du coup je cherche la solution la plus "rapide et elegante" qui peut etre VBA (je sais coder, ou tout du moins adapter des codes a mes besoins) ou une formule adaptable.

Un exemple vaut mieux qu'un long discours, ce que je cherche a avoir et un exemple sont dans la PJ.

Par avance merci beaucoup a vous,
Carnage029
 

Pièces jointes

  • BookExcelDL.xlsx
    13.2 KB · Affichages: 68
Trier par date Trier par votes
C

Carnage029

XLDnaute Occasionnel
Merci beaucoup pierrejean,

Je me pose la question, la double boucle for, pour une table de donnee de plus de 1000 lignes par 500 colonnes, est ce supportable a long terme ?

Petit bonus, je vais essayer d'adapter le code pour en faire une function, cependant, est t'il possible d'avoir la donnee et l'appel de la function dans deux workbook differents ? (du genre le path/name de la database etant un argument de la function). ?

EDIT: de ce que je vois le code fais une boucle sur toutes les colonnes, j'ai oublie de preciser mes mes dates sont toutes en ordre croissant sans omission. Je pense qu'il est possible de limiter le "tablo" en bornant les colonnes ou je me trompe totallement...

EDIT 2 : voila le code que j'ai commence a adapter, en revanche je vois pas trop comment fonctionne la boucle sur le critere "fruit"

EDIT 3: je pense avoir trouve, je laisse le dernier code que j'ai fait, je pense qu'il est ameliorable, surtout en terme de temps d'execution.

Code: 
Function RAverage(startdate As Date, enddate As Date, PG As String, DataRange As Range, CriteriaRange As Range)

Dim tablo1 As Variant
tablo1 = DataRange.Value
Criteria1 = CriteriaRange.Value


For n = LBound(tablo1, 1) + 1 To UBound(tablo1, 1)
  If Criteria1(n, 1) = PG Then
  For m = LBound(tablo1, 2) To UBound(tablo1, 2)
  If tablo1(1, m) >= startdate And tablo1(1, m) <= enddate Then
  tot = tot + tablo1(n, m)
  End If
  Next
  End If
 Next
nb = enddate - startdate + 1
RAverage = tot / nb

End Function


Bon weekend a vous
Carnage029
 
Dernière édition:
Vote positif 0 Vote négatif
C

Carnage029

XLDnaute Occasionnel
Bonjour JHA et eddy1975 et bienvenue sur le post,

La formule fonctionne bien et j'espere qu'elle pourra aider les lecteurs de ce billet. J'ai opte pour l'approche VBA et cherche a faire ce que j'appelle l'etape 2 maintenant. Avant de vous embeter je vais quand meme essayer de mon cote, pour eviter de demander des choses toutes cuites dans la bouche.
L'idee est d'avoir une "array" d'arguments optionels de criteres et de CriteriaRange.

J'actualiserai ce post avec mon premier "brouillon"

(PS: desole pour les accents, je travaille sur une machine/clavier anglais)
 
Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour à tous,

Un essai de fonction VBA pour le calcul de la moyenne par jour.

La fonction MOY a quatre paramètres en entrée:
Function MOY(xplage As Range, xdeb As Date, xfin As Date, Optional xcat As Range)
  • la plage source avec les en-têtes
  • la date de début
  • la date de fin
  • la plage des critères (paramètre facultatif). Si absent, on prend en compte toutes les catégories. Si tous les critères sont vides, alors on prend également en compte toutes les catégories.
Présentation du fichier:
  • après avoir ouvert le fichier, cliquer sur le bouton "Init" pour mettre des valeurs dans le tableau
  • trois formules utilisant des paramètres différents sont en exemple dans les cellules G5 à G7
  • la zone de critères est la plage D3 à D7
  • en cliquant sur le bouton 'Test durée "Fruit"', on mesure combien la formule de JHA est plus rapide (pour un seul critère "Fruit")
 

Pièces jointes

  • Carnage029- BookExcelDL- v1.xlsm
    46.5 KB · Affichages: 73
Vote positif 0 Vote négatif
C

Carnage029

XLDnaute Occasionnel
Merci beaucoup mapomme pour cet exemple et cette belle function.

C'est etonnant car c'est assez proche de ce que je cherche a faire au final final sur ce fichier. L'idee serait le meme calcul mais avec une range differente de critere pour chaque cellule D37 un peu a la mode des sumifs ou il y a "critere" et "range" falcultatif.

Je vais pouvoir y arriver je pense, bien que je n'ai encore jamais code de function ou routine avec des arguments falcultatifs mais je vais me renseigner.

Je vais travailler dessus et posterai mes avancees
 
Vote positif 0 Vote négatif
C

Carnage029

XLDnaute Occasionnel
EDIT: Avance majeur de mon cote, j'ai reussi a faire ce que je voulais (juste pas encore travaille sur le multiplicateur, mais ca devrait pas etre trop dur...) Je ferai un nouveau commentaire ce soir (heure USA) avec mon fichier et la function que j'ai faite

Bon bon, je bloque un peu quand meme sur cet histoire d'arguments falcultatifs... et je voudrais aussi ajouter quelque chose a cette function. Je voudrais en plus des N PGN et N DataRangeN, et N CriteriaRangeN (avec le tout en falcultatif) ajouter un critere disons OUI/NON pour multiplier chaque somme de chaque jour par une valeur, exemple le header.

En piece jointe, j'ai remis un fichier propre avec les explications detaillees. J'espere que c'est suffisament clair. N'hesitez pas a me poser des questions si ce n'est pas le cas.


Code: 
Function RAverage(startdate As Date, enddate As Date, PG As String, DataRange As Range, CriteriaRange As Range)

Dim tablo1 As Variant
tablo1 = DataRange.Value
Criteria1 = CriteriaRange.Value


For n = LBound(tablo1, 1) + 1 To UBound(tablo1, 1)
   If Criteria1(n, 1) = PG Then
   For m = LBound(tablo1, 2) To UBound(tablo1, 2)
   If tablo1(1, m) >= startdate And tablo1(1, m) <= enddate Then
   tot = tot + tablo1(n, m)
   End If
   Next
   End If
  Next
nb = enddate - startdate + 1
RAverage = tot / nb

End Function

EDIT: Ooups la piece jointe
 

Pièces jointes

  • C029-XLD.xlsm
    22.6 KB · Affichages: 71
Dernière édition:
Vote positif 0 Vote négatif
C

Carnage029

XLDnaute Occasionnel
Merci beaucoup a tous, sujet resolu j'ai fait ma function avec un nombre illimites d'arguments.
Pour ceux qui souhaitent voir le resultat, la piece jointe comprend la function BAverage qui est le resultat final.

Si certains arrivent a "epurer" ma function pour qu'elle soit plus "propre" je suis preneur

Un grand merci en particulier a pierrjean, JHA et mapomme
 

Pièces jointes

  • C029-XLD.xlsm
    26.8 KB · Affichages: 59
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…