XL 2019 Calcul de moyenne sur plage variable

[Frank Bellaisch]

Bonjour à tous
sur mon tableau ci-joint, j'ai par semaine (1, 2, 3 etc etc) les km parcourus par véhicule.
J'aimerais calculer la moyenne de km parcourus pour chacun et que ça s'adapte semaine après semaine puisque les données s'ajoutent avec le temps.
Autre particularité, il y a des véhicules qui arrivent en cours d'année et d'autres qui partent. Du coup, ceux qui arrivent n'ont pas de donnée avant leur présence dans le parc. Donc il ne faut pas compter les 0 sinon ça fait baisser la moyenne inutilement.
D'autre part, parfois, il y a des 0 car le véhicule n'a pas bougé et ça peut arriver plusieurs semaines de suite. Mais là il faut compter ces 0 puisque le véhicule est dans le parc mais ne roule pas.

Voilà, mon niveau ne me permet pas de trouver une formule adéquate. MErci d'avance pour votre aide

 

[JHA]

Bonjour à tous,

Il y a des anomalies dans ton fichier peux-tu les corriger?
J'ai remplacé les valeurs de -0.0000001 par "parc"

JHA

 

[Frank Bellaisch]

Bonjour JHA et merci.
oui effectivement, j'ai corrigé. Ce sont des valeurs à 0.
Voici le fichier. Du coup, est-ce qu'il est nécessaire d'avoir la 2eme partie du dénominateur ?
Si non, il semble que cela ne fonctionne pas car, il est possible d'avoir des valeurs nulles entre 2 valeurs non nulles. Je peux imaginer que je laisse ma voiture au parking pendant les vacances car je prends l'avion. Mais le fait de ne pas avoir roulé pendant 2 semaines doit entrer dans le calcule de la moyenne. C'est là la difficulté à mon sens.
Merci pour ton aide précieuse

 

[Frank Bellaisch]

J'ajoute que si on prend toute la plage jusqu'à la semaine 52, on n'aura également pas une bonne moyenne sauf si on attend la fin de l'année. En gros il faut que la plage soit allongée semaine après semaine.

 

[JHA]

Bonjour à tous,

Peux-tu expliquer:
1) les valeurs en colonnes "AY-AZ" de -20000 à -200000 par semaine, pourquoi moins?
2) comment tu différencies les valeurs 0 avec le format personnalisé "_-* # ##0_-;-* # ##0_-;_-* "-"??_-;_-@_-" qui te retourne un "-"
3) Dans la formule proposée, si tu ne rempli la cellule, elle n'est pas prise en compte dans la moyenne. Les cellules vides sont de couleur orange par une MFC.

Pour excel un zéro reste un zéro, qu'il soit pour une voiture qui reste sur le parc ou pour une voiture qui a disparue du parc.


Un peu plus d'explications ou de clarifications permettront peut-être de t'aider.

JHA

 

[Frank Bellaisch]

Re-bonjour
Oui bien sûr, je vais tenter de donner plus d'explications :
1) ces colonnes AY-AZ sont négatives car elles résultent de la différence de 2 autres colonnes (les relevés du compteur kilométrique faits chaque semaine) qui ne sont pas encore remplies. C'est donc un tableau qui se remplit chaque semaine. Si on était en avril, la fin du tableau serait vide et la colonne de la semaine suivante serait négative.
2) j'ai mis ce format pour ne pas alourdir de 0 qui sont moins lisibles. mais ça reste des 0. On a des 0 pour 2 raisons : soit le véhicule ne roule pas pendant x semaines donc la différence de compteur est nulle. Soit parce que le véhicule est arrivé en cours d'année et donc avant qu'il soit dans le parc, le compteur est nul aussi

3) le souci est bien là et c'est toute ma difficulté. Prenons la ligne 21 (véhicule 16), en colonne M, on a 0 (ou - mais c'est pareil). Ca veut dire que le véhicule n'a pas roulé. Mais sa moyenne sur les semaines 10, 11 et 12 est de 39 km/ semaine et non pas 59 (si on ne compte que 33 et 85 km). Donc la formule qui ne tient pas compte de ce 0, ne donne pas la vraie moyenne.

Pour faire simple : à partir du moment où un véhicule entre dans le parc (donc avec une valeur non nulle sur une semaine), toutes les semaines suivantes y compris celles à 0, doivent entrer dans le calcul.
Il reste la question du véhicule sortant (qui va se retrouver avec des semaines à 0 également) pour qu'on arrête de prendre en compte ces semaines puisque le véhicule est sorti du parc. (mais là, je peux très bien mettre une donnée négative).
Je ne sais pas si c'est plus clair. Merci encore

 

[JHA]

Bonjour à tous,

A essayer

=MOYENNE(DECALER($C6;;;;SIERREUR(EQUIV(VRAI;$C6:$BC6<0;0)-1;NO.SEMAINE.ISO(AUJOURDHUI()))))

la formule decaler recherche la colonne où la donnée est inférieure à zéro, si elle n'existe pas, on prend le n° de semaine de la date du jour.
Les zéros dans les colonnes comprises avant la donnée négative ou la semaine du jour sont pris en compte dans la formule.


JHA

 

[Frank Bellaisch]

Merci.
Alors, ca fonctionne bien sauf pour les véhicules qui sont entrés en cours d'année ou qui sont sortis en cours d'année. Exemple le véhicule 66 (les 2 premières colonnes ne doivent pas entrer dans le calcul de la moyenne) ou le véhicule 24 (les dernières colonnes après la derniers valeur non nulle ne doivent pas entrer dans le calcul)

 

[ALS35]

Bonjour à tous,

Ce serait plus simple si tu pouvais mettre 0 pour les véhicules qui n'ont pas roulés, ne rien mettre (vide) pour les véhicules non entrés ou sortis du parc, et pour les semaines à venir avec :
=MOYENNE.SI(G6:BG6;">=0")
parce que de toutes façons il faut bien pouvoir distinguer les deux cas.
Un début d'essai ci-joint
Cordialement

 

[Frank Bellaisch]

Effectivement, je vais tenter un truc dans ce genre pour différencier à la source
Encore merci pour votre aide à tous les 2
Bonne fin de journée

 

[JHA]

Bonjour à tous,

Un dernier essai, cette formule calcule la moyenne à partir de la première donnée supérieure à zéro et s'arrête soit juste avant la donnée négative ou la semaine du jour.

=MOYENNE(DECALER(INDIRECT(ADRESSE(LIGNE();3+PETITE.VALEUR(SI($C6:$BC6>0;COLONNE($C$5:$BC$5)-3);1)));;;;SIERREUR((EQUIV(VRAI;$C6:$BC6<0;0)-1)-PETITE.VALEUR(SI($C6:$BC6>0;COLONNE($C$5:$BC$5)-3);1);NO.SEMAINE.ISO(AUJOURDHUI())-PETITE.VALEUR(SI($C6:$BC6>0;COLONNE($C$5:$BC$5)-3);1))))

JHA

 

[Frank Bellaisch]

Bonjour
Ah oui cette formule fonctionne encore mieux mais elle prend encore en compte les données nulles quand le véhicule est sorti du parc. PAr exemple le véhicule 24 a été rendu en semaine 40. Sa moyenne devrait être 515 et la formule calcule 410 parce qu'elle va jusqu'en semaine 49.
Mais comme ça ne concerne pas non plus bcp de véhicules, je vais partir sur cette base et ne pas vous ennuyer plus. Un grand merci parce que cette petite demande n'est pas si simple finalement.

 

[ALS35]

Bonjour,
Et comment tu feras pour les véhicules qui sont toujours dans le parc mais qui n'auront pas roulé les premières ou dernières semaines de l'année ?
La solution est de distinguer les deux informations, sinon c'est de la divination.
Cordialement

 

[Frank Bellaisch]

Bonjour
oui, comme je disais, ce n'est pas simple. Peut-être une intervention divine....non humaine sur les cas les plus extrêmes.
Je vais réfléchir à tout cela pour essayer de distinguer les cas.
Quoiqu'il en soit, encore une fois merci de votre temps et de mettre à profit vos connaissances sur Excel sur ce forum.

 

[mapomme]

Bonjour à tous,

Quoiqu'il en soit, encore une fois merci de votre temps et de mettre à profit vos connaissances sur Excel sur ce forum.

Ne pas faire la distinction entre "absent" du parc et "n'a pas roulé" n'a rien à voir avec Excel mais avec la logique de ce qu'on veut savoir et dans quel but.
En général, ce type de données sert à éliminer les voitures qui roulent le moins (rarement à ajouter un véhicule mais cela se peut - il faut une intervention divine - ) ou bien à savoir par quel type de véhicule (essence, hybride, électrique, etc) on doit faire le remplacement d'un véhicule arrivé à terme.
Ne pas distinguer les véhicules hors parc (même temporairement par exemple pour grosse réparation) aboutit à des valeurs sans grande signification et ne peuvent pas justifier les modifications de parc vis à vis des utilisateurs qui en général sont très sensibles sur le sujet.
Il suffit de mettre un "x" pour les véhicules hors parc et on pourra aisément distinguer ceux qui ne roulent pas de ceux qui ne "sont pas là". La fonction Moyenne() devrait dans ce cas suffire puisqu'elle ne prend pas en compte les valeurs textuelles (ni les cellules vides).