Problème basique avec INDIRECT
Bonjour à toutes et à tous,
J'ai une formule de base et qui marche bien :
=SIERREUR(INDEX('[PLANNING EMPOTAGE.xlsm]Planning'!$C$4:$K$1099;EQUIV($O3;'[PLANNING EMPOTAGE.xlsm]Planning'!$K$4:$K$1099;0);1);0)Le problème, c'est que la dernière ligne, ici 1099, est variable car je suis susceptible de rajouter des lignes au tableau.
J'ai donc mis ça dans une cellule que j'ai appelée NbLignes :
=NBVAL('[PLANNING EMPOTAGE.xlsm]Planning'!$B:$B)qui a donc pour valeur 1 099.
Et j'ai donc modifié ma formule initiale pour ça :
=INDEX(INDIRECT("'[PLANNING EMPOTAGE.xlsm]Planning'!$C4:$K"&NbLignes);EQUIV($O3;INDIRECT("'[PLANNING EMPOTAGE.xlsm]Planning'!$C4:$K"&NbLignes);0);1)Et... ça ne marche pas, ça me renvoie #N/A.
Qu'est-ce que j'ai raté ? J'ai essayé de modifier ma cellule NbLignes en rajoutant TEXTE(...;"####"), mais pareil.
Bonsoir
Il vous faut utiliser la formule DECALER en définissant un nom sur la plage utilisée via le "gestionnaire de noms" -> "Formule" -> "Nouveau"
Là vous définissez PLAGE (ou un autre qui vous convient) et dans "référence à" vous mettez --> =DECALER(PLANNING!$C$4;;;NBVAL(PLANNING!$C:$C);9)
Ensuite dans votre formule vous remplacez C4:K1099 par le mot PLAGE
Le mieux serait de voir un fichier (sans données confidentielles)
Cordialement
Edit :
- cela suppose que toutes les cellules de la colonne C comporte une valeur
- Pour la colonne K (plage K4 à K1099) dans votre formule, vous pouvez créer une deuxième nom en mettant cette formule dans "Référence à" --> =DECALER(Planning!$C$4;;8;NBVAL(Planning!$C:$C);)
- Ensuite dans votre formule vous remplacer K4:K1099 par ce nom
Merci Dan pour votre réponse.
Donc, si j'ai bien compris, ma formule devient (sans l'utilisation pour le moment de cellules nommées) :
=INDEX(INDIRECT(DECALER('[PLANNING EMPOTAGE.xlsm]Planning'!$C$4;;;NBVAL('[PLANNING EMPOTAGE.xlsm]Planning'!$C:$C);9));EQUIV($O3;INDIRECT(DECALER('[PLANNING EMPOTAGE.xlsm]Planning'!$K$4;;;NBVAL('[PLANNING EMPOTAGE.xlsm]Planning'!$K:$K)));0);1)Là, j'obtiens un joli "#Valeur!"... J'ai raté quelque chose !
Là, j'obtiens un joli "#Valeur!"... J'ai raté quelque chose !
Pourquoi vous ne repartez pas de la colonne C dans la deuxième partie de la formule (mettre $C$4, le chiffre 8 et $C:$C)
Ensuite réutilisez la première formule =SIERREUR(INDEX..... que vous avez postée en premier
Alors, ça me donne :
=INDEX(INDIRECT(DECALER('[PLANNING EMPOTAGE.xlsm]Planning'!$C$4;;;NBVAL('[PLANNING EMPOTAGE.xlsm]Planning'!$C:$C);9));EQUIV($O3;INDIRECT(DECALER('[PLANNING EMPOTAGE.xlsm]Planning'!$C$4;;;NBVAL('[PLANNING EMPOTAGE.xlsm]Planning'!$C:K);8));0);1)J'ai toujours comme résultat #Valeur!
Et je ne mets pas de SI.ERREUR pour le moment car dans ma formule initiale, j'obtiens bien un résultat et non #Valeur!. Je ne voudrais pas qu'on confonde (pour le moment) un retour de #Valeur! par une réelle valeur nulle...
Pourquoi utiliser vous Indirect puisque dans votre premier post vous dites --> J'ai une formule de base et qui marche bien. Le problème, c'est que la dernière ligne, ici 1099, est variable car je suis susceptible de rajouter des lignes au tableau.
Puis ce n'est pas correct dans cette partie de votre formule --> ...!$C$4;;;NBVAL('[PLANNING EMPOTAGE.xlsm]Planning'!$C:K);8)
regardez ce que j'ai proposé avant --> ...!$C$4;;8;NBVAL('[PLANNING EMPOTAGE.xlsm]Planning'!$C:$C);)
OK, j'ai supprimé les INDIRECT(...) et j'ai modifié le :K par :$C (logique...). J'ai donc ça :
=INDEX(DECALER('[PLANNING EMPOTAGE.xlsm]Planning'!$C$4;;;NBVAL('[PLANNING EMPOTAGE.xlsm]Planning'!$C:$C);9);EQUIV($O3;DECALER('[PLANNING EMPOTAGE.xlsm]Planning'!$C$4;;;NBVAL('[PLANNING EMPOTAGE.xlsm]Planning'!$C:$C);8);0);1)Et...
#N/A
[CENSURÉ] !!!
Non le 8 n'est pas au bon endroit !
=INDEX(DECALER('[PLANNING EMPOTAGE.xlsm]Planning'!$C$4;;;NBVAL('[PLANNING EMPOTAGE.xlsm]Planning'!$C:$C);9);EQUIV($O3;DECALER('[PLANNING EMPOTAGE.xlsm]Planning'!$C$4;;8;NBVAL('[PLANNING EMPOTAGE.xlsm]Planning'!$C:$C););0);1)edit : au fait vous travaillez sur deux fichiers ? vous récupérez une info depuis le fichier Planning Empotage, feuille Planning pour mettre le résultat dans un autre fichier ?
Le boulet... Le boulet...
Effectivement, c'est beaucoup mieux comme ça !!
Du coup, j'ai essayé de mettre :
=DECALER('[PLANNING EMPOTAGE.xlsm]Planning'!$C$4;;;NBVAL('[PLANNING EMPOTAGE.xlsm]Planning'!$C:$C);9)dans une cellule dans l'objectif de la nommer (PLAGE ?) mais j'ai un #VALEUR!
Comme je vais répéter cette partie plus de 2 000 fois, ça m'aurait bien plus d'optimiser le fichier en ne faisant le calcul qu'une fois au lieu des 2 000 fois... Mais est-ce que ça a un intérêt (à part réduire le fichier des quelques dizaines de ko car la formule est un peu plus longue) ?
Et question subsidiaire : ai-je intérêt à créer dans ce classeur une feuille "Données" qui reprendrait les 400 lignes x 5 colonnes de mes données issues de l'autre classeur, au lieu de les appeler directement, pour optimiser la rapidité du tableau ?
Bonjour
Non il ne faut pas mettre la formule dans la cellule mais créer un nom avec cette formule et utiliser le nom dans la formule sur votre feuille. Utilisée comme cela dans une cellule na va rien vous donner sauf une erreur. Vous devez faire comme je vous ai expliqué hier à 18h15 -> https://forum.excel-pratique.com/s/goto/933398
Et question subsidiaire : ai-je intérêt à créer dans ce classeur une feuille "Données" qui reprendrait les 400 lignes x 5 colonnes de mes données issues de l'autre classeur, au lieu de les appeler directement, pour optimiser la rapidité du tableau ?
Il faudrait que je comprenne mieux ce que vous faites. Là sans fichier ce n'est pas facile de vous aider.
Là j'ai l'impression que vous avez deux fichiers que vous voulez récupérer quelque chose du fichier Planning empotage....
Bonjour Dan,
Tout d'abord, je vais me fustiger. Non mais, sérieux, il faut vraiment que j'apprenne à lire et que j'arrête de parcourir en diagonale. J'ai effectivement raté cette information capitale. J'ai donc suivi vos instructions et ça marche terriblement bien. J'ai réduit mon fichier de 320 ko à un peu plus de 270 ko.
Je tenais donc à vous présenter mes excuses pour ne pas avoir lu, ce qui m'horripile pourtant quand ça vient de quelqu'un d'autre. Mea culpa.
Concernant ma question sur l'import global des données, effectivement, c'est délicat pour moi de vous envoyer des fichiers. Dans le cas présent, j'ai deux classeurs (on va la faire courte, car j'ai deux autres classeurs qui viennent se brancher sur le classeur Alpha avec les mêmes problématiques).
L'un (Alpha) contient des données de gestion générale mains interne : N° de commande (ma clé au sens Access), suivi d'un grand nombre de données (bateau, clients, dates, quantités, ...). Alpha regroupe toutes les commandes, actuellement de 2019 à 2021 (et amené à s'étendre), soit environ 150 commandes par an, réparties sur 365 lignes par an (une ligne par jour, obligatoire). Soit actuellement 1 099 lignes, entête compris. Et 48 colonnes... Oui, je sais, mais on ne peut rien toucher de fondamental là dedans.
Le second (Beta) contient 13 feuilles, soit une feuille pour chaque client, avec des informations comptables et diverses, destinées à chaque client, rattachées au numéro de commande. Et donc sur la base de ce numéro de commande saisi manuellement (pour le moment, mais je réfléchis à automatiser ça), ça va importer depuis Alpha 5 données en relation avec cette commande : nombre de containers, Compagnie et bateau, dates de départ et d'arrivée. Le reste est strictement limité à ce classeur (et 20 colonnes de plus !)
Le calcul sur lequel vous m'avez aidé se répète donc 5 fois par ligne dans Beta, en allant en rapatriant depuis Alpha dans Beta les 5 informations parmi aujourd'hui 1 099 lignes, dont à l'heure actuelle 300 sont réellement utiles, sans doute environ 450 fin 2021.
La question est donc la suivante : est-il plus efficace d'aller récupérer les 300 à 450 lignes de n° de commande dans Alpha, ainsi que les 5 données afférentes, de les placer en dynamique dans une feuille Données dans Beta, pour qu'ensuite chaque feuille de Beta ne pointe plus qu'en local et sur un nombre plus limité de lignes (300/450 au lieu de 1 099) ?
Sur mon PC, à chaque modification de Alpha, ça me ralentit déjà significativement, le temps d'aller mettre à jour les 13 feuilles de Beta. Et pourtant c'est une bonne machine. Le PC entrée de gamme qui a 3 ans de ma femme ne le vit pas aussi bien ! D'où ma volonté d'optimiser mon code.
En tout cas, un grand merci pour votre aide et votre patience !
La question est donc la suivante : est-il plus efficace d'aller récupérer les 300 à 450 lignes de n° de commande dans Alpha, ainsi que les 5 données afférentes, de les placer en dynamique dans une feuille Données dans Beta, pour qu'ensuite chaque feuille de Beta ne pointe plus qu'en local et sur un nombre plus limité de lignes (300/450 au lieu de 1 099) ?
Par rapport à votre question, ma réponse est uniquement OUI !. Les liaisons entre fichiers ralentissent toujours excel.
Gardez en mémoire que pour chaque modification apportées à Beta ou Alpha, excel va recalculer toutes les formules. et là....
Au vu de ce que vous écrivez, le mieux pour vous est d'importer les données nécessaires de Alpha vers Beta via un petit code VBA. Vos formules étant dans Beta uniquement vous allez gagner en rapidité mais aussi en lecture de formules au cas où vous auriez un changement à apporter.
Cordialement