Calculer une valeur par rapport au mois d'avant en fonction d'une reference

[fnony]

Bonjour a tous

Après recherche dans le forum sur des pb de similaire (recherche sur les message "conso")
je n'ai pas trouvé de réponse..

Je voudrais calculer ma consommation d'un mois sur l'autre par rapport a un kilométrage parcouru.
Mon extraction me donne le [dernier KM saisi], la [quantité l] et [la date]
Je sais calculer dans un TCD la [différence de KM entre le mois précedent] mais je ne sais pas calculer ma consommatin sur le même TCD a savoir
[somme de quantité L] / [différence de KM entre le mois précedent] *100 (pour avoir une conso aux 100 KM

J'essaie donc de faire sans TCD mais je n'arrive pas à faire ceci:
en 1) dans la colonne G
= pour une immatriculation de réference, par rapport au mois précedent, faire une soustraction du Km (Km mois M - Km mois M-1)
en 2) dans la colonne H
c'est facile de faire dans une autre colonne ma conso
quantité / Nb km parcouru (colonne G)

Avez vous une idée ? petit tableau en PJ

Merci de votre aide

Francois

 

[CISCO]

Re : Calculer une valeur par rapport au mois d'avant en fonction d'une reference

Bonjour

Tu dois pouvoir faire avec :

B71-INDEX(B$2:B70;EQUIV(A71&MOIS(C71)-1;A$2:A$70&MOIS(C$2:C70);0))

en matriciel (ctrl+maj+entrer)

Je regarde si il ni a pas une possibilité sans calcul matriciel

@ plus

 

[CISCO]

Re : Calculer une valeur par rapport au mois d'avant en fonction d'une reference

Rebonjour

Une autre posssibilité, sans calcul matriciel, mais qui ne fonctionne que si les relevés sont datés du 1er du mois.

B71-INDEX(B$2:B70;EQUIV(A71;DECALER(A$1;EQUIV(DATE(ANNEE(C71);MOIS(C71)-1;JOUR(C71));C$2:C70;0);;LIGNE()-EQUIV(DATE(ANNEE(C71);MOIS(C71)-1;JOUR(C71));C$2:C70;0));0))

@ plus

 

[fnony]

Re : Calculer une valeur par rapport au mois d'avant en fonction d'une reference

Bonjour Cisco

Merci beaucoup pour ta formule, elle marche bien pour le mois de février, mais pas pour le mois de mars (et donc pour les mois d'après)
je pense que cela viens du tableau de recherche qui est "figé" pour le mois de février?¨Par contre la formuel fonctionne pour la première valeur du nouveau mois ??? cellule G138 (en PJ)

- dans une formule comme celle ci est il possible de dire que si je n'ai pas de correspondance d'immat (ex : nouveau véhicule) alors la cellule =0 ? (si, b71 n'existe pas dans mon mois précent alors 0; sinon B71-INDEX(B$2:B70;EQUIV(A71&MOIS(C71)-1;A$2:A$70&MOIS(C$2:C$70);0))


Merci de ton aide

 

[fnony]

Re : Calculer une valeur par rapport au mois d'avant en fonction d'une reference

Bonjour
Cisco

Nos messages ont du se croiser ....
ta deuxième formule marche bien aussi mais j'ai toujours le même souci de réference au mois M-1. . Pour les relevés, il seront datés du 1er du mois car par la suiote pour les TCD, je ferais des groupe sur le mois entier.

@ plus

 

[CISCO]

Re : Calculer une valeur par rapport au mois d'avant en fonction d'une reference

Bonjour

Il faut enlever le $ devant le 70 dans ma première formule, dans C$70. Je l'avais fait dans mon fichier, mais pas sur mon post...

Pour ce qui est de la seconde formule, je regarde tout de suite.

@ plus

 

[fnony]

Re : Calculer une valeur par rapport au mois d'avant en fonction d'une reference

Re bonjour

J'ai bien fait la manip et enlever le $ dans la formule ce qui donne : B71-INDEX(B$2:B70;EQUIV(A71&MOIS(C71)-1;A$2:A$70&MOIS(C$2:C70);0))
j'ai le même souci pour le mois de mars avec des valeurs #N/A ?

?
merci

@ plus

 

[CISCO]

Re : Calculer une valeur par rapport au mois d'avant en fonction d'une reference

Rebonjour

Bon, tout compte fait, cela donne, en éliminant aussi les messages #NA

en matriciel

=SI(ESTNA(EQUIV(A71&MOIS(C71)-1;A$2:A70&MOIS(C$2:C70);0));"";B71-INDEX(B$2:B70;EQUIV(A71&MOIS(C71)-1;A$2:A70&MOIS(C$2:C70);0)))

et pas en matriciel

SI(ESTNA(EQUIV(A71;DECALER(A$1;EQUIV(DATE(ANNEE(C71);MOIS(C71)-1;JOUR(C71));C$2:C70;0)-1;;LIGNE()-EQUIV(DATE(ANNEE(C71);MOIS(C71)-1;JOUR(C71));C$2:C70;0));0));"";B71-INDEX(B$1:B70;EQUIV(DATE(ANNEE(C71);MOIS(C71)-1;JOUR(C71));C$2:C70;0)-1+EQUIV(A71;DECALER(A$1;EQUIV(DATE(ANNEE(C71);MOIS(C71)-1;JOUR(C71));C$2:C70;0)-1;;LIGNE()-EQUIV(DATE(ANNEE(C71);MOIS(C71)-1;JOUR(C71));C$2:C70;0));0)))

Comme dans ce dernier cas, cela fait une formule à rallonge, avec des termes qui se répètent, on peut utiliser des noms, par exemple

SI(ESTNA(EQUIV(A71;plageA;0));"";B71-INDEX(B$1:B70;EQUIV(dateprécédente;C$2:C70;0)-1+EQUIV(A71;plageA;0)))

Cf. fichier en pièce jointe

@ plus

P.S : Dans mon post précédent, ma correction / $70 ne permet que d'obtenir les résultats pour mars, avril..., pas d'enlever les #NA.

 

[CISCO]

Re : Calculer une valeur par rapport au mois d'avant en fonction d'une reference

Rebonjour

Et une autre formule non matricielle, un peu plus courte, en ligne 71

SI(ESTNA(EQUIV(A71;DECALER(A70;;;EQUIV(DATE(ANNEE(C71);MOIS(C71)-1;JOUR(C71));C$2:C70;0)-LIGNE());0));"";B71-INDEX(B$1:B70;EQUIV(DATE(ANNEE(C71);MOIS(C71)-1;JOUR(C71));C$2:C70;0)-1+EQUIV(A71;DECALER(A70;;;EQUIV(DATE(ANNEE(C71);MOIS(C71)-1;JOUR(C71));C$2:C70;0)-LIGNE());0)))

Bien sûr, là aussi on peut donner des noms pour la rendre plus courte encore et aussi plus compréhensible.

@ plus

 

[fnony]

Re : Calculer une valeur par rapport au mois d'avant en fonction d'une reference

Re
et Bonsoir
Une réunion plus tard et quelques ennuis plus tard..
Bravo.... tout simplement génial cette maitrise des formules
Un grand merci de ton aide, je vais opté pour la gestion par plage nommée....

Pour mon info: ce genre de formule est elle aplicable (avec les modif nécessaires) à "l'envers"

C'est à dire pour calculer des données d'une période m à une période m+1?? par exemple pour pouvoir calculer ma [quantité mois]-[quantité mois+1] (avec toujours mon Immat come réference ??

merci encore
@plus

francois

 

[CISCO]

Re : Calculer une valeur par rapport au mois d'avant en fonction d'une reference

Bonsoir

C'est même plus simple : Essayes avec, en ligne 71.

SI(ESTNUM(EQUIV(A71;A72:A$205;0));B71-INDEX(B71:B205;EQUIV(A71;A72:A$205;0)+1;0);"")

, à adapter en fonction de ce que tu veux exactement.

Je peux modifier la formule pour que les plages A$1;A$205 et B$1:B$205 s'adaptent automatiquement à la longueur réelle de ton fichier.

@ plus

 

[fnony]

Re : Calculer une valeur par rapport au mois d'avant en fonction d'une reference

Bonjour Cisco

Merci pour cette nouvelle formule. Concernant la modification proposée, sauf a ne pas avoir compris ce aue tu proposes, Je ne connais pas la longueur qu'aura mon fichier, il s'agit d'extraction mensuelle et le nombre d'Immat peut varier mmensuellement, j'ai une idée de longueur (environ 900 à 1000 lignes par an) ...

J'ai regardé les plages de cellule que tu as faites, je dois reconnaître que je ne comprends pas ce que les formule veulent dire..il va falloir que j'épluche ça aevc les neurones restants....
bon c'est vendredi et ça marche ...

Merci
bon week end
Francois

 

[fnony]

Re : Calculer une valeur par rapport au mois d'avant en fonction d'une reference

Bonjour Cisco

En espérant que tu puisse m'aider a nouveau.

Je reviens vers toi car après recherche sur le forum je n'arrive pas a formuler ce que je veux.
Le fichier et tes formules ont super bien marchées mais la donne a changé.. et je n'arrive pas a reproduire ce que tu avait fait ...

Toujours la même problématique, calculer la différence par rapport a une référence mais par rapport a la dernière date et non par rapport au mois d'avant.. J'ai cherché avec Max et Min par rapport a la date, avec rechercheV, en combinant ... sans succès..
J'ai bien fait un tri par date puis immat pour calculer C3-C2 .. mais c'est nul ..

Mon tableau doit il être trié dans un ordre particulier?
J'ai mis un tableau test en PJ avec les resultats voulu.

Saurais tu regarder mon fichier et me donner tes lumières? Merci par avance de ton aide .. et / ou me rediriger .

Merci encore

François

 

[CISCO]

Re : Calculer une valeur par rapport au mois d'avant en fonction d'une reference

Bonjour fnony

Je vais essayer de te faire ça, mais il faut que je recomprenne la problématique, la méthode utilisée...

@ plus

 

[CISCO]

Bonsoir

Dans mon post #8, j'ai remplacé les A$70 par des A70 (En ligne 71, on fait une recherche dans la plage au dessus, finissant en ligne 70. En ligne 80 par exemple, on fait une recherche dans la plage finissant en ligne 79. Il ne faut donc pas mettre de $ devant le 70 de la plage A$2:A70 en ligne 71).

Ceci dit, Cf. la pièce jointe avec, en colonne F, une formule matricielle qui devrait répondre à ton nouveau besoin.

@ plus