Obtenir des données d'un TCD

bep · 20 Juillet 2007

Bonjour à tous,

Je travail actuellement sur un outil de reporting en excel.

Contexte: Dans le cadre d'améliorer le quotidien des personnes qui l'utilisent, j'ai modifié un fichier excel qui permet de formatter des données sous forme de tableaux croisés dynamiques. J'ai environ 2500 lignes données à triées par mois pour effectuer differents tests et regroupement sur ces données.

Problème: En m'inspirant de l'exmple fournit par Dan (Lire des données dans un Tableau Croisé Dynamique), J'ai reussi à sortir une partie des données que j'avais besoin. Néanmoins, les données que j'ai dans un top 5, je n'arrive pas à les sortir car je ne peux pas utiliser un terme fixe dans la fonction.

Exemple:
J'utilise cela pour sortir mes données du tableau général

=LIREDONNEESTABCROISDYNAMIQUE(TCD;"Total")

Mais le terme Total ne peux pas etre appliquer sur le tableau récapitulatif du top 5 car j'ai besoin de recuprer le terme Z_summary_short 1, 2, 3, 4 et 5 ainsi que les totaux associé a ces termes.

Info complémentaire:
Je ne peux joindre mon tableau car mon fichier excel fait environ 30 Mo et je n'arrive pas a decendre en dessous des 80Ko avec une capture ecran (Merci paint )
Mon tcd a:
pour donnée : nombre_de_z_summary_court
en entrée ligne : z_summary_court (que j'aimerai recuperer), assign_to_group (qui me donne une precision sur qui a fait quoi), et donnée (qui me permet d'avoir un total de z_summary_court et un % du total de z_summary_court)
en entree colonne : plein de choses pas forcement importantes ici.

Merci d'avance.

chris · 20 Juillet 2007

Re : Obtenir des données d'un TCD

Bonjour
Pas simple sans le tableau.
Une solution pour faire maigrir le classeur
Dans les données sources ne garder que très peu de lignes : 10 à 12 représentatives.
Actualiser le tableau et enregistrer.
Une fois zippé cela devrait passer.

Le principe est de trouver les 5 valeurs maximales avec la fonction GRANDE.VALEUR mais il faut souvent des critères supplémentaires pour ne retenir que des lignes de sous-totaux.
Ensuite on peut retrouver le nom correspondant.

@+

bep · 20 Juillet 2007

Re : Obtenir des données d'un TCD

chris à dit:
Bonjour
Pas simple sans le tableau.
Une solution pour faire maigrir le classeur
Dans les données sources ne garder que très peu de lignes : 10 à 12 représentatives.
Actualiser le tableau et enregistrer.
Une fois zippé cela devrait passer.

Le principe est de trouver les 5 valeurs maximales avec la fonction GRANDE.VALEUR mais il faut souvent des critères supplémentaires pour ne retenir que des lignes de sous-totaux.
Ensuite on peut retrouver le nom correspondant.

@+

Bonjour chris merci de ta reponse mais encore une fois cela ne sera pas possible je comprend la difficulté sans le tableau et cela et également difficile de l'expliquer.

Mon classeur fait 30Mo pour de bonne raison c'est que j'ai besoin de toutes les données dedans si je ne laisse que 10 à 12 valeurs représentatives, il n'y a plus d'interet de faire un TOP5

En très grande ligne, mon tableau comporte 5 onglets de synthése qui contiennent chaqu'un 10 TCD de grande taille. je vais de la colonne A à IV parce que je ne peux pas allez plus loin
J'ai également 5 onglets qui me fournissent les données 4 hebdomadaires (environ 500 lignes chaqu'un) et 1 mensuelle (environ 2500 lignes)
S'ajoute à cela des onglets récapitulatifs pour simplifier la lecture (C'est la que j'ai besoin de recup les données des TCD TOP5 pour avoir une synthèse.
Une solution envisageable est de te fair parvenir un mail avec le tableau ou une partie ou encore un screen du tableau.

Merci.

bep · 24 Juillet 2007

Re : Obtenir des données d'un TCD

Je met un fichier xls pour expliquer un peu mieux.
Les données sont un peu farfelues mais pour des raisons de sécurité je suis obligées.

Je cherche donc à recupérer les données du TCD qui est sous la forme d'un TOP 3 ici pour les afficher dans le tableau de recap juste à coté.

Le TCD change toute les semaines, je ne peux donc pas utiliser la même chose que pour le total qui lui le terme reste inchangé quelques soit les données du TCD

Merci d'avance.

chris · 24 Juillet 2007

Re : Obtenir des données d'un TCD

Bonjour
Ci-joint une solution.
Elle utilise une plage nommée valeurs (voir dans insertion nom) obtenue par une formule décaler qui tient compte du changement de hauteur du TCD d'une actualisation à l'autre.
Si ton TCD varie aussi en largeur, il faudra adapter cette formule pour trouver la colonne.

Celeda · 24 Juillet 2007

Re : Obtenir des données d'un TCD

BOnjour,

ouille ouille ouille ouille!!!!!

quand le papillon s'envole il nous éclate de toute beauté!!!!

alors l'admiration s'empare de moi et je ne peux que te faire
une grande révérence.

super Chris!!! je référence!!!! bravo Madame Chris!!!

bep · 24 Juillet 2007

Re : Obtenir des données d'un TCD

Merci chris pour ta réponse rapide.
Je me suis bien creusé la tête pour comprendre comment cela fonctionne et ainsi pouvoir le réadapter à mes tableaux. Cela fonctionne comme je le souhaitais et je t'en remercie

chris à dit:
Bonjour
Si ton TCD varie aussi en largeur, il faudra adapter cette formule pour trouver la colonne.

Comme tu l'avais déjà deviné mon tableau varie également en largeur.
La question que je me pose est comment ajouter la variation en largeur dans

Code:

=DECALER(Feuil1!$BK$7;0;0;EQUIV("Total*";Feuil1!$A:$A;0)-7;1)

étant donné que je ne comprend pas dans le détails le comment cela fonctionne.

chris · 24 Juillet 2007

Re : Obtenir des données d'un TCD

RE

Partant de l'hypothèse que le mot Total figurera toujours ligne 4 de la dernière colonne du TCD :
=DECALER(DECALER(Feuil1!$A$4;3;EQUIV("Total";Feuil1!$4:$4;0)-1);0;0;EQUIV("Total*";Feuil1!$A:$A;0)-7;1)
pour la formule nommée valeurs

Tu as vu cela Celeda : tu m'as initiée à la fonction DECALER pour les plages sources des TCD, maintenant j'en mets partout ainsi que les SOMMEPROD chère à notre reine Monique. Mais je me fais encore pièger par SOMMEPROD de temps en temps.

Bonne soirée à tous.

bep · 25 Juillet 2007

Re : Obtenir des données d'un TCD

Merci chris pour ta réponse.

J'ai bien compris à quoi sert la variable valeurs.
J'ai pu remettre en pratique et en place les données en modifiant la taille du tableau. par contre je n'arrive pas à retrouver les valeurs des cellules qui me permettent de récupérer les Ref.
Je pense que d'un point de vu simplicité, il est plus simple de rester sur la ligne où l'on a recuperé le nombre et d'afficher le texte en bout de ligne (Nombre de lignes Ref0001) et d'appliquer la fonction droite() sur cette référence.

Ci joint le premier exemple modifié avec la nouvelle formule.

Celeda · 25 Juillet 2007

Re : Obtenir des données d'un TCD

Bonjour,

Decaler !!!

en fait, je t'avoue Chris que je comprends Decaler dans sa plus stricte forme et intimité !!lol;

les imbrications comme tu l'as fait avec Equiv ne me sont pas familières et j'ai du séparer les "morceaux" pour bien l'interpreter surtout que j'essaie de la mettre en place sur 6 TCD boulots !!!!!! car si cela fonctionne bien, j'aurai automatiser complètement les 6 synthèses !! et les six graphs qui s'ensuivent d'où tu vois mon fervent intérêt pour ce fil (j'avais d'ailleurs trouvé une autre solution avec deux colonnes supplémentaires à cette demande et vu ta réponse, je me suis abstenue de poster).

Quant à Sommeprod, là cela va mieux mais il faut dire que c'est parfois le casse-tête et je ne te parle pas de Min SI avec Lignes en matricielle....
mais je ne desespère pas d'y arriver.

Merci

bep · 25 Juillet 2007

Re : Obtenir des données d'un TCD

Celeda à dit:
surtout que j'essaie de la mettre en place sur 6 TCD boulots !!!!!! car si cela fonctionne bien, j'aurai automatiser complètement les 6 synthèses !!

J'ai plus ou moins la même chose avec environ 25 TCD sur lesquelles je me prend la tête depuis un petit moment pour sortir une synthése ;-)

chris · 25 Juillet 2007

Re : Obtenir des données d'un TCD

Bonjour

bep à dit:
Je pense que d'un point de vu simplicité, il est plus simple de rester sur la ligne où l'on a recuperé le nombre et d'afficher le texte en bout de ligne (Nombre de lignes Ref0001) et d'appliquer la fonction droite() sur cette référence.

Je comprends ce que tu veux dire car ton dernier TCD à une structure différente du premier : dans le premier cas le nom (Ref...) se trouvait sur la ligne située au-dessus du total lignes correspondant.

Dans le nouveau TCD
=DROITE(DECALER($A$7;EQUIV(BQ28;Valeurs;0)-1;0;1;1);9)

Cependant dans ce nouveau TCD, je ne suis pas sûre qu'on puisse se contenter de chercher les trois plus hautes valeurs de la colonne des totaux : on pourrait en théorie avoir un groupe ayant un nombre de lignes aussi élévé que le total d'une autre Ref.
Donc vérifie bien que ce cas n'est que théorique.

Une précision : en cas d'ex-equo dans les 3 premières valeurs , la formule ne trouvera qu'un des deux.
Je réfléchis à une solution pour ce cas.

bep · 25 Juillet 2007

Re : Obtenir des données d'un TCD

chris à dit:
Cependant dans ce nouveau TCD, je ne suis pas sûre qu'on puisse se contenter de chercher les trois plus hautes valeurs de la colonne des totaux : on pourrait en théorie avoir un groupe ayant un nombre de lignes aussi élévé que le total d'une autre Ref.
Donc vérifie bien que ce cas n'est que théorique.

Une précision : en cas d'ex-equo dans les 3 premières valeurs , la formule ne trouvera qu'un des deux.
Je réfléchis à une solution pour ce cas.

J'ai mis en pratique sur mon tableau, et j'ai effectivement un petit soucis concernant les valeurs a savoir:
Il me sort ça comme valeur (J'affiche un top 5):

110 >Afficher
11
3
181 >Afficher
10
---
315 >>> Total du dessus (Afficher)
===
30 >Afficher
---
30 >>> Total du dessus (Afficher)
===
19
1
---
20 >>> Total du dessus
===
5
14
---
19 >>> Total du dessus
===
15
---
15 >>> Total du dessus
===

Je comprend le pourquoi et je pense mais je suis pas sur que l'on pourrait peut être passé uniquement par les lignes où il y a les totaux (Nombres de lignes RefXXX) en incluant un test lors de la recuperation de la valeur avec la formule que tu m'as donné : =GRANDE.VALEUR(Valeurs;1) en sachant que si l'on a la ligne de cette valeur il doit être possible de récupérer le texte de la première ligne avec la fonction DECALER.
Mais je suis pas encore trop a l'aise avec cette fonction.

chris · 25 Juillet 2007

Re : Obtenir des données d'un TCD

Bonjour à toi Celeda

Celeda à dit:
les imbrications comme tu l'as fait avec Equiv ne me sont pas familières et j'ai du séparer les "morceaux" pour bien l'interpreter

En fait je suis partie du principe que pour chacun des arguments de cette fonction, on devait pouvoir si nécessaire le calculer par une autre fonction.
Du coup les fonctions EQUIV, RECHERCHEV, NB, NBVAL, NB.SI, SOMMEPROD ... qui permettent de travailler avec des conditions pour retrouver nos petits s'imposaient. Alors petit à petit j'avance dans cette direction pour résoudre des cas tordus.

J'en arrive même comme tu vois, à imbriquer deux DECALER. Mais rassure-toi, il m'arrive de me perdre en route et de faire injurier par Excel avec ces onomatopées pleine de #

N'hésite pas si tu as des cas, il faut que je m'amélore encore pour, comme dit un de mes copains de randonnée, garder Alzheimer et sa bande d'affreux à bonne distance.

chris · 25 Juillet 2007

Re : Obtenir des données d'un TCD

Re Bep

Pas bien suivi la description mais en appliquand le top 5 sur ton 2ème exemple, j'ai effectivement un 5ème qui est un groupe.
Déterminer une plage continue avec DECALER n'est pas simple mais faisable. Par contre déterminer des plages disjointes, je ne sais pas faire.
Je pense qu'il faudrait passer par un tableau intermédiaire rapatriant toutes les lignes et valeurs de totaux puis exploiter ce second tableau.
Par formules c'est faisable mais lourd.
J'avoue que pour mon propre usage, je ferais une macro.

Je dois y aller, mais je vais continuer à cogiter : j'aurais peut être une autre piste.

Obtenir des données d'un TCD

bep

XLDnaute Nouveau

chris

XLDnaute Barbatruc

bep

XLDnaute Nouveau

bep

XLDnaute Nouveau

Pièces jointes

chris

XLDnaute Barbatruc

Pièces jointes

Celeda

XLDnaute Barbatruc

bep

XLDnaute Nouveau

chris

XLDnaute Barbatruc

bep

XLDnaute Nouveau

Pièces jointes

Celeda

XLDnaute Barbatruc

bep

XLDnaute Nouveau

chris

XLDnaute Barbatruc

bep

XLDnaute Nouveau

chris

XLDnaute Barbatruc

chris

XLDnaute Barbatruc

Discussions similaires