moyenne d'articles sur les 5 dernières années

[munetta]

Bonjour,
je suis magasinier dans le domaine de la construction,
j'ai 5 classeurs excel, 1 par année sur les articles achetés pendant cette période, j'ai a peu près 3000 articles différents /année, il y a des articles qui peuvent être présent sur 1 ou 2 années mais pas sur les autres.
Ma question est est ce que je peux croiser les 5 classeurs pour calculer la moyenne des articles afin de déterminer la quantité de chaques articles a avoir en stock, je peux le faire à la main mais cela vas me prendre un temps fou
Merci à toutes et tous

 

[Hasco]

je ne comprend pas quand tu dis de créer une requête qui associe les 2 premières ?

C'est déjà fait dans le fichier du post #27 la requête s'appelle "Tout"

puis j'aimerais ajouter mon fichier de 17-18,

Cliquez sur l'étape source de la requête "Périodes" (au pluriel)
Dans la barre de formule de l'éditeur de requête changez le -4 par -5 dans

= List.Transform({Date.Year(DateTime.FixedLocalNow())-4 .. Date.Year(DateTime.FixedLocalNow())}, each Text.End(Text.From(_),2) & "-" & Text.End(Text.From(_+1),2))

Pour avoir :

= List.Transform({Date.Year(DateTime.FixedLocalNow())-5 .. Date.Year(DateTime.FixedLocalNow())}, each
Text.End(Text.From(_),2) & "-" & Text.End(Text.From(_+1),2))

Puis validez.

 

[munetta]

je fais comme tu m'as dis mais je n'ai rien dans les requêtes, désolé je suis un peu abruti, je te laisse l'image

 

[Hasco]

re

Je ne sais pas sur quel fichier vous travaillez mais moi sur le fichier du post#27 j'ai ça :

Avant d'ouvrir l'éditeur de requête, vérifiez bien qu'en cellule B2 (nommée 'Dossier') de l'onglet Param, que le nom du répertoire qui apparaît est bien le bon. Sinon, faites recalculer la feuille ou le classeur.

Dans l'éditeur de requête dans l'onglet affichage cliquez sur le bouton (à gauche) "Paramètre d'une requête" pour que le volets à droite s'ouvre et que vous ayez accès aux différentes étapes des requêtes.

 

[Hasco]

Re,

Puisque le sommeil ne vient pas, je vous ai fait une version nouvelle. Et si vous voulez bien, nous partirons de celle-ci dans nos futurs échanges.

Pour la requête "Toutes périodes" la liste des fichiers est établit à partir de l'année en cours -5 juqu'à l'année en cours +1.

Si dans le dossier certains de ces fichiers attendus, n'existent pas (par exemple 22-23) faut-il en garder la trace dans les résultats (afficher une ligne de données nulles) ?

J'ai largement commenté chaque étape des requêtes. Laissez trainer le curseur de votre souris sur leur nom pour en avoir une description.
Dans power query, si une icone roue dentée est présente à droite du nom d'une étape, c'est que vous pouvez double-cliquer dessus pour ouvrir l'interface qui l'a créée sinon regardez en la définition dans la barre de formule de l'éditeur PQ

Cordialement

 

[job75]

Bonjour munetta, le forum,

Je n'ai pas suivi ce fil, j'ai juste vu ceci :

Ma question est est ce que je peux croiser les 5 classeurs pour calculer la moyenne des articles afin de déterminer la quantité de chaques articles a avoir en stock, je peux le faire à la main mais cela vas me prendre un temps fou

Voici une solution VBA qui traite les fichiers des posts #4 et #26 :

Sub Calcul()
Dim t, d As Object, chemin$, fichier$, titres(), coldeb%, nfich%, n%, tablo, i&, x$, p&, quant(), nn&
t = Timer
Set d = CreateObject("Scripting.Dictionary")
chemin = ThisWorkbook.Path & "\"
fichier = Dir(chemin & "*.xls*") '1er fichier du dossier
'---titres---
titres = Array("Article", "Quantités", "Nombre d'années", "Moyenne des quantités")
coldeb = UBound(titres) + 1
While fichier <> ""
    If fichier <> ThisWorkbook.Name Then
        ReDim Preserve titres(coldeb + nfich) 'base 0
        titres(coldeb + nfich) = fichier
        nfich = nfich + 1
    End If
    fichier = Dir
Wend
'---tableau des quantités---
fichier = Dir(chemin & "*.xls*") '1er fichier du dossier
Application.ScreenUpdating = False
While fichier <> ""
    If fichier <> ThisWorkbook.Name Then
        n = n + 1
        With Workbooks.Open(chemin & fichier) 'ouvre le fichier
            tablo = .Sheets(1).Cells(1).CurrentRegion.Resize(, 3) 'matrice, plus rapide
            For i = 2 To UBound(tablo)
                x = Trim(tablo(i, 1))
                If x <> "" Then
                    If Not d.exists(x) Then
                        p = p + 1
                        d(x) = p 'mémorise la position
                        ReDim Preserve quant(1 To nfich, 1 To p)
                    End If
                    nn = d(x)
                    quant(n, nn) = quant(n, nn) + tablo(i, 3)
                End If
            Next i
            .Close False 'ferme le fichier
        End With
    End If
    fichier = Dir 'fichier suivant
Wend
'---restitution---
With Feuil1 'CodeName
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    .Cells.ClearContents 'RAZ
    With .[A2] '1ère cellule de destination
        .Resize(, coldeb + nfich) = titres
        If n * p Then
            .Cells(2).Resize(p) = Application.Transpose(d.keys)
            .Cells(2, coldeb + 1).Resize(p, n) = Application.Transpose(quant)
            .Cells(2, 2).Resize(p) = "=SUM(RC[" & coldeb - 1 & "]:RC[" & coldeb + nfich - 2 & "])"
            .Cells(2, 3).Resize(p) = "=COUNT(RC[" & coldeb - 2 & "]:RC[" & coldeb + nfich - 3 & "])"
            .Cells(2, 4).Resize(p) = "=RC[-2]/RC[-1]"
            .Resize(p + 1, coldeb + n).Sort .Cells(1), xlAscending, Header:=xlYes 'tri sur les articles
        End If
    End With
    .Columns.ColumnWidth = 10.71
    .Columns.AutoFit 'ajustement largeurs
End With
Application.ScreenUpdating = True
MsgBox n & " fichiers et " & Format(p, "#,##0") & " articles traités en " & Format(Timer - t, "0.00 \sec")
End Sub

Tous les fichiers qui sont dans le même dossier que celui du fichier de la macro sont traités.

Vous pouvez mettre ceux que vous voulez.

A+

 

[Hasco]

Bonjours,

Tiens, je l'attendais celle-là...

 

[job75]

Je viens de simplifier le calcul du tableau titres (en base 0).

 

[job75]

Bien entendu, si on le désire, on peut supprimer les formules et les colonnes des fichiers :

.Resize(p + 1, coldeb) = .Resize(p + 1, coldeb).Value 'supprime les formules
.Cells(1, coldeb + 1).Resize(p + 1, n).Delete xlToLeft 'supprime les colonnes des fichiers
.Resize(p + 1, coldeb).Sort .Cells(1), xlAscending, Header:=xlYes 'tri sur les articles

 

[job75]

Avec le fichier (3) une CheckBox permet de choisir le type d'affichage, voyez la variable test

 

[job75]

Concernant la durée d'exécution chez moi ce n'est pas très stable.

Tantôt elle est de 1 seconde, tantôt elle tourne autour de 5 secondes.

 

[munetta]

Bonjour à toutes et tous,
super merci pour toutes vos astuces, je vais regarder cela cette semaine et si j'ai des questions je vous reviens
merci encore

 

[munetta]

Avec le fichier (3) une CheckBox permet de choisir le type d'affichage, voyez la variable test

Allo job,
ton tableau fonctionne bien sur mes 4 autres fournisseurs mais sur celui-ci il n'affiche que des zero ?
aurais je fait une mauvaise manip
merci

 

[munetta]

Allo job,
ton tableau fonctionne bien sur mes 4 autres fournisseurs mais sur celui-ci il n'affiche que des zéros ?
aurais je fait une mauvaise manip
merci

et si je veux faire un fournisseur sur 2 années cela m'affiche aussi que des zéros

 

[job75]

Bonjour munetta, le forum,

C'est dû au fait que maintenant les quantités ne sont plus en colonne C (3) mais en colonne E (5).

Il faut donc modifier la macro en 2 endroits, fichier (4) :

tablo = .Sheets(1).Cells(1).CurrentRegion.Resize(, 5) 'matrice, plus rapide
'---
        quant(n, nn) = quant(n, nn) + tablo(i, 5)

A+

 

[job75]

2 remarques pour terminer.

1. Avec les 5 fichiers .xlsx la durée d'exécution est maintenant stable, autour de 1,1 seconde.

2. On peut s'étonner de trouver en A4 le texte " R32CW" puisque la macro utilise x = Trim(tablo(i, 1)).

En fait il commence par un espace insécable de code 160, vérifiez avec =CODE(A4).

Même chose en A3.