Consolidation de lignes Excel via VBA
Bonjour,
Ayant un peu fouillé sur le forum et sur Internet en général, je n'arrive décidément pas à trouver une solution adéquate à mon problème.
J'ai un tableau avec deux colonnes :
- date ;
- quantité.
Voilà un morceau simplifié du fichier :
ligne 1 : date = 01/01/2016 - quantité = 10 ;
ligne 2 : date = 01/01/2016 - quantité = 15 ;
ligne 3 : date = 01/01/2016 - quantité = 5 ;
ligne 4 : date = 02/01/2016 - quantité = 10 ;
ligne 5 : date = 02/01/2016 - quantité = 25 ;
ligne 6 : date = 03/01/2016 - quantité = 20 ;
ligne 7 : date = 04/01/2016 - quantité = 5 ;
ligne 8 : date = 04/01/2016 - quantité = 15 ;
etc...
Je voudrais pouvoir consolider les lignes libellées à la même date et sommer les quantités correspondantes, de telle façon qu'on obtienne :
ligne 1 : date = 01/01/2016 - quantité = 30 ;
ligne 2 : date = 02/01/2016 - quantité = 35 ;
ligne 3 : date = 03/01/2016 - quantité = 20 ;
ligne 4 : date = 04/01/2016 - quantité = 20.
Tout ceci dans un petit code VBA, merci par avance pour vos pistes :)
1 pièce(s) jointe(s)
Programmons en VBA en pensant à Excel
Bonjour,
Certes l'utilisation des dictionnaires, Collections, etc. est une belle performance de programmation mais pourquoi faire compliqué quand on peut faire simple.
La question porte sur la consolidation et....... Excel offre une fonctionnalité de consolidation ([Données] - Outils de données - Consolidation) et j'ai plutôt tendance à utiliser en priorité ce qu'excel m'offre nativement car chaque outil ou fonctionnalité d'excel à sa méthode en VBA.
Scénario
Imaginons que la plage de données soit en A1:B21 avec en colonne A les dates et en colonne B les quantités, la première ligne contenant les étiquettes de colonnes Nous souhaitons donc consolider ces données à partir de la cellule H1 de la même feuille.
Que faire ?
Utilisons l'enregistreur de macros
Etapes à suivre
- Se placer sur la cellule H1
- Démarrer l'enregistreur de macros
- Cliquer sur le bouton Consolider
- Dans la boîte de dialogue Consolider
- sélectionner la base de données (A1:B21) dans la zone Référence et cliquer sur le bouton Ajouter
- cocher les options Ligne du haut et Colonne de gauche
- Formater la première colonne des données consolidées
- Arrêter l'enregistreur de macros
Illustration
Pièce jointe 206350
Code généré par l'enregistreur
Code:
1
2
3
4
5
6
7
8
9
10
| Sub Consolidation()
'
' Consolidation des données
'
Selection.Consolidate Sources:= _
"'\\VBOXSVR\Partage\Forums\[160407 dvp Consolidation.xlsm]Feuil1'!R1C1:R21C2", _
Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
Range("H2:H9").Select
Selection.NumberFormat = "m/d/yyyy"
End Sub |
Que reste-t-il à faire ?
Adapter le code généré par l'enregistreur de macros pour en faire une procédure réutilisable dans n'importe quelle situation.
C'est à dire prévoir les arguments Source, Cible, etc. et surtout rendre dynamique ces plages en supprimant les constantes par l'utilisation des propriétés CurrentRegion, Resize, etc.
Code de la procédure remaniée
Voici le code de la procédure permettant de l'utilisée d'une façon transparente.
Cette procédure étant une fonction, elle peut être utilisée comme une procédure Sub ou comme une fonction.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| Function ConsolidateByCategory(rngSources As Range, rngTarget As Range, Optional xFunction As Integer = xlSum) As Range
' Fonction consolidant par catégorie
' Renvoie un objet range (plage cible)
' Source obj Range : Plage source
' Target objRange : Cellule de destination
' [xFunction] Integer : Fonction de synthèse, Somme par défaut(xlSum)
' Peut-être une des constantes XlConsolidationFunction
Dim CompleteAdressR1C1 As String, Col As Integer
CompleteAdressR1C1 = rngSources.Address(ReferenceStyle:=True, external:=True)
rngTarget.Consolidate CompleteAdressR1C1, xFunction, Toprow:=True, LeftColumn:=True
' Formatage nombre de la cible comme la source
Set ConsolidateByCategory = rngTarget.CurrentRegion
With ConsolidateByCategory
For Col = 1 To rngSources.Columns.Count
.Offset(1, Col - 1).Resize(.Rows.Count).NumberFormat = rngSources.Cells(2, Col).NumberFormat
Next
End With
End Function |
Exemple d'utilisation de cette procédure
Exemple 1
Code:
1
2
3
4
5
6
| Sub ConsolidateScenario_1()
' Consolidation sur la même feuille
With ThisWorkbook.Worksheets("Feuil1")
ConsolidateByCategory .Range("A1").CurrentRegion, .Range("H1")
End With
End Sub |
Exemple 2
Code:
1
2
3
4
5
6
7
8
9
| Sub ConsolidateScenario_2()
' Consolidation sur une autre feuille que la source
Dim rngSource As Range, rngTarget As Range
With ThisWorkbook
Set rngSource = .Worksheets("Feuil1").Range("A1").CurrentRegion
Set rngTarget = .Worksheets("Feuil2").Range("A1")
End With
ConsolidateByCategory rngSource, rngTarget
End Sub |
Exemple 3
Utilisation de la procédure en renvoyant l'adresse de la plage cible.
Code:
1
2
3
4
5
6
7
8
9
| Sub ConsolidateScenario_3()
' Consolidation sur une autre feuille que la source en utilisant la procédure comme une fonction
Dim rngSource As Range, rngTarget As Range
With ThisWorkbook
Set rngSource = .Worksheets("Feuil1").Range("A1").CurrentRegion
Set rngTarget = .Worksheets("Feuil2").Range("A1")
End With
MsgBox "Plage cible " & ConsolidateByCategory(rngSource, rngTarget).Address(external:=True)
End Sub |
Exemple 4
Utilisation du 3ème argument facultatif en faisant une moyenne
Code:
1
2
3
4
5
6
7
8
9
| Sub ConsolidateScenario_4()
' Consolidation sur une autre feuille que la source avec moyenne
Dim rngSource As Range, rngTarget As Range
With ThisWorkbook
Set rngSource = .Worksheets("Feuil1").Range("A1").CurrentRegion
Set rngTarget = .Worksheets("Feuil2").Range("H1")
End With
ConsolidateByCategory rngSource, rngTarget, xlAverage
End Sub |
