Discussion :

[jpboucher]

Bonjour à tous,

Je suis nouveau sur ce forum, bien que je l'ai utilisé à plusieurs reprises dans les dernières semaines pour démarrer avec VBA. Je suis encore neophyte, mais vraiment, jusqu'ici la forum m'a permis de progresser rapidement dans mon apprentissage (un gros merci à tous, vraiment super!).

Mais là, j'ai peut-être bien vu un peu trop grand pour mes connaissances encore un peu trop rudimentaire. En gros, voici mon problème:

J'ai une feuille ("Strategie" qui me présente un tableau de bord de données que je collecte et met à jour continuellement dans l'onglet (feuille "Enregistrements") à l'aide de formulaire. Depuis des heures, j'essai de trouver la manière de monter un code VBA qui me permettrait de:

1 - Filtrer les résultats de ma feuille "Enregistrements" en fonction du mois (colonne R) et de l'année (colonne S) que j'aurais choisis en cellules B43 et C43 de ma feuille "Strategie"
2 - Obtenir dans les résultats filtrés le min, le max et la moyenne
3 - Afficher les résultats dans les cases b50, b51 et b52 de ma feuille "Strategie"

Pour ce qui est de faire le filtre dans la feuille d'enregistrements, pas de problème avec autofiltermode.

Par contre, ensuite, si j'applique par exemple le max sur les résultats filtrés de la feuille "Enregistrement" sur le range en colonne H (dans mon test j'ai juste utilisé le mois de décembre (12)), il prend en compte quand même toute les cellules qu'elles soient filtrées ou non. J'ai cherché du côté de array pour essayer de voir si je pouvais garder en mémoire seulement les données filtrées, mais je n'ai pas bien compris semble-t-il comment travailler encore avec les array.

Est-ce que quelqu'un a une suggestion pas trop compliqué pour me faire avancer un peu dans mes recherches. Je veux apprendre car, le code VBA est vraiment un outil excellent pour travailler avec excel! Et je connais les fonctions excel native Min et Max conditionnelles, mais ce sont des fonctions matricielles et elles sont lourdes à opérer avec le temps, d'où mon intérer supplémentaire pour le VBA.

Ci joint ma feuille excel. Mon Code est dans le module 8, le reste des modules étant des tests pour apprendre le VBA ... :0)

[parmi]

Bonjour,

Je n'ouvre pas les fichiers avec macro... (voir règlements du site)
Si la formule n'a pas besoin d'être inscrite comme telle, tu pourrais inscrire les valeurs en utilisant xlCellTypeVisible (à voir dans ton aide)

Par exemple, les données à calculer sont en A
Modifie la plage selon tes besoins

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
    Range("E1") = Application.WorksheetFunction.Min(Range("A2:A20").SpecialCells(xlCellTypeVisible))
    Range("F1") = Application.WorksheetFunction.Max(Range("A2:A20").SpecialCells(xlCellTypeVisible))
    Range("G1") = Application.WorksheetFunction.Average(Range("A2:A20").SpecialCells(xlCellTypeVisible))

[jpboucher]

Super, j'obtiens le résultat que je souhaitais. Et dire que je cherchais depuis des heures...

Vite comme ça, ensuite, comment faire en sorte de prendre le résultat que j'affiche dans le message, et le coller dans ma feuille "Stratégie" dans un cellule particulière? Si j'y arrive, j'aurais résolut un bon problème...

Voici mon code (merci pour l'info concernant les doc avec macro... )

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub max_enregistrement()
Dim TheMax As Double
'TheMax = WorksheetFunction.Max(Range("h4:h1048572"))
TheMax = WorksheetFunction.max(Range("h4:h1048572").SpecialCells(xlCellTypeVisible))
MsgBox TheMax
End Sub
 
Sub filtrer_enregistrement()
Worksheets("Enregistrements").Range("$A$3:$AB$17").AutoFilter Field:=17, Criteria1:="12", VisibleDropDown:=False
Call max_enregistrement
Worksheets("Enregistrements").AutoFilterMode = False
End Sub

C'est bon, j'ai trouvé avec un peu de recherche... super!!! Merci Merci!

[Philippe Tulliez]

Bonjour,
Les fonctions BDMAX, BDMIN et BDMOYENNE font cela très bien sans passer par un filtre. Il suffit de définir une zone de critères.

En convertissant une plage de données en tableau, il est possible également de faire une synthèse (Min, Max, Moyenne, Somme, Nombre, etc.) par colonne. Cette synthèse s'adapte en fonction des lignes filtrées.

Utiliser du VBA pour faire cela est parfaitement inutile.

[Philippe Tulliez]

Bonjour,

J'ai ouvert ton classeur (ce que je ne fais que rarement) afin de me rendre compte de visu si ce que je disais plus haut était exact.

1 - Filtrer les résultats de ma feuille "Enregistrements" en fonction du mois (colonne R) et de l'année (colonne S) que j'aurais choisis en cellules B43 et C43 de ma feuille "Strategie"

Pourquoi as-tu besoin d'extraire dans deux colonnes distinctes le mois (en chaîne de caractères en plus) et l'année d'une date qui se trouve dans une autre colonne.
Pour favoriser le filtre ?
Si tu as une version supérieure à Excel 2003, le filtre simple regroupe par année, par mois et par jour toutes les dates contenues dans une colonne.

2 - Obtenir dans les résultats filtrés le min, le max et la moyenne

Si tu converti ta plage de données en tableau, il suffit de cocher l'option Ligne des totaux du groupe Options de style de tableau de l'onglet [Création] de l'onglet contextuel [Outils de tableau]
Ensuite il est possible de choisir une synthèse (Max, Min, Moyenne, etc) pour chaque colonne du tableau.

3 - Afficher les résultats dans les cases b50, b51 et b52 de ma feuille "Strategie"

C'est là qu'il faut penser plutôt aux fonctions de la catégorie Base de données comme BDMAX, BDMIN, BDMOYENNE car c'est leur rôle d'être utilisées pour les tableaux de bord.

Définir une zone des critères dans la feuille nommée [Stratégie] et dans ce cas précis utiliser un critère nommé dont la formule est

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ET(ANNEE(Enregistrements!F4)=2015;MOIS(Enregistrements!F4)=12)

F4 de la feuille nommée [Enregistrement] étant la colonne dont l'étiquette est Période livraison qui est semble-t-il le critère souhaité
Pour rendre dynamique ce tableau de bord, il suffira de remplacer les constantes 2015 et 12 par la référence à des cellules qui contiendront ces valeurs .
En supposant que la zone des critères soit en cellules B41:B42 de la feuille nommée [Strategie], voici un exemple de la formule à placer en cellule B50 pour obtenir la moyenne des Base $can (colonne G) de la feuille [Enregistrement]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=BDMOYENNE(Enregistrements!A3:N17;Enregistrements!G3;Strategie!B41:B42)

Ceci n'est qu'un exemple, il est évident que nommer ensuite les plages de cellules rendra plus clair la lecture des formules.

Ceci juste pour illustrer l'inutilité de l'emploi de code VBA là où excel a des fonctions et des outils parfaitement adaptés à l'établissement de tableau de bord (C'est le rôle de ce programme)

[jpboucher]

Un grand merci Philippe

Je vais évaluer en détail ta suggestion. Il y a beaucoup d'info. Je t'en donnerai des nouvelles. Je reviens quand même à l'idée que je veux maitriser davantage VBA.

J'utilise depuis très longtemps Excel et je m'en sors très bien avec de nombreuses fonctions. C'est qu'avec le temps, je réalise que certaines fonctions, comme par exemple celles matricielles, posent souvent problème car elles alourdissent et ralentissent dangereusement mes tableaux de bord.

Dans cet ordre d'idée, je pousse davantage vers le VBA pour m'ouvrir plus de possibilités.

Je te reviens avec mes conclusions sur tes suggestions.

Encore merci!

[Philippe Tulliez]

Bonjour,

Je vais évaluer en détail ta suggestion. Il y a beaucoup d'info. Je t'en donnerai des nouvelles. Je reviens quand même à l'idée que je veux maitriser davantage VBA.

Je comprends ton envie de maîtriser davantage le VBA. Cependant, il est indéniable qu'une bonne connaissance d'excel, de ces outils, de ces fonctions, etc. fera de toi un véritable expert plutôt que de bricoler des lignes de VBA qui ne font qu'apporter un résultat identique là où une fonction native du produit placée dans une cellule apporte le même résultat.

Refaire en VBA ce qu'excel offre nativement est pour moi de la débauche d'énergie et n'apporte rien de plus à celui qui crée ces lignes si ce n'est peut-être, et je ne dis pas que c'est ton cas, le plaisir de se faire passer pour un génie auprès de ceux qui ne connaissent pas le VBA (comme l'écrivait Boileau "Un sot trouve toujours un plus sot qui l'admire").

J'utilise depuis très longtemps Excel et je m'en sors très bien avec de nombreuses fonctions. C'est qu'avec le temps, je réalise que certaines fonctions, comme par exemple celles matricielles, posent souvent problème car elles alourdissent et ralentissent dangereusement mes tableaux de bord.

Les fonctions de la catégorie Base de données ne sont pas des fonctions matricielles et offrent des perspectives énormes pour élaborer des tableaux de bord

En résumé, j'utilise évidemment abondamment le VBA mais je pense d'abord à la façon dont je peux le faire facilement avec les outils et fonctions natives du produit et si je dois le faire en VBA, j'exploiterai au maximum ce qui existe déjà dans excel que je traduis en VBA avec les méthodes appropriées ce qui finit par se résumer à de courtes lignes de programmation au lieu de la création d'usines à gaz.