Vous utilisez un navigateur obsolète. Il se peut que ce site ou d'autres sites Web ne s'affichent pas correctement. Vous devez le mettre à jour ou utiliser un navigateur alternatif.
Bonjour ou bonsoir à tous.
Je cherche une solution pour créer un tableau de consolidation de mes ventes annuelles.
Je vous mets en pièce jointe un petit fichier ou j'ai créer des ventes pour trois mois ainsi que la feuille de consolidation dans laquelle je souhaiterais avoir la liste de mes clients sans doublon triée et dans la colonne suivante la somme des montants des ventes pour ce même clients.
Merci par avance à toutes les personnes qui pourront m'aider.
En PJ, une solution par fonction personnalisée, qui répond à la demande (j'ai un écart par rapport à votre exemple, mais à priori, l'erreur provient de vous) :
VB:
Function magicSom(Nom As String)
Application.Volatile
Dim ws As Worksheet, Som As Double
For Each ws In ThisWorkbook.Worksheets
If ws.Range("A1") = "Nom du client" And ws.Name <> "Consolidation" Then
Som = Som + Application.WorksheetFunction.SumIf(ws.Range("A1:A1000"), Nom, ws.Range("B1:B1000"))
End If
Next
magicSom = Som
End Function
Voici une récap par power query (inclus dans votre version excel).
Par contre, nommez vos tableaux avec les noms de mois tels que excel les comprends : "Janvier Février Mars Avril Mai Juin Juillet Août Septembre Octobre Novembre Décembre" avec accent ou modifiez l'étape 'Source' de la requête en conséquence.
Merci beaucoup Softmama, mais lorsque j'ajoute le mois d'avril (et il faudra aller jusqu'en décembre il n'en tient pas compte ! Pourtant du peu que je comprenne du VBA tu as bien mis la condition : "pour chaque feuille contenant dans la cellule A1 le texte "Nom du client, sauf pour la feuille "Consolidation" !
P.S. : C'est bien moi qui ai fais une faute de calcul dans mon modèle LOL
Voici une récap par power query (inclus dans votre version excel).
Par contre, nommez vos tableaux avec les noms de mois tels que excel les comprends : "Janvier Février Mars Avril Mai Juin Juillet Août Septembre Octobre Novembre Décembre" avec accent ou modifiez l'étape 'Source' de la requête en conséquence.
Quand vous ajouterez un nouveau tableau mensuel, il ne vous restera qu'à actualiser la requête (click-droit dans une cellule du tableau -> Actualiser)
En effet, il y a un élément non pris en compte dans ma proposition.
En colonne A de la feuille consolidation, il faut que tous les vendeurs soient listés, il n'y a pas de mise à jour automatique. La fonction personnalisée donne le total des ventes pour un vendeur choisi. Si vous ne notez pas son nom, il n'y a pas de résultat. En notant Walter en colonne A de l'onglet Consolidation, son total s'affiche bien en colonne B.
Mais effectivement, ce n'est pas dans votre cas probablement le plus pertinent pour répondre à votre besoin.
J'ai donc modifié le fichier pour que l'ensemble des vendeurs soit toujours listé dans l'onglet consolidation.
Les calculs se mettent à jour à chaque activation de l'onglet Consolidation :
Dans le Module de feuille "Consolidation" :
VB:
Private Sub Worksheet_Activate()
Dim ws As Worksheet, Som As Double, Vendeurs(), SommeVentes(), c As Range, n As Integer, x As Integer, t As Integer, Trouvé As Boolean
n = 0
ReDim Vendeurs(1 To 1)
ReDim SommeVentes(1 To 1)
For Each ws In ThisWorkbook.Worksheets
If ws.Range("A1") = "Nom du client" And ws.Name <> "Consolidation" Then
Set c = ws.Range("A2")
Do While c <> ""
'Recherche si nouveau vendeur ?
Trouvé = False
For t = LBound(Vendeurs) To UBound(Vendeurs)
If Vendeurs(t) = c Then Trouvé = True: Exit For
Next
If Not Trouvé Then
'Nouveau vendeur, on l'ajoute au tableau
n = n + 1
ReDim Preserve Vendeurs(1 To n)
ReDim Preserve SommeVentes(1 To n)
Vendeurs(n) = c
SommeVentes(n) = 0
x = n
End If
'mise à jour des ventes faites pour ce vendeur
SommeVentes(x) = SommeVentes(x) + c.Offset(, 1)
'on passe à la vente suivante
Set c = c.Offset(1)
Loop
End If
'on passe au mois suivant
Next
'affichage des résultats
Feuil4.Range("A2:B100000").ClearContents
Feuil4.Range("A2").Resize(UBound(Vendeurs), 1) = Application.Transpose(Vendeurs())
Feuil4.Range("B2").Resize(UBound(Vendeurs), 1) = Application.Transpose(SommeVentes())
End Sub
En effet, il y a un élément non pris en compte dans ma proposition.
En colonne A de la feuille consolidation, il faut que tous les vendeurs soient listés, il n'y a pas de mise à jour automatique. La fonction personnalisée donne le total des ventes pour un vendeur choisi. Si vous ne notez pas son nom, il n'y a pas de résultat. En notant Walter en colonne A de l'onglet Consolidation, son total s'affiche bien en colonne B.
Mais effectivement, ce n'est pas dans votre cas probablement le plus pertinent pour répondre à votre besoin.
J'ai donc modifié le fichier pour que l'ensemble des vendeurs soit toujours listé dans l'onglet consolidation.
Les calculs se mettent à jour à chaque activation de l'onglet Consolidation :
Dans le Module de feuille "Consolidation" :
VB:
Private Sub Worksheet_Activate()
Dim ws As Worksheet, Som As Double, Vendeurs(), SommeVentes(), c As Range, n As Integer, x As Integer, t As Integer, Trouvé As Boolean
n = 0
ReDim Vendeurs(1 To 1)
ReDim SommeVentes(1 To 1)
For Each ws In ThisWorkbook.Worksheets
If ws.Range("A1") = "Nom du client" And ws.Name <> "Consolidation" Then
Set c = ws.Range("A2")
Do While c <> ""
'Recherche si nouveau vendeur ?
Trouvé = False
For t = LBound(Vendeurs) To UBound(Vendeurs)
If Vendeurs(t) = c Then Trouvé = True: Exit For
Next
If Not Trouvé Then
'Nouveau vendeur, on l'ajoute au tableau
n = n + 1
ReDim Preserve Vendeurs(1 To n)
ReDim Preserve SommeVentes(1 To n)
Vendeurs(n) = c
SommeVentes(n) = 0
x = n
End If
'mise à jour des ventes faites pour ce vendeur
SommeVentes(x) = SommeVentes(x) + c.Offset(, 1)
'on passe à la vente suivante
Set c = c.Offset(1)
Loop
End If
'on passe au mois suivant
Next
'affichage des résultats
Feuil4.Range("A2:B100000").ClearContents
Feuil4.Range("A2").Resize(UBound(Vendeurs), 1) = Application.Transpose(Vendeurs())
Feuil4.Range("B2").Resize(UBound(Vendeurs), 1) = Application.Transpose(SommeVentes())
End Sub
C'est du plus gênant effet...
Une petite erreur de variable : je l'appelle t en début de macro, puis elle se nomme x ensuite... donc effectivement les sommes sont erronées...
Désolé pour ce manque d'attention
Correctif ci dessous...
Voici un autre essai avec un TCD regroupant les douze mois, avec une macro permettant uniquement d'actualiser le TCD ainsi qu'un segment permettant le choix du mois à afficher les résultats..
C'est du plus gênant effet...
Une petite erreur de variable : je l'appelle t en début de macro, puis elle se nomme x ensuite... donc effectivement les sommes sont erronées...
Désolé pour ce manque d'attention
Correctif ci dessous...
Bonjour Softmana,
Effectivement, les additions fonctionnent parfaitement.
Mais serait-il possible (sans trop te déranger) de faire un tri par ordre alphabétique sur 'le nom des clients' juste après l'affichage ?
Avec cette petite correction, je pourrais corriger (et simplifier quelques un de mes fichiers Excel !
D'avance un tout grands merci à toi et à la collectivité qui passez du temps à aider les autres.
Une approche rapide qui devrait répondre au besoin, avec un ajout de tri en fin de procédure :
VB:
Private Sub Worksheet_Activate()
Dim ws As Worksheet, Som As Double, Vendeurs(), SommeVentes(), c As Range, n As Integer, x As Integer, t As Integer, Trouvé As Boolean, Flag as Boolean
n = 0
ReDim Vendeurs(1 To 1)
ReDim SommeVentes(1 To 1)
For Each ws In ThisWorkbook.Worksheets
If ws.Range("A1") = "Nom du client" And ws.Name <> "Consolidation" Then
Set c = ws.Range("A2")
Do While c <> ""
'Recherche si nouveau vendeur ?
Trouvé = False
For x = LBound(Vendeurs) To UBound(Vendeurs)
If Vendeurs(x) = c Then Trouvé = True: Exit For
Next
If Not Trouvé Then
'Nouveau vendeur, on l'ajoute au tableau
n = n + 1
ReDim Preserve Vendeurs(1 To n)
ReDim Preserve SommeVentes(1 To n)
Vendeurs(n) = c
SommeVentes(n) = 0
x = n
End If
'mise à jour des ventes faites pour ce vendeur
SommeVentes(x) = SommeVentes(x) + c.Offset(, 1)
'on passe à la vente suivante
Set c = c.Offset(1)
'on passe au mois suivant
Loop
End If
Next
'Tri par ordre alpha des résultats
1 Flag = False
For t = 1 To UBound(Vendeurs) - 1
If Vendeurs(t) > Vendeurs(t + 1) Then
temp = Vendeurs(t + 1): Vendeurs(t + 1) = Vendeurs(t): Vendeurs(t) = temp: temp = SommeVentes(t + 1): SommeVentes(t + 1) = SommeVentes(t): SommeVentes(t) = temp: flag = True
End If
Next
If flag Then GoTo 1 'Besoin de retrier ?
'affichage des résultats
Feuil4.Range("A2:B100000").ClearContents
Feuil4.Range("A2").Resize(UBound(Vendeurs), 1) = Application.Transpose(Vendeurs())
Feuil4.Range("B2").Resize(UBound(Vendeurs), 1) = Application.Transpose(SommeVentes())
End Sub
Bonjour Softmama,
Mille merci. Avec cette correction c'est parfaitement parfait !!! LOL
Je me m'est tout de suite à l'ouvrage pour la consolidation de certains de mes fichiers.
Merci aussi à ce site et ses participants qui nous permets de mieux comprendre Microsoft Excel !
P. S. : Ne faudrait il pas ajouter "Résolu' sur le titre et si 'OUI' comment le faire ?
Ce site utilise des cookies pour personnaliser le contenu, adapter votre expérience et vous garder connecté si vous vous enregistrez.
En continuant à utiliser ce site, vous consentez à notre utilisation de cookies.
