XL 2013 Calculs automatique en VBA ou PowerQuery

cece44

XLDnaute Junior
Bon,jour Le Forum,

Me voici face à une difficulté. Je ne sais pas utiliser le VBA mais peut être que cela résoudrait mon problème. Voici l'explication et ce que j'ai fait :
1/ Objectif : Avoir le taux d'avoir par Article (Qté Retour/Qté Livré) hebdomadaire.

2 /Sources : Plusieurs tableaux (3 au total) extraits du logiciel CIEL : Livraison Semaine 1 - Cumul livraisons S1 et S2 - Cumul livraisons S1, S2 et S3. Il faut donc soustraire les cumuls des livraisons pour avoir la qté hebdomadaire réelle.
On fait la même chose avec 3 tableaux mais ce sont des avoirs Avoir Semaine 1 - Cumul Avoir S1 et S2 - Cumul Avoir S1, S2 et S3.

3/ Une fois que l'on a calculé les quantités réelles pour chaque semaine, on calcule le taux d'avoir de la manière suivante : Retour Avoir Semaine 2 / Livraison Semaine 1

Voilà le besoin, pour cela j'ai essayé de réaliser quelque chose avec le module PowerQuery, mais cela m'implique beaucoup de manipulations. (voir pj)

Peut être qu'avec du VBA cela serait plus simple mais je n'y connais rien.

Bien cordialement,

Céline
 

Fichiers joints

eloiiid

XLDnaute Nouveau
Bonjour,

Pour faire simplement je suggère de créer une page que tu nommera tableau de bord par exemple et toutes les informations que tu souhaites tu te fais de lignes.
avec =+sélection de la feuille + sélection de la cellule
PS si le modèle de classification des tableaux est identique, tu peux tirer la formule pour qu'elle s'adapte à tes critères. Tu fais du coup 1 seule fois la manip

Je ne vois pas trop l'utilité de faire du VBA qui sera aussi long à faire que de le faire manuellement

@+
 

cece44

XLDnaute Junior
Re,

Heu cela ne peut pas être aussi simple car il faut retrouver les articles correspondant et ensuite soustraire les qtés. Excusez moi mais je ne comprends pas du tout votre méthode.
 

eriiiic

XLDnaute Barbatruc
Bonjour,

c'est n'importe quoi tes noms de feuille non ?
On a "AV Cumul S41" et "AV Cumul S41 " avec un espace en plus
Ou bien "AV Cumul S42" et "AV S42 Cumul"

Il faut mettre les noms exacts fournis par ciel sans que tu les aies retouchés.
eric
 

eriiiic

XLDnaute Barbatruc
PS : et comment ça se passe au changement d'année ?
S52 ou S53 est suivi de S01 ou de S54, S55, etc
Si c'est S01, comment savoir de quelle année ?
 

job75

XLDnaute Barbatruc
Bonjour,

Oui je me demande bien comment on s'y retrouve avec des feuilles qui ont le même nom (si l'on ignore les espaces bien sûr).

A+
 

eriiiic

XLDnaute Barbatruc
Bonjour job,

PS2 : en fait j'ai l'impression que tu as essayé de bricoler pour t'approcher de ton besoin. Mais le chemin pris n'est pas bon.
Il faut partir d'une (ou 2) extraction avec toutes les commandes et tous les retours de la période qui t'intéresse ( ça peut-être tout ou juste une année ou ...) avec :
- date
- n° commande
- produit
- et bien sûr Qté, prix et ce que tu juges utile en plus
A partir de là tout sera plus simple et surtout plus fiable.
Avec ce que tu as fourni, on ne peut même pas attribuer un retour à la semaine de commande. Tu pourrais très bien te retrouver avec un taux de retour de 150%, plus de retours que d'envois
 

chris

XLDnaute Barbatruc
Bonjour à tous

Tu as beaucoup compliqué les choses...

J'ai renommé tes tableaux :
LIV_S1, LIV_S2, LIV_S3 et AV_S1, AV_S2, AV_S3

Ceci afin que la requête fonctionne quelle que soit le trio (ou plus) de semaines sans besoin de modifier

J'ai fait 2 requêtes PowerQuery
  • Tout qui
    • récupère tout les tableaux du classeur dont le nom commence par LIV et AV
    • supprime les espaces de la colonne FAMILLE afin d'harmoniser l'écriture différente selon le tableau source
    • nettoie les erreurs de la colonne Montant HT (des valeurs, qui ont du être saisies, sont considérées comme texte au lieu de valeurs)
    • récupère le sens (LIV ou AV) et la semaine S
    • ajoute le numéro de semaine suivante (S+1)
  • LIV qui
    • croise Tout avec elle même en utilisant la semaine S d'un côté et S +1 de l'autre
    • récupère montant et Qté de S+1 et calcule les écarts
    • calcule la semaine de comparaison : S pour LIV, S+2 pour AV
Comme l'add on pose quelques soucis lors de la modification de requête et pour la cohérence avec les versions 2016+, une requête FINAL pointe simplement sur LIV

Ancune des requêtes n'est récupérée dans un tableau Excel (inutile et lourd),
FINAL étant directement utilisée par le TCD où sont calculés les sommes (pas forcément nécessaires) et les ratios (masqués pour les colonnes LIV)

Edit : à noter que Ciel permet des extractions assez fines et que l'extraction par période est possible....
 

Fichiers joints

Dernière édition:

cece44

XLDnaute Junior
Bonjour à tous

Tu as beaucoup compliqué les choses...

J'ai renommé tes tableaux :
LIV_S1, LIV_S2, LIV_S3 et AV_S1, AV_S2, AV_S3

Ceci afin que la requête fonctionne quelle que soit le trio (ou plus) de semaines sans besoin de modifier

J'ai fait 2 requêtes PowerQuery
  • Tout qui
    • récupère tout les tableaux du classeur dont le nom commence par LIV et AV
    • supprime les espaces de la colonne FAMILLE afin d'harmoniser l'écriture différente selon le tableau source
    • nettoie les erreurs de la colonne Montant HT (des valeurs, qui ont du être saisies, sont considérées comme texte au lieu de valeurs)
    • récupère le sens (LIV ou AV) et la semaine S
    • ajoute le numéro de semaine suivante (S+1)
  • LIV qui
    • croise Tout avec elle même en utilisant la semaine S d'un côté et S +1 de l'autre
    • récupère montant et Qté de S+1 et calcule les écarts
    • calcule la semaine de comparaison : S pour LIV, S+2 pour AV
Comme l'add on pose quelques soucis lors de la modification de requête et pour la cohérence avec les versions 2016+, une requête FINAL pointe simplement sur LIV

Ancune des requêtes n'est récupérée dans un tableau Excel (inutile et lourd),
FINAL étant directement utilisée par le TCD où sont calculés les sommes (pas forcément nécessaires) et les ratios (masqués pour les colonnes LIV)

Edit : à noter que Ciel permet des extractions assez fines et que l'extraction par période est possible....

Re,

Merci pour ce magnifique travail, maintenant j'essaye depuis 2 heures de le comprendre, pour être en mesure de le reproduire.
C'est vraiment bluffant.
Si je n'y arrive pas pourrais je vous demander de l'aide???
Concernant le logiciel CIEL ce n'est pas moi qui l'utilise, c'est le service compta d'une autre société qui nous envoi les fichiers excell par famille j'ai regroupé l'ensemble par date .
 

cece44

XLDnaute Junior
Bonjour job,

PS2 : en fait j'ai l'impression que tu as essayé de bricoler pour t'approcher de ton besoin. Mais le chemin pris n'est pas bon.
Il faut partir d'une (ou 2) extraction avec toutes les commandes et tous les retours de la période qui t'intéresse ( ça peut-être tout ou juste une année ou ...) avec :
- date
- n° commande
- produit
- et bien sûr Qté, prix et ce que tu juges utile en plus
A partir de là tout sera plus simple et surtout plus fiable.
Avec ce que tu as fourni, on ne peut même pas attribuer un retour à la semaine de commande. Tu pourrais très bien te retrouver avec un taux de retour de 150%, plus de retours que d'envois
Re,

Concernant les extractions que je ne fais pas (externe), on m’envoie les fichiers par famille .... . Ils sont en mesure de faire une extraction la 1ère semaine du mois et ensuite extraire les données mais de manière cumulée jusqu'au 30 ou 31 du mois. Ensuite en début de mois tout repart à 0.
C'est un peu compliqué, apparemment Chris a réussi avec powerquery, mais j'ai un peu de mal à le reproduire. Je vais persévérer.
Merci pour tout.
 

chris

XLDnaute Barbatruc
RE

Il n'y a rien de très compliqué mais, Oui bien sûr, tu peux demander de l'aide (pas toujours immédiate car je m'absente)

Dans la 1ere requête 6 commandes sur 11 servent à faire le ménage dans les noms de FAMILLE et dans les montants : peut-être que la source réelle est propre et permet d'allèger...
 

cece44

XLDnaute Junior
RE

Il n'y a rien de très compliqué mais, Oui bien sûr, tu peux demander de l'aide (pas toujours immédiate car je m'absente)

Dans la 1ere requête 6 commandes sur 11 servent à faire le ménage dans les noms de FAMILLE et dans les montants : peut-être que la source réelle est propre et permet d'allèger...
Bonjour,

J'ai effectivement besoin d'aide et je ne trouve pas sur des tutos, pour commencer je n'arrive pas à reproduire la source de la 1ère requête "Tout"

Quand je fait la requête et que je sélectionne les 6 tableaux cela me créer 6 requêtes ??? Je n'arrive pas à comprendre votre source suivante :

ContentName
TableLIV-S1
TableLIV-S2
TableLIV-S3
TableAV-S1
TableAV-S2
TableAV-S3

Et je ne sais pas comment reproduire les étapes appliquées suivantes :
Lignes Filtrées ?
Content développé?
Valeur remplacée ?
Type Modifié1
Fractionner la colonne par délimiteur
Colonne personnalisée ajoutée : J'ai compris la manipulation. (Au moins une).

Si déjà vous pouvez m'expliquer ces manipulations j'essayerai de me débrouiller toute seule ensuite.
 

chris

XLDnaute Barbatruc
Bonjour

Pour la source : on crée une requête vide puis on tape dans la barre de formule de PowerQuery
=Excel.CurrentWorkbook()
La liste des tableaux structurés apparait

Et je ne sais pas comment reproduire les étapes appliquées suivantes :

C'est assez proche de manips Excel et essentiellement fait par les commandes des onglets ou clic droit

Lignes Filtrées ? Dérouler la flèche de la colonne Name, Filtres Textuels : Commence par LIV ou Commence par AV
Content développé? Cliquer sur la double flèche près du titre Content
Valeur remplacée ? Clic droit sur la colonne FAMILLE, Remplacer les valeurs...
Type Modifié1 Sélectionner la colonne Total HT, onglet Accueil, Type de données : texte
Fractionner la colonne par délimiteur Sélectionner la colonne Total HT,onglet Transformer, Fractionner la colonne, par délimiteur (ou idem par clic droit) : espace, le plus à gauche. On corrige le titre de la 1ère colonne dans la barre de formule.
Même principe pour l'autre avec comme délimiteur _S

Colonne personnalisée ajoutée : J'ai compris la manipulation. (Au moins une). Ce sont des formules comme dans Excel mais avec une formulation différente.
Reviens si tu coinces
 

cece44

XLDnaute Junior
Bonjour

Pour la source : on crée une requête vide puis on tape dans la barre de formule de PowerQuery
=Excel.CurrentWorkbook()
La liste des tableaux structurés apparait



Reviens si tu coinces
Bonjour,

Je vous sollicite de nouveau, j'ai réussi a reproduire les requêtes mais je me trouve de nouveau face à une difficulté :( lorsque que je veut faire le tableau croisé dynamique, il me note qu'il y a un problèmes sont apparus durant l'obtention des données et rien ne se passe.
Avez vous une idée ???
Et autre question, est ce qu'il faut que je refasse ces requêtes à chaque nouvelle semaine ?? Ou est ce que je peux l'utiliser comme base ?
 

chris

XLDnaute Barbatruc
Bonjour

Sans les données difficile de savoir ce qui coince....

Comme je l'ai dit au post#8, si tu nommes les Tableaux
LIV_S1, LIV_S2, LIV_S3 et AV_S1, AV_S2, AV_S3, tu peux ajouter LIS_S4, LIV_S5 etc et juste actualiser.
 

cece44

XLDnaute Junior
Bonjour

Sans les données difficile de savoir ce qui coince....

Comme je l'ai dit au post#8, si tu nommes les Tableaux
LIV_S1, LIV_S2, LIV_S3 et AV_S1, AV_S2, AV_S3, tu peux ajouter LIS_S4, LIV_S5 etc et juste actualiser.
Re,

Ok c'est noté.
Je ne peux pas vous envoyer le fichier car il est trop lourd.
 
Haut Bas