Calcul écarts de manière linéaire

[Claude]

Bonjour le Forum,
Il s'agit de calculer les écarts des numéros dans les 4 lignes de 5 numéros chacune ci-dessous :
3 4 10 23 43
4 12 24 27 36
1 4 10 19 23
14 15 28 35 40
comme si chaque ligne était disposée verticalement
de telle sorte à faire une colonne de 20 numéros.
En effet : si l'on considère les 4 lignes de 5 numéros ci-dessus, le N°3 est à l'écart 3 et si l'on dispose chaque ligne en position verticale, le N°3 sera à l'écart 19.
Comment réaliser cela en ayant une liste de numéros disposée en lignes de 5 numéros?

Merci de me renseigner. Ci-joint explication plus détaillée [file name=ecartsnumeroslineaire.zip size=3629]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/ecartsnumeroslineaire.zip[/file]

 

[andré]

Salut Claude,

Peu compréhensible comme question, sauf si tu expliques ce que tu entends par 'écarts'.

Dis-nous comment tu obtiens 3 et 19.

Â+

 

[JeanMarie]

Bonjour Claude, André

Claude essaye avec cette formule,
=SI(NB.SI($A$1:$E$4;T4);20-SOMMEPROD((T4=$A$1:$E$4)*((5*(LIGNE($A$1:$E$4)-1))+COLONNE($A$1:$E$4)));'-')

A coller en U4, et fait un glisser déposser

@+Jean-Marie

 

[Claude]

Salut Jean-Marie, André,

La formule ne marche pas partout :
Numéros Ecart Ta formule
de réf.
1 9 9
2 - -
3 19 19
4 8 0

En effet, dans la colonne de gauche tu vois bien
que le N°4 est bien à l'écart 8, pour le vérifier, il suffit de compter du dernier N°40 jusqu'au N°10 qui suit le N°4 pour constater que l'écart ou la distance qui les sépare est de 8 et non de 0 !

Pour répondre à André, je dirais que si je positionne les 4 lignes de 5 numéros verticalement j'aurais une colonne de 20 numéros qui commence par le N°3 et qui fini par 40.
Donc, si je veux connaître l'écart ou la distance entre le dernier 40 et le premier le 3, j'aurais la valeur de 19.
Par contre, si je considère mes 4 lignes de 5 numéros comme faisant partie de 4 tirages différents, en comptant verticalement, pour le N°3 nous aurons un écart de 3 !

Merci de votre aide à tous, en attendant la solution. Merci Jean-Marie je pense que tu ais sur la bonne voie

 

[Claude]

mes excuses pour la présentation du petit tableau, il était bien pendant ma saisie mais après sur la page Web il est plutôt bizarre.
En fait, la première colonne
1
2
3
4
représent le numéros
la 2ème colonne
9
-
19
8
représente la colonne écarts de référence
et enfin la 3e colonne :
9
-
19
0
correspond à la formule de Jean-Marie où l'on remarque bien que l'écart du N°4 qui est de 8 dans la colonne de référence, est de 0 dans la 3è colonne, ce qui indique un problème dans la formule.
à +

 

[JeanMarie]

Bonsoir

Voilà une autre formule, mais à valider par Ctrl+shift+Entrer (c'est une formule matricielle)
=SI(NB.SI($A$1:$E$4;T4);20-MAX((T4=$A$1:$E$4)*((5*(LIGNE($A$1:$E$4)-1))+COLONNE($A$1:$E$4)));'-')

@+Jean-Marie

 

[Claude]

Bravo Jean-Marie, c'est la bonne formule, ça marche, tu es un champion!

Avec tous mes remerciements A bientôt

Claude

 

[Claude]

Claude écrit:

Bravo Jean-Marie, c'est la bonne formule, ça marche, tu es un champion!

Avec tous mes remerciements A bientôt

Claude

En fait me suis précipité trop vite dans mon enthousiasme, si j'applique ta formule à un ensemble plus grand de ligne de 5 numéros, les résultats sont totalement erronés.
Je t'adresse en détail le travail en question

Merci de voir s'il est possible de faire quelque chose.

Claude

 

[Claude]

Claude écrit:

Claude écrit:

Bravo Jean-Marie, c'est la bonne formule, ça marche, tu es un champion!

Avec tous mes remerciements A bientôt

Claude

En fait me suis précipité trop vite dans mon enthousiasme, si j'applique ta formule à un ensemble plus grand de ligne de 5 numéros, les résultats sont totalement erronés.
Je t'adresse en détail le travail en question

Merci de voir s'il est possible de faire quelque chose.

Claude

Bon le fichier est trop lourd je peux pas le joindre hélas!.. à +

 

[JeanMarie]

Re...

Indique moi la référence de la plage qui contient tes valeurs.

@+Jean-Marie

 

[Claude]

Bonjour Jean-Marie,

La référence de la plage est :
B4:F105
en fait ça représente 102 lignes de 5 numéros qui vont augmenter par la suite.
A mon avis la difficulté vient du fait que la formule rencontre plusieurs fois le N°1, 2, 3 ect et qu'en fait l'écart doit être calculé par rapport aux tous derniers numéros sortis.
A noter que les numéros sont dans le désordre dans chacune des lignes.

En te remerciant. à + -
Claude

 

[JeanMarie]

Re

Essaye ceci
=SI(NB.SI($B$4:$F$105;T4);510-MAX((T4=$B$4:$F$105)*((5*(LIGNE($A$1:$E$102)-1))+COLONNE($A$1:$E$102)));'-')

@+Jean-Marie

 

[Claude]

Merci Jean-Marie,

Curieusement elle semble marcher.
Voici le contexte exact :
de A4 à A102 les numéros des tirages de 1 à 102
de B4:F102 la plage des lignes à 5 numéros
De H4 à H53 la liste des N° de 1 à 50
En I4 la formule
Je dis curieusement, car la formule fait état de $A$1:$E$102 qui correspond à une autre feuille de calcul où figurait la première question !
Mais si je veux transposer la formule dans un autre contexte, les résultats sont à nouveau erronés, je pense qu'il ne manque pas grand chose pour que ca marche vraiment.

Avec tous mes remmerciements à+

Claude -

 

[JeanMarie]

Bonsoir Claude

J'aurais peut écrire cette partie
5*(LIGNE($A$1:$E$102)-1))+COLONNE($A$1:$E$102)) );'-')
ainsi (pour la faire correspondre à la plage B4:F105)
5*(LIGNE($B$4:$F$105)-4))+COLONNE($B$4:$F$105)-1) );'-')

Donc, le -4 doit correspondre au n° de la ligne où commence ta plage, et le -1 doit correspondre au n° de la colonne où commence ta plage.

@+Jean-Marie

 

[Claude]

Bonsoir Jean Marie,

Tes dernière indications ont été capitales, le problème était surtout au niveau des numéros de lignes et de colonnes.
Pour ma part j'ai transposé selon mes coordonnées propres et la formule tombe juste.
Par contre, une chose qui m'échappe au niveau des numéros de colonnes. Dans un cas ou j'ai dans la formule COLONNE($B$4:$F$120)-1) ) le comptage se fait en partant de la gauche et effectivement ma première colonne démarre en B4. Dans un autre cas, pour le m^me résultat, j'ai COLONNE($J$2:$N$5000)-14) ) j'ai du compter 14 colonnes jusqu'a la dernière en N2, alors que la première colonne démarre en J2 !
Je comprends pas

Merci à bientôt

Claude -