POWERQUERY :: Faire la somme d'un tableau horizontalement et verticalement (somme des lignes, somme des colonnes)

oguruma · 26 Janvier 2024

A partir de ces types de tableaux :

Obtenir les cumuls soit en ligne soit en colonne soit les deux.
Bien que cela soit largement possible en formule Excel la solution powerquery a de multiples avantages :
- c'est dynamique et pas besoin d'ajouter des formules en cas d'ajout de colonnes ou de retravailler le tableau
- ça nous économise aussi des formules à maintenir (bon a du code pwq à la place... certes...)
- ça peut aussi nous éviter un TCD légèrement compliqué à maintenir
- avec une petite modification sur la source que je vous laisse faire.... on peut aussi récupérer la source des données dans un classeur externe et l'importer dans une feuille pour en faire les cumuls ... et à partir de là les consolidations sont simplifiées (voir les Post à ce propos)

le code est ouvert et peut être repris selon vos besoins d'évolutions

ou lignes et colonnes

Environnement PowerQuery

Le plus simple étant de construire les fonctions :
- de cumul vertical
- de cumul horizontal
- de cumul combiné H+V
Le cumul vertical fnTotalEachColumnsV

Dans cette fonction on découvre au passage une autre manière d'utiliser la boucle List.Accumulate
pOffset : correspondant aux colonnes de gauche qui ne sont pas à prendre en compte
pBuff : Bufferisation de la table (par défaut non)
pLibTotal : on précise le libellé du total

PowerQuery:

let

   fnTotalEachColumns =(pTable as any, pOffset as number, pLibTotal as text, optional pBuff as logical) as table =>

   let

      //-----------------------------------------------------------------------------------
      // Fonction permettant de totaliser les colonnes d'un tableau
      //-----------------------------------------------------------------------------------

      /*
      pTable="TB_VENTES",
      pOffset=1,
      pLibTotal="Total ventes",
      pBuff=true
      */

      //-----------------------------------------------------------------------------------
      // On importe la source en passant par les buffers pour optimiser les calculs
      // Fait-on le choix de bufferiser la table des données
      //-----------------------------------------------------------------------------------
      Source = if pTable is table then
                  pTable
                  else Excel.CurrentWorkbook(){[Name = pTable]}[Content],

      // Choix si buffer
      bSource=if pBuff is null then
                 Source
                 else if pBuff then
                 Table.Buffer(Source)
                 else Source ,

      //-----------------------------------------------------------------------------------
      // On récupère les colonnes de la table des données
      // Là on peut bufferiser sans risque sur le nbr de colonnes
      //-----------------------------------------------------------------------------------
      ColNames = List.Buffer(Table.ColumnNames(bSource)),

      //-----------------------------------------------------------------------------------
      // On ne retient que les colonnes à cumuler en écartant celles de gauche
      //-----------------------------------------------------------------------------------
      TotalsHeaders=List.Buffer(List.LastN(ColNames,List.Count(ColNames) - pOffset)),
    
      //-----------------------------------------------------------------------------------
      // On crée une table temporaire qui va contenir le total de chaque colonne
      // Et conserve le titre de la 1ère colonne par ColNames{0}
      //-----------------------------------------------------------------------------------
      TblFirstColTmp = Table.FromColumns({{pLibTotal}}, {ColNames{0}}),
    
      //-----------------------------------------------------------------------------------
      // On fait le cumul de chaque colonne dans la table temporaire
      // Boucle pour les cumuls par mois
      // Une manière de mettre en oeuvre List.Accumulate
      //-----------------------------------------------------------------------------------
      CumulColumnsTblTmp = List.Accumulate(
         TotalsHeaders,        // Liste des colonnes à cumuler
         TblFirstColTmp,       // Initialisation sur la 1ère colonne de la table temporaire
         (state, current) =>   // On va boucler sur les colonnes à totaliser
                               // La colonne en cours de traitement est totalisée
                               // Table.Column(bSource, current) ==> représente la colonne en cours
                               // traduite sous forme de liste pour faire le total de cette liste
         Table.AddColumn(state, current, each List.Sum(Table.Column(bSource, current)), type number)
      ),

      //-----------------------------------------------------------------------------------
      // On fusion la table des cumuls à la table des données pour le tableau final
      // Petite astuce pour ajouter un enregistrement à la fin d'un tableau
      //-----------------------------------------------------------------------------------
      ToTable = Table.Combine({bSource, CumulColumnsTblTmp})

   in
      ToTable
in
   fnTotalEachColumns

Le cumul horizontal fnTotalEachColumnsH

Attention particulière sur la colonne ajoutée pour effectué le total en ligne

each List.Sum( // Pour chaque ligne on fait la somme
Record.ToList( // On prend le parti de faire somme de toutes les colonnes
Record.SelectFields(_, // mais on supprime les colonnes de tête
List.RemoveItems(Table.ColumnNames(Source), FirstN)))))
pOffset : correspondant aux colonnes de gauche qui ne sont pas à prendre en compte
FirstN=List.FirstN(ColNames,pOffset), --> on déterminer quelles seront les valeurs(colonnes) qui seront éliminées
Le List.Sum a l'avantage entre autres de faire abstraction des valeurs null pour ne pas avoir au final un total null

PowerQuery:

let

   fnTotalEachColumnsH =(pTable as any, pOffset as number, pLibTotalH as text, optional pBuff as logical) as table =>

   let

      //-----------------------------------------------------------------------------------
      // Fonction permettant de totaliser les colonnes d'un tableau
      //-----------------------------------------------------------------------------------

      /*
      pTable="TB_VENTES",
      pOffset=1,
      pLibTotalH="Total"
      pBuff=true
      */

      //-----------------------------------------------------------------------------------
      // On importe la source en passant par les buffers pour optimiser les calculs
      // Fait-on le choix de bufferiser la table des données
      //-----------------------------------------------------------------------------------
      Source = if pTable is table then
                  pTable
                  else Excel.CurrentWorkbook(){[Name = pTable]}[Content],

      // Choix si buffer
      bSource=if pBuff is null then
                 Source
                 else if pBuff then
                 Table.Buffer(Source)
                 else Source ,

      //-----------------------------------------------------------------------------------   
      // Nombre de colonnes à la source
      //-----------------------------------------------------------------------------------
      ColNamesSource = List.Buffer(Table.ColumnNames(bSource)),
      FirstN=List.FirstN(ColNamesSource,pOffset),

      //-----------------------------------------------------------------------------------
      // On ajoute la colonne total en fin de ligne
      //-----------------------------------------------------------------------------------
      TblAddColumnTotal = Table.AddColumn(
                          bSource,
                          pLibTotalH,
                          each List.Sum(   // Pour chaque ligne on fait la somme
                                    Record.ToList(  // On prend le parti de faire somme de toutes les colonnes
                                           Record.SelectFields(_,   // mais on supprime les colonnes de tête
                                           List.RemoveItems(Table.ColumnNames(Source), FirstN)))))

   in
      TblAddColumnTotal
in
   fnTotalEachColumnsH

Le cumul combiné

C'est en fait l'association des fonctions précédentes et on précise les libellés H+V

PowerQuery:

let

   fnTotalEachColumnsHV =(pTable as any, pOffset as number, pLibTotalH as text, pLibTotalV as text, optional pBuff as logical) as table =>

   let

      //-----------------------------------------------------------------------------------
      // Fonction permettant de totaliser les colonnes d'un tableau
      //-----------------------------------------------------------------------------------

      /*
      pTable="TB_VENTES",
      pOffset=1,
      pLibTotalV="Total ventes",
      pLibTotalH="Total"
      pBuff=true
      */

      //-----------------------------------------------------------------------------------
      // On importe la source en passant par les buffers pour optimiser les calculs
      // Fait-on le choix de bufferiser la table des données
      //-----------------------------------------------------------------------------------
      Source = if pTable is table then
                  pTable
                  else Excel.CurrentWorkbook(){[Name = pTable]}[Content],

      // Choix si buffer
      bSource=if pBuff is null then
                 Source
                 else if pBuff then
                 Table.Buffer(Source)
                 else Source ,

      // Nombre de colonnes à la source
       ColNamesSource = List.Buffer(Table.ColumnNames(bSource)),
      FirstN=List.FirstN(ColNames,pOffset),

      // On ajoute la colonne total en fin de ligne
      TblAddColumnTotal = Table.AddColumn(
                          bSource,
                          pLibTotalH,
                          each List.Sum(
                                    Record.ToList(
                                           Record.SelectFields(_,
                                           List.RemoveItems(Table.ColumnNames(Source), FirstN))))),

      //-----------------------------------------------------------------------------------
      // On récupère les colonnes de la table des données + la colonne total ajoutée
      // Là on peut bufferiser sans risque sur le nbr de colonnes
      //-----------------------------------------------------------------------------------
      ColNames = List.Buffer(Table.ColumnNames(TblAddColumnTotal)),

      //-----------------------------------------------------------------------------------
      // On ne retient que les colonnes à cumuler en écartant celles de gauche
      // La colonne total ajoutée est conservée
      //-----------------------------------------------------------------------------------
      TotalsHeaders=List.Buffer(List.LastN(ColNames,List.Count(ColNames) - pOffset)),
    
      //-----------------------------------------------------------------------------------
      // On crée une table temporaire qui va contenir le total de chaque colonne
      // Et conserve le titre de la 1ère colonne par ColNames{0}
      //-----------------------------------------------------------------------------------
      TblFirstColTmp = Table.FromColumns({{pLibTotalV}}, {ColNames{0}}),
    
      //-----------------------------------------------------------------------------------
      // On fait le cumul de chaque colonne dans la table temporaire
      // Boucle pour les cumuls par mois
      // Une manière de mettre en oeuvre List.Accumulate
      //-----------------------------------------------------------------------------------
      CumulColumnsTblTmp = List.Accumulate(
         TotalsHeaders,        // Liste des colonnes à cumuler
         TblFirstColTmp,       // Initialisation sur la 1ère colonne de la table temporaire
         (state, current) =>   // On va boucler sur les colonnes à totaliser
                               // La colonne en cours de traitement est totalisée
                               // Table.Column(bSource, current) ==> représente la colonne en cours
                               // traduite sous forme de liste pour faire le total de cette liste
         Table.AddColumn(state, current, each List.Sum(Table.Column(TblAddColumnTotal, current)), type number)
      ),

      //-----------------------------------------------------------------------------------
      // On fusion la table des cumuls à la table des données pour le tableau final
      // Petite astuce pour ajouter un enregistrement à la fin d'un tableau
      //-----------------------------------------------------------------------------------
      ToTable = Table.Combine({TblAddColumnTotal, CumulColumnsTblTmp})

   in
      ToTable
in
   fnTotalEachColumnsHV

Utilisation des fonctions

PowerQuery:

let
   // Avec buffer = false (par défaut)
   Source = fnTotalEachColumnsV("TB_VENTES",1,"Total des ventes")
in
    Source

let
   // Avec buffer = true
   Source = fnTotalEachColumnsV(TB_VENTES_2,3,"Total des ventes",true)
in
    Source
  
let
   // Avec buffer = true
   Source = fnTotalEachColumnsH(TB_VENTES_2,3,"Total des ventes",true)
in
    Source
  
let
   // Avec buffer = true
   Source = fnTotalEachColumnsHV(TB_VENTES_2,3,"Total produit","Total vendeur",true)
in
    Source

POWERQUERY :: Faire la somme d'un tableau horizontalement et verticalement (somme des lignes, somme des colonnes)

oguruma

XLDnaute Occasionnel

Pièces jointes

Discussions similaires