Automatiser le calcul des écarts ?

[thierry]

Bonjour,

Dans le tableau ci-joint, j'aimerais pouvoir automatiser le calcul des écarts de présence d'une même valeur et renvoyer les résultats dans un feuillet 3. Pour trouver l'écart max, il suffira de rechercher la plus grande valeur dans le feuillet 3.

Si quelqu'un veut bien se donner la peine de solutionner ce problème, merci par avance.

Amicalement.

 

[JJ1]

Re : Automatiser le calcul des écarts ?

Bonsoir Thierry,

J'avais déjà répondu à une question similaire, regarde ce lien:
https://www.excel-downloads.com/threads/excel-et-euromillion-calcul-des-ecarts.146885/
le fichier Excel est "nouveau2", il donne tous les écarts successifs des nombres de 1 à 10 en exemple.
(c'est une matrice)
Bonne soirée

 

[thierry]

Re : Automatiser le calcul des écarts ?

Bonjour JJ1,

Sympa la matrice ! Cependant, dans mon cas c'est difficilement exploitable car mes résultats sont classés du plus ancien (en haut) au plus récent. Cela ne donne pas grand chose (voir résultats en rouge dans le fichier ci-joint)

Merci pour ton aide.

Bonne journée !

 

[thierry]

Re : Automatiser le calcul des écarts ?

Re,

Ci-joint, fichier modifié avec les résultats que l'on devrait trouver.

@ +

 

[JJ1]

Re : Automatiser le calcul des écarts ?

Bonsoir Thierry,
Je ne comprends pas pourquoi ma formule ne va pas?
Je l'ai mise dans ton fichier, regarde et dis-moi?
Bonne soirée

 

[thierry]

Re : Automatiser le calcul des écarts ?

Re,

Lorsque je renseignerai la ligne 27 du feuillet 1, la même ligne 27 du feuillet 2 se complétera automatiquement et ladite formule deviendra inopérante car elle est limitée à : 7-26. J'ai essayé de trier les données du plus récent au plus ancien mais cela entraîne d'autres problèmes. De toute façon, en groupant les lignes le tableau est lisible.

Je te remercie pour ton attention.

Bonne soirée à toi aussi !

 

[david84]

Re : Automatiser le calcul des écarts ?

Bonsoir,
testé uniquement sur ton 1er fichier :

=SI(LIGNES($1:1)<=NB.SI(H$7:H$449;"<>"&0);PETITE.VALEUR(SI(SI(LIGNE(H$7:H$449)=LIGNE(H$7);SUBSTITUE(H$7;" ";1);H$7:H$449)<>0;LIGNE(H$7:H$449));LIGNES($1:2))-PETITE.VALEUR(SI(SI(LIGNE(H$7:H$449)=LIGNE(H$7);SUBSTITUE(H$7;" ";1);H$7:H$449)<>0;LIGNE(H$7:H$449));LIGNES($1:1))-1;"")

Formule matricielle à valider par Ctrl, Maj et entrée.
A+

 

[thierry]

Re : Automatiser le calcul des écarts ?

Merci, c'est exactement ce que je recherchais. Maintenant, il devient plus facile de calculer l'écart moyen et l'écart maxi. Il y a néanmoins encore une question que je me pose : pourquoi "#NOMBRE!" apparaît-il en AN8 ? Normalement, la colonne devrait s'achever par 4 comme dans la colonne AL...

Bonne soirée !

 

[david84]

Re : Automatiser le calcul des écarts ?

Re
soit :

=SI(ESTERREUR(PETITE.VALEUR(SI(SI(LIGNE(H$7:H$449)=LIGNE(H$7);SUBSTITUE(H$7;" ";1);H$7:H$449)<>0;LIGNE(H$7:H$449));LIGNES($1:2))-PETITE.VALEUR(SI(SI(LIGNE(H$7:H$449)=LIGNE(H$7);SUBSTITUE(H$7;" ";1);H$7:H$449)<>0;LIGNE(H$7:H$449));LIGNES($1:1))-1);"";PETITE.VALEUR(SI(SI(LIGNE(H$7:H$449)=LIGNE(H$7);SUBSTITUE(H$7;" ";1);H$7:H$449)<>0;LIGNE(H$7:H$449));LIGNES($1:2))-PETITE.VALEUR(SI(SI(LIGNE(H$7:H$449)=LIGNE(H$7);SUBSTITUE(H$7;" ";1);H$7:H$449)<>0;LIGNE(H$7:H$449));LIGNES($1:1))-1)

soit, le plus simple : utiliser une MFC avec une formule utilisant ESTERREUR.
A+

 

[thierry]

Re : Automatiser le calcul des écarts ?

Encore un petit inconvénient. Lorsque l'on souhaite éliminer les zéros inutiles qui apparaissent sous les cellules pleines, les formules de calcul des écarts actuel et précédant rendent ERREUR. Voir fichier joint pour explications.

Existe-t-il une autre alternative ?

Bonne journée !

 

[david84]

Re : Automatiser le calcul des écarts ?

Re
dans tes formules en H3:K4, tu as =EQUIV(0;$A:$A;0) qui te ramène N/A...
Pourquoi utilises-tu =EQUIV(0;$A:$A;0) dans tes formules ? A quoi te sert cette partie de formule ?
Il faudrait que tu reprennes cette partie.
Edit : peut-être remplacer

EQUIV(0;$A:$A;0)

par

EQUIV(RECHERCHE(9^9;$A:$A);$A:$A;0)

 

[thierry]

Re : Automatiser le calcul des écarts ?

Re David,

En remplaçant les termes de la formule, on obtient une fois sur deux un résultat erroné (voir doc ci-joint). Serait-il plus simple de se servir des résultats du calcul des écarts qui se trouvent plus à droite (en modifiant légèrement la formule afin qu'elle indique également le dernier écart) ? De plus, il serait possible de calculer l'écart moyen en divisant la somme des résultats par leur nombre.

 

[david84]

Re : Automatiser le calcul des écarts ?

Re

Serait-il plus simple de se servir des résultats du calcul des écarts qui se trouvent plus à droite (en modifiant légèrement la formule afin qu'elle indique également le dernier écart) ?

Pas compris : qu'entends-tu par

afin qu'elle indique également le dernier écart

.
Quels sont les résultats attendus ? Marque-les manuellement sur le fichier.
A+

 

[thierry]

Re : Automatiser le calcul des écarts ?

Les résultats attendus sont ceux qui apparaissent en AL7:AO9 (à l'exception de AN9 puisqu'il n'y a deux résultats). On retranche 1 au premier résultat puisque la première ligne ne compte pas dans ce cas. Ce qui me surprend dans ce fichier c'est que le dernier écart apparaît alors que dans un autre fichier (trop lourd pour pouvoir le poster ici) où je n'ai pas supprimé les zéros inutiles ce résultat n'apparaît pas ? Par contre, des zéros inutiles se retrouvent sous AL7:AO9 (sauf AN9) alors qu'il n'y en a aucun dans le gros fichier. Comme quoi le fait de toucher un seul élément a de multiples repercussions.

 

[david84]

Re : Automatiser le calcul des écarts ?

Re
Pas claire ta demande (si je te demande de me marquer manuellement les résultats, c'est qu'il y a une raison).
Ci-joint fichier :
- formule simplifiée en AL7/AO26
- pour le dernier écart, dans le doute 2 propositions en H3:K3 et AP3:AS3
- écart précédent : formule en H4:K4
A+