Excel -Matrice triple entrée

Bonjour,

je cherche à traiter un tableau classique comportant une liste de charges, et à réaliser un bilan global dans une matrice comportant des postes en lignes et colonnes (cf exemple joint, tableau (4), à partir des données brutes) ).

certaines lignes sont facilement affectables via SOMMEPROD (tableau (1)). Toutefois, certaines lignes du tableau des données brutes doivent être réparties sur plusieurs lignes de la matrice de synthèse (calculs de la matrice (2a) et) ou plusieurs colonnes (matrice 2b). Les clés de répartition à utiliser sont définies.

Je n'arrive pas à créer les matrices intermédiaires (2af) et (2bf) sans passer par des sous-tableaux intermédiaires... qui dans cet exemple, ne sont pas un réel problème, mais qui le seront dans mon fichier de travail réel (qui est beaucoup plus conséquent!!)

Auriez vous une solution pour cela?

Merci par avance pour votre aide, j'avoue bloquer un peu..

bonjour

tu nous parles de solution Excel, il vaudrait mieux nous expliquer le problème concret, genre "je cherche à affecter des coûts à des gammes de produits"

j'ai 16 gammes, et 3 sortes de coûts, et des clés de répartition...

oui je suis conscient que ce n'est pas très clair...

Donc, j'ai mes données brutes qui sont des coûts avec des caractéristiques (poste & flux)

Je dois synthétiser ces données dans une matrice double entrée (poste en ligne, flux en colonne)

certains des coûts doivent être affectés sur plusieurs lignes ou plusieurs colonnes (ils concernent donc plusieurs postes ou plusieurs flux). Les clés de répartition entre lignes et colonnes sont déjà définis.

Pour les autres (simple poste et simple flux), je les affecte facilement avec un SOMMEPROD.

les coûts "complexes" sont "pré-traités" dans la méthode que j'ai fourni en PJ. mais du coup, cela me donne de nombreux tableaux intermédiaires que j'aimerais ne pas avoir à réaliser..

Donc je cherche une méthode pour passer plus rapidement du tableau "données brutes " au tableau (4) (matrice finale)!

c'est plus clair ou toujours pas?

Merci pour l'aide!

Bonjour,

Erreurs dans ton tableau de base, lignes 19 et 20 ?

Le total fait toujours 661 mais j'ai un petit écart avec ton résultat, quelle que soit la solution que j'adopte pour les lignes 19 et 20...

Proposition à venir, dès que tu auras levé cette indécision.

Cordialement.

Bonjour,

Oui effectivement, j'avais simplifié les données d'entrée, mais seulement en partie.. ci-joint le tableau modifié, j'ai supprimé les références aux clés de répartition fausses!

Merci par avance,

Ok! Bon ! Ma proposition en deux étapes :

1) Extension de 6 colonnes sur ton tableau (de H à M donc) : 2 colonnes (une par flux) par poste : on va y calculer la part de montant qui revient à chaque poste-flux.

Aménagement des formats de cellules de colonnes F et G : "P"0;; pour F et "F"0;; pour G qui permet de porter des nombres (1, 2, 3) dans ces colonnes, le P ou le F relevant du format.

Aménagement aussi de la zone d'extension : Standard;; pour inhiber les 0.

Formule en H7 :

=SI($D7="divers";DECALER($Q$9;ENT(COLONNE(B:B)/2);$F7);($D7=H$5))*SI($E7="divers";DECALER($Q$16;$G7;MOD(COLONNE();2)+1);($E7=H$6))

La formule se recopie sur toute la zone d'extension.

(La ligne 6 contient OM ou CS, la ligne 5 Prévention ou Collecte ou Traitement).

J'ai ramené le petit tableau de calcul par poste-flux en J32:K34...

Formule en J32 :

=SOMMEPROD($C$7:$C$26*DECALER($G$7:$G$26;;EQUIV($I32;$H$5:$M$5;0)+EQUIV(J$31;$H$6:$I$6;0)-1))

Elle se recopie sur J32:K34.

Cordialement.

Bonjour,

Merci beaucoup pour cette réponse, qui effectivement marche bien sur l'exemple.

Toutefois, j'ai un petit souci.. comme je le disais, ce fichier reste un exemple. j'ai en fait dans mes véritables données non pas 2 flux et 3 postes mais 23 postes et 12 flux... ce qui complexifie grandement (et alourdit également!) chacune des feuilles.

C'est pour cela que j'avais tenté dans mon exemple de dissocier les différentes entités en fonction de leur gradient de difficulté à calculer..

Ne pourrait on pas trouver une formule permettant de calculer directement les valeurs dans votre exemple J32:k35?

Merci par avance,

Bien cordialement

Re-bonjour,

J'ai testé une nouvelle méthode permettant de supprimer une des matrices intermédiaires (le tableur de calcul).

Les formules utilisées sont les suivantes (pour les tableaux b50:e53 et I50:k53 :

=RECHERCHEH($B$43;clesp;EQUIV($B51;$Q$9:$Q$12;0))*C$43+RECHERCHEH($B$44;clesp;EQUIV($B51;$Q$9:$Q$12;0))*C$44+RECHERCHEH($B$45;clesp;EQUIV($B51;$Q$9:$Q$12;0))*C$45

et

=RECHERCHEV($J$42;clesf;EQUIV(J$50;$Q$16:$S$16;0))*$J43+RECHERCHEV($K$42;clesf;EQUIV(J$50;$Q$16:$S$16;0))*$K43+RECHERCHEV($L$42;clesf;EQUIV(J$50;$Q$16:$S$16;0))*$L43

le principe étant d'accumuler les recherchev ou rechercheh en pour sommer les différentes valeurs. Mais existerait il une manière plus simple de l'écrire, ce qui permettrait d'augmenter les clés de répartition sans trop compliquer la formule?

Merci de vos retours,

Bonjour,

Je ne sais pas ! Je n'ai pas vu de moyen plus simple de calcul direct avec des formules classiques.

En principe, ce système est applicable quand tu augmentes le nombre de poste et de flux, mais évidemment, cela peut faire grossir considérablement le tableau...

Pour simplifier alors, pour ma part, je passerai à une fonction personnalisée (matricielle) opérant tous les calculs à partir du tableau source et des deux tables de répartition, pour renvoyer en une seule opération de validation le tableau de résultats final.

Cordialement.

Je n'avais pas vu ton dernier post lors de mon précédent...

Pas vu les formules que tu indiques dans ton dernier fichier.

Tu gagnerais à aligner tes tables Postes et Flux dans le même sens, rien ne l'empêche.

Comme je l'ai dit j'ai scindé l'opération en 2 dans ma proposition, pour obtenir une matrice de répartition de chaque montant, et ensuite une formule l'appliquant aux montants, ce qui permet 2 formules de taille raisonnable et gérable.

Si on doit allonger, je suis partisan d'un calcul VBA qui renvoie alors le résultat, on n'a plus à se casser la tête à formuler, et on maîtrise le calcul avec des moyens simples.

Voilà l'illustration ! C'est un peu fastidieux à faire mais pas plus que manipuler des formules longues...

J'ai "arrangé" pour pouvoir nommer les plages et faciliter la compréhension (soit mettre les données en commençant en A1 !)

La plage source des charges est nommé Charges, en dynamique (voir formule dans le gestionnaire de noms) le nom correspondra toujours à la plage (excluant la ligne d'en-tête).

Les tables de clés Poste et Flux sont nommées : CleP et CleF, les zones nommées sont celles colorées en bleu clair. CleF a été redressées pour être dans le même sens que CleP (pour faciliter l'utilisation).

La fonction personnalisée, que j'ai appelée REPARTCHARGES va fournir un tableau des charges par Poste-Flux, elle inscrit également les en-têtes Poste et Flux. Elle renvoie ce qui est dans la zone colorée en jaune.

C'est une fonction matricielle : on commence donc par sélectionner la zone jaune, soit autant de lignes que de postes+1 et autant de colonnes que de flux+1. On tape ensuite la formule en lui fournissant en arguments dans l'ordre la plage de charges à 6 colonnes, en-têtes exclues, la table de clés Poste et la table de clés flux (ces 2 dernières complètes avec en-têtes qui sont des identifiants, la fonction récupères les noms de postes et les noms de flux dans ces tables pour monter le tableau résultats).

On valide par Ctrl+Maj+Entrée (matricielle renvoyant u tableau de résultats).

Elle ne fait pas le quadrillage et la mise en forme qui reste à ta charge !

Function REPARTCHARGES(TbCh As Range, P As Range, F As Range)
    Dim TbR(), d As Object, i%, k%, np%, nf%, q%, g%, nmp$, nmf$, mt
    np = P.Rows.Count - 1: nf = F.Rows.Count - 1
    ReDim TbR(np, nf)
    Set d = CreateObject("Scripting.Dictionary")
    With P
        For i = 2 To .Rows.Count
            TbR(i - 1, 0) = .Cells(i, 1)
            For k = 2 To .Columns.Count
                d(.Cells(i, 1).Value & .Cells(1, k).Value) = .Cells(i, k)
            Next k
        Next i
    End With
    With F
        For i = 2 To .Rows.Count
            TbR(0, i - 1) = .Cells(i, 1)
            For k = 2 To .Columns.Count
                d(.Cells(i, 1).Value & .Cells(1, k).Value) = .Cells(i, k)
            Next k
        Next i
    End With
    For i = 2 To P.Rows.Count
        For k = 2 To F.Rows.Count
            d(P.Cells(i, 1).Value & F.Cells(k, 1).Value) = 0
        Next k
    Next i
    With TbCh
        For i = 1 To .Rows.Count
            If .Cells(i, 3) = "divers" Then
                If .Cells(i, 4) = "divers" Then
                    For q = 1 To np
                        For g = 1 To nf
                            mt = CCur(d(TbR(q, 0) & TbR(0, g)))
                            mt = mt + CCur(d(TbR(q, 0) & .Cells(i, 5).Value)) _
                             * CCur(d(TbR(0, g) & .Cells(i, 6).Value)) * .Cells(i, 2)
                            d(TbR(q, 0) & TbR(0, g)) = mt
                        Next g
                    Next q
                Else
                    nmf = .Cells(i, 4)
                    For q = 1 To np
                        mt = CCur(d(TbR(q, 0) & nmf))
                        mt = mt + CCur(d(TbR(q, 0) & .Cells(i, 5).Value)) * .Cells(i, 2)
                        d(TbR(q, 0) & nmf) = mt
                    Next q
                End If
            Else
                nmp = .Cells(i, 3)
                If .Cells(i, 4) = "divers" Then
                    For g = 1 To nf
                        mt = CCur(d(nmp & TbR(0, g)))
                        mt = mt + CCur(d(TbR(0, g) & .Cells(i, 6).Value)) * .Cells(i, 2)
                        d(nmp & TbR(0, g)) = mt
                    Next g
                Else
                    nmf = .Cells(i, 4)
                    mt = CCur(d(nmp & nmf))
                    mt = mt + .Cells(i, 2)
                    d(nmp & nmf) = mt
                End If
            End If
        Next i
    End With
    For i = 1 To np
        For k = 1 To nf
            TbR(i, k) = CCur(d(TbR(i, 0) & TbR(0, k)))
        Next k
    Next i
    TbR(0, 0) = ""
    REPARTCHARGES = TbR
End Function

Ne te laisse pas impressionner par la longueur (relative toutefois). Elle constitue un dictionnaire successivement des poste-cléPoste (=valeur de la clé), des flux-cléFlux (=valeur de la clé), des poste-flux (=0 en attente des résultats à y cumuler par calcul sur le tableau des charges.

Après calculs on sert le tableau résultats avec cette dernière série d'éléments du dictionnaire, et on renvoie le tableau résultats.

L'outil Dictionnaire est l'un des plus rapides utilisables en VBA.

Cordialement.

Bonjour,

encore une fois, merci pour cette rapidité de réponse.. Il me manque de sérieuses notions en vba pour comprendre cette dernière formule toutefois!

super boulot en tout cas, ça m'a l'air parfait! il ne reste plus qu'à me l'approprier.. ce qui risque de prendre du temps

Merci encore!

Function REPARTCHARGES(TbCh As Range, P As Range, F As Range)
    Dim TbR(), d As Object, i%, k%, np%, nf%, q%, g%, nmp$, nmf$, mt
    Application.Volatile

Tu rajoutes Application.Volatile à cet endroit, que j'ai oublié. C'est la commande qui permet à la formule d'être recalculée lors du recalcul de la feuille.

Sinon, pas de difficultés, ça fonctionne comme une fonction Excel ordinaire. Tu lui fournis 3 plages composées de la même façon que sur le classeur modèle et elle renvoie le résultat. Il faut évidemment que si le poste est "divers" elle trouve un code Poste dans la colonne prévue, même chose pour flux. Et que les tables répertorient bien tous les postes et flux... Elle doit s'adapter de la même façon, quels que soient le nombre de Postes et de Flux.

Cordialement.