Budget / compilation de certaines lignes et extraction vers un autre onglet

GADENSEB

GADENSEB

XLDnaute Impliqué
Bonjour Le Forum,
Je reviens avec mon fichier de budget personnel.

J'ai pas eu de succès sur dévelloppez.com, comme je connais votre talent, j'essaye ici aussi !

Là je souhaite faire une opération un peu compliquée :

Je vais essayer de décrire plus en détail mon besoin :
Je souhaite faire l’opération d’analyse (comme un TCD) de toutes LIGNE (Onglet « COMPTES », Colonne I)
En faisant la différence pour REEL – BUDGET (calcul sur la colonne R)

Les données se trouvant dans l'onglet COMPTES dans les cellule colonnes A à T sur environ 2000 lignes actuellement, le nombre de lignes augmentant au fur et a mesure du temps.

Selon les critères suivants :
Pour les données REEL (il y a une zone de critères appelée : AreacriteraReel, dans l’onglet PARAMETRES)
Filtres 1.PNG

Pour les données BUDGET (il y a une zone de critères appelée : Areacriterabudget, dans l’onglet PARAMETRES)
Filtres 2.PNG


Pour Exemples :

Les calculs se font à Fin.mois-1 donc comme on est le 26/01/2015 -> les calculs se font jusqu’à fin Décembre 2015 (31/12/2015)
Les calculs se font sur la Colonne R « DEBITCREDIT » de l’onglet « COMPTES »)
Le cumul se fait pour chaque item de la colonne I « LIGNE »

Pour la ligne « COURSES » :

Le total REEL se monte à 436.95 €
Le total BUDGET se monte à 405€
Soit une Différence de + 31.95 €

Pour la ligne « INTERNET » :

Le total REEL se monte à 30 €
Le total BUDGET se monte à 40
Soit une Différence de - 10 €

--> Ce qui m’intéresse c’est tous les calculs qui font apparaitre une différence (+ ou-) différente de 0
--> Et extraire toutes ces LIGNE dans l’onglet INTERFACE sur l’emplacement I7 à J…..

EXTRACT.PNG

J'espére que j'ai était clair sur mon besoin

si qqn à une idée ...



Bonne journée
Seb
 

Pièces jointes

  • BUDGET - TEST.zip
    242.2 KB · Affichages: 46
  • BUDGET - TEST.zip
    242.2 KB · Affichages: 53
Trier par date Trier par votes
Paf

Paf

XLDnaute Barbatruc
Re : Budget / compilation de certaines lignes et extraction vers un autre onglet

Bonjour

Si j'ai bien tout compris (?) , un essai par macro:

Code: 
Sub ListDifference()
 Dim MonTab, Tablo, TabTemp, i As Long
 Dim DicoList
 Set DicoList = CreateObject("Scripting.Dictionary")

 With Worksheets("PARAMETRES")
 Tablo = .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)
 End With
 For i = LBound(Tablo) To UBound(Tablo)
     DicoList(Tablo(i, 1)) = 0
 Next

 datefin = DateSerial(Year(Date), Month(Date), 0) 'fin de mois du mois précédent
 With Worksheets("COMPTES")
 MonTab = .Range("A2:S" & .Range("A" & Rows.Count).End(xlUp).Row)
 End With
 For i = LBound(MonTab) To UBound(MonTab)
    If MonTab(i, 2) <= datefin Then
        If MonTab(i, 5) = "BUDGET" Then DicoList(MonTab(i, 9)) = CDbl(DicoList(MonTab(i, 9))) - CDbl(MonTab(i, 18))
        If MonTab(i, 5) = "REEL" Then DicoList(MonTab(i, 9)) = CDbl(DicoList(MonTab(i, 9))) + CDbl(MonTab(i, 18))
    End If
 Next

 For Each clé In DicoList.keys
    If DicoList(clé) = 0 Then DicoList.Remove (clé)
 Next
 With Worksheets("INTERFACE")
 TabTemp = Application.Transpose(Array(DicoList.keys, DicoList.Items))
 .Range("A8").Resize(UBound(TabTemp), 2) = TabTemp 'emplacement à adapter
 End With

End Sub

en additionnant les REEL et soustrayant les BUDGET j'obtiens
pour la ligne « COURSES » : une Différence de - 31.95
pour la ligne « INTERNET » : une Différence de + 10

A+
 
Vote positif 0 Vote négatif
GADENSEB

GADENSEB

XLDnaute Impliqué
Re : Budget / compilation de certaines lignes et extraction vers un autre onglet

Me revoila !

J'adore ton code, simple et efficace : Quel talent !



J'ai une modif
J'ai inversé les + et le - sur les sur les CDBL (plus simple pour moi au niveau lecture) ;-)
Code: 
        If MonTab(i, 5) = "BUDGET" Then DicoList(MonTab(i, 9)) = CDbl(DicoList(MonTab(i, 9))) - CDbl(MonTab(i, 18))
        If MonTab(i, 5) = "REEL" Then DicoList(MonTab(i, 9)) = CDbl(DicoList(MonTab(i, 9))) + CDbl(MonTab(i, 18))


J'ai fait une modif
J'ai remplacé A8 par I8 car cela s'adapte mieux à mon besoin ;-)
Code: 
With Worksheets("INTERFACE")
 TabTemp = Application.Transpose(Array(DicoList.keys, DicoList.Items))
 .Range("I8").Resize(UBound(TabTemp), 2) = TabTemp 'emplacement à adapter
 End With

End Sub


---->J'ai rajouter un niveau de filtre sur la Colonne F sur le mot "COURANT"

Code: 
 For i = LBound(MonTab) To UBound(MonTab)
    If MonTab(i, 2) <= datefin Then
    If MonTab(i, 6) = "COURANT" Then
        If MonTab(i, 5) = "BUDGET" Then DicoList(MonTab(i, 9)) = CDbl(DicoList(MonTab(i, 9))) + CDbl(MonTab(i, 18))
        If MonTab(i, 5) = "REEL" Then DicoList(MonTab(i, 9)) = CDbl(DicoList(MonTab(i, 9))) - CDbl(MonTab(i, 18))
    End If
    End If
 Next

encore merci tout est parfait !!!




bonne am, A bientôt

Seb
 
Dernière édition:
Vote positif 0 Vote négatif
GADENSEB

GADENSEB

XLDnaute Impliqué
Re : Budget / compilation de certaines lignes et extraction vers un autre onglet

Re moi

Je vais jouer mon pénible mais peut-on classer par ordre alphabetique les données qui sont extraites ?

Je suppose qu'il faut faire un classement sur MonTab(i, 5) !

Bonne am
Seb
 
Vote positif 0 Vote négatif
Paf

Paf

XLDnaute Barbatruc
Re : Budget / compilation de certaines lignes et extraction vers un autre onglet

re,

les données extraites sont dans le tableau TabTemp, c'est donc lui qu'il faut trier. Ici avec un basique tri à bulle pas très performant mais vu le nombre de lignes à trier ce n'est pas sensible.

la fin du code modifiée pour trier les différences, si elles existent, dans l'ordre alphabétique , et si elles n'existent pas on affiche "Aucune différence" :

Code: 
 For Each clé In DicoList.keys
    If DicoList(clé) = 0 Then DicoList.Remove (clé)
 Next
 With Worksheets("INTERFACE")
 If DicoList.Count > 0 Then  ' s'il existe des lignes avec différence
    TabTemp = Application.Transpose(Array(DicoList.keys, DicoList.Items))
    Do
    For i = LBound(TabTemp) To UBound(TabTemp) - 1
        Trié = True
        If TabTemp(i, 1) > TabTemp(i + 1, 1) Then
            tmp = TabTemp(i, 1)
            tmp2 = TabTemp(i, 2)
            TabTemp(i, 1) = TabTemp(i + 1, 1)
            TabTemp(i, 2) = TabTemp(i + 1, 2)
            TabTemp(i + 1, 1) = tmp
            TabTemp(i + 1, 2) = tmp2
            Trié = False
        End If
    Next i
    Loop Until Trié = True
    .Range("I8").Resize(UBound(TabTemp), 2) = TabTemp 'emplacement à adapter
 Else
    .Range("I8") = "Aucune différence"
 End If
 End With

A+
 
Vote positif 0 Vote négatif
GADENSEB

GADENSEB

XLDnaute Impliqué
Re : Budget / compilation de certaines lignes et extraction vers un autre onglet

Hello

Merci !

Le tri ne s’opère pas ...

J'ai replacé tout le code définitif, si tu vois qqc je suis preneur.


Merci bonne journée


Code: 
Sub ListDifference()
 Dim MonTab, Tablo, TabTemp, i As Long
 Dim DicoList
 Set DicoList = CreateObject("Scripting.Dictionary")

With Worksheets("INTERFACE")
Range("I8:K" & .Range("J" & Rows.Count).End(xlUp).Row).ClearContents
End With

 With Worksheets("PARAMETRES")
 Tablo = .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row)
 End With
 For i = LBound(Tablo) To UBound(Tablo)
     DicoList(Tablo(i, 1)) = 0
 Next

 datefin = DateSerial(Year(Date), Month(Date), 0) 'fin de mois du mois précédent
 With Worksheets("COMPTES")
 MonTab = .Range("A2:S" & .Range("A" & Rows.Count).End(xlUp).Row)
 End With
 For i = LBound(MonTab) To UBound(MonTab)
    If MonTab(i, 2) <= datefin Then
    If MonTab(i, 6) = "COURANT" And MonTab(i, 8) <> "RESERVES" Then
        If MonTab(i, 5) = "BUDGET" Then DicoList(MonTab(i, 9)) = CDbl(DicoList(MonTab(i, 9))) + CDbl(MonTab(i, 18))
        If MonTab(i, 5) = "REEL" Then DicoList(MonTab(i, 9)) = CDbl(DicoList(MonTab(i, 9))) - CDbl(MonTab(i, 18))
    End If
    End If
 Next


For Each clé In DicoList.keys
    If DicoList(clé) = 0 Then DicoList.Remove (clé)
 Next
 With Worksheets("INTERFACE")
 If DicoList.Count > 0 Then  ' s'il existe des lignes avec différence
    TabTemp = Application.Transpose(Array(DicoList.keys, DicoList.Items))
    Do
    For i = LBound(TabTemp) To UBound(TabTemp) - 1
        Trié = True
        If TabTemp(i, 1) > TabTemp(i + 1, 1) Then
            tmp = TabTemp(i, 1)
            tmp2 = TabTemp(i, 2)
            TabTemp(i, 1) = TabTemp(i + 1, 1)
            TabTemp(i, 2) = TabTemp(i + 1, 2)
            TabTemp(i + 1, 1) = tmp
            TabTemp(i + 1, 2) = tmp2
            Trié = False
        End If
    Next i
    Loop Until Trié = True
    .Range("I8").Resize(UBound(TabTemp), 2) = TabTemp 'emplacement à adapter
 Else
    .Range("I8") = "Aucune différence"
 End If
 End With


End Sub
 
Vote positif 0 Vote négatif
Paf

Paf

XLDnaute Barbatruc
Re : Budget / compilation de certaines lignes et extraction vers un autre onglet

re,

Rrrrrr ! Ben oui ... !!!

Juste déplacer la ligne
Code: 
Trié = True
juste avant la ligne
Code: 
For i = LBound(TabTemp) To UBound(TabTemp) - 1


A+
 
Vote positif 0 Vote négatif
GADENSEB

GADENSEB

XLDnaute Impliqué
Re : Budget / compilation de certaines lignes et extraction vers un autre onglet

Re !!
Ton code est vraiment au top !!!
Il me manque qu'un truc trier le range

MonTab = .Range("A2:S" & .Range("A" & Rows.Count).End(xlUp).Row)

Par la colonne B (dates), en ascendant.

J'ai testé plusieurs trucs différents mais a chaque fois je fou en l'air ma Bdd.... tu aurais une idée?

Bonne journée
Seb
 
Vote positif 0 Vote négatif
Paf

Paf

XLDnaute Barbatruc
Re : Budget / compilation de certaines lignes et extraction vers un autre onglet

Re bonjour,

je ne comprend pas bien le souci !

a quoi peut bien servir de trier le 'range' MonTab = .Range("A2:S" & .Range("A" & Rows.Count).End(xlUp).Row)?
cette instruction permet juste de 'monter' en mémoire les données de la feuille pour gagner en temps d'exécution, elle n'affecte pas la base de données;

Pour trier la base de données une solution consiste à y appliquer une filtre automatique, puis sur la colonne B choisir Tri croissant.

A+
 
Vote positif 0 Vote négatif
GADENSEB

GADENSEB

XLDnaute Impliqué
Re : Budget / compilation de certaines lignes et extraction vers un autre onglet

Hello !!!
Mes excuses..... je me suis planté...
C'est pas sur ce code que je faire un tri par date ....
sorry

En tt cas merci pour le code !


Bonne journée
Seb
 
Vote positif 0 Vote négatif

Discussions similaires

C
  • Question
Mettre une cellule en jaune quand le contenue correspond a une des valeurs d'une colonne d'un autre onglet
Réponses
9
Affichages
189
job75
job75
P
  • Question
Power Pivot Filtrer une somme en fonction de filtres sur un graphique
Réponses
2
Affichages
368
Pierre111
P
cathodique
  • Résolu(e)
XL 2010 tableau de comptage sans doublon
2 3
Réponses
44
Affichages
1 K
cathodique
cathodique
A
  • Question
XL 2016 Améliorations pour un tableau de budget : Vos idées ?
Réponses
10
Affichages
652
amgue
A
O
  • Question
XL 2013 Recopier automatique des valeurs d'un onglet vers un autre onglet selon critère
Réponses
2
Affichages
429
olivieroma
O
M
  • Question
XL 2019 Copier des lignes les unes a la suite des autres dans une autre feuille sous conditions et seulement certaines colonnes
Réponses
7
Affichages
571
MGH
M
JoBar57
  • Question
XL 2021 Collecter des données et les restituer après mise en forme dans un autre onglet
Réponses
8
Affichages
574
JoBar57
JoBar57
7
  • Question
Microsoft 365 Tableau de Géolocalisation de vidéos de drone avec lien vers Google Earth
2
Réponses
15
Affichages
555
sylvanu
sylvanu
L
  • Question
Microsoft 365 TCD et référence de cellules
Réponses
7
Affichages
346
alexga78
A
W
  • Résolu(e)
XL 2016 Gestion de liste transposée dans une autre feuille avec décalage d'une colonne et exclusion de certains éléments
Réponses
9
Affichages
364
Webperegrino
W

Membres actuellement en ligne

Aucun membre en ligne actuellement.
1 membres et 45 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
314 628
Messages
2 111 337
Membres
111 105
dernier inscrit
Joffrette