oguruma
XLDnaute Impliqué
Bonjour,
ce post fait suite à celui-ci : https://excel-downloads.com/threads...les-colonnes-dun-tableau-3-methodes.20083284/
Il semblait normal donc de faire une totalisation par trimestre ou par période.
Exemples de tableau
Environnement PowerQuery
Fonction 1 : fnSplitByQuarterV1
Ici nous sommes dans un cas très simple - un tableau avec 12 mois
"Produits", ==> titre de la colonne libellé
"Trimestre_", ==> titre des colonnes trimestres
true, ==> on active la somme des trimestres
"Total Trimestres" ==> libellé de la colonne total des trimestres
On accepte les valeurs par défaut et on fait la somme des trimestres
Code de la fonction
Cette fonction comporte néanmoins un inconvénient quand deux années se suivent la notion de trimestre perd son sens. La période doit être uniquement sur l'année afin que le calcul et la répartition par trimestre soit cohérente.
Afin palier à cette incohérence voici la fonction fnSplitByAnyV1
Nous obtenons ici le même résultat que la fonction précédente
Nous avons ici le choix du nombre de colonnes à totaliser (4) et du nombre de colonne à présenter "1;2;3" [transformer en liste afin de boucler pour construire les colonnes]
Nous avons ici un découpage en semestre
Autre possibilité de regroupement
Il n'est pas obligatoire d'avoir des dates dans le titre des colonnes
Code de la fonction
Fonction permettant de corriger le calcul des trimestres quand deux années se suivent
Code de la fonction
Pour plus de précisions voir le fichier joint
ce post fait suite à celui-ci : https://excel-downloads.com/threads...les-colonnes-dun-tableau-3-methodes.20083284/
Il semblait normal donc de faire une totalisation par trimestre ou par période.
Exemples de tableau
Environnement PowerQuery
Fonction 1 : fnSplitByQuarterV1
Ici nous sommes dans un cas très simple - un tableau avec 12 mois
PowerQuery:
let
Source = fnSplitByQuarterV1 ("VENTES_1")
in
Source
PowerQuery:
let
Source = fnSplitByQuarterV1 ("VENTES_1","Produits","Trimestre_",true,"Total Trimestres")
in
Source"Produits", ==> titre de la colonne libellé
"Trimestre_", ==> titre des colonnes trimestres
true, ==> on active la somme des trimestres
"Total Trimestres" ==> libellé de la colonne total des trimestres
PowerQuery:
let
Source = fnSplitByQuarterV1 ("VENTES_1",null,null,true,null)
in
SourceOn accepte les valeurs par défaut et on fait la somme des trimestres
Code de la fonction
PowerQuery:
let fnSplitByQuarterV1 = (
pTableToQuarter as any,
optional pTitleColumnsPivot as text,
optional pTitleColumnsQuarters as text,
optional pSumQuaters as logical,
optional pTitleSumQuaters as text
) =>
let
//******************************************************************************
// Gestion des paramètres
//******************************************************************************
TableToQuarter=pTableToQuarter,
TitleSumQuaters=if pTitleSumQuaters is null then "Total Trim" else pTitleSumQuaters,
TitleColumnsPivot=if pTitleColumnsPivot is null then "Articles" else pTitleColumnsPivot,
TitleColumnsQuarters=if pTitleColumnsQuarters is null then "Trim" else pTitleColumnsQuarters,
bSumQuaters=if pSumQuaters is null then false else pSumQuaters,
//******************************************************************************
// Table pour laquelle il faut faire le découpage en trimestres
//******************************************************************************
Source = if pTableToQuarter is text
then Excel.CurrentWorkbook(){[Name=TableToQuarter]}[Content]
else pTableToQuarter,
//******************************************************************************
// Alimentation des constantes
//******************************************************************************
BASE3=3,
BASE1=1,
//**************************************************************************************************
// Dans cette liste on ne tient pas compte qu'une série de mois peut se suivre sur deux années
// On considère pour simplifier le découpage que l'on est dans la même année
// On pourrait donc avoir un découpage qui n'aurait pas de sens si deux années se suivent
// Cette liste est utilisée pour numéroter les trimestres dans l'ordre
//**************************************************************************************************
QUATER_LIST= {1,2,3,4},
//******************************************************************************
// Titres des colonnes avec ou sans la colonne total des trimestres
// On va boucler pour numéroter les trimestres
//******************************************************************************
TitleTrim= if bSumQuaters
then {TitleColumnsPivot} & List.Transform(QUATER_LIST, (Trim) => TitleColumnsQuarters & Text.From(Trim)) & {TitleSumQuaters}
else {TitleColumnsPivot} & List.Transform(QUATER_LIST, (Trim) => TitleColumnsQuarters & Text.From(Trim)),
//******************************************************************************
// Regroupement par trimestre ligne à ligne {}
// Doit-on faire la somme totale des trimestres
//******************************************************************************
ComputeTrim= if bSumQuaters
then
//----------------------------------------------------------------------
// Dans ce cas il faut tenir compte de la colonne total des trimestres
//----------------------------------------------------------------------
List.Transform( List.Skip(Table.ToRows(Source),1),
(RecordLine) =>
{RecordLine{0}} & // Découpage modulo BASE3 (3 mois pour un trimestre) et il faut tenir compte des trimestres incomplets
List.Transform(QUATER_LIST,each List.Sum(List.Range(RecordLine, (_ - 1) * BASE3 + BASE1,BASE3))) & {List.Sum( List.Skip(RecordLine,1))})
else
//----------------------------------------------------------------------
// On ne prend pas en compte la colonne total des trimestres
//----------------------------------------------------------------------
List.Transform( List.Skip(Table.ToRows(Source),1),
(RecordLine) =>
{RecordLine{0}} &
List.Transform(QUATER_LIST,each List.Sum(List.Range(RecordLine, (_ - 1) * BASE3 + BASE1,BASE3)))),
//******************************************************************************
// On construit dynamiquement la table avec son constructeur
//******************************************************************************
// Construction de la table finale avec les trimestres
//******************************************************************************
ToTableTrim=#table(TitleTrim,ComputeTrim)
in
ToTableTrim
in
fnSplitByQuarterV1Cette fonction comporte néanmoins un inconvénient quand deux années se suivent la notion de trimestre perd son sens. La période doit être uniquement sur l'année afin que le calcul et la répartition par trimestre soit cohérente.
Afin palier à cette incohérence voici la fonction fnSplitByAnyV1
PowerQuery:
let
Source = fnSplitByAnyV1 ("VENTES_1")
in
SourceNous obtenons ici le même résultat que la fonction précédente
PowerQuery:
let
Source = fnSplitByAnyV1 ("VENTES_1",4,"1;2;3","Art","Periode")
in
SourceNous avons ici le choix du nombre de colonnes à totaliser (4) et du nombre de colonne à présenter "1;2;3" [transformer en liste afin de boucler pour construire les colonnes]
Nous avons ici un découpage en semestre
PowerQuery:
let
Source = fnSplitByAnyV1 ("VENTES_1",6,"1;2",null,"Semestre_")
in
SourceAutre possibilité de regroupement
PowerQuery:
let
Source = fnSplitByAnyV1 ("VENTES_1",3,"1;2;3;4","Libellé","Cadence")
in
SourceIl n'est pas obligatoire d'avoir des dates dans le titre des colonnes
Code:
let
/*
pTableToQuarter as any,
optional pTitleColumnsPivot as text,
optional pTitleColumnsQuarters as text,
optional pSumQuaters as logical,
optional pTitleSumQuaters as text,
optional pYyyy as number,
optional pSeparator as text
*/
Source = fnSplitByQuarterV2 ("VENTES_2","Libellé","Trim",false,null,4,"-")
in
SourceCode de la fonction
PowerQuery:
let fnSplitByAnyV1 = (
pTableToQuarter as any,
optional pBase as number,
optional pAnyList as text,
optional pTitleColumnsPivot as text,
optional pTitleColumnsQuarters as text,
optional pSumQuaters as logical,
optional pTitleSumQuaters as text
) =>
let
//******************************************************************************
// Gestion des paramètres
//******************************************************************************
TableToQuarter=pTableToQuarter,
Base = if pBase is null then 3 else pBase,
AnyNumberList = if pAnyList is null then {1,2,3,4} else List.Transform(Text.Split(pAnyList,";"), each Number.FromText(_)),
TitleSumQuaters=if pTitleSumQuaters is null then "Total Trim" else pTitleSumQuaters,
TitleColumnsPivot=if pTitleColumnsPivot is null then "Articles" else pTitleColumnsPivot,
TitleColumnsQuarters=if pTitleColumnsQuarters is null then "Trim" else pTitleColumnsQuarters,
bSumQuaters=if pSumQuaters is null then false else pSumQuaters,
//******************************************************************************
// Table pour laquelle il faut faire le découpage en trimestres
//******************************************************************************
Source = if pTableToQuarter is text
then Excel.CurrentWorkbook(){[Name=TableToQuarter]}[Content]
else pTableToQuarter,
//******************************************************************************
// Alimentation des constantes
//******************************************************************************
BASE1=1,
//**************************************************************************************************
// Dans cette liste on ne tient pas compte qu'une série de mois peut se suivre sur deux années
// On considère pour simplifier le découpage que l'on est dans la même année
// On pourrait donc avoir un découpage qui n'aurait pas de sens si deux années se suivent
// Cette liste est utilisée pour numéroter les trimestres dans l'ordre
//**************************************************************************************************
QUATER_LIST= AnyNumberList,
//******************************************************************************
// Titres des colonnes avec ou sans la colonne total des trimestres
// On va boucler pour numéroter les trimestres
//******************************************************************************
TitleTrim= if bSumQuaters
then {TitleColumnsPivot} & List.Transform(QUATER_LIST, (Trim) => TitleColumnsQuarters & Text.From(Trim)) & {TitleSumQuaters}
else {TitleColumnsPivot} & List.Transform(QUATER_LIST, (Trim) => TitleColumnsQuarters & Text.From(Trim)),
//******************************************************************************
// Regroupement par trimestre ligne à ligne {}
// Doit-on faire la somme totale des trimestres
//******************************************************************************
ComputeTrim= if bSumQuaters
then
//----------------------------------------------------------------------
// Dans ce cas il faut tenir compte de la colonne total des trimestres
//----------------------------------------------------------------------
List.Transform( List.Skip(Table.ToRows(Source),1),
(RecordLine) =>
{RecordLine{0}} & // Découpage modulo BASE3 (3 mois pour un trimestre) et il faut tenir compte des trimestres incomplets
List.Transform(QUATER_LIST,each List.Sum(List.Range(RecordLine, (_ - 1) * Base + BASE1,Base))) & {List.Sum( List.Skip(RecordLine,1))})
else
//----------------------------------------------------------------------
// On ne prend pas en compte la colonne total des trimestres
//----------------------------------------------------------------------
List.Transform( List.Skip(Table.ToRows(Source),1),
(RecordLine) =>
{RecordLine{0}} &
List.Transform(QUATER_LIST,each List.Sum(List.Range(RecordLine, (_ - 1) * Base + BASE1,Base)))),
//******************************************************************************
// On construit dynamiquement la table avec son constructeur
//******************************************************************************
// Construction de la table finale avec les trimestres
//******************************************************************************
ToTableTrim=#table(TitleTrim,ComputeTrim)
in
// QUATER_LIST
ToTableTrim
in
fnSplitByAnyV1Fonction permettant de corriger le calcul des trimestres quand deux années se suivent
PowerQuery:
let
Source = fnSplitByQuarterV2 ("VENTES_2","Libellé")
in
Source
PowerQuery:
let
/*
pTableToQuarter as any,
optional pTitleColumnsPivot as text,
optional pTitleColumnsQuarters as text,
optional pSumQuaters as logical,
optional pTitleSumQuaters as text,
optional pYyyy as number,
optional pSeparator as text
*/
Source = fnSplitByQuarterV2 ("VENTES_2","Libellé","Trimestre",true,"Total Trim",2,"-")
in
Source
PowerQuery:
let
/*
pTableToQuarter as any,
optional pTitleColumnsPivot as text,
optional pTitleColumnsQuarters as text,
optional pSumQuaters as logical,
optional pTitleSumQuaters as text,
optional pYyyy as number,
optional pSeparator as text
*/
Source = fnSplitByQuarterV2 ("VENTES_2","Libellé","Trim",false,null,4,"-")
in
SourceCode de la fonction
PowerQuery:
let fnSplitByQuarterV2 = (
pTableToQuarter as any,
optional pTitleColumnsPivot as text,
optional pTitleColumnsQuarters as text,
optional pSumQuaters as logical,
optional pTitleSumQuaters as text,
optional pYyyy as number,
optional pSeparator as text
) =>
let
TableToQuarter=pTableToQuarter,
TitleColumnsPivot=if pTitleColumnsPivot is null then "Articles" else pTitleColumnsPivot,
TitleColumnsQuarters=if pTitleColumnsQuarters is null then "Trim" else pTitleColumnsQuarters,
bSumQuaters=if pSumQuaters is null then false else pSumQuaters,
TitleSumQuaters=if pTitleSumQuaters is null then "Total Trim" else pTitleSumQuaters,
Yyyy=if pYyyy is null then 2 else pYyyy,
Separator=if pSeparator is null then "-" else pSeparator,
IgnoreCount=1,
Base=3,
Source = if pTableToQuarter is text
then Excel.CurrentWorkbook(){[Name=TableToQuarter]}[Content]
else pTableToQuarter,
//**************************************************************************************************
// Pour chaque date on va déterminer le trimestre et l'année afin de les distinguer
// Ainsi on résoud le problème quand deux années se suivent
//**************************************************************************************************
QuatersList = List.Transform(
List.Skip( Record.FieldValues(Table.First(Source)), IgnoreCount),
each TitleColumnsQuarters &
Separator &
Text.From(Date.QuarterOfYear(_)) &
Separator &
Text.End(Text.From( Date.Year(_)), Yyyy)
),
//************************************************************************************************************
// Titre des colonnes trimestres
//************************************************************************************************************
TitleListQuarters= if bSumQuaters
then {Table.FirstValue(Source)} & List.Distinct(QuatersList) & {TitleSumQuaters}
else {Table.FirstValue(Source)} & List.Distinct(QuatersList),
//************************************************************************************************************
// Autre méthode pour le découpage en tenant compte de liste des trimestres calculés à l'étape précédente
//************************************************************************************************************
ComputeList=if bSumQuaters
then
List.Transform(List.Skip(Table.ToRows(Source),IgnoreCount),
(RecordLine) =>
{RecordLine{0}} &
List.Transform(
// On va répérer les mois les uns après les autres
List.Transform(List.Distinct(QuatersList), each List.PositionOf(QuatersList, _)),
// Cumul des mois détectés
each List.Sum(List.Range(RecordLine, _ + 1, Base))
// On ajoute la colonne total des trimestres
) & {List.Sum(List.Skip(RecordLine,IgnoreCount))})
else
List.Transform(List.Skip(Table.ToRows(Source),IgnoreCount),
(RecordLine) =>
{RecordLine{0}} &
List.Transform(
// On va répérer les mois les uns après les autres
List.Transform(List.Distinct(QuatersList), each List.PositionOf(QuatersList, _)),
// Cumul des mois détectés
each List.Sum(List.Range(RecordLine, _ + 1, Base))
) ),
//************************************************************************************************************
// Construction de la table finale avec son constructeur
//************************************************************************************************************
TableToQuaters= #table(
TitleListQuarters,
ComputeList)
in
TableToQuaters
in
fnSplitByQuarterV2Pour plus de précisions voir le fichier joint
