Somme "spéciale"

[Moulinois]

Bonjour !

Dans le fichier joint, j'ai une nomenclature à niveaux (tel produit de niveau 3 entre dans la fabrication de tel produit de niveau 2 etc).

Les prix de revient de chaque niveau sont décomposés dans les colonnes D à G.

Je voudrais qu'une macro puisse me calculer le prix de revient de chaque niveau en incluant celui de ses sous-niveaux, comme sur l'exemple.

Chaque niveau est la somme de tous ceux qui sont en-dessous jusqu'au prochain niveau supérieur ou égal et je dois dire que j'ai du mal à le conceptualiser 😀


Merci de m'aider.

 

[wilfried_42]

Re : Somme "spéciale"

Bonjour moulinois

le fichier joint ? sa taille doit etre au maximum 48,8Ko

@ te lire

 

[Moulinois]

Re : Somme "spéciale"

Wilfried,

J'ai joint le fichier joint. Désolé de l'impair.

A bientôt.

 

[wilfried_42]

Re : Somme "spéciale"

re:

ton fichier en retour avec 3 plages nommées et une formule matricielle

de meilleurs formulistes pourront mieux faire que moi, je suis le fil

 

[Moulinois]

Re : Somme "spéciale"

Re,

Merci, ça correspond au cahier des charges 🙂

Questions : Une macro (plus simple à lancer sur un fichier brut) peut-elle écrire les formules matricielles qui vont bien comme ça ?
(EDIT : Réponse : "FormulaArray")

A plus,
M.

 

[Moulinois]

Re : Somme "spéciale"

Re,

J'ai encore besoin d'aide 😀
Je ne comprends pas tout sur les plages. En ai-je vraiment besoin (la fomule sera illisible mais c'est plus simple pour ma macro) ?
Comment les virer 😕
(J'ai essayé de remplacer les noms dans la formule par ce à quoi ils font référence mais ça ne marche pas pour l'un d'entre eux...)


Pour l'instant j'ai ça :

Sub Macro1()

Dim D As Integer
Dim Max As Integer

    D = Range("A65536").End(xlUp).Row 'Calcul dernière ligne
    
    For i = 2 To D
        Range("A" & i) = Range("A" & i).Value 'Transforme le format texte en nombre pour calculer le max
    Next
    
    Max = Application.WorksheetFunction.Max(Range("A2:A33")) 'Calcul du max
       
    For i = 1 To Max 'Permet de "clore les groupes"
        Range("B" & D + i) = i
        Range("A" & D + i).Formula = "=RC[1]"
    Next
    
    Range("H2").FormulaArray = "=SUM(IF(ROW(test)=ROW(),plageS,IF(test>RC[-7],plageS,0)))" 'Formule matricielle ("[-7]" car je n'utilise plus la même colonne)
    Range("H2").AutoFill Range("H2:H" & D) 'Etendue de la formule matricielle
End Sub

A te lire,

 

[wilfried_42]

Re : Somme "spéciale"

re:

voici une macro

Sub macro1()
Dim i As Long, montant As Double, k As Integer, j As Long
For i = 2 To Range("A65535").End(xlUp).Row 
    montant = 0
    For k = 4 To 7: montant = montant + Cells(i, k): Next k
    For j = i + 1 To Range("A65536").End(xlUp).Row
         If Range("A" & j) <= Range("A" & i) Then Exit For
         For k = 4 To 7: montant = montant + Cells(j, k): Next k
    Next j
    Range("H" & i) = montant
Next i
End Sub

plus imple à utiliser, une fonction personalisée :

Public Function somme_plus(ligne As Long) As Double
    Application.Volatile
    Dim i As Long, montant As Double, k As Integer, j As Long
    i = ligne
    montant = 0
    For k = 4 To 7: montant = montant + Cells(i, k): Next k
    For j = i + 1 To Range("A65536").End(xlUp).Row
         If Range("A" & j) <= Range("A" & i) Then Exit For
         For k = 4 To 7: montant = montant + Cells(j, k): Next k
    Next j
    somme_plus = montant
End Function

pour utiliser la fonction : mette en H2 et tirer la formule vers le bas

=somme_plus(ligne())

version par fonction personalisée conseillée, si tu modifies une valeur dans le tableau, pas besoin de relancer la macro, tout se recalcule en automatique

voir fichier joint : colonne I par macro, et colonne j par fonction perso

 

[Moulinois]

Re : Somme "spéciale"

Re,

voici une macro

Parfait 🙂

plus imple à utiliser, une fonction personalisée :

Nop, pas plus simple dans mon cas.

version par fonction personalisée conseillée, si tu modifies une valeur dans le tableau, pas besoin de relancer la macro, tout se recalcule en automatique

Le tableau sort d'une extraction de l'ERP (c'est d'ailleurs pour cela qu'il est si moche 😀) donc n'a pas à être modifié à la main 😉


Merci beaucoup,
A plus.

 

[Moulinois]

Re : Somme "spéciale"

Re, une semaine plus tard,

Je suis très embêté.
J'ai un truc pas prévu qui me tombe sur le coin de la figure : quand un article est acheté, il n'y a que sa ligne à prendre en compte, les sous-niveaux ne sont là qu'à titre informatif mais on a des montants (à retraiter donc car ils sont foireux là) tout de même.


En clair, on devrait avoir ligne 562 le montant de la 563 (527,99) plus celui de son détail (30), soit 557,99.


Voilà le tout début de ma macro, soit le code de wilfried légèrement modifié :

Sub CalculPR2()

Dim i As Long, Montant As Double, k As Integer, j As Long, D As Long, V As Long, Cel As Range

With ActiveWindow
    .WindowState = xlMaximized
    .Zoom = 82
End With

D = Range("A65535").End(xlUp).Row

'Suppression/Insertion colonnes
    Columns("O:X").Delete
    Columns("K:K").Delete
    Columns("I:I").Delete
    Columns("F:G").Delete
    Columns("C:D").Delete
    Columns("A:A").Insert Shift:=xlToRight
    

    Range("A2").FormulaR1C1 = "=LEFT(TRIM(SUBSTITUTE(RC[1],""."","""")),2)"
    Range("A2").AutoFill Range("A2:A" & D)
    Range("A2:A" & D).HorizontalAlignment = xlCenter
    Range("A2:A" & D).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
For i = 2 To D
    Montant = 0
    For k = 6 To 9
     Montant = Montant + Cells(i, k) 'Ca, ça marche
    Next k
    For j = i + 1 To D
         If Range("A" & j) <= Range("A" & i) Then Exit For
         If Range("D" & j) = "A" Then Exit For '### ICI, ça marche pas ###
         For k = 6 To 9
          Montant = Montant + Cells(j, k)
         Next k
    Next j
    Range("J" & i) = Montant
    
    
    Columns("J").NumberFormat = "#,##0.00"
    Range("J1") = "Prix Revient"
    Range("J1").Font.Bold = True
    
    Range("A1").Select
    
Next i
End Sub

Je ne vois pas par quel bout prendre le problème.
Est-ce que ça vous inspire ? 🙁

A plus

 

[Moulinois]

Re : Somme "spéciale"

Je pense que le mieux est en fait de partir du bas pour que la ligne 562 (par exemple) puisse ne se baser sur la 563 qu'une fois qu'elle a été calculée.
562 ne se baserait pas sur les colonnes F:I mais sur le résultat juste calculé en J.


Qu'est-ce que vous en pensez ?

 

[wilfried_42]

Re : Somme "spéciale"

bonjour

j'ai regardé ton fichier et je n comprends pas grand chose

m'enfin tu testes la Colonne D alors que dans ton fichier c'est la colonne E

 

[Moulinois]

Re : Somme "spéciale"

bonjour

j'ai regardé ton fichier et je n comprends pas grand chose

m'enfin tu testes la Colonne D alors que dans ton fichier c'est la colonne E

Bonsoir,
Je teste D car je supprime C & D et que je rajoute A. E devient donc D, que je teste.

Mais J'avoue que ce n'est pas clair tout de suite 😀

Oublie les modifs de la macro, elles sont foireuses.

Que penses-tu de la faire tourner depuis le bas ?


A plus

 

[wilfried_42]

Re : Somme "spéciale"

re:

je ne pense pas, les niveaux seraient inversés et le calculs completement faux
ou bien le faire en remontant et calculer en descendant, je ne vois pas la difference avec maintenant

 

[Moulinois]

Re : Somme "spéciale"

re:

je ne pense pas, les niveaux seraient inversés et le calculs completement faux
ou bien le faire en remontant et calculer en descendant, je ne vois pas la difference avec maintenant

Bonjour !

Je ne comprends pas. Les niveaux (créés par la macro) seraient toujours les bons puisqu'ils ne se basent pas sur l'ordre de "déroulement".

Ensuite la macro, en cheminant depuis la fin, utiliserait pour le niveau 2 les bonnes valeurs des niveaux 3 d'en-dessous etc.

Dans l'idée que j'ai, les niveaux N devraient être sommés et stockés dans une variable (une par type de niveau) puis additionnés à la valeur du premier niveau strictement inférieur trouvé (puis vidée puis re-remplie avec les valeurs des autres niveau de même niveau du dessus). Mais bon, y'a peut-être bien mieux.


M'enfin déjà, as-tu compris le principe pour articles achetés ? 😀
(qu'on parle de la même chose, enfin qu'on cherche une solution au même problème)

@ +

 

[Moulinois]

Re : Somme "spéciale"

Re,

Je cogite et j'ai une autre idée qui m'a l'air plus simple (enfin qui entraîne moins de modifs) : supprimer les valeurs (non essentielles en fait) des colonnes G:I pour tous les sous-niveau d'un produit acheté.

On commencerait donc toujours par le haut.
Si on reprend l'exemple du BAC à copeaux, il faudrait détecter le A en D563 puis mettre 0 ou supprimer toutes les cellules jusqu'au prochain niveau inférieur, donc pour F564:I575.
Ensuite, on pourrait faire tourner le code actuel par-dessus 🙂

EDIT : J'ai fait quelque chose qui a l'air de tourner correctement :

    For i = 2 To D
        If Range("D" & i) = "A" Then
            For j = i + 1 To D
                If Range("A" & j) > Range("A" & i) Then
                    Range("F" & j & ":I" & j).Clear
                Else: Exit For
                End If
            Next
        End If
    Next

Je teste en ce moment même.

Dis-moi ce que tu en penses.

@+