XL 2013 Afficher la dernière valeur d'une catégorie donnée pour calculer des soldes intermédiaires

[Keyotione]

Bonjour à tous,

J'ai un tableau avec plusieurs centaines de transactions de plusieurs dizaines de produits différents (exemple court ci-après). Afin d'effectuer plusieurs calculs intermédiaires automatiques (soldes intermédiaires par exemple), j'ai besoin de sélectionner la dernière valeur précédant la cellule dans une ligne donnée et appartenant à la même catégorie. J'ai bien tenté plusieurs formules avec EQUIV, INDEX, RECHECHE, RECHERCHEV mais je ne trouve pas la formulation idéale.

Merci mille fois !!

Catégorie	Quantité	Prix	Solde intermédiaire
Y	55	12
X	1,5	54
Z	9	43
Y	100	1,35
Y	32	17
Z	5	38
X	120	1,7

 

[eriiic]

Bonjour,
en supposant que ce soit le prix que tu veux, en D2 :

=SI(NB.SI($A$2:A2;A2)=1;"";INDEX(C:C;GRANDE.VALEUR(($A$2:A2=A2)*LIGNE($A$2:A2);2)))

à valider en matriciel avec Shift.Ctrl.Entrée
eric

 

[Keyotione]

C'est exactement ça que je cherchais !
Merci !!!
Excellentes fêtes

 

[Keyotione]

Me revoilà,

Alors la formule fonctionne très bien pour associer des variables situées dans d'autres colonnes sauf lorsqu'il y a des colonnes comportant des cellules vides.

Autrement dit, y a-t-il un moyen de prendre en compte, pour une catégorie donnée en A, une valeur dans une autre colonne (ici D), qui soit dans la première cellule au-dessus non vide, et correspondant à la même catégorie (X, Y, ou Z), pour un calcul en E ? La formule d'eriiiic est super mais elle compte les cellules vides comme un 0.

J'espère avoir été assez clair.
Merci encore !

Catégorie	Quantité	Prix	Autre variable	A afficher
Y	55	12	0,15
X	1,5	54	5
Z	9	43	2
Y	100	1,35	0,6	0,15 (D2)
Y	32	17
Z	5	38
X	120	1,7
Y	90	13	0,5	0,6 (D6)
Z	8	40	3	2 (D4)
X	150	20	6	5 (D3)
X	160	19	2,5	6 (D11)
Y	86	12	0,7	0,5 (D9)

 

[eriiic]

Bonjour,

=SI(OU(NB.SI($A$2:A2;A2)=1;D2="");"";INDEX(D:D;GRANDE.VALEUR(($A$2:A2=A2)*LIGNE($A$2:A2)*($D$2:D2<>"");2)))

toujours en matricielle
eric

 

[Keyotione]

C'est super, ça m'aide énormément !
On y est presque : j'ai besoin d'intégrer les valeurs en E pour un calcul en D sans créer de boucle, est-ce possible ?
J'ai essayé de modifier INDEX(D: D;GRANDE.VALEUR(($A$2:A2=A2)*LIGNE($A$2:A2)*($D$2: D2<>"");2)) pour prendre en compte une matrice qui s'arrête à la ligne juste au-dessus de la cellule à remplir mais j'obtiens un message d'erreur de formule circulaire...

Encore merci !

 

[eriiic]

Bonjour,

pas compris...
Dépose un fichier avec des explications détaillées.
eric

 

[JHA]

Bonjour à tous,

Je pense qu'il faut mettre la formule proposée par "eriiiic" en colonne "E"

JHA

 

[Keyotione]

Bonjour,

Voici le fichier,
J'aurais besoin que la formule dérivée de celle d'Eric soit en I, en affichant la valeur en H si c'est la première fois que la catégorie en B apparaît, ou bien en appliquant le calcul suivant :

=(([@Quantité]*[@[Prix après frais]])+(Ky*Ix))/[@[Solde intermédiaire]]
Ky étant le dernier solde intermédiaire correspondant à la catégorie en B
Ix étant la dernière valeur en I directement dessus correspondant à la catégorie en B
En plus, la formule ne s'applique que pour le type de transaction 'Buy' en colonne A.

J'ai tenté cette formule grâce aux apports d'Eric mais elle est circulaire en I (j'imagine qu'elle peut être simplifiée mais c'est au-delà de mes limites) :
=SI(NB.SI($B$2:B2;B2)=1;SI(NB.SI($B$2:B2;B2)=1;[@[Prix après frais intermédiaire]];"");(([@Quantité]*[@[Prix après frais intermédiaire]])+(INDEX(K:K;GRANDE.VALEUR(($B$2:B2=B2)*LIGNE($B$2:B2);2))*(INDEX(I:I;GRANDE.VALEUR(($B$2:B2=B2)*LIGNE($B$2:B2)*($I$2:I2<>"");2)))))/[@[Solde intermédiaire]])

Par exemple :
- en I2 : H2 car il s'agit de la première occurrence de "X" en colonne B ;
- en I8 : rien afficher car il s'agit d'une transaction "Sell" ;
- en I12 : Ky=K8 et Ix=I6 car K8 est l'occurrence correspondant à "X" en K et I6 est la dernière occurrence non vide correspondant à "X" immédiatement au-dessus de I12 en colonne I.

J'espère avoir été plus clair cette fois

Encore merci !!!

 

[Keyotione]

Si quelqu'un a une idée... !

Merci d'avance

 

[eriiic]

J'en ai une : que tu fasses un retour sur la proposition de JHA

 

[Keyotione]

Bonjour,

Mon dernier post prenait en compte la proposition de JHA. La solution fonctionne mais je me suis rendu compte que ce n'est pas exactement ce qu'il me fallait (cf. mon fichier joint dans le dernier message) car j'aurais besoin que la formule soit associée à un calcul et j'obtiens un message d'erreur de formule circulaire lorsque je transpose la formule à l'endroit désiré dans ma feuille de calcul.

Merci !

Bonjour,

Voici le fichier,
J'aurais besoin que la formule dérivée de celle d'Eric soit en I, en affichant la valeur en H si c'est la première fois que la catégorie en B apparaît, ou bien en appliquant le calcul suivant :

=(([@Quantité]*[@[Prix après frais]])+(Ky*Ix))/[@[Solde intermédiaire]]
Ky étant le dernier solde intermédiaire correspondant à la catégorie en B
Ix étant la dernière valeur en I directement dessus correspondant à la catégorie en B
En plus, la formule ne s'applique que pour le type de transaction 'Buy' en colonne A.

J'ai tenté cette formule grâce aux apports d'Eric mais elle est circulaire en I (j'imagine qu'elle peut être simplifiée mais c'est au-delà de mes limites) :
=SI(NB.SI($B$2:B2;B2)=1;SI(NB.SI($B$2:B2;B2)=1;[@[Prix après frais intermédiaire]];"");(([@Quantité]*[@[Prix après frais intermédiaire]])+(INDEX(K:K;GRANDE.VALEUR(($B$2:B2=B2)*LIGNE($B$2:B2);2))*(INDEX(I:I;GRANDE.VALEUR(($B$2:B2=B2)*LIGNE($B$2:B2)*($I$2:I2<>"");2)))))/[@[Solde intermédiaire]])

Par exemple :
- en I2 : H2 car il s'agit de la première occurrence de "X" en colonne B ;
- en I8 : rien afficher car il s'agit d'une transaction "Sell" ;
- en I12 : Ky=K8 et Ix=I6 car K8 est l'occurrence correspondant à "X" en K et I6 est la dernière occurrence non vide correspondant à "X" immédiatement au-dessus de I12 en colonne I.

J'espère avoir été plus clair cette fois

Encore merci !!!

 

[eriiic]

Bonjour,

On aurait deviner tout ça ?
Si tu te retrouves avec des ref circulaires c'est que tu as oublié d'en adapter certaines non ?
S'il n'y en a pas dans la proposition de JHA, elles n'apparaissent pas ex-nihilo.
Là, perso, je n'ai pas trop le temps de lire en détail ton laïus, ni d'ouvrir ton fichier
eric

 

[eriiic]

Bonjour,

alors, tu as trouvé ? Tu en es où ?
eric

 

[Keyotione]

Bonjour,

Je suis passé à autre chose car je ne trouve pas la bonne solution.
Merci,