Extraction et implémentation

Y compris Power BI, Power Query et toute autre question en lien avec Excel
F
Femto
Jeune membre
Jeune membre
Messages : 13
Inscrit le : 26 avril 2016
Version d'Excel : 2010 FR

Message par Femto » 26 avril 2016, 12:08

Bonjour à tous !

Tout d'abord, désolé pour le titre, mais je ne savais vraiment pas comment nommer ça.

Je vous explique mon problème :

Je travaille dans une société d'impression numérique. Toutes nos commandes sont transcrites dans des tableaux excel. Un tableau par année, un onglet par mois.
Dans chaque onglet-mois, on y note les noms des clients, références, surface imprimée, etc, etc...et ce par jour.

J'ai une demande de mon patron :
Pour des raisons qui me dépassent un peu, il voudrait UN tableau, avec :
Les noms des clients, et le total de mètre carré imprimés pour eux par mois/année.
Il faudrait arriver à un résultat tel que sur le fichier "exemple_final" (je n'ai pas pu insérer l'xls, trop lourd.)
Car pour arriver à ça, j'ai du faire des copier/coller, virer les doublons pour ne garder qu'une seule occurrence de chaque nom de client, faire des formules à l'arrache...

Le souci, c'est que cette demande va arriver tous les mois. Donc de potentiels nouveaux clients. Je me vois pas tout recommencer à chaque fois.

Le fichier "2016.xls" est un exemple simplifié de nos tableaux de saisie, de là où doivent être extraites les données. J'aurais besoin des colonnes B, V et W

Je ne sais pas si je me fais bien comprendre...même moi je m'y perd^^

Je sèche absolument sur la marche à suivre. Les TCD fonctionnent, mais je ne peux pas afficher les résultats de tous les mois en même temps. C'est soit janvier, soit février, soit mars...

Y aurait-il une âme charitable pour me filer un coup de main ?
Je n'ai jamais touché à VBA (au cas où), mais j'apprend vite^^

Merci d'avance
2016.xls
Base de données clients
(185.5 Kio) Téléchargé 14 fois
Résultat désiré
Résultat désiré
Avatar du membre
M12
Passionné d'Excel
Passionné d'Excel
Messages : 3'226
Appréciations reçues : 129
Inscrit le : 3 juillet 2015
Version d'Excel : 2013

Message par M12 » 26 avril 2016, 12:52

Bonjour,

Etant sur le version 2013, je ne sais pas si sur 2010 cela existe, mais par le menu DONNEES / Consolider tu peux faire une RECAP des données, puis ensuite un TDC
Merci de clôturer le fil en cliquant sur le bouton V vert du post à coté du bouton EDITER si problème résolu

Je ne répond pas au message privé
F
Femto
Jeune membre
Jeune membre
Messages : 13
Inscrit le : 26 avril 2016
Version d'Excel : 2010 FR

Message par Femto » 26 avril 2016, 13:18

J'ai bien l'option Consolider, je vais regarder un peu à quoi cela correspond :)
Merci bien en tous cas

EDIT : Bon j'ai essayé la fonction Consolider. Pour tout rassembler, c'est vraiment bien pratique.

Mais mon souci c'est qu'il n'y a pas la différenciation des périodes. Par exemple, j'ai testé en prenant 2 plages, celle de janvier 2015 et celle de janvier 2016
J'ai bien la totalité de mes clients correspondant à ces deux périodes, mais avec le cumul de leurs commandes. Je n'ai pas "Client 1 : Janvier 2015 = XXX M2 - Janvier 2016 = XXX M2"

Je ne sais pas si je me fais bien comprendre^^ C'est assez casse-reins ce truc...
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'171
Appréciations reçues : 448
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 26 avril 2016, 14:38

Bonjour,

Une petite contribution si cela peut t'aider...
Sub RécapMois()
    Dim n%, nc%
    With ActiveSheet
        n = .Cells(Rows.Count, 2).End(xlUp).Row
        .Range("B8:B" & n).AdvancedFilter xlFilterCopy, , .Range("AA1"), True
        .Range("AB1:AC1").Value = .Range("V8:W8").Value
        nc = .Range("AA" & .Rows.Count).End(xlUp).Row
        .Range("AA1:AA" & nc).Sort key1:=.Range("AA1"), order1:=xlAscending, Header:=xlYes
        .Range("AB2").FormulaArray = "=sum(if(R9C2:R" & n & "C2=RC27,R9C22:R" & n & "C22))"
        .Range("AC2").FormulaArray = "=sum(if(R9C2:R" & n & "C2=RC27,R9C23:R" & n & "C23))"
        .Range("AB2:AC2").AutoFill Range("AB2:AC2").Resize(nc - 1)
    End With
End Sub
Cette macro fait une récap mensuelle. Compte tenu des éléments que tu as fourni, elle fait ici la récap en colonne AA à AC sur la feuille, en procédant par une extraction d'éléments clients uniques par filtre avancé copiés en AA, puis insertion de formules matricielles en AB2 et AC2, étendues jusqu'à la fin de liste, et on obtient la récap du mois.
On peut copier l'extraction sur une autre feuille, évidemment, et pour bien faire il faudrait éliminer le client "vide" qui correspond aux lignes vides de la liste, que le tri place déjà à la fin. On peut aussi compléter en recopiant la plage sur elle-même en valeurs pour écraser les formules.
Une fois des récaps mois faites, il faudrait reprendre les listes clients de chaque mois, les mettre bout à bout sur une feuille temporaire afin de refaire une extraction d'éléments uniques pour l'année. Cette extraction triée servirait de base à la récap annuelle. A ce niveau, je pense qu'une procédure de recueil des données dans les différentes récap mensuelles serait préférable à une insertion de formules...

Cordialement.
F
Femto
Jeune membre
Jeune membre
Messages : 13
Inscrit le : 26 avril 2016
Version d'Excel : 2010 FR

Message par Femto » 26 avril 2016, 15:11

Merci beaucoup, VDD :)

Alors bon, j'essaie un peu de décortiquer le script.
C'est déjà beaucoup moins fastidieux comme ceci.

Seul "problème", je ne sais pas si c'est au niveau du "somme.si.ens" en AB et AC, mais le total ne correspond pas (en additionnant tout, j'arrive à environ 19000+ contre 35000+ dans la réalité)
Reste plus qu'à comprendre pourquoi, et ça devrait le faire comme ça

Merci beaucoup beaucoup :D

EDIT : Mea culpa, je suis un crétin, j'avais sucré des données pour baisser le poids du fichier :oops: :oops: :oops: tout fonctionne :mrgreen:

EDIT2 : Par contre sur mon tableau "réel", les données extraites via ta macro doivent être beaaaaaucoup plus loin sur la droite, style en DA. J'ai beau tout changer, ça fonctionne, sauf que le somme.si.ens continue à aller chercher les valeurs en AA, donc 0.

Une idée de comment faire siouplé m'sieur ?
F
Femto
Jeune membre
Jeune membre
Messages : 13
Inscrit le : 26 avril 2016
Version d'Excel : 2010 FR

Message par Femto » 27 avril 2016, 13:38

Snif, je crois que c'est au niveau de tes formules matricielles que ça coince, mais là je vois pas quoi faire...
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'171
Appréciations reçues : 448
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 27 avril 2016, 13:59

Bonjour,

Elle fonctionnait sur ton exemple.
Tu peux effacer et relancer la macro pour voir.
Si tes feuilles mensuelles sont semblables, pas de raison que cela ne marche pas pareil.
NB- Les formules n'utilisent pas SOMMEPROD qui serait en erreur à cause de tes cellules vides intercalées.

Cordialement.
femto_2016.xls
(212 Kio) Téléchargé 9 fois
F
Femto
Jeune membre
Jeune membre
Messages : 13
Inscrit le : 26 avril 2016
Version d'Excel : 2010 FR

Message par Femto » 27 avril 2016, 14:28

Ah oui pas de soucis, elle fonctionne. Juste que quand je décale les colonnes recap, (je les place en DA-DB-DC au lieu de AA-AB-AC), la formule donne =SOMME(SI($B$9:$B$641=$AA2;$V$9:$V$641)), or cela "devrait" pointer sur $DA2 et non $AA2.

C'est en cela que je demandais de l'aide pour modifier ça dans la macro, car je ne vois pas où/quoi/comment modifier ca :)

.Range("DB2").FormulaArray = "=sum(if(R9C2:R" & n & "C2=RC27,R9C22:R" & n & "C22))"
.Range("DC2").FormulaArray = "=sum(if(R9C2:R" & n & "C2=RC27,R9C23:R" & n & "C23))"
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'171
Appréciations reçues : 448
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 27 avril 2016, 14:45

Si les références changent, il faut les modifier dans la macro !

Je reconnais que ce n'est pas immédiat du fait que les formules matricielles ne peuvent être programmées qu'en anglais et en style L1C1 (soit R1C1 en anglais).
Dans les deux formules il faut alors remplacer RC27 par RC105 (on s'éloigne !)

J'espère que tu as fait les autres modifs dans la macro...

Cordialement.
F
Femto
Jeune membre
Jeune membre
Messages : 13
Inscrit le : 26 avril 2016
Version d'Excel : 2010 FR

Message par Femto » 27 avril 2016, 15:51

J'ai dû mal m'exprimer, les modifs oui je les fait dans la macro.
Mais je ne comprenais pas les lignes que j'avais copié au dessus.

Mais tes explications m'ont éclairé, tout fonctionne, un grand merci :)
Répondre
  • Sujets similaires
    Réponses
    Vues
    Dernier message