Extraction de données depuis une feuille

Bonjour à tous,

J'ai une petite problématique, ou plutot je ne sais pas comment aborder un problème.

J'utilise Power Query pour extraire des data d'une base Access sur une Feuille "Data" de mon fichier Excel. Jusque là pas de soucis mis à part que cela prend des plombes (base Access sur un réseau d'entreprise, accès vpn et beaucoup de lignes).

Une fois que j'ai récupéré mes data bruts sur ma feuille "Data": toutes les livraisons des différentes références depuis plusieurs années, je souhaiterai sur une seconde Feuille "Bilan", faire des "statistiques"....

Par exemple pour une référence XXX, je souhaiterai sur 5 colonnes, lister:

• le nombre total de coffrets de livré sur l'année n-2 (année pleine de janvier à décembre)
• le nombre total de coffrets de livré sur l'année n-1
• le nombre total de coffrets sur les 12 derniers mois (en fonction du mois sélectionnés)
• le nombre total de coffrets sur les 6 derniers mois
• le nombre total de coffrets sur les 3 derniers mois

Je veux faire ce process en automatique sur 90 références en fonction du mois sélectionné....

n'etant pas expert d'Excel, je ne sais pas trop comment aborder le problème:

• formules
• vba
• tableau croisés

Ci-joints un exemple de fichier (avec nettement moins de ligne).

Les nombres de coffrets sont dans la colonne H de la Feuille "Data"

Vos lumières seraient les bienvenue.

Merci

Bonjour,

Peut-être quelque chose comme dans le fichier joint... mais de nombreuses autres possibilités.

Si le nombre de période cherchés est limité ça ira très bien.

Pour la dernière colonne j'ai fait au hasard avril et 2019 : on peu automatiser un peu si on veut un max de choix, mais il y a aussi d'autres méthodes...

A+

Salut Galopin,

Cela fait à peu près le job mais cela implique de modifier les formules au fils du temps... Par exemple pour l'année n-2 (cela ne sera plus valide en 2020), de même mon feuillet "data" va avoir un nombre variable de lignes (plusieurs milliers).

De plus, pour le calcul des 3 derniers mois ou 6 derniers mois, tes formules prennent en compte Q1 ou Q2 est mais si on prend comme base le mois de MAI, le calcul des 3 derniers mois doit se faire sur fevrier, mars, avril.... qui ne correspond ni à Q1, ni Q2....

Tu parles d'autres possibilités pour calculer tout cela, tu penses à quoi? (le but serait du ready to use). Seul l'onglet "data" est mis à jour.

merci pour ton aide

Bonjour,

On peut adosser ça à un peu de VBA, utiliser des zones nommées dynamiques... Ça fera le job sans problème !

Les sommeprod sont un peu chronovores moi je ferai ça avec les fonctions de base de données (et du VBA)

Les fonctions de base de données sont des fonctions natives d'excel: Les plus puissantes !

Ce ne sont somme toute que des calculs élémentaires : En théorie pas la peine d'utiliser du VBA pour faire ces calculs ou Excel se débrouille très bien : Le VBA ne sert qu'à modifier les paramètres...

Après YFO trouver le mec motivé pour te le faire. Moi je suis un peu feignant : Je laisse ça aux "morts de faim" qui se jettent sur tous les sujets...

D'autres adeptes de la modernité te suggèreront du POWER machin... D'apprendre le langage M ou je ne sais quoi encore. Je doute qu'eux non plus te fasse du clef en main.

A+

Bonjour à tous,

Avec du simple Sql on y arrive facilement :

Sub Bilan(Optional mois As Byte = 0)
Dim Dt As Date, Req As String, T As Variant

    Dt = IIf(mois = 0, Date, DateSerial(Year(Date), mois, 1))
    Req = "SELECT `Material`," & _
          " SUM (IIF (YEAR(`Created on`) = " & Year(Dt) - 2 & ",`Billed Quantity`,0) )," & _
          " SUM (IIF (YEAR(`Created on`) = " & Year(Dt) - 1 & ",`Billed Quantity`,0) )," & _
          " SUM (IIF (`Created on` >=" & CLng(DateAdd("yyyy", -1, Date)) & ",`Billed Quantity`,0) )," & _
          " SUM (IIF (`Created on` >=" & CLng(DateAdd("m", -6, Dt)) & ",`Billed Quantity`,0) )," & _
          " SUM (IIF (`Created on` >=" & CLng(DateAdd("m", -3, Dt)) & ",`Billed Quantity`,0) )" & _
          " FROM [Data$] " & _
          " GROUP BY `Material`"

    Connect_Xls ThisWorkbook.Path & "\" & ThisWorkbook.Name
    T = Select_Db(Req, 0)
    Close_Cnx

    Sheets("Bilan").Range("A2:F2000").ClearContents
    Sheets("Bilan").Range("A2").Resize(UBound(T, 1), UBound(T, 2)) = T
End Sub

Voir fichier démo joint, avec une liste déroulante pour choisir un mois de l'année en cours

Pierre

Salut Pierre,

C'est excellent ton code... exactement ce qu'il me faut.

Juste une question concernant la combobox. Nous sommes en Mai, donc si je choisis Janvier, Fevrier, Mars ou Avril cela prend comme origine des extractions l'année 2019. Mais si je choisi des mois non encore passés (Juillet par exemple), l'extraction se fait comment? depuis le mois de juillet de l'année précédente?

Bonjour Kev,

Si on veut prendre en compte l'année, il suffit de l'indiquer quelque part (par exemple ici dans la démo en I1) et d'intégrer cette donné dans la requête :

Sub Bilan(Optional mois As Byte = 0)
Dim Dt As Date, Req As String, T As Variant, An As Integer

    An = Sheets("Bilan").Range("I1").Value
    Dt = IIf(mois = 0, Date, DateSerial(An, mois, 1))
    Req = "SELECT `Material`," & _
          " SUM (IIF (YEAR(`Created on`) = " & An - 2 & ",`Billed Quantity`,0) )," & _
          " SUM (IIF (YEAR(`Created on`) = " & An - 1 & ",`Billed Quantity`,0) )," & _
          " SUM (IIF (`Created on` >=" & CLng(DateAdd("yyyy", -1, Dt)) & ",`Billed Quantity`,0) )," & _
          " SUM (IIF (`Created on` >=" & CLng(DateAdd("m", -6, Dt)) & ",`Billed Quantity`,0) )," & _
          " SUM (IIF (`Created on` >=" & CLng(DateAdd("m", -3, Dt)) & ",`Billed Quantity`,0) )" & _
          " FROM [Data$] " & _
          " GROUP BY `Material`"

    Connect_Xls ThisWorkbook.Path & "\" & ThisWorkbook.Name
    T = Select_Db(Req, 0)
    Close_Cnx

    Sheets("Bilan").Range("A2:F2000").ClearContents
    Sheets("Bilan").Range("A2").Resize(UBound(T, 1), UBound(T, 2)) = T
End Sub

A noter : quand aucun mois n'est sélectionné, le bilan est calculé en fonction de la date du jour. Si un mois est sélectionné le bilan est calculé à partir du 1er du mois indiqué => DateSerial(An, mois, 1)

Pour avoir un bilan calculé à partir de la fin du mois en cours, on écrira :

DateAdd("m", 2, DateSerial(An, mois, 1) - 1)

Pour la fin du mois précédent :

DateAdd("m", 1, DateSerial(An, mois, 1) - 1)

Voir fichier démo

Pierre

Bonjour Pierre,

Merci beaucoup pour ton aide, j'ai reussi à adapter ton exemple à mon cas. Par contre j'ai encore un petit problème.

Sur la feuille "Bilan", j'ai la liste de mes références dans un certain ordre (que je ne souhaite pas changer) et lorsque j'utilise ton code cela efface la Feuille pour remettre par ordre croissant... cela ne me convient malheuresement pas.

De plus avec ce code, si des références ne sont pas présentent dans l'onglet "data" (non commandées par exemple) alors elles disparaissent de l'onglet "Bilan"....

Comment faire pour adapter ton code, pour qu'il ne touche pas à la structure de ma Feuille Bilan (liste de X Nom & références listé à partir de la ligne 4 sur les colonnes A et B) et viennent seulement compléter les colonnes correspondantes (en mettant zero si cette référence n'est pas commandée par exemple).

Je suis loin d'être un expert SQL... je vois pas trop si c'est faisable facilement ce genre de chose.

Mais je pense qu'il faut faire une boucle de vérification dans la sub Bilan, pour comparer les références de mon tableau d'origine (sur "Bilan") stocké sur la colonne B et les références extraitent depuis la requete sql.

     Sheets(FeuilName).Range("C4:F2000").ClearContents

    ' Numero derniere ligne
    Ligne = Sheets(FeuilName).Range("A" & Rows.Count).End(xlUp).Row

    For i = 4 To Ligne
    if Cells (i,2).....  A completer

    Next

Par contre votre tableau de recupération des Data depuis la requete sql étant déclaré comme un variant, je ne vois pas trop comment le manipuler...

Bonjour,

Pas de soucis, il suffit effectivement de boucler comme ceci :

    With Sheets("Bilan")
        lg = .Cells(Rows.Count, 1).End(xlUp).Row
        T2 = .Range(.Cells(2, 1), .Cells(lg, 6)).Value
        .Range("B2:F2000").ClearContents
        For i = 1 To UBound(T2, 1)
            idx = Idx_T2D(T, T2(i, 1), 1)
            For j = 2 To 6
                If idx > 0 Then T2(i, j) = T(idx, j) Else T2(i, j) = ""
            Next j
        Next i
        .Range("A2").Resize(UBound(T2, 1), UBound(T2, 2)) = T2
    End With

Autre chose?

Pierre

Bonsoir Pierre,

Un grand merci pour ton aide.

Par contre pas moyen d'adapter à mon cas.... dû à la structure un peu particulière de mon onglet "Bilan".... (voir fichier ci-joint).

Encore un peu d'aide serait la bienvenue.

Par contre peux-tu commenter un peu ton code que je vois comment cela fonctionne (je ne maitrise pas du tout les tableaux sous vba) et que je puisse m'en servir à l'avenir.

Merci

Bonjour,

Bien sûr qu'on peut adapter! Quelque soit la structure du résultat attendu.

Voir démo avec année indiquée en G1, code de recopiage commenté ligne par ligne et moyennes au format 2 décimales.

Pierre

Bonjour Pierre,

C'est exactement ce que je veux, un grand merci pour ton aide et le temps que tu y as passé.

Dernière question: la gestion de la combobox me perturbe un peu car à chaque reouverture du fichier, on perd la dernière sélection.

Comment résoudre ce problème? Ne pas gérer la combobox avec vba et plutôt créer une liste déroulante? Ou stocker la dernière sélection dans une cellule du fichier?

Encore merci

C'est très simple.

Dans la procédure Init_combo(), le curseur est "remis à zéro".

Pour garder la dernière sélection, il suffit d'enlever la ligne :

.ListIndex = -1 (remise à zéro)

Pierre

Perfect,

Un grand merci à toi

excellente fin de week-end

Re Bonjour Pierre,

Je viens de me rendre compte que les calculs (calcul des moyennes mensuelles) ne vont pas dans ton fichier d'exemple.

En effet, pour le calcul mensuel tu parts sur 1/12 eme de la colonne précédente...

 T2(i, (j * 2) - 1) = T(idx, j) / 12 

Cela fonctionne parfaitement pour les colonnes "année n-2", "année n-1" et "12 derniers mois" mais cela ne colle plus pour les colonnes "6 derniers mois" et "3 derniers mois".....

Comme il s'agit d'une boucle, je vois pas trop comment faire pour ces deux dernières colonnes.

Enfin je souhaiterai ajouter une colonne supplémentaire pour le calcul du mois en cours...

Peux-tu m'aider une dernière fois ?

Merci

En effet, je suis allé un peu vite sur ce coup là, désolé.

Alors voici une autre approche, le calcul mensuel (/12 ou /6 ou /3) se fait directement dans la requête. C'est plus simple comme ça.

Avec ajout de la colonne "Mois en cours" en M

(avec les données de test la colonne est à 0 puisqu'il n'y a pas de données pour juin 2019)

Pierre

    Req = "SELECT `Material`," & _
          " SUM (IIF (YEAR(`Created on`) = " & An - 2 & ",`Billed Quantity`,0) ) AS Moins2," & _
          " Moins2/12," & _
          " SUM (IIF (YEAR(`Created on`) = " & An - 1 & ",`Billed Quantity`,0) ) AS Moins1," & _
          " Moins1/12," & _
          " SUM (IIF (`Created on` >=" & CLng(DateAdd("yyyy", -1, Dt)) & ",`Billed Quantity`,0) ) As A," & _
          " A/12," & _
          " SUM (IIF (`Created on` >=" & CLng(DateAdd("m", -6, Dt)) & ",`Billed Quantity`,0) ) AS DER6," & _
          " DER6/6," & _
          " SUM (IIF (`Created on` >=" & CLng(DateAdd("m", -3, Dt)) & ",`Billed Quantity`,0) ) AS DER3," & _
          " DER3/3," & _
          " SUM (IIF (YEAR(`Created on`) = " & Year(Date) & " AND MONTH(`Created on`)=" & Month(Date) & _
            ",`Billed Quantity`,0) )  " & _
          " FROM [Data$] " & _
          " GROUP BY `Material`"

Hello

La colonne "Mois en cours" ne semble pas fonctionnelle....

En tout cas, je ne savais pas qu'on pouvait faire tout cela directement dans les requêtes SQL.... ça simplifie pas mal la mise en place.

Va falloir que je me penche sérieusement sur les requetes SQL car mise à part une extraction simple, je ne sais pas faire grand chose avec

encore merci pour ton aide.

kevlille a écrit :

La colonne "Mois en cours" ne semble pas fonctionnelle....

pierrep56 a écrit :

(avec les données de test la colonne est à 0 puisqu'il n'y a pas de données pour juin 2019)

Re,

Je viens de comprendre le problème à la lecture de ta réponse... enfin je pense.

Pour toi le "mois en cours" est le mois actuel (Juin), alors que moi j'entendais plutôt le mois sélectionné dans la combobox (en faite la combinaison mois + année).

Du coup pour corriger cela, je dois modifier cette ligne?

" SUM (IIF (YEAR(`Created on`) = " & Year(Date) & " AND MONTH(`Created on`)=" & Month(Date) & _
            ",`Billed Quantity`,0) )  " & _

en un truc du genre?

" SUM (IIF (YEAR(`Created on`) = " & Year(Dt) & " AND MONTH(`Created on`)=" & Month(Dt) & _
            ",`Billed Quantity`,0) )  " & _

Et là ça devrait être bon?

Moi aussi, je viens de comprendre.

En fait ce que tu souhaites en dernière colonne c'est le bilan du mois sélectionné dans la liste.

Si c'est le cas, il suffit de modifier la requête :

" SUM (IIF (YEAR(`Created on`) = " & Year(Date) & " AND MONTH(`Created on`)=" & mois & _

Est-ce que c'est bien ça?

Pierre