Discussion :

[Novice72]

Bonjour à toutes et à tous,

J'ai une plage d'un tableau ( que je considère à dimension fixe ici ) par exemple F7:Q18.
Sur cette plage, il n'y a malheureusement pas que des nombres ( ce serait trop simple ). Il y a aussi des croix ( représentées pas des "X" ) et aussi des cellules vides ( vierges ).
Si je fais moyenne(F7:Q18) , ma moyenne est faussée car elle considère les cellules vides et les "X" comme 0.

Voici donc ma question : Comme récupérer uniquement les valeurs d'une plage afin d'en calculer une moyenne ( en VBA ) ?



Merci d'avance.

Novice72

[Philippe Tulliez]

Bonjour,

Si je fais moyenne(F7:Q18) , ma moyenne est faussée car elle considère les cellules vides et les "X" comme 0.

Je suis très étonné de ce que tu écris car la fonction native d'excel MOYENNE ne prend justement pas en compte les données alphanumériques ni les cellules vides dans son calcul.

En vba

Code :

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

MsgBox Evaluate("=AVERAGE(H2:H9)")

ou

Code :

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

MsgBox Application.WorksheetFunction.Average(Range("H2:H9"))

[Nicolas JACQUIN]

Bonjour,

Code :

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

MsgBox Application.WorksheetFunction.Average(Range("F7:Q18"))

A bientôt

[Novice72]

Merci à vous 2 Philippe et Nicolas,

Je ne comprends pas ce qui j'ai fais avant. Maintenant ça fonctionne.

Encore merci.

NOVICE72

[Novice72]

(re)bonjour,

Pouvez-vous me dire pourquoi ce code ne fonctionne pas ?

Code :

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

1
2
 
MsgBox Application.WorksheetFunction.Average(Range(cells(7,6),cells(8,17))

En effet, j'ai juste remplacé ("F7:Q18") par ( cells(7,6),cells(8,17) ) comme je le fais souvent.

quand je verrai mon erreur de syntaxe, le but sera de remplacer 8 et 17 par "j" et "p" afin d'avoir un tableau variable tout en maintenant le coin haut gauche fixe.


Merci d'avance.


NOVICE72

[Nicolas JACQUIN]

Code :

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

MsgBox Application.WorksheetFunction.Average(Range(Cells(7, 6), Cells(8, 17)))

le nombre de parenthèse

[Novice72]

(re)bonjour,

Comment cela se fait-il que je ne l'ai pas vu ? ça m'arrive assez souvent mais là j'avais cru avoir bien regardé.......

Merci.



Novice72

[Novice72]

Erreur d'exécution '1004'

Impossible de lire la propriété Average de la classe Worksheetfunction

Un problème se pose. sur une feuille excel classique avec des nombres déjà écrit, la macro fonction très bien car il y a au moins une cellule non vide.

Par contre, je voulais créer un tableau vierge dans un premier temps ( toutes les cellules sont vides ) et préparer une autre cellule qui calculera la moyenne ( du tableau ) dès lors qu'une valeur est entrée. Et là problème au lancement de la macro qui fait le tableau et le reste.....

Quelqu'un peut-il m'aider ?


Cordialement

Novice72

[Philippe Tulliez]

Bonjour,

Comment cela se fait-il que je ne l'ai pas vu ?

Si tu ne l'as pas vu, pourtant au moment où tu as encodé cette ligne tu as certainement vu que ta ligne était en rouge ce qui signifie qu'il y a une erreur de syntaxe

Une petite remarque sur cette ligne de code encore.
Telle qu'elle est écrite elle calculera la moyenne de la plage référencée (ici F7:Q18) de la feuille active. Si tu veux calculer la plage d'une feuille et d'un classeur précis, il est important de préciser la parentalité complète de l'objet Range soit

Code :

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

1
2
3
With ThisWorkbook.Worksheets("Feuil1")
  MsgBox Application.WorksheetFunction.Average(.Range(.Cells(7, 6), .Cells(18, 17)))
End With

[Novice72]

(re)bonjour Philippe,

Merci pour ton aide. Toutefois, mon problème n'est pas résolu.
N'y-a-t-il qu'une solution ? celle qui consisterait à lancer la macro une fois le tableau compléter ( au moins en partie )???

Sur une feuille excel classique, lorsqu'un fait la moyenne d'un ensemble de cellules vides, la "cellule moyenne" affiche "Div/0!" . Si après on entre les nombre, le div/0! devient alors la vraie moyenne et il n'y a pas blocage. Par contre en VBA Si...


Merci encore.


Novice72

[Philippe Tulliez]

Bonjour,

Sur une feuille excel classique, lorsqu'un fait la moyenne d'un ensemble de cellules vides, la "cellule moyenne" affiche "Div/0!"

La fonction SIERREUR permet d'éviter ce genre de soucis

Exemple
Avec Excel

Code :

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

=SIERREUR(MOYENNE(C6:C15);"Pas malin de diviser par 0")

ou

Code :

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

=SIERREUR(MOYENNE(C6:C15);"")

ou encore

Code :

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

=SIERREUR(MOYENNE(C6:C15);0)

en VBA à utiliser avec WorksheetFunction ou Evaluate

Code :

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

=IFERROR(AVERAGE(C6:C15),"Pas malin de diviser par 0")

[Novice72]

Merci pour ta ligne de code Philippe.
Toutefois, j'ai fais plusieurs essais :
avec ou sans le "="
en mettant une cellule ( cells (20,20) ) devant le égal
en positionnant ta ligne ( avec toutes les combinaisons ) à diverses endroits de

Code :

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

1
2
3
4
 
With ThisWorkbook.Worksheets("Feuil2")          ' j'ai adaptée la feuille
  cells(20,20)= Application.WorksheetFunction.Average(.Range(.Cells(7, 6), .Cells(18, 17)))    ' ici j'ai préféré que la cellule T20 ( par exemple ) affiche la moyenne plutôt qu'une boite
End With

ensuite :
j'ai écrit ta ligne avant Cells (20,20) puis après


Bref, je n'arrive pas à résoudre ce problème de div/0


Merci d'avance.


Novice72

[Nicolas JACQUIN]

Est ce que cela te conviendrai ??

Code :

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

1
2
3
4
5
6
7
8
9
Sub moyenne_plage()
 
If Application.CountA(Range("F7:Q18")) < 2 Then 'si tu n'as pas au minimum 2 cellules notées
MsgBox "Notes insuffisantes pour exécuter la moyenne"
Exit Sub 'on sort de la procédure
End If
MsgBox "La moyenne est de" & " " & Application.WorksheetFunction.Average(Range("F7:Q18"))
 
End Sub

[Novice72]

Bonjour Nicolas,

Ton idée est bonne mais le souci c'est qu'au départ on a toujours 0 puisque la macro créer le tableau, et par conséquent toutes les cellule sont vides.
Ce que je recherche, c'est:
1) Je crée un tableau suivant des sélections faites depuis un "tableau général"
2) Je prépare 90% des cellules ( les autres sont du texte ) à leur "Mise en Forme Conditionnel = MFC ". Comme ça, après, lorsque j'écris dans une cellule, la cellule devient Orange, jaune, vert clair ou vert foncé ( écriture et fond de la même couleur ).
3) Ensuite, dans une autre cellule, une moyenne doit être faites ( avec la même MFC ).

Et, c'est bien au point 3 que mon problème se pose puisque toutes les cellules sont vides.

Remarque : sur Excel ( SANS LE VBA ) j'arrive à avoir DIV/0! ( et ça je veux bien l'avoir car il suffit de l'afficher en blanc....... ) mais là le programme se bloque sur une erreur 1004 qui explique que la procédure ( ou propriété je ne me souviens plus trop ) est bloquée...


Encore merci pour ton aide.


Novice72

[Philippe Tulliez]

Bonjour,

Remarque : sur Excel ( SANS LE VBA ) j'arrive à avoir DIV/0! ( et ça je veux bien l'avoir car il suffit de l'afficher en blanc....... ) mais là le programme se bloque sur une erreur 1004 qui explique que la procédure ( ou propriété je ne me souviens plus trop ) est bloquée...

On se demande à quoi cela sert de répondre.
Pour éviter le message d'erreur, il y a la fonction SIERREUR et de plus j'ai publié des exemples quant à son utilisation.

Voici un nouvel exemple où la formule

Code :

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

=SIERREUR(MOYENNE(Feuil1!$D$2:$D$9);"")

est placée en cellule B10 de la feuille nommée Resultat et calcule la moyenne de la plage $D$2;$D$9 de la feuille nommée Feuil1

Code :

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

1
2
3
4
5
6
7
8
9
10
 Dim rngValue As Range  ' Plage à calculer
 Dim rngResult As Range            ' Cellule contenant la formule
 Const myFormula As String = "=IFERROR(AVERAGE(<plage>),"""")"  ' Modèle formule
 With ThisWorkbook
  With .Worksheets("Feuil1")
   Set rngValue = .Range(.Cells(2, 4), .Cells(9, 4))
  End With
  Set rngResult = .Worksheets("Resultat").Range("B10")
 End With
 rngResult.Formula = Replace(myFormula, "<plage>", rngValue.Address(External:=True)) ' Ecrit la formule en remplaçant la balise <plage> par l'adresse complète de rngValue

Si l'on souhaite ne conserver que le résultat, il suffit de remplacer la ligne 10 par

Code :

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

1
2
3
4
 With rngResult
 .Formula = Replace(myFormula, "<plage>", rngValue.Address(External:=True)) ' Ecrit la formule en remplaçant la balise <plage> par l'adresse complète de rngValueEnd Sub
 .Value = .Value ' Equivalent du Copier/Collage  Special-valeur
 End With

[Novice72]

Merci Philippe,

Avec ce code je commence à mieux comprendre ( beaucoup mieux ).
En effet, quand je vois : =SIERREUR............
ceci signifie pour moi que cela s'écrit dans une cellule excel mais moi je recherchais du VBA.

La c'est plus clair.

Je continue de regarder, d'essayer et te redis.


Encore merci.


Novice72

[Novice72]

test moyenne macro sans erreur.xlsm

(re)Bonjour Philippe,

Je n'avais pas vu ton "fil". Je l'ai regardé, je l'ai adapté.

Il n'y a pas d'erreur de syntaxe mais le résultat ne s'affiche pas.

Pour être plus complet, j'ai crée un fichier test ( en pièce jointe ). j'ai entièrement copié ton code ( copier/coller ) 2 fois.
Le premier "collage" je l'ai mis en "commentaire" afin de faciliter la comparaison avec les modifications apportées au 2ème "collage" ( dans le cas d'une erreur )

eh ben non ( je suis d'origine normande ). PAS D'ERREUR DE SYNTAXE OU AUTRE mais PAS DE MOYENNE AFFICHEE NON PLUS

Encore merci pour ton aide.

Novice72

[Philippe Tulliez]

Bonjour Novice72,
Sauf si je le précise explicitement, je teste toujours les procédures que je publie et je confirme que cela fonctionne.
As-tu effectué un test avec des valeurs dans la plage de cellules concernée ?
Il est évident que si les cellules dont on veux connaître la moyenne sont vides ou contiennent du texte, il n'y aura rien dans la cellule contenant le calcul puisque dans mon exemple la fonction SIERREUR (IFERROR en VBA) revoie une chaîne vide.
Si tu remplaces la chaîne vide de la constante myFormula (ligne 3 dans mon exemple) par un texte comme ci-dessous en rouge, tu devrais voir ce texte

Code :

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

Const myFormula As String = "=IFERROR(AVERAGE(<plage>),""Aucune valeur"")"  ' Modèle formule

[Marc-L]

Bonjour,

pour un débutant en VBA, la voie la plus simple est de d'abord commencer du côté d'Excel
juste avec des formules de feuille de calculs !
Une fois ces formules fonctionnelles, il est simple et bien plus rapide à l'exécution
d'utiliser directement ces formules via par exemple la propriété FormulaLocal
au lieu de tenter de vaines boucles forcément plus lentes …

Donc en indiquant ici au moins ces formules fonctionnelles et leurs plages de cellules associées …

Autre point essentiel :  ne pas utiliser WorksheetFunction, Application suffisant largement et
dont le résultat peut être contrôlé via les fonctions IsError et IsNumeric …

___________________________________________________________________________________________________________

Je suis Paris, Egypte, Stockholm, London, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

[Novice72]

Bonjour Novice72,
Sauf si je le précise explicitement, je teste toujours les procédures que je publie et je confirme que cela fonctionne.
As-tu effectué un test avec des valeurs dans la plage de cellules concernée ?

(re)bonjour Philippe,


Oui j'ai bien sûr testé avec des valeurs dans la plage concernée.
Par contre, quand j'ajoute "Aucune valeur" dans la ligne 3 de ton exemple ( post précédent ), IL S'AFFICHE "AUCUNE VALEUR" ( alors qu'il y en a 50... )
Le mieux, si je peux me permettre c'est que tu regardes mon fichier joint ( je ne te demandes pas de le corriger mais de m'expliquer dans ce cas où est l'erreur )

PS: en effet, quelqu'un sur ce forum a pour devise "il ne faut pas donner du poisson a quelqu'un qui a faim mais lui apprendre à pêcher". Cette bonne devise ne s'applique peut-être pas si la leçon a été effectuée en pleine mer et qu'on se retrouve en eau douce...

Voilà pour cette métaphore.
Franchement, je ne vois vraiment pas ce qui coince dans la comparaison des 2 programmes ( le tien que j'ai copié/coller et le mien , celui qui a été adapté ) qui se trouve dans le fichier joint.

Encore merci.

Novice72test moyenne macro sans erreur2.xlsm