Remplacer "power query" par une formule

Bonjour à tous,

J'espère que vous allez bien. Dans le cadre d'une documentation demandée par mon expert comptable je dois faire un tableau de synthèse des coûts par projets et par équipes.

J'ai un problème qui est que les ingénieurs chez nous peuvent selon leur envies ou les priorités changer d'équipe en cours d'année. Du coup il peuvent être dans différentes équipes, voir si ils sont sur un poste de program manager ils le sont de facto.

Je ne sais pas trouver la formule pour remplir la partir orange de mon tableau "Repartition". On m'a fait une power query mais je ne sais pas le faire seul du coup je suis bloqué

Y aurait-il une ame charitable pour m'aider? soit en m'indiquer la bonne formule soit en m'expliquant les étapes de la power querrry.

Merci par avance,

Bojour Adrien,

sujet intéressant que j'avais vu mais pas pris en charge car il avait été traité par PowerQ

néanmoins, j'aime bien quand il est possible de proposer différentes solutions ... je regarde ! et tu pourras comparer

Bonjour Jean-Eric

Bonjour à tous

As-tu regardé le lien de Jean-Eric pour déjà te faire une idée

Sinon je donnerai les explications ce soir (pas le temps avant)

Chris : Je suis aussi preneur d'explications

J'essaierai de transposer sur une structure en tableau (qui m'a perturbée, pour une fois !)

Feuil2 : formules matricielles, en respectant scrupuleusement les lignes et colonnes car je n'ai pas recherché par equiv les lignes et colonnes concernées

=SOMME(TRANSPOSE(C$33:C$39)*($D23:$J23))

Feuil3 : formules simples mais nécessite de transposer un tableau, par contre intègre equiv

=SOMMEPROD(DECALER($D$22:$J$22;EQUIV($B43;$B$23:$B$29;0);)*DECALER($C$32:$I$32;EQUIV(D$42;$B$33:$B$39;0);))

Bonjour Chris,

oui j'ai bien pris connaissance de son approche en détail et me suis familiarisé avec l'éditeur power query. je l'ai même un peu améliorier en intégrant des contrôles automatisés. J'uploaderai le fichier demain à cet effet. Mais j'aime bien avoir 2 solutions. ;)

Bonne soirée,

Adrien

Bonsoir

Les colonnes Matricule, Nom, Salaire brut chargé font l'objet d'un tableau à part nommé Salaires

Le tableau Tableau4 est renommé avec un nom signifiant : Projets, de même que Tbableau1 -> Equipes

• Le tableau Salaires est basculé simplement dans PowerQuery : depuis une cellule du tableau, Données, A partir d'un tableau puis sortir par Fermer et Charger dans, Connexion seulement
• Le tableau Projets est basculé de même dans PowerQuery puis :
  supprimer dans le volet à droite l'action de typage des donnéessélectionner la colonne Equipe, clic droit, Dépivoter les autres colonnes
  dans la barre de formule remplacer Attribut par Projetfiltrer la colonne Projet pour éliminer Total
  • sélectionner les colonnes Equipe et projet, clic droit, Modifier le Type, Texte
  • sélectionner la colonne Valeur, clic droit, Modifier le Type, Nombre décimal
  • sortir par Fermer et Charger dans, Connexion seulement
• Basculer le tableau Projets dans PowerQuery puis :
  supprimer dans le volet à droite l'action de typage des donnéessélectionner les colonnes Matricule, Nom, clic droit, Dépivoter les autres colonnes
  dans la barre de formule remplacer Attribut par Equipefiltrer la colonne Equipe pour éliminer Total
  • sélectionner les colonnes Matricule, Nom, Equipe, clic droit, Modifier le Type, Texte
  • sélectionner la colonne Valeur, clic droit, Modifier le Type, Nombre décimal
• Créer la dernière requête Repartition :
  Accueil, Fusionner des requêtes : choisir les requêtes Equipes et Projets, sélectionner la colonne Equipe de chaque requête, jointure externe gauchecliquer sur la double flèche près du titre Projets, décocher tout sauf Projet et ValeurAccueil, Fusionner des requêtes : choisir la requête Salaires en seconde requête, sélectionner la colonne Nom (ou mieux la requête Matricule) dans chaque requête, jointure externe gauche
  • cliquer sur la double flèche près du titre Salaires, décocher tout sauf Salaire brut chargé
  • Ajouter une colonne, colonne personnalisée : nom Répartition salaires, formule
    =[Valeur]*[Valeur.1]*[Salaire brut chargé]
  • sélectionner les colonnes Matricule, Nom, Projet, Salaire brut chargé, Répartition salaires, clic droit, Supprimer les autres colonnes
  • trier par Projet et Matricule
  • faire glisser la colonne Projet pour mettre les colonnes dans l'ordre Matricule, Nom, Salaire brut chargé, Projet, Répartition salaires
  • sélectionner la colonne Projet, Transformer, Pivoter, Répartition salaires, Somme
  • sortir par Fermer et Charger dans, Connexion seulement
• Afficher le volet des requêtes, sélectionner Repartition, clic droit, Charger dans, Tableau et choisir l'emplacement

A noter que j'ai un poil modifié le classeur de Jean-Eric

Edit : Arf, tout cela pour rien...

Bonjour,

Merci 78chris

Cela m'évite de le faire.

Cdlt.

Bonjour Adrien

Après ces grandes explications, je signale que j'ai posté une version classique ici : https://forum.excel-pratique.com/excel/remplacer-power-query-par-une-formule-144884#p890465

La version la plus simple (correspondant au titre du topic) reste celle-ci

=SOMME(TRANSPOSE(C$23:C$29)*($D13:$J13)*$D3)

Il existe aussi une autre voie : VBA

merci à tous :)

version macro pour compléter ton panel de solutions

Sub calcul()
Dim resultat()

    salaires = ActiveSheet.ListObjects("Salaires").DataBodyRange
    equipes = ActiveSheet.ListObjects("Equipes").DataBodyRange
    projets = ActiveSheet.ListObjects("Projets").DataBodyRange

    Range("B33").Resize(UBound(salaires), UBound(salaires, 2)) = salaires

    ReDim resultat(1 To UBound(salaires), 2 To UBound(projets, 2) - 1)
    For i = 1 To UBound(salaires)
        For j = 2 To UBound(projets, 2) - 1
            ' i = individu, j = projet
            resultat(i, j) = 0
            For k = 1 To UBound(projets)
                resultat(i, j) = resultat(i, j) + equipes(i, k + 2) * projets(k, j)
            Next
            resultat(i, j) = resultat(i, j) * salaires(i, 3)
        Next
    Next
    Range("E33").Resize(UBound(resultat), UBound(resultat, 2) - 1) = resultat

End Sub