XL 2016 Structuration de tableaux pour traitement des données

[Chubby]

Bonjour le forum,

Je reçois régulièrement un tableau reprenant certaines données et traitements de celles-ci. L'inconvénient est qu'il fait quelques colonnes pour ne pas dire une centaine et de ce fait impossible d'en visualiser l'essentiel. Il comporte différents critères qui l'alourdissent considérablement et je sais pas trop comment je pourrais alléger sa structure sinon en faisant un tableau par an ... même là c'est lourd. Le tableau est en PJ.
Auriez vous des idées à me donner de sorte que je puisse ensuite traiter les données facilement?
D'avance un grand merci.

 

[job75]

Bonsoir Chubby,

Le tableau que vous nous montrez est tout à fait digeste.

Sinon supprimez les colonnes qui ne vous intéressent pas.

A+

 

[Chubby]

Bonsoir Job75, bonsoir les zotres,

Je te remercie de ton message.
Le tableau comporte presque 100 colonnes avec des analyses pas toujours faciles à lire d’autant plus qu’une autre personne y ajoute les siennes dans un coin.
En fait je suis souvent moi même confronté à ce genre de tableau avec grand nombre d’entrées.

 

[TooFatBoy]

Bonjour,

Il comporte différents critères qui l'alourdissent considérablement et je sais pas trop comment je pourrais alléger sa structure sinon en faisant un tableau par an ... même là c'est lourd. Le tableau est en PJ.

Je crois que tu devrais commencer par faire une mise en forme cohérente, permettant de mieux faire ressortir les différents groupes informations.

Ensuite tu peux figer les volets, mais ça c'est juste utile lors du déplacement dans le tableau.

Tu pourrais aussi simplifier les formules de la ligne "cumul premiers mois", en remplaçcant

=F6+F7+F8+F9+F10+F11+F12+F13+F14+F15

par

=SOMME(F6:F15)

Exemple en pj.

 

[Chubby]

Bonjour TooFatBoy, bonjour le forum,

Je te remercie de ton intérêt pour mon post. Bien sûr les somme() sont préférables à cette liste de ++++.
A la réflexion je pense que je vais préparer un onglet par nature des ventes (grossistes, GMS, CHR ...) avec millésimes et nbre de cols, nbre de commandes ... en colonne et les mois en ligne.
Un onglet récapitulatif reprenant différentes années à comparer fera le boulot.
Merci en tous cas pour votre intérêt à ma problématique.

 

[chris]

Bonjour

Retraitement par PowerQuery + TCD

 

[job75]

Bonjour Chubby, TooFatBoy, chris, le forum,

Power Query paraît incontournable pour traiter le tableau.

Pour des tâches ponctuelles on peut cependant utiliser VBA.

Par exemple pour filtrer les données d'une année avec la feuille "Filtre annuel" :

Private Sub Worksheet_Activate()
Dim An, c As Range, deb As Range, n%
An = Val(Application.InputBox("Entrez l'année :"))
With Feuil1
    If Application.CountIf(.Rows(3), An) = 0 Then Exit Sub
    Application.ScreenUpdating = False
    Cells.Delete 'RAZ
    .Columns(1).Copy [A1]
    Set deb = [B1]
    For Each c In .Rows(3).SpecialCells(xlCellTypeConstants)
        If c = An Then
            n = c.MergeArea.Columns.Count
            c.MergeArea.EntireColumn.Copy deb
            deb(2) = c(0).MergeArea(1)
            With deb(2).Resize(, n)
                .HorizontalAlignment = xlCenterAcrossSelection
                .Interior.Color = vbCyan
                .BorderAround Weight:=xlThin 'pourtour
            End With
            Set deb = deb.Offset(, n)
        End If
    Next
End With
Rows("20:" & Rows.Count).Delete 'adapter au besoin
End Sub

A+

 

[Chubby]

Bonjour

Retraitement par PowerQuery + TCD

Bonjour Chris, le forum

Voilà qui est pour le moins clair et bien présenté. Des lignes "d'instructions" que je me dois de fouiller pour les comprendre.
D'ailleurs à ce propos je n'arrive pas à me servir de ton PWQ, la source data est inconnue.
Je m'aperçois que je ne t'ai pas remercier pour ce super travail.

 

[Chubby]

Bonsoir Job, le forum,

Merci pour ta proposition. J'avoue être assez réticent avec les macro ... certainement car je ne les comprends pas.
En fait je ne vois pas la possibilité de faire des comparaisons entre années.
Merci en tout cas.

 

[chris]

RE

D'ailleurs à ce propos je n'arrive pas à me servir de ton PWQ, la source data est inconnue.
Je m'aperçois que je ne t'ai pas remercier pour ce super travail.

Dans le fichier que j'ai transmis, cela fonctionne ?

Si tu copies la requête dans un autre il faut au préalable créer la plage nommée Data par formule comme dans mon fichier (voir gestionnaire de noms)...

 

[job75]

En fait je ne vois pas la possibilité de faire des comparaisons entre années.

Mais si bien sûr, il suffit d'adapter légèrement ma macro précédente :

Private Sub Worksheet_Activate()
Dim An, a, c As Range, deb As Range, n%
An = Application.InputBox("Entrez les années séparées par un espace :")
If An = False Then Exit Sub
Application.ScreenUpdating = False
Cells.Delete 'RAZ
With Feuil1
    For Each a In Split(An)
        If Application.CountIf(.Rows(3), Val(a)) Then
            .Columns(1).Copy [A1]
            If deb Is Nothing Then Set deb = [B1]
            For Each c In .Rows(3).SpecialCells(xlCellTypeConstants)
                If c = Val(a) Then
                    n = c.MergeArea.Columns.Count
                    c.MergeArea.EntireColumn.Copy deb
                    deb(2) = c(0).MergeArea(1)
                    With deb(2).Resize(, n)
                        .HorizontalAlignment = xlCenterAcrossSelection
                        .Interior.Color = vbCyan
                        .BorderAround Weight:=xlThin 'pourtour
                    End With
                    Set deb = deb.Offset(, n)
                End If
            Next c
        End If
    Next a
End With
Rows("20:" & Rows.Count).Delete 'adapter au besoin
End Sub

 

[job75]

Bonjour Chubby, le forum,

Pour pouvoir mieux comparer il vaut mieux grouper les "GMS" ensemble à droite :

Private Sub Worksheet_Activate()
Dim An, a, c As Range, deb As Range, n%, dercol%, P As Range
An = Application.InputBox("Entrez les années séparées par un espace :")
If An = False Then Exit Sub
Application.ScreenUpdating = False
Cells.Delete 'RAZ
With Feuil1
    For Each a In Split(An)
        If Application.CountIf(.Rows(3), Val(a)) Then
            .Columns(1).Copy [A1]
            If deb Is Nothing Then Set deb = [B1]
            For Each c In .Rows(3).SpecialCells(xlCellTypeConstants)
                If c = Val(a) Then
                    n = c.MergeArea.Columns.Count
                    c.MergeArea.EntireColumn.Copy deb
                    deb(2) = c(0).MergeArea(1)
                    With deb(2).Resize(, n)
                        .HorizontalAlignment = xlCenterAcrossSelection
                        .Interior.Color = vbCyan
                        .BorderAround Weight:=xlThin 'pourtour
                    End With
                    Set deb = deb.Offset(, n)
                End If
            Next c
        End If
    Next a
End With
'---place GMS à droite---
dercol = UsedRange.Columns.Count
For n = 1 To dercol
    If Cells(2, n) = "GMS" Then Set P = Union(IIf(P Is Nothing, Cells(3, n).MergeArea.EntireColumn, P), Cells(3, n).MergeArea.EntireColumn)
Next
If Not P Is Nothing Then
    P.Copy Columns(dercol + 1)
    P.Delete
End If
Rows("20:" & Rows.Count).Delete 'adapter au besoin
End Sub

A+

 

[Chubby]

RE

Dans le fichier que j'ai transmis, cela fonctionne ?

Si tu copies la requête dans un autre il faut au préalable créer la plage nommée Data par formule comme dans mon fichier (voir gestionnaire de noms)...

Bonsoir Chris, bonsoir le forum,

Alors ça fonctionne ou du moins je peux sélectionner via le segment les années. En revanche le lien avec la plage nommée "data" est, selon un message d'erreur introuvable.
Je dois contourner l'obstacle en passant par le chemin que j'ai recopié sous forme de saisies d'écran.

Autre chose comment peut on copier la requête pour se servir de ton travail sur un autre fichier?
Un grand merci à toi

 

[chris]

RE

Dans le classeur que j'ai transmis je n'ai pas ce message d'erreur (testé en téléchargeant le fichier placé ici, sur un autre PC tournant avec Excel 2010).

Data étant une plage nommée dans le classeur je ne comprends pas ton histoire de chemin

Je dois contourner l'obstacle en passant par le chemin que j'ai recopié sous forme de saisies d'écran.

Si dans ton classeur tu définis une plage Data dans le gestionnaire de noms, tu dois respecter le casse car PowerQuery est sensible à la casse

Une fois cette plage définie il suffit d'afficher le voler des requêtes dans chacun des fichiers (Données, Requêtes et connexions) et de copier de l'un à l'autre la requête qui y est listée.
Je ne sais plus si sur 2016 si le volet existait. Si non, ouvrir PowerQuery dans mon fichier, copier la requête, fermer PowerQuery, l'ouvrir dans l'autre fichier et copier la requête.