XL 2013 importer et lister à partir d'une base de donnée

[an@s]

Bonjour à tous,

je reviens vers vous après une longue absence,
dans le fichier nommé AP j'aimerais lié au bouton importer un code permettant de :

importer les noms de la colonne D du fichier Apports dans la colonne C du fichier AP
importer les quantités de la colonne F du fichier Apports dans la colonne D du fichier AP
importer les données de la colonne J du fichier Apports dans la colonne E du fichier AP
importer les données de la colonne P du fichier Apports dans la colonne F du fichier AP

Sauf qu'il faut lister les noms de la colonne C du fichier AP en mettant les 10 premiers fournisseurs qui ont les quantités de la colonne D les plus élevées,
le reste des quantités il faut les rassembler dans la ligne 52 (autres) en mettant dans E52 un prix moyen.

Merci d'avance

 

[job75]

Bonjour an@s, le forum,

1) Fichier Apports.xlsx :

- il est constitué de 2 articles DECHET et ENTIER, y en a-t-il toujours 2 ou un seul ou davantage ?

- pour un fournisseur donné comme "LALO" que faut-il faire avec les quantités en F9 et F25 ? Les additionner ? Ce serait bizarre car on additionnerait des choux et des carottes

- même question pour les colonnes J et P.

2) Fichier AP.xlsx : il y a 2 tableaux, on ne fait rien avec le 2ème ?

A+

 

[an@s]

Bonjour JOB,

j'espère que vous allez bien,

- il y'a toujours deux articles DECHET & ENTIER
- pour les quantités F9 cad DECHET c'est à mettre dans la colonne C du 2ème tableau de AP
pour les quantités de F25 cad ENTIER c'est à mettre dans la colonne L du 1er tableau de AP

(pareille pour J et P)

Merci pour votre assistance

 

[job75]

pour les quantités F9 cad DECHET c'est à mettre dans la colonne C du 2ème tableau de AP
pour les quantités de F25 cad ENTIER c'est à mettre dans la colonne L du 1er tableau de AP

??? Ce ne serait pas plutôt :

pour les quantités F9 cad DECHET c'est à mettre dans la colonne L du 2ème tableau de AP
pour les quantités de F25 cad ENTIER c'est à mettre dans la colonne C du 1er tableau de AP

??? Faut être précis !!!

 

[an@s]

Oups,
Oui vous avez raison je me suis trompé

Amicalement

 

[job75]

Téléchargez les fichiers joints dans le même dossier (le bureau).

La macro du bouton :

Sub Importer()
Dim chemin$, fichier, F As Worksheet, a, b, n%, dest As Range, i&, j&, h%
chemin = ThisWorkbook.Path & "\" 'à adapter
fichier = "Apports.xlsx"
If Dir(chemin & fichier) = "" Then MsgBox "Fichier '" & fichier & "' introuvable !", 48: Exit Sub
Set F = ActiveSheet
a = Array("DECHET", "ENTIER")
b = Array("DECHET Total", "ENTIER Total")
Application.ScreenUpdating = False
F.[C42:C51,D42:F52,L42:L51,M42:O52].ClearContents 'RAZ
On Error Resume Next 'si le fichier n'est pas ouvert
Workbooks(fichier).Close False 'si le fichier est ouvert on le ferme
On Error GoTo 0
With Workbooks.Open(chemin & fichier).Sheets(1) 'ouverture du fichier source
    For n = 0 To UBound(a)
        Set dest = IIf(n, F.[C42], F.[L42]) '1ère cellule de destination
        i = Application.Match(a(n), .Columns(3), 0)
        j = Application.Match(b(n), .Columns(3), 0)
        If j > i + 1 Then
            With .Rows(i & ":" & j - 1)
                .UnMerge 'défusionne les cellules pour permettre le tri
                .Sort .Columns(6), xlDescending, Header:=xlNo 'tri décroissant sur la colonne F
                h = IIf(.Rows.Count > 10, 10, .Rows.Count)
                dest.Resize(h) = .Columns(4).Resize(h).Value 'D
                dest(1, 2).Resize(h) = .Columns(6).Resize(h).Value 'F
                dest(1, 3).Resize(h) = .Columns(10).Resize(h).Value 'J
                dest(1, 4).Resize(h) = .Columns(16).Resize(h).Value 'P
                If .Rows.Count > 10 Then
                    .Resize(10).ClearContents 'pour ne pas tenir compte des valeurs
                    dest(11, 2) = Application.Sum(.Columns(6)) 'somme sur F
                    dest(11, 3) = Application.Average(.Columns(10)) 'moyenne sur J
                    dest(11, 4) = Application.Average(.Columns(16)) 'moyenne sur P
                End If
            End With
        End If
    Next
    .Parent.Close False 'fermeture du fichier source
End With
End Sub

 

[an@s]

Re-Bonjour Mr Job,

je vous remercie infiniment, c'est vraiment ce que je voulais comme résultat,
j'ai juste une petite remarque :

pour le E52 de la feuille AP il faut calculer le prix d'achat en utilisant la fonction sommeprod, au lieu de la fonction moyenne. dans E52 vous devrez avoir comme résultat 2509 au lieu de 2514,
c'est pareil pour F52, N52, O52.

NB: le nombre de lignes des données du fichier Apports est variable, nous pouvons avois plusieurs lignes de plus, donc à chaque fois il y'a de nouvelles lignes le code doit les mettre à jour

je vous remercie encore une autre fois

 

[job75]

pour le E52 de la feuille AP il faut calculer le prix d'achat en utilisant la fonction sommeprod

En effet il faut calculer la moyenne pondérée alors utilisez ce fichier (2) :

If dest(11, 2) Then
    dest(11, 3) = Application.SumProduct(.Columns(6), .Columns(10)) / dest(11, 2) 'moyenne pondérée
    dest(11, 4) = Application.SumProduct(.Columns(6), .Columns(16)) / dest(11, 2) 'moyenne pondérée
End If

NB: le nombre de lignes des données du fichier Apports est variable

Bien sûr c'est pour ça qu'il faut calculer à chaque fois les variables i et j pour chaque tableau.

 

[job75]

J'ai corrigé une petite erreur : il faut écrire If i > j Then et non If I > j + 1 Then

 

[an@s]

Bonsoir Mr Job,

maintenant ça fonctionné pour la moyenne comme souhaité,
si vous remarquer le fichier AP ci-joint, j'ai rajouté un 3ème tableau de U jusqu'à W,
ce serait possible de modifier le code pour qu'il mettent dans la colonne U les noms des fournisseurs et dans la colonne V le tonnage mais en rassemblant les quantités des articles DECHET + ENTIER

je vous remercie infiniment pour votre efficacité Mr Job

 

[job75]

Désolé mais là je vous laisse tomber.

 

[an@s]

Oups,
D'accord pas de souci...
Je vous remercie beaucoup déjà pour le travail efficace....

Amicalement

 

[job75]

Bonsoir an@s, le forum,

Je vous ai quand même traité le 3ème tableau :

'---3ème tableau---
With F
    .[U52].Resize(10, 2).Insert xlDown
    .[C42].Resize(10, 2).Copy .[U42]
    .[L42].Resize(10, 2).Copy .[U52]
    With .[U42].Resize(20, 2)
        .Sort .Columns(1), Header:=xlNo 'tri alphabétique
        For i = 2 To 20
            If LCase(.Cells(i - 1, 1)) = LCase(.Cells(i, 1)) Then
                .Cells(i, 2) = .Cells(i, 2) + .Cells(i - 1, 2) 'consolidation
                .Cells(i - 1, 1).Resize(, 2) = "" 'effacement
            End If
        Next
        .Sort .Columns(2), xlDescending, Header:=xlNo 'tri décroissant sur les tonnages
    End With
    .[V62] = Application.Sum(.[V52].Resize(10), .[D52], .[M52])
    .[U52].Resize(10, 2).Delete xlUp
End With
End Sub

A+

 

[job75]

Cela dit par cette méthode on récupère les 10 premiers des 2 premiers tableaux de la même feuille.

Cela ne correspond pas forcément aux 10 premiers du fichier Apports.xlsx.

 

[an@s]

Bonsoir Mr Job,

oh je vous remercie beaucoup pour votre intérêt envers ma problématique.
en revanche, la méthode que vous avez utilisé n'est pas valable pour tous les cas,
il faut rassembler les quantités du fichier Apport, parce qu'il se peut que nous ayons dans le premier tableau un fournisseur qui a une grande quantité et qui fait parti des 10 premiers mais dans le 2ème tableau il n'existe pas parmi les 10 premiers,
alors que dans ce cas dans le 3ème tableau il faut mettre sa quantité qui existe dans le 1ere tableau et l'autre quantité qu'il faut importer du fichier Apport

j'espère que vous avez compris cela

Amicalement,
An@s