moyenne variable dans le tableau

[cyrilV]

Bonsoir à tout le forum,

Dans un ensemble de cellules formant un tableau, je souhaite en cliquant "copier" sur une cellule de référence en dehors du tableau, et en la cliquant sur l'une des cellules du tableau, que cela calcule automatiquement la moyenne des cellules allant des colonnes E à K en l'occurence.
Cela semblait assez facile en utilisant la formule =moyenne($E:$K2), cependant il fait face à son propre résultat et donc une fonction circulaire car je souhaite qu'il ne prenne pas en compte la valeur dans la cellule concernée. Sachant que la cellule concernée n'est jamais là même.

Je ne sais pas si je suis bien clair, mais voici le tableau concerné avec le résultat attendu.

Merci d'avance

Cordialement

Cyril

 

[cyrilV]

Re : moyenne variable dans le tableau

Personne pour m'aider SVP? merci d'avance

 

[ROGER2327]

Re : moyenne variable dans le tableau

Bonsoir cyrilV

Pressé ? En attendant les vrais formulistes de ce forum, voyez si cela peut faire l'affaire :

=(SI(COLONNE()>5;SOMME(INDIRECT("E"&LIGNE()&":"&CHOISIR(COLONNE()-5;"E";"F";"G";"H";"I";"J";"K")&LIGNE()));0)+SI(COLONNE()<11;SOMME(INDIRECT(CHOISIR(COLONNE()-3;"E";"F";"G";"H";"I";"J";"K")&LIGNE()&":"&"K"&LIGNE()));0))/(SI(COLONNE()>5;SOMME(1*(NB(INDIRECT("E"&LIGNE()&":"&CHOISIR(COLONNE()-5;"E";"F";"G";"H";"I";"J";"K")&LIGNE()))));0)+SI(COLONNE()<11;SOMME(1*(NB(INDIRECT(CHOISIR(COLONNE()-3;"E";"F";"G";"H";"I";"J";"K")&LIGNE()&":"&"K"&LIGNE()))));0))

Ouf !​

ROGER2327

__________________

Oubliez ces formules et voyez

Re...

 

[cyrilV]

Re : moyenne variable dans le tableau

Bonjour Roger,

Merci pour cette formule qui quand j'essaie de la déchiffrer, je ne comprends rien, mais qui fonctionne!!
En effet, quand je l'insère dans mon programme qui en fait a une colonne supplémentaire, j'ai essayé de transposer la formule en décalant les colonnes (F, G, H, I, J, K, L) au lieu de E, F, G, H, I, J, K.
Donc en décalant ça ne marchait pas, et en laissant comme initial, ça fonctionne...je ne comprends pas pk, mais tant pis, le tout c'est que ça marche!!

Puis je abuser encore un peu, pour compléter cette formule je pense avec un truc qui dit "si dans les différents colonnes il n'y a pas de valeur, ou il y a des "x", et bien ne rien écrire comme valeur", car là ça marque un défaut.
Si tu n'y arrives pas( ça j'en doute) ou si tu n'as pas le temps, ça n'est pas grave, cela marche déjà très bien comme ça.

A bientôt et bonne journée

Cyril

 

[ROGER2327]

Re : moyenne variable dans le tableau

Bonjour cyrilV

La formule pour prendre en compte la colonne L :

=(SI(COLONNE()>5;SOMME(INDIRECT("E"&LIGNE()&":"&CHOISIR(COLONNE()-5;"E";"F";"G";"H";"I";"J";"K";"L")&LIGNE()));0)+SI(COLONNE()<12;SOMME(INDIRECT(CHOISIR(COLONNE()-3;"E";"F";"G";"H";"I";"J";"K";"L")&LIGNE()&":"&"L"&LIGNE()));0))/(SI(COLONNE()>5;SOMME(1*(NB(INDIRECT("E"&LIGNE()&":"&CHOISIR(COLONNE()-5;"E";"F";"G";"H";"I";"J";"K";"L")&LIGNE()))));0)+SI(COLONNE()<12;SOMME(1*(NB(INDIRECT(CHOISIR(COLONNE()-3;"E";"F";"G";"H";"I";"J";"K";"L")&LIGNE()&":"&"L"&LIGNE()))));0))

Pour comprendre le fonctionnement de la formule, vous pouvez consulter l'aide d'Excel concernant la fonction INDIRECT().
Pour le reste, je ne vois pas de solution : Si vous collez la formule dans une cellule, vous remplacez la valeur que contenait cette cellule par la formule. Or, une formule ne peut prendre en compte la valeur qu'avait la cellule avant qu'on l'y colle...
Mais peut être n'ai-je pas compris la question.
​

ROGER2327

__________________

Oubliez ces formules et voyez

Re...

 

[cyrilV]

Re : moyenne variable dans le tableau

Rebonjour Roger,

Merci pour les explications.
Pour ce que je vous demandais, je ne devais pas être clair en effet. Il ne s'agit pas de savoir ce qu'il y avait dans la cellule avant qu'elle soit copiée, mais de ce qu'il y a dans les cellules voisines de la même ligne.
En fait, dans cet exemple, si aucune course n'a été concurue par la personne concernée, nous mettons une croix ou un blanc. Par conséquent la moyenne de cette ligne ne peut être prise en compte. Il faudrait que dans ce cas, la cellule dont la valeur doit être affichée soit vide.

Je ne sais pas si j'ai été plus clair, mais ne vous tracassez pas la tête si c'est complexe, je copierai une autre cellule sans formule s'il s'agit de la première course faite.

Merci pour le complément de formule

Cordialement

Cyril V.

 

[ROGER2327]

Re : moyenne variable dans le tableau

Re...

Essayez cela :

=SI(SI(COLONNE()>5;SOMME(1*(NB(INDIRECT("E"&LIGNE()&":"&CHOISIR(COLONNE()-5;"E";"F";"G";"H";"I";"J";"K";"L")&LIGNE()))));0)+SI(COLONNE()<12;SOMME(1*(NB(INDIRECT(CHOISIR(COLONNE()-3;"E";"F";"G";"H";"I";"J";"K";"L")&LIGNE()&":"&"L"&LIGNE()))));0);(SI(COLONNE()>5;SOMME(INDIRECT("E"&LIGNE()&":"&CHOISIR(COLONNE()-5;"E";"F";"G";"H";"I";"J";"K";"L")&LIGNE()));0)+SI(COLONNE()<12;SOMME(INDIRECT(CHOISIR(COLONNE()-3;"E";"F";"G";"H";"I";"J";"K";"L")&LIGNE()&":"&"L"&LIGNE()));0))/(SI(COLONNE()>5;SOMME(1*(NB(INDIRECT("E"&LIGNE()&":"&CHOISIR(COLONNE()-5;"E";"F";"G";"H";"I";"J";"K";"L")&LIGNE()))));0)+SI(COLONNE()<12;SOMME(1*(NB(INDIRECT(CHOISIR(COLONNE()-3;"E";"F";"G";"H";"I";"J";"K";"L")&LIGNE()&":"&"L"&LIGNE()))));0));"")

ROGER2327

__________________

Oubliez cette formule et voyez

Re...

 

[cyrilV]

Re : moyenne variable dans le tableau

Rebonsoir Roger,

Merci pour le complément de formule. Ca colle avec mes attentes.
En revanche en essayant la formule, je ne peux l'appliquer qu'une seule fois dans la ligne, au delà la formule fait référence à son propre résultat.
Un peu dur j'ai l'impression ce que je demande...

En tout cas merci du travail réalisé.

Cordialement

Cyril

 

[ROGER2327]

Re : moyenne variable dans le tableau

Re...

(...) En revanche en essayant la formule, je ne peux l'appliquer qu'une seule fois dans la ligne, au delà la formule fait référence à son propre résultat.
Un peu dur j'ai l'impression ce que je demande... (...)

Cette fois-ci, il n'y a pas de solution. En effet, si vous prenez une série de quatre valeurs (pour faire simple, car le raisonnement peut s'étendre à un nombre quelconque de valeurs) dont une manque, vous pouvez la compléter par une quatrième valeur qui est la moyenne des trois valeurs connues. Le résultat sera une série de quatre valeurs dont la moyenne sera égale à la moyenne des trois valeurs connues.
Exemple : 0, 5, 4, ?.
La moyenne de 0, 5 et 4 est 3.
En complétant ainsi la série : 0, 5, 4, 3, vous obtenez une série de quatre valeurs dont la moyenne est... ...3.
C'est ce que fait la formule que vous utilisez.
Supposons maintenant que nous ayons la série 0, ?, 4, ?. Nous pourrions dire que, la moyenne de 0 et 4 étant 2, il suffit de compléter la série ainsi : 0, 2, 4, 2, dont la moyenne est effectivement 2. Mais vous ne pouvez obtenir ce résultat avec votre formule : elle est conçue pour compléter la série par une valeur manquante, et pas plus. Remarquez en effet que si 0, 2, 4, 2 est correct, 0, 1, 4, 3 ou 0, -20, 4, 24 le sont tout autant. Il est donc tout à fait normal que l'application de votre formule conduise à un problème de référence circulaire, car les deux valeurs manquantes dépendent l'une de l'autre.
Le problème que vous soulevez maintenant est donc un nouveau problème. Il peut avoir des solutions, à condition que vous précisiez la procédure de décision à appliquer à la recherche de plusieurs valeurs manquantes.
J'espère être compréhensible et, si c'est le cas, vous permettre de préciser ce que vous voulez faire.
​

Cordialement,
ROGER2327

 

[cyrilV]

Re : moyenne variable dans le tableau

Je vois ce que vous voulez dire. Pas simple.
Et si on détournait le problème en passant dans mon fichier par mon résultat de la ligne en colonne M (qui est la somme des courses, avec somme(E:K) que je divise par le nombre de courses réalisés en colonne N avec NB(E:K).
Je pensais à copier coller une formule qui pourrait ressembler à cela pour qu'elle s'applique à chaque fois: ma cellule mise en ligne 3 par exemple
=$M3/$N3 comme cela si je la copie en G7 j'aurais =$M7/$N7

Le soucis c'est que je fais référence aussi à une variable. Est-il possible de copier uniquement la valeur de ma colonne M et N? du style =valeur($M3/$N3)

voici le fichier joint avec ces formules

Merci encore

 

[ROGER2327]

Re : moyenne variable dans le tableau

Re...

D'abord une importante correction. Les formules que j'ai données précédemment fonctionnent mais sont beaucoup trop compliquées. En voici d'autres, plus légères :
Pour prendre en compte les colonnes E à K

=SI(NB(SI(COLONNE()=5;$F2:$K2;$E2:H2);SI(COLONNE()=11;$E2:$J2;J2:$K2));MOYENNE(SI(COLONNE()=5;$F2:$K2;$E2:H2);SI(COLONNE()=11;$E2:$J2;J2:$K2));"")

Pour prendre en compte les colonnes E à L

=SI(NB(SI(COLONNE()=5;$F2:$L2;$E2:H2);SI(COLONNE()=12;$E2:$K2;J2:$L2));MOYENNE(SI(COLONNE()=5;$F2:$L2;$E2:H2);SI(COLONNE()=12;$E2:$K2;J2:$L2));"")

Maintenant, concernant votre dernier message, il me semble qu'en calculant la moyenne avec les colonnes M et N, vous ne faites que déplacer le problème. Car comme vous le dites :

Le soucis c'est que je fais référence aussi à une variable.

cette variable étant justement dans la cellule où vous coller la formule. D'où référence circulaire... On n'en sort pas !
Je n'ai pas d'autre idée pour l'instant.​

ROGER2327

 

[cyrilV]

Re : moyenne variable dans le tableau

Bonsoir Roger,

Je m'apperçois que le problème est très complexe; voire impossible à résoudre. Je vais veiller à trouver un autre cheminement pour avoir les attentes que j'ai dans mon programme.
En tout cas merci sincèrement d'avoir passé du temps à le résoudre.

Bon WE

Cyril

 

[ROGER2327]

Re : moyenne variable dans le tableau

Bonsoir Roger,

Je m'aperçois que le problème est très complexe; voire impossible à résoudre. Je vais veiller à trouver un autre cheminement pour avoir les attentes que j'ai dans mon programme.
En tout cas merci sincèrement d'avoir passé du temps à le résoudre.

Bon WE

Cyril

Merci, mais je n'ai fait qu'émettre un avis. Je serais heureux que d'autres se mêlassent de la discussion, que ce soit pour infirmer ou confirmer cet avis.​

Bon courage pour la suite.
ROGER2327

 

[pierrejean]

Re : moyenne variable dans le tableau

bonjour cyrilV
Salut ROGER

une tentative par fonction personnalisée

 

[ROGER2327]

Re : moyenne variable dans le tableau

Bonjour à tous

La formule de pierrejean calcule la moyenne des valeurs présentes dans les cellules de la ligne où on la colle (ou renvoie une chaîne vide s'il n'y a pas de valeurs numériques dans la ligne. Elle remplace donc (en plus court !) les formules déjà données dans cette discussion.
Cependant, comme les autres formules, elle ne peut être utilisée qu'une seule fois dans la même ligne. Si elle est utilisée plusieurs fois, un référence circulaire se produit.
On peut expliquer pourquoi : Lorsqu'on place la formule pour la deuxième fois dans une même ligne, elle calcule la moyenne des valeurs déjà présentes dans la ligne. Or, l'une de ces valeurs est déjà calculée par la formule. Donc, elle fait appel à son propre résultat, ce qui est exactement ce qu'on appelle une référence circulaire.
Le problème reste donc posé.​

Bonne journée !
ROGER2327