Moyenne et mediane conditionnelles avec filtres

**R4gtl** · 19/07/2010, 15h33

Bonjour a tous.
J'ai automatisé une bd a traiter, et je bloque sur 2 éléments.

La base est du type :
Metiers %BtoB %BtoC
Boulanger 60 40
Macon 0 100
Peintre 100 0
Garagiste 50 50

En filtrant la colonne metiers, je souhaite avoir la moyenne et la mediane des données affichées superieures a 0.
Comme je n'y parvient pas par des formules, je me doute qu'il existe un moyen de le faire en VBA.
Vu la taille de la base (ici, ce n'est qu'un tout petit echantillon^^), je ne peux me permettre de faire les calculs a la main, en filtrant tout. La seule chose que je doit selectionner, c'est le metier.

en plus, c'est assez urgent

Merci d'avance (je ne peux envoyer de fichiers, le proxy bloque trop)

PS : meme si c'est pas du vba, si qqun pouvait en plus, m'expliquer l'interet de la fonction décaler dans ce type de formule que j'applique, sans comprendre.
=SOMMEPROD((SOUS.TOTAL(3;DECALER($AJ$3;LIGNE($AJ$3:$AJ$919)-3;0)))*($AQ$3:$AQ$919=7)) .... (en clair, nombre d'elements de la plage visible, apres filtrage de la plage des metiers)

**mercatog** · 19/07/2010, 16h03

Une première approximation catégorie en A, valeurs en B (pour la moyenne)

Code :

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

1
2
3
4
5
6
7
8
9
Dim Plage As Range, PlageFiltre As Range
Dim m As Double
Set Plage = Range("B1:" & Cells(Rows.Count, "B").End(xlUp).Address)
Set PlageFiltre = Plage.SpecialCells(xlCellTypeVisible)
m = Application.Average(PlageFiltre)
MsgBox m
Columns(2).FormatConditions.Delete
PlageFiltre.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=m
PlageFiltre.FormatConditions(1).Interior.ColorIndex = 3

edit en ligne 1, titres

**R4gtl** · 20/07/2010, 10h02

merci, mais ca ne fontionne pas. J'ai droit a un beau message d'erreur "13" sur

Code :

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

m = Application.Average(PlageFiltre)

**mercatog** · 20/07/2010, 10h34

Parce que le code est adapter. le code proposé fonctionne sur la feuille active avec la moyenne calculée sur la colonne B et le filtre appliqué en colonne A

**R4gtl** · 20/07/2010, 11h42

Effectivement.
J'ai réussi a faire apparaitre l'info dans une case, et pas dans une boite de message, mais, le resultat doit apparaitre sur une autre feuille.
J'ai testé (en essayant de garder la forme de ta proposition)

Code :

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

Set Plage = Range("Base_cible_Cma_codifiée!BS$3:" & Cells(Rows.Count, "BS").End(xlUp).Address)

Mais j'ai une réponse abhérente (moyenne 27 a la place de 90)
et

Code :

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

Set Plage = Range("Base_cible_Cma_codifiée!BS3:" & Cells(Rows.Count, "Base_cible_Cma_codifiée!BS").End(xlUp).Address)

Et j'ai encore une erreur 13.

Je ne peux pas afficher les calculs directement sur la feuille de données, car de nouvelles données peuvent y etre ajouté a tous moments. (sinon, ca aurait été trop simple).
Et mes cours de vba sont trop loin pour m'adapter aussi facilement.
Merci d'avance

apres verification (sur un petit echantillon ^^), en fait, la macro ne donne pas le bon resultat, puisqu'il calcule la moyenne sur toutes les cellules visibles, y compris les vides et les nulles. L'objectif etant de calcurer la moyenne (et la mediane) que sur les resultats supérieurs a 0.
(pour obtenir comme resultat type, pour les entreprises faisant du commerce BtoC, elles font en moyenne, x% de BtoC)
Désolé de compliquer les choses

**mercatog** · 20/07/2010, 13h01

Ci-joint code modifié pour tenir compte des explications supplémentaire:
La colonne A, colonne de catégorie
La colonne B, colonne des valeurs à moyenner
le code ci-après à mettre dans un module standard
on appelle la fonction par exemple par:

Code :

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

1
2
3
Private Sub CommandButton1_Click()
Filtrage Range("G26").Value 'ou dans G26 se trouve la catégorie
End Sub

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Public Sub Filtrage(ByVal Crit As String)
Dim Plage As Range, PlageFiltre As Range
Dim m As Double
Dim LastLig As Long
With Sheets("Base_cible_Cma_codifiée")
    .Range("A1:B1").AutoFilter
    LastLig = .Cells(Rows.Count, "A").End(xlUp).Row
    Set Plage = .Range("A1:B" & LastLig)
    With Plage
        .AutoFilter field:=1, Criteria1:=Crit
        .AutoFilter field:=2, Criteria1:=">0"
    End With
    If Plage.SpecialCells(xlCellTypeVisible).Count > 2 Then
        Set PlageFiltre = .Range("B2:B" & LastLig).SpecialCells(xlCellTypeVisible)
        m = Application.Average(PlageFiltre)
        MsgBox m
    End If
    Set Plage = Nothing
    Set PlageFiltre = Nothing
End With
End Sub

**R4gtl** · 20/07/2010, 15h41

ca ne fonctionne pas chez moi. j'ai meme tenté de faire une base test, et le resultat est le meme.
Ca me déselectionne toutes les données (jai failli bousiller la base avec ca :/), meme en rentrant une catégorie dans le "G26", sans aucun calcul.
En plus, la notion de private, public sub, ca me dépasse un peu.
N'y aurait il pas plus simple, pour avoir cette moyenne, des éléments positifs affichés, et ce quelque soit le filtre appliqué? (j'ai plus de 200 colonnes).

En tout cas, chapeau pour la réactivité, ca fait plaisir

**mercatog** · 20/07/2010, 17h09

un fichier test est nécessaire

**R4gtl** · 20/07/2010, 18h52

Voila un extrait du fichier.
Le calcul peut se faire via un bouton (pour alleger les manipulations)
Merci d'avance
J'ai excel 2007

**mercatog** · 20/07/2010, 19h16

Ton fichier est loin de tes explications!
Bon, tu crée un module public, tu y colle ceci

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Public Sub Filtrage(ByVal Colonne As Integer, cible As String) 'Colonne, colonne de catégorie, cible adresse de la cellule cible
Dim Plage As Range, PlageFiltre As Range
Dim m As Double
Dim LastLig As Long
 
Application.ScreenUpdating = False
With Sheets("Feuil1")
    .Range("A1:IV1").AutoFilter
    LastLig = .Cells(Rows.Count, "A").End(xlUp).Row
    Set Plage = .Range("A1:IV" & LastLig)
    Plage.AutoFilter field:=Colonne, Criteria1:=">0"
    If Plage.SpecialCells(xlCellTypeVisible).Count > 2 Then
        Set PlageFiltre = .Range(.Cells(2, Colonne), .Cells(LastLig, Colonne)).SpecialCells(xlCellTypeVisible)
        Sheets("Feuil2").Range(cible) = Application.Average(PlageFiltre)
        Sheets("Feuil2").Range(cible).Offset(1, 0) = Application.Median(PlageFiltre)
    End If
    .Range("A1:IV1").AutoFilter
    Set Plage = Nothing
    Set PlageFiltre = Nothing
End With
End Sub

Tu crée un CommandButton sur ta Feuil2 et y colle ce code

Code :

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

1
2
3
4
5
6
Private Sub CommandButton1_Click()
Dim i As Byte
For i = 0 To 3
   Filtrage 4 + i, CStr("D" & 8 + 4 * i)
Next i
End Sub

**R4gtl** · 21/07/2010, 10h08

Arf, désolé si je m'etait mal expliqué.
En tout cas, le resultat avance, mais (il en faut un).
La macro annule sytématiquement la selection fait par le filtrage.
Du coup, il fait les calculs sur la totalité de la base >0, résultats que j'avais deja par des fonctions (dans le 1er cadre des resultats).
Il retire carrement le filtre.
il doit manquer un élément dans la macro, pour ne garder que la selection et faire les calculs que sur les éléments affichés apres filtrage.

**mercatog** · 21/07/2010, 10h39

De quel filtre tu parles; tu es avare en explication et je ne peux pas deviner ce que tu as dans la tête

**R4gtl** · 21/07/2010, 11h53

Le filtrage de base d'excel. Je selectionne via les index (trier/filtrer>filtrer) sur la colonne de titres, je selectionne ce qui m'interesse (une activité), et tout le reste disparait.
Si je selectionne "alimentation", je ne souhaite avoir les calculs que sur ce qui s'affiche (un peu comme les sous.totaux), mais ici, avec la condition "pour les données >0", afin d'exclure les cellules vides ou nulles.
Heu, j'espere que j'ai été plus clair (je croyais l'avoir été auparavant

)

**mercatog** · 21/07/2010, 12h23

Change ta sub précédemment proposée comme ceci:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Public Sub Filtrage(ByVal Colonne As Integer, cible As String) 'Colonne, colonne de catégorie, cible adresse de la cellule cible
Dim Plage As Range, PlageFiltre As Range
Dim m As Double
Dim LastLig As Long
 
Application.ScreenUpdating = False
With Sheets("Feuil1")
    LastLig = .Cells(Rows.Count, "A").End(xlUp).Row
    Set Plage = .Range("A1:IV" & LastLig)
    Plage.AutoFilter field:=Colonne, Criteria1:=">0"
    If Plage.SpecialCells(xlCellTypeVisible).Count > Columns.Count Then
        Set PlageFiltre = .Range(.Cells(2, Colonne), .Cells(LastLig, Colonne)).SpecialCells(xlCellTypeVisible)
        Sheets("Feuil2").Range(cible) = Application.Average(PlageFiltre)
        Sheets("Feuil2").Range(cible).Offset(1, 0) = Application.Median(PlageFiltre)
    Else
        Sheets("Feuil2").Range(cible) = 0
        Sheets("Feuil2").Range(cible).Offset(1, 0) = 0
    End If
    Plage.AutoFilter field:=Colonne
    Set Plage = Nothing
    Set PlageFiltre = Nothing
End With
End Sub

**R4gtl** · 21/07/2010, 14h28

Mercatog, t'es un pro, ca fonctionne nickel.
J'ai failli lacher un cri de victoire quand j'ai vu le resultat (mais, il y a des entretiens dans le bureau voisin).

J'imagine bien que tu as du raler un peu en te disant que je ne savais pas m'expliquer, ou ne savais pas ce que je voulais. Je te remercie du temps que t'as passé.
En tout cas, ce site, je le garde dans mes favoris

**mercatog** · 21/07/2010, 14h43

Juste une petite rectification au cas où tu auras à travailler dans une autre version d'excel autre que excel 2003

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Public Sub Filtrage(ByVal Colonne As Integer, cible As String) 'Colonne, colonne de catégorie, cible adresse de la cellule cible
Dim Plage As Range, PlageFiltre As Range
Dim m As Double
Dim LastLig As Long
 
Application.ScreenUpdating = False
With Sheets("Feuil1")
    LastLig = .Cells(Rows.Count, "A").End(xlUp).Row
    Set Plage = .Range(.Cells(1, 1), .Cells(LastLig, Columns.Count))
    Plage.AutoFilter field:=Colonne, Criteria1:=">0"
    If Plage.SpecialCells(xlCellTypeVisible).Count > Columns.Count Then
        Set PlageFiltre = .Range(.Cells(2, Colonne), .Cells(LastLig, Colonne)).SpecialCells(xlCellTypeVisible)
        Sheets("Feuil2").Range(cible) = Application.Average(PlageFiltre)
        Sheets("Feuil2").Range(cible).Offset(1, 0) = Application.Median(PlageFiltre)
    Else
        Sheets("Feuil2").Range(cible) = 0
        Sheets("Feuil2").Range(cible).Offset(1, 0) = 0
    End If
    Plage.AutoFilter field:=Colonne
    Set Plage = Nothing
    Set PlageFiltre = Nothing
End With
End Sub

**R4gtl** · 21/07/2010, 15h37

j'ai quelques difficultés pour l'adapter sur ma base, vu que les colonnes sur lesquels les calculs se font, ne sont pas immédiatement a coté de la colonne filtrée (la fichier joint, etait qu'un echantillon), et qu'elles ne sont pas explicitement nommée dans la macro.
Dans la macro, comment je peux préciser où les calculs doivent commencer?
Pour info, dans ma base, ce sont les colonnes BS3:BS919 à BV3:bv919.

**mercatog** · 21/07/2010, 16h07

La procédure Filtrage à 2 paramètres: colonne (integer) et cible (string)
colonne: désigne ta colonne de données à filtrer par ">0"
cible: l'adresse de la cellule où tu veux transcrire le résultat de la moyenne
et de faite la médiane se transcrit dans la cellule en bas de la cellule cible

si on fait:

Code :

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

1
2
3
Private Sub CommandButton1_Click()
Filtrage 17,"E25"
End Sub

le code filtre sur la colonne 17 "Q" de Feuil1 et transcrit la moyenne en E25 et la médiane en E26 de Feuil2

**R4gtl** · 22/07/2010, 09h45

Merci, j'ai pu adapter la macro (l'avant derniere, car la derniere me met une erreur) a ma base, grace a tes conseils. J'ai malgré tout reperé un defaut. Si il y a moins de 3 réponses positives, la macro renvoit systématiquement 0.

En attendant, j'ai mis une formule qui m'indique le nombre de cellule positive. Si moins de 3, je fais a la main

En tout cas, ca me force a m'y remettre au vba (8 ans sans en avoir fait, ca s'oublie vite)

**mercatog** · 22/07/2010, 10h35

j'ai pu adapter la macro (l'avant derniere, car la derniere me met une erreur)

De quel code tu parle? et quel est l'erreur?
Sinon, une petite retouche sur la procédure Filtrage

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Public Sub Filtrage(ByVal Colonne As Integer, cible As String) 'Colonne, colonne de catégorie, cible adresse de la cellule cible
Dim Plage As Range, PlageFiltre As Range
Dim m As Double
Dim LastLig As Long
 
Application.ScreenUpdating = False
With Sheets("Feuil1")
    LastLig = .Cells(Rows.Count, "A").End(xlUp).Row
    Set Plage = .Range(.Cells(1, 1), .Cells(LastLig, .Columns.Count))
    Plage.AutoFilter field:=Colonne, Criteria1:=">0"
    If Plage.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
        Set PlageFiltre = .Range(.Cells(2, Colonne), .Cells(LastLig, Colonne)).SpecialCells(xlCellTypeVisible)
        Sheets("Feuil2").Range(cible) = Application.Average(PlageFiltre)
        Sheets("Feuil2").Range(cible).Offset(1, 0) = Application.Median(PlageFiltre)
    Else
        Sheets("Feuil2").Range(cible) = 0
        Sheets("Feuil2").Range(cible).Offset(1, 0) = 0
    End If
    Plage.AutoFilter field:=Colonne
    Set Plage = Nothing
    Set PlageFiltre = Nothing
End With
End Sub

et si tu mettais le code que tu as adapté? parce que j'avais testé et ça fonctionnait dans tous les cas de figure, indépendamment du nombre de lignes filtrées.

Moyenne et mediane conditionnelles avec filtres [XL-2007]

Macros et VBA Excel

Discussions similaires

Partager

Partager