[Résolu] Insérer une variable dans une formule comprenant INDEX et AGREGAT

[Eric 888]

Bonjour à tous

J'ai trouvé sur le forum une formule pour contourner l'absence de FILTER dans Excel (je viens de GSheet).

Dans mon cas ça donne ça, formule 1:
=INDEX(collectecptes!$A$1:$A$4800;AGREGAT(15;6;((collectecptes!$B$1:$B$4800 = E$3)/(collectecptes!$B$1:$B$4800 = E$3)*LIGNE(collectecptes!$B$1:$B$4800))-LIGNE(collectecptes!$B$1)+1;LIGNES(collectecptes!$B$1:$B1)))

Le 4800 est lié aux données sources sur la feuille 'collectecptes', je veux récupérer des données qui elles-mêmes sont le résultat d'un filtre, ça peut faire pas mal de lignes à évaluer (je passe sur le pourquoi).

Double problème:
- comment optimiser les calculs en limitant le nombre de lignes évaluées à ce qui est réellement utile
- comment éviter d'avoir à recoder en dur ce chiffre si j'ajoute des lignes à évaluer

Du coup je voudrais pouvoir faire passer en variable le nombre de lignes utiles; je voudrais donc remplacer 4800 par le résultat du calcul des lignes utiles.
Dans mon cas ça donne ça:
=((COLONNE('methode portefeuilles 2020'!AW8)-COLONNE('methode portefeuilles 2020'!C8))/2)-(LIGNE('methode portefeuilles 2020'!B263)-LIGNE('methode portefeuilles 2020'!B63))
Je stocke le résultat dans la cellule: 'calculs intermediaires'!B4

Donc, dans la formule 1, je voudrais remplacer 4800 par la valeur de la cellule 'calculs intermediaires'!B4

J'ai essayé de bidouiller avec CONCATENER, ADRESSE, INDIRECT... j'y arrive point :-(

Si vous aviez une soluce, ça m'aiderait bien, merci d'avance
(pas de VBA: ça doit tourner dans Excel Online)

 

[chris]

Bonjour

C'est INDIRECT
INDIRECT("collectecptes!$A$1:$A$"&'calculs intermediaires'!B4)

Mais tu devrais poster une exemple, il y a peut-être plus simple

 

[Hasco]

Bonjour,

Transformez votre tableau en tableau structuré (Insertion/Tableau) donnez lui le nom que vous désirez dans l'onglet création qui s'affichera après la création du tableau.

Les tableaux structurés adaptent leurs références en fonction du nombre de lignes.

Dans votre formule utilisez les références structurées ex NomTableau[NomColone].

Si vous ne connaissez pas les tableaux structurés voici un tuto (qui date un peu mais encore très bien) https://fauconnier.developpez.com/tutoriels/tableaux-structures/

Cordialement

[Edit] hello @chris

 

[MP59]

Bonjour à tous,
voici un petit exemple de l'utilisation d'indirect.

 

[Eric 888]

Bonjour

C'est INDIRECT
INDIRECT("collectecptes!$A$1:$A$"&'calculs intermediaires'!B4)

Mais tu devrais poster une exemple, il y a peut-être plus simple

Miracle, joie, extase: ça marche.
Honte sur moi.
Merci bcp

Merci à tous les ceusses qui ont répondu.

 

[chris]

RE

Je continue à penser qu'une optimisation est possible...

 

[Eric 888]

RE

Je continue à penser qu'une optimisation est possible...

hello Chris
j'en suis moi aussi persuadé, surtout vu mon niveau de maîtrise d'Excel, mais vu tous les trucs en cascade qui aboutissent à mon besoin, je vois pas comment formuler mon attente sans te balancer tout le tableur et passer un moment à en causer de vive voix
moi chuis preneur mais bon j'ai peur qu'une réelle optimisation suppose de carrément repartir de la logique de construction et de passer en revue tous mes bricolages - c'est dingue comment c'est pas simple de passer de Gsheets à Excel quand on a appris les tableurs, en autodidacte, dans le premier...
++