IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Excel Discussion :

Une alternative aux TCD ou à la fonction BDSOMME ?


Sujet :

Excel

  1. #1
    Nouveau membre du Club
    Inscrit en
    septembre 2007
    Messages
    57
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 57
    Points : 34
    Points
    34
    Par défaut Une alternative aux TCD ou à la fonction BDSOMME ?
    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,
    Fichiers attachés Fichiers attachés

  2. #2
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    18 269
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 18 269
    Points : 52 912
    Points
    52 912
    Billets dans le blog
    121
    Par défaut
    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...
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  3. #3
    Nouveau membre du Club
    Inscrit en
    septembre 2007
    Messages
    57
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 57
    Points : 34
    Points
    34
    Par défaut
    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 ?

  4. #4
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    18 269
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 18 269
    Points : 52 912
    Points
    52 912
    Billets dans le blog
    121
    Par défaut
    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.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  5. #5
    Nouveau membre du Club
    Inscrit en
    septembre 2007
    Messages
    57
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 57
    Points : 34
    Points
    34
    Par défaut
    Je suis en train de tester, ça me parait très intéressant.
    Merci pour ton aide.

  6. #6
    Nouveau membre du Club
    Inscrit en
    septembre 2007
    Messages
    57
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 57
    Points : 34
    Points
    34
    Par défaut
    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.

  7. #7
    Nouveau membre du Club
    Inscrit en
    septembre 2007
    Messages
    57
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 57
    Points : 34
    Points
    34
    Par défaut
    En fait le top serait d'isoler sur une autre feuilles mes sommes au delà des seuils.

  8. #8
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    18 269
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 18 269
    Points : 52 912
    Points
    52 912
    Billets dans le blog
    121
    Par défaut
    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
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  9. #9
    Expert confirmé

    Profil pro
    Inscrit en
    mai 2005
    Messages
    3 419
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2005
    Messages : 3 419
    Points : 4 224
    Points
    4 224
    Par défaut
    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
    Elle est pas belle la vie ?

  10. #10
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    18 269
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 18 269
    Points : 52 912
    Points
    52 912
    Billets dans le blog
    121
    Par défaut
    Citation Envoyé par random Voir le message
    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
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  11. #11
    Nouveau membre du Club
    Inscrit en
    septembre 2007
    Messages
    57
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 57
    Points : 34
    Points
    34
    Par défaut
    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.

  12. #12
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    18 269
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 18 269
    Points : 52 912
    Points
    52 912
    Billets dans le blog
    121
    Par défaut
    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
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  13. #13
    Nouveau membre du Club
    Inscrit en
    septembre 2007
    Messages
    57
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 57
    Points : 34
    Points
    34
    Par défaut
    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+

  14. #14
    Nouveau membre du Club
    Inscrit en
    septembre 2007
    Messages
    57
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 57
    Points : 34
    Points
    34
    Par défaut
    Citation Envoyé par Pierre Fauconnier Voir le message
    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 ?

  15. #15
    Expert confirmé

    Profil pro
    Inscrit en
    mai 2005
    Messages
    3 419
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2005
    Messages : 3 419
    Points : 4 224
    Points
    4 224
    Par défaut
    à 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
    Fichiers attachés Fichiers attachés
    Elle est pas belle la vie ?

  16. #16
    Nouveau membre du Club
    Inscrit en
    septembre 2007
    Messages
    57
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 57
    Points : 34
    Points
    34
    Par défaut
    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à.
    Fichiers attachés Fichiers attachés

  17. #17
    Nouveau membre du Club
    Inscrit en
    septembre 2007
    Messages
    57
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 57
    Points : 34
    Points
    34
    Par défaut
    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.
    Fichiers attachés Fichiers attachés

  18. #18
    Nouveau membre du Club
    Inscrit en
    septembre 2007
    Messages
    57
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 57
    Points : 34
    Points
    34
    Par défaut
    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
    Fichiers attachés Fichiers attachés

  19. #19
    Expert confirmé

    Profil pro
    Inscrit en
    mai 2005
    Messages
    3 419
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : mai 2005
    Messages : 3 419
    Points : 4 224
    Points
    4 224
    Par défaut
    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é
    Elle est pas belle la vie ?

  20. #20
    Nouveau membre du Club
    Inscrit en
    septembre 2007
    Messages
    57
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 57
    Points : 34
    Points
    34
    Par défaut
    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.

Discussions similaires

  1. Réponses: 13
    Dernier message: 21/07/2011, 09h41
  2. Réponses: 0
    Dernier message: 15/07/2011, 12h10
  3. une alternative aux variables globales ?
    Par scheme dans le forum Général Python
    Réponses: 18
    Dernier message: 24/07/2009, 00h55
  4. Je cherche une alternative aux frames
    Par Gizmil dans le forum Général Conception Web
    Réponses: 9
    Dernier message: 02/11/2007, 08h40
  5. Je cherche une alternative aux frames
    Par Gizmil dans le forum Général JavaScript
    Réponses: 1
    Dernier message: 29/10/2007, 14h25

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo