XL 2016 consolidation plusieurs feuilles

gds77

gds77

XLDnaute Occasionnel
Bonjour à vous tous,
Je vais essayer d'être claire:cool:. Sur le fichier joint je dispose de 3 feuilles pour mes banques et pour lesquelles je souhaiterais sur la feuille synthèse consolider les colonnes G "montant" et les colonnes F "catégorie" en fonction des années 2017 et 2018. Mon point d'entrée serait la colonne E "émetteur".

Merci d'avance pour votre aide.;):)
 

Pièces jointes

  • Banques 2017.xlsx
    51.9 KB · Affichages: 43
Trier par date Trier par votes
sousou

sousou

XLDnaute Barbatruc
Bonjour
Je te propose une manière qui te permettra d'aller plus loin dans tes synthèses.
Dans la feuille consolidation , j'intègre toutes les données de toutes les banques plus une formule qui donne l'année.
Dans la feuille Tcd un tableau croisée que tu peux modifier, tu peux aussi ajouter d'autres tableaux dans cette feuille
Tu lances la macro consolidation.
 

Pièces jointes

  • Banques 2017.xlsm
    84.2 KB · Affichages: 57
Vote positif 0 Vote négatif
gds77

gds77

XLDnaute Occasionnel
Bonjour Sousou,
Merci pour cette super proposition, mais mon employeur aura accès a ce document, et pour lui expliquer le fonctionnement impossible :confused:moi même trop novice sur ce sujet. C'est pourquoi, je pensais à des fonctions indirect en utilisant les années et les catégories, dans un premier temps. Mais je ne trouve pas de solutions.
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Bonjour gds77, sousou,

Voyez le fichier joint et cette macro dans le code de la feuille SYNTHESE :
Code: 
Private Sub Worksheet_Activate()
Dim a, annee1, annee2, d1 As Object, d2 As Object, i%, derlig&, j&, v1#, v2#, x$
a = Array("BP", "BCP", "CDN") 'feuilles à consolider
annee1 = [E3]: annee2 = [F3]
Set d1 = CreateObject("Scripting.Dictionary")
d1.CompareMode = vbTextCompare 'la casse est ignorée
Set d2 = CreateObject("Scripting.Dictionary")
d2.CompareMode = vbTextCompare 'la casse est ignorée
For i = 0 To UBound(a)
    With Sheets(a(i))
        derlig = .Range("E" & .Rows.Count).End(xlUp).Row
        For j = 6 To derlig
            If IsDate(.Cells(j, 2)) Then 'sécutité
                v1 = 0: v2 = 0
                If IsNumeric(.Cells(j, 7)) Then 'sécurité
                    If Year(.Cells(j, 2)) = annee1 Then
                        v1 = .Cells(j, 7)
                    ElseIf Year(.Cells(j, 2)) = annee2 Then
                        v2 = .Cells(j, 7)
                    End If
                End If
                x = .Cells(j, 5) & Chr(1) & .Cells(j, 6)
                d1(x) = d1(x) + v1: d2(x) = d2(x) + v2
            End If
        Next j
    End With
Next i
Application.ScreenUpdating = False
Range("C5:F" & Rows.Count).ClearContents
If d1.Count = 0 Then Exit Sub 'sécurité
[C5].Resize(d1.Count) = Application.Transpose(d1.keys) 'Transpose est limitée à 65536 lignes
[C5].Resize(d1.Count).TextToColumns [C5], xlDelimited, Other:=True, OtherChar:=Chr(1) 'commande Convertir
[E5].Resize(d1.Count) = Application.Transpose(d1.items)
[F5].Resize(d2.Count) = Application.Transpose(d2.items)
[C5].Resize(d1.Count, 4).Sort [C5], xlAscending, [D5], Header:=xlNo 'tri
Columns("C:F").AutoFit 'ajustement largeur
End Sub
Elle s'exécute quand on active la feuille.

PS : il y a des références circulaires dans votre fichier, tâchez de voir ça...

A+
 

Pièces jointes

  • Banques 2017(1).xlsm
    69 KB · Affichages: 27
Dernière édition:
Vote positif 0 Vote négatif
gds77

gds77

XLDnaute Occasionnel
job75 à dit:
Bonjour gds77, sousou,

Voyez le fichier joint et cette macro dans le code de la feuille SYNTHESE :
Code: 
Private Sub Worksheet_Activate()
Dim a, annee1, annee2, d1 As Object, d2 As Object, i%, derlig&, j&, v1#, v2#, x$
a = Array("BP", "BCP", "CDN") 'feuilles à consolider
annee1 = [E3]: annee2 = [F3]
Set d1 = CreateObject("Scripting.Dictionary")
d1.CompareMode = vbTextCompare 'la casse est ignorée
Set d2 = CreateObject("Scripting.Dictionary")
d2.CompareMode = vbTextCompare 'la casse est ignorée
For i = 0 To UBound(a)
    With Sheets(a(i))
        derlig = .Range("E" & .Rows.Count).End(xlUp).Row
        If derlig > 5 Then
            For j = 6 To derlig
                If IsDate(.Cells(j, 2)) Then
                    v1 = 0: v2 = 0
                    If IsNumeric(.Cells(j, 7)) Then
                        If Year(.Cells(j, 2)) = annee1 Then
                            v1 = .Cells(j, 7)
                        ElseIf Year(.Cells(j, 2)) = annee2 Then
                            v2 = .Cells(j, 7)
                        End If
                    End If
                    x = .Cells(j, 5) & Chr(1) & .Cells(j, 6)
                    d1(x) = d1(x) + v1: d2(x) = d2(x) + v2
                End If
            Next j
        End If
    End With
Next i
Application.ScreenUpdating = False
Range("C5:F" & Rows.Count).ClearContents
If d1.Count = 0 Then Exit Sub 'sécurité
[C5].Resize(d1.Count) = Application.Transpose(d1.keys) 'Transpose est limitée à 65536 lignes
[C5].Resize(d1.Count).TextToColumns [C5], xlDelimited, Other:=True, OtherChar:=Chr(1) 'commande Convertir
[E5].Resize(d1.Count) = Application.Transpose(d1.items)
[F5].Resize(d2.Count) = Application.Transpose(d2.items)
[C5].Resize(d1.Count, 4).Sort [C5], xlAscending, [D5], Header:=xlNo 'tri
Columns("C:F").AutoFit 'ajustement largeur
End Sub
Elle s'exécute quand on active la feuille.

PS : il y a des références circulaires dans votre fichier, tâchez de voir ça...

A+
Cliquez pour agrandir...
 

Pièces jointes

  • Banque.xlsx
    52.7 KB · Affichages: 25
Vote positif 0 Vote négatif
gds77

gds77

XLDnaute Occasionnel
Oups fichier parti trop tôt ,

Merci job 75 pour cette macro, mais malheureusement je ne maitrise pas le fonctionnement et serais incapable de la recopier dans mon fichier de base , et je dois rendre ma synthèse pour ce soir. Alors j'ai chercher et pour simplifier j'ai essayé la fonction indirect uniquement sur les catégories mais je ne sais pas comment dissocier les années 2017 et 2018. Alors si vous pouvez m'indiquer la formule :)

Merci d'avance
 

Pièces jointes

  • Banque.xlsx
    52.7 KB · Affichages: 20
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re,

J'ai supprimé un test inutile sur derlig dans ma macro précédente et j'ai mis le format "Comptabilité" en colonnes E et F.
gds77 à dit:
Merci job 75 pour cette macro, mais malheureusement je ne maitrise pas le fonctionnement et serais incapable de la recopier dans mon fichier de base
Cliquez pour agrandir...
Si votre fichier de base a la même structure que celui du post #1 il n'est pas nécessaire de comprendre la macro .

Et copier la macro est hyper facile :

- clic droit sur l'onglet SYNTHESE et Visualiser le code

- coller le code

- enregistrer le fichier en .xlsm (acceptant les macros).

A+
 
Vote positif 0 Vote négatif
gds77

gds77

XLDnaute Occasionnel
Surement, mais mon employeur me demande en plus de distinguer des colonnes débit et crédit sur une même catégorie quand le cas se présente....Alors pour modifier votre macro :eek::eek:
Donc si une solution existe sans macro pour utiliser la fonction indirect + année + colonne débit et crédit....Je suis preneuse avec grand plaisir.

A+
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re,
gds77 à dit:
mon employeur me demande en plus de distinguer des colonnes débit et crédit sur une même catégorie
Cliquez pour agrandir...
Il n'est pas difficile de modifier la macro, il suffit d'utiliser 4 Dictionary :
Code: 
Private Sub Worksheet_Activate()
Dim a, annee1, annee2, d1 As Object, d2 As Object, d3 As Object, d4 As Object, i%, derlig&, j&, v1#, v2#, v3#, v4#, x$
a = Array("BP", "BCP", "CDN") 'feuilles à consolider
annee1 = [E3]: annee2 = [G3]
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
Set d3 = CreateObject("Scripting.Dictionary")
Set d4 = CreateObject("Scripting.Dictionary")
d1.CompareMode = vbTextCompare 'la casse est ignorée
d2.CompareMode = vbTextCompare
d3.CompareMode = vbTextCompare
d4.CompareMode = vbTextCompare
For i = 0 To UBound(a)
    With Sheets(a(i))
        derlig = .Range("E" & .Rows.Count).End(xlUp).Row
        For j = 6 To derlig
            If IsDate(.Cells(j, 2)) Then 'sécutité
                v1 = 0: v2 = 0: v3 = 0: v4 = 0
                If IsNumeric(.Cells(j, 7)) Then 'sécurité
                    If Year(.Cells(j, 2)) = annee1 Then
                        If .Cells(j, 7) > 0 Then v1 = .Cells(j, 7) Else v2 = .Cells(j, 7)
                    ElseIf Year(.Cells(j, 2)) = annee2 Then
                        If .Cells(j, 7) > 0 Then v3 = .Cells(j, 7) Else v4 = .Cells(j, 7)
                    End If
                End If
                x = .Cells(j, 5) & Chr(1) & .Cells(j, 6)
                d1(x) = d1(x) + v1: d2(x) = d2(x) + v2
                d3(x) = d3(x) + v3: d4(x) = d4(x) + v4
            End If
        Next j
    End With
Next i
Application.ScreenUpdating = False
Range("C5:H" & Rows.Count).ClearContents
If d1.Count = 0 Then Exit Sub 'sécurité
[C5].Resize(d1.Count) = Application.Transpose(d1.keys) 'Transpose est limitée à 65536 lignes
[C5].Resize(d1.Count).TextToColumns [C5], xlDelimited, Other:=True, OtherChar:=Chr(1) 'commande Convertir
[E5].Resize(d1.Count) = Application.Transpose(d1.items)
[F5].Resize(d2.Count) = Application.Transpose(d2.items)
[G5].Resize(d3.Count) = Application.Transpose(d3.items)
[H5].Resize(d4.Count) = Application.Transpose(d4.items)
[C5].Resize(d1.Count, 6).Sort [C5], xlAscending, [D5], Header:=xlNo 'tri
Columns("C:H").AutoFit 'ajustement largeur
End Sub
Fichier (2).

A+
 

Pièces jointes

  • Banques 2017(2).xlsm
    69.8 KB · Affichages: 37
Vote positif 0 Vote négatif
gds77

gds77

XLDnaute Occasionnel
Sur mon fichier d'origine, les cellules en colonne "E" ne sont pas toutes sous le même format (trop compliqué pour l'analyse). Pour simplifier, Est il possible donc de supprimer cette colonne et de n'afficher que les catégories.

Encore Merci:)
 
Vote positif 0 Vote négatif
gds77

gds77

XLDnaute Occasionnel
Merci Sousou, en effet ton TCD est une bonne solution à mon problème, mais je ne retrouve pas le bon résultat entre Débit - Crédit et Résultat sur le tri fait par catégorie.
exemple : BFA 2017 en débit 283 €- en Crédit 0€ - et résultat 40431 €.
de plus je dois afficher les année 2017 et 2018 en colonne et pas en ligne.
 
Vote positif 0 Vote négatif

Discussions similaires

B
  • Question
XL 2013 Transposition de filtres multiples sur plusieurs feuilles
Réponses
11
Affichages
250
BIROULIG
B
S
  • Question
Microsoft 365 Tableau nuage de points avec plusieurs données
Réponses
11
Affichages
415
alexga78
A
P
  • Question
XL 2019 Bloquer la saisie dans une colonne sous condition
Réponses
1
Affichages
188
sylvanu
sylvanu
A
  • Question
XL 2019 Canva sur Excel
Réponses
7
Affichages
713
Gégé-45550
Gégé-45550
A
  • Question
XL 2019 recherche & consolidation
Réponses
4
Affichages
476
Alex78
A
H
  • Question
Autres mise a jour automatique feuille excel vba
Réponses
2
Affichages
288
HANN
H
king76
  • Question
XL 2016 Calculer des données sur plusieurs colonnes
Réponses
7
Affichages
499
king76
king76
F
  • Question
Microsoft 365 Regrouper certaines colonnes de plusieurs feuilles en une seule
Réponses
0
Affichages
639
flo33200
F
C
  • Résolu(e)
Microsoft 365 Amélioration Macro.
Réponses
7
Affichages
321
crazy
C
G
  • Résolu(e)
Microsoft 365 Formule avec pour référence le résultat d'une autre formule pour indiquer la colonne de référence
Réponses
1
Affichages
407
JHA
J

Membres actuellement en ligne

9 membres et 103 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 203
Messages
2 086 191
Membres
103 152
dernier inscrit
Karibu