Forum |  HardWare.fr | News | Articles | PC | S'identifier | S'inscrire | Shop Recherche
1288 connectés 

  FORUM HardWare.fr
  Windows & Software
  Logiciels

  [Excel] - gestionnaire de noms et variables dans formules

 



 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

[Excel] - gestionnaire de noms et variables dans formules

n°2959395
krapaud
Modérateur
Posté le 31-08-2010 à 17:45:58  profilanswer
 

Bonjour,
 
J'ai le problème suivant :  

  • j'ai une feuille de calcul dans laquelle je réalise un certain nombre d'opérations simples sur des valeurs;
  • j'ai des classeurs excel qui contiennent les valeurs utiles pour les calculs à réaliser
  • les classeurs bougent tout le temps...


Bref, c'est une vraie galère pour fonctionner et j'aimerai rationaliser les choses.
Exemple de ce qui est fait aujourd'hui :  
 

classeur 1
feuille 1
reference   prix   libelle
test        test2  test3
feuille 2
reference   prix   libelle
XXX        XXX2  XXX3


 

feuille de calcul
reference   prix   libelle   remise   prix net
test      


Dans ce mode de fonctionnement, pour les prix et le libellé je fais une recherche (fonction RECHERCHEV) pour obtenir les informations avec la clé "reference".
Le problème en pareil cas c'est que je gère unitairement chaque feuille de chaque classeur dans lesquels je vais chercher les données.
Sachant que non seulement j'ai plein de fichiers de références contenant une a plusieurs feuilles, mais en plus les fichiers sont régulièrement renommés et les chemins d'accès changent.
 
J'ai essayé de comprendre le fonctionnement de la fonction INDIRECT, mais honnêtement je galère vraiment.
 
Ce que je souhaiterai c'est :  
- créer un formulaire de saisie du chemin (ex. D:\donnees\excel\ )
- créer un formulaire de saisie du classeur de référence (ex. fichier.xlsx )
- créer un formulaire de saisie du nom de l'onglet concerné (ex. datas)
- créer un formulaire de saisie de la plage concernée par les données (ex. $B$1:$G$6000)
- créer un formulaire de saisie des correspondances (ex. colonne prix = colonne 2 ; colonne libelle = colonne 5)
 
Avec pour but au final de pouvoir réaliser "automatiquement" la fonction :  
=SI(B4<>"";(RECHERCHEV(B4;'D:\donnees\excel\[fichier.xlsx]datas'!$B$1:$G$6000;5;0));"" )
 
Problème : sans être une bille en Excel, je ne suis pas forcément très compétent, surtout s'il s'agit des formulaires et autres menus du mode développeur...
 
Alors je compte sur votre aide :D
 
Merci :jap:


Message édité par krapaud le 02-09-2010 à 09:05:34
mood
Publicité
Posté le 31-08-2010 à 17:45:58  profilanswer
 

n°2959494
olivthill
Posté le 01-09-2010 à 11:32:16  profilanswer
 

Bonjour,
 
Je suppose que le smiley est un "up".
Je n'ai pas répondu avant parce que je n'ai pas bien compris la question, et c'est peut-être le cas pour les autres.
Vous dites : "Je souhaiterais..." Ok, alors allez-y. Où est le problème ?
 
Vous dîtes qu'il y a beaucoup de changements;
Est-ce que les formules arrivent à suivre ces changements ?
Théoriquement, elles devraient arriver à se mettre à jour automatiquement quand une cellule, une ligne ou une colonne, est insérée ou supprimée.
S'il vous faut remettre à jour manuellement une ou plusieurs formules, c'est probablement par ce qu'il y a une mauvais utilisation des symboles "$" dans le référencement des cellules. La présence ou l'absence du dollar sert à indiquer si c'est une référence fixe ou non.
S'il y des changements et que vous trouvez que vos formules sont peu lisibles, vous pouvez utiliser les "plages nommées", au lieu d'utiliser des références de cellules.
Pour le dollar, et les plages nommées, voir votre doc, ou par exemple http://www.linternaute.com/pratiqu [...] mules.html
 
La fonction INDIRECT a plusieurs variantes. Voici des exemples :
 
=INDIRECT("'"&D18&"'!G18" )
Référence à la cellule G18 de la feuille dont le nom se trouve dans la case D18
 
=INDIRECT(CELLULE("contenu";A1)&"!E1" )
=INDIRECT(ADRESSE(1;5;1;;A1))  
Les deux formules sont équivalentes et servent à récupérer le contenu de la cellule E1 pour la feuille dont le nom est dans la cellule A1.
 
=INDIRECT("L7C"&CELLULE("contenu";C6)+2; FAUX)
Met dans une cellule, le contenu d'une cellule de la ligne 7, en fonction du mois contenu dans la celule C6
(je ne me souviens plus très bien du pourquoi du +2, mais c'était un exemple qui marchait bien dans mon cas).
 

n°2959500
krapaud
Modérateur
Posté le 01-09-2010 à 12:07:23  profilanswer
 

alors je n'ai pas été clair :D
 
Le problème que j'ai c'est que pour des formules identiques, je vais utiliser des bases de données excel réparties dans plusieurs fichiers différents, sur plusieurs serveurs différents, et enfin qui sont régulièrement renommés.
 
Je sais faire un fichier de calcul pour une base de données (enfin du moins pour une feuille de calcul dans un classeur donné) mais je ne sais pas unifier tout ça dans un seul et même fichier.
 
Pour détailler, disons que je travaille sur des catalogues de produits, et mon travail est d'appliquer des remises, des taux de change etc... aux produits afin de produire une offre .
 
Mettons que j'ai trois catalogues nommés respectivement :  
- fournisseur1.xls
- fourniss_aout_2.xls
- four_3 aout final.xls
 
Et tous les mois, le nom de chacun de ses fichiers peut changer. Pour le contenu la structure reste identique donc pas de soucis.
 
Mettons que j'ai une feuille de calcul pour un produit du fournisseur1, ce produit étant détaillé dans le catalogue fournisseur1.xls à la page pinard2.
 
Dans mon tableau, je vais appliquer ma formule recherchev pour simplement rentrer la référence que je connais, et obtenir de facto tous les éléments liés à cette référence et listés dans le fichier fournisseur1.xls à la page pinard2.
 
Pour fonctionner ainsi, j'ai un fichier de calcul par feuilles de classeurs et en plus je dois constamment remettre à jour les formules avec le nouveau nom du fichier de référence (ex. fourniss_aout_3.xls devient fourniss_sept_3.xls).
 
Dans mes essais, INDIRECT ne m'aide pas puisque je ne parviens pas à aller chercher l'information dans une structure (colonne A à G par exemple), pas plus que la concaténation des informations (nom du classeur, nom de la feuille, plage de données).
 
Je cherche donc le moyen de mettre dans ma formule recherchev, dans la plage de données, le lien vers le classeur, la feuille et la zone de données.
En grossissant le tableau je voudrais avoir :  
var = 'D:\donnees\excel\[fichier.xlsx]datas'!$B$1:$G$6000
et une formule :  
RECHERCHEV(B4;var;5;0));""  
 
Le gestionnaire de noms est-il la solution, ou est-ce qu'il y a plus simple...?
 
Merci

n°2959521
krapaud
Modérateur
Posté le 01-09-2010 à 14:57:21  profilanswer
 

bon, j'ai un début de réponse qui pourrait presque convenir : en utilisant le gestionnaire de noms (ctrl+F3), je variablise mon fichier, l'onglet et la zone de recherche.
Est-il possible de créer un menu déroulant en en-tête dans lequel je sélectionne le nom créé pour l'appliquer dans ma formule de recherche?

n°2959526
seniorpapo​u
Posté le 01-09-2010 à 15:36:46  profilanswer
 

Bonsoir,
tu étais peut-être sur la bonne voie avec ton
 RECHERCHEV(B4;var;5;0));""  
as-tu essayé de placer dans des cellules  les différents éléments qui définissent ta matrice (chemin,classeur,onglet..),  puis dans une cellule G1 par exemple une formule "CONCATENER" prenant en compte les différentes cellules variables, avec les éparateurs adéquates (!:etc  )et enfin de mettre à la place de ton "var" : INDIRECT(G1)
Cordialement

n°2959527
krapaud
Modérateur
Posté le 01-09-2010 à 15:39:02  profilanswer
 

j'avance!
 
Dans ma liste de noms je crée les références suivantes :  

DATA1    ='D:\donnees\excel\[fichier.xlsx]datas'!$B$1:$G$6000      
 COEFF2  ='\\serveur\partage\excel\[coef.xls]coef'!$B$11    
 CHANGEX  ='c:\windows\exc\[classeur.xlsx]changex'!$B$1:$J$10      


Est-il possible de créer une liste déroulante dans laquelle je vulgarise les noms (par ex. COEFF2 est présenté comme "coefficient de pondération" ), et quand je sélectionne cette ligne dans la liste déroulante ma formule RECHERCHEV est mise à jour :  
RECHERCHEV(B4;DATA1;5;0) ou
RECHERCHEV(B4;COEFF2;5;0) ou
RECHERCHEV(B4;CHANGEX;5;0)
 
Merci!
 
Donc trois étapes dans la création de mon fichier :  
1/ mettre à jour le gestionnaire de noms
2/ créer une liste avec un libellé X correspondant à un élément du gestionnaire de noms
3/ mettre à jour la formule suivant la sélection opérée dans la liste
 
 
:jap:

n°2959528
krapaud
Modérateur
Posté le 01-09-2010 à 15:40:30  profilanswer
 

seniorpapou a écrit :

Bonsoir,
tu étais peut-être sur la bonne voie avec ton
 RECHERCHEV(B4;var;5;0));""  
as-tu essayé de placer dans des cellules  les différents éléments qui définissent ta matrice (chemin,classeur,onglet..),  puis dans une cellule G1 par exemple une formule "CONCATENER" prenant en compte les différentes cellules variables, avec les éparateurs adéquates (!:etc  )et enfin de mettre à la place de ton "var" : INDIRECT(G1)
Cordialement


:hello:
 
Oui j'ai essayé, mais malheureusement sans résultat. Je ne sais pas pourquoi il ne veut pas en tenir compte :/

n°2959542
seniorpapo​u
Posté le 01-09-2010 à 16:47:29  profilanswer
 

Bonsoir,
désolé, pour que INDIRECT fonctionne il faut que le "classeur externe" soit ouvert.
cordialement

n°2959543
krapaud
Modérateur
Posté le 01-09-2010 à 16:47:36  profilanswer
 

le mieux que j'arrive à faire :  
je crée une liste depuis les données du gestionnaire de noms, comprenant toutes mes références.
 


 DATA1    ='D:\donnees\excel\[fichier.xlsx]datas'!$B$1:$G$6000      
 COEFF2  ='\\serveur\partage\excel\[coef.xls]coef'!$B$11    
 CHANGEX  ='c:\windows\exc\[classeur.xlsx]changex'!$B$1:$J$10


 
Ensuite je choisis une cellule (D33) sur laquelle, avec le menu validation de données, je fige une liste déroulante dans les noms créés précédemment.
Une fois la sélection effectuée, je sélectionne la cellule contenant ma formule RECHERCHEV (E26) et je remplace le champs qui va bien.
 
Avec l'enregistrement de macro ça donne ça :  
 
 

Code :
  1. Sub Macro1()
  2.     Range("D33" ).Select
  3.     ActiveCell.FormulaR1C1 = "MLOAD"
  4.     Range("E26" ).Select
  5.     ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-1]C[-1],MLOAD,2,FALSE)"
  6.     Range("D33" ).Select
  7.     ActiveCell.FormulaR1C1 = "MAINTDISC"
  8.     Range("E26" ).Select
  9.     ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-1]C[-1],MAINTDISC,2,FALSE)"
  10. End Sub


 
 
Maintenant il faudrait qu'au lieu d'écrire dans la barre de formule le résultat de la sélection en D33, il le stocke et l'écrive à la bonne position dans la formule de la cellule E26.

n°2959544
krapaud
Modérateur
Posté le 01-09-2010 à 16:49:19  profilanswer
 

dans l'exemple ci-dessus :
DATA1 = MLOAD
MAINTDISC = COEFF2
 
pour la compréhension ;)

mood
Publicité
Posté le 01-09-2010 à 16:49:19  profilanswer
 

n°2960642
krapaud
Modérateur
Posté le 09-09-2010 à 17:50:21  profilanswer
 

up! :)

n°2960859
seniorpapo​u
Posté le 11-09-2010 à 11:03:11  profilanswer
 

Bonjour,
tu peux essayer ceci:
mettre dans le VB de feuil1...
 
J'ai la fenêtre de mise à jour qui s'ouvre et cela me dérange, mais ce ne sera pas le cas chez toi.
 

Code :
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. If Target.Address = "$D$33" Then
  3. Application.EnableEvents = False
  4. rere = "=vlookup(a1," & Range("D33" ).Value & ",2,false)"
  5. Range("E26" ).Formula = rere
  6. Application.EnableEvents = True
  7. End If
  8. End Sub


 
Si tu n'as que trois chemins, tu peux aussi créer un bouton par chemin et attacher à chaque bouton une macro qui génère ton formula.
 
Cordialement


Message édité par seniorpapou le 11-09-2010 à 11:06:50
n°2961220
krapaud
Modérateur
Posté le 13-09-2010 à 11:19:25  profilanswer
 

Merci :jap:
 
Entre temps j'en étais arrivé là :  

Code :
  1. Sub Bouton6_Clic()
  2. varliste = Range("E29" ).Value
  3. Range("E26" ).FormulaR1C1 = "=VLOOKUP(R[-1]C[-1]," & varliste & ",2,FALSE)"
  4. End Sub


 
Je vais me servir de tes éléments pour perfectionner un peu la macro, voire si possible l'intégrer dans une fenêtre de contrôle (sélection de la cellule cible... liste déroulante de noms...)
 
Merci encore pour ton aide :jap:


Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Windows & Software
  Logiciels

  [Excel] - gestionnaire de noms et variables dans formules

 

Sujets relatifs
Fichiers Word transformés en .plist/fichiers BDD/etc. Help...Pb infobulle fichiers vidéo
[Excel 07] Copie de ligne groupéePublipostage entre Word et Excel ; problème de macro, besoin d'aide !
pb avec les apercus de fichiers[excel] TDC min de "différence par rapport"
problème ouverture excel 2007[Aide] Fichiers effacés sur le second disque dur !
Modification date acces fichiers photos xpRécupérer de fichiers effacés ?
Plus de sujets relatifs à : [Excel] - gestionnaire de noms et variables dans formules


Copyright © 1997-2022 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR