Analyse d'écarts valeur et pourcentage Résolu/Fermé

Question

Bonjour,

J'ai besoin d'aide pour évaluer des écarts en valeurs, valeur absolue et pourcentage. 
Je m'explique : dans mon fichier ci-joint j'ai les résultats de plusieurs formules et je souhaiterai déterminer quelle est celle qui se rapproche le plus de la réalité (colonne I : Sorties 12H).
J'ai besoin de faire plusieurs comparaisons :

	Evaluer l'écart entre colonne E/F/G et colonne I 
==> BUT : voir qui se rapproche le plus de la colonne I 

	Ecart entre la PREV (colonne H) et la réalité (colonne I)
	Ecart entre les colonnes E/F/G et la PREV
Dans un 2ème temps, je souhaiterai également déterminer le poids (coefficient) de sortie pcd j (colonne C) par rapport à la sortie globale. Pour cela exemple ligne 2 : le pcd J = I2 -B2 = 199 donc 199/205 = 0,97 donc pour cet article 97% sont des PCD. 
Mais comment déterminer un coefficient global . ? pour l'ensemble de mes articles ?
Voici le fichier : https://www.cjoint.com/c/EGwpVrNMSFm
Merci beaucoup.

ccm81 · Answer

Bonjour

Quelques tentatives, mais tes demandes manquent de précision(s)
http://www.cjoint.com/c/EGwrdspS0bR

Cdlmnt

Kivanc · Answer

Bonjour,
Merci beaucoup pour votre aide.
Je vous explique la situation : je n'arrive à obtenir les commandes du jour qu'à partir de 12H or à 8h il faudrait que j'arrive à avoir une idée (la plus proche) des sorties réelles de 12h.
Pour cela je teste plusieurs formule car les sorties HS à 8h sont complètes mais les PCD ne sont définitives qu'à 12h.
1ère solution (colonne F) : additionner les sorties HS et  la sortie PCD du même article à J-7 mais je me suis rendue compte qu'elle était parfois nulle donc ça fausse les résultats.
2éme solution (colonne E) : prendre la plus grande valeur entre PCD J et PCD J-7 (pour régler le pb de PCDS J-7 = 0) mais ça n'est pas une solution car à 8h les PCD ne sont pas du tout visible en totalité.
3ème solution : Je pense qui sera la plus fiable : additionner les HS et les PCD du jour (8H) * un coefficient.
J'ai mis 2 en colonne G mais seulement pour tester la formule : il faudrait que je trouve un coefficient fiable par lequel multiplier la sortie PCD pour obtenir un résultat des plus proches de celui de 12H.
Ensuite nous avons la possibilité d'obtenir la prevision de sortie (calculer la veille) colonne H : voir si elle peut être plus proche de 12h et peut être l'utiliser pour une solution 4.

Voilà ma demande plus en détail, j'espère avoir été claire. Pouvez-vous de plus m'expliquer vos formules ?

merci

Kivanc · Answer

De plus pour avoir une meilleure vision je pense qu'il faut mesurer l'écart en valeurs, valeur absolue et pourcentage.

ccm81 · Answer

Le même avec les formules et quelques explications
http://www.cjoint.com/c/EGxj3HWFUAR

RQ. Je veux bien t'aider à écrire les formules excel qui correspondent à tes demandes, mais je ne suis pas compétent pour te dire si tes demandes sont pertinentes par rapport à ton problème !

Cdlmnt

Kivanc · Answer

Bonjour,
« J 'ai mis la valeur du coeff en Q1, il te suffit de changer sa valeur  pour voir ce que ça donne en colonne G »
Quelle est justement la meilleure solution pour déterminer ce coefficient ? 
Quand je demandais de mesure les écarts des différentes colonnes je pensais à le faire séparément. Ce que vous m'avez envoyé est très bien mais pour ces quelques lignes. Mon fichier d'origine contient dans les 9000 lignes... je pensais à avoir des pourcentages pour pouvoir ensuite voir par exemple dans quelle colonne il y'a le plus de valeurs négatives...
Voici un fichier fait par une autre personne du même style... mais il ne contient pas de formules : https://www.cjoint.com/c/EGxmRWyjm4n
Merci à vous.

Kivanc · Answer

Bonjour,

Mes explications sont elles assez claires ?

merci à vous

ccm81 · Answer

Pas vraiment

Envoies un bout de fichier avec une vingtaine de lignes et un maximum de ces différents, mais où tu indiques les colonnes à "formuler" et surtout, les résultats attendus dans ces colonnes 

Cdlmnt

ccm81 · Answer

S'il y a des 0 ou des cellules vides, que fait on ?

ccm81 · Answer

Quand tu dis
écart (HS + PCD J-7) Sortie 12H en %

1. il faut préciser
écart (HS + PCD J-7) par rapport à Sortie 12H en %
ou 
écart Sortie 12H  par rapport à  (HS + PCD J-7)  en %

2. c'est signé ou en valeur absolue ?

ccm81 · Answer

Un début de réponse avec ce que j'ai compris
http://www.cjoint.com/c/EGyp3rBlX4R
Je n'ai mis les formules que de la ligne 80 à la ligne 101
Tu dis si ça correspond

Pour les deux colonnes restantes, il me faut plus d' explications.

Cdlmnt

Kivanc · Answer

Merci à vous.

Pour la ligne 80 : la formule qui se rapproche le plus de la sortie de 12h est la colonne S: l"écart en valeur absolue le plus faible. 
Ce que je veux c'est analyser ces écarts que vous avez calculé en colonnes W à AF afin de savoir la formule qui se rapproche le plus des sorties réelles de 12h Mais je ne sais pas comment faire. Avec une formule ? ou un tableau croisé dynamique ?

Pour la colonne AG : c'est la part du PCD. en effet les sorties totales sont égales à HS + PCD mais pour le moment on ne connait pas la portion des PCD voilà pourquoi je souhaiterai évaluer la portion des PCD pour comme dans la colonne S et T pouvoir faire une nouvelle formule : HS + PCD J * COEFFICIENT et analyser l'écart à nouveau.

Kivanc · Answer

Bonjour,

Merci de me dire si ce n'est pas clair

ccm81 · Answer

Bonjour

Non, ce n'est pas clair. 
Que doit on calculer en colonnes AG et AH, comment, à partir de quoi. 
De plus, je t'ai demandé de donner des exemples de résultat attendu. 

Cdlmnt

Kivanc · Answer

En AG : Sortie 12H/ Sortie HS en ignorant les 0. 
=> Ici je voudrai trouver le coefficient du PCD pour tester une nouvelle formule. Donc mon idée était de faire la moyenne des valeurs obtenues en colonne AG; Qu'en pensez vous ?

En AH : Déterminer quelle colonne (W,X,Y,Z ou AA) a l'écart le plus faible avec la colonne V. L'idéal serait ici d'avoir des caractères. Exemple : formule 1 pour la colonne W, formule 2 pour la colonne X... Pour que je puisse à la fin dans mon analyse voir s'il y'a plus de formule 1 que de formule 2 .... Je pense que ça va être compliqué : 
Donc est-il possible colonne AH : déterminer la valeur ayant l'écart le moins gros avec la sortie 12h entre les colonnes W à AA;
Et en colonne AI : reconnaitre la valeur en AH et déterminer si c formule 1 ou 2..
Merci de me dire si ça n'est toujours pas clair

Kivanc · Answer

Bonjour :

Voici à peu près ce que je recherche j'ai ajouté les colonnes : https://www.cjoint.com/c/EGBhkCfJQzn

merci

ccm81 · Answer

Ce que j'ai compris (pour la colonne formule)  (lignes 80-101)
http://www.cjoint.com/c/EGBj5Ac7AJR

Cdlmnt

Lien modifié

Kivanc · Answer

Merci 
J'ai complété : https://www.cjoint.com/c/EGBkk5yx3ln

Comment enlever les #Valeur colonne AI.
Pouvez vous svp m'expliquer cette formule matricielle ? et m'expliquer également le si(ou des formules des écarts?
La formule ignore la veleur si elle est à 0 ou vide ?

ccm81 · Answer

Tu n'as pas utilisé le bon fichier, j'ai modifié le lien du message 19 à 11h58

Cdlmnt

Kivanc · Answer

Très bien merci.

Pouvez vous m'expliquer littéralement ce que font les formules des écarts, puis la formule matricielle et celle qui permet de reconnaitre la Formule.

merci

ccm81 · Answer

=SI(ET(W80="";X80="";Y80="";Z80="");""; MIN(SI(W80:AA80<>"";ABS(W80:AA80))))
Si les 4 cellules de W80:AA80 sont vides on renvoie ""
sinon on calcule le min des cellules non vides de la plage W80:AA80
RQ. Si on ne fait pas les test MIN(SI(... la formule renvoie une erreur dès qu'une cellule de W80:AA80 est vide 

=SI(ESTERR(SI(ESTNA("F" & EQUIV(AH80;ABS(W80:AA80);0));EQUIV(-AH80;ABS(W80:AA80);0);"F" & EQUIV(AH80;ABS(W80:AA80);0)));"";SI(ESTNA("F" & EQUIV(AH80;ABS(W80:AA80);0));EQUIV(-AH80;ABS(W80:AA80);0);"F" & EQUIV(AH80;ABS(W80:AA80);0)))

on décompose un peu

EQUIV(AH80;ABS(W80:AA80);0) renvoie le rang (1, 2, 3 ou 4) de ABS(AH80) dans la plage W80:AA80
EQUIV(-AH80;ABS(W80:AA80);0) renvoie le rang (1, 2, 3 ou 4) de -ABS(AH80) dans la plage W80:AA80

"F" & EQUIV(AH80;ABS(W80:AA80);0) concatène (colle) le rang obtenu à la lettre F

Tout le reste est là pour gérer les erreurs éventuelles

1. La fonction EQUIV renvoie #N/A lorsque la valeur cherchée (AH80) n'est pas trouvée dans la plage W80:AA80, dans ce cas, on recherche -ABS(AH80)
SI(ESTNA("F" & EQUIV(AH80;ABS(W80:AA80);0));EQUIV(-AH80;ABS(W80:AA80);0);"F" & EQUIV(AH80;ABS(W80:AA80);0)))
se lit 
Si AH80 n'est pas trouvé dans la plage W80:AA80
Alors on recherche -ABS(AH80)
Sinon on reherche ABS(AH80)

2. =SI(ESTERR( dans la recherche ci-dessus .... on renvoie vide sinon on renvoie le résulat de la recherche)
Est là pour gérer les autres cas d'erreur 

Voilà

Kivanc · Answer

Très bien merci !

ccm81 · Answer

De rien

Si c'est fini, peux mettre le sujet à résolu (en dessous du titre de ton premier message)

Bon courage pour la suite ...

Analyse d'écarts valeur et pourcentage

22 réponses

Discussions similaires