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

[Carnage029]

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

 

[pierrejean]

Bonjour Carnage029

A tester
Nb le calcul se fait au changement de B3 C3 ou D3

 

[Carnage029]

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.

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

 

[JHA]

Bonjour à tous,

Un essai par formule

=SOMMEPROD(($B$11:$B$16=$D$3)*($C$10:$CP$10>=$B$3)*($C$10:$CP$10<=$C$3)*($C$11:$CP$16))/($C$3-$B$3+1)

JHA

 

[eddy1975]

Bonsoir à tous,
Encore une superbe formule de JHA. Je suggère juste une liste déroulante en D3 pour visualiser plus rapidement les catégories.
@+

 

[Carnage029]

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)

 

[mapomme]

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")

 

[Carnage029]

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

 

[Carnage029]

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.

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

 

[Carnage029]

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