Selection plage de données dans une ligne à intervalle régulier

[alexbej]

Bonjour,

Je cherche désespérément à appliquer différentes formules (MOYENNE; ECARTYPE...) à une plage de données qui est en ligne et qui se répète à intervalle régulier.
Pour être plus clair, je souhaiterai faire la moyenne (par exemple) des valeurs de la ligne 1, à partir de la cellule D1 jusqu'à la fin de la ligne, avec un intervalle de 7 cellules (-->ce qui donnerait quelque chose comme =MOYENNE(D1;K1;R1;Y1;AF1...)).
Est-il possible, sans macro, de rentrer la ligne d'intérêt, la première cellule d'intérêt et l'intervalle à respecter? (formule du type --> MOYENNE((D1:ZZ1);7) ou D1-ZZ1 serait l'ensemble de l'espace d'intérêt de la ligne 1, et 7 l'intervalle de sélection des cellules)

D'avance, merci beaucoup de votre aide.
alexandre

 

[pijaku]

Re : Selection plage de données dans une ligne à intervalle régulier

Bonjour,

Cette formule semble fonctionner :
=MOYENNE(D1;DECALER(D1;;7;;246))

On utilise la fonction DECALER pour créer une plage dynamique.
246 est obtenu en faisant : 256 (total des colonnes) - 7 - 4 (colonne de D1) + 1 (colonne A)
Je n'ai qu'Excel 2003, mais vous pourrez aisément adapter la largeur de la plage maximale allouée à la formule.

 

[alexbej]

Re : Selection plage de données dans une ligne à intervalle régulier

Bonjour,

Merci de votre réponse mais ça ne fonctionne pas (en pj, un exemple très basique avec la formule indiquée: le résultat devrait être 4). Auriez-vous une autre idée?

A nouveau, merci.

 

[VDAVID]

Re : Selection plage de données dans une ligne à intervalle régulier

Bonjour Alexbej,

Un test en P.J,

"Est-il possible, sans macro"

En utilisant une fonction personnalisée depuis un module.
Le calcul se fait donc depuis une formule mais créée par macro, je ne sais pas si ça va

Elle est sous la forme:

=sommecar(Plage;Ecart colonnes)

Bonne journée !

EDIT : Autant pour moi, j'ai mal lu le sujet, ma proposition ne convient pour obtenir une plage de données variable dans une formule mais uniquement pour la fonction moyenne.

 

[pijaku]

Re : Selection plage de données dans une ligne à intervalle régulier

Oui, j'ai bien une idée. Ce sera une formule matricielle, à valider par Ctrl+Maj+Entrée en fin de saisie, au lieu de faire Entrée.
Si la manip est bien faite, la formule apparait entourée de {}.

=MOYENNE(SI((MOD(COLONNE(1:1)+3;7)=0)*(1:1<>"");1:1))

 

[alexbej]

Re : Selection plage de données dans une ligne à intervalle régulier

Bonjour VDAVID,

Merci de vos réponses, à vous et pijaku. Concernant la formule, elle semble à première vue fonctionner. Toutefois, 2 problèmes: lorsqu'on l'étend sur la ligne, pour l'appliquer aux cellules suivantes, on obtient bien au début une moyenne de 5, de 6, puis de 7. Mais une fois 7 dépassé, on s'attendrait à retomber sur 1 (la moyenne des 1, à intervalle de 7, en partant de la colonne H). Or ce n'est pas le cas, le résultat est de 0.857... L'autre problème est l'application de cette formule dans un autre classeur, qui ne fonctionne pas (et je n'ai pas trouvé dans le fichier la macro qui vous a permit de la générer).

Pijaku, merci pour votre solution également. A première, ça fonctionne également. Toutefois, je ne suis pas coutumier de l'écriture matricielle et je manque donc de flexibilité avec. Pourriez-vous s'il vous plait me détailler à quoi correspondent chaque parties pour que je puisse l'essayer sur d'autres colonnes. J'ai par exemple essayé de changer le "+3" qui me semblait désigner la cellule de référence, mais sans succès (si je mets 4, je m'attendais à trouver une moyenne de 5, mais au contraire, je trouve une moyenne de 3!).

Merci de votre aide,
Cordialement.

alexandre

 

[pijaku]

Re : Selection plage de données dans une ligne à intervalle régulier

=MOYENNE(SI((MOD(COLONNE(1:1)+3;7)=0)*(1:1<>"");1: 1))
Le +3 vient en soustraction du 7. Il désigne effectivement la cellule de départ, D1, mais à partir de G1... OK? Non? [moi non plus...]

MOD(Nombre;diviseur) pas de souci? Retourne le reste. Ici on veux des multiples de 7 donc MOD(Nombre;7)=0

1:1 : désigne la totalité de la ligne 1

Le test SI testes si : le numéro de la colonne est un multiple de 7 (avec écart initial de 3) ET si la cellule est non vide (1:1<>"")

Si ce test est correct alors la cellule est ajoutée à la matrice de calcul.

Pour comprendre les étapes de ce calcul, vous pouvez :
- saisir la formule dans une cellule : =MOYENNE(SI((MOD(COLONNE(1:1)+3;7)=0)*(1:1<>"");1:1))*a
- valider par Ctrl+Maj+Entrée
- le *a ajouté à la fin va faire un message d'erreur
- Cliquez sur le point d'interrogation en commentaire de cette cellule / afficher les étapes du calcul
- cliquez sur Evaluer puis sur Redémarrer, evaluer evaluer et regardez comment se comporte la formule...

 

[VDAVID]

Re : Selection plage de données dans une ligne à intervalle régulier

Re Alexbej, Pijaku,

Le code rectifié si jamais il t'intéresses quand même:

Il faut le placer dans nimporte quel Module VBA de ton classeur:
Outils>Macro>Visual Basic Editor ou Alt + F11 puis clique droit Insertion>Module

Private Function Moyennecar(ByVal Arg2 As Range, Arg3 As Integer)
Dim Flag1 As Boolean
Flag1 = False
z = ""

Absc = Right(Arg2.Address, InStr(1, Right(Arg2.Address, 5), "$"))
Absc2 = Mid(Arg2.Address, InStr(2, Arg2.Address, "$") + 1, InStr(InStr(2, Arg2.Address, "$") + 1, Arg2.Address, "$") - (InStr(2, Arg2.Address, "$") + 2))
Ordo1 = Range(Mid(Arg2.Address, 2, InStr(2, Arg2.Address, "$") - 2) & "1").Column
Ordo2 = Range(Mid(Arg2.Address, InStr(5, Arg2.Address, "$") + 1, InStr(InStr(5, Arg2.Address, "$") + 1, Arg2.Address, "$") - 7) & "1").Column
Ordof = (Ordo2 - Ordo1) Mod Arg3
Ordof = Ordo2 - Ordof
nb = (((Ordof - Ordo1) / Arg3) + 1) * ((Absc - Absc2) + 1)

Dim k&
For k = Absc2 To Absc Step 1
Dim i&
For i = Ordo1 To Ordof Step Arg3

    If Flag1 = False Then
        z = Cells(k, i)
        Flag1 = True
        GoTo Borne
    End If

z = z + Cells(k, i)
Borne:
Next i
Next k
Moyennecar = z / nb
End Function

Il prend en compte plusieurs lignes selon la plage que tu choisis dans le premier argument.

Bonne journée

 

[alexbej]

Re : Selection plage de données dans une ligne à intervalle régulier

Je pense que je devrais m'en sortir avec tout ça! Merci beaucoup de votre aide.
Cordialement.