Discussion :

[surzone]

Bonjour,

J'utilise une table de donnée que vous trouverez en pièces jointe.
Elle est composée de la manière suivante :

- Colonne "ID" : un numéro identifiant, cet ID se répète sur plusieurs lignes
- Colonne "SEM" : le numéro de la semaine
- Colonne "RUB1" : une première variable
- Colonne "RUB2" : une deuxième etc...

Dans mon export complet, j'ai entre 30 et 50 colonnes "RUB" différentes. Le ,nombre de colonne "RUB" varie chaque mois lors de l'export que je réalise...et c'est bien ça mon problème, vous comprendrez plus tard.

MON OBJECTIF :
- Pour chaque ID obtenir la somme de chaque rubrique par semaine
- Pour chaque ID obtenir la somme de chaque rubrique cumulée par mois (sommes des 4 ou 5 semaines)
- Une fois les sommes obtenues, grâce à une mise en forme conditionnelle, les cellules dont la somme est supérieure à un seuil préalablement définies se colorent en rouge, et ce par colonne RUB.

Ce que j'ai déjà testé :

- Tableau croisé : dynamique

==> Le TCD m'apport un résultat idéal puisqu'il me donne bien les cumuls demandés. Le problème est que dans la mesure ou le nombre des colonnes "RUB" varie chaque mois, il faudrait tous les mois que je recrée mon TCD en sachant qu'il peut y avoir plus de 60 colonnes...c'est long et pénible.
il en est de même pour ce qui concerne les mises en forme conditionnelles : puisque le nombre de colonnes RUB varie chaque mois.

- BDSOMME

==> cette fonction me permet d'avoir mes sommes de colonnes "RUB" mais ID par ID et semaine par semaine. Hors ce que je souhaite obtenir c'est une vue avec l'ensemble des "ID" et des "RUB" pour d'un coup d'oeil identifier les montant supérieurs au seuil définit.

Là j'avoue que je suis dans une impasse et malgré tout le temps que j'ai passé sur ce problème, je n'ai plus d'idées. J'èspère que ce que j'ai écrit est compréhensible .

Une ame charitable serait elle prete à me venir en aide ?

Merci à tous,

[Pierre Fauconnier]

Bonjour

Si tu définis ta plage avec DECALER, elle s'étendra aux nouvelles données et aux nouveaux champs. Dès lors, lorsque tu automatiseras ton TCD, tu auras tes nouvelles rubriques à disposition...

[surzone]

Merci pour ta réponse,

Je n'avais pas envisagé cette solution. Je connais mal cette fonction.
Et si j'ai moins de colonnes que la fois précédente, comme est ce que ça va réagir ?

Pourrais tu me préciser comment utiliser DECALER STP ?

[Pierre Fauconnier]

Pour autant que:
- tu n'aies aucune cellule vide en A entre la ligne 1 et la dernière ligne de ton tableau
- que tu n'aies aucune cellule vide en ligne 1 entre la colonne A et la dernière colonne de ton tableau
- Que tu n'aies que tes données sur la feuille "données", à l'exclusion de toute formule, tableau, valeur, ...

Ce qui semble être le cas.

Tu peux créer une plage nommée via Insertion/Noms/Définir, tu la nommes par exemple "tDonnees" et tu lui donnes comme référence:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=DECALER(Données!$A$1;0;0;NBVAL(Données!$A:$A);NBVAL(Données!$1:$1))

Tu appuies alors ton TCD sur la plage tDonnees. Le tour est joué. A chaque actualisation du TCD (éventuellement par VBA sur événement Activate de la feuille qui le contient)), tu as ton TCD mis à jour avec les nouvelles lignes et les nouvelles colonnes...

Si tu supprimes une colonne, ton TCD supprimera la colonne lors de l'actualisation.

[surzone]

Je suis en train de tester, ça me parait très intéressant.
Merci pour ton aide.

[surzone]

C'est parfait !
A présent, je souhaite identifier les sommes qui dépassent un certain seuil, en sachant que les seuils sont différents pour chaque rubrique.
J'ai pensé à la mise en forme conditionnelle sur toute une colonne, ce qui me permettrait de colorer un cellule lorsqu'elle dépasse le seuil.

Mais le problème est que si une nouvelle colonne apparait au milieu des autres, ma MEF conditionnelle ne sera plus valable pour la colonne concernée.

Je sais pas si j'ai reussi à être clair là désolé mais je fais de mon mieux.

[surzone]

En fait le top serait d'isoler sur une autre feuilles mes sommes au delà des seuils.

[Pierre Fauconnier]

Tu as plusieurs solutions:

MFC dans le tableau => VBA pour adapter les mises en forme par rapport aux colonnes. Cela peut vite devenir casse-tête en fonction des sous-totaux de lignes et/ou de colonnes...

Extraire les données dans une autre feuille. C'est possible via un tableau préformaté et la fonction SOMMEPROD (Eventuellement, SOMME.SI.ENS ou NB.SI.ENS si tu es en 2007), avec une construction de tableau (ligne ou tableau intermédiaire) qui te permet de faire varier facilement les seuils.

Note que SOMMEPROD est gourmand en temps de calcul sur de gros tableaux

[random]

il serait intéréssant que tu précises de quelle source vient l'export

en fait je pense que tes données seraient plus facilement exploitables sous access

[Pierre Fauconnier]

il serait intéréssant que tu précises de quelle source vient l'export

Très juste. En fonction de cela, il y a peut-être à gratter du côté de MSQuery, par exemple

[surzone]

Mes données proviennent de SAP.
J'ai tenté une exploitation par le biais d'access...mais j'avoue sans succés toujours par rapport au fait que ma table n'a pas un contenu standard en fonction des exports.

[Pierre Fauconnier]

Alors, je suppose que l'extraction du SAP est assez sale au départ (entêtes de pages, lignes vides et tout et tout), et tu dois alors passer par du nettoyage en Excel.

Tu as deux solutions:

- Passer par SommeProd
- Passer par VBA et utiliser une requête sql sur ta table nettoyée via des objets adodb.connection et adodb.recordset

[surzone]

Bonjour,

Mon export SAP est relativement propre en fait. Il est sur un format table exploitable directement en TCD.

Je vais regarder de plus pret la solution SommeProd et te tiens informé.

Encore merci

a+

[surzone]

Tu as plusieurs solutions:
Extraire les données dans une autre feuille. C'est possible via un tableau préformaté et la fonction SOMMEPROD (Eventuellement, SOMME.SI.ENS ou NB.SI.ENS si tu es en 2007), avec une construction de tableau (ligne ou tableau intermédiaire) qui te permet de faire varier facilement les seuils.

Pourrais tu m'éguiller davantage sur cette méthode STP ?

[random]

à mon avis tes problèmes proviennent du fait que tes données ne sont pas normées (nombre de colonnes variables) ce qui interdit les traitements standards et nécessiteraient de la programmation

il existe je pense deux voies

obtenir de sap une extraction canonique sous une forme invariable du type

ID Sem rub mt
116186 36 Rub 1 28.00
116186 36 Rub 2 441.00

si ce n'est pas possible transformer tes données (voire pièce jointe) pour leur donner une forme normée

tu retrouveras alors sans difficulté les fonctionnalités de bd et de tcd

[surzone]

Merci pour cette info,

En effet, la transaction SAP que j'utilise ne me permet pas d'obtenir un export sous forme canonique invariable.

Je me penche sur la solution que tu me proposes.

Par ailleurs voilà en pièces jointe à quoi je suis arrivé avec l'aide d'un autre internaute : Ma table se convertie en TCD qui lui même est mis en forme conditionnelle à partir des seuils définis par rubrique dans l'onglet "seuil"

Mon problème réside maintenant dans l'ajout ou la suppression de rubriques. JE précise que je suis sous excel 2000.

Je suis un peu perdu là.

[surzone]

Je trouve ta solution très intéressante et apparemment moins gourmande en ressource que la solution que j'ai proposé dans mon précédent message.

Je pense que je vais poursuivre sur la voie que tu m'as indiqué.

Maintenant mon objectif serait de colorer les cellules du TCD en fonction de leur intitulé et du seuil définit pour chacune d'entre elle dans l'onglet "Seuil".

Je n'ai pas encore de solution.
Merci pour ton aide.

[surzone]

En fait je me suis emballé, la conversion ne fonctionne pas totalement, dans l'exemple ci-joint, on observe que la feuille "Canon" omet des rubriques.

Quelqu'un aurait il une solution svp ?

merci

[random]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
Sub macrocoloriage()
Dim deb As Long
Dim seuil As Double
Dim couleur As Integer
Dim rub As String
Dim colonne As Long
Dim ligne As Long
Sheets("tcd").Select
colonne = 2
Do
colonne = colonne + 1
rub = Cells(4, colonne)
If rub = "Total" Then Exit Do
seuil = Application.WorksheetFunction.VLookup(rub, Sheets("seuils").Range("a2:c100"), 2, False)
couleur = Application.WorksheetFunction.VLookup(rub, Sheets("seuils").Range("a2:c100"), 3, False)
ligne = 4
    Do
 ligne = ligne + 1
 If Cells(ligne, 1) = "Total" Then Exit Do
 If Not IsEmpty(Cells(ligne, 2)) And Cells(ligne, colonne) >= seuil Then
 Cells(ligne, colonne).Interior.Color = couleur
 End If
    Loop
Loop
End Sub

attention ceci suppose que ta feuille seuils et ta forme tcd ne changent pas de nom
et que l'emplacement des données soient respecté

[surzone]

Je n'ai pas reussi a adapter ton code malheureusement.

Je poursuis le fil sur un autre post afin de pouvoir collaborer davantage :

http://www.developpez.net/forums/d83...s/#post4755669

encore merci.