trouver PLUSIEURS valeurs proches d'une cible

[Justa22]

Bonjour,
Je n'ai jamais utilisé Excel et je cherche à trouver 15 valeurs (sur 40 chiffres) qui sont le plus près d'une cible. La cible est la moyenne des valeurs regroupées dans les deux lignes (sans prendre en compte les quatre valeurs 0,0000). Je sais comment faire d'abord une moyenne réduite qui élimine ces quatre valeurs de 0, mais ensuite pour trouver les 15 valeurs sur les 40 qui sont le plus près de cette moyenne réduite, je ne connais pas de formule pour le faire automatiquement.
J'ai besoin de reproduire cette même opération plusieurs centaines de fois alors j'aimerais trouver une façon rapide de le faire.
Est-ce que quelqu'un pourrait m'aider?
Merci beaucoup

 

[eriiic]

Bonjour,

pas simple ton truc...
Je t'ai laissé la construction en gris pour t'aider à comprendre le cheminement.
Résultat sous 2 formes : tableau ou MFC
Par contre je ne suis pas sûr à 100% que tu auras toujours 15 nombres. S'il y a des doublons à l'une ou l'autre extrémité ils risquent de tous sortir.
Avec 16 décimales je pense que le risque est minime, mais pas nul.
eric

Edit : je viens de voir une anomalie, je retire le fichier

 

[Justa22]

Merci beaucoup d'avoir pris le temps de regarder, par contre si je regarde visuellement (facile à faire pour un seul cas) les chiffres que vous avez générés ne sont pas les plus près de la moyenne réduite de 0,0563... (les chiffres dans les cases C2 ou O2 par exemple sont beaucoup plus près!)

 

[eriiic]

Oui, c'était l'ano en question (j'ai édité mon post mais tu as été trop rapide), je prenais les 15 plus éloignés.
Nouvelle version
eric

 

[Justa22]

Merci, ça a l'air de très bien fonctionner, le hic c'est que je n'ai littéralement jamais utilisé excel, donc je n'arrive pas à reproduire votre démarche sur une nouvelle feuille de calcul :/
Plus simplement (sans la fonction moyenne réduite), comment je peux utiliser votre formule "centile" et ensuite comment faites-vous pour souligner les valeurs sélectionnées (la fonction mfc?)
merci beaucoup

 

[eriiic]

Bonjour,

c'est une formule assez complexe, trop pour l'expliquer simplement.
Tu aurais dû déposer un fichier conforme à ce que tu utilises.
Remarque que les formules encadrées de { } sont des formules matricielles à valider avec Shift+Ctrl+Entrée (sur feuille, dans la MFC c'est fait d'office).
Les { } se mettent toutes seules si formule bien validée.

Pour les mises en forme conditionnelles (MFC) il y a beaucoup de tuto sur google.
Pour la voir ou la modifier :
- sélectionner B2:U3
- 'Mise en forme conditionnelle / Gérer les règles...'
- double-clic dessus pour l'éditer.
Je te remet la formule car j'avais oublié de me débarrasser d'une cellule intermédiaire :

=ABS(MOYENNE.SI($B$2:$U$3;">0")-B2)<=CENTILE(ABS(MOYENNE.SI($B$2:$U$3;">0")-$B$2:$U$3);15/40)

Adapter les plages si tu crées la MFC ailleurs ('Nouvelle règle / Utiliser une formule...').

Je te remet le fichier aussi vu qu'en B13:U14 il restais aussi cette cellule intermédiaire.
eric

 

[eriiic]

Quand je mets ta formule =ABS(MOYENNE.SI($B$2:$U$3;">0")-B2)<=CENTILE(ABS(MOYENNE.SI($B$2:$U$3;">0")-$B$2:$U$3);15/40) dans la case A10 ça n'inscrit que "faux"

C'est normal. C'est la formule de la MFC qui ne doit renvoyer que vrai ou faux.

Les étapes grisées sont là uniquement pour t'expliquer le cheminement.
Si tu veux les résultats sur feuille prend la formule en B13 et oublie les autres:

=SI(ABS(MOYENNE.SI($B$2:$U$3;">0")-B2)<=CENTILE(ABS(MOYENNE.SI($B$2:$U$3;">0")-$B$2:$U$3);15/40);B2;"")

que tu peux mettre sur la ligne que tu veux, à tirer vers le bas et vers la droite.
Et adapte les ref :
$B$2:$U$3 : ta plage de données
B2 : 1 cellule de tes données

Sinon montre ton vrai fichier, il y a peut-être une particularité.
eric

 

[eriiic]

En notant min et max en C2: D2, après avoir sélectionné toute la plage commençant en B4, la formule de MFC est :

=ET(B4>=$C$2;B4<=$D$2)

eric