XL 2013 calcul unite precedent et unite cumulative

[xoxopeter]

Bonjour,

Depuis ce matin j'essaie de mettre en place une formule qui me permettrait de réaliser un suivi exact de mon stock en affichant le nombre d’unité précèdent et le nombre cumulative, mais je n'arrive pas à l'automatiser

L'objectif est donc de rentrer ligne par ligne un ensemble d'informations (la date, le type de transaction, le nom de la variété et la quantité vendu) puis de pouvoir faire une sorte d'analyse du tableau (B210 pour mon exemple en pièce jointe) puis de détecter automatiquement le nombre vendu par variété en affichant pour chaque variété le nombre d'unité précédente et le nombre cumulative en prenant en compte si c'est un achat ou une vente (cellule Bx). Il faut donc pouvoir suivre l'ensemble des actions par variété afin de pouvoir un suivi meme si entre temps j'achète de nouvelles variétés

Je ne sais pas si j'ai réussi à me faire comprendre mais je l'espère,

Dans le fichier excel j'ai mis un exemple concret du résultat que j'essaie d'avoir (E18:E26) mais je n'arrive pas l'automatiser dynamiquement (E2:E10)

Je suis actuellement sur office 2013 donc je ne dispose pas forcément de toutes les fonctions possible avec office 365 donc si vous avez une idée ou la formule qui pourrait répondre à mon besoin, cela serait super,

Merci d'avance

 

[st007]

Bonjour, des pistes
Avec des valeurs négatives pour tes ventes, le total par variété donnerait pour la gala

=SOMMEPROD((C2:C10=$C$2)*(D2:D10))

Un petit croisé dynamique avec une colonne supplémentaire

 

[Hasco]

Bonjour le fil,

Pour la valeur précédente :

=SIERREUR(INDEX($F$1:$F1;AGREGAT(14;6;LIGNE($C$1:$C1)/($C$1:$C1=$C2);1));0)

Pour la valeur cumulative :

=SOMME.SI.ENS($D$2:$D$10;$A$2:$A$10;"<="& $A2;$B$2:$B$10;"Achat";$C$2:$C$10;$C2)-SOMME.SI.ENS($D$2:$D$10;$A$2:$A$10;"<="& $A2;$B$2:$B$10;"Vente";$C$2:$C$10;$C2)

Toutes deux disponibles sous excel 2013.

Cordialement

 

[xoxopeter]

Bonjour St007 et Hasco et merci pour vos retours,

je viens de faire un test et j'ai une préférence pour la méthode de Hasco qui semble plus adapté à ce que je souhaite faire, mais pour lequel je n'arrive pas à adapter la formule^^

@Hasco, je n'arrive pas à comprendre pourquoi tu te bases sur la colonne des dates ? en parallèle j'ai fait évoluer l'excel car les achats/ventes peuvent être réalisés pour différents dépôts donc j'ai essayé de ré adapter ta formule avec plutôt la colonne dépôt que la colonne date mais sans succès,

En pièce jointe j'ai mis le fichier à jour,

Si quelqu'un a une idée, je suis preneur,

Cordialement,

 

[Hasco]

Bonjour,

La fonction s'appuyait sur les dates, car vous vouliez une somme cumulative. Donc il fallait que les données sélectionnées par la formule aient une date inférieure ou égale à la date de la ligne en cours.

Avec la fonction Somme.Si.Ens vous pouvez rajouter des critères :

=SOMME.SI.ENS($E$2:$E$10;$A$2:$A$10;"<=" &$A2; $B$2:$B$10;"="& $B2;$C$2:$C$10;"Achat";$D$2:$D$10;$D2)-SOMME.SI.ENS($E$2:$E$10;$A$2:$A$10;"<=" &$A2;$B$2:$B$10;"="& $B2;$C$2:$C$10;"Vente";$D$2:$D$10;$D2)

J'ai remis le critère de date pour la raison invoquée plus haut.

Dans votre nouveau fichier, je ne suis pas d'accord avec votre énoncé et le résultat que vous semblez en attendre :

Pour la gala, ligne 22 vous mettez 4 puis 0 pour 23. Le 4 est une quantité 'Achat' et vous parlez dans l'énoncé des quantités 'Vendues'
C'est pourquoi dans la première version, je n'ai pas distingué les 'Achat' des 'Vendu' dans ce calcul.

Cordialement

 

[xoxopeter]

Très bien je comprends mieux la raison de la date et cette raison est très claire, merci

Concernant la ligne 22 du gala a 4 et la ligne 23 ou il est a 0 c'est parce qu'à la ligne 22 la vente se fait pour le dépôt 1 et il y avait eu un achat pour le dépôt 1 en ligne 18. Alors que la ligne 23 concerne un achat mais pour le dépôt 2 et pour celui-ci c'était le 1er achat donc avant il y avait bien 0 unité dans ce dépôt-la,

Je viens de faire une nouvelle version avec la mise à jour de la formule et cela fonctionne bien pour l'unité cumulative (colonne G) maintenant.

Il me reste plus qu'à trouver la nouvelle formule pour l'unité précédente (colonne F) afin que cela prenne en également compte la colonne B du dépôt afin que le 1er achat dans un second dépôt soit bien à 0 comme l'exemple en cellule F23,

Je ne suis pas sur que avec la fonction actuellement utilisé (SIERREUR) ce soit possible d'utiliser une plage de critère donc je vais voir comment faire,

Si un de vous à une idée, je reste à l'écoute

Merci encore pour votre aide,

Cordialement,

 

[Hasco]

Je ne suis pas sur que avec la fonction actuellement utilisé (SIERREUR) ce soit possible d'utiliser une plage de critère donc je vais voir comment faire,

La fonction SIERREUR n'a rien à voir là-dedans. Elle ne fait que masquer les retours en erreur de la fonction agregat. Je ne l'ai mise qu' après avoir fait les tests.

 

[Hasco]

Re,

Voilà qui est corrigé.

=SIERREUR(@INDEX($E$1:$E1;AGREGAT(14;6;LIGNE($D$1:$D1)/($D$1:$D1=$D2)/($B$1:$B1=$B2);1));0)

Cordialement

 

[xoxopeter]

Bonjour Hasco,

Je viens de tester la nouvelle formule de la colonne F (unité précédente) et j'ai remarqué que si je tire la formule vers les cellules du bas ou si simplement je clique sur la formule puis je tape entrée, cela met la valeur ou toutes les valeurs à 0 si je tire la formule au lieu de laisser la valeur réel --> pour le fichier excel que vous avez envoyé

Par contre si on utilise la formule de votre dernier échange:
=SIERREUR(INDEX($E$1:$E1;AGREGAT(14;6;LIGNE($D$1:$D1)/($D$1:$D1=$D2)/($B$1:$B1=$B2);1));0)

Cela fonctionne parfaitement si on tire la formule ou si on met à jour la cellule de la formule,

En parallèle je suis allez voir le fonctionnement de la fonction AGREGAT afin de parfaitement comprendre la formule:

AGREGAT (AGREGAT, fonction) - Support Microsoft

La fonction AGREGAT peut appliquer diverses fonctions d’agrégation à une liste ou à une base de données en proposant l’option d’ignorer les lignes masquées et les valeurs d’erreur.

support.microsoft.com

Je me suis ensuite amusé à modifier certaines cellules afin de voir comme la tableau se comportait de manière dynamique et j'ai remarqué qu'il y avait un souci en cellule F5 du nouveau fichier excel que je viens d'envoyer. La valeur de cette cellule était affiché à 2 alors qu'elle devrait être affiché à 6 étant donné qu'il y a eu 2 achats pour le depot 1 (4+2).

Dans mon cas on utilise la valeur 14 dans la fonction pour prendre la plus grande valeur pour la colonne unité acheté et dans ce cas précis, je pense que c'est ce qui fait que cela ne fonctionne pas correctement,

Afin de vérifier ma théorie, j'ai tenté de me baser sur la colonne unité cumulative au lieu de la colonne quantité acheter/vendu pour la formule de la colonne F (unité prècedente) et ça à l'air de bien fonctionner même si je pensais que la valeur 14 de la formule ferait que cela ne fonctionnerait pas étant donné que c'est censé prendre la GRANDE.VALEUR

Me reste plus qu'à adapter cette nouvelle formule sur mon autre excel, mais je pense que cela devrait fonctionner correctement maintenant,

Je vous remercie pour votre aide,

Cordialement,

 

[xoxopeter]

Bonjour,

J'ai crée une nouvelle feuille 3 avec les même formules/valeurs/données que la feuille 2, mais impossible de faire fonctionner la colonne "previous unit" de la feuille 3.

La seul chose qui change est la lettre des colonnes et j'ai adapté cela dans la feuille 3 mais cela ne change rien, les valeurs reste à 0 et je n'arrive pas du tout à comprendre pourquoi.

Si quelqu'un a une idée, je suis preneur,

Cordialement

 

[Hasco]

Bonjour,

Je dois souligner ici la clarté et la précision de vos messages, qui permettent d'aller droit au but. Alors merci à vous.

Index renvoie ici la Nième valeur du tableau renvoyé par Agregat.
Le Troisième agrument d'agregat est un tableau sur lequel on veut appliqué grande.valeur.

LIGNE($D$1:$D1)/LIGNE($D$1:$D1/($B$1:$B1=$B2)

LIGNE($D$1:$D1) = va renvoyer 1 pour la ligne 1, 2 pour la deuxième etc....
($D$1:$D1=$D2) va renvoyer Vrai ou Faux suivant que la ligne répond ou non au critère
($B$1:$B1=$B2);1) idem pour le critère de B

nous aurons pour chaque ligne de tableau
N°ligne/Vrai/Vrai si la ligne répond aux critères
ce qui est égal à 1 (ou N)/1/1 ce qui donne N
Pour une ligne fausse, nous aurons 3 possibilité
N°ligne/FAUX/VRAI soit N/0/1
N°Ligne/VRAI/FAUX soit N/1/0
N°Ligne/FAUX/FAUX soit N/0/0
ce qui donne pour chaque cas faux une erreur #DIV/0!
Comme nous avons choisit 6 (ignorer les valeur d'erreur) en deuxième critère d'agregat, ces valeurs seront ignorée et agregat renverra le plus grand numéro du tableau calculé.

Pour la ligne 4 agregat va retourner 2 e t la deuxième valeur du tableau $G$1:$G2 est 4.

P.S. je viens de voir que vous aviez posté pendant que je vous répondais.
je regarde votre tableau.

Cordialement

 

[Hasco]

Re,

Votre tableau n'est plus en ligne 1 je vous laisse réfléchir à la solution et si vous n'y arrivez pas revenez.

Pensez que pour la fonction index, agregat doit renvoyer une valeur croissante de 1 à N, je vois au moins deux solutions possibles.

Cordialement

 

[xoxopeter]

Re Hasco,

Merci pour vos explications, cela ne fait pas trop longtemps que j'ai débuté avec Excel donc les détails que vous apportez m'éclaire assez bien sur le fonctionnement de cette formule agrégat.

J'ai essayer différentes formules dans l'après midi mais rien n'y fait. Je n'arrives pas à avoir le résultat que je souhaite.

Est-ce que vous pourriez me donner un peu plus d'informations/indices sur les 2 solutions que vous avez imaginé s'il vous plait,

Cordialement,

 

[Hasco]

Bonjour,

Essayez quand même ceci :

=SIERREUR(INDEX($L$5:$L5;AGREGAT(14;6;LIGNE($1:1)/($G$5:$G5=$G6)/($C$5:$C5=$C6);1));0)

cordialement

 

[xoxopeter]

Bonjour Hasco,

Je viens de tester la formule et elle solutionne bien mon problème, je l'ai à peine adapté (j'ai juste mis LIGNE($1:5) mais c'est bon.

J'ajoute en pièce jointe le formulaire pour complété avec les dernières formules à jour,

Encore merci pour votre aide,

Cordialement