CMUP

Bonjour,

Je vais faire un deal avec vous.

Vous me fournissez la formule dans Excel qui permet de faire le calcul. Je parle ici d’une formule “unique” pour toute la colonne et non d’une formule qui change à chaque ligne. Si vous me fournissez cette formule dans Excel, je vous transpose le tout dans Power Query. Si c’est impossible à faire dans Excel, j’ai bien l’impression que ce sera impossible dans Power Query aussi.

Merci.

Sophie

Bonjour

Comme votre situation nécessite de calculer un élément en fonction du précédent, j’ai pensé à une fonction Power Query un peu complexe : List.Accumulate

Voici comment je procède :
– j’ajoute une colonne “Index” pour pouvoir trier dans le même ordre à la fin
– j’ajoute une colonne “Quantité_signée” pour mettre en négatif les ventes
– je regroupe par article (car il faut que ça fonctionne avec plusieurs références) je compte le nombre de lignes et je récupère “toutes les lignes” pour avoir une table avec l’ensemble des entrées et des sorties de chacun des articles
– j’ajoute un “Index2” à cette sous-table afin d’identifier chaque ligne
– je calcule avec un List.Accumulate la quantité en stock et le montant en stock.
voir plus bas les explications
– je ne conserve que les tables et les listes générées à l’étape précédente, je développe les tables et je trie suivant l’index initial. Les listes sont dupliquées sur chacune des lignes
– je calcul le CUMP en divisant le montant stocké par la quantité en me servant de l’index2 pour récupérer le bon élément de la liste. j’indique que le stock est nul s’il n’y a plus de produit. Votre formule génère un DIV/0 dans ce cas.

Voici la formule au coeur du calcul : c’est une liste de listes
chaque “sous-liste” comprenant 2 éléments : le stock en cours et son montant
le champ [Données] contient les tables avec les achats et les ventes de chacun des articles

List.Accumulate(
{1..[Nb_Lignes]-1},
{{[Données][Quantité_signée]{0},[Données][Quantité_signée]{0}*[Données][#”PA Un”]{0}}},
(state,current)=>
List.Combine (
{state,{{state{current-1}{0}+[Données][Quantité_signée]{current},
state{current-1}{1}+[Données][Quantité_signée]{current} *
(if [Données][Quantité_signée]{current}>0
then [Données][#”PA Un”]{current}
else state{current-1}{1}/state{current-1}{0})}}})))

{1..[Nb_Lignes]-1} crée une liste à partir de 1 pour aller chercher les infos dans chaque ligne de la table de l’article (power query commence à 0 et termine à Nb_Lignes -1)

{{[Données][Quantité_signée]{0},[Données][Quantité_signée]{0}*[Données][#”PA Un”]{0}}}
défini la première valeur de ma liste : c’est une liste avec la première quantité et le premier montant (quantité *prix)

(state,current)=>
List.Combine (
{state,…

à partir de la première liste de ma liste, j’agrège les sous-listes les unes derrière les autres

{{state{current-1}{0}+[Données][Quantité_signée]{current},

le premier élément de ma nouvelle sous-liste est la quantité en stock : la quantité précédente avec le “current-1″ + la quantité en cours (négative si vente)

et
state{current-1}{1}+[Données][Quantité_signée]{current} *
(if [Données][Quantité_signée]{current}>0
then [Données][#”PA Un”]{current}
else state{current-1}{1}/state{current-1}{0})}}

le second élément de ma sous-liste est le montant : le montant précédent + la quantité en cours multipliée par le prix en cours si achat ou le CMUP précédent si vente

J’obtiens ainsi une liste pour tous les achats et les ventes de l’article avec à chaque fois le stock et sa valeur résultant de l’entrée ou de la sortie de cet article.

J’ai dupliqué vos données 100 fois pour avoir 900 lignes, le temps de calcul est de l’ordre de 10 secondes sur mon PC qui n’est pas de première jeunesse !

J’espère que cela vous sera utile,

Stéphane