Discussion :

[l.flipper]

Bonjour,
J'ai un tableau de données (issu d'un Query).
Je voudrais faire un somme.si sur ces données. (Somme des vendus par date)
Colonnes : Date - Vendus
Ce code me retourne 0 (je n'ai pas réussi non plus avec un somme.si dans excel)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
         For Each r In Range("T_Données").ListObject.ListRows
            Sheets("a").Cells(Lg, 4) = Application.WorksheetFunction.SumIf(r.Range(r.Parent.ListColumns("vendus").Index), r.Range(r.Parent.ListColumns("date").Index), Sheets("b").Cells(Lg, 1))
         Next r

Comment faire ?
Merci

[Malick]

Salut,

Je pense qu'un TCD devrait faire l'affaire sans lignes de code.

[l.flipper]

Je ne souhaite pas passer par un TCD, je fais des tests sur les données et je ne veux faire cette action que sur une partie des données qui répondent à certains critères, je fais autre chose selon les cas.

[Pierre Fauconnier]

Salut

Ta variable lg n'est pas incrémentée dans la boucle => tu écris toujours ton résultat dans la même cellule. A la fin de la boucle, tu as donc dans range(lg,4) la valeur du sumif calculé sur la dernière ligne du tableau.

Dans la mesure où tu passes sur toutes les lignes avec la même fonction, tu pourrais:

• soit placer la formule dans le "tableau" de résultat de la feuille A;
• soit utiliser un TCD comme le propose Malick, car malgré ce que tu dis, c'est exactement le même traitement sur toutes les lignes.

Du coup, il serait probablement intéressant de nous dire ce que tu veux réaliser en français (sans jargon Excel ou VBA), avec une copie d'écran dépersonnalisée du tableau de départ et de la feuille d'arrivée


En règle générale, SUMIF (SOMME.SI en Excel) est obsolète et remplacé dans les versions actuelles par SUMIFS (SOMME.SI.ENS en Excel) qui accepte 127 conditions. L'ordre des arguments n'étant pas identique entre les deux fonctions, il conviendrait selon moi d'utiliser la version actuelle de la fonction

[l.flipper]

Bonjour, j'incrémente lg dans mon code, j'ai juste mis la ligne avec le calcul.

Dans mon tableau, j'ai des lignes avec date, famille, lot, quantité vendue ou quantité fabriquée.
Date - Famille - Lot - Qté vendue - qté fabriquée
01/05 - 1 - 150 - 2
01/05 - 3 - 152 - 50
01/05 - 1 - 150 - - 250
02/05 - 3 - 153 - - 100
02/05 - 4 - 150 - 10

Ma clé est famille-lot.
Lorsque pour la même famille et le même lot, la quantité fabriquée est supérieure à la quantité vendue, je veux créer une fiche de stock avec les quantités vendues et/ou fabriquées par jour
01/05 - famille 1 - lot 150 - qté fabriquée 300 - qté vendue 200 - stock (calculé) 100
02/05 - famille 1 - lot 150 - qté vendue 50 - stock (calculé) 50

Lorsque pour la même famille et le même lot, la quantité fabriquée est inférieure à la quantité vendue, je veux mettre dans un onglet Erreur
Famille 1 - lot 150 - qté fabriquée 300 - qté vendue 400
Famille 1 - lot 152 - qté fabriquée 200 - qté vendue 201


Je ne fais rien si la quantité fabriquée correspond à la quantité vendue

Si besoin de captures d'écran, je pourrais le faire demain.

Merci

[Pierre Fauconnier]

Tu incrémentes lg dans ton code, mais pas dans la boucle que tu nous montres et que je reproduis ci-dessous. Donc, lg est toujours le même pour chaque ligne du tableau => c'est la valeur calculée pour la dernière ligne (le dernier r de la boucle) qui se trouvera dans Sheets("a").Cells(Lg, 4)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
         For Each r In Range("T_Données").ListObject.ListRows
            Sheets("a").Cells(Lg, 4) = Application.WorksheetFunction.SumIf(r.Range(r.Parent.ListColumns("vendus").Index), r.Range(r.Parent.ListColumns("date").Index), Sheets("b").Cells(Lg, 1))
         Next r

[l.flipper]

sumif me renvoie la valeur 0 à chaque ligne.
Lorsque je fais somme.si dans une cellule, j'ai 0 également comme résultat.

[Pierre Fauconnier]

A mon avis, tu as des valeurs qui sont en texte d'un côté et en numérique de l'autre. Vérifie déjà cela. Cela ne change rien au problème d'incrémentation de Lg...

Cela dit, j'ai une solution via Power Query sans VBA:

• On importe le tableau dans Power Query;
• On regroupe sur la date, la famille et le lot;
• On ajoute une colonne pour calculer la différence Fabriquée - Vendue;
• On crée deux références à cette requête, l'une pour filtrer les valeurs positives, l'autre filtrer les valeurs négatives.

Ici, j'ai mis les tableaux les uns en dessous des autres pour la copie d'écran, mais ce n'est bien sûr à ne pas faire et l'on préfèrera renvoyer les tableaux sur des feuilles disctinces.




Requête de calcul du stock:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    TypesAdaptés = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Famille", Int64.Type}, {"Lot", Int64.Type}, {"Qté vendue", Int64.Type}, {"Qté fabriquée", Int64.Type}}),
    LignesGroupées = Table.Group(TypesAdaptés, {"Date", "Famille", "Lot"}, {{"Vendu", each List.Sum([Qté vendue]), type nullable number}, {"Fabriqué", each List.Sum([Qté fabriquée]), type nullable number}}),
    StockCalculé = Table.AddColumn(LignesGroupées, "Stock", each List.Sum({[Fabriqué],-[Vendu]}))
in
    StockCalculé

[l.flipper]

Bonjour,
J'ai essayé mais j'ai encore des erreurs.

Il faut respecter la casse ? (j'avais lot au lieu de Lot et un message d'erreur).



Voici des captures d'écran

Mes données de base



Le query


Les données de la colonne Qté fabriquée


Le résultat



Merci pour votre aide

[Pierre Fauconnier]

Oui, Power Query est "case sensitive" et c'est "un peu" perturbant lorsque l'on vient du monde Excel et VBA. On s'y fait vite.

Autre différence avec Excel, Power Query ne mélange pas les types de données, et il est donc impossible d'additionner des valeurs dont certaines sont Null. On peut éviter cela soit avec des IF, soit en passant par List.Sum qui permet les Null et n'en tient tout simplement pas compte.

Il est préférable de renommer les étapes d'une requête et d'éviter les espaces. Ca alourdit la syntaxe car chaque étape reprend le nom de la précédente et les noms avec espace sont exprimés de façon verbeuse: Je préfère TypesAdaptés à #"Types Adaptés", perso. Dans le fichier que j'ai donné précédemment, j'ai renommé systématiquement les requêtes pour alléger le code M.


Dans ce que tu as montré, il n'y a pas de qtés fabriquées => il est malaisé de tester. Dans "mon" fichier, j'avais mis quelques qtés fabriquées pour pouvoir créer un jeu de test correct.


Tu peux visualiser tout le script d'une requête via l'outil"Editeur avancé" que tu trouves sur les onglets Accueil et Affichage. Tu peux ainsi voir que pour la première requête, la plus importante puisque les deux tableaux de résultat en découlent, que le code M est assez léger en fait, et reprend bien les étapes que j'ai développées dans la réponse précédente.

Code Power query :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    TypesAdaptés = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Famille", Int64.Type}, {"Lot", Int64.Type}, {"Qté vendue", Int64.Type}, {"Qté fabriquée", Int64.Type}}),
    LignesGroupées = Table.Group(TypesAdaptés, {"Date", "Famille", "Lot"}, {{"Vendu", each List.Sum([Qté vendue]), type nullable number}, {"Fabriqué", each List.Sum([Qté fabriquée]), type nullable number}}),
    StockCalculé = Table.AddColumn(LignesGroupées, "Stock", each List.Sum({[Fabriqué],-[Vendu]}))
in
    StockCalculé

A toi d'adapter et d'essayer sur tes propres données, c'est ainsi que l'on apprend le mieux

[l.flipper]

Dans ce que tu as montré, il n'y a pas de qtés fabriquées => il est malaisé de tester. Dans "mon" fichier, j'avais mis quelques qtés fabriquées pour pouvoir créer un jeu de test correct.

Autre différence avec Excel, Power Query ne mélange pas les types de données, et il est donc impossible d'additionner des valeurs dont certaines sont Null. On peut éviter cela soit avec des IF, soit en passant par List.Sum qui permet les Null et n'en tient tout simplement pas compte.

Il est préférable de renommer les étapes d'une requête et d'éviter les espaces. Ca alourdit la syntaxe car chaque étape reprend le nom de la précédente et les noms avec espace sont exprimés de façon verbeuse: Je préfère TypesAdaptés à #"Types Adaptés", perso. Dans le fichier que j'ai donné précédemment, j'ai renommé systématiquement les requêtes pour alléger le code M.

Les Null ne sont pas dans mes données de départ, peut-on les éviter à la source ?
Je vais chercher des infos sur l'utilisation des Query, c'est tout nouveau pour moi.
Merci

[Pierre Fauconnier]

Null veut dire "cellule sans info" pour Power Query, car Power Query ne connait pas le vide. Lorsque l'on importe une table dans Power Query, "il remplit toute la grille" et place la valeur Null pour les cellules vides.

Normalement, tu devrais t'en tirer avec ce que j'ai donné comme exemple et comme explications. Si tu coinces, donne le script de la requête pour que l'on puisse voir ce que tu as fait, car voir l'interface avec juste les noms des étapes ne nous aide pas beaucoup.

[l.flipper]

C'est bon, j'ai des résultats, j'avais des lignes où la colonne "Famille" n'était pas renseignée, d'où les erreurs depuis ce matin je suppose.


Par contre, la requête ne fait pas tout à fait ce que je veux. J'obtiens ceci


J'aimerais que le stock du 05/01 soit de 415, le 08/01 : 410 et de 0 le 11/01