[VBA] Extraction des données d'un TCD
Bonjour à tous,
Dans le cadre d'un projet je souhaite parvenir à faire une extraction des données d'un tableau croisé dynamique vers un tableau récapitulatif.
Vous trouverez un fichier d'exemple en pièce jointe.
Je souhaite pouvoir extraire mensuellement les sommes de CA et de quantité vendue. Ces valeurs sont résumées dans le TCD et sont distinguées en fonction du mois. Le principal problème qui m'empêche d'utiliser une fonction Excel est que j'utilise un filtre de TCD correspondant a un commissionnement.
J'ai essayé de trouver une macro mais mon niveau en VBA n'est pas encore assez important.
Je souhaiterai pouvoir pour chaque tableau avoir un bouton (correspondant à une macro) qui extrait après activation les valeurs du TCD actuel pour les mettre dans le tableau correspondant.
De cette manière lorsque j'aurai choisi le filtre 7 % je n'aurai qu'a appuyer sur le bouton du tableau récapitulatif des 7% pour lancer la macro d'extraction. Ensuite je recommence avec le filtre 3% etc...
L'autre spécificité est que ce fichier servira en tant que contrôle mensuel, c'est à dire que chaque mois les valeurs mensuels seront ajoutées ( Janvier puis Février, Mars..), il faudra ainsi que la macro arrête de copier lorsque la dernière ligne du TCD a été atteint.
Etant un débutant en VBA je peux être en mesure de modifier une macro afin qu'elle s'applique à mon projet mais dans la conception de la macro complète je vous avoue que je ne suis pas en mesure de la faire .
Je vous remercie d'avance.
Cordialement
bonjour
il est pénible d'extraire des infos d'un TCD (mais c'est possibleLIREDONNEESTABCROISDYNAMIQUE )
le plus facile en général est d'extraire les valeurs directement depuis les données, avec SOMMEPROD
=SOMMEPROD ( (ANNEE(colonnedate)=tonannée) * (MOIS(colonnedate)=tonmois) * (colonnecritère=toncritère) * (colonnedesvaleursàsynthétiser
avec ce modèle, tu peux ajouter tous les critères que tu veux
tonannée étant une année saisie dans une cellule
surtout pas de VBA.
Je te remercie je vais de ce pas appliquer ta fonction
Mais ma question est que j'avais précédemment essayé d'utiliser une fonction ( "Si" et "ET") mais le problème qui s'est posé est que lorsque je change les filtres du tableau (passer de 3% à 7% par exemple). Les valeurs des cellule du filtre précédent s'efface car les critères (3% devenant 7%) ne sont plus respectés.
Est il possible qu'une fois la valeur extraite par la fonction celle-ci se verrouille et ne changera pas lorsque je changerai de filtre dans le TCD ?
Cordialement
re
dans ta feuille tu vas voir 2 cellules
une avec =SOMMEPROD( .... * (taux=0.03) *.... )
une autre = =SOMMEPROD( .... * (taux=0.07) *.... )
j'ai des feuilles contenant des dizaines de SOMMEPROD en particulier pour faire des bilans à partir d'une feuille de données contenant plusieurs années de saisie et pleine de critères (marchés, ventes, seuils de CA... )
Re
Encore merci !
Bon j'ai appliqué cette fonction sur le fichier original de mon projet avec une base de donnée de plusieurs milliers de lignes.
Afin qu'il soit dynamique j'ai utilisé le gestionnaire de nom avec une formule qui prolonge la plage de donnée voulue lorsqu'on en rajoute dans la base.
Je me permets de te poser deux dernières question et je pense que je serais bon.
Alors, je souhaite déterminer le nombre de cellule non vide dans la plage de donnée (colonne), hors j'utilise habituellement la fonction NBVAL. Comment puis-je l'intégrer à ma formule Sommeprod et cela avec les critères imposé (c'est à dire avec les critère de mois, de pourcentage etc) ?
Ensuite je souhaite pouvoir dénombrer le nombre de cellule dans une colonne mais sans compter les doublons (un numéro de commande peut réapparaitre plusieurs fois dans la base car il est constitué de plusieurs objets; hors je souhaite compter seulement les numéro de commande
Cordialement
re
nbre lignes correspondant à des critères = SOMMEPROD ( (cirtère1)*(critère2)*.... * 1 )
nbre lignes avec critères ET sans vide en col Z = SOMMEPROD ( (cirtère1)*(critère2)*.... * (col Z <>"") * 1 )
pour comptage sans doublons, j'ai trouvé ceci
=SOMMEPROD(SI(A1:A10<>"";1/NB.SI(A1:A10;A1:A10)))
Cette formule est à valider en matricielle (Ctrl+Maj+Entrée)
Elle accepte du texte et des cellules vides. Re
Bon je vais essayer tout ca je vous tient au courant sur l'application de ce travail.
Cdlt
Bonjour,
J'interviens un peu tard, mais personnellement j'aurais utilisé quand même LIREDONNEESTABCROISDYNAMIQUE préconisé par jmd.
Il suffit de créer dans un autre onglet un nouveau TCD identique au premier (et qui donc se met à jour en même temps que le premier puisque les données source sont partagées. Et appliquer ensuite LIREDONNEESTABCROISDYNAMIQUE sur ce second TCD. Bien sûr tu n'appliques alors ton filtre que sur le premier TCD.
Je te laisse poursuivre sur ta lancée mais n'oublie pas cette solution.
Bonjour,
Effectivement à l'origine je m'étais dirigé vers cette fonction (Je l'utilise d'ailleurs pour une autre partie de ce projet).
Mais le problème que j'avais rencontré est que cette fonction affiche seulement les valeurs du TCD à un moment donné. Hors sachant que j'applique deux filtres (chacun ayant deux types de critère possible cad deux type de % et deux type de lieu) dans mon TCD et que j'ai des tableaux synthétisant chacun de ces filtres. Les valeurs du TCD changent régulièrement.
Ce qui fait par exemple : Les valeurs affichés dans le tableau 3% (ayant les critères 3%) disparaissent lorsque je change le filtre de mon TCD à 7%.
Connais tu un moyen me permettant de verrouiller la valeur affiché par la fonction ?
Car même si je me suis dirigé dans une autre direction pour l'instant, j'aurais sans doute besoin de cette fonction dans l'avenir.
Merci d'avance
Cordialement
Re,
J'ai réussi à appliquer la formule pour le comptage des cellules non vide. Elle marche parfaitement.
Par contre j'ai des problèmes à appliquer la formule pour le comptage sans les doublons.
J'ai cette formule là
=SOMMEPROD((mois=A21)*(SI(Nombre_commande<>"";1;"")*NB.SI(Nombre_commande;Nombre_commande)))
' l'item Nombre_commande correspond à une plage de donnée que j'ai selectionné elle correspond à la colonne avec les n° de commande et celle-ci est dynamique cad que lorsque j'ajoute des données celle-ci s'adapte automatiquement. Ce que je ne comprend pas est tout d'abord l'utilité du "/" dans la formule de plus je ne comprend pas pourquoi dans la formule NB.SI, il est nécessaire d'utiliser la plage de donnée en tant que "plage" et en tant que "critère".
Ensuite pourquoi est il nécessaire de passer en mode "matrice" (avec le raccourci ctrl+maj+ Entrée) ? En quoi cela change la fonction.
Encore merci pour le temps que vous accordez à ce problème
Cordialement
Bonjour
J'ai réussi à appliquer une formule pour faire les calculs sans les doublons et selon plusieurs critères.
=SOMME(SI((mois="janvier")*(commission="4%");1/NB.SI.ENS(mois;"janvier";commission;"4%";Nbr_cmde;Nbr_cmde)))Le seul problème est cette formule est assez "lourde" à calculer et sachant que j'ai une base de donnée de 35k lignes je peux vous assurer que l'ordinateur est à genoux.
Savez-vous si il est possible d'alléger cette formule ?
Cordialement
Kurumy
bonjour
serait-il possible maintenant d'effacer d'abord les doublons ?
ce qui allège la table et allège encore plus la formule ?
travailler sur une copie de la table
Non malheureusement je ne peux pas effacer les doublons.
Car les doublons représentent un n° de commande hors chaque commande peut être constitué de plusieurs produits et sachant que chaque produit représente une ligne. Chaque ligne est composé de plusieurs colonne avec différentes indications (lieux de facturation, nom de l'entreprise, prix de vente etc...). Ce qui fait que si on supprime les doublons on supprime en même temps de nombreuses informations
kurumy a écrit :Le seul problème est cette formule est assez "lourde" à calculer et sachant que j'ai une base de donnée de 35k lignes je peux vous assurer que l'ordinateur est à genoux.
Savez-vous si il est possible d'alléger cette formule ?
D'où l'intérêt de s'appuyer sur le TCD avec LIREDONNEESTABCROISDYNAMIQUE ! CQFD
Oui je m'en doute bien de l'intérêt indéniable de cette fonction.
Mais connais tu une solution pour le problème que je t'ai indiqué auparavant avec la conservation des résultats ?
Merci d'avance.
re à vous
pour extraire une donnée "fixe", on utilise un SOMMEPRO ( (conditiondedate) * ... )
où conditiondedate est un test entre une date mini et une date maxi
exemple ;
(colonnedate>=01/03/2017)*(colonnedate<=31/03/2017)
kurumy a écrit :Oui je m'en doute bien de l'intérêt indéniable de cette fonction.
Mais connais tu une solution pour le problème que je t'ai indiqué auparavant avec la conservation des résultats ?
Merci d'avance.
Comme ceci :
(ne modifie pas le TCD répliqué en TCDbis)
Merci beaucoup je vais essayer de suite
... et cela donne quoi ? ...
PC à genoux visiblement