Plage dynamique + hauteur dynamique dans la formule DECALER()

sdevers · 27 Février 2016

Bonjour le forum,

Je voudrais nommer une plage dynamique. J'ai donc trouvé un tuto m'expliquant comment faire avec la formule DECALER(). J'y arrive sans problème jusque là.

La syntaxe est donc la suivante
DECALER( Réf; Nombre de lignes; Nombre de colonnes; Hauteur; Largeur )

Ce qui me pose problème c'est définir la Hauteur dont j'ai besoin. Je voudrais que ma plage s'arrête lorsqu'elle rencontre le mot "TOTAL". Mais le mot "TOTAL" peut se trouver dans n'importe quelle colonne (on va dire de A à K) et surtout sur n'importe quelle ligne (l'utilisateur a la faculté d'ajouter autant de lignes dont il a besoin). Moi ce que j'ai besoin de savoir c'est jusqu'à quelle ligne je dois m'arrêter dans ma fonction DECALER.

J'ai créé une formule :SI(NB.SI(4:4;"*TOTAL");TRUE;FALSE) qui me permet de dire si oui ou non le mot "TOTAL" figure oui ou non sur la ligne. Deux problèmes, je n'ai pas envie d'avoir cette colonne de tests, deuxièmement je dois l'intégrer dans ma formule DECALER.

En plus, je devrai lui dire de s'arrêter au moment où il trouve la première fois le mot TOTAL car il y en a évidement plus loin dans le fichier.

Est-ce que l'un de vous pourrait m'aiguiller vers une solution? J'imagine qu'il doit y avoir moyen de calculer mon nombre de lignes sans le mot "TOTAL"...

Merci d'avance,

djidji59430 · 27 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

re
si total est toujours dans la colonne I, la ligne est

equiv("total";$i:$i);0) => 7 en l’occurrence, et ta formule devient :

DECALER( Réf; Nombre de lignes; Nombre de colonnes; equiv("total";$i:$i;0); Largeur )

CISCO · 27 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

Bonjour

Tu peux faire en matriciel, donc à valider avec Ctrl+maj+entrer, avec

Code:

=MIN(SI(NB.SI(DECALER(A$1:M$1;LIGNE(INDIRECT("1:16")););"TOTAL");LIGNE($2:$17)))

mais il a certainement plus simple.

@ plus

sdevers · 27 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

djidji59430 à dit:
re
si total est toujours dans la colonne I, la ligne est

equiv("total";$i:$i);0) => 7 en l’occurrence, et ta formule devient :

DECALER( Réf; Nombre de lignes; Nombre de colonnes; equiv("total";$i:$i;0); Largeur )

Non malheureusement il n'est pas toujours dans la même colonne. Dans l'exemple joint, j'en ai un en I et l'autre en K. Techniquement, le mot TOTAL va se retrouver dans une colonne entre A et K... ça dépendra de plusieurs éléments...

Jocelyn · 27 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

Bonjour le Forum,
Bonjour sdevers,

Un essai en ficchier joint fournis avec un essai d'explication sur la façon de renseigner la partie hauteur de la formule décaler

Cordialement

EDIT Bonjour djidji59430, CISCO , désolé pour la collision

CISCO · 27 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

Bonjour à tous, bonjour Jocelyn

@ Jocelyn : presque la même méthode... mais la tienne est plus concise.

@ plus

sdevers · 27 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

Re,

Merci pour vos formules. Je pense effectivement que ça va fonctionner... C'est sur la bonne voie en tous cas.

Question subsidiaire, j'ai utilisé les formules pour nommer mes plages dynamiques. Si vous regardez dans le Name Manager du fichier en annexe, j'ai créé une plage "Ecart_consersion" et une autre "Produits_reporter".

La plage Ecart_conversion fonctionne comme je le souhaite, c'est-à-dire qu'elle sélectionne de A1 (en l'occurence l'endroit où se trouve mon titre) jusqu'à 3 lignes en-dessous du premier mot "TOTAL" que ma formule trouve (ce qui en principe, revient à dire la ligne juste au-dessus du titre suivant).

Par contre, j'ai voulu utiliser exactement la même formule pour ma plage Produits_reporter, et là il prend 10 lignes de trop. Je ne comprends pas pourquoi...

Voici mes formules:

Code:

=OFFSET(Sheet3!$A$1;;;MIN(IF(ISNUMBER(FIND(IF(Sheet3!$O$1="Français";"TOTAL";"TOTAAL");Sheet3!$1:$10));ROW(Sheet3!$1:$10)))+3;16384)

=OFFSET(Sheet3!$A$11;;;MIN(IF(ISNUMBER(FIND(IF(Sheet3!$O$1="Français";"TOTAL";"TOTAAL");Sheet3!$11:$20));ROW(Sheet3!$11:$20)))+3;16384)

Une idée?

Merci

CISCO · 27 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

Bonjour

Comme tu l'as écrit, la syntaxe de la fonction DECALER est
DECALER( Réf; Nombre de lignes; Nombre de colonnes; Hauteur; Largeur )

et tu utilises ROW(Sheet3!$11:$20) comme hauteur. Donc la plage renvoyée par ce DECALER est au minimum de 11 lignes, et au maximum de 20 lignes.

Dans les deux cas, il faut que tu fasses avec ROW(Sheet3!$1:$10)))+3, ou même plus simplement avec ROW(Sheet3!$4:$13))), ou encore plus simplement ROW($4:$13))), si tu veux renvoyer une plage contenant le même nombre de lignes..

@ plus

sdevers · 27 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

CISCO à dit:
Bonjour

Comme tu l'as écrit, la syntaxe de la fonction DECALER est
DECALER( Réf; Nombre de lignes; Nombre de colonnes; Hauteur; Largeur )

et tu utilises ROW(Sheet3!$11:$20) comme hauteur. Donc la plage renvoyée par ce DECALER est au minimum de 11 lignes, et au maximum de 20 lignes.

Dans les deux cas, il faut que tu fasses avec ROW(Sheet3!$1:$10)))+3, ou même plus simplement avec ROW(Sheet3!$4:$13))), ou encore plus simplement ROW($4:$13))), si tu veux renvoyer une plage contenant le même nombre de lignes..

@ plus

Ok j'ai remis mon ROW(Sheet3!$1:$10)))+3. J'ai créé un 3ème tableau, qui lui n'a pas 10 lignes par défaut mais 20. Comment je peux adapter mon =OFFSET(Sheet3!$A$1;;;MIN(IF(ISNUMBER(FIND(IF(Sheet3!$O$1="Français";"TOTAL";"TOTAAL");Sheet3!$1:$10));ROW(Sheet3!$1:$10)))+3;16384) pour qu'il tienne compte du nombre de lignes avant de trouver le mot TOTAL?
Je pense que EQUIV (MATCH) pourrait servir mais je n'y parviens pas. Un truc du genre à =MATCH(C41;C21:C41;0)-1 mais où je pourrais faire en sorte que C41 soit mon mot TOTAL et que C21:C41 soit la hauteur entre mon titre (mon A1 dans l'autre formule) et la ligne où se trouve TOTAL.

A+

CISCO · 27 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

Rebonjour

Tu peux très bien faire avec Sheet3!$1:$100 ));ROW(Sheet3!$1:$100)))+3, ainsi tu pourras utiliser des tableaux contenant au max 100 lignes. La formule repérera toujours la position du premier TOTAL.

@ plus

sdevers · 27 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

Merci, vous m'avez servi de déclic.

J'avais aussi testé avec une plage plus grande mais sans succès... et j'ai cru que j'étais parti dans une mauvaise direction. Mais j'ai retenté suite à votre remarque !

En fait, je dois juste adapter le début de la zone de test. Je lui demandais de regarder à partir de la ligne 1 et de trouver le premier TOTAL. Pour la première plage, nickel évidemment. Pour la seconde page... nickel aussi (mais je viens de comprendre que c'était un pur "hasard" car mes deux zones avaient le même nombre de lignes jusqu'au TOTAL). Et à la 3ème (plus grande), ça ne marchait plus.

Maintenant, j'ai donc créé les noms de plages suivantes:

Code:

=OFFSET(Sheet3!$A$1;;;MIN(IF(ISNUMBER(FIND(IF(Sheet3!$O$1="Français";"TOTAL";"TOTAAL");Sheet3!$1:$100));ROW(Sheet3!$1:$100)))+3;16384) 'on part de la ligne 1 car c'est l'endroit de mon premier titre

=OFFSET(Sheet3!$A$11;;;MIN(IF(ISNUMBER(FIND(IF(Sheet3!$O$1="Français";"TOTAL";"TOTAAL");Sheet3!$11:$111));ROW(Sheet3!$1:$100)))+3;16384) 'on part la ligne 11 car c'est l'endroit de mon second titre

=OFFSET(Sheet3!$A$21;;;MIN(IF(ISNUMBER(FIND(IF(Sheet3!$O$1="Français";"TOTAL";"TOTAAL");Sheet3!$21:$121));ROW(Sheet3!$1:$100)))+3;16384) 'on part de la ligne 21 car c'est l'endroit de mon troisième titre

=OFFSET(Sheet3!$A$45;;;MIN(IF(ISNUMBER(FIND(IF(Sheet3!$O$1="Français";"TOTAL";"TOTAAL");Sheet3!$45:$145));ROW(Sheet3!$1:$100)))+3;16384) 'on part de la ligne 45 car c'est l'endroit de mon quatrième titre

Test concluant ! Et pourtant ce n’est pas encore parfait. J’ai remarqué que si j’ajoutais des lignes à un endroit du fichier, les noms ne fonctionnaient plus…

Par exemple, en ajoutant 3 lignes, j’obtiens :
=OFFSET(Sheet3!$A$48;;;MIN(IF(ISNUMBER(FIND(IF(Sheet3!$O$1="Français";"TOTAL";"TOTAAL");Sheet3!$48:$148));ROW(Sheet3!$1:$104)))+3;16384)
Et ça ne fonctionne plus du tout sur ma plage n°4.

Et quand j'enlève les $, ça marche encore moins car là, c'est l'endroit où je me trouve dans ma feuille qui influence les zones...

CISCO · 27 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

Bonjour

STP, met un petit fichier en pièce jointe, et explique nous exactement ce que tu veux.

Si c'est avoir plusieurs noms correspondant aux divers tableaux, placés les uns en dessous des autres, tu peux faire avec une formule comme celle proposée par Jocelyn, au début de ce fil, en remplaçant le MIN par un PETITE.VALEUR et en utilisant des différences du style PETITE.VALEUR(......;2) - PETITE.VALEUR(.......;1) pour le deuxième tableau, PETITE.VALEUR(......;3) - PETITE.VALEUR(.......;2) pour le troisième.

@ plus

sdevers · 28 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

Re bonjour à tous,

Voilà mon fichier. J'ai y mis un mot d'explication. Les formules dans Name Manager fonctionnent correctement pour le moment (cliquez sur la formule, vous verrez que la zone sélectionnée correspondt à ce que je veux).
Par contre, si j'aoute des lignes, les formules se décalent et ça ne fonctionne plus...

Si j'enlève les $, c'est pire.

Merci pour votre aide.

CISCO · 28 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

Bonjour

Cf. en pièce jointe.

Comme déja écrit, la syntaxe de la fonction DECALER est
DECALER( Réf; Nombre de lignes; Nombre de colonnes; Hauteur; Largeur )

Si tu veux que tes noms définissent des plages démarrant sur différentes cellules (A1 pour ecart_conversion, A11 pour produits_reporter, etc dans ton fichier exemple) et de différentes hauteurs, tout cela étant variable au cours du temps, il faut une formule modifiant la ref et la hauteur. Il ne faut donc pas que toutes les formules commencent simplement avec un DECALER($A$1;...

C'est fait par ex, dans le fichier "test OFFSET v3", pour produits_reporter, c-à-d pour le second tableau, avec

Code:

=DECALER(
DECALER($A$1:$M$1;PETITE.VALEUR(SI(ESTNUM(TROUVE(SI($O$1="Français";"TOTAL";"TOTAAL");A$1:M$100));LIGNE($1:$100));1)+3;)
;;;PETITE.VALEUR(SI(ESTNUM(TROUVE(SI($O$1="Français";"TOTAL";"TOTAAL");A$1:M$100));LIGNE($1:$100));
2)-PETITE.VALEUR(SI(ESTNUM(TROUVE(SI($O$1="Français";"TOTAL";"TOTAAL");A$1:M$100));LIGNE($1:$100));1))

1) Le second DECALER, c-à-d le DECALER($A$1:$M$1;PETITE.VALEUR(SI(ESTNUM(TROUVE(SI($O$1="Français";"TOTAL";"TOTAAL");A$1:M$100));LIGNE($1:$100));1)+3; ) permet de positionner la ref en A11:M11 (décalage de 10 lignes à partir de A1:M1).

2) Pour ce qui est de la hauteur, la formule repère la position du second TOTAL avec
PETITE.VALEUR(SI(ESTNUM(TROUVE(SI($O$1="Français";"TOTAL";"TOTAAL");A$1:M$100));LIGNE($1:$100));2)
et du premier TOTAL avec
PETITE.VALEUR(SI(ESTNUM(TROUVE(SI($O$1="Français";"TOTAL";"TOTAAL");A$1:M$100));LIGNE($1:$100));1)
En faisant la différence, on obtient la hauteur du second tableau produits_reporter.

Pour les autres tableaux, il suffit de procéder de même mais en changeant les 2 et les 1 par des 3 et des 2 pour le troisième tableau, et par des 4 et des 3 par le quatrième tableau.

Tu remarqueras que la partie SI($O$1="Français";"TOTAL";"TOTAAL");A$1:M$100));LIGNE($1:$100)) se retrouve très souvent dans les formules ci-dessus et dans les noms dans le gestionnaire de noms. On peut donc lui donner un nom, par ex plage, et la formule pour produits_reporter, pour le second tableau, devient tout simplement

Code:

=DECALER(DECALER($A$1:$M$1;PETITE.VALEUR(plage;1)+3;);;;PETITE.VALEUR(plage;2)-PETITE.VALEUR(plage;1))

Pour le troisième tableau, cela donne

Code:

DECALER(DECALER($A$1:$M$1;PETITE.VALEUR(plage;2)+3;);;;PETITE.VALEUR(plage;3)-PETITE.VALEUR(plage;2))

et pour le quatrième

Code:

=DECALER(DECALER($A$1:$M$1;PETITE.VALEUR(plage;3)+3;);;;PETITE.VALEUR(plage;4)-PETITE.VALEUR(plage;3))

J'ai utilisé cette méthode dans tous les noms du fichier "test OFFSET v3bis".

Autre détail, tu remarqueras que la formule démarre avec un DECALER($A$1:$M$1 et que j'ai supprimé le 16384 à la fin de tes formules. J'ai fait cela, car mon ordi ramait vraiment trop en travaillant sur 16384 colonnes, dans la fenêtre "Evaluation des formules" ! Tu peux bien sûr faire avec des DECALER($A$1; et mettre à la fin 13, si tu ne veux n'utiliser que les 13 premières colonnes dans ton fichier réel, ou remettre 16384 si tu veux utiliser toutes ces colonnes.

Dernière remarque : On peut procéder autrement, en utilisant comme ref les noms qu'il y a au début des plages correspondantes, Ecarts de conversion, Produits à reporter. J'essaye de te faire cela dans l'après midi.

@ plus

CISCO · 28 Février 2016

Re : Plage dynamique + hauteur dynamique dans la formule DECALER()

Bonjour

Comme dit dans le précédent post, on peut aussi faire avec les "noms" des tableaux, écrits dans A1, A11... Cf. en pièce jointe.

@ plus

Plage dynamique + hauteur dynamique dans la formule DECALER()

sdevers

XLDnaute Nouveau

Pièces jointes

djidji59430

XLDnaute Barbatruc

CISCO

XLDnaute Barbatruc

sdevers

XLDnaute Nouveau

Jocelyn

XLDnaute Barbatruc

Pièces jointes

CISCO

XLDnaute Barbatruc

sdevers

XLDnaute Nouveau

Pièces jointes

CISCO

XLDnaute Barbatruc

sdevers

XLDnaute Nouveau

CISCO

XLDnaute Barbatruc

sdevers

XLDnaute Nouveau

CISCO

XLDnaute Barbatruc

sdevers

XLDnaute Nouveau

Pièces jointes

CISCO

XLDnaute Barbatruc

Pièces jointes

CISCO

XLDnaute Barbatruc

Pièces jointes

Discussions similaires