1. #1
    Candidat au Club
    Homme Profil pro
    Chargé de projets
    Inscrit en
    juin 2017
    Messages
    2
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Ille et Vilaine (Bretagne)

    Informations professionnelles :
    Activité : Chargé de projets

    Informations forums :
    Inscription : juin 2017
    Messages : 2
    Points : 2
    Points
    2

    Par défaut Formule matricielle | Nom d'une plage nommée en paramètre ?

    Bonjour,

    Dans une formule matricielle qui comprend plusieurs conditions je souhaite que ma formule soit la plus générique (valable pour tout le tableau) en fonction des paramètres.
    Le fichier d'exemple en pièce jointe illustre mon problème: Les conditions marchent bien mais la variable à calculer varie en fonction de la colonne (BO_P puis BIB puis MB) et ce paramètre génère une erreur. J'ai essayé INDIRECT() sans succès. Comment faire pour avoir en paramètre le nom d'une plage nommée?

    Merci de vos lumières,
    Cordialement,
    G.
    Nom : Capture.JPG
Affichages : 35
Taille : 177,5 Ko
    Fichiers attachés Fichiers attachés

  2. #2
    Responsable
    Office & Excel

    Avatar de Pierre Fauconnier
    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    10 392
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur informatique indépendant
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 10 392
    Points : 25 760
    Points
    25 760
    Billets dans le blog
    4

    Par défaut

    Salut.


    Pour moi, ton tableau source est mal formé.

    Pourquoi?
    Parce le type (BO_P, BIBE, ...) est une dimension du tableau, alors que ton tableau crée trois dimensions différentes.

    Normalement, le tableau devrait se présenter comme sur l'illustration suivante, et tu pourrais alors utiliser une somme matricielle, ou même un tableau croisé dynamique... Ce serait mille fois plus efficace...

    Nom : 2017-06-16_181118.png
Affichages : 17
Taille : 16,2 Ko
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Vous souhaitez rédiger pour DVP? Contactez-moi
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié l'intervention => Merci pour le
    ---------------

  3. #3
    Nouveau membre du Club
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    août 2012
    Messages
    20
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Maroc

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : août 2012
    Messages : 20
    Points : 33
    Points
    33

    Par défaut les plages non sont pas trés bien nommées

    Tu peux supprimer les noms des plages et simplifier la formule par référence de colonne a calculer :

    =SOMME(D$2$43*(scenario=J$4)*(Periode=$I6)*(Essence=$H$4))
    une seule formule qui marche pour tous le tableau


    Ou bien tu renomme tes plages et normalement avec indirect la formule ca va marcher

  4. #4
    Responsable
    Office & Excel

    Avatar de Pierre Fauconnier
    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    10 392
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur informatique indépendant
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 10 392
    Points : 25 760
    Points
    25 760
    Billets dans le blog
    4

    Par défaut

    Citation Envoyé par mortad123 Voir le message
    Tu peux supprimer les noms des plages et simplifier la formule par référence de colonne a calculer :

    =SOMME(D$2$43*(scenario=J$4)*(Periode=$I6)*(Essence=$H$4))
    une seule formule qui marche pour tous le tableau[...]
    Sur base du tableau proposé par Gil_Abi, j'ai des doutes avec ta formule, qui ne fonctionnera que pour les trois premières colonnes du tableau final.

    Citation Envoyé par mortad123 Voir le message
    [...]
    Ou bien tu renomme tes plages et normalement avec indirect la formule ca va marcher
    As-tu déjà essayé d'utiliser INDIRECT() dans une matricielle pour affirmer cela?
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Vous souhaitez rédiger pour DVP? Contactez-moi
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié l'intervention => Merci pour le
    ---------------

  5. #5
    Responsable
    Office & Excel

    Avatar de Pierre Fauconnier
    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    10 392
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur informatique indépendant
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 10 392
    Points : 25 760
    Points
    25 760
    Billets dans le blog
    4

    Par défaut

    Tu peux utiliser DECALER() en conjonction avec EQUIV(), sur base de ton tableau de départ...

    Nom : 2017-06-16_182346.png
Affichages : 16
Taille : 10,3 Ko
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Vous souhaitez rédiger pour DVP? Contactez-moi
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié l'intervention => Merci pour le
    ---------------

  6. #6
    Candidat au Club
    Homme Profil pro
    Chargé de projets
    Inscrit en
    juin 2017
    Messages
    2
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Ille et Vilaine (Bretagne)

    Informations professionnelles :
    Activité : Chargé de projets

    Informations forums :
    Inscription : juin 2017
    Messages : 2
    Points : 2
    Points
    2

    Par défaut Formule résolue

    Merci à tous pour vos réponses.
    Effectivement la structuration différente de la base aurait pu être une bonne solution mais la façon dont les colonnes sont liées par des calculs ne le permet pas. De plus ma base fait déjà près de 130 000 lignes, Excel a déjà un peu de mal à mouliner!
    Par contre j'ai réussi avec les fonctions DECALER et EQUIV en spécifiant la plage directement dans la formule au lieu de faire appel à son nom. Je me doutais bien qu'il y avait peut-être à faire avec ces formule-là et l'exemple de Pierre m'a mis sur la voie.

    J'ai créé une plage intermédiaire "Titres" (=DECALER(Test!$A$1;0;0;1;NBVAL(Test!$1:$1))) car je ne veux pas figer la base avec des valeurs vérouillées (ajouts de données).

    Merci et bonne soirée.

    Nom : Capture2.JPG
Affichages : 17
Taille : 92,1 Ko

  7. #7
    Responsable
    Office & Excel

    Avatar de Pierre Fauconnier
    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    10 392
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur informatique indépendant
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 10 392
    Points : 25 760
    Points
    25 760
    Billets dans le blog
    4

    Par défaut

    Tu aurais intérêt à travailler avec des tableaux de données (activer une cellule de la plage source puis Insertion>Tableaux>Tableau). Tu pourrais alors utiliser les références structurées. Elles sont un peu verbeuses en 2007 mais ça te permettrait de ne pas devoir prendre toute la colonne (1048576 lignes, tout de même)...

    Attention, en 2007, la syntaxe des références structurées n'est probablement pas pile poil la même, mais c'est l'idée. Pour utiliser la référence structurée, tu peux la saisir à la main, mais pour être certain d'avoir la bonne syntaxe, le plus simple est de sélectionner la colonne du tableau de données (de la ligne 2 à la fin du tableau).

    Nom : 2017-06-16_201736.png
Affichages : 17
Taille : 48,7 Ko

    Nom : 2017-06-16_201810.png
Affichages : 17
Taille : 21,2 Ko

    Nom : 2017-06-16_201859.png
Affichages : 17
Taille : 11,2 Ko


    Pour sélectionner une colonne de tableau, tu peux t'inspirer de l'image suivante qui permet la sélection de la colonne du tableau, et surtout ne pas faire comme sur la dernière illustration car tu sélectionnerais toute la colonne de la feuille...

    Nom : 2017-06-16_202106.png
Affichages : 17
Taille : 11,3 Ko

    Nom : 2017-06-16_202137.png
Affichages : 17
Taille : 16,2 Ko
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Vous souhaitez rédiger pour DVP? Contactez-moi
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié l'intervention => Merci pour le
    ---------------

  8. #8
    Nouveau membre du Club
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    août 2012
    Messages
    20
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Maroc

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : août 2012
    Messages : 20
    Points : 33
    Points
    33

    Par défaut

    Citation Envoyé par Pierre Fauconnier Voir le message
    Sur base du tableau proposé par Gil_Abi, j'ai des doutes avec ta formule, qui ne fonctionnera que pour les trois premières colonnes du tableau final.



    As-tu déjà essayé d'utiliser INDIRECT() dans une matricielle pour affirmer cela?
    Oui bien-sûr çà marche seulement la plage nommé est très grande si non la formule donne un résultat correct

  9. #9
    Responsable
    Office & Excel

    Avatar de Pierre Fauconnier
    Homme Profil pro
    Formateur et développeur informatique indépendant
    Inscrit en
    novembre 2003
    Messages
    10 392
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur informatique indépendant
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 10 392
    Points : 25 760
    Points
    25 760
    Billets dans le blog
    4

    Par défaut

    Citation Envoyé par mortad123 Voir le message
    [...]

    =SOMME(D$2:D$43*(scenario=J$4)*(Periode=$I6)*(Essence=$H$4))[...]
    Si tu mets ta formule en J, tu pointeras vers D avec D$2:D$43. En la recopiant vers la droite, tu pointeras vers E en K, vers F en L, puis vers G en M. Or, en G, il n'y a pas de données. Elle n'est donc pas valable pour tout le tableau. De plus, elle dépend de l'organisation des colonnes. Si on change l'ordre des colonnes dans un des tableaux, ta formule ne peut plus être recopiée latéralement.

    Or, parmi les sept règles capitales d'Excel, on retrouve les deux suivantes:
    • une règle de gestion ne peut être traduite que par une seule formule. Ta formule impose une modification à partir de la colonne M du tableau de résultat;
    • Les résultats des formules ne peuvent pas être impactés par une modification de l'ordre des colonnes ou des lignes. Ta formule impose que les code BO_P et autres soient organisés dans le même ordre dans les deux tableaux.



    C'est pourquoi, pour moi, il est préférable d'utiliser une matricielle comme illustré dans mon message précédent, voire même de retravailler à la source pour présenter un tableau exploitable notamment par TCD. On pourrait alors utiliser tous les outils d'analyse d'Excel puisque les données seraient présentés dans une table de données bien construite.

    Perso, c'est ce que j'imposerais si j'étais aux commandes ;)
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Vous souhaitez rédiger pour DVP? Contactez-moi
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    Vous avez apprécié l'intervention => Merci pour le
    ---------------

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 7
    Dernier message: 24/01/2017, 07h23
  2. Réponses: 3
    Dernier message: 12/09/2013, 11h27
  3. [XL-2007] Comment récupérer les valeurs d'une plage nommée avec le gestionnaire de nom
    Par Mamadou79 dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 27/09/2010, 10h50
  4. [Toutes versions] Accéder aux éléments d'une plage nommée dans une formule
    Par TOM3110 dans le forum Excel
    Réponses: 1
    Dernier message: 13/05/2009, 11h53
  5. Formule avec renvoie d'une plage nommée partielle
    Par doudouallemand dans le forum Excel
    Réponses: 2
    Dernier message: 22/05/2008, 10h25

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