Conseils pour calcul de moyenne en VBA selon critères dans autre colonne
11 réponses
plexus
Bonjour,
Je veux faire la moyenne de valeurs situ=E9es dans une colonne (J) selon
la pr=E9sence dans la colonne P de la lettre "M".
Ma sub fonctionne, mais je me demandais s'il n'y a pas plus simple et
efficace.
D'autre part, j'ai remarqu=E9 que si je ne d=E9clare pas mon tableau
tabval en variant mais en double, j'obtiens un r=E9sultat tout =E0 fait
diff=E9rent (environ la moiti=E9). Auriez vous une id=E9e du pourquoi de ce
ph=E9nom=E8ne ? (pour ma culture personelle)
Sub essai_moyenne()
Dim derligne As Long
Dim fintab As Long, I As Long
Dim tabzone() As Variant
Dim tabval() As Variant
derligne =3D Range("A:A").Find("*", , xlFormulas, , , xlPrevious).Row
fintab =3D derligne -
12 'les donn=E9es
commencent =E0 la ligne 13
'% si M en colonne P
ReDim tabzone(1 To fintab, 1 To 2)
a =3D 1
ReDim tabval(1 To a)
For L =3D 1 To fintab
tabzone(L, 1) =3D Sheets(1).Cells(12 + L, 10).Value
'les donn=E9es commencent =E0 la ligne 13
tabzone(L, 2) =3D Sheets(2).Cells(12 + L, 16).Value
'les donn=E9es commencent =E0 la ligne 13
If InStr(1, tabzone(L, 2), "M") <> 0 Then
tabval(a) =3D tabzone(L, 1)
a =3D a + 1
ReDim Preserve tabval(1 To a)
End If
Next L
With Application.WorksheetFunction
moy =3D .Average(tabval)
End With
Noms de champ: Critere (P13:P1000) et Valeur (J13:J1000) ou noms de champ dynamique.
x = Evaluate("=average(if(critere=""M"",valeur))")
JB
On 15 fév, 16:55, "plexus" wrote:
Bonjour,
Je veux faire la moyenne de valeurs situées dans une colonne (J) selon la présence dans la colonne P de la lettre "M". Ma sub fonctionne, mais je me demandais s'il n'y a pas plus simple et efficace.
D'autre part, j'ai remarqué que si je ne déclare pas mon tableau tabval en variant mais en double, j'obtiens un résultat tout à fait différent (environ la moitié). Auriez vous une idée du pourquoi de ce phénomène ? (pour ma culture personelle)
Sub essai_moyenne()
Dim derligne As Long Dim fintab As Long, I As Long Dim tabzone() As Variant Dim tabval() As Variant
derligne = Range("A:A").Find("*", , xlFormulas, , , xlPrevious).Row fintab = derligne - 12 'les données commencent à la ligne 13
'% si M en colonne P ReDim tabzone(1 To fintab, 1 To 2) a = 1 ReDim tabval(1 To a) For L = 1 To fintab
tabzone(L, 1) = Sheets(1).Cells(12 + L, 10).Value 'les données commencent à la ligne 13 tabzone(L, 2) = Sheets(2).Cells(12 + L, 16).Value 'les données commencent à la ligne 13 If InStr(1, tabzone(L, 2), "M") <> 0 Then tabval(a) = tabzone(L, 1) a = a + 1 ReDim Preserve tabval(1 To a) End If Next L
With Application.WorksheetFunction moy = .Average(tabval) End With
MsgBox moy
End Sub
Merci de vos remarques,
Bonjour,
Noms de champ: Critere (P13:P1000) et Valeur (J13:J1000) ou noms de
champ dynamique.
x = Evaluate("=average(if(critere=""M"",valeur))")
JB
On 15 fév, 16:55, "plexus" <lucienple...@mageos.com> wrote:
Bonjour,
Je veux faire la moyenne de valeurs situées dans une colonne (J) selon
la présence dans la colonne P de la lettre "M".
Ma sub fonctionne, mais je me demandais s'il n'y a pas plus simple et
efficace.
D'autre part, j'ai remarqué que si je ne déclare pas mon tableau
tabval en variant mais en double, j'obtiens un résultat tout à fait
différent (environ la moitié). Auriez vous une idée du pourquoi de ce
phénomène ? (pour ma culture personelle)
Sub essai_moyenne()
Dim derligne As Long
Dim fintab As Long, I As Long
Dim tabzone() As Variant
Dim tabval() As Variant
derligne = Range("A:A").Find("*", , xlFormulas, , , xlPrevious).Row
fintab = derligne -
12 'les données
commencent à la ligne 13
'% si M en colonne P
ReDim tabzone(1 To fintab, 1 To 2)
a = 1
ReDim tabval(1 To a)
For L = 1 To fintab
tabzone(L, 1) = Sheets(1).Cells(12 + L, 10).Value
'les données commencent à la ligne 13
tabzone(L, 2) = Sheets(2).Cells(12 + L, 16).Value
'les données commencent à la ligne 13
If InStr(1, tabzone(L, 2), "M") <> 0 Then
tabval(a) = tabzone(L, 1)
a = a + 1
ReDim Preserve tabval(1 To a)
End If
Next L
With Application.WorksheetFunction
moy = .Average(tabval)
End With
Noms de champ: Critere (P13:P1000) et Valeur (J13:J1000) ou noms de champ dynamique.
x = Evaluate("=average(if(critere=""M"",valeur))")
JB
On 15 fév, 16:55, "plexus" wrote:
Bonjour,
Je veux faire la moyenne de valeurs situées dans une colonne (J) selon la présence dans la colonne P de la lettre "M". Ma sub fonctionne, mais je me demandais s'il n'y a pas plus simple et efficace.
D'autre part, j'ai remarqué que si je ne déclare pas mon tableau tabval en variant mais en double, j'obtiens un résultat tout à fait différent (environ la moitié). Auriez vous une idée du pourquoi de ce phénomène ? (pour ma culture personelle)
Sub essai_moyenne()
Dim derligne As Long Dim fintab As Long, I As Long Dim tabzone() As Variant Dim tabval() As Variant
derligne = Range("A:A").Find("*", , xlFormulas, , , xlPrevious).Row fintab = derligne - 12 'les données commencent à la ligne 13
'% si M en colonne P ReDim tabzone(1 To fintab, 1 To 2) a = 1 ReDim tabval(1 To a) For L = 1 To fintab
tabzone(L, 1) = Sheets(1).Cells(12 + L, 10).Value 'les données commencent à la ligne 13 tabzone(L, 2) = Sheets(2).Cells(12 + L, 16).Value 'les données commencent à la ligne 13 If InStr(1, tabzone(L, 2), "M") <> 0 Then tabval(a) = tabzone(L, 1) a = a + 1 ReDim Preserve tabval(1 To a) End If Next L
With Application.WorksheetFunction moy = .Average(tabval) End With
MsgBox moy
End Sub
Merci de vos remarques,
Youky
cela doit faire idem je pense
For k = 13 To Feuil1.[A65000].End(3).Row If Feuil2.cells(16, k) = "M" Then total = total + Feuil1.cells(10, k) nb = nb + 1 End If Next MsgBox total / nb
sheets(2) ou Feuil2 à voir Youky
cela doit faire idem je pense
For k = 13 To Feuil1.[A65000].End(3).Row
If Feuil2.cells(16, k) = "M" Then
total = total + Feuil1.cells(10, k)
nb = nb + 1
End If
Next
MsgBox total / nb
For k = 13 To Feuil1.[A65000].End(3).Row If Feuil2.cells(16, k) = "M" Then total = total + Feuil1.cells(10, k) nb = nb + 1 End If Next MsgBox total / nb
sheets(2) ou Feuil2 à voir Youky
anonymousA
Bonjour, si tu recherches le caractère P et pas seulement la valeur exacte.plg est la plage de la colonne où se situent les éventuels P et plg1 la plage de données correspondantes
x = Evaluate("(SUMPRODUCT((plg1)*(IF(ISERROR((SEARCH(""P"",plg)>0)*1), 0,1))))/SUMPRODUCT(IF(ISERROR((SEARCH(""P"",plg)>0)*1),0,1))")
Renverra une magnifique erreur s'il n'existe aucune occurence de caractère P dans la plage mais marche autrement. On peut se prémunir de l'erreur.
A+
On 15 fév, 16:55, "plexus" wrote:
Bonjour,
Je veux faire la moyenne de valeurs situées dans une colonne (J) selon la présence dans la colonne P de la lettre "M". Ma sub fonctionne, mais je me demandais s'il n'y a pas plus simple et efficace.
D'autre part, j'ai remarqué que si je ne déclare pas mon tableau tabval en variant mais en double, j'obtiens un résultat tout à fait différent (environ la moitié). Auriez vous une idée du pourquoi de ce phénomène ? (pour ma culture personelle)
Sub essai_moyenne()
Dim derligne As Long Dim fintab As Long, I As Long Dim tabzone() As Variant Dim tabval() As Variant
derligne = Range("A:A").Find("*", , xlFormulas, , , xlPrevious).Row fintab = derligne - 12 'les données commencent à la ligne 13
'% si M en colonne P ReDim tabzone(1 To fintab, 1 To 2) a = 1 ReDim tabval(1 To a) For L = 1 To fintab
tabzone(L, 1) = Sheets(1).Cells(12 + L, 10).Value 'les données commencent à la ligne 13 tabzone(L, 2) = Sheets(2).Cells(12 + L, 16).Value 'les données commencent à la ligne 13 If InStr(1, tabzone(L, 2), "M") <> 0 Then tabval(a) = tabzone(L, 1) a = a + 1 ReDim Preserve tabval(1 To a) End If Next L
With Application.WorksheetFunction moy = .Average(tabval) End With
MsgBox moy
End Sub
Merci de vos remarques,
Bonjour,
si tu recherches le caractère P et pas seulement la valeur exacte.plg
est la plage de la colonne où se situent les éventuels P et plg1 la
plage de données correspondantes
x = Evaluate("(SUMPRODUCT((plg1)*(IF(ISERROR((SEARCH(""P"",plg)>0)*1),
0,1))))/SUMPRODUCT(IF(ISERROR((SEARCH(""P"",plg)>0)*1),0,1))")
Renverra une magnifique erreur s'il n'existe aucune occurence de
caractère P dans la plage mais marche autrement. On peut se prémunir
de l'erreur.
A+
On 15 fév, 16:55, "plexus" <lucienple...@mageos.com> wrote:
Bonjour,
Je veux faire la moyenne de valeurs situées dans une colonne (J) selon
la présence dans la colonne P de la lettre "M".
Ma sub fonctionne, mais je me demandais s'il n'y a pas plus simple et
efficace.
D'autre part, j'ai remarqué que si je ne déclare pas mon tableau
tabval en variant mais en double, j'obtiens un résultat tout à fait
différent (environ la moitié). Auriez vous une idée du pourquoi de ce
phénomène ? (pour ma culture personelle)
Sub essai_moyenne()
Dim derligne As Long
Dim fintab As Long, I As Long
Dim tabzone() As Variant
Dim tabval() As Variant
derligne = Range("A:A").Find("*", , xlFormulas, , , xlPrevious).Row
fintab = derligne -
12 'les données
commencent à la ligne 13
'% si M en colonne P
ReDim tabzone(1 To fintab, 1 To 2)
a = 1
ReDim tabval(1 To a)
For L = 1 To fintab
tabzone(L, 1) = Sheets(1).Cells(12 + L, 10).Value
'les données commencent à la ligne 13
tabzone(L, 2) = Sheets(2).Cells(12 + L, 16).Value
'les données commencent à la ligne 13
If InStr(1, tabzone(L, 2), "M") <> 0 Then
tabval(a) = tabzone(L, 1)
a = a + 1
ReDim Preserve tabval(1 To a)
End If
Next L
With Application.WorksheetFunction
moy = .Average(tabval)
End With
Bonjour, si tu recherches le caractère P et pas seulement la valeur exacte.plg est la plage de la colonne où se situent les éventuels P et plg1 la plage de données correspondantes
x = Evaluate("(SUMPRODUCT((plg1)*(IF(ISERROR((SEARCH(""P"",plg)>0)*1), 0,1))))/SUMPRODUCT(IF(ISERROR((SEARCH(""P"",plg)>0)*1),0,1))")
Renverra une magnifique erreur s'il n'existe aucune occurence de caractère P dans la plage mais marche autrement. On peut se prémunir de l'erreur.
A+
On 15 fév, 16:55, "plexus" wrote:
Bonjour,
Je veux faire la moyenne de valeurs situées dans une colonne (J) selon la présence dans la colonne P de la lettre "M". Ma sub fonctionne, mais je me demandais s'il n'y a pas plus simple et efficace.
D'autre part, j'ai remarqué que si je ne déclare pas mon tableau tabval en variant mais en double, j'obtiens un résultat tout à fait différent (environ la moitié). Auriez vous une idée du pourquoi de ce phénomène ? (pour ma culture personelle)
Sub essai_moyenne()
Dim derligne As Long Dim fintab As Long, I As Long Dim tabzone() As Variant Dim tabval() As Variant
derligne = Range("A:A").Find("*", , xlFormulas, , , xlPrevious).Row fintab = derligne - 12 'les données commencent à la ligne 13
'% si M en colonne P ReDim tabzone(1 To fintab, 1 To 2) a = 1 ReDim tabval(1 To a) For L = 1 To fintab
tabzone(L, 1) = Sheets(1).Cells(12 + L, 10).Value 'les données commencent à la ligne 13 tabzone(L, 2) = Sheets(2).Cells(12 + L, 16).Value 'les données commencent à la ligne 13 If InStr(1, tabzone(L, 2), "M") <> 0 Then tabval(a) = tabzone(L, 1) a = a + 1 ReDim Preserve tabval(1 To a) End If Next L
With Application.WorksheetFunction moy = .Average(tabval) End With
MsgBox moy
End Sub
Merci de vos remarques,
plexus
Merci de vos réponses,
Youki, il me semble avoir lu dans un post de Laurent Longre, qu'il vallait mieux (dans ce cas de calcul avec fonctions excel) faire des calculs sur des variables tableau contenant les valeurs de cellules que sur les cellules elles-même. Mais j'ai peut-être mal interprété ses dires. de plus, je me suis aperçu (enfin chez moi) que j'avais des soucis d'arrondis en faisant les calculs sous vba, précisément pour le calcul de moyenne : somme/ somme des éléments. donc c'est pour cela que je vais essayer d'adapter la solution de JB.
Par contre quelle différence (efficacité,temps, mémoire?) entre For k = 13 To Feuil1.[A65000].End(3).Row (pourquoi end(3) ?) et derligne = Range("A:A").Find("*", , xlFormulas, , , xlPrevious).Row - 12
tentative d'adaptation : (je prèfère éviter d'utiliser les noms de champs et j'ai des plages de données de longueur variables)
1/ MsgBox Evaluate("=Average(OFFSET(J13,,,COUNTA(J:J)-1))") fonctionne bien
2 / par contre MsgBox Evaluate("=Average(If(((OFFSET(P13,,,COUNTA(P:P)-1))=""M""), (OFFSET(J13,,,COUNTA(J:J)-1))))") me renvoi incompatibilité de type. Me serais-je perdu dans les parenthèses ou est-ce incompatible sous cette forme ?
Merci à vous 2 pour vos remarques
Merci de vos réponses,
Youki, il me semble avoir lu dans un post de Laurent Longre, qu'il
vallait mieux (dans ce cas de calcul avec fonctions excel) faire des
calculs sur des variables tableau contenant les valeurs de cellules
que sur les cellules elles-même.
Mais j'ai peut-être mal interprété ses dires. de plus, je me suis
aperçu (enfin chez moi) que j'avais des soucis d'arrondis en faisant
les calculs sous vba, précisément pour le calcul de moyenne : somme/
somme des éléments. donc c'est pour cela que je vais essayer d'adapter
la solution de JB.
Par contre quelle différence (efficacité,temps, mémoire?) entre
For k = 13 To Feuil1.[A65000].End(3).Row (pourquoi end(3) ?)
et
derligne = Range("A:A").Find("*", , xlFormulas, , , xlPrevious).Row -
12
tentative d'adaptation : (je prèfère éviter d'utiliser les noms de
champs et j'ai des plages de données de longueur variables)
1/ MsgBox Evaluate("=Average(OFFSET(J13,,,COUNTA(J:J)-1))")
fonctionne bien
2 / par contre MsgBox
Evaluate("=Average(If(((OFFSET(P13,,,COUNTA(P:P)-1))=""M""),
(OFFSET(J13,,,COUNTA(J:J)-1))))")
me renvoi incompatibilité de type. Me serais-je perdu dans les
parenthèses ou est-ce incompatible sous cette forme ?
Youki, il me semble avoir lu dans un post de Laurent Longre, qu'il vallait mieux (dans ce cas de calcul avec fonctions excel) faire des calculs sur des variables tableau contenant les valeurs de cellules que sur les cellules elles-même. Mais j'ai peut-être mal interprété ses dires. de plus, je me suis aperçu (enfin chez moi) que j'avais des soucis d'arrondis en faisant les calculs sous vba, précisément pour le calcul de moyenne : somme/ somme des éléments. donc c'est pour cela que je vais essayer d'adapter la solution de JB.
Par contre quelle différence (efficacité,temps, mémoire?) entre For k = 13 To Feuil1.[A65000].End(3).Row (pourquoi end(3) ?) et derligne = Range("A:A").Find("*", , xlFormulas, , , xlPrevious).Row - 12
tentative d'adaptation : (je prèfère éviter d'utiliser les noms de champs et j'ai des plages de données de longueur variables)
1/ MsgBox Evaluate("=Average(OFFSET(J13,,,COUNTA(J:J)-1))") fonctionne bien
2 / par contre MsgBox Evaluate("=Average(If(((OFFSET(P13,,,COUNTA(P:P)-1))=""M""), (OFFSET(J13,,,COUNTA(J:J)-1))))") me renvoi incompatibilité de type. Me serais-je perdu dans les parenthèses ou est-ce incompatible sous cette forme ?
Merci à vous 2 pour vos remarques
plexus
Bonjour, Encore merci à tous de vos efforts. J'ai finalement opté pour un mix des solutions, mais j'ai encore un petit problème :
1/ Sub retmoy() MsgBox Evaluate("=Average(OFFSET('feuil1'!$J$13,,,COUNTA('feuil1'!$J: $J)-1))") End Sub cette sub fonctionne bien et me retourne bien la somme sur une plage variable
2/ Sub retmoycrit() ThisWorkbook.Names.Add Name:="y", RefersTo:="=(OFFSET('feuil1'!$J $13,,,COUNTA('feuil1'!$J:$J)-1))" ThisWorkbook.Names.Add Name:="x", RefersTo:="=(OFFSET('feuil1'!$P $13,,,COUNTA('feuil1'!$P:$P)-1))" MsgBox Evaluate("=Average(If(x=""M"";y))") End Sub Me renvoi une erreur mais fonctionne si je défini des plages "fixes" pour les noms x et y
Mon idée était de récupérer l'adresse de la dernière ligne de ma plage "variable" afin de redefinir les champs en fixe à chaque fois, mais là je sèche un peu
Cette solution serait-elle plus efficace et moins gourmande que ma sub initiale ? et avec des variables tableau serait-ce plus efficace ou vaut-il mieux utiliser les fonctions prédéfinies d'excel ?
Merci encore pour votre aide
Bonjour,
Encore merci à tous de vos efforts.
J'ai finalement opté pour un mix des solutions, mais j'ai encore un
petit problème :
1/ Sub retmoy()
MsgBox Evaluate("=Average(OFFSET('feuil1'!$J$13,,,COUNTA('feuil1'!$J:
$J)-1))")
End Sub
cette sub fonctionne bien et me retourne bien la somme sur une plage
variable
2/
Sub retmoycrit()
ThisWorkbook.Names.Add Name:="y", RefersTo:="=(OFFSET('feuil1'!$J
$13,,,COUNTA('feuil1'!$J:$J)-1))"
ThisWorkbook.Names.Add Name:="x", RefersTo:="=(OFFSET('feuil1'!$P
$13,,,COUNTA('feuil1'!$P:$P)-1))"
MsgBox Evaluate("=Average(If(x=""M"";y))")
End Sub
Me renvoi une erreur mais fonctionne si je défini des plages "fixes"
pour les noms x et y
Mon idée était de récupérer l'adresse de la dernière ligne de ma plage
"variable" afin de redefinir les champs en fixe à chaque fois, mais là
je sèche un peu
Cette solution serait-elle plus efficace et moins gourmande que ma sub
initiale ? et avec des variables tableau serait-ce plus efficace ou
vaut-il mieux utiliser les fonctions prédéfinies d'excel ?
Bonjour, Encore merci à tous de vos efforts. J'ai finalement opté pour un mix des solutions, mais j'ai encore un petit problème :
1/ Sub retmoy() MsgBox Evaluate("=Average(OFFSET('feuil1'!$J$13,,,COUNTA('feuil1'!$J: $J)-1))") End Sub cette sub fonctionne bien et me retourne bien la somme sur une plage variable
2/ Sub retmoycrit() ThisWorkbook.Names.Add Name:="y", RefersTo:="=(OFFSET('feuil1'!$J $13,,,COUNTA('feuil1'!$J:$J)-1))" ThisWorkbook.Names.Add Name:="x", RefersTo:="=(OFFSET('feuil1'!$P $13,,,COUNTA('feuil1'!$P:$P)-1))" MsgBox Evaluate("=Average(If(x=""M"";y))") End Sub Me renvoi une erreur mais fonctionne si je défini des plages "fixes" pour les noms x et y
Mon idée était de récupérer l'adresse de la dernière ligne de ma plage "variable" afin de redefinir les champs en fixe à chaque fois, mais là je sèche un peu
Cette solution serait-elle plus efficace et moins gourmande que ma sub initiale ? et avec des variables tableau serait-ce plus efficace ou vaut-il mieux utiliser les fonctions prédéfinies d'excel ?
Merci encore pour votre aide
JB
Bonjour,
-La solution initialse était la moins optimale (lecture de n cellules du tableur) -La dernière solutionest sans doute la + optimale
Pour mesurer le temps:
t=timer() ... msgbox timer()-t
-Le champ =[P2:P1000] pourrait être transféré dans un tableau a() p ar
a=[P2:P1000]
Les calculs sur le tableau seraient + rapides que sur des cellules (rapport >10)
Bonjour, Encore merci à tous de vos efforts. J'ai finalement opté pour un mix des solutions, mais j'ai encore un petit problème :
1/ Sub retmoy() MsgBox Evaluate("=Average(OFFSET('feuil1'!$J$13,,,COUNTA('feuil1'!$J: $J)-1))") End Sub cette sub fonctionne bien et me retourne bien la somme sur une plage variable
2/ Sub retmoycrit() ThisWorkbook.Names.Add Name:="y", RefersTo:="=(OFFSET('feuil1'!$J $13,,,COUNTA('feuil1'!$J:$J)-1))" ThisWorkbook.Names.Add Name:="x", RefersTo:="=(OFFSET('feuil1'!$P $13,,,COUNTA('feuil1'!$P:$P)-1))" MsgBox Evaluate("=Average(If(x=""M"";y))") End Sub Me renvoi une erreur mais fonctionne si je défini des plages "fixes" pour les noms x et y
Mon idée était de récupérer l'adresse de la dernière ligne de m a plage "variable" afin de redefinir les champs en fixe à chaque fois, mais l à je sèche un peu
Cette solution serait-elle plus efficace et moins gourmande que ma sub initiale ? et avec des variables tableau serait-ce plus efficace ou vaut-il mieux utiliser les fonctions prédéfinies d'excel ?
Merci encore pour votre aide
Bonjour,
-La solution initialse était la moins optimale (lecture de n cellules
du tableur)
-La dernière solutionest sans doute la + optimale
Pour mesurer le temps:
t=timer()
...
msgbox timer()-t
-Le champ =[P2:P1000] pourrait être transféré dans un tableau a() p ar
a=[P2:P1000]
Les calculs sur le tableau seraient + rapides que sur des cellules
(rapport >10)
On 16 fév, 09:59, "plexus" <lucienple...@mageos.com> wrote:
Bonjour,
Encore merci à tous de vos efforts.
J'ai finalement opté pour un mix des solutions, mais j'ai encore un
petit problème :
1/ Sub retmoy()
MsgBox Evaluate("=Average(OFFSET('feuil1'!$J$13,,,COUNTA('feuil1'!$J:
$J)-1))")
End Sub
cette sub fonctionne bien et me retourne bien la somme sur une plage
variable
2/
Sub retmoycrit()
ThisWorkbook.Names.Add Name:="y", RefersTo:="=(OFFSET('feuil1'!$J
$13,,,COUNTA('feuil1'!$J:$J)-1))"
ThisWorkbook.Names.Add Name:="x", RefersTo:="=(OFFSET('feuil1'!$P
$13,,,COUNTA('feuil1'!$P:$P)-1))"
MsgBox Evaluate("=Average(If(x=""M"";y))")
End Sub
Me renvoi une erreur mais fonctionne si je défini des plages "fixes"
pour les noms x et y
Mon idée était de récupérer l'adresse de la dernière ligne de m a plage
"variable" afin de redefinir les champs en fixe à chaque fois, mais l à
je sèche un peu
Cette solution serait-elle plus efficace et moins gourmande que ma sub
initiale ? et avec des variables tableau serait-ce plus efficace ou
vaut-il mieux utiliser les fonctions prédéfinies d'excel ?
Bonjour, Encore merci à tous de vos efforts. J'ai finalement opté pour un mix des solutions, mais j'ai encore un petit problème :
1/ Sub retmoy() MsgBox Evaluate("=Average(OFFSET('feuil1'!$J$13,,,COUNTA('feuil1'!$J: $J)-1))") End Sub cette sub fonctionne bien et me retourne bien la somme sur une plage variable
2/ Sub retmoycrit() ThisWorkbook.Names.Add Name:="y", RefersTo:="=(OFFSET('feuil1'!$J $13,,,COUNTA('feuil1'!$J:$J)-1))" ThisWorkbook.Names.Add Name:="x", RefersTo:="=(OFFSET('feuil1'!$P $13,,,COUNTA('feuil1'!$P:$P)-1))" MsgBox Evaluate("=Average(If(x=""M"";y))") End Sub Me renvoi une erreur mais fonctionne si je défini des plages "fixes" pour les noms x et y
Mon idée était de récupérer l'adresse de la dernière ligne de m a plage "variable" afin de redefinir les champs en fixe à chaque fois, mais l à je sèche un peu
Cette solution serait-elle plus efficace et moins gourmande que ma sub initiale ? et avec des variables tableau serait-ce plus efficace ou vaut-il mieux utiliser les fonctions prédéfinies d'excel ?
Merci encore pour votre aide
plexus
Bonjour Jb, et merci pour ton fichier, j'essaye de bien tout comprendre mais mes méninges font de la résistance concernant les tableaux... J'ai lu sur le site de Laurent longre qu'on pouvait utiliser dans les formules d'excel une variable tableau en le passant par une fonction je tente donc les lignes suivantes :
Public tabl() As Variant
Private Function deftab() deftab = tabl End Function
Sub testmoy() tabl = [M13:M24] moy = Evaluate("=Average(deftab())") MsgBox moy End Sub
sauf que j'ai l'erreur incompatibilité de type au moment de l'affectation du tableau : tabl = [M13:M24]
est-ce une erreur de synthaxe ? avec cette écritue y a t'il moyen de définir un tableau de longueur variable du style table = [M13:M & derligne ] ou derligne serait du type derligne = 13 To Feuil1.[M65000].End(3).Row ?
(je sais toujours pas pourquoi End(3) d'ailleurs)
Merci pour la patience, mais je crois que je touche au but.....
Bonjour Jb, et merci pour ton fichier, j'essaye de bien tout
comprendre mais mes méninges font de la résistance concernant les
tableaux...
J'ai lu sur le site de Laurent longre qu'on pouvait utiliser dans les
formules d'excel une variable tableau en le passant par une fonction
je tente donc les lignes suivantes :
Public tabl() As Variant
Private Function deftab()
deftab = tabl
End Function
Sub testmoy()
tabl = [M13:M24]
moy = Evaluate("=Average(deftab())")
MsgBox moy
End Sub
sauf que j'ai l'erreur incompatibilité de type au moment de
l'affectation du tableau :
tabl = [M13:M24]
est-ce une erreur de synthaxe ?
avec cette écritue y a t'il moyen de définir un tableau de longueur
variable du style
table = [M13:M & derligne ] ou derligne serait du type derligne = 13
To Feuil1.[M65000].End(3).Row ?
(je sais toujours pas pourquoi End(3) d'ailleurs)
Merci pour la patience, mais je crois que je touche au but.....
Bonjour Jb, et merci pour ton fichier, j'essaye de bien tout comprendre mais mes méninges font de la résistance concernant les tableaux... J'ai lu sur le site de Laurent longre qu'on pouvait utiliser dans les formules d'excel une variable tableau en le passant par une fonction je tente donc les lignes suivantes :
Public tabl() As Variant
Private Function deftab() deftab = tabl End Function
Sub testmoy() tabl = [M13:M24] moy = Evaluate("=Average(deftab())") MsgBox moy End Sub
sauf que j'ai l'erreur incompatibilité de type au moment de l'affectation du tableau : tabl = [M13:M24]
est-ce une erreur de synthaxe ? avec cette écritue y a t'il moyen de définir un tableau de longueur variable du style table = [M13:M & derligne ] ou derligne serait du type derligne = 13 To Feuil1.[M65000].End(3).Row ?
(je sais toujours pas pourquoi End(3) d'ailleurs)
Merci pour la patience, mais je crois que je touche au but.....
JB
Sub essai() Dim b As Variant Dim a(3) a(1) = 12 a(2) = 15 a(3) = 12 x = Application.Average(a) MsgBox x '--- b = [A1:A1000] y = Application.Average(b) MsgBox y MsgBox Moyenne(b) End Sub
Function Moyenne(t) Moyenne = Application.Average(t) End Function
JB
On 16 fév, 11:47, "plexus" wrote:
Bonjour Jb, et merci pour ton fichier, j'essaye de bien tout comprendre mais mes méninges font de la résistance concernant les tableaux... J'ai lu sur le site de Laurent longre qu'on pouvait utiliser dans les formules d'excel une variable tableau en le passant par une fonction je tente donc les lignes suivantes :
Public tabl() As Variant
Private Function deftab() deftab = tabl End Function
Sub testmoy() tabl = [M13:M24] moy = Evaluate("=Average(deftab())") MsgBox moy End Sub
sauf que j'ai l'erreur incompatibilité de type au moment de l'affectation du tableau : tabl = [M13:M24]
est-ce une erreur de synthaxe ? avec cette écritue y a t'il moyen de définir un tableau de longueur variable du style table = [M13:M & derligne ] ou derligne serait du type derligne = 13 To Feuil1.[M65000].End(3).Row ?
(je sais toujours pas pourquoi End(3) d'ailleurs)
Merci pour la patience, mais je crois que je touche au but.....
Sub essai()
Dim b As Variant
Dim a(3)
a(1) = 12
a(2) = 15
a(3) = 12
x = Application.Average(a)
MsgBox x
'---
b = [A1:A1000]
y = Application.Average(b)
MsgBox y
MsgBox Moyenne(b)
End Sub
Function Moyenne(t)
Moyenne = Application.Average(t)
End Function
JB
On 16 fév, 11:47, "plexus" <lucienple...@mageos.com> wrote:
Bonjour Jb, et merci pour ton fichier, j'essaye de bien tout
comprendre mais mes méninges font de la résistance concernant les
tableaux...
J'ai lu sur le site de Laurent longre qu'on pouvait utiliser dans les
formules d'excel une variable tableau en le passant par une fonction
je tente donc les lignes suivantes :
Public tabl() As Variant
Private Function deftab()
deftab = tabl
End Function
Sub testmoy()
tabl = [M13:M24]
moy = Evaluate("=Average(deftab())")
MsgBox moy
End Sub
sauf que j'ai l'erreur incompatibilité de type au moment de
l'affectation du tableau :
tabl = [M13:M24]
est-ce une erreur de synthaxe ?
avec cette écritue y a t'il moyen de définir un tableau de longueur
variable du style
table = [M13:M & derligne ] ou derligne serait du type derligne = 13
To Feuil1.[M65000].End(3).Row ?
(je sais toujours pas pourquoi End(3) d'ailleurs)
Merci pour la patience, mais je crois que je touche au but.....
Sub essai() Dim b As Variant Dim a(3) a(1) = 12 a(2) = 15 a(3) = 12 x = Application.Average(a) MsgBox x '--- b = [A1:A1000] y = Application.Average(b) MsgBox y MsgBox Moyenne(b) End Sub
Function Moyenne(t) Moyenne = Application.Average(t) End Function
JB
On 16 fév, 11:47, "plexus" wrote:
Bonjour Jb, et merci pour ton fichier, j'essaye de bien tout comprendre mais mes méninges font de la résistance concernant les tableaux... J'ai lu sur le site de Laurent longre qu'on pouvait utiliser dans les formules d'excel une variable tableau en le passant par une fonction je tente donc les lignes suivantes :
Public tabl() As Variant
Private Function deftab() deftab = tabl End Function
Sub testmoy() tabl = [M13:M24] moy = Evaluate("=Average(deftab())") MsgBox moy End Sub
sauf que j'ai l'erreur incompatibilité de type au moment de l'affectation du tableau : tabl = [M13:M24]
est-ce une erreur de synthaxe ? avec cette écritue y a t'il moyen de définir un tableau de longueur variable du style table = [M13:M & derligne ] ou derligne serait du type derligne = 13 To Feuil1.[M65000].End(3).Row ?
(je sais toujours pas pourquoi End(3) d'ailleurs)
Merci pour la patience, mais je crois que je touche au but.....
plexus
Merci JB
Adapté cela me donne : et cela fonctionne très bien
Public tabl As Variant
Function deftab() deftab = tabl End Function
Sub testmoy() derligne = [O65000].End(3).Row
tabl = Range("O13:O" & derligne)
'tabl = [O13:O24] 'ce format fonctionne également mais je ne sais pas y inclure ma variable derligne 'cela revient au même ?
moy = Evaluate("=Average(deftab())") MsgBox moy End Sub
Mais si je comprends bien, là il ne sagit plus d'une variable tableau mais d'une simple variable variant qui se transforme en tableau ceci dit cela fonctionne également pareil en décalrant tabl en tableau sauf avec la notation [O13:O24] ou là j'ai incompatibilité de type
Public tabl() as variant
Function deftab() deftab = tabl End Function
Sub testmoy() derligne = [O65000].End(3).Row
tabl = Range("O13:O" & derligne)
moy = Evaluate("=Average(deftab())") MsgBox moy End Sub
Merci beaucoup pour cette aide précieuse
Merci JB
Adapté cela me donne : et cela fonctionne très bien
Public tabl As Variant
Function deftab()
deftab = tabl
End Function
Sub testmoy()
derligne = [O65000].End(3).Row
tabl = Range("O13:O" & derligne)
'tabl = [O13:O24]
'ce format fonctionne également mais je ne sais pas y inclure ma
variable derligne
'cela revient au même ?
moy = Evaluate("=Average(deftab())")
MsgBox moy
End Sub
Mais si je comprends bien, là il ne sagit plus d'une variable tableau
mais d'une simple variable variant qui se transforme en tableau
ceci dit cela fonctionne également pareil en décalrant tabl en tableau
sauf avec la notation [O13:O24] ou là j'ai incompatibilité de type
Public tabl() as variant
Function deftab()
deftab = tabl
End Function
Sub testmoy()
derligne = [O65000].End(3).Row
tabl = Range("O13:O" & derligne)
moy = Evaluate("=Average(deftab())")
MsgBox moy
End Sub
Adapté cela me donne : et cela fonctionne très bien
Public tabl As Variant
Function deftab() deftab = tabl End Function
Sub testmoy() derligne = [O65000].End(3).Row
tabl = Range("O13:O" & derligne)
'tabl = [O13:O24] 'ce format fonctionne également mais je ne sais pas y inclure ma variable derligne 'cela revient au même ?
moy = Evaluate("=Average(deftab())") MsgBox moy End Sub
Mais si je comprends bien, là il ne sagit plus d'une variable tableau mais d'une simple variable variant qui se transforme en tableau ceci dit cela fonctionne également pareil en décalrant tabl en tableau sauf avec la notation [O13:O24] ou là j'ai incompatibilité de type
Public tabl() as variant
Function deftab() deftab = tabl End Function
Sub testmoy() derligne = [O65000].End(3).Row
tabl = Range("O13:O" & derligne)
moy = Evaluate("=Average(deftab())") MsgBox moy End Sub
Merci beaucoup pour cette aide précieuse
JB
Sub testmoy() tabl = Range("O2:A" & [O65000].End(xlUp).Row) moy = Application.Average(tabl) MsgBox moy End Sub
JB
On 16 fév, 14:41, "plexus" wrote:
Merci JB
Adapté cela me donne : et cela fonctionne très bien
Public tabl As Variant
Function deftab() deftab = tabl End Function
Sub testmoy() derligne = [O65000].End(3).Row
tabl = Range("O13:O" & derligne)
'tabl = [O13:O24] 'ce format fonctionne également mais je ne sais pas y inclure ma variable derligne 'cela revient au même ?
moy = Evaluate("=Average(deftab())") MsgBox moy End Sub
Mais si je comprends bien, là il ne sagit plus d'une variable tableau mais d'une simple variable variant qui se transforme en tableau ceci dit cela fonctionne également pareil en décalrant tabl en tableau sauf avec la notation [O13:O24] ou là j'ai incompatibilité de type
Public tabl() as variant
Function deftab() deftab = tabl End Function
Sub testmoy() derligne = [O65000].End(3).Row
tabl = Range("O13:O" & derligne)
moy = Evaluate("=Average(deftab())") MsgBox moy End Sub
Merci beaucoup pour cette aide précieuse
Sub testmoy()
tabl = Range("O2:A" & [O65000].End(xlUp).Row)
moy = Application.Average(tabl)
MsgBox moy
End Sub
JB
On 16 fév, 14:41, "plexus" <lucienple...@mageos.com> wrote:
Merci JB
Adapté cela me donne : et cela fonctionne très bien
Public tabl As Variant
Function deftab()
deftab = tabl
End Function
Sub testmoy()
derligne = [O65000].End(3).Row
tabl = Range("O13:O" & derligne)
'tabl = [O13:O24]
'ce format fonctionne également mais je ne sais pas y inclure ma
variable derligne
'cela revient au même ?
moy = Evaluate("=Average(deftab())")
MsgBox moy
End Sub
Mais si je comprends bien, là il ne sagit plus d'une variable tableau
mais d'une simple variable variant qui se transforme en tableau
ceci dit cela fonctionne également pareil en décalrant tabl en tableau
sauf avec la notation [O13:O24] ou là j'ai incompatibilité de type
Public tabl() as variant
Function deftab()
deftab = tabl
End Function
Sub testmoy()
derligne = [O65000].End(3).Row
tabl = Range("O13:O" & derligne)
moy = Evaluate("=Average(deftab())")
MsgBox moy
End Sub
Sub testmoy() tabl = Range("O2:A" & [O65000].End(xlUp).Row) moy = Application.Average(tabl) MsgBox moy End Sub
JB
On 16 fév, 14:41, "plexus" wrote:
Merci JB
Adapté cela me donne : et cela fonctionne très bien
Public tabl As Variant
Function deftab() deftab = tabl End Function
Sub testmoy() derligne = [O65000].End(3).Row
tabl = Range("O13:O" & derligne)
'tabl = [O13:O24] 'ce format fonctionne également mais je ne sais pas y inclure ma variable derligne 'cela revient au même ?
moy = Evaluate("=Average(deftab())") MsgBox moy End Sub
Mais si je comprends bien, là il ne sagit plus d'une variable tableau mais d'une simple variable variant qui se transforme en tableau ceci dit cela fonctionne également pareil en décalrant tabl en tableau sauf avec la notation [O13:O24] ou là j'ai incompatibilité de type
Public tabl() as variant
Function deftab() deftab = tabl End Function
Sub testmoy() derligne = [O65000].End(3).Row
tabl = Range("O13:O" & derligne)
moy = Evaluate("=Average(deftab())") MsgBox moy End Sub
