A voir également:
- Moyenne en retirant les extrêmes + mode conditionnelle
- Mise en forme conditionnelle excel - Guide
- God mode - Guide
- Mode sécurisé samsung - Guide
- Démarrer en mode sans echec - Guide
- Formule moyenne excel - Guide
6 réponses
via55
Messages postés
14408
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
5 mai 2024
2 705
24 juin 2022 à 17:18
24 juin 2022 à 17:18
Bonjours Lola
Oui la moyenne sans les extrêmes peut se faire avec la fonction MOYENNE.SI.ENS
Quant à la mise en couleurs ce n'est pas par fonction mais par Mise en forme conditionnelle que cela se fait
Exemple de tout ceci, à adapter à ton fichier :
https://www.cjoint.com/c/LFypq2ZoDOJ
Cdlmnt
Via
Oui la moyenne sans les extrêmes peut se faire avec la fonction MOYENNE.SI.ENS
Quant à la mise en couleurs ce n'est pas par fonction mais par Mise en forme conditionnelle que cela se fait
Exemple de tout ceci, à adapter à ton fichier :
https://www.cjoint.com/c/LFypq2ZoDOJ
Cdlmnt
Via
via55
Messages postés
14408
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
5 mai 2024
2 705
28 juin 2022 à 20:14
28 juin 2022 à 20:14
Re,
Les valeurs max et min c'est parce que tu voulais exclure les valeurs extrêmes de ta moyenne
Mais comme tu prends la médiane ce n'est plus d'actualité ☺ et je pense que vu la dispersion de tes séries c'est mieux de se baser sur la médiane comme je disais dans mon dernier post
Ton fichier avec les MFC
https://www.cjoint.com/c/LFCsfjAD40J
Pour les valeurs proches de la médiane j'ai pris l'option comme j'avais fait dans l'autre fichier de mettre l'écart en plus ou en moins non pas en dur dans les formules de MFC mais dans la cellule V1 de la feuille, comme ça tu peux le modifier et voir celui qui te convient le mieux
J'ai pris ensuite l'option non pas de le mettre en montant mais en pourcentage car vu la grande diversité des séries un écart de 50 € par ex aurait été énorme pour certaines et trop peu pour d'autres alors qu'en % c'est cohérent pour chaque série
Explication de la formule de MFC pour les valeurs proches (orange) :
=ET(EST.IMPAIR(COLONNE());C4<>"";C4<=$S4+$S4*$V$2;C4>=$S4-$S4*$V$2)
La MFC s'applique :
- si la colonne est impaire (3eme, 5eme etc) puisque les PU se trouvent que dans les colonnes impaires
- et si la cellule n'est pas vide (<>"")
- et si la valeur de la cellule est inférieure ou égale à la valeur de la médiane augmentée du % d'écart
- et si la valeur de la cellule est supérieure ou égale à la valeur de la médiane diminuée du % d'écart
Cdlmnt
Via
Les valeurs max et min c'est parce que tu voulais exclure les valeurs extrêmes de ta moyenne
Mais comme tu prends la médiane ce n'est plus d'actualité ☺ et je pense que vu la dispersion de tes séries c'est mieux de se baser sur la médiane comme je disais dans mon dernier post
Ton fichier avec les MFC
https://www.cjoint.com/c/LFCsfjAD40J
Pour les valeurs proches de la médiane j'ai pris l'option comme j'avais fait dans l'autre fichier de mettre l'écart en plus ou en moins non pas en dur dans les formules de MFC mais dans la cellule V1 de la feuille, comme ça tu peux le modifier et voir celui qui te convient le mieux
J'ai pris ensuite l'option non pas de le mettre en montant mais en pourcentage car vu la grande diversité des séries un écart de 50 € par ex aurait été énorme pour certaines et trop peu pour d'autres alors qu'en % c'est cohérent pour chaque série
Explication de la formule de MFC pour les valeurs proches (orange) :
=ET(EST.IMPAIR(COLONNE());C4<>"";C4<=$S4+$S4*$V$2;C4>=$S4-$S4*$V$2)
La MFC s'applique :
- si la colonne est impaire (3eme, 5eme etc) puisque les PU se trouvent que dans les colonnes impaires
- et si la cellule n'est pas vide (<>"")
- et si la valeur de la cellule est inférieure ou égale à la valeur de la médiane augmentée du % d'écart
- et si la valeur de la cellule est supérieure ou égale à la valeur de la médiane diminuée du % d'écart
Cdlmnt
Via
Bonjour,
Les prix sont très hétérogènes. Donc, si je m'appuie sur ce que vous venez de dire, la médiane n'est pas adaptée. Pensez-vous que la formule moyenne en retirant les valeurs extrêmes (cad le prix très élevée et le prix le plus bas) est adaptée pour mon analyse ?
Si oui, comment dois-je procéder pour appliquer cette formule à mon tableau sachant que ce sont les prix unitaires HT qui m'intéresse.
En d'autres termes, il me faut une formule qui me permette d'avoir pour une ligne de prestation (par exemple la prestation n°1), la moyenne des prix unitaires HT de cette ligne. Tout en sachant que les prix proposés par les candidats sont très différents et donc je risque de fausser mon analyse en utilisant simplement la formule de calcul "moyenne".
Je ne peux malheureusement pas le faire à la main car il y a plus de 800 lignes et 20 colonnes environ.
Les prix sont très hétérogènes. Donc, si je m'appuie sur ce que vous venez de dire, la médiane n'est pas adaptée. Pensez-vous que la formule moyenne en retirant les valeurs extrêmes (cad le prix très élevée et le prix le plus bas) est adaptée pour mon analyse ?
Si oui, comment dois-je procéder pour appliquer cette formule à mon tableau sachant que ce sont les prix unitaires HT qui m'intéresse.
En d'autres termes, il me faut une formule qui me permette d'avoir pour une ligne de prestation (par exemple la prestation n°1), la moyenne des prix unitaires HT de cette ligne. Tout en sachant que les prix proposés par les candidats sont très différents et donc je risque de fausser mon analyse en utilisant simplement la formule de calcul "moyenne".
Je ne peux malheureusement pas le faire à la main car il y a plus de 800 lignes et 20 colonnes environ.
brucine
Messages postés
14656
Date d'inscription
lundi 22 février 2021
Statut
Membre
Dernière intervention
18 mai 2024
1 891
27 juin 2022 à 17:54
27 juin 2022 à 17:54
Je suis peut-être allé trop vite parce que passer par MIN et MAX, enlever ces valeurs et diviser par le nombre de valeurs restantes ne fonctionne que s'il n'y a pas plusieurs valeurs MIN ou MAX identiques.
La formule de via55 est très certainement meilleure, le hic c'est que tes valeurs ne sont pas contiguës; je n'en suis pas expert, je le laisse revenir gérer cette situation.
Ma remarque est comme je l'ai déjà dit inapplicable si grand nombre de colonnes, mais 20 (colonnes ou candidats, donc 40?) ce n'est pas impossible, seulement fastidieux: on ajoute C3 + E3 + ....
La formule de via55 est très certainement meilleure, le hic c'est que tes valeurs ne sont pas contiguës; je n'en suis pas expert, je le laisse revenir gérer cette situation.
Ma remarque est comme je l'ai déjà dit inapplicable si grand nombre de colonnes, mais 20 (colonnes ou candidats, donc 40?) ce n'est pas impossible, seulement fastidieux: on ajoute C3 + E3 + ....
Merci tout de même d'avoir essayé
D'accord. J'attends la réponse de via55 ou toute autre personne qui a la réponse
D'accord. J'attends la réponse de via55 ou toute autre personne qui a la réponse
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
via55
Messages postés
14408
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
5 mai 2024
2 705
Modifié le 27 juin 2022 à 20:34
Modifié le 27 juin 2022 à 20:34
Re,
Pour poster ton fichier tu fais comme moi, tu vas sur le site cjoint.com, tu télécharges ton fichier, tu fais créer en lien (en bas de la feuille) que tu copies et reviens coller ici
Avec des colonnes alternées il faut procéder un peu autrement : calculer le max, le min puis la moyenne seulement si le titre de la colonne correspond au PU
Pour la médiane comme il n'y a pas de formule MEDIANE.SI je fais d'abord extraire dans un tableau secondaire les valeurs qui sont supérieures au min et inférieures au max et on calcule la médiane sur ces dernières
Quant à savoir si c'est la moyenne ou la médiane qui sera la plus pertinente dans ton cas il n'y a que toi qui peut le dire, mais comme l'a relevé brucine , que je salue au passage ☺, la médiane donne une mesure de la tendance meilleure que la moyenne dans le cas de série très dispersée et asymétrique, exemple prenons une série de 5 prix : 5, 10, 10, 10, 20 et 30, la moyenne est d'environ 14 et la médiane de 10
https://www.cjoint.com/c/LFBsDskhzgJ
Quant aux MFC j'ai d'abord mis une cellule pour entrer l'écart en plus ou en moins autour de la moyenne qui permet de déterminer la fourchette de valeurs proches qui seront en orange, j'ai aussi fait afficher de manière particulière la valeur mini et la valeur maxi
En modifiant les formules de MFC (en remplaçant les P3 par des R3) tu peux faire appliquer les couleurs en fonction de la médiane au lieu de la moyenne si tu veux
Cdlmnt
Via
Pour poster ton fichier tu fais comme moi, tu vas sur le site cjoint.com, tu télécharges ton fichier, tu fais créer en lien (en bas de la feuille) que tu copies et reviens coller ici
Avec des colonnes alternées il faut procéder un peu autrement : calculer le max, le min puis la moyenne seulement si le titre de la colonne correspond au PU
Pour la médiane comme il n'y a pas de formule MEDIANE.SI je fais d'abord extraire dans un tableau secondaire les valeurs qui sont supérieures au min et inférieures au max et on calcule la médiane sur ces dernières
Quant à savoir si c'est la moyenne ou la médiane qui sera la plus pertinente dans ton cas il n'y a que toi qui peut le dire, mais comme l'a relevé brucine , que je salue au passage ☺, la médiane donne une mesure de la tendance meilleure que la moyenne dans le cas de série très dispersée et asymétrique, exemple prenons une série de 5 prix : 5, 10, 10, 10, 20 et 30, la moyenne est d'environ 14 et la médiane de 10
https://www.cjoint.com/c/LFBsDskhzgJ
Quant aux MFC j'ai d'abord mis une cellule pour entrer l'écart en plus ou en moins autour de la moyenne qui permet de déterminer la fourchette de valeurs proches qui seront en orange, j'ai aussi fait afficher de manière particulière la valeur mini et la valeur maxi
En modifiant les formules de MFC (en remplaçant les P3 par des R3) tu peux faire appliquer les couleurs en fonction de la médiane au lieu de la moyenne si tu veux
Cdlmnt
Via
Je te remercie pour ton retour. Je t'avouerai que je ne comprends pas la formule. Pourquoi mettre valeur min et valeur max.
J'ai essayé de simplifier la formule en utilisant la fonction : =MEDIANE(....;....)
Toutefois, je bloque au niveau de la mise en forme conditionnelle, je n'arrive pas à trouver un moyen d'utiliser cette mise en forme pour tout le tableau : pour chaque ligne, les prix les plus bas par rapport à la valeur considérée comme médiane en vert, les prix les plus élevé par rapport à la médiane en rouge et les prix se rapprochant de la médiane en orange.
Ci-joint le lien pour accéder au document : https://cjoint.com/c/LFCmZ111KIc
Merci d'avance
Cordialement,
J'ai essayé de simplifier la formule en utilisant la fonction : =MEDIANE(....;....)
Toutefois, je bloque au niveau de la mise en forme conditionnelle, je n'arrive pas à trouver un moyen d'utiliser cette mise en forme pour tout le tableau : pour chaque ligne, les prix les plus bas par rapport à la valeur considérée comme médiane en vert, les prix les plus élevé par rapport à la médiane en rouge et les prix se rapprochant de la médiane en orange.
Ci-joint le lien pour accéder au document : https://cjoint.com/c/LFCmZ111KIc
Merci d'avance
Cordialement,
Modifié le 27 juin 2022 à 16:49
Je vous remercie de votre retour. Toutefois, j'ai encore du mal à comprendre.
Je vous explique mon problème (je vous ai joint une image, je n'arrive pas à publier un fichier excel sur le forum) : je constate des différences importantes de prix unitaire HT. Il faut que je fasse la moyenne pour chaque ligne de prestations des prix unitaires HT en retirant les prix extrêmes (la plus chère et la plus basse). Comment dois-je procéder en utilisant votre formule ? j'ai essayé mais je n'y arrive pas.
En outre, pensez-vous que c'est la meilleure méthode qui permette de comparer les prix unitaires HT par ligne de prestation ? Aussi quelle est la différence entre la formule de calcul permettant de calculer la moyenne en retirant les valeurs extrêmes et la formule de calcul permettant de calculer la médiane ?
Enfin si la formule de calcul de la moyenne en retirant les valeurs extrêmes est la plus appropriée pour mon analyse, il faut que j'utilise la mise en forme conditionnelle pour mettre en évidence les prix unitaires HT qui sont trop élevés de cette moyenne (en rouge) et ceux qui sont plus bas que cette moyenne (en vert) et ceux qui rapprochent de la moyenne (en orange).
D'avance merci
27 juin 2022 à 17:09
La médiane est la valeur centrale (celle autour de laquelle il y a autant de valeurs plus basses que de valeurs plus élevées): elle ne correspond donc à la moyenne que si la dispersion autour de la moyenne est homogène: exprimé autrement, elle n'a pas de sens si par exemple quelques valeurs sont très basses et beaucoup très hautes pour la même moyenne.
Du fait que tu as 2 colonnes par valeur (prix unitaire et prix global), je ne sais pas si tu n'as pas plus vite fait de faire ça "à la main" réserve faite que, si le nombre de lignes est très élevé, celui des colonnes reste faible: il suffit de faire la somme des cellules, d'en enlever la valeur rendue par MIN et par MAX de cet ensemble de cellules et de diviser par le nombre des cellules concernées moins 2; la coloration conditionnelle ne pose ensuite pas de problème dès lors qu'on a défini le pourcentage et le sens de différence avec cette moyenne.