XL 2013 Cumul mensuel sur plusieurs années

Leguyl

XLDnaute Nouveau
Bonjour, ou plutôt bonsoir à toutes et à tous.

J'ai un premier tableau avec 5 colonnes :

La première, contient des dates au format JJ/MM/AAAA étalées sur plus de 3 ans.
Les 4 autres, au format monétaire, sont, dans l'ordre : Débit, Crédit, Différence et Cumul.

Pas de problème avec ce tableau mais là où ça coince, c'est que je dois, dans un second tableau, automatiser le calcul de la somme de Débit et Crédit pour chaque mois depuis le début jusqu'à la fin du chantier et je ne vois pas trop comment m'y prendre.

Je sais que je pourrais utiliser des TCD mais ils ne permettent pas de faire des cumuls mensuels sur plusieurs années.

Si quelqu'un a ne serait-ce qu'un début de solution, ce serait sympa de la partager.

Merci d'avance
 

Fichiers joints

Dernière édition:

Leguyl

XLDnaute Nouveau
Merci beaucoup, frangy, ça marche ;)

Je n'avais pas du tout pensé à cette approche. Comme je l'ai dit dans ma présentation, je reste un éternel apprenti devant Excel. Je progresse lentement mais je progresse.

Il ne me reste plus qu'à automatiser ça (enfin au moins essayer) pour ne plus à avoir à entrer manuellement les années et mois dans la première colonne du second tableau et que celui-ci s'adapte au nombre de dates du premier.
 

Leguyl

XLDnaute Nouveau
Oui, bien sûr, mais dans un TCD les cumuls mensuels sont justement groupés par année et ne s'étalent pas du premier au dernier mois d'un chantier qui peut durer des années. Voir fichier joint

Maintenant, je cherche, à automatiser la solution de frangy en VBA. C'est à dire, quelle que soit le nombre d'entrées dans le premier tableau, le second soit rempli automatiquement sans avoir à entrer aucune donnée manuellement.

Je cherche, je cherche mais sans doute mal, je ne trouve pas (pas encore) comment procéder.

Quoi qu'il en soit, merci
 

Fichiers joints

Laurent78

XLDnaute Junior
Avec PowerQuery, par contre lors de l'actualisation, la mise en forme n'est pas reproduite sur les nouvelles lignes. C'est bien bizarre. Idem sur l'onglet Tableau, su j'ajoute une ligne, la date la pas la même mise en forme que la ligne précédente. Je ne sais pas pourquoi, et ça m'énerve ...
 

Fichiers joints

Amilo

XLDnaute Impliqué
Bonjour le forum, Leguyl, Frangy, Lauent78,

Une proposition avec Power query (voir onglet "Tab2"),
C'est possible également dans un TCD mais avec une mesure DAX dans Power Pivot,
Cette dernière est plus compliquée à mettre en œuvre et surtout à expliquer

Bonne soirée
 

Fichiers joints

Leguyl

XLDnaute Nouveau
Merci Laurent. Je n'ai jamais utilisé Power Query et vais l'installer sur mon propre ordi. Il n'y a pas si longtemps, on utilisait encore Office 2007 et 2010 dans la boîte. Par contre, pas sûr qu'il le soit sur le poste où je suis chargé de travailler sur le vrai fichier de travail ni que je puisse l'y installer immédiatement. C'est à peine s'il ne faut pas demander une autorisation une ou deux semaines à l'avance pour simplement installer son navigateur préféré ou autre programme utile.
 

Amilo

XLDnaute Impliqué
Avec PowerQuery, par contre lors de l'actualisation, la mise en forme n'est pas reproduite sur les nouvelles lignes. C'est bien bizarre. Idem sur l'onglet Tableau, su j'ajoute une ligne, la date la pas la même mise en forme que la ligne précédente. Je ne sais pas pourquoi, et ça m'énerve ...
@Laurent78,

Voici une réponse aux erreurs que vous signalez dans votre message :
- Dans l'onglet "Tableau" qui est la source, la colonne "Date" comporte également le format "Personnalisé" en dehors du tableau structuré
- Vous avez personnalisé la colonne "Date" en "Année - Mois" dans Excel sur l'onglet "Power Query" : il faudrait le faire dans Power query directement dans la requête
- la formule pour le "Cumul" qui se trouve dans Excel n'est pas une formule au sens "tableau structuré" donc celle-ci ne va pas se recopier automatiquement dans la colonne : idem, il faut calculer le "Cumul" dans Power query directement

Edit : bonjour Job75:)….., oups j'ai cru voir passer Job75 dans ce fil

Cordialement
 
Dernière édition:

job75

XLDnaute Barbatruc
Bonjour Leguyl, frangy, Laurent78, Amilo,
Maintenant, je cherche, à automatiser la solution de frangy en VBA.
Voyez le fichier joint et cette macro dans le code de la 2ème feuille (clic droit sur l'onglet et Visualiser le code) :
VB:
Private Sub Worksheet_Activate()
Dim tablo, resu(), d As Object, i&, x As Variant, mois As Date, n&
tablo = Sheets("Tableau").ListObjects(1).Range.Resize(, 3) 'matrice, plus rapide
ReDim resu(1 To UBound(tablo), 1 To 3)
Set d = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(tablo)
    x = tablo(i, 1)
    If IsDate(x) Then
        mois = DateSerial(Year(x), Month(x), 1)
        If Not d.exists(mois) Then n = n + 1: d(mois) = n: resu(n, 1) = mois
        If IsNumeric(tablo(i, 2)) Then resu(n, 2) = resu(n, 2) + CDbl(tablo(i, 2))
        If IsNumeric(tablo(i, 3)) Then resu(n, 3) = resu(n, 3) + CDbl(tablo(i, 3))
    End If
Next
'---restitution---
With ListObjects(1).Range.Rows(2)
    If n Then
        .Cells(1).Resize(n, 3) = resu
        .Cells(1).Resize(n, 3).Sort .Cells(1), xlAscending, Header:=xlYes 'tri sur les mois
        .Cells(1, 4).Resize(n) = "=RC[-1]-RC[-2]"
        .Cells(1, 5).Resize(n) = "=SUM(R" & .Cells(1).Row & "C[-1]:RC[-1])"
    End If
    .Offset(n).Resize(Rows.Count - n - .Row + 1).EntireRow.Delete 'RAZ en dessous
End With
Columns.AutoFit 'ajustement largeurs
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub
A+
 

Fichiers joints

Laurent78

XLDnaute Junior
@Laurent78,

Voici une réponse aux erreurs que vous signalez dans votre message :
- Dans l'onglet "Tableau" qui est la source, la colonne "Date" comporte également le format "Personnalisé" en dehors du tableau structuré
- Vous avez personnalisé la colonne "Date" en "Mois - Année" dans Excel sur l'onglet "Power Query" : il faudrait le faire dans Power query directement dans la requête
- la formule pour le "Cumul" qui se trouve dans Excel n'est pas une formule au sens formule de "tableau structuré" donc celle-ci ne va pas se recopier automatiquement dans la colonne : idem, il faut calculer le "Cumul" dans Power query directement

Edit : bonjour Job75:)….., oups j'ai cru voir passer Job75 dans ce fil

Cordialement
Merci Amilo,
- je n'avais pas vu le format personnalisé dans l'onglet "Tableau"
- pourquoi pas en effet
- la formule se recopie bien, c'est la mise en forme qui ne se recopie pas.

Je viens de voir votre requête PowerQuery, c'est parfait. Merci

Merci
Bonne soirée à Tous
 
Dernière édition:

Leguyl

XLDnaute Nouveau
Que dire, si ce n'est un grand merci à toi également job75. Ça marche au poil et là je suis sûr que je pourrai utiliser cette solution au boulot.

Quant aux autres intervenants, vous ne m'avez pas donné des solutions pour rien, elles vont personnellement bien me servir. Ça a vraiment l'air génial ce Power Query.

Mille mercis à tous, votre aide est vraiment appréciée :)
 

Amilo

XLDnaute Impliqué
Merci Amilo,
- je n'avais pas vu le format personnalisé dans l'onglet "Tableau"
- pourquoi pas en effet
- la formule se recopie bien, c'est la mise en forme qui ne se recopie pas.

Je viens de voir votre requête PowerQuery, c'est parfait. Merci

Merci
Bonne soirée à Tous
Re,

@Laurent78,

La formule se recopie dans le tableau source mais voyez de quelle manière dans la capture image en pièce jointe,
La formule "Crédit-Débit" est bien une formule de tableau structuré avec les crochets et le nom des colonnes en référence.
Alors que la formule somme() ne l'est pas et ne peut fonctionner correctement (contexte de colonne vs contexte de cellule).

Il existe une fonction semblable dans Power query à saisir manuellement en ajoutant 2 colonnes :
- colonne Index qui s'incrémente et commençant par 1
- une 2ème colonne en saisissant manuellement la combinaison de 2 fonctions : List.Sum et List.Range
Ici, la fonction List.Range renvoie la liste des valeurs de la colonne "Différence" en fonction de la valeur de la colonne "Index"
Par exemple, si "Index" est égal à 5, la fonction renvoie les 5 premières valeurs de la colonne "Différence"
La fonction, List.Sum réalise donc la somme de cette liste à hauteur du numéro de "Index"

Concernant, la mise en forme de la colonne "Date" dans le tableau Power query, il y a également un souci de format en réalité "Standard" qui est récalcitrant
Si vous l'essayez sur nouvelle feuille, avec la 1ère colonne au format "Date", la mise en forme personnalisée reste.

Bonne soirée
 

Fichiers joints

Dernière édition:

job75

XLDnaute Barbatruc
La formule "Crédit-Débit" est bien une formule de tableau structuré avec les crochets et le nom des colonnes en référence.
Alors que la formule somme() ne l'est pas et ne peut fonctionner correctement (contexte de colonne vs contexte de cellule).
Validée en F3 la formule =SOMME($E$3:E3) se propage automatiquement sur toute la colonne F.
 

Amilo

XLDnaute Impliqué
Bonjour Job75,
Pourtant chez moi, la formule se propage mais ne s'incrémente pas correctement,
Pour ma part, il faut effectivement recliquer dans la cellule F3 pour corriger la formule qui fonctionne correctement tant qu'il n'y a pas de nouvelles lignes…!!!
Il y a sinon quelque chose qui m'échappe certainement
Bonne soirée
 
Dernière édition:

Amilo

XLDnaute Impliqué
Re,
Voilà ce que j'obtiens dans le fichier en insérant des lignes (voir capture d'écran), la formule devrait être comme sur la colonne en vert,
Cordialement
 

Fichiers joints

Amilo

XLDnaute Impliqué
Merci Job75 pour votre réponse,
Pour vérifier si ça venait pas du fichier, j'ai en créé un nouveau et toujours le même souci,
Je ne comprends alors pas pourquoi chez vous cela fonctionne…!!!
Bonne nuit
 

Fichiers joints

Amilo

XLDnaute Impliqué
Re,
C'est bon Job75, je dois être fatigué, j'ai modifié le contexte et cela fonctionne effectiement (voir capture)
Bonne nuit
 

Fichiers joints

Discussions similaires


Haut Bas