Décaler une formule sur plusieurs colonnes
Bonjour à tous,
J'ai la formule suivante :
=INDIRECT("D"&MIN(SI(NON(ESTNA(INDIRECT("D$7:D$1000")));LIGNE(INDIRECT("D$7:D$1000")))))-D2
Celle-ci me permet d'aller chercher dans la colonne D, la première valeur trouvée après les #N/A. Cette valeur est ensuite soustraite à la valeur renseigné en D2. Vous trouverez la formule dans la cellule Jaune du fichier en PJ.
Mon fichier faisant env. 1000 colonnes, je souhaite trouver le moyen "d'étirer" la formule vers la droite. Existe-t-il un complément de formule (avec DECALER peut-être) pour pouvoir y arriver ?
Actuellement, je suis obligé de remplacer tous les "D" par "E" pour la colonne E, par "F" pour la colonne F; etc.... Je vous laisse imaginer le temps que ça prend...
Merci pour votre aide !!
YggyPop, jeune apprenti excel
Bonjour,
YggyPop, jeune apprenti excel
formule balaise déjà !!
On aurait pu remplacer le D par CAR(64+COLONNE()) mais cela semble pas opportun avc une formule matricielle
Néanmoins je pense que le INDIRECT n'est pas utile et qu'il vaut mieux utiliser INDEX ou DECALER (si j'y arrive ...)
Tu peux tirer cette formule sur la droite
=INDEX(D$7:D$1000;MIN(SI(NON(ESTNA(D$7:D$1000));LIGNE(D$7:D$1000)))-6)-D2Salut tout le monde,
sinon comme ca:
=INDIRECT(ADRESSE(MIN(SI(NON(ESTNA(D$7:D$1000));LIGNE(D$7:D$1000)));COLONNE()))-D2Bonjour,
Merci pour vos proposition !!
En effet, votre formule fonctionne et je peux la décaler. Néanmoins, tous les jours je vais insérer des lignes et toutes les dates antérieures vont basculer en #N/A. avec votre formule, la ligne 7 et la ligne 1000 vont basculer en 8 et 1001 ce qui ne m'arrange pas... est-il possible de "bloquer" le D7:D1000 tout en gardant la possibilité d'étirer la formule ?
=INDIRECT(ADRESSE(MIN(SI(NON(ESTNA(D$7:D$1000));LIGNE(D$7:D$1000)));COLONNE()))-D2
=INDEX(D$7:D$1000;MIN(SI(NON(ESTNA(D$7:D$1000));LIGNE(D$7:D$1000)))-6)-D2
Merci,
YggyPop, jeune apprenti Excel
En complément, ci-après les trois formules qu'on m'a proposé :
=INDIRECT(ADRESSE(MIN(SI(NON(ESTNA(D$7:D$1000));LIGNE(D$7:D$1000)));COLONNE()))-D2 -->OK je peux étirer mais dès qu'on insère une ligne, la 7 et 1000 deviennent 8 et 1001; et ainsi de suite...
=INDEX(D$7:D$1000;MIN(SI(NON(ESTNA(D$7:D$1000));LIGNE(D$7:D$1000)))-6)-D2 -->OK je peux étirer mais dès qu'on insère une ligne, la 7 et 1000 deviennent 8 et 1001; et ainsi de suite...
=DECALER(E$1;MIN(SI(NON(ESTNA(DECALER(E$6;1;;994)));LIGNE(INDIRECT("D7:D1000"))))-1;)-E2 --> les cellules sont bien figés mais je ne peux pas l'étirer vers la droite...
Comme mentionné, tous les jours je vais utiliser une simple macro pour coller les valeurs d'un onglet, sur un autre onglet et automatiquement insérer une nouvelle ligne en ligne 7.
Je dois donc à la fois trouver une formule qui "fige" pour ne pas que les lignes bougent et que je puisse également l'étirer vers la droite.
Merci,
YggyPop
=INDEX(D$7:D$1000;MIN(SI(NON(ESTNA(DECALER($D$1;6;;1000)));LIGNE(DECALER($D$1;6;;1000))))-6)-D2=INDEX(D$7:D$1000;MIN(SI(NON(ESTNA(DECALER($D$1;6;;1000)));LIGNE(DECALER($D$1;6;;1000))))-6)-D2
Bingo, ça fonctionne !
Par ailleurs, pensez-vous pouvoir me faire la même chose avec la formule en cellule D2 du fichier que vous venez de m'envoyer ?
Idem, je dois pouvoir l'étirer vers la droite tout en maintenant la ligne 7:1000
formule de base : =RECHERCHE(9^9;INDIRECT("D$7:D$1000"))
L'idée étant de trouver, dans la colonne X, la première valeur renseignée.
Je ne suis pas très familier avec la fonction décaler qui portant à l'air d'être la solution à tout problème !
Merci,
YggyPop
D'abord, il y a une erreur sur la formule que j'avais donnée ($ en trop) :
=INDEX(D$7:D$1000;MIN(SI(NON(ESTNA(DECALER(D$1;6;;1000)));LIGNE(DECALER(D$1;6;;1000))))-6)-D2Et voilà pour D2
=RECHERCHE(9^9;DECALER(D$1;6;;1000))avec le fichier
Steelson,
j'avais repéré la petite coquille du "$"
Merci pour la nouvelle formule, c'est parfait !!
Merci à vous et excellente journée