XL 2010 Formule pour récupérer les données d'un autre classeur

[fb62840]

Bonjour à toutes et tous,

J'ai besoin de rédiger une formule compliquée (du moins pour moi).
Il s'agit de reporter la valeur d'une cellule en fonction de plusieurs caractéristiques :
- un onglet
- un nom
- une date

C'est la mise en forme du classeur où se trouve les données à reporter qui me pose problème.

J'aimerais rédiger une formule qui soit valide "partout" où j'ai besoin de faire ce report sans avoir à rédiger une formule différente à chaque fois, et surtout sans avoir à faire un simple "égale".

Merci beaucoup pour votre aide

Les 2 fichiers en pièce jointe vous permettront d'y voir plus clair.

 

[Modeste]

Bonjour fb62840,

Parmi les discussions que tu as déjà ouvertes, le même sujet revient régulièrement ... pour autant que ma mémoire ne défaille pas complètement, il me semble qu'on a déjà évoqué la fonction INDIRECT. Comme celle-ci ne fonctionne pas avec des classeurs fermés, il me semble que des pistes par macro ont été données et sans doute aussi MOREFUNC et la fonction INDIRECT.EXT

... Si le sujet revient, Est-ce que ça veut dire qu'aucune des solutions proposées ne fonctionne, que tu ne vois pas comment adapter, que ta réflexion évolue au même rythme que le classeur sur lequel tu travailles, etc. ?

Ce serait bien que tu dises ici ce que tu as déjà essayé, pour qu'on ne refasse pas un tour complet de la question, ni qu'on soit obligé de lire toutes les autres discussions.

 

[fb62840]

Bonjour,

En effet, mes "problèmes" tournent souvent autour de la récupération de données d'un classeur à l'autre.
Avec l'aide des membres d'ExcelDownload j'ai compris comment procéder avec la fonction INDIRECT

où en premier argument on a la colonne dans laquelle sera prélevée l'information à afficher
en second argument le "critère" qui sera recherché
en troisième argument la colonne dans laquelle ce critère est cherché

Mais dans le cas présent, je ne sais pas si je peux multiplier les critères pour la fonction Indirect car, j'ai plus d'un critère :

j'ai un critère de date qui doit :
- d'une part me permettre de choisir l'onglet qui convient (de janvier à décembre)
- d'autre part de déterminer la colonne dans laquelle se trouve l'information à récupérer et qui apparemment pourrait peut être me servir à :

- identifier la ligne dans laquelle prélever l'information​

J'ai un critère de nom dans une cellule fusionnée

Je n'ai pas besoin d'une solution "clé en main", si on m'explique comment obtenir le résultat je peux y travailler.

Merci

 

[Modeste]

Re,

Je suis fort désolé pour toi, mais les explications que tu donnes à propos de la fonction INDIRECT ... ne sont pas correctes. Je suis plus désolé encore de devoir t'annoncer qu'elles ne ressemblent même pas à ce que fait cette fonction (et sans vouloir t'assommer, je dois avouer que je ne sais même pas quelle fonction tu décris Ce pourrait être la RECHERCHEV dont les 2 premiers arguments seraient inversés et le 4e absent; la fonction EQUIV, mais l'explication du 3e argument ne serait pas la bonne ... bref, on est mal! )

Pour illustrer avec un exemple "simple", la fonction INDIRECT permettrait ici de choisir le bon onglet dans le fichier Source. Dans le classeur "Sem 2.xlsx", la cellule B7 contient une date (le 02/01/2017); si on veut créer une référence vers le classeur "Source", sa feuille "Janvier 2007" et la cellule A11, par exemple, on écrirait:

=INDIRECT("'[Source.xlsx]"&TEXTE(B7;"mmmm aaaa")&"'!A11")

Comme on devra, pour répondre à ta demande, "pointer" vers le bon onglet, parmi les 12 du fichier Source, la fonction INDIRECT pourrait être une partie de la solution MAIS elle ne fonctionnera que si le classeur Source est chaque fois ouvert en même temps que le classeur "Sem 2"
Si tu peux le garantir, on peut avancer dans cette voie. Sinon, soit tu peux rassembler les classeurs en un seul, soit on est reparti sur les solutions INDIRECT.EXT (avec les macros complémentaires de Laurent Longre) soit une autre macro qui inscrirait "en dur" dans les formules INDEX/EQUIV, par exemple le nom de l'onglet.

PS: sous ton avatar, c'est Excel 97 qui est renseigné, mais tes fichiers sont des .xlsx ... il faudrait que tu nous dise quelle version tu utilises.

 

[fb62840]

Rebonjour,

Désolé, pour l'avatar je vais vérifier comment changer, mais j'ai posé ma question en précisant Excel 2010.

J'ai fait une confusion avec INDEX et indirect, toutes mes excuses...

Pour l'utilisation avec les 2 fichiers ouverts ça ne marchera pas, je ne peux garantir qu'ils seront ouverts tous les 2 à chaque fois.

J'ai testé ta proposition et elle fonctionne, il me faut la modifier dans chaque cellule pour obtenir le bon résultat mais ça marche... le problème c'est que là je n'ai que 5 noms et qu'au final je vais en avoir plusieurs dizaines et ça ne me parait pas très "intelligent" (et là je parle de moi bien entendu) de faire ça comme ça.

Une solution pour macro, pourquoi pas ? pourriez-vous m'aider ?

 

[Modeste]

Re-bonjour,

Pour la version d'Excel, tout en haut de la page, dans le ruban bleu, ton pseudo doit apparaître à coté de "Boîte de réception" et "Alertes". En cliquant sur le pseudo, tu arrives sur la page détails personnels, dans laquelle tu trouveras une liste déroulante pour sélectionner ta version.

Pour ce qui est de ta demande, commençons par essayer de comprendre: en Janvier 2007 de Source.xlsx, il y a deux lignes par personne ... chacune devrait correspondre à la plage matin pour la première et après-midi pour la seconde?
Si oui, ouvre les deux fichiers et, en B9 de Sem 2, teste:

=INDEX('[Source.xlsx]Janvier 2017'!$B$11:$AE$21;EQUIV($A9;'[Source.xlsx]Janvier 2017'!$A$11:$A$21;0)+MOD(COLONNE();2);EQUIV(DECALER(B$7;;-MOD(COLONNE();2));'[Source.xlsx]Janvier 2017'!$B$8:$AE$8;0))&""

Formule à recopier à droite, puis arrivé en colonne K, toute la plage B9:K9 est à copier en lignes 13, 17, 21, etc.

Les résultats sont-ils corrects?
Si oui, en fermant le fichier Source, les infos sont-elles toujours affichées en Semaine 2?

 

[fb62840]

Bonjour,

Merci beaucoup pour votre aide et votre patience.
Oui cela fonctionne, et j'aimerais comprendre le fonctionnement de cette formule complexe.
Pourriez-vous m'en décrire le fonctionnement, voici déjà ce que je pense avoir compris

1) '[Source.xlsx]Janvier 2017'!$B$11:$AE$21
Il s'agit de la plage contenant les valeurs à reporter (ABS, E, R, T)

2) ;EQUIV($A9;'[Source.xlsx]Janvier 2017'!$A$11:$A$21;0)+MOD(COLONNE();2)
je crois comprendre que cette portion recherche le nom présent en $A9 dans la plage $A$11:$A:21)
L'utilisation de la forme relative pour le nom permet un ajustement du nom si on recopie la formule tandis que la forme absolue de la plage dans laquelle on recherche le nom fixe celle-ci

3) La fonction MOD renvoie le reste d'une division, ici donc on divise :
Colonne() par 2 et le reste est donc ajouté à la première valeur

4) ;'[Source.xlsx]Janvier 2017'!$B$8:$AE$8;0))&""
Il s'agit de la plage contenant les dates

Par contre je ne comprends pas très bien comment on obtient le résultat final... merci pour votre aide

 

[Modeste]

Bonjour,

Le principe de base est une combinaison très "classique" des fonctions INDEX et EQUIV

Index renvoie la valeur qui, dans une plage donnée (B11:AE21 du fichier Source), figure à l'intersection d'une ligne et d'une colonne. Pour obtenir le n° de ligne, il faut rechercher -dans le même fichier Source- le nom figurant en colonne A et pour la colonne, la position de la date figurant en ligne 7.

C'est à ça que servent les deux fonctions EQUIV; la première permet effectivement de trouver la position de chaque nom, comme tu l'avais compris. L'utilisation de MOD, dans le cas présent, permet de déterminer si on est dans une colonne dont le n° est pair ou impair. Pour les colonnes B, D, F, etc. il faut récupérer les infos du fichier Source qui sont sur la même ligne que le nom; s'il s'agit d'une colonne impaire, il faut que la fonction renvoie ce qui figure une ligne plus bas dans le fichier Source.

La seconde fonction EQUIV, permet de repérer la position des différentes dates. Ici, le problème qui se poserait si on écrivait simplement

=EQUIV(B$7;'[Source.xlsx]Janvier 2017'!$B$8:$AE$8;0)

serait qu'en B7, on a bien une date, mais pas en C7. On en retrouve une en D7, mais pas en E7, ... la fonction ne renverrait donc un résultat qu'une colonne sur 2. Il faut donc qu'en recopiant la formule à droite, on prenne la date en ligne 7 si elle y est bien renseignée, ou dans la colonne qui précède, dans les autre cas. C'est à ça que sert la partie

DECALER(B$7;;-MOD(COLONNE();2))

Elle "décale" donc alternativement, de -0 colonne ou de -1 colonne.

Serait-ce plus clair?

Quant à moi, je n'ai pas compris "je ne comprends pas très bien comment on obtient le résultat final" ... on verra si les explications qui précèdent t'aident (ou si la question était autre)?

Pour la suite, tu n'as pas dit si les résultats étaient toujours corrects lorsque le fichier Source était fermé? Si c'est le cas, ta formule doit se transformer et renseigner, juste devant [Source.xls], le chemin d'accès complet au fichier. Il faudrait que tu le fournisses ici, qu'on puisse l'ajouter dans la macro (ou que tu l'ajoutes toi-même ... à toi de voir).
Par ailleurs, il va falloir décider à quel moment toutes ces formules devront s'inscrire ... on ne peut pas le faire avant que les dates soient renseignées, évidemment. Tu envisages quoi? Un bouton, un raccourci clavier, ou ce serait l'encodage de la première date en B7 qui déclencherait le code?
Enfin, dans ton fichier réel, les noms apparaîtront-ils à partir de la ligne 9 et puis toutes les 4 lignes?

 

[fb62840]

Re-bonjour,

Je vais regarder ça de très près.
Je confirme que même classeur fermé les valeurs apparaissent.

Un énorme merci