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

munetta

XLDnaute Nouveau
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

XLDnaute Barbatruc
Repose en paix
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.

1671307760249.png
 

Hasco

XLDnaute Barbatruc
Repose en paix
re

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


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

XLDnaute Barbatruc
Repose en paix
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
 

Pièces jointes

  • munetta - v2.xlsx
    287.3 KB · Affichages: 3

job75

XLDnaute Barbatruc
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 :
VB:
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+
 

Pièces jointes

  • Moyenne.zip
    510.9 KB · Affichages: 2
Dernière édition:

job75

XLDnaute Barbatruc
Bien entendu, si on le désire, on peut supprimer les formules et les colonnes des fichiers :
VB:
.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
 

Pièces jointes

  • Moyenne.zip
    511.2 KB · Affichages: 1

munetta

XLDnaute Nouveau
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
 

Pièces jointes

  • Moyenne(3).xlsm
    26 KB · Affichages: 2
  • deschenes 21-22.xlsx
    138.9 KB · Affichages: 2
  • deschenes 20-21.xlsx
    143.6 KB · Affichages: 1
  • deschenes 19-20.xlsx
    133 KB · Affichages: 1
  • deschenes 18-19.xlsx
    224.6 KB · Affichages: 1
  • deschenes 17-18.xlsx
    689.2 KB · Affichages: 1

job75

XLDnaute Barbatruc
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) :
VB:
tablo = .Sheets(1).Cells(1).CurrentRegion.Resize(, 5) 'matrice, plus rapide
'---
        quant(n, nn) = quant(n, nn) + tablo(i, 5)
A+
 

Pièces jointes

  • Moyenne(4).xlsm
    28.8 KB · Affichages: 1

job75

XLDnaute Barbatruc
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.
 
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
313 258
Messages
2 096 614
Membres
106 684
dernier inscrit
ekouepatrick