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

CISCO

XLDnaute Barbatruc

• 31 Mars 2011

• #2

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

Bonjour

Tu dois pouvoir faire avec :

Code:

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

 

Dernière édition: 31 Mars 2011

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 31 Mars 2011

• #3

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.

Code:

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

 

Vote positif 0 Vote négatif

fnony

XLDnaute Occasionnel

• 31 Mars 2011

• #4

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

 

Pièces jointes

• calcul conso m-1.xlsx

  39.7 KB · Affichages: 105
• calcul conso m-1.xlsx

  39.7 KB · Affichages: 111
• calcul conso m-1.xlsx

  39.7 KB · Affichages: 111

Vote positif 0 Vote négatif

fnony

XLDnaute Occasionnel

• 31 Mars 2011

• #5

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

 

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 31 Mars 2011

• #6

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

 

Dernière édition: 22 Novembre 2014

Vote positif 0 Vote négatif

fnony

XLDnaute Occasionnel

• 31 Mars 2011

• #7

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

 

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 31 Mars 2011

• #8

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

Code:

=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

Code:

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

Code:

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.

 

Pièces jointes

• calcul conso.xlsx

  45 KB · Affichages: 66

Dernière édition: 22 Novembre 2014

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 31 Mars 2011

• #9

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

Code:

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

 

Vote positif 0 Vote négatif

fnony

XLDnaute Occasionnel

• 31 Mars 2011

• #10

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

 

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 31 Mars 2011

• #11

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.

Code:

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

 

Dernière édition: 31 Mars 2011

Vote positif 0 Vote négatif

fnony

XLDnaute Occasionnel

• 1 Avril 2011

• #12

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

 

Vote positif 0 Vote négatif

fnony

XLDnaute Occasionnel

• 20 Novembre 2014

• #13

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

 

Pièces jointes

• testkm.xlsx

  76.1 KB · Affichages: 61
• testkm.xlsx

  76.1 KB · Affichages: 56
• testkm.xlsx

  76.1 KB · Affichages: 59

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 21 Novembre 2014

• #14

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

 

Vote positif 0 Vote négatif

CISCO

XLDnaute Barbatruc

• 22 Novembre 2014

• #15

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

 

Pièces jointes

• testkm.xlsx

  91.8 KB · Affichages: 48
• testkm.xlsx

  91.8 KB · Affichages: 57
• testkm.xlsx

  91.8 KB · Affichages: 59

Vote positif 0 Vote négatif