Excel 2003 - Exercice complexe Fermé

Question

Bonjour,

Je dois réaliser un exercice pour un cours sous Microsoft Office Excel 2003 et je peux demander à d'autres personnes un coup de main, je vous rassure. Le but étant de trouver une solution par n'importe quel moyen. Le professeur nous a dit, quand vous travaillerez, vous devrez parfois demander des informations à d'autres personnes pour arriver à réaliser un projet, car même un professeur ne peut pas connaître un domaine à 100 %.

J'ai un classeur (1) avec des feuilles 00001, 00002… contenant diverses informations (pour l'exemple je n'entrerai pas dans des détails superflu).

A1: numéro du client : 1250
A2: 10 commandes passées chez nous
A3: le client a commandé pour 2500 euros chez nous
... (bien sûr dans la réalité les feuilles contiennent beaucoup plus d'informations).

Ces informations ne sont pas toujours sur la même ligne, car il y a des lignes vides ou d'autres informations qui s'intercalent entre dont je n'ai pas besoin, ces informations varient suivant le client.

Sur un autre classeur (2) j'ai un tableau en colonne

Numéro du client - Nombre de commandes passées - Gain… (dans le tableau, j'utilise des abréviations, j'ai mis les noms complets ici pour plus de compréhension).

Le but de l'exercice est de transférer les données des feuilles du classeur (1) dans le tableau du classeur (2), le plus rapidement possible et avec une précision de 100 % (donc je dois éviter de retaper les informations car il pourrait y avoir des erreurs).

Donc, je pensais réaliser l'exercice comme suite:

Ajouter une première colonne (appelons-là, référence) dans le tableau du classeur (2) qui reprendrait le numéro de la feuille du classeur (1) dont les informations viennent.

Sélectionner toutes les feuilles du classeur (1) et puis sélectionner la première colonne.

Ensuite, je voulais effectuer l'opération suivante: Données > Délimité > Cocher Espace > Suivant > Terminer pour avoir une partie d'une information dans plusieurs colonne d'une ligne, malheureusement cela ne marche pas quand je suis en groupe de travail.

Après, je pensais effectuer les opérations ci-dessous dans le tableau du classeur (2) en partant de l'information dans la colonne référence (pour l'exemple, je prendrai A2 = 00001 (numéro de la première feuille du classeur (1))).

En B2 dire au système qu'il recherche "client" dans la feuille 00001 et qu'il prenne l'information correspondante de la colonne E (le numéro du client).

En C2 dire au système qu'il recherche "commandes" dans la feuille 00001 et qu'il prenne l'information correspondante de la colonne A (nombre de commandes passées).

En D2 dire au système qu'il recherche "euros" dans la feuille 00001 et qu'il prenne l'information correspondante de la colonne F (gain).

Mes questions sont:

Comment dois-je faire pour réaliser l'opération Données > Délimité... sur plusieurs feuilles?

Dans un tableau y a-t-il moyen d'utiliser une référence (nom ou numéro) et dire au système d'aller chercher des informations dans la feuille ayant le même nom ou numéro?

Y a-t-il moyen de dire au système de recherche un mot dans une feuille et lui dire de prendre l'information correspondante (sur la même ligne) dans une autre colonne?

Quand je vais tirer ma formule le système va-t-il prendre en compte la référence (nom ou numéro) suivant (sa je sais que oui) et aller chercher les informations de la nouvelle feuille ayant le même nom ou numéro?

Je ne sais pas si en expliquant en phrases comme ceci j'ai été assez clair.

Avez-vous une solution pour résoudre l'exercice?

Au fait, nous avons tous un exercice différent suivant notre niveau et parfois le professeur donne des exercices soit irréalisables ou très difficilement réalisables pour essayer que nous évoluions même si nous sommes plus doués que les autres. Je pense que c'est bien, car même les plus doués apprennent quelque chose mais d'un autre côté, nous ne savons pas toujours si l'exercice est réalisable ou pas.

Désolé pour ce long message et merci pour votre aide.

eriiic · Answer

Bonjour,

Tu as bien expliqué mais ce n'est pas très visuel...
Tu devrais déposer un classeur exemple sur cijoint.fr (en reprenant les explications : dans telle cellule je veux la valeur de telle feuille telle colonne avec tel mot clé dans telle colonne...) et coller ici le lien fourni
eric

demandes · Answer

Bonjour eriiic,

Je ne suis pas chez moi et je n’ai pas l’exercice complet. Cependant, je l’ai "recréé" plus simplement.

http://www.cijoint.fr/cjlink.php?file=cj200905/cij109tGY8.xls
http://www.cijoint.fr/cjlink.php?file=cj200905/cijreCRLky.xls
http://www.cijoint.fr/cjlink.php?file=cj200905/cijahdEfxT.xls

Maintenant ce sera valable uniquement si c'est possible de réaliser l'opération suivante sur plusieurs feuilles d'un coup (j’ai dû le faire feuille par feuille, donc ce n’est peut-être pas la solution la plus adéquate): Délimité > Cocher Espace > Suivant > Terminer pour avoir une partie d'une information dans plusieurs colonne d'une ligne

Autrement en partant du classeur 1 - Avant transformation, il y a peut-être une solution plus simple.

Je vous remercie pour vos conseils.

PS: j’espère que mes classeurs seront clairs…

eriiic · Answer

Re,

Tu n'es pas obligé de redistribuer tes données dans différentes colonnes.

Pour retrouver le n° de ligne où se trouve client ou commandes tu vas utiliser equiv() (regarde l'aide excel)
ex :
=EQUIV("*commandes*";'[Classeur1.xls]01'!$A:$A;FAUX) te retourne bien 7.

mais comme le nom de la feuille est variable on va utiliser indirect(). 
Il faut refabriquer la référence '[Classeur1.xls]01'!$A:$A en une chaine de caractère, et utiliser B2 où se trouve le nom de la feuille
=> INDIRECT("'[Classeur1.xls]" & B2 & "'!$A:$A")
et la formule finale : 
=EQUIV("*commandes*";INDIRECT("'[Classeur1.xls]" & B2& "'!$A:$A");FAUX)
Dans l'exemple j'ai également utiliser une cellule pour le nom du classeur Classeur1.xls dans la recherche de la ligne du n° client.

Pour récuperer les valeur il faut utiliser les fonction de traitement de chaine de caractère (stxt() gauche() droite()...) et remplacer la référence par indirect(sa_chaine_fabriquée)
ex pour le n° de client :
=CNUM(STXT(INDIRECT("'["&A2&"]" & B2& "'!$A$1");20;50))

Je te laisse terminer sur le même principe
Classeur2.xls

Pour rechercher le total en euros ça sera un peu plus compliqué. Utilise cherche(), tu auras besoins de la position de euros dans la chaine pour extraire le bon nombre de caractères de ta somme.
eric

demandes · Answer

Bonsoir eriiic,

Merci pour tout, j'essayerai prochainement, car mardi j'ai un examen et il faut bien étudier lol. De plus, l'exercice réel contient beaucoup plus d'informations à extraire, il me faudra du temps pour trouver comment prendre toutes ces informations.

Par contre je ne sais pas si je dois mettre résolu ou pas donc je vais laisser comme cela en attendant.

Bonne soirée

PS: merci l’explication sur certaines fonctions que je ne connaissais pas.

eriiic · Answer

Pas de soucis...
Par contre j'avais oublié un truc dans la recherche du n° client, c'est d'utiliser le n° de ligne récupéré avant (ben oui, il faut bien qu'il serve).
Donc la formule en H2 est :
=CNUM(STXT(INDIRECT("'[" & A2 & "]" & B2 & "'!$A$" & G2);20;50))
à recopier vers le bas.
On a une indirection sur le nom du classeur, sur le nom de la feuille, et sur le n° de ligne

eric

demandes · Answer

Bonjour eriiic,

Désolé pour le retard, mais j'ai eu des soucis avec ma connexion Internet. J'ai essayé de comprendre tes explications, mais n'étant pas sûr d'avoir compris exactement où je devais utiliser les deux formules (dans quelle cellule), je préfère demander.

Je pense que qu'en B2 j'aurai la formule =CNUM(STXT(INDIRECT("'[" & A2 & "]" & B2 & "'!$A$" & G2);20;50)) mais où dois-je placé la formule =EQUIV("*commandes*";INDIRECT("'[Classeur1.xls]" & B2& "'!$A:$A");FAUX)?

Par contre pour le montant en euros, le nombre de caractères avant le montant est toujours le même "le client a commandé pour " soit 26 caractères espaces compris.

Au fait, le Classeur2 que tu as joins est vide, est-ce normal?

J'ai une autre question (tant que j'y suis), j'ai remarqué que dans certaines feuilles (voir feuille 4 dans le classeur d'exemple), il y avait:

2008
10 commandes passées chez nous
2007
15 commandes passées chez nous

Il y a bien sûr des informations entre celles-ci, mais ma question est: y a-t-il moyen de lui dire dans telle cellule tu prends l'information 2008 (13) et dans telle autre cellule tu prends l'information 2007 (15)?

Je sais que je dois être emmerdant car cet exercice ne doit pas être simple, mais j'aimerais arriver à le résoudre si c'est possible.

Je joins les deux classeurs d'exemples que j'ai un peu modifié.

https://www.cjoint.com/?fFj0SNjc0F
https://www.cjoint.com/?fFko3dZP5Y

Je te remercie pour tous tes conseils et te souhaite une bonne journée.

demandes · Answer

Bonjour eriiic,

Il y a une petite erreur de frappe: "10 commandes passées chez nous" devient "13 commandes passées chez nous".

MERCI

eriiic · Answer

Bonjour,

ton classeur_2  avec l'exemple pour retrouver le n° de client. J'ai décomposé la formule sur les colonnes suivantes.

2008
10 commandes passées chez nous
2007
15 commandes passées chez nous 

Ca complexifie beaucoup et ça va faire des formules imbuvables. Fais toi déjà la main sur des fichiers simples. 
Il faut que tu obtiennes des extractions plus 'propres'. Sans même cette histoire d'année c'est bien se compliquer la vie de récupérer les infos sur ton fichier alors que si l'extraction était correcte une formule toute simple suffirait.

eric

demandes · Answer

Bonjour eriiic,

Je te remercie énormément pour tes conseils.

Je crois avoir compris toute la formule (ou presque) dont j'avais besoin (B2+F2). Cependant, je te remercie pour les autres formules, elles me seront sûrement utile dans d'autres cas.

Ce qui fait un formule totale par exemple pour B2 =CNUM(GAUCHE(INDIRECT("'[Classeur_1.xls]"& A2&"'!$A$"&EQUIV("*commandes*";INDIRECT("'[Classeur_1.xls]"&A2&"'!$A:$A");FAUX));CHERCHE(" ";INDIRECT("'[Classeur_1.xls]"&A2&"'!$A$"&EQUIV("*commandes*";INDIRECT("'[Classeur_1.xls]"&A2&"'!$A:$A");FAUX)))-1))

Par contre pourquoi faut-il des ' dans la fonction INDIRECT exemple: …'[Classeur_1.xls]…?

Dommage qu'il faille que l'autre classeur (base de données) soit ouvert avec la fonction INDIRECT

Je suppose que le -1, est utilisé pour supprimer l'espace que le système prend dans la recherche pour la fonction GAUCHE (d'ailleurs, je n'ai pas très compris comment le système détermine le nombre de caractères à prendre, mais je n'ai pas encore tout analysé à fond).

Par contre pour les cas "euros" et "quand il y a plusieurs années", je ne pense pas trouver une solution "raisonnable".

Malheureusement, il n'y a pas moyen d'avoir des extractions plus propres, car elles dépendent d'un système que je ne peux changer.

J'avoue que je n'aurai pas su trouver cette longue formule moi-même, car je ne connaissais pas les fonctions utilisées. De plus, les imbriquées l'une dans l'autre est ardu.

Je vais essayer maintenant d'appliquer la formule à l'exercice réel, je te tiendrai au courant…

PS1: je pensais avoir un bon niveau en bureautique, mais quand je vois les BIG BOSS ici, je me rends compte que mon niveau est "faible".

PS2: encore un grand merci et bon appétit si tu passes à table…

demandes · Answer

une formule totale (désolé)

demandes · Answer

Bonjour eriiic,

J'ai pu utiliser la formule pour mon exercice sauf pour les "euros" et "quand il y a plusieurs années" où il faudra que je trouve une solution…

J'ai voulu aussi expérimenter cette formule sur un jeu auquel je joue (gestions de ressources…) où il y a des rapports de combat assez complexe sur lesquels, j'aimerais faire des statistiques. Cependant, j'aurai besoin des formules "euros" et "quand il y a plusieurs années" pour arriver à un résultat optimal mais bon, on verra plus tard.

En attendant, je ne vais pas détailler tout un rapport de combat, car ils sont parfois immenses, mais, j'ai un petit souci qui pourrait être peut-être résolu facilement. En effet, Excel ne reconnaît pas la casse (majuscules ou minuscules pour Excel, c'est pareil).

Y a-t-il un moyen de lui faire tenir compte de cette casse? Pour qu'il considère "Cables" et "cables" différemment (je râle car pour "métal" et "Metal", là, il fait la différence). Pour mieux comprendre, voici une partie du rapport de combat:

Débris de métal récupérés : 2250.
Débris de cables récupérés : 1500 .
Débris de piles récupérés : 1000 .

Vos Gains :
321701 Metal 
7693 Cables 
5453 Piles 
0 Microns 
4241 Platine 
0 Energie

Pour les débris, je devrai aussi utiliser une autre formule sûrement beaucoup plus compliquée, on verra plus tard. Cependant, j'ai réussi avec: "Débris de cables 1500" et la formule: =CNUM(DROITE(INDIRECT("'[Essais 3 - Classeur 1 - BDD.xls]"&A2&"'!$A$"&EQUIV("*cables*";INDIRECT("'[Essais 3 - Classeur 1 - BDD.xls]"&A2&"'!$A:$A");FAUX));CHERCHE(" ";INDIRECT("'[Essais 3 - Classeur 1 - BDD.xls]"&A2&"'!$A$"&EQUIV("*cables*";INDIRECT("'[Essais 3 - Classeur 1 - BDD.xls]"&A2&"'!$A:$A");FAUX)))-2)).

Au fait, si je laisse -1 "#VALEUR!", si je mets -2 "1500", si je mets -3 "1500", si je mets -4 "500", si je mets -5 "0", je n'ai pas très bien compris là, mais bon.

Désolé d'abuser de ton temps, mais j'aime bien évoluer dans l'utilisation d'Excel, il me semble que cela me sera bénéfique pour un emploi plus tard.

Je te remercie encore pour tous tes conseils qui m'ont permis déjà d'évoluer et de réaliser une partie de l'exercice pour le cours.

PS: certaines fautes d'orthographe (cables à la place de câbles sont intentionnelles car le rapport de combat est comme cela).

eriiic · Answer

Bonjour,

Beaucoup trop de demandes (sans et avec jeu de mot sur ton pseudo).... ;-)

Je répond au plus simples que tu avances un peu.
Par contre pourquoi faut-il des ' parce que c'est la syntaxe, et ne demande pas pourquoi le classeur est entre [ ].  
Le plus simple pour avoir la bonne syntaxe : tu te mets dans une cellule, tu tapes = et tu vas activer le classeur qui t'interesse, puis l'onglet, pour cliquer sur la cellule que tu veux, et entrée.

Par contre pour les cas "euros" et "quand il y a plusieurs années", je ne pense pas trouver une solution "raisonnable". 
Oui, on atteint la limite du raisonnable pour les formules, trop compliquées si elles se mettent à plus marcher il est souvent plus simple de la reconstruire que d'essayer de l'adapter.
Je te conseille quand tu seras pret de te pencher sur vba qui simplifie ces problèmes. 
Un exemple vite fait mais pas le temps de mettre trop d'explications : faire une fonction personnalisée.Function SommeTotale(source As Range, année As Long) As Double
    Dim c As Range, s As String, r As Double
    ' si année présente
    For Each c In source
        If c.Value = "année " & année Then
            s = c.Offset(4, 0).Value
            Exit For
        End If
    Next c
    ' année non trouvée
    If s = "" Then
        For Each c In source
            If Left(c.Value, 26) = "le client a commandé pour " Then
                s = c.Value
            Exit For
            End If
        Next c
    End If
    r = Mid(s, 27, InStr(s, " euros") - 27)
    SommeTotale = r
End Functionet la syntaxe devient :
=SommeTotale('[Classeur_1.xls]01'!$A$5:$A$18;2008)
(je n'ai pas mis d'indirection pour ne pas embrouiller)
[ http://www.cijoint.fr/cjlink.php?file=cj200906/cijWqOd2lI.xls Exemple] pour te mettre l'eau à la bouche si tu ne sais pas ajouter du vba.

Y a-t-il un moyen de lui faire tenir compte de cette casse? Pour qu'il considère "Cables" et "cables" différemment
Pour equiv() non. 
Pour cherche() tu as trouve() qui tient compte de la casse.
Accessoirement tu peux savoir s'il un mot commence par une majuscule avec =CODE(A4)<97. 
Si VRAI: majuscule, si FAUX: minuscule.

"métal" et "Metal" : fonction personnalisée

Pour mieux comprendre, voici une partie du rapport de combat: 
J'ai l'impression que la structure est toujours la même, en attendant mieux tu peux tenter ça :
Coller en colonne A, menu 'données / convertir...' délimité avec espace et :
Les données qui t'interessent devraient toujours être dans la même cellule et tu te fais une synthèse avec des formules bcp plus simples dans une autre feuille.

eric.

demandes · Answer

Bonjour eriiic,

Je te remercie d'avoir répondu aussi rapidement, oui je sais j'en demande beaucoup... Au fait, il y a beaucoup de i dans ton prénom (rire).

Les crochets me semblaient normaux dans la syntaxe pour attirer l'attention que c'était un nom de fichier et délimiter son nom/extension. Néanmoins, j'ai fait l'essai avec le = mais il faut que les classeurs soient ouverts avant.

Pour reconstruire une formule comme tu m'as fourni, il faut vraiment bien connaître et maîtriser les fonctions et toutes leurs propriétés. Parfois, j'y arrive (longues fonctions SI imbriquées), mais là, cela me semble "ingérable", bien que je n'aie pas encore étudié la question sous tous ces aspects. De plus, les fonctions ont souvent plusieurs utilisations.

Exemple, au cours j'avais appris à utiliser la RECHERCHE VERTICALE pour une utilisation bien précise (recherche d'une donnée par rapport à un numéro client dans une base de données) et au stage, quelqu'un m'a montré une autre utilisation à laquelle je n'aurai pas pensé (comparaison de deux colonnes similaires entre deux bases de données).

J'aimerais sincèrement apprendre le VBA, car je me rends compte que dans certains cas, nous devons passer par là. D'ailleurs, j'aimerais aussi apprendre la programmation des sites Internet pour des projets futurs (création d'un site avec des leçons, des test…; par exemple, un test sur les verbes irréguliers en anglais, avec une vérification et une correction automatique…). Au fait, sais-tu si le VBA peut m'être utile pour un site Internet? Si tu connais des sites Internet intéressants sur la programmation, je suis tout ouïe.

Pour ta macro, je l'essayerai prochainement. Malheureusement, j'ai encore des examens à étudier, notamment, une partie en anglais mercredi (vocabulaire et grammaire). Pour l'exercice, j'ai encore du temps pour le terminer.

Pour la casse, j'essayerai avec les fonctions que tu m'as indiquées. Pour la structure, malheureusement, elle est toujours la même. J'avais pensé à la fonction Données/Convertir…, mais le problème est que nous ne pouvons l'utiliser sur une feuille à la fois et pas sur un groupe de feuilles (autrement j'aurai déjà résolu, une grosse partie des problèmes, sauf le fait que des mêmes informations ne soient pas toujours sur la même ligne; ce qui est résolu en partie avec ta formule sauf quand il y a plusieurs fois le même terme).

Si tu as une solution pour utiliser la fonction Données/Convertir… sur plusieurs feuilles cela m'arrangerait énormément. Je devrai néanmoins utiliser ta formule, car les mêmes informations ne seront pas toujours sur la même ligne (je sais c'est très embêtant) et puis trouver une solution pour utiliser ta formule quand il y a plusieurs termes les mêmes; à suivre donc…

Ceci dit, je pense que tu m'as déjà énormément aidé et par la même occasion, appris (tu dois être formateur comme m@rina (ce n'est pas possible autrement (rire))). Néanmoins, avec les projets que j'ai, je devrai certainement encore vous ennuyer plusieurs fois, avant d'être capable de réaliser tout ce que souhaiterait.

Je te remercie mille fois pour ton partage d'expérience et ta générosité. Au plaisir de te lire…

PS: tout d'un coup j'ai un mal de tête (rire), le temps passé sur l'ordinateur doit être une des conséquences (rire).